IBM Support

Insight ETL job is not pulling data from data warehouse when using Distributed ETL server

Troubleshooting


Problem

Inconsistencies in the ETL (Extract Transform, Load) data load results, when executing from a schedule or manually, against a publish ETL jobs from an IBM Cognos Business Intelligence (BI) reporting server. Job logs report all good, but data is not loaded into the data warehouse repository.

Symptom

Review of log file reveals that hasmore or more condition node in the ETL jobs reports a succeeded (FALSE) execution. Leaving return values of these conditions to report no data to load, when in fact there is new data in the targeted repository to load into the data warehouse.


    Note: These conditions will be exposed early in the log file, At the end of the log file the condition node becomes a validation test to find if more data needs loading.


    Data Manager v10.1.6235.143 -- (C) Copyright IBM Corp. 1999, 2012
    jobstream -- start run on aserver.example.ibm.com (25-Nov-2014 19:05:11)

    Run context:
    JobStream 'RQM4.0.3'
    JobStream 'RQM_ODS4.0.3_DeltaLoad'
    JobStream 'RQM_ODS4.0.3'

    [PROGRESS - 19:05:11] JobStream 'RQM_ODS4.0.3'; starting
    [PROGRESS - 19:05:11] Procedure Node 35 'init'; executing (inline)
    [PROGRESS - 19:05:13] Procedure Node 35 'init'; succeeded
    [PROGRESS - 19:05:13] Condition Node 37 'hasMore'; executing (inline)
    [PROGRESS - 19:05:13] Condition Node 37 'hasMore'; succeeded (FALSE)

The Build or Job stream completes in a much shorter time frame than would a normal ETL collection run.


Inconsistent location of the log file: At times the job logs from the ETL collection are on the reporting BI server, at other times they are on the ETL server.

Cause

Cognos Configuration > Data movement service enabled? value is set to true on both the reporting server and the ETL server. This allows the reporting server to act as a ETL server, when in fact the reporting server is not configured to run the jobs.

When configuring the reporting server, the Insight configuration artifact details like the ODBC CLM XDC files and other configuration components are left out. This leads the ETL job into a situation where it cannot Identify the collection resource groups. When the task is being executed from the reporting server, the hasmore or more condition in the ETL job returns an empty resource group, the job determines there is no other collection and moves on to the next step of the collection task. This is a failure condition the ETL task cannot capture, since it is also possible there is actually no data to load.

Eventually this lead to an complete ETL collection without any runtime failures, and the job terminates successfully, but does not actually load new data to the data warehouse. You will also notice that the total execution time of the Collection task is very fast compared to a normal execution.

Environment

This can occur on any operating system when using the Insight distribute ETL server scenario.

Diagnosing The Problem

Review the following two conditions:

  • Monitor the execution time of the ETL if it completes faster then expected.
  • Review Job stream logs: search for the Log files that contain the hasmore and more condition near the top files. Based on the target source CLM repository user usage, determine if in fact it should have capture data to load.

Resolving The Problem

Procedure:

Open the Cognos configuration tool: $RRDI_HOME/cognos/bin64/cogconfig.sh(bat) on the reporting server RRDI software home.


    Note: On a Linux operating system make sure the export DISPLAY environment variable correctly setup and make sure you have XWindow properly configure. Some environments may require "xhosts + server.name.ibm.com" be executed against the DISPLAY values to load Host into the XWin security policies. Alternatively use VNC or remote desktop software and use the default desktop tool for Linux (Gnome or KDE).

  1. Expand the Environment node on the right side panel.

  2. Traverse to and select the IBM Cognos service node.

  3. On the left panel find the property Data movement service enable? and update the value to FALSE.




  4. Save the configuration change, using the disk Icon, top right, CTRL+S or the menu, File -> Save.

  5. Recycle the IBM WebSphere Application Server profile that supports the IBM Cognos,ear file.

Leverage the Jazz Community

The Jazz based products have an active community that can provide you with additional resources. Browse and contribute to the User forums, contribute to the Team Blog and review the Team wiki.
Refer to technote 1319600 for details and links.

[{"Product":{"code":"SSRL5J","label":"Rational Insight"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"RRDI","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"1.1.1.1;1.1.1.2;1.1.1.3;1.1.1.4;1.1.1.5;1.1.1.6","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
16 June 2018

UID

swg21696844

Page Feedback