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 Express

When installing Cognos Express there are some things to remember when it comes to the location of the installation sofware

1.Special characters are not supported in the installation path
Certain extended characters are not supported when specifying the installation path for IBM Cognos Express and its components. Use only 7-bit ASCII characters when specifying the installation path for IBM Cognos Express as well as any of the client software applications provided with each of the IBM Cognos Express products.

2. Installation from a UNC path is not supported
You may be lucky and Congos Express will install and confiure properly. But when you try to install the Cognos Express modules, they all fail. Just uninstall Cognos Express, copy the installation software to a local disk of your server and install Cognos Express again. Now the Cognos Express modules will install properly

Product: Cognos TM1 9.x

1)  Open a DOS box / command prompt:  Start -> Run -> cmd
2)  Navigate to the TM1 bin directory using the cd command. Go to the TM1 bin directory, the default installation path is “C:\Program Files\Cognos\TM1\bin”.
3)  Type the following command:
tm1sd.exe -install -n”NameOfTM1Server” -z”PathtoTm1s.cfgFile”
Where the “NameOfTM1Server” (after the -n) is the name you want to give to this TM1 Server service.  If the name you are going to use contains any spaces, this value must be specified in double quotes.
Where the “PathtoTm1s.cfgFile” (after the -z) is the path to the tm1s.cfg file for this the TM1 Server you are setting up.
For example:
tm1sd.exe -install -n”TM1 Production Server” -z”C:\Program Files\Cognos\TM1\Custom\TM1Data\PData”

If the command is successful, it will return the following message:

TM1 Server / tm1 production server installed.

4)  You can then go into the services listing, and you will see the new TM1 Server service you setup.  Following the above example, the service would be listed as:
TM1 Server / tm1 production server
You can then configure the service to run automatically, and to run under a specific domain user.
NOTE:  If you are setting up a completely new TM1 Server for which you don’t already have a tm1s.cfg file, you can copy the tm1s.cfg from the Planning Sample TM1 Server and modify it accordingly.  At a minimum, you will need to modify the ServerName, DataBaseDirectory, and PortNumber parameter values.
If the service does not start (error message stating the service cannot be started), starting the same TM1 Logical server as a desktop application will usually provide more detailed information on the error.

Removal of the TM1 service

To remove a TM1 Server service (again, following the example above), follow steps 1 and 2, then type the following command:
tm1sd.exe -remove -n”NameOfTM1Server”

Where the “NameOfTM1Server” (after the -n) is the name of the TM1 Server service you wish to remove.
For example:
  tm1sd.exe -remove -n”TM1 Production Server”

 

Symptom
Schedule a Planning job to run but it stays pending.
Problem
Schedule a Planning Job to run a macro. The job stays in pending status. The macro by itself can be set to run later. This occurs mostly in a multi server environment.
Cause
Monitor service is set to False
Solution
Change the monitor service to true on the Application server and Web Server, restart services.

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.