asked on

DB2 and WMB

I am using WMB 7.0.0.1 and WMQ 7.0.1

I am trying to modify a Compute node in an existing flow to use a database

I created a database named IPC and a table named test

I used the setdbparms command and mqsicvp commands as follows:

+ mqsistop WQM1
BIP8071I: Successful command completion.
+ mqsisetdbparms WQM1 -n IPC -u db2instl -p db2instl
BIP8071I: Successful command completion.
+ mqsisetdbparms WQM1 -n dsn::DSN -u db2instl -p db2instl
BIP8071I: Successful command completion.
+ mqsistart WQM1
BIP8096I: Successful command initiation, check the system log to ensure that the component started without problem and that it continues to run without problem.
+ mqsicvp -n IPC -u db2inst1 -p db2inst1

BIP8270I: Connected to Datasource 'IPC' as user 'db2inst1'. The datasource platform is 'DB2/LINUXX8664', version '09.07.0000'.
===========================
databaseProviderVersion      = 09.07.0000
driverVersion                = 09.07.0000
driverOdbcVersion            = 03.51
......

Open in new window

Then without modifying mu flow, I run a test, and it works great.

I did not specify a uid or pw in the create command, but I did in the setdbparms command as can be seen above

Then when I make one single change to the flow.

I place the value IPC into the Compute node->Properties->Basic tab->Datasource field. I changed nothing else, not even in the Compute node ESQL.

I delete the old flow from the execution group and re-promote the new flow to the exe group

I then run the flow, but it fails

The syslog reports the following

(WQM1.a1)[63]BIP2322E: Database error: SQL State '08001'; Native Error Code '-30082'; Error Text '[IBM][CLI Driver] SQL30082N  Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID").  SQLSTATE=08001 '. : WQM1.3e9ded37-3301-0000-0080-ac1ae0224106: /build/S700_P/src/DataFlowEngine/ImbOdbc.cpp: 480: ImbOdbcHandle::checkRcInner: :

Open in new window

So my uid and pw do not work for IPC with WMB, so from mqm, I log into db2inst1 using the pw, and try to connect to IPC

$ su - db2inst1
Password:
[db2inst1@sandbox1 ~]$ db2 connect to IPC

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = IPC

Open in new window

This works from the command line, but WMB cant do it. I set the setdbparms command correctly. Any ideas ?

Kent Olsen

Hi Greensburo,

Just to be sure, can you set up a JDBC default, too?

mqsisetdbparms WQM1 -n jdbc::JDBC -u db2instl -p db2instl

Kent

Los Angeles1

ASKER

I just ran the command:

$ whoami
+ whoami
mqm
$ mqsisetdbparms WQM1 -n jdbc::JDBC -u db2instl -p db2instl
+ mqsisetdbparms WQM1 -n jdbc::JDBC -u db2instl -p db2instl
BIP8071I: Successful command completion.
$

Open in new window

However, the error still occurs when I do a test

Syslog reports:

Oct 25 18:50:53 localhost WebSphere Broker v7001[12354]: (WQM1.a1)[11]BIP2393E: Database error: ODBC return code '-1' from data source 'IPC' using ODBC driver manager 'libbipodbc.so'. : WQM1.3e9ded37-3301-0000-0080-ac1ae0224106: /build/S700_P/src/DataFlowEngine/ImbOdbc.cpp: 341: ImbOdbcHandle::checkRcInner: :
Oct 25 18:50:53 localhost WebSphere Broker v7001[12354]: (WQM1.a1)[11]BIP2322E: Database error: SQL State '08001'; Native Error Code '-30082'; Error Text '[IBM][CLI Driver] SQL30082N  Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID").  SQLSTATE=08001 '. : WQM1.3e9ded37-3301-0000-0080-ac1ae0224106: /build/S700_P/src/DataFlowEngine/ImbOdbc.cpp: 480: ImbOdbcHandle::checkRcInner: :

Open in new window

I haver ran the setdbparms command that way, does this open up all jdbc connections to any DataSource using those uid and pw ? That is what it looks like

Los Angeles1

ASKER

I run my profile and can get mqsicvp to work (but still get a secuirty error from the broker when I run a test). This is the profile results:

# su - mqm

MQSI 7.0.0.1
/opt/ibm/mqsi/7.0

+ mqsistop WQM1
BIP8019E: Component stopped.
A previous command was issued to stop this component or it has never been started.
This component may be started, changed or deleted.
+ mqsisetdbparms WQM1 -n IPC -u db2instl -p db2instl
BIP8071I: Successful command completion.
+ mqsistart WQM1
BIP8096I: Successful command initiation, check the system log to ensure that the component started without problem and that it continues to run without problem.
+ mqsicvp -n IPC -u db2inst1 -p db2inst1

BIP8270I: Connected to Datasource 'IPC' as user 'db2inst1'. The datasource platform is 'DB2/LINUXX8664', version '09.07.0000'.
===========================
databaseProviderVersion      = 09.07.0000
driverVersion                = 09.07.0000
.......

Open in new window

I am using this profile:

+ cat .profile
. /opt/ibm/mqsi/7.0/bin/mqsiprofile
. /home/db2inst1/sqllib/db2profile
export ODBCINI64=/var/mqsi/odbc/.odbc.ini
export ODBCINI=/var/mqsi/odbc/.odbc.ini
set -x
mqsistop WQM1
mqsisetdbparms WQM1 -n IPC      -u db2instl -p db2instl
mqsistart WQM1
mqsicvp -n IPC -u db2inst1 -p db2inst1

Open in new window

I checked for the existance of the ODBC file, as follows:

$ ls -la $ODBCINI
+ ls --color=tty -la /var/mqsi/odbc/.odbc.ini
-rw-rw-r-- 1 mqm mqbrkrs 416 Oct 27 11:28 /var/mqsi/odbc/.odbc.ini
$
$
$ ls -la $ODBCINI64
+ ls --color=tty -la /var/mqsi/odbc/.odbc.ini
-rw-rw-r-- 1 mqm mqbrkrs 416 Oct 27 11:28 /var/mqsi/odbc/.odbc.ini

Open in new window

Unless I am missing something, the files have the correct permissions and are in the correct group

$ cat $ODBCINI
+ cat /var/mqsi/odbc/.odbc.ini
; odbc.ini
[ODBC Data Sources]
IPC=IBM DB2 ODBC Driver

;# DB2 stanza
[IPC]
DRIVER=/opt/ibm/mqsi/7.0/lib/libdb2Wrapper.so
Description=IPC DB2 ODBC Database
Database=IPC

[ODBC]
;# To turn on ODBC trace set Trace=1
Trace=1
;#TraceOptions=3
TraceFile=/tmp/odbc/odbctrace.out
TraceDll=/opt/ibm/mqsi/7.0/ODBC64/V6.0/lib/odbctrac.so
InstallDir=/opt/ibm/mqsi/7.0/ODBC64/V6.0
UseCursorLib=0
IANAAppCodePage=4
UNICODE=UTF-8

Open in new window

I echeked the existence of the files:

$ ls -la /opt/ibm/mqsi/7.0/lib/libdb2Wrapper.so
+ ls --color=tty -la /opt/ibm/mqsi/7.0/lib/libdb2Wrapper.so
-rwxr-xr-x 1 bin bin 107295 Jul  7  2010 /opt/ibm/mqsi/7.0/lib/libdb2Wrapper.so

$ ls -la /opt/ibm/mqsi/7.0/ODBC64/V6.0
+ ls --color=tty -la /opt/ibm/mqsi/7.0/ODBC64/V6.0
total 28
drwxr-xr-x 4 root root 4096 Oct 13 15:01 .
drwxr-xr-x 4 root root 4096 Oct 13 15:13 ..
-rwxr-xr-x 1 bin  bin  2200 Jun 22  2010 DDUK.LIC
drwxr-xr-x 2 root root 4096 Oct 13 15:01 lib
drwxr-xr-x 3 root root 4096 Oct 13 15:13 locale
-rwxr-xr-x 1 bin  bin  5037 Jun 22  2010 odbc.ini

Open in new window

Everything seems to be in order.

The odd thing is that mqsicvp works, but WMB broker does not and says I have a security error

If I delete the .odbc.ini file, the mqsicvp will NOT work.

Any ideas

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Los Angeles1

ASKER

Turns out I used the wrong uid and pw. I confused the number one witht he letter 'l'

I used the following command to check

mqsicvp WQM1 -n IPC

Kent Olsen

If these silly things would just do what we want instead of what we tell them! :)

Glad you saw that!

Kent