Additional Blogs by Members
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
0 Kudos

Consider that you need to configure a JDBC Adapter on receiver side to mass update a specific DB table and lots of messages are processed by the Receiver JDBC Channel executing SQL statements that affects different table records.

In my example the recuirement is to update the table Employee updating the job fields:
UPDATE EMPLOYEE SET JOB = 'MANAGER' WHERE EMPNO = 000010
UPDATE EMPLOYEE SET JOB = 'MANAGER' WHERE EMPNO = 000020
UPDATE EMPLOYEE SET JOB = 'MANAGER' WHERE EMPNO = 000030
UPDATE EMPLOYEE SET JOB = 'MANAGER' WHERE EMPNO = 000050


The JDBC Adapter builds Dynamic SQL Statement (from XML SQL or Native SQL string) doing someting like this:


sqlString = "UPDATE EMPLOYEE SET JOB = 'MANAGER' WHERE EMPNO = '000010'";
Statement stmt = con.createStatement();
stmt.executeUpdate(sqlString );  //<---- Compilation and Execution on DBMS

In this way, using the Statement class, the SQL statement is passed when the executeUpdate() method is called.
The RDBMS get the whole SQL Statement, compile it, and execute it.
This means that in my example the RDBMS will receive and compile four different statements.

According with the official SUN documentation, with java and jdbc it's possible to use a smarter way to manage the SQL statement by using the PreparedStatement class.
This interface allow to create such as a model of a SQL Statement and pass it to the RDBMS for the preliminar phase of compilation of the statement:


sqlString = "UPDATE EMPLOYEE SET JOB = ? WHERE EMPNO = ?";
PreparedStatement pstmt = con.prepareStatement(sqlString); //<---- Compilation on DBMS
pstmt.setString(1, "MANAGER");
pstmt.setString(2, "000010");
pstmt.executeUpdate(); //<---- Execution on DBMS


In this way When the object pstmt is created on Database Engine site the "model" of the statement is passed and compiled; after this the values are passed to the PreparedStatement object with parameter markers and then the statement execution.
The important thing is that with PreparedStatement the SQL Statement is compiled by the database engine once. With some database there is a cache for compiled statement that avoid to compile thousand of similar statement.

Having a look to the standard SAP documentation for the JDBC Adapter there's no mention about the usage of PreparedStatement. Also the Note 831162 - FAQ: XI 3.0 / PI 7.0 / PI 7.1 JDBC Adapter doesn't threat the topic.

Decompiling a little bit of JDBC Adapter seems there is the possibility to specify the advanced parameter sqlBindMode. This parameter by default is set to "false", setting it to "true" the statement will be created with PreparedStatement interface.

image 

With the help of the parameter logSQLStatement we have a look to the SQL Statement in the audit log:

 

On Database Engine side (in my example IBM DB2 Express-C v9.1) it's possible to have a look to the monitor of sql statements:

image


Note that the number of compilation is one, but the number of executions is 84!!

PS: Looking at the java code of the JDBC Adapter it seems that the sqlBindMode parameter just works if Batch Mode option is set.
With my tests seems that there's no relation between these two parameters.

 

***UPDATE***

Please have a look to OSS Note:

Note 1404778 - Prepared Statement usage in JDBC Reciever Adapter 

 

References:

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/PreparedStatement.html
http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.java.doc/do...
http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#35_02

7 Comments