IBM Support

S&P agent or WPA showing error SQLSTATE=40001

Technical Blog Post


Abstract

S&P agent or WPA showing error SQLSTATE=40001

Body

image

S&P agent  or WPA showing error SQLSTATE=40001

As with a lot of the warehouse errors this error can be seen on the WPA or the S&P agent.

On the S&P agent it could be shown like:

freddie:SY KNT 18000 NT_Processor NT_Processor 02/15/18 11:27:58 com.ibm.db2.jcc.am.SqlTransactionRollbackException: The current transaction has been rolled back because of a deadlock or timeout. Reason code "68".. SQLCODE=-911, SQLSTATE=40001, DRIVER=3.62.57 -911 40001

As the error message show the issue is due to a deadlock or time out.

These errors can be causes by a number of reason some of which are stated below along with some tuning that may help.

However these error message should not mean any loss of data as the WPA and S&P will retry the action later, and should succeed.

Reasons for the Error and possible tuning for warehouse:

If this message is being seen often and historical data is not being seen, it could be that:

1) The sy agent takes more time than the timeout to summarize or prune a table the timeout is 120 seconds,  however if there is more data to summarize it may take longer.

2) There is more than one WPA in the ITM environment and there is a clash when more than one agent tries to write to the same table.

3) There are issues with the performance of the warehouse these  can be avoided in some cases with warehouse tuning which is discussed here:

https://www.ibm.com/support/knowledgecenter/SSTFXA_6.3.0/com.ibm.itm.doc_6.3/install/datawarehousetuning.htm

If there is a database administrator then they can check the db2 instance for errors;  however one area to check is that LOCKLIST and MAXLOCKS are set to automatic tuning.

This can be checked by the commands:

db2 connect to WAREHOUS user db2inst1 using <password>

db2 get db cfg | grep LOCK


The values can be set to AUTOMATIC with the commands:

db2 update DB CFG using LOCKLIST AUTOMATIC

db2 update DB CFG using MAXLOCKS AUTOMATIC


However the DB2 instance needs to be recycled for these to take effect, commands for this are:

db2 disconnect current

db2 force applications all

db2 terminate

db2stop

db2start

Tutorials Point

Subscribe and follow us for all the latest information directly on your social feeds:

image

image

image

  

Check out all our other posts and updates:
Academy Blogs: https://goo.gl/U7cYYY
Academy Videos: https://goo.gl/TLfMoF
Academy Google+: https://goo.gl/HnTs0w
Academy Twitter : https://goo.gl/HnTs0w


image

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"","label":""},"Component":"","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSTFXA","label":"Tivoli Monitoring"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

UID

ibm11083231

Page Feedback