how to download table data with out extra ? for null values

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
sganesh_85
Member
Posts: 14
Joined: Thu Jun 16, 2011 10:18 am

how to download table data with out extra ? for null values

Post by sganesh_85 » Wed Jun 29, 2011 11:28 am

Hi

i have a requirement to send a file to my downstream application.
my TABLE structure is
DECLARE TAB003 TABLE
( ADDRESS_ID INTEGER NOT NULL,
CREATED_BY CHAR(8 NOT NULL,
CREATION_DATE TIMESTAMP NOT NULL,
LOCALITY_ID INTEGER NOT NULL,
STREET_ID INTEGER,
POT_ID SMALLINT,
LGA_TYPE VARCHAR(4))

i need to download data from this table, when i did that i am getting

" 12345abcd 2011-06-28 05:01:30.123456 78963 2349 32 adsd "
" 67891acgd 2011-06-28 05:01:30.123456 56432 4786 ? ?"

the length of the first record is 81 (displayed on file) but it should be 78.
in second record is 77 but it shoud be 78.
----in first record an extra space is added for all optional declarations( may be null or not null)
---in second record question mark is inserted at end for null fields
--- and for varchar first 2 bytes for length and next 4 for data, if null data ? is inserted in the 3 position.

MY QUESTION IS
1)how to download the data without the extra space for optional
declaration?
2) how to make the Varchar declaration as fixed( i need 4 spaces if no data or only the 4 character data?

i need the actual record length 78 to be downloaded..

please help?

i used COALESEC funtion but still i am getting the mismatch data..

Thanks,
Ganesh.
Thanks,
Ganesh.

sganesh_85
Member
Posts: 14
Joined: Thu Jun 16, 2011 10:18 am

Post by sganesh_85 » Wed Jun 29, 2011 11:32 am

in file the datatypes occupy

integer --11
small int --6
char(8) --8
timestamp--26
varchar(4) --- 6 (2+4)

for optional (null or not null ) declarations all the above occupies +1

i dont want this +1 added
Thanks,
Ganesh.

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Wed Jun 29, 2011 8:36 pm

before you can download data from one platform to another (mainframe to server/pc)
you need to unload your data from your db2 table.

what utility and control cards/sql are you using to unload your data?
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

sganesh_85
Member
Posts: 14
Joined: Thu Jun 16, 2011 10:18 am

Post by sganesh_85 » Thu Jun 30, 2011 4:47 pm

Hi Dick

i am using the utility ADUUMAIN
PGM=ADUUMAIN,
PARM='DB1T,,NEW

and in sysin dd
UNLOAD FORMAT EXTERNAL
UNLOADDN(SYSREC1)
SHRLEVEL CHANGE DIRECT NO
SELECT
ADDRESS_ID ,CREATED_BY, CREATION_DATE ,LOCALITY_ID,STREET_ID,POT_ID,LGA_TYPE)
FROM DADBU2.TAB003
FETCH FIRST 10 ROWS ONLY
WITH UR ;

SYSREC1 DD DSN=&HLQ..&MLQ..TAB003.D&ODATE,
DISP=(NEW,CATLG,DELETE),
SPACE=(CYL,(150,350),RLSE),UNIT=(SYSALLDA,25)
SYSCNTL DD DUMMY
Thanks,
Ganesh.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others