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
 

 

Check number of deleted records in DSNTIAUL

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

New User


Joined: 14 Feb 2012
Posts: 5
Location: USA

PostPosted: Thu Feb 23, 2012 9:34 pm    Post subject: Check number of deleted records in DSNTIAUL
Reply with quote

A team member accidentally deleted all records from a table using DSNTIAUL. I wish to prevent this in future. So far I have tried to use -

SOLUTION 1 -
DELETE FROM tablename
WHERE key IN ( SELECT key FROM tablename
WHERE key = 0002000100
FETCH FIRST 3 ROWS ONLY );

but I got an error -
DSNT408I SQLCODE = -4700, ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW
FUNCTION MODE

Looks like although we have DB2 V9, it is still not in New Function Mode.

SOLUTION 2 - Creating a view -
CREATE VIEW view1 AS
SELECT key, ROW_NUMBER() OVER (PARTITION BY key) RN
FROM tablename;
SELECT FROM view1
WHERE key = 0002000100
AND RN=3;

But I get an error -
SQL0104N An unexpected token "(" was found following "". Expected tokens may include: ", FROM INTO". SQLSTATE=42601
----------------------------------------------------------------------------------

I am fancying that there could be a way to check the value of SQLERRD(3) after the execution of the Delete statement. If the count is not what you expect then issue a Rollback.

Any ideas?

Regards
Back to top
View user's profile Send private message

chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Feb 24, 2012 8:58 am    Post subject:
Reply with quote

Hi Rishi..

What is your exact requirement? How may records you want to delete?

You can use SQLERRD(3) to check how may no of records deleted in COBOL but I am not sure about DSNTIAUL.

Also your Subquery from Solution 1 will always return key 0002000100 as it is in where clause and in turn it will delete all records from table with key =0002000100.

How are you going to prevent deleting of records by creatinga VIEW?

Plase elaborate your requirement.

Regards,
Chandan
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1788
Location: UK

PostPosted: Fri Feb 24, 2012 11:03 am    Post subject:
Reply with quote

chandan.inst wrote:

What is your exact requirement? How may records you want to delete?

How about reading and UNDERSTANDING the very first line of the topic. Read EVERY word. Then go away and blush and come back and say 'Ooops'.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Feb 24, 2012 12:07 pm    Post subject:
Reply with quote

Hi Nic,

I read the thread and understood the requirement.

I asked for the exact requirement after going through the below solution provided

SOLUTION 1 -
Code:
DELETE FROM tablename
WHERE key IN ( SELECT key FROM tablename
WHERE key = 0002000100
FETCH FIRST 3 ROWS ONLY );


With this all rows with key = 0002000100 will be deleted but subquery selects only first 3 rows

So I guess requirement is to delete only 3 rows which is not happening.

So just wanted to confirm the assumption what is the basic criteria for deleting the records

Regards,
Chandan
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1788
Location: UK

PostPosted: Fri Feb 24, 2012 7:20 pm    Post subject:
Reply with quote

The requirement is to stop someone from accidentally deleteing ALL rows in the table as has happened.
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 Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm
No new posts Edit large number of datasets (QSAM) zh_lad TSO/ISPF 3 Tue Apr 04, 2017 6:08 pm
No new posts how to mask the phone number kumarinfy DB2 4 Mon Apr 03, 2017 5:23 pm
No new posts Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am


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