-LOAD (IBM LOAD utility)

The load statement includes the unload data set identifier that is created for the unload command. This command and its parameters are passed through to Db2. The definitions are ordered by the new column sequence and refer to the new column names. New columns with NULL=YES or DEFAULT=YES are omitted.

You can run the LOAD utility to perform the following functions:

• Load data in XML and LOB columns
• Migrate tables with ROWID columns
• Migrate only data

After a LOAD RESUME fails, an SQL DELETE statement executes before the LOAD restarts. The SQL statement cleans up any data that is left in the table by the failed LOAD attempt. 

When you create a data-only migrate worklist, the Analysis component generates and groups all of the -SQL DELETE commands before the first LOAD command. These commands appear in phase 2 of the worklist, which is indicated by the -MIGR PHASE-2 command.

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

The IBM LOAD utility can be used to load data contained in LOB and XML columns that the IBM UNLOAD utility unloaded.

The data set that the SYRC TEMPLATE descriptor references contains all of the data and a reference to a file name for each LOB or XML column in the table. The following figure shows an example of a -LOAD command that loads XML data.

Analysis generates the IGNOREFIELDS YES parameter for the LOAD statement. This parameter allows the NULL nnn column to be generated, but not referenced. The column list does not include the DB_GENERATED_DOCID_FOR_XML column.

Use of -LOAD 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 -LOAD 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 (see the following figure) when you are migrating only data from:

  • A single table at the table level of a segmented table space
  • The second and succeeding tables of multiple tables at the table space 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.

  -LOAD 000600 DELETE FROM
       J1ALLN1.T_T03MSEG_A
  
    TEMPLATE SYRC
        DSN 'RDAJZB4.DEGA.JB11128B.SR000002'
        UNIT SYSDA
  
               LOAD DATA  RESUME YES
               INDDN SYRC
               ERRDDN SYSER001
               DISCARDS 1
               DISCARDDN SYSDS001
               EBCDIC CCSID(37,0,0)
               WORKDDN SYSUT001
               LOG NO
               ENFORCE NO
               INTO TABLE J1ALLN1.T_T03MSEG_A
               IGNOREFIELDS YES
   (
               NULL001             POSITION(*) CHAR(1),
    COLC_0
      POSITION(*) CHAR(20)
                           NULLIF NULL001=X'FF',
               NULL002             POSITION(*) CHAR(1),
    COLBI_1
      POSITION(*) BIGINT
                           NULLIF NULL002=X'FF',
               NULL003             POSITION(*) CHAR(1),
    COLBN_2
      POSITION(*) BINARY
                           NULLIF NULL003=X'FF',
               NULL004             POSITION(*) CHAR(1),
    COLBN_3
      POSITION(*) BINARY
                           NULLIF NULL004=X'FF',
               NULL005             POSITION(*) CHAR(1),
    COLVB_4
      POSITION(*) VARBINARY
                           NULLIF NULL005=X'FF',
               NULL006             POSITION(*) CHAR(1),
    COLC_5
      POSITION(*) CHAR(4)
                           NULLIF NULL006=X'FF',
               NULL007             POSITION(*) CHAR(1),
    COVC_6
      POSITION(*) VARCHAR
                           NULLIF NULL007=X'FF',
               NULL008             POSITION(*) CHAR(1),
    COLC_7
      POSITION(*) CHAR(8)
                           NULLIF NULL008=X'FF',
               NULL009             POSITION(*) CHAR(1),
    COLC_8
      POSITION(*) CHAR(8)
                           NULLIF NULL009=X'FF',
               NULL010             POSITION(*) CHAR(1),
    COLVC_9
      POSITION(*) VARCHAR
                           NULLIF NULL010=X'FF',
    COLTS_10
      POSITION(*) TIMESTAMP EXTERNAL(32))
  
  -SYNC 000650 LOAD TB J1ALLN1.T_T03MSEG_A COMPLETE
  -SYNC 000700 END OF LOAD TABLES SECTION

• LOAD DATA REPLACE INTO TABLE

  Analysis uses this syntax (the following figure) when you are migrating only data from:

  • A single table at the table space level of a segmented table space
  • The first table of multiple tables at the table space level of a segmented table space
  • 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.

  -LOAD 000450
    TEMPLATE SYRC
        DSN 'RDAJZB4.DEGA.JB11128A.SR000001'
        UNIT SYSDA
  
               LOAD DATA  REPLACE
               INDDN SYRC
               ERRDDN SYSER001
               DISCARDS 1
               DISCARDDN SYSDS001
               EBCDIC CCSID(37,0,0)
               WORKDDN SYSUT001
               LOG NO
               ENFORCE NO
               REUSE
               INTO TABLE J1ALLY1.T_T03MSEG_A
               IGNOREFIELDS YES
   (
               NULL001             POSITION(*) CHAR(1),
    COLC_0
      POSITION(*) CHAR(20)
                           NULLIF NULL001=X'FF',
               NULL002             POSITION(*) CHAR(1),
    COLBI_1
      POSITION(*) BIGINT
                           NULLIF NULL002=X'FF',
               NULL003             POSITION(*) CHAR(1),
    COLBN_2
      POSITION(*) BINARY
                           NULLIF NULL003=X'FF',
               NULL004             POSITION(*) CHAR(1),
    COLBN_3
      POSITION(*) BINARY
                           NULLIF NULL004=X'FF',
               NULL005             POSITION(*) CHAR(1),
    COLVB_4
      POSITION(*) VARBINARY
                           NULLIF NULL005=X'FF',
               NULL006             POSITION(*) CHAR(1),
    COLC_5
      POSITION(*) CHAR(4)
                           NULLIF NULL006=X'FF',
               NULL007             POSITION(*) CHAR(1),
    COVC_6
      POSITION(*) VARCHAR
                           NULLIF NULL007=X'FF',
               NULL008             POSITION(*) CHAR(1),
    COLC_7
      POSITION(*) CHAR(8)
                           NULLIF NULL008=X'FF',
               NULL009             POSITION(*) CHAR(1),
    COLC_8
      POSITION(*) CHAR(8)
                           NULLIF NULL009=X'FF',
               NULL010             POSITION(*) CHAR(1),
    COLVC_9
      POSITION(*) VARCHAR
                           NULLIF NULL010=X'FF',
    COLTS_10
      POSITION(*) TIMESTAMP EXTERNAL(32))
  
  -SYNC 000500 LOAD TB J1ALLY1.T_T03MSEG_A COMPLETE
  -SYNC 000550 END OF LOAD TABLES SECTION

Notes for -LOAD

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

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

• A SYSMAP data set is always generated by JCL Generation if the worklist contains -LOAD.
• If a table space is being created in the worklist, Analysis includes the REUSE parameter in the -LOAD command.
• If you are using a tape data set for SYSUT with IBM LOAD, you must manually edit the worklist and JCL to ensure that multiple instances of IBM LOAD use unique work data sets and ddname for SYSUT.

For more information, see the IBM documentation.