Resources

RXSQL: Process SQL file input from a file with LRECL > 72

Topic: Code Place

Subtopic: DB2 for z/OS



This Rexx program frees you from the input LRECL restrictions imposed by SQL processing utilities such as DSNTEP2.  It accepts non-Select SQL statements from an input file of any LRECL less than 32K.  Each input record must contain a complete SQL statement and is passed to DB2 for processing via the EXECUTE IMMEDIATE instruction.

Why do this?  This program arose from my habit of using various tools to generate SQL statements.  For example, I recently used the following DFSORT control statements to generate an SQL deck to delete rows based on an input file.

SORT FIELDS=COPY

 OUTFIL BUILD=(C'DELETE FROM T99XYZ.VABCTCI WHERE',                  

                    C' BRANCH_NUM = ',20,3,PD,EDIT=(TTTTT),                       

                    C' AND ACCOUNT_NUM = ',23,4,PD,EDIT=(TTTTTTT))   

The result was a file containing several hundred records that looked like:

DELETE FROM T99XYZ.VABCSTCI WHERE BRANCH_NUM = 09939 AND ACCOUNT_NUM = 3119500

The above statement will not be accepted by DSNTEP2 because it is 78 bytes long and there is no continuation character.  Since there are hundreds of records, manually reformatting these statements to make DSNTEP2 happy would be a lot of work.

RXSQL has no such requirement; it accepts this statement as written and runs it via EXECUTE IMMEDIATE.  

RXSQL has a dependency on RXSQLCA, an SQL error handler, which is also available in the Code Place.  (Search for "Painlessly call DSNTIAR from Rexx").  

However, the source code for RXSQL is extremely simple, and you can feel free to substitute your own SQL error handler if you wish.

 

 

 



Click Here to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.