Skip to Content
Information
Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Apptune for Db2 13.1.

Loading data into Db2 tables

Use the DB2LOAD DD statement to show where the data is to be written.

Warning

Note: Future deprecation of PSSBLOAD and LOADxxx reports

BMC plans to deprecate the PSSBLOAD utility and the usage of DB2LOAD DD statement for LOADxxx and other reports in the next release of Apptune. As an alternative, consider using the BMC Performance Advisor Database component. Performance Advisor Database is a part of the following solutions:

  • SQL Performance for DB2
  • BMC AMI SQL Performance for Db2

To load data into Db2 tables

  1. Run the DOMBRPT1 utility specifying the DB2LOAD parameter in the REPORT statement.

  2. Run the DOMBLOD1 utility to produce the CREATE TABLE DDL and Load utility control statements for the tables. Input to DOMBLOD1 is the DB2LOAD data set written by DOMBRPT1. The resulting output consists of two data sets:

    • LOADDEF—This data set contains the Data Definition Language (DDL) that is required to create the Db2 table or tables.
    • LOADCTL—This data set contains Load utility control statements that are required to load the data into a table or tables.

    Both output data sets have a fixed or fixed block record format with 80-byte records.

    //DOMLOAD JOB (ACCT),'DB2LOAD',        <== MODIFY
    //            MSGCLASS=X,              <== CHECK
    //            CLASS=A                  <== CHECK
    //*
    //**********************************************************************
    //*  CREATE DDL AND LOAD STMTS TO LOAD DB2 TABLES WITH BMCSFTWR DATA   *
    //**********************************************************************
    //*                          INSTRUCTIONS
    //*
    //*--> CHANGE: ?BMC-HLQ?  = AMFORDB2 HIGH LEVEL QUALIFIER
    //*            ?REPORT?   = NAME OF AMFORDB2 REPORT TO RUN
    //*                         LOAD307  : GENERATE APPTUNE IFCID 307-SQLSTM
    //*                         LOAD307  : GENERATE APPTUNE IFCID 008-SQLOBJ
    //*            ?TRACEDSN? = NAME OF DATA SET CONTAINING THE SMF,
    //*                         GTF, OR AMFORDB2 TRACE DATA
    //*            ?MYHLQ?    = HIGH LEVEL QUALIFIER OF DATA SET WHERE
    //*                         THE DDL AND LOAD STMTS WILL BE SAVED
    //*            ?DUNIT?    = UNIT TYPE OF ?MYHLQ? DATA SET
    //*            ?DVOL?     = VOLSER OF ?MYHLQ? DATA SET
    //*
    //*   STEP # 1: - GENERATE DATA FOR STEP 2
    //*   STEP # 2: - GENERATE DDL AND LOAD STATEMENTS
    //*
    //*   AFTER THE DDL HAS BEEN GENERATED, YOU MUST USE THE DDL TO CREATE
    //*   THE TABLE. THIS CAN BE DONE THROUGH SPUFI. BEFORE CREATING THE
    //*   TABLE, YOU SHOULD REVIEW THE DDL TO DETERMINE IF MORE THAN ONE
    //*   TABLE IS BEING CREATED. IF MORE THAN ONE TABLE IS BEING CREATED,
    //*   YOU MUST EDIT THE DDL SO THAT ALL THE TABLES ARE CREATED IN THE
    //*   SAME TABLESPACE.
    //*
    //*   YOU SHOULD ALSO REVIEW THE LOAD STATEMENTS TO VERIFY THEY ARE
    //*   CONSISTENT WITH ANY CHANGES YOU MAY HAVE HAD TO MAKE TO THE
    //*   DDL. THE LOAD STATEMENTS ARE USED AS INPUT TO THE #DOMLOAD JOB.
    //*
    //**********************************************************************
    //*
    //GENDATA  EXEC PGM=DOMBRPT1,REGION=4M
    //STEPLIB  DD  DISP=SHR,DSN=?BMC-HLQ?.LOAD
    //SYSPRINT DD  SYSOUT=*
    //REPORT   DD  SYSOUT=*
    //TRACEIN  DD  DISP=SHR,DSN=?TRACEDSN?
    //*
    //TRACEWRK DD DISP=(NEW,PASS),         ** STAGED SORT OUTPUT.  IF   **
    //            UNIT=WORK,               ** DD DUMMY, REPORT OUTPUT   **
    //            SPACE=(CYL,(5,5),RLSE),  ** WILL NOT BE PRODUCED      **
    //            DCB=(RECFM=VBS,LRECL=32760,BLKSIZE=8192)//*
    //DB2LOAD  DD DISP=(NEW,PASS),
    //            UNIT=WORK,DSN=&&DB2LOAD,
    //            SPACE=(CYL,(5,5),RLSE),
    //            DCB=(RECFM=VB,LRECL=32752,BLKSIZE=32756)
    //*
    //* SORT WORK FILES - ADJUST SIZE RELATIVE TO THE AMOUNT OF INPUT DATA
    //*
    //SORTWK01 DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
    //SORTWK02 DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
    //SORTWK03 DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
    //SORTWK04 DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
    //*
    //SYSIN    DD  *
    REPORT(NAME(?REPORT?) DB2LOAD)
    /*
    //*
    //*   GENERATE THE DDL AND LOAD STMTS THAT CAN BE USED TO CREATE
    //*   THE DB2 TABLES AND LOAD THE DATA
    //*
    //GENCTL EXEC  PGM=DOMBLOD1,COND=(4,LT)
    //STEPLIB  DD  DISP=SHR,DSN=?BMC-HLQ?.LOAD
    //DB2LOAD  DD  DISP=(SHR,PASS),DSN=&&DB2LOAD
    //LOADDEF  DD  DISP=(NEW,CATLG),DSN=?MYHLQ?.DDL,
    //             UNIT=?DUNIT?,SPACE=(TRK,(2,2),RLSE),
    //             VOL=SER=?DVOL?,
    //             DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
    //LOADCTL  DD  DISP=(NEW,CATLG),DSN=?MYHLQ?.CTL,
    //             UNIT=?DUNIT?,SPACE=(TRK,(2,2),RLSE),
    //             VOL=SER=?DVOL?,
    //             DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)

  3. Use the LOADDEF DDL that is generated by DOMBLOD1 to create the Db2 tables. Before defining the tables, check the column names generated by DOMBLOD1. The column names are taken from item names and formula numbers. You might want to change the column names to something more meaningful.

    Warning

    Note

    Use the recommended LOAD* reports to avoid generating duplicate column names. Duplicate column names cause an SQL error. See the table in DB2LOAD-statement-and-parameter for a list of the LOAD* reports.

  4. Use the DB2LOAD data generated by DOMBRPT1 and the LOADCTL control statements that are generated by DOMBLOD1 to load the table with data from the report. If you change column names, you must make corresponding changes in the LOADCTL control statements.

  5. For subsequent runs of a report, you can use the sample JCL provided in member #DOMLOAD of the hlq.llqSAMP data set (see Loading-Apptune-data-into-Db2-tables).This job runs the batch report specifying DB2LOAD and invokes the Db2 Load utility to load the table. Use the LOADCTL data set generated by DOMBLOD1 for the //SYSIN DD statement of the Db2 Load utility.

    //DOMLOAD JOB (ACCT),'DB2LOAD',        <== MODIFY
    //            MSGCLASS=X,              <== CHECK
    //            class="A"                  <== CHECK
    //*
    //**********************************************************************
    //*                  LOAD DATA INTO DB2 TABLES                         *
    //**********************************************************************
    //*                          INSTRUCTIONS
    //*
    //*--> CHANGE: ?DB2?      = NAME OF DB2 SYSTEM TO CONTAIN DATA
    //*            ?BMC-HLQ?  = DATA COLLECTOR HIGH LEVEL QUALIFIER
    //*            ?DB2HILEV? = DB2 HIGH LEVEL QUALIFIER
    //*            ?MYHLQ?    = HIGH LEVEL QUALIFIER OF LOAD STMTS
    //*            ?REPORT?   = NAME OF BMCSFTWR REPORT TO RUN
    //*                         LOAD307  : GENERATE APPTUNE IFCID 307-SQLSTM
    //*                         LOAD307  : GENERATE APPTUNE IFCID 008-SQLOBJ
    //*            ?TRACEDSN? = NAME OF DATA SET CONTAINING THE SMF,
    //*                         GTF, OR BMCSFTWR TRACE DATA
    //*
    //*            GENERATE OUTPUT DATA FOR SUBSEQUENT LOAD
    //*
    //GENDATA  EXEC PGM=DOMBRPT1,REGION=4M
    //STEPLIB  DD  DISP=SHR,DSN=?BMC-HLQ?.LOAD
    //SYSPRINT DD  SYSOUT=*
    //REPORT   DD  SYSOUT=*
    //TRACEIN  DD  DISP=SHR,DSN=?TRACEDSN?
    //*
    //TRACEWRK DD DISP=(NEW,PASS),         ** STAGED SORT OUTPUT.  IF   **
    //            UNIT=WORK,               ** DD DUMMY, REPORT OUTPUT   **
    //            SPACE=(CYL,(5,5),RLSE),  ** WILL NOT BE PRODUCED      **
    //            DCB=(RECFM=VBS,LRECL=32760,BLKSIZE=8192)
    //*
    //DB2LOAD  DD DISP=(NEW,PASS),       ** DB2 LOAD-FORMAT DATA TO BE  **
    //            UNIT=WORK,DSN=&&DB2LOAD, ** PASSED AS INPUT TO THE DB2 **
    //            SPACE=(CYL,(5,5),RLSE),   ** LOAD UTILITY (#DOMLOAD)  **
    //            DCB=(RECFM=VB,LRECL=32752,BLKSIZE=32756)
    //*
    //* SORT WORK FILES - ADJUST SIZE RELATIVE TO THE AMOUNT OF INPUT DATA
    //*
    //SORTWK01 DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
    //SORTWK02 DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
    //SORTWK03 DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
    //SORTWK04 DD DISP=(NEW,PASS),UNIT=WORK,SPACE=(CYL,(10,10),RLSE)
    //*
    //SYSIN    DD  *
    REPORT(NAME(?REPORT?) DB2LOAD)
    /*
    //*                     LOAD DATA TO DB2 TABLES
    //*
    //LSTATS EXEC  PGM=DSNUTILB,PARM='?DB2?,BMC',COND=(4,LT)
    //STEPLIB  DD  DISP=SHR,DSN=?DB2HILEV?.DSNEXIT
    //         DD  DISP=SHR,DSN=?DB2HILEV?.DSNLOAD
    //SYSOUT   DD  SYSOUT=*
    //SYSERR   DD  DISP=(NEW,PASS),DSN=&&DISC,UNIT=SYSDA,SPACE=(CYL,(1,1))
    //SYSDISC  DD  DISP=(NEW,PASS),DSN=&&DISC,UNIT=SYSDA,SPACE=(CYL,(1,1))
    //SYSPRINT DD  SYSOUT=*
    //UTPRINT  DD  SYSOUT=*
    //SYSREC   DD  DISP=(SHR,PASS),DSN=&&DB2LOAD         ** DB2LOAD DATA **
    //SYSUT1   DD  DISP=(NEW,DELETE),UNIT=WORK,
    //             SPACE=(CYL,(10,10))
    //SYSIN    DD  DISP=SHR,DSN=?MYHLQ?.CTL              ** DB2LOAD CTL  **
    //*

Related topic

Other-batch-utilities



 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI Apptune for Db2 12.1