-BMCL (BMC LOADPLUS utility)

Use of -BMCL to perform a fast load

If you specify to use the UNLOAD PLUS and LOADPLUS utilities, the utilities can quickly unload data from one table and load it into another table that has a similar structure.

The FORMAT BMCUNLOAD parameter specifies to move the data.

Example of -BMCL command—FORMAT BMCUNLOAD

Use of -BMCL with multiple work data sets

You can specify to use multiple SYSUT temporary work data sets to enable LOADPLUS to build nonclustering indexes for a table concurrently.

If the number of indexes in a table is equivalent to or less than the maximum number of SYSUT data sets, ALTER and BMC AMI Change Manager for Db2 create one SYSUT data set for each nonclustering index. When you specify to use multiple SYSUT temporary work data sets, SUT is used as the prefix for the WORKDDN parameter in the -BMCL command (see the following figure for an example of a table that is stored in a single-table table space). In addition, the -JCLP command precedes the -BMCL command in the worklist and indicates the number of SYSUT data sets that are generated in the JCL.

Example of -BMCL command—multiple SYSUT data sets

One SYSUT data set is used when the table contains only one index or when the number of nonclustering indexes for a table exceeds the number of work data sets that you specified as the maximum. SYSUT001 is used for the WORKDDN parameter in the -BMCL command, as shown below. The table in this example is stored in a multi-table table space.

Example of -BMCL command—single SYSUT data set

Use of -BMCL to register image copies

If any of the four image copies (primary and backup copies for the local and remote sites) are registered, Analysis inserts the REGISTER DDName parameter.

The following example shows an example of the -BMCL worklist command with the REGISTER parameter. If none of the image copies are registered, Analysis inserts the REGISTER NONE parameter.

Example of -BMCL command—REGISTER parameter

Use of -BMCL to create partition-level image copies

Analysis can generate parameters for a partition-level image copy of a partitioned table space or index in a database when you select to use LOADPLUS as the load utility.

The example below shows a  -BMCL command that is used when partition-level image copies are requested for a table space that has five partitions.

 -BMCL command—partition-level image copies

Use of -BMCL to perform multitasking

The LOADPLUS utility can be used with the UNLOAD PLUS utility to multitask the unloading and loading of data.

When you unload and load data in partitioned table spaces that contain more partitions than the value of the MAXSYSREC keyword, the product requires multitasking. The product uses the value of the MAXSYSREC keyword in the ALUIN input stream to determine the number of dynamically allocated data sets to use in a single -BMCL command. The default value of the MAXSYSREC keyword is 256. The first -BMCL command contains the LOAD REPLACE parameter; the remaining -BMCL commands contain the LOAD RESUME YES parameter and the REPLACE parameter for each partition.

The LOAD statement in the -BMCL command will contain the ORDER YES parameter if the following conditions exist:

• The utility is multitasking the loading of data.
• The table space contains compressed partitions.
• The value of the MAXSYSREC keyword is less than the number of partitions in the table space or a value is not specified for MAXSYSREC.

The following example shows a -BMCL command for loading a table space that contains 540 partitions. The IPPARTS parameter in the -JCLP command specifies the number of partitions for each -BMCL command.

Use of -BMCL to migrate tables with ROWID columns

You can modify or migrate tables that contain ROWID columns with data.

If the ROWID column is defined as GENERATED ALWAYS, ALTER and Change Manager omit the ROWID column in the UNLOAD and LOAD statements. If the ROWID column is defined as GENERATED BY DEFAULT, ALTER and Change Manager include the ROWID column in the UNLOAD and LOAD statements. The column is unloaded last in the sequence of columns and is also loaded last, even though it might not be the last column in the table. When a table that contains a ROWID column is unloaded, Analysis lists all of the columns that are unloaded in the UNLOAD statement in the worklist.

Use of -BMCL to load data in XML and LOB columns

When you use the LOADPLUS utility to unload data contained in XML or LOB columns, the utility loads the data from a file reference input data set that contains the data.

When the -BMCL command contains the LOAD DATA REPLACE parameter, Analysis adds the PRELOAD LOAD parameter. If the -BMCL command contains the LOAD DATA RESUME YES parameter and the number of discards is greater than zero, Analysis adds the PRELOAD CONTINUE parameter. 

Example of-BMCL command—loading XML data

Use of -BMCL to delete dynamically allocated discard data sets

When you specify to use the UNLOAD PLUS and LOADPLUS utilities and to dynamically allocate unload data sets, the LOADPLUS utility also dynamically allocates a discard data set for each LOAD statement, if needed.

The LOADPLUS utility does not delete the discard data set automatically, even if the data set is empty. The Analysis component adds the SYSDISC YES option to the DELETEFILES NO parameter in the -BMCL worklist command, as shown in the following example. The SYSDISC YES option instructs LOADPLUS to delete the discard data set if the data set is empty.

ALTER and Change Manager perform the actions cited below if you do one of the following:

• You do not specify to dynamically allocate the unload (SYSREC) data set in the Analysis override options
• You specify to dynamically allocate the unload (SYSREC) data set and you manually add the DISCARDS 1 keyword to the ALUIN input stream (or you specify DISCARDS=(1,R), in the installation options)

The resulting actions are as follows:

• When generating the worklist, the Analysis component omits the SYSDISC YES option in the DELETEFILES NO parameter in the -BMCL worklist command.
• If DISCARDS is set to 1, the JCL Generation component allocates the discard data set in the JCL that it generates for the worklist.
• When the Execution component runs the worklist, the LOADPLUS utility does not delete the discard data set.

Use of -BMCL to migrate only data with FORCELOADREPLACE

When you specify the FORCELOADREPLACE keyword in the ALUIN input stream, Analysis generates one of a few forms of the LOAD DATA syntax.

Analysis generates the following forms of the syntax:

• LOAD DATA RESUME YES INTO TABLE

  Analysis uses this syntax when you are migrating only data from a single table at the table level of a segmented table space. Analysis does not assume that the structures on the sending and receiving subsystems are identical. The load utility deletes the existing data in the table before loading.

  Example of -BMCL command—LOAD DATA RESUME YES INTO TABLE

  -SQL  000850 DELETE FROM J1ALLN1.T_T02SSEG
  -SYNC 000900 END OF SQL DELETE FROM TABLES SECTION
  ********************************************************************
  -JCLP 000950 BMCL NUMIX   2
  -BMCL 001000
               LOAD DATA  RESUME YES
   INDSN('RDAJZB4.DEGA.J120824G.SR000001')
               ERRDDN SYSER001
               PRELOAD CONTINUE
               DISCARDS 1
               DISCARDDN SYSDS001
               DELETEFILES NO
               ORDER YES
               EBCDIC CCSID(37,0,0)
               WORKDDN (SUT)
               LOADDN SORT0001
               ENFORCE NO
               UNIQUEINTO YES
               INTO TABLE J1ALLN1.T_T02SSEG
   (
    COLIN_1
      POSITION(*) INTEGER,
    COLSI_2
      POSITION(*) SMALLINT,
    COLCH_3
      POSITION(*) CHAR(20),
    COLBI_4
      POSITION(*) BINARY(200),
    COLDC_5
      POSITION(*) DECIMAL(31,20),
    COLDC_6
      POSITION(*) DECIMAL(11,2),
    COLFL_7
      POSITION(*) FLOAT(21),
    COLFL_8
      POSITION(*) FLOAT(53),
    COLDT_9
      POSITION(*) DATE EXTERNAL,
    COLTM_10
      POSITION(*) TIME EXTERNAL,
       BMC_SKIPTMSTAMP001   POSITION(*) CHAR(26))
  -SYNC 001050 LOAD TB J1ALLN1.T_T02SSEG COMPLETE

• LOAD DATA RESUME YES INTO TABLE REPLACE

  Analysis uses this syntax when you are migrating only data from one or more tables at the table space level of a segmented table space. Analysis assumes that the structures on the sending and receiving subsystems are identical, and generates the LOAD statements based on the structure of the receiving subsystem. The load utility replaces the data in the table; an SQL DELETE statement is not needed.

  Example of  -BMCL command—LOAD DATA RESUME YES INTO TABLE REPLACE

  * -SQL DELETE NOT NEEDED - J1DYNR4.T_T02SSEG
  -SYNC 001850 END OF SQL DELETE FROM TABLES SECTION
  ********************************************************************
  -SYNC 001900 LOAD TB J1DYNR4.T_T01ICP COMPLETE
  -JCLP 001950 BMCL NUMIX   2
  -BMCL 002000
               LOAD DATA  RESUME YES
   INDSN('RDAJZB4.DEGA.J120727D.SR000002')
               ERRDDN SYSER001
               PRELOAD CONTINUE
               DISCARDS 1
               DISCARDDN SYSDS001
               DELETEFILES NO
               ORDER YES
               FORMAT BMCUNLOAD
               WORKDDN (SUT)
               LOADDN SORT0001
               ENFORCE NO
               UNIQUEINTO YES
               INTO TABLE J1DYNR4.T_T02SSEG
                      REPLACE
               WHEN TABLE = 1
  -SYNC 002050 LOAD TB J1DYNR4.T_T02SSEG COMPLETE

• LOAD DATA REPLACE INTO TABLE

  Analysis uses this syntax when you are migrating only data from an entire table space at the table space level of a partitioned table space.

  Analysis assumes that the structures on the sending and receiving subsystems are identical, and generates the LOAD statements based on the structure of the receiving subsystem. The load utility replaces the data in the table space.

  Example of  -BMCL command—LOAD DATA REPLACE INTO TABLE

  -JCLP 001900 BMCL NUMIX   2
  -JCLP 001950 BMCL IPPARTS 3
  -BMCL 002000
               LOAD DATA  REPLACE
   INDSN('RDAJZB4.DEGA.J120727D.SR000001.P001'
   ,'RDAJZB4.DEGA.J120727D.SR000001.P002'
   ,'RDAJZB4.DEGA.J120727D.SR000001.P003'
   )
               ERRDDN SYSER001
               DISCARDS 1
               DISCARDDN SYSDS001
               DELETEFILES NO
               ORDER YES
               FORMAT BMCUNLOAD
               WORKDDN (SUT)
               LOADDN SORTP
               ENFORCE NO
               REDEFINE NO
               UNIQUEINTO YES
               INTO TABLE J1DYNR4.T_T01ICP
               WHEN TABLE = 1
  -SYNC 002050 LOAD TB J1DYNR4.T_T01ICP COMPLETE

Notes for -BMCL

For some objects, Analysis generates the -BMCL command differently.

Note the following items when the -BMCL command is used in a worklist:

• The utility command starts on the second line of the statement. The first line is reserved for parameters that are passed to the utility.
• If a table space is being created in the worklist, Analysis includes the REDEFINE NO parameter in the -BMCL command.

• When using LOADPLUS in a worklist environment, your work files might not be dynamically allocated automatically, even if you have specified ACTIVE=YES in your utility installation options module.

  If you are operating in a worklist environment, LOADPLUS ignores the value that you specified in the DELFILES utility installation option and always uses DELFILES=NO. To delete your work files, you must edit your worklist and change DELETEFILES NO to DELETEFILES YES in the LOAD command. However, BMC recommends that you do not delete your work files because subsequent steps in the worklist might require the use of the files. Instead, use the JCL Generation Static Data Set Options panel to specify to use a data set cleanup step. (For more information, see Setting-the-JCL-options-for-static-data-sets.)

• If you specify to collect statistics with other utilities (such as the LOADPLUS utility), and you specify to use the BMCSTATS utility to update statistics, Analysis includes the BMCSTATS YES parameter in the -BMCL command. When the command includes the BMCSTATS YES parameter, generated messages are output to SYSPRINT. The REPORT NO parameter, which suppresses the creation of a report of updated statistics to output to SYSPRINT, is available only when the BMCSTATS utility is run as a stand-alone utility (with the -BMCS command). BMCSTATS does not generate a statistics report when other utilities collect statistics.
• If you are converting from a DECIMAL column with a scale of 0 to a CHAR or VARCHAR column, Analysis includes the REMOVEDECIMAL parameter on the column in the -BMCL command. The REMOVEDECIMAL parameter instructs LOADPLUS to remove the fractional portion, including the decimal point, of the value that it loads.

• Unique values that are assigned to rows in an identity column might change when the following conditions exist:

  • You have defined an identity column in your table with GENERATED ALWAYS.
  • The table is dropped and re-created, and the values are reloaded. To have LOADPLUS preserve the existing values in the identity column, simply run Analysis and Execution.

  By default, the ALUIN input stream includes the NOREGENIDENTITY keyword. NOREGENIDENTITY instructs Analysis not to allow Db2 to generate new values for an identity column that is defined as GENERATED ALWAYS if the table is dropped and re-created. In addition, Analysis adds the IDENTITYOVERRIDE YES and UPDATEMAXA YES parameters to the -BMCL (LOADPLUS) worklist command. IDENTITYOVERRIDE YES tells LOADPLUS to load identity column values from the input file. UPDATEMAXA YES tells LOADPLUS to update the MAXASSIGNEDVAL column of SYSIBM.SYSSEQUENCES when loading identity column values from an input file.

For more information about LOADPLUS, see LOADPLUS for DB2 documentation.