PA 2.0.9.14 and changed behaviour with ODBC!

Post Reply
JohnO
Posts: 32
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: 2286
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: 32
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: 1056
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.10; PAW 2.0.71
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: 32
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: 2286
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: 32
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)
Post Reply