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.

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

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).

Please find the database and database manager configuration file attached.

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.

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.

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.