ABSTRACT (by Federico Levis ) Category: performance
Performance Tip: usually the first step to investigate performance is to analyze Cognos queries. We will descrive how to enable Cognos query Log in cogserver.log
1. Enable Cognos query log in cogserver.log
Login into Cognos Connection with administrator permission
Launch - IBM Cognos Administrator
Configuration - Dispatcher and Settings
Click Set Properties
Select Logging and insert a large number in entries filed to see all the entries.
Check following entries
Stop / Start Dispatcher (using More)
2. [Optional] Clear cogserver.log
Optionally you can clear cogserver.log:
Windows: open it with an editor (e.g Notepad++) and delete all rows
Unix: echo “ “ > cogserver.log
3. Queries in cogserver.log:
The Cognos Queries are logged into c10/logs/cogserver.log with the prefix Native
Real Oracle Query starts after NativeQuery <thirdparty><![CDATA[with "
Examples
NativeQuery <thirdparty><![CDATA[
select 123456/100 as CognosNumber from dual
]]></thirdparty>
NativeQuery <thirdparty><![CDATA[
select distinct `cognos_sort`.`SORT_ID` as "SORT_ID" , `cognos_sort`.`SORT_INFO` as "SORT_INFO" , `cognos_sort`.`SORT_POS` as "SORT_POS" from `cognos_sort` where `REPORT_ID` = 101 order by `SORT_POS` asc
]]></thirdparty>
NativeQuery <thirdparty><![CDATA[ select `city`.`city` as "C0" , `customer`.`first_name` as "C1" , `customer`.`last_name` as "C2" , `customer`.`email` as "C3" , DATE_FORMAT(`payment`.`payment_date`, '%b %d, %Y %H:%i:%S') as "C4" , sum(`payment`.`amount`) as "C5" , min(`country`.`country`) as "C6" , `country`.`country` as "C7" , `country`.`country_id` as "C8" , `city`.`city_id` as "C9" , `payment`.`payment_date` as "C10" , {fn CONCAT({fn CONCAT(`country`.`country`, ': ')}, `city`.`city`)} as "C11" , DATE_FORMAT(`payment`.`payment_date`, '%b %d, %Y %H') as "C12" , DATE_FORMAT(`payment`.`payment_date`, '%Y %m %d %H') as "C13" , `address`.`address` as "C14" , `address`.`postal_code` as "C15" from `city` `city`, `customer` `customer`, `payment` `payment`, `country` `country`, `address` `address` where `city`.`city_id` in (251, 59, 63, 483) and `city`.`city_id` = `address`.`city_id` and `address`.`address_id` = `customer`.`address_id` and `customer`.`customer_id` = `payment`.`customer_id` and `country`.`country_id` = `city`.`country_id` group by `country`.`country`, `city`.`city`, `customer`.`first_name`, `customer`.`last_name`, `customer`.`email`, DATE_FORMAT(`payment`.`payment_date`, '%b %d, %Y %H:%i:%S'), `country`.`country_id`, `city`.`city_id`, `payment`.`payment_date`, {fn CONCAT({fn CONCAT(`country`.`country`, ': ')}, `city`.`city`)}, DATE_FORMAT(`payment`.`payment_date`, '%b %d, %Y %H'), DATE_FORMAT(`payment`.`payment_date`, '%Y %m %d %H'), `address`.`address`, `address`.`postal_code` ]]></thirdparty>
Internal Cognos functions (if present)
They are individuated by "NativeQuery <function"