| Author |
Message
|
| pcoffey |
 Posted: Thu Apr 27, 2006 4:36 am Post subject: WMB 6 against Oracle 10g - SQL SELECT failure |
 |
|
Apprentice
Joined: 03 Feb 2006
Posts: 33
|
Hi,
Running WMB 6.01 on AIX against an Oracle 10g database, I find that a simple SELECT statement against a table containing an INTEGER column fails, i.e. returns an empty result set. Instead I have to use a PASSTHRU to get the correct result set. Has anyone noticed anything similar? Are there any similar 'gotcha's' using this configuration?
Thanks for any tips,
Pauline |
|
| Back to top |
|
 |
| mgk |
| Posted: Thu Apr 27, 2006 4:37 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
This should work without PASSTHRU. Are you sure you are using the correct DataDirect driver? Can you post the error message (if any) along with the table definition and your ESQL if you are?
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| pcoffey |
| Posted: Thu Apr 27, 2006 5:45 am Post subject: |
|
|
Apprentice
Joined: 03 Feb 2006
Posts: 33
|
Hi,
Thanks for the prompt response.
I can re-create the problem on both Windows and AIX. On Windows the driver is "MQSeries Data Direct Technologies 5.0 32-BIT Oracle". On AIX, it is "Datadirect 5.0 Oracle".
There is no error message - it's just that no data is returned from the query - a user trace confirms this.
The table definition is as follows:
CREATE TABLE CMN_INFLIGHT_TIMING(
PROCESS_NAME VARCHAR2(40) NOT NULL,
PROCESS_ALLOWED_SECONDS INTEGER NOT NULL,
EMAIL_QUEUE VARCHAR2(4 DEFAULT 'EAI.NOTIFICATION.EMAIL' NOT NULL);
The ESQL is as follows:
DECLARE outEnv REFERENCE to OutputLocalEnvironment;
SET outEnv.CMN_INFLIGHT_TIMING[] = (SELECT T.PROCESS_ALLOWED_SECONDS, T.EMAIL_QUEUE
FROM Database.CMN_INFLIGHT_TIMING AS T
WHERE T.PROCESS_NAME = processName);
where processName is just a local variable - I have tried using a literal string too but it also fails - the DDL integer column is the only difference bwteen this and another similar query which works. The PASSTHRU seems to be the only way to get data from this table.
I suspected the driver myself and am wary of such a problem given that I might encounter similar further issues. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Apr 27, 2006 6:07 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Did you "Enable SQLDescribeParam" on the ODBC DSes?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Ian |
| Posted: Thu Apr 27, 2006 6:15 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Pauline,
As MGK said earlier, this should work asis (and not require a PASSTHRU statement).
The most likely reason is that the WHERE-clause is not satisfied and therefore no rows are returned.
I would suggest that you take a User (debug level) trace and check what value is assigned to processName.
_________________
Regards, Ian |
|
| Back to top |
|
|
| pcoffey |
| Posted: Thu Apr 27, 2006 7:01 am Post subject: |
|
|
Apprentice
Joined: 03 Feb 2006
Posts: 33
|
Yes, the SQL Describe param is set. The stanza from the .odbc.ini:
Driver=/opt/IBM/mqsi/6.0/merant/lib/UKor820.so
WorkArounds=536870912
Description=Datadirect 5.0 Oracle
ServerName=EAIIFG.WORLD
EnableDescribeParam=1
OptimizePrepare=1
Ian, I am sure this is not a data issue as I use a PASSTHRU statement directly after the failing call and it does return data i.e.
SET outLocalEnv.CMN_INFLIGHT_TIMING[] = PASSTHRU('SELECT PROCESS_ALLOWED_SECONDS, EMAIL_QUEUE
FROM CMN_INFLIGHT_TIMING
WHERE PROCESS_NAME = ?', processName);
The only other possible unusual aspect of my configuration is that the Broker is running on 32 bit AIX whereas the database is installed on 64 bit AIX. Hence the Oracle client is 32-bit. |
|
| Back to top |
|
|
| mgk |
| Posted: Fri Apr 28, 2006 12:42 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
As a test, can you remove the WHERE clause and see if you get data back ?
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| pcoffey |
| Posted: Fri Apr 28, 2006 12:44 am Post subject: |
|
|
Apprentice
Joined: 03 Feb 2006
Posts: 33
|
| I removed the where clause (and just used a SELECT *) and still no data is returned. |
|
| Back to top |
|
|
| Ian |
| Posted: Fri Apr 28, 2006 8:05 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Pauline,
| Quote: |
| The only other possible unusual aspect of my configuration is that the Broker is running on 32 bit AIX whereas the database is installed on 64 bit AIX. Hence the Oracle client is 32-bit. |
This is supported and should not be an issue. Many of our internal machines are configured in this way and a number of our customer have this configuration as well. I am also not aware of any known problems in this area which match these symptoms.
It looks like we need to look at this in more detail to understand why this is not working in your case. The best way to do this would be for you to open a PMR and provide the following information ...
Write a simple test case :
MQInput -> Compute -> MQOutput
In the Compute node include both the native ESQL SELECT and PASSTHRU SELECT and assign both the result sets to the output message.
1. WMB project (messageflows and ESQL)
2. WMB service level trace
3. Output message
4. ODBC trace
5. ODBC ini file(s)
6. env output
7. Oracle table DDL
8. ls -laR $ORACLE_HOME > oracle.txt
9. ls -laR /var/mqsi/lib/ > mqsilib.txt
10. ls -laR /usr/opt/mqsi/ > optmqsi.txt
If you do open a PMR then please send me a private message with the PMR number so that I can follow it up.
_________________
Regards, Ian |
|
| Back to top |
|
|
| pcoffey |
| Posted: Fri Apr 28, 2006 8:11 am Post subject: |
|
|
Apprentice
Joined: 03 Feb 2006
Posts: 33
|
| Thanks Ian - a PMR may be the best route. I'll organise getting the materials together. |
|
| Back to top |
|
|
| pcoffey |
| Posted: Mon May 08, 2006 8:32 am Post subject: |
|
|
Apprentice
Joined: 03 Feb 2006
Posts: 33
|
Hi,
Thanks to IBM PMR support, my problem has been identified as a result of an incorrect ODBC configuration:
In the [ODBC] stanza we were referencing an earlier library, i.e.:
[ODBC]
Trace=0
TraceFile=/MQ/BKSYSTEM01/data/odbc/odbctrace.out
TraceDll=/usr/opt/wmqi/merant/lib/odbctrac.so
InstallDir=/usr/opt/wmqi/merant
whereas it should be
[ODBC]
Trace=0
TraceFile=/var/mqsi/odbc/odbctracev6.out
TraceDll=/opt/IBM/mqsi/6.0/merant/lib/odbctrac.so
InstallDir=/opt/IBM/mqsi/6.0/merant
Thanks all,
Pauline |
|
| Back to top |
|
|
|
|
