Examples

All of the examples in this section are run using the gfxd utility. See Procedures for more details about the syntax for each procedure.

Importing table data from a file

The following example shows how to import data into the APP.STAFF table in a sample database from the myfile.del fille:

gfxd> CALL SYSCS_UTIL.IMPORT_TABLE_EX 
   ('APP','STAFF','myfile.del',null,null,null,0,0,3,1,null,null,null);

In the above command, the three "null" values indicate default values for the column delimiter, character delimiter, and codeset. The next two zero values indicate default behavior for replacing data and locking the table (by default, INSERT mode is used to check primary key constraints, and the table is not locked during the insert). The value 3 indicates that data is appended to the existing data in the table using 3 separate threads. The final 1 value (any non-zero value) specifies case-sensitive names. The remaining null values use defaults for the custom import classname (none used), and error file for logging import errors (none specified). See SYSCS_UTIL.IMPORT_TABLE_EX.

Importing table data from a delimited file

The following example shows how to import data into the APP.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. GemFire XD ignores primary key constraints (using PUT INTO syntax) and imports using 3 threads. The table is not locked during import.

gfxd> CALL SYSCS_UTIL.IMPORT_TABLE_EX 
   ('APP','STAFF','c:\output\myfile.del',';','%',null,1,0,3,1,null,null);

See SYSCS_UTIL.IMPORT_TABLE_EX.

Using a separate import file for 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:

gfxd> CALL SYSCS_UTIL.IMPORT_TABLE_LOBS_FROM_EXTFILE
    ('APP','STAFF','c:\data\staff.del',',','"','UTF-8',0,0,3,0,null,null); 

This example specifies a comma (,) as the column delimiter and a double quotation mark (") as the character delimiter. The data codeset is specified as UTF-8. The zero value indicates that GemFire XD uses insert mode to append data to the existing data in the table (instead of using REPLACE mode). The next two zero values indicate default behavior for replacing data and locking the table (by default, INSERT mode is used to check primary key constraints, and the table is not locked during the insert). The value 3 indicates that data is appended to the existing data in the table using 3 separate threads. The final 0 value specifies case-insensitive names (the default). The remaining null values use defaults for the custom import classname (none used), and error file for logging import errors (none specified). See SYSCS_UTIL.IMPORT_TABLE_LOBS_FROM_EXTFILE.

Importing table data into specific columns, using a separate import file for LOB data

The following example shows how to import data into several columns of the APP.STAFF table. Data is loaded into the specified columns: NAME, DEPT, SALARY, and PICTURE having column indexes of 2, 3, 4, and 6, respectively:
CALL SYSCS_UTIL.IMPORT_DATA_LOBS_FROM_EXTFILE
    ('APP', 'STAFF', 'NAME,DEPT,SALARY,PICTURE', '2,3,4,6', 
    'c:\data\staff.del', ',','"','UTF-8',0,0,3,0,null,null);

The STAFF table includes a LOB column in a sample database. The import file staff.del is the delimited data file, which contains references that point to a separate file that contains the LOB data. See SYSCS_UTIL.IMPORT_DATA_LOBS_FROM_EXTFILE.

Exporting all data from a table to a single export file

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

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

The three null values indicate default values for the column delimiter (,), character delimiter ("), and codeset (default codeset of the GemFire XD node).

This example uses the percentage character (%) as the character delimiter, and a semicolon as the column delimiter:

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

See SYSCS_UTIL.EXPORT_TABLE.

Exporting data from a query to a single export file

The following example shows how to use a query to export employee data from department 20 of the STAFF table into the file awards.del.

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

The three null values indicate default values for the column delimiter (,), character delimiter ("), and codeset (default codeset of the GemFire XD node). See SYSCS_UTIL.EXPORT_QUERY.

Exporting data from a table, storing LOB data in a separate export file

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

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

Exporting data from a query, storing LOB data in a separate export file

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.EXPORT_QUERY_LOBS_TO_EXTFILE(
    'SELECT * FROM STAFF WHERE dept=20',
    'c:\data\staff.del', ',' ,'"', 
    'UTF-8','c:\data\pictures.dat');