24
Mon, Jun
3 New Articles

TechTip: Suggestions for DB2 for i Code Quality Testing

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.

 

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: