how to update sqlj routine source in catalogs ?

[Jean-Marc Blaise]

Hi,

The dev center calls sqlj.DB2_UPDATEJARINFO
('JMARC.JMB','JMB','file:JMB.sqlj') to update the sqlj routine source.

I tried in CLP from the directory containing jar and sqlj files (Windows 8.2
FP9):

db2 connect to sample
db2 call sqlj.DB2_UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0444N Routine "SQLJ.DB2_UPDATEJARINFO" (specific name "") is
implemented with
code in library or path "", function "" which cannot be accessed. Reason
code: "". SQLSTATE=42724

db2 call sqlj.UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0804N The application program parameters for the current request are
not
valid. Reason code "109". If a host variable or SQLVAR in the SQLDA is
invalid then: host variable/SQLVAR number = "", SQLTYPE = "", SQLLEN = "",
host variable/SQLVAR type = "". SQLSTATE=07002

Basically, I have lost half a day trying to get rid of the deployment tool
and do scripting ... and I'm again fighting against the lack of
documentation in this area.
All I have found on googles is related to OS/390 and this is very non
explicit in terms of SQL codes !!!

:-((((((((((((((((((

Regards,

JM

[Sean McKeough]

Did you try qualifying the path to the file?

Jean-Marc Blaise wrote:

Hi,

The dev center calls sqlj.DB2_UPDATEJARINFO
('JMARC.JMB','JMB','file:JMB.sqlj') to update the sqlj routine source.

I tried in CLP from the directory containing jar and sqlj files (Windows 8.2
FP9):

db2 connect to sample
db2 call sqlj.DB2_UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0444N Routine "SQLJ.DB2_UPDATEJARINFO" (specific name "") is
implemented with
code in library or path "", function "" which cannot be accessed. Reason
code: "". SQLSTATE=42724

db2 call sqlj.UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0804N The application program parameters for the current request are
not
valid. Reason code "109". If a host variable or SQLVAR in the SQLDA is
invalid then: host variable/SQLVAR number = "", SQLTYPE = "", SQLLEN = "",
host variable/SQLVAR type = "". SQLSTATE=07002

Basically, I have lost half a day trying to get rid of the deployment tool
and do scripting ... and I'm again fighting against the lack of
documentation in this area.
All I have found on googles is related to OS/390 and this is very non
explicit in terms of SQL codes !!!

:-((((((((((((((((((

Regards,

JM

[Jean-Marc Blaise]

Hi Sean, yes I did:

H:\jmarc\testDB2\sqlj>db2 call sqlj.UPDATEJARINFO
('JMARC.JMB','JMB','file:///h:
\jmarc\testDB2\sqlj\JMB.sqlj')
SQL0804N The application program parameters for the current request are not
valid. Reason code "109". If a host variable or SQLVAR in the SQLDA is
invalid then: host variable/SQLVAR number = "", SQLTYPE = "", SQLLEN = "",
host variable/SQLVAR type = "". SQLSTATE=07002

H:\jmarc\testDB2\sqlj>db2 call sqlj.DB2_UPDATEJARINFO
('JMARC.JMB','JMB','file:/
//h:\jmarc\testDB2\sqlj\JMB.sqlj')
SQL0444N Routine "SQLJ.DB2_UPDATEJARINFO" (specific name "") is implemented
with code in library or path "", function "" which cannot be accessed.
Reason
code: "". SQLSTATE=42724

I don't understand why the later gives a SQL0444N ... it should work isn't
it ?

Regards,

Jean-Marc

"Sean McKeough" <mc******@nospam.ibm.com> a écrit dans le message de
news:42********@news3.prserv.net...

Did you try qualifying the path to the file?

Jean-Marc Blaise wrote:

Hi,

The dev center calls sqlj.DB2_UPDATEJARINFO
('JMARC.JMB','JMB','file:JMB.sqlj') to update the sqlj routine source.

I tried in CLP from the directory containing jar and sqlj files (Windows 8.2 FP9):

db2 connect to sample
db2 call sqlj.DB2_UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0444N Routine "SQLJ.DB2_UPDATEJARINFO" (specific name "") is
implemented with
code in library or path "", function "" which cannot be accessed. Reason code: "". SQLSTATE=42724

db2 call sqlj.UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0804N The application program parameters for the current request are not
valid. Reason code "109". If a host variable or SQLVAR in the SQLDA is
invalid then: host variable/SQLVAR number = "", SQLTYPE = "", SQLLEN = "", host variable/SQLVAR type = "". SQLSTATE=07002

Basically, I have lost half a day trying to get rid of the deployment tool and do scripting ... and I'm again fighting against the lack of
documentation in this area.
All I have found on googles is related to OS/390 and this is very non
explicit in terms of SQL codes !!!

:-((((((((((((((((((

Regards,

JM

[Sean McKeough]

These two things are slightly different versions of each other...I'm
following up with the developer that wrote the DB2_ version of this
recently...will let you know what I hear.

Jean-Marc Blaise wrote:

Hi Sean, yes I did:

H:\jmarc\testDB2\sqlj>db2 call sqlj.UPDATEJARINFO
('JMARC.JMB','JMB','file:///h:
\jmarc\testDB2\sqlj\JMB.sqlj')
SQL0804N The application program parameters for the current request are not
valid. Reason code "109". If a host variable or SQLVAR in the SQLDA is
invalid then: host variable/SQLVAR number = "", SQLTYPE = "", SQLLEN = "",
host variable/SQLVAR type = "". SQLSTATE=07002

H:\jmarc\testDB2\sqlj>db2 call sqlj.DB2_UPDATEJARINFO
('JMARC.JMB','JMB','file:/
//h:\jmarc\testDB2\sqlj\JMB.sqlj')
SQL0444N Routine "SQLJ.DB2_UPDATEJARINFO" (specific name "") is implemented
with code in library or path "", function "" which cannot be accessed.
Reason
code: "". SQLSTATE=42724

I don't understand why the later gives a SQL0444N ... it should work isn't
it ?

Regards,

Jean-Marc

"Sean McKeough" <mc******@nospam.ibm.com> a écrit dans le message de
news:42********@news3.prserv.net...

Did you try qualifying the path to the file?

Jean-Marc Blaise wrote:

Hi,

The dev center calls sqlj.DB2_UPDATEJARINFO
('JMARC.JMB','JMB','file:JMB.sqlj') to update the sqlj routine source.

I tried in CLP from the directory containing jar and sqlj files (Windows
8.2
FP9):

db2 connect to sample
db2 call sqlj.DB2_UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0444N Routine "SQLJ.DB2_UPDATEJARINFO" (specific name "") is
implemented with
code in library or path "", function "" which cannot be accessed.
Reason
code: "". SQLSTATE=42724

db2 call sqlj.UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0804N The application program parameters for the current request
are
not
valid. Reason code "109". If a host variable or SQLVAR in the SQLDA is
invalid then: host variable/SQLVAR number = "", SQLTYPE = "", SQLLEN =
"",
host variable/SQLVAR type = "". SQLSTATE=07002

Basically, I have lost half a day trying to get rid of the deployment
tool
and do scripting ... and I'm again fighting against the lack of
documentation in this area.
All I have found on googles is related to OS/390 and this is very non
explicit in terms of SQL codes !!!

:-((((((((((((((((((

Regards,

JM

[amurchis]

I'm going to start by saying that noone should be using those functions
outside of the development center. Those functions are implemented
specifically for their use (with the debugger), and DB2 does not support
their use outside of their intended use within the development center.
Customers should not use this function directly because they will not be
supported.

With that said, SQLJ.UPDATEJARINFO is functionality that is built into
the JDBC driver. The CALL is intercepted and interpreted into an API
call, and the specified file is read into a BLOB parameter before
sending it over to the server. This functionality is not implemented in
the CLP because it is not intended for use from the CLP.

SQLJ.DB2_UPDATEJARINFO is an actual cataloged procedure that directly
takes a BLOB as a parameter, and then calls the API inside the
procedure's body. That is, you must pass a BLOB directly into the
procedure call -- specifying a filename will pass the string value in as
a VARCHAR, as it doesn't know you're trying to pass in a BLOB. It's not
actually possible to open a file as a BLOB in CLP. From a JAVA
application, it would look something like this:

stmt = conn.prepareCall("CALL SQLJ.DB2_UPDATEJARINFO(?, ?, ?)");
stmt.setString(1, jar_id);
stmt.setString(2, className);
fileStream = new FileInputStream(classFilename);
stmt.setBinaryStream(3, fileStream, fileStream.available());
stmt.execute();

Again, I reiterate, these functions are not supported outside of the
development center environment.

Hi,

The dev center calls sqlj.DB2_UPDATEJARINFO
('JMARC.JMB','JMB','file:JMB.sqlj') to update the sqlj routine source.

I tried in CLP from the directory containing jar and sqlj files (Windows 8.2
FP9):

db2 connect to sample
db2 call sqlj.DB2_UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0444N Routine "SQLJ.DB2_UPDATEJARINFO" (specific name "") is
implemented with
code in library or path "", function "" which cannot be accessed. Reason
code: "". SQLSTATE=42724

db2 call sqlj.UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0804N The application program parameters for the current request are
not
valid. Reason code "109". If a host variable or SQLVAR in the SQLDA is
invalid then: host variable/SQLVAR number = "", SQLTYPE = "", SQLLEN = "",
host variable/SQLVAR type = "". SQLSTATE=07002

Basically, I have lost half a day trying to get rid of the deployment tool
and do scripting ... and I'm again fighting against the lack of
documentation in this area.
All I have found on googles is related to OS/390 and this is very non
explicit in terms of SQL codes !!!

:-((((((((((((((((((

Regards,

JM

[Jean-Marc Blaise]

Thanks a lot for your detailed explanations.

Jean-Marc

"amurchis" <am******@ca.ibm.com> a écrit dans le message de
news:42********@news3.prserv.net...

I'm going to start by saying that noone should be using those functions
outside of the development center. Those functions are implemented
specifically for their use (with the debugger), and DB2 does not support
their use outside of their intended use within the development center.
Customers should not use this function directly because they will not be
supported.

With that said, SQLJ.UPDATEJARINFO is functionality that is built into
the JDBC driver. The CALL is intercepted and interpreted into an API
call, and the specified file is read into a BLOB parameter before
sending it over to the server. This functionality is not implemented in
the CLP because it is not intended for use from the CLP.

SQLJ.DB2_UPDATEJARINFO is an actual cataloged procedure that directly
takes a BLOB as a parameter, and then calls the API inside the
procedure's body. That is, you must pass a BLOB directly into the
procedure call -- specifying a filename will pass the string value in as
a VARCHAR, as it doesn't know you're trying to pass in a BLOB. It's not
actually possible to open a file as a BLOB in CLP. From a JAVA
application, it would look something like this:

stmt = conn.prepareCall("CALL SQLJ.DB2_UPDATEJARINFO(?, ?, ?)");
stmt.setString(1, jar_id);
stmt.setString(2, className);
fileStream = new FileInputStream(classFilename);
stmt.setBinaryStream(3, fileStream, fileStream.available());
stmt.execute();

Again, I reiterate, these functions are not supported outside of the
development center environment.

Hi,

The dev center calls sqlj.DB2_UPDATEJARINFO
('JMARC.JMB','JMB','file:JMB.sqlj') to update the sqlj routine source.

I tried in CLP from the directory containing jar and sqlj files (Windows 8.2 FP9):

db2 connect to sample
db2 call sqlj.DB2_UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0444N Routine "SQLJ.DB2_UPDATEJARINFO" (specific name "") is
implemented with
code in library or path "", function "" which cannot be accessed. Reason code: "". SQLSTATE=42724

db2 call sqlj.UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0804N The application program parameters for the current request are not
valid. Reason code "109". If a host variable or SQLVAR in the SQLDA is
invalid then: host variable/SQLVAR number = "", SQLTYPE = "", SQLLEN = "", host variable/SQLVAR type = "". SQLSTATE=07002

Basically, I have lost half a day trying to get rid of the deployment tool and do scripting ... and I'm again fighting against the lack of
documentation in this area.
All I have found on googles is related to OS/390 and this is very non
explicit in terms of SQL codes !!!

:-((((((((((((((((((

Regards,

JM

[Jean-Marc Blaise]

Hi,

There is this technote "Steps to create a procedure from a JAR file" that
explains all steps ... except how not to loose the source ...
http://www-1.ibm.com/support/docview...=utf-8&lang=en

I don't find very consistent the fact to be able to CALL SQLJ.INSTALL_JAR
from CLP and not be able to update the source from CLP.
The reason I have to go outside the Development Center is we've got serious
problems with the deployment tool GUI. Today, we tried deploying about 128
SQLJ procs, not much. We exported as a export zip file, then deployed all
jar files with sources. The GUI was stuck around 79 procs (30 minutes later
!!!), we got an empty javacore...txt on the server. On the client, I checked
the JVM that was more than 210 MB (customer is on FP8 client / FP8 or FP9
for server, Windows 2003).

Problems encountered with the Dev Center deploy tool:

* deployment wizard / options / error handling section: you cannot select
"Ignore errors" unless you select any "Duplicate handling button first".
Basically this makes the design non understandable.

* you can deploy with binaries if available and input the source. But you
cannot simply deploy and skip this sqlj phase and customization. My script
is intended to start from a exported zip file, then for each proc, call
sqlj.install_jar, reference proc, input source, generate binds from the .ser
file using db2sqljbind.

* in the output window, deploy is too much verbose ... you cannot event
redirect this output to a file. Why not input a single line for each proc
deployed successfully, and the all package for each proc that has failed ?

I even tested at home on FP9 and this fixpack seems to break the client
deployment tool: I get the message "No project file found" ... I started
from scratch, removing all DC files to see if it changes anything and it
didn't.

Thanks again for your detailed explanation.

Jean-Marc
"amurchis" <am******@ca.ibm.com> a écrit dans le message de
news:42********@news3.prserv.net...

I'm going to start by saying that noone should be using those functions
outside of the development center. Those functions are implemented
specifically for their use (with the debugger), and DB2 does not support
their use outside of their intended use within the development center.
Customers should not use this function directly because they will not be
supported.

With that said, SQLJ.UPDATEJARINFO is functionality that is built into
the JDBC driver. The CALL is intercepted and interpreted into an API
call, and the specified file is read into a BLOB parameter before
sending it over to the server. This functionality is not implemented in
the CLP because it is not intended for use from the CLP.

SQLJ.DB2_UPDATEJARINFO is an actual cataloged procedure that directly
takes a BLOB as a parameter, and then calls the API inside the
procedure's body. That is, you must pass a BLOB directly into the
procedure call -- specifying a filename will pass the string value in as
a VARCHAR, as it doesn't know you're trying to pass in a BLOB. It's not
actually possible to open a file as a BLOB in CLP. From a JAVA
application, it would look something like this:

stmt = conn.prepareCall("CALL SQLJ.DB2_UPDATEJARINFO(?, ?, ?)");
stmt.setString(1, jar_id);
stmt.setString(2, className);
fileStream = new FileInputStream(classFilename);
stmt.setBinaryStream(3, fileStream, fileStream.available());
stmt.execute();

Again, I reiterate, these functions are not supported outside of the
development center environment.

Hi,

The dev center calls sqlj.DB2_UPDATEJARINFO
('JMARC.JMB','JMB','file:JMB.sqlj') to update the sqlj routine source.

I tried in CLP from the directory containing jar and sqlj files (Windows 8.2 FP9):

db2 connect to sample
db2 call sqlj.DB2_UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0444N Routine "SQLJ.DB2_UPDATEJARINFO" (specific name "") is
implemented with
code in library or path "", function "" which cannot be accessed. Reason code: "". SQLSTATE=42724

db2 call sqlj.UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0804N The application program parameters for the current request are not
valid. Reason code "109". If a host variable or SQLVAR in the SQLDA is
invalid then: host variable/SQLVAR number = "", SQLTYPE = "", SQLLEN = "", host variable/SQLVAR type = "". SQLSTATE=07002

Basically, I have lost half a day trying to get rid of the deployment tool and do scripting ... and I'm again fighting against the lack of
documentation in this area.
All I have found on googles is related to OS/390 and this is very non
explicit in terms of SQL codes !!!

:-((((((((((((((((((

Regards,

JM

[amurchis]

I just wanted to expand on this to state that my statement on the use of
the SQLJ-schema'd procedures ONLY applies to the updatejarinfo
functions. This function has never been documented (that I am aware
of). It's specific use is for the development center.

Currently, none of the SQLJ.DB2_* procedures are documented, but given
that there is currently no other alternative for those who want to use
JCC, DB2 will have to document them at some point unless an SQLJ
standard for use in JCC is decided upon.

I am not involved in any development with the development center, so I
cannot help with any problems you are experiencing while using it. I am
familiar with the DB2 SP/UDF infrastructure itself -- that is, what goes
on in the actual DB2 engine. I have not personally used the development
center.

amurchis wrote:

I'm going to start by saying that noone should be using those functions
outside of the development center. Those functions are implemented
specifically for their use (with the debugger), and DB2 does not support
their use outside of their intended use within the development center.
Customers should not use this function directly because they will not be
supported.

With that said, SQLJ.UPDATEJARINFO is functionality that is built into
the JDBC driver. The CALL is intercepted and interpreted into an API
call, and the specified file is read into a BLOB parameter before
sending it over to the server. This functionality is not implemented in
the CLP because it is not intended for use from the CLP.

SQLJ.DB2_UPDATEJARINFO is an actual cataloged procedure that directly
takes a BLOB as a parameter, and then calls the API inside the
procedure's body. That is, you must pass a BLOB directly into the
procedure call -- specifying a filename will pass the string value in as
a VARCHAR, as it doesn't know you're trying to pass in a BLOB. It's not
actually possible to open a file as a BLOB in CLP. From a JAVA
application, it would look something like this:

stmt = conn.prepareCall("CALL SQLJ.DB2_UPDATEJARINFO(?, ?, ?)");
stmt.setString(1, jar_id);
stmt.setString(2, className);
fileStream = new FileInputStream(classFilename);
stmt.setBinaryStream(3, fileStream, fileStream.available());
stmt.execute();

Again, I reiterate, these functions are not supported outside of the
development center environment.

Hi,

The dev center calls sqlj.DB2_UPDATEJARINFO
('JMARC.JMB','JMB','file:JMB.sqlj') to update the sqlj routine source.

I tried in CLP from the directory containing jar and sqlj files
(Windows 8.2
FP9):

db2 connect to sample
db2 call sqlj.DB2_UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0444N Routine "SQLJ.DB2_UPDATEJARINFO" (specific name "") is
implemented with
code in library or path "", function "" which cannot be accessed. Reason
code: "". SQLSTATE=42724

db2 call sqlj.UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj')
==> SQL0804N The application program parameters for the current
request are
not
valid. Reason code "109". If a host variable or SQLVAR in the SQLDA is
invalid then: host variable/SQLVAR number = "", SQLTYPE = "", SQLLEN
= "",
host variable/SQLVAR type = "". SQLSTATE=07002

Basically, I have lost half a day trying to get rid of the deployment
tool
and do scripting ... and I'm again fighting against the lack of
documentation in this area.
All I have found on googles is related to OS/390 and this is very non
explicit in terms of SQL codes !!!

:-((((((((((((((((((

Regards,

JM