Product:

Planning Analytics Workspace

Microsoft Windows 2019 server

Issue:

After patch or restart of the Windows server, sometimes the PAW does not come up.

Suggested solution:

In Daemon.json file shutdown timeout settings update and changed to 600 sec from 180 sec

Found in folder C:\ProgramData\docker\config

Registry key settings update WaitToKillServiceTimeout increase to 20 sec from 5 sec

  • WaitToKillServiceTimeout: Windows normally waits 5 seconds for background services to clean up and close when you tell your computer to shut down. Some applications may change this value when you install them, giving their background services extra time to clean up. Windows forcibly shuts down background services after this period of time. This value controls how many seconds Windows waits before doing so. Windows will automatically shut down if all services close successfully before the timer expires.

It should be created in regedit under this branch:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

Change the Service type of ‘Docker Engine’ to manual from automatic.

Create a task scheduler so that this ‘Docker Engine’ service should start only after 15 minutes of the server restart.

https://www.maketecheasier.com/start-scheduled-tasks-with-delay-windows/

https://www.technipages.com/scheduled-task-windows

More information:

https://www.ibm.com/support/pages/troubleshooting-planning-analytics-workspace-related-docker-issues

https://exploringtm1.com/how-to-install-planning-analytics-workspace-to-windows-server-2019/

https://www.ibm.com/support/pages/how-configure-planning-analytics-timeout-settings

https://www.howtogeek.com/282062/control-how-long-windows-waits-before-killing-apps-at-shutdown/

Product:

Microsoft SQL server 2016

Issue:

How do I give access only to a table?

Solution:

Ensure the user exist in the database. Enter below SQL query to create a role (RESTRICTED_SELECT), and set the SELECT to a table (TABLE_NAME) for that role. Last add the user (USER_NAME) to the role.  Replace values with your user and table names.

Or better add a AD group to the role, then the IT department can add people to that AD group to get access to the table.

CREATE ROLE RESTRICTED_SELECT
GO
GRANT SELECT ON TABLE_NAME TO RESTRICTED_SELECT
GO
SP_ADDROLEMEMBER RESTRICTED_SELECT , USER_NAME

If i want give access to a function, for instance the user should be able to see Active Monitor from SSMS enter below SQL query.

USE master;
CREATE SERVER ROLE SQLMonitor ;
GRANT VIEW SERVER STATE TO SQLMonitor ;
GO

To view the Data File I/O pane, besides VIEW SERVER STATE, the login must be granted CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION permissions.

To kill a process, it’s necessary to be a member of the sysadmin role.

To add the user to a server role enter:

sp_addsrvrolemember USER_NAME, SQLMonitor

More Information:

https://www.sqlshack.com/sql-server-activity-monitor/

https://www.codeproject.com/Tips/1103206/SQL-Server-Grant-Permission-to-Particular-Table

https://www.guru99.com/sql-server-create-user.html

https://sqlserverplanet.com/dba/using-sp_addrolemember

Product:

Cognos Analytics 11.1.7

Microsoft Windows 2019 server

Issue:

After restart of Windows server for a windows patch, the CA11 is not responding.

If you go to http://servername.domain.com:9300/p2pd/servlet you get a blank screen.

In log file D:\Program Files\ibm\cognos\analytics\logs\p2pd_messages.log you see error like this:

********************************************************************************
product = WebSphere Application Server 20.0.0.7 (wlp-1.0.42.cl200720200625-0300)
wlp.install.dir = D:/Program Files/ibm/cognos/analytics/wlp/
java.home = D:\Program Files\ibm\cognos\analytics\ibm-jre\jre
java.version = 1.8.0_301
java.runtime = Java(TM) SE Runtime Environment (8.0.6.35 – pwa6480sr6fp35-20210714_01(SR6 FP35))
os = Windows Server 2019 (10.0; amd64) (sv_SE)
********************************************************************************
[2022-08-18 20:43:41:231 CEST] 00000001 com.ibm.ws.kernel.launch.internal.FrameworkManager I CWWKE0002I: The kernel started after 3,435 seconds
[2022-08-18 20:43:41:481 CEST] 00000028 com.ibm.ws.kernel.feature.internal.FeatureManager I CWWKF0007I: Feature update started.
[2022-08-18 20:43:43:530 CEST] 00000028 com.ibm.ws.kernel.feature.internal.FeatureManager E CWWKF0003E: An exception was generated when installing or uninstalling bundle INSTALL file:/D:/Program%20Files/ibm/cognos/analytics/wlp/usr/extension/lib/logging-feature-log4j2.jar (resolved from: com.ibm.bi.logging.onlog4j2/[1.0.0,2.0.0)@file:/D:/Program%20Files/ibm/cognos/analytics/wlp/usr/extension/lib/logging-feature-log4j2.jar). Exception: java.lang.NullPointerException
at com.ibm.ws.kernel.feature.internal.Provisioner$2.installFeatureBundle(Provisioner.java:379)
at com.ibm.ws.kernel.feature.internal.Provisioner$2.handle(Provisioner.java:273)
at com.ibm.ws.kernel.feature.internal.BundleList.foreach(BundleList.java:612)
at com.ibm.ws.kernel.feature.internal.Provisioner.installBundles(Provisioner.java:251)
at com.ibm.ws.kernel.feature.internal.FeatureManager.updateFeatures(FeatureManager.java:1355)
at com.ibm.ws.kernel.feature.internal.FeatureManager.update(FeatureManager.java:736)
at com.ibm.ws.kernel.feature.internal.FeatureManager.processFeatureChanges(FeatureManager.java:844)
at com.ibm.ws.kernel.feature.internal.FeatureManager$1.run(FeatureManager.java:636)
at com.ibm.ws.threading.internal.ExecutorServiceImpl$RunnableWrapper.run(ExecutorServiceImpl.java:239)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1160)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.lang.Thread.run(Thread.java:825)…..

 

 

Solution:

Restart the IBM Cognos service again on all servers – start with the CA11 content manager first.

In case that does not help, stop the IBM Cognos service.

Erase the workarea content – that is under folder D:\Program Files\ibm\cognos\analytics\wlp\usr\servers\cognosserver\workarea

Start the IBM Cognos service.

This to clean out any “corruption” in cognos websphere applications files.

 

 

More Information:

https://www.ibm.com/support/pages/cfg-err-0106-ibm-cognos-configuration-did-not-receive-response-when-trying-start-ibm-cognos-service-caused-corruption-inside-workarea-folder

https://www.ibm.com/support/pages/cfg-err-0106-ibm-cognos-configuration-did-not-receive-response-ibm-cognos-service-time-allotted-2

 

Product:

Cognos Analytics 11

Issue:

During installation of a fix pack on top of a existing installation of CA11 you get a error.

Solution:

Close all instance of file explorer – if you have a window open of the folder where you are going to install Cognos product, you will get a error like this.

You also need write access to the drive where you read the installation media from.

More Information:

https://www.ibm.com/support/pages/how-upgrade-your-version-cognos-analytics

https://www.ibm.com/docs/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/inst_cr_winux.pdf

Product:

Planning Analytics 11.0.911.20-0

Issue:

After upgrade of Cognos Analytics fix pack, then the login to Tm1Web does not work.

Error: No webpage was found for the web address:  http://servername/ibmcognos/bi/tm1/web/tm1web.html…

HTTP ERROR 404

Solution:

On the CA11 servers, ensure that the folder C:\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web exist, and contain the tm1web.html file.

The fix pack installation of CA11 can remove files from cognos folders, like variables_tm1.xml

The tm1web.html file should contain a list of the formats that you are going to access the tm1web with.

var tm1webServices = [“http://SystemName:PortNumber“,”https://SystemName:PortNumber“];

 

More information:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=security-configuring-tm1-web-use-cognos

 

Product:

Microsoft SQL server 2016

Issue:

How log start and stop times of SP in your database?

Suggested solution:

Create a table to store time values, and create a SP that you call from other SP to update the table.

Create the log table:

CREATE TABLE [dbo].[_log_logged](
[LogID] [BIGINT] NULL,
[starttime] [DATETIME] NULL,
[endtime] [DATETIME] NULL,
[iscomplete] [INT] NULL,
[runstatus] [INT] NULL,
[durationInSeconds] [INT] NULL,
[whatProcess] [VARCHAR](100) NULL
) ON [PRIMARY]
GO

 

Create the SP to call to create log values:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[_logger] @LogID [INT], @StartTime [DATETIME],@EndTime [DATETIME], @WhatProcess [VARCHAR](100), @NewLogID [INT] OUT 
AS BEGIN 
SET NOCOUNT ON;
DECLARE @RunTimeSeconds INT,
@RunStatus INT,
@IsComplete INT,
@NewLogRowID INT,
@myDuration NUMERIC(20,10)

IF @LogID IS NULL
SET @LogID = 0

IF @LogID = 0
BEGIN 
SET @RunStatus = 1
SET @IsComplete = 0

SET @LogID = (SELECT ISNULL((MAX(LogID)),0) + 1 AS maxID FROM _log_logged)
INSERT INTO _log_logged(LogID
,StartTime
,runStatus
,IsComplete
,WhatProcess
)
SELECT @LogID,
@StartTime, 
@RunStatus,
@IsComplete,
@WhatProcess

END

ELSE IF @LogID != 0
BEGIN
SET @RunStatus = 0
SET @IsComplete = 1
UPDATE a
SET EndTime = @EndTime,
IsComplete = @IsComplete,
RunStatus = @RunStatus,
whatProcess = @WhatProcess,
DurationInSeconds= DATEDIFF(SECOND,@StartTime, @EndTime) 
FROM _log_logged a
WHERE a.LogID = @LogID

END

SET @NewLogID = @LogID
END
GO

Add below code to your SP, to enable them to log values to the above table.

-- =============================================
-- Author: 
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE [dbo].[USP_ThisIsTheTest]
AS
BEGIN

SET NOCOUNT ON;
declare @StartTime datetime = GETDATE();
declare @EndTime datetime 
declare @logid int
DECLARE @NewLogID INT
DECLARE @whatProcessText VARCHAR(100) = 'Your name of the sp to be logged';


EXEC [dbo].[_logger] @LogID = 0
,@StartTime = @StartTime
,@EndTime = NULL
,@whatProcess = @whatProcessText
,@NewLogID = @NewLogID OUTPUT

-- =============================================
-- Here should your code be for the process to execute


WAITFOR DELAY '00:00:10.000'


-- =============================================
-- Log the end of the sp process with this call:

SET @EndTime = GETDATE()
EXEC [dbo].[_logger] @LogID = @NewLogID, @StartTime = @StartTime ,@EndTime = @EndTime ,@whatProcess = @whatProcessText ,@NewLogID = @NewLogID OUTPUT


END

Run the SP to test the log table.

 

The result can look like this:

 

More information:

SLEEP Command in Sql Server

https://www.mssqltips.com/sqlservertip/2003/simple-process-to-track-and-log-sql-server-stored-procedure-use/

https://www.sqlservercentral.com/articles/logging-and-error-handling-for-sql-stored-procedures

https://www.tech-recipes.com/database/sql-server-error-logging-and-reporting-within-a-stored-procedure/ 

Product:

Planning Analytics

Issue:

When was a version released?

Solution:

List from IBM.

https://www.ibm.com/support/pages/node/6519826

 

 

More Information:

https://www.ibm.com/support/pages/ibm-planning-analytics-microsoft-excel-conformance-requirements

https://www.ibm.com/support/pages/ibm-planning-analytics-tm1-web-conformance-requirements

https://www.tm1forum.com/viewtopic.php?t=16128

End Of Support Summary

  • PA2.0.0 to PA 2.0.8 inclusive: 30 Sep 2022
  • 10.2.2: 30 Sep 2019
  • 10.2.0: 30 Sep 2018
  • 10.1.x: 30 Apr 2017
  • 9.5.x: 30-Sep-2015
  • 9.4.x and Executive Viewer: 30-Sep 2013

https://blog.octanesolutions.com.au/pa-paw-pax-version-conformance

IBM Planning Analytics TM1