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



Joined: 05 Feb 2008
Posts: 72

Points: 652

Post Posted: Mon Mar 03, 2008 7:29 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Windows
Hi,

I am using an sql server stage to run sql against a table.
To data both the code and the db have been on the same machine and so all i need to specify was the DB name, user name and password.

Unfortunately, i now need to move to an environment where the db and ds are no different servers.

can you tell me how to connect the sql stage to the new db on the new server please?

Thanks, as always

John Fitz
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: Mon Mar 03, 2008 8:10 am Reply with quote    Back to top    

DS works thru database connectivities defined via setup activities. There's no requirement that DS reside on the same server as the database. SQL-Server on Windows is accessed via an ODBC connection defined on the DS Server. One of the fields in that setup is the server DSN/IP address. Just update that.

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



Group memberships:
Premium Members

Joined: 22 Feb 2006
Posts: 309
Location: Urbandale, IA
Points: 3577

Post Posted: Wed Mar 05, 2008 5:35 pm Reply with quote    Back to top    

UNLESS...

the DataStage server resides on Unix, at which point you will not be able to use the SQL Server stage. I have this issue and find the best solution to be the Dynamic RDBMS stage.

Toodles,

_________________
Bestest!

John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services


"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
Rate this response:  
Not yet rated
flashgordon



Group memberships:
Premium Members

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

Post Posted: Wed Mar 05, 2008 7:18 pm Reply with quote    Back to top    

One presumes both your database and you Datastage server are windows servers. On the datastage server go into Administrative tools as admin then Data Sources and point to the Database on the other server with "Add" a data source. Then you should be able to reference the remote database through odbc stage.

In the unlikely you are Unix for Datastage but if you are tell me and I'll show you some examples of how to point at an ms sql instance using odbc on Unix.

If Ray or Craig are looking at this, you can't use SQL Server stage on a Unix box, right?

... Flash Gordon

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



Joined: 05 Feb 2008
Posts: 72

Points: 652

Post Posted: Thu Mar 06, 2008 3:58 am Reply with quote    Back to top    

thanks to all

have that sorted now, as usual it's simple when you know how!

Regards
John Fitz
Rate this response:  
Not yet rated
jdmiceli



Group memberships:
Premium Members

Joined: 22 Feb 2006
Posts: 309
Location: Urbandale, IA
Points: 3577

Post Posted: Thu Mar 06, 2008 8:28 am Reply with quote    Back to top    

Correct, you cannot use the SQL Server stage on Unix. You can use the ODBC stages as you have pointed out. I have this setup right now and it works fine (though not with the faster speeds of native stages).

_________________
Bestest!

John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services


"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
Rate this response:  
Not yet rated
flashgordon



Group memberships:
Premium Members

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

Post Posted: Thu Mar 06, 2008 7:54 pm Reply with quote    Back to top    

John,

I was looking at this today. On Unix you can use DRS stage and select MS SQL Server as the database. Probably just doing odbc anyway, right? I haven't used Datastage for windows for a long time so I was curious.

... Tom

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



Group memberships:
Premium Members

Joined: 22 Feb 2006
Posts: 309
Location: Urbandale, IA
Points: 3577

Post Posted: Fri Mar 07, 2008 8:36 am Reply with quote    Back to top    

Hi Flash!

That is correct. My understanding is that it is an optimized ODBC connector. If I remember correctly, using the Dynamic RDBMS allows better logging capabilities than the standard ODBC connector (don't quote that as gospel). I find it much easier to work with than the standard connector. It also allows me to change database engines very easily should I need to.

Toodles!

_________________
Bestest!

John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services


"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
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