SAP Adaptive
Server Enterprise Performance and locking
An important consideration when designing a database application is
deciding when CONNECT and COMMIT statements should occur to maximize
performance and limit locking and resource use. A CONNECT takes a certain
amount of time and can tie up resources during the life of the connection.
If this time is significant, then limiting the number of CONNECT
statements is desirable.
In addition, after a connection is established, SQL statements can
cause locks to be placed on database entities. The more locks at a given
moment in time, the more likely it is that the locks will hold up another
transaction.
Rules
No set of rules for designing a database application is totally
comprehensive. However, when you design a PowerBuilder application, you
should do the following:
-
Long-running connections
Determine whether you can afford to have long-running
connections. If not, your application should connect to the database
only when absolutely necessary. After all the work for that connection
is complete, the transaction should be disconnected.If long-running connections are acceptable, then COMMITs should
be issued as often as possible to guarantee that all changes do in
fact occur. More importantly, COMMITs should be issued to release any
locks that may have been placed on database entities as a result of
the statements executed using the connection. -
SetTrans or SetTransObject function
Determine whether you want to use default DataWindow transaction
processing (the SetTrans function) or control the transaction in a
script (the SetTransObject function).If you cannot afford to have long-running connections and
therefore have many short-lived transactions, use the default
DataWindow transaction processing. If you want to keep connections
open and issue periodic COMMITs, use the SetTransObject function and
control the transaction yourself.
Isolation feature
SAP Adaptive Server Enterprise databases use the isolation feature
to support assorted database lock options. In PowerBuilder, you can use
the Lock property of the transaction object to set the isolation level
when you connect to the database.
The following example shows how to set the Lock property to Read
uncommitted:
Example 1
This script uses embedded SQL to connect to a database and insert a
row in the ORDER_HEADER table and a row in the ORDER_ITEM table. Depending
on the success of the statements in the script, the script executes either
a COMMIT or a ROLLBACK.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
// Set the SQLCA connection properties. SQLCA.DBMS = "SYC" SQLCA.servername = "SERVER24" SQLCA.database = "ORDERS" SQLCA.logid = "JPL" SQLCA.logpass = "TREESTUMP" // Connect to the database. AutoCommit is set to // False by default. CONNECT USING SQLCA; // Insert a row into the ORDER_HEADER table. // A ROLLBACK is required only if the first row // was inserted successfully. INSERT INTO ORDER_HEADER (ORDER_ID,CUSTOMER_ID) VALUES ( 7891, 129 ); // Test return code for ORDER_HEADER insertion. if SQLCA.sqlcode = 0 then // Since the ORDER_HEADER is inserted, // try to insert ORDER_ITEM. INSERT INTO ORDER_ITEM(ORDER_ID, ITEM_NBR, PART_NBR, QTY) VALUES ( 7891, 1, '991PLS', 456 ); // Test return code for ORDER_ITEM insertion. if SQLCA.sqlcode = -1 then // If insert failed, roll back insertion of // ORDER_HEADER. ROLLBACK USING SQLCA; end if end if // Commit changes and disconnect from the database. DISCONNECT USING SQLCA; |
Error checking
Although you should test the SQLCode after every SQL statement,
these examples show statements to test the SQLCode only to illustrate a
specific point.
Example 2
This example uses the scripts for the Open and Close events in a
window and the Clicked event in a CommandButton to illustrate how you can
manage transactions in a DataWindow control. Assume that the window
contains a DataWindow control dw_1 and that the user enters data in dw_1
and then clicks the Cb_Update button to send the data to the
database.
Since this script uses SetTransObject to connect to the database,
the programmer is responsible for managing the transaction.
The window OPEN event script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
// Set the transaction object properties // and connect to the database. // Set the SQLCA connection properties. SQLCA.DBMS = "SYC" SQLCA.servername = "SERVER24" SQLCA.database = "ORDERS" SQLCA.logid = "JPL" SQLCA.logpass = "TREESTUMP" // Connect to the database. CONNECT USING SQLCA; // Tell the DataWindow which transaction object // to use. SetTransObject( dw_1, SQLCA ) |
The CommandButton CLICKED event script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
// Declare ReturnValue an integer. integer ReturnValue ReturnValue = Update( dw_1 ) // Test to see if updates were successful. if ReturnValue = -1 then // Updates were not successful. Since we used // SetTransObject, roll back any changes made // to the database. ROLLBACK USING SQLCA; else // Updates were successful. Since we used // SetTransObject, commit any changes made // to the database. COMMIT USING SQLCA; end if |
The window CLOSE event script:
|
1 2 |
// Disconnect from the database. DISCONNECT USING SQLCA; |
See also
SAP Adaptive Server
Enterprise Using CONNECT, COMMIT, DISCONNECT, and ROLLBACK