Installation

Errors in db2diag.log while installing

• Context:

• You are installing the utility and you are monitoring the diagnostic file.

• Question:

• What is the meaning of the following messages?

db2diag.log:

FUNCTION: DB2 UDB, relation data serv, sqlr_init_tstat, probe:14696
FUNCTION: DB2 UDB, relation data serv, sqlr_init_tstat, probe:14717
FUNCTION: DB2 UDB, relation data serv, sqlr_init_tstat, probe:14777

• Answer:
• They are normal, however the origin of these messages is unknown. They are produced when executing the file: 04-Body.sql, specifically when creating the RUN_SUITE procedure.

Error DUIN1 when installing (Step 1)

The next error message appears when installing:

SQL0438N  Application raised error or warning with diagnostic text: "log4db2 
is not installed. Step 1".  SQLSTATE=DUIN1

This means that log4db2 is not installed in the database. Please visit the log4db2 site to install it in your database before installing db2unit.

For more information visit this section: https://github.com/angoca/db2unit/wiki/Install#log4db2-prerequisite

Error DUIN1 when installing (Step 5 or higher)

The next error message appears when installing:

SQL0438N  Application raised error or warning with diagnostic text: "log4db2 
is not installed. Step 5".  SQLSTATE=DUIN1

The log4db2 version is not recognized. Please visit the log4db2 site to install a correct version in your database.

For more information visit this section: https://github.com/angoca/db2unit/wiki/Install#log4db2-prerequisite

Tests Development

Message "String too long" in the report

String too long: "SQL0433N  Value "XXXXX"

One reason you will get this message in the report is because you called an assertion with a very long message that overpass the limit of the assertion procedure. You can check the precision of the assertion procedures in the API section. The provided message to the assertion should be less than 256 characters.

You need to modify the messages of your tests in the tests suite, reinstall them and execute the tests suite again.

Restrictions

• The schema name for the test suite could have maximum 102 characters. Try to use a shorter schema if you have problems with very long names for the tests suite names.
• When comparing numbers, if a SQL0413N appears it is because the maximum BIG INT has been reached. You should test smaller numbers, because this number overpasses the Db2 capacity.

How to test a not published function or procedure in a module

When using modules, you can have non-published functions or procedures, and they are only accessible from members of the same module, but not from external routines.

Because db2unit is considered an external routine, this utility is declared on its own module, thus, "private" routines cannot be called, nor tested. However, the content of the routines is stored in the database catalog, and it can be retrieved with a query, modified and re-executed with other parameters.

In the following example, a "private" procedure is recreated with a similar name, and some parameters are changed in order to be public (published), and remove the possible name collision (specific). At the same time, a private type is dropped, in order to create a public type. At the end, everything is reversed.

-- Test fixtures
CREATE OR REPLACE PROCEDURE ONE_TIME_SETUP()
 P_ONE_TIME_SETUP: BEGIN
  DECLARE QUERY VARCHAR(4096);

  -- Extract the private function and publish it.
  SELECT
    REPLACE
     (REPLACE
      (REPLACE
       (REPLACE
        (REPLACE
         (BODY,
         'ALTER MODULE LOGGER ADD',
         'ALTER MODULE LOGGER PUBLISH'),
        'FUNCTION GET_LOGGER_DATA',
        'FUNCTION GET_LOGGER_DATA2'),
       'SPECIFIC F_GET_LOGGER_DATA',
       'SPECIFIC F_GET_LOGGER_DATA2'),
      'F_GET_LOGGER_DATA: BEGIN',
      'F_GET_LOGGER_DATA2: BEGIN'),
     'END F_GET_LOGGER_DATA',
     'END F_GET_LOGGER_DATA2')
    INTO QUERY
    FROM SYSCAT.FUNCTIONS
    WHERE FUNCNAME LIKE 'GET_LOGGER_DATA'
    AND FUNCSCHEMA LIKE 'LOGGER_1RC';
  EXECUTE IMMEDIATE QUERY;
  SET QUERY = 'ALTER MODULE LOGGER DROP TYPE LOGGERS_ROW';
  EXECUTE IMMEDIATE QUERY;
  SET QUERY = 'ALTER MODULE LOGGER PUBLISH '
    || 'TYPE LOGGERS_ROW AS ROW ('
    || 'NAME ANCHOR COMPLETE_LOGGER_NAME, '
    || 'LEVEL_ID ANCHOR LOGDATA.LEVELS.LEVEL_ID, '
    || 'HIERARCHY ANCHOR LOGDATA.CONF_LOGGERS_EFFECTIVE.HIERARCHY)';
  EXECUTE IMMEDIATE QUERY;
 END P_ONE_TIME_SETUP @

CREATE OR REPLACE PROCEDURE ONE_TIME_TEAR_DOWN()
 P_ONE_TIME_TEAR_DOWN: BEGIN
  DECLARE QUERY VARCHAR(4096);

  SET QUERY = 'ALTER MODULE LOGGER DROP FUNCTION GET_LOGGER_DATA2';
  EXECUTE IMMEDIATE QUERY;
  SET QUERY = 'ALTER MODULE LOGGER DROP TYPE LOGGERS_ROW';
  EXECUTE IMMEDIATE QUERY;
  SET QUERY = 'ALTER MODULE LOGGER ADD '
    || 'TYPE LOGGERS_ROW AS ROW ('
    || 'NAME ANCHOR COMPLETE_LOGGER_NAME, '
    || 'LEVEL_ID ANCHOR LOGDATA.LEVELS.LEVEL_ID, '
    || 'HIERARCHY ANCHOR LOGDATA.CONF_LOGGERS_EFFECTIVE.HIERARCHY)';
  EXECUTE IMMEDIATE QUERY;
 END P_ONE_TIME_TEAR_DOWN @

What are the guidelines to develop tests?

New tests could be difficult to isolate from previous executions, and it is even harder with random tests executions because the error is more difficult to detect. For this reason, it is better to execute tests sequentially (not randomly), to identify errors rapidly. Also, in order to reduce the possible exceptions, it is better to start executing without autonomous transactions, because the exceptions take a long time to be rollbacked with that kind of transactions.

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

Also, it is recommended to always provide a different message explaining what is the assertion. This is very useful when old tests start to fail due to a new change in the code tested. With descriptive messages in the assertions, it is easier to find the bugs.

Once the tests are passing, you can switch to random test and active autonomous transaction. This will change the normal behavior of your tests. Once, with that configuration you can run the tests suite many times, and eventually this could raise exceptions that will help you identify error in your code and tests.

Tests execution

Execution error

SQL0727N  An error occurred during implicit system action type "3". 
Information returned for the error includes SQLCODE "-206", SQLSTATE "42703" 
and message tokens "EXECUTION_REPORTS.EXECUTION_ID".  LINE NUMBER=13.  
SQLSTATE=56098

log4db2 has been uninstalled and this tool is a prerequisite for db2unit. Reinstall an updated version that framework.

For more information visit this section: https://github.com/angoca/db2unit/wiki/Install#log4db2-prerequisite

Message: There is another concurrent execution of the same test suite

There is a message at the end of the execution and in the EXECUTION_REPORTS table that says:

 There is another concurrent execution of the same test suite.                                                                 
 If not, please execute CALL DB2UNIT.RELEASE_LOCK('MY_SCHEMA')

db2unit has a lock mechanism that prevents concurrent executions of the same tests suite. The lock is just a row with the tests suite name in the SUITE_LOCKS table.

There are two reasons for this message:

• There is effectively another concurrent execution of the same tests suite.
• The last execution of that tests suite did not finished correctly, and the lock was never released.

In order to solve the problem, you have two options:

• If there is really another execution, you just need to wait to finish. You can check what is happening by taking a look at the logs table (log4db2) or analyzing the database with db2top or similar tools.
• If you are sure there is not another execution, you can release the lock manually by doing what the message says, by passing the suite name (schema).

Way to release the lock:

CALL DB2UNIT.RELEASE_LOCK('TEST_DB2UNIT');

Or manually delete the corresponding row in the SUITE_LOCKS table.

Message CLI0112E Error in assignment. when calling RUN_SUITE

This is due to an invalid call. Probably you are trying to execute just one test of a suite, but the TEST_NAME parameter should be named (TEST_NAME => 'TEST_1'). This is the same error as SQL0420N.

Message "Previous execution ID (PREV_EXEC_ID) and a test name (TEST_NAME) were given"

When you see a message like this:

Previous execution ID (PREV_EXEC_ID) and a test name (TEST_NAME) were given (0, test_1)

It means that you called the RUN_SUITE procedure with three parameters. The first one is mandatory, and you could pass any or none of the two others. However, you cannot pass the three parameters:

• SCHEMA_NAME: Execute the given suite.
• SCHEMA_NAME and PREV_EXEC_ID: For self testing. Actually, you should never use this kind of call.
• SCHEMA_NAME and TEST_NAME: Execute the given test name that is in the given suite.

Execution time is too long

The tests suite execution could take a lot of time. It happens because the framework is using autonomous transactions to write the report and to execute each of the tests. The transaction mode change is expensive and takes time. If you want to see the progress execution, check the logs or query the report table with UR isolation.

db2 "CALL LOGGER.NEXT_LOGS()"
db2 "SELECT * FROM myschema.REPORT_TESTS WITH UR"
db2 "SELECT * FROM DB2UNIT_EXECUTION_REPORTS"

You can change this behavior to use the same transaction scope for the whole process.

Suite execution does not do nothing

You could arrive to a situation when you call a tests suite execution you get nothing. It is probably due to previously cancelled execution, and the global variables still keep values that restrict the normal execution. You can reset the environment by calling this procedure:

CALL DB2UNIT.CLEAN();

How to prevent the execution of a tests suite

The tests suites are installed on Db2, and they depend on the security of the database. If you have a strong security schema, you can prevent the execution of your tests by allowing some users to execute the procedures that contain your tests. If the same way, if you cannot execute an existing tests suite, probably it is because you do not have the necessary rights to run it.

Error "PSSecurityException" when executing installation from PowerShell

Like this:

No se puede cargar el archivo C:\Users\AngocA\Documents\GitHub\db2unit\src\test\scripts\init-dev.ps1 porque en el sistema está deshabilitada la ejecución de scripts. Vea "get-help about_signing" para obtener más información.
En línea: 1 Carácter: 15
    + .\init-dev.ps1 <<<<
    + CategoryInfo          : NotSpecified: (:) [], PSSecurityException
    + FullyQualifiedErrorId : RuntimeException

The PowerShell scripts are not allowed to be executed by default. You should change this policy in order to execute these scripts. You execute PowerShell as administrator, and run this:

Set-ExecutionPolicy unrestricted

Tests report

Error in report

TEST_01          Error             301505 Executing TEST_01                                               
TEST_01          -                      - Exception: SQLCode-420-SQLState22018-SQL0420N  Invalid character

If the test is valid, but you are still receiving this message, it is because the Assertion procedure used is not valid to compare the objects. For example you are using ASSERT_INT_EQUALS to compare two strings.

There are too many messages in the logs about 'write_in_report'

You can change the log granularity by changing the log4db2 configuration. In order to prevent the messages about WRITE_IN_REPORT, you can execute this:

CALL LOGADMIN.REGISTER_LOGGER('DB2UNIT_1B.DB2UNIT.WRITE_IN_REPORT', 0);

What is the purpose of a dedicated user temporary table space

This tablespace is created as part of the installation process. This is necessary for some process, like create the TAP report. If there is another user temporary tablespace, you can delete the one created at the db2unit installation. The important thing is to have access to a user tablespace with the same page size.

What is the license of this framework

This framework is licensed under the terms of the GPL v3 license. You can see the license by executing:

CALL DB2UNIT.LICENSE;

If you just want to check which license is used in this utility:

VALUES DB2UNIT.LICENSE;