CMNPMLOD - Master File XML Extractor
Data extract program CMNPMLOD (previously called SERPMLOD) uses XML Services to provide you with access to package master and component master data while insulating you from changes in how ChangeMan ZMF data is stored. Extracted data is delivered as XML Service replies, with each reply in a separate sequential file.
Program CMNPMLOD can be run against production master file data or against backup data, but the data must be stored in VSAM files. CMNPMLOD does not require ChangeMan ZMF to be running. CMNPMLOD is available only in batch.
Sample JCL for CMNPMLOD is now delivered in member EXEPMLOD in the vendor CMNZMF.CNTL library.
A LIST facility has been introduced which shows which services are currently supported. To use this function, specify an execution PARM. For example:
//PMLOD EXEC PGM=CMNPMLOD,PARM=’LIST’
Output is routed to SYSPRINT.
DDname Service Scope Message Unloadable Description
-------- -------- -------- -------- ---------- -----------------------------------------------
CMN$GPRM : PARMS GBL LIST Yes Global parameters
CMN$GSIT : SITE GBL LIST Yes Global sites
CMN$GPRC : PROCS GBL LIST Yes Global procedures
CMN$GLTP : LIBTYPE GBL LIST Yes Global library types
CMN$UFNS
...
CMNPMLOD Input
-
Package master
-
Component master
-
Long name component master
-
XML services MAPDATA file
Output
One or more QSAM files containing master file data in XML format, or SYSPRINT output with PARM LIST.
Sample JCL
The following is a sample job for a CMNPMLOD step that extracts one type of package master data (global compile procedures).
//CMNPMLD EXEC PGM=CMNPMLOD,REGION=0M
//STEPLIB DD DISP=SHR,DSN=CMNTP.CMN810.LOAD
// DD DISP=SHR,DSN=CMNTP.SER810.LOAD
//*===============================================================*
//* ChangeMan ZMF master files
//CMNPMAST DD DISP=SHR,DSN=CMNTP.S6.V810T06.CMNZMF.CMNPMAST
//CMNCMPNT DD DISP=SHR,DSN=CMNTP.S6.V810T06.CMNZMF.CMNCMPNT
//CMNCMPNL DD DISP=SHR,DSN=CMNTP.S6.V810T06.CMNZMF.CMNCMPNL
//*Note: CMNELDSP is obsolete as of ZMF 7.1.3
//*CMNELDSP DD DISP=SHR,DSN=somnode.subsys.CMNELCTX
//*===============================================================*
//* XML DATASPACE BACKUP
//MAPDATA DD DISP=SHR,DSN=CMNTP.SER810.MAPDATA
//*===============================================================*
...
//* TRACES AND DUMPS
//SERPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//*===============================================================*
//* GLOBAL XML DOCUMENT FILES
//CMN$GPRM DD DISP=(,CATLG),DSN=USER015.CMN$GPRM.XMLDATA,
// UNIT=SYSDA,SPACE=(TRK,(10,10),RLSE),
// DCB=(RECFM=VB,LRECL=4096,BLKSIZE=0)
...
This is an excerpt of what was created in the CMN$GPRM data set by the JCL above:
********************************* Top of Data ********
<?xml version="1.0"?>
<service name="PARMS">
<scope name="GBL">
<message name="LIST">
<result>
<cmnEnvironment>3</cmnEnvironment>
<enablePanBaseLib>N</enablePanBaseLib>
<enableLibrBaseLib>N</enableLibrBaseLib>
<enableLLamBaseLib>N</enableLLamBaseLib>
<enableOtherBaseLib>N</enableOtherBaseLib>
<allowStageOverlay>Y</allowStageOverlay>
<autoScratchLoadMbr>N</autoScratchLoadMbr>
<enableJes2Spool>Y</enableJes2Spool>
<disableCalendar>N</disableCalendar>
<useSerCompress>N</useSerCompress>
<createCmpWorkRecs>N</createCmpWorkRecs>
<showUserPanels>Y</showUserPanels>
<allowOnlyOneApproval>N</allowOnlyOneApproval>
<keepBaselineBySite>N</keepBaselineBySite>
...
<enableDisplayOrderDbdOverride>N</enableDisplayOrderDbdOverride>
<enableDisplayOrderPsbOverride>N</enableDisplayOrderPsbOverride>
<enableDisplayOrderXmlReport>N</enableDisplayOrderXmlReport>
<enableDisplayOrderApplication>N</enableDisplayOrderApplication>
<enableDisplayOrder3dSkel>N</enableDisplayOrder3dSkel>
</result>
</message>
</scope>
</service>
******************************** Bottom of Data ******
...
DD Statements
This table describes DD statements for program CMNPMLOD.
| DDNAME | I/O | Purpose |
|---|---|---|
| CMNPMAST | Input | Package master VSAM KSDS |
| CMNCMPNT | Input | Component master VSAM KSDS |
| CMNCMPNL | Input | Long name component master VSAM KSDS |
| MAPDATA | Input | MAPDATA sequential file created by the XMLLOAD job that creates the XMLSPACE VSAM LDS that is coded in the started task JCL |
| SERPRINT | Output | Program messages |
| SYSPRINT | Output | LIST output |
| CMN$ssss | Output | One or more sequential files containing extracted master file data in XML format, where CMN$ssss is the XML service name |
...
Extract processing is triggered by the presence of an output DD statement with a ddname that matches an XML service name.
The ddnames you can use to trigger extracts of package master and component master data are described in the following subtopics.
Global Records:
| DD Name | Service | Scope | Message | Description |
|---|---|---|---|---|
| CMN$GPRM | PARMS | GB | L LIST | General Parameters |
| CMN$GSIT | SITE | GBL | LIST | Global Sites |
| CMN$GPRC | PROCS | GBL | LIST | Procedure Names |
| CMN$GLTP | LIBTYPE | GBL | LIST | Global library types |
| CMN$UFNS | FLDNAMES | SERVICE | LIST | Field name substitutions |
| CMN$RESN | REASONS | SERVICE | LIST | Reason Codes |
| CMN$GLNG | LANGUAGE | GBL | LIST | Global languages |
| CMN$GRPT | REPORT | GBL | LIST | Global report definitions |
| CMN$GICR | IMSCRGN | GBL | LIST | IMS System Information |
| CMN$GIDO | IMSOVRD | GBL_DBD | LIST | IMS DBD Overrides |
| CMN$GIPO | IMSOVRD | GBL_PSB | LIST | IMS PSB Overrides |
| CMN$GOFM | FORMS | GBL | LIST | Global forms definitions |
| CMN$GDBP | DB2ADMIN | GBL_PHYS | LIST | Db2 Physical Subsystem |
| CMN$GDBL | DB2ADMIN | GBL_LOGL | LIST | Db2 Logical Subsystem |
| CMN$HLLA | HLLEXIT | ADMIN | LIST | HLLX admin/exit definitions |
| CMN$SCHD | SCHEDULE | SERVICE | LIST | Global scheduler information |
| CMN$3DSH | SKELS | GBL_HEDR | LIST | Global 3d-skels header information |
| CMN$3DSV | SKELS | GBL_VAR | LIST | Global 3d-skels variables |
...
Package Records
| DD Name | Service | Scope | Message | Description |
|---|---|---|---|---|
| CMN$PPRM | PACKAGE | GEN_PRMS | LIST | Package Information |
| CMN$PDSC | PACKAGE | GEN_DESC | LIST | Package Description |
| CMN$PIMI | PACKAGE | IMP_INST | LIST | Implementation Inst. |
| CMN$PSCD | PACKAGE | SCH_RECS | LIST | Scheduling Information |
| CMN$PLTP | LIBTYPE | PKG | LIST | Library Types |
| CMN$PAPR | APPROVER | PKG | LIST | Approval/Reject/Checkoff |
| CMN$PPPK | PACKAGE | PRT_PKGS | LIST | Participating Packages |
| CMN$PAAR | PACKAGE | AFF_APLS | LIST | Affected Applications |
| CMN$PRBR | PACKAGE | REASONS | LIST | Revert/Backout Reasons |
| CMN$PURC | PACKAGE | USR_RECS | LIST | Package User Records |
...
Package "I" Records
| DD Name | Service | Scope | Message | Description |
|---|---|---|---|---|
| CMN$PSCC | CMPONENT | PKG_COMP | LIST | Source components |
| CMN$PILC | CMPONENT | PKG_LOD | LIST | Load Information |
| CMN$PUTL | CMPONENT | PKG_UTIL | LIST | Scratch/Rename info. |
| CMN$PISC | CMPONENT | SRC_INCL | LIST | Source to Includes |
| CMN$PCUW | CMPONENT | PKG_WRKL | LIST | Component Work List |
| CMN$PICR | PACKAGE | IMS_CRGN | LIST | IMS Control Region |
| CMN$PIAS | PACKAGE | IMS_ACB | LIST | IMS ACB statements |
| CMN$PIDO | IMSOVRD | PKG_DBD | LIST | IMS DBD overrides |
| CMN$PIPO | IMSOVRD | PKG_PSB | LIST | IMS PSB Overrides |
| CMN$PLNK | PACKAGE | PKG_LINK | LIST | Linked Packages |
| CMN$PINC | CMPONENT | LOD_SUBR | LIST | Load to included CSECTs |
| CMN$PSIT | SITE | PKG | LIST | Site Information |
| CMN$PPRH | PACKAGE | PRM_HIST | LIST | Package promote history |
| CMN$PPCH | CMPONENT | PRM_HIST | LIST | Component promote history |
...
Application Records
| DD Name | Service | Scope | Message | Description |
|---|---|---|---|---|
| CMN$APRM | PARMS | APL | LIST | Application parameters |
| CMN$ASIT | SITE | APL | LIST | Sites, Jobcards |
| CMN$ALTP | LIBTYPE | APL | LIST | Library Types |
| CMN$ALNG | LANGUAGE | APL | LIST | Language Names |
| CMN$AAPR | APPROVER | APL | LIST | Approval List |
| CMN$ADBA | DB2ADMIN | APL_ACTV | LIST | Application Db2 active libraries |
| CMN$APRC | PROCS | APL | LIST | Procedure Names |
| CMN$ARPT | REPORT | APL | LIST | Application report definitions |
| CMN$AICR | IMSCRGN | APL | LIST | Application IMS control regions |
| CMN$AIDO | IMSOVRD | APL_DBD | LIST | Application IMS DBD overrides |
| CMN$AIPO | IMSOVRD | APL_PSB | LIST | Application IMS PSB overrides |
| CMN$BASL | BASELIB | SERVICE | LIST | Baseline Libraries |
| CMN$PRDL | PRODLIB | SERVICE | LIST | Production Libraries |
| CMN$PRMS | PROMLIB | SITE | LIST | Promotion Site Information |
| CMN$PRML | PROMLIB | LIBRARY | LIST | Promotion Site Libraries |
...
ERO Records
| DD Name | Service | Scope | Message | Description |
|---|---|---|---|---|
| CMN$RLSM | RLSMRLSE | SERVICE | LIST | Release Data |
| CMN$RGAP | RLSMAPPR | GLOBAL | LIST | Release Global Approvers |
| CMN$RASC | RLSMAPPR | ASCAPPRV | LIST | Release Area Approvers |
| CMN$RARE | RLSMAREA | SERVICE | LIST | Release Area Data |
| CMN$RAPL | RLSMAPPL | SERVICE | LIST | Release Application Data |
| CMN$RLTP | RLSMLTYP | SERVICE | LIST | Release |
| CMN$RAAP | RLSMAPPR | RELEASE | LIST | Release security entity data |
| CMN$RPRD | RLSMRLSE | PRIOR | LIST | Release Prior Release |
| CMN$RRBR | RLSMRLSE | REASONS | LIST | ERO revert/backout reasons |
| CMN$RSYD | RLSMAPPL | SYSLIB | LIST | Release Application Syslib |
| CMN$RPRM | RLSMAPPL | PROMOTE | LIST | Release Promotion |
| CMN$RCLK | RLSMAREA | CMP_LOCK | LIST | Release Component Lock |
| CMN$RPKG | RLSMRLSE | PACKAGE | LIST | ERO release packages |
...
Component Records
| DD Name | Service | Scope | Message | Description |
|---|---|---|---|---|
| CMN$GCGD | CMPONENT | GBL_CDSC | LIST | Global Description |
| CMN$ACGD | CMPONENT | APL_CDSC | LIST | Application Description |
| CMN$GCSC | CMPONENT | GBL_SECR | LIST | Component Security |
| CMN$ACSC | CMPONENT | APL_SECR | LIST | Application Security |
| CMN$CHIS | CMPONENT | HISTORY | LIST | Component History |
| CMN$GDCP | CMPONENT | GBL_DPRC | LIST | Designated Compile Procs |
| CMN$ADCP | CMPONENT | APL_DPRC | LIST | Application Designated Procs |
...
PARM Options
No PARM parameter is required in the EXEC statement for CMNPMLOD except when invoking the LIST function or the Unload function to extract records to load into Db2 or another database.
Return Codes and Error Messages
This table describes return codes for CMNPMLOD.
| Return Code | Description |
|---|---|
| 04 | There was a B37 condition for one or more output files, but processing continued for other output DD statements. See JESMSGLG. |
...
Reporting
This is an example of EXEPMLOD job output at the JESMSGLG DD statement.
********************************* TOP OF DATA *************************************************************************
J E S 2 J O B L O G -- S Y S T E M C 0 0 1 -- N O D E M P 3 J E S 2
19.02.02 J0620680 ---- SUNDAY, 22 MAR 2015 ----
19.02.02 J0620680 IRR010I USERID USER015 IS ASSIGNED TO THIS JOB.
19.02.02 J0620680 ICH70001I USER015 LAST ACCESS AT 18:47:31 ON SUNDAY, MARCH 22, 2015
19.02.02 J0620680 $HASP373 USER015G STARTED - INIT 1 - CLASS A - SYS C001
19.02.04 J0620680 +SER6702I SERNET XML Dsect Cross Reference. Created: 30 Jan 2015 16:48:44
19.02.04 J0620680 +SER6710I CMNPMSEQ processing - 00001402 records read - key JHFS 000000
19.02.04 J0620680 +SER6708I CMN$GPRM Closed - 00000145 records written
19.02.04 J0620680 - --TIMINGS (MINS.)-- -----PAGING COUNTS----
19.02.04 J0620680 -STEPNAME PROCSTEP RC EXCP CONN TCB SRB CLOCK SERV WORKLOAD PAGE SWAP VIO SWAPS
19.02.04 J0620680 -CMNPMLD 00 391 290 .00 .00 .0 51156 BATCH 0 0 0 0
19.02.04 J0620680 -USER015G ENDED. NAME-PACKAGE CACHE LOAD TOTAL TCB CPU TIME= .00 TOTAL ELAPSED TIME= .0
19.02.04 J0620680 $HASP395 USER015G ENDED
...
------ JES2 JOB STATISTICS ------
22 MAR 2015 JOB EXECUTION DATE
128 CARDS READ
189 SYSOUT PRINT RECORDS
0 SYSOUT PUNCH RECORDS
11 SYSOUT SPOOL KBYTES
0.04 MINUTES EXECUTION TIME
******************************** BOTTOM OF DATA ***********************************************************************
...
Sample CMNPMLOD Extract
This is the first 16 records of CMNPMLOD program output into the data set used at the CMN$GPRC DD statement.
<?xml version="1.0"?>
<service name="PROCS">
<scope name="GBL">
<message name="LIST">
<result>
<procName>CMNCOB2</procName>
<language>COBOL2</language>
<procDesc>Stage COBOL2 source</procDesc>
<displayOrderNo>00000</displayOrderNo>
</result>
<result>
<procName>CMNSQL</procName>
<language>SQL</language>
<procDesc>Translate, compile, and link SQL Stored Proc</procDesc>
<displayOrderNo>00000</displayOrderNo>
</result>
<result>
<procName>CMNMAPGN</procName>
<language>ASM</language>
<procDesc>CICS BMS MAP Gen</procDesc>
<displayOrderNo>00000</displayOrderNo>
</result>
...
Notes or Comments
- Execution parameter REGION=0M is suggested. The storage required to process up to 64 extract files can be substantial.
-
DD statements CMNPMAST, CMNCMPNT, and CMNCMPNL are optional, but you must input either the package master file or the two component masters.
-
If you are extracting data from the package master only, you can omit DD statements CMNCMPNT and CMNCMPNL.
-
If you are extracting data from the component masters only, you can omit DD statement CMNPMAST.
-
-
Use BLSR to reduce EXCP on the VSAM package master and to shorten job runtimes. Specify BLSR in your JCL as follows:
//CMNPMAST DD SUBSYS=(BLSR,'DDNAME=CMNPMALT','STRNO=255')
//CMNPMALT DD DISP=SHR,DSN=CMNTP.S6.V810T06.CMNZMF.CMNPMAST * Package Master
-
Output extract XML files all have DCB attribute LRECL=4096. However, you can use RECFM FB or VB.
-
In FB files, XML statements occupy the complete 4096 record, with a record break on a new result.
-
In VB files, each XML tag is on separate record.
-
-
XML extract file space requirements depend broadly on the type of data being extracted.
-
For global and application data extracts, a few tracks should suffice depending on the number of library types, sites, applications, etc. in your ChangeMan ZMF system.
-
For package data extracts, the space required is proportional to the number of packages in your system. As an example, for PACKAGE PARMS LIST allow 1 cylinder of standard 3390 space per 90 packages on the package master.
-
If a B37 abend occurs on an XML extract file, the extract to the affected file is suspended, but extracts to other files continue. The job ends with RC=04.
Sample CMNPMLOD LIST
This is the (minimal) JCL required:
//PMLODLST EXEC PGM=CMNPMLOD,PARM='LIST' //STEPLIB DD DISP=SHR,DSN=CMNTP.CMN820.LOAD // DD DISP=SHR,DSN=CMNTP.SER820.LOAD //SYSPRINT DD SYSOUT=*
Here are the first few records of the SYSPRINT output:
********************************* TOP OF DATA ***********************************************
List of service output currently supported by CMNPMLOD at 8.2.0 follows:
DDname Service Scope Message Unloadable Description
-------- -------- -------- -------- ---------- -----------------------------------
CMN$GPRM : PARMS GBL LIST Yes Global parameters
CMN$GSIT : SITE GBL LIST Yes Global sites
CMN$GPRC : PROCS GBL LIST Yes Global procedures
CMN$GLTP : LIBTYPE GBL LIST Yes Global library types
CMN$UFNS : FLDNAMES SERVICE LIST Yes Field name substitutions
CMN$RESN : REASONS SERVICE LIST Yes Global reason code information
CMN$GLNG : LANGUAGE GBL LIST Yes Global languages
CMN$GRPT : REPORT GBL LIST Yes Global report definitions
CMN$GICR : IMSCRGN GBL LIST Yes Global IMS control regions
CMN$GIDO : IMSOVRD GBL_DBD LIST Yes Global IMS DBD overrides
CMN$GIPO : IMSOVRD GBL_PSB LIST Yes Global IMS PSB overrides
CMN$GOFM : FORMS GBL LIST No Global forms definitions
CMN$GDBP : DB2ADMIN GBL_PHYS LIST Yes Global Db2 physical subsystems
...
Note the unloadable column specifies whether the record type can be extracted in a format compatible with Db2 LOAD.
CMNPMLOD - UNLOAD to Db2 Loadable Format
Standard CMNPMLOD produces XML as output. If you want to load package master/component master data into Db2, you can use PARM=UNLOAD to do this.
The prime purpose of CMNPMLOD is to extract package master/component master data and present it in a format that does not change with each release. The standard mechanism for doing this is to generate XML in the same way as the ZMF XML services. In this way each piece of data is tagged with the name associated with that data. This information can be parsed with no further input.
PARM=UNLOAD provides output in a format that can be directly loaded into Db2 (or any other DBMS).
This facility has been tailored specifically for use with Db2 in that the DDL and LOAD utility statements required to create and load a Db2 table with the results of the CMNPMLOD unload extract are generated at the same time as that output. If you are not going to use Db2, you can still make use of the LOAD utility statements to define the format of the output:
-
Db2 users can use the PARM=UNLOAD execution parameter to generate package master record output along with the DDL and LOAD utility parameters that can be used to load the package master information directly into a Db2 table. This method uses the least amount of DASD for the unload file but requires all columns to be loaded.
-
Db2 users who want to load a subset of the available columns to a Db2 table can use the PARM=UNLOADFIXED execution parameter to generate fixed position data in the unload record along with associated DDL and LOAD utility parameters. The DDL can be modified to generate a table with the desired sub-selection columns. Similarly, the LOAD parameters can be modified to load just the columns of interest. This method uses more DASD but the unload file can be discarded once the load has completed.
-
Non-Db2 users who want to load data to a database of choice can use PARM=UNLOADFIXED to produce fields at fixed locations in the output record and then use the sample DDL/LOAD parameters to build the utility control information they need to load to their target database.
We make use of the existing DD name method to select the data to be output. For example, if the DD statement CMN$ALTP is present, the program will generate output as provided by the libtype.apl.list service.
To generate Db2-loadable data, instead of xml, into the CMN$xxxx file you need to specify the execution parameter PARM=UNLOAD. For example:
//PMLOD EXEC PGM=CMNPMLOD,PARM='UNLOAD'
//*
//CMNPMAST DD SUBSYS=(BLSR,'DDNAME=CMNPMALT','STRNO=255')
//CMNPMALT DD DISP=SHR,DSN=CMNTP.S7.CMNPMAST
//CMNPMSEQ DD DISP=SHR,DSN=CMNTP.S7.CMNPMAST
//CMNCMPNT DD DISP=SHR,DSN=CMNTP.S7.CMNCMPNT
//CMNCMPNL DD DISP=SHR,DSN=CMNTP.S7.CMNCMPNL
//MAPDATA DD DISP=SHR,DSN=CMNTP.S7.MAPDATA
//SERPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//*
//CMN$ALTP DD DISP=(,CATLG),
// DSN=CMNTP.PMLOD.ALTP.UNLOAD,
// UNIT=SYSDA,SPACE=(CYL,(5,10),RLSE),
// DCB=(RECFM=VB,LRECL=4096,BLKSIZE=0)
...
Note that the 4096 LRECL with a RECFM of VB will suffice for all service output, except for CMN$BASL where an LRECL of 12000 is recommended.
This execution will dynamically allocate two further data sets, namely DDL$ALTP and LOD$ALTP. They will be allocated to SYSOUT. However, if these DDnames are precoded in the JCL, those allocations are used instead. (See information on the sample EXEPMUNL member of the CMNZMF.CNTL distribution library below).
The first of these DDnames will contain the DDL required to define the table that will hold this data. In this example, the DDL is as follows (but will vary depending on the service output that has been requested):
DROP TABLESPACE dbname.CMN$ALTP;
COMMIT WORK;
CREATE TABLESPACE CMN$ALTP IN dbname
FREEPAGE 0 PCTFREE 10
CLOSE NO BUFFERPOOL BP0
USING STOGROUP SYSDEFLT
SEGSIZE 32 LOCKSIZE PAGE;
COMMIT WORK;
CREATE TABLE CMNx.CMN$ALTP
(APPLNAME CHAR(4)
,LIBTYPE CHAR(3)
,LIKETYPE CHAR(1)
,MANAGEMENTCLASS CHAR(8)
,STORAGECLASS CHAR(8)
,UNITNAME CHAR(8)
,VOLUME CHAR(6)
,SPACETYPE CHAR(3)
,PRIMARYSPACE CHAR(8)
,SECONDARYSPACE CHAR(8)
,DIRBLOCKS CHAR(6)
,RECORDFORMAT CHAR(3)
,RECORDLENGTH CHAR(6)
,BLOCKSIZE CHAR(6)
,LIBRARYVERSION CHAR(1)
,ISPDSELIBTYPE CHAR(1)
,ISPDSLIBTYPE CHAR(1)
,ISSYSMANAGED CHAR(1)
,ISPDSEOBJECT CHAR(1)
,ISIMSLIBTYPE CHAR(1)
,ISSSVALLOWED CHAR(1)
,ISSSVENFORCED CHAR(1)
,ISDB2LIBTYPE CHAR(1)
,CHKOUTCOMPONENTGENDESC CHAR(1)
,CHKOUTACTIVITYFILE CHAR(1)
,DEFERSTAGELIBCREATION CHAR(1)
,INCLUDEUTILITYINFO CHAR(1)
,TARGETLOADLIBTYPE CHAR(3)
,TARGETACTIVITYFILE CHAR(3)
,LIBTYPEDESC VARCHAR(44)
,IMSENTITY CHAR(1)
,SSVOPTION CHAR(8)
,DDLSQLSUBTYPE CHAR(1)
,STOREDPROCSUBTYPE CHAR(1)
...
,TRIGGERSUBTYPE CHAR(1)
,PLANBINDCONTROLSUBTYPE CHAR(1)
,PACKAGEBINDCONTROLSUBTYPE CHAR(1)
,SQLSTOREDPROCDEFINITION CHAR(1)
,DBRMSUBTYPE CHAR(1)
,NATIVESQLSPDEFINITION CHAR(1)
,DB2SQLTERMINATIONCHAR CHAR(1)
,LIBRARYSEQUENCENO CHAR(3)
,ISHFSLIBTYPE CHAR(1)
,EATTR CHAR(1)
,DISPLAYORDERNO CHAR(5)
)
IN dbname.CMN$ALTP;
COMMIT WORK;
...
Column names are the same as the XML tag names. (If any tag name is greater then 30 bytes, which is the Db2 maximum length for a column name, it is truncated from the left.)
The default database name is dbname and the table qualifier is 'CMNx'. However these names may be changed by adding to the execution parameter. For example:
//PMLOD EXEC PGM=CMNPMLOD,PARM='UNLOAD,SCD820,CMNI'
will replace the database name with SCD820 and the qualifier with CMNI.
The second DDname, LOD$ALTP, contains the Db2 load utility parameters required to load this data to the table:
LOAD DATA INDDN UNLD LOG NO REPLACE NOCOPYPEND
EBCDIC CCSID(01047,00000,00000) INTO TABLE
"CMNx"."CMN$ALTP" (
"APPLNAME"
POSITION(*) CHAR(4)
,"LIBTYPE"
POSITION(*) CHAR(3)
,"LIKETYPE"
POSITION(*) CHAR(1)
,"MANAGEMENTCLASS"
POSITION(*) CHAR(8)
,"STORAGECLASS"
POSITION(*) CHAR(8)
,"UNITNAME"
POSITION(*) CHAR(8)
,"VOLUME"
POSITION(*) CHAR(6)
,"SPACETYPE"
POSITION(*) CHAR(3)
,"PRIMARYSPACE"
POSITION(*) CHAR(8)
,"SECONDARYSPACE"
POSITION(*) CHAR(8)
...
,"DIRBLOCKS"
POSITION(*) CHAR(6)
,"RECORDFORMAT"
POSITION(*) CHAR(3)
,"RECORDLENGTH"
POSITION(*) CHAR(6)
,"BLOCKSIZE"
POSITION(*) CHAR(6)
,"LIBRARYVERSION"
POSITION(*) CHAR(1)
,"ISPDSELIBTYPE"
POSITION(*) CHAR(1)
,"ISPDSLIBTYPE"
POSITION(*) CHAR(1)
,"ISSYSMANAGED"
POSITION(*) CHAR(1)
,"ISPDSEOBJECT"
POSITION(*) CHAR(1)
,"ISIMSLIBTYPE"
POSITION(*) CHAR(1)
,"ISSSVALLOWED"
POSITION(*) CHAR(1)
,"ISSSVENFORCED"
POSITION(*) CHAR(1)
,"ISDB2LIBTYPE"
POSITION(*) CHAR(1)
,"CHKOUTCOMPONENTGENDESC"
POSITION(*) CHAR(1)
,"CHKOUTACTIVITYFILE"
POSITION(*) CHAR(1)
,"DEFERSTAGELIBCREATION"
POSITION(*) CHAR(1)
,"INCLUDEUTILITYINFO"
POSITION(*) CHAR(1)
,"TARGETLOADLIBTYPE"
POSITION(*) CHAR(3)
,"TARGETACTIVITYFILE"
POSITION(*) CHAR(3)
,"LIBTYPEDESC"
POSITION(*) VARCHAR
,"IMSENTITY"
POSITION(*) CHAR(1)
,"SSVOPTION"
POSITION(*) CHAR(8)
,"DDLSQLSUBTYPE"
POSITION(*) CHAR(1)
,"STOREDPROCSUBTYPE"
POSITION(*) CHAR(1)
,"TRIGGERSUBTYPE"
POSITION(*) CHAR(1)
,"PLANBINDCONTROLSUBTYPE"
POSITION(*) CHAR(1)
,"PACKAGEBINDCONTROLSUBTYPE"
POSITION(*) CHAR(1)
,"SQLSTOREDPROCDEFINITION"
POSITION(*) CHAR(1)
,"DBRMSUBTYPE"
POSITION(*) CHAR(1)
,"NATIVESQLSPDEFINITION"
POSITION(*) CHAR(1)
,"DB2SQLTERMINATIONCHAR"
POSITION(*) CHAR(1)
,"LIBRARYSEQUENCENO"
POSITION(*) CHAR(3)
,"ISHFSLIBTYPE"
POSITION(*) CHAR(1)
,"EATTR"
POSITION(*) CHAR(1)
,"DISPLAYORDERNO"
POSITION(*) CHAR(5)
)
...
This format of output is variable in length. To save space any field over 16 bytes in length is presented as varchar. For example, for CMN$ADCP we have:
LOAD DATA INDDN UNLD LOG NO REPLACE NOCOPYPEND
EBCDIC CCSID(01047,00000,00000) INTO TABLE
"CMNx"."CMN$ADCP" (
"COMPONENT"
POSITION(*) VARCHAR
,"COMPONENTTYPE"
POSITION(*) CHAR(3)
,"APPLNAME"
POSITION(*) CHAR(4)
,"BUILDPROC"
POSITION(*) CHAR(8)
,"COMPILEOPTIONS"
POSITION(*) VARCHAR
,"LINKOPTIONS"
POSITION(*) VARCHAR
,"LANGUAGE"
POSITION(*) CHAR(8)
,"USEDB2PRECOMPILEOPTION"
POSITION(*) CHAR(1)
,"FORCEASSIGNEDBUILDPROC"
POSITION(*) CHAR(1)
,"USEROPTION01"
POSITION(*) CHAR(1)
,"USEROPTION02"
POSITION(*) CHAR(1)
,"USEROPTION03"
POSITION(*) CHAR(1)
,"USEROPTION04"
POSITION(*) CHAR(1)
,"USEROPTION05"
POSITION(*) CHAR(1)
,"USEROPTION06"
POSITION(*) CHAR(1)
,"USEROPTION07"
POSITION(*) CHAR(1)
,"USEROPTION08"
POSITION(*) CHAR(1)
...
,"USEROPTION09"
POSITION(*) CHAR(1)
,"USEROPTION10"
POSITION(*) CHAR(1)
,"USEROPTION11"
POSITION(*) CHAR(1)
,"USEROPTION12"
POSITION(*) CHAR(1)
,"USEROPTION13"
POSITION(*) CHAR(1)
,"USEROPTION14"
POSITION(*) CHAR(1)
,"USEROPTION15"
POSITION(*) CHAR(1)
,"USEROPTION16"
POSITION(*) CHAR(1)
,"USEROPTION17"
POSITION(*) CHAR(1)
,"USEROPTION18"
POSITION(*) CHAR(1)
,"USEROPTION19"
POSITION(*) CHAR(1)
,"USEROPTION20"
POSITION(*) CHAR(1)
,"USEROPTION0101"
POSITION(*) CHAR(1)
,"USEROPTION0102"
POSITION(*) CHAR(1)
,"USEROPTION0103"
POSITION(*) CHAR(1)
,"USEROPTION0104"
POSITION(*) CHAR(1)
,"USEROPTION0105"
POSITION(*) CHAR(1)
,"USEROPTION0201"
POSITION(*) CHAR(2)
,"USEROPTION0202"
POSITION(*) CHAR(2)
,"USEROPTION0203"
POSITION(*) CHAR(2)
,"USEROPTION0301"
POSITION(*) CHAR(3)
,"USEROPTION0302"
POSITION(*) CHAR(3)
,"USEROPTION0303"
POSITION(*) CHAR(3)
,"USEROPTION0401"
POSITION(*) CHAR(4)
,"USEROPTION0402"
POSITION(*) CHAR(4)
,"USEROPTION0403"
POSITION(*) CHAR(4)
,"USEROPTION0801"
POSITION(*) CHAR(8)
,"USEROPTION0802"
POSITION(*) CHAR(8)
,"USEROPTION0803"
POSITION(*) CHAR(8)
,"USEROPTION0804"
POSITION(*) CHAR(8)
,"USEROPTION0805"
POSITION(*) CHAR(8)
,"USEROPTION1001"
POSITION(*) CHAR(10)
,"USEROPTION1002"
POSITION(*) CHAR(10)
,"USEROPTION1601"
POSITION(*) CHAR(16)
,"USEROPTION1602"
POSITION(*) CHAR(16)
,"USEROPTION3401"
POSITION(*) VARCHAR
,"USEROPTION3402"
POSITION(*) VARCHAR
,"USEROPTION4401"
POSITION(*) VARCHAR
,"USEROPTION4402"
POSITION(*) VARCHAR
,"USEROPTION6401"
POSITION(*) VARCHAR
,"USEROPTION6402"
POSITION(*) VARCHAR
,"USEROPTION6403"
POSITION(*) VARCHAR
,"USEROPTION6404"
POSITION(*) VARCHAR
,"USEROPTION6405"
POSITION(*) VARCHAR
,"USEROPTION7201"
POSITION(*) VARCHAR
,"USEROPTION7202"
POSITION(*) VARCHAR
,"USEROPTION7203"
POSITION(*) VARCHAR
,"USEROPTION7204"
POSITION(*) VARCHAR
,"USEROPTION7205"
POSITION(*) VARCHAR
)
...
The Db2 load utility works out the location of each field to account for the varchar nature of much of the data. This format is typically of use only if you are using Db2 and if you will be loading all columns to your table.
If desired, you can work these positions out for yourself, each CHAR data item is exactly the length as specified, each VARCHAR begins with a 2-byte length followed by the data item with the length just specified (that is, the length does not include the length 2 bytes. For example, XL2'0008',CL8'12345678'), each value follows on immediately from the one before. This data format takes up the least amount of space in the output extract file.
The sample EXEPMUNL member of the CMNZMF.CNTL distribution library is provided to show how to extract, create a table, and load it in one job, discarding the extract. For example:
//* ddnames are processed sequentially.
//*
//* The output file, CMN$xxxx, is in a format which may be used
//* to immediately load a DB2 table.
//*
//* The DDL$xxxx file contain DDL which can be used to create a table
//* to hold the output fields.
//*
//* The LOD$xxxx contains a DB2 load command for this data but it can
//* also be used to show the format of the output file records for
//* use by other DBMS load processes.
//*
//* If no records are found for the requested record type then
//* CMNPMLOD will end with rc=6 to prevent further steps from
//* executing.
//*
//JOBLIB DD DISP=SHR,DSN=somnode.CMNZMF.LOAD
// DD DISP=SHR,DSN=somnode.SERCOMC.LOAD
// DD DISP=SHR,DSN=DSNvrm.SDSNEXIT
// DD DISP=SHR,DSN=DSNvrm.SDSNLOAD
//*
//* Execute the ZMF extract utility with the UNLOAD parm
//*
//PMLOD EXEC PGM=CMNPMLOD,REGION=0M,
// PARM='UNLOAD,datbase,qual'
//*
//CMNPMAST DD SUBSYS=(BLSR,'DDNAME=CMNPMALT','STRNO=255')
//CMNPMALT DD DISP=SHR,DSN=somnode.CMNZMF.CMNPMAST
//CMNPMSEQ DD DISP=SHR,DSN=somnode.CMNZMF.CMNPMAST
//CMNCMPNT DD DISP=SHR,DSN=somnode.CMNZMF.CMNCMPNT
//CMNCMPNL DD DISP=SHR,DSN=somnode.CMNZMF.CMNCMPNL
//*
//* XML dataspace backup
//*
//MAPDATA DD DISP=SHR,DSN=somnode.SERCOMC.MAPDATA
//*
//* Traces and dumps
//*
//SERPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//*
//* DB2 unload output
//*
//CMN$xxxx DD DISP=(,PASS),DSN=&&UNLOAD,
// UNIT=SYSDA,SPACE=(CYL,(ppp,sss),RLSE),
// DCB=(RECFM=VB,LRECL=4096,BLKSIZE=0)
//*CMN$BASL DCB=(RECFM=VB,LRECL=12000,BLKSIZE=0)
//*
//* Sample Drop/Create DDL
//*
//DDL$xxxx DD DISP=(,PASS),DSN=&&DDL,
// UNIT=SYSDA,SPACE=(TRK,(1,1)),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=0)
//*
//* Sample DB2 LOAD command
//*
//LOD$xxxx DD DISP=(,PASS),DSN=&&LOAD,
// UNIT=SYSDA,SPACE=(TRK,(1,1)),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=0)
//*
//* Drop/Create the table required to hold this data
//*
//CREATE EXEC PGM=IKJEFT01,COND=(4,LT),
// DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(ssss)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIAvr) -
LIB('DB2ssss.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSIN DD DISP=(OLD,DELETE),DSN=&&DDL
//*
//* Load the data into the table
//*
//LOAD EXEC PGM=DSNUTILB,
// PARM='ssss'
//SYSPRINT DD SYSOUT=*
//UNLD DD DISP=(OLD,DELETE),DSN=&&UNLOAD
//SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,10)
//SORTOUT DD UNIT=SYSDA,SPACE=(CYL,10)
//SYSMAP DD UNIT=SYSDA,SPACE=(CYL,10)
//UTPRINT DD SYSOUT=*
//SYSIN DD DISP=(OLD,DELETE),DSN=&&LOAD
...
An easier way to extract specific items of data (at the cost of using more DASD for the extract), or to extract data for loading into a DBMS other than Db2, is to force each item (whether CHAR or VARCHAR) to occupy the maximum width. This format is requested using the PARM='UNLOADFIXED' execution parameter. For example:
//PMLOD EXEC PGM=CMNPMLOD,PARM='UNLOADFIXED'
or
//PMLOD EXEC PGM=CMNPMLOD,PARM='UNLOADFIXED,SCD820,CMNI'
Using this mechanism, for example, the format of the LOAD parameters produced in the LOD$ALTP ddname now looks like this (and the data output to CMN$ALTP matches this different format):
LOAD DATA INDDN UNLD LOG NO REPLACE NOCOPYPEND
EBCDIC CCSID(01047,00000,00000) INTO TABLE
"CMNx"."CMN$ALTP" (
"APPLNAME"
POSITION(00001:00004) CHAR(4)
,"LIBTYPE"
POSITION(00005:00007) CHAR(3)
,"LIKETYPE"
POSITION(00008:00008) CHAR(1)
,"MANAGEMENTCLASS"
POSITION(00009:00016) CHAR(8)
,"STORAGECLASS"
POSITION(00017:00024) CHAR(8)
,"UNITNAME"
POSITION(00025:00032) CHAR(8)
,"VOLUME"
POSITION(00033:00038) CHAR(6)
,"SPACETYPE"
POSITION(00039:00041) CHAR(3)
,"PRIMARYSPACE"
POSITION(00042:00049) CHAR(8)
,"SECONDARYSPACE"
POSITION(00050:00057) CHAR(8)
,"DIRBLOCKS"
POSITION(00058:00063) CHAR(6)
,"RECORDFORMAT"
POSITION(00064:00066) CHAR(3)
,"RECORDLENGTH"
POSITION(00067:00072) CHAR(6)
,"BLOCKSIZE"
POSITION(00073:00078) CHAR(6)
,"LIBRARYVERSION"
POSITION(00079:00079) CHAR(1)
,"ISPDSELIBTYPE"
POSITION(00080:00080) CHAR(1)
,"ISPDSLIBTYPE"
POSITION(00081:00081) CHAR(1)
,"ISSYSMANAGED"
POSITION(00082:00082) CHAR(1)
,"ISPDSEOBJECT"
POSITION(00083:00083) CHAR(1)
,"ISIMSLIBTYPE"
POSITION(00084:00084) CHAR(1)
,"ISSSVALLOWED"
POSITION(00085:00085) CHAR(1)
,"ISSSVENFORCED"
POSITION(00086:00086) CHAR(1)
,"ISDB2LIBTYPE"
POSITION(00087:00087) CHAR(1)
,"CHKOUTCOMPONENTGENDESC"
POSITION(00088:00088) CHAR(1)
,"CHKOUTACTIVITYFILE"
POSITION(00089:00089) CHAR(1)
,"DEFERSTAGELIBCREATION"
POSITION(00090:00090) CHAR(1)
,"INCLUDEUTILITYINFO"
POSITION(00091:00091) CHAR(1)
,"TARGETLOADLIBTYPE"
POSITION(00092:00094) CHAR(3)
,"TARGETACTIVITYFILE"
POSITION(00095:00097) CHAR(3)
,"LIBTYPEDESC"
POSITION(00098) VARCHAR
,"IMSENTITY"
POSITION(00144:00144) CHAR(1)
,"SSVOPTION"
POSITION(00145:00152) CHAR(8)
,"DDLSQLSUBTYPE"
POSITION(00153:00153) CHAR(1)
,"STOREDPROCSUBTYPE"
POSITION(00154:00154) CHAR(1)
,"TRIGGERSUBTYPE"
POSITION(00155:00155) CHAR(1)
,"PLANBINDCONTROLSUBTYPE"
POSITION(00156:00156) CHAR(1)
,"PACKAGEBINDCONTROLSUBTYPE"
POSITION(00157:00157) CHAR(1)
,"SQLSTOREDPROCDEFINITION"
POSITION(00158:00158) CHAR(1)
,"DBRMSUBTYPE"
POSITION(00159:00159) CHAR(1)
,"NATIVESQLSPDEFINITION"
POSITION(00160:00160) CHAR(1)
,"DB2SQLTERMINATIONCHAR"
POSITION(00161:00161) CHAR(1)
,"LIBRARYSEQUENCENO"
POSITION(00162:00164) CHAR(3)
,"ISHFSLIBTYPE"
POSITION(00165:00165) CHAR(1)
,"EATTR"
POSITION(00166:00166) CHAR(1)
,"DISPLAYORDERNO"
POSITION(00167:00171) CHAR(5)
)
...
Note that the field positions are now each at an explicit, fixed, location in the extract record (as given by the POSITION sub-parameter).
A comparison of DASD usage for the worst case scenario, that is, CMN$CHIS, shows the following for a test subsystem, which had 328,574 component master history records at the time these extracts were run:
Standard XML: 510 Cyls
UNLOAD: 179 Cyls
UNLOADFIXED: 913 Cyls
Other records, less populous and with fewer fields per record, will not show such a wide spread.