Hi,

I'm a Db2-newbie with some Oracle background. I have to unlaod some columns from a table. 2 of these are of type integer an should be unloaded as comp-3.
According to IBM's example I sketched something like:

//*
//STEP3 EXEC DSNUPROC,UID='JUQBU105.UNLD1',
// UTPROC='',
// SYSTEM='SSTR'
//UTPRINT DD SYSOUT=*
//SYSREC DD DSN=JUQBU105.UNLD1.STEP3.TBQB0501,DISP=(MOD,DELETE ,CATLG),
// UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSPUNCH DD DSN=JUQBU105.UNLD1.STEP3.SYSPUNCH
// DISP=(MOD,CATLG,CATLG)
// UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD*
UNLOAD TABLESPACE DBQB0501.TSQB0501
DELIMITED CHARDEL '#' COLDEL ';' DECPT '!'
PUNCHDDN SYSPUNCH
UNLDDN SYSREC EBCDIC
FROM TABLE ADMF001.TBQB0501
(FILLER POSITION(*) CHAR(3),
PERS POSITION(*) CHAR(26),
KNR POSITION(*) ?????, -- should be comp-3
PNR POSITION(*) ????? -- should be comp-3
/*


So, how to specify the '????'-marked columns as comp-3?


Any help welcome

Db2NewBy

Try DECIMAL PACKED.

This might come handy in the future: IBM Information Management Software for z/OS Solutions Information Center

Hi n_j,

cause I don't ahv the grants affordable, I always have to ask a collegue of mine to do something. So it takes some time to get the resulst. What we did:

//P020 EXEC PGM=IKJEFT01,
// DYNAMNBR=10
//* UNLOAD DB2 -> SEKV. DATEI
//STEPLIB DD DISP=SHR,
// DSN=T1A.LOADMOD.TAZUMG
// DD DISP=SHR,
// DSN=T1A.STAGEC.GL00.DUMMY
// DD DISP=SHR,
// DSN=T1A.STAGED.GL00.DUMMY
// DD DISP=SHR,
// DSN=T1A.STAGEA.GL00.LOADMOD
// DD DISP=SHR,
// DSN=T1A.STAGEB.GL00.LOADMOD
// DD DISP=SHR,
// DSN=T1A.STAGE1.GL00.LOADMOD
// DD DISP=SHR,
// DSN=T1A.STAGE2.GL00.LOADMOD
// DD DISP=SHR,
// DSN=T1A.STAGE3.GL00.LOADMOD
// DD DISP=SHR,
// DSN=T1A.STAGE4.GL00.LOADMOD
// DD DISP=SHR,
// DSN=T1A.STAGE5.GL00.LOADMOD
// DD DISP=SHR,
// DSN=T1A.STAGE6.GL00.LOADMOD
// DD DISP=SHR,
// DSN=R1A.STAGE7.GL00.LOADMOD
// DD DISP=SHR,
// DSN=R1A.STAGE8.GL00.LOADMOD
// DD DISP=SHR,
// DSN=Q1A.STAGE0.GL00.LOADMOD
//SYMFILE DD DISP=SHR,
// DSN=T1A.STAGEC.GL00.SYMDUMMY
// DD DISP=SHR,
// DSN=T1A.STAGED.GL00.SYMDUMMY
// DD DISP=SHR,
// DSN=T1A.STAGEA.GL00.SYMFILE
// DD DISP=SHR,
// DSN=T1A.STAGEB.GL00.SYMFILE
// DD DISP=SHR,
// DSN=T1A.STAGE1.GL00.SYMFILE
// DD DISP=SHR,
// DSN=T1A.STAGE2.GL00.SYMFILE
// DD DISP=SHR,
// DSN=T1A.STAGE3.GL00.SYMFILE
// DD DISP=SHR,
// DSN=T1A.STAGE4.GL00.SYMFILE
// DD DISP=SHR,
// DSN=T1A.STAGE5.GL00.SYMFILE
// DD DISP=SHR,
// DSN=T1A.STAGE6.GL00.SYMFILE
// DD DISP=SHR,
// DSN=R1A.STAGE7.GL00.SYMFILE
// DD DISP=SHR,
// DSN=R1A.STAGE8.GL00.SYMFILE
// DD DISP=SHR,
// DSN=Q1A.STAGE0.GL00.SYMFILE
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//LISTING DD SYSOUT=*
//SYSUDUMP DD SYSOUT=E
//CEEOUT DD SYSOUT=Y
//CEEDUMP DD SYSOUT=E
//SYSOUT DD SYSOUT=*
//SYSREC00 DD DISP=(,CATLG,DELETE),
// DSN=J132560.USER.I304.PERSON.DB2,
// SPACE=(TRK,(06235,02078),RLSE),
// DATACLAS=COMPACT
//SYSPUNCH DD DUMMY
//SYSTSIN DD DISP=SHR,
// DSN=DBDT1OS.G032.SYSIN(DSNTISQL)
//SYSIN DD *
UNLOAD TABLESPACE s304.t_person
PUNCHDDN SYSPUNCH
UNLDDN SYSREC EBCDIC
FROM TABLE s304.t_person
(pers.art POSITION(*) CHAR(1),
pers.art POSITION(*) CHAR(1),
pers.art POSITION(*) CHAR(1),
PERSONEN_NR POSITION(*) DECIMALPACKED,
pers.art POSITION(*) CHAR(1),
pers.art POSITION(*) CHAR(1),
pers.art POSITION(*) CHAR(1),
pers.art POSITION(*) CHAR(1),
pers.art POSITION(*) CHAR(1),
pers.art POSITION(*) CHAR(1),
PERSONEN_NR POSITION(*) DECIMALPACKED,
char(pers_nr) POSITION(*) char(26),
char(pers_nr) POSITION(*) char(26),
pers.art POSITION(*) CHAR(1),
pers.art POSITION(*) CHAR(1),
pers.art POSITION(*) CHAR(1))
//*

the result we got:

DSNT502I ERROR IN DSNTIAUL SQL INPUT:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "UNLOAD". SOME SYMBOLS THAT MIGH
GET SQL SAVEPOINT HOLD FREE ASSOCIATE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 3 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION


regards

Db2NewBy, In your first post, you are trying to create a delimited output file. You can't have COMP-3 in that output.

What DB2 are you using? DB2 for z/OS or DB2i?

If it is DB2z, I would just replace your entire SYSIN DD * with:

SELECT *
FROM s304.t_person

NOTE: This assumes your SYSTSIN has PARMS('SQL')

If you only want some of the columns, replace * with the list of columns.

With standard DSNTIAUL output, Decimals are written in COMP-3 format. You don't have to do anything special to get it.

Hello,

my fault was to use functions in the unload statement instead of plain column names. This will do it:

UNLOAD TABLESPACE GK200304.TSGK2300
PUNCHDDN SYSPUNCH
UNLDDN SYSREC EBCDIC
FROM TABLE S304.T_PERSON
(PERS_TYPE POSITION(*) CHAR(1),
PERSONEN_NR POSITION(*) DECIMAL PACKED,
PERS_ART POSITION(*) CHAR(1),
PERSONEN_NR POSITION(*) DECIMAL PACKED)


Thanks

DbNewBy

(Matthias)