Part_1 - Convert data files to pipe delimited files for loading Relational DBs

Mainframe Conversion doc

• this ('DATAcnv3.doc') is an older piece of documentation
• saved for Part 2 IMS conversion
• the original mainframe conversion document (IBM2unix.doc) was
  split into multiple documents as shown below:

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

Part_1 Convert data files to | delimited format for loading DataBases

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

1A1. SQLcnvrt - Convert data to | delimited format - Introduction

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

1A2. SQLcnvrt - Convert data to | delimited format - Introduction

conversions performed - by genpipe1

1. Translate EBCDIC to ASCII if files are coming directly from a mainframe. Translate only 'CHARACTER' fields not packed or binary (see below).
   
   
2. Does NOT translate 'PACKED' fields, which would be destroyed by translation. Packed data is the same on UNIX (at least for Micro Focus COBOL) as it was on the mainframe.
   
   
3. Does NOT translate 'BINARY' fields, which would be destroyed by translation. Binary data is the same on UNIX RISC machines, but must be converted from 'big end' to 'little end' for INTEL machines.
   
   
4. The easiest way to translate only the character fields, is to first translate the entire record, and then move the packed/binary fields from input to output.
   
   
5. Correct any signed numeric (unpacked) fields from EBCDIC data files. Mainframes carry the sign in the zone (overpunch) of the units digit. Positive signs for digits 0-9 appear as '{' & upper case letters A-I. Negative signs for digits 0-9 appear as '}' & upper case letters J-R.
   
   
6. Since the translate & sign correction apply only to EBCDIC inputs, the instructions to move packed/binary fields & correct numeric signs are generated by a separate job 'uvdata2' & then inserted into the main job generated by 'genpipe1'
   
   
7. Prepare for the conversion of fixed fields to variable | delimited by first moving the various fixed length fields to fixed length 100 byte fields with data left adjusted within each 100 byte area. If you have any fields longer that 100 bytes, you can easily modify the genpipe1 job as required (try 200 ?). It is at this point (while moving to the 100 byte fields) that any packed fields are unpacked & any binary fields may be switched to 'little end' format.
   
   
8. Numeric signed or decimal point fields will be edited as -zzzzzzzzzzz.99
   
   
9. Date-fields will be edited as 'ccyy/mm/dd', if the fieldname includes 'date' & the length is 4,6,or 8 bytes. Four digit dates are assumed to be yymm and day '01' is supplied. Century 19 or 20 will be inserted depending on the year > 50 or < 50.
   
   
10. Time-fields will be edited as 'HH:MM:SS' if the fieldname includes 'time' and the length is 6 bytes.
    
    
11. Convert the fixed length 100 byte fields to '|' delimited format. This removes any trailing blanks from each field.
    
    
12. Generate the control file to load the delimited file into a Microsoft SQL Server database.
    
    

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

1B1. SQLcnvrt - Convert data to | delimited format - Improving Copybooks

copybook changes to improve genpipe1 conversion

Most mainframe copybooks will need to be modified, to make the output suitable for loading relational databases.

1. Eliminate 'redefined fields', that would cause duplicate data in the output records. For example date fields are often redefined as follows:
   
   

       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|

2. Eliminate 'redefined records'. Since databases do not allow multiple record types in 1 table, you will have to separate different record types into different files prior to converting to variable | delimited format.
   
   

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.

3. Combine separately defined Year,month,day into 1 combined date field. The example in 5a above illustrates this point. genpipe1 will then generate the 'bal dat6' subroutine to edit the date as |CCYY/MM/DD|.
   
   
4. Watch for date fields without the word 'date' as part of the field name. If necessary change the field name to include the word 'date' so that genpipe1 will generate the 'bal dat6' subroutine to edit as |CCYY/MM/DD|. genpipe1 includes a table of date field patterns & you could add other keywords to identify your date fields.
   
   
5. The arguments above in 5d & 5d also apply to 'time' fields which will be edited as |HH:MM:SS| by the 'bal tim6' subrtn.
   
   

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

1B2. SQLcnvrt - Convert data to | delimited format - Improving Copybooks

modify COBOL copybook (continued)

6. Watch for packed date/time fields, defined as 7 digits vs 6 in 4 packed bytes, or 9 digits vs 8 in 5 packed bytes. The extra digit would cause an extra leading '0' in the output fields eg: |01999/11/29|. The easiest thing to do is to modify the generated code, for example:
   
   

         mvn   c500(7),b44(4p)       <-- change (7) to (6)
         bal   dat6,'500'

7. Eliminate 'occurs'. Since databases do not allow 'occurs' you will have to eliminate by modifying the copybook. For example, 12 months sales defined using occurs might be eliminated as follows:
   
   

       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.

8. Watch for 'occurs depending on'. For example, legal descriptions in a tax file might be defined at the end of the fixed record portion as follows:
   
   

       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

1C1. SQLcnvrt - Convert data to | delimited format - Other Related Jobs

Other Jobs Related to SQLcnvrt

generate SQL*LOADER commands

The Vancouver Utilities includes some other jobs that are somewhat similar to genpipe1, in that they automatically generate parameters from copybooks.

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

1D1. SQLcnvrt - convert data files to | delimited - Operating Instructions

preparation

We will assume the following directories are present (mkdir if not).

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

1E1. SQLcnvrt - Operating Instructions - ALL FILES in Directory

Preparations - Convert All Files in Directory

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.

control file to relate copybooknames to datafilenames

 # 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

1F1. SQLcnvrt - Operating Instructions - ALL FILES in Directory

Note that some of these steps (cobmap1) have already been performed in previous parts of this documentation (DATAcnv3.doc).

Operating Instructions - ALL COPYBOOKS & ALL DATA-FILES

 #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

1F2. SQLcnvrt - Operating Instructions - ALL FILES in Directory

execute the generated jobs

 #6a. export UVDATA1=datf  - setup path to input data files
      ===================
 #6b. export UVDATA2=datp  - setup path to output data files
      ===================

 #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
      ===================

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

1G1. SQLcnvrt - Operating Instructions - for 1 FILE AT A TIME

Demo Op Instrns - 1 file at a time

generating the uvcopy job

 #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

execute generated job - method #1

 #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

on NT SQL server

 #8. sqlload CONTROL=sql\warmas1 DATA=data\warmas1
     =============================================

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

1G2. SQLcnvrt - Operating Instructions - for 1 FILE AT A TIME

execute generated job - method #2

 #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

on NT SQL server

 #8. sqlload CONTROL=sql\warmas1 DATA=data\warmas1
     =============================================

Notes

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

1H1. warmas1 - sample conversion for ASCII file (no packed fields)

warmas1 - cobmap maps/warmas1 from copybook cpys/warmas1

 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

input data file - datf/warmas1

 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

output file - datp/warmas1

 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||

Notes

1. We recommend combining the year, month,& day fields into 1 combined date field, which will then be converted to 'ccyy/mm/dd'. To do this simply modify the copybook, combining the 3 2 byte fields into 1 6 byte field, regenerate the uvcopy job,& rerun it.
   
   
2. The century will be inserted as 19 for years 50-99 & 20 for 00-49.
   
   

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

1H2. warmas1 - sample conversion for ASCII file (no packed fields)

uvcopy job - pfp2/warmas1

 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

1H3. warmas1 - sample conversion for ASCII file (no packed fields)

listpipe - verify '|' delimited files

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

1H4. warmas1 - sample conversion for ASCII file (no packed fields)

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
 ===================================

SQL Loader control file

 -- 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).
 )

NOTES

1. The control file & data file can be moved to a Microsoft SQL Server. The command or gui equivalent would be something like:
   
   

    sqlload CONTROL=sql\warmas1 DATA=datp\warmas1
    =============================================

2. There is an alternative job 'sqlload2' for generating SQL Loader files to load ORACLE data-bases.
   
   
3. Note the '--' comments on the right side of each statement.
   
   

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

1H5. warmas1 - sample conversion for ASCII file (no packed fields)

SQL Loader control file for Oracle

 -- 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).
 )

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

1I1. warmas2 - demo conversion file with packed & signed numeric fields

warmas2 - cobmap maps/warmas2 from copybook cpys/warmas2

 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

output file - datp/warmas2

 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||

Notes

1. Since the input has packed fields, we cannot display in the normal manner. We will use the 'uvhd' utility to display in 'vertical hexadecimal' Please see the next page --->
   
   
2. The packed date fields are automatically unpacked & edited 'ccyy/mm/dd'.
   
   
3. The century is inserted as 19/20 depending on year > 50 or < 50, by default, but there is an option to inhibit this.
   
   

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

1I2. warmas2 - demo conversion file with packed & signed numeric fields

input data file - datf/warmas2

 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

1. Note the packed dates in bytes 44-47 & 58-62.
   
   
2. Note the signed $amount field in bytes 49-57. The sign is carried in the units digit byte 57 & is compatible with Micro Focus ASCII. Negative signs 0-9 are letters p-y which are x'70'-x'79'. Positive signs 0-9 are unchanged digits 0-9 which are x'30'-x'39'.
   
   

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

1I3. warmas2 - demo conversion file with packed & signed numeric fields

uvcopy job - pfp2/warmas2

 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

1J1. SQLcnvrt.doc - convert data files to | delimited format to load DataBases

summary of uvcopy jobs used in this section

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

1K1. SQLcnvrt.doc - convert data files to | delimited format to load DataBases

summary of scripts used in this section

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

Part_2 Convert IMS hierarchal DBs to | delimited ASCII text to load Relationals

Part 2 - Convert Hierarchal DBs to | delimited ASCII to load Relationals

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

2A1. Convert IMS hierarchal DBs to | delimited ASCII text to load Relationals

Introduction & subdirs required

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.

subdirs required for conversion to pipe delimited

 /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

2A2. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

subdirs required for conversion to pipe delimited

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

2B1. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

IMS BMC UNLOAD file format

Records are variable length with header & data lengths specified by 2 byte binary fields at the begining of each unloaded segement/record.

1. 00(2b) - HDRLEN (2b) means 2 bytes binary
2. 02(2b) - SEGDATSZ
3. 04(8c) - SEGNAME (8c) means 8 bytes character
4. 12(1b) - SEGCODE
5. 13(1b) - SEGLEV1
6. 14(2b) - SEGKEYSZ
7. 16(?c) - SEGKEY
8. ??(1b) - SEGDBYTE
9. ??(??) - data ** sample IMS unloaded data **
   

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

2B2. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

investigate IMS unload files

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

2C1. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

imsbmcx2 - Split IMS unload file to separate files

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).

imsbmcx2 output record format

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

2C2. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

seq# control file written at end imsbmcx2

 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)

control file - to continue sequence#s

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)
 =======================

sample control file ctl/c9bcaa0p.seq

 <--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

2C3. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

control file squeezed to show right side

 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.

Data records vs control 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

2D1. Split IMS unload segments to separate files for conversion & pipe delimit

Operating Instructions

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.

1. FTP the IMS unload file to UNIX. For our demo op. instrns this will be stored as: '/opt/apps/IMS/IMSdumps/c9bcaa0p'.
   
   
2. Investigate the file with uvhd & print out some records to verify conversion.
   
   

 #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'

3. Run imsbmcx2 to extract separate files for each segment data type.
   
   

 #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

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

2D2. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

4. Create the 2nd control file ctl/c9bcaa0p.gen from the seq# control file for later use by uvdata3 to insert filenames & record sizes in the pipe delimit jobs.
   
   

 #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

5. Copy the copybooks for the segment data files from the previously converted libs/cpam/cpys/... to this directory IMS/cpyrs. Note - the required copybooks usually match the segment names from the control files shown above
   
   

 #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

2D3. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

6. Edit/Modify/Optimize the copybooks for Relational databases - See prior discussion in part 8, but briefly: - eliminate redefined fields & redefined records - eliminate occurs by duplicating & appending seq# on end of fieldnames - might combine year,month,day to create 1 date field
   
   

 #6a. vi cpyrs/c9spcaac.cpy
      =====================
 #6b. vi cpyrs/c9spcant.cpy
      =====================
       - - - etc - - -

generate conversion jobs for ALL segments

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
      ======================================

8. Generate the uvcopy instructions to preserve packed/binary fields. These instructions will be edited into the pipe delimit jobs
   
   

 #8a. uvcopyx uvdata2 maprs pfxr1 uop=q0i7
      ====================================

9. Generate the pipe delimit uvcopy jobs from the cobmaps
   
   

 #9a. uvcopyx genpipe2 maprs pfpr1 uop=q0i7
      =====================================

10. Complete the pipe delimit jobs with correct filenames & record sizes
    
    

 #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

2D4. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

script to regenerate 1 job (after copybook changes)

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

2D5. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

 #13.  List all generated jobs for inspection to determine if changes required

 #13a. uvlpd12 pfpr2             <-- list all files in subdir pfpr2
      =============

14. Copy each job from pfpr2 to prprs before modify/test/debug - copy desired segment conversion job from pfpr2 to pfprs - will perform editing in pfprs, never in pfpr2, this protects our edited jobs from overwriting if jobs regenerated
    
    

 #14a. cp pfpr2/c9spcant pfprs    - copy to pfprs before modify/test/debug
       =======================

15. Check to see if any packed/binary field preserve instructions required - by examining the corresponding job in the pfxr1 subdir
    
    

 #15a. vi pfxr1/c9spcant          - see if any packed/binary instructions
       =================

16. Edit the jobs to insert instructions to preserve packed/binary fields These instructions can be read in from the pfxr1 jobs & the framework of the pfxr1 job can be deleted.
    
    

 #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

17. Edit the jobs as required for various contingencies: - multiple record types - some IMS unload files have 2 extra bytes at begining of data (c9spcant demonstrates this)
    
    

 #17a. vi pfprs/c9spcant
       =================
      - edit segment conversion job for other conditions as required:
        (record types, 2 extra bytes in unload files, etc)

18. Relist modified jobs as desired - to serve as examples for future similar modifications
    
    

 #18a. uvlp12 pfprs/c9spcant       <-- relist modified job
       =====================

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

2D6. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

Preparation to Execute the Pipe Delimit jobs

19. Export data I/O directories for pipe delimit jobs
    
    

 #19a. export UVDATA1=c9bcaa0p
       =======================

 #19b. export UVDATA2=c9bcaa9p.pipe
       ============================

execute pipe delimit job - for 1 file at a time

 #20a. uvcopy pfprs/c9spcaac
       =====================

 #20b. uvcopy pfprs/c9spcant
       =====================
         - - - etc - - -

execute pipe delimit jobs - for ALL files in directory

 #21.  uvcopyxx 'pfprs/*'
       =================

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

2D7. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

test/debug pipe delimit jobs

               (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)

• reply null to print (as below) or 'vi' to see with vi
  if you chose 'vi', you could subsequently print as follows:

 #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

2E1. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

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

2E2. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

 #6. Edit/Modify/optimize copybooks for Relational DBs

copybook before modifications

  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

2E3. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

modify/optimize c9spcant.cpy for Relational DBs

 #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
      =====================

copybook AFTER modifications

 *  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

2E4. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

regenerate pipe delimit job after copybook changes

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
      =======================

15. We must check to see if there are any packed/binary field preserve instructions generated for this copybook in the pfxr1 subdir.
    
    

 #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

 #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

2E5. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

pipe delimit job pfprs/c9spcant

 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

2E6. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

Check for special changes required

17. We will now edit for other contingencies, such as record types, etc. For c9spcant, we need to correct for 2 extra bytes at the begining of the segment data that is not accounted for in the copybook ?????
    
    

      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)

 #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

2E7. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

completed job to pipe delimit c9spcant segment file

 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

2E8. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

Execute the regenerated job pfprs/c9spcant

 #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 --->

22. will display the input file using uvhd without option 'a' to see record headers already in ASCII & pipe delimited but this is no good to see the EBCDIC data following each hdr
    
    
23. will display the input file using uvhd with option 'a' this is more useful to examine the EBCDIC data to be converted to pipe delimited format by out pfprs job
    
    
24. will display the output file using 'vi' but you might prefer uvhd with option 't' below.
    
    
25. will display the output file using uvhd with options 'r1000ts2' to display ASCII text records up to 1000 bytes long with s2 option for extra space between scale & data
    
    
26. will create a report from the output file showing the pipe delimited data fields beside their copybook names. uvcopy utility 'listpipe2' creates the report for listing with the 'uvlp12' script.
    
    

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

2E9. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

debug pipe delimit job c9spcant

investigate the input data file - without option 'a'

22. Use uvhd to investigate the input to & output from the pipe delimit job. Specify the record size listed on the right side of seq# control file For spcant this will be 'r677', also specify option 's2' for more space
    
    

 #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 ...

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

2E10. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

investigate the input data file - with option 'a'

23. Repeat uvhd with option 'a' to translate EBCDIC char lines to ASCII. This is better to inspect the EBCDIC data beyond the 128 byte header which will be converted & pipe delimited by the genpipe2 generated job.
    
    

 #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

2E11. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

24. Display the output file using 'vi'. This is rather hard to read & you might prefer uvhd with option 't' on the next 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

2E12. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

25. Display the output file using uvhd with options 'r1000ts2' to display ASCII text records up to 1000 bytes long with s2 option for extra space between scale & data
    
    

 #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

26. On the next page we will create a report from the output file showing the pipe delimited data fields beside their copybook names. uvcopy utility 'listpipe2' creates the report for listing with the 'uvlp12' script.
    
    

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

2E13. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

 #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

2F1. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

demo segment C9SPCAPL - multiple Record Types

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.

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

2F2. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

c9spcapl copybook (map) BEFORE changes

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

2F3. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

 #6. Edit/Modify/optimize copybooks for Relational DBs

 *  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

2F4. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

regenerate pipe delimit job after copybook changes

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
      =======================

15. We must check to see if there are any packed/binary field preserve instructions generated for this copybook in the pfxr1 subdir.
    
    

 #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

 #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

2F5. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

pipe delimit job pfprs/c9spcapl (before R/T changes)

 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

2F6. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

Check for special changes required

17. We will now edit for other contingencies, such as record types, etc. c9spcapl has 2 record types & we will modify the pipe delimit job to write 2 separate files for loading separate Relational tables.
    
    

      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)

 #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

2F7. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

          ** 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

2F8. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

Execute the regenerated job pfprs/c9spcapl

 #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 --->

22. will display the input file using uvhd without option 'a' to see record headers already in ASCII & pipe delimited but this is no good to see the EBCDIC data following each hdr
    
    
23. will display the input file using uvhd with option 'a' this is more useful to examine the EBCDIC data to be converted to pipe delimited format by out pfprs job
    
    
24. will display the output file using 'vi' but you might prefer uvhd with option 't' below.
    
    
25. will display the output file using uvhd with options 'r1000ts2' to display ASCII text records up to 1000 bytes long with s2 option for extra space between scale & data
    
    
26. will create a report from the output file showing the pipe delimited data fields beside their copybook names. uvcopy utility 'listpipe2' creates the report for listing with the 'uvlp12' script.
    
    

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

2F9. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

debug pipe delimit job c9spcapl

investigate the input data file - without option 'a'

22. Use uvhd to investigate the input to & output from the pipe delimit job. Specify the record size listed on the right side of seq# control file For spcapl this will be 'r219', also specify option 's2' for more space
    
    

 #22a. uvhd c9bcaa0p/c9spcapl r219s2  - investigate input to c9spcant
       =============================

                       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

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

2F10. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

investigate the input data file - with option 'a'

23. Repeat uvhd with option 'a' to translate EBCDIC char lines to ASCII. This is better to inspect the EBCDIC data beyond the 128 byte header which will be converted & pipe delimited by the genpipe2 generated job.
    
    

 #23a. uvhd c9bcaa0p/c9spcapl r219s2a  - investigate with option 'a'
       ==============================

                       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

2F12. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

25. Display output files using uvhd with options 'r1000ts2'
    
    

 #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

Goto:   Begin this doc ,  End this doc ,  Index this doc ,  Contents this library ,  UVSI Home-Page

2G1. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

demo segment C9SPCAAJ - Redefined fields

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.

sample cobmap showing redefined fields

    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

pipe delimit code for above fields

        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

2H1. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

uvcopy jobs & scripts used in part 9

uvcopy jobs used in part 9

scripts used in part 9

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

2H2. Convert IMS DBs to pipe delimited ASCII text to load Relational DBs

 #!/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

Visitor Counters for ThisYear and LastYear