DB2 tip: How to know the no. of rows that got affected

As a DBA, often I am asked to run delete / update / insert statements. I would be curious to know how many rows got affected after I run these DML statements. (DML = Data Manipulation Language). In this blog psot, I am going to share how to know no. of rows that got affected when a delete/update statement was run.

DB2 LUW 9.7 comes with few default command line options and these options do not reveal the no. of rows that got affected/modified. With the default command line options, you only would know if the command went through okay or not..

Example:

db2 “delete from metrics.dept”
DB20000I The SQL command completed successfully.

If you are curious like me, you have couple of options to identify the no. of rows that got affected:

1) Option 1 — Temporary setting — Follow this option if you would want to see the ‘affect’ only for a single statement. This would not influence other users in any way. So feel free to use it at will.

Run the delete with ‘-m’ option

$db2 -m “delete from metrics.dept”
Number of rows affected : 6
DB20000I The SQL command completed successfully.

2) Option 2 — Temporary setting — Follow this option if you would like to see the ‘affect’ in a given terminal session. This would not influence other users in any way. So feel free to use this one at will as well.

$export DB2OPTIONS=-m (Remember there is  no ‘db2’ before ‘export’. This is an OS level command that you are running)

$db2 “delete from metrics.dept”
Number of rows affected : 6
DB20000I The SQL command completed successfully.

3) Option 3 — Follow this option if you would want to see the ‘affect’ every time you login with your user id. This is the setting that I use .

In your .profile, add the following line after you source the db2profile.

Example: (Your db2profile would be different based on the instance owner’s home directory but this is what I have)

if [ -f /db2home/db2inst1/sqllib/db2profile ]; then
. /db2home/db2inst1/sqllib/db2profile
fi

export DB2OPTIONS=’-m’ ## This line is added to identify the no. of rows that got affected in a delete / update / insert statement.

Now, sign back in or alternately execute the .profile (just run . $HOME/.profile — there is a space between ‘.’ and $HOME) and you should be able to see this option persisting between your sign-ons.

Comments / Questions are welcome.

P.S.: It is always a good idea to know how many rows would get affected with each delete / update / insert statement even BEFORE running those statements for a good reason. I will cover this in my future blog post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s