Product:
IBM DB2 Data Studio
Microsoft Windows 2022 server

Issue:

Error when starting DS4.1.2 client

An error has occurred, See the log file d:\program files\ibm\ds4.1.2\configuration\1747225616665.log

org.osgi.framework.BundleException: Unable to acquire the state change lock for the module: osgi.identity; osgi.identity=”org.eclipse.core.runtime”

Solution:

Before starting the DB2 studio client program

1. Go to your eclipse directory and open configuration directory. (can be D:\Program Files\IBM\DS4.1.2\configuration\org.eclipse.osgi\.manager )
2. open org.eclipse.osgi directory.
3. open .manager folder.
4. Delete .fileTableLock file.
5. Restart your DB2 data studio program.

Then start the program as “administrator”, right click on icon and select “run as administrator”.

and select the path to the folder to be something like d:\IBM\rationalsdp\workspace

Then you should be inside DB2 Data Studio, and see the list of database on the side.

More Information:

DB2 9.5 and IBM Data Studio: Building an SQL Statement

https://www.eclipse.org/forums/index.php/t/823236/

https://www.ibm.com/docs/en/db2/11.5.x?topic=objects-retrieving-data-from-tables-views 

https://www.raghu-on-tech.com/2020/02/29/db2-finding-top-10-most-active-tables/

Db2 Basics: Getting Data Out of Db2

Product:

Microsoft Windows 2022 server
DB2 driver

Issue:

Can not connect to DB2 server with ODBC, the dbalias is not found.

Solution:

Inside the ODBC program, you need to setup a alias.

This is controlled by the db2cli.ini file.

If you run a bat file to catalog the sql db2 settings this file will end up in your profile folder, e.g. c:\users\yourname

Copy the file to the common folder c:\programdata\ibm\db2\db2copy1\cfg

Search for db2cli.ini to find the folders to use. The db2cli.ini have lines like below:

[db2instance]
DBALIAS=db2instance
UID=donald
DESCRIPTION=db2instance - PROD

But first, you need to install the db2 drivers from IBM Data Server Client Packages (12.1, All platforms)

Run the setup.exe from inside folder \wib_data_server_client_winx64_v11.1\client\image.

Create a catalog.sql file with the content you need; (you must adjust to your environment)

UNCATALOG NODE db2instance;
CATALOG TCPIP NODE db2instance
REMOTE db2servername.domain.com
SERVER 50000
REMOTE_INSTANCE db2instance
OSTYPE LINUX;

The run a command like this from a bat file:

db2cmd db2setcp ” db2 -tvf C:\install\catalog.sql ”

This will give you the User DSN setup.

You can find the values in the registry, download to a reg file, and edit it to be HKLM as below;

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\db2instance]
"Driver"="C:\\PROGRA~1\\IBM\\SQLLIB\\BIN\\DB2CLIO.DLL"
"Description"="db2instance"

Load this file, and you will get it in System DNS.

Then you need to update the db2cli.ini in the folder c:\programdata\ibm\db2\db2copy1\cfg to get the dbalias.

If it does not work, check the links below.

More information:

https://www.ibm.com/docs/en/db2-warehouse?topic=installing-windows

https://www.ibm.com/support/pages/db2-odbc-cli-driver-download-and-installation-information

Installing and configuring an DB2 ODBC CLI-driver on Windows

https://www.ibm.com/docs/en/db2/11.5.x?topic=commands-catalog-database

https://help.hcl-software.com/bigfix/9.2/platform/Platform/Config/c_creating_db2_dsn.html

https://www.ibm.com/docs/en/db2/11.5.x?topic=odbc-cliodbc-configuration-keywords

https://www.columbia.edu/sec/acis/db2/db2iy/db2iy47.htm

https://www.ninjaone.com/blog/registry-editor/

https://www.techtarget.com/searchwindowsserver/tip/Command-line-options-for-Regeditexe

Product:

Microsoft Excel 365 32 bit version

Microsoft Windows 11

Issue:

Inside a excel report, you have a power query that get data from a other excel file on sharepoint.

When you do refresh to update you get a error.

Possible cause:

The Excel file have to many rows.

When using Excel, it’s important to note which file format you’re using. The .xls file format has a limit of 65,536 rows in each sheet, while the .xlsx file format has a limit of 1,048,576 rows per sheet. For more info, see File formats that are supported in Excel and Excel specifications and limits.

If you load a csv file, you may only get a part of the rows of data. And the sum result may not be what you expect, if you reach a limit in excel program.

More Information:

The Excel Row Limit is 1,048,576 Rows | Row Zero

Loading CSV/text files with more than a million rows into Excel – Jose Barreto’s Blog

https://flatfile.com/blog/top-6-csv-import-errors-and-how-to-fix-them/ 

https://flatfile.com/blog/why-isnt-my-csv-file-importing/

Product:
Microsoft SQL Azure

Issue:

How remove all rows of data from table when the date column is INT and contain both year and date?

Suggested solution:

In SQL SSMS write like this (to erase all rows with year 2024) :

DELETE FROM [DM].[dbtablename]
where 1=1
and SUBSTRING (( CAST ( [key_dimdate] AS varchar) ), 1 , 4 ) = '2024'

if the key_dimdate format is ‘20240112’ as a INT

More Information:

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

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16 

https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver16

Product:
Microsoft Power BI service Dataflow

Issue:

How change on-prem gateway from prod to dev?

Solution:

You have to click on EDIT for the dataflow.

Then click on EDIT TABLE.

Then on the HOME ribbon find OPTIONS.

Scroll down to Data Load, and select the on-prem gateway you should use in this dataflow.

To showcase how to change the gateway in a dataflow project, this article uses a query that connects to a local folder as an example.

This query previously used a gateway named “Gateway A” to connect to the folder. But “Gateway A” no longer has access to the folder due to new company policies. A new gateway named “Gateway B” is registered and now has access to the folder that the query requires. The goal is to change the gateway used in this dataflow project so it uses the new “Gateway B.”

To change the gateway:

1. From the Home tab in Power Query, select Options.

   

2. In the Options dialog box, select Data load, and then select the gateway to use for your project, in this case, Gateway B.

   

    Tip

   If there were recent changes to your gateways, select the small refresh icon to the right of the drop-down menu to update the list of available gateways.

3. After selecting the correct gateway for the project, in this case, Gateway B, select OK to go back to the Power Query editor.

To change the dataflow to use a different SQL view for the table of data, go to the navigation icon in the right list, and select a different view from the SQL database, from the list of view in the databases.

More Information:

https://learn.microsoft.com/en-us/power-query/change-gateway-dataflow 

Product:

Microsoft SQL Azure

Issue:

What user have the db_owner role?

Solution:

Start SSMS and login to your SQL azure database, and run below script (found on internet)

SELECT CASE princ.[type]
WHEN 'S' THEN
princ.[name]
END AS [UserName],
CASE princ.[type]
WHEN 'S' THEN
'SQL User'
WHEN 'U' THEN
'Windows User'
END AS [UserType],
princ.[name] AS [DatabaseUserName],
NULL AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc], 
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--database user
sys.database_principals AS princ
LEFT JOIN
--Permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
LEFT JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
WHERE princ.[type] IN ( 'S', 'U' )
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT CASE memberprinc.[type]
WHEN 'S' THEN
memberprinc.[name]
END AS [UserName],
CASE memberprinc.[type]
WHEN 'S' THEN
'SQL User'
WHEN 'U' THEN
'Windows User'
END AS [UserType],
memberprinc.[name] AS [DatabaseUserName],
roleprinc.[name] AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc], 
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--Role/member associations
sys.database_role_members AS members
JOIN
--Roles
sys.database_principals AS roleprinc
ON roleprinc.[principal_id] = members.role_principal_id
JOIN
--Role members (database users)
sys.database_principals AS memberprinc
ON memberprinc.[principal_id] = members.member_principal_id
LEFT JOIN
--Permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
LEFT JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT '{All Users}' AS [UserName],
'{All Users}' AS [UserType],
'{All Users}' AS [DatabaseUserName],
roleprinc.[name] AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc], 
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--Roles
sys.database_principals AS roleprinc
LEFT JOIN
--Role permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
JOIN
--All objects 
sys.objects AS obj
ON obj.[object_id] = perm.major_id
WHERE
--Only roles
roleprinc.[type] = 'R'
AND
--Only public role
roleprinc.[name] = 'public'
AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY princ.[name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.permission_name,
perm.state_desc,
obj.type_desc; --perm.[class_desc]

More information:

https://www.scarydba.com/ 

Product:
Microsoft Power BI portal / service

Issue:

How move a dataflow gen1 from one workspace to a another workspace?

Solution:

From source workspace, click on the 3 dots … and select export json file.

Save the file on your hard disk.

Change the powerbi workspace to the new workspace where you want to import the dataflow

Click on “new item” and select Dataflow Gen1

Click on “import model”

Select the json file you have saved – and click open.

When the dataflow is imported, you need to update the credentials to connect to the data source.

Click on the 3 dots … and select edit.

Click on the edit table icon for one of the tables in the list.

You may get a message like: Credentials are required to connect to the SQL source.

Click on options.

If you use a power bi query gateway to access your SQL database, you need to select it at data load, and click OK.

Click Save and close.

Now it should work to update (refresh) your dataflow.

If it does not help, you may need to go to manage connections, and try to set a different user to access the data source.

More Information:

https://forum.enterprisedna.co/t/moving-data-flows-and-report-to-a-new-workspace/22166 

Move dataflows across workspaces with the Power BI REST API

https://www.phdata.io/blog/migrating-queries-from-power-bi-desktop-to-dataflows/ 

Product:
Microsoft PowerBI desktop

Issue:

A column in a table is not sorted correct, it sorted alphabetical and not by date. Even do the column should show a date format.

Solution:

Inside PowerBI Desktop, go to the table view.

Select the column (that you want change the sort order for) and click on icon for “sort by column”.

In the dropdown menu, select the column who have a correct format, in our example the date column.

Now check the table in Report view.

You need to repeat this for all columns, in your report in PowerBI for Desktop.

More Information:

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column

https://www.techrepublic.com/article/how-to-sort-by-column-power-bi/ 

https://help.zebrabi.com/kb/power-bi/add-a-sorting-column/ 

Product:

Microsoft Power BI desktop
Microsoft Windows

Issue:

Error when you try to add a measure in the visualizations list of data.

Solution:

Try to add a new column instead. The icons tell what type of value it is.

More Information:

Another important difference between measures and calculated columns is that measures are evaluated in the filter context of the visual in which they are applied. The filter context is defined by the filters applied in the report such as row selection, column selection, report filters and slicers applied. Measures are only evaluated at the level of granularity they are plotted at. As calculated columns are computed when you first define them/ when you refresh your dataset, they do not have access to the filter context. Calculated columns are calculated outside of the filter context and do not depend on user interaction in the report.

When you write a calculated column, you need to rely only on the row context. The row context, is simply the notion of a current row. It specifies which row we are calculating the values for, so that DAX can look at other values in the same row. In a calculated column, the row context is implied. When you write an expression in a calculated column, the expression is evaluated for each row of the table. The calculated column has knowledge of the current row.

By contrast, measures implicitly do not have a row context. This is because, by default, they work at the aggregate level. So, you cannot refer to columns directly in a DAX measure, you will get an error because no row context exists. This is because the measure will not know which row to choose in the table

https://endjin.com/blog/2022/04/measures-vs-calculated-columns-in-dax

Measures vs. Calculated Columns in Power BI

https://www.thedataschool.com.au/mipadmin/the-differences-between-new-measure-and-new-column-in-power-bi/ 

https://biinsight.com/define-measure-table-power-bi-desktop/ 

https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/

Product:
Planning Analytics 2.0.9.19
Microsoft Windows 2019 server
Google Chrome

Issue:
The background image in TM1WEB is changed on the server – but the change is not shown on end users web browsers.

Solution:

On many browsers, pressing Control-F5 will reload the page and bypass the cache. The Wikipedia “Bypass your cache” page has details for all modern web browsers.

Or you can change the name of the file PA_header.svg in folder D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\images\login to something else. Then you need to update all code that calls that file – can be this css-html files for tm1web:

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\carbon.css

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\all.css

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\LoginDialog.css

Or clean the Web Browser cache at the end users laptop.

More Information:

https://medium.com/@gulshan_n/overcoming-persistent-image-caching-how-to-ensure-visitors-see-your-latest-website-changes-cd89a1434e1a 

https://www.avast.com/c-how-to-clear-cache

Resurrecting images from a web browser cache can be a useful way to recover lost or deleted images that you previously viewed online. Here’s a step-by-step guide on how to do this for various web browsers:

Google Chrome

1. Access Cache:
   – Type chrome://cache in the address bar and press Enter. This will take you to a list of cached files.
2. Find Images:
   – You can’t view the images directly, but you can see the URLs. To find images, you can also use a tool like ChromeCacheView (a third-party utility) that allows you to view and extract images from the cache more easily.
3. Extract Images:
   – If using ChromeCacheView, download and run it. It will display cached files, including images. You can select the images you want to save and extract them to your computer.

Mozilla Firefox

1. Access Cache:
   – Type about:cache in the address bar and press Enter. This will show you the disk and memory cache information.
2. Find Images:
   – Look for the section that lists the cache entries. You can find images by checking the file type and URL.
3. Extract Images:
   – You can use Mozilla’s cache viewer or a third-party tool like Firefox Cache Viewer to extract images more conveniently.

Microsoft Edge

1. Access Cache:
   – Type edge://cache in the address bar and press Enter. This will show you cached items.
2. Find Images:
   – Similar to Chrome, you might need a third-party tool like EdgeCacheView to find and extract images.

Safari

1. Access Cache:
   – For Safari, the cache is not easily accessible like in other browsers. You can use Terminal commands or third-party tools.
2. Find Images:
   – You can also look in the ~/Library/Caches/com.apple.Safari/ directory to find cached files.
3. Extract Images:
   – Use a third-party application like SafariCache to help retrieve images from the cache.

General Tips

• Look for File Types: When browsing the cache, look specifically for file types like .jpg, .png, or .gif.
• Use Third-Party Tools: Tools like WebCacheImage, ChromeCacheView, and others can simplify the process.
• Browser Extensions: Some extensions can help manage and view cached files directly from the browser.

https://www.linkedin.com/pulse/11-ways-fix-google-chrome-loading-images-benjamin-ladegbaye 

How to refresh cached images and files in Chrome