Hi Brenda,
On page 125 in the book, I display the Linux path I found, using the resulting createDb.sql DDL output in :
(/opt/ibm/cognos/analytics114/configuration/schemas/content/db2 ).
This procedure I outlined should have been used by your current version of Cognos on your system to create the CM database.
(I used Cognos version 11.1.4, in November 2019, so the actual table structures have altered now, depending on which Cognos version you currently use, but note this DDL is dated from 2008, so maybe not much will have changed!).
The DDL which was created, was used by me to actually create a Cognos DB2 database using the Linux commands :
su - db2inst1
db2 -tvf /opt/ibm/cognos/analytics114/configuration/schemas/content/db2/createDb.sql
This SQL, listed as below, just creates the main users and tablespaces, so is just the starting point.
If you have IBM Data Studio 4.1.2 (or similar version), you can load the DB2 Database
(NOTE: After an upgrade of DB2 you may have to upgrade the cm database using:
[db2inst1@ECMUKDEMO6 ~]$ db2 upgrade database cm
DB20000I The UPGRADE DATABASE command completed successfully.
[db2inst1@ECMUKDEMO6 ~]$ )
The IBM Data Studio 4.1.2 can be used to connect to the Cognos cm database :
In the above, the server name I use is ecmukdemo6 and the port is set as 50000 , so your database admin should be able to tell you what to use instead of this.
To set up an Audit database (if you haven't already got one) , the following youtube video should help:
You may also find the link as follows useful:
Which does indicate that there have been a few changes:
" As of Cognos Analytics 11.1.7, the Audit Database has grown to 21 table"
and
"The key tables most administrators will be interested in are the 6 tables which log the user activity and reporting activity in the system.
- COGIPF_USERLOGON : Stores user logon (including log off) information
- COGIPF_RUNREPORT : Stores information about report executions
- COGIPF_VIEWREPORT : Stores information about report view requests
- COGIPF_EDITQUERY : Stores information about query runs
- COGIPF_RUNJOB : Stores information about job requests
- COGIPF_ACTION : Records user actions in Cognos (this table may grow much more rapidly than the others) "
As I mentioned above, the DDL I mentioned as the SQL as follows:
--
-- US Government Users Restricted Rights - Use, duplication or disclosure
-- restricted by GSA ADP Schedule Contract with IBM Corp.
-- Copyright (C) 2008 Cognos ULC, an IBM Company. All rights reserved.
-- Cognos (R) is a trademark of Cognos ULC, (formerly Cognos Incorporated).
-- Use this template to create the IBM Cognos content database. Replace these variables:
-- cm : Database name
-- db2inst1 : User ID, this account is used by the product to connect to the content store
-- This script must be run as a user that has sufficient privileges to access and create the database.
-- The database user account needs to exist for the product to function.
CREATE DATABASE cm AUTOMATIC STORAGE YES ALIAS cm USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 8192;
CHANGE DATABASE cm COMMENT WITH 'IBM Cognos Content Store';
CONNECT TO cm;
UPDATE DATABASE CONFIGURATION USING APPLHEAPSZ 1024 DEFERRED;
UPDATE DATABASE CONFIGURATION USING LOCKTIMEOUT 240 DEFERRED;
CONNECT RESET;
CONNECT TO cm;
CREATE BUFFERPOOL cm_08KBP IMMEDIATE SIZE 1000 PAGESIZE 8K;
CREATE BUFFERPOOL cm_32KBP IMMEDIATE SIZE 1000 PAGESIZE 32K;
CONNECT RESET;
CONNECT TO cm;
CREATE SYSTEM TEMPORARY TABLESPACE TSN_SYS_cm IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 32K BUFFERPOOL cm_32KBP;
CREATE USER TEMPORARY TABLESPACE TSN_USR_cm IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8K BUFFERPOOL IBMDEFAULTBP;
CREATE REGULAR TABLESPACE TSN_REG_cm IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8K BUFFERPOOL cm_08KBP;
DROP TABLESPACE USERSPACE1;
CONNECT RESET;
CONNECT TO cm;
CREATE SCHEMA db2COGNOS AUTHORIZATION db2inst1;
COMMENT ON SCHEMA db2COGNOS IS 'IBM Cognos Content Store';
GRANT CREATETAB,BINDADD,CONNECT,IMPLICIT_SCHEMA ON DATABASE TO USER db2inst1;
GRANT CREATEIN,DROPIN,ALTERIN ON SCHEMA DB2COGNOS TO USER db2inst1;
GRANT USE OF TABLESPACE TSN_USR_cm TO USER db2inst1;
GRANT USE OF TABLESPACE TSN_REG_cm TO USER db2inst1;
CONNECT RESET;
Hope this helps,