Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2016
    Posts
    3

    Unanswered: DB2 stored procedure fails and start after recompile : SQLCode=-901&SQLState=58004

    I have a Db2/iseries(V7R1) stored procedure running in my PROD enviroment.
    The SP will continue to work for majority of the time.
    The SP is been called from a java program and from other sp's. One a certain day, this SP always throws error SQLCode=-901&SQLState=58004 for any requests.

    There is no change happened to the SP in a long time. Once the SP is recompiled, everything starts working fine with same request which was failed earlier. Since its running for the same data after recompile, i don't suspect any data/logic issue.
    Now this starts working for another 3-4 months and then it will fail one day. This SP is heavily used by different programs.
    Is there any counter/statistic something link that internally with as400 system? not sure why it starts working when it recompile.

    This happens 3-4 times a year. This has been very critical issue in our prod environment
    Any idea why this happens?

    I have put some logger statements to capture the exact line where SQL exception happens. The HANDLER throws SQLException error happens when inserting to a session table because nothing after this statement is getting logged. Following is the statement.

    insert into session.temp_STG_SIZING_OUTPUT ( SSZI_SIZING_ID,SSZO_BATCH_ID,-- SSZO_BRAND_GRP_ID,-- SSZO_MERCH_SEASON_ID, SSZO_DELIVERY,-- SSZO_PROJ_DIVISION_ID,-- SSZO_CUSTOMER_CD, SSZO_DOOR_GROUP_ID, SSZO_DOOR_CD, -- SSZO_MERCH_DIV_ID, SSZO_MERCH_DIV_CD,-- SSZO_PROD_CTG_CD, SSZO_CLASS_CD, SSZO_SUB_CLASS_CD, SSZO_ITEM_CD, SSZO_COLOR_CD, SSZO_MATERIAL_NO ,-- SSZO_CHANNEL_CD,-- SSZO_PLANNING_ATTR_DESC, SSZO_SUB_SIZE_RANGE_ID,-- SSZO_UNITS, SSZO_STATUS, SSZO_ERROR_DESC, SSZO_SIZE, SSZO_QTY,SSZO_RULE_ID, SSZO_AAP_ID) (select SSZI_SIZING_ID,SSZI_BATCH_ID, SSZI_BRAND_GRP_ID,-- SSZI_MERCH_SEASON_ID, SSZI_DELIVERY,-- SSZI_PROJ_DIVISION_ID,-- SSZI_CUSTOMER_CD, -1, SSZI_DOOR_CD, SSZI_MERCH_DIV_CD,-- SSZI_PROD_CTG_CD, SSZI_CLASS_CD, SSZI_SUB_CLASS_CD, SSZI_ITEM_CD, SSZI_COLOR_CD, SSZI_MATERIAL_NO ,-- SSZI_CHANNEL_CD,-- SSZI_PLANNING_ATTR_DESC, SSZI_SUB_SIZE_RANGE_ID,-- SSZI_UNITS, 'SZERROR' , 'PURCHASE GRID defined for this item is not valid - '||SSZI_SUB_SIZE_RANGE_ID, 'N/A',0,-1, SSZI_AAP_ID FROM STG_SIZING_INPUT WHERE SSZI_BATCH_ID=IN_BATCH_ID AND SSZI_MERCH_SEASON_ID=IN_SEASON AND SSZI_BRAND_GRP_ID=IN_BRAND_GRP AND NOT EXISTS (SELECT SSZO_ITEM_CD FROM session.temp_STG_SIZING_OUTPUT WHERE SSZI_BATCH_ID=SSZO_BATCH_ID AND SSZI_MERCH_SEASON_ID=SSZO_MERCH_SEASON_ID AND SSZI_BRAND_GRP_ID=SSZO_BRAND_GRP_ID AND SSZI_MATERIAL_NO=SSZO_MATERIAL_NO) );

    Any help is appreciated.

  2. #2
    Join Date
    Jul 2016
    Location
    Germany
    Posts
    32
    Provided Answers: 2

    We need more details

    Hi mastsetup,

    please give us some additional information (see Must Read before posting)

    1) Every question posted must include your DB2 Version, fixpack and Edition + your Operating System(including version
    info) + info on any third party software you use.
    You can get his info using the following commands
    db2level -> to get db2 version and fixpack level
    db2licm -l -> to get the db2 type (WSE, ESE, etc)

    What type of stored procedure are we talking about ?
    Has there been a change in the java environment ?
    Do you have the Diaglog to this ERROR ?
    Do you have the java log to this ERROR ?

    Good luck
    db2dp

  3. #3
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Have you check with your admin? Specifically what is the non-severe system error that happens? I would expect DB2 to record that information in some diagnostic location and I would also expect the operating system to show some evidence of the error or warning if sufficient diagnostics are enabled. If the -901 symptom repeats on a certain day then that sounds like an idea candidate for tracing to get further diagnostics. Most likely it is a resource issue, like memory or shared-memory, or process-related issue etc.

  4. #4
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Db2/iseries(V7R1) does not record error messages in central location like DB2 LUW. The information (error details) is captured in job log associated with the database connection. You need to look into individual job log associated with database connection to find out more details about error.


    Satya...

  5. #5
    Join Date
    Sep 2016
    Posts
    3
    Quote Originally Posted by db2dp View Post
    Hi mastsetup,

    please give us some additional information (see Must Read before posting)

    1) Every question posted must include your DB2 Version, fixpack and Edition + your Operating System(including version
    info) + info on any third party software you use.
    You can get his info using the following commands
    db2level -> to get db2 version and fixpack level
    db2licm -l -> to get the db2 type (WSE, ESE, etc)

    What type of stored procedure are we talking about ?
    Has there been a change in the java environment ?
    Do you have the Diaglog to this ERROR ?
    Do you have the java log to this ERROR ?

    Good luck
    db2dp

    db2 version is i5/OS 5770SS1 V7R1M0 100423

  6. #6
    Join Date
    Sep 2016
    Posts
    3
    Quote Originally Posted by stiruvee View Post
    Db2/iseries(V7R1) does not record error messages in central location like DB2 LUW. The information (error details) is captured in job log associated with the database connection. You need to look into individual job log associated with database connection to find out more details about error.


    Satya...
    thanks for the response. The joblog is not showing any error message when this error occurred.
    the HANDLER is throwing an error from SQL stored procedure at the above query which mentioned with SQLCode=-901& SQLState=58004 , SQL_ERROR= SQL system error, which we are logging to a table.

  7. #7
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Check table QRECOVERY.QSQ901S.


    Satya...

Posting Permissions

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