Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2013
    Posts
    3

    Exclamation Unanswered: DB2 data load issue

    Actually I am using JCL to run the DSNUPROC in a step.
    In this step i have infile(SYSREC00) with data and sysin cards that
    tells to load data into table.Please find the JCL step below
    //LOADAIM EXEC DSNUPROC,
    // COND=(1,LT),
    // PARM='&DB2SYS,LSPMAIM'
    //SYSREC00 DD DSN=XX.AGT.SPM948.SORT02.CRPITM,
    // DISP=(SHR,KEEP,KEEP)
    //SYSIN DD DSN=XX.AG.DATA(LDRPLAIM),
    // DISP=(SHR,KEEP,KEEP)
    // DD DSN=XX.AG.DATA(LSPMAIM),
    // DISP=(SHR,KEEP,KEEP)
    //SYSUT1 DD UNIT=SYSDA,
    // SPACE=(TRK,(500,5000),RLSE)
    //SORTOUT DD UNIT=SYSDA,
    // SPACE=(TRK,(100,5000),RLSE)


    Here SYSIN card is having the data like below:
    LOAD DATA LOG NO NOCOPYPEND REPLACE INDDN SYSREC00
    INTO TABLE DB2PDBA.AIM_CRPRT_RTL_ITEM
    (
    FACILITY POSITION( 1 )
    CHAR( 2) ,
    WAREHOUSE POSITION( 3 )
    CHAR( 2) ,
    ITM_CD POSITION( 5 )
    CHAR( 7) ,
    PROD_UPC POSITION( 12 )
    CHAR( 15) ,
    ITM_SIZ_DSC POSITION( 27 )
    CHAR( 15) ,
    ITM_UNT_CMP_DSC_CD POSITION( 42 )
    CHAR( 2) ,
    DESC_SHORT POSITION( 44 )
    CHAR( 20) ,
    RTL_CMDTY_CD POSITION( 183 )
    CHAR( 3) ,
    RTL_SUB_CMDTY_CD POSITION( 186 )
    CHAR( 3) ,
    ORDR_BOOK_INDX_CD POSITION( 189 )
    CHAR( 5) ,
    NEW_ITM_DT POSITION( 194 )
    DATE EXTERNAL( 10) ,
    VEN_CD POSITION( 204 )
    CHAR( 7) ,
    ORGNL_FCLTY_CD POSITION( 211 )
    CHAR( 2)
    )


    But when I ran this step thru job, input file SYSREC00 has data but
    message is coming as below:

    db2 -mvtf /tmp/load.55.DSNUPROC_DSNUPROC
    LOAD CLIENT FROM /awg/p1/agt/XX/XX.AGT.SPM948.SORT02.CRPITM OF ASC MODIFIED BY CODEPAGE=819 STRIPTNULLS BINARYNUMERICS PAC
    KEDDECIMAL DATEFORMAT="YYYY-MM-DD" TIMESTAMPFORMAT="YYYY-MM-DD-HH.MM.SS.UUUUUU" TIMEFORMAT="HH.MM.SS" NULLINDCHAR=X6F RECL
    EN=212 METHOD L ( 1 2,3 4,5 11,12 26,27 41,42 43,44 63,64 66,67 69,70 72,73 74,75 76,77 77,78 79,80 80,81 83,84 133,134 14
    8,149 151,152 154,155 158,159 162,163 163,164 165,166 166,167 176,177 177,178 178,179 180,181 181,182 182,183 185,186 188,
    189 193,194 203,204 210,211 212 ) null indicators ( 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,
    0,0 ) REPLACE INTO DB2PDBA.AIM_CRPRT_RTL_ITEM ( FACILITY,WAREHOUSE,ITM_CD,PROD_UPC,ITM_SIZ_DSC,ITM _UNT_CMP_DSC_CD,DESC_SHO
    RT,POS_RTL_STO_DPT,ITM_CMD_CD,ITM_SUB_CMD_CD,UNT_P RC_WGT_DSC_CD,CUR_RTL_WHT_FOR,ITM_PRV_LAB_CD,WHS_D PT,ITM_CHK_DGT,RTL_UNT
    _WGT_AMT,ITM_DSC,CASE_UPC,POS_RTL_MIX_MCH,ORD_MULT _QTY,RTL_UNT_VEN_CSE,RTL_UNT_STR_CSE,RDM_WGT_FL,VE N_CSE_PER_LAY,ITM_STT_
    DCT,ITM_DCT_DT,SHP_FL,SPR_SHF_LBL,NBR_LAY_PER_PAL, VNDR_PACK_CHG,PRMTN_FL,RTL_CMDTY_CD,RTL_SUB_CMDTY_ CD,ORDR_BOOK_INDX_CD,N
    EW_ITM_DT,VEN_CD,ORGNL_FCLTY_CD ) NORECOVERABLE
    SQL0104N An unexpected token "NORECOVERABLE" was found following ")".
    Expected tokens may include: "COPY". SQLSTATE=42601

    Actually keyword NORECOVERABLE is not in the card only after the Load query
    but still this keyword NORECOVERABLE got added and saying unable to
    identify the keyword NORECOVERABLE.
    And sending this Sqlstate as 42601.
    Can any one help me on this issue please.
    Last edited by hkumardbforum; 09-27-13 at 09:02. Reason: updated the issue

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You seem to be mixing DB2 LUW (Linux, UNIX, Windows) with DB2 z/OS. They are two different products, especially when it comes to commands and utilities (although insert, update, delete, and select statements are almost identical).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Wherever that's coming from, it should be NONRECOVERABLE.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Sep 2013
    Posts
    3

    Hi Marcus

    Yes you are right, I am trying to run in DB2 LUW.
    I want to get rid of that abend saying unkown keyword Norecoverable
    after the load query as i ve given it initially.
    Norecoverable keyword is not in the query but i dont know it is still getting added and saying that Norecoverable word is not recognised and giving the error as sqlstate 42601.
    Do i need to do any modification to the load query to prevent this abend.
    Please help me on this.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Who created /tmp/load.55.DSNUPROC_DSNUPROC? Do you have some tool that reads your JCL (specifically SYSIN input) and creates load.55.DSNUPROC_DSNUPROC? This file contains NORECOVERABLE and, as n_i pointed out, it's misspelled.

  6. #6
    Join Date
    Sep 2013
    Posts
    3

    Exclamation DB2 data load issue in DB2 LUW

    Yes you are right, but I am running through the JCL only as I have given earlier.
    This is the jcl step that runs the DSNUPROC:
    //LOADAIM EXEC DSNUPROC,
    // COND=(1,LT),
    // PARM='&DB2SYS,LSPMAIM'
    //SYSREC00 DD DSN=XX.AGT.SPM948.SORT02.CRPITM,
    // DISP=(SHR,KEEP,KEEP)
    //SYSIN DD DSN=XX.AG.DATA(LDRPLAIM),
    // DISP=(SHR,KEEP,KEEP)
    // DD DSN=XX.AG.DATA(LSPMAIM),
    // DISP=(SHR,KEEP,KEEP)
    //SYSUT1 DD UNIT=SYSDA,
    // SPACE=(TRK,(500,5000),RLSE)
    //SORTOUT DD UNIT=SYSDA,
    // SPACE=(TRK,(100,5000),RLSE)

    and my sysin card is like below:
    LOAD DATA LOG NO NOCOPYPEND REPLACE INDDN SYSREC00
    INTO TABLE DB2PDBA.AIM_CRPRT_RTL_ITEM
    (
    FACILITY POSITION( 1 )
    CHAR( 2) ,
    WAREHOUSE POSITION( 3 )
    CHAR( 2) ,
    ITM_CD POSITION( 5 )
    CHAR( 7) ,
    PROD_UPC POSITION( 12 )
    CHAR( 15) ,
    ITM_SIZ_DSC POSITION( 27 )
    CHAR( 15) ,
    ITM_UNT_CMP_DSC_CD POSITION( 42 )
    CHAR( 2) ,
    DESC_SHORT POSITION( 44 )
    CHAR( 20) ,
    RTL_CMDTY_CD POSITION( 183 )
    CHAR( 3) ,
    RTL_SUB_CMDTY_CD POSITION( 186 )
    CHAR( 3) ,
    ORDR_BOOK_INDX_CD POSITION( 189 )
    CHAR( 5) ,
    NEW_ITM_DT POSITION( 194 )
    DATE EXTERNAL( 10) ,
    VEN_CD POSITION( 204 )
    CHAR( 7) ,
    ORGNL_FCLTY_CD POSITION( 211 )
    CHAR( 2)
    )


    As you mentioned, /tmp/load.55.DSNUPROC_DSNUPROC is getting created automatically with the keyword NORECOVERABLE added at the last of the command which i have given above.
    But we have not coded this word NORECOVERABLE I am not understanding why this word is getting added.
    Please help me to get avoid from this abend and this keyword NORECOVERABLE.

Posting Permissions

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