Contents


IBM Cognos Proven Practices

IBM Cognos 8 Content Store Back up and Clean up

Nature of Document: Troubleshooting; Product(s): IBM Cognos BI 8.4; Area of Interest: Infrastructure

Comments

Content series:

This content is part # of # in the series: IBM Cognos Proven Practices

Stay tuned for additional content in this series.

This content is part of the series:IBM Cognos Proven Practices

Stay tuned for additional content in this series.

Purpose

In this article, learn how to back up and restore the Content Store as a Cognos deployment. Also, learn the steps required to run the database clean up scripts for the Cognos Content store supported databases: IBM DB2, Oracle, Microsoft SQL server 2005 and Apache Derby.

Applicability

The technique and behaviour outlined in this document have been tested on IBM Cognos 8.4.

IBM Cognos Content Store

IBM Cognos 8 Content Store is the central metadata repository for the Cognos server instance. It stores the Cognos models, packages, report templates and specifications, user preferences and datasource connection information. It also contains security namespace information and access control policies on all the Cognos objects.

IBM Cognos 8 includes the IBM Cognos Content Database, an instance of Apache Derby database, as the default content store. Other supported databases as content stores are Microsoft SQL Server, Oracle, IBM DB2, and Sybase.

Content Store Back up as a Cognos deployment

IBM Cognos Content Store can be backed up as a Cognos deployment, in order to migrate, redeploy or replicate Cognos packages and reports to a new Cognos server instance.

To take a back up of IBM Cognos content store, logon to IBM Cognos home page

Launch -> IBM Cognos Administration.

Figure 1: Launch Cognos Administration
Figure 1: Launch Cognos Administration
Figure 1: Launch Cognos Administration

Click on "New Export" button in Content Administration tab.

Figure 2: Create New Export
Figure 2: Create New Export
Figure 2: Create New Export

Specify name of the deployment and choose option "Select the entire Content Store".

Figure 3: Deployment Option
Figure 3: Deployment Option
Figure 3: Deployment Option
Figure 4: Enter the New archive name
Figure 4: Enter the New archive name
Figure 4: Enter the New archive name
Figure 5: Schedule when the export task
Figure 5: Schedule when the export task
Figure 5: Schedule when the export task

Complete the export wizard steps. The new export deployment entry will appear in Content Administration tab. Also, the deployment archive can be found at <Cognos Installation Path>\deployment as shown in Figure 6.

Figure 6: Deployment Archive location
Figure 6: Deployment Archive location
Figure 6: Deployment Archive location

Content Store clean up: Executing database scripts

Some troubleshooting situations may require starting with a new content store configuration data. This is achieved by cleaning up Cognos specific tables in the Content Store database using SQL scripts provided by IBM Cognos 8 for any of the supported databases. This can solve configuration problems that occur due to non-existing security namespaces and broken links to packages and old report content lying in the Content Store. The clean up scripts delete all Cognos Content Store tables from the database, leaving other tables intact.

Consider the scenarios and benefits of cleaning up the Content Store rather than deleting the database itself:

  • In some environments, Cognos administrator might not have rights to delete and create databases in the given database instance. This is possible if the database environment has a large number of users and access has to be controlled to the database instance. If access is given only to work with the given database, using Content Store clean up scripts is the right option.
  • Databases allocated to Content Store might be shared with other system or application data. In such cases, deleting and recreating the entire database is not an option.
  • In a distributed Cognos deployment, there can be multiple installations of Content Manager: one active CM and one or more stand by CM instances. In such an environment, if a new Content Store database is created (with a new name), the connection settings will have to be updated on all instances of Content Manager.

Before execution of clean up scripts, back up Cognos Content (packages/reports) or the entire Content Store as required, as all content will be deleted after clean up.

The clean scripts need to be executed from the server node on which Content Manager is installed. Following are the steps to clean up the content stores on the various supported databases as Content Store.

Content Store clean up scripts are located at:

${cognosInstallLocation}\configuration\schemas\content\

E.g. C:\Program Files\cognos8\configuration\schemas\content\db2

IBM DB2

In order to run clear the DB2 content store, the database needs to be added to the DB2 catalog on the server node from where the clean up scripts will be executed.

Catalog DB2 database

  • Create the instance alias and catalog the remote DB2 instance using the following command:

    db2 "catalog tcpip node remoteinst remote remotehost server 50000"
    // ‘remote’ followed by ip/hostname address of the server and ‘server’ keyword followed by instance name or port number.

    e.g.:
    # db2 catalog tcpip node bluehost remote remotehost.com server 60000
    # db2 "terminate"
  • Create the database alias and catalog a remote DB2 database using the following command:

    db2 "catalog database testdb as remotedb at node bluehost"

    e.g.:
    # db2 "catalog database SALES as SALESDB at node svlxcog8"
    # db2 "terminate"

Executing DB2 clean up scripts

Navigate to the script location and execute the DB2 script against the Content Store database.

# cd ${cognosInstallLocation}\configuration\schemas\content\db2
# db2 connect to SALESDB user bob using cognos1 
# db2 –tvf dbClean_db2.sql

This will clear /drop all the Content store related tables from the database. The tables will be recreated when the Content Manager Service is restarted.

To clean up the log database,

cd ${cognosInstallLocation} \configuration\schemas\logging\db2
# db2 –tvf LS_dbClean_db2.sql

To clean up the notification database,

# cd {cognosInstallLocation}\configuration\schemas\delivery\db2
# db2 –tvf NC_DROP_DB2.sql

IBM Cognos Content Database (Apache Derby)

IBM Cognos Content Database is an instance of Apache Derby, which is the default content store, bundled in a full IBM Cognos installation. To clean up this content store database:

  • Navigate to the directory where the embedded Derby instance is located:
    # cd ${cognosInstallLocation}\derby10.1.2.1\bin
  • To run the clean ups scripts, use ij, the interactive SQL scripting tool, present as ij.bat (Windows) or ij.sh (Unix).Modify this utility to suit the Cognos environment.
    Change the hostname, port, user and password values present by default in this file.
    e.g.:
    set IJ_HOST=localhost
    set IJ_PORT=1529
    set IJ_USER=cognos
    set IJ_PASSWORD=cognos
  • Execute the ij scriptfile:/C:/Documents%20and%20Settings/harringd/Desktop/WIP%20Docs/Templates/CP2-Symphony-Doc-Template.ott
  • CP2-Symphony-Doc-Template
    #ij.bat
    Or
    #ij.sh
  • Connect to the Content Store database. The name, by default is ‘cm’, found in the Cognos Configuration -> Content Manager section.
     # ij>connect ‘cm’;
  • Execute the dbClean_derby.sql. Replace ${cognosLocation} with the actual path.
     # ij>run ‘${cognosLocation}\configuration\schemas\content\derby\dbClean_derby.sql';

These steps will clear the content store tables from the ‘cm’ database, which will be recreated once the Content Manager Service is restarted.

  • Similarly, to clean up the log store, connect to log database and execute the SQL script.
    # ij>connect ‘logdb’;
    # ij>run 
    ‘${cognosLocation}\configuration\schemas\logging\derby\LS_dbClean_derby.sql';
  • To clean up the notification store, in case it is separate from the Content Store(distributed install)
    # ij>connect ‘logdb’;
    # ij>run 
    ‘${cognosLocation}\configuration\schemas\delivery\derby\LS NC_DROP_Derby.sql';

Oracle

In order to clear the Oracle content store, install Oracle client to connect to the remote Oracle database. The remote database needs to be added in tnsnames.ora file.

e.g. tnsnames.ora file location and database entry.

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora

COGNOSCS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 9.XXX.XXX.XXX)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = cognoscs)
    )
  )
  • To delete tables from the content store
    #  cd   ${cognosInstallLocation}\configuration\schemas\content\oracle
    # sqlplus username@[Alias name for remote database]
    Enter the password
    SQL> @dbClean_oracle.sql
  • To delete tables from the log database
    #cd ${cognosInstallLocation} \configuration\schemas\logging\oracle
    # sqlplus username@[ Alias name for remote database]
    Enter the password
    SQL>@LS_dbClean_oracle.sql
  • To delete tables from the notification database
    # cd {cognosInstallLocation}\configuration\schemas\delivery\oracle
    # sqlplus username@[Alias name for remote database]
    Enter the password
    SQL> @NC_DROP_ORA.sql

Microsoft SQL Server

For Microsoft SQL ,clean scripts are executed through osql utility.

  • To delete tables from the content store
    #cd ${cognosInstallLocation} \configuration\schemas\content\sqlserver
        #osql –U <user> –P <password> -d <database> -i dbClean_mssqlserver.sql
  • To delete tables from the log database
    #cd ${cognosInstallLocation} \configuration\schemas\logging\sqlserver
        #osql –U <user> –P <password> -d <database> -i LS_dbClean_mssql.sql
  • To delete tables from the notification database
       #cd ${cognosInstallLocation}\configuration\schemas\delivery\sqlserver
        #osql –U <user> –P <password> -d <database> -i NC_DROP_MS.sql

Conclusion

This article describes the steps to back up the Cognos Content Store, to store Cognos packages, reports, datasources and other Cognos objects as a deployment archive. Also, for troubleshooting scenarios, the steps for using Cognos database clean up scripts are described for most of the supported databases.


Downloadable resources


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Data and analytics, Information Management
ArticleID=492239
ArticleTitle=IBM Cognos Proven Practices: IBM Cognos 8 Content Store Back up and Clean up
publish-date=05282010