You should be aware of the following limitations when working with stored procedures under SQL Option:
- Stored procedures cannot be debugged when the XDB server is running as a service.
- We recommend you do not use STOP RUN statements within a stored procedure. If you use STOP RUN instead of GOBACK you will receive a -471 sqlcode on the EXEC SQL CALL STATEMENT.
Note that IBM also recommends against the use of STOP RUN statements in stored procedures. This is because in the DB2 subsystem, a STOP RUN terminates the Language Environment enclave, so the next time a DB2 stored procedure is called, the Language Environment enclave has to be rebuilt. This extra work being done by the DB2 region severely impacts the benefits of running the stored procedure as a DB2 subprogram.
- Currently, the VALIDATE preprocessor directive will provide minimal assistance with stored procedure SQL statements (CALL, ASSOCIATE, ALLOCATE).
- No support is provided for performing a PREPARE statement using ALLOCATE CURSOR or ASSOCIATE LOCATORS syntax. ALLOCATE CURSOR and ASSOCIATE LOCATORS statements must be embedded within the SQL language EXEC SQL ... END-EXEC delimiters. ALLOCATE CURSOR and ASSOCIATE LOCATORS statements cannot be dynamically prepared.