Does SQLJ provide a static SQL interface ?

Mohammad Khan

Does SQLJ provide a static SQL interface ?
Hi everyone
Till now I'm under the impression that SQLJ is a static SQL interface i.e.
no statement preparation occurs at run time. But the following text from
the book "DB2 High Performance Design and Tuning by Yevich & Lawson" casts
doubt on this impression. The text is on page 580 :

"But we need to focus on the fact that SQLJ really is JDBC. DB2 does
provide statement caching to help with all this dynamic SQL that is
frequently called and executes in a very short time; it provides almost
static performance. It allows the reuse of dynamically prepared access
plan, reducing dynamic PREPARE overhead."

Would anyone please clarify this.
Thanks
Mohammad



Michael Piontkowski

Re: Does SQLJ provide a static SQL interface ?
(in response to Mohammad Khan)
My understanding is that SQLJ will 1st attempt to execute static
SQL for a SQL statement. If the static SQL does not exist, then
the SQL statement will be executed using JDBC.



Mike Piontkowski

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]On Behalf Of
Mohammad Khan
Sent: Wednesday, December 19, 2001 21:25
To: [login to unmask email]
Subject: [DB2-L] Does SQLJ provide a static SQL interface ?


Hi everyone
Till now I'm under the impression that SQLJ is a static SQL interface i.e.
no statement preparation occurs at run time. But the following text from
the book "DB2 High Performance Design and Tuning by Yevich & Lawson" casts
doubt on this impression. The text is on page 580 :

"But we need to focus on the fact that SQLJ really is JDBC. DB2 does
provide statement caching to help with all this dynamic SQL that is
frequently called and executes in a very short time; it provides almost
static performance. It allows the reuse of dynamically prepared access
plan, reducing dynamic PREPARE overhead."

Would anyone please clarify this.
Thanks
Mohammad








Michael Deer

Re: Does SQLJ provide a static SQL interface ?
(in response to Michael Piontkowski)
My understanding is that for non-DB2 sqlj, this is true: sqlj
statements are turned into jdbc calls at run time. However, for db2
sqlj, when you run 'db2profc', a flag is set in the '.ser' file which
causes the program to go look for the corresponding db2 package (static
sql). Yet, even if your on DB2 for os390, if this flag is not set,
then it will do run-time jdbc calls. I think this is a
portability/compatibility thing. The ability to do 'real' static sql
via sqlj is one of the advantages to using DB2 as your dbms behind your
java apps. I dont have the book you referenced, but it sounds like it
is in error or at least needs clarification in this area.

Mike Deer
DB2 Systems Programmer
United Airlines

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]On Behalf Of
Mohammad Khan
Sent: Wednesday, December 19, 2001 21:25
To: [login to unmask email]
Subject: [DB2-L] Does SQLJ provide a static SQL interface ?


Hi everyone
Till now I'm under the impression that SQLJ is a static SQL interface
i.e.
no statement preparation occurs at run time. But the following text
from
the book "DB2 High Performance Design and Tuning by Yevich & Lawson"
casts
doubt on this impression. The text is on page 580 :

"But we need to focus on the fact that SQLJ really is JDBC. DB2 does
provide statement caching to help with all this dynamic SQL that is
frequently called and executes in a very short time; it provides
almost
static performance. It allows the reuse of dynamically prepared
access
plan, reducing dynamic PREPARE overhead."

Would anyone please clarify this.
Thanks
Mohammad



visit the
DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can be




visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
the list can



Roger Miller

Re: Does SQLJ provide a static SQL interface ?
(in response to Michael Deer)
Java and DB2 are changing so fast that it's very hard to keep up. The key
advantage for DB2 is static SQL with the embedded syntax in SQLJ. The
structure was built upon JDBC and if the customizing is not done,
(db2profc), then you run as dynamic SQL.

There are so many Redbooks and presentations available that it's hard to
choose. One of the best is Curt Cotner's Java Scales up to Support the
Whole Enterprise! if you want DB2 for z/OS and OS/390. For performance
considerations, see the V7 Performance Topics Redbook, SG24-6129.

For Curt's presentation, start with the DB2 for z/OS home page

ibm.com/software/db2os390
Click on Support
Click on Technical Presentations
Put Java into the filter and click Go.
Choose the Java Scales up to Support the Whole Enterprise!

For Redbooks, from the Support page above, choose Redbooks, and filter with
Java as well. For the performance book, try Performance Topics.

Roger Miller, DB2 for z/OS



Simon George

Re: Does SQLJ provide a static SQL interface ?
(in response to Roger Miller)

SQLJ is a sub-set of the JDBC API that supports only static statements (you cannot for instance issue a PREPARE statement in SQLJ, that has to be done under JDBC, the book DB2 UDB for OS/390 and z/OS V7 Application Programming Guide and Reference for Java list which SQL statements are supported under SQLJ). By default SQLJ SQL statements will still execute dynamically. You will see this happen if you use a windows based development environment such as VisualAge for Java. The point is that to this point, what you have written is still deployment independent just like JAVA code. That changes once you run the profiler, PROFC. This is a specific customiser for the deployment environment. This changes the serialised profile (the SQL if you like) to be specific for DB2/390, indicate that its expecting to run static and produces the DBRM. However, just because you have run profc does not automatically mean that a statement will run statically. It is very possible for sql statements to fail to customise, in which case it will still execute the statement dynamically. The moral of that is to check the output from profc VERY carefully, it will fail to customise for resons that will a baffle you. To complete the picture, if at execution time the .ser file cannot be found for some reason the JVM will throw a class not found error and terminate.

Hope this helps


Cheers Simon

Get your FREE download of MSN Explorer at http://explorer.msn.com.