Concurrent User - DB2 WorkloadDriver Error --

[kathryn chou]

I'm running the Workload Driver and scaling up the number of concurrent users on DB2 9.5.

When I increase the number of users above 20 -- the driver gives me the following error:

[spec@idb2 WorkloadDriver]$ test_get_order.sh
+ java WorkloadDriver -d tpox -u 30 -w properties/get_order_db2.properties -tr 10
The WorkloadDriver program is running...

The following arguments are used (user id/password omitted):
-d tpox -u 30 -w properties/get_order_db2.properties -tr 10
com.ibm.db2.jcc.b.SqlException: [jcc][4038][12241][3.50.152] T2LUW exception: SQL0902C  A system error (reason code = "") occurred.  Subsequent SQL statements cannot be processed.  SQLSTATE=58005
ERRORCODE=-902, SQLSTATE=58005
        at com.ibm.db2.jcc.b.wc.a(wc.java:55)
        at com.ibm.db2.jcc.b.wc.a(wc.java:102)
        at com.ibm.db2.jcc.uw.UWExceptionGenerator.a(UWExceptionGenerator.java:83)
        at com.ibm.db2.jcc.uw.UWExceptionGenerator.a(UWExceptionGenerator.java:106)
        at com.ibm.db2.jcc.uw.UWConnection.a(UWConnection.java:623)
        at com.ibm.db2.jcc.uw.f.w(f.java:201)
        at com.ibm.db2.jcc.uw.UWConnection.a(UWConnection.java:555)
        at com.ibm.db2.jcc.uw.UWConnection.a(UWConnection.java:400)
        at com.ibm.db2.jcc.uw.UWConnection.<init>(UWConnection.java:290)
        at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:253)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:207)
        at DatabaseOperations.obtainConnectionOnDB2(DatabaseOperations.java:151)
        at DatabaseOperations.establishConnection(DatabaseOperations.java:118)
        at ConcurrentUser.run(ConcurrentUser.java:267)
[spec@idb2 WorkloadDriver]$

Please help -- Any tips on resolving this?
Thanks! :)

[Kevin Xie]

Hi, Kathryn,

It's hard to tell what the problem is just by this error message. We need more information to investigate this problem. If you can, could you please collect and provide us these data. You're using jcc type 2 connectivity, I'm assuming the user spec@idb2 is a db2 instance owner (ie, you can start your db2 database using this user with command "db2start")

1) The get_order_db2.properties file and the queries/statements used by the workload
2) db2 version information: by running "db2level" command in your linux command prompt
3) db2diag.log located at ~/sqllib/db2dump
4) do a jdbc type 2 trace by following this instruction at this link and send us the trace file:
     http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.trb.doc/doc/t0020708.html
Note: the db2cli.ini file is located at ~/sqllib/cfg
5) collect db2 trace data by using db2trc command by following these steps:
   in a seperate command prompt:
[spec@idb2 WorkloadDriver]$ db2trc on
[spec@idb2 WorkloadDriver]$  java WorkloadDriver -d tpox -u 30 -w properties/get_order_db2.properties -tr 10
[spec@idb2 WorkloadDriver]$ db2trc dump db2trc.dmp
[spec@idb2 WorkloadDriver]$ db2trc off
[spec@idb2 WorkloadDriver]$ db2trc fmt db2trc.dmp db2trc.fmt
[spec@idb2 WorkloadDriver]$ db2trc flw db2trc.dmp db2trc.flw

and then send us the db2trc.fmt and db2trc.flw files, here is the link on how to use db2 trace command in case you need more detail information:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0002027.html

Just want to make sure that you can successfully run the WorkloadDriver with less than 20 users, can't you?

Thanks,
Kevin

[kathryn chou]

Hi Kevin,

I get SQL0930N and SQL0902C errors when the number of users are above 30.
A simple TPOX get_order query works fine for 20users or less -- These SQL
errors only happen when the number of users go up.

Is there some DB2 or OS setting I can change/tune to increase
system level resources?

Here's a sample DB2 get_order query (DB2/queries_pm/get_order_sqlxml.xqr)
--##############################################################################
--# Retrieve an order with the specific ID.
--##############################################################################

SELECT XMLQUERY
(
'declare namespace o="http://www.fixprotocol.org/FIXML-4-4";

for $ord in $odoc/o:FIXML
return $ord/o:Order
'
PASSING odoc AS "odoc"
)
FROM order_tab
WHERE XMLEXISTS
(
'declare namespace o="http://www.fixprotocol.org/FIXML-4-4";
$odoc/o:FIXML/o:Order[@ID=$id]
'PASSING odoc AS "odoc", cast (? as varchar(10)) as "id"
)

%

-------------------------------------------------------------------------------
[spec@idb2 tpox_kit]$ db2level
DB21085I  Instance "spec" uses "64" bits and DB2 code release "SQL09050" with
level identifier "03010107".
Informational tokens are "DB2 v9.5.0.0", "s071001", "LINUXAMD6495", and Fix
Pack "0".
Product is installed at "/home/spec/sqllib".

==================================================
[spec@idb2 WorkloadDriver]$ test_get_order.sh
+ java WorkloadDriver -d tpox -u 40 -w properties/get_order_db2.properties -tr 10
The WorkloadDriver program is running...

dBforums > Database Server Software  > DB2  > SQL0930N - There is not Enough Storage available to process the statement
SQL0930N There is not enough storage available to process the
statement.

Explanation: A request was made to the database that required
another memory page but no more pages are available to the
database manager.

The statement cannot be processed.

User Response: Possible solutions include:

o Verify that your system has sufficient real and virtual
memory.

o Remove background processes.

o If the error occurs during DUOW resynchronization, increase
the maxagents and reduce the resync_interval database manager
configuration parameter values.

You have to change your database configuration and or Windows OS config to allow more memory for the process

The following arguments are used (user id/password omitted):
-d tpox -u 40 -w properties/get_order_db2.properties -tr 10
com.ibm.db2.jcc.b.SqlException: [jcc][4038][12241][3.50.152] T2LUW exception: SQL0930N  There is not enough storage available to process the statement.  SQLSTATE=57011
ERRORCODE=-930, SQLSTATE=57011
        at com.ibm.db2.jcc.b.wc.a(wc.java:55)
        at com.ibm.db2.jcc.b.wc.a(wc.java:102)
        at com.ibm.db2.jcc.uw.UWExceptionGenerator.a(UWExceptionGenerator.java:83)
        at com.ibm.db2.jcc.uw.UWExceptionGenerator.a(UWExceptionGenerator.java:106)
        at com.ibm.db2.jcc.uw.UWConnection.a(UWConnection.java:623)
        at com.ibm.db2.jcc.uw.f.w(f.java:201)
        at com.ibm.db2.jcc.uw.UWConnection.a(UWConnection.java:555)
        at com.ibm.db2.jcc.uw.UWConnection.a(UWConnection.java:400)
        at com.ibm.db2.jcc.uw.UWConnection.<init>(UWConnection.java:290)
        at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:253)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:207)
        at DatabaseOperations.obtainConnectionOnDB2(DatabaseOperations.java:151)
        at DatabaseOperations.establishConnection(DatabaseOperations.java:118)
        at ConcurrentUser.run(ConcurrentUser.java:267)
================================

[Kevin Xie]

Hi Kathryn,

It seems that there is not enough memory available to DB2 in your system. This could be that some kernal parameters limit applications from getting share memory, please refer this short documentation on how to modify linux kernel parameter for DB2:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.qb.server.doc/doc/t0008238.html

Apply the required changes and restart DB2 instance, and let us know whether it helps!

Regards,
Kevin