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

How to find the no of rows deleted


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

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Fri Jan 23, 2009 6:28 pm
Reply with quote

In our program we are emptying/deleting the table and again inserting fresh data using a file.

EXEC SQL
DELETE FROM SFR_cLS
END-EXEC.

My query is how to found the number of rows deleted. Here SQLERRD(3) won't work, since the query executes once and all the rows will be deleted, so SQLERRD(3) contains a row count of 1.

Thanks in advance
Ravi
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jan 23, 2009 6:41 pm
Reply with quote

Quote:
My query is how to found the number of rows deleted. Here SQLERRD(3) won't work, since the query executes once and all the rows will be deleted, so SQLERRD(3) contains a row count of 1



I have taken an oath of niceness,
so let me just say:
you have been mis-informed.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Fri Jan 23, 2009 7:00 pm
Reply with quote

Ravi,

Just count before deleting it.
Code:
    Select count(*) from SFR_cLS


Sushanth
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Fri Jan 23, 2009 7:45 pm
Reply with quote

Quote:
you have been mis-informed.


SQLERRD(3) still works.
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Fri Jan 23, 2009 8:07 pm
Reply with quote

Dick,

I tested the program, SQLERRD(3) contains 1, immediatly after executing the query, I am checking the SQLCODE for 0 and the displaying SQLERRD(3). Here is the code, I moved SQLERRD(3) to a variable and then displayed also used SQLERRD(3) directly to display, the result is 1 only

EXEC SQL
DELETE FROM SFR_CLASS
END-EXEC

EVALUATE SQLCODE
WHEN 0
MOVE SQLERRD(3) TO WS-NUM-ROWS-DEL
DISPLAY '*********************'
DISPLAY 'NO. OF ROWS DELETED :' WS-NUM-ROWS-DEL
DISPLAY 'NO. OF ROWS DELETED :' SQLERRD(3)
DISPLAY '*********************'

WHEN +100
CONTINUE
WHEN OTHER
DISPLAY 'DB2 ERROR DELETING SFR CLASS'
PERFORM 9999-SQL-ERROR
END-EVALUATE

Sushanth,
I am aware of it, but I don't want to waste system resources again using an sql query for total count.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Jan 23, 2009 8:16 pm
Reply with quote

The count in sqlerrd(3) does not include deletes that may occur in other tables due to foreign key constrains.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Sat Jan 24, 2009 2:06 pm
Reply with quote

venkata.ravi,

If you're getting a value of SQLERRD(3) = 1 after your DELETE, does n't it seem to you that only 1 row got deleted from that table? Or what proof do you have to establish that more rows really got deleted from the table?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Mon Jan 26, 2009 12:44 am
Reply with quote

Ravi,

You are performing a mass delete on the table. Acutally SQLERRD(3) has to be -1, after performing the delete. SQLERRD(3) returns number of rows if there is where conditiion in the DELETE STATEMENT.

info - DELETE statement is not an efficient method to delete all the records from the table, logging happens. There is a statement called TRUNCATE.

Sushanth
Back to top
View user's profile Send private message
Amsar

New User


Joined: 26 Sep 2008
Posts: 84
Location: Chennai

PostPosted: Mon Jan 26, 2009 3:40 pm
Reply with quote

sushanth bobby wrote:
Ravi,

You are performing a mass delete on the table. Acutally SQLERRD(3) has to be -1, after performing the delete. SQLERRD(3) returns number of rows if there is where conditiion in the DELETE STATEMENT.
Sushanth


Sushant,

Sqlerrd(3) will give you the number of rows deleted. Even if there is no WHERE clause.
Code:
***INPUT STATEMENT:                                                             
    DELETE FROM TEST_TABD                                                       
 RESULT OF SQL STATEMENT:                                                       
 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION                                 
 DSNT416I SQLERRD    = 0  0  8  -1  0  0 SQL DIAGNOSTIC INFORMATION             
 DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000008'  X'FFFFFFFF'  X'000
          INFORMATION                                                           
SUCCESSFUL DELETE    OF        8 ROW(S)       


Here is the query i have ran without where clause and all the 8 rows from the table got deleted and sqlerrd(3) has value of 8.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Jan 27, 2009 10:42 am
Reply with quote

Yes. You are right AMSAR.
Since, iam working on a segmented tablespace, mine will return -1 for mass delete's & number of rows if there is a where condition.

Thank You For that INFORMATION.
Sushanth
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Tue Jan 27, 2009 8:01 pm
Reply with quote

Thank you all for the replies,
Yes I am also getting -1, I haven't checked the no of rows it is deleting.
My issue is how to capture the no of rows it is deleting, bcoz we have to display it in spool for our batch programs. Let me try and comeback for any queries.

Thanks
Ravi
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Jan 28, 2009 12:19 am
Reply with quote

Ravi,
Good to know its -1 not 1.
Since the delete is happening in the segmented tablespace, it doesn't go through the records. It just deletes the data in that tablespace.

Sushanth
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 To get the count of rows for every 1 ... DB2 3
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Find the size of a PS file before rea... COBOL Programming 13
No new posts Convert single row multi cols to sing... DFSORT/ICETOOL 6
Search our Forums:

Back to Top