DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
flashgordon



Group memberships:
Premium Members

Joined: 17 Aug 2004
Posts: 99
Location: Boulder, Colorado
Points: 1188

Post Posted: Tue Jan 08, 2008 5:04 pm Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Server
OS: Unix
Additional info: more .dsrel 7.5.1.35, Oracle 10g
Hi,

I read the good postings on SQLExecDirect, they gave me some good things to try, but I'm still not fixing our problem. High level bits - our ODBC against Oracle 10g is working, I can import metadata. When we try execute an Oracle command with SQLExecDirect in a "Before" routine, it fails. The DB connect commands seem to work. Any kind of of Oracle command seems to fail with the same error message (100, I think this is an ODBC.H error message) in SQLExecDirect, selects, exec's, desc's, commands with bad syntax, commands with good syntax that run in Toad. The code in full debug mode is shown below. Some important entries from the log file are shown directly below:

Reply from calling SQLAllocStmt
test..BeforeJob (Reporting): Successfully called SQLExecDirect

Reply from analyzing the Error return code
test..BeforeJob (): status of SQL100

Reply from analyzing the SQLExecDirect Call
est..BeforeJob (Reporting): Error calling SQLExecDirect.
*** the Loop analyzing the return code gave nothing ***

Here is the code in full debug mode. The Oracle command we wanted to run was the update statement:

status = SQLAllocStmt(hConn, hStmt)
Begin Case
Case status = SQL.SUCCESS
Call DSLogInfo("Successfully called SQLExecDirect", "Reporting")
Case status = SQL.SUCCESS.WITH.INFO
Msg = "Successfully called SQLExecDirect with information."
Temp = SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Msg<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMSCode
Msg<-1> = ErrText
Call DSLogInfo(Msg, "Reporting")
Case @TRUE
Msg = "Error calling SQLExecDirect."
Temp = SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Loop
While Temp <> SQL.NO.DATA.FOUND And Temp <> SQL.ERROR
Msg<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMSCode
Msg<-1> = ErrText
Repeat
Call DSLogWarn(Msg, "Reporting")
End Case

** Stmt= "UPDATE " :schema:".PS_NW_EPM_JOB_STAT SET JOB_START_DTTM = SYSDATE, NW_ETL_JOB_STATUS = 'P' WHERE ETL_JOB_NAME =" :"'":jobName:"'"
** Stmt = "select * from sysadm.ps_ledger where rownum < 2"
Stmt = "exec sysadm.NW_PS_NW_DBALEA_F_SR('27NOV2007')"
Call DSLogInfo("request SQL: " : Stmt, "NWETLJobStartTom")

status = SQLExecDirect(hStmt, Stmt)
ErrorCode=SQLError(hEnv,hConn,hStmt,SQLstate,DBMSCode,ErrText)
Call DSLogInfo("status of SQL":SQLError(hEnv,hConn,hStmt,SQLstate,DBMSCode,ErrText),SQLstatus)
Begin Case
Case status = SQL.SUCCESS
Call DSLogInfo("Successfully called SQLExecDirect", "Reporting")
Case status = SQL.SUCCESS.WITH.INFO
Msg = "Successfully called SQLExecDirect with information."
Temp = SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Msg<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMSCode
Msg<-1> = ErrText
Call DSLogInfo(Msg, "Reporting")
Case @TRUE
Msg = "Error calling SQLExecDirect."
Temp = SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Loop
While Temp <> SQL.NO.DATA.FOUND And Temp <> SQL.ERROR
Msg<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMSCode
Msg<-1> = ErrText
Repeat
Call DSLogWarn(Msg, "Reporting")
End Case

_________________
Flash Gordon
Hyperborean Software Solution
ray.wurlod

Premium Poster
Participant

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

Joined: 23 Oct 2002
Posts: 54595
Location: Sydney, Australia
Points: 296053

Post Posted: Tue Jan 08, 2008 7:41 pm Reply with quote    Back to top    

You should enclose your code in Code tags, to preserve the indenting and make it easier to understand. You should not report the status returned from SQLAllocStmt as having been returned from SQL ...

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Rate this response:  
Not yet rated
flashgordon



Group memberships:
Premium Members

Joined: 17 Aug 2004
Posts: 99
Location: Boulder, Colorado
Points: 1188

Post Posted: Fri Jan 11, 2008 1:42 pm Reply with quote    Back to top    

Ray,

Thanks. I'm trying some of your options. Obviously I need a way to see the return code so I'm calling a Log posting after the line you suggested. Is this the right way to do that?

ErrCode = SQLError(hEnv, SQL.NULL.HDBC, SQL.NULL.HSTMT, SQLState, DBMScode, ErrText)
Call DSLogInfo("status of SQL":SQLError(hEnv,SQL.NULL.HDBC, SQL.NULL.HSTMT,SQLstate,DBMSCode,ErrText),SQLstatus)

I am moderately certain some combination of this routine (it wasn't your code suggestions) was knocking the whole Datastage Server down. It happened 3 times. It was kind of upsetting when it happened and I had a reason to need to fix it quickly, so I don't have the code combination any more. Have you ever heard of a before routine with an oracle odbc link knocking down all of Datastage?

... Flash

_________________
Flash Gordon
Hyperborean Software Solution
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: 54595
Location: Sydney, Australia
Points: 296053

Post Posted: Fri Jan 11, 2008 3:20 pm Reply with quote    Back to top    

I've never heard of BCI code knocking out the entire machine. Here's how I prefer to log BCI errors. Code: ErrCode = SQLError(hEnv,hConn,hStmt,SQLState,DBMScode,ErrText) Message ...

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Rate this response:  
Not yet rated
flashgordon



Group memberships:
Premium Members

Joined: 17 Aug 2004
Posts: 99
Location: Boulder, Colorado
Points: 1188

Post Posted: Fri Jan 11, 2008 5:29 pm Reply with quote    Back to top    

Ray,

All 3 iterations came out with this.

test..BeforeJob (SQL Client Interface): Error in ODBC interface
SQL State Ray = 00000, DBMS code = 5549056

5549056 doesn't seem like an Oracle return code. Any thoughts?

... Flash

_________________
Flash Gordon
Hyperborean Software Solution
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: 54595
Location: Sydney, Australia
Points: 296053

Post Posted: Fri Jan 11, 2008 6:22 pm Reply with quote    Back to top    

You never know with exec. Could be the address of a pointer to a cursor structure, or something. Try something simpler, like SELECT COUNT(*) FROM DUAL and build up from there. It's more usu ...

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Rate this response:  
Not yet rated
flashgordon



Group memberships:
Premium Members

Joined: 17 Aug 2004
Posts: 99
Location: Boulder, Colorado
Points: 1188

Post Posted: Tue Jan 22, 2008 1:21 pm Reply with quote    Back to top    

Ray,

Just to try something, I deliberately made a bad sql update statement in my before job. When I did the error message became more explicit. This is what it said:

est..BeforeJob (SQL Client Interface): Error in ODBC interface
SQL State = S1000, DBMS code = 6091
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle driver]
The DataDirect ODBC driver you are attempting to access has been
provided to you by Ascential Software for exclusive use with
Ascential Data Integration Suite. You are not licensed to
use this driver with any application other than Ascential
Data Integration Suite. If you would like to purchase a
driver for some other use, please call 800-876-3101 or
visit DataDirect at www.datadirect.com

Does this give us any hints why a valid sql statement wouldn't run?

... Flash

_________________
Flash Gordon
Hyperborean Software Solution
Rate this response:  
Not yet rated
Teej
Participant



Joined: 08 Aug 2003
Posts: 677
Location: USA
Points: 4844

Post Posted: Tue Jan 22, 2008 1:55 pm Reply with quote    Back to top    

Wait, are you using a BeforeJob command line call using ODBC libraries that was provided by DataDirect as part of the DataStage package?

If so, you can't. It is not allowed as per the license provided from my understanding. The libraries are meant to only be used within the ODBC stages, although it is possible to test the connections using DataDirect's test program (example, if I recall correctly, can verify if needed.)
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: 54595
Location: Sydney, Australia
Points: 296053

Post Posted: Tue Jan 22, 2008 6:00 pm Reply with quote    Back to top    

The Data Direct drivers are not meant to be used with BCI, only from stages. They are licensed that way. What's really annoying is that they work OK for 30 days before giving this message. If y ...

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Rate this response:  
Not yet rated
flashgordon



Group memberships:
Premium Members

Joined: 17 Aug 2004
Posts: 99
Location: Boulder, Colorado
Points: 1188

Post Posted: Tue Jan 22, 2008 7:14 pm Reply with quote    Back to top    

Teej/Ray,

Thanks I think you guys got it. Makes sense. This ran for a while on two different datastage machines and then stopped working. It stopped working on the later datastage install more recently.

Ascential isn't building any loyal customers with the obscure error message. I don't why they just don't say directly "You cannot use this odbc license directly without paying for it". The message hints ("You are attempting to access") that it is denying access but doesn't say it directly. It took me 2 weeks to figure this out. With Dsxchange Premium everyone sees code that works and wants to use it.

... Flash

_________________
Flash Gordon
Hyperborean Software Solution
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: 54595
Location: Sydney, Australia
Points: 296053

Post Posted: Tue Jan 22, 2008 8:42 pm Reply with quote    Back to top    

That's a good point. Will include a rider about the Data Direct drivers in any BCI code posted in future.

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Rate this response:  
Not yet rated
Teej
Participant



Joined: 08 Aug 2003
Posts: 677
Location: USA
Points: 4844

Post Posted: Tue Jan 22, 2008 9:09 pm Reply with quote    Back to top    

The problem is that the drivers themselves are making those messages and have this restriction. DataStage got something, and just simply dumped the message to a designated log location.

It's the best way to maximize performance -- just go directly to the tools, and let it rip, and dump any output from those tools. That is what ExecSH (or ExecDOS for the Windows version) is all about -- run something like it is from the command line.

I am also pretty sure that somewhere within the legalese documents, there is a line or two noting that the drivers are not to be used for anything else.

DataDirect want to make money, after all. Nothing's wrong with that. However, there are indeed free drivers available on a quick search on Google, so that's an option if you need this to be done.
Rate this response:  
Not yet rated
flashgordon



Group memberships:
Premium Members

Joined: 17 Aug 2004
Posts: 99
Location: Boulder, Colorado
Points: 1188

Post Posted: Tue Jan 22, 2008 9:27 pm Reply with quote    Back to top    

Teej/Ray,

It may be obvious to you guys that I was using the drivers directly and you set me straight. But to poor smuck me, I was using a licensed datastage product with database calls that were in the Ascential documentation and accepted by the compiler. When I read the error message, I thought they meant I couldn't write an outside of Datastage C++/perl program with Data Direct ODBC calls in it. I didn't know they were talking about the call I had just made.

I agree Data Direct deserves to make money. They'd make more money if they told IBM/Ascential to put in a message that says "Your trial period is over, you have to pay to use Data Direct ODBC from here forward." I could have bought the product for all the time me and our expensive consultants spent on this.

... Flash

_________________
Flash Gordon
Hyperborean Software Solution
Rate this response:  
Not yet rated
flashgordon



Group memberships:
Premium Members

Joined: 17 Aug 2004
Posts: 99
Location: Boulder, Colorado
Points: 1188

Post Posted: Tue Jan 22, 2008 9:38 pm Reply with quote    Back to top    

And by the way, as slow as I was getting back to you on this stuff, you beat the formal support in figuring out what this was.

... Tom

_________________
Flash Gordon
Hyperborean Software Solution
Rate this response:  
Not yet rated
kcbland

Premium Poster
Participant

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

Joined: 15 Jan 2003
Posts: 5209
Location: Lutz, FL
Points: 39192

Post Posted: Tue Jan 22, 2008 10:28 pm Reply with quote    Back to top    

You could always switch to using DSExecute to run command line sqlplus statements to fetch/dml data against Oracle. You just formulate your queries within DS BASIC. One method is to generate a serie ...

_________________
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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