Informix "foreach execute procedure": converting to DB2

[Gustavo Randich]

Hello,

I'm writing an automatic SQL parser and translator from Informix to
DB2. Now I'm faced with one of the most difficult things to translate,
the "foreach execute procedure" functionality provided by Informix.
This is the problem: the execution of the translated SQL leaves the
rows in the temp table correctly but raises error SQL0480N. It's very
simple to try it:

------------
Original SQL:
------------

create temp table a(a smallint)
;
drop procedure a
;
create procedure a() returning smallint;
return 1 with resume;
return 2 with resume;
end procedure
;
drop procedure b
;
create procedure b()
define b smallint;
foreach execute procedure a() into b
insert into a values(b);
end foreach;
end procedure
;
execute procedure b()
;
select * from a
;
drop table a
;

Results:
-------
1
2
----------------------------
Automatically translated SQL:
----------------------------

DECLARE GLOBAL TEMPORARY TABLE a(a SMALLINT) ON COMMIT PRESERVE ROWS
NOT LOGGED WITH REPLACE
@
CREATE PROCEDURE DROP_IFEXISTS()
BEGIN
DECLARE DROP_SQL VARCHAR(255);
IF EXISTS (SELECT routinename FROM syscat.routines WHERE
routineschema = current_schema AND routinename = 'A' AND routinetype =
'F') THEN
SET DROP_SQL = 'DROP FUNCTION a';
EXECUTE IMMEDIATE DROP_SQL;
ELSEIF EXISTS (SELECT routinename FROM syscat.routines WHERE
routineschema = current_schema AND routinename = 'A' AND routinetype =
'P') THEN
SET DROP_SQL = 'DROP PROCEDURE a';
EXECUTE IMMEDIATE DROP_SQL;
END IF;
END
@
CALL DROP_IFEXISTS
@
DROP PROCEDURE DROP_IFEXISTS
@
CREATE PROCEDURE a() DYNAMIC RESULT SETS 1
BEGIN
DECLARE a_OUTPUT_1_TAB VARCHAR(20);
DECLARE a_OUTPUT_1_SQL VARCHAR(1024);
DECLARE a_OUTPUT_1_CUR CURSOR WITH RETURN TO CALLER FOR
a_OUTPUT_1_SEL;

SET a_OUTPUT_1_TAB = 'a_OUTPUT_1';
SET a_OUTPUT_1_SQL = 'DECLARE GLOBAL TEMPORARY TABLE ' ||
a_OUTPUT_1_TAB || '(a_1 SMALLINT) ON COMMIT PRESERVE ROWS NOT LOGGED
WITH REPLACE';
EXECUTE IMMEDIATE a_OUTPUT_1_SQL;
SET a_OUTPUT_1_SQL = 'INSERT INTO SESSION.' || a_OUTPUT_1_TAB || '
VALUES(1)';
EXECUTE IMMEDIATE a_OUTPUT_1_SQL;
SET a_OUTPUT_1_SQL = 'INSERT INTO SESSION.' || a_OUTPUT_1_TAB || '
VALUES(2)';
EXECUTE IMMEDIATE a_OUTPUT_1_SQL;
SET a_OUTPUT_1_SQL = 'SELECT * FROM SESSION.' || a_OUTPUT_1_TAB;
PREPARE a_OUTPUT_1_SEL FROM a_OUTPUT_1_SQL;
OPEN a_OUTPUT_1_CUR;
END
@
CREATE PROCEDURE DROP_IFEXISTS()
BEGIN
DECLARE DROP_SQL VARCHAR(255);
IF EXISTS (SELECT routinename FROM syscat.routines WHERE
routineschema = current_schema AND routinename = 'B' AND routinetype =
'F') THEN
SET DROP_SQL = 'DROP FUNCTION b';
EXECUTE IMMEDIATE DROP_SQL;
ELSEIF EXISTS (SELECT routinename FROM syscat.routines WHERE
routineschema = current_schema AND routinename = 'B' AND routinetype =
'P') THEN
SET DROP_SQL = 'DROP PROCEDURE b';
EXECUTE IMMEDIATE DROP_SQL;
END IF;
END
@
CALL DROP_IFEXISTS
@
DROP PROCEDURE DROP_IFEXISTS
@
CREATE PROCEDURE b()
BEGIN
DECLARE b SMALLINT;
DECLARE SQLSTATE CHAR(5);
DECLARE a_RESULTS_1 RESULT_SET_LOCATOR VARYING;

CALL a;
ASSOCIATE RESULT SET LOCATORS(a_RESULTS_1) WITH PROCEDURE a;
ALLOCATE a_OUTPUT_CURSOR_1 CURSOR FOR RESULT SET a_RESULTS_1;
FETCH FROM a_OUTPUT_CURSOR_1 INTO b;
WHILE (SQLSTATE = '00000') DO
INSERT INTO SESSION.a VALUES(b);
FETCH FROM a_OUTPUT_CURSOR_1 INTO b;
END WHILE;
CLOSE a_OUTPUT_CURSOR_1;
END
@
CALL b
@
SELECT * FROM SESSION.a
@
DROP TABLE SESSION.a
@
Results:
-------

DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.

Return state = 0

DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.

Return state = 0

DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.

SQL0480N The procedure "B" has not yet been called. SQLSTATE=51030

Return state = 0

A
------
1
2

DB20000I The SQL command completed successfully.
----------------------------------------------------------------------------

ANY HINT?............ (using DB2 v8.1.0.64, "s040812", "MI00086",
FixPak 7)

[Knut Stolze]

Gustavo Randich wrote:

Hello,

I'm writing an automatic SQL parser and translator from Informix to
DB2. Now I'm faced with one of the most difficult things to translate,
the "foreach execute procedure" functionality provided by Informix.
This is the problem: the execution of the translated SQL leaves the
rows in the temp table correctly but raises error SQL0480N. It's very
simple to try it:

------------
Original SQL:
------------

create temp table a(a smallint)
;
drop procedure a
;
create procedure a() returning smallint;
return 1 with resume;
return 2 with resume;
end procedure
;
drop procedure b
;
create procedure b()
define b smallint;
foreach execute procedure a() into b
insert into a values(b);
end foreach;
end procedure
;
execute procedure b()
;
select * from a
;
drop table a
;

Not knowing Informix well enough, I'd say that your procedure "A" is just a
table function, i.e. each call made to the function returns the next row.

In DB2 you could achieve something like it in the following way:

CREATE FUNCTION a()
RETURNS TABLE ( col SMALLINT )
LANGUAGE SQL
RETURN SELECT *
FROM TABLE ( VALUES ( 1 ),
( 2 ) ) AS t
@

Now you can use this function in procedure B:

CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
FOR fetch AS loop CURSOR FOR SELECT * FROM TABLE ( a() ) AS t
DO
INSERT INTO a VALUES (col);
END FOR;
END
@

In this particular case, I would even further simplify the procedure:

CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
INSERT INTO a SELECT col FROM TABLE ( a() ) AS t;
END
@

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

[gustavo_randich]

Knut,

Thanks for the reply. The procedure you wrote has exactly the same
behavior as mine. The table is populated, but I'm still getting the
error "SQL0480N The procedure "B" has not yet been called.
SQLSTATE=51030"; have you tried it?

Knut Stolze wrote:

Not knowing Informix well enough, I'd say that your procedure "A" is just a table function, i.e. each call made to the function returns the next row.
In DB2 you could achieve something like it in the following way:

CREATE FUNCTION a()
RETURNS TABLE ( col SMALLINT )
LANGUAGE SQL
RETURN SELECT *
FROM TABLE ( VALUES ( 1 ),
( 2 ) ) AS t
@

Now you can use this function in procedure B:

CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
FOR fetch AS loop CURSOR FOR SELECT * FROM TABLE ( a() ) AS t
DO
INSERT INTO a VALUES (col);
END FOR;
END
@

In this particular case, I would even further simplify the procedure:

CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
INSERT INTO a SELECT col FROM TABLE ( a() ) AS t;
END
@

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

[Serge Rielau]

Knut Stolze wrote:

Gustavo Randich wrote:

Hello,

I'm writing an automatic SQL parser and translator from Informix to
DB2. Now I'm faced with one of the most difficult things to translate,
the "foreach execute procedure" functionality provided by Informix.
This is the problem: the execution of the translated SQL leaves the
rows in the temp table correctly but raises error SQL0480N. It's very
simple to try it:

------------
Original SQL:
------------

create temp table a(a smallint)
;
drop procedure a
;
create procedure a() returning smallint;
return 1 with resume;
return 2 with resume;
end procedure
;
drop procedure b
;
create procedure b()
define b smallint;
foreach execute procedure a() into b
insert into a values(b);
end foreach;
end procedure
;
execute procedure b()
;
select * from a
;
drop table a
;

Not knowing Informix well enough, I'd say that your procedure "A" is just a
table function, i.e. each call made to the function returns the next row.

In DB2 you could achieve something like it in the following way:

CREATE FUNCTION a()
RETURNS TABLE ( col SMALLINT )
LANGUAGE SQL
RETURN SELECT *
FROM TABLE ( VALUES ( 1 ),
( 2 ) ) AS t
@

Now you can use this function in procedure B:

CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
FOR fetch AS loop CURSOR FOR SELECT * FROM TABLE ( a() ) AS t
DO
INSERT INTO a VALUES (col);
END FOR;
END
@

In this particular case, I would even further simplify the procedure:

CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
INSERT INTO a SELECT col FROM TABLE ( a() ) AS t;
END
@

Alternatively (a bit more dumbed down and less performant) you could
do a CALL. Go through the allocate/associate locator logic
and then fetch from the result set of the procedure in a WHILE loop.

Of cousre the fastest would be:
INSERT INTO T SELECT * FROM TABLE(foo(a)) AS F

Cheers
Serge

Cheers
Serge

[Knut Stolze]

gu*************@yahoo.com.ar wrote:

Knut,

Thanks for the reply. The procedure you wrote has exactly the same
behavior as mine. The table is populated, but I'm still getting the
error "SQL0480N The procedure "B" has not yet been called.
SQLSTATE=51030"; have you tried it?

Yes, I have tried it and it did work quite fine. In particular, I didn't
see the SQL0480 that you got.

You might want to set the DIAGLEVEL to 4, run your statements again and see
if there is anything interesting in the db2diag.log.

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

[Gustavo Randich]

Knut Stolze <st****@de.ibm.com> wrote in message news:<co**********@fsuj29.rz.uni-jena.de>...

gu*************@yahoo.com.ar wrote:

Knut,

Thanks for the reply. The procedure you wrote has exactly the same
behavior as mine. The table is populated, but I'm still getting the
error "SQL0480N The procedure "B" has not yet been called.
SQLSTATE=51030"; have you tried it?

Yes, I have tried it and it did work quite fine. In particular, I didn't
see the SQL0480 that you got.

You might want to set the DIAGLEVEL to 4, run your statements again and see
if there is anything interesting in the db2diag.log.

Strange thing... no info about this ins db2diag.log; fortunately, the
applications I'm working on execute this SQL via ADO, which doesn't
intercept's the error. Thanks again.