News
Abstract
The INCLUDE statement can be used in SQL PL to embed SQL or C code.
Content
Enhanced with IBM i 7.3 SF99703 Level 3 and IBM i 7.2 SF99702 Level 14
- Add support for INCLUDE SQL and INCLUDE of ILE C code
SQL trigger consideration: When an SQL trigger uses the INCLUDE statement to include ILE C, any operation that will cause the trigger program to be rebuilt will have a runtime dependency on the include file.
Trigger programs can be automatically rebuild by the database when the trigger program is deleted, the file is copied or when the file is restored. In all these scenarios, if the INCLUDE file cannot be found, an SQL7032 failure will be returned.
For example, in the case of using INCLUDE within an Instead of Trigger, the following failures are examples of proper behavior because the INCLUDE'd source is not found when DB2 for i needs to rebuild the trigger program.
If you try to copy the view....
> CRTDUPOBJ OBJ(DBMONP) FROMLIB(TOYSTORE) OBJTYPE(*FILE) TOLIB(TOYSTOREB) DATA(*YES)
Object DBMONP in TOYSTOREB type *FILE created.
1 objects duplicated.
> CRTDUPOBJ OBJ(DBMONV) FROMLIB(TOYSTORE) OBJTYPE(*FILE) TOLIB(TOYSTOREB) DATA(*YES)
File QSQLSRC in library QTEMP already exists.
Member DBMONIOT already exists in file QSQLSRC in library QTEMP.
File QSQDSRC in library TOYSTORE already exists.
File QSQDSRC not created in library TOYSTORE.
Member DBMONIOT already exists in file QSQDSRC in library TOYSTORE.
Member DBMONIOT not added to file QSQDSRC in TOYSTORE.
File QCSRC in library SCOTTF with member IOT2 not found.
Member IOT2 for SQL INCLUDE not found or not authorized.
Request CREATE TRIGGER to module QSQTRG not valid.
SQL procedure, function, trigger, or variable DBMONIOT in TOYSTOREB not
created.
The trigger operation failed.
Trigger operation not successful.
Cannot create duplicate file DBMONV in TOYSTOREB.
0 objects duplicated. 1 objects not duplicated.
If you try to save and restore the view, and then use it....
SAVOBJ OBJ(DB*) LIB(TOYSTORE) DEV(*SAVF) OBJTYPE(*FILE) SAVF(TOYSTORE/SAV2)
2 objects saved from library TOYSTORE.
RSTOBJ OBJ(DB*) SAVLIB(TOYSTORE) DEV(*SAVF) OBJTYPE(*FILE) SAVF(TOYSTORE/SAV2) RSTLIB(TOYSTOREB)
Journal identifier X'A69000365149FE210221' is currently in use.
Journal identifier X'A69000365149FE210222' is currently in use.
File DBMONV restored with different library for depended-on files.
Journal identifier X'A69000365149FE210225' is currently in use.
File DBMONP in library TOYSTOREB restored.
File DBMONV in library TOYSTOREB restored.
2 objects restored from TOYSTORE to TOYSTOREB.
> strdbmon toystoreb/dbmonv
Open of member DBMONV was changed to SEQONLY(*NO).
File QSQLSRC in library QTEMP already exists.
Member DBMONIOT already exists in file QSQLSRC in library QTEMP.
File QSQDSRC in library TOYSTORE already exists.
File QSQDSRC not created in library TOYSTORE.
Member DBMONIOT already exists in file QSQDSRC in library TOYSTORE.
Member DBMONIOT not added to file QSQDSRC in TOYSTORE.
File QCSRC in library SCOTTF with member IOT2 not found.
Member IOT2 for SQL INCLUDE not found or not authorized.
Request CREATE TRIGGER to module QSQTRG not valid.
SQL procedure, function, trigger, or variable DBMONIOT in TOYSTOREB not
created.
The trigger operation failed.
Trigger operation not successful.
Program *N in TOYSTOREB not found.
Database trace record not written to the log.
Database monitor started for job *, monitor ID 3416890001.
> strsql
Open of member DBMONV was changed to SEQONLY(*NO).
File QSQLSRC in library QTEMP already exists.
Member DBMONIOT already exists in file QSQLSRC in library QTEMP.
File QSQDSRC in library TOYSTORE already exists.
File QSQDSRC not created in library TOYSTORE.
Member DBMONIOT already exists in file QSQDSRC in library TOYSTORE.
Member DBMONIOT not added to file QSQDSRC in TOYSTORE.
File QCSRC in library SCOTTF with member IOT2 not found.
Member IOT2 for SQL INCLUDE not found or not authorized.
Request CREATE TRIGGER to module QSQTRG not valid.
SQL procedure, function, trigger, or variable DBMONIOT in TOYSTOREB not
created.
The trigger operation failed.
Trigger operation not successful.
Program *N in TOYSTOREB not found.
Was this topic helpful?
Document Information
Modified date:
13 January 2020
UID
ibm11142896