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:

Yay, VMware Fusion and macOS Big Sur - no longer "NAT good friends" - forgive the double negative and the terrible pun ...

After macOS 11 Big Sur was released in 2020, VMware updated their Fusion product to v12 and, sadly, managed to break Network Address Trans...