Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: DB2 Unload using DSNTIAUL

    Hi

    Im trying to use the following code to unload some data from 3 tables.

    //DBUNLOAD EXEC PGM=IKJEFT01
    //SYSTSPRT DD SYSOUT=*
    //SYSPRINT DD SYSOUT=*
    //SYSUDUMP DD SYSOUT=*
    //SYSREC00 DD DSN=JD1234.UNLOAD,
    // DISP=(NEW,CATLG,CATLG),SPACE=(CYL,(5,5),RLSE)
    //SYSPUNCH DD DSN=JD1234.SYSPUNCH,
    // DISP=(NEW,CATLG,CATLG),SPACE=(CYL,(1,1),RLSE)
    //SYSTSIN DD *
    DSN SYSTEM(DB2A) RETRY(3)
    RUN PROGRAM(DSNTIAUL) PARMS('SQL')
    END


    However the code keeps abending and i get the following error:

    DSNT490I SAMPLE DATA UNLOAD PROGRAM
    DSNT505I DSNTIAUL OPTIONS USED: SQL
    DSNT503I UNLOAD DATA SET SYSPUNCH RECORD LENGTH SET TO 80
    DSNT504I UNLOAD DATA SET SYSPUNCH BLOCK SIZE SET TO 27920
    DSNT506I INPUT STATEMENT WAS NOT A FULL SELECT ON A SINGLE TABLE. LOAD STATEMENT WILL NEED MODIFICATION.
    DSNT503I UNLOAD DATA SET SYSREC00 RECORD LENGTH SET TO 4076
    DSNT504I UNLOAD DATA SET SYSREC00 BLOCK SIZE SET TO 24456


    Despite getting this error, some data is being loaded into JD1234.UNLOAD, however this is only 100 lines. when i try to update PARMS('SQL') to PARMS('SQL,500'), it abends with a S322 error.

    Would anyone be able to help me out.


    Many thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Where is your SQL statement?

    //SYSIN DD *
    SELECT * FROM DSN8810.PROJ;
    SELECT * FROM DSN8810.EMP
    WHERE WORKDEPT LIKE 'D%'
    ORDER BY EMPNO;
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2012
    Posts
    2
    Didn't think you's would have needed to see it but here it is:

    //SYSIN DD *
    SELECT ABC_COL1, ABC_COL2, ABC_COL3 ,DEF_COL1, DEF_COL2, GHI_COL1
    FROM TABLE1 ,TABLE2 ,TABLE3
    WHERE ABC_COL1 = DEF_COL1
    AND ABC_COL1 = GHI_COL1
    WITH UR;


    Many thanks

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dontbehasty, In all the DSNTIAUL jobs I have run and seen they include a PLAN and LIBRARY parameter in the SYSTSIN statement.

    Code:
    //SYSTSIN DD *
    DSN SYSTEM(DB2A) RETRY(3)
    RUN PROGRAM(DSNTIAUL) PLAN(plan-name) -
      LIB('location-of-load-lib') PARMS('SQL')
    END
    /*

  5. #5
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    We'll assume you understand that the S322 completion code indicates that your job exceeded the CPU time allocated for the Job Class that is was run in. So a reasonable action would be to submit the job in a Job Class that allows more CPU time.

    The number following the SQL parm specifies the number of rows that will be returned by a Multi-Row Fetch in the DSNTIAUL program. The default is 100. The fact that your output dataset contained 100 records following the first failure makes sense because your job timed out after DB2 completed the first Multi-Row Fetch operation for 100 rows. You increased the Multi-Row Fetch number to 500, and now your job timed out before DB2 completed the first Multi-Row Fetch operation for 500 rows.

    The problem is most likely a poorly tuned query, but you didn't provide anything useful for anybody to assist with tuning.

Posting Permissions

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