Installing the FTM NACHA sample reports

To use the sample reports, do the following steps.
  1. Ensure that IBM® Cognos® is installed. For more information about the supported version, see FTM Hardware and Software requirements.
  2. Create a database to use as the content store for Cognos. This database is separate from the FTM database.
  3. The reports use a data source to access the database where the data for the report is located. Use the Cognos Administration portal to create the following data sources for the reports.
    • ftmdb_source
    For more information about creating the data sources, see Creating the Cognos data sources.
  4. Install the UDF functions for time zone conversion. For more information, see Install the UDF functions for time zone conversion.
  5. If you want to run the Cognos reports as a Services Framework task, you need to provide Red Hat® OpenShift® with user credentials for your Cognos server. If you have more than one server, you need to provide the credentials for each one that your Services Framework report tasks need to access. For more information, see Configure Red Hat OpenShift with your report user credentials.
  6. Use the Cognos Administration portal to import the report archive files. The files that you need are provided in the FTM artifacts pod and must be downloaded from the pod. For more information about getting files from the artifacts container for your offering, see Getting the files from the artifacts container for your FTM offering. For more information, see Getting the files from the artifacts container for your FTM offering.
  7. The reports can be run in Cognos Connection portal, Cognos Report Studio, or both. They can also be run as a Services Framework task. To run the reports as a Services Framework task, the Cognos reports task must be added to Services Framework. For more information about adding Cognos reports to Services Framework, see IBM Cognos reports task.

Creating the Cognos data sources

Depending on your requirements, you might want all of the reports to run with data from the same database. Or, you might want some reports to run with data from one database and some reports to run with data from a different database. The databases that are usually used for the Common Services reports are shown in the following list.
  • FTM database
  • Reporting and analytics database
The Cognos reports have a data source name that is embedded in their report definitions. You can configure the data source to connect to either a single database or to multiple databases as described in the following list.
Single database
Create the data source with a single database connection string parameter that connects to the one database that you want all of the reports to use. All of the reports that use this data source retrieve data from that database. For example, when the database connection string parameter for this data source connects to the FTM database, all of the reports run with data from the FTM database.
Multiple databases
To create a data source that connects to multiple databases, configure more than one database connection string parameter for the data source. For example, configure one database connection string parameter to connect to the FTM database and a second one to connect to the reporting and analytics database. When a report that uses this data source is run, you are prompted to choose which database connection to use. You must also provide other report-specific parameters.
Depending on your database host, you might also need to do the following configuration steps to be able to run the Cognos sample reports on SSL-enabled Red Hat OpenShift Container Platform.
  1. Export the certificate from the Db2® environment to the Cognos server. For more information, see https://www.ibm.com/docs/cognos-analytics/11.1.0?topic=communications-enabling-ssl-db2-informix-databases.
  2. Create a JDBC connection to this Db2 database with sslConnection set to true.
  3. In the Framework Manager model, convert the sample reports package from compatible query mode to dynamic query mode. For more information, see https://www.ibm.com/docs/cognos-analytics/11.1.0?topic=udqm-enabling-framework-manager-models-packages-use-dynamic-query-mode.
  4. Republish the existing Framework Manager model. For more information, see https://www.ibm.com/docs/cognos-analytics/11.1.0?topic=packages-publishing.
Make sure that you use the Cognos documentation for the version that you installed.

The reports use the ftmdb_source data source, unless otherwise specified. The following table, or tables, show the parameters for the data sources that are used by the reports.

Table 1. Data source parameters for pfs_digital_pmts_source
Parameter name Value or description
Name pfs_digital_pmts_source
Data Source Type Set the data source type to DQM by selecting the Configure JDBC Connection checkbox .
Isolation Level Select the isolation level for the data source.
Database Typically, this data source is connected to the FTM database (FTMDB).
Db2 connect string jdbc:db2://localhost:50000/FTMDB
Db2 Connection Parameters Specify any database connection parameters. Test the connection to verify that the configuration works.
Db2 JDBC Connection Parameters Specify any JDBC database connection parameters. Test the connection to verify that the configuration works.
Open Session Commands 
<commandBlock>
  <commands>
    <sqlCommand>
      <sql>SET SCHEMA FTMPUB</sql>
    </sqlCommand>
    <sqlCommand>
      <sql>SET PATH = FTMPUB,FTM</sql>
    </sqlCommand>
  </commands>
</commandBlock>

Install the UDF functions for time zone conversion

All Cognos reports and dashboards use time zone conversions to report data in the operational time zone. This conversion is done by using Java™ user-defined functions (UDF) that are registered with your Db2 instance. These user-defined functions are provided in the base_udf_utilities.jar file. The files that you need are provided in the FTM artifacts pod and must be downloaded from the pod. For more information about getting files from the artifacts container for your offering, see Getting the files from the artifacts container for your FTM offering.

If you are using the Db2 Universal Container (Db2U) for your FTM database, the following steps are a guideline for how you can register the UDF JAR file with your Db2U instance. You can use the Red Hat OpenShift Container Platform or the Red Hat OpenShift Container Platform command-line interface to access your Red Hat OpenShift cluster.
  1. Get the JAR file from the artifacts container for your FTM offering. For more information, see Getting the files from the artifacts container for your FTM offering..
  2. Use the Red Hat OpenShift Container Platform to copy the JAR file to the Db2U container that has the FTM database. For example, you can copy it to the /tmp directory of the c-db2u-ftm-db-db2u-0 pod. Verify that the file was copied.
  3. Edit the class path variable of the Db2U pod that is running the FTM database. For example, 
    CLASSPATH=$CLASSPATH:/mnt/blumeta0/home/db2inst1/sqllib/function/jar
  4. Configure the Cognos Db2 functions by using the steps in the DBInstallInstructionsDB2_LUW.txt file that you got from the artifacts container.

Configure Red Hat OpenShift with your report user credentials

Before you configure a report task, the user authentication credentials to use to access your Cognos server for the report must be added to Red Hat OpenShift. These credential values are provided in the following parameters that must be added as key and value pairs to the ftm-serviceengine-tasks-secret secret in Red Hat OpenShift.
  • Namespace ID, which is the ID of the Cognos authentication namespace.
  • Username used to log on to the Cognos server.
  • User password used to log on to the Cognos server.
  • Alternative credentials used to log on to the Cognos server. Surround the credentials in the XML elements that are needed by the Cognos authentication provider that is being used. This parameter is required only when Cognos is configured to use an authentication provider that expects credentials other than the combination of namespace, username, and password.
If these values were already added to the secret, you do not need to add them again.
To add parameters to the Red Hat OpenShift secret, do the following steps:
  1. Determine what names you want to use for the keys for the report parameters. You can use any word with alphanumeric characters for a key. For example, you might use the following key and value pairs for the report parameters. 
    cognosnamespaceid=CognosNS
cognosusername=Administrator
cognosuserpass=mypass
cognosaltcredentials=<ID>myuser</ID><password>ADFJWF230FJWWEFLJW#(@#*</password><SSO_PROVIDER>http://example.com</SSO_PROVIDER>
    If you have more than one report server that you are using to run the Services Framework report tasks, you need a unique set of user credential keys for each server. For example, you can add an identifier as part the key names, such as cognosnamespaceidserv1 and cognosnamespaceidserv2.
  2. Use the Red Hat OpenShift Container Platform to open the ftm-serviceengine-tasks-secret secret.
  3. Edit the secret to add your key and value pairs in the Value field for the key secret.properties. The format for the key and value pairs is key=value.
  4. The key names that you added to the secret must be specified in the configuration for the report tasks that use Cognos.