DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
stuartjvnorton
Participant



Joined: 19 Apr 2007
Posts: 516
Location: Melbourne
Points: 3846

Post Posted: Thu Apr 12, 2018 6:07 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54166
Location: Sydney, Australia
Points: 293772

Post Posted: Fri Apr 13, 2018 5:53 pm Reply with quote    Back to top    

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.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
stuartjvnorton
Participant



Joined: 19 Apr 2007
Posts: 516
Location: Melbourne
Points: 3846

Post Posted: Sun Apr 15, 2018 10:26 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42474
Location: Denver, CO
Points: 218484

Post Posted: Mon Apr 16, 2018 7:10 am Reply with quote    Back to top    

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

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
stuartjvnorton
Participant



Joined: 19 Apr 2007
Posts: 516
Location: Melbourne
Points: 3846

Post Posted: Tue Apr 17, 2018 12:49 am Reply with quote    Back to top    

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! Wink

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...
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54166
Location: Sydney, Australia
Points: 293772

Post Posted: Tue Apr 17, 2018 6:34 am Reply with quote    Back to top    

Idle thought - can you create a Virtual Column in IA that refers somehow to the column with the "/" in its name?

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42474
Location: Denver, CO
Points: 218484

Post Posted: Tue Apr 17, 2018 7:04 am Reply with quote    Back to top    

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

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours