This article describes the integration between SPSS and
Cognos solutions. The integration with Cognos comes in the form of being able
to directly connect to Cognos as a data source for SPSS modeler and also being
able to export the results to Cognos directly to allow Cognos to report on the
results. The benefits of using Cognos as a source is that your data is all in
one place and its formatted and tidied up suitable for SPSS analytics.
Exporting the results to Cognos has the benefit of being able to report on the
results using the familiar.

Cognos reporting formats and various different types of Cognos reports, such as dashboards and active reports. Below figure showcases one such context diagram and its integration with SPSS:

To implement this is reasonably straight forward and
involves selecting the Cognos source node or the Cognos export node and dragging it into your stream as shown below:

You then have to edit the Cognos node and add the Cognos
server IP address details and login credentials. As shown on Figure below –

You also need to make an ODBC connection to the Cognos Data
warehouse from the SPSS server. This needs to have the same name and details as
the Cognos data source. If these details do not match on the Cognos and the
SPSS server the integration will not work.

Below figure shows the details:

The benefits of using Cognos as a source is that your data
is all in one place and its formatted and tidied up suitable for SPSS
analytics. Exporting the results to Cognos has the benefit of being able to
report on the results using the familiar reporting format and various different
types of Cognos reports that the company uses.

Note: To either
export to Cognos or use Cognos as a data source an ODBC connection to the
Cognos data warehouse must be established as well as the Cognos Server
connection.

Summary:

In this way, we can integrate IBM Cognos BI with IBM SPSS product and get reporting data from prediction and analysis from SPSS.

We are all seeing it ! The databases are getting bigger and bigger and the queries are becoming more and more complex. Under these circumstances, if a particular SQL query does not perform well then it is very critical to find and remove the performance bottleneck as soon as possible. In this blog, I would like to share a systematic step-by-step method to tune the given query.

Prerequisite: We need to know the most important resources required for query processing.

A query is processed in two phases: compilation and execution. During compilation phase, the most important work done is query optimization. The optimizer considers various access plans and chooses the least expensive one for executing the given query. During execution phase, the query is actually executed by following the steps mentioned in the access plan.

For query-tuning purposes, it is very important to know the major resources required for the query compilation and execution phases. The compilation phase will use/access statement-heap, catalog-cache, package-cache, etc. The execution phase will use/access package-cache, sort-heap, shared-sort-heap, lock-list, buffer-pool, etc. The following block-diagram shows these resources. It also shows which resources are part of agent's private memory and which resources are part of database global memory.

Step 1: Monitor the actual resources consumed while DB2 was processing the given query.

By using access plan ( db2exfmt output ), DB2 Snapshots and/or DB2 SQL Monitoring Functions, we can quickly identify the heavily utilized resources for processing our SQL query. Sometimes, it is enough to know which resource was not sufficient for our query. Let us consider a simple example.

Example 1: If the db2exfmt output shows the following message, then it tells us that statement heap was not enough for query compiler: SQLCA : (Warning SQLCA from compile)
SQLCODE 437; Function SQLNO***; Message token '1'; Warning 'None'

In this case, the access plan could be sub-optimal. Increasing statement heap should help here.

However, many times, it is not enough to just know the heavily utilized resource. We also need to find out the context in which, the resource was utilized. This is where access plan analysis helps a lot !

Step 2: Analyze the access plan to get more insights.

The access plan ( generated by using db2exfmt tool ) provides good insights for query-tuning. We can find out the “context” in which , the given resource was heavily consumed. Let us take an example:

Example 2: If the snapshots/monitoring-functions show one or more of the following symptoms, then it means the sort-heap was very heavily consumed:
- sort overflows
- rows written for a SELECT query
- temporary data logical/physical reads
- hash loops
- hash join overflows

In this case, we need to use section-actuals and find out if the input cardinality of SORT/HSJOIN operation was underestimated or not. If underestimated, then we need to provide more statistics to help optimizer choose a better access plan. Otherwise, we can create an index, which may help us avoid the SORT/HSJOIN operations altogether.

In this way, we can find out the reason behind slow running query and then figure out ways to improve the query performance. In this blog, I have given a high level overview of the step-by-step query tuning method. Stay tuned for some concrete examples ! In my next blog, I will share a scenario, where a statistical view helped improve query performance.

Disclaimer: The postings on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.