RSS

#DB2-3 The current transaction was rolled back because of error “-430”. SQLCODE=-1476, SQLSTATE=40506,DRIVER=3.51.90

26 Feb

Few days back during Load Testing on performance environment I came across one issue. The issue was as follows:

When I connect to the application then initially it works fine. But in performance environment as the number of users connecting to the applications
increases at one point all the connections got terminated giving the following error:

com.s1.arch.persistence.exception.PersistenceException: The current transaction was rolled back because of error “-430”.. SQLCODE=-1476, SQLSTATE=40506, DRIVER=3.51.90

Environment Details:
IBM InfoSphere Federation Server with IBM DB2 9.7 FP6

In this case the federated tables are not being able to access through nicknames.While searching out for this
I came across the following possible causes that might be responsible for this situation:

1. The Temporary Tablespace might be full.
2. The application Heap might got full because of which application was unable to process the sql statements.

But both these was not the cause since temporary tablespace was system managed and the APP_HEAP was AUTO-RESIZE.
Tablespace can be cross-verified using the syscat.tablespaces and APP_HEAP was checked in the database configuration parameters.
When I delved more into db2diag.log, I came to know about the actual cause behind this.
The issue was that the resources memory used by the processes was very low.
Due to limited resources db2fmp process was unable to create the new db2fmp thread which were causing the db2fmp unstable.
Because of which we were unable to login to the application after sometime.

What is USER’s RESOURCE MEMORY?
It defines process resource limits for users.
Changing the limit does not affect those processes that were started by init.
Alternatively, ulimits are only used by those processes that go through the login processes.

How to see the current values for the ulimit?
Use the following simple command:
“ulimit -a”.
Points to be remember:
1) You have to set above ulimit settings, not only for db2 instance user, but also for root, db2 fenced user, DAS user, and any other users that will issue “db2start”
2) After the values are set, we have to recycle the db2 (db2stop force && db2start).
3) Recommended Values:
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 4194304
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) unlimited
threads(per process) unlimited
processes(per user) unlimited

4) We can monitor the total memory and the memory used by the application using the following command:
svmon -G
5) Following Link will help you to explore the each and every parameter of the ulimit:
http://bio.gsi.de/DOCS/www.coredumps.de/doc/ibm/aix/aix_hints_and_tips/8514736.htm

I hope this will help all my DB2 friends.

Advertisements
 
Leave a comment

Posted by on February 26, 2014 in DB2

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: