Cannot profile columns with a "/" in the name

This forum contains ProfileStage posts and now focuses at newer versions Infosphere Information Analyzer.

Moderators: chulett, rschirm

Post Reply
stuartjvnorton
Participant
Posts: 527
Joined: Thu Apr 19, 2007 1:25 am
Location: Melbourne

Cannot profile columns with a "/" in the name

Post by stuartjvnorton »

I am attempting to run column analysis on SAP HANA.
We have:
- connected to it via ODBC through the HANA driver
- and have successfully imported and shared metadata through IMAM
- created an IA project and associated the data

When it comes to the column analysis, some of the tables were successfully profiled, but some had errors.
Upon investigation, I found that the SQL that IA generates has issues when slashes are contained within the column names.

From SystemOut.log:
[4/12/18 12:54:53:745 AEST] 00000085 SorcererServi I CDIIA0001I: PXBridgeHelper.java:588 select statement to be executed as part of this PX Job is: select
DIMID,
/B28/S_A9DPSV0 as IA_ALIAS_1
from SAPPB1."/B28/DA9INS7X1"
...
Job 634: BaseProfile1_1523501691188_d70c6594_80cb2b5c_0ab65du6k_mtudqth_jl6q9q_189s5ka4ailo4doomrg0m failed, please verify that the job is submitted correctly. DS job Number is RT_SC4157.
Detailed Log:
Event 1: pxbridge(0): ODBC function "SQLPrepare" reported: SQLSTATE = 42000: Native Error Code = 257: Msg = [SAP AG][LIBODBCHDB SO][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "/": line 3 col 1 (at pos 17) (CC_OdbcDBStatement::dbsPrepare, file CC_OdbcDBStatement.cpp, line 362)

You can see that the table name is quoted for the slashes, but the column name is not.

Has anyone come across this before?

TIA.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I haven't seen this, but I haven't needed to. In your position I'd ask IBM support whether this is a known issue and, if so, whether a patch exists for it.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
stuartjvnorton
Participant
Posts: 527
Joined: Thu Apr 19, 2007 1:25 am
Location: Melbourne

Post by stuartjvnorton »

Hey Ray,

I raised a PMR but they just came back with "SAP HANA is not supported".
This isn't just a HANA problem. I've seen this sort of thing in SQL Server in the past, and SQL Server is obviously supported.

The silly thing is that the code that generates the SQL recognises that there is something non-standard about the name, because it tries to create an alias for the field. It just doesn't quote the field so the SQL is still no good.

I was hoping there's some setting or environment variable living in the bowels of IIS that would cause it to quote the column names the same way it is currently quoting table names.
I don't suppose you know of one I could try out?

Cheers,
Stuart.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Sounds like you may need to fall back on the workaround of defining a view over the table, one without those pesky characters in the column names.
-craig

"You can never have too many knives" -- Logan Nine Fingers
stuartjvnorton
Participant
Posts: 527
Joined: Thu Apr 19, 2007 1:25 am
Location: Melbourne

Post by stuartjvnorton »

Hi Craig,

Yeah, we've resigned ourselves to that fact too.

We were hoping to avoid that, as:
- we don't own the database so only have read-only access to it.
- the issue affects around 14k of the 50k tables in the database. 8-(

I'd better get started then! ;-)

PS: The thing that annoys me is that if I recreate the scenario in Oracle, IA adds the quotes around column names and it works just fine...
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Idle thought - can you create a Virtual Column in IA that refers somehow to the column with the "/" in its name?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Another Idle Thought and Pet Peeve Alert. Anyone who designs databases with special characters like that in object names should be severely... disciplined. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
stuartjvnorton
Participant
Posts: 527
Joined: Thu Apr 19, 2007 1:25 am
Location: Melbourne

Post by stuartjvnorton »

Had to go with the views, unfortunately.
HANA is not on Information Server's list of ODBC supported databases.
Post Reply