DB2/SQL Statements

Coding SQL Statements	SQL Command Format
Supported SQL Statements	Special Statements

Coding SQL Statements

     [EXEC SQL] sqlstatement [;]

Please note the following:

1. You may enclose a host command in either single or double quotes. Double quotes is usually the better choice, since literals within SQL statements must be contained in single quotes. If you choose to enclose your SQL host commands in single quotes, remember that you must "double-up" on any single quotes within the command. For example:

   'exec sql select lastname from dsn8230.emp',
             'where firstnme = ''FRED'';'
   

2. As indicated by the syntax diagram, the "EXEC SQL" and the semicolon are optional.

Command	Description
ALTER	Structurally modifies certain DB2 objects.
BEGIN DECLARE SECTION	Indicates the beginning of variable declarations.
CLOSE	Closes a cursor.
COMMENT	Updates the descriptions of various DB2 objects.
COMMIT	Terminates a unit of work and makes permanent any changes which may have taken place. Also closes "no hold" cursors.
CONNECT	Connects an application process to a server.
CREATE	Creates various DB2 objects.
DECLARE CURSOR	Associates a SELECT statement with a cursor.
DECLARE TABLE	Used to document the structure of a table.
DELETE	Used to delete rows from a table or a view. Can be used in "searched" or "positioned" modes.
DESCRIBE TABLE	Places column information into an SQLDA.
DROP	"Un-creates" or removes DB2 objects.
END DECLARE SECTION	Marks the end of variable declarations.
EXPLAIN	Obtains path selection information about statements that use searches.
FETCH	Retrieves a row from an open cursor's result set.
GRANT	Authorizes access to DB2 objects.
INSERT	Adds new rows to a table or a view.
LABEL	Updates the labels associated with various DB2 objects.
LOCK	Obtains a table-wide lock.
OPEN	Opens a cursor for processing.
RELEASE	Releases connections.
REVOKE	Removes authorizations.
ROLLBACK	Terminates a unit of work and cancels any changes that may have been made. Closes both "hold" and "no hold" cursors.
SELECT INTO	Retrieves rows of information into host variables. The REXXTOOLS implementation retrieves multiple rows. See "Multi-row SELECT".
SET CONNECTION	Establishes an application server.
SET CURRENT DEGREE	Controls use of parallel I/O.
SET CURRENT PACKAGESET	Changes the value of the CURRENT PACKAGESET special register.
SET CURRENT SQLID	Changes the SQL authorization ID.
SET :var = special register	Assigns the value of a special register into a host variable.
UPDATE	Changes the rows in a table or view. Can be used in "searched" or "positioned" modes.

Special Statements

Multi-row SELECT

A statement for retrieving multiple rows from a table or view.

OPTIONS

A statement for setting Shadow REXXTOOLS processing options.

DECLARE VARIABLE

A statement for specifying the type and size of host variables.

FREE VARIABLE

A statement for removing variable definitions.

FREE CURSOR

A statement for removing cursor definitions.

The following sections describe each of these statements in detail.

Multi-row SELECT

select-clause
  [INTO host-var-expression[,host-var-expression...]]
  from-clause
  [where-clause]

The host-var-expressions of the optional INTO clause specify the names of the stem arrays into which the columns and rows of returned data are to be copied. If you do not code an INTO clause, the REXX stem names will be based on the names of the columns specified in the select-clause (See the topic "Variables")

The following program demonstrates the operation of the SELECT command:

/* REXX */
address sql
"select firstnme, salary",
  "into :fn:fni, :sal:sali",
  "from dsn8230.emp",
  "where salary > 20000"
if (rc=0) & (sqlca.sqlrows>0) then
  do i = 1 to sqlca.sqlrows
    say fn.i fni.i sal.i sali.i
  end

OPTIONS [SQLCA|NOSQLCA] [SQLDA|NOSQLDA] [MSGS|NOMSGS]
        [SUBSYS(subsys)] [PLAN(plan)]

Where:

SQLCA

indicates that the interface is to produce a full complement of SQLCA variables for all eligible statements (some statements such as declare never cause an SQLCA to be produced).

NOSQLCA

indicates that the interface is not to produce all of the SQLCA variables. This is the default setting. Please note the following exceptions:

1. SQLCA.SQLCODE is always produced for eligible statements (statements other than BEGIN, END, DECLARE, and FREE VARIABLE).
2. SQLCA.SQLROWS is always produced for Multi-row SELECT statements.
3. Whenever a non-zero SQLCODE is encountered, the entire SQLCA is produced.

SQLDA

indicates that the interface is to produce all of the SQLDA variables for all eligible statements (OPEN and SELECT).

NOSQLDA

indicates that the interface is not to produce any SQLDA variables. This is the default setting.

MSGS

indicates that messages are to be displayed. This is the default setting.

NOMSGS

indicates that messages are not to be displayed.

SUBSYS(subsys)

specifies the 1-4 character subsystem name to use when opening an implicit connection to DB2. See "Implicit OPENs".

PLAN(plan)

specifies the 1-8 character plan name to use when opening an implicit connection to DB2. See "Implicit OPENs".

Notes:

1. The SQLCA consists of 23 variables, and the SQLDA consists of 2 variables containing header information and 7 variables for each column selected (see "Variables" for more information on these). The creation of SQLCA/SQLDA variables is somewhat time-consuming. You can enhance the performance of your programs by using the NOSQLCA and NOSQLDA options whenever possible.
2. The OPTIONS command of the SQL host command environment shares responsibility for REXXTOOLS message processing with the OPTIONS command of ADDRESS REXXTOOL. Use of the MSGS/NOMSGS keywords in either host command environment affects message processing for the entire product.

Example

address sql "exec sql options sqlca sqlda;"

DECLARE varname[,varname...] VARIABLE db2type

do i = 1 to 10
  "exec sql insert into mytab (col1, col2)",
     "values(:a.i, :b.i);"
end

"exec sql declare a.i variable integer;"
"exec sql declare b.i variable decimal(5,0);"

The valid values for db2type are:

INTEGER

For a large integer.

SMALLINT

For a small integer.

REAL

For a single precision floating point number.

FLOAT

For a double precision floating point number.

DECIMAL(integer,integer)

For a packed decimal number. The first integer is the precision (the total number of digits), and the second is the scale (the number of fractional digits). Precision can range from 1 to 31. Scale can range from 0 to the precision, inclusive.

CHAR(integer)

For a fixed-length character string, of length integer, where integer can range from 1 to 254.

VARCHAR(integer)

For a varying-length character string, of maximum length integer, where integer can range from 1 to 32767.

GRAPHIC(integer)

For a fixed-length graphic (Double Byte Character Set) string of length integer, where integer can range from 1 to 127.

VARGRAPHIC(integer)

For a varying-length graphic (DBCS) string of maximum length integer, where integer can range from 1 to 16383.

DATE

For a date.

TIME

For a time.

TIMESTAMP

For a timestamp.

For more information on DB2 data types please refer to the DB2 SQL Reference.

The scope of a declaration is a REXX source program. You must ensure that any internal subroutines using declared variables expose them with the EXPOSE option of the REXX PROCEDURE statement.

Notes:

1. For db2type, all of the DB2 data-type specifications described under the topic "DECLARE TABLE" in the DB2 SQL Reference are accepted.
2. Only input variables requiring conversions (see "Input Variables") need to be declared. Undeclared input variables are assumed to be of type VARCHAR. Output variable declarations are accepted, but DB2-provided information will be used to drive conversions in all cases. Indicator variables need never be declared. If an indicator variable is not declared, its type, SMALLINT, is inferred from usage.
3. Variable names can be up to 250 bytes long (the REXX limit) and can contain any symbol that can be used in a REXX variable symbol, except the question mark.
4. The BEGIN/END DECLARE SECTION statements are not required. In the dynamic interface, variable declarations are executed statements, so you may intermingle them with other statements if you wish; however, if you desire a declaration for a variable, it must be executed before the variable is used.
5. The size specifications of declarations are checked, but not used. Neither truncation nor rounding is performed. Because of this, if you try to INSERT a value that is larger than its corresponding column, a negative SQLCODE will result.
6. The data type information for input variables is used to determine what conversions, if any, are required. For numeric types, your program must ensure that a variable's data is consistent with its declaration or a run-time error will result.

Example

"exec sql declare firstnme,lastname variable varchar(15);"
"exec sql declare salary variable decimal(9,2);"
"exec sql declare hiredate variable date;"

FREE [varname|*]  VARIABLE

Example

"exec sql free abc variable;"
"exec sql free * variable;"

FREE [csrname|*] CURSOR

Note: Cursors are unconditionally closed, prior to freeing. Because of this, it is permissible to FREE a cursor without closing it first.