Hi All !

We have an application that uses java and sqlj. I believe sqlj is used for static sql.
In that case, how does one determine the access path of the various sql statements used in the java code.
- is there a package that can be explained ?

thank you.
Anil

for static sql, there is always a package created (that can be explained)
have also a look at db2sqljbind command

when is a db2 package created for the java application ?
does 'rebuild all' create a package ?
how do you determine if your sqlj application uses static sql or dynamic sql ?
what are the sqlj settings that would force the application to use static sql ?
is it possible to enable the use of static and dynamic sql at the same time ?

thanks
Anil

sqlj is static only, but you can mix in JDBC calls using the standard JDBC libraries to do dynamic sql.

The command "sqlj" reads the .java file and produces a revised .java file and a .ser file.

db2sqljcustomize takes the .ser file and creates a package in the database.

db2sqljbind can be used to bind that .ser file and create a package in other databases.

how does one invoke the sqlj command ?
is that something that happens at the time of rebuild ? or is that something that needs to be done explicitly ?

thanks
Anil

Explicitly, although something like Websphere might take care of that for you.

When I first posted my question to the forum, I was confused about the role of SQLJ in a java based applications (developed using WSAD).
I wasn't sure if SQLJ always meant static SQL ? Some the replies to my question did help me understand a bit of how SQLJ is used.
However, I am still not clear on how to verify if the application uses static or dynamic sql (depsite the fact we are using sqlj).

I searched the help in WSAD, and I read the following:
"Running the customization script binds the SQL statements to the database and enables your application to use static SQL rather than dynamic SQL. If you omit this step, then your SQLJ application will use dynamic SQL, as JDBC does."
I have never seen any of the developer do the step for 'Running the customization script binds ', and given thatI don't have any java programming background, my question to those have used WSAD and SQLJ is:

what happens during REBUILD ? I know that after REBUILD ALL, it does validate the SQL statements, and also creates the .ser files. But, is that just half the process without the customization script binds ? Or Does WSAD take care of the customization script binds step when you perform a REBUILD ALL of project ?

Maybe it would simpler if I just ask what are the steps you do inorder to prepare (build) your application such that you use static sql ?

Thanks,
Anil

I'm really surprised, I can't find any reference to this in the DB2 docs? Can someone point it out if they know where to find it?

In v6 and v7 you actually had bind files, not just .ser file, and you bound them normally. If you didn't, the app couldn't run. Looks like they've added a feature that it can run without the bind? In which case it sounds like it's taking what should be static sql and treating it dynamically. Not really "dynamic SQL" in the truest sense.

At any rate, the command "db2sqljbind" will perform the bind from the .ser file, I think that's what you're looking for.

I had a hard time getting this info.
It turns out, that it's in your WSAD help.
Open WSAD
Choose the help option from the main menu bar
Choose help Contents
Search on developing SQLJ applications
and read Overview of developing SQLJ applications

Based on the info it has in there, clearly this application (that I am supposed to analyze for db2 performance) does not use static SQL (it does not perform the run the customization script).

What I am looking for is information on what are the implications of using the static option ?
How do i deploy my application on a different database ? I would have to perform a bind on the new database ?
is it a manual procedure ?

As I have asked before, if someone tells me the steps involved in building their java sqlj applications, and how do they port it across platforms, that would help a lot.

thanks
Anil

The detailed steps are in "Application Development Guide: Building and Running Applications". See the UNIX script that's there.

At a high level it's:

1) sqlj MySQLJApp.sqlj
-> parses your java class and replaces the sql portions with generated code, creates the ".ser" files with the statement information

2) db2sqljcustomize
-> changes the .ser file and by default creates the packages in the database

At this point it's static and ready to run.

If you need it on other databases:

3) db2sqljbind (optional)
-> from your customized .ser file, binds to a given database (creates packages)

So it sounds like Websphere is only doing step (1), resulting in DB2 having to dynamically fire off the statements... something I didn't know was even an option, and certainly it's not desirable for SQLJ apps! Defeats the purpose.

Well, since JDBC could be mixed up with SQLJ before, I don't see a problem here ... finally using the same calls, one time dynamically, onther time static. SQLJ is translated into Java code - which looks suspiciously like JDBC if I remember correctly, using probably rather often the same methods. Since you can create packages from JDBC code, why not the other way round ?

Still - my own experience here is rather long time back so I am not sure whether that is all correct, it is just what I think.

Well, Anil, you have two options now:

Either teach you application developers to create packages. Since they use SQLJ already, this might be the preferred option. Then you can explain the SQL in those packages

Or: use the tools to catch and explain dynamic SQL. The easiest one to start with would be the SQL snapshot, followed by monitoring SQL. Not sure whether db2audit has an option to catch statements. I was always happy with the snapshot. The better the coding style of your programmers (parameter markers / host variables etc) the less disadvantage you have, since also dynamic SQL is being cached.

Actually it is a good option to compare both aproaches performance wise to decide which one is actually best for you: dynamic SQL using actuall statistic values - or static hopefully having the advantage that the access path is good.