SQLjobs.doc - Converting Mainframe files & Loading SQL databases - CONTENTS

A1. Overview
B1. Initial Setup Sub-Directories for data files & files required to
  generate the uvcopy jobs that perform the conversions
C00. Operating Instructions - Overview
------------ detailed Operating Instructions --------------
C01.  Creating the COBOL copy-book (record-layout) if not provided
C02.  Loading mainframe data (from mag tape usually)
C03.  Printing 1st few blocks (with uvhd) to determine data format
C04.  Writing special uvcopy jobs to pre-process (split) complex data files
 (multi record types, occurs depending on, variable lth block/record sizes)
C05.  Creating spearate copybooks for the separated data files above
C06.  Verifying the data matches the copy-book.
C07.  Modifying the copy-book to produce output suitable for loading databases
C08.  Generating the uvcopy job to translate EBCDIC to ASCII (if required)
C09.  Generating the uvcopy job to convert data to '|' delimited format
C10.  Modifying the generated job if required
C11.  Executing the generated job to convert data to '|' delimited format
C12.  Verifying the '|' delimited output file vs input data & copybook
C13.  Writing the '|' delimited file to CD for transport to SQL server
C14.  Generating the control file to load the SQL database
C15.  Printing vital items & filing in 3 ring binder for documentation
C16.  Create diskette with copybooks, uvcopy conversion job,& SQL loader file
C17.  Loading delimited file on SQL Server, using generated control file
D1. Concise Operating Instructions (on 1 page)
- for the citytax test file used in section C above
- EBCDIC file with packed & signed fields, but fixed length
  with no complexities requiring special pre-processing
E1. Sample conversion of a multi record type file EBCDIC file
- demos special uvcopy job 'passtax0' to split test file 'passtax'
  to separate files depending on record type
F1. Sample conversion of 'occurs depending on' & 'variable length file'
- demos special uvcopy job 'hightax0' to strip variable length prefixes
  from test file 'hightax' writing base records & legal descriptions
  to separate files.
G1. Demo program 'unvar1' to strip variable length block & record prefixes.
- includes the 'getv' subrtn intended to help you write jobs to handle
  occurs depending on when combined with variable length prefixes.
H1. Summary of uvcopy jobs & scripts documented in this section
- cobmap1, genpipe1, genpipe1.sub, uvdata52, listpipe1, sqlload1
- genpipeB, gencnvB

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

A1. SQLjobs.doc - converting files & loading SQL databases - OVERVIEW

These jobs will convert mainframe type data into formats suitable for loading data-bases, possibly for internet applications. The uvcopy interpretive language is ideally suited to these data conversions, and will save you a lot of time vs the alternative of writing lengthy programs in COBOL or C.

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

If you do not already have COBOL copybooks, you could make them so you can generate the conversion jobs automatically, or you could write the uvcopy job code directly (using the many examples presented in this section).

SQLjobs.doc vs SQLcnvrt.doc

This section (SQLjobs) is a continuation of SQLcnvrt. SQLjobs presents more detailed operating instructions, and demonstrates how to write special uvcopy jobs to convert files with multiple record types, occurs depending on, or variable length files with block/record size prefixes.

The previous section (SQLcnvrt) presents the conversion theory & gives simple examples (in preparation for this section SQLjobs).

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

B1. SQLjobs.doc - converting files & loading SQL databases - Initial SETUP

Initial Setup Sub-directories

You should setup a separate super-directory & multiple sub-directories for this process of converting mainframe files to '|' delimited files. Pick a file system with plenty of space for your projected requirements. For discussion purposes let us call our super-directory /home2/SQL/.

Sub-directories are made for the different types of files, so that we can keep the same name as the original copybook, as we convert copybooks to record layouts, to skeletons,& to complete jobs.

 1a. cd /home2          - change to a file system with sufficient space
 1b. mkdir SQL          - make a super-directory for the various subdirs
 1c. cd SQL             - change into it
                        - make sub-sub-directories for:
 2a. mkdir dat1         - original input: mainframe data files
                          loaded here from mag tape, 3480, CD, ??
 2b. mkdir dat1a        - input data files split into separate record types
                          if required (else convert direct from dat1 to dat2)
 2c. mkdir dat2         - output from conversion to '|' delimited format
 2d. mkdir dat3         - output from 'mkisofs' preparation for writing CD
 2e. mkdir cpy1 cpy2 cpys - COBOL copybooks
                          - as received, corrected, after optimization
 2f. mkdir map2 maps      - 'cobmap's generated from copybooks
 2g. mkdir ctl          - control files to relate actual data-file-names
                          to COBOL copy-book-names
 2h. mkdir pfs          - uvcopy jobs for special circumstances
                          (splitting input data files for record types,
                           occurs depending on, variable length tape, etc)
 2g. mkdir pfx1         - uvcopy jobs generated by gencnv1
                          assuming data file names same as copybook names
 2h. mkdir pfx2         - uvcopy jobs (with correct data filenames)
                   note - pfx1 & pfx2 required only for EBCDIC files with
                          packed decimal &/or unpacked zoned decimal signs.
 2j. mkdir pfp1         - uvcopy jobs generated by genpipe1 (to | delimit)
                          assuming data file names same as copybook names
 2k. mkdir pfp2         - uvcopy jobs (with correct data filenames)
 2l. mkdir pfp2.bak     - uvcopy jobs backup, in case of regens overwriting
                          pfp2 jobs with modified code (R/T tests, etc)
                          (don't want to lose manual editing)
 2j. mkdir sqls         - SQL loader control files

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

B2. SQLjobs.doc - converting files & loading SQL databases - Initial SETUP

load test data & copybook to demo Op. Instrns.

You can demo these OPERATING INSTRUCTIONS, by setting up the directories suggested on the previous page,& using the supplied test file & copybook.


 3a. cd /home2/SQL                  - change to subdir setup on prior page
     =============

 3b. cp /home/uvadm/dat1/citytax dat1  - copy test data file to subdir dat1
     ================================

 3c. cp /home/uvadm/cpy1/citytax cpys  - copy test copybook to subdir cpys
     ================================

Notes re Input Media

If you have purchased the Vancouver Utilities, the input will be determined by your gardware configuration. If you are sending media to UV Software, the following media are available:

  1. 9 track open reel magnetic tape (1600 or 6250 BPI)
  2. 3480 or 3490 cartridge magnetic tape
  3. 4 MM DAT tape DDS, DDS2, or DDS3
  4. CD

    Notes re Mag Tape Labels

Data from mainframes is usually supplied on mag tape (9 track open reel, or 3480 cartridge). You must 1st determine whether the tapes have IBM standard labels or are unlabeled. uvcopy job t9list1 will list the 1st 60 bytes of each block on mag tape. If you see VOL1, HDR1, HDR2, TAPEMARK, then you know the tape has standard labels which need to be bypassed before copying (with the UNIX dd utility below).

These instructions are being written for SCO OpenServer UNIX, and the tape drive on my system is known as '/dev/rmt/ntape2'. This will be different on other systems (/dev/rmt/nrtape2 for Unix Ware). Note that you need to specify the 'no rewind' device if standard labels are present.

We can use the UNIX 'tape' utility to bypass the standard labels & then use the UNIX 'dd' utility to copy tape to disc. The Open Server tape utility is 'tape', but is 'tapecntl' on UnixWare & 'mt' on some systems.

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

C00. Convert Mainframe files for Loading SQL Databases

Operating Instructions - Contents

This page is a summary or overview of the procedures involved. See the detailed Operating Instructions on the following pages --->

  1. Create the COBOL copybook (record layout). - generate/print the 'cobmap', showing field start/end/length bytes.

  2. Loading data from mainframe for conversion on UNIX - usually on 9 track tapes or 3480 cartridges

  3. Print 1st few blocks to determine rcsz & any complexities - using the 'uvhd' (hexdump) utility

  4. Determine if input needs to be split to separate files. - if multiple record types, or if 'occurs depending on' If so - create separate copybooks for split files.

  5. Write special uvcopy jobs to split file, if required.

  6. Verify data matches copybook record layout, using 'uvhdcob' which displays the data beside the COBOL copybook fieldnames. - list the 1st few records for documentation.

  7. Modify COBOL copybook as required to improve database loading. - eliminate redefined fields & occurs fields

  8. Generate job to translate EBCDIC to ASCII & preserve packed fields. 'gencnvB' reads the cobmap & creates uvcopy job in subdir pfx2. - not required if input files is ASCII & no packed fields present.

  9. Generate job to convert data to '|' delimited format 'genpipeB' reads the cobmap & creates uvcopy job in subdir pfp2.

  10. Modify generated job to convert data to '|' delimited - integrate instructions from gencnvB into the genpipeB output job.

  11. Execute the job to convert data to '|' delimited.

  12. Verify the '|' delimited output file. 'listpipe1' lists the data fields beside the COBOL copybook fieldnames.

  13. Write delimited file to CD for transport to SQL Server 'mkisofs' pre-processes the file for writing to the CD by 'cdrecord'

  14. Generate the SQL Loader control file.

  15. create printouts for documentation & file in 3-ring binder - in case of reruns, repeat runs, finger pointing, good practices - copybookmaps, uvhd/uvhdcob 1st few input records, listpipe1 1st few output records, SQL loader control file, uvcopy conversion job created by genpipeB, any special uvcopy jobs for complex files.

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

C01. Convert Mainframe files for Loading SQL Databases

#01 Create the COBOL copybook (record layout)

If the data files are coming from outside agencies, be sure to request the COBOL copybook on diskette or via Email. If the copybook is supplied on paper, you will have to key it or scan it if you have a PC scanner.


 01a. vi cpys/citytax           - create copybook from documents supplied
      ===============             (if not otherwise available)
      For the demo setup on pages B1 & B2, the citytax copybook was
      copied from /home/uvadm/cpy1 to /home2/SQL/cpys.

 01b. uvcopy cobmap1,fili1=cpys/citytax,filo1=maps/citytax
      ====================================================
           - convert the copybook into the 'cobmap' record layout with
             calculated field start,end,length,type on right side

 01c. uvlp12 maps/citytax       - list the cobmap
      ===================
 cobmap1  start-end bytes for cobol record fields    199911301835  pg# 0001
 cpys/citytax                   taxrec             RCSZ=0128  bgn-end lth typ
 * citytax - test/demo copybook for sqljobs.doc
  01  taxrec.
      10 folio             pic  x(8).                         000-0007 008
      10 section           pic  x(2).                         008-0009 002
      10 owner             pic  x(25).                        010-0034 025
      10 address           pic  x(25).                        035-0059 025
      10 city              pic  x(20).                        060-0079 020
      10 state             pic  x(2).                         080-0081 002
      10 zip               pic  9(6).                         082-0087 006 n  06
      10 post-date         pic  9(6)     comp-3.              088-0091 004pn  06
      10 land-value        pic  s9(9)    comp-3.              092-0096 005pns 09
      10 imprv-value       pic  s9(9)    comp-3.              097-0101 005pns 09
      10 face-value        pic  s9(7)v99 comp-3.              102-0106 005pns 09
      10 maint-tax-due     pic  s9(7)v99.                     107-0115 009 ns 09
      10 maint-date        pic  x(6).                         116-0121 006
      10 maint-time        pic  x(6).                         122-0127 006
 *RCSZ=0128                                                       0128

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

C02. Convert Mainframe files for Loading SQL Databases

#02 Loading data from mainframe for conversion on UNIX

If you are demonstrating these operating instructions, as suggested on the previous page, you would skip this #01 page, since we have already loaded the test file & copybook (see the previous page).

Loading Data from Mainframe Magnetic Tapes

<---- See notes re mag tape on page 'B2'.


 02a. uvcopy t9list1,fili1=/dev/rmt/ntape2   - list 1st few blocks to see if
      ====================================     tape has standard labels ?

 02b. tape rfm /dev/rmt/ntape2               - bypass standard tape labels
      ========================                 (rfm = Read Forward Mark)

 02c. dd if=/dev/rmt/ntape2 of=dat1/citytax bs=32000  - copy tape to disc
      ==============================================

Alternative for multi-file standard labelled tape


 02d. uvcopy t9copy2,fili1=/dev/rmt/ntape2,fild2=dat1
      ===============================================
           - copy all files on mag tape into the dat1 directory
             & name them using the HDR1 filenames on the mag tape

Alternative Loading from CD

 02e. su root                   - must be root to mount CD

02f. mount -r /dev/cd1 /mnt - mount CD

 02g. ls -l /mnt                - display CD contents
 02h. cp /mnt/citytax dat1      - copy data file to dat1
 02i. umount /dev/cd1           - unmount CD

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

C03. Convert Mainframe files for Loading SQL Databases

#03 Print 1st few blocks to determine rcsz & any complexities

Use 'uvhd' to print the 1st few blocks of data to verify the data agrees with the supplied COBOL copybook (record layout). Verify the record size & whether any complexities exist that require writing a special uvcopy job to pre-process the data into consistent fixed rcsz files required by the conversion script (genpipeB).


 03a. uvhd dat1/citytax r256a   - investigate data file
      =======================   - determine correct record size ?
                             ** sample uvhd report **
 r#       1           1         2         3         4         5         6
 b#       0 0123456789012345678901234567890123456789012345678901234567890123
          0 10130140  JOHN HENRY               1815 BOWEN ROAD          VANC
            FFFFFFFF44DDCD4CCDDE444444444444444FFFF4CDECD4DDCC4444444444ECDC
            1013014000168508559800000000000000018150266550961400000000005153
         64 OUVER           BCV9S1H1.bi...Wb....g...qq.00014906A970530191809
            DEECD44444444444CCEFEFCF0888005880008800770FFFFFFFFCFFFFFFFFFFFF
            645590000000000023592181029C0072C0047C0111C000149061970530191809
        128 10139923  GEORGE BROWN             1250 EAST PENDER STREET  VANC
            FFFFFFFF44CCDDCC4CDDED4444444444444FFFF4CCEE4DCDCCD4EEDCCE44ECDC
            1013992300756975029665000000000000012500512307554590239553005153
        192 OUVER           BCV5L1W1.bi...Wb....g...yR<00014626J980601190156
            DEECD44444444444CCEFDFEF0888005880008800754FFFFFFFFDFFFFFFFFFFFF
            645590000000000023553161029C0072C0047C0182C000146261980601190156
 dat1/citytax rsz=256 totrecs=5 current=1 fsiz=1280 fptr=0
 null=next,r#=rec,s=search,u=update,p=print,i=iprint,w=write,t=tally,c=checkseq
 ,h1=char,h2=hex,q=quit,?=help --> q

This is an EBCDIC data file, but option 'a' translates the character line (1st of the 3 line groups) to ASCII. This is 'vertical hexadecimal'. The 2nd line shows the zones & the 3rd line shows the digits.

For example the 1st byte is shown as a '1' on the character line, the zone is 'F' on the 2nd line,& the digit is '1' on the 3rd line. An EBCDIC 1 is x'F1' & this has been translated to x'31' which is ASCII '1'.

On our 1st uvhd above, we specified r256 (rcsz=256) since we did not know. You can see from the above display that the record size is actually 128 so our command should be as follows (to display 1 record at a time (vs 2):


 uvhd dat1/citytax r128as2  <-- option 'r' specifies rec size (256 default)
 =========================  - option 'a' to translate to ASCII
                            - option 's2' for space 2 (as shown above
 --> i3                     - uvhd command to print 1st 3 records
   ======

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

C04. Convert Mainframe files for Loading SQL Databases

#04 Create separate COPY-BOOKS if required (for split files)


 04. vi cpys/citytax     - split redefined records to separate copybooks
     ===============

This is NOT required for citytax demo, but see examples in sections E,F,& G.

Creating separate copybooks is directly related to splitting the original data file into separate files (see next page) & is required for the same reasons (multiple record types, occurs depending on, or variable length tape format).

Creating separate copybooks is usually easy to do if we have the redefined definitions 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.

 04a. Eliminate 'redefined records' (multiple record types in 1 file).
      Since databases do not allow multiple record types in 1 table, you
      would write a special uvcopy job to split different record types into
      different files (see example in section 'E').
      You would then split the original copybook to separate copybooks
      for each redefined record.
 04b. 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 section 'F'.
 04c. Variable length tape format (with block & record size prefixes)
      You will not need a separate copybook for this case since the block
      & record prefixes are not part of the copybook definitions.
      You will only need the special uvcopy job to strip off the record
      size prefixes, to make the data records agree with the copybook.
      See the example presented in section 'G'.
      The variable length problem could be combined with the occurs depending
      on, and the uvcopy job could be writtent o perform both functions.

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

C05. Convert Mainframe files for Loading SQL Databases

#05 Write special uvcopy jobs if required to split file

This is NOT required for this citytax demo (see examples in sections E,F,G).

Special uvcopy jobs would be required for the following reasons:

If you need to split the original data file, I suggest you would write the separate files into the 'dat1a' subdirectory & name the separate files with a suffix 1,2,3,etc. For example, 'dat1/citytax' might be split to: dat1a/citytax1, dat1a/citytax2, & dat1a/citytax3.

If you need to split the data file, you will also need to create separate copybooks for the separate files. These could be named cpys/citytax1, cpys/citytax2, cpys/citytax3 (see previous page).

I suggest you name the special uvcopy job as 'citytax0' & note that the setup procedures (in section 'B'), created a separate sub-directory for these jobs which was named 'pfs'.


 05. vi pfs/citytax0       - create special uvcopy job to split data file
     ===============

 05b. uvcopy pfs/citytax0,fili1=dat1/citytax,filo1=dat1a/citytax1
      ===========================================================
                         ,filo2=dat1a/citytax2,filo3=dat1a/citytax3
                         ==========================================

Note that steps 6-15 would then be performed multiple times for each of the split files (citytax1, citytax2, citytax3), and that the conversion of these split files will go from dat1a to dat2, instead of from dat1 to dat2 (when no splits have to be done).

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

C06. Convert Mainframe files for Loading SQL Databases

#06 Verify data (uvhd) to COBOL layout (cobmap)

If possible use uvhdcob which displays data beside COBOL copybook fieldnames making it easy to see if data is consistent with the copybook.


 06a. uvhdcob dat1/citytax maps/citytax a     - verify data vs copybook
      ===================================
 dat1/citytax rsz=128 totrecs=10 current=1 fsiz=1280 fptr=0
 cobmapfile=maps/citytax today=199911301836 datalastmod=1999113017
 rec#       1 fieldname occurs  bgn end typ<------ data (hex if typ=p/b) --->
 001 folio                        0   7    10130140
 002 section                      8   9
 003 owner                       10  34    JOHN HENRY
 004 address                     35  59    1815 BOWEN ROAD
 005 city                        60  79    VANCOUVER
 006 state                       80  81    BC
 007 zip                         82  87 n  V9S1H1
 008 post-date                   88  91pn  0082898C
 009 land-value                  92  96pns 000057828C
 010 imprv-value                 97 101pns 000004878C
 011 face-value                 102 106pns 000171710C
 012 maint-tax-due              107 115 ns 00014906A
 013 maint-date                 116 121    970530
 014 maint-time                 122 127    191809
 null=next,r#=rec,s=search,u=update,p=print,i=iprint,w=write,t=tally,c=checkseq
 ,e=exit to uvhd, q=quit, ?=help --> q

'uvhdcob' makes it easy to see if data is consistent with the copybook, but you may not be able to use at this point for complex files - files that have multiple record types, occurs depending on, or variable length with block/record size prefixes.

These files must be split to separate files by record types or occurs depending on, or stripped of variable block/record size prefixes.

Then you can make separate copybooks to be able to use uvhdcob.

See examples of these problems & solutions later in sections E,F,G.

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

C07. Convert Mainframe files for Loading SQL Databases

#07 Modify COBOL copybook as required

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

 07a. 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|
 07b. 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.

<--- This point has already been covered in 'C04' & 'C05'.

 07c. 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|.
 07d. 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.
 07e. The notes above for date fields also apply to 'time' fields which will
      be edited as |HH:MM:SS| by the 'bal tim6' subrtn.

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

C07a. SQLjobs - Convert Mainframe files for Loading SQL Databases

#07 modify COBOL copybook (continued)

 07f. Watch for date fields in format 'MMDDYY' (vs default 'YYMMDD') and
      add the 'mdy' option on the 'bal dat6' instruction, for example:
         mvn    c700(6),b88(4p)                   #8 post-date
         bal    dat6,'700','mdy'        <-- ADD 'mdy' date format <-- NOTE
 07g. 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    c700(7),b88(4p)      #8 post-date <-- change (7) to (6)
         bal    dat6,'700'
 07h. 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.
 07i. Watch for 'occurs depending on'.
      For example, legal descriptions in a tax file might be defined
      at the end of the fixed record portion with occurs depending on.
      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 section 'F'.

<--- This point has already been discussed in sections 'C04' & 'C05'.

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

C08. Convert Mainframe files for Loading SQL Databases

#08 Generate job to translate EBCDIC to ASCII

Actually we will extract only the instructions that preserve packed fields & correct numeric signs. These instructions will be inserted into the uvcopy job generated by 'genpipeB' (see next step). We wont need the translate to ASCII instruction since the next job has an option for this.

See complete documentation in MFcnvrt.doc. Once you understand the concepts the following should be enough for operating instructions.

You might also review section 'B' above explaining the sub-directories & control file required for these generation jobs.

cpys
  • COBOL copybooks
maps
  • cobmaps, record layouts generated from copybooks
ctl
  • control file to relate data filenames to copybook names
pfx1
  • uvcopy job generated from copybook (intermediate)
pfx2
  • uvcopy job complete with correct data filenames

First add an entry to the control file for the data file to be converted. Let us assume the control file already has an entry for 'warmas1', and we will add an entry for 'citytax'.


 08a. vi ctl/ctlfile1     - edit control file to add new datafile/copybook
      ===============
      # ctlfile1 - control file for genpipe1 & gencnv1
      #          - see MFcnvrt.doc & SQLcnvrt.doc
      # - used to determine copybook name from datafilename
      #   (most same here, but could be different at some sites)
      #
      warmas1             cpy=warmas1  rcs=00064
      citytax             cpy=citytax  rcs=00225

 08b. gencnvB citytax citytax
      =======================
           - generate uvcopy job to translate data from EBCDIC to ASCII

The generated uvcopy job is left in subdir 'pfx2', see next page --->

You can see the 'gencnvB' script listed in MFcnvrt.doc.

Note that this step is not required for files that have no packed or zoned decimal signs in the EBCDIC file. You would still need to add an entry to the control file as explained above.

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

C08a. SQLjobs - Convert Mainframe files for Loading SQL Databases

uvcopy job generated by gencnvB - pfx2/citytax

 opr='citytax citytax - generated by cobmap1,uvdata51,uvdata52'
 uop=q0
 was=a25000b25000
 fili1=${UVDATA1}/citytax,rcs=00128,typ=RSF
 filo1=${UVDATA2}/citytax,rcs=00128,typ=RSF
 @run
        opn    all
 loop   get    fili1,a0
        skp>   eof
        mvc    b0(00128),a0       move rec to outarea before field processing
        tra    b0(00128)          translate entire outarea to ASCII
 #      ---                <-- insert R/T tests here for redefined records
 #------------------------------------------------------------------------
        mvc    b88(19),a88          packed post-date:face-value     <-- NOTE
        trt    b107(9),$trtsea      num-sign maint-tax-due          <-- NOTE
 #------------------------------------------------------------------------
 put1   put    filo1,b0
        skp    loop
 eof    cls    all
        eoj

This job (created by 'gencnvB') performs the basic conversion of EBCDIC files to ASCII, preserving the packed fields (which would be destroyed by translation) & correcting any zoned decimal signs.

This job is not required if no packed or signed fields are present, since we have an option to do the EBCDIC to ASCII translate in the next job (generated by 'genpipeB), which will perform the '|' delimiting.

 To save running 2 jobs, we will simply steal the instructions that preserve
 packed fields & correct signs, from this job & insert into the next job.
 In this case there are only 2 instructions (between the #--------- lines).
 This instruction transfer is illustrated on the next few pages.

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

C09. Convert Mainframe files for Loading SQL Databases

#09 Generate job to convert data to '|' delimited

See complete documentation in SQLcnvrt.doc. Once you understand the concepts the following should be enough for operating instructions.

You might also review section 'B' above explaining the sub-directories & control file required for these generation jobs.

cpys
  • COBOL copybooks
maps
  • cobmaps, record layouts generated from copybooks
ctl
  • control file to relate data filenames to copybook names
pfp1
  • uvcopy job generated from copybook (intermediate)
pfp2
  • uvcopy job complete with correct data filenames
  • note these are 'pfp1/2' vs 'pfx1/2' on previous page.

Add an entry to the control file for the data file to be converted, if you have not already done so for the EBCDIC to ASCII conversion on the previous page. Assuming the control file already has an entry for 'warmas1', we will add an entry for 'citytax'.


 09a. vi ctl/ctlfile1     - edit control file to add new datafile/copybook
      ===============
      # ctlfile1 - control file for genpipe1 & gencnv1
      #          - see MFcnvrt.doc & SQLcnvrt.doc
      # - used to determine copybook name from datafilename
      #   (most same here, but could be different at some sites)
      #
      warmas1             cpy=warmas1  rcs=00064
      citytax             cpy=citytax  rcs=00225

 09b. genpipeB citytax citytax
      ========================
                - generate uvcopy job to convert data to '|' delimited
      --> a1  <-- reply 'a1' at the prompt if the data file is EBCDIC.

Most mainframe data files will be EBCDIC, but this option allows us to convert files that are already in ASCII.

You can see the 'genpipeB' script listed in SQLcnvrt.doc. Note that this script is 'genpipeB' vs 'gencnvB' on the previous page.

The generated uvcopy job is left in 'pfp2', see listing next page --->

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

C09a. SQLjobs - Convert Mainframe files for Loading SQL Databases

uvcopy job generated by genpipeB - pfp2/citytax

 opr='citytax citytax - generated by cobmap1,genpipe1,uvdata52'
 # - uvcopy job to convert & fix data fields 100 bytes apart
 #   for compress & '|' delimit (see 'var' instrn below)
 uop=q0
 was=a10000b10000c50000d10000
 fili1=${UVDATA1}/citytax,rcs=0128,typ=RSF
 filo1=${UVDATA2}/citytax,rcs=9000,typ=LSTtd
 @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(0128),a0             - move input area a to area b
        tra    b0(0128)      <-- for EBCDIC input, else remove
 #      ---                  <-- insert R/T tests if redef records
 #      ---                  <-- insert instrns from uvdata51 if packed fields
        mvc    c0(8),b0(8)                       #1 folio
        mvc    c100(2),b8(2)                     #2 section
        mvc    c200(25),b10(25)                  #3 owner
        mvc    c300(25),b35(25)                  #4 address
        mvc    c400(20),b60(20)                  #5 city
        mvc    c500(2),b80(2)                    #6 state
        mvc    c600(6),b82(6)                    #7 zip
        mvn    c700(6),b88(4p)                   #8 post-date
        bal    dat6,'700','mdy'        <-- ADD 'mdy' date format <-- NOTE
        edt    c800(11),b92(5p),'+zzzzzzzzz9'    #9 land-value
        sqz    c800(11),' '
        edt    c900(11),b97(5p),'+zzzzzzzzz9'    #10 imprv-value
        sqz    c900(11),' '
        edt    c1000(11),b102(5p),'+zzzzzzz.99'  #11 face-value
        sqz    c1000(11),' '
        edt    c1100(11),b107(9),'+zzzzzzz.99'   #12 maint-tax-due
        sqz    c1100(11),' '
        mvc    c1200(6),b116(6)                  #13 maint-date
        bal    dat6,'1200'
        mvc    c1300(6),b122(6)                  #14 maint-time
        bal    tim6,'1300'
        var    d0(8000),c0(100),0014,'|'
        trt    d0(8000),$trtchr
        clr    c0(01400),' '
 put1   put    filo1,d0
        skp    loop
 #
 eof    cls    all
        eoj
 @pf2=genpipe1.sub

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

C10. Convert Mainframe files for Loading SQL Databases

#10 Modify job to convert data to '|' delimited

We will extract instructions from the job generated by 'gencnvB' & insert them into the job generated by 'genpipeB'. We need only the instructions that preserve the packed fields & correct numeric signs.


 10a. vi pfp2/citytax    - Modify job to convert data to '|' delimited
      ===============
      :19                - position after the 'tra' translate instruction
      :r pfx2/citytax    - read in the gencnvB job preserve packed & signed
      :12dd              - delete 1st 12 lines, since we want only the
                           instructions that preserve packed & correct signs
      /put1              - advance to end of desired instructions
      :4dd               - delete the tail-end of the gencnvB uvcopy job
      :wq                - write out our combined job

Please see the result listed on the next page --->

other changes to data '|' delimit jobs

You may need to make other changes to the jobs generated by genpipeB, depending on the circumstances.

 10b. add option on the date conversion 'bal' instruction for dates
      that are in the 'mmddyy' format vs the default 'yymmdd'.
        mvn    c700(6),b88(4p)                   #8 post-date
        bal    dat6,'700','mdy'        <-- ADD 'mdy' date format <-- NOTE

Note that procedures for items such as 'occurs depending on' will be illustrated later in this SQLjobs.doc. A special 1-shot uvcopy job will be written to separate the 'occurs depending on' data to a separate file. A separate copybook will be created & the conversion jobs can then be generated. You might name the copybook 'citytax2' & the procedures would be similar to these procedures (which might be renamed 'citytax1').

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

C10a. SQLjobs - Convert Mainframe files for Loading SQL Databases

genpipeB uvcopy job - after inserting instructions from gencnvB

 opr='citytax citytax - generated by cobmap1,genpipe1,uvdata52'
 # - uvcopy job to convert & fix data fields 100 bytes apart
 #   for compress & '|' delimit (see 'var' instrn below)
 uop=q0
 was=a10000b10000c50000d10000
 fili1=${UVDATA1}/citytax,rcs=0128,typ=RSF
 filo1=${UVDATA2}/citytax,rcs=9000,typ=LSTtd
 @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(0128),a0             - move input area a to area b
        tra    b0(0128)      <-- for EBCDIC input, else remove
 #      ---                  <-- insert R/T tests if redef records
 #---------------------------------------------------------------------------
 #      ---        <-- insert instrns from uvdata51 if packed fields
        mvc    b88(19),a88          packed post-date:face-value      <-- NOTE
        trt    b107(9),$trtsea      num-sign maint-tax-due           <-- NOTE
 #---------------------------------------------------------------------------
        mvc    c0(8),b0(8)                       #1 folio
        mvc    c100(2),b8(2)                     #2 section
        mvc    c200(25),b10(25)                  #3 owner
        mvc    c300(25),b35(25)                  #4 address
        mvc    c400(20),b60(20)                  #5 city
        mvc    c500(2),b80(2)                    #6 state
        mvc    c600(6),b82(6)                    #7 zip
        mvn    c700(6),b88(4p)                   #8 post-date
        bal    dat6,'700','mdy'          <-- ADD 'mdy' date format <-- NOTE
        edt    c800(11),b92(5p),'+zzzzzzzzz9'    #9 land-value
        sqz    c800(11),' '
        edt    c900(11),b97(5p),'+zzzzzzzzz9'    #10 imprv-value
        sqz    c900(11),' '
        edt    c1000(11),b102(5p),'+zzzzzzz.99'  #11 face-value
        sqz    c1000(11),' '
        edt    c1100(11),b107(9),'+zzzzzzz.99'   #12 maint-tax-due
        sqz    c1100(11),' '
        mvc    c1200(6),b116(6)                  #13 maint-date
        bal    dat6,'1200'
        mvc    c1300(6),b122(6)                  #14 maint-time
        bal    tim6,'1300'
        var    d0(8000),c0(100),0014,'|'
        trt    d0(8000),$trtchr
        clr    c0(01400),' '
 put1   put    filo1,d0
        skp    loop
 #
 eof    cls    all
        eoj
 @pf2=genpipe1.sub

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

C11. Convert Mainframe files for Loading SQL Databases

#11 Execute job to convert data to '|' delimited

You might review section 'B' above explaining the sub-directories used for generating & executing these jobs.

pfp2
  • uvcopy jobs are generated here
dat1
  • original mainframe datafile copied here [from mag tape]
dat1a
  • optional, used for data files that need to be split due to:
    record types, occurs depending on, etc
dat2
  • datafiles converted to ASCII '|' delimited format
dat3
  • datafiles converted by 'mkisofs', ready to be written to CD
    by the 'cdrecord' utility.
 11a. export UVDATA1=dat1        - export directory path for input file
 11b. export UVDATA1=dat2        - export directory path for output file
                                  (dat2 assuming no record types, split file)

 11c. uvcopy pfp2/citytax        - convert data from $UVDATA1/citytax (dat1)
      ===================          to $UVDATA2/citytax (dat2 in this case)

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 (just specify jobname).

sample output listed by listrec1


 11d. uvcopy listrec1,fili1=dat2/citytax,filo1=tmp/citytax.pipe
      =========================================================
 listrec1 - list text records (100 bytes/line) file=dat2/citytax 1999/11/30_19:30:25 count=|
 rec#:rcsz          10        20        30        40        50        60        70        80        90       10
           0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
 0001:0000 10130140||JOHN HENRY|1815 BOWEN ROAD|VANCOUVER|BC|V9S1H1|1998/08/28|57828|4878|1717.10|1490.61|1997/
      0100 05/30|19:18:09|
      0115 '|'=014
 0002:0000 10139923||GEORGE BROWN|1250 EAST PENDER STREET|VANCOUVER|BC|V5L1W1|1998/08/28|57828|4878|1785.24|-14
      0100 62.61|1998/06/01|19:01:56|
      0126 '|'=014
 0003:0000 10147615||BONNIE SMITH|44430 YALE ROAD WEST|VANCOUVER|BC|V2P6J1|1995/12/13|39623|0|519.14|376.92|199
      0100 5/06/01|20:10:31|
      0117 '|'=014

See improved listing by 'listpipe1' on the next page --->

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

C12. Convert Mainframe files for Loading SQL Databases

#12 Verify '|' delimited output file

Run the 'listpipe1' job to count fields & verify the conversion. listpipe1 will list the data fields vertically with copybook names, and sequence numbers to count fields.


 12a. uvcopy listpipe1,fili1=dat2/citytax,fili2=maps/citytax,filo1=tmp/citytax
      ========================================================================

sample output from listpipe1

 listpipe1 - list '|' delimited files with copybook fieldnames
           - to verify files created by genpipe1 (see SQLcnvrt.doc)
 datafile=dat1/payrec1  copybookmap=maps/payrec1  datetime=1999/11/30_18:40:24
 SEQ# FIELDNAME              COBOL PICTURE      DATA

datafile = dat2/citytax, record# = 1, field count = 14

 10130140||JOHN HENRY|1815 BOWEN ROAD|VANCOUVER|BC|V9S1H1|1998/08/28|57828|4878|1
 717.10|1490.61|1997/05/30|19:18:09|
 001 folio                   x(8).              10130140
 002 section                 x(2).
 003 owner                   x(25).             JOHN HENRY
 004 address                 x(25).             1815 BOWEN ROAD
 005 city                    x(20).             VANCOUVER
 006 state                   x(2).              BC
 007 zip                     9(6).              V9S1H1
 008 post-date               9(6) comp-3.       1998/08/28
 009 land-value              s9(9) comp-3.      57828
 010 imprv-value             s9(9) comp-3.      4878
 011 face-value              s9(7)v99 comp-3.   1717.10
 012 maint-tax-due           s9(7)v99.          1490.61
 013 maint-date              x(6).              1997/05/30
 014 maint-time              x(6).              19:18:09

 12b. uvhdcob dat1/citytax maps/citytax a
      ===================================
      --> i3f1    - printout 1st 3 records with COBOL fieldnames
                    for comparison to the | delimited fields above
                  - 1st check last field sequence#s for match
NOTE
  • this is a repeat of the uvhdcob 1st run in step #06
    (in case you did not make a listing then)

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

C13. Convert Mainframe files for Loading SQL Databases

#13 Write delimited file to CD for transport to SQL Server

The software to write data to CD on UNIX systems is shareware & was down- loaded from Jorg Schilling's web site:

 http://www.fokus.gmd.de/research/glone/employees/joerg.schilling/
Also see: http://www.sco.com/skunkware/cdrecord/SCO-NOTES.html
cdrecord
  • software to write CD, copyright by Jorg Schilling
    schilling@fokus.gmd.de
mkisofs
  • software to prepare data file for cdrecord, by eric Youngdale
    ericy@gnu.ai.mit.edu, copyright Yggdrasil Computing Inc.

 13a. mkisofs -L -l -v -r -o dat3/citytax dat2/citytax
      ================================================
                               - prepare data file for writing to CD
 13b. su root                  - must be root to run cdrecord

 13c. cdrecord -v dev=1,6,0 dat3/citytax
      ==================================
                               - write data to CD

 13d. mount -r /dev/cd1 /mnt   - mount the CD to check output file
      ======================

 13e. uvhd /mnt/citytax tr800  - check file with uvhd
      =======================     (file often too big for vi)

 13f. unmount /dev/cd1         - unmount the CD
      ================

13g. exit (from root)

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

C14. Convert Mainframe files for Loading SQL Databases

#14 Generate control file to load SQL database


 14. uvcopy sqlload1,fili1=maps/citytax,filo1=sqls/citytax.ctl
     =========================================================
         - generate the control file to load a Microsoft SQL Server database

sample SQL loader control file

 -- citytax.ctl - control file for SQL*LOADER 1999/11/30_18:37:45
 --              - generated by sqlload1 (see SQLcnvrt.doc)
 -- LOAD DATA STREAM FIELDS TERMINATED by '|'
 CREATE TABLE [citytax] (
 [folio]             [char]    (08) NULL ,  --#001 x(8).
 [section]           [char]    (02) NULL ,  --#002 x(2).
 [owner]             [varchar] (25) NULL ,  --#003 x(25).
 [address]           [varchar] (25) NULL ,  --#004 x(25).
 [city]              [varchar] (20) NULL ,  --#005 x(20).
 [state]             [char]    (02) NULL ,  --#006 x(2).
 [zip]               [integer]      NULL ,  --#007 9(6).
 [post_date]         [datetime]     NULL ,  --#008 9(6) comp-3.
 [land_value]        [integer]      NULL ,  --#009 s9(9) comp-3.
 [imprv_value]       [integer]      NULL ,  --#010 s9(9) comp-3.
 [face_value]        [float]        NULL ,  --#011 s9(7)v99 comp-3.
 [maint_tax_due]     [float]        NULL ,  --#012 s9(7)v99.
 [maint_date]        [datetime]     NULL ,  --#013 x(6).
 [maint_time]        [char]    (06) NULL ,  --#014 x(6).
 )

Verify that this is correct by comparing to the 'listpipe1' or 'uvhdcob' listings shown 2 pages back.

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

C15. Convert Mainframe files for Loading SQL Databases

Documents Stored for each file converted

For each file converted, I suggest you create the following listings & store in a 3 ring binder with an index tab divider for each file.

Copybooks & Conversion Control files

a1. COBOL copybook BEFORE optimization (from subdir cpy2)

 a2. Copybook AFTER optimization for conversion to Relational DataBases
     - from subdir cpys
 a3. uvcopy data conversion job (from subdir pfp2)
     - generated from COBOL copybook
     - translates EBCDIC data to ASCII pipe delimited data
     - special editing for numeric signed fields & date fields
     - instructions run under control of the uvcopy interpreter
 a4. SQL Loader control file (from subdir sqls), generated from COBOL copybook
     - returned on MSDOS diskette (includes files A2, A3,& A4).

Data Dumps

 b1. uvhd data dump of 1st INPUT record (from files in subdir dat1)
     - record dump in vertical hexadecimal
     - 64 byte segments on 3 lines (characters, zones, digits)
 b2. uvhdcob data dump of 1st INPUT record (from files in subdir dat1)
     - lists data fields with COBOL copybook fieldnames (1 field per line)
 b3. listpipe1 data dump of 1st OUTPUT record (from files in subdir dat2)
     - lists data fields with COBOL copybook fieldnames (1 field per line)

Purpose of these documents

  1. Verifying the accuracy of the conversion, you can compare the input data to the output data field by field.

  2. Useful for reruns of same data files & for creating future conversion jobs that are similar.

  3. History of conversions performed. Useful for answering questions that may come up in future.

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

C15a. SQLjobs - Convert Mainframe files for Loading SQL Databases

Create the following printouts & file in a 3-ring binder with tabs to identify the various jobs as they occur. Do this in case: rerun required, future repeat runs with same data layouts, resolve finger pointing, & good business practices. You may need to make a 2nd copy of some of these to accompany the CD going to an outside agency.

Copybook (maps) & Conversion control files


 a1. uvlp12 map2/citytax    - print 'cobmap' BEFORE optimization
     ===================

 a2. uvlp12 maps/citytax    - print 'cobmap' AFTER optimization
     ===================
      always print the 'cobmap' NOT the copybook.
      - the cobmap includes start,end,length bytes & type on right
      - genpipeB will have left the cobmaps in subdir maps vs cpys
      - may have had to create multiple copybooks if original file contained
        record types, or occurs depending on. In this case the multiple
        copybooks would have been named citytax1, citytax2, citytax3, etc.

 a3. uvlp12 pfp2/citytax      - list generated job to convert to | delimited
      ===================
      list the uvcopy job generated by genpipeB to convert the data to the
      desired output '|' delimited file.
      - if file had to be split to multiple files due to record types, etc
        these jobs would be named citytax1, citytax2, citytax3, etc

 a4. uvlp12 sqls/citytax.ctl  - list SQL loader control file
      =======================
      list Sql loader control file to accompany CD going to outside agency
      - also provide on diskette or Email, etc

 a5. uvlp12 pfs/citytax0      - list any special uvcopy job required
      ===================
      list any special uvcopy job required to split files with multiple record
      types, occurs depending on, or variable length block/record prefixes.

DATA dumps


 b1. uvhd dat1/citytax r500      - print uvhd dump of 1st record or 2 in file
      ======================
      uvhd dump of 1st record in file (dat1 holds the INPUT files)
      - may need to investigate data to determine rcsz (else use r256)
      - used to verify ASCII or EBCDIC, variable length block/record prefixes

 b2. uvhdcob dat1/citytax maps/citytax [a]  - print uvhdcob 1st 1 or 2 records
      =====================================
      uvhdcob listing of 1st record in the file
      - requires accurate copybook matching the data
      - use option 'a' if the data file is EBCDIC

 b3. uvcopy listpipe1,fili1=dat2/citytax,fili2=maps/citytax,filo1=tmp/citytax
      ========================================================================
      list 1st '|' delimited OUTPUT record, should match uvhdcob above

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

C16. Convert Mainframe files for Loading SQL Databases

Create diskette with conversion control files


 mcopysubs      <-- script copies relevant subdirs to diskette
 =========
cpy2
  • copybooks BEFORE optimization
cpys
  • copybooks AFTER optimization
map2
  • cobmaps BEFORE optimization
maps
  • cobmaps AFTER optimization
ctl
  • control file (relates data filename to copybook/cobmap name)
pfp2
  • uvcopy data conversion parameter files
  • executed via uvcopy interpreter
sqls
  • SQL Loader control files

Script 'mcopysubs' creates the above subdirs on the diskette & copies all files from the similarly named subdirs within the current directory.

Note that the subdirs are necessary because the filenames are the same in many of the subdirs listed above. The name is usually the same as the data file name chosen for each conversion (taxdata for example).

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

C17. Convert Mainframe files for Loading SQL Databases

#16 Loading delimited data on SQL server

This will probably be done with a GUI interface, but it would be equivalent to the following command (which applies to Oracle). The input would be from the CD written on the UNIX system.


 17. sqlload CONTROL=sql\citytax DATA=E:\citytax
     ===========================================

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

D1. Convert Mainframe files for Loading SQL Databases

Concise Op. Instrns. for citytax demo

  1. Create the COBOL copybook (record layout)


 01a. vi cpys/citytax           - create copybook from documents supplied
      ===============
 01b. uvcopy cobmap1,fili1=cpys/citytax,filo1=maps/citytax  - convert to map
 01c. uvlp12 maps/citytax       - print copybook 'map' (record layout)
  1. Loading data from mainframe tape for conversion on UNIX


 02a. dd if=/dev/rmt/ntape2 of=dat1/citytax bs=32000
      ==============================================
      - not required for citytax demo, file copied from /home/uvadm/dat1
  1. Print 1st few blocks to determine rcsz & any complexities


 03a. uvhd dat1/citytax r256a   - investigate data file
      =======================
  1. Create separate COPY-BOOKS for split files, NOT REQUIRED for citytax demo


 04a. vi cpys/citytax           - split copybook to redefined records
      ===============
  1. Write special uvcopy jobs to split file, NOT REQUIRED for citytax demo 05a. vi pfs/citytax0 - create uvcopy job to split file NOT Required 05b. uvcopy pfs/citytax0 - run job to split file NOT REQUIRED here

  2. Verify data matches copybook record layout, using uvhdcob


 06a. uvhdcob dat1/citytax maps/citytax a  - verify data matches copybook ?
      ===================================
 06b. uvlp12 maps/citytax                  - print for documentation
  1. Modify COBOL copybook as required to improve database loading


 07a. vi cpys/citytax           - modify copybook for database load
      ===============
  1. Generate translate EBCDIC to ASCII & preserve packed/signed fields

 08a. vi ctl/ctlfile1           - add new datafile/copybook to control file
 08b. gencnvB citytax citytax   - run script to generate translate job
      =======================
  1. Generate job to convert data to '|' delimited


 09b. genpipeB citytax citytax  - run script to generate '|' delimit job
      ========================

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

D2. Convert Mainframe files for Loading SQL Databases

Concise Op. Instrns. for citytax demo (continued)

  1. Modify job to convert data to '|' delimited


 10a. vi pfp2/citytax           - edit job to convert data to '|' delimited
      ===============
      :r pfx2/citytax           - insert instructions to preserve packed/signed
  1. Execute job to convert data to '|' delimited

 11a. export UVDATA1=dat1       - export directory path for input file
 11b. export UVDATA2=dat2       - export directory path for output file

 11c. uvcopy pfp2/citytax       - $UVDATA1/citytax to $UVDATA2/citytax
      ===================
  1. Verify '|' delimited output file


 12a. uvcopy listpipe1,fili1=dat2/citytax,fili2=maps/citytax,filo1=tmp/citytax
      ========================================================================
  1. Write delimited file to CD for transport to SQL Server


 13a. mkisofs -L -l -v -r -o dat3/citytax dat2/citytax  - prepare write CD
      ================================================
 13b. su root                   - must be root to run cdrecord

 13c. cdrecord -v dev=1,6,0 dat3/citytax    - write data to CD
      ==================================

13d. exit (from root)

  1. Generate control file to load SQL database


 14a. uvcopy sqlload1,fili1=maps/citytax,filo1=sqls/citytax.ctl
      =========================================================
  1. create printouts for documentation & file in 3-ring binder - in case of reruns, repeat runs, finger pointing, good practices - 2nd copy to send with CD if going to outside agency - copybookmaps, uvhd/uvhdcob 1st few input records, listpipe1 1st few output records, SQL loader control file, uvcopy conversion job created by genpipeB, any special uvcopy jobs for complex files.

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

D3. Convert Mainframe files for Loading SQL Databases

Op. Instrns. for Repeat conversions

The operating instructions for repeat conversions are much simpler, because we will execute the already generated conversion jobs. Of course we should use uvhd &/or uvhdcob to confirm that the current file data matches the expected layout.

Note that files with multiple record types, variable length, or occurs depending on, require extra steps before the conversion to '|' delimited. We will assume this is NOT Required here & see next page if required.

  1. Loading data from mainframe tape for conversion on UNIX


 01a. dd if=/dev/rmt/ntape2 of=dat1/citytax bs=32000
      ==============================================
  1. Verify data matches expected layout (documented on initial conversion)


 02a. uvhd dat1/citytax r256a   - investigate data file
      =======================

 02b. uvhdcob dat1/citytax maps/citytax a  - verify data matches copybook ?
      ===================================
  1. For multi record types, variable length,& occurs depending on, - run special job to split into separate files - steps 04-07 would then be repeated for each separate file


      uvcopy pfs/citytax.split     - NOT REQUIRED for citytax demo file
      ========================
  1. Execute job to convert data to '|' delimited

 04a. export UVDATA1=dat1     - export directory path for input file
 04b. export UVDATA2=dat2     - export directory path for output file

 04c. uvcopy pfp2/citytax     - convert $UVDATA1/citytax to $UVDATA2/citytax
      ===================
  1. Verify '|' delimited output file (list 1st 3 records with fieldnames)


 05a. uvcopy listpipe1,fili1=dat2/citytax,fili2=maps/citytax
      ======================================================
  1. Write delimited file to CD for transport to SQL Server


 06a. mkisofs -L -l -v -r -o dat3/citytax dat2/citytax  - prepare file for CD
      ================================================
 07b. su root                   - must be root to run cdrecord

 07c. cdrecord -v dev=1,6,0 dat3/citytax    - write data to CD
      ==================================
 07d. exit  (from root)

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

D4. Convert Mainframe files for Loading SQL Databases

Op. Instrns. for Repeat conversions with Multiple Record Types

For example, let's assume that the citytax file had multiple record types requiring a special job to split the file before converting to '|' delimited. We will assume we have already created the split & multiple conversion jobs. First lets review our directories:

dat1
  • input data from mag tape
dat1a
  • used only when input needs to be split to multiple files
    (otherwise we convert from dat1 directly to dat2)
dat2
  • output of conversion to '|' delimited
dat3
  • file prepared by mkisofs for writing to CDROM by cdrecord
 --------------- 1st 2 steps same as on previous page ------------------
  1. Split multi record types (assuming base record & legal descriptions) - citytax.split can define I/O files within itself, but for clarity we will define them on the command line as follows:


 03a. uvcopy pfs/citytax.split,fili1=dat1/citytax,filo1=dat1a/citytax.base
      ====================================================================
                                                 ,filo2=dat1a/citytax.legal
      --- now run steps 4-7 for base file & again for legal descriptions ---
  1. Execute job to convert data to '|' delimited 04a. export UVDATA1=dat1a - directory for input file dat1a (not dat1) 04b. export UVDATA2=dat2 - directory for output file dat2 04c. uvcopy pfp2/citytax.base - dat1a/citytax.base to dat2/citytax.base ========================

  2. Verify '|' delimited output file (list 1st 3 records with fieldnames) 05a. uvcopy listpipe1,fili1=dat2/citytax.base,fili2=maps/citytax.base ================================================================

  3. Write delimited file to CD for transport to SQL Server 06a. mkisofs -L -l -v -r -o dat3/citytax.base dat2/citytax.base ==========================================================

 07b. su root                   - must be root to run cdrecord
 07c. cdrecord -v dev=1,6,0 dat3/citytax.base  - write data to CD
      =======================================
 07d. exit  (from root)
   --- now repeat steps 4-7 for legal descriptions (change .base to .legal) ---

These Op. Instrns. are intended to illustrate some conventions you might use for processing files with multiple record types. Please see the next section for generating the multiple jobs required.

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

E1. Convert Mainframe files for Loading SQL Databases

passtax - sample file to demo Record Types

The main intention here is to help you to write the special uvcopy jobs required to split multi-record-type files to separate files (required for loading relational databases).

Only the most pertinent steps of this process are documented here. Please see other steps documented in sections 'C' & 'D' for the citytax example.

  1. Print 1st few blocks to determine rcsz & any complexities


 03a. uvhd dat1/passtax r256a   - investigate data file
      =======================
      Note the 3 different record types (taxmaster,name/address,legal description).
  1. Create separate COPY-BOOKS for split files.


 04a. vi cpys/passtax           - split copybook to redefined records
      ===============
      :w cpys/passtax1, cpys/passtax2, cpys/passtax3
  1. Write special uvcopy job to split file.


 05a. vi pfs/passtax0       - create special uvcopy job to split data file
      ===============
                            - execute the special job as follows:

 05b. uvcopy pfs/passtax0,fili1=dat1/passtax,filo1=dat1a/passtax1
      ===========================================================
                         ,filo2=dat1a/passtax2,filo3=dat1a/passtax3
                         ==========================================

Note that steps 6-15 would then be performed multiple times for each of the split files (passtax1, passtax2, passtax3), and that the conversion of these split files will go from dat1a to dat2, instead of from dat1 to dat2 (when no splits have to be done).

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

E2. Convert Mainframe files for Loading SQL Databases

Copybook maps to demo Multi-Record-Type file

 cobmap1  start-end bytes for cobol record fields    199912011514  pg# 0001
 new/ptcpy                      tax-master         RCSZ=0225  bgn-end lth typ
 * passtax1 - taxmaster record - 1st of 3 types (r/t cols 10-
  01  tax-master.
      10 parcel-no                 pic x(09).                 000-0008 009
      10 rec-type                  pic x(02).                 009-0010 002
 * rec-type 01=taxmaster, 02=name & adrs, 03=legal description
      10 paid                      pic x(01).                 011-0011 001
      10 filler001                 pic x(02).                 012-0013 002
      10 section                   pic x(02).                 014-0015 002
      10 township                  pic x(02).                 016-0017 002
      10 range                     pic x(02).                 018-0019 002
      10 subdivision               pic x(04).                 020-0023 004
 *    --------------- etc (many fields omitted) -------------
      10 filler002                 pic x(201).                042-0224 201
 *
 *
 * passtax2 - name & address record - 2nd of 3 types (r/t col
  01  tax-nameadrs redefines tax-master.
      10 parcel-no                 pic x(09).                 000-0008 009
      10 rec-type                  pic x(02).                 009-0010 002
 * rec-type 01=taxmaster, 02=name & adrs, 03=legal description
      10 name1                     pic x(30).                 011-0040 030
      10 name2                     pic x(30).                 041-0070 030
      10 address1                  pic x(30).                 071-0100 030
      10 address2                  pic x(30).                 101-0130 030
      10 city-state-zip            pic x(30).                 131-0160 030
      10 filler003                 pic x(64).                 161-0224 064
 *
 *
 * passtax3 - legal descriptions - 3rd of 3 types (r/t cols 1
  01  tax-legal redefines tax-master.
      10 parcel-no                 pic x(09).                 000-0008 009
      10 rec-type                  pic x(02).                 009-0010 002
 * rec-type 01=taxmaster, 02=name & adrs, 03=legal description
      10 legal1                    pic x(30).                 011-0040 030
      10 legal1                    pic x(30).                 041-0070 030
      10 legal1                    pic x(30).                 071-0100 030
      10 legal1                    pic x(30).                 101-0130 030
      10 filler004                 pic x(94).                 131-0224 094
 *RCSZ=0225                                                       0225

The original copybook would be split into 3 & named as shown above: passtax1, passtax2,& passtax3.

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

E3. Convert Mainframe files for Loading SQL Databases

data file to demo multi record type files


 uvhd dat1/passtax r225a       - investigate data file to see Record Types
 =======================         R/T in bytes 9-10 zero relative
 r#       1           1         2         3         4         5         6
 b#       0 0123456789012345678901234567890123456789012345678901234567890123
          0 100001030010  1923220000002000010 051000         1000000000  000
            FFFFFFFFFFFF44FFFFFFFFFFFFFFFFFFF4FFFFFF444444444FFFFFFFFFF44FFF
            1000010300100019232200000020000100051000000000000100000000000000
         64 0000000050000000000000000000000000000000000000000000000000000000
            FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
            0000000050000000000000000000000000000000000000000000000000000000
        128 0000002429900002429900000000000000000005400000046165000000000
            FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF444
            0000002429900002429900000000000000000005400000046165000000000000
        192                  00000
            44444444444444444FFFFF44444444444
            000000000000000000000000000000000
 r#       2           1         2         3         4         5         6
 b#     225 0123456789012345678901234567890123456789012345678901234567890123
          0 10000103002BUCHANAN GAYLE &              BUCHANAN JAMES
            FFFFFFFFFFFCECCCDCD4CCEDC4544444444444444CECCCDCD4DCDCE444444444
            1000010300224381515071835000000000000000024381515011452000000000
         64        PO BOX 174
            4444444DD4CDE4FFF44444444444444444444444444444444444444444444444
            0000000760267017400000000000000000000000000000000000000000000000
        128    SEATTLE                WA 83537-0174
            444ECCEEDCCC44444444444444EC4FFFFF6FFFF4444444444444444444444444
            0002513335550000000000000061083537001740000000000000000000000000
        192
            444444444444444444444444444444444
            000000000000000000000000000000000
 r#       3           1         2         3         4         5         6
 b#     450 0123456789012345678901234567890123456789012345678901234567890123
          0 10000103003S 1/2 OF SW 1/4 OF NW 1/4 OF  SW 1/4
            FFFFFFFFFFFE4F6F4DC4EE4F6F4DC4DE4F6F4DC44EE4F6F44444444444444444
            1000010300320112066026011406605601140660026011400000000000000000
         64        OR 930 PG 1424                OR 3864 PG 789
            4444444DD4FFF4DC4FFFF4444444444444444DD4FFFF4DC4FFF4444444444444
            0000000690930077014240000000000000000690386407707890000000000000
        128
            4444444444444444444444444444444444444444444444444444444444444444
            0000000000000000000000000000000000000000000000000000000000000000
        192
            444444444444444444444444444444444
            000000000000000000000000000000000

Note record types in bytes 9-10: 01=Taxmaster, 02=Name&Address, 03-10=Legal Descriptions

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

E4. Convert Mainframe files for Loading SQL Databases

sample uvcopy job to split Multi-Record-Type file

 # passtax0 - split pass tax file into 3 (taxmaster, name&adrs, legals)
 #
 fili1=dat1/passtax,typ=RSF,rcs=225
 filo1=dat1a/passtax1,typ=RSF,rcs=225
 filo2=dat1a/passtax2,typ=RSF,rcs=225
 filo3=dat1a/passtax3,typ=RSF,rcs=225
 @run
        opn    all
 #
 # begin loop to get records, test R/T, write records until EOF
 man20  get    fili1,a0
        skp>   eof
 #
 # since no packed, we can translate to ASCII now instead of later
        tra    a0(225)              translate to ASCII
 #
 # test R/T & write to 1 of 3 files
        cmc    a9(2),'01'                taxmaster ?
        skp=   man30
        cmc    a9(2),'02'                name & address ?
        skp=   man40
        tstl2  a9(2),'0304050607080910'  legal discrepant ?
        skp=   man50
        msg    a0(78)                show record in err
        msgw   'record type cols 10-11 not 01-09 ? enter to bypass ?'
        skp    man20
 #
 # R/T 01 taxmaster
 man30  put    filo1,a0
        skp    man20
 #
 # R/T 02 Name & Address
 man40  put    filo2,a0
        skp    man20
 #
 # R/T 03-10 Legal descriptions
 # note - letter said 03-09, but I see R/T 10 legal descriptions ??
 man50  put    filo3,a0
        skp    man20
 #
 # EOF - close files & end job
 eof    cls    all
        eoj

See detailed documentation for the uvcopy utility in volume 2 of UVdoc.

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

F1. Convert Mainframe files for Loading SQL Databases

hightax - demo OCCURS DEPENDING ON & VARIABLE LENGTH

The main intention here is to help you to write the special uvcopy jobs required to split multi-record-type files to separate files (required for loading relational databases).

Only the most pertinent steps of this process are documented here. Please see other steps documented in sections 'C' & 'D' for the citytax example.

  1. Print 1st few blocks to determine rcsz & any complexities


 03a. uvhd dat1/hightax r256a   - investigate data file
      =======================
      See the uvhd vertical hexadecimal dump on page 'F3' --->
      Note - variable length block/record prefixes in 1st 8 bytes
           - base/fixed record portion is 476 bytes
           - Legal Description records follow 'occurs depending on'
           - last 2 bytes of fixed portion specifies # of LD's
  1. Create separate COPY-BOOKS for split files.


 04a. vi cpys/hightax           - split copybook to:
      ===============
      :w cpys/hightax1          - base/fixed portion 476 bytes
      :w cpys/hightax2          - legal descriptions 61 bytes (52 + 9 key)
  1. Write special uvcopy job to split file.


 05a. vi pfs/hightax0       - create special uvcopy job to split data file
      ===============
                            - execute the special job as follows:

 05b. uvcopy pfs/hightax0,fili1=dat1/hightax,filo1=dat1a/hightax1
      ===========================================================
                                            ,filo2=dat1a/hightax2
                                            =====================

Note that steps 6-15 would then be performed twice, once for each of the split files (hightax1 & hightax2), and that the conversion of these split files will go from dat1a to dat2, instead of from dat1 to dat2.

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

F2. Convert Mainframe files for Loading SQL Databases

Copybook maps to demo Multi-Record-Type file

 * hightax  - Highland tax master.
 * must split to 2 files due to ld occurs depending on.
 * hightax1 - fixed portion 476 bytes.
 * hightax2 - legal descriptions 50 bytes + 13 key = 61 bytes
  01 tax-mast.
     05 mst-key.
        10 f-num             pic 9(6)v9(4)        comp-3.     000-0005 006pn  10
        10 tax-yr            pic 99.                          006-0007 002 n  02
        10 ctc               pic x.                           008-0008 001
     05 sec-twp-rge          pic 9(6)             comp-3.     009-0012 004pn  06
     05 owner                pic x(50).                       013-0062 050
     05 addr-1               pic x(25).                       063-0087 025
     05 addr-2               pic x(25).                       088-0112 025
           - - - - - - - - - - etc - - - - - - - - -
     05 filler001            pic x(19).                       455-0473 019
     05 num-of-ld            pic 999              comp-3.     474-0475 002pn  03
     05 legal-descr occurs 350 depending on num-of-ld.
        10 ll.
           15 legal-line-num pic 999              comp-3.     476-0477 002pn  03
           15 legal-line     pic x(50).                       478-0527 050
 *RCSZ=8676                                                       8676

We would use the original copybook above (hightax) to create 2 new copybooks (hightax1 & hightax2). 'hightax1' will be the tax master fixed portion which is 476 bytes, 'hightax2' will be the separated legal description records which will be 61 bytes each. Note that the special uvcopy job will prefix each legal description record with the 9 byte record key, to link the 2 records in the database.

 * hightax2  - Highland legal descriptions
 * must split to 2 files due to ld occurs depending on.
 * hightax1 - fixed portion 476 bytes.
 * hightax2 - legal descriptions 52 bytes + 9 key = 61 bytes
  01 tax-legal.
     05 mst-key.
        10 f-num             pic 9(6)v9(4)        comp-3.     000-0005 006pn  10
        10 tax-yr            pic 99.                          006-0007 002 n  02
        10 ctc               pic x.                           008-0008 001
 * legal descriptions only in this file (hightax2)
 *   05 legal-descr occurs 350 depending on num-of-ld.
        10 ll.
           15 legal-line-num pic 999              comp-3.     009-0010 002pn  03
           15 legal-line     pic x(50).                       011-0060 050
 *RCSZ=0061                                                       0061

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

F3. Convert Mainframe files for Loading SQL Databases

data file to demo multi record type files


 uvhd dat1/hightax r256a    - investigate data file = EBCDIC variable length
 =======================    - option 'a' translates char lines to ASCII
            bb00rr00<-- note blk/rec lth prefixes 4 bytes each = 8 bytes
 r#       1           1         2         3         4         5         6
 b#       0 0123456789012345678901234567890123456789012345678901234567890123
          0 F8 ..@........96I..q"KNIGHT JANET ESTATE OF
            4F200700000920FFC0177DDCCCE4DCDCE4CEECEC4DC444444444444444444444
            68002C0000000F969021F2596830115530523135066000000000000000000000
         64        3601 W JETTON AVE                                 SEATTLE
            4444444FFFF4E4DCEEDD4CEC444444444444444444444444444444444ECCEEDC
            0000000360106015336501550000000000000000000000000000000002513335
        128  WA               .b. ...97 00050055..Qb.1-D  ...        ...N
            4EC4444444444444443694000FF4FFFFFFFF03562F6C4400044444444033D444
            06100000000000000032F000F970000500554712C1040000C0000000005C5000
        192   030............Wb....g. ..... ... ...0000 ... ... .....00001 .
            44FFF0000000000005880008840000040004000FFFF40004000400000FFFFF40
            000300000C0000C0072C0047C00000C000C000C0000000C000C00000C0000100
 r#       2           1         2         3         4         5         6
 b#     256 0123456789012345678901234567890123456789012345678901234567890123
          0 ...-.00000 ......00000 ......00001 ....p.00000 ..............I..
            00060FFFFF4000000FFFFF4000000FFFFF4000091FFFFF400000000000000400
            0000C00000000000C00000000000C00001000087C00000000000C0000C00196C
         64 R.bi....qq....U@..b*.....e.04......................v............
            D08880007700015700650000262FF00000000100000000000007900000000000
            9029C00111C0055C002C000C35C0400000C087C000C0000C0045C0000C0000C0
        128 ..........k.....l..r<..<..*..*..*..%..%.A@.v..a..g......bi. 9705
            000001000198020093094004015015025026036047072083083000008884FFFF
            000C05C00C2C75C13C19C25C21C27C23C29C25C21C15C11C17C000C029C09705
        192 301918090  0000                   ....KEYSTONE PARK COLONY
            FFFFFFFFF44FFFF44444444444444444440301DCEEEDDC4DCDD4CDDDDE444444
            30191809000000000000000000000000000F0F25823655071920363658000000
                                               |<-- end base, start legals
 r#       3           1         2         3         4         5         6
 b#     512 0123456789012345678901234567890123456789012345678901234567890123
          0                         ..TRACT 2 IN NE 1/4 LESS S 210 FT OF W 2
            44444444444444444444444402EDCCE4F4CD4DC4F6F4DCEE4E4FFF4CE4DC4E4F
            0000000000000000000000000F39133020950550114035220202100630660602
         64 25 FT AND   ..LESS N 322.24 FT AND LESS W 15 FT FOR RD R/W
            FF4CE4CDC44403DCEE4D4FFF4FF4CE4CDC4DCEE4E4FF4CE4CDD4DC4D6E444444
            2506301540000F3522050322B240630154035220601506306690940916000000
        128 .@........97I..q"KNIGHT JANET ESTATE OF
            0700000920FFC0177DDCCCE4DCDCE4CEECEC4DC4444444444444444444444444
            2C0000000F979021F25968301155305231350660000000000000000000000000
            |<-- start rec #2 x'027C' binary rcsz = 636 (2*512+7*16+1*12)

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

F4. Convert Mainframe files for Loading SQL Databases

sample uvcopy job to split Multi-Record-Type file

 # hightax0 - split highland taxmaster file variable lth EBCDIC file
 #            to 2 files (base master + variable# of Legal descriptions)
 #
 # This job removes the blk/rec prefixes & writes 2 files
 #   1 - fixed length taxmaster base record 476 bytes
 #   2 - variable no of legal description records 61 bytes each
 # Output files still EBCDIC & packed (translate & unpack in following jobs)
 #
 opr='$jobname - convert EBCDIC var lth file to 2 fixed files'
 was=a65000b32000u32000v32000
 fili1=?dat1/hightax,rcs=32000,typ=RSF
 filo1=?dat1a/hightax1,rcs=476,typ=RSF      #<-- base records
 filo2=?dat1a/hightax2,rcs=61,typ=RSF       #<-- legal description records
 # LD lth 61 = folio#(6p), year(2), ctc(1), ld#(2p), legal description(50)
 @run
        opn    all                   open files
 # store out rcsz in rgstr 'r' & 's' (ease changes for other clients)
        mvn    $rr,476               out rcsz base                    #<--
        mvn    $rs,52                out rcsz legal descriptions          #<--
 # $rr minimizes the no of changes thruout job, but some required
 #
 # begin loop to process each record, converting var lth to fixed lth
 # will write 2 files: base data rec ($rr size) & variable LD recs
 # - getv subrtn points rgstr 'a' to next rec & loads lth in rgstr 'b'
 # - rgstr a points to rec prefix & data is 4 bytes higher
 man20  bal    getv                   get next record from varlth file
        skp>   eof                    cc set > at EOF
        mvc    b0($rr4000),aa4        move data to output area
        put    filo1,b0               write out rec lth spcfd by oprtr
 #
 # write legal description records to 2nd file
 # - variable# depending on LD count in last 2 bytes of record
 man30  mvn    $rh,0                  clr ctr
        mvn    $ri,$ra                point rgstr i to current rec base
        add    $ri,$rr                +base rcsz
        add    $ri,4                  +base+4 points to 1st ld record
 #
 # begin loop to write legal descriptions until count reached (could be 0)
 man32  add    $rh,1                  count ld out recs
        cmn    $rh,b474(2p)           reached ld count ?              #<--
        skp>   man20
        mvc    d0(9),b0               key (folio+yr+ctc)              #<--
        mvc    d9($rs100),ai0         current ld record               #<--
        put    filo2,d0               output to file #2
        add    $ri,$rs                up index rgstr to next ld
        skp    man32                  return to test next
 #
 # EOF - close files & end job
 eof    cls    all                    close files
        eoj

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

 #------------------------------------------------------------------------
 # getv - subrtn to get records from IBM std variable length file
 #      - sets rgstr 'a' to displacement of next record in area 'a'
 #        (points to 4 byte rec lth prefix, data is 4 bytes higher)
 #      - sets rgstr 'b' to length of next record
 #      - return with cc > at EOF
 #
 # IBM std variable lth format has 4 byte block & 4 byte record prefixes
 # - blk lth & rec lth in 1st 2 bytes of 4 with last 2 x'0000'
 # - block & record lengths include the length of prefixes & data
 # - blk/rec lth in binary big end format, need 's'witch option for INTEL
 #
 # 1st add prior record length (in rgstr b) to current displacement (rg a)
 # & test for next block read required, when rgstr a => rgstr c (block lth)
 # - this logic works for next block & also at begin job (all rgstrs 0)
 getv   add    $ra,$rb              add prior reclth to prior rec dsp
        cmn    $ra,$rc              reached end of block ?
        skp<   getv4
 #
 # read 4 byte block prefix to get block lth to be read next
 getv2  get    fili1,a0(4)          read 4 byte block lth prefix
        skp>   getv9
        add    $ca9,1               count blocks (in case errmsg)
        mvn    $rc,a0(2bs)          store blk lth (switch ends INTEL)
        cmc    a2(2),x'0000'        verify blk lth prefix ?
        skp!   getve1
        sub    $rc,4                -4 to allow for blk prefix
        skp<=  getve1
 #
 # now read the block (lth now in rgstr c) & reset rgstr a to 0
 getv3  get    fili1,a0($rc64000)   read block (lth in rgstr c)
        skp>   getv9
        mvn    $ra,0                reset rgstr a to 1st rec in new block
 #
 # common point - new block or next rec in current blk
 # - store current reclth in rgstr b & verify rec lth prefix
 getv4  mvn    $rb,aa0(2bs)         store reclth in rgstr b
        cmc    aa2(2),x'0000'       verify reclth prefix
        skp!   getve1
 #
 # exit with cc = for normal, or cc > for EOF
 getv8  ret=
 getv9  ret>
 #
 # error rtn for invalid block or record prefix
 getve1 msgv1  'blk/rec prefix 2&3 not x0000, blk#=$ca9,rgstrs a=$ra,b=$rb,c=$rc'
        hxc    g0(60),a0(30)        convert blk prefix to hex rep for display
        hxc    g100(60),aa0(30)     convert rec prefix to hex rep for display
        msg    g0(60)               display block prefix
        msg    g100(60)             display record prefix
        msgw   '2nd line above = begin block, last line above = current record'
        can    'unrecoverable'
 #----------------------------------------------------------------------------

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

G1. Convert Mainframe files for Loading SQL Databases

unvar1 - strip variable length block & record prefixes

The main intention here is to help you to write the special uvcopy jobs required to process variable length files to fixed record length files & then to '|' delimited files for loading relational databases.

This discussion is a follow-on to the 'hightax' demo file in section 'F' above which illustrates splitting files with 'occurs depending on', which means variable length records.

Files with 'occurs depending on' are often in the standard IBM variable length format with block & record prefixes.

Section 'F' illustrated a special uvcopy job 'hightax0' to strip the block & record prefixes, and split the file into 2 fixed record files (base taxmaster 476 bytes & a variable number of 61 byte legal descriptions).

This section presents a uvcopy job 'unvar1' that strips the block & record prefixes & would work for any IBM standard variable length file, but would have to be followed by another job customized to the occurs depending on.

It is actually easier to combine these 2 functions since the 'getv' subrtn looks after the variable block & record prefixes. This separate job is intended to help you understand the subroutine & how you can plug it in to your custom uvcopy job.

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

G2. Convert Mainframe files for Loading SQL Databases

unvar1 - strip variable length block & record prefixes

 # unvar1 - strip off IBM standard variable length block & record prefixes
 #        - usually on mag tape, but could be on disc or CD
 #
 # This job demos the 'getv' subrtn to strip the block/record prefixes.
 # - you can steal the getv subrtn & include it into your special purpose
 #   uvcopy jobs.
 #
 # IBM std variable lth format has 4 byte block & record prefixes
 # - blk lth & rec lth in 1st 2 bytes of 4 with last 2 x'0000'
 # - block & record lengths include the length of prefixes & data
 # - blk/rec lth in binary big end format, need 's'witch option for INTEL
 #
 # This job strips the prefixes, & writes the data records unchanged
 # - could be used to strip prefixes from any file, but would then need
 #   a following job that understands the data (how to tell record lth).
 # - therefore it is better to combine these 2 functions.
 #
 # For example see the 'hightax' file in section 'F' of SQLjobs.doc
 # - hightax records contain a variable number of 61 byte legal description
 #   records following the tax master 476 byte base. Last 2 bytes of the
 #   fixed base record specify the number of LD records.
 #
 opr='$jobname - strip IBM standard variable length block & record prefixes'
 was=a65000b65000
 fili1=?dat1/hightax,rcs=64000,typ=RSF
 filo1=?dat2/hightax0,rcs=64000,typ=RSF
 @run
        opn    all
 #
 # begin loop to get, strip,& write records until EOF
 # - using subrtn getv which sets rgstr a to next record & rgstr b to lth
 loop   bal    getv                    set rgstrs a & b to next record
        skp>   eof
 #      ---                        <-- could customize here
        put    filo1,aa4($rb64000)     write current record (using a & b)
        skp    loop
 #
 # EOF - close files & end job
 eof    cls    all
        eoj
 #

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

 #------------------------------------------------------------------------
 # getv - subrtn to get records from IBM std variable length file
 #      - sets rgstr 'a' to displacement of next record in area 'a'
 #        (points to 4 byte rec lth prefix, data is 4 bytes higher)
 #      - sets rgstr 'b' to length of next record
 #      - return with cc > at EOF
 #
 # IBM std variable lth format has 4 byte block & record prefixes
 # - blk lth & rec lth in 1st 2 bytes of 4 with last 2 x'0000'
 # - block & record lengths include the length of prefixes & data
 # - blk/rec lth in binary big end format, need 's'witch option for INTEL
 #
 # 1st add prior record length (in rgstr b) to current displacement (rg a)
 # & test for next block read required, when rgstr a => rgstr c (block lth)
 # - this logic works for next block & also at begin job (all rgstrs 0)
 getv   add    $ra,$rb              add prior reclth to prior rec dsp
        cmn    $ra,$rc              reached end of block ?
        skp<   getv4
 #
 # read 4 byte block prefix to get block lth to be read next
 getv2  get    fili1,a0(4)          read 4 byte block lth prefix
        skp>   getv9
        add    $ca9,1               count blocks (in case errmsg)
        mvn    $rc,a0(2bs)          store blk lth (switch ends INTEL)
        cmc    a2(2),x'0000'        verify blk lth prefix ?
        skp!   getve1
        sub    $rc,4                -4 to allow for blk prefix
        skp<=  getve1
 #
 # now read the block (lth now in rgstr c) & reset rgstr a to 0
 getv3  get    fili1,a0($rc64000)   read block (lth in rgstr c)
        skp>   getv9
        mvn    $ra,0                reset rgstr a to 1st rec in new block
 #
 # common point - new block or next rec in current blk
 # - store current reclth in rgstr b & verify rec lth prefix
 getv4  mvn    $rb,aa0(2bs)         store reclth in rgstr b
        cmc    aa2(2),x'0000'       verify reclth prefix
        skp!   getve1
 #
 # exit with cc = for normal, or cc > for EOF
 getv8  ret=
 getv9  ret>
 #
 # error rtn for invalid block or record prefix
 getve1 msgv1  'blk/rec prefix 2&3 not x0000, blk#=$ca9,rgstrs a=$ra,b=$rb,c=$rc'
        hxc    g0(60),a0(30)        convert blk prefix to hex rep for display
        hxc    g100(60),aa0(30)     convert rec prefix to hex rep for display
        msg    g0(60)               display block prefix
        msg    g100(60)             display record prefix
        msgw   '2nd line above = begin block, last line above = current record'
        can    'unrecoverable'
 #----------------------------------------------------------------------------

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

H1. SQLjobs.doc - convert data files to | delimited format to load DataBases

summary of uvcopy jobs used in this section

cobmap1
  • convert COBOL copybooks to 'cobmaps' (record layouts),
    showing field start,end,length,& type on the right hand side.
genpipe1
  • reads a 'cobmap' & generates a uvcopy job to convert the
    corresponding data file to a | delimited file.
genpipe1.sub
  • uvcopy sub-routine called by the jobs generated by genpipe1
    to edit dates as ccyy/mm/dd & times as HH:MM:SS.
 uvdata52     - inserts the correct data-file-name into the uvcopy jobs
               generated by genpipe1, using a control file to relate the
               copy-book-name to the data-file-name.
listpipe1
  • list '|' delimited output file to verify conversion
  • lists data fields with copybook names & field sequence#s
sqlload1
  • generate SQL loader control file for Microsoft SQL server
sqlload2
  • generate SQL loader control file for ORACLE

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

summary of scripts used in this section

genpipeB
  • runs cobmap1, genpipe1,& uvdata52 to create 1 job for 1 data file.
  • The data-file-names in the generated job are corrected, using
    a control file to relate copy-book-name to the data-file-name.
gencnvB
  • runs cobmap1, uvdata51,& uvdata52 to create 1 job for 1 data file.
  • The data-file-names in the generated job are corrected, using
    a control file to relate copy-book-name to the data-file-name.

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/genpipeB        <-- examine genpipe1
 ==========================

 uvlp12 /home/uvadm/sf/genpipeB    <-- print genpipe1
 ==============================

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

Visitor Counters for ThisYear and LastYear