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.
Posted: Sun Apr 14, 2013 3:35 am