Author: Roger

DB21061E Command line environment not initialized.

March 23, 2023 by Roger·Comments Off

Product:

DB2 connect

Issue:

How see what server is in use for the DB2 ODBC connection, when the setup of the DB2 ODBC connection does not work.

Suggested solution:

Go to a command prompt, and go to the folder where the DB2 active client files are installed. Can be C:\Program Files\IBM\SQLLIB\BIN folder.

Run command db2 list node directory to see what server you are connecting to.

If it does not work, start the program db2cwadmin to get a CMD prompt where you can run the db2 commands.

In windows registry you can see part of the ODBC settings in your setup;

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
“PROD”=”IBM DB2 ODBC DRIVER – DB2COPY1”

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\PROD]
“Driver”=”C:\\PROGRA~1\\IBM\\SQLLIB\\BIN\\DB2CLIO.DLL”

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\IBM DB2 ODBC DRIVER]
“UsageCount”=dword:00000001
“Driver”=”C:\\Program Files\\IBM\\SQLLIB\\BIN\\DB2CLIO.DLL”
“Setup”=”C:\\Program Files\\IBM\\SQLLIB\\BIN\\DB2ODBC64.DLL”
“CPTimeout”=”60”

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\IBM DB2 ODBC DRIVER – DB2COPY1]
“UsageCount”=dword:00000001
“Driver”=”C:\\PROGRA~1\\IBM\\SQLLIB\\BIN\\DB2CLIO.DLL”
“Setup”=”C:\\PROGRA~1\\IBM\\SQLLIB\\BIN\\DB2ODBC64.DLL”
“CPTimeout”=”60”

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\IBM DB2 ODBC DRIVER – IBMDBCL1]
“UsageCount”=dword:00000001
“Driver”=”C:\\PROGRA~1\\IBM\\IBMDAT~1\\BIN\\DB2CLIO.DLL”
“Setup”=”C:\\PROGRA~1\\IBM\\IBMDAT~1\\BIN\\DB2ODBC64.DLL”
“CPTimeout”=”60”

More of the DB2 connection is stored in files, that are updated when you run command like this:

Use a bat file to call the config file:

if not exist "\Program Files\IBM\SQLLIB\BIN" goto stopp
cd \Program Files\IBM\SQLLIB\BIN
db2cmd db2setcp " db2 -tvf C:\db2script\catalog.sql " 
:stopp

Catalog.sql file can contain command like this;

UNCATALOG NODE db2prod;
CATALOG TCPIP NODE db2prod
REMOTE db2servername.domain.com
SERVER 60000
REMOTE_INSTANCE db2prod
OSTYPE LINUX;

UNCATALOG DATABASE PROD;
CATALOG DATABASE PROD
AS PROD
AT NODE db2prod
AUTHENTICATION SERVER;

catalog odbc data source PROD;

(PROD is the database name and the ODBC connection name, change to your need)

Also check the C:\Windows\System32\drivers\etc\hosts file for any hard coded references to the DB2 server.

Add row like this in the HOSTS file, to point to the DB2 server ip to maybe improve connection start speed;

# the new DB2server values
10.20.30.40 db2servername.domain.com

More Information:

https://www.dbisoftware.com/db2nightshow/20181214DB2NightZ92.pdf

https://www.scriptcase.net/docs/en_us/v81/connecting-your-database/ibm-db2/windows/connecting-with-ibm-db2

https://docs.rackspace.com/blog/ibm-db2-database-administration/

https://en.wikiversity.org/wiki/Hosts_file/Edit

https://helgeklein.com/blog/where-is-the-hosts-file-on-windows-x64/

https://www.inmotionhosting.com/support/website/modifying-your-hosts-file/

How show network diagram of Azure?

March 22, 2023 by Roger·Comments Off

Product:

Microsoft Azure Network

Issue:

Can I show a diagram over the network in my Azure subscription?

Solution:

Log into the Azure portal with an account that has the necessary permissions.
On the top, left corner of the portal, select All services.
In the All services filter box, enter Network Watcher. When Network Watcher appears in the results, select it.
Select Topology. The network watchers are created in a resource group named NetworkWatcherRG.
Select a subscription, the resource group of a virtual network you want to view the topology for, and then select the virtual network.

In Azure you can select the Virtual network, and click on Diagram on the left side.

Then you get a picture of the resource you have in Azure.

More Information:

https://learn.microsoft.com/en-us/azure/network-watcher/view-network-topology

https://learn.microsoft.com/en-us/azure/security/fundamentals/infrastructure-network

List only the latest records from a date column

March 20, 2023 by Roger·Comments Off

Product:

Microsoft SQL server 2016

Issue:

How list only the latest rows with the same date in the update column?

Solution:

Create a view – who will only list the latest row based on the date column;

CREATE VIEW [DM].[AccountView-Latest]
AS
SELECT [key_dimAccount]
,[Name]
,[InsertDate]
,[UpdateDate]
FROM [DM].[dimAccount]
WHERE UpdateDate = ( SELECT MAX(UpdateDate) FROM [DM].[dimAccount ] )

The where statement will only take the latest date (max) from the updatedate column – should give a list with only the records that are updated at same date.

More Information:

https://www.w3resource.com/sql/aggregate-functions/max-date.php

https://www.w3schools.com/sql/func_sqlserver_getdate.asp

When did some one access the database?

March 15, 2023 by Roger·Comments Off

Product:

Microsoft SQL server 2016

Issue:

How do i see when someone last accessed the database?

Solution:

Use any of this SQL querys;

SELECT d.name,
last_user_seek = MAX(last_user_seek),
last_user_scan = MAX(last_user_scan),
last_user_lookup = MAX(last_user_lookup),
last_user_update = MAX(last_user_update)
FROM sys.dm_db_index_usage_stats AS i
JOIN sys.databases AS d ON i.database_id=d.database_id
GROUP BY d.name

Declare @last_boot datetime
Set @last_boot = (select [sqlserver_start_time] from sys.dm_os_sys_info)

select @@servername as [ServerName],'last_boot' = @last_boot, 'days_since_last_boot' = datediff(d, @last_boot, getdate())

if object_id('tempdb..##Table_usage_data') is not null
drop table ##Table_usage_data
create table ##Table_usage_data (ID int identity (1,1), [database] varchar(255),[last_user_seek] datetime,[last_user_scan] datetime, [last_update] datetime)

declare @get_last_user_activity_timestamp varchar(max)
set @get_last_user_activity_timestamp = ''
select @get_last_user_activity_timestamp = @get_last_user_activity_timestamp +
'select db_name([database_id]), max(last_user_seek), max(last_user_scan), max([last_user_update]) from sys.dm_db_index_usage_stats where db_name([database_id]) = ''' + [name] + ''' group by [database_id];' + char(10)
from sys.databases where [database_id] > 4 and [state_desc] = 'online'

insert into ##Table_usage_data ([database],[last_user_seek],[last_user_scan],[last_update])
exec (@get_last_user_activity_timestamp)

select
[database], last_user_scan,last_user_seek,last_update
from
##Table_usage_data

More Information:

https://www.dbblogger.com/post/identify-when-your-sql-database-was-last-used

What version do i have?

March 15, 2023 by Roger·Comments Off

Product:

Cognos Analytics 11.1.7

Microsoft Windows 2019 server

Issue.

How do i find the version of CA11 i have installed?

Solution:

Check the cmplst.txt file in folder D:\Program Files\ibm\cognos\analytics

The row Manifest=casrv-manifest is the closeset to find the version/build number you can compare to site at IBM.

Operating System Version: 10.0
Manifest=casrv-manifest-11.1.7-2101131319-winx64h.json
Installer=analytics-installer-2.0.20100517-win.exe

Search 11.1.7-2101131319 on the IBM page – if it is not lised, you have a interim fix pack between two other numbers.

Cognos Analytics 11.1.R7 FP3	11.1.7.3	11.1.7-2106251648	2.2.2
Cognos Analytics 11.1.R7 FP4	11.1.7.4	11.1.7-2111101256	2.2.11
Cognos Analytics 11.1.R7 Interim Fix 6	11.1.7.4	11.1.7-2112131555	2.2.11
Cognos Analytics 11.1.R7 Interim Fix 7	11.1.7.4	11.1.7-2112191704	2.2.11
Cognos Analytics 11.1.R7 Interim Fix 8	11.1.7.4	11.1.7-2201050500	2.2.11
Cognos Analytics 11.1.R7 Interim Fix 9	11.1.7.4	11.1.7-2204120500	2.2.15
Cognos Analytics 11.1.R7 FP5	11.1.7.5	11.1.7-2205311318	2.2.16
Cognos Analytics 11.1.R7 FP6	11.1.7.6	11.1.7-2210041400	2.2.22

More Information:

https://www.ibm.com/support/pages/how-determine-which-version-cognos-analytics-you-have-installed

https://pmsquare.com/analytics-blog/2022/6/8/how-to-find-your-cognos-version-build-and-common-name

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

Checking Driver Capabilities for database

March 8, 2023 by Roger·Comments Off

Product:

Planning Analytics 2.0.9.3

Microsoft Windows 2019 server

Issue:

In tm1server.log file there is reference to wrong ODBC connection. Like this

TM1.Process Process “second.process” run from process “main.process” by user “AD/donald”
TM1.SQLAPI Checking Driver Capabilities for database “TEST”
TM1.SQLAPI Driver supports SQLFetchScroll

Possible solution:

As the Data Source tab is pointing to the TEST odbc connection, a test connection is made and written in the log file.

Change the ODBC name in the Data Source tab to the expected ODBC connection like: PROD

You can still have the TI process change the ODBC connection to correct one in the prolog tab, with code like this:

#--------------------------------------------------
# Set source with values from variables you have defined before
#--------------------------------------------------
DataSourceType='ODBC';
DatasourceNameForClient=sODBCConnection;
DatasourceNameForServer=sODBCConnection;
DatasourceUsername=sUser;
DatasourcePassword=sPassword;
DatasourceQuery = sDataSourceQuery ;


#-----------------------------------------------------------------------------
# open the connection to the database ODBC connection
#-----------------------------------------------------------------------------
ODBCOpen(sODBCconnection, sUser , sPassword );

# this will change the ODBC connection, and the metadata and data tab will use the new ODBC source.

The DatasourceNameForServer= variable will set the ODBC source used by the process when run from a chore.

More Information:

https://www.ibm.com/support/pages/tm1sqlapi-01000microsoftodbc-driver-manager-cursor-library-not-used-load-failed

https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=variables-datasourcenameforserver

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tv-turbointegrator-local-variables

What did you say?

March 6, 2023 by Roger·Comments Off

Product:

Microsoft Windows

Issue:

Sometimes it is hard to explain a word over the phone – then you can use the phonetic alphabet to help out.

Solution:

Button does not work in TM1 WEB APPS

February 22, 2023 by Roger·Comments Off

Product:

Planning Analytics 2.0.9.3

Microsoft Windows 2019 server

Issue:

You have create a TM1 WEBSHEET in excel (Tm1 perspective) with a ACTION BUTTON that run a process.

This works fine in TM1WEB, but not in TM1 APP WEB (old contributor). When you in the web page (inside contributor session) click on the button icon, nothing happens.

Solution:

To be able to click on websheet buttons in Tm1 Application Web, you need to first take ownership.

Click on the icon take ownership.

Then the buttons in the websheet will work in the Tm1 App Web (contributor session).

When you are done, you need to relase the owner ship, so other can access that company node.

More information:

https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=applications-ownership-bouncing-releasing

Adding or editing data in the web client allows you to submit information to your datastore. To modify data, your system administrator must grant you access.

Data that you can edit has a white background. Read-only data has a gray background. If you are not the current owner, the data opens in a read-only view. To start adding or editing data or click on button, click Take Ownership Take ownership icon .

You can edit data only if it has a workflow state of Available Not started icon or Reserved Work in progress icon . The icons indicate the workflow state.

Ownership availability for a particular node can be changed depending on how the parent node is opened. For example, contributors and reviewers who open the parent node in IBM® Cognos® Insight are not able to take ownership of the node. See the TM1® Performance Modeler documentation and the Cognos Insight documentation for details on ownership and nodes.

After taking ownership, use the Release Release ownership icon icon to release the data so other people can use it . In Cognos TM1 Application Web, you must submit all nodes at the level at which you take ownership and you can only release ownership at the level you have taken ownership.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=applications-working-data

You can insert an Action button into a worksheet so users can run a TurboIntegrator process and/or navigate to another worksheet. Users can access these buttons when working with worksheets in Microsoft Excel with TM1, or with Websheets in TM1 Web.

The action buttons will work in TM1 application Web, too, even not stated in the IBM documentation.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=developers-using-tm1-action-buttons-build-worksheet-applications

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=excel-action-buttons

https://www.ibm.com/docs/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/tm1_inst.pdf

A fatal error occurred while creating a TLS client credential. The internal error state is 10013.

February 18, 2023 by Roger·Comments Off

Product:

Planning Analytics 2.0.9

Microsoft Windows 2019 server

Issue:

After change in Windows registry to prevent use of TLS 1.0 and TLS 1.1 communication, the ODBC driver to SQL server does not work.

ODBCOpen ( vSource, vClient, vPassword ); does not work.

Error can be: DCOM was unable to communicate with the computer SQLserver using any of the configured protocols; requested by PID

SQLState: 01000
SQL Server Error:1
Microsoft ODBC SQL Server Driver DBNETLIB ConnectionOpen SECCreateCredentials()
Connection Failed
SQLState: 08001
SQL Server Error:18
SSL Security Error

Solution:

Change the ODBC driver from Microsoft SQL Server ODBC Driver Version 10.00.14393 to a new, like Microsoft SQL Server Native Client Version 11.00.7462.

Backup the registry values under [HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\ODBC Data Sources], to be able to restore it.

Go to Control Panel – Administrative tools – ODBC Data Sources (32-bit), to add the new driver with the same name and selected database.

You can run below commands to set the values for disabled TLS 1.0 and TLS 1.1 on the server:

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" /v Enabled /t REG_DWORD /d 0 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" /v DisabledByDefault /t REG_DWORD /d 1 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client" /v Enabled /t REG_DWORD /d 0 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client" /v DisabledByDefault /t REG_DWORD /d 1 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server" /v Enabled /t REG_DWORD /d 0 /f 

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server" /v DisabledByDefault /t REG_DWORD /d 1 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client" /v Enabled /t REG_DWORD /d 0 /f 

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client" /v DisabledByDefault /t REG_DWORD /d 1 /f

More Information:

https://thesecmaster.com/how-to-enable-tls-1-2-and-tls-1-3-on-windows-server/

https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe

https://support.microsoft.com/en-us/topic/october-20-2020-kb4580390-os-build-17763-1554-preview-ac4799c9-838f-8665-a968-0f19b6cb1049

https://think.unblog.ch/en/how-to-use-tls-1-2-and-tls-1-3-on-windows-server/

https://support.site24x7.com/portal/en/kb/articles/how-to-check-if-tls-1-2-is-enabled