| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| UGT: function PASSTHRU not fully working |
« View previous topic :: View next topic » |
| Author |
Message
|
| Manuel_ch |
 Posted: Thu Jul 28, 2005 7:54 am Post subject: |
 |
|
Apprentice
Joined: 27 Jul 2005
Posts: 31
|
One of my previus test consisted to change the format 'YYYY-MM-DD ...' to 'DD/MM/YYYY ...' in the node, and in the message.
The same error has appeared.
More over I tried to insert myself the TO_DATE('2005-07-27 ...','YYYY-MM-DD ...') instead of TO_DATE(?,?) in my node and the others TO_DATE( ?,?) by there values and the line was inserted successfully.
I don't think it's a probleme of data or date format. |
|
| Back to top |
|
 |
| Ian |
| Posted: Thu Jul 28, 2005 8:05 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
| Quote: |
| Driver=/usr/opt/wmqi/merant/lib/UKor818.so |
UKor818 indicates you are using the 4.1 driver so I would expect the broker to be at CSD05 or above.
There is something here that requires further investigation in terms of the data, column definition and what the application (Broker) is binding these as.
At this point I would suggest doing the comparative analysis between your working and failing environments. If this does not highlight the problem then raise the PMR with IBM Support and we can look into it further.
You can post the PMR number here for reference purposes.
_________________
Regards, Ian |
|
| Back to top |
|
|
| Ian |
| Posted: Thu Jul 28, 2005 8:26 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
OK, I have found something which may cover this ...
We have a defect (APAR IY68868 / DataDirect Case # 15014793) which covers where TO_DATE is not the last parameter passed to the PASSTHRU statement. In this case the error message is different but this may be because the datatypes are different (ie, not dates).
| Code: |
BIP2321E: Database error: ODBC return code '-1'.
BIP2322E: Database error: SQL State '22001'; Native Error
Code '0'; Error Text '[DataDirect][ODBC Oracle driver]
String data, right truncated. Error in parameter 2.'.
.
Where parameter 2 indicates that TO_DATE function is the
second parameter marker in the PASSTHRU statement.
|
The problem is where the TO_DATE function is used as one of the parameters in PASSTHRU, and when it is placed as the beginning or middle parameter.
When TO_DATE function is used as the last parameter in the PASSTHRU statement the above problem does not occur.
The fix for this is in the DataDirect driver and not the Broker code.
You can create a simple test in your environment and insert into 2-3 columns defined as DATE and use the TO_DATE function.
To get the fix (patched DataDirect driverS) you will have to open a PMR.
Best to reference this discussion ( http://www.mqseries.net/phpBB2/viewtopic.php?t=23412 ) when you open the PMR to speed it up.
_________________
Regards, Ian |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jul 28, 2005 8:31 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Will level 2 look at that?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Ian |
| Posted: Thu Jul 28, 2005 8:45 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
I hope they would 
_________________
Regards, Ian |
|
| Back to top |
|
|
| Manuel_ch |
| Posted: Fri Jul 29, 2005 12:36 am Post subject: |
|
|
Apprentice
Joined: 27 Jul 2005
Posts: 31
|
I've just a very intresting test:
I just keep one TO_DATE and placed it at the and of the insert function.
The line have been succesfully inserted.
| Quote: |
2005-07-29 10:02:33.288490 2314 UserTrace BIP2544I: Node 'FL_Archivage.C_InsDB_EAI_ARCHIVAGE': Executing database SQL statement 'INSERT INTO EAI_ARCHIVAGE (
IDARCHIVAGE,
IDLOT,
ORDERID,
LOGINUSER,
FLOWNAME,
SENDINGAPPLICATION,
MESSAGETYPE,
QMANAGER,
QNAME,
SUBMITNUMBER,
ERRORCODE,
ERRORTEXT,
ERRORNODE,
BUFFERXML,
HISTORY,
EVENTDATE)
VALUES (?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, TO_DATE( ?, ?))' derived from (72, 1); expressions 'nIdArchi, nIdLotArchi, InputBody.Siprog.MsgEntry.Header.OrderId, sLoginUser, InputBody.Siprog.MsgEntry.Header.FlowName, InputBody.Siprog.MsgEntry.Header.SendingApp, InputBody.Siprog.MsgEntry.Header.MessageType, InputBody.Siprog.MsgEntry.Header.QueueManagerName, InputBody.Siprog.MsgEntry.Header.QueueName, InputBody.Siprog.MsgEntry.Header.SubmitNumber, InputBody.Siprog.MsgEntry.Header.Error.Code, InputBody.Siprog.MsgEntry.Header.Error.Text, InputBody.Siprog.MsgEntry.Header.Error.Node, sContent, InputBody.Siprog.MsgEntry.Header.History, sDate1, sFormat1'; resulting parameter values '16386, NULL, '1234', 'EAIMCHE', 'F_EC9', 'Nabucco', 'CREATION_ECRITURE', 'QM_EAI_1_DEV', 'Q_NABU.F_EC', NULL, NULL, NULL, NULL, '<Fournisseur>…</Fournisseur>', 'M. Jackson', '22/07/2005 07:36:38', 'DD/MM/YYYY HH24:MI:SS''.
2005-07-29 10:02:33.299911 2314 UserTrace BIP4124I: Message propagated to 'out' terminal of compute node 'FL_Archivage.C_InsDB_EAI_ARCHIVAGE'.
|
In conclusion, the DB driver has a bug, as you suggested.
Something strange :
How it possible that this same driver could worked before (the AIX where the broker is haven't been reinstall)?
Why others environnements (which are still on CSD03) always worked?
Does the migration from NT server to Win 2000 server come from the problem?
I am opening a call to my support and we'll see. |
|
| Back to top |
|
|
| Manuel_ch |
| Posted: Fri Jul 29, 2005 1:21 am Post subject: |
|
|
Apprentice
Joined: 27 Jul 2005
Posts: 31
|
I place all the TO_DATE at the end of the insert function.
It's working perfectly I'll work like that for the moment.
Well I'll see what the support is going to tell me about it.
Thank you all |
|
| Back to top |
|
|
| Ian |
| Posted: Fri Jul 29, 2005 2:03 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Your tests confirms this is APAR IY68868 / DataDirect Case # 15014793.
As per my earlier posting ...
| Code: |
WMQIv2.1.0 CSD02 -> shipped with the Merant 3.7 driver -> MERANT 3.70 Oracle8 Driver
WMQIv2.1.0 CSD03 -> shipped with the Merant 3.7 driver + fixes -> MERANT 3.70 Oracle8 Driver
WMQIv2.1.0 CSD05 -> shipped with the Merant 4.1 driver -> MQSeries DataDirect Technologies 4.10 32-BIT Oracle Driver |
This explains why it may work in some of your environments running v2.1 CSD03 and fail in other environments running v2.1 CSD05+
DataDirect Case # 15014793 is a defect/regression in the 4.1 driver manager / driver.
_________________
Regards, Ian |
|
| Back to top |
|
|
| vk |
| Posted: Mon Mar 27, 2006 12:19 pm Post subject: |
|
|
Partisan
Joined: 20 Sep 2005
Posts: 302
Location: Houston
|
Was this issue resolved in WMQI v2.1?
We are currently at CSD 9. IBM said that DataDirect had given a fix for this issue with TO_DATE, but that was not included in CSD 9 as it had another issue with respect to LOB datatype.
They are suggesting to move all columns involving TO_DATE to the end or install the ODBC libraries which came as part of CSD 5.
Please let me know if anyone has more updates on this.
Thanks,
VK. |
|
| Back to top |
|
|
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
