SQLJ vs. JDBC performance

Arthur Brack

SQLJ vs. JDBC performance
Hello,

I am developing a java application for z/OS which stores data in a DB2
database.
Our customers demand static SQL, therefore I use the SQLJ library of IBM.


My testprogram inserts 100,000 rows in a table and does a commit after
each 10,000 inserts. The table has 35 columns with a primary key on one
column. For the connection I used the Type 4 Driver and I also
customized and bound the SQL-Statements to the database using the
"com.ibm.db2.jcc.sqlj.Customizer"! But my program was not very fast, on
the z/OS machine it needs for the insertion of 100,000 rows round about
one minute.

In order to compare the performance with dynamic SQL I also wrote a
similar program using plain JDBC.
I used a PreparedStatement and added the inserts to the batch of
commands of the PreparedStatement. Running this program on the z/OS
machine needs about 18 seconds.


Why is the SQLJ program more than three times slower than the dynamic
SQL program????
(Everywhere I can read that SQLJ shall be faster than dynamic SQL.)


Does anyone have the same experience using SQLJ with DB2 V9 and can
provide tuning tipps?



Thanks in advance!

Kind regards,

Arthur Brack


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Mike Hutchinson

Re: SQLJ vs. JDBC performance
(in response to Arthur Brack)
Was SQL_ATTR_AUTOCOMMIT set to OFF, rather than the default value of ON?


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Arthur Brack
Sent: Friday, January 30, 2009 9:41 AM
To: [login to unmask email]
Subject: [DB2-L] SQLJ vs. JDBC performance

Hello,

I am developing a java application for z/OS which stores data in a DB2
database.
Our customers demand static SQL, therefore I use the SQLJ library of
IBM.


My testprogram inserts 100,000 rows in a table and does a commit after
each 10,000 inserts. The table has 35 columns with a primary key on one
column. For the connection I used the Type 4 Driver and I also
customized and bound the SQL-Statements to the database using the
"com.ibm.db2.jcc.sqlj.Customizer"! But my program was not very fast, on
the z/OS machine it needs for the insertion of 100,000 rows round about
one minute.

In order to compare the performance with dynamic SQL I also wrote a
similar program using plain JDBC.
I used a PreparedStatement and added the inserts to the batch of
commands of the PreparedStatement. Running this program on the z/OS
machine needs about 18 seconds.


Why is the SQLJ program more than three times slower than the dynamic
SQL program????
(Everywhere I can read that SQLJ shall be faster than dynamic SQL.)


Does anyone have the same experience using SQLJ with DB2 V9 and can
provide tuning tipps?



Thanks in advance!

Kind regards,

Arthur Brack


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html