db2unit provides a set of assert methods that allow to compare a value against another, and to perform other tests to validate your code. However, the provided assert methods could not be enough for all cases, and you could require other ways to perform tests.

Thus, you could need to create your own assertions for your own purposes or when using elements like:

  • Arrays.
  • Cursors.
  • Distinct types.
  • Row types.
  • Data in the database.

Let's suppose you need to check if a row exists in a table, and it is associated with the given values.

CREATE TABLE TOOLS (
  ID INT,
  NAME VARCHAR(32),
  BRAND VARCHAR(32)
  );

INSERT INTO TOOLS VALUES
  (1, 'Hammer', 'DeWalt'),
  (2, 'Screwdriver', 'Stanley'),
  (3, 'Driller', 'Black&Decker');

With the given table and values, let's suppose you need to create an assert that verifies the existence of an ID, and check that it corresponds to the provided tool brand.

ALTER MODULE DB2UNIT ADD
  PROCEDURE ASSERT_EXIST_TOOL (
  IN GIVEN_ID INT,
  IN BRAND VARCHAR(32)
  )
 BEGIN
  DECLARE CUR_BRAND VARCHAR(32);
  DECLARE RET INT;

  CALL BEGIN_ASSERTION();

  SET RET = RET_OK;

  SET CUR_BRAND = (SELECT BRAND FROM TOOLS WHERE ID = GIVEN_ID);

  IF (CUR_BRAND IS NULL) THEN
   CALL WRITE_IN_REPORT ('The given ID does not correspond to an existent tool: '
     || GIVEN_ID);
   SET TEST_RESULT = RESULT_FAILED;
   SET RET = RET_OPPOSITE_NULL;
  ELIF (CUR_BRAND <> BRAND) THEN
   CALL WRITE_IN_REPORT ('The brand of the given ID does not match the given brand: '
     || CUR_BRAND || '<>' || BRAND);
   SET TEST_RESULT = RESULT_FAILED;
   SET RET = RET_OPPOSITE_NULL;
  END IF;

  CALL END_ASSERTION();
  RETURN RET;
 END @

There are some important elements to take into account when developing your own assert methods:

  • The TEST_RESULT variable should be set to RESULT_FAILED when the assertion is not satisfied. This global variable tells the framework when consider a test as failed, hit errors or passed. This variable is set to null when executing the test. If the execution of the test is finished, and this variable is still null, it is changed to PASSED. If a condition was caught by the framework during the test execution, the variable is changed to ERROR. And if the test does not satisfy an assertion, it should be changed to FAILED by the assert method.
  • The WRITE_IN_REPORT procedure allows you to write in the tests report. This report provides explicit message about why the assertion was not satisfied.
  • (v2) The assertion should start with a call to the BEGIN_ASSERTION procedure to set the environment (for calls outside a test suite), and finish with a call to the END_ASSERTION procedure to return a cursor with the output. These two calls allow to test the assertion independently (not as part of a Tests Suite execution).
  • Return a Return Status after the execution. This is useful to provide a mechanism external to Db2 that check that all assertion were successful or not. 0 indicates that everything is OK, otherwise is an error. There is a set of predefined numbers in the 05-asserts.sql file. You can define any number greater to 12 in order to not have collisions with the existing numbers; however, it does not have an impact as long as you make the difference between 0 as OK, and any other number as error.

The previous assert method is a basic one, with the minimal functionality. It can be improved in different ways:

  • Receive a message to indicate something about the environment, when executing the assert.
  • Use an assert type to identify the kind of assertion.
  • Use log4db2 to log messages about the execution.

Here, a more sophisticated version of the previous assert method:

ALTER MODULE DB2UNIT ADD
  PROCEDURE ASSERT_EXIST_TOOL (
  IN MESSAGE ANCHOR MAX_VALUES.MESSAGE_ASSERT,
  IN GIVEN_ID INT,
  IN BRAND VARCHAR(32)
  )
  LANGUAGE SQL
  SPECIFIC P_ASSERT_EXIST_TOOL
  DYNAMIC RESULT SETS 0
  MODIFIES SQL DATA
  NOT DETERMINISTIC
  NO EXTERNAL ACTION
 P_ASSERT_EXIST_TOOL: BEGIN
  DECLARE LOGGER_ID SMALLINT;
  DECLARE ASSERT_TYPE VARCHAR(16) CONSTANT 'EXISTING_TOOL';
  DECLARE CUR_BRAND VARCHAR(32);
  DECLARE RET INT;

  CALL LOGGER.GET_LOGGER('DB2UNIT_1.DB2UNIT.ASSERT_EXIST_TOOL',
    LOGGER_ID);
  CALL BEGIN_ASSERTION();

  -- Pre process
  SET MESSAGE = PROC_MESSAGE(MESSAGE);
  SET RET = RET_OK;
  CALL LOGGER.DEBUG(LOGGER_ID, MESSAGE);

  SET CUR_BRAND = (SELECT BRAND FROM TOOLS WHERE ID = GIVEN_ID);

  IF (CUR_BRAND IS NULL) THEN
   CALL WRITE_IN_REPORT ('The given ID does not correspond to an existent tool: '
     || COALESCE(GIVEN_ID));
   SET TEST_RESULT = RESULT_FAILED;
   SET RET = RET_OPPOSITE_NULL;
  ELIF (CUR_BRAND <> BRAND) THEN
   CALL WRITE_IN_REPORT ('The brand of the given ID does not match the given brand: '
     || COALESCE(CUR_BRAND) || '<>' || COALESCE(BRAND));
   SET TEST_RESULT = RESULT_FAILED;
   SET RET = RET_OPPOSITE_NULL;
  END IF;

  CALL END_ASSERTION();
  RETURN RET;
 END P_ASSERT_EXIST_TOOL @