The most common state of DB2 applications

Source: Internet
Author: User
Tags db2 db2 connect db2 connect to reserved

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.