Declaring table and view definitions

Before your program issues SQL statements that select, insert, update, or delete data, the program needs to declare the tables and views that those statements access.

About this task

Your program is not required to declare tables or views, but doing so offers the following advantages:
  • Clear documentation in the program

    The declaration specifies the structure of the table or view and the data type of each column. You can refer to the declaration for the column names and data types in the table or view.

  • Assurance that your program uses the correct column names and data types

    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 in SQL statements do not correspond to the table and view declarations in your program.

Procedure

To declare table and view definitions:

Perform one of the following actions:
  • Include an SQL DECLARE TABLE statement in your program. Specify the name of the table or view and list each column and its data type.

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

    In a COBOL program, code the DECLARE TABLE statement in the WORKING-STORAGE SECTION or LINKAGE SECTION within the DATA DIVISION.

    Example DECLARE statement in a COBOL program: The following DECLARE TABLE statement in a COBOL program defines the DSN8A10.DEPT table: 
    EXEC SQL
 DECLARE DSN8A10.DEPT TABLE
   (DEPTNO    CHAR(3)           NOT NULL,
    DEPTNAME  VARCHAR(36)       NOT NULL,
    MGRNO     CHAR(6)                   ,
    ADMRDEPT  CHAR(3)           NOT NULL,
    LOCATION  CHAR(16)                  ) 
END-EXEC.
  • Use DCLGEN, the declarations generator that is supplied with DB2, to create these declarations for you and then include them in your program.
    Restriction: You can use DCLGEN for only C, COBOL, and PL/I programs.