ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportIIB9: ESQL UPDATE

Post new topicReply to topic
IIB9: ESQL UPDATE View previous topic :: View next topic
Author Message
akil
PostPosted: Thu Dec 10, 2015 5:39 am Post subject: IIB9: ESQL UPDATE Reply with quote

Partisan

Joined: 27 May 2014
Posts: 338
Location: Mumbai

Hi

For the following query
Code:

      UPDATE Database.FUNDS_TRANSFERS
         SET STATUS_CODE = COALESCE(statusCode, FUNDS_TRANSFERS.STATUS_CODE),
             BANK_REF = COALESCE(bankRef, FUNDS_TRANSFERS.BANK_REF),
             FAULT_CODE = COALESCE(faultCode, FUNDS_TRANSFERS.FAULT_CODE),
             FAULT_REASON = faultReason,
             RECONCILED_AT = CURRENT_TIME
       WHERE FUNDS_TRANSFERS.ID = LocalEnvironment.Database.Input.Event.Usr.FUNDS_TRANSFER_ID;
      RETURN TRUE;


The ESQL node seems to be firing the following SQL (with Oracle at the backend)

Code:

SELECT STATUS_CODE, BANK_REF, FAULT_CODE, ID FROM FUNDS_TRANSFERS FOR UPDATE OF STATUS_CODE, BANK_REF, FAULT_CODE, FAULT_REASON, RECONCILED_AT


Does the DELETE statement of ESQL fire a SELECT for UDPATE? It does not seem to be documented anywhere.

Further, the WHERE condition is missing. This creates a nightmare !.

Is this a defect?
_________________
Regards
Back to top
View user's profile Send private message Visit poster's website
inMo
PostPosted: Thu Dec 10, 2015 6:19 am Post subject: Reply with quote

Master

Joined: 27 Jun 2009
Posts: 214
Location: NY

How did you arrive at your conclusion?
Back to top
View user's profile Send private message
akil
PostPosted: Thu Dec 10, 2015 7:02 am Post subject: Reply with quote

Partisan

Joined: 27 May 2014
Posts: 338
Location: Mumbai

Hi

I could figure out the possible reason for a SELECT FOR UPDATE, & that is because of the nature of the query,

When the query is as follows, there is no SELECT FOR UPDATE fired

Code:

UPDATE Database.FUNDS_TRANSFERS
         SET STATUS_CODE = statusCode,
             BANK_REF = bankRef,
             FAULT_CODE = faultCode,
             FAULT_REASON = faultReason,
             RECONCILED_AT = CURRENT_TIME
       WHERE FUNDS_TRANSFERS.ID = LocalEnvironment.Database.Input.Event.Usr.FUNDS_TRANSFER_ID;
      RETURN TRUE;


So i guess the SELECT FOR UPDATE is to satisfy the SET statement.

However, the defect seems to be that the SELECT FOR UPDATE has not WHERE clause!.
_________________
Regards
Back to top
View user's profile Send private message Visit poster's website
Vitor
PostPosted: Thu Dec 10, 2015 7:10 am Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25823
Location: Ohio, USA

I think the question was more:

"how have you determined the SQL being run on the database side?"

Oracle diagnostics? 3rd party tools? Inference from the result set being returned? Psychic powers?
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
akil
PostPosted: Thu Dec 10, 2015 9:47 am Post subject: Reply with quote

Partisan

Joined: 27 May 2014
Posts: 338
Location: Mumbai

The queries come in the brokers user trace .

They are also visible in oracle's awr reports.
_________________
Regards
Back to top
View user's profile Send private message Visit poster's website
Vitor
PostPosted: Thu Dec 10, 2015 10:00 am Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25823
Location: Ohio, USA

akil wrote:
The queries come in the brokers user trace .

They are also visible in oracle's awr reports.


Seems definitive.

So apparently a defect, so your next move is a PMR.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
inMo
PostPosted: Thu Dec 10, 2015 10:08 am Post subject: Reply with quote

Master

Joined: 27 Jun 2009
Posts: 214
Location: NY

Have to ask - You are certain that the statement you highlighted is in fact the statement that is being executed? No chance it is the statement you think is being executed?
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Thu Dec 10, 2015 11:37 am Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20109
Location: LI,NY

akil wrote:
Hi

I could figure out the possible reason for a SELECT FOR UPDATE, & that is because of the nature of the query,

When the query is as follows, there is no SELECT FOR UPDATE fired

Code:

UPDATE Database.FUNDS_TRANSFERS
         SET STATUS_CODE = statusCode,
             BANK_REF = bankRef,
             FAULT_CODE = faultCode,
             FAULT_REASON = faultReason,
             RECONCILED_AT = CURRENT_TIME
       WHERE FUNDS_TRANSFERS.ID = LocalEnvironment.Database.Input.Event.Usr.FUNDS_TRANSFER_ID;
      RETURN TRUE;


So i guess the SELECT FOR UPDATE is to satisfy the SET statement.

However, the defect seems to be that the SELECT FOR UPDATE has not WHERE clause!.

How did you determine that there was no WHERE CLAUSE? Could it have been because LocalEnvironment.Database.Input.Event.Usr.FUNDS_TRANSFER_ID was not populated?

What steps are you taking to avoid an empty / null where clause?
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
akil
PostPosted: Thu Dec 10, 2015 9:20 pm Post subject: Reply with quote

Partisan

Joined: 27 May 2014
Posts: 338
Location: Mumbai

For one ESQL statement, the trace shows 2 statements

SELECT FOR UPDATE --- without a where clause
UPDATE --- with a where clause
_________________
Regards
Back to top
View user's profile Send private message Visit poster's website
gisly
PostPosted: Fri Sep 13, 2019 2:00 am Post subject: Reply with quote

Apprentice

Joined: 10 May 2012
Posts: 29

Hi! Sorry for reviving this post but I just wanted to say that we've come across a similar behaviour in IIB v. 10.0.0.13

Code:
UPDATE DOCUMENT AS SET FIELD1=someFunction() WHERE D.FIELD2 = ?

fires two statements:
Code:
SELECT FIELD2 FROM DOCUMENT FOR UPDATE OF FIELD1

and the update itself

whereas
Code:

DECLARE myValue CHARACTER someFunction();
UPDATE DOCUMENT AS SET FIELD1=myValue WHERE D.FIELD2 = ?

fires only one statement (the update itself).

So, the workaround is to avoid using functions in the right-hand side of the statement but I think this is definitely a bug.
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportIIB9: ESQL UPDATE
Jump to:



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
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.