Oracle7 Server Utilities User's Guide Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence

Notes for DB2/DXT Users

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:

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 Non-Empty Database Tables" [*] for more details about the SQL*Loader options summarized below.

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.
Table C - 1. DB2 Functions and Equivalent SQL*Loader Operations

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.




Appends the new rows to rows already in the table.


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.


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 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 ]

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.


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.)


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)
[ CHARACTERSET character_set_name ]
[ { INFILE | INDDN } { filename | * }
	   [ "OS-dependent file processing options string" ]
	   [ { BADFILE | BADDN } filename ]
	   [ { DISCARDFILE | DISCARDDN } filename ]
	   [ { DISCARDS | DISCARDMAX } n ] ]   
[ { INFILE | INDDN } ] ...
    RESUME [(] { YES | NO [REPLACE] } [)]  ]
[ LOG { YES | NO } ]        
[ WORKDDN filename ]
[ SORTDEVT device_type ]
[ { CONCATENATE [(] n [)] |
                 [(] ( start [ { : | - } end ] ) | LAST }
                 operator  { 'char_str' | X'hex_str' } [)] } ]   
INTO TABLE tablename
	[ CHARACTERSET character_set_name ]
	[ SORTED [ INDEXES ] ( index_name [ ,index_name... ] ) ]
	[ PART n ]
	  RESUME [(] { YES | NO [REPLACE] } [)] ] 
	[ WHEN field_condition [ AND field_condition ... ] ]
	[ FIELDS  [ delimiter_spec ] ]
	[ SKIP n ]
	  { [ 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 ] ...)

Go to previous file in sequence Prev Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index