Data Set Options for Relational Databases

BL_DB2TBLXST= Data Set Option

Indicates whether the LOAD utility runs against an existing table.

Default value:	NO
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS Support:	DB2 under z/OS

Syntax
Syntax Description
Details
See Also

Syntax

BL_DB2TBLXST=YES | NO

Syntax Description

YES: specifies that the LOAD utility runs against an existing table. This is not a replacement operation. (See "Details.")
NO: specifies that the LOAD utility does not run against an existing table.

Details

To specify this option, you must first set BULKLOAD=YES.

SAS/ACCESS does not currently support table replacement. You cannot simply create a new copy of an existing table, replacing the original table. Instead, you must delete the table and then create a new version of it.

The DB2 LOAD utility does not create tables--it loads data into existing tables. The DB2 under z/OS interface creates a table before loading data into it whether you use SQL INSERT statements or start the LOAD utility) You might want to start the utility for an existing table that the DB2 engine did not create. If so, specify BL_DB2TBLXST=YES to tell the engine that the table already exists. When BL_DB2TBLXST=YES, the engine neither verifies that the table does not already exist, which eliminates the NO REPLACE error, nor creates the table. Because BULKLOAD= is not valid for update opening of tables, which include appending to an existing table, use BL_DB2TBLXST= with an output open, which would normally create the table, to accomplish appending, or use the LOAD utility against a previously created table. You can also use BL_DB2TBLXST= with BL_DB2LDEXT=GENONLY if the table does not yet exist and you do not want to create or load it yet. In this case the control and data files are generated but the table is neither created nor loaded.

Because the table might be empty or might contain rows, specify the appropriate LOAD utility control statement values for REPLACE, RESUME, or both by using BL_DB2LDCT1, BL_DB2LDCT2 , or both.

The data to be loaded into the existing table must match the table column types. The engine does not try to verify input data with the table definition. The LOAC utility flags any incompatible differences.