Db2 connector properties for bulk loading to z/OS

Set the bulk load properties for the IBM® Db2® connector to perform a bulk load on Db2 for z/OS®.

To enable the bulk load properties, you must set the Write mode to Bulk load and select Bulk load to Db2 on z/OS to Yes.

Load method
Specify the load method to use for loading input data into Db2 for z/OS.
If you select to bulk load to Db2 for z/OS, you must specify a load method. The following values are valid:
  • MVS dataset(s)
  • Batch pipe(s)
  • USS pipe(s)

The connector runs in parallel for all the load methods. When the connector loads a partitioned table, a separate load utility is invoked for each partition.

Transfer
Set the transfer properties to transfer data to Db2 for z/OS. See Transfer properties for more information.
DSN prefix
Specify a DSN prefix to use to construct the names of data sets, batch pipes, and USS pipes. This property is enabled for all load methods. If you do not specify a DSN prefix, the value in the Bulk load to Db2 on z/OS -> Transfer -> User property is used. If a value is not specified for the User property, the value from the Connection -> User name property is used.

When you specify a DSN prefix, names are constructed in the following ways:

  • Data files are named as prefix.IN.P#####, where ##### is the partition number, padded with zeros. If the table that is loaded is not partitioned, the partition number is 00000.
  • Discard files are named as prefix.DSC.P#####, where ##### is the partition number, padded with zeros. If the table that is loaded is not partitioned, the partition number is 00000.
  • Work files are named as prefix.WORK1.P##### and prefix.WORK2.P#####, where ##### is the partition number, padded with zeros. If the table that is loaded is not partitioned, the partition number is 00000.
  • Error files are named as prefix.SYSERR.P#####, where ##### is the partition number, padded with zeros. If the table that is loaded is not partitioned, the partition number is 00000.
  • Map files are named as prefix.SYSMAP.P#####, where ##### is the partition number, padded with zeros. If the table that is loaded is not partitioned, the partition number is 00000.
The DSN prefix includes date and time placeholders, which are replaced with the actual date and time when the job runs. The date format is YYMMDD  The time format is HHMMSS.  For example, if the value in the DSN prefix property is USER1.DYYMMDD.THHMMSS and the job is run on 8 February 2010 at 10:21:24 a.m on a nonpartitioned table by using the MVS dataset load method, the following names are used:
  • USER1.D100208.T102124.IN.P00000
  • USER1.D100208.T102124.DSC.P00000
  • USER1.D100208.T102124.WORK1.P00000
  • USER1.D100208.T102124.WORK2.P00000
  • USER1.D100208.T102124.SYSERR.P00000
  • USER1.D100208.T102124.SYSMAP.P00000
Batch pipe system ID
Provide the name for the batch pipe system.

This property is enabled only if you selected Batch pipes as the load method. This property determines how the data is transferred to Db2 on z/OS. If you specify a value for this property, then batch pipes are used to transfer the data to z/OS.

File(s) only
Use the Files only property to create data files on z/OS.
The Files only property is optional. Valid values for this property are Yes and No. If you select Yes, data files are created on z/OS but the LOAD utility is not invoked.
Note: This property is enabled only if the Load method is set to MVS dataset(s).
Device type
The Device type property is used to identify the device type to be used for data set allocation.

This property is optional. If you do not specify a value, SYSDA is the default value.

Partition number
Specify the partition to be loaded.

The Partition number property is optional. The value for this property must be an integer. If you do not specify a value, the data is loaded into all partitions.

Row count estimate
Use the Row count estimate property to provide the estimated number of rows to load into all the partitions combined.

This estimate is used to calculate the amount of disk space to allocate for the data sets. The Row count estimate property is optional. The value for this property must be an integer. If you do not specify a value, 1000 is the default value.

Statistics
Use the Statistics property to specify a display of statistics at the end of the load.

The Statistics property is optional. Valid values are None, All, Index, and Table. None is the default value.

Utility ID
Specify a name to use in Db2 to identify the execution of the load utility. The utility ID that you specify is appended with the suffix .P#####, where ##### is the partition number padded with zeros. If the table that is loaded is not partitioned, the partition number is 00000. If the length of the name is greater than 16 characters after the connector concatenates the utility ID and the suffix, the connector issues an error message. If no value is specified in the utility ID, the default value Db2.LOAD.P##### is used.

This property is optional.

Concurrent access level
Level of application's concurrent access to the table space or partition. Parameter's value corresponds to SHRLEVEL option of LOAD command. Available values for this property are:
  • NONE (default)
  • REFERENCE
  • CHANGE
Load records into a non-empty table space
Option specifies whether records are to be loaded into an empty or non-empty table space. Parameter's value corresponds to RESUME option of LOAD command. Available values for this property are:
  • No (default)
  • Yes
Note: By default, option RESUME NO is accompanied with option REPLACE in LOAD command, however user can modify that behavior by setting environment variable CC_DB2_ZLOAD_ADD_OPT_REPLACE:
  • Set variable to YES or 1 in order to enforce adding REPLACE option to the LOAD command irrespective of other settings.
  • Set variable to NO or 0 in order to enforce not adding REPLACE option to the LOAD command irrespective of other settings.
Load with logging
Use the Load with logging property to indicate whether logging must occur during the load process.

The Load with logging property is optional. Valid values are No and Yes. If you do not specify a value, No is used.

Set copy-pending
Use the Set copy-pending property to specify whether table space is set to copy-pending status.
The Set copy-pending property is optional. Valid values for this property are No and Yes. If you do not specify a value, No is the default value.
Note: This property is applicable when the value for Load with logging is No.
Encoding
Use the Encoding property to specify input data set encoding. See Encoding properties properties for more information.
Image-copy function
Use the properties in this group to specify details about image copy and recovery files. Specify whether to run an Image-copy function after completing a bulk load job.
The valid values for this property are:
  • Concurrent
  • Full
  • Incremental
  • No (default)
You can configure the Image-copy function by specifying values to the properties that are displayed when you select a valid value.
Scope
Specify the scope of the image-copy. The valid values are Full and Single partition.
Image-copy file
Specify the characteristics about the image-copy output file.
Image-copy backup file
Specify whether to create an image-copy file. The Image-copy backup file property is optional. The value for this property should be Boolean.
Recovery file
Specify whether to create the recovery file.
Recovery backup file
Specify whether to create an additional recovery file. The Recovery backup file property is optional.
Change limit percent 1
Specify the percentage limit of changed pages in the table space at which an incremental image-copy is to be taken. The value of this property can only be specified when Image copy is as same as the Incremental image-copy.
Change limit percent 2
Specify the percentage limit of changed pages in the table space at which an incremental image-copy is to be taken. The value of this property can only be specified when Image copy is as same as the Incremental image-copy, and when Change limit percent 1 is specified
Report only
The Report only file property is optional. Report only can be specified only when Image-copy is as same as the Incremental image-copy.
System pages
Specify whether the copy utility must put system pages at the beginning of the image-copy file.
Allow changes
Specify whether other programs can update the table space while the copy is running. This property is valid for the Image-copy function only.
Data file attributes
Set additional options to connect to Db2 connector to perform bulk load jobs. Set options, such as to create files on z/OS, to load a particular partition, and to display statistics at the end of the load. See Data file attributes for more information.