Troubleshooting
Problem
User receives various errors when using Controller.
Symptom
The errors vary depending on what function the user is performing. Below are some examples:
Database Optimise:
COMException (0x80004005): [DB2/AIX64] SQL0727N An error occurred during implicit system action type "1". Information returned for the error includes SQLCODE "-551", SQLSTATE "42501" and message tokens "<username>|SELECT|SYSCAT.TABLES". SQLSTATE=56098
Consolidation:
Number: 5
Source: ControllerProxyClient
Description: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Runtime.InteropServices.COMException (0x800A01B8): Automation error (Error occured within method=RunConsol in the module with progid=FrMiscBT.ConsBusinessT)
at FrBatchBT.clsBatchTClass.Batch(String sGuid, String sUser, Int16 iProcessId, String sJobName, String sParams, Int32 lRegDate, Int32 lRegTime, Int32 lSchedDate, Int32 lSchedTime, Int16 iScheduleType, String sUdl, Int32 lDependentBy, String sTimeChain, Boolean bBatchMode)
Saving changes inside 'Server Preferences':
Number: 5
Source: FrangoDirect.StructureVersionsD.UpdateStrucVer#ControllerProxyClient
Description: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Runtime.InteropServices.COMException (0x80004005): CLI0115E Invalid cursor state. SQLSTATE=24000
at FrTransferBT.StructureVersionsTClass.UpdateStrucVer(String sGuid, String sUser, String sTyp2, String sSource, Object bUpdateChangeDate, Object bUpdateReleaseDate, String sChangeDate, String sReleaseDate, Connection& conDB)
at Cognos.Controller.Proxy.CCRWS.StructureVersionsT_UpdateStrucVer(String sGuid, String sUser, String sTyp2, String sSource, Object bUpdateChangeDate, Object bUpdateReleaseDate, String sChangeDate, String sReleaseDate, Object& conDB)
Cause
Incorrect security configuration on the DB2 server/database.
- Specifically, the most likely root cause (of this) is that the database was moved from one DB2 server (e.g. 'test') to a different DB2 server (e.g. 'production'), but the steps taken to restore it were incorrect.
More Information:
The crucial error message is: SQLCODE "-551"
- This indicates a security error (for example see separate Technote #1304863).
In one real-life example, the customer was incorrectly restoring the database (onto the different/target DB2 server) by using the following command:
- db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=
db2stop force
db2start
db2 restore db CCR_DEV from . on /home/prod_system_account REPLACE EXISTING
db2 rollforward db CCR_DEV complete NORETRIEVE
db2 update db cfg for CCR_DEV using LOGARCHMETH1 DISK:/home/prod_system_account/logs
db2 activate db ccr_dev
Where:
- CCR_DEV = the name of the Controller database
- test_system_account = the name of the administrative operating 'system account' user (which runs the database server instance) for the test DB2 server
- prod_system_account = the name of the administrative operating 'system account' user (which runs the database server instance) for the production DB2 server
Environment
Controller databases hosted on DB2.
Diagnosing The Problem
In one real-life example, the Controller application server was configured to connect to the database using a specific operating system user account (for example 'fastnet'). However, the error message mentioned a different user, for example 'test_system_account':
SQLCODE "-551", SQLSTATE "42501" and message tokens "test_system_account|SELECT|SYSCAT.TABLES". SQLSTATE=56098
The customer has several databases, stored on two separate DB2 servers ('test' and 'production'). The 'test' databases all worked OK, but the production databases (which were copies of the 'test' databases) all failed.
- The error message mentioned a different user (for example 'test_system_account') which only existed on the 'test' (good) DB2 server, not the 'production' (bad) DB2 server.
Resolving The Problem
Fix:
When restoring DB2 Controller-database backups onto a different DB2 server (from the original source DB2 server) use the correct commands.
Example:
In one real-life example, the following is the correct script:
db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
db2stop force
db2start
db2 restore db CCR_DEV from . on /home/prod_system_account REPLACE EXISTING
db2 rollforward db CCR_DEV complete NORETRIEVE
db2 update db cfg for CCR_DEV using LOGARCHMETH1 DISK:/home/prod_system_account/logs
db2 activate db ccr_dev
db2 connect to ccr_dev
db2 revoke accessctrl, bindadd, connect, createtab, create_external_routine, create_not_fenced_routine, dbadm, dataaccess, implicit_schema, load, quiesce_connect on database from user test_system_account;
db2 connect reset
Workaround:
Ask the DB2 administrator (DBA) to correct the security configuration on the DB2 server/database.
Example #1:
In one real-life example, the following is the correct script:
db2 connect to ccr_dev
db2 grant accessctrl, bindadd, connect, createtab, create_external_routine, create_not_fenced_routine, dbadm, dataaccess, implicit_schema, load, quiesce_connect on database to user test_system_account;
db2 connect reset
db2 terminate
Example #2:
Here is an alternative script which solved the problem (in one real-life example):
db2 connect to ccr_dev
cd ../sqllib/bnd;
db2 bind @db2ubind.lst ACTION REPLACE blocking all grant public OWNER prod_system_account;
db2 bind @db2cli.lst ACTION REPLACE blocking all grant public OWNER prod_system_account;
db2 bind db2schema.bnd ACTION REPLACE blocking all grant public OWNER prod_system_account;
db2 bind db2look.bnd ACTION REPLACE blocking all grant public OWNER prod_system_account;
db2 bind db2lkfun.bnd ACTION REPLACE blocking all grant public OWNER prod_system_account;
db2 bind db2ueiwi.bnd ACTION REPLACE blocking all grant public OWNER prod_system_account;
db2 grant accessctrl, bindadd, connect, createtab, create_external_routine, create_not_fenced_routine, dbadm, dataaccess, implicit_schema, load, quiesce_connect on database to user test_system_account;
db2 connect reset
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21681677