How to display the number of rows affected in DB2 INSERT / DELETE / UPDATE / MERGE commands

 By default, DB2 does not print the number of records affected when we run the DML  commands like INSERT,  DELETE, UPDATE or MERGE in the command prompt. So to display number of records affected when you run the above DML commands, you can use  following options.

You  can use the option m, in the command itself as given below

db2 -m update employee set payscale='42000-3%-72000' where doj<='1976-01-01'
  Number of rows affected : 50
DB20000I  The SQL command completed successfully.


Also, You can use the below db2set command to set the command options "m" permanently for all instances and databases,

db2set DB2OPTIONS=-m

db2stop

db2start

Or you can update the options m to ON  permanently using the update command options.

db2 update command options using m ON

            In windows, if you may get the  below error OR your changes done to the command option may not be  retained when you run the above command,

error :   db2 update command options using m ON SQL0104N  An unexpected token "m" was found following "USING".  Expected tokens may include:  "A".  SQLSTATE=42601. Solution will be

Just go to db2 prompt.

db2=> update command options using m ON

You can cehck the command options using the below command

list command options


db2 => delete from Employee where emp_code='E1022'
  Number of rows affected : 1
DB20000I  The SQL command completed successfully.