| Team-Fly | | | DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson | Table of Contents | | Chapter 5. Using SQL in an Application Program | Declaring Table and View Definitions Before your program issues SQL statements that retrieve, update, delete, or insert data, you must declare the tables and views your program accesses . To do this, include a 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 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. A way to declare a table or 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 view, you specify the table name in the DECLARE 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. NOTE | 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. | NOTE | When a table or view is declared that contains a column with a distinct type, 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. | |