com!online 2002 April

home *** CD-ROM | disk | FTP | other *** search

/ com!online 2002 April / comcd0402.iso / vollver / adabas / f_0001 / env / load.enh < prev next >

Wrap

Text File | 1999-11-01 | 129.1 KB | 1,786 lines

ENG|06 | 0 LOAD.enh 10.05.01 1998-04-24 ENG|06 | 1| | Index of the Functions which May Be Used in LOAD ENG|06 | 2| | ------------------------------------------------ ENG|06 | 4| M| #02How^to^Use^LOAD^Interactively ENG|06 | 5| M| #03...^Fill^Database^Tables (DATALOAD / FASTLOAD) ENG|06 | 6| M| #04...^Change^Column^Contents (DATAUPDATE) ENG|06 | 7| M| #05...^Merge^a^File^into^a^Table (DUPLICATES clause) ENG|06 | 8| M| #06...^Create^Formatted^Reports (DATAEXTRACT) ENG|06 | 9| M| #07...^Put^and^Get^LONG^Column^Values (using DATALOAD / DATAEXTRACT) ENG|06 | 10| M| #08...^Transfer^the^Database^Catalog (EXTRACT/LOAD CATALOG) ENG|06 | 11| M| #09...^Transfer^Table^Contents (EXTRACT/LOAD TABLE) ENG|06 | 12| M| #10...^Transfer^Catalog^and^Data (DBEXTRACT/DBLOAD) ENG|06 | 13| M| #11...^Use^the^ORACLE^Crossloader (LOAD ORACLEDB, INFILE ... ORACLE) ENG|06 | 14| M| #13Other^LOAD^Statements (SQLMODE, AUTOCOMMIT ON/OFF etc) ENG|06 | 15| M| #14Control^File^Statements ENG|06 | 16| M| #15LOAD^Commands (==> RUN etc) ENG|06 | 17|RI| ##09SQL^Statements (SELECT, CREATE etc) ENG|060103 | 1| | <load statement> ENG|060103 | 4| M| { #01<dataload^statement> ENG|060103 | 5| M| | #02<dataload^default^statement> ENG|060103 | 6|RM| | #03<fastload^statement> } ENG|06010301 | 1| | <dataload statement> ENG|06010301 | 3| | <load table spec>... ENG|06010301 | 4| I| ##06011721<infile^spec> ENG|06010301 | 7| | <load table spec> ENG|06010301 | 9| | { DATALOAD | LOAD DATA } ENG|06010301 | 10| | TABLE <table name> ENG|06010301 | 11| I| [ { IF ##06010307<condition> | OTHERWISE } ] ENG|06010301 | 12|RI| ##06010305<load^column^spec>... ENG|06010302 | 1| | <dataload default statement> ENG|06010302 | 3| | { DATALOAD | LOAD DATA } ENG|06010302 | 4| | TABLE <table name> ENG|06010302 | 5| I| [ IF ##06010307<condition> ] ENG|06010302 | 6|RI| ##06011721<infile^spec> ENG|06010303 | 1| | <fastload statement> ENG|06010303 | 3| | { FASTLOAD | LOAD FAST } ENG|06010303 | 4| | [ WITH <unsigned integer> % USAGE ] ENG|06010303 | 5| | TABLE <table name> ENG|06010303 | 6| I| [ IF ##06010307<condition> ] ENG|06010303 | 7| I| [ ##06010305<load^column^spec>... ] ENG|06010303 | 8|RI| ##06011721<infile^spec> ENG|06010305 | 1| | <load column spec> ENG|06010305 | 3| I| { ##0601030501<column^descriptor> [ <null condition> ] ENG|06010305 | 4| I| | ##0601030502<column^assignment> } ENG|06010305 | 7| | <null condition> ENG|06010305 | 9|RI| { NULL [ IF ] ##06010307<condition> | DEFAULT NULL } ENG|0601030501 | 1| | <column descriptor> ENG|0601030501 | 3|RI| <column name> ##06010308<pos^spec> ##06010306<format^spec> ENG|0601030502 | 1| | <column assignment> ENG|0601030502 | 3| | <column name> { '<literal>' | <generate spec> } ENG|0601030502 | 6| | <generate spec> ENG|0601030502 | 8| | { <adabas value> ENG|0601030502 | 9| | | <ansi value> ENG|0601030502 | 10| | | <db2 value> ENG|0601030502 | 11| | | <oracle value> ENG|0601030502 | 12| | | SEQNO [ <start> [ <increment> ] ] } ENG|0601030502 | 15| | <adabas value> ENG|0601030502 | 17|S | { USER | USERGROUP | DATE | TIME | TIMESTAMP | TRUE | FALSE | STAMP } ENG|0601030502 | 19| | <ansi value> ENG|0601030502 | 21| | USER ENG|0601030502 | 24| | <db2 value> ENG|0601030502 | 26| | { USER | CURRENT { SQLID | DATE | TIME | TIMESTAMP } } ENG|0601030502 | 29| | <oracle value> ENG|0601030502 | 31| | { USER | SYSDATE | UID } ENG|0601030502 | 34| | <start>, <increment> ENG|0601030502 | 36|R | [ signed ] integer ENG|06010306 | 1| | <format spec> ENG|06010306 | 3| I| { <format of numeric columns> [ HEX ] ##0601030601<options> ENG|06010306 | 4| | | <format of oracle exp columns> ENG|06010306 | 5| | | [ CHAR ] [ HEX ] } ENG|06010306 | 8| | <format of oracle exp columns> ENG|06010306 | 10| | { DATE ENG|06010306 | 11| | | NUMBER } ENG|06010306 | 14| | <format of numeric columns> ENG|06010306 | 16| | { [ CHAR ] [ FLOAT ] ENG|06010306 | 17| | | DECIMAL [ <fraction> ] ENG|06010306 | 18|S | | ZONED [ <fraction> ] ENG|06010306 | 19| | | INTEGER ENG|06010306 | 20|R | | REAL } ENG|0601030601 | 1| | <options> ENG|0601030601 | 3| | [ <scale spec> ] [ <round or trunc spec> ] ENG|0601030601 | 6| | <scale spec> ENG|0601030601 | 8| | SCALE { <integer> | ( <integer> ) } ENG|0601030601 | 11| | <round or trunc spec> ENG|0601030601 | 13| | { ROUND | TRUNC } { <unsigned integer> | ( <unsigned integer> ) } ENG|0601030601 | 16| | <integer> ENG|0601030601 | 18|R | [ <sign> ] <unsigned integer> ENG|06010307 | 1| | <condition> ENG|06010307 | 3| | { <simple condition> ENG|06010307 | 4| | | ( <condition> ) ENG|06010307 | 5| | | <condition> AND <condition> ENG|06010307 | 6| | | <condition> OR <condition> ENG|06010307 | 7| | | NOT <condition> } ENG|06010307 | 10| | <simple condition> ENG|06010307 | 12| I| POS ##06010308<pos^spec> [ <field format> ] [ HEX ] <comp op> '<literal>' ENG|06010307 | 15| | <field format> ENG|06010307 | 17| | { [ CHAR ] ENG|06010307 | 18|S | | DECIMAL <fraction> ENG|06010307 | 19| | | ZONED <fraction> ENG|06010307 | 20| | | INTEGER ENG|06010307 | 21| | | REAL } ENG|06010307 | 24| | <comp op> ENG|06010307 | 26|R | { < | <= | = | >= | > | <> } ENG|06010308 | 1| | <pos spec> ENG|06010308 | 3| | <start pos> [ - <end pos> ] ENG|06010308 | 6| | <start pos>, <end pos> ENG|06010308 | 8|R | 1 <= Position <= 4096 ENG|060104 | 1| | <dataupdate statement> ENG|060104 | 3| | <update table spec>... ENG|060104 | 4| I| ##06011721<infile^spec> ENG|060104 | 7| | <update table spec> ENG|060104 | 9| | { DATAUPDATE | UPDATE DATA } ENG|060104 | 10| | TABLE <table name> ENG|060104 | 11| I| [ { IF ##06010307<condition> | OTHERWISE } ] ENG|060104 | 12| | <access column spec>... ENG|060104 | 13| | <set column spec>... ENG|060104 | 16| | <access column spec> ENG|060104 | 18|SI| [ KEY ] { ##0601030501<column^descriptor> |##0601030502<column^assignment> } ENG|060104 | 19| | <set column spec> ENG|060104 | 21|RI| SET ##06010305<load^column^spec> ENG|060105 | 1| | <dataload-and-update statement> ENG|060105 | 3| | <load-and-update spec>... ENG|060105 | 4| I| ##06011721<infile^spec> ENG|060105 | 7| | <load-and-update spec> ENG|060105 | 9| | { DATALOAD | LOAD DATA } ENG|060105 | 10| | TABLE <table name> ENG|060105 | 11| I| [ { IF ##06010307<condition> | OTHERWISE } ] ENG|060105 | 12| | { IGNORE | REJECT | UPDATE } DUPLICATES ENG|060105 | 13| | <key column spec>... ENG|060105 | 14|S | <set column spec>... ENG|060105 | 19| | <key column spec> ENG|060105 | 21| I| KEY { ##0601030501<column^descriptor> |##0601030502<column^assignment> } ENG|060105 | 24| | <set column spec> ENG|060105 | 26|RI| SET ##06010305<load^column^spec> ENG|060106 | 1| | <extract statement> ENG|060106 | 3| | { DATAEXTRACT | EXTRACT DATA } ENG|060106 | 4| | [ [ WITH ] LOCK ] ENG|060106 | 5| | <select expression> ; ENG|060106 | 6| | [ <output field spec>... ] ENG|060106 | 7| | [ OUTFILE <external file name> ] ENG|060106 | 8| I| ##06011722<outfile^spec> ENG|060106 | 9| I| [ ##06011723<longfile^spec>... ] ENG|060106 | 12| | <select expression> ENG|060106 | 14| | <select statement> without keyword SELECT ENG|060106 | 16| | <output field spec> ENG|060106 | 18|S | { <column data spec> | <constant data spec> } ENG|060106 | 19| | <column data spec> ENG|060106 | 21| I| <column id> ##06010308<pos^spec> ##06010306<format^spec> [ <null presentation> ] ENG|060106 | 24| | <constant data spec> ENG|060106 | 26| I| '<literal>' ##06010308<pos^spec> ENG|060106 | 29| | <column id> ENG|060106 | 31| | { <column name> | <column number> } ENG|060106 | 34| | <null presentation> ENG|060106 | 36|RI| [ IF ] NULL [ SET ] POS ##06010308<pos^spec> = '<literal>' ENG|06010608 | 1| | <restore statement> ENG|06010608 | 3| | { DATAEXTRACT | EXTRACT DATA } ENG|06010608 | 4| | [ [ WITH ] LOCK ] ENG|06010608 | 5| | { FOR DATALOAD | FOR FASTLOAD | FOR DATAUPDATE } ENG|06010608 | 6| | TABLE <table name> ENG|06010608 | 7| | [ <order clause> ; ] ENG|06010608 | 8| | { <command and data file> | <command file> <data file> } ENG|06010608 | 9| I| [ ##06011723<longfile^spec>... ] ENG|06010608 | 12| | <command and data file>, <command file>, <data file> ENG|06010608 | 14| | OUTFILE <external file name> ENG|06010608 | 15| I| ##060117<file^format> ENG|06010608 | 16|R | [ APPEND ] ENG|06010801 | 1| | <catalogextract statement> ENG|06010801 | 3| | { EXTRACT CATALOG | CATALOGEXTRACT } ENG|06010801 | 4| | { TABLE <table name> | USER | ALL } ENG|06010801 | 5| | OUTFILE <external file name> ENG|06010801 | 6| | [ <code spec> <sqlmode> <compress spec> <count spec> ] ENG|06010801 | 7| | [ APPEND ] ENG|06010801 | 10| | <code spec>, <sqlmode>, <compress spec>, <count spec> ENG|06010801 | 12|RI| ##060117<file^format> ENG|06010802 | 1| | <catalogload statement> ENG|06010802 | 3| | { LOAD CATALOG | CATALOGLOAD } ENG|06010802 | 4| | { TABLE <table name> | USER | ALL } ENG|06010802 | 5| | INFILE <external file name> ENG|06010802 | 6| | [ <code spec> <sqlmode> <count spec> ] ENG|06010802 | 9| | <code spec>, <sqlmode>, <count spec> ENG|06010802 | 11|RI| ##060117<file^format> ENG|06010901 | 1| | <tableextract statement> ENG|06010901 | 3| | { EXTRACT TABLE | TABLEEXTRACT } ENG|06010901 | 4| | { [ TABLE ] <table name> | USER | ALL } ENG|06010901 | 5| | <table content file> ENG|06010901 | 8| | <table content file> ENG|06010901 | 10| | OUTFILE <external file name> ENG|06010901 | 11| | [ <count spec> ] ENG|06010901 | 14| | <count spec> ENG|06010901 | 16|RI| ##060117<file^format> ENG|06010902 | 1| | <tableunload statement> ENG|06010902 | 3| | { UNLOAD TABLE | TABLEUNLOAD } ENG|06010902 | 4| | { [ TABLE ] <table name> | USER | ALL } ENG|06010902 | 5| | <table content file> ENG|06010902 | 8| | <table content file> ENG|06010902 | 10| | OUTFILE <external file name> ENG|06010902 | 11| | [ <count spec> ] ENG|06010902 | 14| | <count spec> ENG|06010902 | 16|RI| ##060117<file^format> ENG|06010903 | 1| | <tableload statement> ENG|06010903 | 3| | { LOAD TABLE | TABLELOAD } { LOAD TABLE | TABLELOAD } ENG|06010903 | 4| | [ TABLE ] <table name> { USER | ALL } ENG|06010903 | 5| | <table content file> <table content file> ; ENG|06010903 | 6| | <rejected table file> ENG|06010903 | 9| | <table content file> ENG|06010903 | 11| | INFILE <external file name> ENG|06010903 | 12| | [ <count spec> ] ENG|06010903 | 15| | <rejected table file> ENG|06010903 | 17| | OUTFILE <external file name> ENG|06010903 | 18|S | [ <count spec> ] ENG|06010903 | 19| | <count spec> ENG|06010903 | 21|RI| ##060117<file^format> ENG|06011001 | 1| | <dbextract statement> ENG|06011001 | 3| | { EXTRACT DB | DBEXTRACT } ENG|06011001 | 4| | <catalog file> ; ENG|06011001 | 5| | <table content file> ENG|06011001 | 8| | <catalog file> ENG|06011001 | 10| | OUTFILE <external file name> [ APPEND ] ENG|06011001 | 11| | [ <code spec> <sqlmode> <compress spec> <count spec> ] ENG|06011001 | 14| | <code spec>, <sqlmode>, <compress spec>, <count spec> ENG|06011001 | 16|SI| ##060117<file^format> ENG|06011001 | 19| | <table content file> ENG|06011001 | 21| | OUTFILE <external file name> ENG|06011001 | 22|R | [ <count spec> ] ENG|06011002 | 1| | <dbload statement> ENG|06011002 | 3| | { LOAD DB | DBLOAD } ENG|06011002 | 4| | <catalog file> ; ENG|06011002 | 5| | <table content file> ; ENG|06011002 | 6| | <rejected table file> ENG|06011002 | 9| | <catalog file> ENG|06011002 | 11| | INFILE <external file name> ENG|06011002 | 12| | [ <code spec> <sqlmode> <count spec> ] ENG|06011002 | 15| | <code spec>, <sqlmode>, <count spec> ENG|06011002 | 17|SI| ##060117<file^format> ENG|06011002 | 19| | <table content file> ENG|06011002 | 21| | INFILE <external file name> ENG|06011002 | 22| | [ <count spec> ] ENG|06011002 | 25| | <rejected table file> ENG|06011002 | 27| | OUTFILE <external file name> ENG|06011002 | 28|R | [ <count spec> ] ENG|06011101 | 1| | <load oracledb statement> ENG|06011101 | 3| | {LOAD | FASTLOAD} ORACLEDB ENG|06011101 | 4| | <oracle exp file> ENG|06011101 | 7| | <oracle exp file> ENG|06011101 | 9| | INFILE <external file name> [ <intpres spec> ] ENG|06011101 | 12| | <intpres spec> ENG|06011101 | 14|RI| ##060117<file^format> ENG|06011102 | 1| | <load oracle table> ENG|06011102 | 3| I| ##060103<load^statement> ENG|06011102 | 4| | <oracle exp file> ORACLE ENG|06011102 | 7| | <oracle exp file> ENG|06011102 | 9| | INFILE <external file name> [ <intpres spec> ] ENG|06011102 | 12| | <intpres spec> ENG|06011102 | 14|RI| ##060117<file^format> ENG|060113 | 1| | <other stmt> ENG|060113 | 3| M| { #01<change autocommit stmt> ENG|060113 | 4| M| | #02<change user stmt> ENG|060113 | 5| M| | #03<change sqlmode stmt> ENG|060113 | 6| M| | #04<change termcharset stmt> } ENG|060113 | 7|RM| | #05<long message stmt> } ENG|06011301 | 1| | <change autocommit stmt> ENG|06011301 | 3|R | AUTOCOMMIT { OFF | ON } ENG|06011302 | 1| | <change user stmt> ENG|06011302 | 3| | { [ USE ] USER <user spec> [ NOLOG ] [ SERVERDB <serverdb spec> ] ENG|06011302 | 4| | | USE USERKEY <xuserkey> } ENG|06011302 | 5| | | USE SERVERDB <serverdb spec> ENG|06011302 | 8| | <user spec> ENG|06011302 | 10| | { <username> <password> | &U } ENG|06011302 | 13| | <serverdb spec> ENG|06011302 | 15|S | <dbname> [ ON <node> ] ENG|06011302 | 19| | <username>, <password> ENG|06011302 | 21| | { <simple identifier> | "<special identifier>" } ENG|06011302 | 24| | <dbname>, <node>, <xuserkey> ENG|06011302 | 26|R | <external identifier> ENG|06011303 | 1| | <change sqlmode stmt> ENG|06011303 | 3|RI| SQLMODE ##06011705<sqlmode> ENG|06011304 | 1| | <change termcharset stmt> ENG|06011304 | 3| | { USE TERMCHARSET <new termcharset name> ENG|06011304 | 4| | | IGNORE TERMCHARSET } ENG|06011304 | 7| | <new termcharset name> ENG|06011304 | 9| | { <simple identifier> ENG|06011304 | 10|R | | "<special identifier>" } ENG|06011305 | 1| | <long message stmt> ENG|06011305 | 3|R | MESSAGE ON ENG|060114 | 1| | <control stmt> ENG|060114 | 3| M| { #01<if stmt> ENG|060114 | 4| M| | #02<return stmt> ENG|060114 | 5| M| | #02<stop stmt> ENG|060114 | 6| M| | <set #03returncode stmt> ENG|060114 | 7| M| | #04<include controlfile stmt> ENG|060114 | 8|RM| | #05<say stmt> ENG|06011401 | 1| | <if stmt> ENG|06011401 | 3| | IF { TRUE ENG|06011401 | 4| | | FALSE ENG|06011401 | 5| | | $RC [ ( <sql stmt> ) ] <comp op> <integer> } ENG|06011401 | 6| | THEN <compound> ENG|06011401 | 7| | [ ELSE <compound> ] ENG|06011401 | 10| | <comp op> ENG|06011401 | 12| | { < | <= | = | >= | > | <> } ENG|06011401 | 15| | <compound> ENG|06011401 | 17|S | { BEGIN <stmt list> END | <single stmt> } ENG|06011401 | 19| | <stmt list> ENG|06011401 | 21| | { <single stmt> [ <single stmt> ]... } ENG|06011401 | 24| | <single stmt> ENG|06011401 | 26| | <separator line> ENG|06011401 | 27| I| { ##060114<control^stmt> ENG|06011401 | 28| | | <load stmt> ENG|06011401 | 29| | | <sql stmt> } ENG|06011401 | 30|R | <separator line> ENG|06011402 | 1| | <return stmt> ENG|06011402 | 3| | RETURN ENG|06011402 | 6| | <stop stmt> ENG|06011402 | 8|R | STOP [ <integer> ] ENG|06011403 | 1| | <set returncode stmt> ENG|06011403 | 3|R | RETURNCODE <unsigned integer> ENG|06011404 | 1| | <include controlfile stmt> ENG|06011404 | 3|RI| INCLUDE <external file name> [ ##06011701<code^spec> ] [ ##06011705<sqlmode> ] ENG|06011405 | 1| | <say stmt> ENG|06011405 | 3|R | SAY <comment> ENG|06011501 | 1| | <end command> ENG|06011501 | 3|R | END ENG|06011502 | 1| | <exit command> ENG|06011502 | 3|R | EXIT ENG|06011503 | 1| | <run command> ENG|06011503 | 3| | <run control file command> ENG|06011503 | 4|S | | <start statement command> ENG|06011503 | 19| | <run control file command> ENG|06011503 | 21| | RUN <external file name> ENG|06011503 | 22| I| [ ##06011701<code^spec> ] ENG|06011503 | 23| I| [ ##06011705<sqlmode> ] ENG|06011503 | 24| | [ PROMPT ] ENG|06011503 | 25| | [ -p <parameter list> ] ENG|06011503 | 28| | <parameter list> ENG|06011503 | 30| | <parameter> [ <blank> <parameter> ]... ENG|06011503 | 33| | <parameter> ENG|06011503 | 35| | { <literal without blanks> ENG|06011503 | 36|S | | '<literal containing blanks>' } ENG|06011503 | 37| | <start statement command> ENG|06011503 | 39| | RUN <line ranges> ENG|06011503 | 42| | <line ranges> ENG|06011503 | 44| | [ FROM <count> ] [ FOR <lines> ] [ STOP <count> ] ENG|06011503 | 47| | <count> ENG|06011503 | 49| | <unsigned integer> ENG|06011503 | 52| | <lines> ENG|06011503 | 54|R | { <count> | * } ENG|06011504 | 1| | <next command> ENG|06011504 | 3| | { <next prompt> ENG|06011504 | 4| | | <next noprompt> } ENG|06011504 | 7| | <next prompt> ENG|06011504 | 9| | NEXT [ PROMPT ] ENG|06011504 | 12| | <next noprompt> ENG|06011504 | 14|R | NEXT NOPROMPT ENG|06011505 | 1| | <skip command> ENG|06011505 | 3| | SKIP { <number of statements> | <keyword> } ENG|06011505 | 6| | <number of statements> ENG|06011505 | 8| | { [ 1 ] ENG|06011505 | 9|R | | <unsigned integer> } ENG|06011506 | 1| | <scan command> ENG|06011506 | 3|R | SCAN [ <literal> ] ENG|06011507 | 1| | <close controlfile command> ENG|06011507 | 3|R | END RUN ENG|06011508 | 1| | <prot command> ENG|06011508 | 3|R | PROT ENG|06011509 | 1| | <version command> ENG|06011509 | 3|R | VERSION ENG|06011510 | 1| | <exec command> ENG|06011510 | 3|R | EXEC [ ASYNC ] <operating system command> ENG|06011511 | 1| | <set command> ENG|06011511 | 3|R | SET ENG|06011512 | 1| | <help command> ENG|06011512 | 3| | HELP [ <helpid> ] ENG|06011512 | 6| | <helpid> ENG|06011512 | 8| | { BATCH ENG|06011512 | 9| | | END ENG|06011512 | 10| | | END RUN ENG|06011512 | 11| | | EXEC ENG|06011512 | 12| | | EXIT ENG|06011512 | 13| | | HELP ENG|06011512 | 14| | | NEXT ENG|06011512 | 15| | | PROT ENG|06011512 | 16| | | RUN ENG|06011512 | 17| | | SCAN ENG|06011512 | 18|S | | SET ENG|06011512 | 19| | | SKIP ENG|06011512 | 20|R | | VERSION } ENG|06011513 | 1| | <batch command> ENG|06011513 | 3| | BATCH <external file name> ENG|06011513 | 4| I| [ ##06011701<code^spec> ] ENG|06011513 | 5| I| [ ##06011705<sqlmode> ] ENG|06011513 | 6| | [ -p <parameter list> ] ENG|06011513 | 9| | <parameter list> ENG|06011513 | 11| | <parameter> [ <blank> <parameter> ]... ENG|06011513 | 14| | <parameter> ENG|06011513 | 16| | { <literal without blanks> ENG|06011513 | 17|R | | '<literal containing blanks>' } ENG|060117 | 1| | <file format> ENG|060117 | 3| M| [ #01<code^spec> ] ENG|060117 | 4| M| [ #02<intpres^spec> ] ENG|060117 | 5| M| [ #03<compress^spec> ] ENG|060117 | 6| M| [ #04<count^spec> ] ENG|060117 | 7| M| [ #05<sqlmode> ] ENG|060117 | 8| M| [ #06<number^layout^spec> ] ENG|060117 | 9| M| [ #07<date^representation^spec> ] ENG|060117 | 10| M| [ #08<time^representation^spec> ] ENG|060117 | 11| M| [ #09<timestamp^representation^spec> ] ENG|060117 | 12| M| [ #10<null^representation^spec> ] ENG|060117 | 13| M| [ #11<bool^representation^spec> ] ENG|060117 | 14|RM| [ #12<concatenate^spec> ] ENG|06011701 | 1| | <code spec> ENG|06011701 | 3|R | { ASCII | EBCDIC } ENG|06011702 | 1| | <intpres spec> ENG|06011702 | 3|R | INTEGER { HILO | LOHI } ENG|06011703 | 1| | <compress spec> ENG|06011703 | 3| | { [ COMPRESS ] <any compress spec> [ <any compress spec> ] ENG|06011703 | 4| | | <default compress spec> } ENG|06011703 | 7| | <any compress spec> ENG|06011703 | 9| | { SEPARATOR <field separator> ENG|06011703 | 10| | | DELIMITER <string delimiter> } ENG|06011703 | 13| | <default compress spec> ENG|06011703 | 15| | { COMPRESS ENG|06011703 | 16|S | | SEPARATOR ',' DELIMITER '"' } ENG|06011703 | 19| | <field separator> ENG|06011703 | 21| | '<any character>' ENG|06011703 | 24| | <string delimiter> ENG|06011703 | 26|R | { '<any character>' | '' } ENG|06011704 | 1| | <count spec> ENG|06011704 | 3| | COUNT <block count> ENG|06011704 | 6| | <block count> ENG|06011704 | 8|R | <unsigned integer> ENG|06011705 | 1| | <sqlmode> ENG|06011705 | 3| | { ADABAS ENG|06011705 | 4| | | ANSI ENG|06011705 | 5| | | DB2 ENG|06011705 | 6|R | | ORACLE } ENG|06011706 | 1| | <number layout spec> ENG|06011706 | 3| | DEC '<number layout mask>' ENG|06011706 | 6| | <number layout mask> ENG|06011706 | 8|R | /[<thousand char>]/<decimal point>/ ENG|06011707 | 1| | <date representation> ENG|06011707 | 3| | DATE '<date mask>' ENG|06011707 | 6| | <date mask> ENG|06011707 | 8| | { INTERNAL /* YYYYMMDD ENG|06011707 | 9| | | EUR /* DD.MM.YYYY ENG|06011707 | 10| | | ISO /* YYYY-MM-DD ENG|06011707 | 11| | | JIS /* YYYY-MM-DD ENG|06011707 | 12| | | USA /* MM/DD/YYYY ENG|06011707 | 13| | | <free format> } ENG|06011707 | 16| | <free format> ENG|06011707 | 18|R | { DD, TT, MM, MMM, JJ, JJJJ, YY, YYYY, <separator>, <blank> } ENG|06011708 | 1| | <time representation> ENG|06011708 | 3| | TIME '<time mask>' ENG|06011708 | 6| | <time mask> ENG|06011708 | 8| | { INTERNAL /* HHHHMMSS ENG|06011708 | 9| | | EUR /* HH.MM.SS ENG|06011708 | 10| | | ISO /* HH.MM.SS ENG|06011708 | 11| | | JIS /* HH:MM:SS ENG|06011708 | 12| | | USA /* HH:MM AM ENG|06011708 | 13| | | <free format> } ENG|06011708 | 16| | <free format> ENG|06011708 | 18|R | { HH, MM, SS, AM, <separator>, <blank> } ENG|06011709 | 1| | <timestamp representation> ENG|06011709 | 3| | TIMESTAMP '<timestamp mask>' ENG|06011709 | 6| | <timestamp mask> ENG|06011709 | 8| | { INTERNAL /* YYYYMMDDHHMMSSNNNNNN ENG|06011709 | 9| | | EUR /* = ISO ENG|06011709 | 10| | | ISO /* YYYY-MM-DD.HH.MM.SS.NNNNNN ENG|06011709 | 11| | | JIS /* = ISO ENG|06011709 | 12| | | USA /* = ISO ENG|06011709 | 13|S | | <free format> } ENG|06011709 | 19| | <free format> ENG|06011709 | 21| | { DD, TT, MM, MMM, JJ, JJJJ, YY, YYYY ENG|06011709 | 22|R | , HH, MM, SS, AM, <separator>, <blank> } ENG|06011710 | 1| | <null representation> ENG|06011710 | 3|R | NULL '<literal>' ENG|06011711 | 1| | <bool representation> ENG|06011711 | 3| | BOOLEAN '<true spec>/<false spec>' ENG|06011711 | 6| | <true spec>, <false spec> ENG|06011711 | 8|R | <literal> ENG|06011712 | 1| | <concatenate spec> ENG|06011712 | 3| | { <unconditional concatenation> | <conditional concatenation> } ENG|06011712 | 6| | <unconditional concatenation> ENG|06011712 | 8| | CONCATENATE <number of lines> ENG|06011712 | 11| | <conditional concatenation> ENG|06011712 | 13| | CONTINUEIF ENG|06011712 | 14| | { <linepos> | LAST } ENG|06011712 | 15| | { = | <> } ENG|06011712 | 16|R | { '<any character>' | X'<hex character>' } ENG|06011720 | 1|R | <infile spec>, <outfile spec> und <longfile spec> ENG|06011721 | 1| | <infile spec> ENG|06011721 | 3| | INFILE <file name> ENG|06011721 | 4| I| ##060117<file^format> ENG|06011721 | 7| | <file name> ENG|06011721 | 9| | { * | <external file name> } ENG|06011721 | 12| | <external file name> ENG|06011721 | 14| | { CLIPBOARD ENG|06011721 | 15| | | <tape ident> ENG|06011721 | 16|R | | <disk file name> } ENG|06011722 | 1| | <outfile spec> ENG|06011722 | 3| | OUTFILE <file name> ENG|06011722 | 4| I| ##060117<file^format> ENG|06011722 | 7| | <file name> ENG|06011722 | 9| | { * | <external file name> } ENG|06011722 | 12| | <external file name> ENG|06011722 | 14| | { PRINTER ENG|06011722 | 15| | | CLIPBOARD ENG|06011722 | 16| | | <tape ident> ENG|06011722 | 17|R | | <disk file name> [ APPEND ] } ENG|06011723 | 1| | <longfile spec> ENG|06011723 | 3| | LONGFILE <column id> <longfile name> ENG|06011723 | 6| | <longfile name> ENG|06011723 | 8| | { PRINTER ENG|06011723 | 9| | | <tape ident> ENG|06011723 | 10| | | <disk file name> ENG|06011723 | 11| | | <filename pattern> } ENG|06011723 | 14| | <filename pattern> ENG|06011723 | 16|R | <disk file name containing >=1 '#'> ENG|0602 | 1| | LOAD Online ENG|0602 | 2| | ----------- ENG|0602 | 4| M| #01Layout^of^the^Screen ENG|0602 | 5| I| ##04Edit^Functions ENG|0602 | 6| M| #02Interactive^Testing ENG|0602 | 7| M| #03Commands^and^Function^Keys ENG|0602 | 8|RM| #04HELP^Features ENG|060201 | 1| | Layout of the Screen ENG|060201 | 3| | The screen consists of several write protected areas where status ENG|060201 | 4| | information, messages, the current key setting etc. is displayed, and ENG|060201 | 5| | of two input areas: ENG|060201 | 7| | the input form for LOAD and SQL statements and ENG|060201 | 9| | the command line. ENG|060201 | 11| | You can scroll over the input form which has a total size of 24Kbyte ENG|060201 | 12| | and a maximum line length of 141 characters. From this input form the ENG|060201 | 13| | LOAD or SQL statements, e.g. DATALOAD and DELETE, are read. ENG|060201 | 15|R | The command line (==>) serves to execute commands like RUN, PUT etc. ENG|060202 | 1| | Interactive Testing ENG|060202 | 3| | If you want to test a single statement you can type it into the input ENG|060202 | 4| | form, PUT it into a file, GET it from the file back onto the screen. ENG|060202 | 6| | A control file started with RUN is more suitable for interactive ENG|060202 | 7| | testing of more than one statement. LOAD offers several commands (like ENG|060202 | 8| | NEXT, SKIP, SCAN) to move within the control file and takes control ENG|060202 | 9|R | statements and separator lines into account. ENG|060203 | 1| | Commands and Function Keys ENG|060203 | 3| | The execution of LOAD functions is controlled via a group of LOAD ENG|060203 | 4| | commands. To enter these commands please use the command line (==>) in ENG|060203 | 5| | the lower part of the screen. ENG|060203 | 7| | LOAD commands consist of a keyword, followed by - in some cases ENG|060203 | 8| | optional - parameters. In general, lowercase letters in the command ENG|060203 | 9| | line are translated to uppercase. An exception is made for file names ENG|060203 | 10| | and parameters enclosed in single quotes. ENG|060203 | 12| | The most frequently used commands can be executed via function keys as ENG|060203 | 13| | well. The current function key setting is displayed in the language ENG|060203 | 14|R | chosen via SET. ENG|060204 | 1| | HELP Features ENG|060204 | 3| | HELP displays short descriptions of all statements and commands that ENG|060204 | 4| | are available within LOAD. Descriptions of SQL statements and editor ENG|060204 | 5| | features can be requested as well. The descriptions are arranged in an ENG|060204 | 6| | hierarchical order. ENG|060204 | 8| | A particular chapter can be selected by placing the cursor on a bright ENG|060204 | 9| | word (use the TAB key) and then pressing the HELP key. By pressing the ENG|060204 | 10| | PREV key you return to the previous chapter or menu. HLP-MENU brings ENG|060204 | 11| | you back to the top level. HELP-END leaves the HELP environment. ENG|060204 | 13| | The PICK key copies an example within a help chapter into the editor's ENG|060204 | 14| | pick buffer. PUT transfers the pick buffer content to the input form. ENG|060204 | 15| | The MARK key marks the currently displayed help text page. During the ENG|060204 | 16| | next call of the HELP function, the TO-MARK key switches directly to ENG|060204 | 17| | the marked page. ENG|060204 | 18|RI| ##06011512Syntax ENG|0603 | 1| | How to Fill Database Tables ENG|0603 | 2| | --------------------------- ENG|0603 | 4| M| #01The^Load^Statement^DATALOAD^TABLE ENG|0603 | 5| M| #02Data^Types^of^File^Fields^(INTEGER,^REAL^etc.) ENG|0603 | 6| M| #03Dimension^and^Precision^of^Numerical^Values ENG|0603 | 7| M| #04Format^Specifications^Related^to^a^File^and^Other^File^Options ENG|0603 | 8| M| #05Selecting^Records^from^a^Source^File ENG|0603 | 9| M| #06Inserting^NULL^Values ENG|0603 | 10| M| #07Loading^Constants ENG|0603 | 11| M| #08Loading^Function^Values^(USER,^DATE,^SEQNO^etc.) ENG|0603 | 12| M| #09Loading^Several^Tables^in^a^Single^Run ENG|0603 | 13| M| #10Selection^with^OTHERWISE ENG|0603 | 14| M| #11Input^of^Test^Data^on^the^Screen ENG|0603 | 15| M| #12DATALOAD^with^Input^Made^in^Default^Format ENG|0603 | 17| M| #13Loading^without^Logging^(FASTLOAD) ENG|0603 | 18|RM| #14FASTLOAD^with^USAGE^Option ENG|060301 | 1| | The Load Statement DATALOAD TABLE ENG|060301 | 3| | A common load statement consists at least of the following parts: the ENG|060301 | 4| | keyword DATALOAD TABLE, a load description, the keyword INFILE and a ENG|060301 | 5| | file name. ENG|060301 | 7| | The load description specifies the target table and combines column ENG|060301 | 8| | names with positions in the file line where the input data shall be ENG|060301 | 9| | read from. Default values are assigned to columns not listed in the ENG|060301 | 10| | load description. ENG|060301 | 12| | The table name is specified in accordance with the SQL conventions. ENG|060301 | 13| | Each column name has to be followed by a position specification ENG|060301 | 14| | refering to the input line. The value found at this position is ENG|060301 | 15| | inserted into the column. Position specifications can consist of ENG|060301 | 16| | starting and ending position with a hyphen in between or designate a ENG|060301 | 17|S | single byte. Counting starts with 1. ENG|060301 | 19| | Into the table CUSTOMER data from the master file (cmaster.data) shall ENG|060301 | 20| | be loaded. The file consists of formatted plaintext with an extra blank ENG|060301 | 21| | after every field. ENG|060301 | 22| E| ENG|060301 | 23| E| DATALOAD TABLE customer ENG|060301 | 24| E| cno 1-4 ENG|060301 | 25| E| lastname 6-15 ENG|060301 | 26| E| city 17-36 ENG|060301 | 27| E| fstate 37-38 ENG|060301 | 28| E| zip 39-43 ENG|060301 | 29| E| INFILE cmaster.data ENG|060301 | 30| E| ENG|060301 | 31|RI| ##06010301Syntax ENG|060302 | 1| | Data Types of File Fields (INTEGER, REAL etc.) ENG|060302 | 3| | For each column of the input file, defined by a position, the data ENG|060302 | 4| | format of the field values contained therein can be specified. The ENG|060302 | 5| | declaration is only needed when the field values are not stored in ENG|060302 | 6| | plaintext (CHAR). ENG|060302 | 8| | If values are to be entered into CHAR or BYTE columns, the input length ENG|060302 | 9| | must not exceed the column length. Plaintext values for numerical ENG|060302 | 10| | columns must be of the currently set number format. ENG|060302 | 12| | Data of the numerical formats INTEGER, REAL, DECIMAL, and ZONED, ENG|060302 | 13| | generated by an application program, can only be inserted into FIXED or ENG|060302 | 14| | FLOAT columns. ENG|060302 | 16| | Regardless of the data format of a numerical value, fractional digits ENG|060302 | 17| | are implicitly truncated when the precision of the column is less than ENG|060302 | 18|S | that of the input value. ENG|060302 | 19| | Data from a binary file shall be loaded into the table ITEM: ENG|060302 | 20| E| ENG|060302 | 21| E| DATALOAD TABLE item ENG|060302 | 22| E| item_id 1-8 ENG|060302 | 23| E| descr 9-39 ENG|060302 | 24| E| stock 40-43 INTEGER ENG|060302 | 25| E| min_stock 44-45 INTEGER ENG|060302 | 26| E| orderdate 46-53 CHAR ENG|060302 | 27| E| delivdate 54-61 CHAR ENG|060302 | 28| E| price 62-69 DECIMAL (2) ENG|060302 | 29| E| weight 70-77 REAL ENG|060302 | 30| E| INFILE item.data ENG|060302 | 31| E| ENG|060302 | 32|RI| ##06010306Syntax ENG|060303 | 1| | Dimension and Precision of Numerical Values ENG|060303 | 3| | Applying the functions SCALE, ROUND, and TRUNC edits numerical values ENG|060303 | 4| | before they are inserted into the database. ENG|060303 | 6| | If SCALE (n) is specified, the values of the input column are raised to ENG|060303 | 7| | the n-th decimal power. ENG|060303 | 9| | If TRUNC (n) is specified, at most n fractional digits of a number are ENG|060303 | 10| | taken into account and the remaining digits are assigned 0. ENG|060303 | 12| | If ROUND (n) is specified, the values are rounded to n fractional ENG|060303 | 13| | digits. If a rounded fractional digit is 5 or higher, the preceding ENG|060303 | 14| | digit is incremented, otherwise it remains unchanged. ENG|060303 | 16| | Before applying TRUNC or ROUND to a floating point number, it is ENG|060303 | 17|S | converted into a fixed point representation. ENG|060303 | 19| E| ENG|060303 | 20| E| DATALOAD TABLE number_table ENG|060303 | 21| E| ins_date 8-17 ENG|060303 | 22| E| ins_time 22-29 ENG|060303 | 23| E| big_number 33-37 SCALE 7 ENG|060303 | 24| E| small_number 33-37 SCALE -3 ROUND 4 ENG|060303 | 25| E| integer 33-37 SCALE 1 TRUNC 0 ENG|060303 | 26| E| INFILE * DEC '/ /,/' ENG|060303 | 27| E| DATE 'YYYY-MM-DD' ENG|060303 | 28| E| TIME 'HH:MM AM' ENG|060303 | 29| E| NULL '-?-' ENG|060303 | 30| E| INPUT: 1992-08-10 at 07:30 AM : 67,76 ENG|060303 | 31| E| ENG|060303 | 32| | The numbers inserted are 677 600 000; 0,0678; 677. ENG|060303 | 34|RI| ##0601030601Syntax ENG|060304 | 1| | Format Specifications Related to a File and Other File Options ENG|060304 | 3| | File options can be used to override several default settings. They can ENG|060304 | 4| | indicate the format of plaintext numbers (DEC), DATE, TIME, TIMESTAMP, ENG|060304 | 5| | BOOLEAN, and NULL values used in the file in hand. Moreover the ENG|060304 | 6| | conversion between ASCII and EBCDIC or from one integer representation ENG|060304 | 7| | to another can be demanded, as may be required if the data stem from ENG|060304 | 8| | another platform. ENG|060304 | 10| | The default setting of the file options DEC, DATE, TIME, TIMESTAMP, ENG|060304 | 11| | BOOLEAN, and NULL tallies with the respective SET value. The default ENG|060304 | 12| | for ASCII/EBCDIC and INTEGER HILO/LOHI is taken from the given ENG|060304 | 13| | hardware. ENG|060304 | 14| I| ##060117Syntax ENG|060304 | 16| | The example on the next page illustrates the use of concatenation, ENG|060304 | 17| | COMPRESS mode, and overriding of SET values, based on the number_table ENG|060304 | 18|S | of the SCALE, ROUND, and TRUNC chapter. ENG|060304 | 19| E| DATALOAD TABLE number_table ENG|060304 | 20| E| ins_date 1 ENG|060304 | 21| E| ins_time 2 ENG|060304 | 22| E| big_number 3 SCALE 7 ENG|060304 | 23| E| small_number 3 SCALE -3 ROUND 4 ENG|060304 | 24| E| integer 3 SCALE 1 TRUNC 0 ENG|060304 | 25| E| INFILE * ENG|060304 | 26| E| COMPRESS ENG|060304 | 27| E| CONTINUEIF LAST = '+' ENG|060304 | 28| E| DATE 'YYYY-MM-DD' ENG|060304 | 29| E| TIME 'HH:MM AM' ENG|060304 | 30| E| DEC '//,/' ENG|060304 | 31| E| "1992-08-10","07:30 + ENG|060304 | 32| E| AM","67,76" ENG|060304 | 33| E| ENG|060304 | 34| | If COMPRESS is set, LOAD can process data that have been generated by ENG|060304 | 35| | PC tools. The positions in the load description designate the ENG|060304 | 36|S | consecutive numbers of the input fields, which are of variable length ENG|060304 | 37| | and terminated by a separator character. The individual values can be ENG|060304 | 38| | enclosed in delimiter characters that are not part of the value. ENG|060304 | 40| | COMPRESS is set by any of the following options: COMPRESS, SEPARATOR, ENG|060304 | 41| | DELIMITER. COMPRESS is equivalent to SEPARATOR ',' DELIMITER '"'. You ENG|060304 | 42| | can override these settings by explicitly specifying SEPARATOR and/or ENG|060304 | 43| | DELIMITER. DELIMITER '' indicates that strings are not delimited. ENG|060304 | 45| | The options CONCATENATE and CONTINUEIF serve to unite several file ENG|060304 | 46| | records into one input line. The positions in the load description ENG|060304 | 47| | refer to this compound input line. ENG|060304 | 49| | CONCATENATE has to be followed by a numeric constant (1 <= n <= 4096) ENG|060304 | 50| | indicating how many file records are to be concatenated. CONTINUEIF ENG|060304 | 51| | checks the given condition and concatenates the next file record if it ENG|060304 | 52| | is fulfilled. LAST compares the last character of the current file ENG|060304 | 53| | record, otherwise the character found at the given position is used for ENG|060304 | 54|R | the comparison. ENG|060305 | 1| | Selecting Records from a Source File ENG|060305 | 3| | Records of a source file can be loaded into a table according to their ENG|060305 | 4| | contents. The selection is done by evaluating a (simple or compound) ENG|060305 | 5| | condition that compares a section of the input line with a constant. ENG|060305 | 6| | Only those input lines are loaded that meet the condition. ENG|060305 | 8| | Let the data of the input file be mingled with comment lines starting ENG|060305 | 9| | with +++. A selection of those hotels with a room rate of more than ENG|060305 | 10| | $500 shall be made. ENG|060305 | 11| I| ##06010301Syntax ENG|060305 | 12| E| DATALOAD TABLE classy_hotels ENG|060305 | 13| E| IF (POS 1-3 <> '+++') AND (POS 73-76 DECIMAL > 50000) ENG|060305 | 14| E| hno 1-10 ENG|060305 | 15| E| name 14-33 ENG|060305 | 16| E| city 37-56 ENG|060305 | 17| E| price 73-76 DECIMAL (2) ENG|060305 | 18|RE| INFILE hotel.all ENG|060306 | 1| | Inserting NULL Values ENG|060306 | 3| | A NULL condition can be specified for any target column which may ENG|060306 | 4| | contain NULL values. It describes the condition on which the NULL value ENG|060306 | 5| | will be loaded as a column value. If a column has a default value ENG|060306 | 6| | definition other than NULL LOAD inserts the explicit default value if ENG|060306 | 7| | the condition is met. ENG|060306 | 9| | The condition DEFAULT NULL is fulfilled if the input field contains the ENG|060306 | 10| | string given in the NULL option or via SET. The shorter value is padded ENG|060306 | 11| | with blanks. ENG|060306 | 13| I| ##06010305Syntax ENG|060306 | 15| | The example on the following page illustrates both forms of NULL ENG|060306 | 16| | conditions. The German address is assigned NULL for FSTATE and ENG|060306 | 17|S | TELEPHONE, the British adress has FSTATE and ZIP set to NULL. ENG|060306 | 19| E| ENG|060306 | 20| E| DATALOAD TABLE addresses ENG|060306 | 21| E| name 1-20 ENG|060306 | 22| E| street 21-40 ENG|060306 | 23| E| city 46-60 ENG|060306 | 24| E| fstate 79-80 NULL IF POS 68-70 <> 'USA' ENG|060306 | 25| E| zip 41-45 NULL IF POS 68-69 = 'GB' ENG|060306 | 26| E| country 61-63 ENG|060306 | 27| E| telephone 64-78 DEFAULT NULL ENG|060306 | 28| E| INFILE * ENG|060306 | 29| E| NULL '-/-' ENG|060306 | 30| E| DEC '//./' ENG|060306 | 31| E| Hugo Mende Elisabethstr. 55 99096Erfurt D -/- ENG|060306 | 32| E| Angus Mcpherson Grinders Walk Upper Chapel GB 04711-118 ENG|060306 | 33|RE| ENG|060307 | 1| | Loading Constants ENG|060307 | 3| | If a column shall be assigned the same value in each line inserted, no ENG|060307 | 4| | space in the source file is required for it. Instead, the value can be ENG|060307 | 5| | specified as a constant within the load description. ENG|060307 | 6| E| ENG|060307 | 7| E| DATALOAD TABLE item IF POS 1-8 = 'H7511P5 ' ENG|060307 | 8| E| ino 1-8 ENG|060307 | 9| E| descr 'hammer' ENG|060307 | 10| E| stock 40-43 INTEGER ENG|060307 | 11| E| min_stock '50' ENG|060307 | 12| E| ordered 46-49 INTEGER ENG|060307 | 13| E| delivery 50-57 CHAR ENG|060307 | 14| E| price 58-65 DECIMAL (2) ENG|060307 | 15| E| INFILE item.data ENG|060307 | 16| E| ENG|060307 | 17|RI| ##0601030502Syntax ENG|060308 | 1| | Loading Function Values (USER, DATE, SEQNO etc.) ENG|060308 | 3| | During the load run LOAD can generate certain values and insert them ENG|060308 | 4| | into a table along with values from the source file. ENG|060308 | 5| E| ENG|060308 | 6| E| DATALOAD TABLE reservation ENG|060308 | 7| E| serial SEQNO 0 10 ENG|060308 | 8| E| dealt_with_by USER ENG|060308 | 9| E| travel_agency USERGROUP ENG|060308 | 10| E| entry_date DATE ENG|060308 | 11| E| entry_time TIME ENG|060308 | 12| E| customer_name 1-20 ENG|060308 | 13| E| hotel_name 21-40 ENG|060308 | 14| E| arrival_date 41-50 ENG|060308 | 15| E| INFILE * DATE 'USA' ENG|060308 | 16| E| Morgan Freeman Holiday Inn 01/11/1994 ENG|060308 | 17| E| ENG|060308 | 18|RI| ##0601030502Syntax ENG|060309 | 1| | Loading Several Tables in a Single Run ENG|060309 | 3| | Several tables can be loaded from one source file in a single run. In ENG|060309 | 4| | this case every target table must be described by a DATALOAD ENG|060309 | 5| | specification of its own. The source file records can be distributed ENG|060309 | 6| | among the target tables by means of the selection criterion. If the ENG|060309 | 7| | criterion is missing for one of the target tables, each input record ENG|060309 | 8| | will be selected for this table. ENG|060309 | 10| E| DATALOAD TABLE addresses ENG|060309 | 11| E| customer_name 1-20 ENG|060309 | 12| E| DATALOAD TABLE reservation ENG|060309 | 13| E| customer_name 1-20 ENG|060309 | 14| E| hotel_name 21-30 ENG|060309 | 15| E| arrival 31-40 ENG|060309 | 16| E| departure 41-50 ENG|060309 | 17| E| INFILE customer.statements DATE 'USA' ENG|060309 | 18|RI| ##06010301Syntax ENG|060310 | 1| | Selection with OTHERWISE ENG|060310 | 3| | If each input row is to be loaded into one table exactly, then the ENG|060310 | 4| | specification of unambiguous selection conditions is required. ENG|060310 | 6| | Should there be a number of rows for which such a criterion cannot be ENG|060310 | 7| | specified, OTHERWISE can be indicated instead of a normal selection ENG|060310 | 8| | condition. The condition thus defined is satisfied if and only if a row ENG|060310 | 9| | was not loaded into any other table. ENG|060310 | 11| I| ##06010301Syntax ENG|060310 | 13| | The example on the following page illustrates how the data of a file is ENG|060310 | 14| | distributed among three tables. An unambiguous criterion can only be ENG|060310 | 15| | defined for the first two tables, all other data is loaded into the ENG|060310 | 16|S | third table. ENG|060310 | 19| E| DATALOAD TABLE part1 IF POS 1 = 'a' ENG|060310 | 20| E| part_no 02-10 CHAR ENG|060310 | 21| E| identifier 11-30 ENG|060310 | 22| E| DATALOAD TABLE part2 IF POS 1 = 'b' ENG|060310 | 23| E| part_no 02-10 CHAR ENG|060310 | 24| E| identifier 11-30 ENG|060310 | 25| E| DATALOAD TABLE part3 OTHERWISE ENG|060310 | 26| E| part_no 02-10 CHAR ENG|060310 | 27| E| identifier 11-30 ENG|060310 | 28|RE| INFILE ... ENG|060311 | 1| | Input of Test Data on the Screen ENG|060311 | 3| | A DATALOAD statement can be entered and tested ad hoc on the screen. ENG|060311 | 4| | The data belonging to it are read from the screen if * is stated as ENG|060311 | 5| | file name. The file name * means generally that the data are to be read ENG|060311 | 6| | from the same file that contained the statement. The options DEC, NULL ENG|060311 | 7| | etc. can be used along with this as well as with any other file name to ENG|060311 | 8| | describe the data format. ENG|060311 | 9| I| ##06011721Syntax ENG|060311 | 10| E| DATALOAD TABLE customer ENG|060311 | 11| E| cno 1-5 ENG|060311 | 12| E| first_name 7-14 ENG|060311 | 13| E| last_name 16-23 ENG|060311 | 14| E| city 25-34 ENG|060311 | 15| E| INFILE * DEC '/ /,/' ENG|060311 | 16| E| 1 001 JULIE ANDREWS SANTA CLARA ENG|060311 | 17| E| 1 002 WARREN BEATTY DALLAS ENG|060311 | 18|RE| 1 003 JEFF BRIDGES HOLLYWOOD ENG|060312 | 1| | DATALOAD with Input Made in Default Format ENG|060312 | 3| | A DATALOAD statement without column descriptions can be used in the ENG|060312 | 4| | case that the input file was created by the statement DATAEXTRACT * ENG|060312 | 5| | FROM one table. ENG|060312 | 7| | LOAD generates the required column descriptions and expects data for ENG|060312 | 8| | all columns of the table. The positions are calculated taking the ENG|060312 | 9| | number representation and separator as defined via SET or a file option ENG|060312 | 10| | into account. ENG|060312 | 11| E| ENG|060312 | 12| E| DATALOAD TABLE customer ENG|060312 | 13| E| INFILE customer.extract ENG|060312 | 14| E| ENG|060312 | 15|RI| ##06010302Syntax ENG|060313 | 1| | Loading without Logging (FASTLOAD) ENG|060313 | 3| | The FASTLOAD statement helps to achieve far faster loading times, but ENG|060313 | 4| | it is much more restrictive than the normal DATALOAD function. ENG|060313 | 6| | Every user can load his tables independently from others with this ENG|060313 | 7| | statement as well, but tables to be loaded with FASTLOAD may possess ENG|060313 | 8| | neither LONG columns nor an INDEX. If a table has a user-defined key, ENG|060313 | 9| | the input lines must be sorted in ascending key order, and the key of ENG|060313 | 10| | the first line must follow all rows that already exist in the table. ENG|060313 | 12| | A table loaded with FASTLOAD becomes read-only, nevertheless it can be ENG|060313 | 13| | accessed by further FASTLOAD statements. ENG|060313 | 14| I| ##06010303Syntax ENG|060313 | 15| E| FASTLOAD TABLE parts ENG|060313 | 16| E| part_no 2-10 ENG|060313 | 17| E| identifier 11-30 ENG|060313 | 18|RE| INFILE part ENG|060314 | 1| | FASTLOAD with USAGE Option ENG|060314 | 3| | The USAGE option of the FASTLOAD statement permits to specify ENG|060314 | 4| | individually for this load action the degree to which the data pages ENG|060314 | 5| | shall be filled. Any degree from 50 to 100 percent is possible, the ENG|060314 | 6| | default is 80 percent. ENG|060314 | 7| E| ENG|060314 | 8| E| FASTLOAD WITH 100% USAGE TABLE parts ENG|060314 | 9| E| part_no 2-10 ENG|060314 | 10| E| identifier 11-30 ENG|060314 | 11| E| INFILE part ENG|060314 | 12| E| ENG|060314 | 13|RI| ##06010303Syntax ENG|0604 | 1| | Updating Table Columns (DATAUPDATE) ENG|0604 | 3| | If new columns are defined for a database table, they are first ENG|0604 | 4| | initialized with the default value (mostly NULL). Therefore they must ENG|0604 | 5| | as a rule be updated. In other cases values within specific table ENG|0604 | 6| | fields must be updated because of an activity file. ENG|0604 | 8| | The DATAUPDATE statement serves to make such updates in dependance from ENG|0604 | 9| | other values. These qualifying values have to be part of the input ENG|0604 | 10| | lines that contain the new values. ENG|0604 | 11| E| ENG|0604 | 12| E| DATAUPDATE TABLE customer ENG|0604 | 13| E| KEY cno 1-4 ENG|0604 | 14| E| SET city 6-25 ENG|0604 | 15| E| INFILE move.act ENG|0604 | 16| E| ENG|0604 | 17|RI| ##060104Syntax ENG|0605 | 1| | Merging a File into a Table ENG|0605 | 3| | Often it is the case that a table shall be refreshed by the contents of ENG|0605 | 4| | a file, whereby many rows do already exist and only a few are new. ENG|0605 | 6| | A normal DATALOAD statement would reject all rows having a key value ENG|0605 | 7| | that already exists in the table. A normal DATAUPDATE statement would ENG|0605 | 8| | critizise the lack of that rows which are only available in the file. ENG|0605 | 10| | The refreshing of such a table can be done by ignoring the error ENG|0605 | 11| | DUPLICATE KEY and leaving the existing rows unchanged (IGNORE ENG|0605 | 12| | DUPLICATES) or by updating those rows that already exist with the ENG|0605 | 13| | values found in the input file (UPDATE DUPLICATES). ENG|0605 | 15| I| ##060105Syntax ENG|0605 | 17| | The example shows how customer data are loaded resp. updated by means ENG|0605 | 18|S | of the UPDATE DUPLICATES clause in a DATALOAD statement. ENG|0605 | 19| E| ENG|0605 | 20| E| DATALOAD TABLE customer ENG|0605 | 21| E| UPDATE DUPLICATES ENG|0605 | 22| E| KEY cno 1-4 ENG|0605 | 23| E| SET city 6-25 ENG|0605 | 24| E| SET street 27-46 ENG|0605 | 25| E| SET telephone 48-60 ENG|0605 | 26| E| INFILE address.list ENG|0605 | 27|RE| ENG|0606 | 1| | Creating Dataextracts (DATAEXTRACT) ENG|0606 | 2| | ----------------------------------- ENG|0606 | 4| M| #01The^DATAEXTRACT^Statement ENG|0606 | 5| M| #02Format^Specifications^for^the^Output^File ENG|0606 | 6| M| #03Structure^of^the^Target^File ENG|0606 | 7| M| #04Data^Formats^in^the^Target^File ENG|0606 | 8| M| #05Options^for^the^Output^of^Numerical^Columns ENG|0606 | 9| M| #06Output^of^NULL^Values ENG|0606 | 10| M| #07Text^Constants^in^the^Target^File ENG|0606 | 11| M| #08Generating^Command^Files^with^DATAEXTRACT^FOR^DATALOAD/DATAUPDATE ENG|0606 | 12| M| #09Generating^a^DATALOAD^Statement^for^the^Extracted^Data ENG|0606 | 13|RM| #10Test^Output^on^Screen ENG|060601 | 1| | The DATAEXTRACT Statement ENG|060601 | 3| | The DATAEXTRACT statement consists of a database query, the ENG|060601 | 4| I| specification of the output file and, if necessary, ##060703LONGFILE ENG|060601 | 5| | descriptions. ENG|060601 | 7| | The database query is formulated in the same way as a SELECT statement ENG|060601 | 8| | in SQL, only that the keyword DATAEXTRACT is used instead of SELECT. ENG|060601 | 9| | The query must produce an unnamed result table. The end of the query ENG|060601 | 10| | must be indicated by a semi-colon (;). ENG|060601 | 11| E| ENG|060601 | 12| E| DATAEXTRACT ENG|060601 | 13| E| customer.cno, name, reservation.arrival, price ENG|060601 | 14| E| FROM customer, reservation ENG|060601 | 15| E| WHERE customer.cno = reservation.cno ; ENG|060601 | 16| E| OUTFILE cres.join ENG|060601 | 17| E| ENG|060601 | 18|RI| ##060106Syntax ENG|060602 | 1| | Format Specifications for the Output File ENG|060602 | 3| | All the file options permissible for input files that override the ENG|060602 | 4| | representation of NULL values, plaintext numbers etc. defined via SET ENG|060602 | 5| | are permitted for output files as well. ENG|060602 | 6| E| ENG|060602 | 7| E| DATAEXTRACT * FROM reservation ; ENG|060602 | 8| E| OUTFILE reservation DEC '/ /./' COMPRESS ENG|060602 | 9| E| ENG|060602 | 10| I| ##060117Syntax ENG|060602 | 12| | COMPRESS is set by any of the following options: COMPRESS, SEPARATOR, ENG|060602 | 13| | and DELIMITER. COMPRESS is equivalent to SEPARATOR ',' DELIMITER '"'. ENG|060602 | 14| | You can override these settings by explicitly specifying SEPARATOR ENG|060602 | 15| | and/or DELIMITER. DELIMITER '' indicates that strings are not ENG|060602 | 16| | delimited. ENG|060602 | 18|S | If COMPRESS is set, LOAD generates statements without unnecessary ENG|060602 | 19| | blanks. Data fields are written in their actual length and separated by ENG|060602 | 20| | the SEPARATOR character. If not chosen otherwise, CHAR data are ENG|060602 | 21| | enclosed in DELIMITER characters. ENG|060602 | 23| | The COUNT option defines how many records are to be written before LOAD ENG|060602 | 24|R | requests the next tape. ENG|060603 | 1| | Structure of the Target File ENG|060603 | 3| | Each result row produces one output record in the target file, the ENG|060603 | 4| | sequence is the same as in the result table. ENG|060603 | 6| | If no column descriptions are present, the column sequence as well is ENG|060603 | 7| | the same as in the result table. In this case the starting position of ENG|060603 | 8| | the first column is 1. All columns are put out in plaintext in the ENG|060603 | 9| | maximum length required by their definition. The column values are ENG|060603 | 10| | separated from each other by a character string (may be empty) ENG|060603 | 11| | currently set as separator via SET. ENG|060603 | 13| | Diverging from these default rules you can specify exactly which ENG|060603 | 14| | columns are to be placed to which positions of the output record that ENG|060603 | 15| | is initially filled with blanks. The instructions for the separate ENG|060603 | 16| | columns are executed in the order in which they appear in the ENG|060603 | 17| | statement. In this case columns that were selected but have no such ENG|060603 | 18|S | instruction do not appear in the output file. ENG|060603 | 19| | If the field length specified by the position is less than the ENG|060603 | 20| | definition length of the column, CHAR and BYTE column values may be ENG|060603 | 21| | truncated on the right. The output length of numerical columns is not ENG|060603 | 22| | allowed to be less than the maximum required. ENG|060603 | 23| E| ENG|060603 | 24| E| DATAEXTRACT name, account, title FROM customer ; ENG|060603 | 25| E| title 1-5 ENG|060603 | 26| E| name 10-19 ENG|060603 | 27| E| account 21-29 ENG|060603 | 28| E| OUTFILE customerextract ENG|060603 | 29|RE| ENG|060604 | 1| | Data Formats in the Target File ENG|060604 | 3| | You can define a data format other than CHAR for every column of the ENG|060604 | 4| | output file determined by a position. LOAD can convert FIXED column ENG|060604 | 5| | values into the numerical formats INTEGER, REAL, DECIMAL, and ZONED ENG|060604 | 6| | that are used in application programs. FLOAT database columns can only ENG|060604 | 7| | produce REAL output columns. ENG|060604 | 8| E| ENG|060604 | 9| E| DATAEXTRACT * FROM item ; ENG|060604 | 10| E| item_no 1-8 ENG|060604 | 11| E| descr 9-39 ENG|060604 | 12| E| stock 40-43 INTEGER ENG|060604 | 13| E| min_stock 44-45 INTEGER ENG|060604 | 14| E| price 46-53 DECIMAL (2) ENG|060604 | 15| E| weight 54-61 REAL ENG|060604 | 16| E| OUTFILE item.data ENG|060604 | 17| E| ENG|060604 | 18|RI| ##06010306Syntax ENG|060605 | 1| | Options for the Output of Numerical Columns ENG|060605 | 3| | All numerical columns of the result table can be edited by using the ENG|060605 | 4| | functions SCALE, ROUND, and TRUNC. ENG|060605 | 6| | SCALE (n) has the effect that the values of the table column are ENG|060605 | 7| | multiplied with the nth decimal power. ENG|060605 | 9| | TRUNC (n) causes the n+1st and all the following fractional digits of ENG|060605 | 10| | each number to be set to 0, while the first n fractional digits remain ENG|060605 | 11| | unchanged. ENG|060605 | 13| | ROUND (n) causes the column values to be rounded in such a way that ENG|060605 | 14| | only n non-zero fractional digits remain. ENG|060605 | 16| | Floating point numbers are converted to fixed point numbers before ENG|060605 | 17|S | TRUNC or ROUND is applied. ENG|060605 | 19| | The following example selects kilometre values from the table DISTANCE ENG|060605 | 20| | and writes meter values into the output file. ENG|060605 | 21| E| ENG|060605 | 22| E| DATAEXTRACT * FROM distance ; ENG|060605 | 23| E| from_city 1-10 ENG|060605 | 24| E| to_city 11-20 ENG|060605 | 25| E| road_kilometres 21-30 SCALE (3) ENG|060605 | 26| E| OUTFILE map.raw_data ENG|060605 | 27| E| ENG|060605 | 28|RI| ##0601030601Syntax ENG|060606 | 1| | Output of NULL Values ENG|060606 | 3| | For the representation of NULL values in the output file a character ENG|060606 | 4| | string is used which can be defined via the SET command or the file ENG|060606 | 5| | option NULL. ENG|060606 | 7| | Moreover you can define a constant value that is written to another ENG|060606 | 8| | place of the output record or replaces the default string and will be ENG|060606 | 9| | put out if the current field value is NULL. ENG|060606 | 10| E| ENG|060606 | 11| E| DATAEXTRACT hno, arrival, departure FROM reservation ; ENG|060606 | 12| E| hno 1-5 ENG|060606 | 13| E| arrival 7-14 ENG|060606 | 14| E| departure 16-23 IF NULL SET POS 16-30 = 'long term guest' ENG|060606 | 15| E| OUTFILE * ENG|060606 | 16| E| ENG|060606 | 17|RI| ##060106Syntax ENG|060607 | 1| | Text Constants in the Target File ENG|060607 | 3| | Additional fields with text constants can be placed into the target ENG|060607 | 4| | file among those output fields that are filled from the result table. A ENG|060607 | 5| | text constant, enclosed in single quotes, has to be specified instead ENG|060607 | 6| | of a column name or column number. ENG|060607 | 7| E| ENG|060607 | 8| E| DATAEXTRACT cno, first_name, name FROM customer ; ENG|060607 | 9| E| 'Customer Number:' 1-16 ENG|060607 | 10| E| 'Name :' 24-30 ENG|060607 | 11| E| cno 18-22 ENG|060607 | 12| E| first_name 32-41 ENG|060607 | 13| E| name 43-62 ENG|060607 | 14| E| OUTFILE customer.out ENG|060607 | 15| E| ENG|060607 | 16|RI| ##060106Syntax ENG|060608 | 1| | Generating Command Files with DATAEXTRACT FOR DATALOAD/DATAUPDATE ENG|060608 | 3| | The statement DATAEXTRACT FOR DATALOAD generates a command file which ENG|060608 | 4| | allows a table to be created and loaded again. ENG|060608 | 6| | The statement DATAEXTRACT FOR DATAUPDATE generates a command file which ENG|060608 | 7| | contains a DATAUPDATE statement and the extracted data. This command ENG|060608 | 8| | file can be used to undo changes made in a table with key columns. ENG|060608 | 10| | In contrast to the simple DATAEXTRACT statement it is not allowed with ENG|060608 | 11| | these backup statements to exclude rows or columns of the table by a ENG|060608 | 12| | WHERE condition. ORDER BY, however, may be specified. ENG|060608 | 14| | DATAEXTRACT FOR DATALOAD is meant for the backup of your own base ENG|060608 | 15| | tables. Only in this case a CREATE TABLE statement is generated. ENG|060608 | 17| | Regardless of the table being a base table or a view, LOAD generates a ENG|060608 | 18|S | DATALOAD, FASTLOAD, or DATAUPDATE statement and extracts the complete ENG|060608 | 19| | table contents. ENG|060608 | 21| | If two OUTFILE specifications are made, then the first file contains ENG|060608 | 22| | the statements, the second the data. This enables the editing of ENG|060608 | 23| | statements even in the case that the table contains BYTE columns or is ENG|060608 | 24| | very large. ENG|060608 | 26| | The COMPRESS option applied to the statement file has the effect that ENG|060608 | 27| | names are only output in their actual lengths. That is advantageous if ENG|060608 | 28| | the table has very many columns. ENG|060608 | 30| | All file options relate only to the file they are given with. Missing ENG|060608 | 31| | options (e.g. ASCII/EBCDIC, DATE format etc.) are set to defaults. The ENG|060608 | 32| | generated OUTFILE description contains all file options in order to ENG|060608 | 33| | make loading independent from the then valid default settings. ENG|060608 | 35| | As always the same character string is used for the representation of ENG|060608 | 36|S | NULL values when extracting data, the generated DATALOAD statement ENG|060608 | 37| | contains DEFAULT NULL conditions for all optional columns. The used ENG|060608 | 38| | NULL representation is recorded as file option NULL '<string>'. ENG|060608 | 40| | The example generates two files, one containing the statements CREATE ENG|060608 | 41| | TABLE customer, DATALOAD TABLE customer etc., the other containing the ENG|060608 | 42| | customer data. ENG|060608 | 43| E| ENG|060608 | 44| E| DATAEXTRACT FOR DATALOAD TABLE customer ENG|060608 | 45| E| OUTFILE customer.define ; OUTFILE customer.data ENG|060608 | 46| E| ENG|060608 | 47|RI| ##06010608Syntax ENG|060609 | 1| | Generating a DATALOAD Statement for the Extracted Data ENG|060609 | 3| | If you specify two OUTFILE descriptions in a normal DATAEXTRACT ENG|060609 | 4| | statement, a DATALOAD statement is written into the first file that ENG|060609 | 5| | allows you to load the extracted data if they stem from one table and ENG|060609 | 6| | include all mandatory columns. ENG|060609 | 7| E| ENG|060609 | 8| E| DATAEXTRACT cno, name, account, title FROM customer ; ENG|060609 | 9| E| title 1-5 ENG|060609 | 10| E| name 10-19 ENG|060609 | 11| E| account 21-29 ENG|060609 | 12| E| cno 31-35 ENG|060609 | 13| E| OUTFILE extract.dataload ENG|060609 | 14| E| OUTFILE customer_extract ENG|060609 | 15| E| ENG|060609 | 16|RI| ##060106Syntax ENG|060610 | 1| | Test Output on Screen ENG|060610 | 3| | Entering OUTFILE * as part of a DATAEXTRACT statement displays a ENG|060610 | 4| | section of the result table on the screen. At most 18 lines are shown. ENG|060610 | 5| | Explicit column descriptions must be formulated in such a way that the ENG|060610 | 6| | output rows fit the screen. Normally the upper left part of the table ENG|060610 | 7| | is displayed. A different area can be chosen via RUN with FROM option. ENG|060610 | 8| E| ENG|060610 | 9| E| DATAEXTRACT cno, first_name, name FROM customer ; ENG|060610 | 10| E| 'Customer Number:' 1-16 ENG|060610 | 11| E| cno 18-22 ENG|060610 | 12| E| 'Name :' 24-30 ENG|060610 | 13| E| first_name 32-41 ENG|060610 | 14| E| name 43-62 ENG|060610 | 15| E| OUTFILE * ENG|060610 | 16| E| ENG|060610 | 17|RI| ##06011722Syntax ENG|0607 | 1| | Loading and Extracting LONG Columns ENG|0607 | 2| | ----------------------------------- ENG|0607 | 4| M| #01Loading LONG Columns with DATALOAD ENG|0607 | 5| M| #02Updating LONG Columns with DATAUPDATE ENG|0607 | 6| M| #03Extracting LONG Columns with DATAEXTRACT ENG|0607 | 7|RM| #04Migration of a Table with LONG Columns ENG|060701 | 1| | Loading LONG Columns with DATALOAD ENG|060701 | 3| | LONG column values can be loaded along with other column values in one ENG|060701 | 4| | DATALOAD run. A long value is read from a separate file. The position ENG|060701 | 5| | specification of a LONG column denotes the position in the input line ENG|060701 | 6| | at which the name of that file can be found. ENG|060701 | 8| | The following statement inserts the content of the files '*.longtext' ENG|060701 | 9| | into the LONG column TEXT of the table COMMENT and at the same time ENG|060701 | 10| | assigns values to the KEY column SERIAL and the index field REFERENCE. ENG|060701 | 11| I| ##06010301Syntax ENG|060701 | 12| E| DATALOAD TABLE comment ENG|060701 | 13| E| serial 1-5 ENG|060701 | 14| E| reference 9-26 ENG|060701 | 15| E| text 30-43 ENG|060701 | 16| E| INFILE * ENG|060701 | 17| E| 1 | customer | customer.longtext ENG|060701 | 18|RE| 2 | hotel | hotel.longtext ENG|060702 | 1| | Updating LONG Columns with DATAUPDATE ENG|060702 | 3| | LONG column values can be updated by means of a DATAUPDATE statement ENG|060702 | 4| | just as other column values. The new values of the LONG columns are ENG|060702 | 5| | taken from extra files. The names of these files form part of the input ENG|060702 | 6| | lines containing the other data. ENG|060702 | 8| | The following statement replaces values of the LONG column CREDIT_STATE ENG|060702 | 9| | of the table CUSTOMER with contents of customer specific files named ENG|060702 | 10| | '*.credit_state'. ENG|060702 | 11| I| ##060104Syntax ENG|060702 | 12| E| DATAUPDATE TABLE customer ENG|060702 | 13| E| KEY cno 1 ENG|060702 | 14| E| SET credit_state 2 ENG|060702 | 15| E| INFILE * ENG|060702 | 16| E| COMPRESS ENG|060702 | 17| E| 3000,"Porter.credit_state" ENG|060702 | 18|RE| 3200,"Datasoft.credit_state" ENG|060703 | 1| | Extracting LONG Columns with DATAEXTRACT ENG|060703 | 3| | If a result table created via DATAEXTRACT contains LONG columns, the ENG|060703 | 4| | LONG field values are written to one or more independent files the ENG|060703 | 5| | names of which are stated in the normal data lines. One file is created ENG|060703 | 6| | for every row of every LONG column where the value is not NULL. ENG|060703 | 8| | The names of these files are generated based upon a pattern given in ENG|060703 | 9| | the LONGFILE description that is mandatory for each LONG column. A ENG|060703 | 10| | LONGFILE description consists of the column identifier (name or number) ENG|060703 | 11| | and a file name pattern with at least one trailing hashmark ('#'). In ENG|060703 | 12| | the actual file name the hashmarks are replaced by the row number, ENG|060703 | 13| | padded with leading zeros. ENG|060703 | 15| | The following statement selects four columns of the table COMMENT and ENG|060703 | 16| | creates the files 'comment.main', 'text0000001', 'addition001', ENG|060703 | 17|S | 'text0000002', 'addition002', etc. ENG|060703 | 19| E| ENG|060703 | 20| E| DATAEXTRACT serial, reference, text, addition FROM comment; ENG|060703 | 21| E| serial 1-5 ENG|060703 | 22| E| reference 9-26 ENG|060703 | 23| E| text 30-40 ENG|060703 | 24| E| addition 44-54 ENG|060703 | 25| E| OUTFILE comment.main ENG|060703 | 26| E| LONGFILE 3 text####### ENG|060703 | 27| E| LONGFILE 4 addition### ENG|060703 | 28| E| ENG|060703 | 29| | If the row number becomes greater than the available digits in any of ENG|060703 | 30| | the file names, the run is aborted inspite of existing result table ENG|060703 | 31| | rows. Note that this example statement would not create more than 1999 ENG|060703 | 32| | (1 + 2 * 999) files. ENG|060703 | 34|RI| ##060106Syntax ENG|060704 | 1| | Migration of a Table with LONG Columns ENG|060704 | 3| | If you do not want to edit the LONG column contents, you can specify a ENG|060704 | 4| | LONGFILE name without hashmarks. This drastically reduces the number of ENG|060704 | 5| | created files. ENG|060704 | 7| | All values of the corresponding LONG column are, one behind the other, ENG|060704 | 8| | written into that file. The same file name is consequently written into ENG|060704 | 9| | every long description field of the main data file, but it is followed ENG|060704 | 10| | by a position specifying the range of bytes that belong to this field. ENG|060704 | 12| | During a load run LOAD expects the positions to be in ascending order, ENG|060704 | 13| | but not necessarily without gaps. ENG|060704 | 15| | The following statement selects four columns of the table COMMENT and ENG|060704 | 16|S | creates the files 'comment.main', 'text.all' and 'addition.all'. ENG|060704 | 19| E| ENG|060704 | 20| E| DATAEXTRACT serial, reference, text, addition FROM comment; ENG|060704 | 21| E| serial 1-5 ENG|060704 | 22| E| reference 9-26 ENG|060704 | 23| E| text 30-40 ENG|060704 | 24| E| addition 44-54 ENG|060704 | 25| E| OUTFILE comment.main ENG|060704 | 26| E| LONGFILE text text.all ENG|060704 | 27| E| LONGFILE addition addition.all ENG|060704 | 28| E| ENG|060704 | 29|RI| ##060106Syntax ENG|0608 | 1| | Migration of the Database Catalog ENG|0608 | 2| | --------------------------------- ENG|0608 | 4| M| #01CATALOGEXTRACT^TABLE ENG|0608 | 5| M| #02CATALOGEXTRACT^USER ENG|0608 | 6| M| #03CATALOGEXTRACT^ALL ENG|0608 | 8| M| #04CATALOGLOAD^TABLE ENG|0608 | 9| M| #05CATALOGLOAD^USER ENG|0608 | 10|RM| #06CATALOGLOAD^ALL ENG|060801 | 1| | CATALOGEXTRACT TABLE ENG|060801 | 3| | Every user can apply the CATALOGEXTRACT TABLE statement to generate a ENG|060801 | 4| | command file which, in combination with DATAEXTRACT/DATALOAD or ENG|060801 | 5| | TABLEEXTRACT/TABLELOAD, allows the same table to be installed in ENG|060801 | 6| | another database. ENG|060801 | 8| | Not only the table definition, but also INDEX, VIEW, and FOREIGN KEY ENG|060801 | 9| | definitions that go with it are saved. If you defined privileges for ENG|060801 | 10| | the table, corresponding GRANT statements are written into the file. ENG|060801 | 11| E| ENG|060801 | 12| E| CATALOGEXTRACT TABLE customer ; OUTFILE customer.cat ENG|060801 | 13| E| ENG|060801 | 14|RI| ##06010801Syntax ENG|060802 | 1| | CATALOGEXTRACT USER ENG|060802 | 3| | Every user can apply the CATALOGEXTRACT USER statement to generate a ENG|060802 | 4| | command file which, in combination with DATAEXTRACT/DATALOAD or ENG|060802 | 5| | TABLEEXTRACT/TABLELOAD, allows his complete database area to be ENG|060802 | 6| | installed in another database. ENG|060802 | 8| | All tables and the dependent objects are saved (see also CATALOGEXTRACT ENG|060802 | 9| | TABLE). ENG|060802 | 10| E| ENG|060802 | 11| E| CATALOGEXTRACT USER ; OUTFILE sqltravel00.cat ENG|060802 | 12| E| ENG|060802 | 13|RI| ##06010801Syntax ENG|060803 | 1| | CATALOGEXTRACT ALL ENG|060803 | 3| | The SYSDBA of a location can apply the CATALOGEXTRACT ALL statement to ENG|060803 | 4| | generate a command file which creates the entire database catalog of ENG|060803 | 5| | this location, thus restoring all local users with all their objects. ENG|060803 | 6| | The restoration has to be done with CATALOAGLOAD ALL on an empty ENG|060803 | 7| | database. ENG|060803 | 8| E| ENG|060803 | 9| E| CATALOGEXTRACT ALL ; OUTFILE node02.cat ENG|060803 | 10| E| ENG|060803 | 11|RI| ##06010801Syntax ENG|060804 | 1| | CATALOGLOAD TABLE ENG|060804 | 3| | Every user can apply the CATALOGLOAD TABLE statement to execute a ENG|060804 | 4| | command file that has been generated by himself or by another user of ENG|060804 | 5| | the same group. ENG|060804 | 6| E| ENG|060804 | 7| E| CATALOGLOAD TABLE customer ; INFILE customer.cat ENG|060804 | 8| E| ENG|060804 | 9|RI| ##06010802Syntax ENG|060805 | 1| | CATALOGLOAD USER ENG|060805 | 3| | Every user can apply the CATALOGLOAD USER statement to execute a ENG|060805 | 4| | command file that has been generated by himself or by another user of ENG|060805 | 5| | the same group. ENG|060805 | 6| E| ENG|060805 | 7| E| CATALOGLOAD USER ; INFILE sqlreisen00.cat ENG|060805 | 8| E| ENG|060805 | 9|RI| ##06010802Syntax ENG|060806 | 1| | CATALOGLOAD ALL ENG|060806 | 3| | LOAD checks whether the executing user is the SYSDBA of this database ENG|060806 | 4| | location. If this is the case, the specified file is run through and ENG|060806 | 5| | the SQL statements included there are executed. Encrypted passwords and ENG|060806 | 6| | OWNER statements are converted into an executable form. ENG|060806 | 7| E| ENG|060806 | 8| E| CATALOGLOAD ALL ; INFILE node02.cat ENG|060806 | 9| E| ENG|060806 | 10|RI| ##06010802Syntax ENG|0609 | 1| | Migration of the Database Contents ENG|0609 | 2| | ---------------------------------- ENG|0609 | 4| M| #01TABLEEXTRACT^TABLE ENG|0609 | 5| M| #02TABLEEXTRACT^USER ENG|0609 | 6| M| #03TABLEEXTRACT^ALL ENG|0609 | 8| M| #04TABLEUNLOAD ENG|0609 | 10| M| #05TABLELOAD^TABLE ENG|0609 | 11| M| #06TABLELOAD^USER ENG|0609 | 12|RM| #07TABLELOAD^ALL ENG|060901 | 1| | TABLEEXTRACT TABLE ENG|060901 | 3| | Every user can apply the TABLEEXTRACT TABLE statement to generate a ENG|060901 | 4| | data file which, in combination with CATALOGEXTRACT/CATALOGLOAD and ENG|060901 | 5| | TABLELOAD TABLE, allows the same table to be installed in another ENG|060901 | 6| | database. ENG|060901 | 7| E| ENG|060901 | 8| E| TABLEEXTRACT TABLE customer ; OUTFILE customer.data ENG|060901 | 9| E| ENG|060901 | 10|RI| ##06010901Syntax ENG|060902 | 1| | TABLEEXTRACT USER ENG|060902 | 3| | Every user can apply the TABLEEXTRACT USER statement to generate a data ENG|060902 | 4| | file which, in combination with CATALOGEXTRACT/CATALOGLOAD and ENG|060902 | 5| | TABLELOAD USER, allows his complete database area to be installed in ENG|060902 | 6| | another database. ENG|060902 | 7| E| ENG|060902 | 8| E| TABLEEXTRACT USER ; OUTFILE sqltravel00.data ENG|060902 | 9| E| ENG|060902 | 10|RI| ##06010901Syntax ENG|060903 | 1| | TABLEEXTRACT ALL ENG|060903 | 3| | The SYSDBA of a location can apply the TABLEEXTRACT ALL statement to ENG|060903 | 4| | generate a data file which saves a given database state if combined ENG|060903 | 5| | with CATALOGEXTRACT ALL. In particular this data file can be used to ENG|060903 | 6| | migrate to another platform. ENG|060903 | 7| E| ENG|060903 | 8| E| TABLEEXTRACT ALL ; OUTFILE node02.data ENG|060903 | 9| E| ENG|060903 | 10|RI| ##06010901Syntax ENG|060904 | 1| | TABLEUNLOAD ENG|060904 | 3| | TABLEUNLOAD differs from TABLEEXTRACT only in so far as the data of all ENG|060904 | 4| | tables concerned are deleted from the database. No operations upon ENG|060904 | 5| | these tables are possible until their contents have been restored with ENG|060904 | 6| | TABLELOAD. ENG|060904 | 7| E| ENG|060904 | 8| E| TABLEUNLOAD customer ENG|060904 | 9| E| OUTFILE customer.data ENG|060904 | 10| E| ENG|060904 | 11|RI| ##06010902Syntax ENG|060905 | 1| | TABLELOAD TABLE ENG|060905 | 3| | Every user can use a file generated by a TABLEEXTRACT TABLE statement ENG|060905 | 4| | to load a matching table which has not to be identical with the one the ENG|060905 | 5| | data were taken from. If the target table can be loaded, it's contents ENG|060905 | 6| | is deleted beforehand. ENG|060905 | 7| E| ENG|060905 | 8| E| TABLELOAD TABLE customer ; INFILE customer.cat ENG|060905 | 9| E| ENG|060905 | 10|RI| ##06010903Syntax ENG|060906 | 1| | TABLELOAD USER ENG|060906 | 3| | Every user can load his tables using a file that has been generated by ENG|060906 | 4| | a TABLEEXTRACT USER statement executed by himself or someone of his ENG|060906 | 5| | user group. To define the tables beforehand, you can e.g. use ENG|060906 | 6| | CATALOGLOAD USER. ENG|060906 | 8| | If some part of the data cannot be loaded because the corresponding ENG|060906 | 9| | table does not exist or has no suitable structure, the records ENG|060906 | 10| | concerned are written into the error file. The error file is a valid ENG|060906 | 11| | input file for another TABLELOAD statement. ENG|060906 | 12| E| ENG|060906 | 13| E| TABLELOAD USER ; INFILE sqltravel00.data ; OUTFILE error.data ENG|060906 | 14| E| ENG|060906 | 15|RI| ##06010903Syntax ENG|060907 | 1| | TABLELOAD ALL ENG|060907 | 3| | This statement is reserved for the SYSDBA and should be combined with ENG|060907 | 4| | CATALOGLOAD ALL. ENG|060907 | 6| | The data file contains the full names (username and tablename) of the ENG|060907 | 7| | tables contained in the file. Users owning such tables should therefore ENG|060907 | 8| | exist in the database. If some part of the data cannot be loaded ENG|060907 | 9| | because the corresponding table does not exist or has no suitable ENG|060907 | 10| | structure, the records concerned are written into the error file. The ENG|060907 | 11| | error file is a valid input file for another TABLELOAD statement. ENG|060907 | 12| E| ENG|060907 | 13| E| TABLELOAD ALL ; INFILE node02.data ; OUTFILE error.data ENG|060907 | 14| E| ENG|060907 | 15|RI| ##06010903Syntax ENG|0610 | 1| | Porting Catalog and Contents of a Database ENG|0610 | 2| | ------------------------------------------ ENG|0610 | 4| M| #01DBEXTRACT ENG|0610 | 5|RM| #02DBLOAD ENG|061001 | 1| | DBEXTRACT ENG|061001 | 3| | DBEXTRACT is a shortened form of CATALOGEXTRACT ALL followed by ENG|061001 | 4| | TABLEEXTRACT ALL. ENG|061001 | 5| E| ENG|061001 | 6| E| DBEXTRACT ; OUTFILE node02.cat ; OUTFILE node02.data ENG|061001 | 7| E| ENG|061001 | 8|RI| ##06011001Syntax ENG|061002 | 1| | DBLOAD ENG|061002 | 3| | DBLOAD is a shortened form of CATALOGLOAD ALL followed by TABLELOAD ENG|061002 | 4| | ALL. ENG|061002 | 5| E| ENG|061002 | 6| E| DBLOAD ; INFILE node02.cat ; INFILE node02.data ; OUTFILE error.data ENG|061002 | 7| E| ENG|061002 | 8|RI| ##06011002Syntax ENG|0611 | 1| | The ORACLE Crossloader ENG|0611 | 2| | ---------------------- ENG|0611 | 4| M| #01Loading^an^ORACLE^Database ENG|0611 | 5|RM| #02Loading^the^Contents^of^an^ORACLE^Table ENG|061101 | 1| | Loading an ORACLE Database ENG|061101 | 3| | A file generated with the ORACLE function EXP containing any section of ENG|061101 | 4| | an ORACLE database can be loaded into an ADABAS database with LOAD ENG|061101 | 5| | ORACLEDB. ENG|061101 | 7| | LOAD executes the DDL statements of the file. Instead of GRANT CONNECT ENG|061101 | 8| | a CREATE USER statement is executed where the password is the same as ENG|061101 | 9| | the username. CONNECT is transformed into a statement that makes this ENG|061101 | 10| | user the active user. All statements starting with ALTER, COMMENT, ENG|061101 | 11| | CREATE or GRANT are executed with SQLMODE ORACLE set. ENG|061101 | 13| | INSERT statements are transformed into DATALOAD or FASTLOAD statements ENG|061101 | 14| | and, like the DDL statements, executed immediately so that the data ENG|061101 | 15| | from the following records of the export file can be processed. ENG|061101 | 17|SE| LOAD ORACLEDB INFILE expdat.dmp ENG|061101 | 19| | If the export file contains only tables of a single user this user can ENG|061101 | 20| | import the file with LOAD ORACLEDB. As soon as LOAD finds a statement ENG|061101 | 21| | that concerns another user's database objects the load run is aborted. ENG|061101 | 22| | Only the SYSDBA can load a FULL export. ENG|061101 | 24|RI| ##06011101Syntax ENG|061102 | 1| | Loading the Contents of an ORACLE Table ENG|061102 | 3| | A DATALOAD/FASTLOAD statement specifying the file option ORACLE loads ENG|061102 | 4| | an ORACLE export file that contains a single table. ENG|061102 | 6| | The load description can contain explicit column descriptions if the ENG|061102 | 7| | file content shall not be loaded as it is. In this case the position ENG|061102 | 8| | specifications must be the same as with the COMPRESS option, i.e. they ENG|061102 | 9| | must denote the serial numbers of the data fields in the input record. ENG|061102 | 10| | The default format without column descriptions is allowed as well. ENG|061102 | 12| | DDL statements in the input file are ignored. ENG|061102 | 13| E| ENG|061102 | 14| E| DATALOAD TABLE customer INFILE expdat.dmp ORACLE ENG|061102 | 15| E| ENG|061102 | 16|RI| ##06011102Syntax ENG|0613 | 1| | Other LOAD Statements ENG|0613 | 2| | --------------------- ENG|0613 | 4| M| #01Turn^AUTOCOMMIT^off/on (AUTOCOMMIT) ENG|0613 | 5| M| #02Switch^to^Another^Database^User (USER/USE) ENG|0613 | 6| M| #03Change^the^SQL^Mode (SQLMODE) ENG|0613 | 7| M| #04Select^the^Terminal^Character^Set (TERMCHARSET) ENG|0613 | 8|RM| #05Get^Detailed^Messages (MESSAGE ON) ENG|061301 | 1| | Turn AUTOCOMMIT off/on ENG|061301 | 3| | LOAD usually works in AUTOCOMMIT mode, i.e. each SQL statement entered ENG|061301 | 4| | by the user will be concluded with COMMIT. INSERT statements executed ENG|061301 | 5| | internally by a DATALOAD statement are committed every time the ENG|061301 | 6| | transaction size number is reached. ENG|061301 | 8| | The status message displayed after the execution of SQL statements ENG|061301 | 9| | includes the information wether AUTOCOMMIT is turned on or off. ENG|061301 | 10| E| ENG|061301 | 11| E| AUTOCOMMIT OFF ENG|061301 | 12| E| ENG|061301 | 13| | causes a transaction to last until you explicitly issue COMMIT or ENG|061301 | 14| | ROLLBACK (or until a timeout occurs). With ENG|061301 | 15| E| ENG|061301 | 16| E| AUTOCOMMIT ON ENG|061301 | 17| E| ENG|061301 | 18|R | the original condition is restored. ENG|061302 | 1| | Switch to Another Database User ENG|061302 | 3| | By applying the different USE statements you can terminate the current ENG|061302 | 4| | database session and start a new one with other parameters while you ENG|061302 | 5| | are executing a command file. ENG|061302 | 7| I| ##06011302Syntax ENG|061302 | 9| | USE SERVERDB changes only the SERVERDB, a user with the same name and ENG|061302 | 10| | password must exist in that database. ENG|061302 | 12| | USE USER changes the user and optionally also the SERVERDB. USE USER &U ENG|061302 | 13| | establishes a session with those parameters (user and database) in ENG|061302 | 14| | force when LOAD was first called. If NOLOG is specified after the ENG|061302 | 15| | connect parameters, a NOLOG session is started. ENG|061302 | 17| | USE USERKEY uses a USERKEY of the XUSER file to provide all required ENG|061302 | 18|S | information about the new user. ENG|061302 | 19| | The external names for SERVERDB, SERVERNODE, and USERKEY are adopted as ENG|061302 | 20| | given in the statement whereas the database objects USERNAME and ENG|061302 | 21| | PASSWORD have to be enclosed in double quotes if they shall not be ENG|061302 | 22| | converted to uppercase letters. ENG|061302 | 24| | The example statement looks for the entry DEFAULT in the XUSER file ENG|061302 | 25| | which shall provide the parameters for the new session: ENG|061302 | 26| E| ENG|061302 | 27| E| use userkey DEFAULT ENG|061302 | 28|RE| ENG|061303 | 1| | Change the SQL Mode ENG|061303 | 3| | The SQLMODE statement tells the database system which SQL dialect is ENG|061303 | 4| | used in the following SQL statements. The mode that has been specified ENG|061303 | 5| | when calling LOAD or via XUSER ceases to be in force. If none of the ENG|061303 | 6| | valid SQLMODE keywords has been specified, the mode is set to ADABAS. ENG|061303 | 7| | The status message displayed after the execution of SQL statements ENG|061303 | 8| | includes the information which SQLMODE was used. ENG|061303 | 10| | The following statement introduces the ANSI compatibility test of a ENG|061303 | 11| | command file: ENG|061303 | 12| E| ENG|061303 | 13| E| SQLMODE ANSI ENG|061303 | 14| E| ENG|061303 | 15|RI| ##06011303Syntax ENG|061304 | 1| | Select the Terminal Character Set ENG|061304 | 3| | Via CONTROL several transformation tables can be defined and activated ENG|061304 | 4| | that define the casting from characters entered on the user side to ISO ENG|061304 | 5| | ASCII resp. EBCDIC in the database. ENG|061304 | 7| | If you want to change temporarily to another transformation script or ENG|061304 | 8| | even to insert e.g. BYTE values without any changes you can apply the ENG|061304 | 9| | TERMCHARSET statements. ENG|061304 | 10| E| ENG|061304 | 11| E| USE TERMCHARSET "french" ENG|061304 | 12| E| ENG|061304 | 13| | The name of the transformation table is a database object like column, ENG|061304 | 14| | table, and user name. Therefore it must be enclosed in double quotes if ENG|061304 | 15| | case sensitivity is required. ENG|061304 | 17|RI| ##06011304Syntax ENG|061305 | 1| | Get Detailed Messages ENG|061305 | 3| | LOAD uses a default message if the first attempt failed to read a text ENG|061305 | 4| | from the message table. The statement ENG|061305 | 5| E| ENG|061305 | 6| E| MESSAGE ON ENG|061305 | 7| E| ENG|061305 | 8| | tells LOAD during the session that the detailed messages are now ENG|061305 | 9| | available and there is no need any more to make do with the default ENG|061305 | 10| | message. ENG|061305 | 12|RI| ##06011305Syntax ENG|0614 | 1| | Control Statements in a Command File ENG|0614 | 2| | ------------------------------------ ENG|0614 | 4| M| #01Structure^of^a^Command^File ENG|0614 | 5| M| #02Input^Data^in^the^Command^File ENG|0614 | 7| M| #03Branching^in^the^Control^Flow (IF-THEN-ELSE) ENG|0614 | 8| M| #04Leaving^the^Control^File (RETURN/STOP) ENG|0614 | 9| M| #05Setting^a^Return^Code (RETURNCODE) ENG|0614 | 10| M| #06Building^Logical^Units (INCLUDE) ENG|0614 | 11| M| #07Signals^During^Batch^Execution (SAY) ENG|0614 | 13|RM| #08Replacement^of^Parameters ENG|061401 | 1| | Structure of a Command File ENG|061401 | 3| | LOAD can start runs which process a series of statements from an ENG|061401 | 4| | external file. A command file can contain SQL and LOAD statements as ENG|061401 | 5| | well as input data. The control flow can be regulated by special ENG|061401 | 6| | control statements. ENG|061401 | 8| | Statements must be isolated by separator lines. Separator lines have a ENG|061401 | 9| | '/' or '*' at the first position. The rest of the line is treated as ENG|061401 | 10| | comment, i.e. it is not interpreted by LOAD. ENG|061401 | 12| | Comments within a statement are only possible if one of the SQL modes ENG|061401 | 13| | ORACLE and ANSI is set. The end of a statement is then marked by ';' or ENG|061401 | 14| | an empty line. In ORACLE mode, comments start with '/*' and are ENG|061401 | 15| | finished only if a '*/' occurs, regardless of the number of lines in ENG|061401 | 16| | between. In ANSI mode, the rest of a line is treated as comment if the ENG|061401 | 17|R | combination '--' occured. ENG|061402 | 1| | Input Data in the Command File ENG|061402 | 3| | If a load statement in a command file specifies INFILE *, the data is ENG|061402 | 4| | read from the command file as well. This data can be put directly after ENG|061402 | 5| | the load statement or be separated from it by a separator line. Use a ENG|061402 | 6| | separator line to indicate the end of data. ENG|061402 | 8| | Data that is not separated will be copied to the screen along with the ENG|061402 | 9| | statement. On the one hand, thus the statement can be executed more ENG|061402 | 10| | than once, on the other hand the size of the edit form might prove a ENG|061402 | 11| | restriction, and data formats other than CHAR cannot be loaded like ENG|061402 | 12|R | this. ENG|061403 | 1| | Branching in the Control Flow ENG|061403 | 3| | In addition to the execution of LOAD and SQL statements the control ENG|061403 | 4| | file can test the return code of a formerly executed statement in the ENG|061403 | 5| | IF part of a condition and branch accordingly to a THEN or ELSE part. ENG|061403 | 6| | The return code of a SQL statement is it's error code, whereas the ENG|061403 | 7| | return code of a DATALOAD or DATAUPDATE statement is the number of ENG|061403 | 8| | rejected lines. ENG|061403 | 10| | In addition to conditions of the form "IF $RC <= -900" constant ENG|061403 | 11| | branching via "IF TRUE" resp. "IF FALSE" is possible. Such conditions ENG|061403 | 12| | offer an easy way to include and exclude statements. ENG|061403 | 14| | A variant of the first condition form prevents a load run from being ENG|061403 | 15| | interrupted because a SQL statement produced a negative return code. ENG|061403 | 16| | The statement to be executed is specified after $RC and enclosed in ENG|061403 | 17| | parentheses, then follow the comparison operator and operand. In this ENG|061403 | 18|S | case $RC is a function; the condition evaluates the return code of the ENG|061403 | 19| | SQL statement in brackets. ENG|061403 | 21| | THEN and ELSE can be followed by single or compound statements. A ENG|061403 | 22| | single statement can itself be an IF-THEN-ELSE structure. A block of ENG|061403 | 23| | statements must be enclosed in BEGIN and END. ENG|061403 | 25| | The ELSE part is always optional. ENG|061403 | 27|RI| ##06011401Syntax ENG|061404 | 1| | Leaving the Control File ENG|061404 | 3| | If the RETURN statement is executed anywhere in the control file, this ENG|061404 | 4| | file is immediately closed. In case of it being an INCLUDE file, the ENG|061404 | 5| | run is continued with the next statement of the master file. RETURN in ENG|061404 | 6| | the command file is equivalent to the online command ==> END RUN. ENG|061404 | 8| | STOP in the command file is equivalent to the online command ==> EXIT. ENG|061404 | 9| | The STOP statement can have a numeric parameter varying from 0 to 127 ENG|061404 | 10| | that will be given as return code of the LOAD program. The default ENG|061404 | 11| | value of this program return code is the result of error codes, return ENG|061404 | 12| | codes and the number of rejected lines as shown below ENG|061404 | 13| | 7, if a SQL error occured, ENG|061404 | 14| | 8, if a LOAD error occured, ENG|061404 | 15| | 9, if input lines where rejected ENG|061404 | 16| | 10, if a file error occured. ENG|061404 | 17| | set in this order of priority. ENG|061404 | 18|RI| ##06011402Syntax ENG|061405 | 1| | Setting a Return Code ENG|061405 | 3| | The RETURNCODE statement explicitly assigns a new value to the variable ENG|061405 | 4| | $RC that is tested in an IF-THEN-ELSE structure. Thus the return code ENG|061405 | 5| | of the former statement can be overridden. ENG|061405 | 7|RI| ##06011403Syntax ENG|061406 | 1| | Building Logical Units ENG|061406 | 3| | The INCLUDE statement can be used to clearly structure a command file. ENG|061406 | 4| | If smaller logical units are kept in separate control files, these ENG|061406 | 5| | files can be executed as if their statements were part of the main ENG|061406 | 6| | control file by specifying an INCLUDE statement for each of them in the ENG|061406 | 7| | main file. ENG|061406 | 9| | In THEN or ELSE parts the INCLUDE statement must be enclosed by BEGIN ENG|061406 | 10| | and END as the included file will usually consist of more than one ENG|061406 | 11| | statement. ENG|061406 | 12| E| ENG|061406 | 13| E| INCLUDE QUERY.ins ENG|061406 | 14| E| ENG|061406 | 16|RI| ##06011404Syntax ENG|061407 | 1| | Signals During Batch Execution ENG|061407 | 3| | SAY displays the text following it. The statement has been designed for ENG|061407 | 4| | BATCH runs but can be applied online as well. The text will then appear ENG|061407 | 5| | in one of the message lines below the edit form. ENG|061407 | 7| | The text can contain parameters named &1 through &9, &U and $RC. These ENG|061407 | 8| | are replaced by proper values, if possible. &1 to &9 refer to the ENG|061407 | 9| | parameters given when calling LOAD. &U is replaced by the current user ENG|061407 | 10| | name, $RC by the return code of the last statement that has been ENG|061407 | 11| | executed. ENG|061407 | 12| E| ENG|061407 | 13| E| SAY Hello, &U, the return code of the last statement was $RC ENG|061407 | 14| E| ENG|061407 | 16|RI| ##06011405Syntax ENG|061408 | 1| | Replacement of Parameters ENG|061408 | 3| | The formal parameters &1 to &9 contained in control files can be ENG|061408 | 4| | replaced by arbitrary strings if these have been specified as actual ENG|061408 | 5| | parameters when calling LOAD with RUN or BATCH. ENG|061408 | 7| | The individual parameters are separated from each other by blanks. If a ENG|061408 | 8| | character string consisting of several words shall be inserted for &n, ENG|061408 | 9| | then the n-th parameter must be enclosed in single quotes. Quotes are ENG|061408 | 10| | also required to preserve mixed case. ENG|061408 | 12| | If a statement contains a formal parameter &n and less than n ENG|061408 | 13| | parameters were specified with the call, then &n will be replaced by an ENG|061408 | 14| | empty string. However, if no actual parameters at all have been given ENG|061408 | 15|R | an error is reported. ENG|0615 | 1| | LOAD Commands ENG|0615 | 2| | ------------- ENG|0615 | 3| M| #01Leaving^LOAD^with^Query (END) ENG|0615 | 4| M| #02Leaving^LOAD^without^Query (EXIT) ENG|0615 | 5| M| #03Starting^a^Statement^or^a^Command^File (RUN) ENG|0615 | 6| M| #04Fetching^the^Logically^Next^Statement (NEXT) ENG|0615 | 7| M| #05Skipping^the^Logically^Next^Statement (SKIP) ENG|0615 | 8| M| #06Fetching^the^Physically^Next^Statement (SCAN) ENG|0615 | 9| M| #07Explicitly^Closing^a^Command^File (END RUN) ENG|0615 | 10| M| #08Displaying^the^Online^Protocol (PROT) ENG|0615 | 11| M| #09Displaying^the^LOAD^Version (VERSION) ENG|0615 | 12| M| #10Branching^to^the^Operating^System (EXEC) ENG|0615 | 13| M| #11Setting^Your^Environment (SET) ENG|0615 | 14|RM| #13Calling^LOAD^in^BATCH^Mode (BATCH) ENG|061501 | 1| | Leaving LOAD with Query ENG|061501 | 3| | ==> END terminates the current session if the user confirms the ENG|061501 | 4| | corresponding query by another END command. The query protects you ENG|061501 | 5| | against an unwanted session end due to an inadvertent use of the F3 or ENG|061501 | 6| | END key. ENG|061501 | 8| | The END key serves moreover to leave display modes like PROTOCOL, ENG|061501 | 9| | OUTPUT, BREAK. ENG|061501 | 11|RI| ##06011501Syntax ENG|061502 | 1| | Leaving LOAD without Query ENG|061502 | 3| | ==> EXIT terminates the LOAD session at once. In some environments ENG|061502 | 4| | there is additionally an EXIT key. ENG|061502 | 6|RI| ##06011502Syntax ENG|061503 | 1| | Starting a Statement or a Command File ENG|061503 | 3| | ==> RUN without parameters starts the statement found in the edit form. ENG|061503 | 5| | RUN with range option serves to load a specific range of the input ENG|061503 | 6| | file. The FROM part determines the starting line, the FOR part the ENG|061503 | 7| | number of lines that shall be loaded. Default values are FROM 1 and FOR ENG|061503 | 8| | * (* = all input lines from the starting line). ENG|061503 | 10| | If you specify RUN FROM 0 or RUN FOR 0 LOAD only checks the syntax of ENG|061503 | 11| | the statement. ENG|061503 | 13| | The STOP option of the RUN command offers the opportunity to interrupt ENG|061503 | 14| | a load run when a certain amount of lines has been rejected. If you ENG|061503 | 15| | omit it, the run is interrupted when the number of rejected lines ENG|061503 | 16| | equals the currently set transaction size. ENG|061503 | 18|S | During the interruption you can do any of the following ENG|061503 | 19| | a) inquire in the online protocol of rejected lines why the lines have ENG|061503 | 20| | not been inserted (PROT) ENG|061503 | 21| | b) cancel the run (QUIT) ENG|061503 | 22| | c) resume the run under the same conditions, i.e. let it stop again ENG|061503 | 23| | when the next n rejected lines have piled up (TEST) ENG|061503 | 24|S | d) continue the run without further interrupts (GO ON) ENG|061503 | 37| | RUN followed by a file name starts the execution of a series of ENG|061503 | 38| | statements contained in this file. The file name can optionally be ENG|061503 | 39| | enclosed by single quotes. It is in neither case converted to uppercase ENG|061503 | 40| | letters if this is not common practice of the operating system. ENG|061503 | 42| | If the option PROMPT is not specified, a statement is displayed on the ENG|061503 | 43| | screen and executed immediately, then the next statement is displayed ENG|061503 | 44| | and executed and so on until the end of the file is reached or until an ENG|061503 | 45| | error occurs. In the latter case LOAD enters INPUT mode again. The next ENG|061503 | 46| I| statement can then be brought to the screen applying ##061504NEXT or SCAN. ENG|061503 | 48| | If the option PROMPT is specified, the first statement is brought to ENG|061503 | 49| I| the screen with ##061506SCAN. ENG|061503 | 51|RI| ##06011503Syntax ENG|061504 | 1| | Fetching the Logically Next Statement ENG|061504 | 3| | ==> NEXT goes through the currently accessed command file, the name of ENG|061504 | 4| | which is displayed above the input area of the screen, and looks for ENG|061504 | 5| | the next statement to be executed taking the flow of control into ENG|061504 | 6| | account. This statement is copied into the edit form and can be started ENG|061504 | 7| | with RUN. ENG|061504 | 9| | SQL statements that are part of IF conditions are not displayed by NEXT ENG|061504 | 10| | and will be executed to determine the control flow. ENG|061504 | 12| | NEXT NOPROMPT switches to NOPROMPT mode where the statements in the ENG|061504 | 13| | file are executed without user interaction. If an error occurs, LOAD ENG|061504 | 14| | returns to PROMPT mode. ENG|061504 | 16|RI| ##06011504Syntax ENG|061505 | 1| | Skipping the Logically Next Statement ENG|061505 | 3| | ==> SKIP goes through the currently accessed command file, the name of ENG|061505 | 4| | which is displayed above the input area of the screen, and skips one or ENG|061505 | 5| | more statements that would be executed taking the flow of control into ENG|061505 | 6| | account. The following statement is copied into the edit form and can ENG|061505 | 7| | be started with RUN. ENG|061505 | 9| | SQL statements that are part of IF conditions are not skipped but ENG|061505 | 10| | executed to determine the control flow. ENG|061505 | 12| | SKIP skips one, SKIP 5 skips five statements. SKIP infile looks for the ENG|061505 | 13| | next statement that contains a string starting with INFILE (case is ENG|061505 | 14| | ignored). ENG|061505 | 16|RI| ##06011505Syntax ENG|061506 | 1| | Fetching the Physically Next Statement ENG|061506 | 3| | ==> SCAN displays the next statement without executing it. In contrast ENG|061506 | 4| | to NEXT control statements are not evaluated but displayed as well. The ENG|061506 | 5| | shown statement consequently consists of all lines of the command file ENG|061506 | 6| | up to the next separator line. ENG|061506 | 8| | SCAN followed by a string looks for a word starting with this string ENG|061506 | 9| | regardless of it's meaning. SCAN stop would display e.g. ENG|061506 | 11| | STOP 100 ENG|061506 | 13|RI| ##06011506Syntax ENG|061507 | 1| | Explicitly Closing a Command File ENG|061507 | 3| | ==> END RUN closes the current command file before it has been gone ENG|061507 | 4| | through completely. In an INCLUDE cascade the file containing the ENG|061507 | 5| | INCLUDE becomes the current command file, it's name is displayed above ENG|061507 | 6| | the input area instead of that of the former file. ENG|061507 | 8| | END RUN is the command equivalent of the control statement RETURN. ENG|061507 | 10|RI| ##06011507Syntax ENG|061508 | 1| | Displaying the Online Protocol ENG|061508 | 3| | ==> PROT displays a protocol of the lines rejected during the last load ENG|061508 | 4| | run. ENG|061508 | 6| | Not more than 18 lines of the protocol are displayed. If truncation ENG|061508 | 7| | proves necessary, the left part of the last lines is shown. ENG|061508 | 9|RI| ##06011508Syntax ENG|061509 | 1| | Displaying the LOAD Version ENG|061509 | 3| | ==> VERSION displays a message containing the version number and date. ENG|061509 | 5|RI| ##06011509Syntax ENG|061510 | 1| | Branching to the Operating System ENG|061510 | 3| | ==> EXEC executes the operating system command given as parameter. ENG|061510 | 5|RI| ##06011510Syntax ENG|061511 | 1| | Setting Your Environment ENG|061511 | 3| | With ==> SET every user can alter certain control parameters according ENG|061511 | 4| | to his own requirements. Apart from the general parameters ENG|061511 | 5| | - language (of the messages) ENG|061511 | 6| | - representation of NULL values (used e.g. by DATAEXTRACT) ENG|061511 | 7| | - plaintext number format (/<thousands>/<decimal>/) ENG|061511 | 8| | - date format (INTERNAL, EUR, ISO, USA, JIS, free format) ENG|061511 | 9| | - time format ( " , " , " , " , " , " ) ENG|061511 | 10| | - timestamp format (" , " , " , " , " , " ) ENG|061511 | 11| | - Boolean ('<TRUE>/<FALSE>') ENG|061511 | 12| | - separator (used as default by DATAEXTRACT) ENG|061511 | 13| | - printer format (see below) ENG|061511 | 14| | - number of copies printed ENG|061511 | 15| | - system editor (enter the command SYSED to use it) ENG|061511 | 16| | there are a few parameters specific for LOAD, namely ENG|061511 | 17| | - LOAD presentation (see below) ENG|061511 | 18|S | - LOAD protocol file name (LOAD does not work without it) ENG|061511 | 19| | - transaction size (number of INSERTs between COMMITs) ENG|061511 | 21| | Presentation and printer setting can be altered via separate sub-menus ENG|061511 | 22| | of SET. The print format menu offers parameters that determine the page ENG|061511 | 23| | layout and the name of the printer to be used. The presentation menu ENG|061511 | 24| | displays colours and attributes of the different screen areas. ENG|061511 | 26| | There is a default setting for every SET parameter. Individual changes ENG|061511 | 27| | remain valid beyond a session's end. ENG|061511 | 29|RI| ##06011511Syntax ENG|061513 | 1| | Calling LOAD in BATCH Mode ENG|061513 | 3| | When calling LOAD, you can specify that the run shall be made batch- ENG|061513 | 4| | like, i.e. without user interaction. To achieve this, you have to ENG|061513 | 5| | specify a BATCH command that starts a command file. ENG|061513 | 7| | A BATCH run is only aborted if a severe error occured that would bring ENG|061513 | 8| | about the session's end in interactive mode as well. Otherwise the ENG|061513 | 9| | faulty statement is skipped. All statements are recorded in the ENG|061513 | 10| | protocol file along with their results resp. error messages. ENG|061513 | 12| | If any other LOAD command than BATCH is specified when calling LOAD ENG|061513 | 13| | this command will be executed as if it had been entered via the command ENG|061513 | 14| | line (==>). ENG|061513 | 15|RI| ##06011513Syntax ENG|0699 | 1| | The Error Message 12308 ENG|0699 | 3| | 12308 File content does not match table definition, reason code &1 ENG|0699 | 5| | Explanations: ENG|0699 | 7| | The following reasons can lead to an abortion of a TABLELOAD run ENG|0699 | 9| | 1. The header block expected in a TABLELOAD file (byte 6 = 0) is not ENG|0699 | 10| | available. ENG|0699 | 12| | 2. The integer swap form (1 for normal, 2 for full swapping, 3 for ENG|0699 | 13| | partial swapping) in byte 8 of the header block was not found. ENG|0699 | 15| | 3. The description of the table stored in the file has an invalid ENG|0699 | 16| | length (e.g. too many rows with byte 6 = 1). ENG|0699 | 18|S | 4. The description of the table stored in the file is not complete. ENG|0699 | 19| | 5. The file contains less CONSTRAINT lines then expected due to the ENG|0699 | 20| | table description. ENG|0699 | 22| | 6. The table description says that DEFAULTS are defined for the source ENG|0699 | 23| | table, these lines are not present in the file. ENG|0699 | 24| | 7. There are no data records for the table stored in the file. ENG|0699 | 25| | 8. The columns of the source table and of the target table differ in ENG|0699 | 26| | their definitions to such an extent that the file cannot be used for ENG|0699 | 27| | the loading of the target table. ENG|0699 | 28| | 9. The source table and the target table have different numbers of ENG|0699 | 29|R | columns.