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
FROM UDB123.CUS065_ACCT;
There are only three fields in this table.

Any ideas?
Thanks very much in advance!

zhao

Zhao,
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.

Hope this helps,
Tony

Thanks Tony,
In order to avoid getting return code 4, is there any db2 unitility can be used to select data on selected columns and get return code 0.
Thanks again!

You can use the sample program, DSNTEP2, but your output will be in a report format instead of just raw data.

Another option is a third party unload utility, such as...
* CA/Platinum Fast Unload (which we use)
* BMC Unload Plus
* IBM High Performance Unload (An IBM unload utility sold seperately from DB2)

These have a lot more functionality and flexability than the DSNTIAUL sample program.

Thanks Tony

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.

Hi Crisscross,
I had looked at the UNLOAD utility yesterday. I found it easy too. But, Do you know about its performance? I use it to unload about 100MB size of data. Would it be a problem?
Thanks.

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.

Thanks.
How do you use UNLOAD with where clause and more than one tables.
ie:
SELECT col1,col2,col3
FROM tbl1,tbl2
WHERE tbl1.col1=tbl2.col2How do you translate it into UNLOAD
Thanks again

I don't think you can do joins, and I thought from your first posting you were talking only about a single table. And I misspoke when I said WHERE clause, it's really a WHEN clause.

Hi Crisscross,
I was talking only about a single table. When you mentioned WHERE clause, I thought you knew how to do joins.
Thanks very much.