Vancouver Utilities for DATA Conversion

The Vancouver Utilities have been widely used to migrate mainframe applications to Unix/Linux. The 3 significant conversions are:

  1. Converting mainframe JCL to Korn shell scripts
  2. Converting mainframe COBOL to Micro Focus COBOL, Open COBOL, or COBOL-IT.
  3. Converting mainframe EBCDIC DATA to ASCII data, preserving packed/binary fields that would be destroyed by FTP translate in ASCII mode.

This newsletter will discuss DATA conversion which is actually the most critical conversion. UV Software has successfully converted very complex data files where other vendors have failed.

The Vancouver Utility data conversion success is largely due to the 'uvcopy' data manipulation utility which runs on unix/linux/windows, and understands mainframe concepts such as packed decimal fields, fixed record length files, and column addressing.

Note that none of the native unix/linux/windows utilities can process packed decimal fields and most cannot process fixed record length files without Line-Feeds. They are also deficient in their ability to address fields by column position as is common in mainframe utilities.

The uvcopy utility has all the instructions required to perform any kind of data manipulation required for data conversion (translations & conversions between packed,binary,& zoned data).

The uvcopy utility can also replace many utilities (such as IEBGENER,IEBCOPY, FILEAID,EASYTRIEVE,QUIKJOB,etc), that were used on the mainframe, but are not available on unix/linux/windows. The JCL converter automatically converts the mainframe parameters to the uvcopy/uvsort/uvcp equivalents when it encounters these steps while converting the JCL to Korn shell scripts.

Goto:   Begin this document End this document UVSI Home-Page

Vancouver Utility Data Conversion Features

  1. The COBOL copybooks are used to automatically generate data conversion uvcopy jobs. The generated uvcopy jobs will need no manual changes for most files when the COBOL copybooks have been proven accurate by years of processing on the mainframe.

  2. Some files, such as those with multiple record-types (redefined records) will need minor manual changes to insert record-type test instructions (because the record-types cannot usually be determined automatically). You will need to insert a few instructions to test the record type and branch to the appropriate bank of automatically generated instructions for that particular record-type. We will give an example further below.

  3. Other files that will require manual changes are those with 'occurs depending on'. You will need to identify the depending-on field, and load it into the automatically generated occurs register. It would take too long to illustrate this here, but I would be pleased to perform a sample conversion if you have such a file. The point here is that uvcopy has the power to handle whatever kind of complex conversion is required.

  4. After generating the uvcopy data conversion jobs and making any changes required for multiple record-types or occurs depending on, you can execute them 1 at a time or you can execute all jobs in the directory (with 1 command). This is convenient since you may need to repeat the data file transfer/conversions several times during the conversion period.

  5. The above anticipates converting the mainframe applications to run on unix/linux/windows without changes, but tools are also provided to convert data files to '|' pipe-delimited files for loading data bases. Any packed/binary fields are unpacked & edited with signs & decimal points required to load data bases.

  6. We also have tools to convert ASCII files back to EBCDIC, for users that can not perform the big-bang conversion. As well as converting the fixed record length files, we can also convert pipe-delimited output from data bases running on unix/linux/windows back to the original mainframe fixed record layout.

  7. Converted data files can be verified with 'uvhdcob' which displays field contents with COBOL copybook fieldnames. You can browse, search, select, etc. The verify command will search for any invalid fields depending on the type defined by the copybook (packed, zoned numeric, character).

    COBOL Copybooks MISSING ?

  8. If COBOL copybooks are missing for some files, the signscan2 utility can create the copybook by scanning for distinctive signs in packed & zoned signed fields, and the lack of zones in binary fields. It assigns fieldnames as f1,f2,f3,etc but this does not matter & you can proceed to generate the uvcopy conversion job which preserves the packed/signed/binary fields. signscan2 may combine contiguous character fields, which again does not matter for generating the conversion job, which will translate the combined field with 1 EBCDIC to ASCII translate instruction.

Goto:   Begin this document End this document UVSI Home-Page

sample Data File conversion

Here is a sample data file conversion of EBCDIC to ASCII preserving packed binary fields & correcting zoned signs in unpacked numeric fields. You can see complete documentation at http://www.uvsoftware.ca/datacnv1.htm#Part_3, but here is a shorter version.

data conversion directories

 /home/mvstest/              <-- suggest setup user 'mvstest' for test/demos
 /home/userxx/                 - or you can perform conversions in your homedir
 :-----cnvdata               <-- superdir 'cnvdata' within mvstest or your homedir
 :     :-----cpys            <-- copybooks with no extensions
 :     :     :-----citytax1    - copybook for data file to be converted
 :     :-----maps            <-- record layouts (cobmaps) created from copybooks
 :     :-----pfx1            <-- uvcopy convert jobs generated from cobmaps
 :     :-----d1ebc           <-- EBCDIC data files (renamed same as copybooks)
 :     :     :-----citytax1    - data file to be converted
 :     :     :                 - renamed same as copybook simplifies conversion
 :     :-----d2asc           <-- output data files converted to ASCII
                                         --- optional ---
 :     :-----d4pipe          <-- convert to pipe delimited to load database tables
 :     :-----sqlTC1          <-- generate SQL scripts to Create Tables
 :     :-----sqlTL1          <-- generate SQL scripts to Load Tables

Assume we have already FTP'd the mainframe copybooks into cpys/... (we can use FTP ASCII mode to translate since no packed fields in copybooks). Also assume we have already FTP'd mainframe data files into d1ebc/... in binary mode because these files do contain packed/binary fields.

We simplify the conversion by renaming the data files the same as the copybooks (would later copy/rename to wherever required for testing). We can now generate the data conversion job for demo copybook 'citytax1' with 1 script as follows:


 gencnvA citytax1
 ================

Script gencnvA 1st calls utility 'cobmap1' to convert copybook to a 'cobmap' (record layout with begin/end/length on right side). It then calls utility 'uvdata51' to convert the cobmap to the uvcopy data conversion job (shown further below).

Goto:   Begin this document End this document UVSI Home-Page

sample copybook/cobmap

 cobmap1  COBOL record layout            2013/06/10_11:00:49  pg# 0001
 cpys/citytax1                  citytax1           RCSZ=00128  bgn  end  lth typ
 * citytax1 copybook for uv demos
  01  citytax1.
      10 folio                 pic  x(00010).                 0000 0009  010
      10 name                  pic  x(00025).                 0010 0034  025
      10 address               pic  x(00025).                 0035 0059  025
      10 city                  pic  x(00020).                 0060 0079  020
      10 province              pic  x(00002).                 0080 0081  002
      10 zip                   pic  x(00006).                 0082 0087  006
      10 post-date             pic s9(00008) comp-4.          0088 0091  004bns 8
      10 land-value            pic s9(00009) comp-3.          0092 0096  005pns 9
      10 improv-value          pic s9(00009) comp-3.          0097 0101  005pns 9
      10 face-value            pic s9(00009) comp-3.          0102 0106  005pns 9
      10 maint-tax             pic s9(00007)v99.              0107 0115  009 ns 9
      10 purchase-date         pic  x(00006).                 0116 0121  006
      10 filler001             pic  x(00006).                 0122 0127  006
 *RCSZ=00128

sample uvcopy conversion job

 # uvcopy job to translate EBCDIC to ASCII, preserve packed, fix zoned signs
 opr='jobname=JOBNAME   - pfx2 name=datafilename'
 opr='copybook=citytax1 - pfx1 name=copybookname'
 uop=q0,was=a33000b33000
 fili1=?d1ebc/citytax1,rcs=00128,typ=RSF
 filo1=?d2asc/citytax1,rcs=00128,typ=RSF
 @run
        opn    all
 loop   get    fili1,a0
        skp>   eof
        mvc    b0(00128),a0       move rec to outarea before field prcsng
        tra    b0(00128)          translate entire outarea to ASCII
 #      ---                <-- insert R/T tests here for redefined records
        mvc    b88(4),a88              bns post-date
        mvc    b92(15),a92             pns land-value:face-value
        trt    b107(9),$trtsea          ns maint-tax
 put1   put    filo1,b0
        skp    loop
 eof    cls    all
        eoj

Executing the Conversion Job

We can now execute the generated data conversion uvcopy job as follows. It will copy d1ebc/citytax1 to d2asc/citytax1, translating EBCDIC to ASCII, preserving packed/binary fields,& correcting zoned signs.


 uvcopy pfx1/citytax1  <-- execute, defaulting filenames (as coded in job above)
 ====================

 uvcopy pfx1/citytax1,fili1=d1ebc/citytax1,filo1=d2asc/citytax1
 ==============================================================
 - could specify filenames on command line
 - unnecessary unless you want to change at run time

Goto:   Begin this document End this document UVSI Home-Page

1st record BEFORE & AFTER conversion

Here is the 'uvhd' display of the 1st record BEFORE & AFTER conversion. 'uvhd' is a free download (link below) for displaying packed/binary files. For the EBCDIC file, we specified option 'a' (after recsise option 'r128') which translates to ASCII so we can read non packed/binary fields. Mainframers will recognize the EBCDIC codes shown in vertical hexadecimal ('1' is x'F1', 'J' is x'D1', etc).

EBCDIC data file - 1st record uvhd


 uvhd /home/mvstest/ALLcnvdata/d1ebc/citytax1 r128a
 ==================================================
 rec#=1 rsize=128 fptr=0 fsize=1280 rcount=10
                 10        20        30        40        50        60
 r#   1 0123456789012345678901234567890123456789012345678901234567890123
      0 10130140  JOHN HENRY               1815 BOWEN ROAD          VANC
        FFFFFFFF44DDCD4CCDDE444444444444444FFFF4CDECD4DDCC4444444444ECDC
        1013014000168508559800000000000000018150266550961400000000005153
     64 OUVER           BCV9S1H1..CK..Wb....g...qq.00014906A970530
        DEECD44444444444CCEFEFCF004D005880008000770FFFFFFFFCFFFFFF444444
        64559000000000002359218101320072C0047C0111C000149061970530000000

ASCII data file - 1st record uvhd


 uvhd /home/mvstest/ALLcnvdata/d2asc/citytax1 r128
 =================================================
 rec#=1 rsize=128 fptr=0 fsize=1280 rcount=10
                 10        20        30        40        50        60
 r#   1 0123456789012345678901234567890123456789012345678901234567890123
      0 10130140  JOHN HENRY               1815 BOWEN ROAD          VANC
        3333333322444424445522222222222222233332445442544422222222225444
        1013014000AF8E085E29000000000000000181502F75E02F14000000000061E3
     64 OUVER           BCV9S1H1..C...W.........qq.000149061970530
        455452222222222244535343004D005880008000770333333333333333222222
        F5652000000000002369318101320072C0047C0111C000149061970530000000
                                bbbbpppp+pppp+pppp+zzzzzzzz+

Note that the binary/packed/zoned fields are in bytes 88-115 agreeing with the copybook/cobmap above. I have marked the 1 binary with 'bbbb', the 3 packed fields with 'pppp+', & the 1 zoned field with 'zzzzzzzz+'. We know the binary field starts at byte 88 since 1st byte is under byte 24 of the 2nd row + 64 bytes on 1st row = 88.

Converting the vertical hex to horizontal hex, the 4 byte binary field is x'000143D2', the 1st 5 byte packed field is x'000057828C'. These are the same in the output ASCII record because they are moved back after translating the entire record (which would destroy packed/binary fields).

The zoned field is '00014906A' in the Input record & '000149061' in the output record. The units position 'A' (+1 in EBCDIC) is converted to '1' since there is no zones in ASCII for positive digits. An EBCDIC -1 is 'J' which would be converted to 'q' in ASCII.

 Negative '0123456789' is '}JKLMNOPQR' in EBCDIC & would be converted to
 'pqrstuvwxy' in ASCII. Positive '0123456789' is '{ABCDEFGHI' in EBCDIC &
 would be converted to just '0123456789' in ASCII (no zones).

Goto:   Begin this document End this document UVSI Home-Page

data conversion explanation

We will briefly explain the conversion instructions, so you will understand the minor changes required for multi record type files. We will sequence number the get/put 'loop' instructions for the explanations following.

 09 loop   get    fili1,a0
 10        skp>   eof
 11        mvc    b0(00128),a0       move rec to outarea before field prcsng
 12        tra    b0(00128)          translate entire outarea to ASCII
 13 #      ---                <-- insert R/T tests here for redefined records
 14        mvc    b88(4),a88              bns post-date
 15        mvc    b92(15),a92             pns land-value:face-value
 16        trt    b107(9),$trtsea          ns maint-tax
 17 put1   put    filo1,b0
 18        skp    loop
  1. 'get' reads next record into area 'a'
  2. 'skp>' branches to close files & end job at EOF
  3. 'mvc' moves all 128 bytes of record from area 'a' to area 'b'

  4. 'tra' translates entire record to ASCII (destroys packed/binary fields)

  5. 'mvc' moves binary field post-data bytes 88-91 from input area 'a' to output area 'b' thus restoring/preserving original contents.

  6. 'mvc' moves 3 packed fields land-value,improv-value,face-value bytes 92-106 (3 * 5 = 15 bytes) from input area 'a' to output area 'b' thus restoring/preserving original contents.

  7. 'trt' corrects the zoned signs in maint-tax using translate table 'trtsea' Note that mainframe zoned signs could be '{ABCDEFGHIJ' for +0123456789 and '}JKLMNOPQR' for -0123456789. For unix/linux/windows Micro Focus COBOL there are no zones for +digits and -digits are 'pqrstuvwxy' The maint-tax shown above is '00014906A' in the EBCDIC record, which is $1,490.61+. In the ASCII record the 'A' (+1) becomes simply '1'. A -1 would have been 'J' in EBCDIC & become 'q' in ASCII.

Goto:   Begin this document End this document UVSI Home-Page

multi record-type conversion sample

Most auto-generated conversion jobs require no manual changes as in the example shown above.

For files that have multiple record-types, you will need to insert a few instructions, to test the record type and branch to the appropriate bank of automatically generated instructions for that record-type.

Here is an example. We will show the results after inserting instructions for citytax2 which has 3 record types - Header (no packed/binary fields), Tax-record (1 binary, 3 packed, 1 zoned signed numeric), and Payment-record (12 contiguous packed 5 byte fields = 60 bytes).

 loop   get    fili1,a0
        skp>   eof
        mvc    b0(00128),a0       move rec to outarea before field prcsng
        tra    b0(00128)          translate entire outarea to ASCII
 #------------------------------------------------------------------------
 # test Record Type & skip to appropriate instrns to preserve packed & signed
        cmc    b8(1),'H'           Header rec (no packed or signed)
        skp=   put1
        cmc    b8(1),'T'           Tax rec ?
        skp=   tax1
        cmc    b8(1),'P'           Payment rec ?
        skp=   pay1
        msg    b0(80)              show record with invalid type
        msgw   'Error Rec Type invalid - enter to bypass'
        skp    loop
 #-------------------------------------------------------------------------
 tax1   mvc    b88(4),a88              bns post-date:face-value
        mvc    b92(15),a92             pns land-value:face-value
        trt    b107(9),$trtsea          ns maint-tax
        skp    put1                 skip to output
 #
 pay1   mvc    b20(60),a20             pns mthpayments
 #      ---
 put1   put    filo1,b0
        skp    loop

See more explanations at http://www.uvsoftware.ca/datacnv1.htm#5A1 - 5B2.

Our demo files have very few packed/binary/signed fields. Actual mainframe data files could have hundreds of fields generating hundreds of instructions, especially when character and packed/binary/signed fields are intermixed, which reduces the consolidations possible for contiguous fields.

Goto:   Begin this document End this document UVSI Home-Page

Comprehensive Data File Conversion

The Data Conversion Instructions in this newsletter convert 1 file at a time and are simplified by renaming the data file the same as the copybook.

Please see http://www.uvsoftware.ca/jclcnv2real.htm#Part_4 for the high volume data conversion instructions you would need to migrate all your files.

Those instructions will generate jobs to convert all required data files, and you can execute them all with 1 instruction. You do need a control-file to relate the copy-book-name to the data-file-name. Some mainframe sites may already have such a file, which can be transferred to unix/linux and reformatted as documented at http://www.uvsoftware.ca/jclcnv2real.htm#4A1.

Goto:   Begin this document End this document UVSI Home-Page

links to related information

 http://www.uvsoftware.ca                - UV Software Home Page
 http://www.uvsoftware.ca/uvintro.htm#G1 - customer list & reference letters
 http://www.uvsoftware.ca/uvprices.htm   - prices & partner agreements
 http://www.uvsoftware.ca/datacnv1.htm        - Data conversion documentation
 http://www.uvsoftware.ca/datacnv1.htm#Part_2 - signscan2 if no copybook available
 http://www.uvsoftware.ca/datacnv1.htm#Part_3 - data conversion from copybook
 http://www.uvsoftware.ca/datacnv1.htm#Part_4 - generate pipe delimited to load RDBMS
 http://www.uvsoftware.ca/datacnv1.htm#Part_5 - converting multi record type files
 http://www.uvsoftware.ca/datacnv1.htm#Part_8 - converting variable length files (FTP RDW)
 http://www.uvsoftware.ca/jclcnv2real.htm#Part_4 - comprehensive Data File Conversion
 http://www.uvsoftware.ca/libuvhd.htm     - display data files in vertical hex (free utility)
 http://www.uvsoftware.ca/uvhdcob.htm     - displays data field contents & copybook fieldnames
 http://www.uvsoftware.ca/uvcopy1.htm     - uvcopy Introduction & Overview
 http://www.uvsoftware.ca/uvcopy3.htm     - uvcopy instructions
 http://www.uvsoftware.ca/uvsort.htm      - uvsort documentation
 http://www.uvsoftware.ca/jclcnv1demo.htm - JCL Conversion to Unix/Linux scripts
 http://www.uvsoftware.ca/news/           - all newsletters past & present

I invite your feedback. Let me know if these news letters are useful to you, and any suggestions you may have for improvements.

Thank You, Owen Townsend, UV Software

Goto:   Begin this document End this document UVSI Home-Page