| Author |
Message
|
| AlexeiSkate |
 Posted: Thu Jun 06, 2002 3:11 pm Post subject: Need help in defining an ODBC data source |
 |
|
Centurion
Joined: 10 Apr 2002
Posts: 123
|
I've defined a db and tables in my local UDB2 on my PC, which is also where the WQSI is installed. I've created an ODBC data source name through the Administrative Tools/Data Soruces(ODBC) menu on window2000. I'm trying to execute a select in a compute node but get error of datasource name not found.
When you create an ODBC data source, do you need to use the same id/psw that you used to create your current broker, or should you be able to use any user id/psw that has db2 administrative authority. When I attempted to create a data source using the same broker userid/psw (eg. usid - mqsiuid), I get an exception like MQSIUID.TABLE_NAME is not a valid table. Is this because I have to use "mqsiuid" to create the TABLE_NAME in this instance ?
Has anyone encountered any issue with database access and can provide any tips ?
Thanks,
Alex |
|
| Back to top |
|
 |
| kirani |
| Posted: Thu Jun 06, 2002 3:22 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Alex,
Try to test your odbc connection by clicking on configure button.
In your ODBC DSN you can use different user-id/pwd. Make sure the user-id exist on the system and has proper authority to perform database operations.
| Quote: |
| When I attempted to create a data source using the same broker userid/psw (eg. usid - mqsiuid), I get an exception like MQSIUID.TABLE_NAME is not a valid table. Is this because I have to use "mqsiuid" to create the TABLE_NAME in this instance ? |
Yes. Alternatively, you can modify your select statement to refer to tables owned by different users.
| Code: |
| select from [b]userid.[/b]TABLE_NAME ..... |
Regards,
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| AlexeiSkate |
| Posted: Thu Jun 06, 2002 4:29 pm Post subject: |
|
|
Centurion
Joined: 10 Apr 2002
Posts: 123
|
I tested my odbc source definition "DB2DATASOURCE" using the configuration button and it connected ok. The exception I'm getting is: "[IBM][CLI DRIVER] SQL100N DB2DATASOURCE IS NOT A VALID DATABASE NAME. SQLSTATE=2E00".
Eventhough the mentioned data source is defined, I can't figure out why QMSI isn't able to connect to it. Is there anything special that need to be done when defining an ODBC datasource so that WQSI can interact with it?
thanks,
- Alex |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Jun 06, 2002 4:46 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
For some reason your broker is not able to connect to database at all.
Do you see any error logged into Event Viewer?
I hope you have defined System ODBC connection on Broker machine and not on Control Center machine.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| Tibor |
| Posted: Thu Jun 06, 2002 11:54 pm Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
| AlexeiSkate wrote: |
I tested my odbc source definition "DB2DATASOURCE" using the configuration button and it connected ok. The exception I'm getting is: "[IBM][CLI DRIVER] SQL100N DB2DATASOURCE IS NOT A VALID DATABASE NAME. SQLSTATE=2E00".
Eventhough the mentioned data source is defined, I can't figure out why QMSI isn't able to connect to it. Is there anything special that need to be done when defining an ODBC datasource so that WQSI can interact with it?
|
Alex,
It seems DB2/CLI problem not an MQSI problem. I think, DB2 doesn't like DB2DATASOURCE for db name because name length more than 8 characters. |
|
| Back to top |
|
|
| kirani |
| Posted: Fri Jun 07, 2002 6:14 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
| Tibor wrote: |
I think, DB2 doesn't like DB2DATASOURCE for db name because name length more than 8 characters. |
Tibor,
I am using ODBC DSN greater than 8 characters and it is working fine with WMQI. I don't think this is name length problem.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| AlexeiSkate |
| Posted: Fri Jun 07, 2002 6:15 am Post subject: |
|
|
Centurion
Joined: 10 Apr 2002
Posts: 123
|
Tibor,
You were right about the too long data source name. Thanks.
I now have a datasource DB2DS. Database alias is TESTGFIS and table name if MERCHANT_NUMBER. In my compute node I specify the DB2DS datasource and MERCHANT_NUMBER table name. My select statement is:
SELECT ITEM X.CHAIN_NUMBER FROM DATABASE.MERCHANT_NUMBER AS X WHERE X.MERCHANT_NUMBER = '666';
I'm getting the error: "SQL3282N - supplied credentials not valid". I've spelled the datasource and table name and field names correctly. I've tried specifying several psw and userid in the db2cli.ini file for this datasource that I thought might be the problem, but I'm having no luck. |
|
| Back to top |
|
|
| AlexeiSkate |
| Posted: Sat Jun 08, 2002 12:35 pm Post subject: |
|
|
Centurion
Joined: 10 Apr 2002
Posts: 123
|
Ok, I finally found the right uid and psw to set for my Data Source in the db2cli.ini file, so my compute node can access the db now. Thanks for everyone's reply.
- Alex |
|
| Back to top |
|
|
| Tibor |
| Posted: Sun Jun 09, 2002 11:03 pm Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
Kiran
| Quote: |
| I am using ODBC DSN greater than 8 characters and it is working fine with WMQI. I don't think this is name length problem. |
ODBC supports long DSN names, but the problem was coming from database name at Alex.
Tibor |
|
| Back to top |
|
|
|
|
