Kbase 20177: Delayed Transaction Write Feature and SQL-89 ODBC Connection
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Unverified
GOAL:
To demonstrate the delayed transaction write (to the .bi file) feature.
GOAL:
Delayed transaction write (to the .bi file) feature demostrated.
FACT(s) (Environment):
Progress 9.x
MERANT 3.70 32-BIT Progress SQL-89
MERANT 3.60 32-BIT Progress SQL-89
MERANT 3.50 32-BIT Progress SQL-89
FIX:
With Progress 8.x, a SQL connection causes an open transaction note to be written to the .bi file. No end statement is written until the SQL-89 user disconnects. This might cause the before-image file (BI) to eventually hit its 2 GB size limit.
Progress 9.x implements what is known as a delayed transaction write for SQL-89 ODBC connections. This is achieved by not actually writing a transaction begin note to the bi file until the user makes a change. Thus, if the user is performing read only operations (selects) the BI file will behave as though a transaction never started.
The following examples demonstrate the differences between how Progress 8.x and 9.x open a transaction in the .bi file when a user is connected via a SQL-89 ODBC connection. Follow these steps:
Version 8.x
1) Connect from WinSQL (or some other SQL client) to the database
via 8.3 OIB/OID.
2) Run: select * from benefits.
3) Run the following Program from the Procedure Editor:
DEF VAR I AS INTEGER.
REPEAT I = 1 TO 100000:
CREATE CUSTOMER.
ASSIGN SALES-REP = "BBB".
END.
4) Run PROMON against the database.
5) In PROMON's first screen, choose #5 - Activity.
Refresh every few seconds. Notice after 60 seconds, the
BI file continues to grow. Clusters are not being re-used.
6) After 10 minutes, disconnect ODBC connection. Notice the BI growth levels off.
Now, in Version 9.x
1)Connect from WinSQL (or some other SQL client) to the database
via 8.3 OIB/OID.
2) Run: select * from benefits.
3) Run the following program from the Procedure Editor:
DEF VAR I AS INTEGER.
REPEAT I = 1 TO 100000:
CREATE CUSTOMER.
ASSIGN SALES-REP = "BBB".
END.
4) Run PROMON against the database.
5) In PROMON's first screen, choose #5 -
Activity.
Refresh every few seconds. Notice after 60
seconds, the .bi growth starts to level
off. Clusters are being re-used.