PA 2.0.9.14 and changed behaviour with ODBC!

Post Reply
JohnO
Posts: 92
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

PA 2.0.9.14 and changed behaviour with ODBC!

Post by JohnO »

This is an FYI only.

I installed 2.0.9.14 (Previously 2.0.9.11) in our dev instance and tried to open an existing ODBC sourced TI but it could not open the connection. "unable to open connection to database"

What IBM have come back with is:

"Based on the discussion from our dev team - There was a change since 2.0.9.13 that we now use ODBC 3.x. Some older ODBC drivers will not work with ODBCOpen and must use ODBCOpenEx"

"This APAR was directly related to this issue - https://www.ibm.com/support/pages/apar/PH46750"

I don't see anything in the release notes in relation to this but in summary the issue is that we now need to use Unicode and ODBCOpenEx meaning all such processes need to be amended and tested.

Unless I am misunderstanding something I think this may potentially be a big issue for many.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: PA 2.0.9.14 and changed behaviour with ODBC!

Post by Steve Rowe »

Thanks for raising JohnO.

Have IBM come back with detail on the required config changes to make this happen?

I assume "use Unicode" must be checked on the face of the TI and if opening the ODBC connection manually using ODBCOpen you need to use ODBCOpenEx instead?

Cheers,
Steve
Technical Director
www.infocat.co.uk
JohnO
Posts: 92
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

Re: PA 2.0.9.14 and changed behaviour with ODBC!

Post by JohnO »

Steve Rowe wrote: Tue Jun 14, 2022 2:49 pm Thanks for raising JohnO.

Have IBM come back with detail on the required config changes to make this happen?

I assume "use Unicode" must be checked on the face of the TI and if opening the ODBC connection manually using ODBCOpen you need to use ODBCOpenEx instead?

Cheers,
Steve
Yes they are the required actions. If you know this has to be done then it can be planned for. I imagine they will get a lot of questions when people find their existing TI's don't work anymore.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: PA 2.0.9.14 and changed behaviour with ODBC!

Post by gtonkin »

This does not sound right - We have noticed massive performance degradation when enabling Unicode on some queries/sources.
Posting this to to possibly help in some way but to keep track of developments.

Besides ODBCOpenEx you could try SetODBCUnicodeInterface to set the flag in the TI code. Also have a look at CharW and CodeW, not documented but supported in Architect and PAW to convert to and from wide character/code if needed.
JohnO
Posts: 92
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

Re: PA 2.0.9.14 and changed behaviour with ODBC!

Post by JohnO »

gtonkin wrote: Wed Jun 15, 2022 5:57 am This does not sound right - We have noticed massive performance degradation when enabling Unicode on some queries/sources.
Posting this to to possibly help in some way but to keep track of developments.

Besides ODBCOpenEx you could try SetODBCUnicodeInterface to set the flag in the TI code. Also have a look at CharW and CodeW, not documented but supported in Architect and PAW to convert to and from wide character/code if needed.
The error appears when opening the TI, so it cannot do a preview without ticking unicode. And the suggestion from IBM was to use
ODBCOpenEx.

Using Unicode hasn't had a performance impact for us, it's still twice as slow as a comparable text load. And to follow up on another of my posts, using runprocess for these ODBC sources will not result in parallel execution, one of the things I have also tried with .14 Using TM1RUNTI does support parallel runs for the ODBC sources, something to note is that it appears TM1RUNTI will not be in the new engine.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: PA 2.0.9.14 and changed behaviour with ODBC!

Post by Steve Rowe »

Hi John,

It seems like ODBC 3.0 has been around since 1995. (https://en.wikipedia.org/wiki/Open_Data ... on_history)

I'm struggling to find much reference material relating to common ODBC drivers and the ODBC version they are built against.

Out of interest what is the source application that is not working for you?

Cheers,
Steve
Technical Director
www.infocat.co.uk
JohnO
Posts: 92
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

Re: PA 2.0.9.14 and changed behaviour with ODBC!

Post by JohnO »

Steve Rowe wrote: Wed Jun 15, 2022 10:23 am Out of interest what is the source application that is not working for you?
SAP Hana and Oracle (ADW)
shahhere
Posts: 10
Joined: Thu Mar 03, 2022 7:13 pm
OLAP Product: IBM PA
Version: 2.0.9.12
Excel Version: O365

Re: PA 2.0.9.14 and changed behaviour with ODBC!

Post by shahhere »

We are having the same issue after upgrade (sadly done in PROD :(). Our connection is to SQL where this is affecting.

Shahhere
shahhere
Posts: 10
Joined: Thu Mar 03, 2022 7:13 pm
OLAP Product: IBM PA
Version: 2.0.9.12
Excel Version: O365

Re: PA 2.0.9.14 and changed behaviour with ODBC!

Post by shahhere »

JohnO wrote: Wed Jun 15, 2022 9:27 am
gtonkin wrote: Wed Jun 15, 2022 5:57 am This does not sound right - We have noticed massive performance degradation when enabling Unicode on some queries/sources.
Posting this to to possibly help in some way but to keep track of developments.

Besides ODBCOpenEx you could try SetODBCUnicodeInterface to set the flag in the TI code. Also have a look at CharW and CodeW, not documented but supported in Architect and PAW to convert to and from wide character/code if needed.
The error appears when opening the TI, so it cannot do a preview without ticking unicode. And the suggestion from IBM was to use
ODBCOpenEx.

Using Unicode hasn't had a performance impact for us, it's still twice as slow as a comparable text load. And to follow up on another of my posts, using runprocess for these ODBC sources will not result in parallel execution, one of the things I have also tried with .14 Using TM1RUNTI does support parallel runs for the ODBC sources, something to note is that it appears TM1RUNTI will not be in the new engine.
There were tons of issues with 2.0.9.14 and reverting back to 2.0.9.13 did not help. Finally version 2.0.9.12 seems to be stable enough for us with the same issues you noted here and a few more with 14/13.

version 14/13: All of our SQL Query ODBC processes were either not opening unless we check "use unicode" and many existing queries were running extremely slow or only pulling in 50 records at a time. Another issue we noticed is that Active form reports were not generating the ending columns in perspectives depending on how many other non DBR/DBRW columns you had in-between (nothing to do with the data ranges fyi as these were existing reports).

Rolling back to version 12: resolved all of the ODBC issues with Oracle and SQL but had to uncheck "use unicode" and that made them run relatively fast. Reporting issues did not exist with the columns in this version as well.

IBM acknowledged the issues but who knows how long a fix would take.

Shahhere
Post Reply