Embedded SQL Guidelines

Table 11.2 outlines the differences between a DB2 program with embedded SQL statements and an application program accessing flat files. Flat files and DB2 tables, however, are not synonymous. The functionality of the two types of data storage objects are quite dissimilar.

Table 11.2. DB2 Programming Versus Flat File Programming

DB2 Programming Considerations	Flat File Programming Considerations
No FD required for DB2 tables; DB2 tables must be declared	FD is required for each flat file to be processed by the program
No DD card needed in execution JCL for programs accessing DB2 tables	DD card required (unless the flat file is allocated dynamically)
DB2 tables need not be opened; instead, cursors are opened for each SQL statement [*]	Flat files must be opened before being processed
DB2 tables need not be closed; instead, cursors are closed for each SQL statement [*]	Flat files must be closed (if opened)
Set-level processing	Record-level processing
Access to tables can be specified at the column (field element) level	Access to files based on reading a full record; all fields are always read or written
Success or failure of data is indicated by SQL	VSAM return code indicates success or failure return code
No more data indicated by +100 SQL return code	End of file is reported to the program
Cursors used to mimic record-level processing (see the section on cursors)	READ and WRITE statements are used to implement record-level processing

^[*] DB2 opens and closes the VSAM data sets that house DB2 table spaces "behind the scenes."

Delimit All SQL Statements

You must enclose all embedded SQL statements in an EXEC SQL block. This way, you can delimit the SQL statements so that the DB2 precompiler can efficiently parse the embedded SQL. The format of this block is

 EXEC SQL  put text of SQL statement here  END-EXEC. 

For COBOL programs, you must code the EXEC SQL and END-EXEC delimiter clauses in your application program starting in column 12.

Explicitly DECLARE All DB2 Tables

Although you are not required to declare DB2 tables in your application program, doing so is good programming practice. Therefore, explicitly DECLARE all tables to be used by your application program. You should place the DECLARE TABLE statements in the WORKING-STORAGE section of your program, and they should be the first DB2- related variables defined in WORKING-STORAGE . This way, you can reduce the precompiler's work and make the table definitions easier to find in the program source code.

Additionally, standard DECLARE TABLE statements should be generated for every DB2 table. Create them with the DCLGEN command (covered in Chapter 13, "Program Preparation"), and then include them in your application program.

Comment Each SQL Statement

Make liberal use of comments to document the nature and purpose of each SQL statement embedded in your program. You should code all comments pertaining to embedded SQL in the comment syntax of the program's host language. Code COBOL comments as shown in the following example:

 Column Numbers          111 123456789012       **       **  Retrieve department name and manager from the       **  DEPT table for a particular department number.       **            EXEC SQL                SELECT   DEPTNAME, MGRNO                INTO     :HOSTVAR-DEPTNAME,                         :HOSTVAR-MGRNO                FROM     DEPT                WHERE    DEPTNO = :HOSTVAR-DEPTNO            END-EXEC. 

Include the SQLCA

You must include a structure called the SQLCA (SQL Communication Area) in each DB2 application program. You do so by coding the following statement in your WORKING-STORAGE section:

 EXEC SQL      INCLUDE SQLCA END-EXEC. 

The COBOL layout of the expanded SQLCA follows :

 01  SQLCA.     05  SQLCAID                   PIC X(8).     05  SQLCABC                   PIC S9(9) COMPUTATIONAL.     05  SQLCODE                   PIC S9(9) COMPUTATIONAL.     05  SQLERRM.         49  SQLERRML              PIC S9(4) COMPUTATIONAL.         49  SQLERRMC              PIC X(70).     05  SQLERRP                   PIC X(8).     05  SQLERRD                   OCCURS 6 TIMES                                   PIC S9(9) COMPUTATIONAL.     05  SQLWARN.         10  SQLWARN0              PIC X(1).         10  SQLWARN1              PIC X(1).         10  SQLWARN2              PIC X(1).         10  SQLWARN3              PIC X(1).         10  SQLWARN4              PIC X(1).         10  SQLWARN5              PIC X(1).         10  SQLWARN6              PIC X(1).         10  SQLWARN7              PIC X(1).     05  SQLEXT.         10  SQLWARN8              PIC X(1).         10  SQLWARN9              PIC X(1).         10  SQLWARNA              PIC X(1).         10  SQLSTATE              PIC X(5). 

The SQLCA is used to communicate information describing the success or failure of the execution of an embedded SQL statement. The following list defines each SQLCA field:

SQLCAID	Set to the constant value SQLCA to enable easy location of the SQLCA in a dump.
SQLCABC	Contains the value 136 , the length of the SQLCA .
SQLCODE	Contains the return code passed by DB2 to the application program. The return code provides information about the execution of the last SQL statement. A value of zero indicates successful execution, a positive value indicates successful execution but with an exception, and a negative value indicates that the statement failed.
SQLERRM	This group -level field consists of a length and a message. SQLERRML contains the length of the message in SQLERRMC . The message contains additional information about any encountered error condition. Usually, only technical support personnel use this field for complex debugging situations, when the value of SQLCODE is not sufficient. Note that if SQLERRML is set to 70 the message may have been truncated.
SQLERRP	Contains the name of the CSECT that detected the error reported by the SQLCODE . This information is not typically required by application programmers.
SQLERRD	This array contains six values used to diagnose error conditions. Only SQLERRD(3) and SQLERRD(5) are of use to most application programmers:
	SQLERRD(1) is the relational data system error code.
	SQLERRD(2) is the Data Manager error code.
	SQLERRD(1) and SQLERRD(2) can contain the number of rows in a result table when the cursor position is after the last row for a sensitive static cursor when the SQLCODE is +100 .
	SQLERRD(3) is the number of rows inserted, deleted, or updated by the SQL statement.
	SQLERRD(3) also will contain the number of rows in a rowset for a multirow FETCH and the number of rows inserted by a REFRESH TABLE .
	SQLERRD(4) is the estimate of resources required for the SQL statement (timerons).
	SQLERRD(5) is the column (position) of the syntax error for a dynamic SQL statement.
	SQLERRD(6) is the Buffer Manager error code.
SQLWARN0	Contains W if any other SQLWARN field is set to W .
SQLWARN1	Contains W if a character column is truncated when it is assigned to a host variable by the SQL statement.
SQLWARN2	Contains W when a null-valued column is eliminated by built-in function processing.
SQLWARN3	Contains W when the number of columns retrieved does not match the number of fields in the host variable structure into which they are being selected.
SQLWARN4	Contains W when the SQL statement is an UPDATE or DELETE without a WHERE clause.
	For a scrollable cursor, SQLWARN4 contains a D for sensitive dynamic cursors, I for insensitive cursors, and S for sensitive static cursors after the OPEN CURSOR , ALLOCATE CURSOR , or DESCRIBE CURSOR statement; blank if cursor is not scrollable.
SQLWARN5	Contains W when an SQL statement that applies only to SQL/DS is issued.
SQLWARN6	Contains W when a DATE or TIMESTAMP conversion is performed during date arithmetic. For example, if 4 months are added to 2003-01-31 , the result is 2003-04-31 . Because April does not have 31 days, the results are converted to 2003-04-30 .
SQLWARN7	Contains W when non-zero digits are dropped from the fractional part of a number used as the operand of a divide or multiply operation.
SQLWARN8	Contains W if a substitute character is used when a conversion routine cannot convert the character.
SQLWARN9	Contains W when COUNT DISTINCT processing ignores an arithmetic exception.
SQLWARNA	Contains W when any form of character conversion error is encountered.
SQLSTATE	Contains a return code indicating the status of the most recent SQL statement.

Check SQLCODE or SQLSTATE

SQLCODE contains the SQL return code, which indicates the success or failure of the last SQL statement executed. SQLSTATE is similar to SQLCODE but is consistent across DB2 (and ANSI-compliant SQL) platforms.

Code a COBOL IF statement immediately after every SQL statement to check the value of the SQLCODE . In general, gearing your application programs to check for SQLCODE s is easier because a simple condition can be employed to check for negative values.

If the SQLCODE returned by the SQLCA is less than zero, an SQL "error" was encountered. The term error , in this context, is confusing. A value less than zero could indicate a condition that is an error using SQL's terminology but is fine given the nature of your application. Thus, certain negative SQL codes are acceptable depending on their context.

For example, suppose that you try to insert a row into a table and receive an SQL code of -803 , indicating a duplicate key value. (The row cannot be inserted because it violates the constraints of a unique index.) In this case, you might want to report the fact (and some details) and continue processing. You can design your application programs to check SQLCODE values like this instead of first checking to make sure that the insert does not violate a unique constraint, and only then inserting the row.

Check the SQLSTATE value, however, when you must check for a group of SQLCODE s associated with a single SQLSTATE or when your program runs on multiple platforms. SQLSTATE values consist of five characters : a two-character class code and a three-character subclass code. The class code indicates the type of error, and the subclass code details the explicit error within that error type.

You can find a complete listing of SQLCODE s, SQLSTATE s, and SQLSTATE class codes in the IBM DB2 Messages and Codes manual. Some of the most common DB2 SQLCODE values are listed on the inside back cover of this book.

Consider Using GET DIAGNOSTICS

DB2 Version 8 augments DB2 error handling with the new GET DIAGNOSTICS statement. GET DIAGNOSTICS complements and extends the diagnostics available in the SQLCA. This is necessary because some error messages will not fit into the 70-byte SQLERRRC field.

GET DIAGNOSTICS provides additional information than is provided in the SQLCA. The information it provides can be broken down into several areas: statement information, condition information, connection information, and combined information. Combined information contains a text representation of all the information gathered about the execution of the SQL statement. Table 11.3 delineates the many different types of diagnostic information that can be returned with GET DIAGNOSTICS .

You use GET DIAGNOSTICS similarly to how you check the SQLCODE : You issue it after executing an SQL statement, and it returns information about the execution of the last statement, along with at least one instance of condition information.

GET DIAGNOSTICS is particularly useful for checking each condition when multiple conditions can occur ”such as with multiple row processing. GET DIAGNOSTICS also supports SQL error message tokens larger than the 70-byte limit of the SQLDA.

A quick example using GET DIAGNOSTICS follows:

 GET DIAGNOSTICS :RC = ROW_COUNT; 

After executing this statement the :RC host variable will be set to the number of rows that were affected by the last SQL statement that was executed.

Table 11.3. GET DIAGNOSTICS Details

Type	Specific Name	Description
Statement	DB2_GET_DIAGNOSTICS_DIAGNOSTICS	Textual information about errors or warnings in the execution of the GET DIAGNOSTICS statement.
	DB2_LAST_ROW	For a multiple-row FETCH statement, contains a value of +100 if the last row currently in the table is in the set of rows that have been fetched .
	DB2_NUMBER_PARAMETER_MARKERS	For a PREPARE statement, contains the number of parameter markers in the prepared statement.
	DB2_NUMBER_RESULT_SETS	For a CALL statement, contains the actual number of result sets returned by the procedure.
	DB2_RETURN_STATUS	Identifies the status value returned from the previous CALL statement (for SQL procdures).
	DB2_SQL_ATTR_CURSOR _HOLD	For an ALLOCATE or OPEN statement, indicates whether a cursor can be held open across multiple units of work.
	DB2_SQL_ATTR_CURSOR_ROWSET	For an ALLOCATE or OPEN statement, indicates whether a cursor can be accessed using rowset positioning.
	DB2_SQL_ATTR_CURSOR_SCROLLABLE	For an ALLOCATE or OPEN statement, indicates whether a cursor can be scrolled forward and backward.
	DB2_SQL_ATTR_CURSOR_SENSITIVITY	For an ALLOCATE or OPEN statement, indicates whether a cursor does or does not show updates to cursor rows made by other connections.
	DB2_SQL_ATTR_CURSOR_TYPE	For an ALLOCATE or OPEN statement, indicates the type of cursor: normal, scrollable static, or scrollable dynamic.
	MORE	Indicates whether there are more warning and errors than could be stored (or whether all the warnings and errors are stored).
	NUMBER	Returns the number of errors and warnings detected by the execution of the previous SQL statement.
	ROW_COUNT	Identifies the number of rows associated with the previous SQL statement.
Condition	CATALOG_NAME	Returns the name of the constraint or table that caused the error.
	CONDITION_NUMBER	Returns the number of the diagnostic returned.
	CURSOR_NAME	Returns the name of the cursor.
	DB2_ERROR_CODE1	Returns an internal error code.
	DB2_ERROR_CODE2	Returns an internal error code.
	DB2_ERROR_CODE3	Returns an internal error code.
	DB2_ERROR_CODE4	Returns an internal error code.
	DB2_INTERNAL_ERROR_POINTER	For some errors, this is a negative value that is an internal error pointer.
	DB2_LINE_NUMBER	For a CREATE PROCEDURE (for SQL procedures), contains the line number where an error possibly occurred in the procedure code.
	DB2_MODULE_DETECTING_ERROR	Returns an identifier indicating which module detected the error.
	DB2_ORDINAL_TOKEN_ n	Returns the n th token, where n is a value between 1 and 100.
	DB2_REASON_CODE	Contains the reason code for errors that have a reason code token in their message text.
	DB2_RETURNED_SQLCODE	Returns the SQLCODE for the specified diagnostic.
	DB2_ROW_NUMBER	For a statement involving multiple rows, the row number where DB2 detected the exception.
	DB2_TOKEN_COUNT	Returns the number of tokens available for the specified diagnostic ID.
	MESSAGE_OCTET_LENGTH	The length of the message text (in bytes).
	MESSAGE_TEXT	The message text associated with the SQLCODE (including substituted tokens).
	RETURNED_SQLSTATE	Returns the SQLSTATE for the specified diagnostic.
	SERVER_NAME	For CONNECT , DISCONNECT , or SET CONNECTION statements, the name of the server; otherwise , the name of the server where the statement executes is returned.
Connection	DB2_AUTHENTICATION_TYPE	Contains the authentication type: server, client, DB2 Connect, DCE security services, or blank.
	DB2_AUTHORIZATION_ID	Authorization ID used by connected server.
	DB2_CONNECTION_STATE	Whether the connection is unconnected, local, or remote.
	DB2_CONNECTION_STATUS	Whether committable updates can be performed on the connection for this unit of work.
	DB2_SERVER_CLASS_NAME	Contains a value specifying the platform of the server.
	DB2_ENCRYPTION_TYPE	The level of encryption.
	DB2_PRODUCT_ID	Returns a product signature.
Combined	ALL	All of the diagnostic items combined into a single string.
	STATEMENT	All statement diagnostic items combined into a single string.
	CONNECTION	All connection diagnostic items combined into a single string.

Standardize Your Shop's Error Routine

Consider using a standardized error-handling paragraph, one that can be used by all DB2 programs in your shop. The programs should load values to an error record that can be interpreted by the error-handling paragraph. When a severe error is encountered, the programs invoke the error-handling paragraph.

The error-handling paragraph should do the following:

1. Call the DSNTIAR module, a program provided with DB2 that returns standard, textual error messages for SQLCODE s.

2. Display, print, or record the following information: the error record identifying the involved table, the paragraph, and pertinent host variables; the error text returned by DSNTIAR ; and the current values in the SQLCA .

3. Issue a ROLLBACK . (This action is not absolutely required because an implicit rollback occurs if one is not requested .)

4. Call an ABEND module to generate a dump.

Your error-handling paragraph can be as complex and precise as you want. Depending on the SQL code, different processing can occur; for example, you might not want to abend the program for every SQLCODE .

Listing 11.1 shows sample COBOL code with an error-handling paragraph as just described. You can tailor this code to meet your needs.

Listing 11.1. Sample COBOL Error-Handling Paragraph

 .        .        . WORKING-STORAGE SECTION.        .        .        . 77  ERROR-TEXT-LENGTH           PIC S9(9)   COMP VALUE +960. 01  ERROR-RECORD.     05  FILLER                  PIC X(11)   VALUE 'SQLCODE IS '.     05  SQLCODE-DISP            PIC -999.     05  FILLER                  PIC X(05)   VALUE SPACES.     05  ERROR-TABLE             PIC X(18).     05  ERROR-PARA              PIC X(30).     05  ERROR-INFO              PIC X(40). 01  ERROR-MESSAGE.     05  ERROR-MSG-LENGTH        PIC S9(9)   COMP VALUE +960.     05  ERROR-MSG-TEXT          PIC X(120)  OCCURS 8 TIMES                                             INDEXED BY ERROR-INDEX. 01  ERROR-ROLLBACK.     05  FILLER          PIC X(20)   VALUE 'ROLLBACK SQLCODE IS '.     05  SQLCODE-ROLLBACK        PIC -999.        .        . PROCEDURE DIVISION.        .        . 1000-SAMPLE-PARAGRAPH.     EXEC SQL  SQL statement here  END-EXEC.     IF SQLCODE IS LESS THAN ZERO        MOVE SQLCODE                 TO SQLCODE-DISP        MOVE '  Table_Name  '            TO ERR-TABLE        MOVE '1000-SAMPLE-PARAGRAPH' TO ERR-PARA        MOVE 'Misc info, host variables, etc.'     TO ERR-INFO        PERFORM 9999-SQL-ERROR     ELSE  Resume normal processing.  .        . 9990-SQL-ERROR.     DISPLAY ERR-RECORD.     CALL 'DSNTIAR' USING SQLCA,                          ERROR-MESSAGE,                          ERROR-TEXT-LENGTH.     IF RETURN-CODE IS EQUAL TO ZERO        PERFORM 9999-DISP-DSNTIAR-MSG            VARYING ERROR-INDEX FROM 1 BY 1            UNTIL ERROR-INDEX > 8     ELSE        DISPLAY 'DSNTIAR ERROR'        CALL  'abend module'  .     DISPLAY 'SQLERRMC   ', SQLERRMC.     DISPLAY 'SQLERRD1   ', SQLERRD(1).     DISPLAY 'SQLERRD2   ', SQLERRD(2).     DISPLAY 'SQLERRD3   ', SQLERRD(3).     DISPLAY 'SQLERRD4   ', SQLERRD(4).     DISPLAY 'SQLERRD5   ', SQLERRD(5).     DISPLAY 'SQLERRD6   ', SQLERRD(6).     DISPLAY 'SQLWARN0   ', SQLWARN0.     DISPLAY 'SQLWARN1   ', SQLWARN1.     DISPLAY 'SQLWARN2   ', SQLWARN2.     DISPLAY 'SQLWARN3   ', SQLWARN3.     DISPLAY 'SQLWARN4   ', SQLWARN4.     DISPLAY 'SQLWARN5   ', SQLWARN5.     DISPLAY 'SQLWARN6   ', SQLWARN6.     DISPLAY 'SQLWARN7   ', SQLWARN7.     DISPLAY 'SQLWARN8   ', SQLWARN8.     DISPLAY 'SQLWARN9   ', SQLWARN9.     DISPLAY 'SQLWARNA   ', SQLWARNA.     EXEC SQL        ROLLBACK     END-EXEC.     IF SQLCODE IS NOT EQUAL TO ZERO         DISPLAY 'INVALID ROLLBACK'         MOVE SQLCODE      TO SQLCODE-ROLLBACK         DISPLAY ERROR-ROLLBACK.     CALL  'abend module'  . 9990-EXIT.     EXIT. 9999-DISP-DSNTIAR-MSG.     DISPLAY ERROR-MSG-TEXT(ERROR-INDEX). 9999-EXIT.     EXIT. 

When a negative SQLCODE is encountered ”in paragraph 1000, for example ”an error message is formatted and an error paragraph is performed. The error paragraph displays the error message returned by DSNTIAR , dumps the contents of the SQLCA , and rolls back all updates, deletes, and inserts since the last COMMIT point.

NOTE

Use a formatted WORKING-STORAGE field to display the SQLCODE ; otherwise, the value will be unreadable.

You can code the error-handling paragraph in Listing 11.1 in a copy book that can then be included in each DB2 program. This way, you can standardize your shop's error processing and reduce the amount of code that each DB2 programmer must write.

Handle Errors and Move On When Possible

Certain SQLCODE and/or SQLSTATE error conditions are not disastrous, and sometimes your program can continue to process after receiving an error code. Of course, it depends on the type of error and the type of work your program is performing. Consider coding your program to handle the "problem" codes, such as the common errors that are outlined in Table 11.4.

Table 11.4. Handling SQL Errors

SQLCODE	SQLSTATE	Meaning	Response
-904	57011	Resource unavailable	Try again (once), then attempt alternate processing; inform user and write out error message.
-911	40000	Timeout or deadlock	Retry several times and try to continue processing; if multiple failures, inform user and write out error message.
-53x	235xx	RI problem	Inform user, write out error message, and reverify data.
-551	42501	Authorization	Inform user of lack of authority to perform the function and write a log to record the attempted access.

Avoid Using WHENEVER

SQL has an error trapping statement called WHENEVER that you can embed in an application program. When the WHENEVER statement is processed, it applies to all subsequent SQL statements issued by the application program in which it is embedded. WHENEVER directs processing to continue or to branch to an error handling routine based on the SQLCODE returned for the statement. Several examples follow.

The following example indicates that processing will continue when an SQLCODE of +100 is encountered:

 EXEC SQL     WHENEVER NOT FOUND         CONTINUE END-EXEC. 

When a warning is encountered, the second example of the WHENEVER statement causes the program to branch to a paragraph (in this case, ERROR-PARAGRAPH ) to handle the warning:

 EXEC SQL     WHENEVER SQLWARNING         GO TO ERROR-PARAGRAPH END-EXEC. 

When any negative SQLCODE is encountered, the next WHENEVER statement branches to a paragraph (once again, ERROR-PARAGRAPH ) to handle errors:

 EXEC SQL     WHENEVER SQLERROR         GO TO ERROR-PARAGRAPH END-EXEC. 

Each of the three types of the WHENEVER statement can use the GO TO or CONTINUE option, at the discretion of the programmer. These types of the WHENEVER statements trap three "error" conditions:

NOT FOUND	The SQLCODE is equal to +100
SQLWARNING	The SQLCODE is positive but not +100 or SQLWARN0 equal to W
SQLERROR	The SQLCODE is negative

Avoid using the WHENEVER statement. It is almost always safer to code specific SQLCODE checks after each SQL statement and process accordingly . Additionally, you should avoid coding the GO TO verb as used by the WHENEVER statement. The GO TO construct is generally avoided in structured application programming methodologies.

Name DB2 Programs, Plans, Packages, and Variables Cautiously

Use caution when naming DB2 programs, plans, packages, and variables used in SQL statements. Do not use the following:

• The characters DB2, SQL, DSN , and DSQ

• SQL reserved words

You should avoid the listed character combinations for the following reasons. DB2 is too generic and could be confused with a DB2 system component. Because SQLCA fields are prefixed with SQL , using these letters with another variable name can cause confusion with SQLCA fields. IBM uses the three-character prefix DSN to name DB2 system programs and DSQ to name QMF system programs.

If SQL reserved words are used for host variables (covered in the next section) and are not preceded by a colon, an error is returned. However, you should not use these words even if all host variables are preceded by a colon . Avoiding these words in your program, plan, and variable names reduces confusion and ambiguity. Table 11.5 lists all DB2 SQL reserved words.

Table 11.5. SQL Reserved Words

ADD	AFTER	ALL	ALLOCATE
ALLOW	ALTER	AND	ANY
AS	ASSOCIATE	ASUTIME	AUDIT
AUX	AUXILIARY	BEFORE	BEGIN
BETWEEN	BUFFERPOOL	BY	CALL
CAPTURE	CASCADED	CASE	CAST
CCSID	CHAR	CHARACTER	CHECK
CLOSE	CLUSTER	COLLECTION	COLLID
COLUMN	COMMENT	COMMIT	CONCAT
CONDITION	CONNECT	CONNECTION	CONSTRAINT
CONTAINS	CONTINUE	CREATE	CURRENT
CURRENT_DATE	CURRENT_LC_CTYPE	CURRENT_PATH	CURRENT_TIME
CURRENT_TIMESTAMP	CURSOR	DATA	DATABASE
DAY	DAYS	DBINFO	DB2SQL
DECLARE	DEFAULT	DELETE	DESCRIPTOR
DETERMINISTIC	DISALLOW	DISTINCT	DO
DOUBLE	DROP	DSNHATTR	DSSIZE
DYNAMIC	EDITPROC	ELSE	ELSEIF
ENCODING	END	END-EXEC	ERASE
ESCAPE	EXCEPT	EXECUTE	EXISTS
EXIT	EXTERNAL	FENCED	FETCH
FIELDPROC	FINAL	FOR	FROM
FULL	FUNCTION	GENERAL	GENERATED
GET	GLOBAL	GO	GOTO
GRANT	GROUP	HANDLER	HAVING
HOUR	HOURS	IF	IMMEDIATE
IN	INDEX	INHERIT	INNER
INOUT	INSENSITIVE	INSERT	INTO
IS	ISOBID	JAR	JAVA
JOIN	KEY	LABEL	LANGUAGE
LC_CTYPE	LEAVE	LEFT	LIKE
LOCAL	LOCALE	LOCATOR	LOCATORS
LOCK	LOCKMAX	LOCKSIZE	LONG
LOOP	MICROSECOND	MICROSECONDS	MINUTE
MINUTES	MODIFIES	MONTH	MONTHS
NO	NOT	NULL	NULLS
NUMPARTS	OBID	OF	ON
OPEN	OPTIMIZATION	OPTIMIZE	OR
ORDER	OUT	OUTER	PACKAGE
PARAMETER	PART	PATH	PIECESIZE
PLAN	PRECISION	PREPARE	PRIQTY
PRIVILEGES	PROCEDURE	PROGRAM	PSID
QUERYNO	READS	REFERENCES	RELEASE
RENAME	REPEAT	RESTRICT	RESULT
RESULT_SET_LOCATOR	RETURN	RETURNS	REVOKE
RIGHT	ROLLBACK	RUN	SAVEPOINT
SCHEMA	SCRATCHPAD	SECOND	SECONDS
SECQTY	SECURITY	SELECT	SENSITIVE
SET	SIMPLE	SOME	SOURCE
SPECIFIC	STANDARD	STATIC	STAY
STOGROUP	STORES	STYLE	SUBPAGES
SYNONYM	SYSFUN	SYSIBM	SYSPROC
SYSTEM	TABLE	TABLESPACE	THEN
TO	TRIGGER	UNDO	UNION
UNIQUE	UNTIL	UPDATE	USER
USING	VALIDPROC	VALUE	VALUES
VARIANT	VCAT	VIEW	VOLUMES
WHEN	WHERE	WHILE	WITH
WLM	YEAR	YEARS

NOTE

As of DB2 V7, APPLICATION , NAME , and TYPE are no longer reserved words.

Additionally, IBM SQL reserves additional words. Using these words will not result in an error, but you should avoid their use to eliminate confusion. Additionally, these words are good candidates for future status as DB2 SQL reserved words when functionality is added to DB2. Table 11.6 lists all IBM SQL reserved words that are not also SQL reserved words. Therefore, Tables 11.5 and 11.6 collectively list all the IBM and DB2 SQL database reserved words.

Table 11.6. IBM SQL Reserved Words

ACQUIRE	ASC	AUTHORIZATION
AVG	CASCADE	COUNT
CROSS	DATE	DBA
DBSPACE	DECIMAL	DESC
EXCEPTION	EXCLUSIVE	EXPLAIN
FOREIGN	GRAPHIC	IDENTIFIED
INDICATOR	INTERSECT	MAX
MIN	MODE	NAMED
NHEADER	ONLY	OPTION
PAGE	PAGES	PCTFREE
PCTINDEX	PRIMARY	PRIVATE
PUBLIC	RESET	RESOURCE
ROW	ROWS	RRN
SCHEDULE	SHARE	STATISTICS
STORPOOL	SUBSTRING	SUM
TRANSACTION	TRIM	VARIABLE
WORK

NOTE

You also should avoid using any ANSI SQL reserved words (that are not already included in the previous two lists) in your program, plan, and variable names. Refer to the ANSI SQL standard for a list of the ANSI reserved words.

You can search for and order documentation on the ANSI SQL standard (and any other ANSI standard) at http://web.ansi.org/default.htm.

The guidelines in this section are applicable to every type of DB2 application program. Chapters 12 through 14 present guidelines for programming techniques used by specific types of DB2 application programs. Additionally, Chapter 15, "Using DB2 Stored Procedures," contains programming guidelines to follow when writing stored procedures.