Software unit testing and DB2 sql loop test code

Software unit testing  is normally a formalised approach to test code and system implementations.

DB2 sql loop  test is  sometimes requested by developers to the  DB2 DBA .

This code is a stored procedure the DB2 DBA can set up and test .

The code creates a table : “test”   and a stored procedure  : “sum_mn”

An example execute of  the code as :call sum_mn 1,1000,?

This starts from 1 looops through to 1000 and adding at every loop

#############CODE START###############################

CREATE TABLE test (num INT);

CREATE PROCEDURE sum_mn (IN p_start INT ,IN p_end INT ,OUT p_sum INT) SPECIFIC sum_mn

LANGUAGE SQL

smn: BEGIN

DECLARE v_temp INTEGER DEFAULT 0;

DECLARE v_current INTEGER;

SET v_current = p_start;

WHILE (v_current <= p_end)

DO INSERT INTO test VALUES(v_current);

SET v_temp = v_temp + v_current; SET v_current = v_current + 1; END WHILE;

SET p_sum = v_temp;

END smn;

 ######################CODE FINIS########################

DB2 sql loop test code  is useful in performance testing and general DB2 code development

Author: Jack Vamvas