4. Troubleshooting and FAQs#
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" (badfile) 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 database. The database character set needs to be changed, which is accomplished by creating the database again.
Only multilingual 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 manually. Aside from such grammatical errors, similar problems can occur if iLoader reservedwords 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
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
iLoader 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 environment 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.
$ 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
$ 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
$ 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
real 0m52.652s
user 0m3.240s
sys 0m2.990s
Note: If the remote server is specified for the -s option and iSQL is executed, a warning message that the ISQL_CONNECTION setting has been ignored is output and iSQL connects to the remote server, regardless of the value set to the ISQL_CONNECTION environment variable.
FAQ#
Question
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" integer;
"PROC" integer;
"CONDITION" integer;
}
Answer
Since CONDITION is a reserved word in Altibase, a parsing error occurs.
Question
Whenever I use iLoader to upload data, I receive errors. I want detailed information about the errors. Can you tell me how to get it?
Answer
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.
Question
Can I combine special characters (such as "^" or "|" ) with "n" (line break)?
Answer
Yes, it's possible. The following example shows how to accomplish this.
$ iloader formout -s 127.0.0.1 -u sys -p manager -f goods.fmt -T goods -silent
$ iloader out -s 127.0.0.1 -u sys -p manager -f goods.fmt -d goods.dat -r '^%n'
DATA_NLS_USE: US7ASCII
Total 30 record download(GOODS)
DOWNLOAD : 9.4280 msec
$ cat goods.dat
cat goods.dat
A111100001,IM-300 ,AC0001 ,1000,78000^
A111100002,IM-310 ,DD0001 ,100,98000^
B111100001,NT-H5000 ,AC0002 ,780,35800^
C111100001,IT-U950 ,FA0001 ,35000,7820.55^
C111100002,IT-U200 ,AC0003 ,1000,9455.21^
…
Question
I tried to download data after adding the following condition clause to the FORM file:
table CUST_PROD
{
"SERV_NO" varchar (10);
"PROD_ID" char (5);
"EFF_DATE" char (14);
"EXP_DATE" char (14);
"CUST_TYPE" char (2);
"L_MOD_DATE" date;
}
DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS
DOWNLOAD CONDITION WHERE EXP_DATE >= '20050301000000'
The following error occured, and it would not run.
Input Command Parser Error or Reserved Keyword Used
DOWNLOAD: 338
Answer
When you specify conditions in a download condition clause, you must use double quotation marks (" ").
DOWNLOAD CONDITION "WHERE EXP_DATE >= '20050301000000'"
This is how you use the quotation marks.
Question
I want to download the data by sorting the data by the primary key. I wonder if orderby is supported in iLoader.
Answer
The where condition using the download condition as in the following example:
$ iloader formout -s 127.0.0.1 -u sys -p manager -f dept.fmt -T department -silent
$ iloader -s 127.0.0.1 -u sys -p manager out -f dept.fmt -d dept.dat -silent
Total 8 record download(DEPARTMENT)
DOWNLOAD : 1.0070 msec
$ cat dept.dat
1001,"RESEARCH DEVELOPMENT DEPT 1 ","New York ",16
1002,"RESEARCH DEVELOPMENT DEPT 2 ","Sydney ",13
1003,"SOLUTION DEVELOPMENT DEPT ","Japan ",14
2001,"QUALITY ASSURANCE DEPT ","Seoul ",17
3001,"CUSTOMER SUPPORT DEPT ","London ",4
3002,"PRESALES DEPT ","Peking ",5
4001,"MARKETING DEPT ","Seoul ",8
4002,"BUSINESS DEPT ","LA ",7
If you modify the FORM file as follows and download it, you can get sorted data for Primary Key.
table departments
{
"DNO" smallint; <= Primary Key
"DNAME" char (30);
"DEP_LOCATION" char (15);
"MGR_NO" integer;
}
download condition "where DNO is not null" <= Revised part
DATA_NLS_USE=US7ASCII
$ iloader -s 127.0.0.1 -u sys -p manager out -f dept.fmt -d dept.dat -silent
Total 8 record download(DEPARTMENT)
DOWNLOAD : 1.0200 msec
$ cat dept.dat
1001,"RESEARCH DEVELOPMENT DEPT 1 ","New York ",16
1002,"RESEARCH DEVELOPMENT DEPT 2 ","Sydney ",13
1003,"SOLUTION DEVELOPMENT DEPT ","Japan ",14
2001,"QUALITY ASSURANCE DEPT ","Seoul ",17
3001,"CUSTOMER SUPPORT DEPT ","London ",4
3002,"PRESALES DEPT ","Peking ",5
4001,"MARKETING DEPT ","Seoul ",8
4002,"BUSINESS DEPT ","LA ",7
Question
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 I use with your MMDBMS?
Answer
A: You can use the iLoader utility, which allows you to download and upload data on a tableby-table basis. First, use iLoader to create a FORM file corresponding to the table. (You can also use a text editor instead of iLoader.)
$ 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 '|'): In this way, the text file can be directly uploaded.
$ iloader in -s 127.0.0.1 -u sys -p manager -f test.FORM-d datafile -t '|'
Answer
When the date formats of two columns in the same table are different, is there any way I can import them?
Answer
In iLoader, DATEFORM can be set for individual columns, as shown in the following example:
table table_name
{
column1 date "YYYY-MM-DD";
column2 date;
...
}
DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS
Note that date format options are applied in the following order, in descending order of preference:
Date format stated right after the DATE column of a FORM file > Environment variable ILO_DATEFORM > Date format stated outside of brackets in a FORM file
Question
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?
Answer
It will delete the existing data.
Question
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?
Answer
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 truncating.
Question
If I need to add columns to a table while a database is online, how can I go about doing that? Also, I made a backup before changing the schema. Will it be all right if I add columns and then upload a backup file that I made before changing the schema?
Answer
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 be taken:
- Backup data before changing schema using iLoader
- stop replication
- 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
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.
Question
Is there any way I can import a dmp file exported from Oracle to Altibase as it is?
Answer
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.
Question
Is it possible to upload or download gis data with iLoader? If so, what format are they saved as in the data file?
Answer
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.