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 17 — Changing overpunch values in zoned decimal data

In this example, any data that is in zoned decimal format is assigned an overpunch value during the unload process.

UNLOAD PLUS formats zoned decimal values with the system default zone digit, which is ‘C’ for positive values and ‘B’ for negative values. You can use the ZONEDDECOVP option to assign overpunch values to zoned decimal numeric values. These values can be either positive or negative. Use this option when you plan to use the unloaded data with an application that requires a specific zone value that is not the traditional default.

The following table shows two examples of the values that are produced when you use the ZONEDDECOVP option. The first example in this table assigns an overpunch value of (C,D) (which is the default) and the second example assigns an overpunch value of (F,F).

Source

Value

Target

SYSREC value with overpunch C,D (default)

SYSREC value with overpunch F,F

Integer

21111

COL1 DECIMAL ZONED (12)

‘F0F0F0F0F0F0F0F2F1F1F1C1’

‘F0F0F0F0F0F0F0F2F1F1F1F1’

Integer

-21117

COL1 DECIMAL ZONED (12)

‘F0F0F0F0F0F0F0F2F1F1F1D7’

‘F0F0F0F0F0F0F0F2F1F1F1F7’

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.

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

Command options used in JCL

Description

FORMAT EXTERNAL

Tells UNLOAD PLUS to use external format for all output data types

UNLOADDN SYSREC ACTIVE NO

Disables dynamic allocation for the primary unload data set, overriding the default in the installation options module

ZONEDDECOVP (F,F)

Assigns an overpunch value of (F,F) to decimal-zoned numeric values

The overpunch values can be positive or negative. In this example, specifying ZONEDDECOVP (F,F) assigns positive overpunch values for any data that is in zoned decimal format.

FIXEDVARCHAR YES

Tells UNLOAD PLUS to convert output records that would normally be variable length to fixed length by padding variable-length columns with spaces

CNTLCARDS 'REPLACE COPYDDN (SYSCPY1)'

Tells UNLOAD PLUS to generate Db2 LOAD control cards (which is the default) and to add the REPLACE and COPYDDN (SYSCPY1) options to the Db2 LOAD control statement

SELECT *

Unloads all columns

INTO

Specifies names and characteristics of output fields

FROM

Names the table to unload

The following figure shows the JCL for example 17:

//        JOB
//UNLOAD17 EXEC PGM=ADUUMAIN,COND=EVEN,
//             PARM='DEHJ,ADUXM17,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           FORMAT EXTERNAL
    UNLOADDN SYSREC ACTIVE NO
    ZONEDDECOVP(F,F)
    FIXEDVARCHAR YES
    CNTLCARDS 'REPLACE COPYDDN (SYSCPY1)'
    SELECT *
      INTO
        COL1  DECIMAL ZONED (12)
       ,COL2  VARCHAR (10)
       ,COL3  DECIMAL ZONED (12)
       ,COL4  VARCHAR (10)
       ,COL5  DECIMAL ZONED (12)
       ,COL6  VARCHAR (10)
       ,COL7  DECIMAL ZONED (12)
       ,COL8  VARCHAR (10)
       ,COL9  DECIMAL ZONED (12)
       ,COL10 VARCHAR (10)
       ,COL11 DECIMAL ZONED (12)
       ,COL12 VARCHAR (10)
   FROM BMC.LONGNAME_TABLE_ON_EXAMPLE17_TBEXMP17
/*
//SYSREC   DD DSN=ADU.EXAMPL17.SYSREC,DISP=(NEW,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(3,1),RLSE)
//SYSCNTL  DD DSN=ADU.EXAMPL17.SYSCNTL,,DISP=(NEW,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(3,1),RLSE)
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*

SYSPRINT for example 17

The following figure shows the SYSPRINT for example 17:

                                *****  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:16:11 ...
 BMC50002I UTILITY ID = 'ADUXM17'.  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=8844K,ABOVE 16M=1412916K,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           FORMAT EXTERNAL
 BMC50102I     UNLOADDN SYSREC ACTIVE NO
 BMC50102I     ZONEDDECOVP(F,F)
 BMC50102I     FIXEDVARCHAR YES
 BMC50102I     CNTLCARDS 'REPLACE COPYDDN (SYSCPY1)'
 BMC50102I     SELECT *
 BMC50102I       INTO
 BMC50102I         COL1  DECIMAL ZONED (12)
 BMC50102I        ,COL2  VARCHAR (10)
 BMC50102I        ,COL3  DECIMAL ZONED (12)
 BMC50102I        ,COL4  VARCHAR (10)
 BMC50102I        ,COL5  DECIMAL ZONED (12)
 BMC50102I        ,COL6  VARCHAR (10)
 BMC50102I        ,COL7  DECIMAL ZONED (12)
 BMC50102I        ,COL8  VARCHAR (10)
 BMC50102I        ,COL9  DECIMAL ZONED (12)
 BMC50102I        ,COL10 VARCHAR (10)
 BMC50102I        ,COL11 DECIMAL ZONED (12)
 BMC50102I        ,COL12 VARCHAR (10)
 BMC50102I    FROM BMC.LONGNAME_TABLE_ON_EXAMPLE17_TBEXMP17

 BMC51654I DIRECT YES IN EFFECT

 BMC51687I ESTIMATED ROWS FOR TABLE SPACE DBEXMP17.TSEXMP17 PART 0 IS 6230 FROM HURBA
 BMC51688I ESTIMATED ROWS FOR TABLE     BMC.LONGNAME_TABLE_ON_EXAMPLE17_TBEXMP17 IS 6230 FROM TABLESPACE
 BMC51689I ESTIMATED ROWS FOR SELECT 1 IS 6230

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

 BMC51639I FOR DDNAME 'SYSREC' DSN=ADU.EXAMPL17.SYSREC,DCB=(RECFM=VB,BLKSIZE=27993,LRECL=152)

 BMC50894I PAGE EXTERNALIZATION PROCESS STARTING AT  10/28/2013  13:16:12

 BMC50650I ESTABLISHING A POINT OF CONSISTENCY FOR:
 BMC50651I SPACE DBEXMP17.TSEXMP17 PART(0) PSID(0002)

 BMC50895I PAGE EXTERNALIZATION PROCESS COMPLETE.  ELAPSED TIME =  00:00:00

 BMC50041I 0: ZIIP  ENABLED (0) USING XBM SUBSYSTEM XBMB
 BMC50474I BELOW 16M = 8492K, ABOVE 16M = 1406204K, CPUS = 3
 BMC51701I MAX TASKS = 1, MAX PARTITIONS PER TASK = 1, SORTWKS PER TASK = 0, MAX OPEN PARTITIONS PER TASK = 1
 BMC50477I 1: PARTITION =    0, ROWS/KEYS = 3, I/O WAITS = 2 ,DDNAME = SYS00002
 BMC50478I 1: RDB LOCK WAITS = 0
 BMC50476I DDNAME = SYSREC, I/OS = 1, I/O WAITS = 1, RDB LOCK WAITS = 0
 BMC51686I UNLOADING OF DATASET 'DEHJCAT.DSNDBD.DBEXMP17.TSEXMP17.I0001.A001' READ 3 PAGES
 BMC51672I UNLOAD STATISTICS:  3 ROWS PROCESSED FROM SPACE 'DBEXMP17.TSEXMP17', 0 NOT SELECTED, 0 DISCARDED
 BMC51674I UNLOAD STATISTICS:  3 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.EXAMPL17.SYSCNTL,DCB=(RECFM=FB,BLKSIZE=3120,LRECL=80)
 BMC51801I LOAD TABLE STATEMENTS WRITTEN TO DDNAME 'SYSCNTL'

 BMC51810I LOAD DATA INDDN SYSREC
 BMC51809I REPLACE COPYDDN (SYSCPY1)
 BMC51940I      EBCDIC CCSID(37,65534,65534)
 BMC51811I   INTO TABLE
 BMC51809I BMC.LONGNAME_TABLE_ON_EXAMPLE17_TBEXMP17
 BMC51813I (COL1 POSITION(1:12) DECIMAL ZONED
 BMC51814I       NULLIF BMC_NULL1=X'6F'
 BMC51819I     ,     BMC_NULL1 POSITION(13) CHAR(1)
 BMC51813I ,COL2 POSITION(14:*) VARCHAR
 BMC51813I ,COL3 POSITION(*:*+11) DECIMAL ZONED
 BMC51814I       NULLIF BMC_NULL2=X'6F'
 BMC51819I     ,     BMC_NULL2 POSITION(*) CHAR(1)
 BMC51813I ,COL4 POSITION(*:*) VARCHAR
 BMC51813I ,COL5 POSITION(*:*+11) DECIMAL ZONED
 BMC51813I ,COL6 POSITION(*:*) VARCHAR
 BMC51813I ,COL7 POSITION(*:*+11) DECIMAL ZONED
 BMC51813I ,COL8 POSITION(*:*) VARCHAR
 BMC51813I ,COL9 POSITION(*:*+11) DECIMAL ZONED
 BMC51814I       NULLIF BMC_NULL3=X'6F'
 BMC51819I     ,     BMC_NULL3 POSITION(*) CHAR(1)
 BMC51813I ,COL10 POSITION(*:*) VARCHAR
 BMC51813I ,COL11 POSITION(*:*+11) DECIMAL ZONED
 BMC51814I       NULLIF BMC_NULL4=X'6F'
 BMC51819I     ,     BMC_NULL4 POSITION(*) CHAR(1)
 BMC51813I ,COL12 POSITION(*:*) VARCHAR
 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*