This DB2 Error occurs when attempting to run a large report or during a search of the content store.

CM-SYS-5092 Content Manager failed while returning the query results.
[IBM][CLI Driver][DB2/6000] SQL0101N The statement is too long or too complex. SQLSTATE=54001

This is an known issue with the heap size for DB2. To update the heap size.
Steps:

The following is an extract from DB2 documentation:
SQL0101N The statement is too long or too complex.
Explanation: The statement could not be processed because it exceeds a system limit for either length or complexity, or because too many constraints or triggers are involved. The default statement heap size for DB2 is too small to support the complex SQL statements. Try updating the statement heap size to be 8000 or more. To update the heap size, open a DB2 command prompt and type the following: > db2 update db cfg for <database name> using stmtheap 8000

Wondering how many MB your Cognos JVM (Java Virtual Machine) uses? Type in the following URL in your browser

http://localhost/cognos8/cgi-bin/cognosisapi.dll?b_action=/diagnostics

Provide login credentials if required and the result appears.

<Diagnostics>
<Dispatcher host=”localhost” port=”9300″ capacity=”1.0″ />
<Memory Amount_of_free_memory_in_the_system=”16199.064 KB” Total_memory_in_the_JVM=”130955.776 KB” />
<CM host=”localhost” port=”9300″ />
</Diagnostics>

 

If you use CGI then the link is

[server address]/cognos8/cgi-bin/cognos.cgi?b_action=/diagnostics

 

In Cognos 8.1 and Reportnet it was possible to delete the group Everyone from the Systemadministrators role in the Cognos namespace. In Cognos 8.2 and higher, it is not possible to delete the group Everyone, before you add another user, group or role to the role Systemadministrators. If you are somehow not able to get Systemadministrator permissions in the content store use the following script:

insert into CMREFORD1 (PROPID, CMID, ORD, REFCMID)
select 27, (select cmid from cmobjprops1 where objid=’::System Administrators’),
coalesce((select max(ord) from CMREFORD1 where propid=27 and cmid=(select cmid from cmobjprops1 where objid=’::System Administrators’)), -1)+1,
CMID
from CMOBJPROPS1 where OBJID=’::Everyone’

This script is called AddSysAdminMember.sql and can be found for SQL, Oracle, DB2, Sybase and Derby in the directory C:\Program Files\COGNOS\c8\configuration\schemas\content\

Run this script in MS SQL Query Analyzer for your Content Store database and you can log in as SystemAdministrator to fix the security.

The cogserver.log file is a good source of information when troubleshooting. But default the cogserver.log file is a 10 MB file (set in Cognos Configuration Manager) in xml format and hard to read.

127.0.0.1:9300 3048 2009-06-04 09:16:53.218 +1     stopper-logService LOGSV 2015 1 server.Audit.IPF StopService LogService  Success  <parameters><item name=”Port”><![CDATA[9362]]></item><item name=”Mode”><![CDATA[TCP]]></item><item name=”Secure”><![CDATA[FALSE]]></item></parameters>
127.0.0.1:9300 1880 2009-06-05 10:07:53.187 +1     main LOGSV 2015 1 server.Audit.IPF StartService LogService  FAILED Log Server Database error <parameters><item name=”DB URL”><![CDATA[jdbc:JSQLConnect://localhost:1433/Audit]]></item><item name=”DB Error”><![CDATA[Failed Logon:com.jnetdirect.jsql.JSQLException: TCP/IP connection failed to host:localhost java.net.ConnectException: Connection refused: connect url:jdbc:JSQLConnect://localhost:1433/Audit]]></item></parameters>
127.0.0.1:9300 1880 2009-06-05 10:07:53.234 +1     main LOGSV 2015 1 server.Audit.IPF StartService LogService  Success  <parameters><item name=”Port”><![CDATA[9362]]></item><item name=”Mode”><![CDATA[TCP]]></item><item name=”Secure”><![CDATA[FALSE]]></item></parameters>

Since Cognos 8.1 there is a handy tool called LogviewV2.exe and can be found in the c8\bin directory. Start LogviewV2.exe and load the cogserver.log file. Now all the messages and errors are formatted, can be read much better.

Transformer batch mode switches in Scheduler

Solution Description
Command line switches are required to run transformer in batch mode from Scheduler.

From the On-Line Help:

You can use command-line options to

? run Transformer in batch to create or update PowerCubes,
? run Transformer in batch and update the model with new categories generated during the category generation process,
? change the current date setting so that relative time calculations in batch cube creation are based on a specific date,
? use different preference settings or preference files,
? open or delete checkpoint files,
? supply database signon information, or

? change the degree of detail for log file messages.

The syntax is

trnsfrmr -<option> <model_file>|<MDL_file>

Notes

? The command-line syntax is not case-sensitive for Transformer running under Windows.
? Command-line options that take arguments cannot contain spaces between the switch and the argument. For example:

trnsfrmr -r 2 july.mdl is not valid, whereas
trnsfrmr -r2 july.mdl is valid.

? You can combine command-line options. For example:

trnsfrmr -n -i sales.py?
trnsfrmr -i -t19932 sales.py?

The ? (question mark) in the extension .py? is replaced by the character that is used in your release of Transformer.

? The space character is used to separate multiple command-line options. Therefore, if any of the arguments contain spaces, you must enclose them in double quotes. For example:

trnsfrmr -n -kfield three=CarlosR/pw462 is not valid, whereas
trnsfrmr -n -k”field three=CarlosR/pw462″ is valid.

Related Topics

-d (Set Preference directory!!!)
-f (Set Preference File)
-i (Open Saved Model)
-k (Specify Database Signon)
-n (Run in Batch Mode)
-o (Do Not Create PowerCubes)
-p (Open Checkpoint File)
-r (Log Level Detail)
-s (Save Model)
-t (Set Current Period)
-u (Get Partition Status)
-nologo

CognosScript programs (macros) can be written, run, debugged and tested in the CognosScript Editor which comes default with the installation of Cognos Impromptu or PowerPlay. The CognosScript editor can be started on edmacro32.exe in the \bin\ cognos installation folder to open mac files.

The Macros are saved to a text format and have a mac extension. When the file is run it is automatically compiled to a binary file, with a mcx extension.
To run the macros from the command line the runmac32.exe in the \bin\ cognos installation folder is used.

To run a cognos macro from the command line just type in the following at the command prompt:
“D:\Program Files\Cognos\cer3\bin\runmac32.exe” D:\myfirstmacro.mac

It is also possible to pass a parameter to the script (you’ll find an example on how to read the parameter in CognosScript on our pages). It can be done by adding the parameter simply at the end of the command: “D:\Program Files\Cognos\cer5\bin\runmac32.exe” D:\myfirstmacro.mac

Product: Cognos BI 10

Symptom
Restrict access to Business Insight and Business Insight Advanced

Solution
Steps to deny users from using the Business Insight tool using capabilities:
1. Go the IBM Cognos Administration page and click on Security tab
2. Under the Capabilities link, click the ‘Set Properties’ on the Executive Dashboard capability. To deny access to a user, deny the execute and traverse rights.

Steps to deny users access to the Business Insight Advanced tool:

Note: Business Insight Advanced tools permissions are linked to Report Studio. So denying access to Business Insight Advanced also denies access to Report Studio

1. Go the IBM Cognos Administration page and click on Security tab
2. Under the Capabilities link, click the Set Properties on the Report Studio capability. To deny access to a user, deny the execute and traverse rights.

 

Product:  Cognos BI 8

Problem
How to determine the version of an Oracle JDBC driver?

Solution
Steps:
1.  Locate the classes12.jar file being used. This is located in: <Cognos Installation Location>\c8\webapps\p2pd\WEB-INF\lib
2.  Make a copy of the classes12.jar file.
3.  Rename the classes12.jar to classes12.zip
4.  Use Winzip or some other file compression tool, extract the content of the classes12.zip to a temporary location.
5.  In the temporary location, locate the file called MANIFEST.MF
6.  Open the MANIFEST.MF file with notepad
7.  Look for the line Specification-Version: “Oracle JDBC Driver version – 9.0.2.0.8” indicates that the version of this JDBC classes12 driver is 9.0.2.0.8.

This Oracle JDBC Driver version, must be exactly same as the version of the Oracle database server were you want to create a metric or content store. Ask the Oracle DBA how to obtain the correct JDBC version is necessary.