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 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 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 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":"SSVJUL","label":"IBM Application Performance Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

UID

ibm11278154

Page Feedback