| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Informix connection : mqsisetdbparms and mqsicvp question. |
« View previous topic :: View next topic » |
| Author |
Message
|
| Cogito-Ergo-Sum |
 Posted: Fri Sep 21, 2012 12:59 am Post subject: Informix connection : mqsisetdbparms and mqsicvp question. |
 |
|
Master
Joined: 07 Feb 2006
Posts: 293
Location: Bengaluru, India
|
My original problem is the inability to insert rows into Informix table using the Mapping node. Yes, the .dbm file exists. After some analysis, I noticed that Informix isn't seeing an incoming connection which later led me to this confusion with mqsisetdbparms and mqsicvp.
Scenario 1
I set the ID and password using mqsisetdbparms as described here with the command below.
| Code: |
mqsisetdbparms BRKR08 -n dsn::my_informix -u informix -p new00pwd
|
I test the connectivity with mqsicvp as described here with the command below.
| Code: |
mqsicvp BRKR08 -n dsn::my_informix
|
.
The connection fails as shown below:
| Code: |
BIP2393E: Database error: ODBC return code '-1' from data source ''dsn::my_informix'' using ODBC driver manager ''/opt/ibm/IE02/2.0.0/lib/libodbcinterface.so''.
The broker received an error when processing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database about this error.
Use the following messages to determine the cause of the error. Typical problems are an incorrect data source, or table names. Correct either the database or the broker configuration.
BIP2322E: Database error: SQL State ''IM002''; Native Error Code '0'; Error Text ''[unixODBC][Driver Manager]Data source name not found, and no default driver specified''.
The error has the following diagnostic information: SQL State ''IM002'' SQL Native Error Code '0' SQL Error Text ''[unixODBC][Driver Manager]Data source name not found, and no default driver specified''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error.
BIP8040E: Unable to connect to the database.
The database cannot be accessed with the specified user Id and password pair.
- Check that the database is running.
- Check that the database has an adequate number of database connections available for use.
- On Windows, check that an ODBC connection has been created.
- On systems that use $ODBCINI, check that the file pointed to by $ODBCINI has been correctly updated.
- On z/OS check the BIPDSNAO member of the component's dataset, and check the DB2 64bit ODBC driver (DSNAO64C) is in the STEPLIB.
- Use mqsisetdbparms to set the password to the correct value and try again.
|
I had enabled the ODBC trace and here is what I see (an excerpt).
| Code: |
[ODBC][10253][3059074768][2012-09-21 14:14:41.293][SQLConnect.c][3621]
Entry:
Connection = 0x9529d68
Server Name = [dsn::my_informix][length = 16 (SQL_NTS)]
User Name = [informix][length = 8 (SQL_NTS)]
Authentication = [********][length = 8 (SQL_NTS)]
[ODBC][10253][3059074768][2012-09-21 14:14:41.297][SQLConnect.c][3830]Error: IM002
[ODBC][10253][3059074768][2012-09-21 14:14:41.299][SQLError.c][430]
Entry:
Connection = 0x9529d68
SQLState = 0xbfbd4202
Native = 0xbfbd4210
Message Text = 0x952d2b0
Buffer Length = 1024
Text Len Ptr = 0xbfbd428a
[ODBC][10253][3059074768][2012-09-21 14:14:41.301][SQLError.c][467]
Exit:[SQL_SUCCESS]
SQLState = IM002
Native = 0xbfbd4210 -> 0
Message Text = [[unixODBC][Driver Manager]Data source name not found, and no default driver specified]
|
Note the server name dsn::my_informix. It does not exist in the odbc32.ini file pointed to by the $ODBCINI variable.
Scenario 2
I set the ID and password using mqsisetdbparms as described here with the command below.
| Code: |
mqsisetdbparms BRKR08 -n dsn::my_informix -d
mqsisetdbparms BRKR08 -n my_informix -u informix -p new00pwd
|
I test the connectivity with mqsicvp as described here with the command below.
| Code: |
mqsicvp BRKR08 -n my_informix
|
.
The connection goes through successfully. However, the Informix server does not report an incoming connection.
| Code: |
informix@nsubrahm:~$ onstat -g ses
Your evaluation license will expire on 2012-11-20 00:00:00
IBM Informix Dynamic Server Version 11.70.UC5TL -- On-Line -- Up 01:17:39 -- 160556 Kbytes
session #RSAM total used dynamic
id user tty pid hostname threads memory memory explain
41 informix - 0 - 0 12288 8984 off
25 informix - 0 - 1 483328 386496 off
24 informix - 0 - 1 454656 377728 off
23 informix - 0 - 1 540672 358552 off
6 informix - 0 - 0 12288 8984 off
4 informix - 0 - 0 12288 10272 off
3 informix - 0 - 0 12288 10272 off
2 informix - 0 - 0 12288 8984 off
|
My questions.
1. Isn't the Scenario 1 the way to specify user ID and password for a ODBC data source ?
2. If indeed Scenario 2 is the way to specify user ID and password for a ODBC data source, then why doesn't Informix report the connection ?
The odbc32.ini file:
| Code: |
[ODBC Data Sources]
my_informix=IBM Informix ODBC Driver
[my_informix]
Driver=/opt/IBM/Informix/lib/cli/iclis09b.so
Description=Informix database - with timeseries
ServerName=ol_informix1170
Database=my_informix
|
WMB 8.0.0.1 on Linux x86.
_________________
ALL opinions are welcome.
-----------------------------
Debugging tip:When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
---Sherlock Holmes |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Fri Sep 21, 2012 4:32 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You need an entry in the odbc ini file for all databases. the DBM file is only ever used in Toolkit, not in runtime and mqsicvp knows nothing about the dbm files.
I'm guessing you're not seeing a connection reported in Informix when mqsicvp works successfully because mqsicvp has already closed the connection by the time you look for it.
Secondly, the dsn:: prefix is only used by mqsisetdbparms, to distinguish a DSN from an FTP or SFTP or POP3 or etc kind of identity. So you shouldn't use it anywhere else. |
|
| Back to top |
|
|
| Cogito-Ergo-Sum |
| Posted: Tue Sep 25, 2012 10:38 pm Post subject: |
|
|
Master
Joined: 07 Feb 2006
Posts: 293
Location: Bengaluru, India
|
| mqjeff wrote: |
| You need an entry in the odbc ini file for all databases. |
The entry exists.
| mqjeff wrote: |
| the DBM file is only ever used in Toolkit, not in runtime and mqsicvp knows nothing about the dbm files. |
Guessed so.
| mqjeff wrote: |
I'm guessing you're not seeing a connection reported in Informix when mqsicvp works successfully because mqsicvp has already closed the connection by the time you look for it. |
I agree that, the mqsicvp does not appear in the connection entry list. Actually, what I meant in my question was as follows (sorry for not writing correctly). Once a message flow (that accesses a Informix database in a Mapping node) is up, shouldn't a connection entry appear in Informix ? Or, does the entry appear only at the instant when an Insert happens in the message flow ? (My message flow is expected to do an Insert.) In case of former, I don't see an entry in Informix. In case of latter, the row does not get inserted and no nothing in ${ExceptionList} or /var/log.
hmm...why didn't I enable ODBC trace while trying to insert ? Let me try this too and see if I get a clue ...
| mqjeff wrote: |
Secondly, the dsn:: prefix is only used by mqsisetdbparms, to distinguish a DSN from an FTP or SFTP or POP3 or etc kind of identity. So you shouldn't use it anywhere else. |
Well the Infocenter said so:
| Quote: |
-n ResourceName or AdapterName
(Required) This parameter identifies one of the following resources:
The ODBC data source for which the user ID and password pair are to be created or modified. The ResourceName takes the form dsn::datasource_name, where datasource_name identifies the data source name for the database to which you want to connect.
Data source names are used by the following nodes:
Compute
Database
DatabaseRetrieve
DatabaseRoute
Filter
Mapping |
_________________
ALL opinions are welcome.
-----------------------------
Debugging tip:When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
---Sherlock Holmes |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
