Specifying a DSN
Moderators: chulett, rschirm, roy
Specifying a DSN
Hi,
I have developed jobs using DataStage to replicate a Data Transfer process.The problem I face is that for every transfer there is a new DataBase which means that I have to create a new DSN Every time to import my table definitions .For example these are my source definitions:
Load01
Server: PSSQL-WP1
Database: PurdueP3Load01
table: tblunique
XRF: PurdueP3Load01.dbo.tbllog
Load 02:
Server: PSSQL-WP1
Database: PurdueP3Load02
table: tblValidation
XRF: PurdueP3Load01.dbo.tbllog
There are 15 such loads and everytime I need to go and define a new DSN for each database to import the column definitions for the table.I am using a DRS stage as my input.Is there a way I could create a DSN on the fly if I give my database as a parameter ?Please let me know.
I have developed jobs using DataStage to replicate a Data Transfer process.The problem I face is that for every transfer there is a new DataBase which means that I have to create a new DSN Every time to import my table definitions .For example these are my source definitions:
Load01
Server: PSSQL-WP1
Database: PurdueP3Load01
table: tblunique
XRF: PurdueP3Load01.dbo.tbllog
Load 02:
Server: PSSQL-WP1
Database: PurdueP3Load02
table: tblValidation
XRF: PurdueP3Load01.dbo.tbllog
There are 15 such loads and everytime I need to go and define a new DSN for each database to import the column definitions for the table.I am using a DRS stage as my input.Is there a way I could create a DSN on the fly if I give my database as a parameter ?Please let me know.
-
- Premium Member
- Posts: 4
- Joined: Sun Mar 07, 2004 8:09 pm
Re: Specifying a DSN
Just have the Database defined as a Parameter, that way you can pass the DBname to the job and change it for each of the loads'.
mprashant wrote:Hi,
I have developed jobs using DataStage to replicate a Data Transfer process.The problem I face is that for every transfer there is a new DataBase which means that I have to create a new DSN Every time to import my table definitions .For example these are my source definitions:
Load01
Server: PSSQL-WP1
Database: PurdueP3Load01
table: tblunique
XRF: PurdueP3Load01.dbo.tbllog
Load 02:
Server: PSSQL-WP1
Database: PurdueP3Load02
table: tblValidation
XRF: PurdueP3Load01.dbo.tbllog
There are 15 such loads and everytime I need to go and define a new DSN for each database to import the column definitions for the table.I am using a DRS stage as my input.Is there a way I could create a DSN on the fly if I give my database as a parameter ?Please let me know.
The table definitons do change sometimes.But If we assume them to be constant the database changes everytime.So there is a different database for every load even though the table in the database may be the same.So I have to point to a different database each time right?This I need to specify in my DSN.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Is there a finite number of these databases, or is it completely arbitrary? In the former case, you could make the DSN a job parameter and control it from a sequence or from job control code quite easily. What you do about differing table metadata is quite a different question and one which is not easily solved without having separate jobs (in which case the issue of different DSNs is moot).
In the latter case, who's creating all these databases?!! It seems like a very strange way to run an IT shop!
In the latter case, who's creating all these databases?!! It seems like a very strange way to run an IT shop!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That's a real pain!
How do you even get to know the names of the databases to which you will need to connect? You need this before you can even create data source names. Could be an interesting process to try to automate!
DSNs can usually be created via a simple edit of the $ODBCINI file (some differences for other than DataDirect drivers, think odbc.ini in these cases) and an even simpler edit of the uvodbc.config file in the DataStage project. You should be able to do both of these using DataStage BASIC quite straightforwardly (the System(32) function returns the pathname of the DSEngine directory).
Will you need to keep all these DSNs forever, or will there be occasional purges? In either case, don't fall into the easy path of adding DSNs even though they already exist (as some software installs do).
How do you even get to know the names of the databases to which you will need to connect? You need this before you can even create data source names. Could be an interesting process to try to automate!
DSNs can usually be created via a simple edit of the $ODBCINI file (some differences for other than DataDirect drivers, think odbc.ini in these cases) and an even simpler edit of the uvodbc.config file in the DataStage project. You should be able to do both of these using DataStage BASIC quite straightforwardly (the System(32) function returns the pathname of the DSEngine directory).
Will you need to keep all these DSNs forever, or will there be occasional purges? In either case, don't fall into the easy path of adding DSNs even though they already exist (as some software installs do).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hey Ray,
I'm not too sure I understood you very clearly.I'm not very familiar with DS Basic.I right now do have to add a new DSN for each load and I really dont like that.Can I in some way create a DSN on the fly based on what you were saying?We dont need the DSN after the load is complete.
I was looking into maybe creating a file DSN and try to vary whats written to the File in the ODBC folder in windows.I still have to test if a File DSN works with DS but see no reason for it not too.
I'm not too sure I understood you very clearly.I'm not very familiar with DS Basic.I right now do have to add a new DSN for each load and I really dont like that.Can I in some way create a DSN on the fly based on what you were saying?We dont need the DSN after the load is complete.
I was looking into maybe creating a file DSN and try to vary whats written to the File in the ODBC folder in windows.I still have to test if a File DSN works with DS but see no reason for it not too.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can't create a DSN on the fly unless you know what the DSN has to refer to, the driver it has to use, and so on.
If you know all this, you can create DataStage BASIC code (probably a job control routine, or a Routine to be called from a Routine Activity in a sequence) to edit the relevant files ($ODBCINI and uvodbc.config) before starting the job that uses them. That job would, of course, need to have the DSN as a job parameter. In short, you need to automate a process that I'm guessing you now perform manually. Can you analyse what you do manually? If so, you should be able easily to turn it into DataStage BASIC code (or create specifications for someone else to do so).
File DSNs are OK for DataStage in Windows, but your original post specified that your DataStage is on UNIX. Therefore you can't use a Windows File DSN, because you can't load the Windows-based ODBC drivers on your UNIX machine.
If you know all this, you can create DataStage BASIC code (probably a job control routine, or a Routine to be called from a Routine Activity in a sequence) to edit the relevant files ($ODBCINI and uvodbc.config) before starting the job that uses them. That job would, of course, need to have the DSN as a job parameter. In short, you need to automate a process that I'm guessing you now perform manually. Can you analyse what you do manually? If so, you should be able easily to turn it into DataStage BASIC code (or create specifications for someone else to do so).
File DSNs are OK for DataStage in Windows, but your original post specified that your DataStage is on UNIX. Therefore you can't use a Windows File DSN, because you can't load the Windows-based ODBC drivers on your UNIX machine.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.