Prepare to put your DB2 SQL code "through the ringer" to ensure it's ready for prime time.

Typically, developers love writing new code. And while an application is new and business rules are fresh in the mind, it's generally pleasurable to enhance code as change requests meander in. But after the years roll on, and business requirements are forgotten, and complex code looks unfamiliar, then maintaining the code can get downright ugly. How does one know an obscure business rule wasn't violated when changing code?   It's usually not until code has been moved into production that the misery starts after a bug rears its ugly head.

Writing unit tests against code is one way to help safeguard from the inherent dangers of changing an application. What is unit testing? Wikipedia defines unit testing as follows:

"…unit testing is a method by which individual units of source code, sets of one or more computer program modules together with associated control data, usage procedures, and operating procedures are tested to determine if they are fit for use. Intuitively, one can view a unit as the smallest testable part of an application. In procedural programming, a unit could be an entire module, but it is more commonly an individual function or procedure. In object-oriented programming, a unit is often an entire interface, such as a class, but could be an individual method. Unit tests are short code fragments created by programmers…during the development process."

The goal of unit tests are to make sure that pieces of code perform as expected. In addition to unit tests, another type of database test is what I call a "quality" test. In other words, even if the logic functions correctly, is the code ready for prime time? A quality test ensures that, for example, the DB2 object is defined optimally for performance (for example, a function that should have determinism defined) and that it is properly secured (can user TOM run a procedure even though he shouldn't be allowed?).

Microsoft .NET and Java developers have the benefit of many available tools and frameworks that assist with unit testing. However, I'm not familiar with unit test tools for traditional IBM I, so I'm going to lay out some ideas on how to approach writing unit tests for DB2 for i code. By extension, these concepts can be applied to HLL code such as RPG, COBOL, and C.

If you're already using a Java unit test framework, you can possibly integrate tests against HLL and database code using PCML calls and external Java wrappers for procedures and functions, although it's up to you to determine if the effort is worthwhile.

Unit and Quality Test Examples

It's probably best to start out with how a unit test may be written against DB2 for i code. In these examples, I'll use dynamic compound statements (available in IBM i 7.1 PTF level 26), although you can also create stored procedures or write embedded SQL programs to accomplish the same result.

With these few examples, I'll provide some ideas about how to test DB2 for i user-defined functions, views, triggers, database object attributes, and security. Generally, when a test fails, an error is signaled, and when it succeeds, the code simply ends.

Sample 1: Deterministic User-Defined Function Unit Tests

The easiest code to test is deterministic code that doesn't depend on database data. Consider a DB2 user-defined scalar function called FMTDATE that is supposed to convert a numeric date in CYYMMDD format to a string formatted according to a secondary parameter. The function definition looks like this:

CREATE OR REPLACE FUNCTION QGPL.FMTDATE(

CYMD_DATE INT,

DATE_FMT VARCHAR(5))

RETURNS VARCHAR(10)

A dynamic compound statement to test this script may look something like this (with possibly more test conditions):

-- Sample 1 - test scalar UDF FMTDATE

BEGIN

   IF (VALUES (QGPL.FMTDATE(1100101,'*ISO')))<>'2010-01-01' THEN

       SIGNAL SQLSTATE '38005'

           SET MESSAGE_TEXT='Function FMTDATE failed the *ISO format test';

   END IF;

   IF (VALUES (QGPL.FMTDATE(1100101,'*MDY')))<>'01/01/10' THEN

       SIGNAL SQLSTATE '38005'

           SET MESSAGE_TEXT='Function FMTDATE failed the *MDY format test';

   END IF;

   IF (VALUES (QGPL.FMTDATE(1100101,'*YMD')))<>'10/01/01' THEN

       SIGNAL SQLSTATE '38005'

           SET MESSAGE_TEXT='Function FMTDATE failed the *YMD format test';

   END IF;

   IF (VALUES (QGPL.FMTDATE(CAST(NULL AS INT),'*YMD'))) IS NOT NULL THEN

       SIGNAL SQLSTATE '38005'

           SET MESSAGE_TEXT='Function FMTDATE is expected to return a NULL when Parameter 1 is NULL';

   END IF;

   IF (VALUES (QGPL.FMTDATE(1100101,CAST(NULL AS CHAR(1))))) IS NOT NULL THEN

       SIGNAL SQLSTATE '38005'

           SET MESSAGE_TEXT='Function FMTDATE is expected to return a NULL when Parameter 2 is NULL';

   END IF;

END;

So the idea of a unit test is to make sure that the code logic behaves as expected under many conditions. With the above date function, a developer would ideally write statements to test…

• various possibilities for the acceptable format values
• unexpected values such as a NULL or a negative numeric date

Since the number of possibilities can grow quite large, the goal is just to test within reason and include any special values. If a developer changes the function in such a way that causes the test script to fail, the failure will indicate the code change should be reviewed.

Sample 2: Verifying a Stored Procedure Result Set Unit Test

Many applications return result sets from queries or stored procedures. When a procedure is run against a known set of data, the result set can be interrogated and verified to be accurate. Consider the following simple procedure that returns one result set:

CREATE OR REPLACE PROCEDURE ADVWORKS.GET_CURRENT_SHIPMENTS

(IN @SHIPDATE DATE)

LANGUAGE SQL

RESULT SETS 1

SET OPTION COMMIT=*NONE,USRPRF=*OWNER,DATFMT=*ISO

BEGIN

   DECLARE SHIPMENTS CURSOR FOR

   SELECT SalesOrderId,SubTotal

     FROM AdvWorks.SalesOrderHeader

     WHERE CAST(SHIPDATE AS DATE)=@SHIPDATE>

   FOR READ ONLY;

   OPEN SHIPMENTS;

END;

The following dynamic compound statement illustrates how the result set can be interrogated for expected results:

BEGIN

   DECLARE @SHIPMENT_COUNT INT DEFAULT 0;

   DECLARE @SALES_ORDER_ID INT;

   DECLARE @SUBTOTAL DEC(17,4);

   DECLARE @SHIPMENT_VALUE DEC(17,4) DEFAULT 0;

   DECLARE @MESSAGE VARCHAR(70);

   DECLARE @SHIPMENT_DATA RESULT_SET_LOCATOR VARYING;

   DECLARE @END_OF_DATA   SMALLINT NOT NULL DEFAULT 0;

   DECLARE CONTINUE HANDLER FOR NOT FOUND

       SET @END_OF_DATA=1;

   CALL ADVWORKS.GET_CURRENT_SHIPMENTS ('2002-05-01');

   ASSOCIATE RESULT SET LOCATOR(@SHIPMENT_DATA)

   WITH PROCEDURE ADVWORKS.GET_CURRENT_SHIPMENTS;

   ALLOCATE SHIPMENT_TEST CURSOR FOR RESULT SET @SHIPMENT_DATA;

   FETCH SHIPMENT_TEST INTO @SALES_ORDER_ID,@SUBTOTAL;

   Read_Data:

   LOOP

       IF @END_OF_DATA=1 THEN

           LEAVE Read_Data;

       END IF;

       SET @SHIPMENT_COUNT=@SHIPMENT_COUNT+1;

       SET @SHIPMENT_VALUE=@SHIPMENT_VALUE+COALESCE(@SUBTOTAL,0);

       FETCH SHIPMENT_TEST INTO @SALES_ORDER_ID,@SUBTOTAL;

   END LOOP Read_Data;

   CLOSE SHIPMENT_TEST;

   -- Check Results

   IF @SUBTOTAL<>3374.9900 THEN

       SET @MESSAGE='Shipment subtotal '

           || CAST(@SHIPMENT_VALUE AS VARCHAR(15))

           || ' does not match expected value of $3374.9900';

       SIGNAL SQLSTATE '38002'

           SET MESSAGE_TEXT=@MESSAGE;

   END IF;

   IF @SHIPMENT_COUNT<>4 THEN

       SET @MESSAGE='Shipment count '

          || CAST(@SHIPMENT_COUNT AS VARCHAR(10))

           || ' does not match expected value of 4';

       SIGNAL SQLSTATE '38003'

           SET MESSAGE_TEXT=@MESSAGE;

   END IF;

END;

Two tests are done here: verifying that only four rows are returned and verifying that the subtotal of the shipments equals a specific amount.

In case you're unfamiliar with reading a result set as a cursor, starting in IBM i 7.1 the ASSOCIATE RESULT SET LOCATOR and ALLOCATE CURSOR statements are available to crack open the result sets of another procedure. For more info on processing result sets, see this article. If you're not on IBM i 7.1 yet, result set based tests can be written in other languages, such as VBScript, JavaScript, Java, or C#.

A more thorough test would be to validate every row and column in the result set. To do this, an expected result set would be persisted as XML (or some other format) and then a more intricate test harness would actually compare each value in every row and column.

For this type of row/column test to work, the result sets have to be ordered and columns containing special registers such as CURRENT_TIMESTAMP, CURRENT_SERVER, and USER should be ignored (because they can vary among runs, IBM i partitions, etc.). The new XMLTABLE table function (requires IBM i 7.1 TR4) can assist with the chore of incorporating XML into a database query. For more details, see the developerWorks XMLTABLE document and this Technology Refresh 4 article.

In this example, the test was more complicated than the procedure itself! Exactly what kind of testing is implemented will depend on perceived code vulnerability, time to write and review the test(s), etc.

Food for Thought: Applying the Above Technique to User-Defined Table Functions, Views

Because table functions return a result set, both table functions and views can be tested in similar fashion to the above stored procedure demonstration. The generic test steps are 1) write a cursor for a test query (based on the view or table function) and 2) read the results. Various kinds of tests can be done, such as comparing aggregates (as shown above) or comparing against a persisted XML or other type of result set. Varying the query filters and focusing on specific complex column expressions that may be prone to error are additional items that can be tested.

Sample 3: Evaluating the Work of a Trigger

Triggers can be tested by updating the base table or view and then checking to make sure the trigger did its work. In this simple example, a row is inserted into the ADVWORKS.STORE table, causing an INSERT trigger to fire. The insert trigger is expected to insert a new row into audit table STORE_MAINT_LOG. The test's logic simply compares the row count of the log table before and after the INSERT is done against table ADVWORKS.STORE. Of course, the trigger test may often need to be much more complicated than a simple row count check.

BEGIN

   DECLARE @BEFORE_COUNT INT NOT NULL DEFAULT 0;

   DECLARE @AFTER_COUNT INT NOT NULL DEFAULT 0;

   -- Find out how many rows are in the log table

   SET @BEFORE_COUNT=

   (SELECT COUNT(*) FROM ADVWORKS.STORE_MAINT_LOG);

   -- Cause the trigger to fire by simulating a new store creation

   INSERT INTO ADVWORKS.STORE (CUSTOMERID, NAME,

   SALESPERSONID,DEMOGRAPHICS, MODIFIEDDATE,ROWGUID)

   VALUES(1002, 'Bed, Bath, Bicycles & Beyond',

         2,4,CURRENT_DATE,GENERATE_UNIQUE());

   -- Find out how many rows are in the log table after the trigger

   SET @AFTER_COUNT=(SELECT COUNT(*) FROM ADVWORKS.STORE_MAINT_LOG);

   IF @BEFORE_COUNT <> @AFTER_COUNT - 1 THEN

       SIGNAL SQLSTATE '38006'

           SET MESSAGE_TEXT='Trigger TRG_STORE_INSERT did not process correctly';

   END IF;

END

Sample 4: Testing Database Object Attributes

Besides unit testing, other quality tests can be done to make sure the code will behave as expected. Say, for instance, there's a C library of advanced math functions you want to make available for DB2 to use as external functions. You know these external functions will perform best when they're defined with DETERMINISTIC and NO EXTERNAL ACTION.

Assuming the following test is to be run after a developer creates or changes a C external function, it's job is to verify that all of the external C functions in a particular library have been defined with DETERMINISTIC and NO EXTERNAL ACTION:

-- Verify that external C functions in test lib

-- are all DETERMINISTIC with NO EXTERNAL ACTION.

BEGIN

   IF EXISTS (

   SELECT *

     FROM QSYS2.SYSFUNCS

     WHERE EXTERNAL_LANGUAGE='C'

       AND SPECIFIC_SCHEMA='MY_TEST_LIB' -- should be a variable!

       AND (IS_DETERMINISTIC='NO'

       OR EXTERNAL_ACTION='E')) THEN

       SIGNAL SQLSTATE '38001'

           SET MESSAGE_TEXT='Inspect all C functions to make sure DETERMINISTIC and NO EXTERNAL ACTION are defined.';

   END IF;

END

Similar tests can be written against the many DB2 catalog views to ensure that other DB2 objects are defined optimally. For example, procedures can also be checked for specific attributes, views can be verified to depend only on objects in the same schema, etc.

Sample 5: Security Test

Another aspect of assuring quality database code is to make sure security works correctly. There's nothing worse than finding out an unauthorized user was able to access sensitive data. In this sample, the test checks whether stored procedure PAYROLL_INFO can be run by user PEASANT. If the execution of the procedure fails with a "not authorized" error, the test will pass. However, if the execution succeeds, then the test fails:

-- This test will use profile PEASANT

CALL dev.ChangeCurrentUser('PEASANT','serf');

BEGIN

   DECLARE @AUTH_FAILED SMALLINT NOT NULL DEFAULT 0;

   DECLARE CONTINUE HANDLER FOR SQLSTATE '42501'

       SET @AUTH_FAILED=1;

   CALL ADVWORKS.PAYROLL_INFO (1,'2010-01-01','2010-01-07');

   IF @AUTH_FAILED = 0 THEN

       SIGNAL SQLSTATE '38004'

           SET MESSAGE_TEXT='User PEASANT was allowed to run the payroll report';

   END IF;

END;

-- Change the profile back to NOBLE

CALL dev.ChangeCurrentUser('NOBLE','password');

Security is one of those areas that's often overlooked when deploying code, especially during a hot bug fix. Having security-related test scripts can help catch whether a GRANT or REVOKE statement that should've been executed was forgotten.

Also, the above example uses the custom stored procedure ChangeCurrentUser to impersonate another user profile. This procedure is an alternative to SET SESSION AUTHORIZATION. For more info on impersonating another user in DB2, see these articles:

TechTip: Impersonate Your Neighbor Using DB2 for i

TechTip: DB2 for i Impersonation Take 2: External Routines

I Know What You're Thinking

If you're like me, because of the amount of work involved, the concept of testing in this manner garners an immediate "NO WAY!" response. However, I've since changed my tune, and I think that testing with these methods is very important. In a future tip, I'll share ideas on how testing can be accomplished on a regular basis, how to setup test data, and why it's worth the effort.