Embedded SQL Programming Guide

Prototyping Your SQL Statements

As you design and code your application, you can take advantage of certain database manager features and tools to prototype portions of your SQL code, and to improve performance. For example, you can do the following:

• Use the command line processor (CLP) to test many SQL statements before having to compile and link a complete program.

  This allows you to define and manipulate information stored in a database table, index, or view. You can add, delete, or update information as well as generate reports from the contents of tables. Note that you have to minimally change the CLP syntax for some SQL statements in order to use host variables in your program. Host variables are used to store data that is output to your screen using the CLP. In addition, some embedded SQL statements (such as BEGIN DECLARE SECTION) are not supported by the CLP as they are not relevant to that environment. See Table 22 to see which SQL statements are not supported by the CLP.

  You can also redirect the input and output of command line processor requests. For example, you could create one or more files containing SQL statements you need as input into a command line processor request, thereby not having to keep retyping the statement.

  For information about the command line processor, see the Command Reference.

• Use the Explain facility to get an idea of the estimated costs of the DELETE, INSERT, UPDATE, or SELECT statements you plan to use in your program. The Explain facility places the information about the structure and the estimated costs of the subject statement into user supplied tables. You can view this information using Visual Explain or the db2exfmt utility.

  For information about how to use the Explain facility, see the Administration Guide.

• Use the system catalog views to easily retrieve information about existing databases. The system catalog tables on which the views are based are created and maintained by the database manager during normal operation as databases are created, altered, and updated. These views contain data about each database, including such things as authorities granted, column names, data types, indexes, package dependencies, referential constraints, table names, views, and so on. Data in the system catalog views is available through normal SQL query facilities.

  You can update some system catalog views containing statistical information used by the SQL optimizer. Some columns in these views may be changed to influence the optimizer or to investigate the performance of hypothetical databases. You can use this method to simulate a production system on your development or test system and analyze how queries perform.

  For a complete description of each system catalog view, see the appendix in the SQL Reference. For information about system catalog statistics and which ones you can change, see the Administration Guide.

[ DB2 List of Books | Search the DB2 Books ]