Why is static SQL in Linux DB2 stored procedure rejected?

Question

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?

Mark Barinstein · Accepted Answer · 2025-06-02 19:41:26Z

1

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

\>SQL-statement

\>All executable SQL statements except for:

\>ALTER

\>...

answered Jun 2 at 19:41

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

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

1 Comment

Dave Clark Jun 2 at 21:10

Thank you. I found the section that outlines those statements not allowed as static in a stored procedure.

Collectives™ on Stack Overflow

Why is static SQL in Linux DB2 stored procedure rejected?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related