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 Feb 05, 2008 6:28 pm Reply with quote    Back to top    

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



Group memberships:
Premium Members

Joined: 20 Aug 2007
Posts: 24

Points: 220

Post Posted: Thu Feb 14, 2008 10:52 am Reply with quote    Back to top    

Where exactly do those 2 lines you posted need to be added? I am a little unclear as to how to implement this 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: Fri Feb 15, 2008 7:13 pm Reply with quote    Back to top    

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



Group memberships:
Premium Members

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

Post Posted: Mon Feb 18, 2008 4:05 pm Reply with quote    Back to top    

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



Group memberships:
Premium Members

Joined: 20 Aug 2007
Posts: 24

Points: 220

Post Posted: Fri Feb 22, 2008 7:44 am Reply with quote    Back to top    

Thanks for the clarification!
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 Feb 29, 2008 5:37 pm Reply with quote    Back to top    

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
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: Fri Feb 29, 2008 6:17 pm Reply with quote    Back to top    

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



Group memberships:
Premium Members

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

Post Posted: Sat Mar 01, 2008 9:23 am Reply with quote    Back to top    

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