Results 1 to 11 of 11

Thread: db2 v7 unload

  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Unanswered: db2 v7 unload

    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

  2. #2
    Join Date
    Oct 2004
    Posts
    6
    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

  3. #3
    Join Date
    Oct 2004
    Posts
    7
    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!

  4. #4
    Join Date
    Oct 2004
    Posts
    6
    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.

  5. #5
    Join Date
    Oct 2004
    Posts
    7
    Thanks Tony

  6. #6
    Join Date
    Feb 2004
    Posts
    25
    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.

  7. #7
    Join Date
    Oct 2004
    Posts
    7
    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.

  8. #8
    Join Date
    Feb 2004
    Posts
    25
    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.

  9. #9
    Join Date
    Oct 2004
    Posts
    7
    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.col2
    How do you translate it into UNLOAD
    Thanks again

  10. #10
    Join Date
    Feb 2004
    Posts
    25
    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.

  11. #11
    Join Date
    Oct 2004
    Posts
    7
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •