IBM Support

"SQLCODE "-551", SQLSTATE "42501" ... "<username>|SELECT|SYSCAT.TABLES" when using Controller

Technote (troubleshooting)


Problem(Abstract)

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

1304863 - SQL0727N error during implicit rebind attempt

Document information

More support for: Cognos Controller
Controller

Software version: 10.1.1

Operating system(s): Windows

Reference #: 1681677

Modified date: 09 September 2014


Translate this page: