Adding a parameter to a Java SP

[Steve Smith]

I need to add an additional String parameter to a Java SP (DB2 8.1 0 using
DB2 Development Center - FP4).

Everytime I do this I get the following error message when building:-
"G2REPORT.G2REP _INTERMEDIARY_S S" contains an invalidly formatted
library/function identification in the EXTERNAL NAME clause. LINE
NUMBER=12. SQLSTATE=42878

G2REPORT.G2REP_ Intermediary_SS - Build failed.
G2REPORT.G2REP_ Intermediary_SS - Roll back completed successfully.

The current parameters for the SP look as below - which works/builds fine.
As soon as I add an additional String parameter (immediately before the
resultset) I get the error above when building. Any ideas ?, are there any
limitations on size/number of parameters a Java SP can take ?

Thanks

public class G2REP_Intermedi ary_SS
{

public static void g2REP_Intermedi ary_SS ( String prmRoleName,

String prmFSAReference No,

String prmRelationship Type,

String prmHeadIntermed iaryNo,

String prmIntermediary No,

String prmIntermediary Name,

String prmFSAChngOptnC ode,

String prmClientMoneyO ptn,

ResultSet[] rs ) throws SQLException, Exception

[Sean McKeough]

Steve,

Can you post the create procedure statement, and anything that appears
in the db2diag.log during the build? One possibility could be that the
old version of the class is loaded, and is preventing the new jar from
being laid down...you might want to recycle db2 and retry as a sanity check.

Steve Smith wrote:

I need to add an additional String parameter to a Java SP (DB2 8.1 0 using
DB2 Development Center - FP4).

Everytime I do this I get the following error message when building:-
"G2REPORT.G2REP _INTERMEDIARY_S S" contains an invalidly formatted
library/function identification in the EXTERNAL NAME clause. LINE
NUMBER=12. SQLSTATE=42878

G2REPORT.G2REP_ Intermediary_SS - Build failed.
G2REPORT.G2REP_ Intermediary_SS - Roll back completed successfully.

The current parameters for the SP look as below - which works/builds fine.
As soon as I add an additional String parameter (immediately before the
resultset) I get the error above when building. Any ideas ?, are there any
limitations on size/number of parameters a Java SP can take ?

Thanks

public class G2REP_Intermedi ary_SS
{

public static void g2REP_Intermedi ary_SS ( String prmRoleName,

String prmFSAReference No,

String prmRelationship Type,

String prmHeadIntermed iaryNo,

String prmIntermediary No,

String prmIntermediary Name,

String prmFSAChngOptnC ode,

String prmClientMoneyO ptn,

ResultSet[] rs ) throws SQLException, Exception

[Sean McKeough]

Steve,

Can you please e-mail me your jar file? I want to see what's up, but
can't hit the error path without having the jar file installed (we fail
because we can't find a matching jar file first)...

Thanks!

Steve Smith wrote:

Sean,

There's nothing in db2diag.log

The output from the build (including the create proc stmt is attached).

We have stopped & restarted DB2 a few times too. Doesn't seem to make any
difference. I've also played around with the parameter sizes a bit (as 8
time 32 is 256 - a suspicious number if ever I saw one). That doesn't seem
to make any difference either.

Steve

"Sean McKeough" <mc******@nospa m.ca.ibm.com> wrote in message
news:bp******** **@hanover.toro lab.ibm.com...

Steve,

Can you post the create procedure statement, and anything that appears
in the db2diag.log during the build? One possibility could be that the
old version of the class is loaded, and is preventing the new jar from
being laid down...you might want to recycle db2 and retry as a sanity

check.

Steve Smith wrote:

I need to add an additional String parameter to a Java SP (DB2 8.1 0
using
DB2 Development Center - FP4).

Everytime I do this I get the following error message when building:-
"G2REPORT.G2 REP_INTERMEDIAR Y_SS" contains an invalidly formatted
library/function identification in the EXTERNAL NAME clause. LINE
NUMBER=12. SQLSTATE=42878

G2REPORT.G2R EP_Intermediary _SS - Build failed.
G2REPORT.G2R EP_Intermediary _SS - Roll back completed successfully.

The current parameters for the SP look as below - which works/builds
fine.
As soon as I add an additional String parameter (immediately before the
resultset) I get the error above when building. Any ideas ?, are there
any
limitation s on size/number of parameters a Java SP can take ?

Thanks

public class G2REP_Intermedi ary_SS
{

public static void g2REP_Intermedi ary_SS ( String prmRoleName,

String prmFSAReference No,

String prmRelationship Type,

String prmHeadIntermed iaryNo,

String prmIntermediary No,

String prmIntermediary Name,

String prmFSAChngOptnC ode,

String prmClientMoneyO ptn,

ResultSet[] rs ) throws SQLException, Exception

G2REPORT.G2REP_ Intermediary_SS - Build started.
C:\PROGRA~1\IBM \SQLLIB\java\jd k\bin\javac -classpath ".;C:\PROGRA~1\ IBM\SQLLIB\java \db2java.zip;C: \PROGRA~1\IBM\S QLLIB\java\runt ime.zip;C:\PROG RA~1\IBM\SQLLIB \java\sqlj.zip" "G2REP_Intermed iary_SS.java"
G2REPORT.G2REP_ Intermediary_SS - Javac completed.
C:\PROGRA~1\IBM \SQLLIB\java\jd k\bin\jar cf SQL310180410077 90.jar G2REP_Intermedi ary_SS.class
G2REPORT.G2REP_ Intermediary_SS - Jar file created.
Call sqlj.install_ja r ('file:///C|/Documents and Settings/A261009/Application Data/IBM/DB2/DC/Projects/bld106931592304 7/SQL310180410077 90.jar', '"G2REPORT".SQL 31018041007790' , 0)
G2REPORT.G2REP_ Intermediary_SS - sqlj.install_ja r using jar name "G2REPORT".SQL3 1018041007790 completed.
Call sqlj.refresh_cl asses( )
G2REPORT.G2REP_ Intermediary_SS - sqlj.refresh_cl asses completed.
Call sqlj.updatejari nfo ('"G2REPORT".SQ L31018041007790 ', 'G2REP_Intermed iary_SS', 'file:///C|/Documents and Settings/A261009/Application Data/IBM/DB2/DC/Projects/bld106931592304 7/dc_1')
G2REPORT.G2REP_ Intermediary_SS - Source updated.
CREATE PROCEDURE G2REPORT.G2REP_ Intermediary_SS ( IN prmRoleName CHARACTER(32),
IN prmFSAReference No CHARACTER(32),
IN prmRelationship Type CHARACTER(32),
IN prmHeadIntermed iaryNo CHARACTER(32),
IN prmIntermediary No CHARACTER(32),
IN prmIntermediary Name CHARACTER(32),
IN prmFSAChngOptnC ode CHARACTER(32),
IN prmClientMoneyO ptn CHARACTER(32),
IN prmTest1 CHARACTER(32) )
DYNAMIC RESULT SETS 1
NOT DETERMINISTIC
LANGUAGE Java
EXTERNAL NAME '"G2REPORT".SQL 31018041007790: G2REP_Intermedi ary_SS.g2REP_In termediary_SS'
FENCED
PARAMETER STYLE JAVA
[IBM][CLI Driver][DB2/6000] SQL0449N The statement defining routine "G2REPORT.G2REP _INTERMEDIARY_S S" contains an invalidly formatted library/function identification in the EXTERNAL NAME clause. LINE NUMBER=12. SQLSTATE=42878

G2REPORT.G2REP_ Intermediary_SS - Build failed.
G2REPORT.G2REP_ Intermediary_SS - Roll back completed successfully.

[Steve Smith]

Sean,

jar file attached.

Thanks
Steve
"Sean McKeough" <mc******@nospa m.ca.ibm.com> wrote in message
news:bp******** **@hanover.toro lab.ibm.com...

Steve,

Can you please e-mail me your jar file? I want to see what's up, but
can't hit the error path without having the jar file installed (we fail
because we can't find a matching jar file first)...

Thanks!

Steve Smith wrote:

Sean,

There's nothing in db2diag.log

The output from the build (including the create proc stmt is attached).

We have stopped & restarted DB2 a few times too. Doesn't seem to make any difference. I've also played around with the parameter sizes a bit (as 8
time 32 is 256 - a suspicious number if ever I saw one). That doesn't seem to make any difference either.

Steve

"Sean McKeough" <mc******@nospa m.ca.ibm.com> wrote in message
news:bp******** **@hanover.toro lab.ibm.com...

Steve,

Can you post the create procedure statement, and anything that appears
in the db2diag.log during the build? One possibility could be that the
old version of the class is loaded, and is preventing the new jar from
being laid down...you might want to recycle db2 and retry as a sanity

check.

Steve Smith wrote:

I need to add an additional String parameter to a Java SP (DB2 8.1 0

using

DB2 Development Center - FP4).

Everytime I do this I get the following error message when building:-
"G2REPORT.G2 REP_INTERMEDIAR Y_SS" contains an invalidly formatted
library/function identification in the EXTERNAL NAME clause. LINE
NUMBER=12. SQLSTATE=42878

G2REPORT.G2R EP_Intermediary _SS - Build failed.
G2REPORT.G2R EP_Intermediary _SS - Roll back completed successfully.

The current parameters for the SP look as below - which works/builds

fine.

As soon as I add an additional String parameter (immediately before the
resultset) I get the error above when building. Any ideas ?, are there

any

limitation s on size/number of parameters a Java SP can take ?

Thanks

public class G2REP_Intermedi ary_SS
{

public static void g2REP_Intermedi ary_SS ( String prmRoleName,

String prmFSAReference No,

String prmRelationship Type,

String prmHeadIntermed iaryNo,

String prmIntermediary No,

String prmIntermediary Name,

String prmFSAChngOptnC ode,

String prmClientMoneyO ptn,

ResultSet[] rs ) throws SQLException, Exception

G2REPORT.G2REP_ Intermediary_SS - Build started.
C:\PROGRA~1\IBM \SQLLIB\java\jd k\bin\javac -classpath ".;C:\PROGRA~1\ IBM\SQLLIB\java \db2java.zip;C: \PROGRA~1\IBM\S QLLIB\java\runt i
me.zip;C:\PROGR A~1\IBM\SQLLIB\ java\sqlj.zip" "G2REP_Intermed iary_SS.java" G2REPORT.G2REP_ Intermediary_SS - Javac completed.
C:\PROGRA~1\IBM \SQLLIB\java\jd k\bin\jar cf SQL310180410077 90.jar G2REP_Intermedi ary_SS.class G2REPORT.G2REP_ Intermediary_SS - Jar file created.
Call sqlj.install_ja r ('file:///C|/Documents and Settings/A261009/Application
Data/IBM/DB2/DC/Projects/bld106931592304 7/SQL310180410077 90.jar',
'"G2REPORT".SQL 31018041007790' , 0) G2REPORT.G2REP_ Intermediary_SS - sqlj.install_ja r using jar name "G2REPORT".SQL3 1018041007790 completed. Call sqlj.refresh_cl asses( )
G2REPORT.G2REP_ Intermediary_SS - sqlj.refresh_cl asses completed.
Call sqlj.updatejari nfo ('"G2REPORT".SQ L31018041007790 ', 'G2REP_Intermed iary_SS', 'file:///C|/Documents and
Settings/A261009/Application
Data/IBM/DB2/DC/Projects/bld106931592304 7/dc_1') G2REPORT.G2REP_ Intermediary_SS - Source updated.
CREATE PROCEDURE G2REPORT.G2REP_ Intermediary_SS ( IN prmRoleName CHARACTER(32), IN prmFSAReference No CHARACTER(32), IN prmRelationship Type CHARACTER(32), IN prmHeadIntermed iaryNo CHARACTER(32), IN prmIntermediary No CHARACTER(32), IN prmIntermediary Name CHARACTER(32), IN prmFSAChngOptnC ode CHARACTER(32), IN prmClientMoneyO ptn CHARACTER(32), IN prmTest1 CHARACTER(32) ) DYNAMIC RESULT SETS 1
NOT DETERMINISTIC
LANGUAGE Java
EXTERNAL NAME '"G2REPORT".SQL 31018041007790: G2REP_Intermedi ary_SS.g2REP_In termediary_SS' FENCED
PARAMETER STYLE JAVA
[IBM][CLI Driver][DB2/6000] SQL0449N The statement defining routine "G2REPORT.G2REP _INTERMEDIARY_S S" contains an invalidly formatted
library/function identification in the EXTERNAL NAME clause. LINE
NUMBER=12. SQLSTATE=42878
G2REPORT.G2REP_ Intermediary_SS - Build failed.
G2REPORT.G2REP_ Intermediary_SS - Roll back completed successfully.

[Sean McKeough]

Steve,

The install & create proc went ok for me on 8.2...I couldn't find a fp4
build to try this on. You may just need to move up...if fp4 doesn't work
for you, then you'll need to contact service to get a special build
containing whatever was done to fix this.

Steve Smith wrote:

Sean,

jar file attached.

Thanks
Steve
"Sean McKeough" <mc******@nospa m.ca.ibm.com> wrote in message
news:bp******** **@hanover.toro lab.ibm.com...

Steve,

Can you please e-mail me your jar file? I want to see what's up, but
can't hit the error path without having the jar file installed (we fail
because we can't find a matching jar file first)...

Thanks!

Steve Smith wrote:

Sean,

There's nothing in db2diag.log

The output from the build (including the create proc stmt is attached).

We have stopped & restarted DB2 a few times too. Doesn't seem to make
any
difference . I've also played around with the parameter sizes a bit (as 8
time 32 is 256 - a suspicious number if ever I saw one). That doesn't
seem
to make any difference either.

Steve

"Sean McKeough" <mc******@nospa m.ca.ibm.com> wrote in message
news:bp***** *****@hanover.t orolab.ibm.com. ..
Steve,

Can you post the create procedure statement, and anything that appears
in the db2diag.log during the build? One possibility could be that the
old version of the class is loaded, and is preventing the new jar from
being laid down...you might want to recycle db2 and retry as a sanity

check.
Steve Smith wrote:
>I need to add an additional String parameter to a Java SP (DB2 8.1 0

using
>DB2 Development Center - FP4).
>
>Everytim e I do this I get the following error message when building:-
>"G2REPORT. G2REP_INTERMEDI ARY_SS" contains an invalidly formatted
>library/function identification in the EXTERNAL NAME clause. LINE
>NUMBER=1 2. SQLSTATE=42878
>
>G2REPORT.G 2REP_Intermedia ry_SS - Build failed.
>G2REPORT.G 2REP_Intermedia ry_SS - Roll back completed successfully.
>
>The current parameters for the SP look as below - which works/builds

fine.
>As soon as I add an additional String parameter (immediately before the
>resultse t) I get the error above when building. Any ideas ?, are there

any
>limitation s on size/number of parameters a Java SP can take ?
>
>Thanks
>
>public class G2REP_Intermedi ary_SS
>{
>
>public static void g2REP_Intermedi ary_SS ( String prmRoleName,
>
>String prmFSAReference No,
>
>String prmRelationship Type,
>
>String prmHeadIntermed iaryNo,
>
>String prmIntermediary No,
>
>String prmIntermediary Name,
>
>String prmFSAChngOptnC ode,
>
>String prmClientMoneyO ptn,
>
>ResultSe t[] rs ) throws SQLException, Exception
>
>
G2REPORT.G2R EP_Intermediary _SS - Build started.
C:\PROGRA~1\ IBM\SQLLIB\java \jdk\bin\javac -classpath
".;C:\PROGRA~1\ IBM\SQLLIB\java \db2java.zip;C: \PROGRA~1\IBM\S QLLIB\java\runt i
me.zip;C:\PROGR A~1\IBM\SQLLIB\ java\sqlj.zip" "G2REP_Intermed iary_SS.java"
G2REPORT.G2R EP_Intermediary _SS - Javac completed.
C:\PROGRA~1\ IBM\SQLLIB\java \jdk\bin\jar cf SQL310180410077 90.jar
G2REP_Intermedi ary_SS.class
G2REPORT.G2R EP_Intermediary _SS - Jar file created.
Call sqlj.install_ja r ('file:///C|/Documents and
Settings/A261009/Application
Data/IBM/DB2/DC/Projects/bld106931592304 7/SQL310180410077 90.jar',
'"G2REPORT".SQL 31018041007790' , 0)
G2REPORT.G2R EP_Intermediary _SS - sqlj.install_ja r using jar name
"G2REPORT".SQL3 1018041007790 completed.
Call sqlj.refresh_cl asses( )
G2REPORT.G2R EP_Intermediary _SS - sqlj.refresh_cl asses completed.
Call sqlj.updatejari nfo ('"G2REPORT".SQ L31018041007790 ',
'G2REP_Intermed iary_SS', 'file:///C|/Documents and
Settings/A261009/Application
Data/IBM/DB2/DC/Projects/bld106931592304 7/dc_1')
G2REPORT.G2R EP_Intermediary _SS - Source updated.
CREATE PROCEDURE G2REPORT.G2REP_ Intermediary_SS ( IN prmRoleName
CHARACTER(32),
IN prmFSAReference No
CHARACTER(32),
IN prmRelationship Type
CHARACTER(32),
IN
prmHeadIntermed iaryNo CHARACTER(32),
IN prmIntermediary No
CHARACTER(32),
IN prmIntermediary Name
CHARACTER(32),
IN prmFSAChngOptnC ode
CHARACTER(32),
IN prmClientMoneyO ptn
CHARACTER(32),
IN prmTest1
CHARACTER(32) )
DYNAMIC RESULT SETS 1
NOT DETERMINISTIC
LANGUAGE Java
EXTERNAL NAME
'"G2REPORT".SQL 31018041007790: G2REP_Intermedi ary_SS.g2REP_In termediary_SS'
FENCED
PARAMETER STYLE JAVA
[IBM][CLI Driver][DB2/6000] SQL0449N The statement defining routine
"G2REPORT.G2REP _INTERMEDIARY_S S" contains an invalidly formatted
library/function identification in the EXTERNAL NAME clause. LINE
NUMBER=12. SQLSTATE=42878
G2REPORT.G2R EP_Intermediary _SS - Build failed.
G2REPORT.G2R EP_Intermediary _SS - Roll back completed successfully.