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: 6968
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: 1772
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: 1539
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: 1539
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: 6968
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: 1281
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: 6968
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: 1848
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 Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Read two inputs and write into output... murali.andaluri DFSORT/ICETOOL 6 Wed Jul 26, 2017 7:35 pm
No new posts Need to write record of PS File in ex... Chandan1993 JCL & VSAM 1 Wed Jun 07, 2017 1:35 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am


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