Dynamic DSN

MrBlack · Post by **MrBlack** » Wed Sep 19, 2012 3:56 pm

Does anyone know how to code a dynamic DSN or if it's even possible for an ODBC connection?

So currently each time I have a DSN I have to statically define it(driver location, database location, etc...) before I can use it as an ODBC connection. What I'm hoping to do it write a routine that I can use to pass in some job parameters to dynamically define a DSN.

Does anyone know if this is even possible?

lstsaur · Post by **lstsaur** » Wed Sep 19, 2012 4:28 pm

Impossible.

ArndW · Post by **ArndW** » Thu Sep 20, 2012 1:33 am

Are you on UNIX or Windows? The post states UNIX but the term ODBC DSNs is a Windows one. The dynamic DSN cannot be done in Windows, but with a bit of effort it might be possible in UNIX by manipulating the odbc.ini or other driver files - but I don't think it would be easy.

MrBlack · Post by **MrBlack** » Thu Sep 20, 2012 8:23 am

It is a Unix environment. And you're right that when I statically define a DSN I'm making an entry in the odbc.ini file.

The purpose behind all of this is I have multiple data files in many folders, so instead of making a bunch of entries in my odbc.ini file I would like to parameterize the DSN connection. My files on the OS are like this

Code: Select all

/foxpro/2012/file.dbf
/foxpro/2011/file.dbf
/foxpro/2010/file.dbf
/foxpro/2009/file.dbf
/foxpro/2008/file.dbf
/foxpro/2007/file.dbf

There is a definate pattern to the layout and I'd like like to dynamically define the folder location of the DSN. So instead of choosing a DSN in the drop down box when defining the ODBC Stage I would just write the routine name and pass whatever parameters required.

ArndW · Post by **ArndW** » Thu Sep 20, 2012 8:32 am

So I could envision a UNIX script which is called and modifies the contents of an odbc.ini file entry "dynamicODBC" (called from a job sequence). The ODBC entry in the job is hard-coded as "dynamicODBC" and would then access whatever path is in the file. At least I think that this would work.

Mike · Post by **Mike** » Thu Sep 20, 2012 8:39 am

Certainly worth a try...

I remember running into cases where ODBC changes required ASB node agents to be stopped and restarted. You may run into caching issues on the application server.

Mike

MrBlack · Post by **MrBlack** » Thu Sep 20, 2012 9:16 am

Here's a thought that I just came up with, what about using the Dynamic-RDBMS stage? What I'm thinking is defining an entry in my odbc.ini file an entry like this:

Code: Select all

[MyAwesomeODBC]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMdbf24.so
Description=DataDirect 6.0 dBASEFile(*.dbf)
CreateType=dBASE5
Database=#myParameter#

Taking note that the database attribute is actually a datastage parameter in my job. I haven't tried this yet but I'm going to familiarize myself with the capabilities of that Dynamic-RDBMS stage and what it can do.

lstsaur · Post by **lstsaur** » Thu Sep 20, 2012 4:22 pm

ODBC connectivity from a UNIX/Linux server is established by the entries defined in both .odbc.ini and uvodbc.config. How is your dynamic DSN going to work to handle the entries in uvodbc.config?

MrBlack · Post by **MrBlack** » Wed Oct 24, 2012 2:11 pm

So I just wanted to post the workaround I found for my problem. I never found a way to dynamically change the ODBC configuration. So the workaround I came up with was to configure a ODBC to point to an empty folder, then I execute several linux commands through the command stage to create symbolic links to the files in all the various folder levels. The symbolic links leverage the ability to trick datastage into think that all the files are in one folder where in reality they're scattered. Also since the source files are a mounted network drive symbolic links save me from the network traffic of make file copies and filling up the hard drive on the server that is hosting my data stage.