SQL Server Stage

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

Post Reply
wjfitzgerald
Participant
Posts: 72
Joined: Tue Feb 05, 2008 4:38 am

SQL Server Stage

Post by wjfitzgerald »

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

Post by kcbland »

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
jdmiceli
Premium Member
Premium Member
Posts: 309
Joined: Wed Feb 22, 2006 10:03 am
Location: Urbandale, IA

Post by jdmiceli »

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

Post by flashgordon »

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
wjfitzgerald
Participant
Posts: 72
Joined: Tue Feb 05, 2008 4:38 am

thanks

Post by wjfitzgerald »

thanks to all

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

Regards
John Fitz
jdmiceli
Premium Member
Premium Member
Posts: 309
Joined: Wed Feb 22, 2006 10:03 am
Location: Urbandale, IA

Post by jdmiceli »

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

Post by flashgordon »

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
jdmiceli
Premium Member
Premium Member
Posts: 309
Joined: Wed Feb 22, 2006 10:03 am
Location: Urbandale, IA

Post by jdmiceli »

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!"
Post Reply