| Author |
Message
|
| pmbsa |
 Posted: Wed Dec 12, 2001 5:39 am Post subject: |
 |
|
Novice
Joined: 06 Aug 2001
Posts: 16
|
Hi, has anybody has problems storing large
messages into Oracle (When I say large I only mean in and around 10K)
When we try to store these large messages the Broker hangs the last thing the trace says is that MQSI is about to store the message.
There is no activity within Oracle, this would lead me to believe that there is an issue with the Merant ODBC driver.
any input to this would be much appreciated
thanks
Paul |
|
| Back to top |
|
 |
| mpuetz |
| Posted: Mon Dec 17, 2001 4:46 am Post subject: |
|
|
Centurion
Joined: 05 Jul 2001
Posts: 149
Location: IBM/Central WebSphere Services
|
Hi,
we have encountered a similar problem
with 2.0.2 HPUX Version against
Oracle 8.1.6 and 8.1.7.
We have nailed it down to be (most likely)
a bug in the Merant ODBC driver which
'magically' inserts a zero byte after
the first 1000 bytes of any SQL parameters.
This PMR is still open and I will post here
if we have gotten a fix from software support.
Mathias Puetz,
IBM-EMEA AIM Services
MQ/MQSI Specialist
|
|
| Back to top |
|
|
| surenat |
| Posted: Wed Jan 02, 2002 4:57 pm Post subject: |
|
|
Apprentice
Joined: 01 Jan 2002
Posts: 32
|
This is a known limitation. Here is a summary explanation :
.
An internal MQSI feature 18003 was opened to describe the problem
where, due to limitations in the native Oracle NT ODBC drivers and the
Merant Oracle ODBC driver, which cause all columns to be described as
VARCHAR(999) when SQLDescribeParam() is called.
.
The most serious known problem this causes is to restrict the size of
a BLOB and CLOB insert to a maximum of 4K.
.
Due to the variations between the datatypes and the sequence the
messages are processed in, the error message will vary. However, the
most likely error message would be a :
Database error: SQL State 'HY104';
Native Error Code '0';
Error Text '[MERANT][ODBC Oracle 8 driver]Invalid precision value
.
To explain this further, MQSI makes a connection to a user database
the first time a messageflow is exercised that contains a datasource.
This connection is then cached and so each subsequent message that is
P
processed will use the values as set by the first message being
processed. In this scenario, we bind the parameters using the ODBC
function SQLBindParameter, and we pass a length of 999 and a datatype
of VARCHAR2 (for the BLOB or CLOB column) and the driver and Oracle
conspire to do the correct conversion from the output data format to
VARCHAR2, followed by another conversion to the real target datatype.
So, as in this case, initial messages with data of a length between
0 and 4K will be processed fine, but any subsequent messages larger
than 4K will fail with an "Invalid precision" type error.
.
This is consistant with what the customer is seeing.
.
This feature is still "open" and there is no resolution to the problem
as yet.
.
A very LIMITED workaround has been developed for this problem, but is
ONLY suitable in situations where BLOBs *OR* CLOBs are used, but not
where both where BLOBs *AND* CLOBs are used. This uses the environment
variable MQSI_ORACLE_LOB_TYPE.
.
This customer is running with MQSIv2.0.1 CSD03 where the environment
variable MQSI_ORACLE_LOB_TYPE is included but undocumented.
.
Environment variable MQSI_ORACLE_LOB_TYPE can be set to either BLOB
or CLOB. This is case sensitive and must be upper case.
.
The following matrix details the maximum length restrictions for data
being inserted into BLOB, CLOB and VARCHAR2 Oracle column datatypes
dependent on the setting of the environment variable:
.
MQSI_ORACLE_LOB_TYPE not set
.
- BLOB, max length = 1998
- CLOB, max length = 3998
- VARCHAR2, max length = 4000
.
MQSI_ORACLE_LOB_TYPE set to BLOB
.
- BLOB, max length as defined by DBMS datatype BLOB (tested to 16000)
- CLOB, max length = 998
- VARCHAR2, max length = 998
.
MQSI_ORACLE_LOB_TYPE set to CLOB
.
- BLOB, max length 498 (hexadecimal string 998)
- CLOB, max length as defined by DBMS datatype CLOB (tested to 16000)
- VARCHAR2, max length = 998
.
There will be some performance degredation if the environment variable
is used, because an SQLPrepare statement is performed for EVERY insert
statement that is executed.
.
To apply this environment variable :
.
- Stop all MQSI brokers using the mqsistop command.
- Set and export the environment variable MQSI_ORACLE_LOB_TYPE to
either BLOB or CLOB.
- Restart the MQSI brokers using the mqsistart command.
.
#####################
Thanks
Suresh Nathani
| Quote: |
On 2001-12-17 04:46, mpuetz wrote:
Hi,
we have encountered a similar problem
with 2.0.2 HPUX Version against
Oracle 8.1.6 and 8.1.7.
We have nailed it down to be (most likely)
a bug in the Merant ODBC driver which
'magically' inserts a zero byte after
the first 1000 bytes of any SQL parameters.
This PMR is still open and I will post here
if we have gotten a fix from software support.
Mathias Puetz,
IBM-EMEA AIM Services
MQ/MQSI Specialist
|
[ This Message was edited by: surenat on 2002-01-02 16:59 ] |
|
| Back to top |
|
|
| pmbsa |
| Posted: Thu Jan 03, 2002 2:33 am Post subject: |
|
|
Novice
Joined: 06 Aug 2001
Posts: 16
|
Thanks Guys, We have just been given the workaround and it appears to work (What a shock)
IBM seems all to un interested in a permenant fix for this though for my liking!!!
Paul |
|
| Back to top |
|
|
| mpuetz |
| Posted: Mon Jan 07, 2002 9:05 am Post subject: |
|
|
Centurion
Joined: 05 Jul 2001
Posts: 149
Location: IBM/Central WebSphere Services
|
Hi Suresh,
thanks for your info, although I'm not
yet sure whether this exactly applies to
the problems we are seeing.
We use 2.0.2 with fixpack 2 /Merant 3.7 and we are getting
the error with 1k string inserted into
a VARCHAR2(2000) without any environment
variables set like you described. So I would
assume inserts should go fine for strings
less than 4000 bytes in size. On our HPUX
platform it looks more like the default is
BLOB or CLOB mode since can't insert anything
(first message or no) larger than 1000 bytes.
By the way error message we get looks
like this
Dec 19 19:01:25 bypdpd MQSIv202: (PDPORTAL_MAIN.OPX2)[10]BIP2322E: Database error: SQL State '32'; Native Error Code '0'; Error Text '[MERANT][ODBC 20101 driver]1816044'
unlike the one you described.
_________________
Mathias Puetz
IBM/Central WebSphere Services
WebSphere Business Integration Specialist |
|
| Back to top |
|
|
| johndins |
| Posted: Thu Jun 10, 2004 4:54 am Post subject: |
|
|
Newbie
Joined: 02 Jun 2004
Posts: 3
|
Hi Guys
How do you go about setting the environment variable
is it programmatical in the flow
i.e. Environment.Variable.MQSI_ORACLE_LOB_TYPE = 'CLOB';
?
or is it
a regedit setting for MQSERIES ?
Thks |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Jun 10, 2004 10:44 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
You cannot set system/user environment variables using ESQL code. You need to set it outside and use some plug-in node to read the variable.
_________________
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 |
|
|
| jefflowrey |
| Posted: Thu Jun 10, 2004 11:35 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Actually, in this case, he doesn't need a plug-in node, as it's the ODBC driver that will be reading the value of the Environment variable.
But, yes, this appears to be a OS level Environment variable rather than a child member of the Environment message tree.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| johndins |
| Posted: Fri Jun 11, 2004 1:06 am Post subject: |
|
|
Newbie
Joined: 02 Jun 2004
Posts: 3
|
Guys
I tried adding a user variable and a system variable on the MQ SERIES SERVER system property environment variables.
I also tried add an entry in the odbc.ini registry setting. However none of this worked.
What does 'Set and Export the environment variable MQSI_ORACLE_LOB_TYPE to CLOB' mean ?
what actions do you have to do ? |
|
| Back to top |
|
|
|
|
