| Author |
Message
|
| svu |
 Posted: Thu Mar 24, 2011 7:40 am Post subject: ODBC: db2 database name and odbc data source name |
 |
|
Voyager
Joined: 30 Jan 2006
Posts: 99
|
Hi ppl
This post is kind of "confirm this WTF".
We have several instances (per environment) of the broker on the same system. Since we're deploying same bar files, with same data source names, we have to use different odbc.ini files mapping same data source names to different Oracle TNS names (the OS is Solaris). That all works fine (if you're interested in details - we have one WMB user per environment, setting different ODBCINI settings in .profile)
But that does not work for DB2. It seems the data source name has to match the database name (of the alias, for remote databases). According to http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.cli.doc/doc/t0006349.htm , Database parameter is not used for DB2. As the result, we have to create one client DB2 instance per environment - to use same database alias name for different remote databases.
Anyone came across that? Is there any way to cheat db2 odbc stack to use different databases for the same ODBC data source name? |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Thu Mar 24, 2011 7:56 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You should absolutely positively invest in infrastructure - through some very basic basic shell scripting - to override the DSNs on your bar files.
You should have a process in place to manage all kinds of BAR file overrides and use this during your deployment process.
This makes it significantly easier to separate administrative tasks and development tasks, which makes it easier to get things done.
And it allows for a proper separation of duties which is required under all kinds of different IT security policies by industry and governmental groups.
So you should not need multiple DB2 client instances as you should be able to catalog all of the relevant db2 databases into the same Db2 instance and set the appropriate DSN on the BAR file as a normal part of deployment.
Again, it's not hard to do this. It's a very good idea to do this, and it will also solve your issue. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Mar 24, 2011 7:57 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Try setting the DB2 alias:
| Code: |
./db2 catalog db firstdb as DB1D at node ND_DB2D
./db2 catalog db seconddb as DB2D at node ND_DB2D
./db2 catalog db thirddb as DB3D at node ND_DB2D
|
Then try connecting to each:
| Code: |
db2 connect to firstdb user <yourusername>
db2 connect to seconddb user <yourusername>
db2 connect to thirddb user <yourusername>
|
Once you get that running, define DSNs that go along with each firstdb, seconddb, thirddb for each user account and place those ODBCINI files with proper $ODBCINI set in each of the user profiles.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| svu |
| Posted: Thu Mar 24, 2011 7:59 am Post subject: |
|
|
Voyager
Joined: 30 Jan 2006
Posts: 99
|
@mqjegff: We had all that horrible overriding business. It created more problems than resolved. So we're in process of dropping it. Having single bar file for all environments is a blessing for us.
Actually, what surprises me is that I could not easily find the way to "sign" bar files the same way as Java people sign jar/ear files.
Last edited by svu on Thu Mar 24, 2011 8:04 am; edited 1 time in total |
|
| Back to top |
|
|
| svu |
| Posted: Thu Mar 24, 2011 8:03 am Post subject: |
|
|
Voyager
Joined: 30 Jan 2006
Posts: 99
|
| lancelotlinc wrote: |
| Once you get that running, define DSNs that go along with each firstdb, seconddb, thirddb for each user account and place those ODBCINI files with proper $ODBCINI set in each of the user profiles. |
If I want to use DSN "myimportantdb" for firstdb or seconddb or thirddb in 3 different odbc.ini files - how would I do that?
This seems to be not working:
[myimportantdb]
Driver=/somelongpath/libdb2Wrapper.so
Description=My Important DB
Database=firstdb |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Mar 24, 2011 8:13 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| svu wrote: |
| lancelotlinc wrote: |
| Once you get that running, define DSNs that go along with each firstdb, seconddb, thirddb for each user account and place those ODBCINI files with proper $ODBCINI set in each of the user profiles. |
If I want to use DSN "myimportantdb" for firstdb or seconddb or thirddb in 3 different odbc.ini files - how would I do that?
This seems to be not working:
[myimportantdb]
Driver=/somelongpath/libdb2Wrapper.so
Description=My Important DB
Database=firstdb |
Did the connect work? Make the connect work first, before you try the ODBC stuff.
| Code: |
| db2 connect to firstdb user <yourusername> |
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| svu |
| Posted: Thu Mar 24, 2011 8:14 am Post subject: |
|
|
Voyager
Joined: 30 Jan 2006
Posts: 99
|
Yes, db2 connect worked.
But I could only use those dbs with DSNs "firstdb", "seconddb", "thirddb" |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Mar 24, 2011 8:19 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Use this as your ODBCINI:
| Code: |
[myimportantdb]
Driver=/somelongpath/libdb2Wrapper.so
Description=My Important DB
Database=firstdb
[ODBC]
;# To turn on ODBC trace set Trace=1
Trace=1
;#TraceOptions=3
TraceFile=/var/ibm/odbctrace.log
TraceDll=/opt/ibm/mqsi/7.0/ODBC/V6.0/lib/odbctrac.so
InstallDir=/opt/ibm/mqsi/7.0/ODBC/V6.0
|
Then try to connect using an application that calls on myimportantdb and post the contents of your log file.
Be sure to issue a db2 terminate before you do anything else, and mqsistop all brokers.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Mar 24, 2011 8:31 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
I do not expect DB2 to function in a manner other than as documented.
I think that, if you found that using mqsiapplybaroverride in a scripted and managed manner caused more issues than it resolved, that you did not script and manage it correctly.
But I hope to be pleasantly surprised in both cases, as it will mean that I have learned something new. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Mar 24, 2011 8:37 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| mqjeff wrote: |
I do not expect DB2 to function in a manner other than as documented.
I think that, if you found that using mqsiapplybaroverride in a scripted and managed manner caused more issues than it resolved, that you did not script and manage it correctly.
But I hope to be pleasantly surprised in both cases, as it will mean that I have learned something new. |
My first inclination would be to do the BFO dance. However, DB2 aliases are well documented and can do what the OP wants.
BTW, isql is a good command line tool to test the conx.
| Code: |
| isql -v DSN_NAME db_username db_password |
isql is a part of the unixODBC collection:
| Code: |
| yum install unixODBC |
So in svu's case, you could use
| Code: |
| isql -v myimportantdb username password |
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| svu |
| Posted: Thu Mar 24, 2011 8:42 am Post subject: |
|
|
Voyager
Joined: 30 Jan 2006
Posts: 99
|
| mqjeff wrote: |
| I think that, if you found that using mqsiapplybaroverride in a scripted and managed manner caused more issues than it resolved, that you did not script and manage it correctly. |
Quote possible. The fact is that any technical solution is more reliable than process-based solution (errare humanum est). And having some step in the development/deployment processes is more expensive than not having it.
If we we can eliminate overriding by building uniform environments (which has zero extra cost) - we do that. And it simplifies archiving and versioning too (of course, that does not eliminate CVS tags) |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Mar 24, 2011 8:45 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| svu wrote: |
| mqjeff wrote: |
| I think that, if you found that using mqsiapplybaroverride in a scripted and managed manner caused more issues than it resolved, that you did not script and manage it correctly. |
Quote possible. The fact is that any technical solution is more reliable than process-based solution (errare humanum est). And having some step in the development/deployment processes is more expensive than not having it.
If we we can eliminate overriding by building uniform environments (which has zero extra cost) - we do that. And it simplifies archiving and versioning too (of course, that does not eliminate CVS tags) |
Going to the BFO dance is quite fun. You never know who you may run into there. People like a man named Hudson. Or there's always the mysterious "CruiseControl". My fav is Lea Thompson. She's still a hottie! BFOs work best in an automated build and deployment tool.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| svu |
| Posted: Thu Mar 24, 2011 8:45 am Post subject: |
|
|
Voyager
Joined: 30 Jan 2006
Posts: 99
|
@lancelotlinc: Unfortunately there is no isql on that Solaris box. And I am not superuser there.
PS Sorry for my ignorance, what is 'BFO'? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Mar 24, 2011 8:50 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
isql does not have any affect or bearing on DataDirect drivers or the DB2 client, AFAIK.
In v7, one can easily use mqsicvp to fully test data sources as if Broker itself was trying to use them.
I agree that DB2 aliases are a good way to create a catalog entry that refers to an alternately named database.
I do not think that they provide a way to create two catalog entries that have the same name and point to different databases.
And I remain sanguine that the Db2 driver will interpret the Database clause of the ODBC ini file to allow for the name of the DSN in the ODBC file to have a name that does not match the name of the catalog entry. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Mar 24, 2011 8:51 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| svu wrote: |
| @lancelotlinc: Unfortunately there is no isql on that Solaris box. And I am not superuser there. |
You need a tool independent of Broker to test the ODBC conx. Cause there is some funny business still to tackle once you prove that the ODBC conx works.
Open a ticket and get a Solaris tech to install unixODBC package. Else, write a custom C app that you can compile on that box to exercise the ODBC conx.
Once you know that the ODBC conx works, by isql or custom C app, then issue this command to Broker:
| Code: |
| mqsisetdbparms <yourbrokername> -n myimportantdb -u dbusername -p dbpassword |
After that, test Broker's conx to ODBC:
| Code: |
| mqsicvp <yourbrokername> -n myimportantdb |
Don't try to do this with Broker until you prove that the ODBC works with isql or other simple app.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
|
|
