problems with SQLExecDirect (again)

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

flashgordon
Premium Member
Premium Member
Posts: 99
Joined: Tue Aug 17, 2004 7:50 am
Location: Boulder, Colorado

problems with SQLExecDirect (again)

Post by flashgordon »

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
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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 SQLExecDirect. That way lies utter confusion.

When analyzing the error, there are three possible handles where it might have occurred. Try them all.

Code: Select all

* Environment handle
ErrCode = SQLError(hEnv, SQL.NULL.HDBC, SQL.NULL.HSTMT, SQLState, DBMScode, ErrorText)
* Connection handle
ErrCode = SQLError(hEnv, hConn, SQL.NULL.HSTMT, SQLState, DBMScode, ErrorText)
* Statement handle
ErrCode = SQLError(hEnv, hConn, hStmt, SQLState, DBMScode, ErrorText)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
flashgordon
Premium Member
Premium Member
Posts: 99
Joined: Tue Aug 17, 2004 7:50 am
Location: Boulder, Colorado

Post by flashgordon »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I've never heard of BCI code knocking out the entire machine.

Here's how I prefer to log BCI errors.

Code: Select all

ErrCode = SQLError(hEnv,hConn,hStmt,SQLState,DBMScode,ErrText)
Message = "Error in ODBC interface"
Message<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMScode
Message<-1> = ErrText
Call DSLogWarn(Message, "SQL Client Interface")
Assign SQL.NULL.HDBC to hConn and SQL.NULL.HSTMT to hStmt as required.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
flashgordon
Premium Member
Premium Member
Posts: 99
Joined: Tue Aug 17, 2004 7:50 am
Location: Boulder, Colorado

Post by flashgordon »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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 usual to use CALL with ODBC to execute stored procedures. Try that instead of EXEC. Otherwise investigate using SQLSetConnectOptions() (from memory) to configure "pass through".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
flashgordon
Premium Member
Premium Member
Posts: 99
Joined: Tue Aug 17, 2004 7:50 am
Location: Boulder, Colorado

Post by flashgordon »

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
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Post by Teej »

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.)
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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 you want to go the BCI route you will need to provide your own ODBC drivers or license the Data Direct drivers. A word of warning - they're not cheap.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
flashgordon
Premium Member
Premium Member
Posts: 99
Joined: Tue Aug 17, 2004 7:50 am
Location: Boulder, Colorado

Post by flashgordon »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Post by Teej »

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.
flashgordon
Premium Member
Premium Member
Posts: 99
Joined: Tue Aug 17, 2004 7:50 am
Location: Boulder, Colorado

Post by flashgordon »

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
flashgordon
Premium Member
Premium Member
Posts: 99
Joined: Tue Aug 17, 2004 7:50 am
Location: Boulder, Colorado

Post by flashgordon »

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
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

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 series of SQL insert/update statements with literal data and output to a .sql file, then run sqlplus naming that .sql file. Just fetching data is easy - screen scrape into a variable and then parse away.
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
Post Reply