The most common state of DB2 applications

The most common status of DB2 applications is UOW executing, UOW waiting, Connect completed, and so on, here is a simple introduction.

UOW Full name is the unit of work, which can be considered a transaction.

Connect completed:           The Application Connection library was successful. UOW Executing:               app is executing an SQL statement UOW waiting: The                 application executes a SQL, waiting to execute the next SQL in the same transaction. or run out of a transaction, waiting to execute the next transaction. Commit active:               in the commit operation Lock wait:                   lock rollback Active for other applications:             in doing rollback operation pending Remote Quest:        DPF Environment only, in the response to other nodes federated request pending:   federal environment only, in the federal data source return results

In the following example, the app connects to the database, completes two transactions, and disconnects from the database, indicating the state of the DB2 app for each time, respectively

$ DB2 Connect to sample<--Database Connect Pending<--Connect completed$ DB2+c"update t1 Set id = max WHERE id ="<--Uow Executing<--Uow waiting$ DB2+c"INSERT INTO T1 values (+)"<--Uow Executing<--Uow waiting$ DB2+c"SELECT * from t1 where id ="<--Uow Executing<--Uow waiting$ DB2 commit<--Commit Active<--Uow waiting$ DB2+c"INSERT into T2 values ($)"<--Uow Executing<--Uow waiting$ DB2 commit<--Commit Active<--Uow waiting$ DB2 terminate<--database Disconnect Pending

Supplementary explanation 1:

If the application state is UOW waiting, how can you tell if it is "waiting for the next SQL in the same transaction", or "executing a transaction, waiting to execute a transaction"?

  after DB2 +c "SELECT * from t1 WHERE id = 300" command is complete, the snapshot of the app is crawled, you can see that UOW stop timestamp is empty, Indicates that the transaction is currently not over and is waiting to execute the next SQL. 
 
 Application snapshotapplication handle  = 1   application status  = UOW Waitinguow start timestamp  = 2017 -03 -07  12 : 57 : 32.931235  uow stop timestamp =  UOW completion status  = 

After the first commit command, and then look at snapshot, you will see that UOW stop timestamp is not empty, stating that the previous UOW completed and is waiting to execute the next transaction

UOW start timestamp                        -Geneva:32.931235UOW Stop timestamp                         = 2017-03-07 13:24:47.092684UOW completion status                      = Committed-commit Statement

Supplementary explanation 2:

Sometimes the state of the application is commit active,commit active means that the application is doing a commit operation, and when DB2LOGGW writes the log record to disk, the state disappears.
If an application stays commitactive for too long, say, for a few seconds, this indicates a performance problem, either because DB2LOGGW is too busy or the IO of the disk on which the log resides is too busy.
For the former, you can increase the LOGBUFSZ to avoid log buffer full (the number of times you can view the log buffer full from the Num_log_buffer_full column of the Mon_get_database table function) for the latter, you can ' IOSTAT-DT 1 ' command to view the Avgserv column, if more than 3 MS, this indicates the need to check the file system layout and storage configuration. The best practice is to physically separate the container disks from the disks that hold the logs.

Supplementary explanation 3:

Transaction occupancy Log Condition

You canview the logs that each transaction is using by Db2pd-db <dbname>-transactions.
" Select Application_handle,uow_log_space_used,uow_start_time from TABLE (Mon_get_unit_of_work (null,-1)) Order BY uow_log_space_used"  View the overall database log's rate of action:
HTTPS://Www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc /r0060791.html

Key focus parameters are:
Applhandl
The application handle of the transaction.
Spacereserved
The amount of log space is reserved for the transaction.
Logspace
The total log space, which is required for the transaction, including the used space and the reserved space for compensation Log records.

The most common state of the DB2 application (RPM)