Specifying a DSN

[mprashant]

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.

[bonthalaprasad]

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

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.

[mprashant]

Its not about passing the parameter to the job...which in any case is already been defined.Its creating a new DSN to point to that particular
server.database.tablename each time the parameters change.

[chulett]

But are you saying the table definitions change from load to load? I'm curious why you would need to 'import table definitions' each time?

[mprashant]

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.

[ray.wurlod]

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!

[mprashant]

Hey Ray
It actually is a very strange way to run a IT shop.The business demands it though.They are in the legal doucment services.SO the number of databases created is arbitrary even though the tables created in this database may have the same metadata most of the time.

regards
Prashant

[ray.wurlod]

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

[mprashant]

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.

[ray.wurlod]

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.