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.

Answer 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');