create procedure problem - db2 8.2.3 (DPF) on AIX - help?

[peteh]

Hi All;
I have what I hope is a simple environmental problem. I have some SQL
that creates a VERY simple procedure. When the create is executed from
Quest (from a Windows client) - no problem. As soon as I go to the
command line (on the actual DB server), I get a strange (and somewhat
frightening) sqlcode -1007 (corrupt tablespace!?). I'm pretty sure it
has something to do with CLP/statement terminator behavior, but cannot
find the recipe for success. Any help really appreciated!

Here's the SQL that runs fine from Windows/Quest:

CREATE PROCEDURE DVLXTRCT_TEST_ECHO_SP
(IN P_ECHO_TEXT CHARACTER(50)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
READS SQL DATA
INHERIT SPECIAL REGISTERS
pgm: begin
DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
values(P_Echo_Text)
;
-- to return result set, do not CLOSE cursor
OPEN c1;
end;
================================================== =======
================================================== =======
Here is my command line session with the sqlcode:
--db2 connect to xxxxx

Database Connection Information

Database server = DB2/AIX64 8.2.3
SQL authorization ID = xxxxxxxx
Local database alias = xxxxxxxxxxx

--db2 -atf pete_test_proc.ddl
SQLCA Information

sqlcaid : SQLCA sqlcabc: 136 sqlcode: 1007 sqlerrml: 0
sqlerrmc:
sqlerrp : SQL08023
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
explanation of sqlcode -1007
----------------------------------------------------------
User Response:

Discontinue use of the object or table space. Contact IBM service
to inspect the object and the table space.

sqlcode : -1007

================================================== ==================
================================================== ==================

I have tried replacing the semi-colons with @, but get the same error.
Help?!

Pete H

[Raj]

I replaced the semi column with an @ and ran it as db2 -td@ -vsf
proc.sql and it compiles succesfully. But the error you got it
weird.... try runnning inspect or db2dart on the db

[Serge Rielau]

Raj wrote:

I replaced the semi column with an @ and ran it as db2 -td@ -vsf
proc.sql and it compiles succesfully. But the error you got it
weird.... try runnning inspect or db2dart on the db

Pete,

Are you sure you didn't misread the SQLCODE? The parser spits
essentially two error codes -104 (syntax error) and -007:
SQL0007N The character "<character>" following "<text>" is
not valid.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[peteh]

Hi Serge;
Below is the actual screen print from my AIX command line:
--db2 -atf pete_test_proc.ddl
SQLCA Information
sqlcaid : SQLCA sqlcabc: 136 sqlcode: 1007 sqlerrml: 0
sqlerrmc:
sqlerrp : SQL08023
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:

Is CLP doing something funky to SQLCA? That might be even more
frightening than a corrupt tables space :-o
Pete H
------------------------------------------------------
Serge Rielau wrote:

Raj wrote:

I replaced the semi column with an @ and ran it as db2 -td@ -vsf
proc.sql and it compiles succesfully. But the error you got it
weird.... try runnning inspect or db2dart on the db

Pete,

Are you sure you didn't misread the SQLCODE? The parser spits
essentially two error codes -104 (syntax error) and -007:
SQL0007N The character "<character>" following "<text>" is
not valid.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[peteh]

Thanks Raj;
I too tried the @ replacement and got the same error. Is this what you
ran (below)?
================================================== ==========
--db2 connect to xxxxxxxx

Database Connection Information

Database server = DB2/AIX64 8.2.3
SQL authorization ID = xxxxxxxx
Local database alias = xxxxxxxx

--db2 -atf dvlxtrct.pete_test_proc.ddl
SQLCA Information

sqlcaid : SQLCA sqlcabc: 136 sqlcode: 1007 sqlerrml: 0
sqlerrmc:
sqlerrp : SQL08023
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:

--more dvlxtrct.pete_test_proc.ddl

CREATE PROCEDURE DVLXTRCT_TEST_ECHO_SP
(IN P_ECHO_TEXT CHARACTER(50)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
READS SQL DATA
INHERIT SPECIAL REGISTERS
pgm: begin
DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
values(P_Echo_Text)@
OPEN c1@
end@
=================================================
Raj wrote:

I replaced the semi column with an @ and ran it as db2 -td@ -vsf
proc.sql and it compiles succesfully. But the error you got it
weird.... try runnning inspect or db2dart on the db

[Raj]

This is the proc.sql
----------------------
CREATE PROCEDURE DVLXTRCT_TEST_ECHO_SP
(IN P_ECHO_TEXT CHARACTER(50)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
READS SQL DATA
INHERIT SPECIAL REGISTERS
pgm: begin
DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
values(P_Echo_Text)
;
-- to return result set, do not CLOSE cursor
OPEN c1;
end
@
----------------------------------------
the statement i used to compile: db2 -td@ -vsf proc.sql

[peteh]

Raj-
Thanks so much for your help. I have gotten the compile to work. It
appears to be my lack of knowleddge of CLP/statement terminator
behavior and a misleading sqlcode that are the issues.

If I could ask you for one more favor, could you help me understand the
CLP options you used so that I can integrate DROP and GRANT statements
into what I'm submitting via CLP?

In effect, I want to do this:
DROP PROCEDURE DVLXTRCT_TEST_ECHO_SP2
;
CREATE PROCEDURE DVLXTRCT_TEST_ECHO_SP2
(IN P_ECHO_TEXT CHARACTER(50)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
READS SQL DATA
INHERIT SPECIAL REGISTERS
pgm: begin
DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
values(P_Echo_Text)
;
-- to return result set, do not CLOSE cursor
OPEN c1;
end;

GRANT EXECUTE ON PROCEDURE DVLXTRCT_TEST_ECHO_SP2 TO PUBLIC
@

When I do, I get the following:
--db2 -td@ -vsf dvlxtrct.pete_test_proc.ddl
GRANT EXECUTE ON PROCEDURE DVLXTRCT_TEST_ECHO_SP2 TO PUBLIC
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "DROP PROCEDURE DVLXTRCT_TEST_ECHO_SP2
;" was
found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<space>". SQLSTATE=42601

It's clear I have to dig into the CLP environment and educate myself,
but GREATLY appreciate your help!

Pete H

===========================================
Raj wrote:

This is the proc.sql
----------------------
CREATE PROCEDURE DVLXTRCT_TEST_ECHO_SP
(IN P_ECHO_TEXT CHARACTER(50)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
READS SQL DATA
INHERIT SPECIAL REGISTERS
pgm: begin
DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
values(P_Echo_Text)
;
-- to return result set, do not CLOSE cursor
OPEN c1;
end
@
----------------------------------------
the statement i used to compile: db2 -td@ -vsf proc.sql

[Serge Rielau]

peteh wrote:

Raj-
Thanks so much for your help. I have gotten the compile to work. It
appears to be my lack of knowleddge of CLP/statement terminator
behavior and a misleading sqlcode that are the issues.

Let's fix this at the root.
At the very first line of your CLP script please add:
--#SET TERMINATOR @
Now all statements will be assumed completed when you type @:

CREATE TABLE T(c1 INT)@

CREATE PROCEDURE P(OUT a INT)
BEGIN
SET a = 5;
END@

.....
If you want to change the terminator back to ; just type:
--#SET TERMINATOR ;
Now you can use db2 -t <filename>

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[peteh]

Hi Serge;
Excellent! Thanks very much. Once again you have saved me from pulling
my (my ever-receding) hair out.

Pete H

Serge Rielau wrote:

peteh wrote:

Raj-
Thanks so much for your help. I have gotten the compile to work. It
appears to be my lack of knowleddge of CLP/statement terminator
behavior and a misleading sqlcode that are the issues.

Let's fix this at the root.
At the very first line of your CLP script please add:
--#SET TERMINATOR @
Now all statements will be assumed completed when you type @:

CREATE TABLE T(c1 INT)@

CREATE PROCEDURE P(OUT a INT)
BEGIN
SET a = 5;
END@

....
If you want to change the terminator back to ; just type:
--#SET TERMINATOR ;
Now you can use db2 -t <filename>

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/