stored-proceduresstaticdb2-luw

Why is static SQL in Linux DB2 stored procedure rejected?

I've seen plenty of examples of static SQL in sample Linux DB2 stored procedures. But if I code static SQL in my Linux DB2 stored procedure, as follows:

SET v_MsgText = 'DROP LOC CHECK failed.';

ALTER TABLE LIBRAT.APNVEND_RECORD_APNV_LOC_DATA 
    DROP CONSTRAINT LIBRAT_APNVEND_RECORD_APNV_LOC_DATA_C1;

Then the compiler rejects it, with this error:

Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=ALTER;C CHECK failed.';
;TRUNCATE, DRIVER=4.33.31
SQLState: 42601
ErrorCode: -104
Error occurred in:
CREATE or REPLACE PROCEDURE LIBRAT.TCVISION_RELOAD_APNVEND...

Rather, it forces me to code it this way:

SET v_MsgText = 'DROP LOC CHECK failed.';
SET v_SqlStmt = 'ALTER TABLE LIBRAT.APNVEND_RECORD_APNV_LOC_DATA DROP CONSTRAINT LIBRAT_APNVEND_RECORD_APNV_LOC_DATA_C1';

EXECUTE IMMEDIATE v_SqlStmt;

Why?

Solution

  • It's because static ALTER is not supported in a compiled compound statement:

    \>SQL-statement

    \>All executable SQL statements except for:

    \>ALTER

    \>...