Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1869
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: 1869
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 Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 21 Sun Aug 27, 2017 10:35 pm
No new posts ALPHABETIC check in COBOL vidyaa COBOL Programming 8 Thu Aug 17, 2017 7:13 pm
No new posts Profile changed to NUMBER ON STD (fro... Martylin TSO/ISPF 1 Thu Aug 03, 2017 9:14 am
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

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us