View previous topic :: View next topic
|
Author |
Message |
venkata.ravi
New User
Joined: 09 May 2005 Posts: 30 Location: Hyderabad
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Ravi,
Just count before deleting it.
Code: |
Select count(*) from SFR_cLS |
Sushanth |
|
Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
Quote: |
you have been mis-informed.
|
SQLERRD(3) still works. |
|
Back to top |
|
|
venkata.ravi
New User
Joined: 09 May 2005 Posts: 30 Location: Hyderabad
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
The count in sqlerrd(3) does not include deletes that may occur in other tables due to foreign key constrains. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
Amsar
New User
Joined: 26 Sep 2008 Posts: 84 Location: Chennai
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
venkata.ravi
New User
Joined: 09 May 2005 Posts: 30 Location: Hyderabad
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
|