-BMCD (BMC UNLOAD PLUS utility)

The -BMCD command invokes the UNLOAD PLUS utility.

The utility runs as a subtask. The -BMCD command can be processed in parallel. For more information about the worklist parallelism feature of the BMC Database Administration for DB2 and BMC AMI Database Administration for Db2 solutions, see Using-worklist-parallelism.

You can run the UNLOAD PLUS utility to perform the following functions:

Dynamically allocate unload data sets
Use fast unload
Unload from image copies
Use multitasking
Unload converted data
Migrate tables with ROWID columns
Unload data in XML and LOB columns

For more information, see UNLOAD PLUS for DB2.

Use of -BMCD to dynamically allocate unload data sets

When you specify to use the UNLOAD PLUS and LOADPLUS utilities, the utilities can dynamically allocate the unload data sets.

When Analysis generates the -BMCD command, it specifies an OUTPUT descriptor for primary data sets. The DSNAME parameter in the descriptor specifies the fully qualified data set name for the object that is unloaded.

Example of -BMCD command—dynamic allocation of data sets

-BMCD 000400
             UNLOAD UNLOADDN (SYSREC01)
             ACTIVE (YES,NO)
             ON FAILURE ALL TERMINATE UTILITY
             DELETEFILES YES
             DISCARDS 1
             NULLTYPE T1 NULLCHAR ?
             SELECT *
             FROM   ACMU02.T_U24PV
                    ORDER BY COLUMN_1
            ;
    OUTPUT SYSREC01
      UNIT SYSDA
    DSNAME 'RDAJZB3.DUNLDA4.ACMU02.U24PV.P'

The data set name that is used for an object for both UNLOAD PLUS and LOADPLUS should be the same in the worklist. Do not modify the name of the data set in the DSNAME parameter for an object without modifying the name of the corresponding data set in the INDSN parameter for the object.

When you use the UNLOAD PLUS utility to dynamically allocate unload data sets, Analysis generates the following parameters for the -BMCD command:

ON FAILURE ALL TERMINATE UTILITY
DELETEFILES YES

If UNLOAD PLUS cannot unload the table, these parameters cause UNLOAD PLUS to delete the dynamically allocated unload (SYSREC) data set.

Use of -BMCD to perform a fast unload

When 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 BMCLOAD parameter specifies to move the data, as shown below.

Example of -BMCD command—FORMAT BMCLOAD

-BMCD 000350
              UNLOAD UNLOADDN (SYSR1001)
              ACTIVE (YES,NO)
              DISCARDS 1
              NULLTYPE T1 NULLCHAR ?
              FORMAT BMCLOAD
              SELECT *
              FROM   ACMU02.T_U20NV
              ;

Note

The fast unload feature requires the CCSID definition of the table space to match the default CCSID definition of the subsystem that is specified in the DSNHDECP load module. If the definitions do not match, the product does not use the feature to unload and load data.

Use of -BMCD to unload from image copies

When you specify to use the UNLOAD PLUS utility, the SHRLEVEL REFERENCE control card specifies to unload from image copies that are marked as SHRLEVEL REFERENCE. (If a SHRLEVEL CHANGE control card was included instead, it would specify to unload from image copies that were marked as SHRLEVEL REFERENCE or SHRLEVEL CHANGE.)

The NULLTYPE T1 NULLCHAR ? control card explicitly specifies the UNLOAD PLUS installation options. See the following example of the -BMCD command with the SHRLEVEL REFERENCE control card and NULLTYPE T1 NULLCHAR ? control card for a single-table table space.

Example of -BMCD command—SHRLEVEL REFERENCE

-BMCD 003200
              UNLOAD UNLOADDN (SYSR1001)
              ACTIVE (YES,NO)
              SHRLEVEL REFERENCE INFILE BMCD1001 FULL
              DISCARDS 1
              NULLTYPE T1 NULLCHAR ?
              SELECT *
              FROM TEST.EMPLS
                   ORDER BY NAME
             ;

Use of -BMCD to perform multitasking

When you specify to use the UNLOAD PLUS and LOADPLUS utilities, the utilities can multitask the unloading and loading of data.

For multitasking to occur, a partitioned table space must be specified. When multitasking is used, the R1001 ddname prefix is used with the UNLOADDN control card, as shown below. The number in the prefix is incremented for each partitioned table space in the worklist. In addition, yyyy is appended to the ddname for each partition in the table space.

Example of -BMCD command—partitioned table space

-BMCD 003200
             UNLOAD UNLOADDN (R1001)
             ACTIVE (YES,NO)
             NULLTYPE T1 NULLCHAR ?
             SELECT *
             FROM TEST.EMPLS
             ORDER BY COL1, COL2
             ;

When you unload and load data in partitioned table spaces that contain over 256 partitions, the product requires multitasking. The product determines the number of dynamically allocated unload (SYSREC) data sets that can be used in a single -BMCD command by the value specified in the MAXSYSREC keyword in the ALUIN input stream. The default value of the MAXSYSREC keyword is 256. Once the product determines the number of data sets, it divides the partitions to be unloaded among several -BMCD commands. For example, if the value of MAXSYSREC is 256 and the number of partitions in the table space is 540, Analysis generates three -BMCD commands:

The first command unloads or loads the first 256 partitions.
The second command unloads or loads the next 256 partitions.
The third command unloads or loads the last 28 partitions.

The IPPARTS parameter in the JCLP command specifies the number of partitions for each -BMCD command.

Example of -BMCD command—multitasking more than 256 partitions

-JCLP 000250 BMCD IPPARTS 256
-BMCD 000300
             UNLOAD UNLOADDN (SYSR)
             ACTIVE (YES,NO)
             ON FAILURE ALL TERMINATE UTILITY
             DELETEFILES YES
             DISCARDS 1
             NULLTYPE T1 NULLCHAR ?
             EBCDIC CCSID(37,0,0)
                    PART       1:256
    SELECT *
    FROM   B91S128A.TB11A
           ORDER BY COL1, COL2
    ;
    OUTPUT SYSR
      UNIT SYSDA
    DSNAME 'RDABKH1.DEBF.BH0514A.SR000001.P&PART'

-JCLP 000350 BMCD IPPARTS 256
-BMCD 000400
             UNLOAD UNLOADDN (SYSR)
             ACTIVE (YES,NO)
             ON FAILURE ALL TERMINATE UTILITY
             DELETEFILES YES
             DISCARDS 1
             NULLTYPE T1 NULLCHAR ?
             EBCDIC CCSID(37,0,0)
                    PART       257:512
    SELECT *
    FROM   B91S128A.TB11A
           ORDER BY COL1, COL2
    ;
    OUTPUT SYSR
      UNIT SYSDA
    DSNAME 'RDABKH1.DEBF.BH0514A.SR000001.P&PART'

-JCLP 000450 BMCD IPPARTS 28
-BMCD 000500
             UNLOAD UNLOADDN (SYSR)
             ACTIVE (YES,NO)
             ON FAILURE ALL TERMINATE UTILITY
             DELETEFILES YES
             DISCARDS 1
             NULLTYPE T1 NULLCHAR ?
             EBCDIC CCSID(37,0,0)
                    PART       513:540
    SELECT *
    FROM   B91S128A.TB11A
           ORDER BY COL1, COL2
    ;
    OUTPUT SYSR
      UNIT SYSDA
    DSNAME 'RDABKH1.DEBF.BH0514A.SR000001.P&PART'

Use of -BMCD to unload converted data

Analysis invokes the UNLOAD PLUS utility twice if the LOADPLUS utility is specified and data conversions are occurring.

In the first invocation, the utility uses a SELECT INTO statement for the columns in which data is converted to ensure that only valid (supported) conversions occur on the load. The data is written to a DUMMY DD; this data is not saved. In the second invocation, the data is unloaded.

Example of -BMCD command—data conversion in a single-table table space

-BMCD 000400
             UNLOAD UNLOADDN (DUMMY)
             ACTIVE (YES,NO)
             NULLTYPE T1 NULLCHAR ?
             SELECT TB3COL1
             INTO   TB3COL1              CHAR(9) TRUNCATE
             FROM   TEST.JFLVERTB3
             ;
-BMCD 000450
             UNLOAD UNLOADDN (BLRP0001)
             ACTIVE (YES,NO)
             NULLTYPE T1 NULLCHAR ?
             SELECT *
             FROM   TEST.JFLVERTB3
             ORDER BY TB3COL1
             ;

Note

If you are restarting a worklist that contains both -BMCD and -UNRC commands, ensure that both commands use identical data set names, because these names are being recorded in a full-recovery baseline. If you must restart -BMCD with a different data set name, modify -UNRC to record the same data set name.

Example of -BMCD command—data conversion in a multi-table table space

-BMCD 000650
             UNLOAD UNLOADDN (DUMMY)
             ACTIVE (YES,NO)
             NULLTYPE T1 NULLCHAR ?
             SELECT TB2COL1
             INTO   TB2COL1              CHAR(9) TRUNCATE
             FROM   TEST.JFLVERTB2
             ;
             SELECT TB1COL1
             INTO   TB1COL1              CHAR(9) TRUNCATE
             FROM   TEST.JFLVERTB1
             ;
-BMCD 000700
             UNLOAD UNLOADDN (SYSR1001)
             ACTIVE (YES,NO)
             NULLTYPE T1 NULLCHAR ?
             SELECT '0001', TB2COL1, TB2COL2
             INTO   BMCCONS_RECORDID   CHAR(4),
                    TB2COL1 ,
                    TB2COL2
             FROM   TEST.JFLVERTB2
                    ORDER BY TB2COL1
             ;
             SELECT '0002', TB1COL1, TB1COL2
             INTO   BMCCONS_RECORDID   CHAR(4),
                    TB1COL1 ,
                    TB1COL2
             FROM   TEST.JFLVERTB1
                    ORDER BY TB1COL1
             ;

Use of -BMCD 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 BMC AMI Change Manager for Db2 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 -BMCD to unload data in XML and LOB columns

When you use the UNLOAD PLUS utility to unload data contained in XML or LOB columns, the utility unloads the data to a file reference output data set; this data set must be dynamically allocated and on DASD.

In addition to the SYRC OUTPUT descriptor, the -BMCD command specifies an OUTPUT descriptor of SYxxnn for file reference data sets that contain LOB or XML data. The xx represents LB for LOB data or XC for XML data, and nn represents a sequential number for each LOB or XML column in the table. Each OUTPUT descriptor includes the directory blocks, and primary and secondary space. Analysis obtains these values from the JCL Generation product options file (POF).

Example of -BMCD command—unloading XML data

-BMCD 000350
                    UNLOAD UNLOADDN (SYSREC01)
                    ACTIVE (YES,NO)
                    ON FAILURE ALL TERMINATE UTILITY
                    DELETEFILES YES
                    DISCARDS 1
                    NULLTYPE T1 NULLCHAR ?
                    EBCDIC CCSID(37,0,0)
SELECT
   TABKEY
, CHAR5
, CHAR10
, DEC15
, DEC15_3
, VAR_CHAR15
, XMLCOL1
, XMLCOL2
, TIMESTAMP
INTO
   TABKEY
, CHAR5
, CHAR10
, DEC15
, DEC15_3
, VAR_CHAR15
, XMLCOL1
   VARCHAR CLOBF SYXC01
, XMLCOL2
   VARCHAR CLOBF SYXC02
, TIMESTAMP
    FROM   C9AXM1.TBMMS03A
     ORDER BY
  TABKEY
             ;

    OUTPUT SYSREC01
      UNIT SYSDA
    DSNAME 'RDACRJ.XML.SYSREC.SR000001'

    OUTPUT SYXC01
      DSNAME 'RDACRJ.DBDC.XML.SR000001.X01'
      UNIT SYSDA
    DIR 250    SPACE(10,2) CYL

    OUTPUT SYXC02
      DSNAME 'RDACRJ.DBDC.XML.SR000001.X02'
      UNIT SYSDA
    DIR 250    SPACE(10,2) CYL

This section contains the following topics: