Truncate in DB2, DB2 CLP tricks, simple DB2 tracing

Posted by David Yahalom under DB2 LUW

Truncate table in DB2

One of the things I really missed when going DB2 from Oracle is the truncate table command.

In Oracle you have the option to quickly truncate a table using the truncate table command. This is a really good feature as the difference between doing truncate or a DML delete is huge in terms of performance.

You have to remember that when doing a truncate you are actually doing a non-logged delete of the table which is much less I/O intensive then a regular DML delete. This is most useful if you want to truncate a table during work hours without increasing server load.

However, apparently, DB2 does have a truncate feature, the command is just not as straightforward as writing truncate table in Oracle.

To truncate a table in DB2, simply write:

alter table schema.table_name activate not logged initially with empty table

From what I was able to read, this will delete the table content without doing any kind of logging which will go much easier on your server’s I/O.

Change the DB2 CLP prompt

Working with multiple servers or databases in DB2 can be dangerous if you are using the command line interface and forget which session is connected to which server.

So, in order to save yourself allot of future headaches and lost sleep hours while trying to mount restore tapes you can issue the following command which will change the DB2 CLP prompt to a meaningful (user@database):

db2set DB2_CLPPROMPT=(%da@%d):

You can get creative and also use the following environment variables in building your custom DB2 prompt:

%d = Currently connected database name
%i = Attached instance name
%ia = Authorization ID of the current instance
%n = Newline (for a two line prompt)
%da = Authorization ID of the current database

Nifty DB2 CLP commands you usually forget:

history

Lists the last 20 commands entered and prefixes each with a number. The maximum number of commands kept in memory can be customized with the DB2 registry variable DB2_CLP_HISTSIZE.

runcmd command_number_n
[/sourcode]

Re executes command number n from the list given by the history command. If n is not specified (or n = -1), the previous command is invoked.

[sourcecode language="sql"]
edit command_number_n
[/sourcode]

Edits the command number n using an editor defined by the DB2 registry variable DB2_CLP_EDITOR. If not set, this uses the vi editor on Linux/UNIX and Notepad on Windows.

[sourcecode language="sql"]
Exclamation mark (!)
[/sourcode]

This is the escape character that lets you issue operating system commands from within the CLP interactive mode

How to monitor locks:

[sourcecode language="sql"]
1) db2 update monitor switches using LOCK on
2) db2 get snapshot for locks on opal_d > /tmp/locks.txt
3) db2 reset monitor all

Do a simple DB2 trace

DB2 has several ways to do an SQL query trace. This method is using the explain plan with snapshotsql prefix:

1) Create a text file with the query you want to trace. For example, create a file named c:\query1.sql and paste your query inside.

2) Add the following text before the SELECT operator:

explain plan with snapshot for

So that your query will look something like this:

explain plan with snapshot for
SELECT count(*) from syscat.tables;

3) Run the following command:

db2 -tvf c:query1.sql

This will prepare the execution plan and save it in the explain tables within the system catalog.

4) Run the following command which will output the execution plan to a txt file:

db2exfmt -d database_name -u username password -1 -o c:explain_plan.txt