Examples of bulk import and export

All of the examples in this section are run using the ij utility.

Example: Importing all data from a file

The following example shows how to import data into the STAFF table in a sample database from the myfile.del file. The data will be appended to the existing data in the table.

CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE( 
    null,'STAFF','myfile.del',null,null,null,0);

Example: Importing all data from a delimited file

The following example shows how to import data into the STAFF table in a sample database from a delimited data file, myfile.del. This example defines the percentage character (%) as the string delimiter, and a semicolon as the column delimiter. The data will be appended to the existing data in the table.

CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(
    null,'STAFF','c:\output\myfile.del',';','%',null,0);

Example: Importing all data from a table, using a separate import file for the LOB data

The following example shows how to import data into the STAFF table in a sample database from a delimited data file, staff.del. The import file staff.del is the main import file and contains references that point to a separate file which contains the LOB data. This example specifies a comma as the column delimiter. The data will be appended to the existing data in the table.

CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE(
    null,'STAFF','c:\data\staff.del',',','"','UTF-8',0);

Example: Importing data into specific columns, using a separate import file for the LOB data

The following example shows how to import data into several columns of the STAFF table. The STAFF table includes a LOB column in a sample database. The import file, staff.del, is a delimited data file. The staff.del file contains references that point to a separate file which contains the LOB data. The data in the import file is formatted using double quotation marks (") as the string delimiter and a comma (,) as the column delimiter. The data will be appended to the existing data in the STAFF table.

CALL SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE(
    null, 'STAFF', 'NAME,DEPT,SALARY,PICTURE', '2,3,4,6', 
    'c:\data\staff.del', ',','"','UTF-8',  0);

Example: Exporting all data from a table to a single export file

The following example shows how to export data from the STAFF table in a sample database to the file myfile.del.

CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(
    null,'STAFF','myfile.del',null,null,null);

Example: Exporting data from a table to a single delimited export file

The following example shows how to export data from the STAFF table to a delimited data file, myfile.del, with the percentage character (%) as the character delimiter, and a semicolon as the column delimiter from the STAFF table.

CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(
    null,'STAFF','c:\output\myfile.del',';','%',null);

Example: Exporting all data from a table, using a separate export file for the LOB data

The following example shows how to export data from the STAFF table in a sample database to the main file, staff.del, and the LOB export file, pictures.dat.

CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE(null,'STAFF',
    'c:\data\staff.del',',','"','UTF-8', 'c:\data\pictures.dat');

Example: Exporting data from a query to a single export file

The following example shows how to export employee data in department 20 from the STAFF table in a sample database to the file awards.del.

CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY(
    'SELECT * FROM STAFF WHERE dept=20', 
    'c:\output\awards.del',null,null,null);

Example: Exporting data from a query, using a separate export file for the LOB data

The following example shows how to export employee data in department 20 from the STAFF table in a sample database to the main file, staff.del, and the LOB data to the file pictures.dat.

CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE(
    'SELECT * FROM STAFF WHERE dept=20',
    'c:\data\staff.del', ',' ,'"', 
    'UTF-8','c:\data\pictures.dat');