IBM Support

** Troubleshooting ** "DB2 SQL Error: SQLCODE=-964, SQLSTATE=57011, SQLERRMC=null, DRIVER=3.59.81 ** ERROR: Invalid datbase, XFRANGO is empty" creating new Controller database

Troubleshooting


Problem

User launches "Controller Configuration" and selects a database connection. User starts the standard (Java-based) "Database Conversion Utility", and chooses "Controller DB". User clicks "Connect" and chooses "Create DB". After a while, an error appears.

Symptom

Running script: script/db2/trigger_trg_saxipmain_insert.sql
Running script: script/db2/trigger_trg_saxipstand_delete.sql
Running script: script/db2/trigger_trg_saxipstand_insert.sql
** ERROR: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-964, SQLSTATE=57011, SQLERRMC=null, DRIVER=3.59.81
** ERROR: Invalid datbase, XFRANGO is empty

Cause

Controller database's transaction log settings are too restrictive.

  • Specifically, the value for "LOGPRIMARY" is too small (for example it was set to 20).
  • For more details on this topic, see separate IBM article "Tivoli Identity Manager Express Version 4.6 - Database update fails with an SQL error".

There are several different possible causes for the log settings being incorrect:
  • Scenario #1 - DBA has configured the database to have the wrong (too restrictive) log settings
  • Scenario #2 - DBA has configured the database to have the correct settings, but these settings have not yet been activated by the database server.
    • See separate IBM Technote 1614478.

More Information:


Transaction logs keep track of each and every transaction - if anything goes wrong with database/instance it gives the database a chance to roll forward/back these transactions. There are many different potential ways to configure transaction logs, such as:
  • In DB2 there are two different ways of taking care of transactions:
    1. Circular
    2. Archive
  • Transaction log file sizes
  • Number of log files

The optimal transaction log configuration varies between database usage (for example 'datawarehouses', 'OLTP') and how intense the usage is (for example the number of concurrent active users).
=> Therefore it is impossible to give precise/exact recommendations for transaction log settings.

Extra Tip:
One tip can be useful is to use the "logsecond" parameter - if 'logprimary' is full a second logfile ('logsecond') is created. This is created "on the fly when needed", and they are freed (over time) when the database manager determines that they are no longer needed. There is a downside to this - there can be some overhead to create a new logfile during a transaction.
  • For more information on 'logsecond' see link at the end of this Technote.

Environment

Controller 10.1 database hosted on DB2.

Resolving The Problem

Scenario #1

Fix:

Increase the value for the DB2 setting "LOGPRIMARY" on the Controller application repository database.

  • NOTE: The optimal value for your environment will vary depending on your circumstances. In one real-life example, the original value was "20", and the problem was fixed by increasing the value to 100.

Steps:

  • To see the existing value for "LOGPRIMARY" run the following command script:
      • get db cfg for <database_name>

Inside the results, there will be a line similar to the following:

    Number of primary log files (LOGPRIMARY) = 20

  • To modify the value for "LOGPRIMARY" run the following command script:
    • update db cfg for <database_name> using LOGPRIMARY <newvalue>

TIP: For more details on this, see separate IBM Technote #1298630.

Workaround:

  • Delete the database
  • Re-create the database (create a new/blank DB2 database)
  • Use the 'legacy' (non-JAVA) version of DbConv instead, to create the database
  • Afterwards, this same program to raise the database version to a compatible version.
  • Afterwards, re-use the JAVA version of DbConv to finish upgrading the database.

Steps:

  • Delete the DB2 database
  • Re-create the DB2 database

Afterwards:
  1. Launch Explorer
  2. Navigate to the "...\c10\legacy" directory. TIP: By default, this is located here: C:\Program Files\ibm\cognos\c10\legacy
  3. Double-click on the file "DbConv.exe"
  4. Inside the 'data' section, click on the '...' button
  5. Browse to the relevant UDL file inside the "DATA" directory (for example C:\Program Files\ibm\cognos\c10\data\production.udl)
  6. Click "Open"
  7. Choose a "Group Language" (for example "EN")
  8. Choose a Local Language" (for example "EN")
  9. Click "CreateDb". TIP: This will upgrade to database version 300.
  10. Click "Run Steps". TIP: This will upgrade to database version 833.
  11. After the processes have completed, click "Close"
  12. Re-launch "Controller Configuration"
  13. Open section 'Database Connections'
  14. Highlight connection name (for example 'production') and clicks the green triangle ("run") button at the top of the screen. This launches DbConv ("Database Conversion Utility")
  15. Click "Connect". Note how the 'Current version" will be set to a higher number.
  16. Click on "Upgrade" to upgrade the database to the current Controller version level.
  17. Finally, click "Close".

TIP: If you get an error "Error 429 (Hex 1AD)..." when using "DbConv.exe" then:

  1. Launch a command prompt by clicking "Start" - "Run" and then typing in "CMD" <Enter>
  2. Change directory to the c10\common folder (for example using: cd "C:\Program Files\ibm\cognos\c10\common")
  3. Register the DLL file using: regsvr32.exe FrCrypto.dll
  4. Test

Scenario #2

Ensure that the correct DB2 database settings have been actioned *before* using "Database Conversion Utility".

  • See separate IBM Technote 1614478.

[{"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.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21503319