Check db2 client latency with db2 database

 Check db2 client latency with db2 database

If you wanted to check latency of db2 client hosted on Linux vs db2 database hosted on Mainframe , you will usually perform ping <db2 hosting host> from client server and that will give response time. 

However if you are not satisfied with response time and think it takes more time then it is probably because of multiple hops between db2 client server and db2 hosting database servers. Usually these servers gateway servers and configured in db2 catalog in nodes. 

How to test actual query response time.

Perform following steps from server where you have indirect/gateway server configured in db2 client catalog nodes.

1. Open the DB2 Command Window: Click Start, navigate to IBM Db2, and select "DB2 Command Window Administrator"
2. Create SQL File: Create a file named test.sql in a local directory (e.g., c:\temp\test.sql) containing your SQL statement (e.g., SELECT * FROM schema.table;).
3. Run db2batch:

db2batch -d DBNAME -f "c:\temp\test.sql" -a dbid/password -i complete

This will give following response with query result.

* Prepare Time is:       0.032398 seconds

* Execute Time is:       0.028289 seconds

* Fetch Time is:         0.000837 seconds

* Elapsed Time is:       0.061525 seconds (complete)

Now perform above steps from server where you have configured db2 hostname in catalog nodes. Whatever complete time difference you observe will be latency.

Reconfigure nodes appropriately for performance improvement.  Comment on blog if you looking for additional information.