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:

Using the DB2 RESUME Option

If the tables you are loading already contain data, you have three choices (shown in Table B-1) for the disposition of that data.

***Table B-1 DB2 Functions and Equivalent SQL*Loader Options***

DB2	*SQLLoader Options**	Result
`RESUME NO` or no `RESUME` clause	`INSERT`	Data is loaded only if the 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.

The DB2 syntax for the RESUME clause is as follows:

RESUME  { YES | NO [ REPLACE ] }

Instead of the DB2 syntax for RESUME, you may prefer to use the equivalent SQL*Loader options.

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 the Oracle database server. The syntactical elements described in the following section 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 you are loading a single DB2-compatible file, these clauses can be in their old position--between the RESUME and RECLEN clauses. Note that while the 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 or from DB2 UNLOAD files. SQL*Loader gives an error upon encountering the DB2 Load Utility commands described in the following sections.

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 the FORMAT statement 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. If the SQL/DS option appears in this statement, SQL*Loader will terminate with an error.

DBCS Graphic Strings

Because the Oracle database server 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... ] ) ]
[ PARTn ]
[ 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