SQLJ Connection Context and SP interportability

[Janick]

With DB2 7.2 we used to call methods from other SQLJ Stored
Procedures, as it was not possible to call SPs via the SQL
Call-statement from within SPs. So we always had a method like this:

protected static void execute(Connection con, (...))

Where we passed a Connection object for dynamic SQL (which we got
using the getConnection() method of the DefaultContext). Static SQL
was always executed on the DefaultContext.

Now with DB2 8.1 we are forced to create a dedicated Context for each
SP, and therefore changed our execute-method to something like this:

protected static void execute(ConnectionContext context, Connection
con, (...))

The problem now is that the SQLJ Precompiler doesn't like the "simple"
ConnectionContext. So if you would try something like this in the
"execute" method

#sql [context] { SELECT imbreqd FROM sysibm.sysdummy1 }

the precompiler says that you have to declare ConnectionContextes
using #sql context declaration. So I changed the execute-method to the
following:

protected static void execute(SPName_ConnectionContext context,
Connection con, (...))

This works fine for the current procedure, but I cannot call the
method from another SP anymore.

There are two solutions which come to mind:
1. Call one SP from another using the SQL-Call-command (now supported
with 8.1)
2. Define one global ConnectionContext used by all SPs

The first solution I don't like as it is much slower than calling a
Java method directly. I'm not sure if the second solution is a
"proper" solution, as you always operate on the same context-class and
this may therefore not be threadsafe (I don't know how those
context-classes are implemented).

Maybe somebody can think of another (better) solution or tell me
whether the second solution would be threadsafe or not.

Regards,
- Janick Bernet, SwissASP

[Sean McKeough]

The reason you need a seperate context in 8.1 is that we're
multithreading on the JVM. Without a context per thread, threads would
run wild over each others objects (cursors etc) closing them, and other
bad things.

If you don't want to use CALL, you can catalog your sqlj stored
procedures as 'not threadsafe' and share the context like you used to
(use the old way of defining the null connection). (I haven't acutally
tried this, but it should work) :-).

You have to choose the tradeoff of multiple JVMs per instance (1 per
concurrently active non threadsafe Java routine) vs. changing your
routines. The change to threadedness was done for scalability.

Janick wrote:

With DB2 7.2 we used to call methods from other SQLJ Stored
Procedures, as it was not possible to call SPs via the SQL
Call-statement from within SPs. So we always had a method like this:

protected static void execute(Connection con, (...))

Where we passed a Connection object for dynamic SQL (which we got
using the getConnection() method of the DefaultContext). Static SQL
was always executed on the DefaultContext.

Now with DB2 8.1 we are forced to create a dedicated Context for each
SP, and therefore changed our execute-method to something like this:

protected static void execute(ConnectionContext context, Connection
con, (...))

The problem now is that the SQLJ Precompiler doesn't like the "simple"
ConnectionContext. So if you would try something like this in the
"execute" method

#sql [context] { SELECT imbreqd FROM sysibm.sysdummy1 }

the precompiler says that you have to declare ConnectionContextes
using #sql context declaration. So I changed the execute-method to the
following:

protected static void execute(SPName_ConnectionContext context,
Connection con, (...))

This works fine for the current procedure, but I cannot call the
method from another SP anymore.

There are two solutions which come to mind:
1. Call one SP from another using the SQL-Call-command (now supported
with 8.1)
2. Define one global ConnectionContext used by all SPs

The first solution I don't like as it is much slower than calling a
Java method directly. I'm not sure if the second solution is a
"proper" solution, as you always operate on the same context-class and
this may therefore not be threadsafe (I don't know how those
context-classes are implemented).

Maybe somebody can think of another (better) solution or tell me
whether the second solution would be threadsafe or not.

Regards,
- Janick Bernet, SwissASP

[Sean McKeough]

The reason you need a seperate context in 8.1 is that we're
multithreading on the JVM. Without a context per thread, threads would
run wild over each others objects (cursors etc) closing them, and other
bad things.

If you don't want to use CALL, you can catalog your sqlj stored
procedures as 'not threadsafe' and share the context like you used to
(use the old way of defining the null connection). (I haven't acutally
tried this, but it should work) :-).

You have to choose the tradeoff of multiple JVMs per instance (1 per
concurrently active non threadsafe Java routine) vs. changing your
routines. The change to threadedness was done for scalability.

Janick wrote:

With DB2 7.2 we used to call methods from other SQLJ Stored
Procedures, as it was not possible to call SPs via the SQL
Call-statement from within SPs. So we always had a method like this:

protected static void execute(Connection con, (...))

Where we passed a Connection object for dynamic SQL (which we got
using the getConnection() method of the DefaultContext). Static SQL
was always executed on the DefaultContext.

Now with DB2 8.1 we are forced to create a dedicated Context for each
SP, and therefore changed our execute-method to something like this:

protected static void execute(ConnectionContext context, Connection
con, (...))

The problem now is that the SQLJ Precompiler doesn't like the "simple"
ConnectionContext. So if you would try something like this in the
"execute" method

#sql [context] { SELECT imbreqd FROM sysibm.sysdummy1 }

the precompiler says that you have to declare ConnectionContextes
using #sql context declaration. So I changed the execute-method to the
following:

protected static void execute(SPName_ConnectionContext context,
Connection con, (...))

This works fine for the current procedure, but I cannot call the
method from another SP anymore.

There are two solutions which come to mind:
1. Call one SP from another using the SQL-Call-command (now supported
with 8.1)
2. Define one global ConnectionContext used by all SPs

The first solution I don't like as it is much slower than calling a
Java method directly. I'm not sure if the second solution is a
"proper" solution, as you always operate on the same context-class and
this may therefore not be threadsafe (I don't know how those
context-classes are implemented).

Maybe somebody can think of another (better) solution or tell me
whether the second solution would be threadsafe or not.

Regards,
- Janick Bernet, SwissASP

[Janick]

> The reason you need a seperate context in 8.1 is that we're

multithreading on the JVM. Without a context per thread, threads would
run wild over each others objects (cursors etc) closing them, and other
bad things.

My main question regarding this is (which is not answered in then docu
and not apperant in the examples): do you need a seperate context
*class* per procedure or is it enough to have a seperate context
*instances*. I'm afraid its the first one, but I just can't really
imagine why, because thread-safety can easily be achieved on different
instance of the same class.
Anyone has more insight on the db2-sqlj-internals?

Regards
- Janick Bernet, SwissASP

[Janick]

> The reason you need a seperate context in 8.1 is that we're

multithreading on the JVM. Without a context per thread, threads would
run wild over each others objects (cursors etc) closing them, and other
bad things.

My main question regarding this is (which is not answered in then docu
and not apperant in the examples): do you need a seperate context
*class* per procedure or is it enough to have a seperate context
*instances*. I'm afraid its the first one, but I just can't really
imagine why, because thread-safety can easily be achieved on different
instance of the same class.
Anyone has more insight on the db2-sqlj-internals?

Regards
- Janick Bernet, SwissASP

[Sean McKeough]

The context as it was created/attained by the SP pre v8 was an SQLJ
static entry (everyone got the same context). If you're running non
threadsafe you can get away with a single instance of this connection
object.

Janick wrote:

The reason you need a seperate context in 8.1 is that we're
multithreading on the JVM. Without a context per thread, threads would
run wild over each others objects (cursors etc) closing them, and other
bad things.

My main question regarding this is (which is not answered in then docu
and not apperant in the examples): do you need a seperate context
*class* per procedure or is it enough to have a seperate context
*instances*. I'm afraid its the first one, but I just can't really
imagine why, because thread-safety can easily be achieved on different
instance of the same class.
Anyone has more insight on the db2-sqlj-internals?

Regards
- Janick Bernet, SwissASP

[Sean McKeough]

The context as it was created/attained by the SP pre v8 was an SQLJ
static entry (everyone got the same context). If you're running non
threadsafe you can get away with a single instance of this connection
object.

Janick wrote:

The reason you need a seperate context in 8.1 is that we're
multithreading on the JVM. Without a context per thread, threads would
run wild over each others objects (cursors etc) closing them, and other
bad things.

My main question regarding this is (which is not answered in then docu
and not apperant in the examples): do you need a seperate context
*class* per procedure or is it enough to have a seperate context
*instances*. I'm afraid its the first one, but I just can't really
imagine why, because thread-safety can easily be achieved on different
instance of the same class.
Anyone has more insight on the db2-sqlj-internals?

Regards
- Janick Bernet, SwissASP

[Janick]

Hmm, Ok. My last chance then to get the thing working threadsafe would
be if the following compiled. Why exactly is that code rejected by the
sqlj-precompiler with the message below?

protected static void execute(ConnectionContext context, Connection
con, (...))
{
#sql [context] { som query };
}

Error: Connection context must have been declared with #sql context
....
It can not be declared as a ConnectionContext.

Maybe its possible to get sqlj to ignore this, as the
ConnectionContext passed would effectively be a "correct"
ConnectionContext declared using "#sql context ...".
Sean McKeough <mc******@nospam.ca.ibm.com> wrote in message news:<c7**********@hanover.torolab.ibm.com>...

The context as it was created/attained by the SP pre v8 was an SQLJ
static entry (everyone got the same context). If you're running non
threadsafe you can get away with a single instance of this connection
object.

[Sean McKeough]

Sadly I do not know the inner workings of sqlj well (I only know the
pieces that caused java routines to break once we moved to
threaded)...sorry. :-(

Janick wrote:

Hmm, Ok. My last chance then to get the thing working threadsafe would
be if the following compiled. Why exactly is that code rejected by the
sqlj-precompiler with the message below?

protected static void execute(ConnectionContext context, Connection
con, (...))
{
#sql [context] { som query };
}

Error: Connection context must have been declared with #sql context
...
It can not be declared as a ConnectionContext.

Maybe its possible to get sqlj to ignore this, as the
ConnectionContext passed would effectively be a "correct"
ConnectionContext declared using "#sql context ...".
Sean McKeough <mc******@nospam.ca.ibm.com> wrote in message news:<c7**********@hanover.torolab.ibm.com>...

The context as it was created/attained by the SP pre v8 was an SQLJ
static entry (everyone got the same context). If you're running non
threadsafe you can get away with a single instance of this connection
object.

[Janick]

Sean McKeough <mc******@nospam.ca.ibm.com> wrote in message news:<c7**********@hanover.torolab.ibm.com>...

Sadly I do not know the inner workings of sqlj well (I only know the
pieces that caused java routines to break once we moved to
threaded)...sorry. :-(

No Problem, thanks for your help so far.

I decided to do a workaround to achieve what I want. I defined the
method in sqlj as enforced by the precompiler using a dedicated
ConnectionContext like this:

#sql context MyProcContext;
...
public static void execute(MyProcContext context, ...);
{
#sql context { some query };
}

And then replace MyProcContext with a general ConnectionContext in the
java-file generated by the precompiler. This way I can call the
execute() method from other procedures with their corresponding
ConnectionContexts as parameter.

I still wonder why the sqlj-precompiler doesn't allow we to define a
general ConnectionContext to use for executing SQL, as this is
completly legal and doesn't raise an exception if done in the
java-source. In the generated source all contexts are even accessed as
a simple ConnectionContext:

sqlj.runtime.ConnectionContext __sJT_connCtx = context;

Anyway, it works now. Hope it will still work on next Fixpack,
though... ;)