| Author |
Message
|
| tanishka |
 Posted: Mon Sep 13, 2010 7:41 am Post subject: Unable to connect Database on As/400 from windows MB7 |
 |
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
Hi all,
I am working on MB7 on windows , and try to connect Database on As/400. We got iseries access for windows, Iseries Access ODBC DRIVER.
I created SYSTEM DSN and specified in datasource property of compute node. I am getting below error.
DatabaseException BIP2393E: Database error: ODBC return code '-1' from data source ''AS400DB''.
The broker received an error when executing 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 causes are incorrect data source or table names. Correct either the database or the broker configuration.
2010-09-13 11:35:12.211993 2544 DatabaseException BIP2322E: Database error: SQL State ''28000''; Native Error Code '8015'; Error Text ''[IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8015 - CWBSY1006 - User ID is invalid, Password length = 10, Prompt Mode = Never, System IP Address = 172.17.78.128''.
The error has the following diagnostic information: SQL State ''28000'' SQL Native Error Code '8015' SQL Error Text ''[IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8015 - CWBSY1006 - User ID is invalid, Password length = 10, Prompt Mode = Never, System IP Address = 172.17.78.128''
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.
2010-09-13 11:35:12.212034 2544 DatabaseException 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.
Please help me. |
|
| Back to top |
|
 |
| Gaya3 |
| Posted: Mon Sep 13, 2010 7:53 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
you should test it from outside at first than from Message broker.
try to connect the remote database through command prompt.
do a tnsping [i guess its for oracle] but try to understand it for AS400
lets resolve that first and get in to message broker.  
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| tanishka |
| Posted: Mon Sep 13, 2010 11:32 am Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
| Through Iseries Navigator access connection workig fine. From broker i am getting exception. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Sep 13, 2010 11:36 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
What database is it on AS/400?
Which ODBC driver are you using? Is it one of the drivers supplied with WMBv7? If not, is it supported?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| tanishka |
| Posted: Mon Sep 13, 2010 11:46 am Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
DB2
iSeries Access ODBC Driver
It is not supplied with MB7 but it is supported. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Sep 13, 2010 11:50 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| tanishka wrote: |
| It is not supplied with MB7 but it is supported. |
Why not use the supplied DB2 driver?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| tanishka |
| Posted: Mon Sep 13, 2010 11:59 am Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
| MB7 they don't supply DB2 DRIVER. |
|
| Back to top |
|
|
| tanishka |
| Posted: Mon Sep 13, 2010 12:01 pm Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Sep 13, 2010 12:12 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
I know WMBv7 doesn't use a database internally, but I was unware it didn't ship with drivers.
As to the other, AS/400 is a foriegn country and I don't live there. If you're certain the driver is correct, then the "User Id is invalid" message gains credibility.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Amitha |
| Posted: Mon Sep 13, 2010 1:28 pm Post subject: |
|
|
Voyager
Joined: 20 Nov 2009
Posts: 80
Location: Newyork
|
| Quote: |
| 2010-09-13 11:35:12.211993 2544 DatabaseException BIP2322E: Database error: SQL State ''28000''; Native Error Code '8015'; Error Text ''[IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8015 - CWBSY1006 - User ID is invalid, Password length = 10, Prompt Mode = Never, System IP Address = 172.17.78.128''. |
Looks like you haven't set userID password for datasource using mqsisetdbparms |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Mon Sep 13, 2010 1:33 pm Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
| Amitha wrote: |
| Quote: |
| 2010-09-13 11:35:12.211993 2544 DatabaseException BIP2322E: Database error: SQL State ''28000''; Native Error Code '8015'; Error Text ''[IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8015 - CWBSY1006 - User ID is invalid, Password length = 10, Prompt Mode = Never, System IP Address = 172.17.78.128''. |
Looks like you haven't set userID password for datasource using mqsisetdbparms |
good catch, that could be one issue.
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| tanishka |
| Posted: Tue Sep 14, 2010 9:23 am Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
| Thanks amitha, That command works. |
|
| Back to top |
|
|
| tanishka |
| Posted: Tue Sep 14, 2010 9:47 am Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
One more question here
I am selecting records from database.
some of the columns may not containing the data.
my requirement is count the columns which contains data.
SET POtype.Result[] = PASSTHRU('SELECT R.COL, R.COL1, R.COL2, R.COL3 FROM schema1.table1 AS R WHERE R.COL4 = ?' TO Database.schema VALUES ('PO'));
SET Environment.Varible.ID[] = LIST{TRIM(POtype.Result.COL), TRIM(TPOtype.Result.COL1), TRIM(TPOtype.Result.COL2),
RIM(TPOtype.Result.COL3)};
Here is the trace data.
Environment:
( ['MQROOT' : 0x1448e88]
(0x01000000:Name):Varible = (
(0x03000000:NameValue):ID = '070293485' (CHARACTER)
(0x03000000:NameValue):ID = '70293' (CHARACTER)
(0x03000000:NameValue):ID = '' (CHARACTER)
SET TPIDCount = CARDINALITY(Environment.Varible.TPID[]);
it retuns 3.
I No need null records. What should i need to do to avoid to store null records in environment varible. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Sep 14, 2010 10:57 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You can modify your SELECT statement so it doesn't return null columns.
You can run an ESQL Select against your Environment tree.
You can probably use a COUNT clause in a select to return the number you need instead of using CARDINALITY. |
|
| Back to top |
|
|
| tanishka |
| Posted: Tue Sep 14, 2010 2:03 pm Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
Mqjeff,
This is actual requirement.
SET POtype.Result[] = PASSTHRU('SELECT R.COL, R.COL1, R.COL2 FROM schema1.table1 AS R WHERE R.COL3# = ?' TO Database.schema VALUES ('PO'));
COL - required(value should be there)
COL1 and COL2 are optional ( blank)
Select statement returns spaces if value not exist.
I should populate list from these three columns.
If col2 and col3 contains spaces the list should populate with COL1. |
|
| Back to top |
|
|
|
|
