You can code SQL statements in your PL/I applications using the language defined in DB2 UDB for z/OS SQL Reference. Specific requirements for your SQL code are described in the sections that follow.
A PL/I program that contains SQL statements must include either an SQLCODE variable (if the STDSQL(86) preprocessor option is used) or an SQL communications area (SQLCA). As shown in Figure 5, part of an SQLCA consists of an SQLCODE variable and an SQLSTATE variable.
The SQLCA declaration should be included by using the EXEC SQL INCLUDE statement:
exec sql include sqlca;
The SQLCA structure must not be defined within an SQL declare section. The scope of the SQLCODE and SQLSTATE declarations must include the scope of all SQL statements in the program.
Dcl
1 Sqlca,
2 sqlcaid char(8), /* Eyecatcher = 'SQLCA ' */
2 sqlcabc fixed binary(31), /* SQLCA size in bytes = 136 */
2 sqlcode fixed binary(31), /* SQL return code */
2 sqlerrmc char(70) var, /* Error message tokens */
2 sqlerrp char(8), /* Diagnostic information */
2 sqlerrd(0:5) fixed binary(31), /* Diagnostic information */
2 sqlwarn, /* Warning flags */
3 sqlwarn0 char(1),
3 sqlwarn1 char(1),
3 sqlwarn2 char(1),
3 sqlwarn3 char(1),
3 sqlwarn4 char(1),
3 sqlwarn5 char(1),
3 sqlwarn6 char(1),
3 sqlwarn7 char(1),
2 sqlext,
3 sqlwarn8 char(1),
3 sqlwarn9 char(1),
3 sqlwarna char(1),
3 sqlstate char(5); /* State corresponding to SQLCODE */The following statements require an SQLDA:
Unlike the SQLCA, there can be more than one SQLDA in a program, and an SQLDA can have any valid name. An SQLDA should be included by using the EXEC SQL INCLUDE statement:
exec sql include sqlda;
The SQLDA must not be defined within an SQL declare section.
Dcl
1 Sqlda based(Sqldaptr),
2 sqldaid char(8), /* Eye catcher = 'SQLDA ' */
2 sqldabc fixed binary(31), /* SQLDA size in bytes=16+44*SQLN*/
2 sqln fixed binary(15), /* Number of SQLVAR elements*/
2 sqld fixed binary(15), /* # of used SQLVAR elements*/
2 sqlvar(Sqlsize refer(sqln)), /* Variable Description */
3 sqltype fixed binary(15), /* Variable data type */
3 sqllen fixed binary(15), /* Variable data length */
3 sqldata pointer, /* Pointer to variable data value*/
3 sqlind pointer, /* Pointer to Null indicator*/
3 sqlname char(30) var ; /* Variable Name */
Dcl
1 Sqlda2 based(Sqldaptr),
2 sqldaid2 char(8), /* Eye catcher = 'SQLDA ' */
2 sqldabc2 fixed binary(31), /* SQLDA size in bytes=16+44*SQLN*/
2 sqln2 fixed binary(15), /* Number of SQLVAR elements*/
2 sqld2 fixed binary(15), /* # of used SQLVAR elements*/
2 sqlvar2(Sqlsize refer(sqln2)), /* Variable Description */
3 sqlbiglen,
4 sqllongl fixed binary(31),
4 sqlrsvdl fixed binary(31),
3 sqldatal pointer,
3 sqltname char(30) var;
dcl Sqlsize fixed binary(15); /* number of sqlvars (sqln) */
dcl Sqldaptr pointer;
dcl Sqltripled char(1) initial('3');
dcl Sqldoubled char(1) initial('2');
dcl Sqlsingled char(1) initial(' ');
The first statement of your program must be a PROCEDURE or a PACKAGE statement. You can add SQL statements to your program wherever executable statements can appear. Each SQL statement must begin with EXEC (or EXECUTE) SQL and end with a semicolon (;).
For example, an UPDATE statement might be coded as follows:
exec sql update DSN8710.DEPT
set Mgrno = :Mgr_Num
where Deptno = :Int_Dept;
In addition to SQL statements, comments can be included in embedded SQL statements wherever a blank is allowed.
SQL style comments ('--') are supported when embedded in SQL statements beginning with Enterprise PL/I V3R6.
The line continuation rules for SQL statements are the same as those for other PL/I statements.
SQL statements or PL/I host variable declaration statements can be included by placing the following SQL statement at the point in the source code where the statements are to be embedded:
exec sql include member;
SQL statements must be coded in columns m through n where m and n are specified in the MARGINS(m,n) compiler option.
Any valid PL/I variable name can be used for a host variable. The length of a host variable name must not exceed the value n specified in the LIMITS(NAME(n)) compiler option.
With the exception of the END DECLARE SECTION statement, and the INCLUDE text-file-name statement, executable SQL statements, like PL/I statements, can have a label prefix.
The target for the GOTO clause in an SQL WHENEVER statement must be a label in the PL/I source code and must be within the scope of any SQL statements affected by the WHENEVER statement.
All host variables used in SQL statements must be explicitly declared. If the ONEPASS option is in effect, a host variable used in an SQL statement must be declared prior to its first use in an SQL statement.
In addition:
Host variable declarations can be made at the same place as regular PL/I variable declarations.
Only a subset of valid PL/I declarations are recognized as valid host variable declarations. The preprocessor does not use the data attribute defaults specified in the PL/I DEFAULT statement. If the declaration for a variable is not recognized, any statement that references the variable might result in the message :
'The host variable token ID is not valid'
Only the names and data attributes of the variables are used by the preprocessor; the alignment, scope, and storage attributes are ignored.
The following figure shows the syntax for valid numeric host variable declarations.
>>-+-DECLARE-+--+-variable-name---------+-----------------------> '-DCL-----' | .-,-------------. | | V | | '-(---variable-name-+-)-' >----+---------+-+-FIXED--+-------------------------+-+---------> +-BINARY--+ | '-(precision-+--------+-)-' | +-BIN-----+ | '-,scale-' | +-DECIMAL-+ '-FLOAT-+-----------------+----------' '-DEC-----' '-(--precision--)-' >--+---------------------------------------+-- ; -------------->< '-Alignment and/or Scope and/or Storage-'
Notes
The following figure shows the syntax for valid character host variables.
>>-+-DECLARE-+--+-variable-name---------+-----------------------> '-DCL-----' | .-,-------------. | | V | | '-(---variable-name-+-)-' >--+-CHARACTER-+--+----------+--+---------+---------------------> '-CHAR------' '-(length)-' +-VARYING-+ '-VAR-----' >--+---------------------------------------+-- ; -------------->< '-Alignment and/or Scope and/or Storage-'
Notes
The following figure shows the syntax for valid graphic host variables.
>>-+-DECLARE-+--+-variable-name---------+-----------------------> '-DCL-----' | .-,-------------. | | V | | '-(---variable-name-+-)-' >--GRAPHIC--+----------+--+---------+---------------------------> '-(length)-' +-VARYING-+ '-VAR-----' >--+---------------------------------------+-- ; -------------->< '-Alignment and/or Scope and/or Storage-'
Notes
The following figure shows the syntax for valid result set locator declarations.
>>-+-DECLARE-+--+-variable-name---------+-----------------------> '-DCL-----' | .-,-------------. | | V | | '-(---variable-name-+-)-' >----SQL TYPE IS RESULT_SET_LOCATOR----+---------+--------------> +-VARYING-+ '-VAR-----' >--+---------------------------------------+-- ; -------------->< '-Alignment and/or Scope and/or Storage-'
The following figure shows the syntax for valid table locators.
>>-+-DECLARE-+--+-variable-name---------+-----------------------> '-DCL-----' | .-,-------------. | | V | | '-(---variable-name-+-)-' >----SQL TYPE IS TABLE LIKE table-name AS LOCATOR---- ; -------><
The following figure shows the syntax for declarations of BLOB, CLOB, and DBCLOB host variables and locators.
>>-+-Declare-+--PL/I host identifier--SQL TYPE IS---------------> '-Dcl-----' >--| PL/I LOB type |--| PL/I LOB type: |------------------------> >--+-+-+-Binary Large Object-+----+--(--length--+---+--)-+----->< | | '-BLOB----------------' | +-K-+ | | +-+-Character Large Object-+-+ +-M-+ | | | +-Char Large Object------+ | '-G-' | | | '-CLOB-------------------' | | | '-DBCLOB---------------------' | '-+-BLOB_LOCATOR---+----------------------------------' +-CLOB_LOCATOR---+ +-DBCLOB_LOCATOR-+ +-BLOB_FILE------+ +-CLOB_FILE------+ '-DBCLOB_FILE----'
The following figure shows the syntax for declarations of the new "XML AS" file reference and LOB variable types.
>>-+-Declare-+--PL/I host identifier--SQL TYPE IS XML AS--------> '-Dcl-----' >--| PL/I LOB type |--| PL/I LOB type: |------------------------> >--+-+-+-Binary Large Object-+----+--(--length--+---+--)-+----->< | | '-BLOB----------------' | +-K-+ | | +-+-Character Large Object-+-+ +-M-+ | | | +-Char Large Object------+ | '-G-' | | | '-CLOB-------------------' | | | '-DBCLOB---------------------' | '-+-BLOB_FILE---+-------------------------------------' +-CLOB_FILE---+ '-DBCLOB_FILE-'
The following constant declarations are generated by the SQL preprocessor and can be used to set the file option variable when you use the file reference host variables:
DCL SQL_FILE_READ FIXED BIN(31) VALUE(2); DCL SQL_FILE_CREATE FIXED BIN(31) VALUE(8); DCL SQL_FILE_OVERWRITE FIXED BIN(31) VALUE(16); DCL SQL_FILE_APPEND FIXED BIN(31) VALUE(32);
The following figure shows the syntax for valid declarations of ROWID variables.
>>-+-DECLARE-+--+-variable-name---------+-----------------------> '-DCL-----' | .-,-------------. | | V | | '-(---variable-name-+-)-' >----SQL TYPE IS ROWID---- ; ----------------------------------><
The base SQLTYPE and SQLLEN of host variables are determined according to the following table. If a host variable appears with an indicator variable, the SQLTYPE is the base SQLTYPE plus one.
| PL/I Data Type | SQLTYPE of Host Variable | SQLLEN of Host Variable | SQL Data Type |
|---|---|---|---|
| BIN FIXED(n), n < 16 | 500 | 2 | SMALLINT |
| BIN FIXED(n), n ranges from 16 to 31 | 496 | 4 | INTEGER |
| BIN FIXED(n), n ranges from 32 to 63 | 492 | 8 | BIGINT |
| DEC FIXED(p,s) 0<=p<=15 and 0<=s<=p | 484 | p (byte 1) s (byte 2) | DECIMAL(p,s) |
| BIN FLOAT(p), 1 ≤ p ≤ 21 | 480 | 4 | REAL or FLOAT(n) 1<=n<=21 |
| BIN FLOAT(p), 22 ≤ p ≤ 53 | 480 | 8 | DOUBLE PRECISION or FLOAT(n) 22<=n<=53 |
| DEC FLOAT(m), 1 ≤ m ≤ 6 | 480 | 4 | FLOAT (single precision) |
| DEC FLOAT(m), 7 ≤ m ≤ 16 | 480 | 8 | FLOAT (double precision) |
| CHAR(n), | 452 | n | CHAR(n) |
| CHAR(n) VARYING, 1 ≤ n ≤ 255 | 448 | n | VARCHAR(n) |
| CHAR(n) VARYING, n > 255 | 456 | n | VARCHAR(n) |
| GRAPHIC(n), 1 ≤ n ≤ 127 | 468 | n | GRAPHIC(n) |
| GRAPHIC(n) VARYING, 1 ≤ n ≤ 2000 | 464 | n | VARGRAPHIC(n) |
| GRAPHIC(n) VARYING, n > 2000 | 472 | n | LONG VARGRAPHIC |
| PL/I Data Type | SQLTYPE of Host Variable | SQLLEN of Host Variable | SQL Data Type |
|---|---|---|---|
| SQL TYPE IS BLOB(n) 1<n<2147483647 | 404 | n | BLOB(n) |
| SQL TYPE IS CLOB(n) 1<n<2147483647 | 408 | n | CLOB(n) |
| SQL TYPE IS DBCLOB(n) 1<n<1073741823 (2) | 412 | n | DBCLOB(n) (2) |
| SQL TYPE IS ROWID | 904 | 40 | ROWID |
| SQL TYPE IS VARBINARY(n) 1<n<32704 | 908 | n | VARBINARY(n) |
| SQL TYPE IS BINARY(n) 1<n<255 | 912 | n | BINARY(n) |
| SQL TYPE IS BLOB_FILE | 916 | 267 | BLOB File Reference (1) |
| SQL TYPE IS CLOB_FILE | 920 | 267 | CLOB File Reference (1) |
| SQL TYPE IS DBCLOB_FILE | 924 | 267 | DBCLOB File Reference (1) |
| SQL TYPE IS BLOB_LOCATOR | 960 | 4 | BLOB Locator (1) |
| SQL TYPE IS CLOB_LOCATOR | 964 | 4 | CLOB Locator (1) |
| SQL TYPE IS DBCLOB_LOCATOR | 968 | 4 | DBCLOB Locator (1) |
| SQL TYPE IS RESULT_SET_LOCATOR | 972 | 4 | Result Set Locator |
| SQL TYPE IS TABLE LIKE table-name AS LOCATOR | 976 | 4 | Table Locator (1) |
|
Note:
|
|||
The following tables can be used to determine the PL/I data type that is equivalent to a given SQL data type.
| SQL Data Type | PL/I Equivalent | Notes |
|---|---|---|
| SMALLINT | BIN FIXED(15) | |
| INTEGER | BIN FIXED(31) | |
| BIGINT | BIN FIXED(63) | |
| DECIMAL(p,s) | DEC FIXED(p) or DEC FIXED(p,s) | p = precision and s = scale; 1 <= p <= 31 and 0 <= s <= p |
| REAL or FLOAT(n) | BIN FLOAT(p) or DEC FLOAT(m) | 1 ≤ n ≤ 21, 1 ≤ p ≤ 21 and 1 ≤ m ≤ 6 |
| DOUBLE PRECISION, DOUBLE, or FLOAT(n) | BIN FLOAT(p) or DEC FLOAT(m) | 22 ≤ n ≤ 53, 22 ≤ p ≤ 53 and 7 ≤ m ≤ 16 |
| CHAR(n) | CHAR(n) | 1 ≤ n ≤ 32767 |
| VARCHAR(n) | CHAR(n) VAR | |
| GRAPHIC(n) | GRAPHIC(n) | n is a positive integer from 1 to 127 that refers to the number of double-byte characters, not to the number of bytes |
| VARGRAPHIC(n) | GRAPHIC(n) VAR | n is a positive integer that refers to the number of double-byte characters, not to the number of bytes; 1 ≤ n ≤ 2000 |
| LONG VARGRAPHIC | GRAPHIC(n) VAR | n > 2000 |
| DATE | CHAR(n) | n must be at least 10 |
| TIME | CHAR(n) | n must be at least 8 |
| TIMESTAMP | CHAR(n) | n must be at least 26 |
| SQL Data Type | PL/I Equivalent | Notes |
|---|---|---|
| Result set locator | SQL TYPE IS RESULT_SET_LOCATOR | Use this data type only for receiving result sets. Do not use this data type as a column type. |
| Table locator | SQL TYPE IS TABLE LIKE table-name AS LOCATOR | Use this data type only in a user-defined function or stored procedure to receive rows of a transition table. Do not use this data type as a column type. |
| BLOB locator | SQL TYPE IS BLOB_LOCATOR | Use this data type only to manipulate data in BLOB columns. Do not use this data type as a column type. |
| CLOB locator | SQL TYPE IS CLOB_LOCATOR | Use this data type only to manipulate data in CLOB columns. Do not use this data type as a column type. |
| DBCLOB locator | SQL TYPE IS DBCLOB_LOCATOR | Use this data type only to manipulate data in DBCLOB columns. Do not use this data type as a column type. |
| BLOB file reference | SQL TYPE IS BLOB_FILE | Use this data type only as a reference to a BLOB file. Do not use this data type as a column type. |
| CLOB file reference | SQL TYPE IS CLOB_FILE | Use this data type only as a reference to a CLOB file. Do not use this data type as a column type. |
| DBCLOB file reference | SQL TYPE IS DBCLOB_FILE | Use this data type only as a reference to a DBCLOB file. Do not use this data type as a column type. |
| BLOB(n) | SQL TYPE IS BLOB(n) | 1<n<2147483647 |
| CLOB(n) | SQL TYPE IS CLOB(n) | 1<n<2147483647 |
| DBCLOB(n) | SQL TYPE IS DBCLOB(n) | n is the number of double-byte characters. 1<n<1073741823 |
| ROWID | SQL TYPE IS ROWID | |
| XML AS | SQL TYPE IS XML AS ... | Used to describe an XML version of a BLOB, CLOB, DBCLOB, BLOB_FILE, CLOB_FILE, or DBCLOB_FILE |