Execute multiple statements in a Procedure

[Nabil]

I am new to DB2 and here is my situation.
I have 2 temp tables where I am trying to insert data (from 2 select
statements)

DECLARE v_t1 VARCHAR(50);
DECLARE v_t1 VARCHAR(50);

DECLARE stmt1 STATEMENT;
DECLARE stmt2 STATEMENT;

v_t1 = 'insert into session.t1 select p_key from tab1';
v_t2 = 'insert into session.t2 select c_key from tab2';

PREPARE stmt1 from v_t1;
EXECUTE stmt1;

--This is where it fails
PREPARE stmt2 from v_t2;
EXECUTE stmt2;
This is kind of how my Proc looks. When I go in the debug mode. I pass
the first EXECUTE and the moment it comes to the second one, it fails
and I get this error message...

A database manager error occurred.[IBM][CLI Driver][DB2/6000] SQL0518N
The statement named in the EXECUTE statement is not in a prepared state
or is a SELECT or VALUES statement. SQLSTATE=07003

----------------------

What am I doing wrong? I would really appreciate if someone could help
me out here.
Thanks in advance.

- Nabil

[Serge Rielau]

Nabil wrote:

I am new to DB2 and here is my situation.
I have 2 temp tables where I am trying to insert data (from 2 select
statements)

DECLARE v_t1 VARCHAR(50);
DECLARE v_t1 VARCHAR(50);

DECLARE stmt1 STATEMENT;
DECLARE stmt2 STATEMENT;

v_t1 = 'insert into session.t1 select p_key from tab1';
v_t2 = 'insert into session.t2 select c_key from tab2';

PREPARE stmt1 from v_t1;
EXECUTE stmt1;

--This is where it fails
PREPARE stmt2 from v_t2;
EXECUTE stmt2;
This is kind of how my Proc looks. When I go in the debug mode. I pass
the first EXECUTE and the moment it comes to the second one, it fails
and I get this error message...

A database manager error occurred.[IBM][CLI Driver][DB2/6000] SQL0518N
The statement named in the EXECUTE statement is not in a prepared state
or is a SELECT or VALUES statement. SQLSTATE=07003

----------------------

What am I doing wrong? I would really appreciate if someone could help
me out here.
Thanks in advance.

- Nabil

Works for me on V8.2.2 (FP9). Obviously you haven't posted the real
source code. So, could it be that you have a typo in one of the PREPARE
statements using the wrong statement name?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Nabil]

Hey thanks. That was it. It was a typo error. I really appreciate your
help.

Nabil