ignorant DB2 dilletante question: what's the syntax for a procedure...

[Joe Weinstein]

Hi all. Sorry for the basic question... What's the syntax for creating a procedure
which simply returns a cursor for "select * from foo"? I am completely illiterate
about DB2, and I'm just reproducing a JDBC problem for a customer. thanks in advance,
Joe Weinstein at BEA

[Joe Weinstein]

Joe Weinstein wrote:

Hi all. Sorry for the basic question... What's the syntax for creating a
procedure
which simply returns a cursor for "select * from foo"? I am completely
illiterate
about DB2, and I'm just reproducing a JDBC problem for a customer.
thanks in advance,
Joe Weinstein at BEA

found it:

String proc = "CREATE PROCEDURE FOO_SELECT ( ) "
+ " LANGUAGE SQL "
+ " RESULT SETS 1 "
+ " BEGIN "
+ " DECLARE C1 CURSOR WITH RETURN FOR "
+ " SELECT JOETEST.FOO, JOETEST.BAR "
+ " from JOETEST "
+ " ORDER BY JOETEST.BAR, JOETEST.FOO; "
+ " OPEN C1; "
+ " END ";

[Knut Stolze]

Joe Weinstein wrote:

Hi all. Sorry for the basic question... What's the syntax for creating a
procedure which simply returns a cursor for "select * from foo"? I am
completely illiterate about DB2, and I'm just reproducing a JDBC problem
for a customer. thanks in advance, Joe Weinstein at BEA

Have a look here:

http://publib.boulder.ibm.com/infoce...help/index.jsp

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Mark A]

> > Hi all. Sorry for the basic question... What's the syntax for creating a

procedure which simply returns a cursor for "select * from foo"? I am
completely illiterate about DB2, and I'm just reproducing a JDBC problem
for a customer. thanks in advance, Joe Weinstein at BEA

Have a look here:

http://publib.boulder.ibm.com/infoce...help/index.jsp

--
Knut Stolze

I believe he answered his own question in a subsequent post a long time ago.
You should have known that.

[Knut Stolze]

Mark A wrote:

> Hi all. Sorry for the basic question... What's the syntax for creating
> a procedure which simply returns a cursor for "select * from foo"? I am
> completely illiterate about DB2, and I'm just reproducing a JDBC
> problem for a customer. thanks in advance, Joe Weinstein at BEA

Have a look here:

http://publib.boulder.ibm.com/infoce...help/index.jsp

--
Knut Stolze

I believe he answered his own question in a subsequent post a long time
ago. You should have known that.

Yes, I saw that. However, I just got an email from him sayingthat the link
to the DB2 documentation was something he did not yet have. Sometimes it
can't hurt to provide some basic information too. ;-)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Joe Weinstein]

Knut Stolze wrote:

Joe Weinstein wrote:

Hi all. Sorry for the basic question... What's the syntax for creating a
procedure which simply returns a cursor for "select * from foo"? I am
completely illiterate about DB2, and I'm just reproducing a JDBC problem
for a customer. thanks in advance, Joe Weinstein at BEA

Have a look here:

http://publib.boulder.ibm.com/infoce...help/index.jsp

Thanks Knut! I *think* I have the syntax, but you could probably tell
I had no resources so your link will help!
My puzzle is that I believe my syntax is correct, and a program
using it was successful for a while and then without change on the
client it started failing, saying:

java.sql.SQLException: [BEA][DB2 JDBC Driver][DB2]The SQL procedure
FOO_SELECT was not created because of a compile error. Detailed
information can be found in the error log file P6352915.log.

There is no such log file on the client, and I'd like to know where
to tell the DBA where to look for it on the DBMS machine...
The Java code was:

String proc = "CREATE PROCEDURE FOO_SELECT ( ) "
+ " LANGUAGE SQL "
+ " RESULT SETS 1 "
+ " BEGIN "
+ " DECLARE C1 CURSOR WITH RETURN FOR "
+ " SELECT JOETEST.FOO, JOETEST.BAR "
+ " from JOETEST "
+ " ORDER BY JOETEST.BAR, JOETEST.FOO; "
+ " OPEN C1; "
+ " END ";

try{s.executeUpdate("DROP PROCEDURE FOO_SELECT");} catch (Exception ignore){}
s.executeUpdate(proc);

Assume a table, JOETEST( FOO INT, BAR INT )...

thanks in advance for any extra help,
Joe Weinstein at BEA

[peteh]

>>information can be found in the error log file P6352915.log.

There is no such log file on the client, and I'd like to know where
to tell the DBA where to look for it on the DBMS machine...

Hi Joe;
This log will be found in (or near) the server libraries that manage
routines, usually like $DB2HOME/sqllib/function/routine/sqlproc/$USER
where $DB2HOME is the root dir for DB2 and $USER is the userid that was
trying to create the proc.

HTH

Pete H

[Joe Weinstein]

peteh wrote:

information can be found in the error log file P6352915.log.
There is no such log file on the client, and I'd like to know where
to tell the DBA where to look for it on the DBMS machine...

Hi Joe;
This log will be found in (or near) the server libraries that manage
routines, usually like $DB2HOME/sqllib/function/routine/sqlproc/$USER
where $DB2HOME is the root dir for DB2 and $USER is the userid that was
trying to create the proc.

HTH

Pete H

Thank you very much for your assistance.

Joe Weinstein at BEA