PL/I applications that issue SQL statements

You can code SQL statements in a PL/I program wherever you can use executable statements.

The first statement of the PL/I program must be the PROCEDURE statement with OPTIONS(MAIN), unless the program is a stored procedure. A stored procedure application can run as a subroutine.

Each SQL statement in a PL/I program must begin with EXEC SQL and end with a semicolon (;). The EXEC and SQL keywords must appear must appear on one line, but the remainder of the statement can appear on subsequent lines.

You might code an UPDATE statement in a PL/I program as follows: 

EXEC SQL UPDATE DSN8C10.DEPT
           SET MGRNO = :MGR_NUM
           WHERE DEPTNO = :INT_DEPT ;
Comments
You can include PL/I comments in embedded SQL statements wherever you can use a blank, except between the keywords EXEC and SQL. You can also include SQL comments in any SQL statement. For more information, see SQL comments.

To include DBCS characters in comments, you must delimit the characters by a shift-out and shift-in control character; the first shift-in character in the DBCS string signals the end of the DBCS string.

Continuation for SQL statements
The line continuation rules for SQL statements are the same as those for other PL/I statements, except that you must specify EXEC SQL on one line.
Delimiters for SQL statements
Delimit an SQL statement in your PL/I program with the beginning keyword EXEC SQL and a Semicolon (;).
Declaring tables and views
Your PL/I program should include a DECLARE TABLE statement to describe each table and view the program accesses. You can use the Db2 declarations generator (DCLGEN) to generate the DECLARE TABLE statements.
Including code
You can use SQL statements or PL/I host variable declarations from a member of a partitioned data set by using the following SQL statement in the source code where you want to include the statements: 
EXEC SQL INCLUDE member-name;

You cannot nest SQL INCLUDE statements. Do not use the PL/I %INCLUDE statement to include SQL statements or host variable DCL statements. You must use the PL/I preprocessor to resolve any %INCLUDE statements before you use the Db2 precompiler. Do not use PL/I preprocessor directives within SQL statements.

Margins
Code SQL statements in columns 2 through 72, unless you have specified other margins to the Db2 precompiler. If EXEC SQL starts before the specified left margin, the Db2 precompiler does not recognize the SQL statement.
Names
You can use any valid PL/I name for a host variable. Do not use external entry names or access plan names that begin with 'DSN', and do not use host variable names that begin with 'SQL'. These names are reserved for Db2.
Sequence numbers
The source statements that the Db2 precompiler generates do not include sequence numbers. IEL0378I messages from the PL/I compiler identify lines of code without sequence numbers. You can ignore these messages.
Statement labels
You can specify a statement label for executable SQL statements. However, the INCLUDE text-file-name and END DECLARE SECTION statements cannot have statement labels.
WHENEVER statement
The target for the GOTO clause in an SQL statement WHENEVER must be a label in the PL/I source code and must be within the scope of any SQL statements that WHENEVER affects.
Using double-byte character set (DBCS) characters
The following considerations apply to using DBCS in PL/I programs with SQL statements:
  • If you use DBCS in the PL/I source, Db2 rules for the following language elements apply:
    • Graphic strings
    • Graphic string constants
    • Host identifiers
    • Mixed data in character strings
    • MIXED DATA option
  • The PL/I preprocessor transforms the format of DBCS constants. If you do not want that transformation, run the Db2 precompiler before the preprocessor.
  • If you use graphic string constants or mixed data in dynamically prepared SQL statements, and if your application requires the PL/I Version 2 (or later) compiler, the dynamically prepared statements must use the PL/I mixed constant format.
    • If you prepare the statement from a host variable, change the string assignment to a PL/I mixed string.
    • If you prepare the statement from a PL/I string, change that to a host variable, and then change the string assignment to a PL/I mixed string.

    Example:

    SQLSTMT = 'SELECT <dbdb> FROM table-name'M;
EXEC SQL PREPARE STMT FROM :SQLSTMT;
  • If you want a DBCS identifier to resemble a PL/I graphic string, you must use a delimited identifier.
  • If you include DBCS characters in comments, you must delimit the characters with a shift-out and shift-in control character. The first shift-in character signals the end of the DBCS string.
  • You can declare host variable names that use DBCS characters in PL/I application programs. The rules for using DBCS variable names in PL/I follow existing rules for DBCS SQL ordinary identifiers, except for length. The maximum length for a host variable is 128 Unicode bytes in Db2. For information about the rules for DBCS SQL ordinary identifiers, see the information about SQL identifiers.
    Restrictions:
    • DBCS variable names must contain DBCS characters only. Mixing single-byte character set (SBCS) characters with DBCS characters in a DBCS variable name produces unpredictable results.
    • A DBCS variable name cannot continue to the next line.
  • The PL/I preprocessor changes non-Kanji DBCS characters into extended binary coded decimal interchange code (EBCDIC) SBCS characters. To avoid this change, use Kanji DBCS characters for DBCS variable names, or run the PL/I compiler without the PL/I preprocessor.
Special PL/I considerations
The following considerations apply to programs written in PL/I:
  • When compiling a PL/I program that includes SQL statements, you must use the PL/I compiler option CHARSET (60 EBCDIC).
  • When compiling a PL/I program that uses BIGINT or LOB data types, specify the following compiler options: LIMITS(FIXEDBIN(63), FIXEDDEC(31))
  • In unusual cases, the generated comments in PL/I can contain a semicolon. The semicolon generates compiler message IEL0239I, which you can ignore.
  • The generated code in a PL/I declaration can contain the ADDR function of a field defined as character varying. This produces either message IBM105l l or IBM1180l W, both of which you can ignore.
  • The precompiler generated code in PL/I source can contain the NULL() function. This produces message IEL0533I, which you can ignore unless you also use NULL as a PL/I variable. If you use NULL as a PL/I variable in a Db2 application, you must also declare NULL as a built-in function (DCL NULL BUILTIN;) to avoid PL/I compiler errors.
  • The PL/I macro processor can generate SQL statements or host variable DCL statements if you run the macro processor before running the Db2 precompiler.

    If you use the PL/I macro processor, do not use the PL/I *PROCESS statement in the source to pass options to the PL/I compiler. You can specify the needed options on the COPTION parameter of the DSNH command or the option PARM.PLI=options of the EXEC statement in the DSNHPLI procedure.

  • Using the PL/I multitasking facility, in which multiple tasks execute SQL statements, causes unpredictable results.
  • PL/I WIDECHAR host data type is supported through the Db2 coprocessor only.
  • When you use PL/I WX widechar constant, Db2 supports only bigendian format. Thus, when you assign a constant to the widechar type host variable in PL/I, ensure that bigendian format is used. For example:
     HVWC1 = '003100320033006100620063'WX;
    Equivalent to:
     HVWC1 = '123abc';
    HVWC1 is defined as a WIDECHAR type host variable.
  • PL/I SQL Preprocessor option, CCSID0 and NOCCSID0, usage consideration when used with the Db2 coprocessor.
    • When you use CCSID0 (default), it promotes compatibility with older PL/I programs, which used the Db2 precompiler. During program preparation, no CCSID value is associated with the host variable except for the WIDECHAR type host variable. For WIDECHAR type host variable, CCSID 1200 is always assigned by the PL/I SQL Preprocessor.

      During BIND and runtime, if no CCSID is associated with the host variable, the BIND option, ENCODING, which is meant for the application data, is used. If the ENCODING BIND option is not specified, then the default value for the ENCODING BIND option is used.

    • When you use NOCCSID0, a CCSID is associated with the host variable during program preparation. The CCSID is derived from the following items during program preparation:
      • DECLARE :hv VARIABLE CCSID xxxx specified.
      • Source CCSID, if no DECLARE VARIABLE ... CCSID xxxx is specified for the host variable. During BIND time, note the CCSID assigned to the host variable during program preparation is not known to the BIND process. For more information about BIND time CCSID resolution, see Determining the encoding scheme and CCSID of a string.

        For host variable used in static SQL, ensuring accurate and matching CCSID is assigned/derived through DECLARE VARIABLE ... CCSID xxxx, source CCSID or ENCODING BIND option or the installation default

        For parameter marker used in dynamic SQL, ensuring accurate CCSID for the corresponding host variable is assigned/derived through DECLARE VARIABLE ... CCSID xxxx, ENCODING BIND option or the installation default. The source CCSID has no influence on parameter marker.

Handling SQL error codes
PLI/I applications can request more information about SQL errors from Db2. For more information, see Handling SQL error codes in PL/I applications.