DB2 Server for VSE & VM: Application Programming
All statements in your PL/I program, including SQL statements, must be
contained in columns 2 through 72 of your source deck. Normal PL/I
continuation rules apply.
Continuation of tokens (the basic syntactical units of a language) is
allowed from one line to the next, by coding the first part of the token up to
column 72 on the line to be continued and coding the next part of the token
from column 2 on the continuation line. If either column 72 of the
continued line or column 2 of the continuation line is blank, the token is not
continued.
See the DB2 Server for VSE & VM SQL
Reference manual for a discussion on tokens.
Delimiters are required on all SQL statements to help the database manager
distinguish them from regular PL/I statements. You must precede each
SQL statement in your program with EXEC SQL, and end each statement with a
semicolon. EXEC and SQL must be on the same line, with only blanks
separating them (no in-line host language or SQL comments).
Within SQL statements, host language and SQL comments are allowed anywhere
that blanks are allowed. However, there should not be any host language
or SQL comments within SQL statements that are dynamically defined and
executed.
An SQL statement cannot be followed on the same line by another SQL
statement, a normal PL/I statement, or a host language comment. When you preprocess a program containing such a combination, the
trailing statements or host language comments are ignored and will not appear
in the SYSPRINT listing.
To include external secondary input, specify the following at the point in
the source code where the secondary input is to be included:
EXEC SQL INCLUDE text_file-name;
The text_file-name is the member name of a P-Type source
member of a VSE library or the file name of a CMS file (with a "PLICOPY"
file type) located on a CMS minidisk accessed by the user.
A declaration for a variable with the attributes STATIC and EXTERNAL must
also have the attribute INITIAL. If it does not, the declaration
generates a common CSECT, which the database manager cannot handle.
PL/I programming using "DEFAULT RANGE (*) STATIC" gives an error
message. The preprocessor builds control blocks that are incompatible
with this statement.
The keywords "EXEC SQL" must appear in uppercase in your PL/I
program. The rest of an SQL statement can be in mixed case, but will be
interpreted as uppercase, except for text within quotation marks, which will
be left in the original case.
You must declare all host variables in an SQL declare section. For a
description of an SQL declare section, refer to Declaring Variables That Interact with the Database Manager.
Declare host variables in the source file before the first use of the
variable in an SQL statement. You can use the following types of
variables in an SQL statement:
- Scalar variables
- Structure variables
- Structure elements
- Array variables
For information on the use of these variables in an SQL statement, refer to
Using Host Variables and Using Host Structures.
| Note: | You can declare non-host variables in an SQL declare section; however,
declarations that do not conform to DB2 Server for VSE & VM declaration
rules may return errors.
|
The declaration of a host variable is subject to the following rules:
- You can use scalar variables and structure elements as main
variables. You can also use them as indicator variables if they are
declared with a data type of short integer.
- The only arrays accepted by the PL/I preprocessor are arrays of short
integer elements. These arrays may be used as indicator arrays
only. The following example is an indicator array:
DCL IND_ARRAY(10) BINARY FIXED(15);
Indicator array elements cannot be used as main or indicator
variables.
- A structure variable (which defines a host structure) is any two-level
structure declared in an SQL declare section. The following example is
a host structure:
DCL 01 PROJ_STRCT,
05 PROJNO CHAR(6),
05 ACTNO BINARY FIXED(15),
05 ACSTAFF BINARY FIXED(31),
05 ACSTDATE CHAR(10),
05 ACENDATE CHAR(10);
This structure represents the following list of host variables when used in
an SQL statement:
PROJNO, ACTNO, ACSTAFF, ACSTDATE, ACENDATE
In other words, the two following SQL statements are equivalent:
EXEC SQL SELECT PROJNO, ACTNO, ACTSTAFF, ACSTDATE, ACENDATE
INTO :PROJ_STRCT
FROM PROJ_ACT
WHERE PROJNO = '100000'
EXEC SQL SELECT PROJNO, ACTNO, ACSTAFF, ACSTDATE, ACENDATE
INTO :PROJNO, :ACTNO, :ACSTAFF, :ACSTDATE, :ACENDATE
FROM PROJ_ACT
WHERE PROJNO = '100000'
A host structure can either be a stand-alone structure or a substructure of
a more complex structure. The following example is a complex structure
that contains a host structure:
DCL 01 EMPLOYEE,
05 EMPNO CHAR(6),
05 EMPNAME
10 FIRSTNAME CHAR(12),
10 MIDINIT CHAR(1),
10 LASTNAME CHAR(15),
05 WORKDEPT CHAR(3),
05 PHONENO CHAR(4);
The structure EMPNAME is a host structure.
You can use the elements of the host structure and the elements of a
complex structure containing a host structure as host variables. In the
previous example, EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT,
and PHONENO can all be used as host variables.
- DCL or DECLARE must be the first character sequence on the line, but
cannot start in column 1. You can, however, have a carriage control
character in column 1. Otherwise, the line is ignored. (You can
place inline host language comments anywhere after the DECLARE or DCL keyword,
and you can continue these comments over multiple lines.)
- DECLARE statements can be continued on additional lines, but you cannot
have more than one DECLARE statement on the same line. All DECLARE
statements must end with a semicolon. Rules for continuation of
variable names and PL/I keywords are the same as those described for SQL
statements.
- Declare only one host variable per DCL or DECLARE statement. If you
declare multiple variables, only the first variable is recognized; the
others are ignored. For example:
The next rule provides one exception to this limitation.
- Factoring of scalar variable names, structure element names and indicator
array names is supported. For example, the following declarations are
valid:
DCL (X,Y,Z) BINARY FIXED(31);
DCL (ARR1(10), ARR2(5), ARR3(6)) BINARY FIXED (15);
DCL 01 STUCT,
05 (FLD1, FLD2, FLD3) CHAR(10),
05 FLD4 CHAR(5);
- In addition to the attributes discussed in Figure 130, the PL/I preprocessor also supports the following
attributes in declarations imbedded in an SQL declare section:
ALIGNED
UNALIGNED
INTERNAL
EXTERNAL
STATIC
AUTOMATIC
DEFINED
CONTROLLED
CONNECTED
INITIAL
- In PL/I, the BASED and LIKE functions are not permitted in host structure
declarations.
- You cannot duplicate variable names in a single source file even if they
are in different blocks or functions. The PL/I preprocessor defines a
duplicate as any name that cannot be referenced unambiguously when fully
qualified.
- You should not declare variables whose names begin with SQL or RDI,
because these names are reserved for database manager use.
- The database manager allows host variable names, statement labels, and SQL
descriptor area names of up to 256 characters in length, subject to any PL/I
language restriction mentioned in this appendix.
You can have a label on the "EXEC SQL BEGIN DECLARE SECTION;",
but not on the "EXEC SQL END DECLARE SECTION;". If you do
place a label on this statement, the preprocessor does not recognize it and
assumes that the SQL declare section has not ended.
When placing host language comments after either of these statements, make
sure the comment ends on the same line. If it does not, PL/I compiler
errors result.
| Note: | Other program variables can also be declared as usual outside the SQL declare
section. The previous restrictions do not apply to non-SQL
declarations.
|
In the declaration below, only DATES and PRODUCTS may
be used as host structures. ORDERNO and CUSTNUM
may be used as scalar host variables and may be qualified as
CUSTORD.ORDERNO and ORDINFO.CUSTNUM or
CUSTORD.ORDINFO.CUSTNUM.
EXEC SQL BEGIN DECLARE SECTION;
DCL 1 CUSTORD,
2 ORDERNO CHAR(10),
2 ORDINFO,
3 CUSTNUM CHAR(10),
3 DATES,
5 ORDDATE CHAR(6),
5 DELIVDTE CHAR(6),
2 PRODUCT,
3 STOCKNO CHAR(10),
3 QUANTITY CHAR(3);
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT STOCKNO, QUANTITY
INTO :PRODUCT
FROM ORDER
WHERE STOCKNO = '1234567890';
When you reference host variables, host structures, structure fields or
indicator arrays in an SQL statement, you must precede each reference by a
colon (:) The colon distinguishes these variables from SQL identifiers
(such as column names). The colon is not required outside an SQL
statement.
Host variables must be type-compatible with the columns with which they are
to be used. For example, if you want to compare a program variable with
the QONHAND column of the database, and the data type of QONHAND is INTEGER,
you should declare the program variable BIN FIXED(31), BIN FIXED(15), BIN
FLOAT, FLOAT BIN, or FIXED DECIMAL(10). (Refer to Assigning Data Types When the Column Is Created for details on the FLOAT data type.)
The database manager considers the numeric data types compatible, as well
as the character string data types (CHAR, VARCHAR, and LONG VARCHAR, including
strings of different declared lengths), and the graphic string data types
(GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC). Of course, an overflow
condition may result if, for example, you assign a 31-bit integer to a 15-bit
integer and the current value of the 31-bit integer is too large to fit in 15
bits. Truncation also occurs when a decimal number having a scale
greater than zero is assigned to an integer. In general, overflow
occurs when significant digits are lost, and truncation occurs when
nonsignificant digits are lost.
The datetime data types are also considered compatible with character data
types (CHAR, and VARCHAR, but not LONG VARCHAR and VARCHAR >
254).
Refer to Converting Data for a data conversion summary.
The rules for the format and use of DBCS characters in SQL statements are
the same for PL/I as for other host languages supported by the system.
For a discussion of these rules, see Using a Double-Byte Character Set (DBCS).
When using the string-constant format of the PREPARE or EXECUTE IMMEDIATE
statement, if the statement in the string-constant contains DBCS characters,
you must append an M to the string-constant. For example:
EXEC SQL PREPARE S13 FROM
'SELECT TRANSLATE(''laabb'') || ''l<(AB)>'' FROM SYSTEM.SYSCCSIDS'M;
When coding graphic constants in static SQL statements, use one of the
following PL/I formats of the graphic constant:
1. '<(XXXX)>'G
2. <@'(XXXX)@'@G>
| Note: | N is a synonym for G.
|
When coding graphic constants in dynamically executed SQL statements, use
the SQL format of the graphic constant (that is,
G'<(XXXX)>'). Refer to Using Graphic Constants for a discussion of graphic
constants.
The first SQL statement encountered in a sequential scan of your
program by the PL/I preprocessor that requires an in-line call to the resource
adapter results in the generation of control blocks SQLTIE and RDIEXT, and
other declarations commonly used by internal DB2 Server for VSE & VM code
that is associated with the remaining SQL statements in your program.
If your program structure involves SQL statements in multiple procedures, you
must maintain structures so that the SQLTIE and RDIEXT are addressable by all
other SQL statement occurrences in your program.
Figure 126 represents an incorrect structure.
Figure 126. Incorrect PL/I Program Structure
A: PROC OPTIONS(MAIN);
.
.
CALL B;
CALL C;
.
.
B: PROC;
.
.
EXEC SQL CONNECT.....
EXEC SQL DECLARE C1 CURSOR....
EXEC SQL OPEN C1 ...
.
.
END B;
C: PROC;
.
.
EXEC SQL DECLARE C2 CURSOR....
EXEC SQL OPEN C2 .....
.
.
.
END C;
.
.
END A;
|
SQLTIE and RDIEXT will be generated from the CONNECT in B, but it is not
addressable from C, where other SQL statements appear. This can be
solved by putting the CONNECT statement in A, where it will cause SQLTIE and
RDIEXT to be generated at a place that is addressable by both B and C.
When executing PL/I application programs in VSE single user mode,
specify SIZE=750K, not SIZE=AUTO, in the EXEC job control statement.
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]