An IMS database typically contains operational data for high-volume transactional workloads. These workloads include finance, insurance, and retail applications that contain a wealth of valuable data about customers, interactions, and business trends. In a typical business intelligence scenario, the operational data must be warehoused and pre-processed before it is accessible to the IBM Cognos Business Intelligence (BI) report author. However, relying only on warehoused data adds constraints to the flexibility of a business reporting application. Because the report author has access only to the warehoused data and not the online transactional system, the most up-to-date detail information is not available.
IBM Cognos BI 10.2 offers a data connection type for direct connections to IMS databases. With direct access to the source database, you can create drill-to-detail reporting applications that leverage IMS data. This article describes best practices for configuring the IBM Cognos BI server, preparing the target IMS system for reporting applications, and creating reports.
This article is written for IBM Cognos BI 10.2 and IMS Version 12.
The IMS system supplying data for the IBM Cognos BI server must have the following features enabled:
- IMS Catalog
- IMS Common Service Layer (CSL)
- IMS Open Database Manager (ODBM)
- IMS Connect
Service updates for the following IMS APARs are required:
Scenario overview and background information
A typical IMS application for insurance processing might contain information such as the policyholder’s name, the policy number, claim data, geographical information, and reference numbers to other data.
The report author treats the IMS data source as they would any other relational data source. In some cases the underlying hierarchical data structures might impose limitations on the types of queries that are supported and how some queries are executed.
In general, the report author does not need to know the specifics of the underlying IMS data structures or how they are presented via the JDBC interface. However, understanding the hierarchical structure of the target IMS database can help you design more efficient reports and queries.
An IMS database is composed of segments that are connected by hierarchical relationships. Each segment contains one or more fields that contain data. IMS database segments are presented as tables, and fields are presented as columns. The hierarchical relationships between IMS database segments are modeled as foreign and primary key relationships between tables.
The IMS Universal JDBC driver presents the underlying hierarchic IMS databases as relational data sources via the JDBC APIs. The JDBC driver also performs query translation on behalf of the IBM Cognos BI server.
To read more about how IMS hierarchical data structures are mapped to the relational database model, see the section titled Comparison of hierarchical and relational databases in the document IMS Version 12 Application Programming.
Configuring IBM Cognos BI for IMS data sources
Configuring JDBC connection parameters
The IMS Universal JDBC driver supports a number of standard connection parameters that control how data is retrieved from IMS. For IBM Cognos BI reporting, it is important that the driver return the full data set for a query. It is recommended that the maxRows and fetchSize parameters be left at their default values (0) so that the entire set of results are returned for a query.
Framework Manager models
An IBM Cognos BI Framework Manager model can contain query subjects that access data from one or more IMS databases. A model consists of a set of database query subjects (db-query subjects) that map to the IMS tables which can be used in query subjects which represent logical/business entities.
A db-query subject is normally created by importing metadata returned by the IMS Universal JDBC driver. A db-query subject contains an abstract logical query that projects the columns of the table, or optionally, a hand-crafted SQL statement.
As users run reports and perform ad-hoc queries, the query engine in the IBM Cognos BI server uses the metadata in the Framework Manager model and knowledge of the IMS Universal JDBC driver SQL syntax to dynamically construct SQL statements.
While the IBM Cognos BI query engine is aware of the SQL syntax supported by the IMS Universal JDBC driver, there are restrictions imposed by the IMS Universal JDBC driver related to the context of an expression or column references which cannot be detected and may cause parsing errors. These scenarios can be avoided by modelling db-query subjects manually.
A db-query subject can be defined using hand-crafted SQL which is defined as a pass-through SQL statement as shown in Figure 1. Using the pass-through approach ensures that the SQL syntax passed to the IMS Universal JDBC driver is supported in all cases. Figure 1 shows the SQL Settings panel for the Query Subject Definition window in Framework Manager, where you can set the pass-through SQL type.
Figure 1: Selecting Pass-Through as the SQL Type for a query subject in Framework Manager
You can convert an imported db-query subject to use Pass-Through SQL in two ways:
- By editing the imported db-query subject and entering a new statement.
- By saving the imported db-query subject, testing the statement generated by the query engine (on the Query Information tab), copying the native SQL statement, and pasting the statement as a db-query SQL definition.
Additional metadata including calculated columns and filters can be defined in query subjects that use the db-query subjects which will be computed by the query engine in the IBM Cognos BI server.
Designing SQL queries for IMS databases
You can reduce the size of the query result set that the IMS Universal JDBC driver returns to the IBM Cognos BI Server by using filters (SQL predicates) that are supported by the IMS Universal JDBC driver. The following examples use the Great Outdoors Sales (GOSALES) sample database to illustrate IMS query syntax rules. In these examples, the GOSALES database is treated as if it was an IMS data source with an underlying hierarchical structure. A subset of the example database structure is shown below in Figure 2. The GOSALES, H_COUNTRY, and H_BRANCH tables are shown as hierarchical database segments. The GOSALES segment is the root, with COUNTRY and H_COUNTRY as children. H_COUNTRY has H_BRANCH as a child. The GOSALES segment contains the ROOTKEY and TABTYPE fields and is 20 bytes. COUNTRY and H_COUNTRY are both 357 bytes long and contain identical fields: GS_ROOTKEY, COUNTRY_CODE, LL, COUNTRY_EN, FLAG_IMAGE, SALES_REGION_CODE, ISO_THREE_LETTER_CODE, ISO_TWO_LETTER_CODE, ISO_THREE_DIGIT_CODE, and EURO_IN_USE_SINCE. The H_BRANCH table is 1572 bytes long and contains the GS_ROOTKEY, HCTRY_COUNTRY_CODE, BRANCH_CODE, COUNTRY_EN, ADDRESS1, ADDRESS1_MB, ADDRESS2, ADDRESS2_MB, CITY, CITY_MB, PROV_STATE, PROV_STATE_MB, POSTAL_ZONE, COUNTRY_CODE, ORGANIZATION_CODE, and WAREHOUSE_BRANCH_CODE fields.
Figure 2 – Sample hierarchical database with two identical child tables off the root table and a branch table off one of the child tables
For more information about the sample database, see section titled The Sample Outdoors Company in the “IBM Cognos Business Intelligence Administration and Security Guide” found in the IBM Cognos 10.2 BI Information Center.
Queries using inner joins
Inner join operations are supported by the IMS Universal JDBC driver for tables with a primary and foreign key relationship where the predicate is expressed on the columns that participate in the foreign key relationship. The following example is supported because the H_BRANCH table has a foreign key (HCTRY_COUNTRY_CODE) relationship with the H_COUNTRY table. This relationship exists because the H_COUNTRY and H_BRANCH segments lie on the same hierarchical database path in the underlying IMS database.
SELECT * FROM H_BRANCH INNER JOIN H_COUNTRY ON H_BRANCH.HCTRY_COUNTRY_CODE=H_COUNTRY.COUNTRY_CODE
An attempt to define a join between two tables without appropriate primary and foreign key relationships is not supported, as in the following example:
SELECT * FROM COUNTRY INNER JOIN H_COUNTRY ON COUNTRY.COUNTRY_CODE=H_COUNTRY.COUNTRY_CODE
Sending a query with an unsupported inner join will result in an error from the IMS Universal JDBC driver that identifies the cause of the problem:
java.sql.SQLException: The tables H_COUNTRY and COUNTRY specified in the query cannot be joined together. They are not along the same hierarchic path in the database.
Queries using math functions or aggregates
Math and aggregate functions are supported for numeric and string literals that do not use nested expressions. Both of the following queries are supported:
SELECT 1 + SUM (COUNTRY_CODE) FROM COUNTRY SELECT SUM (SIN (COUNTRY_CODE+2)) FROM COUNTRY
However, the following example is not supported because the expression 1 + SUM(COUNTRY_CODE) is nested inside the expression -():
SELECT - (1 + SUM(COUNTRY_CODE)) FROM COUNTRY
The following example is not supported because the expression (2 * 5) is nested inside the expression SIN (COUNTRY+()).
SELECT SUM( SIN(COUNTRY + (2 * 5 ))) FROM COUNTRY
Unsupported nested expressions cause the following type of error in the IMS Universal JDBC driver:
java.sql.SQLException: Encountered " "(" "( "" at line 1, column 23.
Queries using BETWEEN and IN
BETWEEN and IN are supported when used with literals:
SELECT * FROM COUNTRY WHERE COUNTRY_CODE BETWEEN 1 AND 5 SELECT * FROM COUNTRY WHERE COUNTRY_CODE IN (1, 3, 5)
However, they are not supported with expressions:
SELECT * FROM COUNTRY WHERE COUNTRY_CODE BETWEEN 1 + 1 AND 5 SELECT * FROM COUNTRY WHERE COUNTRY_CODE IN ( 1 + 1, 3, 5)
The error for an unsupported expression in a query with BETWEEN or IN varies, depending on the expression that was used in the query. For the second unsupported example, the following error message is thrown:
java.sql.SQLException: Encountered " "+" "+ "" at line 1, column 44
Expressions in the projection list
Calls using expressions exclusively in the projection list are supported:
SELECT COUNTRY_CODE + 1 FROM COUNTRY WHERE COUNTRY_CODE > 0
Expressions are not supported in the predicate (WHERE clause):
SELECT COUNTRY_CODE FROM COUNTRY WHERE COUNTRY_CODE + 1 > 0
The error messages for this scenario are similar to the previous case. The exact content of the error message depends on the expression that caused the error.
Using keywords that are not supported by the IMS Universal JDBC driver
SQL syntax that is not directly supported by the IMS Universal JDBC driver can be used in Cognos SQL mode. However, there is a direct performance cost to using unsupported keywords. The following example statement does not work in Pass-Through SQL mode because the IMS Universal JDBC driver does not support the syntax, but it will execute in Cognos SQL mode:
SELECT STDDEV(EURO_IN_USE_SINCE) FROM COUNTRY
The IMS database interpretation layer does not support the STDDEV keyword. Instead, IBM Cognos BI server interprets the syntax and sends the following query:
SELECT EURO_IN_USE_SINCE FROM COUNTRY
The resulting standard deviation value is calculated by the IBM Cognos BI server from the entire set of returned values. Using keywords that are supported in Cognos SQL mode but not by the IMS Universal JDBC driver will result in large query result sets and additional post-processing workload on the IBM Cognos BI server.
Using IBM Cognos BI macros and prompts with IMS data
You can use the macro facility of the IBM Cognos BI query engine to parameterize SQL statements for greater flexibility. IBM Cognos BI macros allow you to add dynamically generated elements to your reporting queries, even in places where the IMS Universal JDBC driver does not support interpreted expressions. For example, the IMS Universal JDBC driver does not support any use of expressions in predicates, but you can use macros to dynamically compute a supported literal value instead.
The following example shows two predicates that exploit IBM Cognos BI macros:
SELECT “BRANCH”.”BRANCH_CODE” AS “BRANCH_CODE”, “BRANCH”.”ADDRESS1” AS “ADDRESS1”, “BRANCH”.”ADDRESS1_MB” AS “ADDRESS1_MB”, “BRANCH”.”ADDRESS2” AS “ADDRESS2”, “BRANCH”.”ADDRESS2_MB” AS ADDRESS2_MB”, “BRANCH”.”CITY” AS “CITY”, “BRANCH”.”CITY_MB” AS “CITY_MB”, “BRANCH”.”PROV_STATE” as “PROV_STATE”, “BRANCH”.”PROV_STATE_MB” as “PROV_STATE_MB”, “BRANCH”.”GOSALES_ROOTKEY” AS “GOSALES_ROOTKEY”, “BRANCH”.”POSTAL_ZONE” AS “POSTAL_ZONE”, “BRANCH”.”COUNTRY_CODE” AS “COUNTRY_CODE”, “BRANCH”.”ORGANIZATION_CODE” as “ORGANIZATION_CODE”, “BRANCH”.”WAREHOUSE_BRANCH_CODE” AS “WAREHOUSE_BRANCH_CODE” FROM “GSPCB”.”BRANCH” “BRANCH” WHERE “BRANCH”.”BRANCH_CODE”=#prompt(‘BranchcodePrompt’,’integer’,’103’)# #prompt(‘CountryCodePrompt’,’integer’,’ ‘,’AND “BRANCH”.”COUNTRY_CODE” =’)#
In the WHERE clause, the BRANCH_CODE value is set either by a prompt to the query, or the default integer value 103. If a COUNTRY_CODE value is passed by a second prompt, a second predicate is included in the WHERE clause with the specified prompt value. For more information about prompts, see the section titled Creating Prompts Using Query Macros in the “IBM Cognos Report Studio User Guide”, available in the IBM Cognos BI 10.2 Information Center.
You can also use macros to incorporate computed values into your queries. For example:
SELECT “ORDER_HEADER”.”ORDER_NUMBER” AS “ORDER_NUMBER”, “ORDER_HEADER”.”RETAILER_NAME” AS “RETAILER_NAME”, “ORDER_HEADER”.”RETAILER_NAME_MB” AS “RETAILER_NAME_MB”, “ORDER_HEADER”.”RETAILER_SITE_CODE” AS “RETAILER_SITE_CODE”, “ORDER_HEADER”.”RETAILER_CONTACT_CODE” AS “RETAILER_CONTACT_CODE”, “ORDER_HEADER”.”SALES_STAFF_CODE” AS “SALES_STAFF_CODE”, “ORDER_HEADER”.”SALES_BRANCH_CODE” AS “SALES_BRANCH_CODE”, “ORDER_HEADER”.”ORDER_DATE” AS “ORDER_DATE”, “ORDER_HEADER”.”GOSALES_ROOTKEY” AS “GOSALES_ROOTKEY”, “ORDER_HEADER”.”ORDER_CLOSE_DATE” AS “ORDER_CLOSE_DATE”, “ORDER_HEADER”.”ORDER_METHOD_CODE” AS “ORDER_METHOD_CODE”, FROM “GSPCB”.”ORDER_HEADER” “ORDER_HEADER” WHERE “ORDER_HEADER”.”ORDER_DATE” > #timestampMask(_add_days( $current_timestamp, -30 ) , ‘yyyymmdd’)# and “ORDER_HEADER”.”ORDER_DATE” < = #timestampMask(#current_timestamp , ‘yyyymmdd’)#
The SQL statement for this db-query subject includes computed values in the WHERE clause. Two integers, representing the current date and 30 days prior to the current date, are computed when the query is run. You can use macros to dynamically modify your queries without introducing additional performance overhead on the target IMS system.
Using master-detail report layouts
A report can contain one or more nested layouts defined as a master-detail relationship. As a report executes, data from a master row can be used as filter values for the associated child queries that are executed. This report pattern can be used to relate the queries in a report without passing an explicit join to the IMS Universal JDBC driver. This report pattern is available to Report Studio report authors.
For more information about master-detail and other report patterns, see the IBM developerWorks article titled IBM Cognos Proven Practices: IBM Cognos BI - Working with Multiple Relational Data Sources.
For more information about optimizing master-detail relationships, see the IBM developerWorks article titled IBM Cognos Proven Practices: Improving IBM Cognos Report Studio Master Detail Report Performance.
Configuring IMS for business intelligence reporting
PSB configuration guidelines
IMS databases are exposed to applications through a Program Specification Block (PSB). A PSB contains database views and permissions in the form of Program Control Blocks (PCBs), which are the equivalent of database schemas in relational databases. The PCB processing options (PROCOPTs) map to the table and column privileges that are exposed to your reporting application via the IMS Universal JDBC driver.
IBM Cognos BI server administrators can override the default data source transaction isolation level that is used for queries against a target database. However, for IMS databases, the transaction isolation level must be set with the PROCOPTs in the PSB for the reporting application.
A best practice is to use only schemas with either uncommitted read access (PCBs with PROCOPTs ‘G’ and ‘O’) or committed read access (PCBs with PROCOPT ‘G’) to the database for reporting applications. Committed reads are appropriate for reports that access data that might be concurrently changed during the execution of the reporting application.
A business reporting application typically expects to use only committed reads. Using committed reads with interactive HTML reports can result in locking contention with other applications that are attempting to update the information. The contention may occur due to the end users pausing for extended periods as they dynamically browse through the paginated output. To minimize the period of time that locks are held, consider producing report outputs that are rendered as PDF, static HTML, or active reports.
For more information about using IMS PROCOPTs to set database access levels, see the section titled Preventing a program from updating data: processing options in the document titled IMS Version 12 Application Programming.
The native data query language for IMS is DLI. The IMS Universal JDBC driver will transform the SQL statements into the DLI equivalent. The SQL predicates (i.e., WHERE clause) are converted into a DLI Segment Search Argument (SSA) which can be more complex than a direct SQL to DLI translation, depending on multiple factors such as the predicate and the IMS database type.
The database administrator can define the amount of main storage reserved in the PSB work pool to hold a copy of the user’s SSA strings during execution of the application program with the SSASIZE parameter of the PSB generation utility. Large SSAs can potentially be generated by reporting queries, so the IMS database administrator should anticipate that a large amount of storage will be required. IMS can dynamically calculate the value of the SSASIZE parameter during Application Control Block generation (ACBGEN), but it is important to note the amount of pool space required by reporting applications with large SSAs so that the IMS system administrator can correctly tune the IMS buffer pools. The database administrator can get this information from the DFS0589I message issued by the ACBGEN utility.
IMS system configuration guidelines
When running a report against a Full Function or a Fast Path (DEDB) IMS Database, the configuration of the IMS buffer pools can affect performance. IMS has tuning parameters to reduce database I/O by adjusting buffer settings so that larger blocks of data can be cached. Tools such as IMS Buffer Pool Analyzer for z/OS can analyze buffer pool environments and recommend changes to the number of buffers in each subpool for improved performance. For Fast Path databases, there is a Virtual Storage Option (VSO) feature that allows data to be mapped into virtual storage or coupling facility structures for faster access, reduced I/O, and reduced locking contention. For more information about buffer pools, see the section titled IMS buffer pool tuning in the document titled IMS Version 12 System Administration.
Program definitions define the schedule and resource requirements for application programs. Program scheduling can be done either serially or in parallel. If the user expects other applications may be attempting to create connections against the PSB while a report is running, it is recommended that the scheduling be set to parallel to allow for multiple simultaneous connections. This value can be set using Dynamic Resource Definition (DRD) through Type-2 commands such as CREATE PGM and UPDATE PGM and the parameter SCHDTYPE. Alternatively, this can be set through the Stage-1 application (APPLCTN) macro parameter SCHDTYP=PARALLEL.
Open Database Manager and IMS Connect configuration guidelines
IMS Connect is a TCP/IP server that is used to manage connections to and from IMS. In the IBM Cognos BI scenario, the connection to IMS is made to the host and port configured in the IMS Connect system definition parameters.
IBM Cognos BI provides the ability to create interactive reports. These reports can leave open ResultSets in an idle state for long periods of time as users browse data interactively. IMS Connect can time out while waiting for a new request by the client. To prevent the report application from losing the connection, set the IMS Connect timeout value (using the PORTTMOT parameter of the ODACCESS statement in the HWSCFGxx member of the IMS.PROCLIB data set) to a larger value than the IBM Cognos BI data source connection timeout value. The default data source connection timeout value is 15 minutes. See Data Source Connection Settings in the “IBM Cognos Business Intelligence Administration and Security Guide”, available in the IBM Cognos 10.2 BI Information Center.
IMS Open Database Manager (ODBM) is a component of the IMS Common Service Layer (CSL). ODBM manages database connections and access requests that use the IMS Universal Drivers, Open Database Access (ODBA), or the CSLDMI interface. ODBM translates database calls from the Distributed Relational Database Architecture (DRDA) format into the DL/I calls that can be interpreted by the IMS database manager.
ODBM optionally supports z/OS Resource Recovery Services (RRS) as the sync point coordinator. RRS is required when the two-phase commit protocol is used, but business intelligence reporting applications do not use two-phase commits. In general, it is recommended that RRS not be enabled (RRS=NO) for the IBM Cognos BI scenario.
Diagnostics for query analysis
Viewing decomposed queries
When Pass-Through SQL mode is not used, IBM Cognos BI attempts to decompose queries into supported syntax before sending them to the IMS Universal JDBC driver. You might want to examine the decomposed syntax to locate performance bottlenecks or determine why a query does not return the expected results. You can view this information in both IBM Cognos BI Framework Manager and Report Studio.
For Framework Manager instructions, see the IBM Software Support Technote at http://www-01.ibm.com/support/docview.wss?uid=swg21345724.
For Report Studio instructions, see the section titled View the SQL for an Entire Report or a Query in the “IBM Cognos Report Studio User Guide”, available in the IBM Cognos BI 10.2 Information Center.
IMS Universal JDBC driver trace
The IMS Universal JDBC driver provides several parameters as part of the DriverManager connection URL that you can use to enable dynamic tracing:
- traceFile - Specifies a file name where the trace data will be logged.
- traceFileAppend - Specifies whether trace entries are appended to an existing log file (true), or overwrite the existing information (false).
- traceDirectory - Specifies the directory path where the trace file is stored.
- traceLevel - A 1-byte flag field that controls the driver tracing configuration. Set this to the value -1 to enable tracing for all of the driver components.
For more information about the DriverManager connection URL, see the section titled Connecting to an IMS database by using the JDBC DriverManager interface in the document titled IMS Version 12 Application Programming.