Part_2 | - extract segments from IMS hierarchal DBs & pipe delimit for Relationals |
MVSJCL.htm | - MVS JCL Conversion |
VSEJCL.htm | - VSE JCL Conversion |
MVSCOBOL.htm | - VSE COBOL Conversion |
VSECOBOL.htm | - MVS COBOL Conversion |
MVSDATA.htm | - Convert MVS EBCDIC Data to ASCII, preserving packed fields |
- high volume (all files in directory) |
VSEDATA.htm | - Convert VSE EBCDIC Data to ASCII, preserving packed fields |
- high volume (all files in directory) |
DATAcnv1.htm | - Basic Mainframe DATA conversion (1 job at a time) |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
1A1. | Introduction & Overview, description of conversions performed. |
1B1. | Changes to copybooks to improve data conversion. |
1C1. | Other related conversions. |
sqlload1 - generate SQL loader control file for Microsoft SQL server | |
sqlload2 - generate SQL loader control file for ORACLE |
1D1. | Directories required for conversions |
1E1. | Control file to supply real data-file-names vs copy-book-names. |
1F1. | Operating Instructions for ALL CopyBooks & ALL Data Files in Directory |
1F1. | Generating conversion jobs for all copybooks |
1F2. | Executing conversion jobs for all data files |
1G1. | Operating Instructions - for 1 FILE AT A TIME |
1G1. | Generating conversion job (1 file at a time) |
1G2. | Executing conversion job (1 file at a time) |
1H1. | Demo conversion of supplied test file 'warmas1' |
- ASCII file without packed fields | |
1H1. | test/demo copybook, data input file,& data output file |
1H2. | generated uvcopy conversion job & operating instructions |
1H3. | listing output '|' delimited file with copybook fieldnames |
- to display field counts & verify conversion |
1I1. | Demo conversion of suppplied test file 'warmas2' |
- ASCII file with packed fields & signed numerics |
1J1. | Summary of uvcopy jobs & scripts documented in this section |
- cobmap1, genpipe1, genpipe1.sub, uvdata3, listpipe, sqlload1 | |
- genpipeA, genpipeB, genpipeC, gencnvA, gencnvB, gencnvC |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Note |
|
The data files may be EBCDIC (from a mainframe) or ASCII (on UNIX or NT). Existing ASCII files on UNIX are probably from COBOL applications, may have been previously converted from a mainframe, and may have mainframe data characteristics (flat/indexed files, packed/binary fields, fixed length records without linefeeds).
For loading data-bases, you must convert any packed fields to zoned numeric and separate any redefined areas. If not already Year 2000 compliant, you may wish to expand all 2 digit years to 4 digits.
This section will illustrate how to convert these mainframe type files to ASCII display pipe (|) delimited data suitable for loading databases.
We will also generate the control file for loading a Microsoft SQL database. Alternatively we can generate the control file to load an Oracle database.
These conversions are driven by the COBOL 'copybooks' (record layouts). The copy-books are first converted into 'cobmaps' which have field start, end, length,& type on the right side (see COBaids1.doc).
The heart of this process is 'genpipe1', a master uvcopy job which reads the COBOL copybooks,& generates worker uvcopy jobs to convert the data files.
Also see genpipe2 for conversion of IMS DB unload files for loading Oracle Relational DBs. See examples in part 9.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Most mainframe copybooks will need to be modified, to make the output suitable for loading relational databases.
10 purchase-date pic x(6). 10 purchase-ymd redefines purchase-date. 15 purchase-year pic x(2). 15 purchase-month pic x(2). 15 purchase-day pic x(2).
If the input were '991129' the output would be '|1999/11/29|99|11|29|' since the redefinition causes genpipe1 to generate code to output the same data field twice, but edited differently as shown. You can delete or *comment out the year/month/day fields, so genpipe1 will output the date only once & edited as: |1999/11/09|
We can write a 1-shot uvcopy job to split the file on record type. See the examples in SQLcnvrt.doc or later in this SQLjobs.doc.
We also need to create separate copybooks, which is usually easy to do if we have the redefined definition to start from. By convention we will name the separate copybooks by appending a 1,2,3,etc to the original name. Thus citytax would become: citytax1, citytax2, citytax3.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
mvn c500(7),b44(4p) <-- change (7) to (6) bal dat6,'500'
10 sales-this-year pic s9(7)v99 occurs 12.
10 sales-jan pic s9(7)v99. - - - - etc - - - - 10 sales-dec pic s9(7)v99.
10 FOLIO-NUM pic 9(10). ...... fields in fixed portion ...... 10 NUMBER-OF-LEGAL-DES pic 999. - - - end fixed begin variable portion - - - 10 LEGAL-DESCRIPTION pic x(50) OCCURS 100 DEPENDING ON NUMBER-OF-LEGAL-DES.
You can write a 1-shot uvcopy job to split the legal-descriptions out to a separate file, prefixing them with the folio# so you can relate the 2 tables in the data-base. You would then create copybooks for the separated files. See an example later in this SQLjobs.doc.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
The Vancouver Utilities includes some other jobs that are somewhat similar to genpipe1, in that they automatically generate parameters from copybooks.
sqlload1 |
|
sqlload2 |
|
sqlload1 & sqlload2 generate loader control files for the '|' delimted files created by the uvcopy jobs generated by genpipe1.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
We will assume the following directories are present (mkdir if not).
datf |
|
datp |
|
cpys |
|
maps |
|
pfp1 |
|
pfp2 |
|
pfp3 |
|
sql1 |
|
sql2 |
|
ctl |
|
tmp |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
We will first present Operating Instructions for all copybooks & all data files, which is almost as easy to do as for 1 at a time. The operating instructions are actually simpler.
The 'ALL FILES in Directory' procedures include 1 extra job 'uvdata3', which generates complete uvcopy jobs with actual data file names. The jobs will be renamed the same as the data file names, rather than the copybook names. Note that there could be multiple files for 1 copybook.
This addded convenience does require the preparation of a control file, to relate the copy-book-names to the data-file-names, but 'mkctlf1' is provided to gnerate the control file from the data file directory.
Mkctlf1 assumes that the copybook name is the same as the data file name, and you will have to make corrections where this is not the case. You will also have to correct the record sizes of sequential files since this cannot be determined from the directory.
# ctl/ctlfile1 - control file for genpipe1 demos (SQLcnvrt.doc) # warmas1 cpy=warmas1 rcs=00064 warmas2 cpy=warmas2 rcs=00064 warmas2E cpy=warmas2 rcs=00064 warmas3 cpy=warmas3 rcs=00064 warmas3a cpy=warmas3a rcs=00064 warmas3b cpy=warmas3b rcs=00064
# ctl/ctlfile1 - control file (sample) # s089.orders.exchange rcs=00134 cpy=exchange s089.orders.promo rcs=00074 cpy=orders u010.dpdt.dalytxns_0001 rcs=00210 cpy=dalytran u010.dpdt.weekly_0001 rcs=00210 cpy=________ u010.f021.acctupdt_0001 rcs=00700 cpy=accounts keys=(0,10,n) u010.f022.ordsupdt_0001 rcs=00700 cpy=orders keys=(0,8,n,10,7,d) u010.updt.cntlfile_0001 rcs=00080 cpy=cntlfile keys=(0,9,n) u075.f607.transfer_0001 rcs=32004 cpy=________
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Note that some of these steps (cobmap1) have already been performed in previous parts of this documentation (DATAcnv3.doc).
#1. uvcopyx cobmap1 cpys maps uop=q0i7p0 ==================================== - create record-layouts from all copybooks in the directory
#2. rmzf maps - remove zero length files (for any procedure copybooks)
#3. uvcopyx genpipe1 maps pfp1 uop=q0i7 =================================== - generate uvcopy jobs for all copybook layouts
#5. uvcopy uvdata3,fili1=ctl/ctlfile1,fild2=pfp1,fild3=pfp2,uop=q0i7a1r2 ==================================================================== - complete uvcopy job, by inserting correct datafilenames
#6. Generate SQL Loader control files for Microsoft or Oracale
#6a. uvcopyx sqlload1 maps sql1 uop=q0i7 <-- for Microsoft ===================================
#6b. uvcopyx sqlload2 maps sql1 uop=q0i7 <-- for Oracle ===================================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#6a. export UVDATA1=datf - setup path to input data files =================== #6b. export UVDATA2=datp - setup path to output data files ===================
Note |
|
#6a. export UVDATA1=/home/uvadm/tstvar/datf <-- for UV demos #6b. export UVDATA2=/home/uvadm/tstvar/datp
#6a. export UVDATA1=/u2/apps/data/cpip/datf <-- for production #6b. export UVDATA2=/u2/apps/data/cpip/datp
#7a. uvcopyxx 'pfp2/*' - execute all uvcopy jobs to convert all files ================
#7b. uvcopy 'pfp2/gl*' - execute all convert jobs for GL data files =================
#7c. uvcopy pfp2/warmas1 - execute a specific job to convert a specific file ===================
Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. uvcopy cobmap1,fili1=cpys/warmas1,filo1=maps/warmas1 ==================================================== - creat cobmap (record layout) from copybook
#2. uvcopy genpipe1,fili1=maps/warmas1,filo1=pfp1/warmas1 ===================================================== - generate uvcopy job from cobmap
#4. uvcopy pfp1/warmas1,fili1=datf/warmas1,filo1=datp/warmas1 =========================================================
#5a. cat datf/warmas1 - inspect input data fixed format #5b. cat datp/warmas1 - inspect output data variable | delimited
#6. uvcopy listpipe,fili1=datp/warmas1,fili2=maps/warmas1,filo1=tmp/warmas1 ======================================================================= - list '|' delimited output file to count fields & verify conversion - lists data fields with copybook names vertically with sequence#s - see example on page '1H3'
#7. uvcopy sqlload1,fili1=maps/warmas1,filo1=sql1/warmas1 ===================================================== - generate the control file to load a Microsoft SQL Server database
#8. sqlload CONTROL=sql\warmas1 DATA=data\warmas1 =============================================
Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#3a. export UVDATA1=datf - export directory path for input file #3b. export UVDATA1=datp - export directory path for output file
#4. uvcopy pfp1/warmas1 - execute convert warmas1 from datf to datp ===================
#5a. cat datf/warmas1 - inspect input data fixed format #5b. cat datp/warmas1 - inspect output data variable | delimited
#6. uvcopy listpipe,fili1=datp/warmas1,fili2=maps/warmas1,filo1=tmp/warmas1 ======================================================================= - list '|' delimited output file to count fields & verify conversion - lists data fields with copybook names vertically with sequence#s - see example on page '1H3'
#7. uvcopy sqlload1,fili1=maps/warmas1,filo1=sql1/warmas1 ===================================================== - generate the control file to load a Microsoft SQL Server database
#8. sqlload CONTROL=sql\warmas1 DATA=data\warmas1 =============================================
See script 'genpipeA' which simplifies the multi-step generation to 1 command.
Execute Method #2 (exporting UVDATA1 & UVDATA2) is recommended since the real data files might be in distant directories with long pathnames.
Exporting the directory paths makes the command much simpler as you can see above (you only need to specify the jobname).
Note that the procedures above assume that the data-file-names are the same as the copy-book-names. If this is not the case, you could modify the generated jobs, or use an alternative generation procedure descibed on the following pages. This involves creating a control file to relate the copybook name to the real data filename. The 'genpipeB' script is provided to generate the job using the control file.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
cobmap1 start-end bytes for cobol record fields 199911091332 pg# 0001 cpys/warmas1 RCSZ=0064 bgn-end lth typ *warmas1 - warranty master record 05 wm-cust pic x(6). 000-0005 006 05 wm-prod pic x(6). 006-0011 006 05 wm-descrip pic x(18). 012-0029 018 05 wm-purchase pic x(6). 030-0035 006 05 wm-policy pic x(8). 036-0043 008 05 wm-expiry. 10 wm-exp-year pic 9(2). 044-0045 002 n 02 10 wm-exp-month pic 9(2). 046-0047 002 n 02 10 wm-exp-day pic 9(2). 048-0049 002 n 02 05 filler001 pic x(1). 050-0050 001 05 wm-paid-amt pic s9(7)v99. 051-0059 009 n 09 05 filler002 pic x(4). 060-0063 004 *RCSZ=0064 0064
12345 12345 sony television 950101 1-year 960101 000002911 22222 11111 refrigerator 960101 2-year 980101 000004922 33333 22222 dish washer 961231 3-year 991231 000006733 33333 11111 refrigerator 970101 3-year 000101 000006744 44444 33333 freezer 970101 4-year 010101 000009955 55555 55555 clothes dryer 991231 5-year 021231 000055566 66666 66666 blank dates 6-year 000066677 77777 77777 zero/blank dates 000000 7-year 000000 000077788 99999 99999 nines dates 999999 7-year 999999 000099999
12345|12345|sony television|950101| 1-year|96|01|01||29.11|| 22222|11111|refrigerator|960101| 2-year|98|01|01||49.22|| 33333|22222|dish washer|961231| 3-year|99|12|31||67.33|| 33333|11111|refrigerator|970101| 3-year|00|01|01||67.44|| 44444|33333|freezer|970101| 4-year|01|01|01||99.55|| 55555|55555|clothes dryer|991231| 5-year|02|12|31||555.66|| 66666|66666|blank dates|| 6-year|||||666.77|| 77777|77777|zero/blank dates|000000| 7-year|00|00|00||777.88|| 99999|99999|nines dates|999999| 7-year|99|99|99||999.99||
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
opr='warmas1 warmas1 - generated by cobmap1,genpipe1,uvdata3' # - uvcopy job to convert & fix data fields 100 bytes apart # for compress & '|' delimit (see 'var' instrn below) uop=q0,was=a10000b10000c50000d10000 fili1=${UVDATA1}/warmas1,rcs=0064,typ=RSF filo1=${UVDATA2}/warmas1,rcs=9000,typ=LSTt @run opn all loop get fili1,a0 skp> eof # area a input, see 'get' instrn # area b translated to ASCII, in case mainframe EBCDIC file (with packed?) # area c data fields fixed 100 bytes apart in prep for var | delimit instrn # area d output, see 'put' instrn at end mvc b0(0064),a0 - move input area a to area b ### tra b0(0064) <-- for EBCDIC input, else remove mvc c0(6),b0(6) #1 wm-cust mvc c100(6),b6(6) #2 wm-prod mvc c200(18),b12(18) #3 wm-descrip mvc c300(6),b30(6) #4 wm-purchase mvc c400(8),b36(8) #5 wm-policy mvc c500(2),b44(2) #6 wm-exp-year mvc c600(2),b46(2) #7 wm-exp-month mvc c700(2),b48(2) #8 wm-exp-day mvc c800(1),b50(1) #9 filler001 edt c900(9),b51(9),'-zzzz.99' #10 wm-paid-amt sqz c900(9),' ' mvc c1000(4),b60(4) #11 filler002 var d0(8000),c0(100),0011,'|' trt d0(8000),$trtchr clr c0(01100),' ' put1 put filo1,d0 skp loop eof cls all eoj @pf2=genpipe1.sub
#1. genpipeB warmas1 - generate uvcopy job from copybook & control file - also generates SQL loader control file - see details in SQLcnvrt.doc & SQLjobs.doc
#2a. export UVDATA1=/home/uvadm/tstvar/datf - setup input directory path #2b. export UVDATA2=/home/uvadm/tstvar/datp - setup output directory path
#3. uvcopy pfp2/warmas1 - execute job to convert file from datf to datp
#4a. cat datf/warmas1 - display input file #4b. cat datp/warmas1 - display output file
#5. uvcopy listpipe,fili1=datp/warmas1,fili2=maps/warmas1,filo1=tmp/warmas1 - list '|' delimited output file to count fields & verify conversion see example on the next page --->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
After converting data files to '|' delimited format, we recommend that you run the 'listpipe' job to count fields & verify the conversion.
listpipe will list the data fields vertically with copybook names, and sequence numbers to count fields.
uvcopy listpipe,fili1=datp/warmas1,fili2=maps/warmas1,filo1=tmp/warmas1 =======================================================================
listpipe - list '|' delimited files with copybook fieldnames - to verify files created by genpipe1 (see SQLcnvrt.doc) datafile=datf/payrec1 copybookmap=maps/payrec1 datetime=1999/11/27_10:50:12 SEQ# FIELDNAME COBOL PICTURE DATA
datafile = datp/warmas1, record# = 1, field count = 11
12345|12345|sony television|950101| 1-year|96|01|01||29.11||
001 wm-cust x(6). 12345 002 wm-prod x(6). 12345 003 wm-descrip x(18). sony television 004 wm-purchase x(6). 950101 005 wm-policy x(8). 1-year 006 wm-exp-year 9(2). 96 007 wm-exp-month 9(2). 01 008 wm-exp-day 9(2). 01 009 filler001 x(1). 010 wm-paid-amt 9(7)v99. 29.11 011 filler002 x(4).
You can now compare this listing of the 1st few records of output with the 'uvhdcob' listing of the 1st few records of original input file:
uvhdcob datf/warmas1 maps/warmas1 =================================
datf/warmas1 rsz=64 totrecs=9 current=1 fsiz=576 fptr=0 cobmapfile=maps/warmas1 today=199911291210 datalastmod=1999111015 rec# 1 fieldname occurs bgn end typ<------ data (hex if typ=p/b) ---> 001 wm-cust 0 5 12345 002 wm-prod 6 11 12345 003 wm-descrip 12 29 sony television 004 wm-purchase 30 35 950101 005 wm-policy 36 43 1-year 006 wm-exp-year 44 45 n 96 007 wm-exp-month 46 47 n 01 008 wm-exp-day 48 49 n 01 009 filler001 50 50 010 wm-paid-amt 51 59 n 000002911 011 filler002 60 63
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Generate control files to load data files into SQL Server as follows:
uvcopy sqlload1,fili1=maps/warmas1,filo1=sql1/warmas1 <-- any 1 file =====================================================
uvcopyx sqlload1 maps sql1 uop=q0i7 <-- generate for all files ===================================
-- warmas1.sql - control file for SQL*LOADER -- LOAD DATA STREAM FIELDS TERMINATED by '|' CREATE TABLE [warmas1] ( [wm_cust] [char] (06) NULL , --#001 x(6). [wm_prod] [char] (06) NULL , --#002 x(6). [wm_descrip] [varchar] (18) NULL , --#003 x(18). [wm_purchase] [char] (06) NULL , --#004 x(6). [wm_policy] [char] (08) NULL , --#005 x(8). [wm_exp_year] [integer] NULL , --#006 9(2). [wm_exp_month] [integer] NULL , --#007 9(2). [wm_exp_day] [integer] NULL , --#008 9(2). [filler001] [char] (01) NULL , --#009 x(1). [wm_paid_amt] [float] NULL , --#010 9(7)v99. [filler002] [char] (04) NULL , --#011 x(4). )
sqlload CONTROL=sql\warmas1 DATA=datp\warmas1 =============================================
The sequence# should match the sequence# for the corresponding field on the listpipe report (see sample on previous page).
The COBOL picture let's you see the original COBOL field definition without having to look up the copybook.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
-- warmas1 - control file for SQL*LOADER -- LOAD DATA STREAM FIELDS TERMINATED by '|' INSERT INTO TABLE warmas1 ( wm_cust char (06) , --#001 x(6). wm_prod char (06) , --#002 x(6). wm_descrip varchar (18) , --#003 x(18). wm_purchase char (06) , --#004 x(6). wm_policy char (08) , --#005 x(8). wm_exp_year integer , --#006 9(2). wm_exp_month integer , --#007 9(2). wm_exp_day integer , --#008 9(2). filler001 char (01) , --#009 x(1). wm_paid_amt float , --#010 9(7)v99. filler002 char (04) , --#011 x(4). )
Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
cobmap1 start-end bytes for cobol record fields 199911091332 pg# 0001 cpys/warmas2 RCSZ=0064 bgn-end lth typ *warmas2 - warranty master record * demo genpipe1 - convert data files to '|' delimited format * - to illustrate packed fields & signed numeric fields 05 wm-cust pic x(6). 000-0005 006 05 wm-prod pic x(6). 006-0011 006 05 wm-descrip pic x(18). 012-0029 018 05 wm-purch-date pic 9(6). 030-0035 006 n 06 05 wm-policy pic x(8). 036-0043 008 05 wm-expiry-date pic 9(6) comp-3. 044-0047 004pn 06 05 filler001 pic x(1). 048-0048 001 05 wm-paid-amt pic s9(7)v99. 049-0057 009 ns 09 05 wm-paid-date pic 9(8) comp-3. 058-0062 005pn 08 05 filler002 pic x(1). 063-0063 001 *RCSZ=0064 0064
12345|12345|sony television|1995/01/01| 1-year|1996/01/01||-29.11|1995/01/01|| 22222|11111|refrigerator|1996/01/01| 2-year|1998/01/01||49.22|1996/01/01|| 33333|22222|dish washer|1996/12/31| 3-year|1999/12/31||-67.33|1996/12/31|| 33333|11111|refrigerator|1997/01/01| 3-year|0000/00/00||67.44|1997/01/01|| 44444|33333|freezer|1997/01/01| 4-year|0000/00/00||-99.55|1997/01/01|| 55555|55555|clothes dryer|1999/12/31| 5-year|0000/00/00||555.66|1999/12/31|| 66666|66666|blank dates|0000/00/00| 6-year|0000/00/00||666.00|0000/00/19|| 77777|77777|zero/blank dates|0000/00/00| 7-year|0000/00/00||-777.00|1900/00/00|| 99999|99999|nines dates|1999/99/99| 7-year|1999/99/99||-999.99|1999/99/99||
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
uvhd datf/warmas2 <-- display in vertical hexadecimal (for packed fields) =================
r# 1 1 2 3 4 5 6 b# 0 0123456789012345678901234567890123456789012345678901234567890123
12345 12345 sony television 950101 1-year .`.. 00000291q..P... 3333323333327667276667676662223333332327667206112333333337095110 1234501234503FE9045C56939FE00095010101D95120900C00000029111900CA
22222 11111 refrigerator 960101 2-year .... 000004922..`... 3333323333327667666767672222223333332327667208112333333333096110 2222201111102562975214F200000096010102D95120900C00000049221900CA
33333 22222 dish washer 961231 3-year ..#. 00000673s..a#.. 3333323333326676276766722222223333332327667209212333333337096210 33333022222049380713852000000096123103D95120913C00000067331913CA
33333 11111 refrigerator 970101 3-year .... 000006744..p... 3333323333327667666767672222223333332327667200112333333333097110 3333301111102562975214F200000097010103D95120000C00000067441900CA
44444 33333 freezer 970101 4-year .... 00000995u..p... 3333323333326766767222222222223333332327667201112333333337097110 4444403333306255A520000000000097010104D95120000C00000099551900CA
55555 55555 clothes dryer 991231 5-year .!#. 000055566...#.. 3333323333326667667267767222223333332327667202212333333333099210 5555505555503CF48530429520000099123105D95120013C00000555661913CA
66666 66666 blank dates 6-year .... 000066600...... 3333323333326666626676722222222222222327667200002333333333000090 6666606666602C1EB041453000000000000006D95120000C00000666000001CA
77777 77777 zero/blank dates 000000 7-year .... 00007770p...... 3333323333327676266666266767223333332327667200002333333337090000 777770777770A52FF2C1EB0414530000000007D95120000C00000777001000CA
99999 99999 nines dates 999999 7-year .... 00009999y...... 3333323333326666726676722222223333332327667209992333333337099990 999990999990E9E53041453000000099999907D95120999C00000999991999CA
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
opr='warmas2 warmas2 - generated by cobmap1,genpipe1,uvdata3' # - uvcopy job to convert & fix data fields 100 bytes apart # for compress & '|' delimit (see 'var' instrn below) uop=q0 was=a10000b10000c50000d10000 fili1=${UVDATA1}/warmas2,rcs=0064,typ=RSF filo1=${UVDATA2}/warmas2,rcs=9000,typ=LSTt @run opn all loop get fili1,a0 skp> eof # area a input, see 'get' instrn # area b translated to ASCII, in case mainframe EBCDIC file (with packed?) # area c data fields fixed 100 bytes apart in prep for var | delimit instrn # area d output, see 'put' instrn at end mvc b0(0064),a0 - move input area a to area b ### tra b0(0064) <-- for EBCDIC input, else remove mvc c0(6),b0(6) #1 wm-cust mvc c100(6),b6(6) #2 wm-prod mvc c200(18),b12(18) #3 wm-descrip mvc c300(6),b30(6) #4 wm-purch-date bal dat6,'300' mvc c400(8),b36(8) #5 wm-policy mvn c500(6),b44(4p) #6 wm-expiry-date bal dat6,'500' mvc c600(1),b48(1) #7 filler001 edt c700(9),b49(9),'-zzzz.99' #8 wm-paid-amt sqz c700(9),' ' mvn c800(8),b58(5p) #9 wm-paid-date bal dat8,'800' mvc c900(1),b63(1) #10 filler002 var d0(8000),c0(100),0010,'|' trt d0(8000),$trtchr clr c0(01000),' ' put1 put filo1,d0 skp loop # eof cls all eoj @pf2=genpipe1.sub
#1. genpipeB warmas2 - generate uvcopy job from copybook & control file - also generates SQL loader control file - see details in SQLcnvrt.doc & SQLjobs.doc
#2a. export UVDATA1=/home/uvadm/tstvar/datf - setup input directory path #2b. export UVDATA2=/home/uvadm/tstvar/datp - setup output directory path
#3. uvcopy pfp2/warmas2 - execute job to convert file from datf to datp
#4a. cat datf/warmas2 - display input file #4b. cat datp/warmas2 - display output file
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
cobmap1 |
|
genpipe1 |
|
genpipe2 |
|
genpipe1.sub |
|
uvdata3 |
|
listpipe1 |
|
sqlload1 |
|
sqlload2 |
|
These uvcopy jobs are stored in the /home/uvadm/pf directory & you can examine or print them as follows, using genpipe1 as an example:
vi /home/uvadm/pf/genpipe1 <-- examine genpipe1 ==========================
uvlp12 /home/uvadm/pf/genpipe1 <-- print genpipe1 ==============================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
genpipeA/B/C |
|
genpipeA |
|
genpipeB |
|
genpipeC |
|
gencnvA/B/C |
|
gencnvA |
|
gencnvB |
|
gencnvC |
|
These KORN shell scripts are stored in the /home/uvadm/sf directory & you can examine or print them as follows, using genpipeB as an example:
vi /home/uvadm/sf/IBM/genpipeB <-- examine genpipe1 ==============================
uvlp12 /home/uvadm/sf/IBM/genpipeB <-- print genpipe1 ==================================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
2A1. | Introductions & sub-directories required for conversion |
2B1. | IMS BMC UNLOAD PLUS file format |
2C1. | Split unload file segments to separate files (imsbmcx1 utility) |
2D1. | Operating Instructions |
2E1. | Demo conversion/test/debug procedures using segment 'c9spcant'. |
- illustrates a problem of 2 extra bytes at begining of segment data | |
that is unaccounted for in the copybook ?? |
2F1. | Demo conversion/test/debug procedures using segment 'c9spcapl'. |
- illustrates the 'multiple record types' not allowed in Relational DBs | |
- will write 2 separate files for loading separate relational tables |
2G1. | Demo conversion/test/debug procedures using segment 'c9spcaaj' |
- illustrates 'redefined fields' (not allowed in Relational DBs) | |
- will test the code that determines which set of fields is active | |
and clear 1 set or the other set |
2H1. | uvcopy jobs & scripts used in this part 9 |
- imsbmcx1, imsbmcx2 | |
- cobmap1, uvdata2, genpipe2, uvdata3, sqlload2, listpipe2, genpipeD | |
- will list a few of the shorter scripts such as genpipeD |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
This part documents the procedures to convert IMS hierarchal database unload files to pipe delimited ASCII text files for loading Relational databases.
The data is extracted from the mainframe IMS database using 'UNLOAD PLUS' from BMC Software, which extracts parent & child segments into a flat file with customized header records prefixed onto the data records. Records are variable length with header & data lengths specified by 2 byte binary fields at the begining of each unloaded segement/record.
We will demo this conversion using the 'c9bcaa0p' file unloaded from IMS & transferred to UNIX. The 'imsbmcx2' job will extract the various segments into separate files in the output directory. The files will be named the same as the segment names found in the unload file, and we will assume that copybooks with matching names are available to describe the data.
We will setup a separate master directory for this conversion and copy over the required copybooks from directories created & discussed in related document DATAcnv2.htm.
/opt :-----apps : :-----IMS : : :-----IMSdumps : : :-----c9bcaa0p : : :-----c9bcaa0p.pipe : : :-----cpyrs : : :-----ctl : : :-----doc : : :-----maprs : : :-----pfpr1 : : :-----pfpr2 : : :-----pfprs : : :-----pfxr1 : : :-----sql1 : : :-----tmp : :-----libs : : :-----cpam : : : :----- : : : :-----cpys : : : :-----
See next page for a description of these sub-directories --->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
IMSdumps |
|
c9bcaa0p |
|
c9bcaa0p.pipe |
|
cpyrs |
|
ctl |
|
maprs |
|
pfpr1 |
|
pfpr2 |
|
pfprs |
|
pfprs(edit) |
|
pfxr1 |
|
sql1 |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Records are variable length with header & data lengths specified by 2 byte binary fields at the begining of each unloaded segement/record.
We will use uvhd to investigate the IMS unload file. We can not use 'vi' since the file is EBCDIC with binary & packed fields & no LineFeeds.
Uvhd displays each 64 bytes on 3 lines - characters, zones,& digits. Note option 'a' on uvhd to translate the character line to ASCII, but we can still see the zones & digits for the EBCDIC code.
uvhd IMSdumps/c9bcaa0p r256a - investigate IMS unload file ============================ --> i1s2 <-- - immediate print 2 records space 2
1 2 3 4 5 6 r# 1 0123456789012345678901234567890123456789012345678901234567890123
0 ...XC9SPCAAC....02010001250.02010001250..R LI 1...............0 0105CFEDCCCC0000FFFFFFFFFFF0FFFFFFFFFFF00D4DC44F000000001900000F 0CB839273113110B020100012500020100012500790390010000C0020C0000C0
64 00000AJULIA HARGAIN 45 SCHEID DR FFFFFCDEDCC4CCDCCCD444444444444FF4ECCCCC4CD444444444444444444444 0000011439108197195000000000000450238594049000000000000000000000
128 PARLIN 444444444444444444444444444444444444444444DCDDCD4444444444444444 0000000000000000000000000000000000000000007193950000000000000000
192 NJ08859 4DDFFFFF44444444444444444444444444444444444444444444444444444444 0510885900000000000000000000000000000000000000000000000000000000
You should be able to relate the uvhd dump to the segment header fields described at the top of the page, but we cannot see parent/child relationships since this is only the 1st part of the 1st record which could be quite long.
Please see the next page which extracts just 64 bytes from each segment using uvcopy job imsbmcx1. Then we can use uvhd to look at the extracted 64 byte headers to better see parent/child relationships.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
This page extracts just 64 bytes from each segment of the IMS unloaded segments using uvcopy job imsbmcx1. Then we can use uvhd to look at the extracted 64 byte headers to better see parent/child relationships.
uvcopy imsbmcx1,fili1=IMSdumps,filo1=tmp/c9bcaa0p ================================================= - extract 64 bytes from each unload record
uvhd IMSdumps/c9bcaa0p r64a - investigate the extracted record hdrs =========================== --> i5s2 <-- - immediate print 5 records space 2
1 2 3 4 5 6 r# 1 0123456789012345678901234567890123456789012345678901234567890123
0 ...XC9SPCAAC....02010001250.02010001250..R LI 1...............0 0105CFEDCCCC0000FFFFFFFFFFF0FFFFFFFFFFF00D4DC44F000000001900000F 0CB839273113110B020100012500020100012500790390010000C0020C0000C0
128 ....C9SPCACU....00.00000000880328RE000 ..000000 0101CFEDCCCE0000FF0FFFFFFFFFFFFFFDCFFF444444444400FFFFFF44444444 033B392731342202000000000008803289500000000000000C00000000000000
256 ...$C9SPCAPL....L 01.L 01 0105CFEDCCDD0000D4444444FF0D4444444FF444444444444444444444444444 0B0B39273173430A300000000103000000001000000000000000000000000000
384 ...1C9SPCAPR....02.02008191I LLI000000000000000000000000000N3 010FCFEDCCDD0000FF0FFFFFFFFC4444DDCFFFFFFFFFFFFFFFFFFFFFFFFFFFDF 0301392731795402020020081919000033900000000000000000000000000053
512 ....C9SPCANT....P80119671hiE@.P80119671hiE@880328 0 0 0000 S0TMF 0101CFEDCCDE0000DFFFFFFFF86470DFFFFFFFF8647FFFFFF4F4F4FFFF4EFEDC 0E2839273153630D780119671895C0780119671895C880328000000000020346
Byte 13 is the level# which indicates the parent/child relationship C9SPCAAC - level 1 (top) C9SPCACU - level 2, parent is C9SPCAAC C9SPCAPL - level 3, parent is C9SPCACU C9SPCAPR - level 4, parent is C9SPCAPL C9SPCANT - level 3, parent is C9SPCACU
Note that imsbmcx1 is a simple job that simply extracts the 1st 64 bytes of each segment into a separate file for viewing with uvhd.
The important job is 'imsbmcx2' which will split the IMS unload file into separate files. It converts the header info into pipe delimited ASCII, but leaves the EBCDIC data for later conversion by genpipe2.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Utility job 'imsbmcx2' writes 1 output file for each segname found in the unload file & names the output files the same as the segment names. You need only to specify the output directory & imsbmcx2 will create a file for each segment it finds in the unload input file.
At the end of the job, imsbmcx2 writes out a control file to allow you to resume sequence#s of data records in case of multiple unload files of the same data type. The control file is named from the input file, for example input file c9bcaa0p creates control file ctl/c9bcaa0p.seq).
Output records will be prefixed with a 128 byte header containing information about the parent/child relationship that will allow subsequent loading into a Relational database. Segment data will start at byte 128. The hdr is ASCII & pipe delimited but the data is still EBCDIC & will be subsequently translated & pipe delimited by the 'genpipe2' utility job. Here is an illustration of the imsbmcx2 output ('P' stands for Parent).
segname|segcode|level#|seq#|Psegname|Psegcode|Plevel#|Pseq#|
segkeylths(8*2)|lev1key|lev2key|lev3key|lev4key|lev5key| |hdrsize|datasize|
---------------------- segment data (EBCDIC) ------------------------->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
000(08) - segname (also used as output filename in subdir) 009(02) - segcode 012(02) - level# 015(08) - sequence# 024(08) - Parent segname 033(02) - Parent segcode 036(02) - Parent level# 039(08) - Parent sequence# 048(16) - segkeylths 8*2=16 bytes 065(60) - concatenated segement keys - |key1|key2|key3|key4|key5|key6|key7|key8| 116(12) - |headersize|datasize| 131(6) - uvcopy logical filename (filo01-filo39) for this segment 140(5) - output record size (datasize+128)
At end job we write out a control file with the last sequence#s used for the various segments found in the input database extract file. If there are more input files for the same database extract, the control file can be read back in on next run to resume sequence#s.
The control file is also very useful as a run status report showing segments found, last seq#s, last keys,& output record sizes. I strongly recommend you print it out for project control.
The control file will be written into the ctl subdir using the same name as the input file with .seq extension appended you can print it out as follows (using c9bcaa0p as an example)
uvLp15 ctl/c9bcaa0p.seq <-- print control file (Landscape at 15 cpi) =======================
<--current segment --> <-- parent segment --> <-keylths 8*2--> <---concatenated keys---> segname cd lvl# seq# segname cd lvl seq#
C9SPCAAC|01|01|00000100| |00|00|00000000|11 |08010835668| |0028|02904| filo01 03032 C9SPCACU|02|02|00000100|C9SPCAAC|01|01|00000100|1102 |08010835668|00| |0019|00795| filo02 00923 C9SPCAPL|04|03|00000113|C9SPCACU|02|02|00000100|110210 |08010835668|00|LTU20 01| |0027|00091| filo04 00219 C9SPCAPR|05|04|00000127|C9SPCAPL|04|03|00000113|11021002 |08010835668|00|LTU20 01|01| |0019|00241| filo05 00369 C9SPCANT|06|03|00000702|C9SPCACU|02|02|00000100|110213 |08010835668|00|X80048872W53 | |0030|00536| filo06 00664 C9SPCAAD|08|03|00000100|C9SPCACU|02|02|00000100|110200 |08010835668|00|| |0017|01426| filo08 01554 C9SPCAPY|12|02|00000269|C9SPCAAC|01|01|00000098|1119 |08010832646|800698719999001 | |0036|00297| filo12 00425 C9SPCACL|13|02|00000067|C9SPCAAC|01|01|00000100|1100 |08010835668|| |0017|00549| filo13 00677 C9SPCAAL|14|03|00002355|C9SPCACL|13|02|00000067|110014 |08010835668||80039798769147| |0031|00813| filo14 00941 C9SPCAAJ|11|02|00000003|C9SPCAAC|01|01|00000061|1114 |06010114586|79989086l- | |0031|00280| filo11 00408
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
C9SPCAAC|01|01|0100| |00|00|0000|11 |08...| ... |0028|02904| filo01 03032 C9SPCACU|02|02|0100|C9SPCAAC|01|01|0100|1102 |08...|00| ... |0019|00795| filo02 00923 C9SPCAPL|04|03|0113|C9SPCACU|02|02|0100|110210 |08...|00|LT... |0027|00091| filo04 00219 C9SPCAPR|05|04|0127|C9SPCAPL|04|03|0113|11021002 |08...|00|LT... |0019|00241| filo05 00369 C9SPCANT|06|03|0702|C9SPCACU|02|02|0100|110213 |08...|00|X8... |0030|00536| filo06 00664 C9SPCAAD|08|03|0100|C9SPCACU|02|02|0100|110200 |08...|00|| ... |0017|01426| filo08 01554 C9SPCAPY|12|02|0269|C9SPCAAC|01|01|0098|1119 |08...|80069... |0036|00297| filo12 00425 C9SPCACL|13|02|0067|C9SPCAAC|01|01|0100|1100 |08...|| ... |0017|00549| filo13 00677 C9SPCAAL|14|03|2355|C9SPCACL|13|02|0067|110014 |08...||8003... |0031|00813| filo14 00941 C9SPCAAJ|11|02|0003|C9SPCAAC|01|01|0061|1114 |06010114586... |0031|00280| filo11 00408
Information in the control file is useful for creating the subsequent uvcopy jobs to convert the EBCDIC data beyond the 128 byte header. The output rcsz is shown on the right hand side of the control file output rcsz is the data segsize + 128 for header inserted with seq#s etc.
This is the record size of the various segments written by imsbmcx2. This size must be edited into the control file used by uvdata3, which creates the pipe delimited ASCII text file.
Note that the data records extracted by imsbmcx2 have a 128 byte prefix (or header) that is similar to the control file records shown above. In fact the control file written out at the end of the job is the same as the header on the last data records written for the various segments.
Also note that there are always exactly 20 '|' pipe delimiters in the data record headers, so your program that loads the Relational database will know where the data fields start.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
We will present the operating instructions & demo the conversion procedures using the 'c9spcant' segment from the 'c9bcaa0p' unload file which has been illustrated above. We will assume the unload file has already been created on the mainframe & can be FTP'd to the UNIX system.
#2a. uvhd IMSdumps/c9bcaa0p a - investigate IMS unload file ======================== (option 'a' for ASCII char line) - see sample dump on page '2B1'
#2b. uvcopy imsbmcx1,fili1=IMSdumps/c9bcaa0p,filo1=tmp/c9bcaa0p.hdrs =============================================================== - extract 64 bytes from begining of each unload record
#2c. uvhd tmp/c9bcaa0p ar64 - investigate the 64 byte extract file ====================== --> i50f5 <-- print out 1st 50 records (5 records per form/page) - you can see the parent child relationships by following the level#s in byte 13 of each hdr - see sample dump on page '2B2'
#3a. mkdir c9bcaa0p - create subdir for output data segment files ==============
#3b. \rm c9bcaa0p/* - may need to remove files for reruns not intended ============== to continue sequence#s, since output files append.
#3c. uvcopy imsbmcx2,fili1=IMSdumps/c9bcaa0p,fild2=c9bcaa0p ====================================================== - extract files for each segment data type found
prompt--> read ctl file ctl/c9bcaa0p.seq to continue sequence#s --> n <-- reply 'no' on 1st run (of IMS unload file type) --> y <-- reply 'yes' if 2nd, 3rd file for unload data type
#3d. uvhd c9bcaa0p/c9spcant r677 - investigate output from imsbmcx2 =========================== #3e. uvhd c9bcaa0p/c9spcant r677a - option 'a' to see EBCDIC data in ASCII ===========================
#3f. uvLp15 ctl/c9bcaa0p.seq - print the sequence# control file ======================= to document segments found in unload file
Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#4a. uvLp15 ctl/c9bcaa0p.seq - print the sequence# control file ======================= (if not already done above in 3d)
#4b. cp ctl/c9bcaa0p.seq ctl/c9bcaa0p.gen - copy & rename for .gen file ====================================
#4c. vi ctl/c9bcaa0p.gen - edit file, convert to format required by uvdata3 =================== - rcs= from the right hand size in c9bcaa0p.seq
# c9bcaa0p.gen - control file for uvcopy conversion jobs # to pipe delimit files from IMS dumps c9spcaac cpy=c9spcaac rcs=03032 c9spcacu cpy=c9spcacu rcs=00923 c9spcapl cpy=c9spcapl rcs=00219 c9spcapr cpy=c9spcapr rcs=00369 c9spcant cpy=c9spcant rcs=00664 c9spcaad cpy=c9spcaad rcs=01554 c9spcapy cpy=c9spcapy rcs=00425 c9spcacl cpy=c9spcacl rcs=00677 c9spcaal cpy=c9spcaal rcs=00941 c9spcaaj cpy=c9spcaaj rcs=00408
#5a. cp /opt/apps/libs/cpam/cpys/c9spcaac.cpy IMS/cpyrs ================================================== - - - etc - - - cp /opt/apps/libs/cpam/cpys/c9spcant.cpy IMS/cpyrs ================================================== - we will use c9spcant for the 1st demo conversion below
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#6a. vi cpyrs/c9spcaac.cpy ===================== #6b. vi cpyrs/c9spcant.cpy ===================== - - - etc - - -
These jobs are the heart of these procedures. They generate conversion jobs for all segments based on the copybooks. Following these automatic generations we will modify & execute them 1 at a time.
#7. Convert copybooks to record layouts (cobmaps) for following jobs
#7a. uvcopyx cobmap1 cpyrs maprs uop=q0i7p0 ======================================
#8a. uvcopyx uvdata2 maprs pfxr1 uop=q0i7 ====================================
#9a. uvcopyx genpipe2 maprs pfpr1 uop=q0i7 =====================================
#10a. uvcopy uvdata3,fili1=ctl/c9bcaa0p.gen,fild2=pfpr1,fild3=pfpr2,uop=q0i7a1r2 ==========================================================================
#11. Generate the SQL Loader control file
#11a. uvcopyx sqlload2 maps sql1 uop=q0i7 ===================================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
During test/debug/optimize, you may need to modify the copybook, which requires rerunning steps 7-11 above. Script 'genpipeD' is provided for this. The script requires 2 args, copybook & control file, for example:
#12. genpipeD c9spcant.cpy c9bcaa0p.gen <-- regenerate 1 job at a time ==================================
The script outputs pfpr2/c9spcant & you would manually copy to pfprs/c9spcant in case there are modifications in existing pfprs you need to save.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#13. List all generated jobs for inspection to determine if changes required
#13a. uvlpd12 pfpr2 <-- list all files in subdir pfpr2 =============
#14a. cp pfpr2/c9spcant pfprs - copy to pfprs before modify/test/debug =======================
#15a. vi pfxr1/c9spcant - see if any packed/binary instructions =================
#16a. vi pfprs/c9spcant ================= - edit the segment conversion job to insert instructions from the corresponding pfxr1 job that preserve packed/binary fields (discarding the framework of the pfxr1 job). - the demo/sample on page '2E4' will clarify this procdedure
#17a. vi pfprs/c9spcant ================= - edit segment conversion job for other conditions as required: (record types, 2 extra bytes in unload files, etc)
#18a. uvlp12 pfprs/c9spcant <-- relist modified job =====================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#19a. export UVDATA1=c9bcaa0p =======================
#19b. export UVDATA2=c9bcaa9p.pipe ============================
#20a. uvcopy pfprs/c9spcaac =====================
#20b. uvcopy pfprs/c9spcant ===================== - - - etc - - -
#21. uvcopyxx 'pfprs/*' =================
Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
(using the c9spcant segment as an example)
22/23 Investigate input file to pipe delimit (was created by imsbmcx2) Note 2 methods shown since records have ASCII headers with EBCDIC data The 2nd method is the most useful to investigate the data to be converted by the pipe delimit jobs.
#22. uvhd c9bcaa0p/c9spcant r677 <-- view input headers =========================== (already pipe delimited)
#23. uvhd c9bcaa0p/c9spcant r677a <-- view EBCDIC data (option a=ASCII chars) ============================ following the headers
24-26 Investigate the output from the pipe delimit jobs and verify that the pipe delimited fields match the input fields as defined by the copybook.
#24. vi c9bcaa0p.pipe/c9spcant <-- investigate with vi =========================
#25. uvhd c9bcaa0p.pipe/c9spcant r1000ts2 <-- investigate with uvhd ==================================== - option r1000 to allow records up to 1000 bytes - option 't' since output is a 'text' file vs fixed records - option 's2' to space between the scale & data
#26a. uvcopy listpipe2,fili1=c9bcaa0p/c9spcant <-- all you have to enter ======================================== ,fili2=maprs/c9spcant,filo1=tmp/c9spcant.pipe.list,uop=f1s3 =========================================================== - utility job to create a report with pipe delimited data fields identified by copybook field names - Note that fili2, filo1,& options default as shown above (option f1= formfeed after each record, s3=stop after 3 records) - the report is created in tmp/... & you get a prompt as follows:
report=tmp/c9spcant.pipe.list, enter: vi/more/lp/etc (default uvlp12)
#26b. uvlp12 tmp/c9spcant.pipe.list <-- list report created by listpipe2 =============================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
This part will illustrate the operating instructions using segment c9spcant. The sequence#s below refer to the operating instructions on page '2D3'. Please refer back to them for steps that are not documented here.
The previous operating instructions are concise, but did not illustrate expected outputs, or discuss possible problems & solutions.
This section will illustrate solutions to problems I have obsrved, such as the 2 extra bytes in the unload file data that is not accounted for in the copybooks.
You can look ahead to the data file dumps shonw on page '2E10' to see the input data to be converted & observe the 2 extra bytes problem. See input data on page '2E10' --->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#6. Edit/Modify/optimize copybooks for Relational DBs
05 cant-seg-notn-acct-cust. 10 cant-agg-notn-memo-key. 15 cant-cd-type-notn pic x. 0000 0000 001 15 cant-agg-dte-notn-estabd. 20 cant-dte-yr-notn-estabd pic 9(2). 0001 0002 002 n 2 20 cant-dte-mo-notn-estabd pic 9(2). 0003 0004 002 n 2 20 cant-dte-day-notn-estabd pic 9(2). 0005 0006 002 n 2 15 cant-agg-dte-flwup-memo redefines cant-agg-dte-notn-esta 20 cant-dte-yr-flwup-memo pic 9(2). 0001 0002 002 n 2 20 cant-dte-mo-flwup-memo pic 9(2). 0003 0004 002 n 2 20 cant-dte-day-flwup-memo pic 9(2). 0005 0006 002 n 2 15 cant-time-notn-estabd usage comp-3 pic s9(7). 0007 0010 004pns 7 10 cant-agg-dte-temp-notn. 15 cant-dte-yr-temp-notn pic 9(2). 0011 0012 002 n 2 15 cant-dte-mo-temp-notn pic 9(2). 0013 0014 002 n 2 15 cant-dte-day-temp-notn pic 9(2). 0015 0016 002 n 2 10 cant-agg-dte-bl-notn. 15 cant-dte-yr-bl-notn pic 9(2). 0017 0018 002 n 2 15 cant-dte-mo-bl-notn pic 9(2). 0019 0020 002 n 2 15 cant-dte-day-bl-notn pic 9(2). 0021 0022 002 n 2 10 cant-num-itm-ajmt pic 9(3). 0023 0025 003 n 3 10 cant-flg-rmt-pe pic x. 0026 0026 001 10 cant-agg-cd-optr-notn-orig. 15 cant-cd-reg-optr-orig pic x(1). 0027 0027 001 15 cant-cd-ofc-optr-orig pic x(2). 0028 0029 002 15 cant-cd-mgr-optr-orig pic x(1). 0030 0030 001 15 cant-cd-setn-optr-orig pic x(2). 0031 0032 002 15 cant-cd-emp-optr-orig pic x(2). 0033 0034 002 10 cant-agg-cd-optr-flwup. 15 cant-cd-reg-optr-flwup pic x(1). 0035 0035 001 15 cant-cd-ofc-optr-flwup pic x(2). 0036 0037 002 15 cant-cd-mgr-optr-flwup pic x(1). 0038 0038 001 15 cant-cd-setn-optr-flwup pic x(2). 0039 0040 002 15 cant-cd-emp-optr-flwup pic x(2). 0041 0042 002 10 cant-cd-type-notn-flwup pic x(1). 0043 0043 001 10 cant-cd-notn-stat-prev pic x(1). 0044 0044 001 10 cant-nme-cust-cllng-pty pic x(25). 0045 0069 025 10 cant-agg-cd-type-cust-cntct. 15 cant-cd-cust-cntct pic x(2). 0070 0071 002 15 cant-cd-type-notn-cntct pic x(1). 0072 0072 001 15 cant-num-trans-cust pic 9(1). 0073 0073 001 n 1 15 cant-cd-cust-spcl-stdy pic x(2). 0074 0075 002 10 cant-txt-ln-notn-1-6 pic x(408). 0076 0483 408 10 cant-txt-ln-notn redefines cant-txt-ln-notn-1-6 occurs 6 0076 0143 068 10 cant-agg-txt-notn-vew-2 redefines cant-txt-ln-notn-1-6. 15 cant-txt-ln-notn-1 pic x(68). 0076 0143 068 15 cant-txt-ln-notn-2-6 pic x(340). 0144 0483 340 10 filler001 pic x(50). 0484 0533 050 *RCSZ=00534 0534
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#6. Edit/Modify/Optimize the copybooks for Relational databases - eliminate redefines, eliminate occurs, combine yr/mth/day to 1 field
#6a. vi cpyrs/c9spcant.cpy <-- modify/optimize copybook =====================
* c9spcant notations segment *nov2001 - modified to create pipe delimited ascii for oracl * - combine 3*2 byte yr/mth/day to 1 6 byte date * - eliminate occurs & redefs (not allowed in relational dbs 05 cant-seg-notn-acct-cust. 10 cant-agg-notn-memo-key. 15 cant-cd-type-notn pic x. 0000 0000 001 15 cant-agg-dte-notn-estabd pic 9(6). 0001 0006 006 n 6 15 cant-time-notn-estabd usage comp-3 pic s9(7). 0007 0010 004pns 7 10 cant-agg-dte-temp-notn pic 9(6). 0011 0016 006 n 6 10 cant-agg-dte-bl-notn pic 9(6). 0017 0022 006 n 6 10 cant-num-itm-ajmt pic 9(3). 0023 0025 003 n 3 10 cant-flg-rmt-pe pic x. 0026 0026 001 10 cant-agg-cd-optr-notn-orig. 15 cant-cd-reg-optr-orig pic x(1). 0027 0027 001 15 cant-cd-ofc-optr-orig pic x(2). 0028 0029 002 15 cant-cd-mgr-optr-orig pic x(1). 0030 0030 001 15 cant-cd-setn-optr-orig pic x(2). 0031 0032 002 15 cant-cd-emp-optr-orig pic x(2). 0033 0034 002 10 cant-agg-cd-optr-flwup. 15 cant-cd-reg-optr-flwup pic x(1). 0035 0035 001 15 cant-cd-ofc-optr-flwup pic x(2). 0036 0037 002 15 cant-cd-mgr-optr-flwup pic x(1). 0038 0038 001 15 cant-cd-setn-optr-flwup pic x(2). 0039 0040 002 15 cant-cd-emp-optr-flwup pic x(2). 0041 0042 002 10 cant-cd-type-notn-flwup pic x(1). 0043 0043 001 10 cant-cd-notn-stat-prev pic x(1). 0044 0044 001 10 cant-nme-cust-cllng-pty pic x(25). 0045 0069 025 10 cant-agg-cd-type-cust-cntct. 15 cant-cd-cust-cntct pic x(2). 0070 0071 002 15 cant-cd-type-notn-cntct pic x(1). 0072 0072 001 15 cant-num-trans-cust pic 9(1). 0073 0073 001 n 1 15 cant-cd-cust-spcl-stdy pic x(2). 0074 0075 002 * 10 cant-txt-ln-notn-1-6 pic x(408). *10 cant-txt-ln-notn redefines cant-txt... occurs 6 pic x(68 *nov2001 - eliminate occurs & redefs for pipe delimit & orac 10 cant-txt-ln-notn-1 pic x(68). 0076 0143 068 10 cant-txt-ln-notn-2 pic x(68). 0144 0211 068 10 cant-txt-ln-notn-3 pic x(68). 0212 0279 068 10 cant-txt-ln-notn-4 pic x(68). 0280 0347 068 10 cant-txt-ln-notn-5 pic x(68). 0348 0415 068 10 cant-txt-ln-notn-6 pic x(68). 0416 0483 068 10 filler001 pic x(50). 0484 0533 050 *RCSZ=00534 0534
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
After modifying copybook, we must reconvert the copybook to cobmap, and regenerate the preserve packed fields,& pipe delimit jobs. We will use the 'genpipeD' script which runs all steps required for any one specified job. (script genpipeD is listed at the end of this part).
#12. genpipeD c9spcant.cpy c9bcaa0p.gen - regenerate from copybook ==================================
#14. cp pfpr2/c9spcant pfprs - copy to pfprs before manual edits =======================
#15a. vi pfxr1/c9spcant - see if any preserve packed/binary instructions =================
opr='JOBNAME c9spcant - genby: cobmap1,uvdata2,uvdata3' uop=q0 was=a33000b33000 fili1=${UVDATA1}/c9spcant,rcs=00534,typ=RSF filo1=${UVDATA2}/c9spcant,rcs=00534,typ=RSF @run opn all loop get fili1,a0 skp> eof mvc b0(00534),a0 move rec to outarea before field prcsng tra b0(00534) translate entire outarea to ASCII # --- <-- insert R/T tests here for redefined records mvc b7(4),a7 pns cant-time-notn-estabd <------NOTE** put1 put filo1,b0 skp loop eof cls all eoj
Note |
|
#16. vi pfprs/c9spcant - insert instrns to preserve packed/binary fields ================= :19 - set cursor to line 19 :r pfxr1/c9spcant - read in the pfxr1 job to preserve packed fields :dd - delete framework of pfxr1 job :wq - write modified job
#19. uvlp12 pfprs/c9spcant - list modified job =====================
Please see the modified job on the next page --->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
opr='c9spcant c9spcant - generated by cobmap1,genpipe2,uvdata3' # - uvcopy job to convert & fix data fields 100 bytes apart # for compress & '|' delimit (see 'var' instrn below) uop=q0 was=a33000b33000c999000d33000e33000r33000 fili1=${UVDATA1}/c9spcant,rcs=00664,typ=RSF filo1=${UVDATA2}/c9spcant,rcs=9000,typ=LSTtd #<-- DOS option d for CR/LF @run opn all open files loop get fili1,r0 get next input rec (fixed size on fili1) skp> eof (cc set > at EOF) mvc a0(00534),r128 reposition EBCDIC data for cobmap conversion ## mvc a0(00534),r130 some IMSdumps have 2 extra bytes not in copybook mvc b0(00534),a0 move input area a to area b tra b0(00534) for EBCDIC input, else remove #-------------------------------------------------------------------------- # --- <-- insert R/T tests if redef records #-------------------------------------------------------------------------- # --- <-- insert instrns from uvdata2 if packed fields mvc b7(4),a7 pns cant-time-notn-estabd <--NOTE** #-------------------------------------------------------------------------- mvc c0(1),b0(1) #1 cant-cd-type-notn mvc c100(6),b1(6) #2 cant-agg-dte-notn-estabd bal dat6,'100' edt c200(9),b7(4p),'+zzzzzzzzz9' #3 cant-time-notn-estabd sqz c200(9),' ' mvc c300(6),b11(6) #4 cant-agg-dte-temp-notn bal dat6,'300' mvc c400(6),b17(6) #5 cant-agg-dte-bl-notn bal dat6,'400' mvc c500(3),b23(3) #6 cant-num-itm-ajmt mvc c600(1),b26(1) #7 cant-flg-rmt-pe mvc c700(1),b27(1) #8 cant-cd-reg-optr-orig mvc c800(2),b28(2) #9 cant-cd-ofc-optr-orig mvc c900(1),b30(1) #10 cant-cd-mgr-optr-orig mvc c1000(2),b31(2) #11 cant-cd-setn-optr-orig mvc c1100(2),b33(2) #12 cant-cd-emp-optr-orig mvc c1200(1),b35(1) #13 cant-cd-reg-optr-flwup mvc c1300(2),b36(2) #14 cant-cd-ofc-optr-flwup mvc c1400(1),b38(1) #15 cant-cd-mgr-optr-flwup mvc c1500(2),b39(2) #16 cant-cd-setn-optr-flwup mvc c1600(2),b41(2) #17 cant-cd-emp-optr-flwup mvc c1700(1),b43(1) #18 cant-cd-type-notn-flwup mvc c1800(1),b44(1) #19 cant-cd-notn-stat-prev mvc c1900(25),b45(25) #20 cant-nme-cust-cllng-pty mvc c2000(2),b70(2) #21 cant-cd-cust-cntct mvc c2100(1),b72(1) #22 cant-cd-type-notn-cntct mvc c2200(1),b73(1) #23 cant-num-trans-cust mvc c2300(2),b74(2) #24 cant-cd-cust-spcl-stdy mvc c2400(68),b76(68) #25 cant-txt-ln-notn-1 mvc c2500(68),b144(68) #26 cant-txt-ln-notn-2 mvc c2600(68),b212(68) #27 cant-txt-ln-notn-3 mvc c2700(68),b280(68) #28 cant-txt-ln-notn-4 mvc c2800(68),b348(68) #29 cant-txt-ln-notn-5 mvc c2900(68),b416(68) #30 cant-txt-ln-notn-6 mvc c3000(50),b484(50) #31 filler001 #-------------------------------------------------------------------- var1 var d0(12000),c0(100),0031,'|' convert to pipe delimited trt d0(12000),$trtchr ensure printable characters mvc e0(128),r0 copy input hdr already pipe delimited mvc e128(12000),d0 append data pipe delimited by this job clr c0(003100),' ' clear fixed field area for next record put1 put filo1,e0 write to output area from area 'e' skp loop # eof cls all eoj @pf2=genpipe1.sub # area 'r' get input, 128 byte pipe delim ASCII hdr + EBCDIC data # area 'a' EBCDIC data moved here so copybook map dsplcmnts will match # area 'b' translated to ASCII, in case mainframe EBCDIC file (with packed?) # area 'c' data fields fixed 100 bytes apart in prep for var | delimit instrn # area 'd' output pipe delimited & squeezed together # area 'e' combines 128 byte input header (in r) with area d var output
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
We can see from the uvhd that these 2 bytes are character data, so we can insert instructions to translate to ASCII & add the '|' pipe delimiter for this extra field.
Note that genpipe2 has generated the instructions to pipe delimit all the other fields that were in the copybook.
#17a. vi pfprs/c9spcant - edit the generated job ================= - edit/fix other conditions required (2 extra bytes in this case)
Note |
|
#17b. uvlp12 pfprs/c9spcant - list job after all corrections inserted =====================
Please see completed job (ready for execute/test/debug) on next page --->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Note |
|
opr='c9spcant c9spcant - generated by cobmap1,genpipe2,uvdata3' # - uvcopy job to convert & fix data fields 100 bytes apart # NOTE - MODIFIED for 2 EXTRA BYTES PROBLEM ------- see markers '<--2EXTRA**' uop=q0 was=a33000b33000c999000d33000e33000r33000 fili1=${UVDATA1}/c9spcant,rcs=00664,typ=RSF filo1=${UVDATA2}/c9spcant,rcs=9000,typ=LSTtd #<-- DOS option d for CR/LF @run opn all open files loop get fili1,r0 get next input rec (fixed size on fili1) skp> eof (cc set > at EOF) ### mvc a0(00534),r128 reposition EBCDIC data for cobmap mvc a0(00534),r130 <-- bypass 2 EXTRA bytes not in cpybk<--2EXTRA** ### - see extra code added below <--2EXTRA** mvc b0(00534),a0 move input area a to area b tra b0(00534) for EBCDIC input, else remove #-------------------------------------------------------------------------- # --- <-- insert instrns from uvdata2 if packed fields mvc b7(4),a7 pns cant-time-notn-estabd <-- ONLY 1 FROM PFXR1 #-------------------------------------------------------------------------- mvc c0(1),b0(1) #1 cant-cd-type-notn mvc c100(6),b1(6) #2 cant-agg-dte-notn-estabd bal dat6,'100' edt c200(9),b7(4p),'+zzzzzzzzz9' #3 cant-time-notn-estabd sqz c200(9),' ' # ------- instrns removed to fit 1 page, see prior listing --------- mvc c2900(68),b416(68) #30 cant-txt-ln-notn-6 mvc c3000(50),b484(50) #31 filler001 #-------------------------------------------------------------------- var1 var d0(12000),c0(100),0031,'|' convert to pipe delimited trt d0(12000),$trtchr ensure printable characters mvc e0(128),r0 copy input hdr already pipe delimited ## ##allow for 2 extra bytes, bypassed above so copybook & data match <--2EXTRA** mvc e128(2),r128 2 extra bytes at begin record <--2EXTRA** tra e128(2) assume char, translate to ASCII <--2EXTRA** mvc e130(1),'|' supply pipe for extra field <--2EXTRA** mvc e131(12000),d0 append pipe delimited by this job <--2EXTRA** ## mvc e128(12000),d0 append pipe delimited by this job <--ORIG## ## clr c0(003100),' ' clear fixed field area for next record put1 put filo1,e0 write to output area from area 'e' skp loop eof cls all eoj @pf2=genpipe1.sub
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#19a. export UVDATA1=c9bcaa0p - export I/O data directories =======================
#19b. export UVDATA2=c9bcaa0p.pipe ============================
#20. uvcopy pfprs/c9spcant <-- execute pipe delimit job =====================
We will now investigate the input & output files to verify proper operation. Please see various display methods on the following pages --->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#22a. vi ctl/c9bcaa0p.seq - get datafile rcsz from seq# ctlfile ===================
#22a. uvhd c9bcaa0p/c9spcant r677s2 - investigate input to c9spcant =============================
1 2 3 4 5 6 r# 1 0123456789012345678901234567890123456789012345678901234567890123
0 C9SPCANT|06|03|00000001|C9SPCACU|02|02|00000001|110216 4355444573373373333333374355444573373373333333373333332222222222 393031E4C06C03C00000001C39303135C02C02C00000001C1102160000000000
64 |02010001250|00|P801196718869457| ||||||0030|00536| 7333333333337337533333333333333372222222222222277777733337333337 C02010001250C00C0801196718869457C00000000000000CCCCCC0030C00536C
128 ..........iE|......@.@.@....@.......@.......@@@.K.K....@....@@@@ DFFFFFFFF8647FFFFFF4F4F4FFFF4EFEDCFF4EFEDCFF444E4C4EDDE4DCDE4444 780119671895C88032800000000002034620020346200002B1B3952093730000
192 @@@@@@@@....@@.......K..@.....k..@.....kM...]k...@....@...K..@.. 44444444DECF44FFFFFFF4DC4EDDCE6DC4EDDEE64DCC56EDE4CCDD4DED4EC4FF 000000006251002447053B34034373B31034363BD596DB253045970339B21036
256 ..K.`..`..K..K...@...@.....@.@....oooook..{@...............K..@@ FF4F6FF6FF4DD4ECC4ECC4DEDCC4C4CDDC666666CC74CFFFFFFFFFFFFFF4CD44 81B3018088B65B29702970143910503692FFFFFB94B0365634206556562B9900
320 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 4444444444444444444444444444444444444444444444444444444444444444 0000000000000000000000000000000000000000000000000000000000000000 ... all EBCDIC blanks to end record (rcs=677) not shown ...
Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#23a. uvhd c9bcaa0p/c9spcant r677s2a - investigate with option 'a' ============================== option 'a' shows character lines in ASCII option 's2' for space between the 3 line groups (chars/zones/digits)
1 2 3 4 5 6 r# 1 0123456789012345678901234567890123456789012345678901234567890123
0 C.S&CA+T@..@..@........@C.S&CACU@..@..@........@................ 4355444573373373333333374355444573373373333333373333332222222222 393031E4C06C03C00000001C39303135C02C02C00000001C1102160000000000
64 @...........@..@&...............@..............@@@@@@....@.....@ 7333333333337337533333333333333372222222222222277777733337333337 C02010001250C00C0801196718869457C00000000000000CCCCCC0030C00536C
*2EXTRA*128 P80119671hiE@880328 0 0 0000 S0TMF20 S0TMF20 S.A.TRNS RCPT 'P8' DFFFFFFFF8647FFFFFF4F4F4FFFF4EFEDCFF4EFEDCFF444E4C4EDDE4DCDE4444 780119671895C88032800000000002034620020346200002B1B3952093730000
192 OSE1 2447053.LD TMLGT,LA TMLWT,(NIF),SNT DERP LTR.SA 36 44444444DECF44FFFFFFF4DC4EDDCE6DC4EDDEE64DCC56EDE4CCDD4DED4EC4FF 000000006251002447053B34034373B31034363BD596DB253045970339B21036
256 81.3-18-88.ON.SIG SIG JULIA E CORB?????,ID# C65634206556562.IR FF4F6FF6FF4DD4ECC4ECC4DEDCC4C4CDDC666666CC74CFFFFFFFFFFFFFF4CD44 81B3018088B65B29702970143910503692FFFFFB94B0365634206556562B9900 ... all EBCDIC blanks to end record (rcs=677) not shown ...
#23b. --> i3f1 <-- list out 1st 3 records (with uvhd immediate list) ====== - print out (same as display above)
The data from byte 128 onwards should match the copybook record layout shown on page '2E3'.
Packed fields are easy to identify & relate to the copybook: ('.'s or lower case alpha on char line & sign 'C' or 'D' in units digit) Note the packed field above in bytes 137-140 'hiE@' x'8869457C', which must be the packed field in bytes 7-10 of the cobmap on page '2E3'.
The data record has 128 byte hdr, so 137-140 is bytes 9-12 zero relative, which is 2 bytes more than expected by the copybook layout ??????? So the 'P8' above is extra data that must be handled by special editing to the pipe delimiting job (see page '2E13' ahead). I have seen this problem on 4 of the 11 segments in c9bcaa0p ????
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Here are the 1st 3 lines as shown with 'vi' except I have created a blank line between the records.
#24a. vi c9bcaa0p.pipe/c9spcant =========================
C9SPCANT|06|03|00000001|C9SPCACU|02|02|00000001|110216 |02010001250|00|P801196718869457| ||||||0030|00536 F),SNT DERP LTR.SA 3681.3-18-88.ON.SIG|SIG JULIA E CORB?????,ID# C65634206556562.IR|||||2015896356|
C9SPCANT|06|03|00000002|C9SPCACU|02|02|00000001|110216 |02010001250|00|X799997958749667| ||||||0030|00536 37 PACIFIC| CC SB NEWARK NEWARK| ST NJ ZIP 07105 ST NJ ZIP 07105||||9
C9SPCANT|06|03|00000003|C9SPCACU|02|02|00000001|110216 |02010001250|00|X800891738743566| ||||||0030|00536 DUE DATE 08-27-91| LR/IN 00819 00-00-00| LR/OUT 00819
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#25a. uvhd c9bcaa0p.pipe/c9spcant r1000ts2 ====================================
1 2 3 4 5 6 r# 1 0123456789012345678901234567890123456789012345678901234567890123
0 C9SPCANT|06|03|00000001|C9SPCACU|02|02|00000001|110216 4355444573373373333333374355444573373373333333373333332222222222 393031E4C06C03C00000001C39303135C02C02C00000001C1102160000000000
64 |02010001250|00|P801196718869457| ||||||0030|00536| 7333333333337337533333333333333372222222222222277777733337333337 C02010001250C00C0801196718869457C00000000000000CCCCCC0030C00536C
128 P8|0|2011/96/71|+8869457|1988/03/28|0000/00/00|000||S|0T|M|F2|0| 5373733332332337233333337333323323373333233233733377573574743737 08C0C2011F96F71CB8869457C1988F03F28C0000F00F00C000CC3C04CDC62C0C
192 S|0T|M|F2|0|||S.A.TRNS RCPT|OS|E|1||2447053.LD TMLGT,LA TMLWT,(N 5735747437377752425545254557457473773333333244254445244254455224 3C04CDC62C0CCC3E1E42E302304CF3C5C1CC2447053EC404DC74CC104DC74C8E
256 IF),SNT DERP LTR.SA 3681.3-18-88.ON.SIG|SIG JULIA E CORB?????,ID 4422545244552455254233332323323324425447544245444242445433333244 969C3E4045200C42E3103681E3D18D88EFEE397C3970A5C910503F22FFFFFC94
320 # C65634206556562.IR|||||2015896356|.. 22433333333333333245777773333333333700 30365634206556562E92CCCCC2015896356CDA
Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#26a. uvcopy listpipe2,fili1=c9bcaa0p/c9spcant,filo1=tmp/c9spcant.pipe.list ===================================================================== - utility job to create a report with pipe delimited data fields identified by copybook field names
#26b. uvlp12 tmp/c9spcant.pipe.list <-- list the report created by listpipe2 =============================
listpipe2 - list '|' delimited files with copybook fieldnames - to verify files created by genpipe2 (see SQLcnvrt.doc) datafile=subdir/filename copybookmap=maprs/filename datetime=2001/11/21_11:48:04 datafile = c9bcaa0p.pipe/c9spcant, record# = 1, field count = 52
C9SPCANT|06|03|00000001|C9SPCACU|02|02|00000001|110216 |02010001250|00|P801196718869457| ||||||0030|00536| P8|0|2011/96/71|+8869457|1988/03/28|0000/00/00|000||S|0T|M|F2|0| S|0T|M|F2|0|||S.A.TRNS RCPT|OS|E|1||2447053.LD TMLGT,LA TMLWT,(N IF),SNT DERP LTR.SA 3681.3-18-88.ON.SIG|SIG JULIA E CORB?????,ID # C65634206556562.IR|||||2015896356|
SEQ# FIELDNAME COBOL PICTURE DATA ================================================================ 001 cant-cd-type-notn x. P8 002 cant-agg-dte-notn-estab 9(6). 0 003 cant-time-notn-estabd s9(7). 2011/96/71 004 cant-agg-dte-temp-notn 9(6). +8869457 005 cant-agg-dte-bl-notn 9(6). 1988/03/28 006 cant-num-itm-ajmt 9(3). 0000/00/00 007 cant-flg-rmt-pe x. 000 008 cant-cd-reg-optr-orig x(1). 009 cant-cd-ofc-optr-orig x(2). S 010 cant-cd-mgr-optr-orig x(1). 0T 011 cant-cd-setn-optr-orig x(2). M 012 cant-cd-emp-optr-orig x(2). F2 013 cant-cd-reg-optr-flwup x(1). 0 014 cant-cd-ofc-optr-flwup x(2). S 015 cant-cd-mgr-optr-flwup x(1). 0T 016 cant-cd-setn-optr-flwup x(2). M 017 cant-cd-emp-optr-flwup x(2). F2 018 cant-cd-type-notn-flwup x(1). 0 019 cant-cd-notn-stat-prev x(1). 020 cant-nme-cust-cllng-pty x(25). 021 cant-cd-cust-cntct x(2). S.A.TRNS RCPT 022 cant-cd-type-notn-cntct x(1). OS 023 cant-num-trans-cust 9(1). E 024 cant-cd-cust-spcl-stdy x(2). 1 025 cant-txt-ln-notn-1 x(68). 026 cant-txt-ln-notn-2 x(68). 2447053.LD TMLGT,LA TMLWT,(NIF),SNT DERP LTR.SA 36 027 cant-txt-ln-notn-3 x(68). SIG JULIA E CORB?????,ID# C65634206556562.IR 028 cant-txt-ln-notn-4 x(68). 029 cant-txt-ln-notn-5 x(68). 030 cant-txt-ln-notn-6 x(68). 031 filler001 x(50).
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
This part will illustrate the conversion using segment c9spcapl, which has 2 record types. Multiple record types are not allowed in Relational DBs so we will split the records to 2 files for loading 2 Relational tables.
Please see the 2 record types in the segment data uvhd dumps on page '2F10' The R/T code is defined in the 1st byte of the copybook, but this is byte 128 of the uvhd dump, due to the 128 byte header/prefix inserted by imsbmcx2 when it splits the segments from the IMS unload file.
R/T 'L' |
|
R/T 'I' |
|
The sequence#s below refer to the operating instructions on page '2D3'. Please refer back to them for steps that are not documented here.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
We have removed most of the '88' levels to save space in the listing, but we have yet to make the optimizations for Relational DBs (see next listing).
* c9spcapl - plan segment 05 capl-seg-pln. 10 capl-agg-fld-key-pln. 15 capl-cd-type-pln pic x(1). 0000 0000 001 88 capl-l88-cd-pln-ord-instl value 'I'. 88 capl-l88-cd-pln-ls value 'L'. 88 capl-l88-cd-pln-ocp value 'O'. 15 capl-agg-num-ord. 20 capl-cd-pe-acct pic 9(2). 0001 0002 002 n 2 20 capl-num-seq-ord pic 9(7). 0003 0009 007 n 7 15 capl-agg-cd-usoc redefines capl-agg-num-ord. 20 capl-cd-usoc pic x(5). 0001 0005 005 20 filler001 pic x(4). 0006 0009 004 15 capl-agg-cd-pln-ls redefines capl-agg-num-ord. 20 capl-cd-pln-ls pic x(4). 0001 0004 004 20 filler002 pic x(3). 0005 0007 003 20 capl-num-seq-pln-ls pic 9(2). 0008 0009 002 n 2 10 capl-agg-data-pln-ocp. 15 capl-agg-cd-tax-exm-cust. 20 capl-cd-tax-exm-fed-cust pic x(1). 0010 0010 001 20 capl-cd-tax-exm-ste-cust pic x(1). 0011 0011 001 20 capl-cd-tax-exm-cny-cust pic x(1). 0012 0012 001 20 capl-cd-tax-exm-cty-cust pic x(1). 0013 0013 001 15 filler003 pic x(1). 0014 0014 001 15 capl-agg-dte-estabd-ocp. 20 capl-dte-yr-estabd-ocp pic 9(2). 0015 0016 002 n 2 20 capl-dte-mo-estabd-ocp pic 9(2). 0017 0018 002 n 2 20 capl-dte-day-estabd-ocp pic 9(2). 0019 0020 002 n 2 15 capl-agg-dte-dscnct-ocp. 20 capl-dte-yr-dscnct-ocp pic 9(2). 0021 0022 002 n 2 20 capl-dte-mo-dscnct-ocp pic 9(2). 0023 0024 002 n 2 20 capl-dte-day-dscnct-ocp pic 9(2). 0025 0026 002 n 2 15 capl-agg-dte-bld-to-last. 20 capl-dte-yr-bld-to-last pic 9(2). 0027 0028 002 n 2 20 capl-dte-mo-bld-to-last pic 9(2). 0029 0030 002 n 2 20 capl-dte-day-bld-to-last pic 9(2). 0031 0032 002 n 2 15 capl-cd-stat-itm-ord-pnd pic x(3). 0033 0035 003 15 capl-flg-chrg-wav-prd-min pic x(1). 0036 0036 001 15 capl-agg-num-ord-ocp. 20 capl-cd-pe-acct-ocp pic 9(2). 0037 0038 002 n 2 20 capl-num-seq-ord-ocp pic 9(7). 0039 0045 007 n 7 15 capl-cd-type-ord pic x(1). 0046 0046 001 15 capl-agg-num-ord-ocp-pnd. 20 capl-cd-pe-acct-ocp-pnd pic 9(2). 0047 0048 002 n 2 20 capl-num-seq-ord-ocp-pnd pic 9(7). 0049 0055 007 n 7 15 capl-agg-dte-ocp-chrg-init. 20 capl-dte-yr-ocp-chrg-init pic 9(2). 0056 0057 002 n 2 20 capl-dte-mo-ocp-chrg-init pic 9(2). 0058 0059 002 n 2 20 capl-dte-day-ocp-chrg-init pic 9(2). 0060 0061 002 n 2 15 capl-agg-dte-bld-fr-last. 20 capl-dte-yr-bld-fr-last pic 9(2). 0062 0063 002 n 2 20 capl-dte-mo-bld-fr-last pic 9(2). 0064 0065 002 n 2 20 capl-dte-day-bld-fr-last pic 9(2). 0066 0067 002 n 2 15 capl-agg-cd-ocp-lec-pln occurs 3. 20 capl-cd-ocp-lec-npa pic x(3). 0068 0070 003 20 capl-cd-ocp-lec-ste pic x(2). 0071 0072 002 10 capl-agg-data-pln-ord-instl redefines capl-agg-data-pln- 15 capl-agg-dte-sl-instl. 20 capl-dte-yr-sl-instl pic 9(2). 0010 0011 002 n 2 20 capl-dte-mo-sl-instl pic 9(2). 0012 0013 002 n 2 20 capl-dte-day-sl-instl pic 9(2). 0014 0015 002 n 2 15 capl-amt-instl-ord comp-3 pic s9(5)v9(2). 0016 0019 004pns 7 15 capl-qty-prd-instlmts comp-3 pic 9(3). 0020 0021 002pn 3 15 capl-qty-prd-instlmts-remng comp-3 pic 9(3). 0022 0023 002pn 3 15 capl-amt-instl-ord-moly comp-3 pic s9(5)v9(2). 0024 0027 004pns 7 15 capl-amt-bal-remng comp-3 pic s9(5)v9(2). 0028 0031 004pns 7 15 capl-amt-bal-remng-prev comp-3 pic s9(5)v9(2). 0032 0035 004pns 7 15 capl-flg-pmt-down pic x(1). 0036 0036 001 15 capl-cd-type-ord-pnd-instl pic x(1). 0037 0037 001 15 capl-cd-auth-acaps pic x(15). 0038 0052 015 15 capl-num-ord-pur pic x(25). 0053 0077 025 10 filler004 pic x(8). 0083 0090 008 *RCSZ=00091 0091
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#6. Edit/Modify/optimize copybooks for Relational DBs
Note |
|
* c9spcapl - plan segment 05 capl-seg-pln. 10 capl-agg-fld-key-pln. 15 capl-cd-type-pln pic x(1). 0000 0000 001 88 capl-cd-pln-ord-instl value 'I'. 88 capl-cd-pln-ls value 'L'. 88 capl-cd-pln-ocp value 'O'. 15 capl-agg-num-ord. 20 capl-cd-pe-acct pic 9(2). 0001 0002 002 n 2 20 capl-num-seq-ord pic 9(7). 0003 0009 007 n 7 10 capl-agg-data-pln-ocp. 15 capl-agg-cd-tax-exm-cust pic x(4). 0010 0013 004 15 filler001 pic x(1). 0014 0014 001 15 capl-agg-dte-estabd-ocp pic 9(6). 0015 0020 006 n 6 15 capl-agg-dte-dscnct-ocp pic 9(6). 0021 0026 006 n 6 15 capl-agg-dte-bld-to-last pic 9(6). 0027 0032 006 n 6 15 capl-cd-stat-itm-ord-pnd pic x(3). 0033 0035 003 15 capl-flg-chrg-wav-prd-min pic x(1). 0036 0036 001 15 capl-agg-num-ord-ocp. 20 capl-cd-pe-acct-ocp pic 9(2). 0037 0038 002 n 2 20 capl-num-seq-ord-ocp pic 9(7). 0039 0045 007 n 7 15 capl-cd-type-ord pic x(1). 0046 0046 001 15 capl-agg-num-ord-ocp-pnd. 20 capl-cd-pe-acct-ocp-pnd pic 9(2). 0047 0048 002 n 2 20 capl-num-seq-ord-ocp-pnd pic 9(7). 0049 0055 007 n 7 15 capl-agg-dte-ocp-chrg-init pic 9(6). 0056 0061 006 n 6 15 capl-agg-dte-bld-fr-last pic 9(6). 0062 0067 006 n 6 * 15 capl-agg-cd-ocp-lec-pln occurs 3. *occurs removed for relational db, append seq#s on fieldname 20 capl-cd-ocp-lec-1 pic x(5). 0068 0072 005 20 capl-cd-ocp-lec-2 pic x(5). 0073 0077 005 20 capl-cd-ocp-lec-3 pic x(5). 0078 0082 005 10 capl-agg-data-pln-ord-instl redefines capl-agg-data-pln- 15 capl-agg-dte-sl-instl pic 9(6). 0010 0015 006 n 6 15 capl-amt-instl-ord comp-3 pic s9(5)v9(2). 0016 0019 004pns 7 15 capl-qty-prd-inst comp-3 pic 9(3). 0020 0021 002pn 3 15 capl-qty-prd-inst-rem comp-3 pic 9(3). 0022 0023 002pn 3 15 capl-amt-instl-ord-moly comp-3 pic s9(5)v9(2). 0024 0027 004pns 7 15 capl-amt-bal-remng comp-3 pic s9(5)v9(2). 0028 0031 004pns 7 15 capl-amt-bal-remng-prev comp-3 pic s9(5)v9(2). 0032 0035 004pns 7 15 capl-flg-pmt-down pic x(1). 0036 0036 001 15 capl-cd-type-ord-pnd-instl pic x(1). 0037 0037 001 15 capl-cd-auth-acaps pic x(15). 0038 0052 015 15 capl-num-ord-pur pic x(25). 0053 0077 025 10 filler002 pic x(8). 0083 0090 008 *RCSZ=00091 0091
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
After modifying copybook, we must reconvert the copybook to cobmap, and regenerate the preserve packed fields,& pipe delimit jobs. We will use the 'genpipeD' script which runs all steps required for any one specified job. (script genpipeD is listed at the end of this part).
#12. genpipeD c9spcapl.cpy c9bcaa0p.gen - regenerate from copybook ==================================
#14. cp pfpr2/c9spcapl pfprs - copy to pfprs before manual edits =======================
#15a. vi pfxr1/c9spcapl - see if any preserve packed/binary instructions =================
opr='JOBNAME c9spcapl - genby: cobmap1,uvdata2,uvdata3' uop=q0 was=a33000b33000 fili1=${UVDATA1}/c9spcapl,rcs=00091,typ=RSF filo1=${UVDATA2}/c9spcapl,rcs=00091,typ=RSF @run opn all loop get fili1,a0 skp> eof mvc b0(00091),a0 move rec to outarea before field prcsng tra b0(00091) translate entire outarea to ASCII # --- <-- insert R/T tests here for redefined records # --- redef, R/T test? mvc b16(20),a16 pns capl-amt-instl-ord:capl-amt-bal-remng-prev <NOTE* put1 put filo1,b0 skp loop eof cls all eoj
Note |
|
#16. vi pfprs/c9spcapl - insert instrns to preserve packed/binary fields ================= :19 - set cursor to line 19 :r pfxr1/c9spcapl - read in the pfxr1 job to preserve packed fields :dd - delete framework of pfxr1 job :wq - write modified job
#19. uvlp12 pfprs/c9spcapl - list modified job =====================
Please see the modified job on the next page --->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
opr='c9spcapl c9spcapl - generated by cobmap1,genpipe2,uvdata3' # - uvcopy job to convert & fix data fields 100 bytes apart # for compress & '|' delimit (see 'var' instrn below) uop=q0 was=a33000b33000c999000d33000e33000r33000 fili1=${UVDATA1}/c9spcapl,rcs=00219,typ=RSF filo1=${UVDATA2}/c9spcapl,rcs=9000,typ=LSTtd #<-- DOS option d for CR/LF @run opn all open files loop get fili1,r0 get next input rec (fixed size on fili1) skp> eof (cc set > at EOF) mvc a0(00091),r128 reposition EBCDIC data for cobmap conversion ## mvc a0(00091),r130 some IMSdumps have 2 extra bytes not in copybook mvc b0(00091),a0 move input area a to area b tra b0(00091) for EBCDIC input, else remove #-------------------------------------------------------------------------- # --- <-- insert R/T tests if redef records #-------------------------------------------------------------------------- # --- <-- insert instrns from uvdata2 if packed fields mvc b16(20),a16 pns capl-amt-instl-ord:capl-amt-bal-remng-prev <NOTE* #-------------------------------------------------------------------------- mvc c0(1),b0(1) #1 capl-cd-type-pln mvc c100(2),b1(2) #2 capl-cd-pe-acct mvc c200(7),b3(7) #3 capl-num-seq-ord mvc c300(4),b10(4) #4 capl-agg-cd-tax-exm-cust mvc c400(1),b14(1) #5 filler001 mvc c500(6),b15(6) #6 capl-agg-dte-estabd-ocp bal dat6,'500' mvc c600(6),b21(6) #7 capl-agg-dte-dscnct-ocp bal dat6,'600' mvc c700(6),b27(6) #8 capl-agg-dte-bld-to-last bal dat6,'700' mvc c800(3),b33(3) #9 capl-cd-stat-itm-ord-pnd mvc c900(1),b36(1) #10 capl-flg-chrg-wav-prd-min mvc c1000(2),b37(2) #11 capl-cd-pe-acct-ocp mvc c1100(7),b39(7) #12 capl-num-seq-ord-ocp mvc c1200(1),b46(1) #13 capl-cd-type-ord mvc c1300(2),b47(2) #14 capl-cd-pe-acct-ocp-pnd mvc c1400(7),b49(7) #15 capl-num-seq-ord-ocp-pnd mvc c1500(6),b56(6) #16 capl-agg-dte-ocp-chrg-init bal dat6,'1500' mvc c1600(6),b62(6) #17 capl-agg-dte-bld-fr-last bal dat6,'1600' mvc c1700(5),b68(5) #18 capl-cd-ocp-lec-1 mvc c1800(5),b73(5) #19 capl-cd-ocp-lec-2 mvc c1900(5),b78(5) #20 capl-cd-ocp-lec-3 mvc c2000(6),b10(6) #21 capl-agg-dte-sl-instl bal dat6,'2000' edt c2100(9),b16(4p),'+zzzzzzz.99' #22 capl-amt-instl-ord sqz c2100(9),' ' mvn c2200(3),b20(2p) #23 capl-qty-prd-inst mvn c2300(3),b22(2p) #24 capl-qty-prd-inst-rem edt c2400(9),b24(4p),'+zzzzzzz.99' #25 capl-amt-instl-ord-moly sqz c2400(9),' ' edt c2500(9),b28(4p),'+zzzzzzz.99' #26 capl-amt-bal-remng sqz c2500(9),' ' edt c2600(9),b32(4p),'+zzzzzzz.99' #27 capl-amt-bal-remng-prev sqz c2600(9),' ' mvc c2700(1),b36(1) #28 capl-flg-pmt-down mvc c2800(1),b37(1) #29 capl-cd-type-ord-pnd-instl mvc c2900(15),b38(15) #30 capl-cd-auth-acaps mvc c3000(25),b53(25) #31 capl-num-ord-pur mvc c3100(8),b83(8) #32 filler002 #-------------------------------------------------------------------- var1 var d0(8000),c0(100),0032,'|' convert to pipe delimited trt d0(8000),$trtchr ensure printable characters mvc e0(128),r0 copy input hdr already pipe delimited mvc e128(8000),d0 append data pipe delimited by this job put filo1,e0 write to output area from area 'e' clr1 clr c0(003200),' ' clear fixed field area for next record clr d0(003200),' ' clear variable field area for next record skp loop # eof cls all eoj @pf2=genpipe1.sub # area 'r' get input, 128 byte pipe delim ASCII hdr + EBCDIC data # area 'a' EBCDIC data moved here so copybook map dsplcmnts will match # area 'b' translated to ASCII, in case mainframe EBCDIC file (with packed?) # area 'c' data fields fixed 100 bytes apart in prep for var | delimit instrn # area 'd' output pipe delimited & squeezed together # area 'e' combines 128 byte input header (in r) with area d var output
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Please see the 2 record types in the uvhd dumps on page '2F10' The R/T code is the 1st byte of the copybook, but this is byte 128 of the uvhd dump, due to the 128 byte header inserted by imsbmcx2 when it splits the segments from the IMS unload file.
R/T 'L' - no packed data
R/T 'I' - packed data in bytes 144-163 (or 16-35 of the copybook)
#17a. vi pfprs/c9spcapl - edit the generated job ================= - edit/fix other conditions required (record types in this case)
Note |
|
#17b. uvlp12 pfprs/c9spcapl - list job after all corrections inserted =====================
Please see completed job (ready for execute/test/debug) on next page --->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
** completed job to pipe delimit c9spcapl segment file ** (after changes to test Record Types & process accordingly)
opr='c9spcapl c9spcapl - generated by cobmap1,genpipe2,uvdata3' # - uvcopy job to convert & fix data fields 100 bytes apart ## - modified for MULTIPLE RECORD TYPES <-- demo page '2F7' in DATAcnv3.doc uop=q0 was=a33000b33000c999000d33000e33000r33000 fili1=${UVDATA1}/c9spcapl,rcs=00219,typ=RSF filo1=${UVDATA2}/c9spcapl1,rcs=9000,typ=LSTtd #<--NOTE file#1 for layout#1 filo2=${UVDATA2}/c9spcapl2,rcs=9000,typ=LSTtd #<--NOTE file#2 for layout#2 @run opn all open files loop get fili1,r0 get next input rec (fixed size on fili1) skp> eof (cc set > at EOF) mvc a0(00091),r128 reposition EBCDIC data for cobmap conversion ## mvc a0(00091),r130 some IMSdumps have 2 extra bytes not in copybook mvc b0(00091),a0 move input area a to area b tra b0(00091) for EBCDIC input, else remove #-------------------------------------------------------------------------- # Test R/Ts, see copybook - 2 layouts, R/T in byte 0 of cpybk, 128 of infile # R/T 'L' layout#1 - fields 1-20 (no packed fields) # R/T 'I' layout#2 - fields 1-3 common + 21-32 (some packed fields) # R/T 'O' layout#2 - fields 1-3 common + 21-32 (some packed fields) # tst b0(1),'IO' R/Ts with packed ? skp! fld1 no - bypass packed field preserve instns mvc b16(20),a16 pns capl-amt-instl-ord:capl-amt-bal-remng-prev <NB*** #-------------------------------------------------------------------------- fld1 mvc c0(1),b0(1) #1 capl-cd-type-pln mvc c100(2),b1(2) #2 capl-cd-pe-acct mvc c200(7),b3(7) #3 capl-num-seq-ord mvc c300(4),b10(4) #4 capl-agg-cd-tax-exm-cust mvc c400(1),b14(1) #5 filler001 mvc c500(6),b15(6) #6 capl-agg-dte-estabd-ocp bal dat6,'500' mvc c600(6),b21(6) #7 capl-agg-dte-dscnct-ocp bal dat6,'600' mvc c700(6),b27(6) #8 capl-agg-dte-bld-to-last bal dat6,'700' mvc c800(3),b33(3) #9 capl-cd-stat-itm-ord-pnd mvc c900(1),b36(1) #10 capl-flg-chrg-wav-prd-min mvc c1000(2),b37(2) #11 capl-cd-pe-acct-ocp mvc c1100(7),b39(7) #12 capl-num-seq-ord-ocp mvc c1200(1),b46(1) #13 capl-cd-type-ord mvc c1300(2),b47(2) #14 capl-cd-pe-acct-ocp-pnd mvc c1400(7),b49(7) #15 capl-num-seq-ord-ocp-pnd mvc c1500(6),b56(6) #16 capl-agg-dte-ocp-chrg-init bal dat6,'1500' mvc c1600(6),b62(6) #17 capl-agg-dte-bld-fr-last bal dat6,'1600' mvc c1700(5),b68(5) #18 capl-cd-ocp-lec-1 mvc c1800(5),b73(5) #19 capl-cd-ocp-lec-2 mvc c1900(5),b78(5) #20 capl-cd-ocp-lec-3 mvc c2000(6),b10(6) #21 capl-agg-dte-sl-instl bal dat6,'2000' edt c2100(9),b16(4p),'+zzzzzzz.99' #22 capl-amt-instl-ord sqz c2100(9),' ' mvn c2200(3),b20(2p) #23 capl-qty-prd-inst mvn c2300(3),b22(2p) #24 capl-qty-prd-inst-rem edt c2400(9),b24(4p),'+zzzzzzz.99' #25 capl-amt-instl-ord-moly sqz c2400(9),' ' edt c2500(9),b28(4p),'+zzzzzzz.99' #26 capl-amt-bal-remng sqz c2500(9),' ' edt c2600(9),b32(4p),'+zzzzzzz.99' #27 capl-amt-bal-remng-prev sqz c2600(9),' ' mvc c2700(1),b36(1) #28 capl-flg-pmt-down mvc c2800(1),b37(1) #29 capl-cd-type-ord-pnd-instl mvc c2900(15),b38(15) #30 capl-cd-auth-acaps mvc c3000(25),b53(25) #31 capl-num-ord-pur mvc c3100(8),b83(8) #32 filler002 #-------------------------------------------------------------------- # Test R/Ts, see copybook - 2 layouts, R/T in byte 0 of cpybk, 128 of infile # R/T 'L' layout#1 - fields 1-20 (no packed fields) # R/T 'I' layout#2 - fields 1-3 common + 21-32 (some packed fields) # R/T 'O' layout#2 - fields 1-3 common + 21-32 (some packed fields) # tst b0(1),'IO' R/Ts with layout#2 ? skp= rec2 #----------------------------------------------------------------------- # process layout#1 fields 1-20 var1 var d0(1000),c0(100),20,'|' convert 20 flds to pipe delimited trt d0(1000),$trtchr ensure printable characters mvc e0(128),r0 copy input hdr already pipe delimited mvc e128(1000),d0 append data pipe delimited by this job put filo1,e0 write layout#1 to file#1 skp clr1 goto clear & repeat loop #----------------------------------------------------------------------- # process layout#2 fields 1-3 (common) + fields 21-32 rec2 mvf c300(3200),c2000(1200) move fields 21-32 after flds 1-3 var d0(1000),c0(100),15,'|' convert 15 flds to pipe delimited trt d0(1000),$trtchr ensure printable characters mvc e0(128),r0 copy input hdr already pipe delimited mvc e128(1000),d0 append data pipe delimited by this job put filo2,e0 write layout#2 to file#2 skp clr1 goto clear & repeat loop #----------------------------------------------------------------------- clr1 clr c0(003200),' ' clear fixed field area for next record clr d0(003200),' ' clear variable field area for next record skp loop # eof cls all eoj @pf2=genpipe1.sub
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#19a. export UVDATA1=c9bcaa0p - export I/O data directories =======================
#19b. export UVDATA2=c9bcaa0p.pipe ============================
#20. uvcopy pfprs/c9spcapl <-- execute pipe delimit job =====================
We will now investigate the input & output files to verify proper operation. Please see various display methods on the following pages --->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#22a. uvhd c9bcaa0p/c9spcapl r219s2 - investigate input to c9spcant =============================
Note |
|
1 2 3 4 5 6 r# 1 0123456789012345678901234567890123456789012345678901234567890123
0 C9SPCAPL|04|03|00000001|C9SPCACU|02|02|00000001|110210 4355445473373373333333374355444573373373333333373333332222222222 3930310CC04C03C00000001C39303135C02C02C00000001C1102100000000000
64 |02010001250|00|L 01| ||||||0027|00091| 7333333333337337422222223372222222222222222222277777733337333337 C02010001250C00CC000000001C00000000000000000000CCCCCC0027C00091C
128 .@@@@@@@..@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ D4444444FF444444444444444444444444444444444444444444444444444444 3000000001000000000000000000000000000000000000000000000000000000
1 2 3 4 5 6 r# 11 0123456789012345678901234567890123456789012345678901234567890123
0 C9SPCAPL|04|03|00000011|C9SPCACU|02|02|00000009|110210 4355445473373373333333374355444573373373333333373333332222222222 3930310CC04C03C00000011C39303135C02C02C00000009C1102100000000000
64 |04010014656|00|I020585033| ||||||0027|00091| 7333333333337337433333333372222222222222222222277777733337333337 C04010014656C00C9020585033C00000000000000000000CCCCCC0027C00091C
128 ......................................@@@@@@@@@@@@@@@@@@@@@@@@@@ CFFFFFFFFFFFFFFF00000100000000000000FF44444444444444444444444444 9020585033881013000C0F0F000C000C000C0200000000000000000000000000
Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#23a. uvhd c9bcaa0p/c9spcapl r219s2a - investigate with option 'a' ==============================
Note |
|
1 2 3 4 5 6 r# 1 0123456789012345678901234567890123456789012345678901234567890123
0 C.S&CA&<@..@..@........@C.S&CACU@..@..@........@................ 4355445473373373333333374355444573373373333333373333332222222222 3930310CC04C03C00000001C39303135C02C02C00000001C1102100000000000
64 @...........@..@<.........@....................@@@@@@....@.....@ 7333333333337337422222223372222222222222222222277777733337333337 C02010001250C00CC000000001C00000000000000000000CCCCCC0027C00091C
128 L 01 D4444444FF444444444444444444444444444444444444444444444444444444 3000000001000000000000000000000000000000000000000000000000000000
1 2 3 4 5 6 r# 11 0123456789012345678901234567890123456789012345678901234567890123
0 C.S&CA&<@..@..@........@C.S&CACU@..@..@........@................ 4355445473373373333333374355444573373373333333373333332222222222 3930310CC04C03C00000011C39303135C02C02C00000009C1102100000000000
64 @...........@..@I.........@....................@@@@@@....@.....@ 7333333333337337433333333372222222222222222222277777733337333337 C04010014656C00C9020585033C00000000000000000000CCCCCC0027C00091C
128 I020585033881013....................02 CFFFFFFFFFFFFFFF00000100000000000000FF44444444444444444444444444 9020585033881013000C0F0F000C000C000C0200000000000000000000000000
#23b. --> i3f1 <-- list out 1st 3 records (with uvhd immediate list) ====== - print out (same as display above)
The data from byte 128 onwards should match the copybook shown on page '2F3'.
We can distinguish the Record Types by testing byte 128 (byte 0 of copybook) R/T 'L' - no packed data R/T 'I' - packed data present in bytes 144-163
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#25a. uvhd c9bcaa0p.pipe/c9spcapl1 r1000ts2 <-- display output file #1 =====================================
1 2 3 4 5 6 r# 1 0123456789012345678901234567890123456789012345678901234567890123
0 C9SPCAPL|04|03|00000001|C9SPCACU|02|02|00000001|110210 4355445473373373333333374355444573373373333333373333332222222222 3930310CC04C03C00000001C39303135C02C02C00000001C1102100000000000
64 |02010001250|00|L 01| ||||||0027|00091| 7333333333337337422222223372222222222222222222277777733337333337 C02010001250C00CC000000001C00000000000000000000CCCCCC0027C00091C
128 L|| 01|||0000/00/00|0000/00/00|0000/00/00||||||||0000/00/00| 4772222233777333323323373333233233733332332337777777733332332337 CCC0000001CCC0000F00F00C0000F00F00C0000F00F00CCCCCCCC0000F00F00C
192 0000/00/00||||.. 3333233233777700 0000F00F00CCCCDA
#25b. uvhd c9bcaa0p.pipe/c9spcapl2 r1000ts2 <-- display output file #2 =====================================
1 2 3 4 5 6 r# 1 0123456789012345678901234567890123456789012345678901234567890123
0 C9SPCAPL|04|03|00000011|C9SPCACU|02|02|00000009|110210 4355445473373373333333374355444573373373333333373333332222222222 3930310CC04C03C00000011C39303135C02C02C00000009C1102100000000000
64 |04010014656|00|I020585033| ||||||0027|00091| 7333333333337337433333333372222222222222222222277777733337333337 C04010014656C00C9020585033C00000000000000000000CCCCCC0027C00091C
128 I|02|0585033|1988/10/13|+.00|001|000|+.00|+.00|+.00|0|2||||.. 4733733333337333323323372233733373337223372233722337373777700 9C02C0585033C1988F10F13CBE00C001C000CBE00CBE00CBE00C0C2CCCCDA
Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
This part will illustrate the conversion using segment c9spcaaj, which has redefined fields vs the redefined records in the previous example c9spcapl.
In the previous example we split the redefined records to 2 separate files for loading 2 relational tables. In this example we will create some new fields in the table, storing data in 1 set of fields & clearing the other or vice versa depending on the type code that determines which set of fields should be used.
We will show only a small portion of the copybook map & the possible solution.
10 caaj-agg-itms-instl. 15 caaj-agg-dte-sl-instl-ajmt pic 9(6). 0088 0093 006 n 6 15 caaj-amt-instl-prod comp-3 pic s9(5)v9(2). 0094 0097 004pns 7 * 10 caaj-agg-itms-oth redefines caaj-agg-itms-instl. 15 caaj-cd-stat-pln-ls pic x(1). 0088 0088 001 15 caaj-amt-rate-un comp-3 pic s9(5)v9(2). 0089 0092 004pns 7 15 caaj-amt-bs comp-3 pic s9(7)v9(2). 0093 0097 005pns 9
mvc c3800(6),b88(6) #39 caaj-agg-dte-sl-instl-ajmt bal dat6,'3800' edt c3900(9),b94(4p),'+zzzzzzz.99' #40 caaj-amt-instl-prod sqz c3900(9),' ' edt c4000(9),b98(4p),'+zzzzzzz.99' #41 caaj-amt-instl-prod-moly sqz c4000(9),' ' # mvc c4100(1),b88(1) #45 caaj-cd-stat-pln-ls edt c4200(9),b89(4p),'+zzzzzzz.99' #46 caaj-amt-rate-un sqz c4200(9),' ' edt c4300(11),b93(5p),'+zzzzzzz.99' #47 caaj-amt-bs sqz c4300(11),' '
Let's assume that byte 37 is 'I' for the 1st set of install fields and anything else indicates the 2nd set of fields. Then we could add the following code:
# added code to test type & clear 1 set of fields or the other cmc b37(1),'I' Install fields ? skp= inst # Other fields present - so clear Install fields clr c3800(300),' ' clear install fields skp man44 # Install fields present - so clear other fields inst clr c4100(300),' ' clear other fields man44 nop
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
imsbmcx1 |
|
imsbmcx2 |
|
cobmap1 |
|
uvdata2 |
|
genpipe2 |
|
uvdata3 |
|
sqlload2 |
|
listpipe2 |
|
genpipeD |
|
We will list a few of the shorter scripts such as genpipeD See next page --->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#!/bin/ksh # genpipeD - Generate uvcopy job to convert files to variable | delimited # - see documentation in part 9 of DATAcnv3.doc in vol 4 of UVdoc # # script genpipeD will generate the pipe delimit job for any 1 copybook # - intended for use after modifying/optimizing the copybook # - see DATAcnv3.doc for 1st procedures that generate pipe delimit # jobs for all copybooks in the directory # genpipeD executes cobmap1, uvdata2, genpipe2, uvdata3,& sqlload2 # # genpipeD must be run from the conversion super-directory which contains # subdirectories (cpyrs,maprs,pfxr1,pfpr1,pfpr2,pfprs,sql1,ctl,tmp) # # usage: genpipeD copyBookName ctlfilename # ================================= # example: genpipeD c9spcant.cpy c9bcaa0p.gen # ================================== # # must have created control file for example: # # ctl/c9bcaa0p.gen - control file for IMS DB unload c9bcaa0p # c9spcaac cpy=c9spcaac rcs=03032 # c9spcant cpy=c9spcant rcs=00664 # echo "genpipeD using: cpyrs,maprs,pfxr1,pfpr1,pfpr2,pfprs,sql1,ctl,tmp" if [[ -f "cpyrs/$1" && -f ctl/$2 ]]; then : else echo "usage: genpipeD CopyBookName controlfile" echo "example: genpipeD c9spcant c9bcaa0p.gen" exit 9; fi cb=${1%.*} # drop any .extension (.cpy) cf=$2 # assign $symbols to args # # select line from ctlfile with matching filename & write to tmp/filename exec 3< ctl/$cf # open ctl file for reading exec 4> tmp/ctl1 # open output file while read -u3 file line do if [[ $file = *${cb}* ]] then print -u4 "$file $line"; break; fi done exec 3<&-; exec 4>&-; # close files if [[ ! -s tmp/ctl1 ]]; then echo "datafilename nomatch in ctlfile";exit 1;fi # uvcopy cobmap1,fili1=cpyrs/$cb,filo1=maprs/$cb,uop=q0i7p0 # uvcopy uvdata2,fili1=maprs/$cb,filo1=pfxr1/$cb,uop=q0i7 # uvcopy genpipe2,fili1=maprs/$cb,filo1=pfpr1/$cb,uop=q0i7a1 # uvcopy uvdata3,fili1=tmp/ctl1,fild2=pfpr1,fild3=pfpr2,uop=q0i7a0r2 # uvcopy sqlload2,fili1=maprs/$cb,filo1=sql1/$cb,uop=q0i7 # echo "output in pfpr2, copy to 'pfprs' & modify as required" echo "Note - may need to save modifications in old version in pfprs ??" exit 0
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page