I ran a test unloading data from a single table using DSNTIAUL in DB2 V7
It gave me return code 4 and complains:
" DSNT506I INPUT STATEMENT WAS NOT A FULL SELECT ON A SINGLE TABLE.
LOAD STATEMENT WILL NEED MODIFICATION"
Here is the SQL:
SELECT ACCT_NUM, START_DT, END_DT
There are only three fields in this table.
The DSNT506I message is displayed because you hard coded the three columns in the SELECT statement. If you the three you specified are the only three in the table, then use SELECT* to keep the DSNT506I message from displaying. However, note that the unload data and load control cards should be OK even though you got this message.
When you use SELECT *, DSNTIAUL uses the DB2 Catalog to determine what the columns are in the table and generates the load control cards based on that info. If you include column names in the SELECT statement, DSNTIAUL can not rely on the catalog for the info to build the control cards with, and so, you get the DSNT506I message by default.
Have you looked at the UNLOAD utility? Check in the Utility Guide. It's a bit different than DSNTIAUL, but we have found it easy to use. We use it to unload a smaller sample size from some of our large tables to get test data for loading.
To be perfectly honest, we haven't changed all of our UNLOAD processes to use the utility instead of DSNTIAUL (many batch jobs!). But where we did use it, it seems to run very quickly - but again we are using a WHERE clause to unload a subset of the whole table.