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

Where is your SQL statement?

//SYSIN DD *
SELECT * FROM DSN8810.PROJ;
SELECT * FROM DSN8810.EMP
WHERE WORKDEPT LIKE 'D%'
ORDER BY EMPNO;

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

dontbehasty, In all the DSNTIAUL jobs I have run and seen they include a PLAN and LIBRARY parameter in the SYSTSIN statement.

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.