B
DB2/DXT User Notes

This appendix describes differences between SQL*Loader DDL syntax and DB2 Load Utility/DXT control file syntax. The topics discussed include:

SQL*Loader Extensions to the DB2 Load Utility

SQL*Loader can use any DB2 Load Utility control file. SQL*Loader also offers numerous extensions to the DB2 loader by supporting the following features:

The DATE datatype
The automatic generation of unique sequential keys
The ability to specify the record length explicitly
Loading data from multiple data files of different file types
Fixed-format, delimited-format, and variable-length records
The ability to treat a single physical record as multiple
logical records
The ability to combine multiple physical records into one logical record via CONCATENATE, CONTINUEIF NEXT, and CONTINUEIF THIS (IBM supports only CONTINUEIF THIS)
More thorough error reporting
Bad file (DB2 stops on first error)
Control over the number of records to skip, the number to load, and the number of errors to allow
ANDed WHEN clause
FIELDS clause for default field characteristics
Direct path loads
Parallel loads

Using the DB2 RESUME Option

You can use the DB2 syntax for RESUME, but you may prefer to use SQL*Loader's equivalent keywords. See "Loading into Empty and Non-Empty Tables" on page 5-27 for more details about the SQL*Loader options summarized below.

Table A-1: **DB2 Functions and Equivalent SQL*Loader Operations**

DB2	SQL*Loader Options	Result
RESUME NO or no RESUME clause	INSERT	Data loaded only if table is empty. Otherwise an error is returned.
RESUME YES	APPEND	New data is appended to existing data in the table, if any.
RESUME NO REPLACE	REPLACE	New data replaces existing table data, if any.

A description of the DB2 syntax follows. If the tables you are loading already contain data, you have three choices for the disposition of that data. Indicate your choice using the RESUME clause. The argument to RESUME can be enclosed in parentheses.

RESUME  { YES | NO [ REPLACE ] }

where:

YES	Appends the new rows to rows already in the table.
NO	Requires the table to be empty before loading. An error message results if the table contains rows and the run is terminated. This is the default.
NO REPLACE	Deletes any data in the table before loading new data. Thus, the new data will replace the old. This argument requires that the username invoking SQL*Loader have DELETE privilege on the table. You cannot recover the data that was in the table before the load, unless you saved it using Export or something comparable.

In SQL*Loader you can use one RESUME clause to apply to all loaded tables by placing the RESUME clause before any INTO TABLE clauses. Alternatively, you can specify your RESUME options on a table-by-table basis by putting a RESUME clause after the INTO TABLE specification. The RESUME option following a table name will override one placed earlier in the file. The earlier RESUME applies to all tables that do not have their own RESUME clause.

Inclusions for Compatibility

The IBM DB2 Load Utility contains certain elements that SQL*Loader does not use. In DB2, sorted indexes are created using external files, and specifications for these external files may be included in the load statement. For compatibility with the DB2 loader, SQL*Loader parses these options, but ignores them if they have no meaning for Oracle. The syntactical elements described below are allowed, but ignored, by SQL*Loader.

LOG Statement

This statement is included for compatibility with DB2. It is parsed but ignored by SQL*Loader. (This LOG option has nothing to do with the log file that SQL*Loader writes.) DB2 uses the log file for error recovery, and it may or may not be written.

SQL*Loader relies on Oracle's automatic logging, which may or may not be enabled as a warm start option.

[ LOG { YES | NO } ]

WORKDDN Statement

This statement is included for compatibility with DB2. It is parsed but ignored by SQL*Loader. In DB2, this statement specifies a temporary file for sorting.

[ WORKDDN filename ]

SORTDEVT and SORTNUM Statements

SORTDEVT and SORTNUM are included for compatibility with DB2. These statements are parsed but ignored by SQL*Loader. In DB2, these statements specify the number and type of temporary data sets for sorting.

[ SORTDEVT device_type ]

[ SORTNUM n ]

DISCARD Specification

Multiple file handling requires that the DISCARD clauses (DISCARDDN and DISCARDS) be in a different place in the control file - next to the datafile specification. However, when loading a single DB2 compatible file, these clauses can be in their old position - between the RESUME and RECLEN clauses. Note that while DB2 Load Utility DISCARDS option zero (0) means no maximum number of discards, for SQL*Loader, option zero means to stop on the first discard.

Restrictions

Some aspects of the DB2 loader are not duplicated by SQL*Loader. For example, SQL*Loader does not load data from SQL/DS files nor from DB2 UNLOAD files. SQL*Loader gives an error upon encountering the DB2 Load Utility commands described below.

FORMAT Statement

The DB2 FORMAT statement must not be present in a control file to be processed by SQL*Loader. The DB2 loader will load DB2 UNLOAD format, SQL/DS format, and DB2 Load Utility format files. SQL*Loader does not support these formats. If this option is present in the command file, SQL*Loader will stop with an error. (IBM does not document the format of these files, so SQL*Loader cannot read them.)

FORMAT { UNLOAD | SQL/DS }

PART Statement

The PART statement is included for compatibility with DB2. There is no Oracle concept that corresponds to a DB2 partitioned table.

In SQL*Loader, the entire table is read. A warning indicates that partitioned tables are not supported, and that the entire table has been loaded.

[ PART n ]

SQL/DS Option

The option SQL/DS=tablename must not be used in the WHEN clause. SQL*Loader does not support the SQL/DS internal format. So if the SQL/DS option appears in this statement, SQL*Loader will terminate with an error.

DBCS Graphic Strings

Because Oracle does not support the double-byte character set (DBCS), graphic strings of the form G'**' are not permitted.

SQL*Loader Syntax with DB2-compatible Statements

In the following listing, DB2-compatible statements are in bold type:

OPTIONS (options)

{ LOAD | CONTINUE_LOAD } [DATA]

[ CHARACTERSET character_set_name ]

[ { INFILE | INDDN } { filename | * }

       [ "OS-dependent file processing options string" ]

       [ { BADFILE | BADDN } filename ]

       [ { DISCARDFILE | DISCARDDN } filename ]

       [ { DISCARDS | DISCARDMAX } n ] ]   

[ { INFILE | INDDN } ] ...

[ APPEND | REPLACE | INSERT | 

RESUME [(] { YES | NO [REPLACE] } [)]  ]

[ LOG { YES | NO } ]        

[ WORKDDN filename ]

[ SORTDEVT device_type ]

[ SORTNUM n ]

[ { CONCATENATE [(] n [)] |

CONTINUEIF { [ THIS | NEXT ]

[(] ( start [ { : | - } end ] ) | LAST }

operator  { 'char_str' | X'hex_str' } [)] } ]   

[ PRESERVE BLANKS ]

INTO TABLE tablename

    [ CHARACTERSET character_set_name ]

    [ SORTED [ INDEXES ] ( index_name [ ,index_name... ] ) ]

    [ PART n ]

    [ APPEND | REPLACE | INSERT | 

      RESUME [(] { YES | NO [REPLACE] } [)] ] 

    [ REENABLE [DISABLED_CONSTRAINTS] [EXCEPTIONS table_name] ]

    [ WHEN field_condition [ AND field_condition ... ] ]

    [ FIELDS  [ delimiter_spec ] ]

    [ TRAILING [ NULLCOLS ] ]

    [ SKIP n ]

    (.column_name

      { [ RECNUM

          | SYSDATE

          | CONSTANT value

          | SEQUENCE ( { n | MAX | COUNT } [ , increment ] )

          | [[ POSITION ( { start [ {:|-} end ] | * [+n] } ) ]

             [ datatype_spec ]

             [ NULLIF field_condition ]

             [ DEFAULTIF field_condition ]

             [ "sql string" ] ] ]  }

      [ , column_name ] ...)

[ INTO TABLE ] ... [ BEGINDATA ]

[ BEGINDATA]

B DB2/DXT User Notes