I have used a lot of time in my years as a mainframe programmer to make SQL that creates other SQL statements or other kind of input to a variety of different utilities. This process has often involved some kind of manual activity because the output from the program that executed the original SQL statement had to be edited slightly before it could be used as input. In many years I used SPUFI for the initial SQL. Then some day a friend of mine and I developed the idea that DSNTIAUL could do the trick. This idea originates from tip week 28/2001.

The following job is an example of the proposed use:

//GENERATE EXEC PGM=IKJEFT01
//SYSTSIN  DD *
    DSN SYSTEM(DB2)
    RUN PROGRAM(DSNTIAUL) PARMS('SQL')
    END
/*
//SYSTSPRT DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSPRINT DD SYSOUT=*
//SYSREC00 DD DSN=&GENDATA,DISP=(NEW,PASS),
//         SPACE=(TRK,(1,10),RLSE)
//SYSIN    DD *
    SELECT SUBSTR
         ( 'REPAIR SET TABLESPACE '
         !! DBNAME !! '.' !! NAME !! ' NOCOPYPEND'
         !! '                                        '
         !! '                                        '
         , 1, 80
         )
    FROM SYSIBM.SYSTABLESPACE
    WHERE DBNAME = 'DSNDB04'
    ORDER BY 1
    ;
/*
//EXECUTE  EXEC PGM=DSNUTILB,REGION=6M,
//         PARM='DB2,EXECUTE.DSNTIAUL'
//SYSIN    DD DSN=&GENDATA,DISP=(OLD,DELETE)
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*

In this example the first step generates a REPAIR statement pr. tablespace in database DSNDB04. This REPAIR removes the COPY PENDING status if it is present. The second step executes the generated REPAIR statements. What kind of processing the second step carries out is completely up to your imagination. This is of course also true for the first step, but some rules are to be obeyed.

Rule number one requires you to put a SUBSTR around the string that your SQL statement returns. If you don't DB2 will return a VARCHAR string and then you will have to cut off the first two bytes in &GENDATA, because these two bytes contains a length field. Rule number two requires you to put a number of spaces after the string in order to make the SUBSTR function operate on a string that is longer than the specified length (in this example 80). According to the manuals of DB2 version 5 and 6 describing the SUBSTR function, DB2 pads the result with spaces but my experience is a bit different. You can try and remove the concatenation of spaces. In the example I put 80 spaces after the string. In version 6 and after you can use SPACE(80) instead.

If any of the columns used to build the string can contain nulls then &GENDATA will get a LRECL of 81 instead of 80. This will cause a lot of trouble for some utilities. In these cases I change the length parameter in the SUBSTR to 79 resulting in a LRECL of 80.

Please note that the SYSPUNCH DD statement is set to DUMMY. DSNTIAUL always generates a LOAD-statement, but you don't need it unless you want to LOAD the generated output. You should also note that the two steps may not work on your installation. The name of your DB2 subsystem may be different and in order to execute DSNTIAUL or DSNUTILB you may have to specify a STEPLIB.