Application of Transaction Management in DB Query Analyzer in DB2

Application of Transaction Management in dB query analyzer in DB2

Magenfeng

(Guangdong Union Electronic Toll Co., Ltd., Guangzhou 510300, China)

 

Summary     Transaction control is one of the key technologies in the database application system. It outlines the concept of transaction control and transaction control in dB query analyzer, this section describes how to use the transaction control in dB query analyzer in DB2.

Keywords   DB query analyzer; transaction control; DB2

 

TheApplication of the transactions control in DB2 with DB Query Analyzer

Ma gen-Feng 

          (Guangdong unitoll collection inconfigurated, Guangzhou 510300)

Abstract   Transaction control is one of the key technology in the database application system. Firstly, the thesis giveABrief Introduction toConception
The transaction control and the transaction control in DB2 with DB query analyzer. because dB query analyzer is a tools for all RDBMS, it is the same way like in DB2 if you process tractions in the other rdbms doesn't db2.

Key words DB query analyzer; transaction control; DB2

 

 

 

1    Introduction

During database maintenance and management, you often need to modify and delete database records. In terms of data security, you can only submit data to the DBMS after confirming that the operation is correct. This requires the use of DBMS transaction management.

DB query analyzer is an English version of universal database query analyzer. It is based on Windows operating systems and supports Windows operating systems such as Windows 7 and Vista. Because dB query analyzer has powerful functions, friendly operation interfaces, good operability, and many advantages over the database platform, it makes it easy to use it for maintenance, management, and development of various relational database systems.

Next we will introduce how to use dB query analyzer for transaction management through specific examples. As DB query analyzer is a cross-database platform product, transaction management in other relational database systems is similar to the example in this article.

 

 

2     Transaction Control Method

2.1Transaction Control

DB query analyzer supports two transaction management modes:

(1) automatic transaction submission: the user does not need to manage the transaction, and the background database manages the transaction. In most cases, users usually adopt this mode. For example, when data is deleted, after the delete operation is completed, the record is deleted.

(2) manual Transaction Management: you can manage the transaction on your own. You can use parameter settings to SET transaction control to manual transaction management, so that SQL statements executed in dB query analyzer will not be submitted, all updates and modifications take effect only when a transaction is submitted for "Transaction Management" in dB query analyzer.

 

 

2.2    SET transaction management to manual management mode in DB Query Analyzer

Click the "configuration" menu under "Tools" to cancel the selected state of the "transaction auto commit" option. And create window 2. "Transaction autocommit" has changed to "off" in the status bar, as shown in figure 1 and figure 2.

 

                      Figure 1 Set the transaction commit Method

 

 

 

                      Figure 2 Open a new window and use the manual transaction commit mode.

 

 

 

3    Transaction Control demo

Next we will take the transaction management in DB Query Analyzer in DB2 as an example to introduce the transaction management in DB Query Analyzer.

 

3.1    Modify records in the table before TB_AREA

First, we need to query the records of the TB_AREA table before modification in the DEVELOP mode, as shown in 3.

 

 

                      Figure 3 Modify records in the tb_area table

 

 

 

3.2    Modify records in Table TB_AREA

Next, we modify the record in the table tb_area in the window 2 for manual transaction submission, and change the areaserverip value of the "test area" from "10.10.10.10" to "11.11.11.11 ", access the table tb_area with a read-only lock. You can see that the record in window 2 has changed, as shown in 4. Then, execute the query statement in window 1, the query result is not displayed because no R (read-only lock) is added during the query. This indicates that there are uncommitted transactions in the table tb_area, as shown in Figure 5.

 

 

                      Figure 4 Modify records in Table tb_area

 

 

 

 

                      Figure 5 The table tb_area contains uncommitted or rolled back transactions. Therefore, you can only use the R lock to query the table in other sessions.

 

 

3.3    Cancel transaction

Switch to window 2 and click "Transaction Manager" in the "Query" menu. A message box is displayed. A message is displayed, indicating that a transaction may exist in the current window. to commit a transaction, click yes. to roll back the transaction, click NO. To cancel nothing, as shown in:

 

 

                      Figure 6 Transaction Management prompt

 

 

Click "no" to cancel the modification. Run the query in window 1 and find that the AREASERVERIP value of the "test area" in the table TB_AREA is still "10.10.10.10", as shown in 7.

 

 

                      Figure 7 Query after transaction rollback

 

 

 

3.4    Commit transactions

Redo Step 3.2 to modify the record, switch to window 2, click "Transaction Management" in "query", and click yes to submit the transaction (see figure 5 ).

Then execute the query in window 1 and find that the AREASERVERIP Value of "test area" in Table TB_AREA is changed to "11.11.11.11", as shown in:

 

 

                      Figure 8 Queries performed after the transaction is committed

 

 

 

 

4    Conclusion

Transaction Management is a frequently used operation during database maintenance, management, and development. Every programmer wants to perform database transaction operations conveniently and securely, while DB Query Analyzer meets this requirement.

 

 

References:

[1]Zhang longxiang, Huang zhengduan, Tao Ying· Database principles and Design · CHENGDU: Southwest Jiaotong University Press, 1995.

[2]Application of transaction control of ActiveX Data Objects in VB and DELPHI · Zhengzhou: Journal of Information Engineering University 2001.

[3]Ma genfeng · Application of Transaction Management in Oracle in universal database query analyzer · Shanghai: Microcomputer Application 2008.