24953 - DB2 Bulk Loading

Sample 24953: DB2 Bulk Loading

If you have a lot of data to load into DB2 from SAS, the SAS/ACCESS Interface to DB2 offers the BULKLOAD=YES option. This will invoke the DB2 LOAD utility, enabling you to bulk load the rows of data as a single unit, which can significantly enhance performance for larger tables, where the speed of the bulk load process outweighs the overhead costs of the bulk load setup.

The SAS/ACCESS Interface to DB2 uses DSNUTILS, an IBM DB2 stored procedure that invokes the DB2 LOAD utility, to implement bulk loading. DSNUTILS is included in DB2 Version 6 and later. It is also available in DB2 Version 5 through a maintenance release. Because the LOAD utility is complex, verify with your database administrator that this utility is available and familiarize yourself with it before using it through SAS/ACCESS.

Note: When using the bulk loader, always look at the SYSPRINT output for information about the load.

Preparing to load: the BLDB2TBLXST and BL_DB2LDEXT options

The DB2 LOAD utility does not create tables; it loads data into existing tables. By default, SAS/ACCESS creates a table before loading data into it. If you want to invoke the utility for an existing table, specify BL_DB2TBLXST=YES to tell the engine that the table already exists.

If instead the table does not exist, and you only want to generate control and data files for a subsequent create and load, specify BL_DB2TBLXST=NO in conjunction with BL_DB2LDEXT=GENONLY.

Note: Data to be loaded into an existing table must match the column types of the table.
SAS does not verify the input data against the table definition. Any incompatibilities are flagged by the LOAD utility.

Configuring the load: the BL_DB2LDCT1, BL_DB2LDCT2, and BL_DB2LDCT3 options

The BL_DB2LDCT1 and BL_DB2LDCT2 options allow you to pass parameters to the load process. By using these options, you can collect statistics about the table being loaded, compress its content, load data using a different character set, or avoid putting the table in copy-pending status when ‘LOG NO’ is also specified.

The parameters specified by using BL_DB2LDCT1 are added to the load command after the LOAD keyword and before the ‘INTO TABLE’ clause. The parameters specified by using BL_DB2LDCT2 are added to the load command between the table name and the column list. The parameters that you can pass depend on which release of DB2 you are running. See the IBM DB2 documentation for your release for the list of options you can pass by using BL_DB2LDCT1 and BL_DB2LDCT2.

BL_DB2LDCT3 allows you to pass extra options that are added at the end of the control file. These options are not related to the load process, but they can be used to call other DB2 utilities, such as the REPAIR utility, to reset the tablespace status when ‘LOG NO’ is used and NOCOPYPEND is not specified. See the examples at the end of this section to see how this option can be used.

Note: Be sure to use uppercase when passing parameters to the LOAD utility with the BL_DB2LDCT1, BL_DB2LDCT2,and BL_DB2LDCT3 options

Configuring the load: the BL_DB2IN, BL_DB2REC, and BL_DB2PRINT options

These options identify the file names for control statement input (BL_DB2IN), table input (BL_DB2REC) and report output (BL_DB2PRINT). The default values for these options are respectively SYSIN, SYSREC, and SYSPRINT.

Input to the load (non-SMS): the BL_DB2UNITCOUNT and BL_DB2DEVT_PERM options

The SYSREC file contains the data to be loaded into the DB2 table. The default unit assignment for this file is SYSDA, but this can be overridden with the BL_DB2DEVT_PERM option. For large input files spanning multiple volumes, use the BL_DB2UNITCOUNT option to indicate the number. The value specified for BL_DB2UNITCOUNT must be an integer from 1 to 59. However, the maximum for a particular unit type may be less, so ask your storage administrator for this number.

Note: If the value specified with BL_DB2UNITCOUNT exceeds the maximum number of volumes for the unit, an error is returned. The option is ignored if its value is greater than 59.

Input to/output from the load (SMS): the BL_DB2DATACLAS, BL_DB2MGMTCLAS, and BL_DB2STORCLAS options

The SAS/ACCESS Interface to DB2 allows SMS parameters to be specified for the SYSREC, as well as SYSIN and SYSPRINT, files that are allocated for the bulk load operation. The SMS data, management, and storage classes can be specified by using the BL_DB2DATACLAS, BL_DB2MGMTCLAS, and BL_DB2STORCLAS options.

The BL_DB2DATACLAS option

Use this option to specify a data class for a new SMS-managed data set. The storage administrator at your installation defines the names of the data classes that you can specify with BL_DB2DATACLAS. SMS ignores this option if you specify it for a data set that SMS does not manage. Also, if SMS is not installed or is not active, the operating system ignores any data class passed through BL_DB2DATACLAS. This option applies to the control file (BL_DB2IN), the input file (BL_DB2REC), and the output file (BL_DB2PRINT) for the bulk loader.

Note: For SMS managed data sets, the data class determines whether they can extend on multiple volumes. When BL_DB2DATACLAS and BL_DB2UNITCOUNT are both specified, the latter overrides the unit count values for the data class.

The BL_DB2MGMTCLAS option

Use this option to specify a management class for a new SMS-managed data set. The storage administrator at your installation defines the names of the management classes that you can specify with BL_DB2MGMTCLAS. If SMS is not installed or is not active, the operating system ignores any management class passed through BL_DB2MGMTCLAS. This option applies to the control file (BL_DB2IN), the input file (BL_DB2REC), and the output file (BL_DB2PRINT) for the bulk loader.

The BL_DB2STORCLAS option

Use this parameter to specify a storage class for a new SMS-managed data set. The storage administrator at your installation defines the names of the storage classes that you can specify with BL_DB2STORCLAS. The storage class contains the attributes that identify a storage service level to be used by SMS for storage of the data set. If SMS is not installed or is not active, the operating system ignores any management class passed through BL_DB2STORCLAS. This option applies to the control file (BL_DB2IN), the input file (BL_DB2REC), and the output file (BL_DB2PRINT) for the bulk loader.

Note: BL_DB2STORCLAS replaces any storage attributes that are specified by using the BL_DB2DEVT_PERM option.

Restarting a failed load: the BL_DB2RSTRT, BK_DB2TBLXST and BL2LDEXT options

In case of failure, you can restart a bulk load operation. Restart functionality is controlled by the BL_DB2RSTRT option, which tells the LOAD utility whether the current load is a restart and, for a restart, indicates where to begin. When you specify a value other than NO, you must also specify BL_DB2TBLXST=YES and BL_DB2LDEXT=USERUN.

Valid values for this option are:

NO: indicates a new invocation of the LOAD utility, not a restart. This is the default.
CURRENT: indicates to restart at the last commit point.
PHASE: indicates to restart at the beginning of the current phase.

How do these options interact with DB2? To answer this question, consider how the DB2 load utility works. The utility has 10 phases: UTILINIT, RELOAD, SORT, BUILD, SORTBLD, INDEXVAL, ENFORCE, DISCARD, REPORT, and UTILTERM. The ability to restart a load operation is determined as follows:

If a failure occurs during the UTILINIT phase, restart the utility from the beginning.
If a failure occurs during the RELOAD, ENFORCE or DISCARD phase, restart the utility using BL_DB2RSTRT=CURRENT.
If a failure occurs during the SORT, INDEXVAL, or REPORT phase, restart the utility using BL_DB2RSTRT=PHASE.
If a failure occurs during the BUILD phase, restart the utility using BL_DB2RSTRT=PHASE if the REPLACE option was also used. If the load was appending data, the utility is not restartable. It must be terminated and the indexes must be rebuilt.
If the utility fails during the UTILTERM phase, there is usually no need to restart it.

Note: When the load SORTKEYS option is used and the utility fails during the RELOAD, SORT, or BUILD phase, the value CURRENT or PHASE restarts the utility from the beginning of the RELOAD phase. For a complete description of all the phases of the DB2 load utility and their ability to be restarted, see the DB2 documentation from IBM.

Examples

The following example creates a SAS data set and uses the SAS/ACCESS Interface to DB2 to load it into a DB2 table using the DB2 bulk load utility.

/* Create a samll SAS sample data set */
data work.customers;
   input custname $ 1-10
   custnum
   custcity $ 16-30
datalines;
Beach Land  16 Ocean City
Coast Shop   3 Myrtle Beach
Coast Shop   5 Myrtle Beach
Coast Shop  12 Virginia Beach
Coast Shop  14 Charleston     
Del Mar      3 Folly Beach
Del Mar      8 Charleston 
Del Mar     11 Charleston 
New Waves    3 Ocean City
New Waves    6 Virginia Beach
Sea Sports   8 Charleston
Sea Sports  20 Virginia Beach
Surf Mart  101 Charleston
Surf Mart  118 Surfside
Surf Mart  127 Ocean Isle
Surf Mart  133 Charleston

/* Open the SAS/ACCESS Engine to DB2 */
libname db2lib db2 ssid-db2a;

/* Load the SAS data set into a DB2 table */
data db2lib.customers (bulkload=yes
   dbtype=(custnum="smallint"));
   set work.customers;

run;

The following example uses the SAS/ACCESS Interface to DB2 to copy one DB2 table to another using the DB2 Bulk Load Utility:

libname db2lib db2 ssid=db2a;
data db2lib.customers (bulkload=yes
   bl_db2tblxst=yes
   bl_db2ldct1='RESUME YEST');
   set db2lib.new-customers;
run;

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Type:	Sample
Topic:	Data Management ==> Data Sources ==> External Databases ==> DB2

Date Modified:	2005-08-19 10:08:18
Date Created:	2004-12-23 09:57:39

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	SAS/ACCESS Interface to DB2	z/OS	n/a	n/a

Support