IBM Cognos Proven Practices: Understanding the IBM Cognos Adaptive Warehouse Log files

Nature of Document: Troubleshooting; Product(s): IBM Cognos Adaptive Warehouse for IBM Cognos Business Intelligence Server versions 8.4 and 10.1; Area of Interest: Troubleshooting

This document describes the numerous log files that are produced by IBM Cognos Adaptive Warehouse, detailing when these files are produced the information they contain.

Share:

Ning Liu, Software Developer, IBM

Ning Liu holds a Masters Degree in Computer Science from the National University of Defence Technology in China and has been an IBM employee for the past 7 years, 6 of which he has spent as a developer on the IBM Cognos Adaptive Warehouse project. Prior to that he spent 10 years working on the OLTP banking system in China. His areas of expertise are in data warehousing, data modelling and business intelligence . He is currently working on an interactive visualisation tool within the Business Analytics department of IBM at the IBM lab in Staines, United Kingdom.



Alban Tsui, Senior Software Developer, Business Analytics, IBM

Alban Tsui holds a Doctorate of Philosophy in Computing from Imperial College and a Masters Degree in Information Processing and Neural Networks from Kings College, University of London, He has been an IBM employee for the past 12 years, 7 of which he has spent as a developer on the Cognos Adaptive Warehouse project. He is one of the original inventors of IBM Cognos Adaptive Applications Framework, which includes IBM Cognos Adaptive Warehouse, as well the inventor of numerous other products, including IBM Cognos Metrics Manager. His experience in software development is in C, C++, Java, JavaScript, Web applications, ETL (Extract, Transform and Load) design and data warehousing. He has a handful of academic papers published on topics ranging from Neural Networks to Chaos Control and Data Modelling and is currently a senior developer working within the Business Analytics department of IBM at the IBM lab in Staines, United Kingdom.



Peter Broadbent, Software Engineer - Interactive Discovery, IBM

Peter Broadbent holds a Masters Degree in Enterprise Systems Development from Brunel University in London and has been an IBM employee for the past 5 years, joining the Cognos Adaptive Warehouse project when he joined IBM. His areas of expertise include business intelligence, business process management and he enjoys writing software in Java and JavaScript. He is currently working on an interactive visualisation tool within the Business Analytics department of IBM at the IBM lab in Staines, United Kingdom.



06 December 2011

Also available in Chinese

Introduction

Purpose

This document describes the numerous log files that are produced by IBM Cognos Adaptive Warehouse, detailing when these files are produced and the information they contain.

This may be useful if you are troubleshooting your install of IBM Cognos Adaptive Warehouse and wish to obtain detailed information about an error you have received or simply want to learn how to understand the log files.

Applicability

The information presented here is applicable to IBM Cognos Adaptive Warehouse for IBM Business Intelligence Server 8.4 and IBM Cognos Adaptive Warehouse for IBM Business Intelligence Server 10.1.

Exclusions and Exceptions

If you are using an earlier version of IBM Cognos Adaptive Warehouse, there may be log files that are not applicable to the version other than those detailed above.


Locating And Enabling Additional Log Files

All the IBM Cognos Adaptive Warehouse log files are placed in the installation directory. In a default installation, these log files will be placed under the subdirectory aaf\logs using the the location specified in IBM Cognos Configuration under Environment > Data files location as the base (see Illustration 1). By default, the location for the log files will be [Install Directory]\data\aaf\logs and will be referred to in this document as the [root_log_directory].

Illustration 1: Data files location in IBM Cognos Adaptive Warehouse configuration
Illustration 1: Data files location in IBM Cognos Adaptive Warehouse configuration

By default, IBM Cognos Adaptive Warehouse does not enable all available log files. To do this, follow these steps:

  1. Change the file properties of the file [Install Directory]\configuration\aaf.ini and remove the read-only file attribute.
  2. Edit the file aaf.ini in a text editor and make the following changes:
    • Locate the text <Section Name="LogControl"> and change <FileLogLevel>INFO</FileLogLevel> to <FileLogLevel>DEBUG</FileLogLevel>
    • Add the following <Section> element at the end of aaf.ini above the </Sections> tag:
      <Section Name=”DebugInfo”>
      <LogSystemTables>true</LogSystemTables>
      <LogModelXML>true</LogModelXML>
      <LogDDL>true</LogDDL>
      <PackagePersistence>true</PackagePersistence>
      </Section>
  3. Restart IBM Cognos Adaptive Warehouse for these changes to take effect.

NOTE : Enabling DEBUG level logging has a substantial negative impact on performance and should only be used when necessary.


Identifying The IBM Cognos Adaptive Warehouse Log Files

There are numerous log files produced by IBM Cognos Adaptive Warehouse, some of which are only created by performing certain sequence of tasks and having the relevant logs enabled.

The IBM Cognos Adaptive Warehouse application log file is always present in the [root_log_directory] and follows a naming schema of aw-[timestamp].log (for example, aw-20101208181201218.log). The following illustration displays log files under the [root_log_directory].

Illustration 2: List of IBM Cognos Adaptive Warehouse log files under the root log directory
Illustration 2: List of IBM Cognos Adaptive Warehouse log files under the root log directory

The ETL log files are explained in detail later in this document.

Table 1 presents the list of log files (excluding the ETL log files) that will be created when the additional log options are switched on. Many of these logs are produced by interaction between the IBM Cognos Adaptive Warehouse engine and the IBM Cognos Adaptive Warehouse User Interface (UI). These logs will not be produced on a system where the IBM Cognos Adaptive Warehouse command line client is used.

Table 1 – Log files and details

Log FileDescriptionEnabled by default?File produced by?
aw-[timestamp].logThe IBM Cognos Adaptive Warehouse application log file. This lists all events.YesDefault
_PostReadFromDBFMModel_[timestamp].xmlIBM Cognos Framework Manager model that IBM Cognos Adaptive Warehouse reads from the target databaseNoOpening a project
_ReadUIModelFromXML_ui_[timestamp].xmlUI warehouse model passed to the Engine by UINoUI->Engine
_ReadUIModelFromXML_whproject_[timestamp].xmlEngine warehouse model passed to Engine by UINoUI->Engine
_ReadFMModelFromXML_xml_[timestamp].xmlIBM Cognos Framework Manager model passed to Engine by UINoUI->Engine
GetUIXML_WHProject[timestamp].xmlUI warehouse XML model passed to UI by EngineNoEngine->UI
WriteFMModelToByteBuffer_[timestamp].xmlIBM Cognos Framework Manager model passed to UI by EngineNoEngine->UI
systemtables_ddlscript-[timestamp].sqlHolds the SQL statements to create the database tables for a new IBM Cognos Adaptive Warehouse projectNoCreating a new project
systemtables_ddlspec-[timestamp].xmlHolds an XML representation of the systemtables_ddlscript log mentioned aboveNoCreating a new project
systemtables_dmlscript-[timestamp].sqlHolds the SQL statements to insert the project data into the database when a new project is createdNoCreating a new project
systemtables_dmlseedscript-[timestamp].sqlHolds the SQL statements to insert seed data into the databaseNoCreating a new project
DDLScript-[timestamp].sqlHolds the DDL and DML statements to perform table managementNoRunning ETL

The log files listed in Table 2 are only created when importing a content library. All of these log files require the additional configuration setting to enable them and are not enabled by default.

Table 2 Log files related to the Content Library

Log FileDescriptionFile produced by?
_FMProjectToImport_Content_[timestamp].xmlIBM Cognos Framework Manager model that IBM Cognos Adaptive Warehouse reads from content files.[Import Step 1]
_WHProjectToImport_Content_[timestamp].xmlThe warehouse model that IBM Cognos Adaptive Warehouse reads from content files.[Import Step 1]
_FMProjectToImport_BeforeFilter_[timestamp].xmlThe IBM Cognos Framework Manager model that IBM Cognos Adaptive Warehouse produces after upgrading the content files.[Import Step 2]
_WHProjectToImport_BeforeFilter_[timestamp].xmlThe warehouse model that IBM Cognos Adaptive Warehouse produces after upgrading the content files.[Import Step 2]
_FMProjectToImport_AfterFilter_[timestamp].xmlThe IBM Cognos Framework Manager model that IBM Cognos Adaptive Warehouse produces after upgrading the content files. This would list ERP sources and terminologies that have been excluded.[Import Step 3]
_WHProjectToImport_AfterFilter_[timestamp].xmlThe warehouse model that IBM Cognos Adaptive Warehouse produces after upgrading the content files. This would list ERP sources and terminologies that have been excluded.[Import Step 3]
_FMProjectToImport_PreSync_[timestamp].xmlIBM Cognos Framework Manager model that IBM Cognos Adaptive Warehouse produces after renaming objects in content files.[Import Step 4]
_WHProjectToImport_PreSync_[timestamp].xmlThe warehouse model that IBM Cognos Adaptive Warehouse produces after renaming objects in content files.[Import Step 4]
_FMProjectToImport_AfterSync_[timestamp].xmlThe IBM Cognos Framework Manager model that IBM Cognos Adaptive Warehouse produces after synchronizing the target model of the content files being imported.[Import Step 5]
_WHProjectToImport_AfterSync_[timestamp].xmlThe warehouse model IBM Cognos Adaptive Warehouse produces after synchronizing the target model of the content files being imported.[Import Step 5]
_FMProjectToImport_[timestamp].xmlThe IBM Cognos Framework Manager model which would actually be imported by IBM Cognos Adaptive Warehouse.[Import Step 6]
_WHProjectToImport_[timestamp].xmlThe warehouse model which would actually be imported by IBM Cognos Adaptive Warehouse.[Import Step 6]
_WriteUIImportExportTask_[timestamp].xmlThe import or export task IBM Cognos Adaptive Warehouse creates and sends to the UI for the user to confirm.[Import Step 6]
_ReadUIImportExportTask_[timestamp].xmlThe import or export task confirmed by the user that will be run by IBM Cognos Adaptive Warehouse.[Import Step 6]
_PostImportEngineFMModel_[timestamp].xmlIBM Cognos Framework Manager model after importing successfully.[Import Step 7]
_PostImportEngineModel_[timestamp].xmlThe warehouse model after importing successfully.[Import Step 7]

For details on content libraries, please refer to the IBM Cognos Adaptive Warehouse user guide.


Understanding the IBM Cognos Adaptive Warehouse Application Log

The IBM Cognos Adaptive Warehouse application log file is the top level log that details all events that occur when the application is running. A sample of a log record is shown below:

29/03/11 13:11 ERROR Unable to read database schema version. No rows found in PWW_SCHEMA_VERSION table. PWOne pweemanager PWEEManager GetDBSchemaVersion

The components of this sample log record is shown in Table 3.

Table 3 Explanation of Application Log File Entries

Log EntryDescription
29/03/11 13:11The timestamp when this event occurred
ERRORThe level of the log message
Unable to read database schema version. The title of the log message
No rows found in PWW_SCHEMA_VERSION table.A description of the log message
PWOneInternal or additional description messages
pweemanagerInternal
PWEEManagerInternal
GetDBSchemaVersionInternal

The [Internal] sections of the log message are useful to developers when trying to resolve an error that has occurred and do not present useful information to the end user.

There are four levels of log messages - INFO, DEBUG, WARNING and ERROR. Table 4 describes the type of information each level will generate. By default, IBM Cognos Adaptive Warehouse will only log events that are defined as INFO, WARNING and ERROR. As highlighted above in Section 4, by turning on additional log file levels, the DEBUG log messages will also be logged.

Table 4 – Log Message Levels

Log LevelDescription
INFONormal execution of the application.
WARNINGUnexpected events that will most likely not interfere with normal execution of the application. In many cases, the log message will detail the outcome of the event logged, the steps the application took to rectify it and an action that the user may perform to avoid this issue in future.
ERRORUnexpected events that will interfere with normal execution of the application. Depending on the sequence of events the user was performing, this may be or may not be an unrecoverable error.
DEBUGAdditional information that is recorded during normal execution and unexpected events.

Common Log Messages

Listed below are some of the most common log messages and their meaning. For clarity, the timestamps and the internal sections of the log messages have been removed.

INFO DBADAPTER-LOG-0005 Successfully connected to database "BROADBEP_MSSQL_2008_SOTTWESTPORT_AW_COL_2".

This is the first step IBM Cognos Adaptive Warehouse performs when attempting to open or create a new project in the database. It does not denote successful opening of an existing project nor successful creation of a new project.

INFO Install or Upgrade of PWOne tables succeeded

A new project and its associated database tables have been successfully created or a previous version of an IBM Cognos Adaptive Warehouse project has been successfully upgraded.

ERROR DBADAPTER-LOG-0003 Error preparing a sql statement. DBADAPTER-LOG-0004 The SQL statement prepare failed with reason "UDASQLException when preparing statement: UDA-SQL-0196 The table or view "DATABASE_MSSQL_2008_MACHINEX_AW_..dbo.PWW_SCHEMA_VERSION" was not found in the dictionary.

ERROR Unable to read database schema version. No rows found in PWW_SCHEMA_VERSION table.

These two log messages are present when a user attempts to open an IBM Cognos Adaptive Warehouse project from a datasource connection where the underlying database does not contain a project. A new project needs to be created in this datasource if it is to be used to hold an IBM Cognos Adaptive Warehouse project.

INFO EEM-LOG-0304 Saving the PWOne model succeeded. EEM-LOG-0305 Saving the PWOne warehouse model to the target database succeeded. All updated objects have been saved.

This message denotes that the IBM Cognos Adaptive Warehouse project has been successfully saved to the database. This normally occurs after a user has made a change to the model and initiates the save action.

INFO Executing ETL

This denotes the start of the Extract, Transform and Load process.

INFO Create ds log directory: ..\data\aaf\logs\aw-etl\20110329_144458_00

The denotes the name of the log directory that is created for this execution of Extract, Transform and Load.


Understanding The Extract, Transform and Load Log

Overview

The ETL (Extract, Transform and Load) log files are always present and are placed under the directory [root_log_directory]\aw-etl. This directory will be referred to as as the [root_ETL_log_directory]. The top level log file generated by Extract, Transform and Load (ETL) is called etl.log and is located within a timestamped directory under the [root_ETL_log_directory]. (for example, [Install Location]\data\aaf\logs\aw-etl\20110329_144458_00\etl.log). A screenshot is shown in the following illustration.

Illustration 3: The etl.log and other ETL log files under a timestamped directory
Illustration 3: The etl.log and other ETL log files under a timestamped directory

When running ETL, a number of jobsteam and sub-jobstreams are created. These are pieces of work that must be executed by IBM Cognos Data Manager. For further explanation of these terms, please see the IBM Cognos Data Manager user guide.

The etl.log file contains details of the entire execution of ETL which includes the jobstream's status, as well as failures. The jobstream node name will loosely match it's relevant log file when you are looking for detail on a particular jobstream. Each jobstream log may also reference other builds and each such build produces another log file. An example directory containing logs for the Dimension 'Company' would be similar to this:

Job_Company_T_0001.log
Job_Companym_0001.log
Job_Companye1_0001.log
Build_Companye1MRG1_0001.log

In this example, Job_Company_T_0001.log is the top level log file, followed by Job_Companym_0001.log and then Job_Companye1_0001.log. This can be determined by opening each log file in a text editor and noting the top section which displays the current jobs context. For the job Job_Companye1_0001.log, the following is displayed:

Run context:
JobStream 'ALLJOBS'
	JobStream 'Company_T'
		JobStream 'Companym'
			JobStream 'Companye1'

If your IBM Cognos Adaptive Warehouse project uses Materialized Views, you will also find log files named MVIEWScript-[timestamp].sql which contains the SQL used to manage the associated materialized view in the database.

For debugging purpose, extra metadata information is already injected by IBM Cognos Adaptive Warehouse as "variables" in job/builds and this metadata will show up in the logs, allowing users to diagnose the issues in the IBM Cognos Adaptive Warehouse model if required. The following example would give you the related information for the warehouse object, in this example 'Job Position', as well as the dataflow this log file is associated with, in this example, 'dataflow'.

[VARIABLE - 11:27:17] .. AAF_X_WHO_ID_X = '{1ee3316a-0907-11e0-81d4-
db9ea33d341b}'
[VARIABLE - 11:27:17] .. AAF_X_WHO_NAME_X = '{Job Position}'
[VARIABLE - 11:27:17] .. AAF_X_DF_ID_X = '{1ee0cd28-0907-11e0-883f-
db9ea33d341b}'
[VARIABLE - 11:27:17] .. AAF_X_DF_NAME_X = '{dataflow}'

Many IBM Cognos Business Intelligence queries are issued from IBM Cognos Adaptive Warehouse ETL to source data from the ERP. Typically these IBM Cognos Business Intelligence queries will exist only in IBM Cognos Data Manager factbuild objects (sometimes referred as databuilds in logs), and they would associate logs with the prefix "Build_". Most common ETL problems, especially configuration problems, may show up here.

Common Log Messages - Connection problems

In the IBM Cognos Adaptive Warehouse application log file, you may notice the following log message:

DM-DBM-0402 COGQF driver reported the following:

COGQF failed to execute query - check logon / credential path

In most instances, an IBM Cognos Adaptive Warehouse configuration step has not been applied correctly or not applied at all. It is essential that the configuration is run for IBM Cognos Adaptive Warehouse to ensure IBM Cognos Data Manager is configured correctly to source data from the IBM Cognos Business Intelligence server. It's possible this may be the first time IBM Cognos Adaptive Warehouse contacts the IBM Cognos BI Server when running ETL so this error would not have been encountered before. This could also be related to an incorrectly published source model, see below for further details.

Action: Rerun the IBM Cognos Adaptive Warehouse configuration tool.

Common Log Messages - Source model not published

If the source model is not published or out of sync from the IBM Cognos Adaptive Warehouse model for ETL loading, you may note the following error in the IBM Cognos Adaptive Warehouse application log:

COGQF failed to execute query - check logon / credential path

Users may try to publish the source model again or double check the model if it exists on the IBM Cognos Business Intelligence server. This can be done by launching IBM Cognos Report Studio and creating a new empty report and sourcing from the appropriate published model.

Action: Verify the package exists in the IBM Cognos Business Intelligence Server content store. Run load management again and ensure the source package is published.

Common Log Messages - Insufficient connections

When a dataflow sources from many input sources and lookups, you may receive a log message stating the maximum number of database connections has been reached.

DM-DBM-0402 COGQF driver reported the following:

RQP-DEF-0370 Maximum number of database connections (20) has been reached.

By default the IBM Cognos Query Framework engine has the maximum number of database connections set to 20. This can be changed to use a higher number by editing the file CQEConfig.xml. If this file does not already exist on your system, there is a sample version that can be used. This file is located at [Install_Directory]\data\aaf\configuration\CQEConfig.xml.sample. Rename this sample file to CQEConfig.xml and then perform the following amendment with a text editor,

In the section,

<component name="CQE">
<section name="DBConnectionPool">
<!~~~-- Description: Database connection timeout. 
Default is 900 seconds (15 minutes) ~~~-->
<entry name="Timeout" value="900"/>
<!~~~-- ~~~-->
<!~~~-- Description: Database connection pool size. ~~~-->
<!~~~-- Maximum number of connections managed by the report server. 
Default=20 ~~~-->
<entry name="PoolSize" value="20"/>
<!~~~-- ~~~-->
</section>

increase the value for PoolSize.

Action: Increase the PoolSize setting in CQEConfig.xml.

Common Log Messages – Further errors

You may also encounter data incompatibility when IBM Cognos Data Manager is trying to insert data into the database tables. In this case, it is always worth checking the IBM Cognos Adaptive Warehouse model to verify the warehouse item types match the corresponding source model data types being sourced from the IBM Cognos Business Intelligence Server queries.

Another problem that may be encountered is sudden lost of connectivity due to the underlying environment or network issues. In this cases, you may notice the ETL log files may not have "completed" or in some cases, when the issue occurs in IBM Cognos Data Manager, it may show some appropriate error messages about not being able to connect to the databases or IBM Cognos Business Intelligence Server.

DDL, System Table And XML Model Logs

The DDL and System Table logs are intended to be consumed by database administrators who wish to understand the database operations IBM Cognos Adaptive Warehouse is performing. They do not contain information that will be useful to the end user for troubleshooting.

The Model XML logs present a snapshot of the IBM Cognos Adaptive Warehouse project in various stages as it responds to events. These models are intended to aid IBM Cognos Adaptive Warehouse developers in troubleshooting but will not be useful to the end user to diagnose error messages.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics, Information Management
ArticleID=778961
ArticleTitle=IBM Cognos Proven Practices: Understanding the IBM Cognos Adaptive Warehouse Log files
publish-date=12062011