IBM Books

Embedded SQL Programming Guide

Setting Up the Testing Environment

In order to perform many of the tasks described in the following sections, you should set up a test environment. For example, you need a database to test your application's SQL code.

A testing environment should include the following:

Creating a Test Database

If a test database must be created, create it by writing a small server application that calls the CREATE DATABASE API, or use the command line processor. See the Command Reference for information about the command line processor, or the API Reference for information about the CREATE DATABASE API.

Creating Test Tables

To design the test tables and views needed, first analyze the data needs of the application. To create a table, you need the CREATETAB authority and the CREATEIN privilege on the schema. Refer to the information on the CREATE TABLE statement in the SQL Reference for alternative authorities.

List the data the application accesses and describe how each data item is accessed. For example, suppose the application being developed accesses the TEST.TEMPL, TEST.TDEPT, and TEST.TPROJ tables. The type of accesses could be recorded as shown in Table 1.

Table 1. Description of the Application Data
Table or View Name Insert Rows Delete Rows Column Name Data Type Update Access
TEST.TEMPL No No 
EMPNO
LASTNAME
WORKDEPT
PHONENO
JOBCODE


CHAR(6)
VARCHAR(15)
CHAR(3)
CHAR(4)
DECIMAL(3)


 Yes
 Yes
 Yes

TEST.TDEPT No No 
DEPTNO
MGRNO


CHAR(3)
CHAR(6)


TEST.TPROJ Yes Yes 
PROJNO
DEPTNO
RESPEMP
PRSTAFF
PRSTDATE
PRENDATE


CHAR(6)
CHAR(3)
CHAR(6)
DECIMAL(5,2)
DECIMAL(6)
DECIMAL(6)


Yes
Yes
Yes
Yes
Yes

When the description of the application data access is complete, construct the test tables and views that are needed to test the application:

If the database schema is being developed along with the application, the definitions of the test tables might be refined repeatedly during the development process. Usually, the primary application cannot both create the tables and access them because the database manager cannot bind statements that refer to tables and views that do not exist. To make the process of creating and changing tables less time-consuming, consider developing a separate application to create the tables. Of course you can always create test tables interactively using the Command Line Processor (CLP).

Generating Test Data

Data can be inserted into a table using any of the following:

For information about the INSERT statement, see the SQL Reference. For information about the IMPORT, LOAD, and RESTORE utilities, see the Administration Guide.

A technique that you may want to use to populate your tables with randomly generated test data is embodied in the following SQL statements for a hypothetical table called EMP. Suppose this table contains four columns, ENO (employee number), LASTNAME (last name), HIREDATE (date of hire) and SALARY (employee's salary) as in the following CREATE TABLE statement: 

  CREATE TABLE EMP (ENO INTEGER, LASTNAME VARCHAR(30),
                    HIREDATE DATE, SALARY INTEGER);

Suppose you want to populate this table with employee numbers from 1 to a number, say 100, with random data for the rest of the columns. You can do this using the following SQL statement:

Figure 5. Generating Test Data

     INSERT INTO EMP 
     -- generate 100 records
     WITH DT(ENO) AS (VALUES(1) UNION ALL SELECT ENO+1 FROM DT WHERE ENO < 100 )
 
     -- Now, use the generated records in DT to create other columns
     -- of the employee record.
       SELECT ENO,
         TRANSLATE(CHAR(INTEGER(RAND()*1000000)),
                   CASE MOD(ENO,4) WHEN 0 THEN 'aeiou' || 'bcdfg'
                                   WHEN 1 THEN 'aeiou' || 'hjklm'
                                   WHEN 2 THEN 'aeiou' || 'npqrs'
                                          ELSE 'aeiou' || 'twxyz' END,
                                               '1234567890') AS LASTNAME,
         CURRENT DATE - (RAND()*10957) DAYS AS HIREDATE,
         INTEGER(10000+RAND()*200000) AS SALARY
       FROM DT;
                                                          
     SELECT * FROM EMP;

The following is an explanation of the above statement:

  1. The first part of the above INSERT statement generates 100 records for the first 100 employees using a recursive subquery to generate the employee numbers. Each record contains the employee number. To change the number of employees, use a number other than 100.

  2. The SELECT statement generates the LASTNAME column. It begins by generating a random integer up to 6 digits long using the RAND function. It then converts the integer to its numeric character format using the CHAR function.

  3. To convert the numeric characters to alphabet characters, the statement uses the TRANSLATE function to convert the ten numeric characters (0 through 9) to alphabet characters. Since there are more than 10 alphabet characters, the statement selects from five different translations. This results in names having enough random vowels to be pronounceable and so the vowels are included in each translation.

  4. The statement generates a random HIREDATE value. The value of HIREDATE ranges back from the current date to 30 years ago. HIREDATE is calculated by subtracting a random number of days between 0 and 10 957 from the current date. (10 957 is the number of days in 30 years.)

  5. Finally, the statement randomly generates the SALARY. The minimum salary is 10 000, to which a random number from 0 to 200 000 is added.

For example programs that are helpful in generating random test date, please see the fillcli.sqc and fillsrv.sqcsample programs in the sqllib/samples/c subdirectory.

You may also want to consider prototyping any user-defined functions (UDF) you are developing against the test data. For more information on why and how you write UDFs, see Chapter 7. "Writing User-Defined Functions (UDFs)" and "User-Defined Functions (UDF)".

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]