You need to know the following basic concepts to understand how db2unit works.

• Test
• Tests Suite
• Test fixtures
• Assertions
• Execution of a test suite

Test definition

Each test is defined in a stored procedure without parameters and the name of this stored procedure must start with the prefix TEST_ (The four letters for the word "test" are in capitals, followed by an underscore). For example:

TEST_CHECK_RATE()

And the db2unit will call your test like:

CALL TEST_CHECK_RATE();

Tests execution

All procedures in the same given schema belong to the same test suite. You have two options to run your tests:

• Execute all tests that belong to a suite.
• Execute just one test of a suite.

As said before, a schema defines a test suite, and you can run all tests in the given suite by calling the procedure: RUN_SUITE(SCHEMA). For example, for the test suite DB2INST1, you execute:

CALL DB2UNIT.RUN_SUITE('DB2INST1');

This will run all tests defined in the DB2INST1 schema.

In contrast, if you want to execute just one test of a given suite, you need to specify the name of your test. Let's suppose you have a test called TEST_MY_FIRST_TEST in the same schema of the previous example, DB2INST1:

CALL DB2UNIT.RUN_SUITE('DB2INST1', TEST_NAME => 'TEST_MY_FIRST_TEST');

NOTE: In Db2 the schemas are case sensitive. It means, if your tests suite is called 'MyTests', you should call your suite with the exact case. Instead, if your tests suite is called 'MY_TESTS', you can call it by 'My_Tests', 'MY_TESTS', or any other combination. db2unit will change the case to capitals if it does not find a schema with the given case. The same happens for the name of the tests (procedures).

The results will be generated in a table called REPORT_TESTS in the given schema (For the previous example, it will be generated under DB2INST1.) It means you should have the CREATE_IN privilege on the given schema.

If you execute RUN_SUITE without any parameter or with null, you will execute the tests suite of the current schema. The following calls are equivalent:

CALL DB2UNIT.RUN_SUITE
CALL DB2UNIT.RUN_SUITE();
CALL DB2UNIT.RUN_SUITE(NULL);
CALL DB2UNIT.RUN_SUITE(CURRENT SCHEMA);

Execution ID

Each time a call to RUN_SUITE is executed, a unique ID is associated with that execution. This allows to identify every single execution. Eventually, you will face cases where a particular order of tests generate errors when normally the tests suite is working. For these cases, you can execute the tests in the same order that produce the error by providing the ID of the execution that generated the errors.

CALL DB2UNIT.RUN_SUITE('DB2INST1', 12345);

If the execution ID does not exist, db2unit will execute an empty set of tests.

Test fixtures

These four procedures could be defined in a tests suite, to initialize, establish or clean the execution environment.

Name	Description
ONE_TIME_SETUP()	Executed at the very beginning of the tests suite.
SETUP()	Executed before each test.
TEAR_DOWN()	Executed after each test.
ONE_TIME_TEAR_DOWN()	Executed at the end after all tests.

Tests suite general structure

When you want to create a tests suite defined in a script, your file will have a structure similar to the next one:

-- Test fixtures <<<
CREATE OR REPLACE PROCEDURE ONE_TIME_SETUP()
 P_ONE_TIME_SETUP: BEGIN
  -- Your code
 END P_ONE_TIME_SETUP @

CREATE OR REPLACE PROCEDURE SETUP()
 P_SETUP: BEGIN
  -- Your code
 END P_SETUP @

CREATE OR REPLACE PROCEDURE TEAR_DOWN()
 P_TEAR_DOWN: BEGIN
  -- Your code
 END P_TEAR_DOWN @

CREATE OR REPLACE PROCEDURE ONE_TIME_TEAR_DOWN()
 P_ONE_TIME_TEAR_DOWN: BEGIN
  -- Your code
 END P_ONE_TIME_TEAR_DOWN @

-- Tests <<<
CREATE OR REPLACE PROCEDURE TEST_my_first_test()
 BEGIN
  DECLARE EXPECTED VARCHAR(32);
  DECLARE ACTUAL VARCHAR(32);

  SET EXPECTED = 'MyValue';
  -- Your code that fills the actual value.
  SET ACTUAL = 'My' || 'Value';

  -- Your code using the test functions.
  CALL DB2UNIT.ASSERT_STRING_EQUALS('Message for the assertion', EXPECTED, ACTUAL);
 END @

CREATE OR REPLACE PROCEDURE TEST_my_second_test()
 BEGIN
  CALL DB2UNIT.REGISTER_MESSAGE('Message when exception');
 END @

CALL DB2UNIT.REGISTER_SUITE(CURRENT SCHEMA)

In the tests section, you create a stored procedure for each test. Inside the tests, you call the assertions (for more information, visit the API) in order to verify the expected result with an actual value.

In order to understand how db2unit works, and the interactions with the suite and testedRoutine, please checkout this sequence diagram.

When using an assertion, try to call it by passing the values (actual and expected) and the message without doing any operation on the call. This will reduce the risk of generating an invalid error in the call. To better understand this, please take a look at the next example:

-- Bad call
CALL DB2UNIT.ASSERT_INT_EQUALS(EXPECTED, MOD(5, 2));

-- Good call
SET ACTUAL = MOD(5, 2)
CALL DB2UNIT.ASSERT_INT_EQUALS(EXPECTED, ACTUAL);

In the bad call, an operation is part of the stored procedure call. But if the operation raise an error or invalid value, it will be more difficult to catch the issue. For this reason, it is recommended to pass just the value.

In some cases, tests do not call any assertion, because the test's objective is to check the normal execution. However, in these cases, an uncontrolled exception could raise, and it is helpful to provide a message about the kind of execution (The meaning of a normal execution):

CALL DB2UNIT.REGISTER_MESSAGE('INSERTING A NEW PERSON');

Execution's output

Once the execution of a suite is finished, it shows two result sets. Once is for the specific execution (table mySchema.REPORT_TESTS) and the other is general for all suites (table DB2UNIT_x.EXECUTION_REPORTS). This output is truncated in order to fit in the screen.

Result set 1
--------------

TEST             FINAL_STATE MICROSECONDS MESSAGE                                                         
---------------- ----------- ------------ ------------------------------
Before Suite     -                      - Starting execution            
TEST_MY_SECOND_T Passed             38445 Executing TEST_MY_SECOND_TEST 
TEST_MY_TEST     Passed            123608 Executing TEST_MY_TEST        
TEST_MY_FIRST_TE Passed            150845 Executing TEST_MY_FIRST_TEST  
After Suite      -                      - Finishing execution           
                 -                      - 3 tests were executed         
                 -                      - 3 tests passed                
                 -                      - 0 tests failed                
                 -                      - 0 tests with errors

9 record(s) selected.


Result set 2
--------------

TIME     EXECUTION_ID STATUS                MESSAGE                                               
-------- ------------ --------------------- ------------------------------------------------------
23:45:54        47221 Initialization        db2unit is licensed under the terms of the GPL v3     
23:45:54        47221 Initialization        Execution of DB2INST1 with ID 47221                   
23:45:54        47221 Prepare Report        The reports table already exist: DB2INST1.REPORT_TESTS
23:45:56        47221 Calculating time      Total execution time is: 2 seconds

4 record(s) selected.

Return Status = 0

If you want to see the complete output, you can query these tables directly

• mySchema.REPORT_TESTS.
• DB2UNIT_x.EXECUTION_REPORTS with a public name as DB2UNIT_EXECUTION_REPORTS.

Correcting problems

Clean environment

When a test suite execution is aborted, many global variables should still keep the old values, and that could restrict the normal execution of the following tests suite calls. In order to clean the environment, you can call the CLEAN procedure, and this will reset all global variables.

CALL DB2UNIT.CLEAN();

Reset framework tables

You could eventually need to clean all the content of the framework tables (old tests, invalid schemas, etc.). In order to do that you can call the following procedure that delete all rows from the basis tables and drop all registered REPORT_TABLES at once:

CALL DB2UNIT.RESET_TABLES();

Drop all tests

If you need to drop all tests from a Suite, you can use the ADMIN_DROP_SCHEMA procedure:

CALL SYSPROC.ADMIN_DROP_SCHEMA('MY_SUITE', NULL, 'ERRORSCHEMA', 'ERRORTABLE');

If there is an error while executing, you could probably need to drop the messages table:

DROP TABLE ERRORSCHEMA.ERRORTABLE;

If the procedure cannot drop your schema, you should see the content of the table:

SELECT * FROM ERRORSCHEMA.ERRORTABLE;

Release lock

When a suite is executed, a lock on that suite is activated, and once the executing is finished that lock is released. However, there are cases when the execution is aborted, and the lock is never released. In order to clean the locks for a given suite, just call:

CALL DB2UNIT.RELEASE_LOCK('MY_SUITE');

Eventually, you will need to release all locks. You can do that by executing:

CALL DB2UNIT.RELEASE_LOCKS();

String too long

If you provide messages to the assertions, and you are not sure they are less than 256 characters, you can pass the message by doing a substring before the calling. However, 256 characters for the message is more than enough, because the message should describe in few words the assertion being performed.

SET MSG = SUBSTR(MSG, 1, 256);
CALL DB2UNIT.ASSERT_INT_EQUALS(MSG, EXPECTED, ACTUAL);

The test script

There is a test script as part of the binaries that provides a simple way to install your test suite and execute them. There is a script like that for all platforms:

• test for bash.
• test.ksh for Korn.
• test.bat for CMD.
• test.ps1 for PowerShell.

In order to execute them you just need to define a environment variable that indicates the directory where the test is. The name of the variable is: DB2UNIT_SRC_TEST_CODE_PATH

The name of the test suite (the schema) is part of the name of the file: Test_{SCHEMA_NAME}.sql Just one test suite can be defined in a file.

The purpose of this script is to clean the environment, prepare the execute, execute and show the results.

There are three parameters to call this script:

• Name of the schema (Test suite name)
• i to install the test suite. If just the schema is provided, this is the default behavior.
• x to execute the test suite.

For example:

• test MY_TESTS - This will install the tests suite called MY_TESTS that is defined in a file named Tests_MY_TEST.sql. This is equivalent to test MY_TESTS i.
• test PERF_TESTS x - This will execute the tests suite called PERF_TESTS in a file called Tests_PERF_TESTS.sql. The PERF_TESTS suite should have been previously installed.
• test OTHER i x - This will install and execute a tests suite called OTHER defined in the file named Test_OTHER.sql.

(v2) Direct execution of assertions

The assertions are normally called in the context of a test suite. This means the RUN_SUITE procedure was called and the environment is prepared to run the assertions; however, some users could tests the assertions when developing the test cases.

(v2) Since version 2, it is possible to execute directly the assertions outside the scope of a test suite. They will not fill a report about the execution but they will retrieve an answer in a temporary table. This allows to have an immediate value for each assertion and better prepare the test cases.

As said before, the values are inserted in a temporary table. The table is cleaned each time an assertion is called. For this reason, they will not contain a whole report, but just the analyzes of the most recent called assertion. The name of the temporal table is: TEMP_REPORT_TESTS. You can test this feature by calling

db2 "CALL DB2UNIT.ASSERT_STRING_EQUALS('Direct call', 'Andres', 'Gomez')"

Result set 1
--------------

MESSAGE
------------------------------------------------------------------------------------------------------------------------
Direct call. Strings have different lengths
Expected      : "Andres"
Actual        : "Gomez"

3 record(s) selected.

Return Status = 3

In addition, you will get a return code that identifies the type of return. For more information about these codes, please check the code.

Advanced concepts

Random execution

By default, tests are executed randomly. However, in some cases tests should be executed sequentially. This can be the case when trying to identify a problem with the environment.

You change the execution behavior by calling:

CALL DB2UNIT.RANDOM_SORT(FALSE);

The previous command change the internal behavior, and execute the test in alphabetical order.

In order to change this behavior to random, you call this:

CALL DB2UNIT.RANDOM_SORT(TRUE);

Sometimes, when developing tests, the environment is not restored correctly (the test fixtures have not been well defiend), and previous tests could change the behavior of following tests. This is a frequent error, and difficult to detect. In order to ease the problem, it is better to design and develop new test with non-random execution.

NOTE: If you provide an execution ID to the RUN_SUITE procedure, the order will be the same of the previous execution with that ID.

Autonomous execution

You can control if the tests should be executed in the same transaction environment (scope) or in an independent transaction environment. This is important for tests that do rollback.

Let's suppose you execute the test with non-autonomous transactions. You did an assertion that wrote in the report, and then, the following part of the code does a rollback. It will undo everything, your previous tests results will be deleted and part of the report will be undo (rollback).

When using autonomous transaction, each message written to the reports, it remains in the report, independently of the rollbacks of the transactions. However, each time a test is executed and each time a message is written, an autonomous transaction is created which could be expensive.

You can switch this behavior by calling the following procedure with true or false, depending if autonomous execution should be activate or not:

CALL DB2UNIT.SET_AUTONOMOUS(TRUE);
CALL DB2UNIT.SET_AUTONOMOUS(FALSE);

In conclusion, with autonomous transactions, you are sure the messages are written, however the tests could take longer. In contrast, with non-autonomous transactions, the tests are faster but you risk to lost some of the results of your previous tests. By default, the tests are in autonomous mode.

When developing new tests, it could be better to not use autonomous transactions, in order to reduce the side effects. Once the tests are finished, they could be executed with autonomous transactions.

Insert sequence of tests

Tests can be executed randomly, and sometimes this randomness should be repeated if an error was detected with a specific order. db2unit saves the order of the tests for each execution, and this information could be replicated in another database.

Let's suppose you have a Continuous Integration server (like Travis-CI), and an unknown error was detected with a specific tests order. In that case, you need to replicate the same tests order in your development environment.

First, you need to recover the tests execution order:

-- Set the current schema to the DB2UNIT version.
SET CURRENT SCHEMA DB2UNIT_X;
SELECT SUITE_NAME, EXECUTION_ID, POSITION, TEST_NAME
FROM SORTS
WHERE SUITE_NAME ='XXXX'
AND EXECUTION_ID = ####;

Where XXXX is the name of your Tests Suite and #### is the execution ID to replicate.

One way to replicate the execution order is with Export/Import. Let's suppose the Tests Suite is called My_Tests and the execution ID is 1234:

-- Set the current schema to the DB2UNIT version.
SET CURRENT SCHEMA DB2UNIT_X;
EXPORT TO SEQ.DAT OF DEL
SELECT SUITE_NAME, EXECUTION_ID, POSITION, TEST_NAME
FROM SORTS
WHERE SUITE_NAME ='MY_TESTS'
AND EXECUTION_ID = 1234;

Once you have the sequence, you connect to the other database, you insert the new ID and just import the sequence:

INSERT INTO EXECUTIONS (EXECUTION_ID)
  VALUES (1234);
INSERT INTO SUITES_EXECUTIONS (SUITE_NAME, EXECUTION_ID)
  VALUES ('MY_TESTS_', 1234);
IMPORT FROM SEQ.DAT OF DEL INSERT INTO SORTS;

If that tests suite has never been executed in the database (or it has not been registered), you need to insert an extra row for the Suite name in the Suites table.

Finally, you just need to call the suite again, by indicating the ID:

CALL DB2UNIT.RUN_SUITE('MY_TESTS', 1234);

When executing the Export/Import, you need to have the current schema as the db2unit schema version (i.e. DB2UNIT_1). Also, you need to be sure that the Execution ID does not exist in the target environment; in that case, you need to modify the exported file.

If you do not want to use Export/Import, you can create the insert statements with the order that you want. The important is to insert a row for the execution ID in the EXECUTIONS table and in the SUISTES_EXECUTIONS.

Batch processing

You can integrate your test suites into a Continuous Integration system by retrieving the set of registered suites, execute all of them and then analyzing the returned error code. Normally, if it is 0, it means all tests passed. To retrieve the set of suites you can use the following line:

CALL DB2UNIT.EXPORT_TESTS_LIST();

This procedure will generate two files:

• Linux/Mac/UNIX
• /tmp/listOfTestSuites - Script that contains all suites in a random order, and with the necessary code to check the return code. This script uses a file called /tmp/sum that will keep the returned codes during the execution.
• /tmp/returnCode - Simple script that return the code of the global execution of all suites.
• Windows
• %TMP%\listOfTestSuites - Script that contains all suites in a random order. This script does not deal with the returned error code.

This procedure was designed to be used with Travis CI. The log4db2 uses this implementation:

• Code for TravisCI
• Execution

License

You can see the license of this framework by executing:

CALL DB2UNIT.LICENSE();

If you just want to check which license is used, you execute:

VALUES DB2UNIT.LICENSE;