Using the INCLUDE statement

The INCLUDE statement can be used in SQL procedures, functions, and triggers to allow reuse of common code. There is no limit to the number of INCLUDE statements that can be used within a single routine body.

When SQL procedures, functions, or triggers are created, DB2 for i generates ILE C code that is precompiled and bound into a program or service program. The INCLUDE statement can be used within SQL procedures, functions, and triggers to include source that is either strictly SQL code or ILE C code.

INCLUDE SQL code

Including SQL allows you to share common declarations, handlers, or routine body code. The INCLUDE statement is replaced with the content of the include file. You must embed the INCLUDE statement at a statement boundary and the source in the include file must be valid at the point where it is included. For example, you can only include declarations in the routine at a point where declarations are allowed.

The resulting source after all the SQL includes are processed is used to create the procedure, function, or trigger. If the content of an include causes an SQL syntax error, the error message will be associated with the position of the INCLUDE statement. If you are unable to determine the cause of the error within the included source, you can temporarily copy the include source into the routine and request the create again.

INCLUDE C code

Including C code directly in a routine body allows you to easily interface with system APIs and other low level ILE code. It can reduce the need to create external procedures and functions to perform these types of operations.

If C code directly checks an indicator value or is written assuming some other specific code generation from the SQL procedure parser, that code could potentially stop working in the future if the SQL procedure parser needs to change the code generation. Use the best practices provided to write the C code so it is as general as possible.

Example of directly calling a C interface

This example creates a procedure named LPRINTF which accepts a VARCHAR parameter. The body of the procedure writes the input parameter to the job log using the Qp0zLprintf interface. Notice that checking the input parameter for the null value is done in the SQL code. When C code is included, the INCLUDE statement must explicitly specify the library where the source file is located. Using *LIBL is not supported.

In this example, you can also see how you would supply additional bind information. If this weren't a built-in C routine, you would need to provide the bind information using the SET OPTION. Any parameter values supplied on the BINDOPT will be used during the bind step during the creation of the C program.

CREATE OR REPLACE PROCEDURE LPRINTF(Print_string VARCHAR(1000))
-- SET OPTION BINDOPT = 'BNDSRVPGM(QSYS/QP0ZCPA)'
BEGIN
  IF Print_string IS NOT NULL THEN 
    INCLUDE MYLIB/QCSRC(MYLPRINTF); 
  END IF;
END;

This is the content of MYLIB/QCSRC(MYLPRINTF). Observe that this code is aware that the input parameter is qualified with the procedure name. It also knows that the parameter is a varying length character, so correctly references the length and the data parts of the generated declaration.

{
  /* declare prototype for Qp0zLprintf */
  extern int Qp0zLprintf (char *format, ...);

  /* print input parameter to job log */
  Qp0zLprintf("%.*s\n", LPRINTF.PRINT_STRING.LEN, LPRINTF.PRINT_STRING.DAT);
}

Example of directly calling a C interface with different parameter types

The previous example showed how tightly connected the C code and SQL need to be when they are compiled as a unit. Sometimes you need access to C code just as utility procedures. In this next example, the C code is wrapped in an SQL procedure that can be called from any SQL routine or trigger. It would be easy to modify these definitions to be functions with the same names that get invoked based on the data type of the input argument.

These procedures each print the value of an input variable to the job log. The first three handle CHAR, VARCHAR, and CLOB as input type. The fourth procedure prints a CLOB variable, but it uses a return code variable ERROR_VAR to indicate if an error occurred during the print of the variable. Following each procedure definition is the C code that is included.

Each of these procedures are written to provide the included C code a predictable variable name to use.

This procedure prints a CHAR variable to the job log.

CREATE OR REPLACE PROCEDURE PRINT_CHAR (Print_string CHAR(100))
 BEGIN
  IF Print_string IS NOT NULL THEN 
    LPRINTF: BEGIN
      DECLARE C_STRING CHAR(100);
      SET C_STRING = Print_string;
      INCLUDE MYLIB/QCSRC(CPRCHAR);
    END;
  END IF;
END;

This is the content of MYLIB/QCSRC(CPRCHAR).

{
  /* declare prototype for Qp0zLprintf */
  extern int  Qp0zLprintf (char *format, ...);

  /* print CHAR variable to job log */
  Qp0zLprintf("%.*s\n", sizeof(LPRINTF.C_STRING), LPRINTF.C_STRING);
}

This procedure prints a VARCHAR variable to the job log.

CREATE OR REPLACE PROCEDURE PRINT_VARCHAR (Print_string IN VARCHAR(1000))
BEGIN
  IF Print_string IS NOT NULL THEN 
    LPRINTF: BEGIN
      DECLARE C_STRING VARCHAR(1000);
      SET C_STRING = Print_string;
      INCLUDE MYLIB/QCSRC(CPRVARCHAR);
    END;
  END IF;
END;

This is the content of MYLIB/QCSRC(CPRVARCHAR).

{
  /* declare prototype for Qp0zLprintf */
  extern int  Qp0zLprintf (char *format, ...);

  /* print VARCHAR variable to job log */
  Qp0zLprintf("%.*s\n", LPRINTF.C_STRING.LEN, LPRINTF.C_STRING.DAT);
}

This procedure prints a CLOB variable to the job log. You must have QSYSINC in your library list to find the C #includes.

CREATE OR REPLACE PROCEDURE PRINT_CLOB (Print_string CLOB(10000))
  SET OPTION COMMIT = *CHG
BEGIN
  IF Print_string IS NOT NULL THEN 
    LPRINTF: BEGIN
      DECLARE C_STRING CLOB(10000);
      SET C_STRING = Print_string;
      INCLUDE MYLIB/QCSRC(CPRCLOB);
    END;
  END IF;
END;

This is the content of QCSRC(CPRCLOB).

{
#include "qp0ztrc.h"                /* for Qp0zLprintf                        */
#include "sqludf.h"                 /* for sqludf_length/sqludf_substr        */

 long lob_length;
 int  rc;

 rc = sqludf_length(&LPRINTF.C_STRING, &lob_length);
 if ((rc == 0) && (lob_length > 0)) {
    unsigned char* lob = malloc(lob_length);

    rc = sqludf_substr(&LPRINTF.C_STRING, 1, lob_length,
                        lob, &lob_length);
    if (rc == 0) {
      /* print CLOB variable to job log */
      Qp0zLprintf("%.*s\n", lob_length, lob);
    }
    free(lob);
  }
}

This procedure prints a CLOB variable to the job log and handles a return code. You must have QSYSINC in your library list to find the C #includes.

CREATE OR REPLACE PROCEDURE PRINT_CLOB_WITH_RC (Print_string IN CLOB(1M))
  SET OPTION COMMIT = *CHG
BEGIN
  IF Print_string IS NOT NULL THEN 
  LPRINTF: BEGIN
    DECLARE C_STRING CLOB(1M);
    DECLARE ERROR_VAR INT;

    SET C_STRING = Print_string;
    SET ERROR_VAR = 0;
    INCLUDE MYLIB/QCSRC(CPRCLOBEC);
    IF ERROR_VAR <> 0 THEN
      BEGIN
        DECLARE MSG_STRING VARCHAR(100);
        SET MSG_STRING = 'Failed in PRINT_CLOB_WITH_RC, ERROR CODE' 
                           CONCAT ERROR_VAR;
        SIGNAL SQLSTATE 'XXXXX' SET MESSAGE_TEXT = MSG_STRING;
      END;
    END IF;
  END;
  END IF;
END;

This is the content of QCSRC(CPRCLOBEC).

{ 
#include "qp0ztrc.h"                /* for Qp0zLprintf                        */
#include "sqludf.h"                 /* for sqludf_length/sqludf_substr        */

 long lob_length;
 int  rc;

 rc = sqludf_length(&LPRINTF.C_STRING, &lob_length);
 if (rc == 0) {
   if (lob_length > 0) {
     unsigned char* lob = malloc(lob_length);

     rc = sqludf_substr(&LPRINTF.C_STRING, 1, lob_length,
                          lob, &lob_length);
     if (rc == 0) {
       /* print CLOB variable to job log */
       Qp0zLprintf("%.*s\n", lob_length, lob);
     }
     else { 
       LPRINTF.ERROR_VAR = rc;  /* indicate error */
     } 
     free(lob);
   }
 }
 else {
   LPRINTF.ERROR_VAR = rc;  /* indicate error */ 
 }
}

Example using SQL and C includes:

Now we will create a procedure that includes a common SQL error handler. The include for the handler is located in the source after any local declarations and before the routine body logic starts, which is where all handlers must be defined.

CREATE OR REPLACE PROCEDURE DROP_TABLE (Drop_schema IN VARCHAR(258), 
                                        Drop_table IN VARCHAR(258))
BEGIN
  DECLARE STATEMENT_TEXT VARCHAR(1000);
  INCLUDE SQL MYLIB/QSQLSRC(SQL_INUSE);
  SET STATEMENT_TEXT = 'DROP TABLE ' || Drop_schema || '.' || Drop_table;
  EXECUTE IMMEDIATE STATEMENT_TEXT;
END;

This is the content of QSQLSRC(SQL_INUSE). It uses two IBM i services to log information about the lock failure and uses built-in global variables to indicate the failing routine. The logged information can be queried after the failure to examine the lock information. The handler also uses the PRINT_VARCHAR procedure to log a message to the job log.

--
-- Common handler for SQL0913, object in use. 
--
-- This handler retrieves the message tokens for the error to determine
--    the table name encountering the error.
-- It gets the most recent 5 rows from the joblog and saves them in a table.
-- It uses the name of the table to find the lock status information 
--    and saves the lock information in a different table.
-- In both tables, rows are tagged with the routine name that 
--    received the error and a common timestamp.
-- Finally, it prints a notification to the joblog.
--
-- This handler assumes the following two tables have been created:
--   CREATE TABLE APPLIB.HARD_TO_DEBUG_PROBLEMS AS 
--        (SELECT SYSIBM.ROUTINE_SCHEMA, SYSIBM.ROUTINE_SPECIFIC_NAME,
--                CURRENT TIMESTAMP AS LOCK_ERROR_TIMESTAMP, 
--                X.* FROM TABLE(QSYS2.JOBLOG_INFO('*')) X) WITH NO DATA;
--   CREATE TABLE APPLIB.HARD_TO_DEBUG_LOCK_PROBLEMS AS 
--        (SELECT SYSIBM.ROUTINE_SCHEMA, SYSIBM.ROUTINE_SPECIFIC_NAME,
--                CURRENT TIMESTAMP AS LOCK_ERROR_TIMESTAMP, 
--                X.* FROM QSYS2.OBJECT_LOCK_INFO X) WITH NO DATA; 
--
DECLARE CONTINUE HANDLER FOR SQLSTATE '57033'
BEGIN 
  DECLARE SCHEMA_NAME VARCHAR(128);
  DECLARE TABLE_NAME VARCHAR(128);
  DECLARE DOT_LOCATION INTEGER;
  DECLARE MSG_TOKEN VARCHAR(1000);
  DECLARE ERROR_TIMESTAMP TIMESTAMP;

  GET DIAGNOSTICS CONDITION 1 MSG_TOKEN = DB2_ORDINAL_TOKEN_1;
  SET DOT_LOCATION = LOCATE_IN_STRING(MSG_TOKEN, '.');
  SET SCHEMA_NAME = SUBSTR(MSG_TOKEN, 1, DOT_LOCATION - 1);
  SET TABLE_NAME = SUBSTR(MSG_TOKEN, DOT_LOCATION + 1, 
                          LENGTH(MSG_TOKEN) - DOT_LOCATION);
  SET ERROR_TIMESTAMP = CURRENT_TIMESTAMP; 

  INSERT INTO APPLIB.HARD_TO_DEBUG_PROBLEMS
    SELECT SYSIBM.ROUTINE_SCHEMA, SYSIBM.ROUTINE_SPECIFIC_NAME,
           ERROR_TIMESTAMP, J.* 
      FROM TABLE(QSYS2.JOBLOG_INFO('*')) J
      ORDER BY A.ORDINAL_POSITION DESC
      FETCH FIRST 5 ROWS ONLY;

  INSERT INTO APPLIB.HARD_TO_DEBUG_LOCK_PROBLEMS
    SELECT SYSIBM.ROUTINE_SCHEMA, SYSIBM.ROUTINE_SPECIFIC_NAME,
            ERROR_TIMESTAMP, L.* 
      FROM QSYS2.OBJECT_LOCK_INFO L
      WHERE OBJECT_SCHEMA = SCHEMA_NAME AND 
            OBJECT_NAME = TABLE_NAME;

  CALL PRINT_VARCHAR('Unexpected lock on table ' CONCAT MSG_TOKEN
              CONCAT '. Information logged to debug tables.');
END;