IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SELECT from data change table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
jzhardy

Active User


Joined: 31 Oct 2006
Posts: 129
Location: brisbane

PostPosted: Thu Apr 20, 2023 6:51 am
Reply with quote

this is posted more out of academic interest, rather than trying to address a specific need.

Is there any formulation for combining "OLD TABLE" and "FINAL TABLE" in the one SQL. I suspect not, but there are situations where it might be beneficial, eg where the cost of evaluating the WHERE clause is expensive.

my approach would be to write two SQLs such as :

SELECT "before update =>", col1,col2 .. from OLD TABLE
(
update <target table>
... where (<some expensive clause > )
);

SELECT "after update =>", col1,col2 .. FROM <target table> WHERE <expensive clause>
;


assume of course that the target_table is eligible for "FINAL TABLE" evaluation - ie, has no associated triggers.
Back to top
View user's profile Send private message
jzhardy

Active User


Joined: 31 Oct 2006
Posts: 129
Location: brisbane

PostPosted: Thu Apr 20, 2023 7:25 am
Reply with quote

I think I worked it out ... I can simply use the INCLUDE modifier within the UPDATE.

eg:

SELECT old_val, Col1
FROM FINAL TABLE (
UPDATE <target table>
INCLUDE (OLD_VAL CHAR(4))
SET <target col> = 'XXXX'
,OLD_VAL = <target col>
WHERE <expensive clause>
);
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3038
Location: NYC,USA

PostPosted: Fri Apr 21, 2023 5:00 pm
Reply with quote

I couldn’t follow the problem or issue they made you to write this sql. If there is a performance issue then many ways it can be improved without changing the query but we don’t know yet the actual problem .
Back to top
View user's profile Send private message
jzhardy

Active User


Joined: 31 Oct 2006
Posts: 129
Location: brisbane

PostPosted: Mon Apr 24, 2023 7:40 am
Reply with quote

just to repeat: this was only asked out of academic interest.

I can't actually think of a real-world scenario where this would be significantly beneficial. If the cost of of the WHERE predicate was high, then probably better to write SQL to generate an update-SQL (to apply update on full primary key), and then simply run this (generated) SQL.

Some shops do have a standard for UPDATE SQL that requires pre- and post- value to be reported in full. For this it does offer a slightly more compact and efficient path. For readability though I would go for two statements , eg:

select 'before update ==>',OT.* from XXX OT where YYY;
select 'after update ==>', FT.* from FINAL TABLE(update XXX where YYY) FT;
Back to top
View user's profile Send private message
jzhardy

Active User


Joined: 31 Oct 2006
Posts: 129
Location: brisbane

PostPosted: Mon Apr 24, 2023 7:56 am
Reply with quote

possibly another use case would be if the transaction was bound at CS, but we want to keep the update statement and the post-select 'in step'. A bit contrived, because we could bind at RR, or just lock down the whole table and run the update/report transaction.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3038
Location: NYC,USA

PostPosted: Tue Apr 25, 2023 10:09 am
Reply with quote

I wouldn’t go fancy much as simplest is best for maintenance purpose for anyone else using taking over the support.

However thanks for sharing the idea.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
No new posts JCL EXEC PARM data in C Java & MQSeries 2
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top