Limited supportBMC 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 UNLOAD PLUS for DB2 13.1.

Example 6 — Unloading a full image copy

This example unloads from a full image copy data set that is registered in the Db2 catalog.

The SYSPRINT shows that UNLOAD PLUS generates Db2 LOAD control cards by default (because the job does not include a CNTLCARDS option, but the JCL includes a SYSCNTL DD statement).

Message 50041I indicates the status of zIIP processing. For this example, the ZIIP installation option is ENABLED, but an XBM subsystem was not specified. UNLOAD PLUS automatically located an available XBM subsystem to provide zIIP processing.

Important

For an example of unloading an image copy other than the most recent one, see Example 10 — Unloading from incremental image copies. Although example 10 illustrates unloading an incremental image copy, the negative integer in the command option applies to either type of image copy.

The following table describes the key command options for this job:

Command options used in JCL

Description

INFILE IMAGECOPY

Unloads the most recent full image copy data set that UNLOAD PLUS finds in the SYSIBM.SYSCOPY table

TSFMT

Overrides the default Db2 external timestamp format and is useful for porting unloaded data to other relational databases

DATEFMT

Overrides the default Db2 external data format and also overrides your Db2 date exit routine

You can use this option to port unloaded data to other relational databases

PART 3

Designates the partitions to unload

SELECT *

Unloads all columns

FROM

Names the table to unload

WHERE

Qualifies the data to unload

ORDER BY

Sorts output records by the specified fields

Important

To order by data-sorting key, use the ORDER YES statement as shown in example 2.

The following figure shows the JCL for example 6:

Example

//        JOB
//UNLOAD06 EXEC PGM=ADUUMAIN,REGION=0M,
//         PARM=(DEHJ,'ADUXM06','NEW ',,'MSGLEVEL(1)')
//*********************************************************************
//STEPLIB  DD DISP=SHR,DSN=<product.libraries>
//         DD DISP=SHR,DSN=DB2.DSNEXIT
//         DD DISP=SHR,DSN=DB2.DSNLOAD
//SYSIN    DD   *
*
 UNLOAD INFILE IMAGECOPY
        TSFMT('MM.DD.YY/HH:MM:SS.NN')
        DATEFMT('MM.DD.YY')
        PART 3
        SELECT *
           FROM BMC.EMPLS WHERE SSN > '200000000'
             ORDER BY HIRE_DATE DESC, EMPL_LNAME
*
//SYSREC   DD   DSN=ADU.EXAMPLE6.SYSREC,
//         DISP=(,CATLG),SPACE=(TRK,(1,1)),UNIT=SYSDA
//SYSCNTL  DD   DSN=ADU.EXAMPLE6.SYSCNTL,
//         DISP=(,CATLG),SPACE=(TRK,(1,1)),UNIT=SYSDA
//UTPRINT  DD   SYSOUT=*
//SYSPRINT DD   SYSOUT=*
//SORTWK01 DD   DISP=(,DELETE),UNIT=SYSDA,SPACE=(CYL,(1,1))
//SORTWK02 DD   DISP=(,DELETE),UNIT=SYSDA,SPACE=(CYL,(1,1))
//SORTWK03 DD   DISP=(,DELETE),UNIT=SYSDA,SPACE=(CYL,(1,1))
//SORTWK04 DD   DISP=(,DELETE),UNIT=SYSDA,SPACE=(CYL,(1,1))

SYSPRINT for example 6

The following figure shows the SYSPRINT for example 6:

Example

 *****  B M C   U N L O A D   P L U S   F O R   D B 2    V11R1.00 *****
                                             (C) COPYRIGHT 1991 - 2013 BMC SOFTWARE, INC.
 BMC50001I UTILITY EXECUTION STARTING   10/28/2013   13:03:59 ...
 BMC50002I UTILITY ID = 'ADUXM06'.  DB2 SUBSYSTEM ID = 'DEHJ'.  OPTION MODULE = 'ADU$OPTS'.
 BMC50471I z/OS 2.1.0,PID=HBB7790,DFSMS FOR Z/OS=2.1.0,DB2=11.1.0
 BMC50471I REGION=0M,BELOW 16M=8832K,ABOVE 16M=1413268K,IEFUSI=NO,CPUS=3
 BMC50471I MEMLIMIT=17592186040320M,AVAILABLE=17592186040311M,MEMLIMIT SET BY:REGION=0

 BMC50471I UNLOAD PLUS FOR DB2--V11.01.00
 BMC50471I   NO MAINTENANCE TO REPORT
 BMC50471I DB2 UTILITIES COMMON CODE--V11.01.00
 BMC50471I   NO MAINTENANCE TO REPORT
 BMC50471I SOLUTION COMMON CODE--V11.01.00
 BMC50471I   MAINT: BPJ0661  BPJ0667  BPJ0671  BPJ0674  BPJ0675  BPJ0676  BPJ0682  BPJ0686  BPJ0689  BPJ0697
 BMC50471I BMCSORT ENGINE--V02.04.01
 BMC50471I   MAINT: BPJ0691
 BMC50471I BMC STATS API--V11.01.00
 BMC50471I   MAINT: BPU5409  BPU5534  BPU5674
 BMC50471I EXTENDED BUFFER MANAGER--V06.01.00
 BMC50471I   MAINT: BPE0401  BPE0403  BPE0405  BPE0407  BPE0410  BPE0412  BPE0416

 BMC50471I ANALYZE=(DB2STATS,NOLIMIT)              FORCE_RPT=NO                            SMAX=16
 BMC50471I CENTURY=(1950,2049)                     FORCE=NONE                              SMCORE=(0K,0K)
 BMC50471I CHANGE_CONSISTENT=NO                    HISTORY=YES                             SORTNUM=32
 BMC50471I CHANGE_QUIESCE=NO                       IBUFFS=25                               SQLDELAY=3
 BMC50471I CMAX=16                                 INLINE=NO                               SQLRETRY=100
 BMC50471I CMRATIO=50                              LOADDECP=NO                             TAPEDISP=DELETE
 BMC50471I CONSTRULES=BMC                          LOCKROW=YES                             TASKMAX=200%
 BMC50471I CURRENTDEGREE=NONE                      MAXP=5                                  UBUFFS=25
 BMC50471I DELFILES=YES                            MSGLEVEL=1                              UNLDMAX=200%
 BMC50471I DRNDELAY=1                              NULLCHAR=X'6F'                          UNLOADDN_ACTIVE=(YES,NO)
 BMC50471I DRNRETRY=255                            NULLTYPE=T1                             UNLOADDN=(SYSREC,SYSRED)
 BMC50471I DRNWAIT=NONE                            OPNDB2ID=YES                            USELRECL=NO
 BMC50471I DSPLOCKS=DRNFAIL                        PLAN=ADUQA                              UXSTATE=SUP
 BMC50471I EXCLDUMP=(X37,X22,X06)                  RECFM=AUTO                              WORKUNIT=SYSALLDA
 BMC50471I FILEREFDN=SYSREF                        ROWSETSZ=100                            ZIIP=ENABLED
 BMC50471I FILL=NO                                 SDUMP=YES                               ZONEDDECOVP=(C,D)
 BMC50471I FORCE_AT=(START,3)                      SHRLEVEL=REFERENCE


 BMC50471I TAPES=NONE

 BMC50470I OUTPUT   = SYSREC                             SYSRED                               SYSREF
 BMC50470I UNIT     = SYSALLDA                           SYSALLDA                             SYSALLDA
 BMC50470I VOLCNT   = 25                                 25                                   25
 BMC50470I GDGLIMIT = 5                                  5                                    5
 BMC50470I GDGEMPTY = NO                                 NO                                   NO
 BMC50470I GDGSCRAT = NO                                 NO                                   NO
 BMC50470I STORCLAS = NONE                               NONE                                 NONE
 BMC50470I DATACLAS = NONE                               NONE                                 NONE
 BMC50470I MGMTCLAS = NONE                               NONE                                 NONE
 BMC50470I UNITCNT  = 0                                  0                                    0
 BMC50470I SPACE    = CYL                                CYL                                  CYL
 BMC50470I PCTPRIM  = AUTO                               AUTO                                 AUTO
 BMC50470I MAXPRIM  = 0                                  0                                    0
 BMC50470I MAXSECD  = 0                                  0                                    0
 BMC50470I FILESZPCT  100                                100                                  100
 BMC50470I NBRSECD  = AUTO                               AUTO                                 AUTO
 BMC50470I DISKRETN = NONE                               NONE                                 NONE
 BMC50470I DISKEXPD = NONE                               NONE                                 NONE
 BMC50470I RETPD    = NONE                               NONE                                 NONE
 BMC50470I EXPDT    = 99000                              99000                                99000
 BMC50470I TRTCH    = NONE                               NONE                                 NONE
 BMC50470I DSNTYPE  = NONE                               NONE                                 PDS
 BMC50483I SYSREC     VOLUMES=NONE
 BMC50483I SYSRED     VOLUMES=NONE
 BMC50483I SYSREF     VOLUMES=NONE

 BMC50483I SYSREC     DSNAME=&USERID.&TYPE.S&SELNUM
 BMC50483I SYSRED     DSNAME=&USERID.&TYPE.S&SELNUM
 BMC50483I SYSREF     DSNAME=&USERID.BMC


 BMC50471I DB2 DSNHDECP MODULE SETTINGS:
 BMC50471I VERSION                 = 1110
 BMC50471I SUBSYSTEM DEFAULT       = DEHJ
 BMC50471I CHARACTER SET           = ALPHANUM
 BMC50471I DATE FORMAT             = USA
 BMC50471I TIME FORMAT             = USA
 BMC50471I LOCAL DATE LENGTH       = 0
 BMC50471I LOCAL TIME LENGTH       = 0
 BMC50471I DECIMAL POINT           = PERIOD
 BMC50471I DECIMAL ARITHMETIC      = 15
 BMC50471I DELIMITER               = DEFAULT
 BMC50471I SQL DELIMITER           = DEFAULT
 BMC50471I ENCODING SCHEME         = EBCDIC
 BMC50471I APPL. ENCODING SCHEME   = EBCDIC
 BMC50471I MIXED                   = NO
 BMC50471I EBCDIC CCSID            = (37,65534,65534)
 BMC50471I ASCII CCSID             = (819,65534,65534)
 BMC50471I UNICODE CCSID           = (367,1208,1200)
 BMC50471I IMPLICIT TIME ZONE      = CURRENT (-05:00)

 BMC50028I DB2 MODE = NFM
 BMC50471I BMC_BMCUTIL       ='BMCUTIL.CMN_BMCUTIL'
 BMC50471I BMC_BMCSYNC       ='BMCUTIL.CMN_BMCSYNC'
 BMC50471I BMC_BMCHIST       ='BMCUTIL.CMN_BMCHIST'
 BMC50471I BMC_BMCXCOPY      ='BMCUTIL.CMN_BMCXCOPY'


 BMC50102I *
 BMC50102I  UNLOAD INFILE IMAGECOPY
 BMC50102I         TSFMT('MM.DD.YY/HH:MM:SS.NN')
 BMC50102I         DATEFMT('MM.DD.YY')
 BMC50102I         PART 3
 BMC50102I         SELECT *
 BMC50102I            FROM BMC.EMPLS WHERE SSN > '200000000'
 BMC50102I              ORDER BY HIRE_DATE DESC, EMPL_LNAME
 BMC50102I *

 BMC51654I DIRECT YES IN EFFECT


 BMC51655I LOCATING FULL IMAGE COPIES
 BMC51624I FOR PARTITION ALL , IMAGE COPY 'ADU.QA.ICOPY.FULL1.BMCEXATS'(2013-10-28-13.03.43.921986) WILL BE USED

 BMC51689I ESTIMATED ROWS FOR SELECT 1 IS 0

 BMC50004I UTILINIT PHASE COMPLETE.  ELAPSED TIME = 00:00:00

 BMC51639I FOR DDNAME 'SYSREC' DSN=ADU.EXAMPLE6.SYSREC,DCB=(RECFM=VB,BLKSIZE=27993,LRECL=103)
 BMC50041I 0: ZIIP  ENABLED (0) USING XBM SUBSYSTEM XBMB
 BMC50474I BELOW 16M = 8492K, ABOVE 16M = 1406268K, CPUS = 3
 BMC51701I MAX TASKS = 1, MAX PARTITIONS PER TASK = 1, SORTWKS PER TASK = 4, MAX OPEN PARTITIONS PER TASK = 1
 BMC50476I DDNAME = SYS00002, I/OS = 1, I/O WAITS = 1, RDB LOCK WAITS = 0
 BMC50477I 1: PARTITION =    3, ROWS/KEYS = 5, I/O WAITS = 0 ,DDNAME = SYS00002
 BMC50478I 1: RDB LOCK WAITS = 0
 BMC50476I DDNAME = SYSREC, I/OS = 1, I/O WAITS = 1, RDB LOCK WAITS = 0
 BMC51671I UNLOAD STATISTICS:  4 ROWS PROCESSED FROM PARTITION 3
 BMC51686I UNLOADING OF DATASET 'ADU.QA.ICOPY.FULL1.BMCEXATS' READ 3 PAGES
 BMC51672I UNLOAD STATISTICS:  4 ROWS PROCESSED FROM SPACE 'BMCEXDB.BMCEXATS', 1 NOT SELECTED, 0 DISCARDED
 BMC51674I UNLOAD STATISTICS:  4 RECORDS WRITTEN TO DDNAME 'SYSREC'
 BMC51675I UNLOAD STATISTICS:  0 RECORDS DISCARDED DUE TO ERRORS
 BMC50041I 0: ZIIP NOT ENABLED (0) USING XBM SUBSYSTEM XBMB
 BMC50004I UNLOAD PHASE COMPLETE.  ELAPSED TIME = 00:00:00


 BMC51639I FOR DDNAME 'SYSCNTL' DSN=ADU.EXAMPLE6.SYSCNTL,DCB=(RECFM=FB,BLKSIZE=3120,LRECL=80)
 BMC51801I LOAD TABLE STATEMENTS WRITTEN TO DDNAME 'SYSCNTL'

 BMC51810I LOAD DATA INDDN SYSREC
 BMC51940I      EBCDIC CCSID(37,65534,65534)
 BMC51811I   INTO TABLE
 BMC51809I BMC.EMPLS
 BMC51813I (EMPL_ID POSITION(1:3) DECIMAL
 BMC51815I ,SSN POSITION(4:12) CHAR  (9)
 BMC51815I ,HIRE_DATE POSITION(13:20) DATE  EXTERNAL(8)
 BMC51814I       NULLIF BMC_NULL1=X'6F'
 BMC51819I     ,     BMC_NULL1 POSITION(21) CHAR(1)
 BMC51815I ,DEPT_NO POSITION(22:25) CHAR  (4)
 BMC51813I ,JOB_CODE POSITION(26:27) SMALLINT
 BMC51814I       NULLIF BMC_NULL2=X'6F'
 BMC51819I     ,     BMC_NULL2 POSITION(28) CHAR(1)
 BMC51815I ,SALARY_CODE POSITION(29:29) CHAR  (1)
 BMC51813I ,SALARY_HRLY POSITION(30:34) DECIMAL
 BMC51814I       NULLIF BMC_NULL3=X'6F'
 BMC51819I     ,     BMC_NULL3 POSITION(35) CHAR(1)
 BMC51815I ,PART_401K POSITION(36:36) CHAR  (1)
 BMC51814I       NULLIF BMC_NULL4=X'6F'
 BMC51819I     ,     BMC_NULL4 POSITION(37) CHAR(1)
 BMC51813I ,EMPL_LNAME POSITION(38:*) VARCHAR
 BMC51813I ,EMPL_FNAME POSITION(*:*) VARCHAR
 BMC51813I ,EMPL_MNAME POSITION(*:*) VARCHAR
 BMC51814I       NULLIF BMC_NULL5=X'6F'
 BMC51819I     ,     BMC_NULL5 POSITION(*) CHAR(1)
 BMC51815I ,REC_CREATED POSITION(*:*+19) TIMESTAMP  EXTERNAL(19)
 BMC51809I )

 BMC50476I DDNAME = SYSCNTL, I/OS = 1, I/O WAITS = 1, RDB LOCK WAITS = 0
 BMC50006I UTILITY EXECUTION COMPLETE, RETURN CODE = 0


Related topic

Examples-of-UNLOAD-PLUS-jobs

 

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