## Calculate exact length of PARSEd field

IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

### Re: Calculate exact length of PARSEd field

Thanks for letting us know.

Perhaps you could post the AWK code, and some instructions on how to run it. Many on z/OS don't know that it is an option for processing things.
BillyBoyo
Global moderator

Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

### Re: Calculate exact length of PARSEd field

BillyBoyo wrote:Thanks for letting us know.

Perhaps you could post the AWK code, and some instructions on how to run it. Many on z/OS don't know that it is an option for processing things.

Sure. Here is a snippet:

Sample input:
<SctiesSttlmCondModStsAdvc>,YES,CTRL
<ReqRef>,NO,CTRL
<Id>,YES,NORMAL
</ReqRef>,NO,CTRL

The AWK script I've finally used is a bit more complicated because it doesn't generate the SQL statements itself, but only a list of variables that are used on a later JCL step to substitute their occurrences in a template, but the idea was to generate the SQL statement automatically calculating the length of the input tags. Here is a snippet that shows how to calculate the XML tag, which is what I wanted:
#!/bin/awk -f
BEGIN {
seqnum=5;  # sequence numbers begin from 5
incr=5;   # increment sequence numbers by 5
}
# Ignore:
#    1. blank lines: !/^\$/, and
#    2. comment lines beginning with ';' : !/^[:space:]*;/
#
!/^(\$|[[:space:]]*;)/ {
# write the SQL INSERT statement generated in output for each input record
# Example: INSERT INTO MyTableName VALUES(5,'<SctiesSttlmCondModStsAdvc>','Y', 'C', 27);
printf("INSERT INTO MyTableName VALUES(%d, '%s', '%s', '%s', %d);", seqnum, \$1, substr(trim(\$2), 1, 1), substr(trim(\$3), 1, 1), length(\$1));

seqnum += incr;
}

Note: The trim() function is not a built-in function in AWK, but it's easy to find an implementation in Internet.

Assuming the AWK script is in /tmp/gensql.awk and the sample input file is /tmp/input.txt, the way we would call the script from a Unix session is the following:
awk -f /tmp/gensql.awk -v FS="," -v OFS="\n" /tmp/input.txt > output.sql

Where:
1) -v FS="," tells AWK to use the comma as the field separator
2) -v OFS="\n" tells AWK to use the newline character as the output field separator
This is the equivalent JCL step to call the AWK script.
//AWK  EXEC PGM=BPXBATCH,REGION=0M,COND=(4,LT)
//STDERR   DD SYSOUT=*
//STDOUT   DD DSN=&&FSQL,DISP=(NEW,PASS),
//            DCB=(LRECL=80,BLKSIZE=800,RECFM=FB),
//            SPACE=(CYL,(30,10),RLSE)...
//STDENV   DD *
_BPXK_JOBLOG=STDERR
AWKPROGRAM=/tmp/gensql.awk
INPUTFILE=/tmp/input.txt
/*
//STDPARM  DD *
SH awk
-f
\${AWKPROGRAM}
-v
FS=","
-v
OFS="\n"
\${INPUTFILE}
/*

I hope this idea helps anybody.
Kind regards.
zost

Posts: 6
Joined: Wed Oct 03, 2007 4:33 pm
Has thanked: 1 time
Been thanked: 0 time

### Re: Calculate exact length of PARSEd field

I didn't find the username/password, but I did. Sorry for being misleading with my Id.
Regards.
mendijur

Posts: 14
Joined: Tue Feb 14, 2012 3:14 am
Has thanked: 2 times
Been thanked: 0 time

### Re: Calculate exact length of PARSEd field

Hi Billy,
Could you plz explain your solution. Specifically, build
gauravfrankly

Posts: 39
Joined: Fri Aug 07, 2015 3:30 pm
Has thanked: 0 time
Been thanked: 0 time

### Re: Calculate exact length of PARSEd field

The BUILD is the least interesting part, because all it is doing is the final calculation (plus the formatting).

It works by appending a sequence of hexadecimal digits at a particular position. After the SQZ with SHIFT=LEFT has removed all the trailing blanks, the number which is now at the position which was previously the start of the sequence tells you the number of bytes which were "squeezed" out. Then the is used as a source item for the calculation (length-of-field minus bytes-squeezed-out is equal to the length of the data).

Take this example, where x represents a blank. Position 10 has the sequence of numbers:

ABCDExxxx0123456789

Now remove the blanks:

ABCDE0123456789

And position 10 contains 4, the number of blanks removed.
BillyBoyo
Global moderator

Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

### Re: Calculate exact length of PARSEd field

Hi Bily,
thanks for your explanation, I just want to understand the use of "Binary Zero". Why we need that.
gauravfrankly

Posts: 39
Joined: Fri Aug 07, 2015 3:30 pm
Has thanked: 0 time
Been thanked: 0 time

### Re: Calculate exact length of PARSEd field

Here was the original explanatory text: "and prefix the first quote by "something" (I chose a binary zero) to avoid confusion from consecutive quotes".

Here's the code:

IFTHEN=(WHEN=INIT,
OVERLAY=(81:81,80,
JFY=(SHIFT=LEFT,
LEAD=C'"',
TRAIL=C'"',
LENGTH=82),
A-BINARY-ZERO,A-QUOTE,C1,C2,C3,A-QUOTE)),

If you look, the TRAIL=C'"' causes the 81,80 field to end in a quote after it is "squeezed". I need to make the next field quoted, so need to start with a C'"' (the value of A-QUOTE). Without something to intervene, there would be consecutive quotes, one from the TRAIL and one I inserted. I don't want that, so I can put anything, except a quote, before the quote I insert. The value I chose was a binary zero, but it could have been anything - except a quote.
BillyBoyo
Global moderator

Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Previous

Return to DFSORT/ICETOOL/ICEGENER