I’ve always been a little frustrated with db2 when running scripts and command lines with fact that when a command would run like insert, update or delete, you never knew how many rows you affected. Some GUI products will tell you, but with command line it would just come back as being successful (or not) and it was up to you to validate what you really did with a subsequent query.
Being a DBA, I always feel the need to validate what I do especially with ad hoc statements to ensure that I have done what I expected.
Enter the m option on the db2 command line. This is a relatively new switch that will cause db2 to return to you how many rows were affected. The m option was introduced with verison 9.
To do this from the command line:
db2 -m “delete from test” will return
Number of rows affected : 18
DB20000I The SQL command completed sucessfully.
To do this from a script:
db2 -tvmf test.db2
By doing this, you will have the number of rows affected at your finger tips, not more hoping or assuming or doing count before/count after!
Happy DBAing
Or we do it in such a way:
SELECT COUNT( * ) FROM OLD TABLE( DELETE FROM test);
Regards Stefan