This set of stored procedures and functions is the db2unit API, and it is divided in two groups:

  • Assertions for development.
  • Administrations and usage.

Assertions

They allow to run assertions by checking values or tables. All assertions provide two type of calls: With and without message. When there is a message and the assertion fails, db2unit will include the given message in the report. This could help to easily identify the reason of the fail.

Note: Try to not use any kind of operation in the values passed to the assertions. Operations like concatenations, substrings, etc., could generate an exception before calling the assertion, and this could lead to an invalid error. Please check the Usage section to see an example of a bad/wrong call.

General

Signature Description
REGISTER_MESSAGE (
MESSAGE VARCHAR(256))
Registers a message for the test that will be used if an exception is raised.
FAIL () Fails the test.
FAIL (
MESSAGE VARCHAR(256))
Fails the test and provides a message.

Booleans

These tests are for boolean data type.

Signature Description
ASSERT_BOOLEAN_EQUALS (
EXPECTED VARCHAR(32672),
ACTUAL VARCHAR(32672))
Compares two booleans values.
ASSERT_BOOLEAN_EQUALS (
MESSAGE VARCHAR(256),
EXPECTED VARCHAR(32672),
ACTUAL VARCHAR(32672))
Compares two booleans values, providing a message for the test.
ASSERT_BOOLEAN_TRUE (
CONDITION BOOLEAN)
Checks if the given boolean has a value of TRUE.
ASSERT_BOOLEAN_TRUE (
MESSAGE VARCHAR(256),
CONDITION BOOLEAN)
Checks if the given boolean has a value of TRUE, providing a message for the test.
ASSERT_BOOLEAN_FALSE (
CONDITION BOOLEAN)
Checks if the given boolean has a value of FALSE.
ASSERT_BOOLEAN_FALSE (
MESSAGE VARCHAR(256),
CONDITION BOOLEAN)
Checks if the given boolean has a value of FALSE, providing a message for the test.
ASSERT_BOOLEAN_NULL (
CONDITION BOOLEAN)
Checks if the given boolean is null.
ASSERT_BOOLEAN_NULL (
MESSAGE VARCHAR(256),
CONDITION BOOLEAN)
Checks if the given boolean is null, providing a message for the test.
ASSERT_BOOLEAN_NOT_NULL (
CONDITION BOOLEAN)
Checks if the given boolean is not null.
ASSERT_BOOLEAN_NOT_NULL (
MESSAGE VARCHAR(256),
CONDITION BOOLEAN)
Checks if the given boolean is not null, providing a message for the test.

Datetime

These tests are for timestamps, date and time data types.

Signature Description
ASSERT_TIMESTAMP_EQUALS (
EXPECTED TIMESTAMP,
ACTUAL TIMESTAMP)
Compares two timestamps.
ASSERT_TIMESTAMP_EQUALS (
MESSAGE VARCHAR(256),
EXPECTED TIMESTAMP,
ACTUAL TIMESTAMP)
Compares two timestamps, providing a message for the test.
ASSERT_TIMESTAMP_NULL (
VALUE TIMESTAMP)
Checks if the given timestamp is null.
ASSERT_TIMESTAMP_NULL (
MESSAGE VARCHAR(256),
VALUE TIMESTAMP)
Checks if the given timestamp is null, providing a message for the test.
ASSERT_TIMESTAMP_NOT_NULL (
VALUE TIMESTAMP)
Checks if the given timestamp is not null.
ASSERT_TIMESTAMP_NOT_NULL (
MESSAGE VARCHAR(256),
VALUE TIMESTAMP)
Checks if the given timestamp is not null, providing a message for the test.
ASSERT_DATE_EQUALS (
EXPECTED DATE,
ACTUAL DATE)
Compares two dates.
ASSERT_DATE_EQUALS (
MESSAGE VARCHAR(256),
EXPECTED DATE,
ACTUAL DATE)
Compares two dates, providing a message for the test.
ASSERT_DATE_NULL (
VALUE DATE)
Checks if the given date is null.
ASSERT_DATE_NULL (
MESSAGE VARCHAR(256),
VALUE DATE)
Checks if the given date is null, providing a message for the test.
ASSERT_DATE_NOT_NULL (
VALUE DATE)
Checks if the given date is not null.
ASSERT_DATE_NOT_NULL (
MESSAGE VARCHAR(256),
VALUE DATE)
Checks if the given date is not null, providing a message for the test.
ASSERT_TIME_EQUALS (
EXPECTED TIME,
ACTUAL TIME)
Compares two times.
ASSERT_TIME_EQUALS (
MESSAGE VARCHAR(256),
EXPECTED TIME,
ACTUAL TIME)
Compares two times, providing a message for the test.
ASSERT_TIME_NULL (
VALUE TIME)
Checks if the given time is null.
ASSERT_TIME_NULL (
MESSAGE VARCHAR(256),
VALUE TIME)
Checks if the given time is null, providing a message for the test.
ASSERT_TIME_NOT_NULL (
VALUE TIME)
Checks if the given time is not null.
ASSERT_TIME_NOT_NULL (
MESSAGE VARCHAR(256),
VALUE TIME)
Checks if the given time is not null, providing a message for the test.

Decimals

These tests are for real, double, float and decfloat data types.

Signature Description
ASSERT_DEC_EQUALS (
EXPECTED DOUBLE,
ACTUAL DOUBLE)
Compares two decimals.
ASSERT_DEC_EQUALS (
MESSAGE VARCHAR(256),
EXPECTED DOUBLE,
ACTUAL DOUBLE)
Compares two decimals, providing a message for the test.
ASSERT_DEC_NULL (
VALUE DOUBLE)
Checks if the given decimal is null.
ASSERT_DEC_NULL (
MESSAGE VARCHAR(256),
VALUE DOUBLE)
Checks if the given decimal is null, providing a message for the test.
ASSERT_DEC_NOT_NULL (
VALUE DOUBLE)
Checks if the given decimal is not null.
ASSERT_DEC_NOT_NULL (
MESSAGE VARCHAR(256),
OBJECT DOUBLE)
Checks if the given decimal is not null, providing a message for the test.

Integers

These tests are for smallint, int, and bigint data types.

Name Description
ASSERT_INT_EQUALS (
EXPECTED BIGINT,
ACTUAL BIGINT)
Compares two integers.
ASSERT_INT_EQUALS (
MESSAGE VARCHAR(256),
EXPECTED BIGINT,
ACTUAL BIGINT)
Compares two integers, providing a message for the test.
ASSERT_INT_NULL (
VALUE BIGINT)
Checks if the given integer is null.
ASSERT_INT_NULL (
MESSAGE VARCHAR(256),
VALUE BIGINT)
Checks if the given integer is null, providing a message for the test.
ASSERT_INT_NOT_NULL (
VALUE BIGINT)
Checks if the given integer is not null.
ASSERT_INT_NOT_NULL (
MESSAGE VARCHAR(256),
VALUE BIGINT)
Checks if the given integer is not null, providing a message for the test.

Strings

These assertions are for char and varchar data type.

Signature Description
ASSERT_STRING_EQUALS (
EXPECTED VARCHAR(32672),
ACTUAL VARCHAR(32672))
Compares two strings.
ASSERT_STRING_EQUALS (
MESSAGE VARCHAR(256),
EXPECTED VARCHAR(32672),
ACTUAL VARCHAR(32672))
Compares two strings, providing a message for the test.
ASSERT_STRING_NULL (
STRING VARCHAR(32672))
Checks if the given string is null.
ASSERT_STRING_NULL (
MESSAGE VARCHAR(256),
STRING VARCHAR(32672))
Checks if the given string is null, providing a message for the test.
ASSERT_STRING_NOT_NULL (
STRING VARCHAR(32672))
Checks if the given string is not null.
ASSERT_STRING_NOT_NULL (
MESSAGE VARCHAR(256),
STRING VARCHAR(32672))
Checks if the given string is not null, providing a message for the test.

Tables

Signature Description
ASSERT_TABLE_EQUALS (
EXPECTED_SCHEMA VARCHAR(128),
EXPECTED_TABLE_NAME VARCHAR(128),
ACTUAL_SCHEMA VARCHAR(128),
ACTUAL_TABLE_NAME VARCHAR(128))
Compares two tables in structure and content.
ASSERT_TABLE_EQUALS (
MESSAGE VARCHAR(256),
EXPECTED_SCHEMA VARCHAR(128),
EXPECTED_TABLE_NAME VARCHAR(128), ACTUAL_SCHEMA VARCHAR(128),
ACTUAL_TABLE_NAME VARCHAR(128))
Compares two tables in structure and content, providing a message for the test.
ASSERT_TABLE_EMPTY (
SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128))
Checks if the given table is empty.
ASSERT_TABLE_EMPTY (
MESSAGE VARCHAR(256),
SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128))
Checks if the given table is empty, providing a message to the test.
ASSERT_TABLE_NON_EMPTY (
SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128))
Checks if the given table is not empty.
ASSERT_TABLE_NON_EMPTY (
MESSAGE VARCHAR(256),
SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128))
Checks if the given table is not empty, providing a message to the test.

(v2) XML

These assertions were introduced since version 2.

Depending on the Db2 version, the XML documents could be referenced as CLOB (v10 and previous version) or like XML directly (most recent versions).

Signature Description
ASSERT_XML_EQUALS (
EXPECTED CLOB|XML,
ACTUAL CLOB|XML)
Compares two XML documents.
ASSERT_XML_EQUALS (
MESSAGE VARCHAR(256),
EXPECTED CLOB|XML,
ACTUAL CLOB|XML)
Compares two XML documents, providing a descriptive message.
ASSERT_XML_NULL (
VALUE CLOB|XML)
Check if the XML document is null.
ASSERT_XML_NULL (
MESSAGE VARCHAR(256),
VALUE CLOB|XML)
Check if the XML document is null, providing a descriptive message.
ASSERT_XML_NOT_NULL (
VALUE CLOB|XML)
Check if the XML document is not null.
ASSERT_XML_NOT_NULL (
MESSAGE VARCHAR(256),
VALUE CLOB|XML)
Check if the XML document is not null, providing a descriptive message.

NOTE: If the provided message, of any of the values exceeds the precision defined here, Db2 will throw an exception that is not related to the assertion. Make sure your message is shorter than 256 chars, and the precision of the asserted data type is greater than the provided.

Administration and usage

These are the stored procedures and functions that you call to interact with the framework. T means type of routine. P means stored procedure. F means function. V means db2unit version.

Signature T V Description
CLEAN() P v1 Cleans the environment. This procedure sets to null the sessions variables used by the framework.
CREATE_TAP_REPORT() P v2 Creates a TAP report. The output is generated in TAP v13.
EXPORT_TESTS_LIST() P v1 For UNIX/Linux/Mac OS X: Generates a file that contains the commands to execute all test suites registered in the database. This is useful to integrate the framework with an external tool.
GET_LAST_EXECUTION_ORDER() P v2 Returns a result set with the order of the tests of the most recent execution in the current session.
LICENSE() P v1 Returns a result set with the license of this framework.
RANDOM_SORT(RANDOM BOOLEAN) P v1 Changes the configuration of the current session to execute the tests in random order or not.
REGISTER_SUITE(SCHEMA_NAME VARCHAR(128)) P v1 Registers the name of a tests suite in the table of test suites. This is useful when configuring an environment, and all tests suites will be executed. This procedure does not execute the test suite.
RELEASE_LOCK(SCHEMA_NAME VARCHAR(128)) P v1 Release the lock associated with the test suite name given if exist. When a tests suite is executed, a lock is created during the execution to prevent concurrent executions. If the execution did not finish correctly (connection forced), the lock is kept in the database. This procedure release this lock.
RELEASE_LOCKS() P v1 Release all locks from the database. This is useful when the database was stopped, or several connections were forced and they were running tests (the execution did not finish correctly.)
REPORT_RECENT_EXECUTIONS() P v2 Returns a result set of the most recent execution for all tests suites.
RESET_TABLES() P v1 Delete the values from all tables of the framework. This brings the installation as when it was installed. All executions of all test suites are deleted.
RUN_SUITE(SCHEMA_NAME VARCHAR(128), PREV_EXEC_ID INT, TEST_NAME VARCHAR(128)) P v1 Main procedure of this framework. The schema_name is the name of the tests suite. The prev_exec_id is the ID of a previous execution that execute the suite in the same random order; if this is null, the order will be randomized again. Test_name is the name of the test that is going to be executed; if null, all tests of the given suite are executed. This procedure registers the tests suites in the tests suites table.
RUN_SUITE(SCHEMA_NAME VARCHAR(128), PREV_EXEC_ID INT) P v1 A wrapper of the previous procedure.
SET_AUTONOMOUS(AUTONOMOUS BOOLEAN) P v1 Changes the configuration of the current session to execute the tests suites in autonomous mode or not.

The following stored procedures and functions are for self-testing.

Signature T V Description
CLEAN_LAST_EXEC() P v2 Cleans the values of the last execution. This is mainly used to test the framework itself (self-testing).
CLEAN_TEST_RESULT() P v1 Cleans some environment variables. This procedure is used for self-testing of db2unit.
GET_CURRENT_EXEC_ID() F v2 Retrieves the ID of the current tests suite execution. This is mainly used to test the framework itself (self-testing).
GET_CURRENT_TEST_SUITE_NAME() F v2 Retrieves the tests suite name of the current execution. This is mainly used to test the framework itself (self-testing).
GET_LAST_EXEC_ID() F v1 Retrieves the ID of the last tests suite execution. If there has not been executed any tests suite in the current session, this will return null. This is mainly used to test the framework itself (self-testing).
GET_LAST_TEST_SUITE_NAME() F v2 Retrieves the test suite name of the most recent execution. This is mainly used to test the framework itself (self-testing).

RUN_SUITE description

This is the main procedure of this framework. It can be called in four ways

  1. All tests in a suite
  2. A given test in a tests suite - TEST_NAME should be specified.
  3. Execution of tests with the order of a previous execution. - PREV_EXEC_ID should be specified.
  4. Self-testing (not direct call)

Also, it returns an error code about the execution:

  • 0: No errors in execution, all tests passed.
  • 1: At least one test failed or had an error.
  • 2: Error executing this procedure.