| Author |
Message
|
| Gideon |
 Posted: Tue Oct 25, 2011 10:50 am Post subject: Compute node and DataSource |
 |
|
Chevalier
Joined: 18 Aug 2009
Posts: 403
|
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:
| Code: |
+ 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
...... |
Then without modifying mu flow, I run a test, and it works great.
From the following site:
http://publib.boulder.ibm.com/infocenter/wmbhelp/v7r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fac04660_.htm
the doc states:
| Code: |
| On the Basic tab, specify in Data Source the name by which the appropriate database is known on the system on which this message flow is to run. The broker connects to this database with user ID and password information that you have specified on the mqsicreatebroker, mqsichangebroker, or mqsisetdbparms command. |
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
What did I do wrong ? |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Tue Oct 25, 2011 11:17 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Oct 25, 2011 11:33 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
So. The first mqsisetdbparms command should be the one that is in play here.
The second one is entirely meaningless.
You should rerun the mqsicvcp command, but without specifying the -u and -p. |
|
| Back to top |
|
|
| Gideon |
| Posted: Tue Oct 25, 2011 12:46 pm Post subject: |
|
|
Chevalier
Joined: 18 Aug 2009
Posts: 403
|
I tried the masicvp command without the -u and -p, but it does not work and gives the following error:
| Code: |
BIP8005E: Flag or argument 'u' missing.
The command will result in no action being taken, a required flag or a flag's argument is missing.
Correct and reissue the command. |
The error reported by syslog was:
| Code: |
| Oct 25 16:18:48 localhost WebSphere Broker v7001[4141]: (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: : |
So my uid and pw do not work for IPC, so from mqm, I log into db2inst1 using the pw, and try to connect to IPC
| Code: |
$ 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 |
This works, but WMB cant do it. I set the setdbparms command correctly. Dont quite understand. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Oct 25, 2011 1:07 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Using a name of "dsn:DSN" for mqsisetdbparms creates a user credential for a dsn that has the EXACT NAME of "DSN". If you have no DSN named "DSN", then the command you posted DOES NOTHING.
Using mqsicvp with just the -n parameter is the correct and exact syntax needed to verify that the mqsisetdbparms command has been issued for the DSN named and that the mqsisetdbparms command created a valid credential. |
|
| Back to top |
|
|
| Gideon |
| Posted: Tue Oct 25, 2011 1:19 pm Post subject: |
|
|
Chevalier
Joined: 18 Aug 2009
Posts: 403
|
I took out the erroneous setdbparms command, as follows:
| Code: |
+ mqsistop WQM1
BIP8071I: Successful command completion.
+ 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 |
Then I ran the mqsicvp with only the -n option, but I get the foloowing (I am using WMB 7.0.0.1):
| Code: |
$ mqsicvp -n IPC
+ mqsicvp -n IPC
BIP8872W: Verifies a broker. On Version 7 only of the product, verifies a user datasource to be used within the broker, or compares primary and secondary datasources for equivalence.
Syntax (1):
mqsicvp <brokerName>
Syntax (2):
mqsicvp <brokerName> -n <primaryDatasource> [-c <secondaryDatasource>] [-v]
Syntax (3):
mqsicvp -n <primaryDatasource> -u <primaryDatasourceUserId> -p <primaryDatasourcePassword> [-c <secondaryDatasource> -i <secondaryDatasourceUserId> -a <secondaryDatasourcePassword>] [-v]
Command options, syntax (1):
<brokerName>
The broker name.
Command options, syntax (2):
<brokerName>
The broker name.
-n <primaryDatasource>
The name of the datasource to verify.
This datasource must have been previously associated with the broker using mqsisetdbparms.
-c <secondaryDatasource>
The name of the second datasource.
If specified, the function of this datasource is compared with that of <primaryDatasource>.
This datasource must have been previously associated with the broker using mqsisetdbparms.
-v
Causes extra, untranslated, diagnostics related to supported CASTS to be output by the command.
Command options, syntax (3):
-n <primaryDatasource>
The name of the datasource to verify.
-u <primaryDatasourceUserId>
The user identifier to be used to connect to <primaryDatasource>.
-p <primaryDatasourcePassword>
The password associated with <primaryDatasourceUserId>
-c <secondaryDatasource>
The name of the second datasource.
If specified, the function of this datasource is compared with that of <primaryDatasource>.
-i <secondaryDatasourceUserId>
The user identifier to be used to connect to <secondaryDatasource>.
-a <secondaryDatasourcePassword>
The password associated with <secondaryDatasourceUserId>
-v
Causes extra, untranslated, diagnostics related to supported CASTS to be output by the command.
BIP8005E: Flag or argument 'u' missing.
The command will result in no action being taken, a required flag or a flag's argument is missing.
Correct and reissue the command.
$ |
|
|
| Back to top |
|
|
| Gideon |
| Posted: Tue Oct 25, 2011 1:26 pm Post subject: |
|
|
Chevalier
Joined: 18 Aug 2009
Posts: 403
|
I still get the error after I killed the second meaningless setdbparms command
Not sure why it wont accept my uid and pw
I am specifying that on the setdbparms command
Do I need to specify that on the create broker command as well ? |
|
| Back to top |
|
|
| MrSmith |
| Posted: Wed Oct 26, 2011 12:31 am Post subject: |
|
|
Master
Joined: 20 Mar 2008
Posts: 215
|
Shoot me down in flames but I see no mention of the odbc file entries for the database and its associated driver as the initial post mentions a NEW database, has the database been defined correctly in this file and move along the checklist that normally would be done when setting a DSN connection up, permissions etc, along with the correct driver etc. etc. there is no mention of this in Gideons post and although the quote would imply that its just a profile issue with the security, if I may be so bold DB2 can often give less than accurate return code situations?
_________________
-------- *
“Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.” |
|
| Back to top |
|
|
| Gideon |
| Posted: Thu Oct 27, 2011 12:30 pm Post subject: |
|
|
Chevalier
Joined: 18 Aug 2009
Posts: 403
|
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:
| Code: |
# 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
....... |
I am using this profile:
| Code: |
+ 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 |
I checked for the existance of the ODBC file, as follows:
| Code: |
$ 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 |
Unless I am missing something, the files have the correct permissions and are in the correct group
| Code: |
$ 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
|
I echeked the existence of the files:
| Code: |
$ 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 |
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 |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Oct 27, 2011 12:48 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Not a 100% sure but you better check
mqsisetdbparms uses db2instl whereas the right name would be db2inst1
L (lower case) vs 1. Depending on the font it is quite hard to see a difference.
This would explain your security problem. 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| simon.harrison |
| Posted: Thu Oct 27, 2011 2:25 pm Post subject: |
|
|
Newbie
Joined: 27 Oct 2011
Posts: 1
|
You are missing the <brokername> from your mqsicvp command
| Code: |
| mqsicvp <brokername> -n IPC |
|
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Oct 27, 2011 10:12 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| simon.harrison wrote: |
You are missing the <brokername> from your mqsicvp command
| Code: |
| mqsicvp <brokername> -n IPC |
|
Actually, BOTH methods are valid.
firstly, this method
| Code: |
| mqsicvp -n IPC -u db2inst1 -p db2inst1 |
Checks the validity of the IPC Stanza in the $ODBCINI file.
If that works then
| Code: |
| mqsicvp <brokername> -n IPC |
checks to see if the broker username/password can login to the target db.
If this passes then you are good to go and use the DSN in your flows.
This is IMHO a MEGA improvement over how it was all done in previous versions (V2.01 to V6.1.0.x)
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Gideon |
| Posted: Fri Oct 28, 2011 10:46 am Post subject: |
|
|
Chevalier
Joined: 18 Aug 2009
Posts: 403
|
I checked the mqsicvp <brk>? -n IPC, and it gave a uid / pw error
Turns out the 'l' should have been a '1' (one)
Thanks for all the help |
|
| Back to top |
|
|
| vininx |
| Posted: Mon Dec 12, 2011 5:54 pm Post subject: |
|
|
Acolyte
Joined: 13 Oct 2009
Posts: 69
|
Hi All,
I am having a message flow which is throwing below error in syslog while connecting to the DB,
| Code: |
BIP2322E: Database error: SQL State 'IM002'; Native Error Code '0'; Error Text '[DataDirect][ODBC lib] System information file not found. Please check the ODBCINI environment variable.' |
I have associated the user and password with mqsisetdbparms. When I use mqsicvp command with user name and password to test the DSN I am able to connec to the database, but when I issue without user name and password, it is throwing the below error,
| Code: |
BIP8272E: Datasource 'ORA_DSN' has not been associated with broker 'MB7BROKER'.
This invocation of the mqsicvp command requires that the datasource name has been associated with the broker, using the mqsisetdbparms command.
Check that this datasource has been associated with the broker |
I am using WMB7.0.0.3 in Linux.I have read that once when mqsisetdbparms is set we need to restart the execution group for it to connect to the DB. I did that , but, still the above error occurs.I havent tried by bouncing the broker. Please confirm whether we need to bounce the broker for mqsisetdbparms to work or am I missing anything in configuration?
My Config settings:-
----------------------
1) Edited odbc.ini to configure the remote DB. Edited all 3 stanzas with appropriate values
2) The odbc.ini has mqm:mqbrkrs, placed in /var/mqsi/odbc/odbc.ini
3) Set the ODBCINI variable in my own shell script and placed in /var/mqsi/common/profile path
4) Used mqsisetdbparms for authorizing user database.
5) Restarted execution group. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Dec 13, 2011 2:33 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| vininx wrote: |
| 3) Set the ODBCINI variable in my own shell script and placed in /var/mqsi/common/profile path |
This is excellent, but be aware that it only takes effect if you apply mqsiprofile again.
Otherwise, hopefully the mods will move this to a new thread, since it's a new issue. |
|
| Back to top |
|
|
|
|
