Home

iLoader User`s Manual - ALTIBASE Customer Support

1. SELECT Indicates an optional element NOT i Indicates a mandatory element comprised of options One and only one option must be specified ADD DROP Indicates an optional element comprised of options 7 ASC f DESC lii Preface About This Manual Elements Meaning Indicates an optional element in which multiple elements may be specified Acomma must precede all but the first option ASC 1 DESC CN KEZ The code examples explain SQL stored procedures iSQL and other command line statements The printing conventions used in the code examples are described in the following table Rules Meaning Example Indicates an optional item VARCHAR size FIXED VARIABLE Indicates a mandatory field for which one or ENABLE DISABLE COMPILE more items must be selected Argument indicating optional or mandatory ENABLE DISABLE COMPILE fields H ENABLE DISABLE COMPILE Indicates that the previous argument is SQL gt SELECT ename FROM repeated or that sample code has been employee omitted ENAME SWNO HJNO HSCHOI 20 rows selected Other svm Svmbols other than those shown above are EXEC p1 1 bols part of the actual code acc NUMBER 11 2 Ital
2. NUM integer NAME varchar 30 DATA NLS USE KO16KSC5601 iSQL INSERT INTO seqTable name VALUES JOHN 1 row inserted iSQL INSERT INTO seqTable name VALUES JOHNSON 1 row inserted iSQL SELECT FROM seqTable SEQTABLE NUM SEQTABLE NAME 1 JANE 2 SARA JOHN JOHNSON 4 rows selected After using the FORM file to back up seqTable table to a file by executing a command like iLoader gt out f seqTable fmt d seqTable dat when the file is examined it looks like shell cat seqTable dat 1 JANE 2 SARA JOHN JOHNSON After uploading the backup file to the current database the following statement is used iLoader gt in f seqTable fmt d seqTable dat mode replace UPLOAD 16467 Load Count 4 If the records in the seqTable table are queried the results will be as follows iSQL gt SELECT FROM seqTable SEOTABLE NUM SEOTABLE NAME 30 JANE 32 SARA 34 JOHN 36 JOHNSON 4 rows selected Modifying Date Format Strings in a FORM File The format for date data type columns can be set as follows case insensitive and download or upload actions can be conducted on the basis of the format The date format should be specified at the bottom of the FORM file when modifying it The default is DATEFORM YYYY MM DD HH MI SS dateFORM YYYY MM DD HH MI SS SSSSSS dateFORM YYYY MM DD HH MI SS SSSSSS dateFORM YY MM DD HH MI SS SSSSSS dateFORM YY MM DD HH MI SS SSSSSS dateFORM YYYYMMDDHHMISSS
3. Records in the database table that satisfy the conditions specified in the FORM file are saved in text file form Downloaded data will be saved according to the character set specified using the ALTIBASE_NLS_USE environment variable If the database character set is different from that speci fied in ALTIBASE_NLS_USE the data will be converted When downloading data ALTIBASE displays the table name every 5 000 records and also displays the total number of records after all records have been downloaded The following syntax is used to download data iloader out S servername U user_id P password f form file d backup files split n array array_size parallel count In the following example the T1 fmt file is used to download data to the T1 dat file shell gt iloader out s 127 0 0 1 u sys p manager f T1 fmt d T1 dat Altibase Data Load Download utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISQL CONNECTION TCP DATA NLS USE KO16KSC5601 NCHAR UTF16 YES Total 3 records downloaded T1 DOWNLOAD 11 2320 msec shell cat T1 dat Ju young 20 M Group A Chang ho 30 M Group C Ji hyun 24 F Group D iLoader can be used to upload backup files or files containing edited data to the database The data character set must be specified using ALTIBASE NLS USE when uploading data iLoader converts data saved in the character set specified in A
4. can be entered as t or At a general command prompt they can be entered as t or rrow term row terminator default is n The details are the same as for the t option e enclosing term Block delimiter for enclosing fields The details are the same as for the t option lob lob option string See LOB Option Because the maximum allowable size of LOB data is 4GB problems may arise when LOB data larger than 2GB are saved on 32 bit operat ing systems Therefore this option can be used to specify how to handle LOB type data If this option is not specified LOB columns will be handled just like other columns replication true false This option specifies whether to perform replication when uploading data If this option is omitted the default is to perform replication so in order to prevent replication this option must explicitly be set to false U u login id The user ID P p password The user password S s servername The server name mode mode type APPEND This adds data to existing tables default REPLACE This uses the DELETE statement to delete all data from the existing table and then populate the table with the new records TRUNCATE Similar to REPLACE this deletes all data from the existing table and then populates the table with the new records however it uses the TRUNCATE statement to do so TRUNCATE affords some performance bene
5. shell gt isql s 192 168 1 71 port 20594 Altibase Client Query utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISQL CONNECTION TCP SERVER 192 168 1 71 PORT NO 20594 iSQL select from department DNO DNAME DEP LOCATION MGR NO A001 technical Mapo 1 D001 engine devt Yeouido 10 iLoader Users Manual 26 Using iLoader in Batch Mode C001 marketing Gangnam 9 C002 planning Gangnam 15 F001 operations Sinchon 9 5 rows selected 27 Using iLoader Using iLoader in Interactive Mode Using iLoader in Interactive Mode If you don t enter any of the mandatory inputs for iLoader execution i e formout out in the iLoader gt prompt will be displayed and iLoader will be executed in interactive mode In interactive mode command line options can be used the same way as in batch mode Starting iLoader You must first be logged in before entering interactive mode Connection information can be entered either on the command line or via iLoader Additional information required to establish a connection with the server includes the server name S user name U and password P The user name and password are case insensitive How to Enter Connection Information on the Command line shell iloader s 127 0 0 1 u sys p manager Altibase Data Load Download utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries
6. FAQs 49 Troubleshooting and FAQs Troubleshooting Troubleshooting Record Upload Failure Records can fail to be uploaded in a variety of ways as described below Duplicate data a primary key or unique index already exists in the table to which data are to be uploaded If duplicate data already exist in the table to which data are to be uploaded the duplicate data and only the duplicate data will be saved in a bad log file which is set using the bad bad file option while the remainder of the data which are not duplicate data will be uploaded normally When this problem occurs the data must be changed manually or the unique key constraint violation must be resolved and another upload attempt made Record data contain characters used as delimiters If characters used as delimiters exist in the record data the delimiters must be changed to some other set of characters and another upload attempt made Insufficient space to save data If there is not enough database space to save the data manage the database or the table or perform compaction to secure more free disk space and then upload data from the point at which the previous upload attempt was interrupted Character data are saved as This occurs if the character data can t be expressed using the character set used by the data base The database character set needs to be changed which is accomplished by creating the database again Only multilin
7. Array Insert and Array Insert A comparison of Atomic Array Insert and Array Insert reveals the following Array Insert executes individual statements separately whereas Atomic Array Insert can combine several statements and handle them as though they were a single statement Therefore when the atomic option is used if even one statement fails all statements fail Only one result is returned for all statements collectively rather than a result being returned for each individ ual statement Figure 2 1 Return Values of Array Insert and Atomic Array Insert Array Insert Atomic Array Insert insert SUCCESS Insert SUCCES Insert FAIL Insert FAIL insert S UCCESS Hollback Insert FAIL While Array Insert s original advantage is low communication overhead Atomic Array Insert realizes even faster performance because it reduces the number of statements Array Insert and Atomic Array Insert are compared in the following table Table 2 1 Differences between Array Insert and Atomic Array Insert Characteristic Array Insert Atomic Array Insert Number of Statements Number of Array Elements One Number of Results Number of Array Elements One Transmission Speed Fast Very Fast Atomic Array Insert Limitations The following limitations govern the use of Atomic Array Insert Note N number of records Table 2 2 Atomic Array Insert Limitations Array Insert Atomic Array Insert Foreign Key Operates
8. Normally Operates Normally Unique Key Operates Normally Operates Normally 19 Using iLoader Performance Options Array Insert Atomic Array Insert Not null Operates Normally Operates Normally Trigger Each Row Executed N times Executed N times Trigger Each Statement Executed N times Executing Once Partitioned Table Operates Normally Operates Normally time Sequence Executed N times Executed N times SYSDATE Executed N times Executing Once LOB Column Operates Normally Atomic attribute is not guaran teed when errors occur Procedure Operates Normally Operates Normally SubQuery A new view is observed every The first executed view is observed The atomic option must be used with array array_size and must not be used on tables that include LOB columns Furthermore this option is only useful when uploading data The so called direct path option has been provided to support the uploading of data into a disk table Direct Path is a method of writing data in a data file using a separate direct path and a flush manager without going through the regular buffer manager Moreover execution takes place according to a new APPEND method whereby an extent is assigned for a tablespace rather than using a normal extent assigned to a table Consequently performance is improved but space may be wasted The following restrictions apply when up
9. after adding the following condition clause to the FORM file table CUST PROD SERV_NO varchar 10 PROD ID char 5 53 Troubleshooting and FAQs FAQs EFF DATE char 14 EXP DATE char 14 CUST_TYPE char 2 L MOD DATE date DATEFORM VVVV MM DD HH MI SS DOWNLOAD CONDITION WHERE EXP DATE gt 20050301000000 The following error occured and it would not run Input Command Parser Error or Reserved Keyword Used DOWNLOAD 338 A When you specify conditions in a download condition clause you must use double quota tion marks DOWNLOAD CONDITION WHERE EXP DATE 20050301000000 This is how you use the quotation marks Q I want to download data after sorting it by the primary key am wondering whether iLoader supports an ORDER BY clause A As shown in the following example use a download condition to specify the where condi tion shell iloader s 127 0 0 1 u sys p manager out f dept fmt d dept dat silent Total 5 records downloaded DOWNLOAD 41044 shell cat dept dat A001 technical 1 Mapo Wed DOO1 engine devt 1 TVeouido n LO C001 marketing Gangnam ut C002 planning Gangnam l5 F001 operations Sinchon 29 After modifying the FORM file as follows you can download data sorted according to the pri mary key table department DNO bytes 2 lt Primary Key DNAME char 20 DEP LOCATION char 9 MGR NO integer download condition where DNO
10. field_term r row_term mode mode_type l commit commit unit bad badfile l log logfile l e enclosing array array sizel l replication true falsel l split number readsize size l errors count lob lob_option_string atomic parallel count direct ioparallel count rule csv This screen Specify server name to connect Specify user name to connect Specify password of specify user name Specify port number to communication No display Copyright No display Elapsed Time displayquery display query string Specify NLS Other Options log cution bad recorded Example iLoader records task results execution progress and errors that occurred during exe rows that could not be uploaded due to the occurrence of errors during loading are iLoader in f book fmt d book dat log book log bad book bad UPLOAD 4272 Load Count 3 shell cat book bad shell cat book log DataLoad TableName book Start Time Mon Jan 24 11 07 00 2005 End Time Mon Jan 24 11 07 00 2005 Total Row Count 3 Load Row Count 3 Error Row Count 0 33 Using iLoader Online Help and Other Options iLoader Users Manual 34 3 FORM Files FORM Files About FORM Files About FORM Files A FORM file is similar to a Create Table statement in that contains information for iLoader about the attributes of a table with which data are to be exchanged iLoader can be used to automatically
11. file but the current time is to be entered in the TIME STAMP column enter ADD DEFAULT without quotation marks at the end of the line describing the TIMESTAMP column in the FORM file to be uploaded or delete the line describing the TIME STAMP column from the FORM file If there are no TIMESTAMP data in the data file and a null value is to be entered in the TIMESTAMP column enter ADD NULL at the end of the line describing the TIMESTAMP column in the FORM file to be used for uploading data If there are no TIMESTAMP data in the data file but a specified value is to be entered in the TIME STAMP column enter ADD YYYYMMDD HHMISS at the end of the line describing the TIMESTAMP column in the FORM file to be used for uploading data YYYYMMDD HHMISS Example of specified values 20040623 20040623102315 If a value corresponding to a TIMESTAMP column is in the data file To discard the value in the data file and enter the current time instead enter SKIP DEFAULT at the end of the TIMESTAMP column of the FORM file to be uploaded To enter NULL instead of the value in the data file enter SKIP NULL at the end of the TIMESTAMP iLoader Users Manual 42 About FORM Files column of the FORM file to be uploaded To enter a specified value instead of the value in the data file enter ADD YYYYMMDD HHMISS at the end of the TIMESTAMP column of the FORM file to be uploaded Use the edited FORM file to upload data to the cu
12. gt vi t2_add_val fmt table TEST2 Il integer I2 timestamp ADD 20040623 lt Revised Part DATEFORM YYYY MM DD HH MI SS DATA NLS USE KO16KSC5601 shell iloader in s 127 0 0 1 u sys p manager f t2 add val fmt d test dat ISQL CONNECTION TCP DATE FORMAT YYYY MM DD HH MI SS UPLOAD 1004 Load Count 3 iSQL gt SELECT FROM test2 TEST2 I1 TEST2 12 1 40D8497000000000 2 40D8497000000000 3 40D8497000000000 3 rows selected SKIP DEFAULT iSQL gt CREATE TABLE test il INTEGER i2 TIMESTAMP 45 FORM Files About FORM Files Create success iSQL CREATE TABLE test2 il INTEGER i2 TIMESTAMP Create success iSQL INSERT INTO test VALUES 1 BYTE 40D930BF000A7566 1 row inserted iSQL INSERT INTO test VALUES 2 BYTE 40D930BF000A7566 1 row inserted iSQL INSERT INTO test VALUES 3 BYTE 40D930BF000A7566 1 row inserted Shell iloader formout s 127 0 0 1 u sys p manager T TEST f test fmt Shell iloader out s 127 0 0 1 u sys p manager f test fmt d test dat DATE FORMAT YYYY MM DD HH MI SS Total 3 records downloaded DOWNLOAD 993 shell cat test dat 1 40D930BF000A7566 2 40D930BF000A7566 3 40D930BF000A7566 Shell iloader formout s 127 0 0 1 u sys p manager T TEST2 f t2 skip default fmt shell vi t2 skip default fmt table TEST2 Il integer I2 timestamp SKIP DEFAULT lt Revised Part DATEFORM YYYY MM DD HH MI SS DATA
13. set to 0 execution continues regardless of the number of errors When this option is used in conjunction with the parallel option if the number of errors exceeds the specified value for one of multiple threads executing in parallel all threads are terminated If the S U and P command line options are omitted the user will be prompted to enter the values of these options manually at the time of execution shell iloader formout T test f test fmt Altibase Data Load Download utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved Write Server Name enter 127 0 0 1 Write UserID sys Write Password ISQL CONNECTION TCP iLoader gt If loader is entered without any options at a shell prompt the iLoader prompt appears and work can be conducted in interactive mode All of the options described above are case sensitive except for S U and P The in out formout option must be entered before any of the other command options Field delimiters row delimiters and block delimiters cannot be subsets of each other In other words the field delimiter must not contain the row delimiter and the row delimiter must not contain the field delimiter Moreover column values cannot contain these delimiters 11 Using iLoader General Options For example if the field delimiter is and the row delimiter is a row having 3 column
14. tct eee eee XE n oe eei e EA Ce RENDER ER REN E ELENA 32 ure P 32 6 at 0 a RUD oe ceo DEE eS 33 35 FORM Md rime E M P 35 AbottiFtORM FIlES i 36 Writing SEQUENCE Statements beinin ea AAEN A e EAEE E S 36 Modifying Date Format Strings in a FORM File ssssscssssscsssecssssseccssescesusecssssseesnsecesusccssusecesssccssnsscessnsccesuseessnseeecanecsenseesaneess 37 Writing Condition Clauses ettet e r A a eee tU epe t nter tie deed 40 Handling TIMESTAMP Columis sissisotaa s esp etb eee EN AAAS eek tee fe a grebbep vete Ria 42 4 Troubleshooting and FAQS RE 49 Ere Viel Tiere 50 Record Upload Failures cito c eet re AREAS e ERR REL EARN P OUR ERE Tee 50 FORM File Parsing Errors terreri ER Vetere b A t rte tee eerte 50 Petformiance Issiles o tee Et eM E Re tu md e rata 51 aO Si OSAAN MNT MMC MM Len PN PON EQ SML OR DRAKACOROUO LM E 53 Preface About This Manual About This Manual This manual describes how to use the iLoader utility Intended Audience The following Altibase users will find this manual useful database administrators performance managers database users application developers technical support workers It is recommended that those reading this manual possess the following background knowledge basic knowledge in the use of computer
15. the records will be uploaded The ioparallel option will realize improved performance when disk I O performance is good To execute direct path loading in logging mode with the maximum array size iloader in tl form d ti dat direct log To execute direct path loading in nologging mode with the maximum array size iloader in tl form d tl dat direct nolog To execute direct path loading in logging mode with a specified array size iloader in ti form d ti dat array 1000 direct llel Option To use two 2 parallel threads to perform direct path loading in nologging mode with the maximum array size iloader in f tl form d ti dat direct nolog ioparallel 2 21 Using iLoader Using iLoader in Batch Mode Using iLoader in Batch Mode iLoader can run in interactive mode or in batch mode When running iLoader in batch mode com mand line options are used Creating FORM Files FORM files provide iLoader with information on the attributes of target tables when data are uploaded or downloaded This information covers all aspects of table schema except constraints In order to use iLoader to load data FORM files containing information about the target tables s must first be created The syntax for creating FORM files is shown below iloader formout S servername U user_id P password T table name f form_file In the following example table T1 and a corresponding FORM file are created iSQL create ta
16. variable ISQL_CONNECTION to UNIX or IPC The details may vary depending on the system sh ISQL CONNECTION IPC export ISQL CONNECTION csh setenv ISQL CONNECTION IPC When iLoader is used to upload data it will attempt to commit a transaction for every record If the commit option is used to increase the commit interval upload speed will be affected to some extent Upload speed increases as the commit interval is increased The following shows the changes in speed depending on the commit interval when 100 000 records are uploaded to a disk table shell gt time iloader s 127 0 0 1 u sys p manager in f test fmt d test dat mode replace silent 100000 record load UPLOAD 1519620035 Load Count 100000 real 25m29 391s user 0m7 310s Sys 0m3 930s shell time iloader s 127 0 0 1 u sys p manager in f test fmt d test dat mode replace silent commit 5000 100000 record load UPLOAD 47475459 Load Count 100000 real 0m54 725s user 0m3 010s Sys 0m2 900s shell time iloader s 127 0 0 1 u sys p manager in f test fmt d test dat mode replace silent commit 10000 100000 record load UPLOAD 45757975 Load Count 100000 51 Troubleshooting and FAQs Troubleshooting real 0m52 652s user 0m3 240s Sys 0m2 990s iLoader Users Manual 52 FAQs FAQs Q When I use iLoader with the following FORM file a parsing error occurs What is the reason table TB SPEC CODE char 6 TIME integer SEQ in
17. 16KSC5601 Total 3 records downloaded MANAGER DOWNLOAD 16 2420 msec iLoader in f MANAGER fmt d MANAGER dat DATA NLS USE KO16KSC5601 UPLOAD 32 5190 msec Load Count 3 MANAGER Remote server 192 168 1 13 iSQL SELECT FROM manager MGR NO MNAME ADRESS 7 HJMIN 44 25 Youido dong Youngdungpo gu Seoul Korea 8 JDLEE 3101 N Wabash Ave Brooklyn NY 12 MYLEE 130 Gongpyeongno Jung gu Daegu Korea 7 HJMIN 44 25 Youido dong Youngdungpo gu Seoul Korea 8 JDLEE 3101 N Wabash Ave Brooklyn NY 12 MYLEE 130 Gongpyeongno Jung gu Daegu Korea 6 rows selected The data have been appended to the table MANAGER 31 Using iLoader Online Help and Other Options Online Help and Other Options Help Help is available for the commands provided with iLoader The HELP command displays all help information For help on a specific command enter the HELP command followed by the information for which help is needed Example iLoader help Usage in out formout structout help d datafile or datafiles f formatfile T table name F firstrow L lastrow t field term r row term mode mode typel commit commit unit bad badfile l log logfile l e enclosing array array size replication true false split number readsize size errors count lob lob option string atomic parallel count direct ioparallel count rule csv iLoader help help Ex help in o
18. 399 Load Count 3 iSQL gt SELECT FROM test2 TEST2 I1 TEST2 12 1 2 3 3 rows selected SKIP YYYYMMDD HHMISS iSQL gt CREATE TABLE test il INTEGER i2 TIMESTAMP Create success iSQL gt CREATE TABLE test2 il INTEGER i2 TIMESTAMP Create success iSQL INSERT INTO test VALUES 1 BYTE 40D930BF000A7566 1 row inserted iSQL INSERT INTO test VALUES 2 BYTE 40D930BF000A7566 1 row inserted iSQL INSERT INTO test VALUES 3 BYTE 40D930BF000A7566 47 FORM Files About FORM Files 1 row inserted Shell iloader formout s 127 0 0 1 u sys p manager T TEST f test fmt Shell iloader out s 127 0 0 1 u sys p manager f test fmt d test dat DATE FORMAT YYYY MM DD HH MI SS Total 3 records downloaded DOWNLOAD 1082 shell cat test dat 1 40D930BF000A7566 2 40D930BF000A7566 3 40D930BF000A7566 Shell iloader formout s 127 0 0 1 u sys p manager T TEST2 f t2 skip val fmt shell vi t2 skip val fmt table TEST2 Il integer I2 timestamp SKIP 20040623 Revised part DATEFORM YYYY MM DD HH MI SS DATA NLS USE KO16KSC5601 shell iloader in s 127 0 0 1 u sys p manager f t2 skip val fmt d test dat DATE FORMAT YYYY MM DD HH MI SS UPLOAD 413 Load Count 3 iSQL gt SELECT FROM test2 TEST2 I1 TEST2 12 1 40D8497000000000 2 40D8497000000000 3 40D8497000000000 3 rows selected iLoader Users Manual 48 4 Troubleshooting and
19. 5 Youido dong Youngdungpo gu Seoul Korea 1 row inserted iSQL gt INSERT INTO manager VALUES 8 JDLEE 3101 N Wabash Ave Brooklyn NY 1 row inserted iSQL gt INSERT INTO manager VALUES 12 MVLEE 130 Gongpyeongno Jung gu Daegu Korea 1 row inserted iSQL SELECT FROM manager MGR NO MNAME ADDRESS 7 HJMIN 44 25 Youido dong Youngdungpo gu Seoul Korea 8 JDLEE 3101 N Wabash Ave Brooklyn NY 12 MYLEE 130 Gongpyeongno Jung gu Daegu Korea 3 rows selected The local server accesses the remote server 192 168 1 11 gt 192 168 1 13 Shell iloader s 192 168 1 13 u sys p manager port 40524 Altibase Data Load Download utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISQL CONNECTION TCP iLoader gt gt iLoader is connected to the server and in this mode commands can be entered to interactively perform a desired task or Shell iloader port 40524 Altibase Data Load Download utility iLoader Users Manual 30 Using iLoader in Interactive Mode Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved Write Server Name enter 127 0 0 1 192 168 1 13 Write UserID sys Write Password manager gt The password is not displayed on the screen ISQL CONNECTION TCP iLoader gt formout T MANAGER f MANAGER fmt iLoader out f MANAGER fmt d MANAGER dat DATA NLS USE KO
20. ALTIBASE Tools iLoader User s Manual release 5 3 3 A ALTIBASE PERFORMANCE SOLUTIONS ALTIBASE Toos iLoader User s Manual Release 5 3 3 Copyright 2001 2009 Altibase Corporation All rights reserved This manual contains proprietary information of Altibase Corporation it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright patent and other intellectual property law Reverse engineering of the soft ware is prohibited All trademarks registered or otherwise are the property of their respective owners Altibase Corporation 10F Daerung PostTower lI 182 13 Guro dong Guro gu Seoul 152 847 Korea Telephone 82 2 2082 1000 Fax 82 2 2082 1099 E mail support altibase com www http www altibase com Content AEE LAE A E O U ERTI tese E EE AE I E Eee atia b apa seen OE tese eai ince upra epe eee ovs Uto rasis pas ER eR eeu i About This Mantal tueras i eee t eee eee esee eee Rete even Peces etse RR Rn ii Intended Audience RR REIR HARE EHE Rote eee ve ate ne URS ii Software Environment ere a eee le eee ee et etel tee hi eue e bee etus ii Organizati h seine n ii DOCUMENTATION CONVENTIONS eet ne e EE e es ene poe rs ii Related Doc rmernits 5m eee RR UR RCM RETOUR etre RICH e doen v Online Man a lennie EEA E N ENEA EEE v Altibase Welcomes Your Opinions ev 1 iLoader Overview 1 Introducing iLoader 2 What is
21. All Rights Reserved ISQL CONNECTION TCP iLoader gt gt When the iLoader prompt appears enter a command to accomplish a desired task in interactive mode How to Enter Connection Information in iLoader shell iloader Altibase Data Load Download utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved Write Server Name enter 127 0 0 1 Write UserID sys Write Password manager The password is not output to the screen ISQL CONNECTION TCP iLoader gt gt When the iLoader prompt appears enter a command to accomplish a desired task in interactive mode Exiting Interactive Mode iLoader gt exit iLoader Users Manual 28 Using iLoader in Interactive Mode Creating FORM Files iLoader formout T employee f employee fmt iLoader exit shell 1s employee fmt Downloading Data iLoader out f employee fmt d employee dat DATE FORMAT YYYY MM DD HH MI SS Total 20 records downloaded DOWNLOAD 56936 iLoader exit shell 1s customer fmt employee dat employee fmt Uploading Data iLoader in f employee fmt d employee dat mode replace DATE FORMAT YYYY MM DD HH MI SS UPLOAD 13880 Load Count 20 Execution Result Code ALTIBASE displays one of the following execution result codes after downloading or uploading data using iLoader 0 Success 1 General Error 2 One or more upload errors have occurred 0 o
22. B batch mode 22 C Condition Clause Creation 40 Creating form file 22 29 D Data Download 23 29 Data Upload 23 29 date format 37 DATE FORMAT environment variable 39 Direct Path 20 Direct Path Restriction 20 Direct Path Simple Rules 21 E Executing iLoader 28 Execution Result Code 25 29 H Help 32 I iLoader overview 2 Interactive Mode interactive mode 28 M Modifying the form files 36 P Performance Option 17 R Remote Access using the Port Number 25 S sequence 36 specifying date format by column 39 T TIMESTAMP column 42 57
23. I SS SSSSSS 2004 11 24 09 55 30 181133 2004 11 24 00 00 00 000000 2 rows selected iSQL SELECT TO CHAR I3 YYYY MM DD HH MI SS SSSSSS FROM t1 TO CHAR I3 YYYY MM DD HH MI SS SSSSSS 2004 11 25 09 55 30 181133 2004 11 25 09 55 30 000000 2 rows selected iSQL SELECT TO CHAR I4 YYYY MM DD HH MI SS SSSSSS FROM t1 TO CHAR I4 YYYY MM DD HH MI SS SSSSSS 2004 11 26 09 55 30 181133 2004 11 26 09 55 30 181133 2 rows selected Note The date format specifications take precedence in the following order from highest precedence to lowest the specification following the DATE column in the FORM file the ILO_DATEFORM environment variable DATEFORM YYYY MM DD HH MI SS in the FORM file Executing Functions Data being uploaded can be placed in a particular column using a function However this is not pos sible for the data types DATE TIMESTAMP or GEOMETRY When modifying a FORM file the function name should be specified in double quotes after the other options Furthermore a question mark should be used to indicate the location at which the column is to be bound shell vi t2 fmt table t2 Il integer trim Revised part I2 varchar 10 trim Revised part I3 varchar 10 concat trim value Revised part Writing Condition Clauses FORM files can be modified so that condition clauses are used when downloading data from tables Hints can also be added to realize i
24. LE ti il INTEGER i2 DATE i3 DATE i4 DATE Create success iSQL INSERT INTO t1 VALUES 1 TO DATE 2004 11 24 09 55 30 181133 YYYY MM DD HH MI SS SSSSSS TO DATE 2004 11 25 09 55 30 181133 YYYY MM DD HH MI SS SSSSSS TO DATE 2004 11 26 09 55 30 181133 YYYY MM DD HH MI SS SSSSSS 1 row inserted iSQL SELECT FROM t1 TII TZ4L2 T1 13 T1 I4 1 2004 11 24 09 55 30 2004 11 25 09 55 30 2004 11 26 09 55 30 1 row selected shell iloader formout s 127 0 0 1 u sys p manager T t1 f tl fmt shell gt vi t1 fmt table t1 Il integer I2 date DATEFORM YYYY MM DD lt Revised part I3 date I4 date DATEFORM YYYY MM DD HH MI SS SSSSSS lt Revised part DATEFORM YYYY MM DD HH MI SS DATA NLS USE KO16KSC5601 shell iloader out s 127 0 0 1 u sys p manager f tl fmt d ti dat DATE FORMAT YYYY MM DD HH MI SS Total 1 records downloaded DOWNLOAD 25893 shell gt vi ti dat 1 2004 11 24 2004 11 25 09 55 30 2004 11 26 09 55 30 181133 shell gt iloader in s 127 0 0 1 u sys p manager f ti fmt d ti dat DATE FORMAT YYYY MM DD HH MI SS UPLOAD 3556 39 FORM Files About FORM Files Load Count 1 iSQL SELECT FROM t1 T1 I1 T1 12 T1 13 T1 14 1 2004 11 24 09 55 30 2004 11 25 09 55 30 2004 11 26 09 55 30 1 2004 11 24 00 00 00 2004 11 25 09 55 30 2004 11 26 09 55 30 2 rows selected iSQL SELECT TO CHAR I2 YYYY MM DD HH MI SS SSSSSS FROM t1 TO CHAR I2 YYYY MM DD HH M
25. LTIBASE_NLS_USE to the database character set and then saves the data If the database character set and the character set specified using ALTIBASE NLS USE are different the data are converted If the character set specified in ALTIBASE NLS USE is different from the character set in which the data were actually saved the data will not be uploaded properly 23 Using iLoader Using iLoader in Batch Mode When uploading data the atomic option can be used to perform Atomic Array Insert to realize improved performance The atomic option must be used with the array option and it is addition ally recommended that the commit option also be used When uploading data ALTIBASE displays the table name every 5 000 records and also displays the total number of records after all records have been uploaded The following syntax is used to upload data iloader in S servername U user id P password f form file d backup files mode mode type F firstrow commit commit unit L lastrow array count atomic parallel count In the following example the T1 fmt file and the T1 dat file are used to upload data shell iloader in s 127 0 0 1 u sys p manager f Tl fmt d Tl dat F 1 L2 Altibase Data Load Download utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISQL CONNECTION TCP DATA NLS USE KO16KSC5601 NCHAR UTF16 YES UPLOAD 24 4040 msec Load Count 2 T1 shel
26. NLS USE KO16KSC5601 shell iloader in s 127 0 0 1 u sys p manager f t2 skip default fmt d test dat DATE FORMAT YYYY MM DD HH MI SS UPLOAD 944 Load Count 3 iSQL gt SELECT FROM test2 TEST2 I1 TEST2 12 T 41CA25880002D405 2 41CA25880002D871 3 41CA25880002D9D1 3 rows selected SKIP NULL iSQL CREATE TABLE test il INTEGER i2 TIMESTAMP Create success iSQL gt CREATE TABLE test2 il INTEGER i2 TIMESTAMP Create success iSQL gt INSERT INTO test VALUES 1 BYTE 40D930BF000A7566 iLoader Users Manual 46 About FORM Files 1 row inserted iSQL INSERT INTO test VALUES 2 BYTE 40D930BF000A7566 1 row inserted iSQL INSERT INTO test VALUES 3 BYTE 40D930BF000A7566 1 row inserted Shell iloader formout s 127 0 0 1 u sys p manager T TEST f test fmt Shell iloader out s 127 0 0 1 u sys p manager f test fmt d test dat DATE FORMAT YYYY MM DD HH MI SS Total 3 records downloaded DOWNLOAD 1170 shell cat test dat 1 40D930BF000A7566 2 40D930BF000A7566 3 40D930BF000A7566 shell gt iloader formout s 127 0 0 1 u sys p manager T TEST2 f t2_skip_null fmt shell gt vi t2 skip null fmt table TEST2 Il integer I2 timestamp SKIP NULL lt Revised part l DATEFORM YYYY MM DD HH MI SS DATA NLS USE KO16KSC5601 shell iloader in s 127 0 0 1 u sys p manager f t2 skip null fmt d test dat DATE FORMAT YYYY MM DD HH MI SS UPLOAD
27. SSSSS dateFORM YYMMDDHHMISSSSSSSS dateFORM YYdateFORM YYYY dateFORM YYMMDD 37 FORM Files About FORM Files dateFORM YYYYMMDD dateFORM HHMISSSSSSS dateFORM HH MI SS SSSSS dateFORM HH MI SS Single quotation marks and can be used to define a new date format that differs from the date formats given above For example a date format that uses a space as the delimiter between the year month and day ele ments can be defined thus DATEFORM YY MM DD or DATEFORM YYYY MM DD iSQL gt CREATE TABLE t1 il INTEGER i2 DATE i3 INTEGER Create success iSQL INSERT INTO ti VALUES 1 to date 2002 01 23 10 11 12 222222 YYYY MM DD HH MI SS SSSSSS 1 1 row inserted iSQL INSERT INTO ti VALUES 2 to date 2002 01 23 13 14 15 333333 YYYY MM DD HH MI SS SSSSSS 2 1 row inserted iSQL INSERT INTO ti VALUES 3 to date 2002 01 23 16 17 18 444444 YYYY MM DD HH MI SS SSSSSS 3 1 row inserted iSQL SELECT TO CHAR i2 YYYY MM DD HH MI SS SSSSSS TESTDATE FROM t1 TESTDATE 2002 01 23 10 11 12 222222 2002 01 23 13 14 15 333333 2002 01 23 16 17 18 444444 3 rows selected The created FORM file iLoader formout T t1 f t1 fmt is modified as follows shell vi ti fmt table ti Il integer I2 date I3 integer DATEFORM YYYY MM DD HH MI SS SSSSSS Revised part DATA NLS USE KO16KSC5601 After the FORM file is used to back up the seqTable table to a file iLoader
28. are referred to in sequence and if still not set the basic character set US7ASCII is used The options S server name U user name P password and PORT port no must be directly input at a command prompt when connecting to the remote server 25 Using iLoader Using iLoader in Batch Mode Remote Data Download Remote server IP 192 168 1 71 PORT_NO 20594 Communication protocol TCP IP ISQL_CONNECTION TCP IP shell gt iloader formout s 192 168 1 71 u sys p manager port 20594 T department f dept fmt Altibase Data Load Download utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISQL CONNECTION TCP shell 1s dept fmt shell iloader out s 192 168 1 71 u sys p manager port 20594 f dept fmt d dept dat Altibase Data Load Download utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISQL CONNECTION TCP DATA NLS USE K016KSC5601 Total 5 record downloaded department DOWNLOAD 5 9540 msec Shell 1s dept dat dept fmt Remote Data Upload Shell iloader in s 192 168 1 71 u sys p manager port 20594 f dept fmt d dept dat mode replace Altibase Data Load Download utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISQL CONNECTION TCP DATA NLS USE KO16KSC5601 UPLOAD 70 6630 msec Load Count 5 department
29. ble T1 name varchar 30 age integer gender char 1 etc nvarchar 30 tablespace USER_DATA Create success iSQL insert into T1 values Ju young 20 M N Group A 1 row inserted iSQL gt insert into TI values Chang ho 30 M N Group C 1 row inserted iSQL insert into T1 values Ji hyun 24 F N Group D 1 row inserted iSQL select from T1 NAME AGE GENDER ETC Ju young 20 M Group A Chang ho 30 M Group C Ji hyun 24 F Group D 3 rows selected iSQL exit shell iloader formout s 127 0 0 1 u sys p manager T T1 f T1 fmt Altibase Data Load Download utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISQL CONNECTION TCP shell cat T1 fmt table T1 NAME varchar 30 AGE integer GENDER char 1 ETC nvarchar 100 DATA NLS USE KO16KSC5601 NCHAR UTF16 YES DATA NLS USE can be used to show how downloaded data will be encoded Normally when a iLoader Users Manual 22 Using iLoader in Batch Mode FORM file is created the client character set is used If the table to be downloaded contains a national character type column NCHAR_UTF16 will be used The encoding method that is used when data are saved is UTF 16BE Big Endian when NCHAR_UTF16 is set to YES If set to NO data will be encoded as CHAR type and could be lost The default value is YES Aaralan Ann Nat Downloading Data
30. blel f tablel fmt is modified as follows shell vi tablel fmt table tablel T1 integer T2 integer T3 date DATEFORM YYYY MM DD HH MI SS DATA NLS USE KO16KSC5601 DOWNLOAD CONDITION where t1 2 Revised Part After using the FORM file to back up table to a file iLoader gt out f table1 fmt d table1 dat when the file is examined the results of application of the condition clause can be seen 41 FORM Files About FORM Files shell gt cat tablel dat 3 3 2002 04 03 00 00 00 4 4 2002 09 04 00 00 00 5 5 2002 10 05 00 00 00 Handling TIMESTAMP Columns After downloading a data file from a current Altibase database using iLoader a value pertaining to a TIMESTAMP column in a data file can be uploaded by using the following options to modify the FORM file ADD DEFAULT ADD NULL ADD YYYYMMDD HHMISS SKIP DEFAULT SKIP NULL SKIP YYYYMMDD HHMISS Below is an example of the use of the above option to download the data in a database table to a file and upload the data to the table again 1 A FORM file is created based on the table to be downloaded from the current Altibase data base 2 Thecreated FORM file is used to back up the table data downloaded from the current data base to a file 3 TheFORM file to be uploaded is modified according to the following guidelines as the circum stances require If the data file has no value in a TIMESTAMP column If there are no TIMESTAMP data in the data
31. columns are saved in data files in numeric order according to row The row numbers start from 1 and have 9 digits If the number of digits in the row number is less than 9 the leftmost positions of the row number are filled with 0 s zeroes For example if columns C1 and C3 of table t1 which has 3 columns are LOB type columns and d d dat lob use separate files yes is specified the following LOB files are created t1 C1 000000001 lob t1 C1 000000002 lob t1 C3 000000002 lob t1 C3 000000002 lob When this option is set the address of the LOB file is written in the corresponding data cell in the LOB column in the data file However if the LOB data are NULL the address of the LOB file isn t writ ten and no LOB file is created lob indicator char sequence This option specifies a block delimiter that denotes a pointer for indicating the offset and size of a LOB file It can be used regardless of whether the data copy direction in iLoader is in or out The default value is 9696 Up to 10 characters can be used However 96t n and 96r are each regarded as one character 96t indicates a tab n indicates a newline character and 96r indicates a carriage return This option cannot have the same value specified for the t r or e options It is recommended that characters that are interpreted on the command line such as 71 N amp not be used as delimiters It is assumed that use lob file yes if it is not specified Wh
32. cre ate FORM files They can also be directly edited to use the following three additional options SEQUENCE sequence name column_name NEXTVAL CURRVAL DATEFORM date format DOWNLOAD CONDITION WHERE condition Writing SEQUENCE Statements A FORM file can be modified to implement a SEQUENCE To modify the FORM file enter the word SEQUENCE followed by the sequence name the name of the column s to be used and pseudocolumns such as NEXTVAL or CURRVAL etc in order at the very beginning of the form file SEQUENCE sequence name column name NEXTVAL CURRVAL The default value for pseudocolumns is NEXTVAL The maximum number of columns that can be used is 8 The default format of the FORM file is as follows and if the table has a column for which the data type is date the date format will be the default table sample A numeric 10 B numeric 10 4 C char 10 D varchar 10 E date DATEFORM YYYY MM DD HH MI SS Example iSQL CREATE TABLE seqTable num INTEGER name VARCHAR 30 Create success iSQL gt INSERT INTO seqTable VALUES 1 JANE 1 row inserted iSQL gt INSERT INTO seqTable VALUES 2 SARA 1 row inserted iSQL CREATE SEQUENCE seql START WITH 30 INCREMENT BY 2 MINVALUE 0 NOMAXVALUE Create success iLoader gt formout T seqTable f seqTable fmt shell gt vi seqTable fmt SEQUENCE seqi NUM table seqTable iLoader Users Manual 36 About FORM Files
33. e parallel option set the perfor mance of repeated bind and fetch operations may be reduced Therefore when downloading data the parallel and array options should be used together If a LOB column is present count should be set to 1 ioparallel count When uploading data using the direct path method this option specifies the number of threads flushed from the direct path buf fer to the disk table This option should be used together with the direct option The maximum possible value is CPU 2 twice the number of CPUs in the system readsize integer When iLoader is executed in in mode this option specifies the amount of data that are read from a file at one time The size must be greater than 0 The default is 1048576 bytes The atomic option is used to execute Atomic Array Insert which is useful only when uploading data Atomic Array Insert can realize faster performance than Array Insert because Atomic Array Insert handles a number of Insert statements up to the size of the array as a single transaction However when uploading records if even one of the records is not successfully uploaded none of the records in the array will be uploaded For example given the options atomic array 1000 if even one record is not successfully uploaded none of the 1000 records will be successfully uploaded iLoader Users Manual 18 Performance Options The Difference between Atomic
34. e downloaded to a LOB file regardless of whether LOB offset and size informa tion is provided between field delimiters or between a pair of block delimiters if using block delim iters This is illustrated in the following figure 15 Using iLoader General Options 0 50000000 file 000000001 lob size 1G 369650000000 70000000 36961 20000000 60000000 size 50M size 50M file 000000002 lob size 0 8G size 20M size 60M When null LOB data are downloaded to a data file there will be no data between the field separators or between the pair of block delimiters if appropriate at the position of the corresponding LOB data within the data file LOB data of size 0 are also stored in the same way Altibase internally han dles LOB data of size 0 as NULL If the use separate files option is specified the data files are organized as follows t1 11 000000001 10b t1 12 000000001 10b t1 13 000000001 10b t1 11 000000002 10b t1 12 000000002 10b t1 13 000000002 10b t1 11 000000003 10b t1 12 000000003 10b t1 13 000000003 10b nee In the above example all of the columns contain LOB data If the LOB data to be stored in a LOB file are NULL no characters are stored at the corresponding LOB data position in the data file as can be seen in the fourth row above iLoader Users Manual 16 Performance Options Performance Options The following options can realize considerable performance gains when runnin
35. e taken 1 Backup data before changing schema using iLoader 2 stopreplication 35 Troubleshooting and FAQs FAQs drop table from replication drop table create table Use iLoader to upload the backup file saved before changing the schema add table to replication Start replication DON ur erts The FORM file and data file generated before the schema was changed do not contain the information about the added columns If the added columns have default values the default values will be inserted but if they do not have any default values NULL will be inserted Q Is there any way can import a dmp file exported from Oracle to Altibase as it is A Oracle exports are in the binary format Exported files cannot be imported into Altibase You must use SQL Loader to access the data Because the binary format of Oracle cannot be read by other databases you must use SQL Loader to import the data Q Is it possible to upload or download gis data with iLoader If so what format are they saved as in the data file A Gis data are saved as the Geometry type which is supported by iLoader Geometry data stored in data files by iLoader have the WKB Well Known Bytes format which enables them to be saved in the byte order used by the server WKB data is encoded in CSV format or is stored in a binary format that consists of distinguishable row and column delimiters iLoader Users Manual 56 Index A Accessing Remote 29 Atomic 18
36. en the data copy direction in iLoader is in and use lob file yes if a LOB column in a data file does not start with the character sequence specified by lob indicator the data row containing that LOB column is treated as an erroneous row To dump LOB data to external files such as t1 000000001 lob t1 000000002 lob etc which are 1 5 GB in size iLoader Users Manual 14 General Options iloader out f tl fmt d ti dat lob lob file size 1 5G To dump LOB data to a single file named t1 lob iloader out f ti fmt d ti dat lob use lob file yes iloader out f tl fmt d ti dat lob use lob file ves lob lob file size 1G iloader out f ti fmt d ti dat lob lob indicator LOBS Data File Format for LOB Data A column containing LOB data can be located using an offset size combination which points to the location of LOB data in an external file If the offset size combination which points to LOB data is not expressly specified then iLoader looks in the data file for the pointer to the position of the LOB data in the LOB file after a 9696 delimiter Example cat ti fmt table ti Il integer I2 blob I3 char 10 DATA NLS USE KO16KSC5601 iloader out f ti fmt d ti dat lob lob file size 3G ls ti 000000001 lob ti 000000002 10b ti dat ti fmt cat tl dat 1 0 2000000000 Terminator3 2 2000000000 1800000000 SixSense The actual LOB data ar
37. fits compared to REPLACE but cannot be performed on tables that are associated with triggers bad badfile If one or more errors occur during an upload operation this option saves rows that were not uploaded in badfile Not saved if not speci fied If stdout or sterr must be in lowercase is specified for this option no file is created Instead the rows are output to stdout standard out or stderr standard error iLoader Users Manual 10 General Options Factor Description log logfile Maintains a record of iLoader execution while iLoader is running The start time end time number of target rows number of pro cessed rows number of erroneous rows and error details are recorded here It will not be saved unless specified If stdout or sterr must be in lowercase is specified for this option no file is created Instead the log information is output to stdout stan dard out or stderr standard error split n Specifies the number of records to copy to each file only meaningful when used with the out option After the command is executed a number of backup files equal to n having the names datefile datO datafile dat1 etc will have been created errors count This specifies the maximum number of allowable errors when iLoader is executed with the in option If the number of errors exceeds the number specified here execution terminates The default is 50 If this value is
38. g iLoader Factor Description array array_size To increase the speed of a data uploading in operation data read from a file are first organized into an array before being sent to the server Performance is increased because the number of times that com munication with the server must be established is decreased However if this value is set excessively high it can have the oppo site effect This value must be set smaller than the size of the com munication buffer otherwise an error will occur If a LOB column exists array is ignored commit commit unit When uploading data this option determines how many records are committed at one time after being inserted By default 1000 records are committed after being inserted If commit unit is set to 0 the application runs in NON AUTOCOM MIT mode in which the commit operation takes place only after all data have been inserted If commit unit is set to 1 the application runs in AUTOCOMMIT mode in which a commit operation takes place for every record at the time that it is inserted When this option is used together with the array option the com mit operation takes place after a number of records equal to array size f commit unit have been inserted atomic This option is set to use the Atomic Array Insert option Atomic Array Insert realizes better performance than Array Insert because Atomic Array Insert handles a number of Insert statements up to the size
39. gle table Data can be uploaded to multiple tables at once Data can be appended to tables that already contain data or can replace the data therein Data input and management can be made more systematic How to Install iLoader iLoader is automatically installed when the Altibase package is installed The executable file is located in SALTIBASE HOME bin iLoader Users Manual 2 Introducing iLoader In order to connect to the server iLoader requires the following information ALTIBASE_HOME A path to a server or client installation server_name The name or IP address of a computer on which Altibase Server is running port no The port number to be used when connecting via TCP or IPC user id a User ID registered in the database password The password corresponding to the User ID NLS USE The character set with which to display retrieved data to the user ALTIBASE HOME can only be set using an environment variable while the other settings may be made using command line options For detailed information please refer to Batch Mode The ALTIBASE HOME environment variable must be set in order to use iLoader This is typically set automatically when the server is installed but in the case of the client must be set manually by the user We strongly suggest that you verify that this setting has been made properly as the application may not run correctly if this setting is not made port no and NLS USE can be set using the c
40. gt out f t1 fmt d t1 dat examining the file reveals the following shell cat ti dat 1 2002 01 23 10 11 12 222222 1 2 2002 01 23 13 14 15 333333 2 3 2002 01 23 16 17 18 444444 3 To upload the backup file to the database the following command is used iLoader in f ti fmt d ti dat mode replace DATE FORMAT YYYY MM DD HH MI SS SSSSSS UPLOAD 4026 Load Count 3 Selecting the records in table t1 using the TO CHAR function results in the following iLoader Users Manual 38 About FORM Files iSQL SELECT TO CHAR i2 YYYY MM DD HH MI SS SSSSSS TESTDATE FROM t1 TESTDATE 2002 01 23 10 11 12 222222 2002 01 23 13 14 15 333333 2002 01 23 16 17 18 444444 3 rows selected However when uploading data it is important to ensure that the previously downloaded data have the same date format specified in the FORM file DATE FORMAT Environment Variable In the FORM file structure an environment variable that plays a role analogous to that of DATEFORM can be specified as follows For example when using the Bourne Korn or Bash Shells export ILO DATEFORM YYYY MM DD How to specify the DATE FORMAT for Respective Columns When dealing with DATE data type columns that have different formats if the FORM file is modified by entering DATEFORM and a desired date format in double quotes after the DATE type decla ration not case sensitive data can be uploaded or downloaded in this format iSQL CREATE TAB
41. gual records fail to be uploaded If the character set specified using the ALTIBASE_NLS_USE environment variable is different from the character set in which the records were actually saved data may not be uploaded normally In this case the value of the ALTIBASE_NLS_USE environment variable should be changed to match the character set actually used by the records FORM File Parsing Errors Most FORM file parsing errors are caused by syntax errors that result from editing FORM files manu ally Aside from such grammatical errors similar problems can occur if iLoader reserved words are used as column names or the like The following is a list of iLoader reserved words ADD APPEND BIGINT BINARY BIT BLOB BOOLEAN BYTES CHAR CLOB CONDITION CURRVAL DATA NLS USE DATE DATEFORM DECIMAL iLoader Users Manual 50 Troubleshooting DEFAULT DOUBLE DOWNLOAD EXIT FLOAT FORMOUT GEOMETRY HELP HINT IN INTEGER LOG NCHAR NCHAR _ UTF16 NEXTVAL NIBBLE NOEXP NOLOG NULL NUMBER NUMERIC NVARCHAR OUT QUEUE QUIT REAL REPLACE SEQUENCE SKIP SMALLINT STRUCTOUT TABLE TIMESTAMP TRUE TRUNCATE VARBIT VARCHAR YES Performance Issues iLoader generally establishes a connection to Altibase using a TCP socket If the process running iLoader and the Altibase server are running on the same host it is helpful to use a Unix domain socket or IPC to connect to Altibase The connection method can be changed by setting the environ ment
42. iLoader 42 Uses wd Functionality wad Advantages 2 How toInstallalGader EE eti bn dA m cies 2 il der Settings M S UD M DU E I S Y 3 Environment Variables ie AREE HR DAE TRUTH EE DRE Hout 4 2 USING EIE CRIME M M 7 Command Eine ODtlOr iii ei e des 8 General Options asena At rw 9 Limitations Of OPON L A 11 IB 1 1 0 f TO 12 Performance Optio S iss assi issue Bee v ke 17 ATOMIC Arrav n arra A A OI 18 Direct Path Option eee et tte e e E tai eec EE boe eoe bee E toe eee be e Ree 20 Using iboader in Batch Mode x ee ERR i 22 Creating FORM Files etre e e epe e eee du 22 Downloadina Data ss eian ta tte 23 Uploading Data ere 23 Execution Result Code sete IM tee EE 25 Using iLoader for Remote Database ACCOSS ssssscssssssssssssssssscssussessneesssnecssnsecesssecsesuscessueceesusecssnscessuseeesnscessusecesnseessneceessneesssess 25 Using iLoader in Interactive Mode entere tenttttebdereeretee tisse tende taie erts tk etis fast tele ape et eu epe entitled bbb ek eta eld 28 Starting ibode nan A RP 28 Creating FORM Files iii ep 29 Downloading Data i irene e EEE EEEE a aa 29 Uploading Data ATEREA AEA E TEA Ba eee ts 29 Exec tioriResult Code bene M IM 29 Remote Access in Interactive Mode sssssscssscssssssscsussessnescsssecssnssscsssecessusecssneesesusecssnscessusceesussecssneesssnseeesuscessusecesnsecesnuceesaneceenaes 29 Online Help and Other OptlOns ce t
43. ics Statement elements in italics indicate vari SELECT FROM table_name ables and special values specified by the CONNECT userlD password user Lower Case Indicate program elements set by the user SELECT ename FROM Characters such as table names column names file employee names etc iLoader Users Manual iv About This Manual Rules Meaning Example Upper Case Keywords and all elements provided by the DESC SYSTEM SYS INDICES Characters system appear in upper case Related Documents For more detailed information please refer to the following document Altibase Installation Manual Altibase Administrator s Manual Altibase Replication User s Manual Altibase Precompiler User s Manual Altibase ODBC User s Manual Altibase Application Program Interface User s Manual Altibase iSQL User s Manual Altibase Utilities User s Manual Altibase Error Message Reference Online Manuals Online versions of our manuals PDF and HTML are available from the Altibase Technical Center http atc altibase com Altibase Welcomes Your Opinions Please feel free to send us your comments and suggestions regarding this manual Your comments and suggestions are important to us and may be used to improve future versions of the manual Please send your feedback to support altibase com making sure to include the following informa tion The name and version of the manual you are using Your comments a
44. ion string after lob option use lob file use separate files lob file size lob indicator use lob file yes no Determines whether LOB data expressions refer to external files LOB files Either value can be used when iLoader is copying data in either direction in or out For information on LOB file naming conventions please refer to the description of the lob file size option here iLoader Users Manual 12 General Options lob file size file size Specifies the maximum size of a LOB file If the use lob file option is set to yes and this option is not specified the size of the external file is only limited by the operating system s file size limit When the file size is limited by the operating system or the platform it is recommended that this option be set to 2 GB or less on platforms on which the long data type is 4 bytes This option is applicable only when the iLoader data copy direction is out iLoader ignores this option when the data copy direction is in When the data copy direction is in the size of the first LOB data file is taken aslob file size If this option is set without the use lob file option being specified it is inferred that use lob file yes If the size of LOB data to be dumped exceeds the size specified in this option a new file is created and the data dump continues The file size is represented in number unit format The number can be represented
45. is not null Modified shell iloader s 127 0 0 1 u sys p manager out f dept fmt d dept dat silent Total 5 records downloaded DOWNLOAD 11614 shell cat dept dat A001 technical 1 Mapo usd C001 marketing Gangnam LES C002 planning Gangnam UE DOO1 engine devt Yeouido 10 FOO1 operations Sinchon 9 Q Is there a command I can use to directly upload a text file in which the delimiter is Mysql has the load data infile command What commands can l use for your MMDBMS iLoader Users Manual 54 FAQs A You can use the iLoader utility which allows you to download and upload data on a table by table basis First use iLoader to create a FORM file corresponding to the table You can also use a text editor instead of iLoader shell gt iloader formout s 127 0 0 1 u sys p manager T test f test FORM This is how to upload a text file delimiter shell gt iloader in s 127 0 0 1 u sys p manager f test FORM d datafile et rp In this way the text file can be directly uploaded Note that the table must already exist Q When the date formats of two columns in the same table are different is there any way l can import them A In iLoader DATEFORM can be set for individual columns as shown in the following example Details of FORM file table table name columni date YYYY MM DD column2 date DATEFORM YYYY MM DD HH MI SS Note that date format options are ap
46. l isql silent iSQL select from t1 select from t1 NAME AGE GENDER ETC Ju young 20 M Group A Chang ho 30 M Group C Ji hyun 24 F Group D Ju young 20 M Group A Chang ho 30 M Group C 5 rows selected The table information in the previously created T1 fmt file is referenced to upload the data in the T1 dat file to table T1 of the current database Since the default value of the mode option is APPEND data are appended to the T1 table The REPLACE option uploads data after first deleting all of the data in table T1 of the current database The 1st and 2nd records are uploaded from the T1 dat file If 100 and 1000 are specified for the first row and the last row respectively data in the file from the 100th record to the 1000th record are uploaded shell gt iloader in s 127 0 0 1 u sys p manager f Tl fmt d Tl dat array 1000 commit 1 atomic The table information in the T1 fmt file is referenced to upload 1000 records at a time from the T1 data file to table T1 of the current database using Atomic Array Insert iLoader Users Manual 24 Using iLoader in Batch Mode Execution Result Code ALTIBASE displays one of the following execution result codes after using iLoader to download or upload data 0 Success 1 General Error 2 One or more upload errors have occured 0 or 1 is displayed to indicate success or failure respectively of a data upload or download opera tion 2 is displa
47. loading data using the direct path option If any of these restrictions are violated data can be uploaded automatically using the Atomic Array Insert atomic option instead of the Direct Path direct option the table can t have an index or a Primary Key the table can t be associated with a trigger the table can t have a LOB column the table must not be required to satisfy referential integrity requirements replication cannot be performed on the table the table must exist in disk tablespace iLoader Users Manual 20 Performance Options When processing data using the Direct Path option please abide by the following If data upload fails while direct path is being executed in nologging mode direct nolog it will be impossible to restore the database normally Therefore it is critical to back up the rele vant table s before using nologging mode The direct path option realizes excellent performance when uploading large amounts of data However when not handling large amounts of data the improvement in performance will not be notable Data can be loaded using the Direct Path option only if the table exists in disk tablespace Therefore when using this method to load data into memory tablespaces the result will be the same as if the atomic option were used As with the atomic option when uploading an array of data if even one of the records fails to be uploaded none of
48. mproved performance Enter the words DOWNLOAD CONDITION without quotes at the very end of a FORM file followed by the condition clause within double quotes At the time of execution the executed query can be checked to verify that hints and condition clauses specified by the user are working properly iLoader Users Manual 40 About FORM Files using the displayquery option Syntax DOWNLOAD CONDITION where conditions clause HINT hint string where conditions clause For specifying conditions Has the same form as the WHERE clause of a SELECT statement hint name For specifying a hint in order to increase the performance of a SELECT statement Has the same form as a HINT used with a SELECT statement Example iSQL CREATE TABLE tablel ti INTEGER t2 INTEGER t3 DATE Create success iSQL INSERT INTO tablel VALUES 1 1 Ol Mav 2002 1 row inserted iSQL INSERT INTO tablel VALUES 2 2 02 Jun 2002 1 row inserted iSQL INSERT INTO tablel VALUES 3 3 03 Apr 2002 1 row inserted iSQL gt INSERT INTO tablel VALUES 4 4 04 Sep 2002 1 row inserted iSQL INSERT INTO tablel VALUES 5 5 05 Oct 2002 1 row inserted iSQL SELECT FROM tablel TABLE1 T1 TABLE1 T2 TABLE1 T3 2002 05 01 00 00 00 2002 06 02 00 00 00 2002 04 03 00 00 00 2002 09 04 00 00 00 5 2002 10 05 00 00 00 rows selected The FORM file created with this command iloader formout T ta
49. nd suggestions regarding the manual Your full name address and phone number In addition to suggestions this address may also be used to report any errors or omissions discov ered in the manual which we will address promptly If you need immediate assistance with technical issues please contact the Altibase Customer Support Center We always appreciate your comments and suggestions v Preface About This Manual iLoader Users Manual vi l iLoader Overview Introducing iLoader Introducing iLoader What is iLoader iLoader is a utility provided by Altibase to download or upload data in Altibase by tables Down loaded data will be saved as a text file and the FORM file containing the structure information of the table is necessary Uses iLoader is mostly used for database migration or when backing up tables However because data base schema and catalog information are not saved care must be taken when using iLoader as a backup utility Functionality FORM file Creation For creating FORM files for storing information about tables to be downloaded or uploaded Downloading Data FORM files are used to save the database table data in files Uploading Data Using FORM files the data saved in the files are uploaded to the relevant database tables Advantages The advantages of iLoader are as follows Data can be uploaded from one or more files Multiple input records can be uploaded to a sin
50. ng data received from the server are saved in the specified character set When uploading sets the character set for data to be transmitted to the server Note when uploading data if the actual data character set is different from the value specified using the ALTIBASE_NLS_USE environment variable the data may not be uploaded normally Please be sure to set the correct value for this variable This can also be set using the NLS_USE option or in altibase properties If NLS_USE is not specified using the NLS_USE option the environment variable ALTIBASE NLS USE or altibase properties in descending order of precedence then US7ASCII is used as the default character set 5 iLoader Overview Introducing iLoader iLoader Users Manual 2 Using iLoader This chapter explains how to use iLoader The various options provided by ALTIBASE can be used with iLoader iLoader can run in batch mode or in interactive mode Command line options are used when run ning iLoader in batch mode 7 Using iLoader Command Line Option Command Line Option The command line options supported by ALTIBASE include general options and options for improv ing performance General Options Performance Options The general options and performance options are all listed below They are explained in the next section iloader in out formout structout help T table name d datafile or datafiles formatfile F firstrow L la
51. of the array as a single transaction Consequently if the upload of even one record fails none of the records in the array are uploaded The array option must be used together with this option Addi tionally tables that contain LOB type columns cannot be handled using this option Furthermore this option is useful only when uploading data 17 Using iLoader Performance Options Factor Description direct logjnolog see Direct Path Option This option is for use with the direct path method when upload ing data to a disk table If omitted log is the default If using nolog mode it is essential to back up the table in ques tion If execution fails in nolog mode recovery to a normal state will be difficult If loading tables for which there are restrictions see Limitaions of Direct Path execution will automatically switch to the atomic option If the array option has not been set the size of the array will auto matically be set to the maximum possible size USHRT MAX 1 or 65535 If the commit option is omitted its value will be set to 1 parallel count This specifies the number of threads that can be handled at the same time A number of threads can be created and executed in parallel up to the specified value the maximum of which is 32 When downloading a number of files equal to the specified value is created and the data are saved therein When downloading with only th
52. orresponding environment variables or the server set tings file altibase properties If these settings are made via all three methods they will take priority as follows in descending order command line options environment variables ALTIBASE PORT NO ALTIBASE NLS USE server settings file altibase properties Therefore when it is desired to connect using options other than those that have been previously set the command line options can be used thus making it unnecessary to change the settings in the server settings file or the environment variables If these options have not been set when iLoader is executed for the first time the user will be 3 iLoader Overview Introducing iLoader prompted to enter the corresponding variables At this time it is essential to enter values that are valid and follow the proper format otherwise iLoader may not run properly However if the NLS_USE option in particular has not been set no command prompt will appear at the time of execution Instead US7ASCII will be used and a connection attempt will be made In such instances if the server is not using US7ASCII the application will not execute properly or some of the users data could become corrupted Thus it is paramount that NLS_USE be set to the value that matches the usage environment In order to ensure stable iSQL operation we strongly recommend that the following environment variables be set ALTIBASE HOME the path to a
53. plied in the following order in descending order of prefer enc Date format stated right after the DATE column of a FORM file environment variable ILO DATEFORM gt date format stated outside of brackets in a FORM file Q I want to use iLoader to upload data into a table that already contains data If I use the mode replace option will it delete the existing data or truncate it A It will delete the existing data Q The manual says if you want to truncate a large table it is recommended that you use the iLoader utility to download and upload data Does that mean I have to download the data from the table truncate it with iLoader and then upload it again with iLoader And is using the mode replace option in iLoader different than truncating A Since it may take a long time to delete large amounts of data it is recommended that data be truncated Data must be downloaded before truncating and uploaded again after truncat ing Q If I need to add columns to a table while a database is online how can go about doing that Also made a backup before changing the schema Will it be all right if add col umns and then upload a backup file that I made before changing the schema A It is possible to change a schema using the ALTER TABLE command but adding columns is not recommended if a table has many records because it takes a long time and uses a large amount of memory In such cases we recommend that the following steps b
54. ploading data When multiple files are specified using the d option they are uploaded in the order that the filenames are entered Moreover when this option is used together with the parallel option during a download operation at least as many files as the number specified in the parallel option are created rule csv This specifies the format of the data file as csv This option cannot be used in conjunction with the t or e options Otherwise an error will be raised If none of the t r e or rule csv options are set csv is used by default f formatfile The full path of formatfile created by a previous call to iLoader F firstrow The line number of the first row to be copied default is 1 This option is meaningful only when uploading data L lastrow The line number of the last row to be copied default is the line num ber of the last row This option is meaningful only when uploading data 9 Using iLoader General Options Factor Description t field_term The delimiter between fields The default is A t signifies a tab char acter n signifies a newline character and r signifies a carriage return The t r and e options must be different from each other e g t A t Caution It is inadvisable to use characters that are interpreted on the com mand line such as 7 N amp as delimiters When using delimiters for example at the iLoader prompt they
55. r 1 is displayed to indicate success or failure respectively of a data upload or download opera tion 2 is displayed to indicate that the overall upload operation succeeded but that one or more errors occurred Remote Access in Interactive Mode When iLoader is executed remotely if none of the command line options formout out in are entered and only the port number is entered the user will be prompted to enter the option values If access is successful an iLoader prompt will appear and a command can be entered to perform a desired task interactively In the following example after the port number of a remote server is used to access the remote server from a local server the iLoader utility is used to download the data from the MANAGER table 29 Using iLoader Using iLoader in Interactive Mode on the remote server to a file and then to upload the data back to the MANAGER table Local server IP 192 168 1 11 PORT_NO 20524 Remote server IP 192 168 1 13 PORT_NO 40524 Communication protocol TCP IP ISQL_CONNECTION TCP IP local server 192 168 1 11 shell gt isql Altibase Client Query utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISQL CONNECTION TCP SERVER 127 0 0 1 PORT NO 20524 iSQL CREATE TABLE manager mgr_no INTEGER mname VARCHAR 20 address VARCHAR 60 Create success iSQL gt INSERT INTO manager VALUES 7 HJMIN 44 2
56. rrent database Example ADD DEFAULT iSQL gt CREATE TABLE test il INTEGER Create success iSQL gt CREATE TABLE test2 il INTEGER i2 TIMESTAMP Create success iSQL INSERT INTO test VALUES 1 1 row inserted iSQL INSERT INTO test VALUES 2 1 row inserted iSQL INSERT INTO test VALUES 3 1 row inserted Shell iloader formout s 127 0 0 1 u sys p manager T TEST f test fmt Shell iloader out s 127 0 0 1 u sys p manager f test fmt d test dat ISQL CONNECTION TCP Total 3 records downloaded DOWNLOAD 15565 shell cat test dat T 2 3 Shell iloader formout s 127 0 0 1 u sys p manager T TEST2 f t2 add default fmt shell vi t2 add default fmt table TEST2 Il integer I2 timestamp ADD DEFAULT lt Revised part DATEFORM YYYY MM DD HH MI SS DATA NLS USE KO16KSC5601 shell iloader in s 127 0 0 1 u sys p manager f t2 add default fmt d test dat DATE FORMAT YYYY MM DD HH MI SS UPLOAD 5205 Load Count 3 iSQL gt SELECT FROM test2 43 FORM Files About FORM Files TEST2 I1 TEST2 12 1 41CA2D400006D26C 2 41CA2D400006D507 3 41CA2D400006D65E 3 rows selected ADD NULL iSQL gt CREATE TABLE test il INTEGER Create success iSQL gt CREATE TABLE test2 il INTEGER i2 TIMESTAMP Create success iSQL INSERT INTO test VALUES 1 1 row inserted iSQL INSERT INTO test VALUES 2 1 row inserted iSQL INSERT INTO
57. s operating systems and operating system utilities experience in using relational databases and an understanding of database concepts computer programming experience experience in database server operating system or network administration Software Environment This manual has been prepared assuming that Altibase 5 3 3 is used as the database server Organization This manual is organized as follows Chapter 1 iLoader Overview Chapter 2 Using iLoader Chapter 3 FORM Files Chapter 4 Troubleshooting and FAQs Documentation Conventions This section describes the conventions used in this manual Understanding these conventions will make it easier to find information in this manual and other manuals in the series There are two sets of conventions iLoader Users Manual ii About This Manual syntax diagrams sample code conventions This manual describes command syntax using diagrams composed of the following elements Elements Meaning The start of a command If a syntactic element starts with an arrow it is not a complete command Reserved word The command continues to the next line If a syntactic ele ment ends with this symbol it is not a complete command p The command continues from the previous line If a syntactic element starts with this symbol it is not a complete com gt mand The end of a statement Indicates a mandatory element
58. s would be displayed as follows Honggildong 027551234 Seoul In this case the third would be recognized as another field delimiter so the row would be handled as though it had 4 columns and no row delimiter In the case below although the field delimiter which is is not a subset of the row delimiter which is the second and third dashes which are part of the data in the second column are errone ously recognized as field delimiters Honggildong 02 755 1234 Seoul Therefore the delimiters must be selected carefully because of the possibility that they will be incor rectly handled In Windows lines end with both a line feed and a carriage return r n So when you write a datafile manually and upload it r n must be specified for the row terminator For example when uploading a data file which is in csv format with n for a row terminator the following options would be specified iloader in T table name gt f format file name d data file name gt r r n rule csv The user can specify how LOB columns are handled by entering lob option string after the lob option To specify multiple keywords see below use the lob option repeatedly on the same line each lob option being followed by its own lob option string enclosed in double quotation marks The keywords for lob option string are as follows The user can determine how a LOB column is processed by specifying lob opt
59. server or client installation ALTIBASE PORT NO the port number to use to connect to the server ALTIBASE NLS USE the character set to use to display retrieved data to the user PATH the path containing the executable file which must equal SALTIBASE_HOME bin This is used to set the directory in which the package is installed In the case of MS Windows this is set automatically when the server is installed but when the client is installed it is not set automatically due to the risk of conflict with the corresponding environment variable for the server It must therefore be set manually by the user at the time that the client is installed This environment variable must be set in order to use iLoader e g Windows Server set ALTIBASE HOME C Program Files Altibase Altibase5 Server altibase home Windows Client set ALTIBASE HOME C Program Files Altibase Altibase5 Client altibase home client This indicates the port number on the server with which a connection is to be established This can be set using the PORT option or altibase properties If no designated port number can be found in descending order of precedence in the PORT option in the environment variable ALTIBASE PORT NO or in altibase properties a prompt to enter the port number will appear iLoader Users Manual 4 Introducing iLoader This sets the character set that is used when data are downloaded or uploaded When downloadi
60. strow t field term r row term U u login id P p password S s servername mode mode type commit commit unit bad badfilel log logfile e enclosing array array_size replication true false split number l errors count lob lob option string atomic parallel count direct ioparallel count rule csv readsize size iLoader Users Manual 8 General Options General Options iLoader is run with the following options Where applicable default values are shown Factor Description in out formout struc Sets the direction in which to copy data it is essential that only one is tout help set in Copy from a file to a database table out Copy from a database table to a file formout Make a table format file FORM file structout Performs a function similar to that of formout Used to cre ate a structure matching that of a given table Used when creating a client application help Explain how to use help T table_name The name of the table to be copied Since the table name is already written in the FORM file this option is ignored when uploading or downloading data d datafile datafiles The full path of the data file used when copying data from the data base to the file or from the file to the database Not used with form out A maximum of 32 filenames can be entered A consecutive series of files can be specified and used only when u
61. teger PROC integer CONDITION integer A Since CONDITION is a reserved word in Altibase a parsing error occurs Q Whenever l use iLoader to upload data I receive errors want detailed information about the errors Can you tell me how to get it A To obtain such error information execute iLoader with the option shown below iloader in f form frm bad a bad log a log If a file named a log is specified using the log option as shown above error contents will be written to the file a log The format is as follows ERR errno error string This is informative when tracing problems For reference if the bad option is used as shown about information about failed records is also saved Q Can combine special characters such as or with n line break A Yes it s possible The following example shows how to accomplish this Shell iloader out s 127 0 0 1 u sys p manager f goods fmt d goods dat r n Altibase Data Load Download utility Release Version 5 3 3 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISQL CONNECTION TCP Total 5 records downloaded DOWNLOAD 57546 shell cat goods dat cat goods dat A111100001 IM 300 ACOO001 1000 7 8E 4 A111100002 IM 310 DD0001 100 9 8E 4 B111100001 NT H5000 AC0002 780 3 58E 4 C111100001 IT U950 FA0001 35000 7 82055E43 C111100002 IT U200 AC0003 1000 9 45521E 3 Q I tried to download data
62. test VALUES 3 1 row inserted Shell iloader formout s 127 0 0 1 u sys p manager T TEST f test fmt Shell iloader out s 127 0 0 1 u sys p manager f test fmt d test dat ISQL CONNECTION TCP Total 3 records downloaded DOWNLOAD 2134 shell cat test dat 1 2 3 Shell iloader formout s 127 0 0 1 u sys p manager T TEST2 f t2 add null fmt shell vi t2 add null fmt table TEST2 Il integer I2 timestamp ADD NULL lt Revised part DATEFORM YYYY MM DD HH MI SS DATA NLS USE KO16KSC5601 shell gt iloader in s 127 0 0 1 u sys p manager f t2 add null fmt d test dat DATE FORMAT YYYY MM DD HH MI SS UPLOAD 443 Load Count 3 iSQL gt SELECT FROM test2 TEST2 I1 TEST2 12 iLoader Users Manual 44 About FORM Files 3 rows selected ADD YYYYMMDD HHMISS iSQL gt CREATE TABLE test il INTEGER Create success iSQL gt CREATE TABLE test2 il INTEGER i2 TIMESTAMD Create success iSQL INSERT INTO test VALUES 1 1 row inserted iSQL INSERT INTO test VALUES 2 1 row inserted iSQL INSERT INTO test VALUES 3 1 row inserted Shell iloader formout s 127 0 0 1 u sys p manager T TEST f test fmt Shell iloader out s 127 0 0 1 u sys p manager f test fmt d test dat Total 3 records downloaded DOWNLOAD 867 shell cat test dat 1 2 3 shell gt iloader formout s 127 0 0 1 u sys p manager T TEST2 f t2_add_val fmt shell
63. to one decimal place The number cannot be 0 The units that can be used for the file size are T and G T indicates terabytes and G indicates gigabytes If no units are specified gigabytes are used by default The name of each LOB file is determined by removing the filename extension from the original data filename and appending serialNumber lob thereto The serial number has 9 digits If the number of digits in the serial number is less than 9 the leftmost positions of the serial number are filled with 0 s zeroes The serial numbers start at 1 If a data file is split into several files using the split option a data file number is appended to each LOB file name For example if the option d dump dat lob lob file size uG is specified the following LOB files are created Total amount of LOB data T GB Size of each resulting file u GB dump 000000001 10b u Gbytes dump 000000002 10b u Gbytes dump n lob T un GBytes where n int T u preceded by 0 use separate files yes no When processing LOB files this option determines whether to save each LOB data item i e contents of one cell in a separate file This option assumes that use lob file is set to yes If this option is set to yes and lob file size is also specified an error occurs When this option is set folders having the same names as tables and columns are created and LOB 13 Using iLoader General Options data from these
64. ut formout structout exit help iLoader help in Usage in out formout structout help d datafile or datafiles f formatfile T table name F firstrow L lastrow l t field term r row term mode mode tvpel commit commit unit bad badfile l log logfile l e enclosing array array size replication true false split number readsize size errors count lob lob option string atomic parallel count direct ioparallel count rule csv iLoader help out Usage in out formout structout help d datafile or datafiles f formatfile T table name F firstrow L lastrow l t field term r row term mode mode tvpel commit commit unit bad badfile l log logfile l e enclosing array array size replication true false split number readsize size errors count lob lob option string atomic parallel count direct ioparallel count rule csv iLoader help exit Ex exit or quit or Shell iloader help ILOADER HELP Screen Usage iloader h iLoader Users Manual 32 h s u pP port silent nst NLS_USE Online Help and Other Options s server_name u user_name p password port port_no silent nst displayquery NLS_USE nls_name in out formout structout help d datafile or datafiles f formatfile T table_name F firstrow L lastrow t
65. yed to indicate that the overall upload operation succeeded but that one or more errors occurred Using iLoader for Remote Database Access You can use iLoader to access a remote server To execute iLoader the Altibase server must have been started The options are as follows and are case insensitive Command line options for running iLoader remotely iloader H S server name U user id P password PORT port no SILENT NST DISPLAYQUERY H provides help on how to execute iLoader S server name specifies the name or IP address of the computer on which the Altibase server is running U user id specifies the user ID with which to access the database P password specifies the password corresponding to the user ID PORT port no specifies the port number for communication with the remote server SILENT specifies execution in silent mode In silent mode nonessential information such as the copyright notice etc is not displayed NST specifies execution in silent mode In silent mode nonessential information such as the copyright notice etc is not displayed DISPLAYQUERY The user can check hints and conditional clauses specified in a FORM file when executing statements They are displayed in query format NLS USE specifies the character set to be used when downloading or uploading data If omit ted the client s environment variable ALTIBASE NLS USE and altibase properties

iLoader User`s Manual - ALTIBASE Customer Support

Contents

Download Pdf Manuals

Related Search

Related Contents