Table and View Definitions

Before your program issues SQL statements that retrieve, update, delete, or insert data, you should declare the tables and views your program accesses. To do this, include an SQL DECLARE statement in your program.

You do not have to declare tables or views, but there are advantages if you do. One advantage is documentation. For example, the DECLARE statement specifies the structure of the table or view you are working with and the data type of each column. You can refer to the DECLARE statement for the column names and data types in the table or view. Another advantage is that the DB2 precompiler uses your declarations to make sure that you have used correct column names and data types in your SQL statements. The DB2 precompiler issues a warning message when the column names and data types do not correspond to the SQL DECLARE statements in your program. If the DECLARE statements are omitted, the precompiler will also generate a warning.

One way to declare a table or a view is to code a DECLARE statement in the working-storage section, or linkage section, within the data division of your COBOL program. Specify the name of the table, and list each column and its data type. When you declare a table or a view, you specify the table name in the DECLARE TABLE statement, regardless of whether the table name refers to a table or a view. For example, the DECLARE TABLE statement for the DB2USER1.TEST table looks like this:

 EXEC SQL DECLARE DB2USER1.TEST_TABLE   (NUMBER         CHAR(6)         NOT NULL,    NAME           VARCHAR(50)     NOT NULL,    TYPE           CHAR(1)         NOT NULL,    CUT_SCORE      DECIMAL(6,2),    LENGTH         SMALLINT        NOT NULL,    TOTALTAKEN     SMALLINT        NOT NULL) END-EXEC. 


An alternative to coding the DECLARE statement manually is to use DCLGEN, the declarations generator supplied with DB2. DCLGEN is covered later in this chapter.


When a table or a view that contains a column with a distinct type is declared, it is best to declare that column with the source type of the distinct type rather than with the distinct type itself. When the column is declared with the source type, DB2 can check embedded SQL statements that reference that column at precompile time.

DB2 for z. OS Version 8 DBA Certification Guide
DB2 for z/OS Version 8 DBA Certification Guide
ISBN: 0131491202
EAN: 2147483647
Year: 2003
Pages: 175
Authors: Susan Lawson © 2008-2017.
If you may any questions please contact us: