Reserved Words in DB2 9 for z/OS

I’ve been seeing many questions related to reserved words lately on the DB2-L list serve.  When designing applications I usually like to keep a list of current reserved words available and cross-check any table object names or column names with this list. You can find the current list of reserved words in appendix B of the SQL Reference Guide. What's even more interesting is I was writing an SQL statement yesterday and used “IS” as a table-alias qualifier and couldn't figure out at first why I was getting:

SQLCODE -199.

This is an example of what I’m talking about:

SELECT is.col1
              , is.col2
              , is.col3
FROM table_name1 is
          , table_name2 t2
WHERE is.col1 = t2.col1

The -199 error I received:

DSNT408I SQLCODE = -199, ERROR:  ILLEGAL USE OF KEYWORD IS.  TOKEN ) UNION 
         EXCEPT WAS EXPECTED

Then I figured, "Hey, maybe “IS’ is a reserved word." So I changed my IS to S2 and my statement ran fine. 

This got me to thinking about reserved words. Where does the use of a reserved word cause problems? 

Taking a look at the DB2 SQL Reference appendix B, I’m told first that certain restrictions are at the database manager and in some cases, names are reserved and cannot be used by the application. I’m sure this depends on the language compiler being used.

I figured I would use the DB2 catalog to experiment with reserved words and their impact on dynamic SQL using SPUFI and also in static SQL using COBOL. The follow SQL will provide a list of DB2 catalog tables with columns in the reserved word list as of DB2 9 for z/OS.

SELECT SUBSTR(TBCREATOR,1,6)
                CONCAT '.' CONCAT  SUBSTR(TBNAME,1,18) AS TABLE_NAME
    ,  SUBSTR(NAME,1,18) AS COLUMN_NAME         
FROM SYSIBM.SYSCOLUMNS                         
WHERE TBCREATOR = 'SYSIBM'                     
  AND NAME IN
(                                                                     
  'ADD' , 'AFTER' , 'ALL' , 'ALLOCATE' , 'ALLOW' ,'ALTER' , 'AND'    
, 'ANY' , 'AS' ,'ASENSITIVE' , 'ASSOCIATE' , 'ASUTIME' , 'AT'         
, 'AUDIT' , 'AUX' , 'AUXILIARY' , 'BEFORE' , 'BEGIN'                  
, 'BETWEEN' , 'BUFFERPOOL' , 'BY' , 'CALL' , 'CAPTURE' , 'CASCADED'   
, 'CASE' , 'CAST' , 'CCSID' , 'CHAR' , 'CHARACTER' , 'CHECK' , 'CLONE'
, 'CLOSE' , 'CLUSTER' , 'COLLECTION' , 'COLLID' , 'COLUMN' , 'COMMENT'
, 'COMMIT' , 'CONCAT' , 'CONDITION' , 'CONNECT' , 'CONNECTION'       
, 'CONSTRAINT' , 'CONTAINS' , 'CONTENT' , 'CONTINUE' ,'CREATE'       
, 'CURRENT' , 'CURRENT_DATE' , 'CURRENT_LC_CTYPE' , 'CURRENT_PATH'   
, 'CURRENT_SCHEMA' , 'CURRENT_TIME' , 'CURRENT_TIMESTAMP'
, 'CURSOR'  , 'DATA' , 'DATABASE' , 'DAY' , 'DAYS' , 'DBINFO'
, 'DECLARE' ,  'DEFAULT' , 'DELETE' , 'DESCRIPTOR' , 'DETERMINISTIC'
, 'DISABLE'   , 'DISALLOW' , 'DISTINCT' , 'DO' , 'DOCUMENT' , 'DOUBLE' , 'DROP'    
, 'DSSIZE' , 'DYNAMIC' , 'EDITPROC' , 'ELSE' , 'ELSEIF' , 'ENCODING' 
, 'ENCRYPTIONEND' , 'ENDINGEND-EXEC' , 'ERASE' , 'ESCAPE' , 'EXCEPT' 
, 'EXCEPTION' , 'EXECUTE' , 'EXISTS' , 'EXIT' , 'EXPLAIN' , 'EXTERNAL'
, 'FENCED' , 'FETCH' , 'FIELDPROC' , 'FINAL' , 'FOR' , 'FREE' , 'FROM'
, 'FULL' , 'FUNCTION' , 'GENERATED' , 'GET' , 'GLOBAL' , 'GO' , 'GOTO'
, 'GRANT' , 'GROUP' , 'HANDLER' , 'HAVING' , 'HOLD' , 'HOUR' , 'HOURS'
, 'IF' ,'IMMEDIATE' , 'IN' , 'INCLUSIVE' , 'INDEX' , 'INF'            
, 'INFINITY' , 'INHERIT' , 'INNER' , 'INOUT' , 'INSENSITIVE' , 'INSERT
, 'INTERSECT' , 'INTO' , 'IS' , 'ISOBID' , 'ITERATE' , 'JAR' , 'JOIN'
, 'KEEP' , 'KEY' , 'LABEL' , 'LANGUAGE' , 'LC_CTYPE' , 'LEAVE' , 'LEFT
, 'LIKE' , 'LOCAL' , 'LOCALE' , 'LOCATOR' , 'LOCATORS' , 'LOCK'      
, 'LOCKMAX' , 'LOCKSIZE' ,'LONG' , 'LOOP' , 'MAINTAINED'             
, 'MATERIALIZED' , 'MICROSECOND' , 'MICROSECONDS' , 'MINUTE'         
, 'MINUTES' , 'MODIFIES' , 'MONTH' , 'MONTHS' , 'NAN', 'NEXTVAL'      
, 'NO', 'NONE', 'NOT' , 'NULL' , 'NULLS', 'NUMPARTS' , 'OBID', 'OF'   
, 'ON' , 'OPEN' , 'OPTIMIZATION' , 'OPTIMIZE', 'OR', 'ORDER', 'OUT'   
, 'OUTER' , 'PACKAGE' , 'PARAMETER' , 'PART' , 'PADDED' , 'PARTITION'
, 'PARTITIONED' , 'PARTITIONING' , 'PATH' , 'PIECESIZE' , 'PLAN'      
, 'PRECISION' , 'PREPARE' , 'PREVVAL' , 'PRIQTY' , 'PRIVILEGES'      
, 'PROCEDURE' , 'PROGRAM' , 'PSID' , 'PUBLIC' , 'QUERY' , 'QUERYNO'   
, 'READS' , 'REFERENCES' , 'REFRESH' , 'RESIGNAL' , 'RELEASE' , 'RENAME’
, 'REPEAT' , 'RESTRICT' , 'RESULT' , 'RESULT_SET_LOCATOR' , 'RETURN' 
, 'RETURNS' , 'REVOKE' , 'RIGHT' , 'ROLE' , 'ROLLBACK' , 'ROUND_CEILING’
, 'ROUND_DOWN' , 'ROUND_FLOOR' , 'ROUND_HALF_DOWN'
, 'ROUND_HALF_EVEN' , 'ROUND_HALF_UP' , 'ROUND_UP'
, 'ROW' , 'ROWSET' , 'RUN' , 'SAVEPOINT’ , 'SCHEMA'
, 'SCRATCHPAD' , 'SECOND' , 'SECONDS' , 'SECQTY' , 'SECURITY’
, 'SEQUENCE' , 'SELECT' , 'SENSITIVE' , 'SESSION_USER' , 'SET'       
, 'SIGNAL' , 'SIMPLE' , 'SNAN' , 'SOME' , 'SOURCE' , 'SPECIFIC'      
, 'STANDARD' , 'STATIC' , 'STATMENT' , 'STAY' , 'STOGROUP' , 'STORES'
, 'STYLE' , 'SUMMARY' , 'SYNONYM' , 'SYSFUN' , 'SYSIBM' , 'SYSPROC'   
, 'SYSTEM' , 'TABLE' , 'TABLESPACE' , 'THEN' , 'TO' , 'TRIGGER'      
, 'TRUNCATE' , 'TYPE' , 'UNDO' , 'UNION' , 'UNIQUE' , 'UNTIL'
, 'UPDATE’ , 'USER' , 'USING' , 'VALIDPROC' , 'VALUE' , 'VALUES'
, 'VARIABLE'  , 'VARIANT' , 'VCAT' , 'VIEW' , 'VOLATILE' , 'VOLUMES'
, 'WHEN'  ,  'WHENEVER' , 'WHERE' , 'WHILE' , 'WITH' , 'WLM'
, 'XMLELEMENT' , 'XMLEXISTS' , 'XMLNAMESPACES' , 'YEAR', 'YEARS'                     
)                                                                     
ORDER BY 1, 2 
WITH UR                                                                     

Using the SQL above, I searched the V7 catalog and found 81 columns; V8 contained 84 columns and DB2 9 for z/OS has 104 columns found in the reserved word list. This tells me the developers aren't paying attention to the reserved word list or they know it won't be a problem with processing because they're using the escape character of double quotes around the column names.

To test my theory on column names I used the following SQL:

SELECT CCSID               
     , DEFAULT             
     , LABEL               
     , NULLS               
FROM SYSIBM.SYSCOLUMNS      
WHERE TBCREATOR = 'SYSIBM' 
  AND TBNAME = 'SYSCOLUMNS'
WITH UR                     

I was surprised to find this ran fine.

So I figured I would try this out in a COBOL program. Guess what? I had no problem in the precompiler, but I did have a problem in the COBOL compiler with reserved word TABLE and LENGTH.

Using the following working storage:

*****************************************************
* WORKAREAS                                         *
*****************************************************
01  DCLSYSCOLUMNS.                                  
     10 NAME.                                        
        49 NAME-LEN          PIC S9(4) USAGE COMP.   
        49 NAME-TEXT         PIC X(128).             
     10 TABLE.                                       
        49 TBNAME-LEN        PIC S9(4) USAGE COMP.   
        49 TBNAME-TEXT       PIC X(128).             
     10 CREATE.                                       
        49 TBCREATOR-LEN     PIC S9(4) USAGE COMP.   
        49 TBCREATOR-TEXT    PIC X(128).             
     10 LENGTH               PIC S9(4) USAGE COMP.   
     10 NULLS                PIC X(1).               

I received the following errors:

    IGYDS0148-S   "TABLE" is a reserved word related to language
                  not supported by this compiler.

    IGYDS1089-S   "LENGTH" was invalid. Scanning was resumed at
                  the next area "A" item, level-num clause.

    IGYDS1089-S   "NULLS" was invalid. Scanning was resumed at
                  the next area "A" item, level-num clause.      

So I changed my host variables to COL-NAME, COL-TABLE, COL-CREATE, COL-LENGTH, COL-NULLS and the compile was clean.

The documentation tells me I cannot use a column name of “ALL” so I set out to try it.
Using the following DDL:

CREATE TABLE CREATOR.TABLE                  
  (ALL       CHAR(6)        NOT NULL,
   IN        VARCHAR(12)    NOT NULL,
   DECIMAL   DECIMAL(9,2)   NOT NULL,
   PRIMARY   DECIMAL(9,2)   NOT NULL,
   KEY       DECIMAL(9,2)   NOT NULL,
   PRIMARY KEY(ALL)                  
   )
                               
I was able to run the following select statement:

SELECT ALL, IN, DECIMAL, PRIMARY, KEY   
FROM TABLE                              
WHERE ALL = ' '                         
ORDER BY IN                              

So I thought something must be wrong with the documentation. Then I read that some reserved words aren't enforced on z/OS but are on LUW. So I created this table on LUW and it worked. This made me think that DB2 is smart and is figuring out what the word is based on the context of how it is being used in the SQL statement. So I simplified the SQL to the following:

  SELECT ALL FROM TABLE;

This causes the error I was looking for:

  DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL
  "<END-OF-STATEMENT>". SOME SYMBOLS THAT MIGHT BE LEGAL
  ARE: , FROM INTO                      

To verify that DB2 can figure this out, I used the escape character double quotes:

SELECT “ALL” FROM TABLE;

This ran successfully as I had expected.

From an SQL standpoint it may be a good practice to always code your SQL with double quotes around the column and table identifier. What was a valid word in the current release of DB2 may become a reserved word in a future release.

Examples of using double quotes around column "ALL" and table “TABLE” and identifier “IS”:

   SELECT "IS"."ALL"         
   FROM "CREATOR"."TABLE" "IS" 
   WHERE "IS"."IN" = 'VALUE'         

In my mind I would say as a standard, it's best to avoid using reserved words. However, to make sure your code is portable between LUW and z/OS and to ensure your code is valid in future releases of DB2, you may want to consider using double quotes “” around your column names, table creator, table name, and table identifier.

I hope this helps and if you have any comments please feel free to post them at DB2utor. Thank you.

Comments

[Adrian]

Hi,

There's two typo's in the SQL at 'RENAM and 'ROUND_CEILIN. I guess you did a cut/paste and got truncated. Also SUBSTR(NAME,1,20) returns an error in a V8 CM environment.

...adg

[Troy Coleman]

Hi Adrian,
Yes I did a cut and paste and I'm not sure how the SUBSTR length was set to 20 when it should have been 18. I've made the needed changes.

Troy

[Thanikachalam]

How will you define a table with Db2 Reserve words as one of column in it? Do we need to take any special attention....

Pls answer... Your effort is greatly appreciated.

[Troy Coleman]

The interesting thing is the SQL parser has changed. It is much smarter in DB2 9 than it was back in version 8. Normally I would say you need to put quotes "IS" around the text. I just ran a test using "IS" without quotes as a table name and as a column name and it worked. But in general you want to avoid using reserved words and when you do use reserved words you want to put quotes around them.