Microsoft SQL server 2016

Try to find if any SQL agent jobs have reference to file foo.


Run this SQL query on the server:

SELECT s.step_id as 'Step ID',
j.[name] as 'SQL Agent Job Name',
s.database_name as 'DB Name',
s.command as 'Command'
FROM msdb.dbo.sysjobsteps AS s
INNER JOIN msdb.dbo.sysjobs AS j ON s.job_id = j.job_id
WHERE s.command LIKE '%foo%'


More Information:

Planning Analytics Workspace 68
Microsoft Windows 2016 server

When from IE browse to PAW portal to login, you get redirected to Cognos Analytics, but are then stuck.

Suggested Solution:

Use Chrome instead.

Internet Explorer 11 is no longer supported with Planning Analytics Workspace 2.0.57 and higher versions of Planning Analytics Workspace.

If you use EDGE, and use a Cognos BI as authenticate, check that not there is a group policy that change EDGE to use IE11 mode, at logon, then you will get about half circle in the browser when you connect to PAW. Switch EDGE to use EDGE (chromium mode) to make it work with PAW.


More information:

Planning Analytics Workspace 68
Microsoft Windows 2016 server

Start of Docker service give error like:

error during connect: Get http://%2F%2F.%2Fpipe%2Fdocker_engine/v1.40/containers/json: open //./pipe/docker_engine: The system cannot find the file specified. In the default daemon configuration on Windows, the docker client must be run elevated to connect. This error may also indicate that the docker daemon is not running.

Suggested solution:

Check the file C:\ProgramData\docker\config\daemon.json for typographical errors.
If you change the file daemon.json, you must reboot the windows server for the changes to take affect.

Rename the file d:\ibm\paw\config\paw.ps1 to paw.ps1.txt and try to start PAW again.

To get info about docker enter

docker info

To remove docker, start PowerShell as administrator, and try this commands:

./paw.ps1 down
docker kill $(docker ps -q)
docker rm $(docker ps -a -q)
docker rmi $(docker images -q)

docker ps --quiet | ForEach-Object {docker stop $_}
docker system prune --volumes --all

If for some reason, the docker command does not work at all. The Docker service does not start. You can try create a new docker folder like d:\dock and then change the C:\ProgramData\docker\config\daemon.json file to point to the new folder. “data-root”: “d:\\dock” tells what folder to use.

Reboot the windows server and wait 30 min to see if the windows docker service starts.

Remove HostValidated file from your folder d:\ibm\paw\config

Remove upgradewarned file from your folder d:\ibm\paw\config

If its starts, you can restart the installation of PAW, by go to your d:\ibm\paw folder and run the ./Start.ps1 command inside PowerShell again.

If you see error in windows event log like this:

Syscall did not complete within operation timeout. This may indicate a platform issue. If it appears to be making no forward progress, obtain the stacks and see if there is a syscall stuck in the platform API for a significant length of time…

cleanup: failed to delete container from containerd: no such container…

driver “windowsfilter” failed to remove root filesystem: hcsshim::GetComputeSystems: The requested compute system operation is not valid in the current state.

and you are using Symantec Endpoint Protection version 14.3.3385.1000 then you should uninstall Symantec software fully from the server.

Then install PAW without the anti-virus software.

When PAW and Docker is installed and working, then you can install your Anti-virus software again.  You may need to configure the Anti-virus software so it works with Docker.

More information:

scripts/paw.ps1 down –rmi all -v

Cognos Analytics 11.1.7

Microsoft Windows 2016 server

How do i install CA11 on my Windows server?

Follow the IBM documentation. Here is only a list of things to think about.

Download the software from IBM

You need at least the analytics-installer-2.2.2-win.exe and

Check this before installation on your new windows server:

Check that you have remote access to all your Cognos servers
Install SQL 2012 native client for ODBC support to SQL databases

Install NET Framework 4.7.2

Turn DEP off in Windows control panel
Set Power Options to HIGH Performance in Windows control panel
Turn off IEESC (internet explorer enhanced security configuration)
Check what port your SQL server will use, for access to Content Store and Audit database.
Exclude cognos folders from anti-virus software scanning
Open firewall ports 80, 443 to end users
Open firewall ports 80, 443, 9300, 9362, 4300, 5701, 9301 between servers.
Open firewall ports 1433 for SQL, 25 for Mail, 389 for Active Directory.
Install 7zip and Notepad++ to edit xml files on the server.



Install IIS on the Windows Server 2016 select Web Server IIS, ASP.NET 4.6, HTTP Activation, TCP Port Sharing, HTTP Redirection, WebDav Publishing, ISAPI Extensions, Websocket, Windows Authentication, IIS Management Scripts and Tools.
Update regedit HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\InetStp\MajorVersion to 9  (only if needed)
Install  or


Run the installation of Cognos Analytics manually

Get the CA_IIS_Config.bat script from folder D:\Program Files\ibm\cognos\analytics\cgi-bin\templates\IIS and copy it to its own folder, e.g. d:\install

Run the installation of the Cognos Analytics developer programs (framework manager)
Update the IIS script with the server name, and run the script CA_IIS_Config.bat
Copy file sqljdbc42.jar into folder d:\program files\ibm\cognos\analytics\drivers

Setup a Notification database in SQL, if you have many users in cognos and many scheduled jobs
Setup a Content Store and Audit database in your SQL server
Configure Cognos Analytics with FQDN, leave Websphere memory at 8182
Install the CA samples

Setup WebDav in IIS

Stop creation of dump files, open the cclWinSEHConfig.xml file from the install_location\configuration folder. Set “CCL_HWE_ABORT” value=”0″
Setup the audit database source and copy the D:\Program Files\ibm\cognos\analytics\samples\Audit_samples\ file to D:\Program Files\ibm\cognos\analytics\deployment folder. Import the audit samples.
Create a company logo in cognos The tags can be different for the different versions.
Tune logging to “Basic” in cognos connection
Activate SSO in Cognos Configuration by adding the advanced property
Name : singleSignonOption

Value: IdentityMapping

Set CAF to exclude * and tm1webserver:9510 and tm1webserver:9511
If you also have Cognos Controller then change Security – Authentication in CA11, Inactivity timeout in seconds to 36000
Update Windows TCP windows settings by import below reg values:

Windows Registry Editor Version 5.00




To get login to Planning Analytics with Cognos BI (CAM) you need to change a few files, as below:

Update tm1web.html with tm1web servername and port, like this

var tm1webServices = ["",""];

Copy tm1web.html to locations;

D:\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web

D:\Program Files\ibm\cognos\analytics\webcontent\tm1\web

Update pmhub.html with also paw servername and port, like this

var pmhubURLs = ["","",""];

Copy pmhub.html to locations;
D:\Program Files\ibm\cognos\analytics\webcontent

D:\Program Files\ibm\cognos\analytics\webcontent\bi

Update planning.html with also tm1servername and port, like this

var planningServices = ["",""];

Copy planning.html to same folders as pmhub.html listed above.

The content manager will look in folder D:\Program Files\ibm\cognos\analytics\webcontent for this files, but the Cognos Gateway will look in folder D:\Program Files\ibm\cognos\analytics\webcontent\bi for above files.


To prevent scriptable report error  when running sample custom control reports
you need to change the sample reports as below (they are written to only work on port 9300).

The global Sales Report is a sample showing how to use a javascript file with a custom control. These are authored to work ‘out of the box’ via dispatcher but not via a gateway.

To use with a gateway you need to edit the custom control in the report to point to the correct path.

1. Open the ‘Global Sales’ report in Edit mode.
2. select the custom control which is the thin blue box underneath the Prompts and view the properties.
3. In properties under General choose the ‘Module Path’ property and click the ellipsis.
4. By default this path is set to ‘/bi/samples/js/HideShowFilterPanel.js’
5. Please add your gateway to the front of this path so it reads something like:’/ibmcognos/bi/samples/js/HideShowFilterPanel.js’ (where ibmcognos is the name of your gateway virtual directory)
6. Save and re-execute the report.

Setup of jupyter notebook is not covered here, you have to follow the IBM documentation.


More information:

Planning Analytics
Microsoft Windows 2016 server

I have several Cognos BI servers, where i need to update the planning.html file on, to make SSO work for TM1.


On your source TM1 server, create a folder c:\script\tm1.

Create a text file serverslist.txt, where you enter the name or IP address of the servers.

Copy the updated planning.html, pmhub.html and tm1web.html files to this folder.

Create a powershell file, copyfiles.ps1, with this content:

# run the script on the source machine
# remote machines list 
$machines= Get-Content -Path "c:\Script\tm1\serverslist.txt" 
foreach ($onemachine in $machines) 
Write-Host "Currently the script is copying files on" $onemachine 
Copy-Item -Path "c:\Script\tm1\planning.html" -Destination "\\$onemachine\c$\Program Files\ibm\cognos\analytics\webcontent" -Recurse
Copy-Item -Path "c:\Script\tm1\planning.html" -Destination "\\$onemachine\c$\Program Files\ibm\cognos\analytics\webcontent\bi" -Recurse
Copy-Item -Path "c:\Script\tm1\pmhub.html" -Destination "\\$onemachine\c$\Program Files\ibm\cognos\analytics\webcontent" -Recurse
Copy-Item -Path "c:\Script\tm1\pmhub.html" -Destination "\\$onemachine\c$\Program Files\ibm\cognos\analytics\webcontent\bi" -Recurse
Copy-Item -Path "c:\Script\tm1\tm1web.html" -Destination "\\$onemachine\c$\Program Files\ibm\cognos\analytics\webcontent\tm1\web" -Recurse
Copy-Item -Path "c:\Script\tm1\tm1web.html" -Destination "\\$onemachine\c$\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web" -Recurse

Ensure that the paths are correct for your servers, if you have installed to D: drive you need to update the powershell script to reflect this; “\\$onemachine\d$\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web”

Start a powershell window as administrator.

The person logged in must have local admin rights on all cognos servers for this to work.

When you run the script it will print out any errors, like above when it can not find the server.

Test the script first in you LAB environment, as it will overwrite any existing files on the CA11 servers.

More Information:

Powershell Copy Files | Single | Multiple Computers

Cognos Enterprise Planning PLANSRVR_version=PLANSRVR-AW32-ML-RTM-10.2.1000.1-0

Cognos Analytics BI kit_version=

Microsoft Windows 2012 R2 server


The IBM Cognos service is restarting and people can not work. Error users can get are;

The server did something wrong</faultstring><detail><bus:exception><severity>error</severity><errorCode>DISPATCHER_NOT_YET</errorCode><bus:message><messageString>DPR-ERR-2109 The dispatcher cannot service the request at this time. The dispatcher is still initializing. Contact your administrator if this problem persists.

Suggested solution:

Stop all services.

Export all configuration.

Recreate the internal Cognos certificates.  Follow instructions at the links below.

Open Cognos Configuration and start the service in correct order. The order is very important. CA content manager must be started first.

Please note that the steps are different for different version of Cognos.

More Information:

Planning Analytics   TM1_version=TM1-AW64-ML-RTM-11.0.910.5-0
Microsoft Windows 2016 server


How upgrade Planning Analytics to later version?

Suggested solution:
Always follow IBM instructions first, this is a suggestion on what to check for at a installation of TM1.

In the new versions of PA, you can stop the IBM services, and do the installation on top of the existing Planning Analytics folder. This is the recommended way for a upgrade.

Download the latest version from IBM:

For a installation on a Windows server, you need the files that are for windows;

and the latest PAW and PASS…

Check this before installation on your new windows server:

Install Microsoft Visual C++ 2010 SP1 Redistributable Package

Install SQL 2012 native client for ODBC support to SQL databases

Install NET Framework 4.8

Turn DEP off in Windows control panel
Set Power Options to HIGH Performance in Windows control panel
Turn off IEESC (internet explorer enhanced security configuration)
Start Print spooler service (on the TM1 server)
Exclude cognos and docker folders from anti-virus software scanning
Open firewall ports 80,443,9510,9511 to TM1 end users
Open firewall ports 80,443,9012,9510,9511, 5495,5498,5895,5898, 12300-12400 between servers.
Open firewall ports 9300 for Cognos BI, 1433 for SQL, 25 for Mail, 389 for Active Directory.
Install 7zip and Notepad++ to edit xml files on the server.


Setup Planning Analytics local:

If more than one network card, then set the order of there use from powershell command;


Set-NetIPInterface -InterfaceIndex 2 -InterfaceMetric 50
Unzip tm1_winx64h_2.0.910.5_ml.tar.gz and Install with issetup.exe
Set maximum memory in MB for WebSphere to 8192 in Cognos Configuration
Check file C:\Program Files\ibm\cognos\analytics\templates\ps\portal\variables_tm1.xml exist on your CA11 server. Rename the variables_tm1.xml.sample file to variables_tm1.xml
Update files planning.html, tm1web.html with tm1web servername and port on CA11 server

// Update the following to point to the location of the planning service(s)
var planningServices = ["",""];

Update file pmhub.html with PAW servername and port on CA11 server

// Update the following to point to the location of the pmhub service(s)
var pmhubURLs = [","",""];
Set your servers ip, in the tm1s.cfg file to IPAddressV4=”″  (replace with your ip value)
Update a planning application for use of SSO and CAM in tm1s.cfg with below values;





Surf to to connect it with a CAM application
To preset the admin host in tm1web, Open D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\WEB-INF\configuration\tm1web_config.xml and change <add key=”AdminHostName” value=”paservername” />

Open D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\WEB-INF\configuration\tm1web_config.xml and change the timeout for the web
<add key=”HttpSessionTimeout” value=”60″ />
Open firewall ports 80,443,9510,9511,8888, 5495,5498,5895,5898,9012 and 12345.. for developers.
CertificateVersion=2 in TM1S.CFG gives that you use 2048-bit encryption internal in TM1
Test to remove any MTQ line in TM1S.CFG for new versions of Planning Analytics
For a new installation create a folder structure for the applications, like d:\tm1\app\datafiles…
Add the PortNumber=12345 (and below numbers) to the tm1s.cfg file for your applications.

HTTPportnumber=12346  (they must be unique for each TM1 application)


Change log time to LOCAL timezone, by in the file edit:

Where TM1 architect is installed, open C:\ProgramData\Applix\TM1\tm1p.ini and add lines

AllowImportCAMClients = T 

CognosGatewayURI =

Language=ENG in TM1P.INI will give English menu in TM1 Architect


Setup new TM1WEB: IBM Planning Analytics Spreadsheet Services

Install tm1web (PASS) from tm1web-11.0.68-21100620-winx64h_bundle folder by click on exe file

Change to port 9511, in file server.xml, located in the folder C:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web

Restart the “IBM Planning Analytics Spreadsheet Services”.

To make TM1 APP WEB work, you need to update file pmpsvc_config.xml in folder D:\Program Files\ibm\cognos\tm1_64\webapps\pmpsvc\WEB-INF\configuration with value:

<client id="thinClient" type="html-new" uri="">

Start “IBM Cognos TM1” service, that is for Tm1 App Web (pmpsvc).

Setup Planning Analytics Workspace on Windows 2016

To install to d drive, create c:\programdata\docker\config\daemon.json with this content:

"debug": true,
"data-root": "d:\\docker"
Run ./Start.ps1

If you have issue, use this powershell commands to get more information:

PS C:\> docker version
PS C:\> docker image ls
PS C:\> docker ps --all

PS C:\> Get-VMSwitch
PS C:\> Get-NetNat
PS C:\> Get-ContainerNetwork
Update PAW administration for CAM
IBM cognos BI gateway URI

IBM cognos BI Dispatcher URI

Change $env:CAMLoginNamespace to same as Namespace ID in Cognos Configuration.

Set timeout to $env:ProxyTimeoutSeconds=900 in file paw.ps1
To get the internal gateway IP for use on windows 2016 server enter:

docker inspect pa-gateway -f' {{.NetworkSettings.Networks.nat.IPAddress}}'
Locate the scripts/shutdown.ps1 script in your Planning Analytics Workspace installation folder.

Copy the scripts/shutdown.ps1 script to a permanent location on your system (d:\ibm\).

In a command window, open the Group Policy Editor (GPE) by running gpedit.msc.

Expand Computer Configuration and select Windows Settings.

Double-click Scripts (Startup/Shutdown).

Switch to the PowerShell Scripts tab.

Click Add and add the shutdown.ps1 script from the folder.

If you need to change port for paw set $env:PAGatewayHTTPPort=”8080” in file paw.ps1
Install the PA agent for the new workspace version on your TM1 server

Setup mail for the PA agent


How to upgrade PAW:
Download the new version and unzip the file in a new folder e.g. \ibm\paw69

  1. Stop the running paw with command ./scripts/paw.ps1 stop
  2. Copy the /ibm/paw68/config/paw.ps1 file from your current installation to the new installation location (/ibm/paw69/config).
  3. Copy the /ibm/paw68/config/certs directory from your current installation to the new installation location.
  4. Optional: If you configured SSL, copy the /ibm/paw68/config/ssl directory from your current installation to the new installation location.
  5. Install Planning Analytics Workspace Local in the new installation location. See Installing Planning Analytics Workspace Local. Go to the /ibm/paw69/ folder and run ./Start.ps1

    Reply “y” when you are prompted to install the Docker images. Reply “y” when you are prompted to open the administration tool.


More Information:

How to Upgrade Planning Analytics Workspace (PAW)

Older versions are listed here:


Cognos Analytics 11

The icon to start cognos configuration is gone, how do i start it?


For CA 11 kit_version=
kit_name=IBM Cognos Analytics

Run D:\Program Files\ibm\cognos\analytics\bin64\cogconfig.bat

For CA 11kit_version=
kit_name=IBM Cognos Analytics

Run “C:\Program Files\ibm\cognos\analytics\bin64\cogconfigw.exe”

For CA kit_version=11.1.7-2106251648

Run D:\Program Files\ibm\cognos\analytics\bin64\cogconfigw.exe

More information:

Microsoft SQL server 2016

Try to find what a users have for access rights inside a database.

Suggested solution:

Enter this code as sysadmin, to find person access rights in one database:

EXECUTE AS LOGIN = 'YourDomain\User.Name' --Change This

USE database  --Change This


SELECT * FROM fn_my_permissions(null, 'database');


Enter this to see what databases a person have access to

EXECUTE AS LOGIN = 'YourDomain\User.Name' --Change This
SELECT [name]
FROM MASTER.sys.databases

To find your rights in the current database:

SELECT * FROM fn_my_permissions(null, 'database');

To list dbowners

SELECT user_name(member_principal_id)

FROM   sys.database_role_members

WHERE  user_name(role_principal_id) = 'db_owner'

More Information:

fn_my_permissions() – Sprawdzenie jakie mamy uprawnienia do bazy danych

Microsoft SQL server 2016

When moving a SQL database to a different Windows server, you want to check the other database if they have any SP reference to that database.


This query will loop all database and run the select statement on each, and find any store procedure that contain the word “foo”.







It is necessary to set the database for the query to run against, by using the USE ? statement, otherwise the code will execute in the context of the current database.

You can also use this to search inside one database:







or use below, as above have a limit of searching only the first 8000 characters of the stored procedure.

SELECT OBJECT_NAME(object_id), definition

    FROM sys.sql_modules

    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1

    AND definition LIKE '%foo%'


More information: