DB2TOP register and replay

Actually DB2TOP is a real-time tool, however many DBA’s don’t know that it can be run in REPLAY mode with captured session information. So, next time you have a big crisis with your database, you can capture all the data from db2top and do the analysis afterwards.

How to capture the data? Option -C to capture. Press N to create a file. The option “-m” to gather data during n minutes.

Options db2 command line – Disable autocommit

DB2 command line utility has different options. In this post we show how to disable the autocommit of DML statements that by default is active. In case you want to do some test, for example, simulating the use of locks could be helpful to disable this feature.

Options from DB2 command line can be obtained with the following sentences:

DB2 Top 10 SQL for execution time

SQL Command to obtain the Top Ten of SELECT SQL sentences with more execution time in your DB2 database:

db2 "SELECT AVERAGE_EXECUTION_TIME_S as TIME_SECONDS, 

        NUM_EXECUTIONS as EXECUTIONS,

        STMT_TEXT as TEXT

        FROM SYSIBMADM.TOP_DYNAMIC_SQL

        WHERE upper(STMT_TEXT) like 'SELECT%'

        ORDER BY AVERAGE_EXECUTION_TIME_S DESC

        FETCH FIRST 10 ROWS ONLY"

 

DB2 with BLU Acceleration for SAP

Every time it’s more common SAP environments running with DB2.

Attached a video regarding the capabilities of DB2 with BLU Acceleration applied to SAP and all SAP notes to consider.

Surprising comparative with SAP HANA, and as with less complexity and investment, you get more performance.

Pay attention to the comparison with SAP HANA:

Attached also the IBM Redbook Architecting and Deploying DB2 with BLU Acceleration.

 

DB2 Write Suspend

When doing a snapshot from a storage array, if the server contains a DB2 instance running, there is no certainty that the snapshot contains a consistent copy of the database.

To launch a snapshot and ensure consistent copy in DB2 is possible to put the database at “write suspend”, that is, it overrides the disk access in write mode, and work in the buffer pool memory. Queries whether it will record but writes are performed only in memory.

How to know the DB2 connection port

Maybe there are other methods, in this short article, a simple way to know the port that serves DB2 server.

We get the name of the service TCP / IP:

> db2 get dbm cfg | grep SVCENAME

Capture the result:

TCP/IP Service name (SVCENAME) = db2TRP

Look at /etc/services:

> cat /etc/services | grep sapdb2QRP

db2TRP 5912/tcp # DB2 Communication Port

 

The listening port is 5912!

Average time of disk dccess read/write in DB2

Through DB2 we can get the average time in ms disk access is having DB2. These times are crucial for the detection of a IO problem with DB2 instance.

Usually we take into consideration that a value close to 2-3ms is good, more than 10ms can indicate problems.

Avg ms/write:

select trunc(decimal(sum(pool_write_time))/decimal(

(sum(pool_data_writes)+sum(pool_index_writes))),3)

from sysibmadm.snaptbsp

 

Avg ms/read: