Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Question Unanswered: Insert statement getting stuck randomly in DB2 stored procedure

    We are running OLTP transactions on DB2 9.5 on Sun solaris server.Oddly, during November-December every year(during peak workload), a stored procedure gets stuck at a single insert statement randomly.The same procedure works fine on killing the application id and manually running the same procedure.The stored procedure runs fine for remaining year without any intervention.
    The stored procedure stucks while processing data of Walmart only while other accounts(like Best Buy etc.) runs fine.Here is the SQL where it gets stucks:

    INSERT INTO VMIITEMLOCMASTER
    ( CUSTOMERCODE, LOCATIONCODE, PRODUCTCODE, SKU, STATUSCODE, EFFSTARTDATE, EFFENDDATE,
    NEWRELEASEDATE, CATALOGSTARTDATE, CATALOGENDDATE, STREETDATE, MSRP, CAPACITY, MINCAPACITY, SAFETYSTOCK,
    NETCHANGEFLAG, EXTRACTFLAG, CREATED, UPDATED, POG_COMMITDATE, CREATEDBY, FACING,
    PARENTCODE, COMPONENTCAP, COMPONENTFLAG, COMPONENTQTY, BOMCODE, SEARCH_GRP, QUANTITYONHAND
    )
    SELECT CUSTOMERCODE, LOCATIONCODE, PRODUCTCODE, SKU, STATUSCODE,
    COALESCE (EFFSTARTDATE, CAST ('01/01/1901' AS DATE)),
    COALESCE (EFFENDDATE, CAST ('01/01/1901' AS DATE)),
    NEWRELEASEDATE, CATALOGSTARTDATE, CATALOGENDDATE, STREETDATE, MSRP, CAPACITY, COALESCE (MINCAPACITY,0), SAFETYSTOCK,
    NETCHANGEFLAG, 'Y', CURRENT DATE, CURRENT DATE, CURRENT DATE, 'DFUEXTRACT',FACING,
    PARENTCODE, COMPONENTCAP, COMPONENTFLAG, COMPONENTQTY, BOMCODE, SUBSTR(PRODUCTCODE, 1, 3), 0
    FROM HISTPOGITEMLOCMASTER
    WHERE SEQ = :HV00033 :HI00033
    AND CUSTOMERCODE = :HV00009 :HI00009
    EXCEPT
    SELECT P.CUSTOMERCODE, P.LOCATIONCODE, P.PRODUCTCODE, P.SKU, P.STATUSCODE,
    COALESCE (P.EFFSTARTDATE, CAST ('01/01/1901' AS DATE)),
    COALESCE (P.EFFENDDATE , CAST ('01/01/1901' AS DATE)), P.NEWRELEASEDATE,
    P.CATALOGSTARTDATE, P.CATALOGENDDATE, P.STREETDATE, P.MSRP, COALESCE(P.CAPACITY, 0), COALESCE(P.MINCAPACITY, 0), P.SAFETYSTOCK,
    P.NETCHANGEFLAG, 'Y', CURRENT DATE, CURRENT DATE, CURRENT DATE, 'DFUEXTRACT', P.FACING,
    P.PARENTCODE, P.COMPONENTCAP, P.COMPONENTFLAG, P.COMPONENTQTY, P.BOMCODE, SUBSTR(P.PRODUCTCODE, 1, 3), 0
    FROM HISTPOGITEMLOCMASTER P, VMIITEMLOCMASTER M
    WHERE M.CUSTOMERCODE = :HV00009 :HI00009
    AND P.SEQ = :HV00033 :HI00033
    AND P.CUSTOMERCODE = :HV00009 :HI00009
    AND P.CUSTOMERCODE = M.CUSTOMERCODE
    AND P.LOCATIONCODE = M.LOCATIONCODE
    AND P.PRODUCTCODE = M.PRODUCTCODE WITH UR


    I have very limited knowledge to trouble shoot this issue.Any help would be greatly appreciated.

  2. #2
    Join Date
    Nov 2011
    Posts
    4

    Insert statement getting stuck in DB2 9.5 Stored procedure

    Please find the snapshot attached.Please try to open it in wordpad,editplus,textpad for legible format
    Attached Files Attached Files

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Check the LOCKTIMEOUT parm in the db cfg (db2 get db cfg for db-name). If it is set to -1 then you have infinite lock wait time and will not receive a -911 when there is lock contention and your SQL may hang. You may want to change that to 30 seconds to force locktimeout errors.

    Another issue is that you may have some insert latency when log files are full or log buffer is full. I would make your log file size at least 10000 pages, maybe more. Increase LOGBUFFSZ to 256.

    Also look at your page cleaning. This includes number of page cleaners (make sure it is set to automatic) and page cleaning threashold (lower this to about 20% or better yet set the environment variable as follows: db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Nov 2011
    Posts
    4

    Question Insert statement getting stuck randomly in DB2 stored procedure

    Please find the database and database manager configuration file attached.
    Attached Files Attached Files

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Based on your db config (LOCKTIMEOUT -1), looks like it could be hung on a lock wait. LOGBUFSZ seems a bit large, but maybe OK.
    Last edited by Marcus_A; 11-17-11 at 16:45.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Nov 2011
    Posts
    4

    Question Insert statement getting stuck randomly in DB2 stored procedure

    Hi Marcus,

    Many thanks for the reply.The log file size in database is set to 65536 pages(4KB each) and LOGBUFSZ is 1536 pages(4 KB each).

    Moreover, the page cleaners are set automatic and page cleaning threshold is already 5%
    The environment variable DB2_USE_ALTERNATE_PAGE_CLEANING is also set as ON.

    I am not sure about impact to other procedures by changing locktimeout value from -1 to something else.So, I'll refrain myself from setting it to some other value for now . I am attaching the screenshot of db2top captured during issue to give you better idea of locks contention.

    Please advise if you notice anything else.
    Attached Files Attached Files

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I noticed 15 deadlocks in the db2top report, so you may have some kind of lock contention. With LOCKTIMEOUT -1, lockwaits (that are not deadlocked) will wait indefinitely if needed until resource requested is available.

    Also could be automatic resize of tablespace, if increment is large.

    The LOGBUFSZ of 1536 is probably too large IMO, unless you have a lot of LOB's that are logged.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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