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

Post by flashgordon »

Gents,

This is Cardinal Richileue. Unfortunately this twisted tale is not over. I am working this problem on two large Sun boxes. I got everything working on one box and then I moved the two lines of bci code over to the other box. The two new lines of bci code did not work on the other box. It appears that the one time removal of this line from .odbc.ini is what makes the license work:

QEWSD=39485

When I removed this line from .odbc.ini on the 2nd box, the bci code started working, with or without the 2 new lines of bci code. It is completely bizarre because after you remove QEWSD=39485 and run a job, Datastage puts the line QEWSD=39485 back in .odbc.ini. But after this one act of removal, bci before routines work with the data direct drivers from then on.

Sometime we'll have to get a IBM/Datastage employee in a bar to explain why the license expires and you have to do this voodoo to make it work again. If the explanation isn't good we will be justified in performing physical violence.

So I assume the point of this is to get the people searching to topic to the right point and I think this is the final point.

... Flash
Flash Gordon
Hyperborean Software Solution
casedwgroup
Premium Member
Premium Member
Posts: 24
Joined: Mon Aug 20, 2007 1:17 pm

Post by casedwgroup »

Where exactly do those 2 lines you posted need to be added? I am a little unclear as to how to implement this solution...
flashgordon
Premium Member
Premium Member
Posts: 99
Joined: Tue Aug 17, 2004 7:50 am
Location: Boulder, Colorado

Post by flashgordon »

Hi,

I'm glad you asked. I owe it to anyone with this problem to be really clear how to fix it. This is what I think worked and you probably have to be dsadm to edit .odbc.ini

1) Put these two lines in your bci program and compile it:

DEFFUN DSAttachODBCDriver(hDBC)
Status = DSAttachODBCDriver(hDBC)


2) then remove the following line from the odbc set you are referencing in in your .odbc.ini file in $DSHOME

QEWSD=39422

3) Then run your bci program with Datastage

After you run your bci program you will probably notice that QEWSD=39422 is back in .odbc.ini. In my case, I could still run bci programs with Data Direct calls after the 3 steps above were done once. My experience was, that once I did the steps above, a bci program with Data Direct calls always worked, even when the bci program didn't have the two lines in step 1 above.

I apologize for this being confusing. A little birdie whispered in my ear that this may be an unsanctioned solution that I got because I have a support contract and I work for a company the vendor wants to make happy. I hope you are able to use this to solve your situation.

... 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 »

Two things to add to what I said above.

I just saw some evidence you have to set the Data Direct drivers to work on a project by project basis. You have to do the 3 steps above for every project you want to use the Data Direct Drivers in. It doesn't work for all the projects on the server if you do the 3 steps once.

Also I didn't say where to put the 2 lines in the bci code. Below is where I put them (above the alloc statement)

$INCLUDE UNIVERSE.INCLUDE ODBC.H

** Initialise parameters ODBC

henv = SQL.NULL.HENV
hConn = SQL.NULL.HDBC
hstmt = SQL.NULL.HSTMT
password = Field(InputArg,",",3)
userName = Field(InputArg,",",2)
dataSource = Field(InputArg,",",1)
jobName = Field(InputArg,",",4)
schema = Field(InputArg,",",5)

Call DSLogInfo("userName " : userName, "NWETLJobStart")
Call DSLogInfo("dataSource ": dataSource, "NWETLJobStart")
Call DSLogInfo("jobName " : jobName, "NWETLJobStart")
Call DSLogInfo("Schema " : schema, "NWETLJobStart")

** do not remove these lines, these lines make this work with the odbc driver license
DEFFUN DSAttachODBCDriver(hDBC)
Status = DSAttachODBCDriver(hDBC)

** Connect to DataSource

status = SQLAllocEnv(hEnv)
status = SQLAllocConnect(hEnv, hConn)
status = SQLConnect(hConn, dataSource, userName , password )
Flash Gordon
Hyperborean Software Solution
casedwgroup
Premium Member
Premium Member
Posts: 24
Joined: Mon Aug 20, 2007 1:17 pm

Post by casedwgroup »

Thanks for the clarification!
flashgordon
Premium Member
Premium Member
Posts: 99
Joined: Tue Aug 17, 2004 7:50 am
Location: Boulder, Colorado

Post by flashgordon »

Sorry but I have to add one more caveat to this sordid tale. What is described above works but it only works temporarily. for se 7.5.1a and solaris 5.10 Generic_125100-10 sun4u sparc the above works but it will only work for a few days or a few weeks and then you have to do the operations describe above to get it working again. This makes the above solution non-workable for a production environment. I am going to speculate below what causes BCI data direct odbc calls to fail after the above actions are taken.

- it is NOT stopping and restarting datastage. I had bci odbc calls work through a datastage -admin -stop, ,,,, -admin -start
- it may be importing the bci routine with the odbc calls in it
- it may be doing any project import

If you were desperate and wanted this to work all the time you would have to remove this line from .odbc.ini before every datastage job with a bci called routine in it, maybe using SED.

QEWSD=39501

the number after the equal sign may vary.

Peoplesoft support wants me to figure out why the license is resetting before they will help but our organization is mostly giving up on using the bci approach so I'm not sure I'm going to get the full solution.

... Flash Gordon
Flash Gordon
Hyperborean Software Solution
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

Care to revisit my original suggestion?
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
flashgordon
Premium Member
Premium Member
Posts: 99
Joined: Tue Aug 17, 2004 7:50 am
Location: Boulder, Colorado

Post by flashgordon »

Kenneth,

You were right that there were other ways to call sql from bci. We had this working, it was compact and did what we wanted it to do, and we had bci before and after coded in 200+ ds jobs, so we struggled to find a way to make that investment pay off. We'll probably be doing your approach now.


... Tom
Flash Gordon
Hyperborean Software Solution
Post Reply