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');
Related concepts
Methods for running the import and export procedures
Bulk import and export requirements and considerations
File format for input and output
Running import and export procedures from JDBC
How the import and export procedures process NULL values
Bulk import and export of large objects
Related tasks
Importing data using the built-in procedures
Exporting data using the built-in procedures
Related reference
CODESET values for import and export procedures