Application Development 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:

• A test database. If your application updates, inserts, or deletes data from tables and views, use test data to verify its execution. If it only retrieves data from tables and views, consider using production-level data when testing it.
• Test input data. The input data used to test an application should be valid data that represents all possible input conditions. If the application verifies that input data is valid, include both valid and invalid data to verify that the valid data is processed and the invalid data is flagged.

Creating a Test Database

If you must create a test database, write a small server application that calls the CREATE DATABASE API, or use the command line processor. Refer to the Command Reference for information about the command line processor, or the Administrative 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. You could record the type of accesses 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:

• Create a test table when the application modifies data in a table or a view. Create the following test tables using the CREATE TABLE SQL statement:
  • TEMPL
  • TPROJ
• Create a test view when the application does not modify data in the production database.

  In this example, create a test view of the TDEPT table using the CREATE VIEW SQL statement.

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

Use any of the following methods to insert data into a table:

• INSERT...VALUES (an SQL statement) puts one or more rows into a table each time the command is issued.
• INSERT...SELECT obtains data from an existing table (based on a SELECT clause) and puts it into the table identified with the INSERT statement.
• The IMPORT or LOAD utility inserts large amounts of new or existing data from a defined source.
• The RESTORE utility can be used to duplicate the contents of an existing database into an identical test database by using a BACKUP copy of the original database.

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

The following SQL statements demonstrate a technique you can use to populate your tables with randomly generated test data. Suppose the table EMP 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:

INSERT INTO EMP 
-- generate 100 records
WITH DT(ENO) AS (VALUES(1) UNION ALL 
SELECT ENO+1 FROM DT WHERE ENO < 100 ) (1)
 
-- Now, use the generated records in DT to create other columns
-- of the employee record.
SELECT ENO, (2)
    TRANSLATE(CHAR(INTEGER(RAND()*1000000)), (3)
              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, (4)
    INTEGER(10000+RAND()*200000) AS SALARY (5)
  FROM DT;
                                                     
SELECT * FROM EMP;

The following is an explanation of the above statement:

1. The first part of the 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 sample programs that are helpful in generating random test data, please see the fillcli.sqc and fillsrv.sqc sample 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 Writing User-Defined Functions (UDFs) and Methods and User-Defined Functions (UDFs) and Methods.