| 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
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).
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
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
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
================================
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:
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
This page is a summary or overview of the procedures involved. See the detailed Operating Instructions on the following pages --->
Goto: Begin this document , End this document , UVSI Home-Page
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
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).
<---- 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
==============================================
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
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
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
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
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
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
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
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
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 |
|
| maps |
|
| ctl |
|
| pfx1 |
|
| pfx2 |
|
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
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
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 |
|
| maps |
|
| ctl |
|
| pfp1 |
|
| pfp2 |
|
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
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
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 --->
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
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
You might review section 'B' above explaining the sub-directories used for generating & executing these jobs.
| pfp2 |
|
| dat1 |
|
| dat1a |
|
| dat2 |
|
| dat3 |
|
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).
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
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
========================================================================
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 |
|
Goto: Begin this document , End this document , UVSI Home-Page
The software to write data to CD on UNIX systems is shareware & was down- loaded from Jorg Schilling's web site:
https://www.fokus.gmd.de/research/glone/employees/joerg.schilling/
Also see: https://www.sco.com/skunkware/cdrecord/SCO-NOTES.html
| cdrecord |
|
| mkisofs |
|
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
14. uvcopy sqlload1,fili1=maps/citytax,filo1=sqls/citytax.ctl
=========================================================
- generate the control file to load a Microsoft SQL Server database
-- 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
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.
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).
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)
Goto: Begin this document , End this document , UVSI Home-Page
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.
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.
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
mcopysubs <-- script copies relevant subdirs to diskette =========
| cpy2 |
|
| cpys |
|
| map2 |
|
| maps |
|
| ctl |
|
| pfp2 |
|
| sqls |
|
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
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
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)
02a. dd if=/dev/rmt/ntape2 of=dat1/citytax bs=32000
==============================================
- not required for citytax demo, file copied from /home/uvadm/dat1
03a. uvhd dat1/citytax r256a - investigate data file
=======================
04a. vi cpys/citytax - split copybook to redefined records
===============
06a. uvhdcob dat1/citytax maps/citytax a - verify data matches copybook ?
===================================
06b. uvlp12 maps/citytax - print for documentation
07a. vi cpys/citytax - modify copybook for database load
===============
08a. vi ctl/ctlfile1 - add new datafile/copybook to control file
08b. gencnvB citytax citytax - run script to generate translate job
=======================
09b. genpipeB citytax citytax - run script to generate '|' delimit job
========================
Goto: Begin this document , End this document , UVSI Home-Page
10a. vi pfp2/citytax - edit job to convert data to '|' delimited
===============
:r pfx2/citytax - insert instructions to preserve packed/signed
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
===================
12a. uvcopy listpipe1,fili1=dat2/citytax,fili2=maps/citytax,filo1=tmp/citytax
========================================================================
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)
14a. uvcopy sqlload1,fili1=maps/citytax,filo1=sqls/citytax.ctl
=========================================================
Goto: Begin this document , End this document , UVSI Home-Page
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.
01a. dd if=/dev/rmt/ntape2 of=dat1/citytax bs=32000
==============================================
02a. uvhd dat1/citytax r256a - investigate data file
=======================
02b. uvhdcob dat1/citytax maps/citytax a - verify data matches copybook ?
===================================
uvcopy pfs/citytax.split - NOT REQUIRED for citytax demo file
========================
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
===================
05a. uvcopy listpipe1,fili1=dat2/citytax,fili2=maps/citytax
======================================================
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
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 |
|
| dat1a |
|
| dat2 |
|
| dat3 |
|
--------------- 1st 2 steps same as on previous page ------------------
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 ---
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
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.
03a. uvhd dat1/passtax r256a - investigate data file
=======================
Note the 3 different record types (taxmaster,name/address,legal description).
04a. vi cpys/passtax - split copybook to redefined records
===============
:w cpys/passtax1, cpys/passtax2, cpys/passtax3
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
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
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
# 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
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.
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
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)
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
* 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
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
# 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
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
# 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
| cobmap1 |
|
| genpipe1 |
|
| genpipe1.sub |
|
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 |
|
| sqlload1 |
|
| sqlload2 |
|
These uvcopy jobs are stored in the /home/uvadm/pf directory & you can examine or print them as follows, using genpipe1 as an example:
vi /home/uvadm/pf/genpipe1 <-- examine genpipe1 ==========================
uvlp12 /home/uvadm/pf/genpipe1 <-- print genpipe1 ==============================
| genpipeB |
|
| gencnvB |
|
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