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

Postby BillyBoyo » Sun Apr 14, 2013 3:35 am

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: 264 times

Re: Re: Calculate exact length of PARSEd field

 

Re: Calculate exact length of PARSEd field

Postby zost » Sat May 11, 2013 1:32 am

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

Postby mendijur » Sat May 11, 2013 2:00 am

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

Postby gauravfrankly » Mon Aug 17, 2015 6:32 pm

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

Postby BillyBoyo » Mon Aug 17, 2015 7:30 pm

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: 264 times

Re: Calculate exact length of PARSEd field

Postby gauravfrankly » Thu Aug 27, 2015 2:24 pm

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

Postby BillyBoyo » Thu Aug 27, 2015 2:44 pm

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: 264 times

Previous

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post