Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

To keep record of delete performed by a query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rahul.arora

New User


Joined: 09 Jul 2011
Posts: 9
Location: India

PostPosted: Mon Nov 14, 2011 8:29 pm    Post subject: To keep record of delete performed by a query
Reply with quote

I want to delete data from a table. Table has columns ID and access group.One Id may be associated to one group or many groups.
I want to use query for deletion is
Select * from tablename
where ID='12345'

This query deletes the all groups associated with that ID.

The thing is I want to insert all the deleted ID-groups mappings in the audit table.
Can anyone suggest the solution with using cursors?
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Nov 14, 2011 9:33 pm    Post subject:
Reply with quote

why have you not thought about

insert into audit_table
select id
, access_group
from tablename
where ID='12345'

before you do the delete?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Mon Nov 14, 2011 9:34 pm    Post subject:
Reply with quote

Whilst I'm not a DBA or a DB2 maven, is this not a situation where a trigger could be used to good purpose?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Nov 14, 2011 9:51 pm    Post subject:
Reply with quote

Quote:

is this not a situation where a trigger could be used to good purpose?


Trigger may be a overhead to the processing when the data to be deleted is large and so I feel Datapropagator is better than trigger when you have huge data to process

Refer the manual here
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Nov 14, 2011 9:57 pm    Post subject:
Reply with quote

Hello,

For my $.02, a very simple and effective way to do this is the way DBZ has suggested. . .
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Nov 14, 2011 10:00 pm    Post subject:
Reply with quote

Very much agreed Dick icon_biggrin.gif
Back to top
View user's profile Send private message
rahul.arora

New User


Joined: 09 Jul 2011
Posts: 9
Location: India

PostPosted: Tue Nov 15, 2011 4:01 pm    Post subject:
Reply with quote

Hello dbzTHEdinosauer,
I believe this will only insert the first id -access group mapping.

suppose we have 4 access groups mapped to single Id in table
ID Access Group
12345 group1
12345 group2
12345 group3
12345 group4

Now we use this query before delete

insert into audit_table
select id
, access_group
from tablename
where ID='12345'

this will insert only 1st Id-access group mapping in the the audit table but delete command will delete the all four mappings....
Please correct me if I am wrong..
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Nov 15, 2011 5:45 pm    Post subject:
Reply with quote

pllllllllleeeeeeeeeeeeeeeeesssssssssssssee.

why don't you experiment with spufi and then come back.

why do you think a select that is part of an insert would function differently than a standalone select?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Nov 15, 2011 7:17 pm    Post subject:
Reply with quote

you forgot the aaaaaaaaaaaaaaaa
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Nov 15, 2011 7:30 pm    Post subject:
Reply with quote

declare a cursor on the table to have deletes,
with an open FOR UPDATE clause

as you fetch each row,
insert what you want in the audit using the fetch-populated host variables,
then delete where current cursor

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaah.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Tue Nov 15, 2011 7:32 pm    Post subject:
Reply with quote

Quote:
query for deletion is
Select * from tablename
where ID='12345'


That seems to be a selection not a deletion. And is this not the same topic as posted on another board?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us