Results 1 to 5 of 5

Thread: Unload Comp-3

  1. #1
    Join Date
    May 2011
    Posts
    3

    Unanswered: Unload Comp-3

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try DECIMAL PACKED.

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

  3. #3
    Join Date
    May 2011
    Posts
    3

    Seems to be tricky

    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

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  5. #5
    Join Date
    May 2011
    Posts
    3

    Thank to All

    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)

Posting Permissions

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