Error in creating stored procedure in db2

Question

I'm creating a stored procedure in DB2, it first checks the existence of a table and if it exists, it first drops it and then try to create it. here is how the code looks like

CREATE OR REPLACE PROCEDURE Schema.R ()
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE SQLCODE integer;
DECLARE table_exists integer default 0;


SELECT 1 INTO table_exists FROM syscat.tables WHERE tabschema = 'schema' AND tabname = 'table1';

IF table_exists = 1
THEN 
    DROP TABLE schema.table1 ;  


    CREATE TABLE schema.table1 AS (
    SELECT
         A.*,
         ...
     ) WITH DATA;
END IF;

END P1

But once deploying it, it fails and throws the following error message

Create stored procedure returns SQLCODE: -601, SQLSTATE: 42710.
Schema.R: 18: The name of the object to be created is identical to the existing name "schema.table1" of type "TABLE".. SQLCODE=-601, SQLSTATE=42710, DRIVER=3.72.30
The name of the object to be created is identical to the existing name "schema.table1" of type "TABLE".. SQLCODE=-601, SQLSTATE=42710, DRIVER=3.72.30
Schema.R - Deploy failed.

mao · Accepted Answer · 2018-04-17 15:27:27Z

1

This happens because the compiler sees that the table already existed at compilation time. Avoid it by using dynamic SQL for the create e.g

execute immediate('create table schema.table1 as ( select ...) with data');

answered Apr 17, 2018 at 15:27

mao

12.4k2 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HHH Over a year ago

this solution works, but why is that the case? should I always use dynamic SQL when creating a table in a SP?

mao Over a year ago

As per answer, the compiler sees 'CREATE TABLE...' and also saw at that moment that the target table already existed. During compilation the compiler only considers the current statement, i.e. it does not consider the previous DROP TABLE. You can either DROP table table before compiling, or use dynamic-SQL (which means the compiler for that statement will run only when you CALL the sproc). Study this page until you understand it: ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/…

Collectives™ on Stack Overflow

Error in creating stored procedure in db2

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related