IBM Support

Enhanced INCLUDE SQL statement

News


Abstract

The INCLUDE statement can be used in SQL PL to embed SQL or C code.

Content

The INCLUDE statement can now be used within SQL PL to include source that is either strictly SQL code or ILE C code.
See the SQL Reference for details: INCLUDE statement
See SQL Programming for examples: Using the INCLUDE statement

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. 
                                     

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
13 January 2020

UID

ibm11142896