How can I deploy a DB2 stored procedure which writes to a table that does not yet exist (but will by the time it does so)?

Question

As you would most likely have deduced from the following question, I am new to DB2 in general. I am attempting to write my second ever stored procedure using IBM Data Studio and am running into an error when trying to deploy it. The point of the stored procedure is to search for a text string in fields across different tables. NOTE: The code is not complete and is not useful in its current form. I am attempting to test each step as I go along.

Here is all of the code I have so far:

CREATE OR REPLACE PROCEDURE sp_find_string (in in_search_string varchar(200), in in_schema varchar(50))
    DYNAMIC RESULT SETS 1
P1: BEGIN
    -- #######################################################################
    -- # 
    -- #######################################################################
    declare table_a varchar(200);
    declare colname varchar(200);
    declare sqlcmd  varchar(2000);
    declare eof smallint default 0;
    declare not_found condition for sqlstate '02000';

    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN for
        select tabname, colname from syscat.columns c
        --inner join syscat.tables t on t.tabschema = c.tabschema
        --  and t.tabname = c.tabname
        where tabname like 'MERLIN%'
        and tabschema = in_schema;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'  -- or SQLEXCEPTION

    -------------------------------------------------
    if (exists
            (
                select 1 from sysibm.systables
                where creator = 'A2815'
                and name = 'DBP_TEMP_SEARCH_RESULTS'
            )
        ) then drop table A2815.DBP_TEMP_SEARCH_RESULTS;
    end if;

    create table A2815.DBP_TEMP_SEARCH_RESULTS
    (text_to_match varchar(200)
    ,table_a varchar(200)
    ,colname varchar(200)
    ,match_count bigint);

    -- Cursor left open for client application
    OPEN cursor1;
        while eof = 0 do
            p2: begin
                declare continue handler for not_found set eof = 1;
                fetch from cursor1 into table_a, colname;
                insert into A2815.DPB_TEMP_SEARCH_RESULTS
                values(table_a, colname);
            end p2;
        end while;
    close cursor1;
    --return;

END P1 

I am getting this error when attempting to deploy:

Deploy [TIO_D]A2815.SP_FIND_STRING(VARCHAR(200), VARCHAR(50))

Running
A2815.SP_FIND_STRING - Deploy started.
Create stored procedure returns SQLCODE: -204, SQLSTATE: 42704.
A2815.SP_FIND_STRING: 44: "A2815.DPB_TEMP_SEARCH_RESULTS" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=4.18.60
"A2815.DPB_TEMP_SEARCH_RESULTS" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=4.18.60
A2815.SP_FIND_STRING - Deploy failed.
A2815.SP_FIND_STRING - Roll back completed successfully.

When I comment out the insert statement, it deploys just fine (but of course the procedure doesn't do me much good without the ability to insert):

OPEN cursor1;
        while eof = 0 do
            p2: begin
                declare continue handler for not_found set eof = 1;
                fetch from cursor1 into table_a, colname;
                --insert into A2815.DPB_TEMP_SEARCH_RESULTS
                --values(table_a, colname);
            end p2;
        end while;
    close cursor1;

It is true that the table does not exist yet, because it should be created by the procedure. However, if I create the table then deploy the procedure I get this error:

Deploy [TIO_D]A2815.SP_FIND_STRING(VARCHAR(200), VARCHAR(50))

    Running
    A2815.SP_FIND_STRING - Deploy started.
    Create stored procedure returns SQLCODE: -601, SQLSTATE: 42710.
    A2815.SP_FIND_STRING: 32: The name of the object to be created is identical to the existing name "A2815.DBP_TEMP_SEARCH_RESULTS" of type "TABLE".. SQLCODE=-601, SQLSTATE=42710, DRIVER=4.18.60
    The name of the object to be created is identical to the existing name "A2815.DBP_TEMP_SEARCH_RESULTS" of type "TABLE".. SQLCODE=-601, SQLSTATE=42710, DRIVER=4.18.60
    A2815.SP_FIND_STRING - Deploy failed.
    A2815.SP_FIND_STRING - Roll back completed successfully.

Can anyone tell me how to get this procedure deployed either when the table exists, when it doesn't exist, or both?

Thank you very much and let me know what other detail is needed. Also, suggestions on how to improve the code in general would be great as well.

Answer 1

The simplest solution is simply to create that table so that it exists before you compile the procedure. If you just run the create table statement manually before compiling the procedure, then there will be no problem.

Commenters have suggested that you should use Declare Global Temporary Table, and I agree with this, since you appear to be using this as a temporary table. However, it doesn't actually solve your specific problem, since the procedure still won't compile if the temporary table doesn't exist at compile time. So, yes, use a temporary table, but you will still have to do the same thing.

Changing the insert statement to dynamic SQL would also work, though it is an ugly solution. Not necessary here, but sometimes it is needed.

Answer 2

Might be a bit late, but the best way to do this would be to create a string with your query, instead of using the query directly, and then using EXECUTE IMMEDIATELY