SYSCS_UTIL.IMPORT_TABLE_EX

Extended version of SYSCS_UTIL.IMPORT_TABLE that enables you to import data without locking the target table. Use this procedure during long import operations when you require continued access to the table.

The SYSCS_UTIL.IMPORT_TABLE_EX system procedure imports data from an input file into all columns of a table. For example, you can use this procedure to import data from a comma-separated value (CSV) file or other delimited file format. This procedure enables you to control whether the table is locked during the import operation, as well as whether SQLFire treats the schema and table names are case sensitive or case-insensitive.

If the table receiving the imported data already contains data, this command appends the new data without affecting existing data. Use TRUNCATE TABLE if you want to clear existing table data before importing data.

Syntax

SYSCS_UTIL.IMPORT_TABLE_EX (IN SCHEMANAME VARCHAR(128),
IN TABLENAME VARCHAR(128), IN FILENAME VARCHAR(32672),
IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1),
IN CODESET VARCHAR(128), IN REPLACE SMALLINT,
IN LOCKTABLE SMALLINT, IN NUMTHREADS INTEGER, IN CASESENSITIVENAMES SMALLINT
IN IMPORTCLASSNAME VARCHAR(32672), IN ERRORFILE VARCHAR(32672))

No result is returned from the procedure.

SCHEMANAME
Input argument of type VARCHAR(128) that specifies the schema of the table. Passing a NULL value uses the default schema name.
TABLENAME
Input argument of type VARCHAR (128) that specifies the table name of the table into which the data is to be imported. This table cannot be a system table or a declared temporary table. Passing a null results in an error.
FILENAME
Input argument of type VARCHAR(32672) that specifies the file that contains the data to be imported. If you do not specify a path, the current working directory is used. Passing a NULL value results in an error.
COLUMNDELIMITER
Input argument of type CHAR(1) that specifies a column delimiter. The specified character is used in place of a comma to signal the end of a column. Passing a NULL value will use the default value; the default value is a comma (,).
CHARACTERDELIMITER
Input argument of type CHAR(1) that specifies a character delimiter. The specified character is used in place of double quotation marks to enclose a character string. Passing a NULL value uses the default value; the default value is a double quotation mark (").
CODESET
Input argument of type VARCHAR(128) that specifies the code set of the data in the input file. The name of the code set should be one of the Java-supported character encodings. Data is converted from the specified code set to the database code set (utf-8). Passing a NULL value interprets the data file in the same code set as the node in which it is being executed.
REPLACE
SQLFire does not implement this argument; specify any value of type SMALLINT. Imported data is always added (inserted) to the target table without changing the existing table data. Use TRUNCATE TABLE if you want to clear existing table data before importing data.
LOCKTABLE
Input argument of type SMALLINT. With a non-zero value, this procedure operates similarly to SYSCS_UTIL.IMPORT_TABLE, and it locks the table during the import operation. Specify a value of zero to prevent SQLFire from locking the table during the import operation.
NUMTHREADS
Specifies the number of threads to use for the import process. Each thread processes a different portion of the file in parallel, and performs a bulk insert to the SQLFire system.
CASESENSITIVENAMES
Input argument of type SMALLINT. With a non-zero value, SQLFire treats the SCHEMANAME and TABLENAME arguments as case-sensitive. With a zero value, SQLFire treats the schema and table names as case insensitive.
IMPORTCLASSNAME
The argument can specify a custom class that extends com.vmware.sqlfire.load.Import. The specified class overrides the default import process, and can perform on-the-fly data conversions from external formats to formats that SQLFire supports. If you specify NULL for this argument, then SQLFire uses the default Import class. See Example with Custom Import Class.
ERRORFILE
This argument is reserved for future use. Always specify NULL for the argument in this release

Usage

For additional information on using this procedure see the section Using the bulk import and export procedures in the Apache Derby documentation .

Example

The following example imports data into the STAFF table from a delimited data file called myfile.del. Individual fields are separated by semicolons (;) and 6 parallel threads are used. The import procedure does not lock the target table:
CALL SYSCS_UTIL.IMPORT_TABLE_EX (null, 'STAFF', 'c:/output/myfile.del', ';', null, null, 0, 
 0 /* don't lock the table */,
 6 /* threads to use for import */,
 0 /* case insensitive table name */,
 null /* use the default import implementation */,
 null /* unused, null required */)

Example with Custom Import Class

The following example uses a custom import implementation class to import data into the STAFF table from a delimited data file called myfile.del. Individual fields are separated by semicolons (;) and 6 parallel threads are used. The import procedure does not lock the target table:
CALL SYSCS_UTIL.IMPORT_TABLE_EX (null, 'STAFF', 'c:/output/myfile.del', ';', null, null, 0, 
 0 /* don't lock the table */,
 6 /* threads to use for import */,
 0 /* case insensitive table name */,
 'examples.load.ImportFromOracle' /* use a custom import implementation */,
 null /* unused, null required */)
The custom import implementation transforms Oracle DATE strings for SQLFire:
public class ImportFromOracle extends com.vmware.sqlfire.load.Import {

  public ImportFromOracle(String inputFileName, String columnDelimiter,
      String characterDelimiter, String codeset, long offset, long endPosition,
      int noOfColumnsExpected, String columnTypes, boolean lobsInExtFile,
      int importCounter, String columnTypeNames, String udtClassNamesString)
      throws SQLException {
    super(inputFileName, columnDelimiter, characterDelimiter, codeset, offset,
        endPosition, noOfColumnsExpected, columnTypes, lobsInExtFile,
        importCounter, columnTypeNames, udtClassNamesString);
  }

  @Override
  public String getString(int columnIndex) throws SQLException {
    String val = super.getString(columnIndex);
    if (val != null && val.length() > 0) {
      switch (getColumnType(columnIndex)) {
        // assuming incoming format YYYYMMDD for DATE columns
        // and YYMMDDHH:MI:SS for TIMESTAMP columns
        // SQLFire format is YYYY-MM-DD HH:MI:SS
        case java.sql.Types.DATE:
          return val.substring(0, 4) + '-' + val.substring(4, 6) + '-'
              + val.substring(6, 8);
        case java.sql.Types.TIMESTAMP:
          return val.substring(0, 4) + '-' + val.substring(4, 6) + '-'
              + val.substring(6, 8) + ' ' + val.substring(8);
        default:
          return val;
      }
    }
    else {
      return val;
    }
  }
}