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
 

 

How to find the no of rows deleted

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: How to find the no of rows deleted
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: 6968
Location: porcelain throne

PostPosted: Fri Jan 23, 2009 6:41 pm    Post subject:
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: 1013
Location: India

PostPosted: Fri Jan 23, 2009 7:00 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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: 2284
Location: @my desk

PostPosted: Sat Jan 24, 2009 2:06 pm    Post subject:
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: 1013
Location: India

PostPosted: Mon Jan 26, 2009 12:44 am    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Tue Jan 27, 2009 10:42 am    Post subject:
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    Post subject: Reply to: How to find the no of rows deleted
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: 1013
Location: India

PostPosted: Wed Jan 28, 2009 12:19 am    Post subject:
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    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 compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts To find out size allocated to a seque... ashek15 JCL & VSAM 15 Thu Apr 27, 2017 9:42 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


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