| Author |
Message
|
| Cogito-Ergo-Sum |
 Posted: Sun Feb 10, 2013 7:09 am Post subject: mqsicvp and ODBC DSN confusion. |
 |
|
Master
Joined: 07 Feb 2006
Posts: 293
Location: Bengaluru, India
|
This is WMB 8.0.0.1 on Windows NT.
I set a data source, its user ID and password using mqsisetdbparms.
| Code: |
C:\Program Files\IBM\MQSI\8.0.0.1>mqsisetdbparms BRKR01 -n dsn::SPDB -u ADMINISTRATOR
Enter password for user ID
Retype password for user ID.
BIP8071I: Successful command completion.
|
To verify the same, I used the mqsicvp command as shown in examples here.
| Code: |
C:\Program Files\IBM\MQSI\8.0.0.1>mqsicvp BRKR01 -n SPDB
BIP8272E: Datasource 'SPDB' has not been associated with broker 'BRKR01'.
This invocation of the mqsicvp command requires that the datasource name has bee
n associated with the broker, using the mqsisetdbparms command.
Check that this datasource has been associated with the broker.
|
That cannot be right as I ran mqsisetdbparms moments ago. So, this time, I ran mqsicvp with the dsn:: prefix.
| Code: |
C:\Program Files\IBM\MQSI\8.0.0.1>mqsicvp BRKR01 -n dsn::SPDB
BIP2393E: Database error: ODBC return code '-1' from data source ''dsn::SPDB'' using ODBC driver manager ''odbc32.dll''.
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''[Microsoft][ODBC 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 ''[Microsoft][ODBC 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.
|
The message about ID and password is wrong as I am able to connect to it successfully.
| Code: |
db2 => CONNECT TO SPDB USER ADMINISTRATOR
Enter current password for ADMINISTRATOR:
Database Connection Information
Database server = DB2/NT 10.1.0
SQL authorization ID = ADMINIST...
Local database alias = SPDB
|
I looked at the message carefully.
| Code: |
BIP2322E: Database error: SQL State ''IM002''; Native Error Code '0'; Error Text''[Microsoft][ODBC 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 ''[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'' |
What if, I created a ODBC DSN on the Windows machine as dsn::SPDB ?
Yes ! That worked !!
D'oh! Is this valid ?
_________________
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: Sun Feb 10, 2013 7:21 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You skipped the part where you said that you created ANY odbc DSN in the first place.
You started by saying "I added a username and password".
mqsisetdbparms does absolutely nothing to create a datasource.
On windows, which you appear to be running, you *must* create a SYSTEM dsn, rather than a USER dsn.
You may have to recycle the broker after creating it, for the broker to see the DSN, too.
Note that the operative word there is 'may'. |
|
| Back to top |
|
|
| sarasu |
| Posted: Mon Feb 11, 2013 7:01 am Post subject: |
|
|
Master
Joined: 02 Feb 2006
Posts: 229
|
On windows 7, I ahve created a System DSN and recycled the Broker too.
But Still getting the same error.
The driver we need to use at run time is any different?
I am able to see the DB from the tool kit though.
Thanks |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Feb 11, 2013 7:05 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| sarasu wrote: |
| On windows 7, I ahve created a System DSN and recycled the Broker too. But Still getting the same error. The driver we need to use at run time is any different? I am able to see the DB from the tool kit though. Thanks |
So what do you think is the problem? When you take a service trace, what does the service trace tell you?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Feb 11, 2013 7:09 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| sarasu wrote: |
On windows 7, I ahve created a System DSN and recycled the Broker too.
But Still getting the same error. |
Do you post under 2 different ids? Or are you getting a similar problem?
Post all the commands you used and the responses.
| sarasu wrote: |
| The driver we need to use at run time is any different? |
If you're on a different OS. You should never use any ODBC that is not supplied with the product.
| sarasu wrote: |
| I am able to see the DB from the tool kit though. |
Well that's nice. This shows that the JDBC you set up for the Toolkit to use is working. Now just set up the ODBC up correctly and you'll be away.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| NealM |
| Posted: Mon Feb 11, 2013 8:57 am Post subject: |
|
|
Master
Joined: 22 Feb 2011
Posts: 230
Location: NC or Utah (depends)
|
| Use the 3rd form of the mqsicvp command to see if your odbc.ini is set up properly. The command in that format does that independent of the Broker. |
|
| Back to top |
|
|
| sarasu |
| Posted: Mon Feb 11, 2013 9:45 am Post subject: |
|
|
Master
Joined: 02 Feb 2006
Posts: 229
|
After I have created the System DSN configurations, the Test Result are as below.
Microsoft SQL Server ODBC Driver Version 06.01.7601
Running connectivity tests...
Attempting connection
Connection established
Verifying option settings
Disconnecting from server
TESTS COMPLETED SUCCESSFULLY!
======
I have followed the steps from the link below.
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/topic/com.ibm.etools.mft.doc/ah14442_.htm
==========
<Text>BIP2230E: Error detected whilst processing a message in node 'gen/operation1_Request_Response.Compute'.
The message broker detected an error whilst processing a message in node 'gen/operation1_Request_Response.Compute'. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error. : F:\build\slot1\S800_P\src\DataFlowEngine\ImbComputeNode.cpp: 497: ImbComputeNode::evaluate: ComIbmComputeNode: gen/Test_Service#FCMComposite_1_8.gen/operation1_Request_Response#FCMComposite_1_1
BIP2393E: Database error: ODBC return code '-1' from data source ''DEVDB'' using ODBC driver manager ''odbc32.dll''.
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. : F:\build\slot1\S800_P\src\DataFlowEngine\ImbOdbc.cpp: 2250: ImbOdbcConnection::checkRcInner: :
BIP2322E: Database error: SQL State ''28000''; Native Error Code '18456'; Error Text ''[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'myid'.''.
The error has the following diagnostic information: SQL State ''28000'' SQL Native Error Code '18456' SQL Error Text ''[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'myid'.''
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. : F:\build\slot1\S800_P\src\DataFlowEngine\ImbOdbc.cpp: 2428: ImbOdbcConnection::checkRcInner: :
BIP2322E: Database error: SQL State ''IM006''; Native Error Code '0'; Error Text ''[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed''.
The error has the following diagnostic information: SQL State ''IM006'' SQL Native Error Code '0' SQL Error Text ''[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed''
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. : F:\build\slot1\S800_P\src\DataFlowEngine\ImbOdbc.cpp: 2428: ImbOdbcConnection::checkRcInner: :</Text>
========== |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Feb 11, 2013 9:56 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Yes, that tells you exactly what went wrong.
The rest of this thread tells you exactly how to fix it. |
|
| Back to top |
|
|
| sarasu |
| Posted: Mon Feb 11, 2013 11:03 am Post subject: |
|
|
Master
Joined: 02 Feb 2006
Posts: 229
|
But when i issue the mqsicvp command I ahve some positive results as below.
==========
C:\Program Files (x86)\IBM\MQSI\8.0.0.1>mqsicvp MB8BROKER -n DEVTEST
BIP8270I: Connected to Datasource 'DEVTEST' as user 'testid'. The datasource platform is 'Microsoft SQL Ser
ver', version '09.00.5000'.
===========================
databaseProviderVersion = 09.00.5000
driverVersion = 06.01.7601
driverOdbcVersion = 03.52
driverManagerVersion = 03.80.7601.0000
driverManagerOdbcVersion = 03.80.0000
databaseProviderName = Microsoft SQL Server
datasourceServerName = DEV
databaseName = devtest
odbcDatasourceName = DEVTEST
driverName = SQLSRV32.DLL
supportsStoredProcedures = Yes
procedureTerm = stored procedure
accessibleTables = Yes
accessibleProcedures = Yes
identifierQuote = "
specialCharacters = None
describeParameter = Yes
schemaTerm = owner
tableTerm = table
sqlSubqueries = 31
activeEnvironments = 0
maxDriverConnections = 0
maxCatalogNameLength = 128
maxColumnNameLength = 128
maxSchemaNameLength = 128
maxStatementLength = 524288
maxTableNameLength = 128
supportsDecimalType = Yes
supportsDateType = No
supportsTimeType = No
supportsTimeStampType = Yes
supportsIntervalType = No
supportsAbsFunction = Yes
supportsAcosFunction = Yes
supportsAsinFunction = Yes
supportsAtanFunction = Yes
supportsAtan2Function = Yes
supportsCeilingFunction = Yes
supportsCosFunction = Yes
supportsCotFunction = Yes
supportsDegreesFunction = Yes
supportsExpFunction = Yes
supportsFloorFunction = Yes
supportsLogFunction = Yes
supportsLog10Function = Yes
supportsModFunction = Yes
supportsPiFunction = Yes
supportsPowerFunction = Yes
supportsRadiansFunction = Yes
supportsRandFunction = Yes
supportsRoundFunction = Yes
supportsSignFunction = Yes
supportsSinFunction = Yes
supportsSqrtFunction = Yes
supportsTanFunction = Yes
supportsTruncateFunction = Yes
supportsConcatFunction = Yes
supportsInsertFunction = Yes
supportsLcaseFunction = Yes
supportsLeftFunction = Yes
supportsLengthFunction = Yes
supportsLTrimFunction = Yes
supportsPositionFunction = No
supportsRepeatFunction = Yes
supportsReplaceFunction = Yes
supportsRightFunction = Yes
supportsRTrimFunction = Yes
supportsSpaceFunction = Yes
supportsSubstringFunction = Yes
supportsUcaseFunction = Yes
supportsExtractFunction = Yes
supportsCaseExpression = Yes
supportsCastFunction = Yes
supportsCoalesceFunction = Yes
supportsNullIfFunction = Yes
supportsConvertFunction = Yes
supportsSumFunction = Yes
supportsMaxFunction = Yes
supportsMinFunction = Yes
supportsCountFunction = Yes
supportsBetweenPredicate = No
supportsExistsPredicate = Yes
supportsInPredicate = No
supportsLikePredicate = No
supportsNullPredicate = Yes
supportsNotNullPredicate = Yes
supportsLikeEscapeClause = Yes
supportsClobType = No
supportsBlobType = No
charDatatypeName = char
varCharDatatypeName = varchar
longVarCharDatatypeName = text
clobDatatypeName = N/A
timeStampDatatypeName = datetime
binaryDatatypeName = binary
varBinaryDatatypeName = varbinary
longVarBinaryDatatypeName = image
blobDatatypeName = N/A
intDatatypeName = int
doubleDatatypeName = N/A
varCharMaxLength = 0
longVarCharMaxLength = 0
clobMaxLength = 0
varBinaryMaxLength = 0
longVarBinaryMaxLength = 0
blobMaxLength = 0
timeStampMaxLength = 0
identifierCase = Mixed
escapeCharacter = \
longVarCharDatatype = -1
clobDatatype = 0
longVarBinaryDatatype = -4
blobDatatype = 0
BIP8273I: The following datatypes and functions are not natively supported by datasource 'DEVTEST' using this O
DBC driver: Unsupported datatypes: 'DATE, TIME, INTERVAL, CLOB, BLOB' Unsupported functions: 'POSITION, BETWEEN, IN, LIK
E'
Examine the specific datatypes and functions not supported natively by this datasource using this ODBC driver.
When using these datatypes and functions within ESQL, the associated data processing is done within WebSphere Message Br
oker rather than being processed by the database provider.
Note that "functions" within this message can refer to functions or predicates.
BIP8071I: Successful command completion.
C:\Program Files (x86)\IBM\MQSI\8.0.0.1> |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Feb 11, 2013 11:17 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
yeah, so it works for a different userid.
which makes perfect sense, given why it told you that it failed. |
|
| Back to top |
|
|
| sarasu |
| Posted: Mon Feb 11, 2013 12:58 pm Post subject: |
|
|
Master
Joined: 02 Feb 2006
Posts: 229
|
I am still getting the below.
C:\Program Files (x86)\IBM\MQSI\8.0.0.1>mqsicvp MB8BROEKR -n DEVTEST
BIP8272E: Datasource 'DEVTEST' has not been associated with broker 'MB8BROEKR'.
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 have created System DSN, mqsisetdbparms, recycled broker.
I am still not getting what else I am missing here.
This is happening with my local broker only.
When i tried with z/linux broker, i am able to do everything using the same SQL DB.
Can we configure the same SQL DB from different brokers with different Operation systems ? |
|
| Back to top |
|
|
| NealM |
| Posted: Mon Feb 11, 2013 1:05 pm Post subject: |
|
|
Master
Joined: 22 Feb 2011
Posts: 230
Location: NC or Utah (depends)
|
| try spelling your broker name with some consistency..... |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Feb 11, 2013 1:07 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
I'm assuming that "MQBROEKR" is a typo in this post, not in what you ran.
You ran two mqsicvp commands, both apparently identical.
One succeeded for a user 'testid'.
The other, apparently identical command, failed because it couldn't find the datasource named.
There's no information that you have presented that shows why one command succeeded and the other, apparently identical, command failed. Are they on the same machine, or not?
The broker error you showed said that the Broker tried to connect as user 'myid', and did not succeed because 'myid' is not authorized. Note that 'myid' is not the same user as 'testid', so if mqsicvp works for 'testid', there's no reason that it will be expected to work for 'myid'.
But that was also for a DSN named DEVDB, not for a DSN named DEVTEST.
I do not at this point understand what steps you have taken in what order on what machine for what DSNs and what is working and what is not working.
I also have stopped caring to answer. |
|
| Back to top |
|
|
| NealM |
| Posted: Mon Feb 11, 2013 1:11 pm Post subject: |
|
|
Master
Joined: 22 Feb 2011
Posts: 230
Location: NC or Utah (depends)
|
| Quote: |
| I'm assuming that "MQBROEKR" is a typo in this post, not in what you ran. |
And I'm betting the typo was in his command. I ran exactly what he entered on my machine, which of course does not have either broker name, nor DSN, and got exactly the error that he got
| Code: |
mqbrkr@lnxm2dev:TEST:/var/mqsi/odbc> mqsicvp MB8BROEKR -n DEVTEST
BIP8272E: Datasource 'DEVTEST' has not been associated with broker 'MB8BROEKR'.
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.
|
|
|
| Back to top |
|
|
| sarasu |
| Posted: Mon Feb 11, 2013 1:32 pm Post subject: |
|
|
Master
Joined: 02 Feb 2006
Posts: 229
|
Guys thanks for your responses and patience on my questions.
You are correct that Its a Typo in my broker name.
====
I just don't want to reveal the real names and just reiterating with common names (like TESTID DEVTEST etc..) because of internal reasons.
I am using the correct values only all the time. I have only one ID & pwd to connect to the DB. Also created one System DSN for data source.
===============================
ALL I DID SO FAR.
Step1: Created System DSN.
Step2: mqsistop MB8BROKER
mqsisetdbparms MB8BROKER –n DEVTEST –u testid –p pwd
mqsistart MB8BROEKR
Step3: Created a simple test service with simple select sql query under the compute node. Used the data source name under the compute node properties.
Step4: Created and deployed the bar file.
Do you think anything else is missing in order to connect to the Broker and retrieve the values from the DB here? |
|
| Back to top |
|
|
|
|
