Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DSNTIAUL truncating COUNT

Status
Not open for further replies.

db2geekami

Programmer
Nov 10, 2011
2
US
I am using DSNTIAUL to unload data from a DB2 table. I count the number of distinct SSN's as well as return two other columns of data.

Code:
SELECT COUNT(DISTINCT SSN),WKS_DUR, TOTAL_WBA          
FROM UIPROD.UCCLMS               
WHERE YEAR(BYB_DTE) = 2010       
GROUP BY WKS_DUR, TOTAL_WBA      
ORDER BY WKS_DUR, TOTAL_WBA DESC 
WITH UR; [code] 

DSNTIAUL creates a sequential file with data fields for each column of data returned. The fields are automatically defined as half word binary (PIC S9(4) COMP). This limits the largest number that can be stored to 65,535. One record has a count that exceeds that value and is being truncated. 

Is there a way to expand the size of data fields created by the DSNTIAUL utility? I've tried CAST with INTEGER but it still creates the data fields as half word binary.
 
happy.gif

Omit the DCB parameter from the JCL for the output data set. Let the DSNTIAUL utility program determine the length of the data extracted from DB2. When the value of COUNT exceeds 65,535, the data field will be defined as a full word binary [PIC S9(8) COMP].

//SYSREC00 DD DSN=AA4717.DB2.UCCLMS,
// DISP=(,CATLG,DELETE),
// UNIT=DISK,SPACE=(TRK,(5,1),RLSE)
//* OMIT THE DCB PARAMETER
//* LET THE UTILITY PROGRAM DETERMINE RECORD LENGTH
//* AND DATA FIELD DEFINITIONS
//*
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top