Database Testing Using SQL

You can use SQL as a tool for prototyping data designs and implementations during the application development process. For example, the ability to CREATE, ALTER, and DROP tables dynamically from an online, interactive environment lets you experiment with different designs.

SQL facilities support these data prototyping functions:
  • Online definition of model designs
  • Generation/loading of test data
  • Design documentation and analysis
  • Sharing host variables or SQL commands.

Online Definition of Model Designs: You can use ISQL to enter table, view, and index definitions for validating and testing data design. The interactive definition through ISQL offers you direct feedback on definitional errors. This feedback addresses both syntax and data mapping errors.

If you issue SQL definitional commands using ISQL, then it can save them as stored queries for later recall, modification, or rerun. You can also save statements in CMS files used as input (SYSIN) to the DBS utility.

Generation/Loading of Test Data: You can load tables created for design purposes with test data using these SQL facilities:
  • Item by item, using the ISQL INPUT command.
  • From existing SQL tables within the database, using the SQL INSERT command.
  • From existing SQL tables in another database, using the DBS UNLOAD and RELOAD commands.

Design Documentation and Analysis: By using the SQL explanation tables and the EXPLAIN command, you can analyze how a given design will perform. You can issue the EXPLAIN command using ISQL, the DBS utility, or an application program. EXPLAIN lets you get information about the structure and execution performance of a SQL command.

You can see how well a SELECT command performs by using the ISQL query cost estimate. ISQL displays this at the end of every SELECT result. This estimate of the resources used during command execution is related to, but is not the same as, that obtained by EXPLAIN.

Sharing Host Variables or SQL Commands: When you are developing programs, you may want to use the SQL INCLUDE command. This is useful when many applications use the same host variables or SQL command sequence. This command causes the preprocessors to include source lines from other CMS files in your source program.

Suppose you have a lengthy SELECT command that many programs use. First, place this SELECT command in a separate CMS file, called SOURCE1, for example. Then, in your source program, put the following SQL statement where you want to include the SELECT command: 
EXEC SQL INCLUDE SOURCE1 END-EXEC.

When developing a program with embedded SQL commands, you can run the SQL preprocessors with a CHECK option. This causes the preprocessor to generate diagnostics on the SQL in the program but not an access module or compiler input. You can thus use a skeleton of the final program to do a lot of initial code development and debugging.