SQL sentence to obtain the SQL TOP TEN with more executions of my BD2 database:
db2 "SELECT NUM_EXECUTIONS as EXECUTIONS,
AVERAGE_EXECUTION_TIME_S as TIME_SECONDS,
STMT_TEXT as TEXT
FROM SYSIBMADM.TOP_DYNAMIC_SQL
WHERE upper(STMT_TEXT) like 'SELECT%'
ORDER BY NUM_EXECUTIONS DESC
FETCH FIRST 10 ROWS ONLY"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.
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:
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"
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.
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.
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!
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:
This work is licensed under a Creative Commons Attribution-Share Alike 4.0 License.