Product:

Cognos Analytics 11.1.7  kit_version=11.1.7-2304260612

Issue:

How apply a fix pack for CA 11.1.7?

Solution:

Download the fix pack from IBM. https://www.ibm.com/support/pages/node/6985631 

Make a backup of the content store by export it from inside Cognos Connection:

On the Cognos Analytics ‘Welcome’ dashboard, click the ‘Manage’ tab and select ‘Administration Console’.

Select the ‘Configuration’ tab and click ‘Content Administration’ on the left-hand side.

In the top right-hand side, click the icon ‘New Export’.

Specify a name for your new export (full backup contentstore) and click ‘Next’.

Click the ‘Select the entire Content Store’ radio button and select ‘Next’.

Choose the location where you want to store the deployment archive. Click ‘Next’.

Assign a password to your archive. The password must contain at least 8 characters. Re-enter your password for confirmation and then click ‘OK’.

Verify the details you have input before clicking ‘Next’.

Select ‘Save and run once’.

Specify a time when you want to run the export and click ‘Run’.

Click ‘OK’ to complete the export.

To save time, move content in folder D:\Program Files\ibm\cognos\analytics\deployment to d:\temp before the upgrade. Copy the few files you need back after the upgrade.

Backup the configuration in cognos configuration:

1. Open Cognos Configuration.
2. Click File > Export As.
3. Select a location and enter a file name for the XML file.
4. Click Save.

Make a backup of the following files to a d:\temp folder:
D:\Program Files\ibm\cognos\analytics\webcontent\planning.html
D:\Program Files\ibm\cognos\analytics\webcontent\pmhub.html
D:\Program Files\ibm\cognos\analytics\webcontent\web.config
D:\Program Files\ibm\cognos\analytics\webcontent\tm1\web\tm1web.html

D:\Program Files\ibm\cognos\analytics\webcontent\bi\planning.html
D:\Program Files\ibm\cognos\analytics\webcontent\bi\pmhub.html
D:\Program Files\ibm\cognos\analytics\webcontent\bi\web.config
D:\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web\tm1web.html

D:\Program Files\ibm\cognos\analytics\templates\ps\portal\variables_CCRWeb.xml
D:\Program Files\ibm\cognos\analytics\templates\ps\portal\variables_plan.xml
D:\Program Files\ibm\cognos\analytics\templates\ps\portal\variables_TM1.xml

D:\Program Files\ibm\cognos\analytics\configuration\cclWinSEHConfig.xml

Restore only the missing files after the installation.
Files to be preserved during an upgrade are listed in the D:\Program Files\ibm\cognos\analytics\configuration\preserve\.ca_base_preserve.txt file. Do not edit this file. Instead, edit the D:\Program Files\ibm\cognos\analytics\configuration\preserve\preserve.txt file if you want to remove or preserve certain files or directories when upgrading.

################################################################
#
# IBM Confidential
#
# IBM Cognos Products: Preserve Files by the Install
#
# (C) Copyright IBM Corp. 2017
#
# Edit this file (preserve.txt) to remove or preserve files or directories when upgrading. 
#
# 
# Instructions:
#
# - Edit preserve.txt before running an upgrade on an existing install.
# - Use '#' at the beginning of a line to insert a comment.
# - The keyword "exclude:" can be used to remove files inside a preserved directory (see examples below).
# - List directories or files relative to the installation root directory (see examples below).
#
#
# e.g.: To remove this file: <installdir>/media/samples.doc, add this line:
# exclude:media/samples.doc 
#
# e.g.: To preserve the file <installdir>/msgsdk/cm_ldkspec.xml, add this line:
# msgsdk/cm_ldkspec.xml
#
# e.g.: To preserve the contents of the folder: <installdir>/cps/sap/webapps, add this line
# cps/sap/webapps
#
# Note on order of precedence: Files to be excluded should be specified first (before the directories which contain them).
#
################################################################

# Specify files to exclude first


# Specify files or folders to preserve

If you have changed security or use certificates, then you need to also backup all the certificates store files.

Stop the Cognos Analytics Service and close down Cognos Configuration. Stop the Apache or IIS webserver services.

Launch the downloaded installation file (analytics-installer-2.2.27-win.exe) and follow the wizard.

Choose your Language and click Next.

Choose What you want to install – for an upgrade this will be IBM Cognos Analytics click Next.

Choose to Accept the license and click Next.

Choose the location. This must be the location of your Cognos Analytics instance that you would like to upgrade and also the shortcut folder name. Click Next.

Click Yes to confirm you are Installing in the same location and are overwriting a previous installation.

Click Install at the summary screen.

When complete click Done to complete the upgrade.

Open Cognos Configuration – you will be prompted that older versions of Configuration files were found and configuration files have been upgrade to the latest version. click OK and Save your configuration.

Repeat the steps for all servers in your distributed environment, before starting the Cognos Analytics Content Manager Services first and then the rest.

Check the file D:\Program Files\ibm\cognos\analytics\cmplst.txt to see what version is installed.

More Information:

https://www.ibm.com/support/pages/ibm-cognos-analytics-11x-fix-lists 

https://www.ibm.com/support/pages/how-export-entire-content-store-cognos-analytics-11 

https://pmsquare.com/analytics-blog/2022/6/8/how-to-find-your-cognos-version-build-and-common-name

https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=servers-copying-cognos-analytics-certificate-another-server

https://www.ibm.com/support/pages/how-add-3rd-party-ca-allow-ssl-between-components-ibm-cognos-analytics-11

Product:

Cognos Analytics

Problem:

What are the tables for in CA11 database?

Solution:

This is a old list for Cognos 8, maybe it is still valid.

CMSYSPROPS This table has the Content Store Version.
CMOBJNAMES This table has the names of all the objects in the content store.
CMOBJPROPS1 Users, Roles \ Group Distribution list and contact information such as Email, phone number, Fax Given name etc are stored in this table
CMOBJPROPS2 Report scheduling information is stored. This table has fields like hour, day week etc
CMOBJPROPS3 Stores Screen Tip and Object description provided while creating the objects are stored here
CMOBJPROPS4 Stores the printer paper setting details like height and width of A3 , A4 , letter and 11X17 paper orientations
CMOBJPROPS6 Has the details of the all packages which were published using the FM.
CMOBJPROPS7 This table stores the XML of all reports and models. This is basically to maintain the metadata about the structure of the reports and models.
CMOBJPROPS10 Contact information is stored in this table. This table has columns like Contact Email and Contact.
CMOBJPROPS11 This table stores the data sources configuration details like connect string, the cube location etc.
CMOBJPROPS13 It stores the names of parameter passed to the range prompts
CMOBJPROPS14 Has details regarding the versions, creation time etc of the objects in the content store.
CMOBJPROPS16 This table provides the status of the multiple services of Cognos like LogService, MonitorService, ReportService, SystemService, JobService
CMOBJPROPS17 Stores performance details of each component like “query Studio, Analysis Studio, Event Studio” etc
CMOBJPROPS18 This table stores the drill path from the source to the final target report.
CMOBJPROPS20 Stores details regarding which are all the reports for which prompting has been enabled. And also has the details of which are the reports which has the default report options overridden.
CMOBJPROPS24 Stores the printer configuration details
CMOBJPROPS25 Stores the data regarding the objects deployed, like the deployed folder, the reports, the number of folders present in the deployment archive, etc
CMOBJPROPS26 This table stores the data about all the packages imported / exported in C8, with the properties selected during the process.
CMOBJPROPS27 Has the details regarding the data source created in the content store using Cubes.
CMOBJPROPS30 Stores the registration , service description etc details about portlets in this table
CMOBJPROPS31 Has the custom logging level for each of the Cognos services, CMID can be be linked to CMOBJNAMES for the names of each of the services
CMOBJPROPS32 Has the details of the stored procedures used as the query items in the FM model.
CMOBJPROPS33 Detail related to users, user groups user roles.
CMOBJPROPS34 Has the details regarding the drill through parameters of the drill through reports. The parameter assign values are present in coded format
CMOBJPROPS36 Has the list of all the Models published using framework manager
CMOBJPROPS37 This table has the details of the routing sets configured for server / load balancing for each package published in the content store
CMOBJPROPS38 Configuration details about number of items to retrieve in studios, for a package are saved under this table.
CMOBJPROPS39 Has values for properties of reports and views. PROPID can be linked to CMPROPERTIES for property names
CMOBJPROPS52 Contains the properties for the connections
CMOBJPROPS55 Has the URI for icons for each entry in Cognos Connection
CMLOCALES Has the locale ids associated with each language supported by Cognos 8

More Information:

https://www.envisn.com/envisn-cognos-blog/bid/43348/Demystify-Your-Cognos-Content-Store-Part-II 

https://www.cognoise.com/index.php?topic=28289.0

Below will list all reports

With CognosContent AS (
Select a.CMID, a.PCMID, b.NAME, b.Name as Parent, c.name as Content
,Cast(b.NAme as varchar(max)) as path
, 0 as level
from cmObjects a
inner join CMOBJNAMES b on a.CMID = b.CMID
inner join CMCLASSES c on a.CLASSID = c.CLASSID
Where b.ISDEFAULT = 1
and a.PCMID =0
and a.CMID = 2
UNION ALL
Select a.CMID, a.PCMID, b.NAME as parent, d.NAME, c.NAME
, d.path + '\' + CAST (b.Name as varchar(Max)) as path
, d.level + 1 as level
from cmObjects a
inner join CMOBJNAMES b on a.CMID = b.CMID
inner join CMCLASSES c on a.CLASSID = c.CLASSID
inner join CognosContent d on a.PCMID = d.CMID
Where b.ISDEFAULT = 1
)
Select NAME as ReportName, path, level, content from CognosContent
Where
Content = 'report' or Content = 'dashboard' or content = 'analysis'
order by path

Product:

Cognos Analytics 11
Microsoft Windows 2019 server
Microsoft SQL server

Issue:

How can i see in a report the schedules that are active in CA11?

Solution:

The information is in the CA11 Content Store database. You have to make a SQL query like this:

SELECT  [CMID]
,[TASKID]
,[STARTDATE]
,[ENDDATE]
,[ENDTYPE]
,[EVERYNPERIODS]
,[DAILYPERIOD]
,[MONTHLYABSDAY]
,[MONTHLYRELDAY]
,[MONTHLYRELWEEK]
,[TYPE]
,[YEARLYABSDAY]
,[YEARLYABSMONTH]
,[YEARLYRELDAY]
,[YEARLYRELMONTH]
,[YEARLYRELWEEK]
,[ACTIVE]
,[WEEKLYMONDAY]
,[WEEKLYTUESDAY]
,[WEEKLYWEDNESDAY]
,[WEEKLYTHURSDAY]
,[WEEKLYFRIDAY]
,[WEEKLYSATURDAY]
,[WEEKLYSUNDAY]
,[PRIORITY]
,[INTRARECURSTART]
,[INTRARECUREND]
,[INTRARECURINTERVAL]
FROM [dbo].[CMOBJPROPS2]
Where ACTIVE = 1

To list all objects name use:

select ob2.cmid,
c.name as className,
n.name as objectName
from [dbo].[CMOBJPROPS2] p
inner join CMOBJPROPS26 o on p.cmid=o.cmid
inner join CMOBJECTS ob on ob.cmid=o.cmid
inner join CMOBJECTS ob2 on ob.pcmid=ob2.cmid
inner join CMOBJNAMES n on n.cmid=ob2.cmid
inner join CMCLASSES c on ob2.classid=c.classid

More Information:

https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=mobile-enable-disable-scheduling

https://community.ibm.com/community/user/businessanalytics/communities/community-home/digestviewer/viewthread?GroupId=3061&MessageKey=439d5b09-46a1-4fe1-988f-e468bb5c2254&CommunityKey=6b10df83-0b3c-4f92-8b1f-1fd80d0e7e58&tab=digestviewer