This is the last tip before the weekly tip enjoys summer holidays and returns again on 1. august 2001.
Dette er det sidste tip før ugens tip holder sommerferie og er tilbage den 1. august 2001.

Now some of you will think, that the only tip about DSNTIAUL is: Don't use this old, slow and obsolete product. But maybe you have overlooked a little detail. DSNTIAUL is able to execute any SQL SELECT statement of your liking. The only limitation is the length and the general limits of SQL SELECT statements in DB2. Here is an example:

//DSNTIAUL EXEC PGM=IKJEFT01
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSPUNCH DD DISP=SHR,DSN=MY.LOAD.STMT
//SYSREC00 DD DISP=SHR,DSN=MY.UNLOADED.DATA
//SYSTSIN  DD *
    DSN SYSTEM(DB2)
    RUN PROGRAM(DSNTIAUL) PARMS('SQL')
    END
/*
//SYSIN    DD *
    SELECT TBCREATOR
         , TBNAME AS TABLENAME
         , COUNT(*) AS COLUMNCOUNT
    FROM SYSIBM.SYSCOLUMNS
    WHERE TBCREATOR = 'SYSIBM'
    GROUP BY TBCREATOR, TBNAME
    ;
/*

The difference is PARMS('SQL'). Notice the use of AS to rename existing columns and to give names to columns, that usually would be nameless in the generated LOAD-statement placed in MY.LOAD.STMT. You can unload any DB2-data in any form you like with DSNTIAUL. It might not be fast, but you can use JOINS and UNIONS and all the other facilities provided by SQL. Only your imagination sets the limits. DSNTIAUL is documented in the DB2 Installation Guide if you want more information.

There is a very irritating detail about DSNTIAUL. In 99% of all cases you have to edit the generated LOAD-statement. I have a version of DSNTIAUL, where I can tell DSNTIAUL which table it should load the unloaded data into with some additional options (use RESUME or REPLACE, generate a REPAIR NOCOPYPEND after LOAD or use LOAD LOG YES). Write to me if you want a copy. I will deliver the assembler source.