IBM Support

"Assignment of a NULL value to a NOT NULL column "TBSPACEID=5, TABLEID=5, COLNO=0" is not allowed" when starting Cognos service for the first time, due to DB2 using Oracle compatibility mode

Troubleshooting


Problem

Administrator has created brand new application server. Administrator has configured Cognos Configuration to use a DB2 ContentStore. Administrator starts Cognos service. An error appears.

Symptom


05:25:37, CAF-WRN-0010 CAF input validation enabled.
05:25:37, CAF-WRN-0021 CAF Third Party XSS checking disabled.
05:25:46, CM-CFG-5063 A Content Manager configuration error was detected while connecting to the content store. CM-CFG-5114 An error occurred while locking the content store database. Cause: Assignment of a NULL value to a NOT NULL column "TBSPACEID=5, TABLEID=5, COLNO=0" is not allowed.. SQLCODE=-407, SQLSTATE=23502, DRIVER=3.58.68 Stack trace: com.ibm.db2.jcc.am.no: Assignment of a NULL value to a NOT NULL column "TBSPACEID=5, TABLEID=5, COLNO=0" is not allowed.. SQLCODE=-407, SQLSTATE=23502, DRIVER=3.58.68 at com.ibm.db2.jcc.am.ed.a(ed.java:672) at com.ibm.db2.jcc.am.ed.a(ed.java:60) at com.ibm.db2.jcc.am.ed.a(ed.java:127) at

Cause

DB2 database server configured to use Oracle compatibility mode.

  • This is the correct configuration for Controller application repository databases
  • However, it is incorrect for the Cognos ContentStore creation scripts.

Diagnosing The Problem

Launch 'DB2 Command Window - Administrator' and run the following command:


    db2set

If you are using Oracle-compatibility mode (bad) then the result will include the following line:

    DB2_COMPATIBILITY_VECTOR=ORA

Resolving The Problem

Modify the DB2 database server to not use Oracle compatibility, during the ContentStore initial creation.

  • Afterwards, change the DB2 server to use Oracle compatibility so that Controller works as expected.

Steps:

1. Obtain some downtime (no users using the DB2 server/databases)

2. Drop (delete) the current

  • Naturally, if required, take a backup of the database before deleting it.

3. Launch 'DB2 Command Window - Administrator'

4. Run the following commands:


    db2set DB2_COMPATIBILITY_VECTOR=

    db2stop

    db2start


5. Re-create the content store database (e.g. 'COGNOSCS')

6. Launch 'Cognos Configuration'

7. Start the Cognos service

8. After this has successfully completed, launch 'DB2 Command Window - Administrator'

9. Run the following commands:


    db2set DB2_COMPATIBILITY_VECTOR=ORA

    db2stop

    db2start


10. Create/use Controller application repository databases as appropriate.

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21677923