DB2 Server Configuration

[n***@infosys.com]

Hi,

We are facing an issue in Performance testing using e-Load of a J2EE application.The application connects to DB2 database on windows.
Following is the exception that we get while testing the application under load.

com.ibm.db2.jcc.b.SqlException: Sort memory cannot be allocated to process the statement. Reason code = "1".
at com.ibm.db2.jcc.b.kf.b(kf.java(Compiled Code))
at com.ibm.db2.jcc.b.kf.a(kf.java(Inlined Compiled Code))
at com.ibm.db2.jcc.c.jb.e(jb.java(Inlined Compiled Code))
at com.ibm.db2.jcc.c.jb.n(jb.java(Compiled Code))
at com.ibm.db2.jcc.c.jb.j(jb.java(Compiled Code))
at com.ibm.db2.jcc.c.jb.c(jb.java(Compiled Code))
at com.ibm.db2.jcc.c.w.c(w.java(Inlined Compiled Code))
at com.ibm.db2.jcc.c.cc.h(cc.java(Compiled Code))
at com.ibm.db2.jcc.b.sf.p(sf.java(Inlined Compiled Code))
at com.ibm.db2.jcc.b.tf.d(tf.java(Compiled Code))
at com.ibm.db2.jcc.b.tf.X(tf.java(Compiled Code))
at com.ibm.db2.jcc.b.tf.executeQuery(tf.java(Compiled Code))
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java(Compiled Code))
at com.royalbank.wcm.common.BaseDAO.execute(Unknown Source)
at com.royalbank.wcm.common.BaseDAO.select(Unknown Source)
??.
??

We have tried to optimize all queries to best possible extent. Also we have removed all order by clauses from queries to reduce sorts.

Even then we have not been able to resolve the issue.
Apparently, we are not sure if we need to change the DB2 parameters to get around this issue. Please let us know if anyone of you would be able to help us to come up with optimal values for DB2 parameters in this case.

Following are the Database details
============================================================
About DB2 Administration Tools Environment
============================================================
DB2 administration tools level:
Product identifier SQL08015
Level identifier 02060106
Level DB2 v8.1.5.449
Build level s040212
PTF WR21334

============================================================
Java development kit (JDK):
Level IBM Corporation 1.3.1
============================================================
PFA the present configuration for this database.
Any help would be greatly appreciated.

Regards,
***@infosys.com

[sree]

Hi
can u send the DB2 instance level parameters also?
by running command

db2 get dbm cfg

also please mention the details of ur DB2 server, OS, h/w details such as RAM
which is ur app server , is it websphere?

[n***@infosys.com]

Find attached.

[n***@infosys.com]

third and last one.

[n***@infosys.com]

Second one.

[Thirumaran Govindaraju]

Hi

As you said your OS is windows (i.e 2000)

DB2 requires the setting of the DB2_AWE registry variable

When using the DB2_AWE registry variable, the pages referred to in the second and third options of this registry variable are DB2 UDB buffer pages and not physical pages (4 KB pages).The page size of a DB2 buffer pool can be 4 KB, 8 KB, 16 KB, or 32 KB in size. The buffer pool referenced when setting this registry variable may have a buffer pool page size larger than a physical page (4 KB). For example, if we run the following command:

issue this

db2set DB2_AWE=2,100,50
and the buffer pool page size is set to 32 KB, you will get an AWE memory region of 100 buffer pool pages in size which is 800 physical pages. These pages are not directly accessible: they must be accessed using the AWE window. From this command, the AWE window is 50 buffer pool pages in size or 400 physical pages. As a result, the use of this registry variable as shown will require at least (800 + 400) * 4 KB = 4800 KB in memory allocated for both the AWE memory pages and the AWE window. This does not include the memory required for AWE metadata, nor the memory requirements of other applications.

source:
http://www-1.ibm.com/support/docview.wss?uid=swg21212174
(support fo all OS related to SORT issues)

windows
http://www.ibm.com/support/docview.wss?&uid=swg21083192
http://www.ibm.com/support/docview.wss?&uid=swg21175378

Regards
Thirumaran
IBM DB2 DBA

[n***@infosys.com]

Our Server is Windows 2003.

We have already applied AWE settings and still DB2 is not able to use more than 2.5 GB RAM. Any idea, which parameter should be modified to make use of around 6 GB RAM (out of Total 8 GB available on the machine).

[sree]

which version of DB2 you are using?
is it Enterprise Edition / workgroup edition?