[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DB2 and WMB

Posted on 2011-10-25
6
Medium Priority
?
2,978 Views
Last Modified: 2013-12-10
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 ?
0
Comment
Question by:Los Angeles1
  • 3
  • 3
6 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 37028167
Hi Greensburo,

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

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


Kent
0
 

Author Comment

by:Los Angeles1
ID: 37028275
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

0
 

Author Comment

by:Los Angeles1
ID: 37041131
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 37041156
Hi Greensburo,

Is anything showing up in the system log?  A security reject (bad user/password) should be logged.  

Let's check to make sure that the correct user name is being passed.  I'm suspicious that the wrong user (perhaps a default) is configured into the middleware or application and it's getting passed instead of the user name that you're trying to configure.


Kent
0
 

Author Comment

by:Los Angeles1
ID: 37046894
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
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 37046908

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


Glad you saw that!


Kent
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

-Xmx and -Xms are the two JVM options often used to tune JVM heap size.   Here are some common mistakes made when using them:   Assume BigApp is a java class file for the below examples. 1.         Missing m, M, g or G at the end …
Verbose logging is used to diagnose garbage collector problems. By default, -verbose:gc output is written to either native_stderr.log or native_stdout.log.   It is also possible to redirect the logs to a user-specified file. This article will de…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question