Tuesday, 26 August 2014

IBM Business Monitor and DB2 users, not instances

So I have been wrestling with IBM Business Monitor and, more specifically, Cognos, which was failing to start up with errors such as DPR-DPR-1035, DPR-CMI-4007CM-CFG-5063 and CM-CFG-5137.

Various Technotes etc. suggested that the problem might be with the DB2 database.

I did some digging: -

db2 list tables for schema db2user2

db2 connect to cognoscs

   Database Connection Information

 Database server        = DB2/AIX64 10.1.0
 SQL authorization ID   = DB2INST3
 Local database alias   = COGNOSCS


db2 list tables for schema DB2USER2 

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
CMCAPACITY                      DB2USER2        T     2014-08-26-15.26.49.093007
CMSYSPROPS                      DB2USER2        T     2014-08-26-15.26.48.688417

  2 record(s) selected.

whereas this is what I should be seeing: -

db2 list tables for schema DB2INST3

...
Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
CMARCHIVEQUEUE                  DB2INST3        T     2014-05-02-19.01.41.736857
CMARCHIVESTATUS                 DB2INST3        T     2014-05-02-18.59.53.722937
CMCAKEYS                        DB2INST3        T     2014-05-02-18.59.47.494415
CMCAPACITY                      DB2INST3        T     2014-05-02-18.59.43.961958
CMCAPROPS                       DB2INST3        T     2014-05-02-18.59.49.009567
CMCLASSES                       DB2INST3        T     2014-05-02-19.01.30.263672
CMCRYPTOKEYS                    DB2INST3        T     2014-05-02-18.59.45.716151
CMDATA                          DB2INST3        T     2014-05-02-18.59.52.343365
CMDATAUPGRADE                   DB2INST3        T     2014-05-02-19.01.39.248572
CMDELETEQUEUE                   DB2INST3        T     2014-05-02-19.01.42.466589
CMGUIDS                         DB2INST3        T     2014-05-02-19.01.32.947199
CMLOCALES                       DB2INST3        T     2014-05-02-19.01.31.703091
CMOBJECTS                       DB2INST3        T     2014-05-02-18.59.50.444984
CMOBJNAMES                      DB2INST3        T     2014-05-02-18.59.51.545778
CMOBJPROPS1                     DB2INST3        T     2014-05-02-18.59.55.174490
CMOBJPROPS10                    DB2INST3        T     2014-05-02-18.59.55.916177
CMOBJPROPS11                    DB2INST3        T     2014-05-02-18.59.57.279869
CMOBJPROPS13                    DB2INST3        T     2014-05-02-18.59.58.081353
CMOBJPROPS14                    DB2INST3        T     2014-05-02-18.59.59.524858
CMOBJPROPS15                    DB2INST3        T     2014-05-02-19.00.00.901173
CMOBJPROPS16                    DB2INST3        T     2014-05-02-19.00.02.495462
CMOBJPROPS17                    DB2INST3        T     2014-05-02-19.00.04.026643
CMOBJPROPS18                    DB2INST3        T     2014-05-02-19.00.04.740375
CMOBJPROPS2                     DB2INST3        T     2014-05-02-19.00.06.155965
CMOBJPROPS20                    DB2INST3        T     2014-05-02-19.00.06.961562

...

I figured out that the problem is  :-)

I'm connecting from WAS to DB2 as a user other than the DB2 instance account, and ( therefore ) I need to change my SQL that's used to create the database objects etc.

Here's what worked for me: - 

db2 "CHANGE DATABASE COGNOSCS COMMENT WITH 'IBM Cognos Content Store'"
db2 CONNECT TO COGNOSCS

db2 GRANT CREATETAB,BINDADD,CONNECT,IMPLICIT_SCHEMA ON DATABASE  TO USER db2user2

db2 UPDATE DATABASE CONFIGURATION USING LOCKTIMEOUT 240 DEFERRED

db2 CREATE BUFFERPOOL CCS_08KBP IMMEDIATE SIZE 1000 PAGESIZE 8K
db2 CREATE BUFFERPOOL CCS_32KBP IMMEDIATE SIZE 1000 PAGESIZE 32K

db2 CREATE SYSTEM TEMPORARY TABLESPACE TSN_SYS_CCS IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 32K BUFFERPOOL CCS_32KBP

db2 CREATE USER TEMPORARY TABLESPACE TSN_USR_CCS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8K BUFFERPOOL CCS_08KBP
db2 CREATE REGULAR TABLESPACE TSN_REG_CCS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8K BUFFERPOOL CCS_08KBP

db2 GRANT USE OF TABLESPACE TSN_USR_CCS TO USER db2user2
db2 GRANT USE OF TABLESPACE TSN_REG_CCS TO USER db2user2

db2 terminate

Once I rebuilt the COGNOSCS database, and restarted the Support cluster, all was well :-)

No comments: