| Author |
Message
|
| bbakerman |
 Posted: Tue Sep 06, 2005 8:16 pm Post subject: Using a different database DSN on AIX |
 |
|
Apprentice
Joined: 17 Dec 2003
Posts: 41
|
I want to abstract the DataSource name used within my broker application so that I can move it from development on Windows, into test on AIX and so on to prod.
On each platform the databases have different names.
What I was hoping to do was create a single DSN (called WBIHMB) on each platform that pointed to the appropriate database on that platform. That way i would have to change the application as I move between platforms.
I have it working on Windows but not on AIX CSD6.
My .odbc.ini file looks like this
| Code: |
[ODBC Data Sources]
PRODBKDB=IBM DB2 ODBC Driver
TESTBKDB=IBM DB2 ODBC Driver
WBIHMB=IBM DB2 ODBC Driver
[PRODBKDB]
Driver=/u/db2inst1/sqllib/lib/libdb2.a
Description=PRODBKDB DB2 ODBC Database
Database=PRODBKDB
[WBIHMB]
Driver=/u/db2itst2/sqllib/lib/libdb2.a
Description=TESTBKDB DB2 ODBC Database
Database=TESTBKDB
[TESTBKDB]
Driver=/u/db2itst2/sqllib/lib/libdb2.a
Description=TESTBKDB DB2 ODBC Database
Database=TESTBKDB
[ODBC]
Trace=0
TraceFile=/var/wmqi/odbc/odbctrace.out
TraceDll=/usr/opt/wmqi/merant/lib/odbctrac.so
InstallDir=/usr/opt/wmqi/merant
|
The ODBCINI var is
ODBCINI=/var/mqsi/odbc/.odbc.ini
I ran setdbparms -n WBIHMB -u mqsitest -p xxxx
mqsitest has access to the database (as it is in fact the broker database)
The compute node has WBIHMB as the data source name.
However when I ran my message flow on AIX I get this error
Sep 7 14:09:12 draco user:err|error MQSIv500[393326]: (TESTBRK01.default)[5147]BIP2322E: Database error: SQL State '08001'; Native Error Code '-1013'
; Error Text '[IBM][CLI Driver] SQL1013N The database alias name or database name "WBIHMB" could not be found. SQLSTATE=42705 '. : TESTBRK01.a41170c
If I change the BAR and the compute DSN properties to use TESTBKDB it works OK presumably because it can resolve the name. This is the name used on the mqsicreatebroker.
if I change the BAR and use a completely non sensical name like BLURG for the DSN I get this error.
Sep 7 12:38:40 draco user:err|error MQSIv500[393326]: (TESTBRK01.default)[5147]BIP2322E: Database error: SQL State 'IM002'; Native Error Code '0'; Error Text '[DataDirect][ODBC lib] Data source name not found and no default driver specified'. : TESTBRK01.a41170cd-0501-0000-0080-cc3bc3b76f33: /build
Notice the sutble change. It rightly cant find the DSN entry. It would seem that my WBIHMB entrie is being found but some how not resolved into an actual database
What else might I be missing to get a different DSN working on AIX WBI5 CSD6?
It appears that the DSN name cant deviate from the name of the actual database? Is this the case or do I have to create some other configuration.[/code] |
|
| Back to top |
|
 |
| sradiraju |
| Posted: Wed Sep 07, 2005 9:30 am Post subject: |
|
|
Apprentice
Joined: 08 Sep 2002
Posts: 34
Location: Chicago,IL
|
bbakerman,
The configuration you have should work on AIX too as I am having similar setup. A question though, did you recycle the broker after making the change in odbc.ini file, if not do that and try again.
hth,
Somesh |
|
| Back to top |
|
|
| bbakerman |
| Posted: Mon Sep 12, 2005 8:22 pm Post subject: |
|
|
Apprentice
Joined: 17 Dec 2003
Posts: 41
|
Yes I defintely recycled the broker and database for that matter.
In your setup was your DSN name completly abstracted away from the name of the actual database.
Could you post your .odbc.ini here so I could compare? |
|
| Back to top |
|
|
| JT |
| Posted: Tue Sep 13, 2005 6:51 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Code: |
[ODBC Data Sources]
AA2A=IBM DB2 ODBC DRIVER
[AA2A]
Driver=/opt/IBMdb2/V7.1/lib/libdb2_36.so.1
Description=DB2 Connect Driver
Database=NOR_AA2A
[ODBC]
Trace=0
TraceFile=/var/wmqi/odbc/odbctrace.out
TraceDll=/opt/wmqi/merant/lib/odbctrac.so
InstallDir=/opt/wmqi/merant |
We're running v5.0.4 on a Solaris platform with DB2 entries like the one above. No problems.
However, in conjunction with the .odbc.ini entry, we defined a DB2 database alias, as follows:
| Code: |
Database 10 entry:
Database alias = AA2A
Database name = NOR_AA2A
Node name = NAMAA2A
Database release level = 9.00
Comment =
Directory entry type = Remote
Authentication = DCS
Catalog node number = -1 |
Hope this helps. |
|
| Back to top |
|
|
| sradiraju |
| Posted: Tue Sep 13, 2005 6:52 am Post subject: |
|
|
Apprentice
Joined: 08 Sep 2002
Posts: 34
Location: Chicago,IL
|
Well, one thing I noticed here is that the abstracted database name is Oracle Database. And, I dont find a reason for it to not work with DB2 database.
[code]
[ODBC Data Sources]
WBIR3UDB=IBM DB2 ODBC Driver
R3HUBDB=DataDirect 410 Oracle 8 Driver
ERPSDR3=DataDirect 410 Oracle 8 Driver
SYBASEDB=DataDirect 410 Sybase ASE Driver
[WBIR3UDB]
Driver=/u/db2inst1/sqllib/lib/libdb2.a
Description=MQSIBKDB DB2 ODBC Database
Database=WBIR3UDB
[R3HUBDB]
Driver=/usr/opt/wmqi/merant/lib/UKor818.so
WorkArounds=536870912
WorkArounds2=2
Description=Oracle8
ServerName=R3HUBSTG
EnableDescribeParam=1
OptimizePrepare=1
[ERPSDR3]
Driver=/usr/opt/wmqi/merant/lib/UKor818.so
WorkArounds=536870912
WorkArounds2=2
Description=Oracle8
ServerName=ERPINT3
EnableDescribeParam=1
OptimizePrepare=1
[ODBC]
Trace=0
TraceFile=/var/mqsi/odbc/odbctrace.out
TraceDll=/usr/opt/wmqi/merant/lib/odbctrac.so
InstallDir=/usr/opt/wmqi/merant[/code] |
|
| Back to top |
|
|
| mq_developer |
| Posted: Tue Sep 13, 2005 8:19 am Post subject: |
|
|
Voyager
Joined: 18 Feb 2002
Posts: 82
|
As pointed by the prior post , Its missing the alias on the DB side which would resolve the AbstractName (WBIHMB) to the Actual Database (TESTBKDB). Your DBA should be able to help here.
Alternatively why dont you think of using Deployment Descriptor ??? - Which gives you the full control over configuring DSN with ease. |
|
| Back to top |
|
|
| bbakerman |
| Posted: Tue Sep 13, 2005 9:19 pm Post subject: |
|
|
Apprentice
Joined: 17 Dec 2003
Posts: 41
|
| JT wrote: |
[code][ODBC Data Sources]
[code]Database 10 entry:
Database alias = AA2A
Database name = NOR_AA2A
Node name = NAMAA2A
Database release level = 9.00
Comment =
Directory entry type = Remote
Authentication = DCS
Catalog node number = -1 [/code]
Hope this helps. |
I figured it I might have to do something on the DB2 side.
Can some one please outline how ones creates a DB2 alias.
I tried the CATALOG DB statement but I think I am getting it wrong.
Unfortunately in this case I am the DB2 admin and hence I just hope DB2 does have too many issues |
|
| Back to top |
|
|
| bbakerman |
| Posted: Tue Sep 13, 2005 10:08 pm Post subject: |
|
|
Apprentice
Joined: 17 Dec 2003
Posts: 41
|
| mq_developer wrote: |
Alternatively why dont you think of using Deployment Descriptor ??? - Which gives you the full control over configuring DSN with ease. |
When you say Deployment Descriptor do you mean the BAR and its configurable properties?
If so then this what we do now but often we forget to update this expecially when we are in heavy development/test cycles.
I wanted to be able to abstract out the actual database via an alias mechanism and then have the broker code use the one name and hence no deployment configuration. |
|
| Back to top |
|
|
| bbakerman |
| Posted: Tue Sep 13, 2005 11:38 pm Post subject: |
|
|
Apprentice
Joined: 17 Dec 2003
Posts: 41
|
Ok i did the following
db2 catalog db TESTBKDB as WBIHMB on /home
This appears to give me these entries
| Code: |
$ db2 list db directory
System Database Directory
Number of entries in the directory = 3
Database 1 entry:
Database alias = WBIHMB
Database name = TESTBKDB
Local database directory = /home
Database release level = a.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = TESTBKDB
Database name = TESTBKDB
Local database directory = /home
Database release level = a.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 3 entry:
Database alias = TESTCMDB
Database name = TESTCMDB
Local database directory = /home
Database release level = a.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
|
I can then connect to the database alias via DB2 CLI and select from table etc...
[code]
$ db2 connect to WBIHMB
Database Connection Information
Database server = DB2/6000 8.2.2
SQL authorization ID = MQSITEST
Local database alias = WBIHMB
[code]
I
then setup the DSN to the broker like this
mqsisetdbparms TESTBRK01 -n WBIHMB -u mqsitest -p xxxxx
My .odbc.ini file looks like this :
[code]
[ODBC Data Sources]
PRODBKDB=IBM DB2 ODBC Driver
TESTBKDB=IBM DB2 ODBC Driver
WBIHMB=IBM DB2 ODBC Driver
[PRODBKDB]
Driver=/u/db2inst1/sqllib/lib/libdb2.a
Description=PRODBKDB DB2 ODBC Database
Database=PRODBKDB
[WBIHMB]
Driver=/u/db2itst2/sqllib/lib/libdb2.a
Description=Is actually TESTBKDB DB2 ODBC Database
Database=WBIHMB
[TESTBKDB]
Driver=/u/db2itst2/sqllib/lib/libdb2.a
Description=TESTBKDB DB2 ODBC Database
Database=TESTBKDB
[ODBC]
Trace=0
TraceFile=/var/wmqi/odbc/odbctrace.out
TraceDll=/usr/opt/wmqi/merant/lib/odbctrac.so
InstallDir=/usr/opt/wmqi/merant
[/code]
I deployed a simple test flow that has a compute node in it that does a select from the database
[code]
set OutputLocalEnvironment.WBIHMB.MsgAuditCtl.Flows[] =
eval('(select T.* from Database.WBIHMB.WBIHMB_MSGAUDITCTL as T)');
[/code]
However I know get the following log entries
[code]
Sep 14 17:13:28 draco user:err|error MQSIv500[782362]: (TESTBRK01.default)[5141]BIP2322E: Database error: SQL State '08001'; Native Error Code '-1224'; Error Text '[IBM][CLI Driver] SQL1224N A database agent could not be started to service a request, or was terminated as a result of a database system shutdown or a force command. SQLSTATE=55032 '. : TESTBRK01.a41170cd-0501-0000-0080-cc3bc3b76f33: /build/S500_P/src/DataFlowEngine/ImbOdbc.cpp: 366: ImbOdbcHandle::checkRcInner: :
[/code]
The DB2 message manual mentions something about maximum number of connections for local agents. Perhaps I am hitting something like that?
If I change the DataSource in the Compute node back to my default broker one of TESTBKDB, then it all works OK.
I am on WBIBK 5 CSD6 on AIX 5.3 |
|
| Back to top |
|
|
| JT |
| Posted: Wed Sep 14, 2005 5:33 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Quote: |
- Scenario: You get DB2error message SQL1224N:
A database agent could not be started to service a request, or was terminated as a result of a database shutdown or force command.
- Solution: On AIX, use TCP/IP node to connect to DB2 to avoid the limit of 10 connections. To set up AIX and DB2 loop-back to use TCP/IP node:
|
The details for applying the solution can be found here |
|
| Back to top |
|
|
| bbakerman |
| Posted: Wed Sep 14, 2005 4:28 pm Post subject: |
|
|
Apprentice
Joined: 17 Dec 2003
Posts: 41
|
Ahh it seems the SQL1224N is known problem on AIX and was even in the broker doco
I resolved it by turing EXTSHM=ON.
I didnt re-config my database as using TCPIP loopback (as per the broker doco) and it still seems to work.
The following is from the DB2 doco.
http://publib.boulder.ibm.com/infocenter/db2help/index.jsp
| Quote: |
By default, AIX(R) does not permit 32-bit applications to attach to more than 11 shared memory segments per process, of which a maximum of 10 can be used for local DB2(R) connections. Development center requires multiple JDBC connections. The EXTSHM environment variable must be configured for multiple JDBC connections.
Action
To resolve this issue if you are using AIX version 4.3.1 or newer, set set the environment variable EXTSHM to ON to increase the number of shared memory segments to which a single process can be attached. EXTSHM must be exported both in the shell where the client application is started and also in the shell where db2start is run. To configure the EXTSHM environment variable for multiple JDBC connections:
1. In client sessions, before starting the client application, type the following command: export EXTSHM=ON
2.
Before starting the DB2 server, type the following commands:
export EXTSHM=ON
db2set DB2ENVLIST=EXTSHM
db2set -all
3.
Add the following lines to the sql/db2profile:
EXTSHM=ON
export EXTSHM
|
I stopped and started both db2 and broker and then was able to get more than 11 connections to the database.
| Code: |
$ db2 list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- ------------------------------ -------- -----
MQSITEST bipconfigmgr.e 19 P84C8A68.CB08.029944080712 TESTCMDB 1
MQSITEST bipconfigmgr.e 18 P84C8A68.CA08.029944080710 TESTCMDB 1
MQSITEST bipconfigmgr.e 17 P84C8A68.C908.029944080708 TESTCMDB 1
MQSITEST bipconfigmgr.e 16 P84C8A68.C808.029944080706 TESTCMDB 1
MQSITEST bipconfigmgr.e 15 P84C8A68.C708.029944080704 TESTCMDB 1
MQSITEST db2bp 34 *LOCAL.db2itst2.050914081136 TESTBKDB 1
MQSITEST DataFlowEngine 28 *LOCAL.db2itst2.050914081059 TESTBKDB 1
MQSITEST DataFlowEngine 27 *LOCAL.db2itst2.050914081058 TESTBKDB 1
MQSITEST DataFlowEngine 14 *LOCAL.db2itst2.050914080646 TESTBKDB 1
MQSITEST DataFlowEngine 13 *LOCAL.db2itst2.050914080645 TESTBKDB 1
MQSITEST DataFlowEngine 12 *LOCAL.db2itst2.050914080636 TESTBKDB 1
MQSITEST DataFlowEngine 11 *LOCAL.db2itst2.050914080635 TESTBKDB 1
MQSITEST DataFlowEngine 10 *LOCAL.db2itst2.050914080631 TESTBKDB 1
MQSITEST bipbroker 8 *LOCAL.db2itst2.050914080629 TESTBKDB 1 |
|
|
| Back to top |
|
|
|
|
