Hi All !

I am working on trying to analyze the performance of an internet application (using java/sqlj).
I was wondering what would be the best way to start looking at the various sql statements used by the application.
My challenge is that the developers have no concept of what happens behind the scenes. All they know is that after a sql statement is ready (in the java code), you invoke the execute method (code to execute the sql).
Therefore, I need to determine how does java code talk to db2 ? is it dynamic sql or static sql, could be either (depending on some setting) ? which setting would determine if it's a dynamic sql or a static sql ? in case, it's a static application ? how does it create a package ? what is the name of the package ?
Could someone briefly explain to me the steps involved in java working with db2 (using sqlj) ? or point me to a link that has more information on this subject.

thanks

Anil

it is asked a lot to quickly explain you the methods for java programming against DB2.

Ressources:
Sathyaram posted a lot of useful stuff:
http://www.dbforums.com/t506495.html

check out the development guide in the Information Center:
http://publib.boulder.ibm.com/infoce...d/cjvintro.htm

a programming class probably would be useful too to get all the concepts.

Anyway, if your programmers come in with JDBC, you can assume it is dynamic SQL(Don't expect them to know about special features on how to create a package from JDBC or stuff like that - they don't know and don't care)

In SQLJ you will have packages.
AFAIK a mixed approach of dynamical and static SQL is supported as well - but usually not used.

I mean you do know whether they use SQLJ and packages or JDBC, do you ?

If you want to know how which method works: try out the samples. That is the quickest way. The radme for each method (SQLJ or JDBC) will guide you through a quickstart, which is very useful to learn how things work.

The samples are installed or you find the in the documentation or here:
http://www-306.ibm.com/software/data...8/samples.html
e.g. for SQLJ:
http://publib.boulder.ibm.com/infoce...j/s-README.htm

Yes, our application does use SQLJ. Does that imply, it is static SQL ?

Anil

SQLJ is always static, *but* you can easily mix in standard dynamic JDBC calls.

IF SQLJ is always static, does it create a package on the server ? how does it come up with package names ? are these packages rebound after a rebuild ?
I viewd the syscat.packages , and I could not see any packages that looked like SQLJ (application) packages.
My goal is to identify the sqlj generated packages,and the explain the packages.

thanks
Anil

I've never not supplied them, but there is a default.

From the Command Reference:

jono

Thanks Jonathan !
Thank you for giving me some idea on what sqlj does. However, the one thing that still continues to puzzle me is that
-if websphere does indeed perform the bind of the packages, there should be a package that I can explain.
I have explained all the packages on my server, and none seem to contain the application sql.

what am I missing ?


Anil

I'm not entirely sure.

Are you sure they're using SQLJ and not just straight JDBC? If it's sqlj, they've got to be there or the app can't run.

Maybe the developers are confused, or they're using SQLJ on a different database?

I had the exact same question. I asked the developer 'how could you be sure that you are using SQLJ (and not jdbc) ?'
And I was shown some files that are generated by the SQLJ precompiler. However, these files are on the application server.
I would think these files, if used to generate a package, would have a package entry on the server.
The db2 manual does talk about the db2sqljcustomize command which is used to determine package names and related options.
But I don't know where in webshpere (WSAD) does one specify these options. I have WSAD installed on my machine and I am not able to track down where do you play around with the db2sqljcustomize settings.


thanks
Anil

Were they ".ser" files? If so, you can run the "db2sqljprint" command to dump the package name and all the SQL.

Yes these are .ser files. I did run the db2sqljprint command.
And yes, I do see the statements.
However, all these .ser files are on the app server (WSAD).
I don't see a corresponding db2 package on the server.
If there exists a pacakge on the server, I could simply run the db2expln command on the package.
And I would get the access path for all the statements included in the .ser file.

Anil

Don't know what else to tell you, it should be on the server, otherwise the statements can't be executed.