Passing And ARRAY to a Stored Procedure

[Viator]

Hi All;

I am working on project; where I need to call a DB2 stored procedure
(also to be written in the project) which will update/insert some
records in the database. The number of rows to be intserted are decide
at runtime and there is a constraint that the procedure should be
called only once.

So the questino is...
Can we pass a java array or a Collection using JDBC to DB2? If yes how
can we read that array in the DB2 stored procedure? We are using DB2
Connect to connect to DB2 server.


Thanks And Regards,
Viator

 

[Sean McKeough]

No, you can't use arrays as parameters to DB2 java routines...what about
using a declared global temp table instead? (Pass the table name into
the java stored proc).

 

[peteh]

You could pass the array as a varchar and parse it in the proc, but you
cannot pass it as an array or collection.

Pete H

 

[amurchis]

Java procedures in DB2 are not known for their performance. Please
consider using another language (SQL, C) if all this procedure is going
to do is execute some SQL based on somewhat trivial processing.

In my experience, the valid uses for JAVA routines are:

1) Complex operations that are difficult in other languages (accessing
non-text file resources)
2) Anything involving inheritance from a base wrapper class (of course)

Consider that JAVA routines always run in FENCED mode -- this is always
true in V8 even if it is cataloged as NOT FENCED due to issues with
hosting the JVM inside an agent -- and execute dynamic SQL statements.
Both are a significant performance hit.

My experience has been that LANGUAGE JAVA should only be used as a last
resort when no other language will do. JAVA itself is a nice language,
but it's not efficient to use it as an everyday procedure language in DB2.

 

[efiryago]

Another good reason for using java stored procs is back end code
portability maintenance. Of course, there are still issues related to
SQL and jdbc implementation differences between database vendors, but
if your java stored procs are mainly used for driving SQL (not for
processing in jvm) the performance shouldn't be a problem. So we use
java stored procs to support our software product for both DB2 and
Oracle from a single code base.

Regards,
-Eugene

 

[efiryago]

Another good reason for using java stored procs is back end code
portability maintenance. Of course, there are still issues related to
SQL and jdbc implementation differences between database vendors, but
if your java stored procs are mainly used for driving SQL (not for
processing in jvm) the performance shouldn't be a problem. So we use
java stored procs to support our software product for both DB2 and
Oracle from a single code base.

Regards,
-Eugene

 

[amitkumarch]

Is there an option of using SQL DA for the same. Well we are not
writing procedures in Java but in COBOL. Client is only to be written
in Java. Is there a way to manipulate SQL DA in Java and passing an
ARRAY like structure using that. If it is at all possible how can it be
manipulated in DB2 side using cobol SPs.

Thanks And Regards
Viator

 

[amurchis]

Provided your executing more than just one or two statements, then yes,
that is true. Then again, I'd never recommend ANYONE executing a single
statement in any procedure, regardless of language. DB2 has a statement
cache (as opposed to some DBM that don't) so you're doubling the
overhead -- call + statement -- if all you're executing is one SQL
statement.

Personally, though (and this is just from my experience) JAVA would be
my last recommendation from a purely performance standpoint. SQL or C
will outperform it every time. I honestly do recommend that if you can
write the procedure in another language, than do so.

 

[Sean McKeough]

No, dari style sps are only supported for C and COBOL.