Establishing a Test Environment

Team-Fly    

 
DB2 Universal Database for OS/390 v7.1 Application Certification Guide
By Susan Lawson
Table of Contents
Chapter 9.  Testing and Debugging Applications

Establishing a Test Environment

This section describes how to design a test data structure and how to fill tables with test data.

Designing a Test Data Structure

When you test an application that accesses DB2 data, you should have DB2 data available for testing. To do this, you can create test tables and views. For information on the DDL to create tables and views, refer to Chapter 2.

Test Views of Existing Tables

If your application does not change a set of DB2 data and the data exists in one or more production-level tables, you might consider using a view of existing tables. To create a test table, you need a database and tablespace. Normally, a database administrator (DBA) can make sure that a database and tablespaces are available for your use.

If the data that you want to change already exists in a table, consider using the LIKE clause of CREATE TABLE. If you want others besides yourself to have ownership of a table for test purposes, you can specify a secondary ID as the owner of the table. For information about ownership and privileges, refer to Chapter 2.

If your location has a separate DB2 system for testing, you can create the test tables and views on the test system, then test your program thoroughly on that system. This chapter assumes that you do all testing on a separate system and that the person who created the test tables and views has an authorization ID of TEST. The table names are TEST.EMP, TEST.PROJ, and TEST.DEPT.

Analyzing Application Data Needs

To design test tables and views, first analyze your application's data needs.

  1. List the data your application accesses and describe how it accesses each data item. For example, suppose you are testing an application that accesses the DSN8710.EMP, DSN8710.DEPT, and DSN8710.PROJ tables. You might record the information about the data as shown below.

    Table or View Name

    Insert Rows?

    Delete Rows?

    Column

    Name Data Type

    Update Access?

    DSN8710.EMP

    No

    No

    EMPNO

    CHAR(6)

     
         

    LASTNAME

    VARCHAR(15)

     
         

    WORKDEPT

    CHAR(3)

    Yes

         

    PHONENO

    CHAR(4)

    Yes

         

    JOB

    DECIMAL(3)

    Yes

    DSN8710.DEPT

    No

    No

    DEPTNO

    CHAR(3)

     
         

    MGRNO

    CHAR (6)

     

    DSN8710.PROJ

    Yes

    Yes

    PROJNO

    CHAR(6)

     
         

    DEPTNO

    CHAR(3)

    Yes

         

    RESPEMP

    CHAR(6)

    Yes

         

    PRSTAFF

    DECIMAL(5,2)

    Yes

         

    PRSTDATE

    DECIMAL(6)

    Yes

         

    PRENDATE

    DECIMAL(6)

    Yes

  2. Determine the test tables and views you need to test your application.

Create a test table on your list when either:

  • The application modifies data in the table.

  • You need to create a view based on a test table because your application modifies the view's data.

To continue the example, create these test tables:

  • TEST.EMP, with the following format:

    EMPNO

    LASTNAME

    WORKDEPT

    PHONENO

    JOB

  • TEST.PROJ, with the same columns and format as DSN8710.PROJ, because the application inserts rows into the DSN8710.PROJ table.

To support the example, create a test view of the DSN8710.DEPT table. Because the application does not change any data in the DSN8710.DEPT table, you can base the view on the table itself (rather than on a test table). However, it is safer to have a complete set of test tables and to test the program thoroughly using only test data. The TEST.DEPT view has the following format:

DEPTNO

MGRNO

Obtaining Authorization

Before you can create a table, you need to be authorized to create tables and to use the tablespace in which the table is to reside. You must also have authority to bind and run programs you want to test. Your DBA can grant you the authorization needed to create and access tables and to bind and run programs.

If you intend to use existing tables and views (either directly or as the basis for a view), you need privileges to access those tables and views. Your DBA can grant those privileges. To create a view, you must have authorization for each table and view on which you base the view. You then have the same privileges over the view that you have over the tables and views on which you based the view. Before trying the examples, have your DBA grant you the privileges to create new tables and views and to access existing tables. Obtain the names of tables and views you are authorized to access (as well as the privileges you have for each table) from your DBA.

Creating a Comprehensive Test Structure

The following SQL statements shows how to create a complete test structure to contain a small table named SPUFINUM. The test structure consists of

  • A storage group named SPUFISG

  • A database named SPUFIDB

  • A tablespace named SPUFITS in SPUFIDB and using SPUFISG

  • A table named SPUFINUM within the tablespace SPUFITS

     CREATE STOGROUP SPUFISG  VOLUMES (user-volume-number) VCAT DSNCAT ; CREATE DATABASE SPUFIDB ; CREATE TABLESPACE SPUFITS IN SPUFIDB USING STOGROUP SPUFISG ; CREATE TABLE SPUFINUM    (XVAL CHAR(12)NOT NULL,    ISFLOAT FLOAT,    DEC30 DECIMAL(3,0),    DEC31 DECIMAL(3,1),    DEC32 DECIMAL(3,2),    DEC33 DECIMAL(3,3),    DEC10 DECIMAL(1,0),    DEC11 DECIMAL(1,1),    DEC150 DECIMAL(15,0),    DEC151 DECIMAL(15,1),    DEC1515 DECIMAL(15,15)) IN SPUFIDB.SPUFITS ; 

For details about each CREATE statement, refer to Chapter 2.

Filling the Tables with Test Data

There are several ways in which you can put test data into a table:

  • INSERT...VALUES (an SQL statement) puts one row into a table each time the statement executes.

  • INSERT...SELECT (an SQL statement) obtains data from an existing table (based on a SELECT clause) and puts it into the table identified with the INSERT statement.

  • The LOAD utility obtains data from a sequential file (a non-DB2 file), formats it for a table, and puts it into a table.

  • The DB2 sample UNLOAD program (DSNTIAUL) can unload data from a table or view and build control statements for the LOAD utility.

  • The UNLOAD utility can unload data from a table and build control statements for the LOAD utility.

Testing SQL Statements Using SPUFI

You can use SPUFI (an interface between ISPF and DB2) to test SQL statements in a TSO/ISPF environment. With SPUFI panels, you can put SQL statements into a dataset that DB2 subsequently executes. The SPUFI main panel has several functions that permit you to

  • Name an input dataset to hold the SQL statements passed to DB2 for execution.

  • Name an output dataset to contain the results of executing the SQL statements.

  • Specify SPUFI processing options.

SQL statements executed under SPUFI operate on actual tables (in this case, the tables you have created for testing). Consequently, before you access DB2 data,

  • Make sure that all tables and views your SQL statements refer to exist.

  • If the tables or views do not exist, create them (or have your DBA create them). You can use SPUFI to issue the CREATE statements used to create the tables and views you need for testing.


Team-Fly    
Top


DB2 Universal Database for OS. 390 v7. 1 Application Certification Guide
DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)
ISBN: 0131007718
EAN: 2147483647
Year: 2002
Pages: 163
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net