Informix 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 connects is desirable.

After a connection is established, SQL statements can cause locks to be placed on database entities. The more locks there are in place at a given moment in time, the more likely it is that the locks will hold up another transaction.

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.

• Background color

  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.

Informix-OnLine 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 Committed read:

// Set the lock property to committed read
// in the default transaction object SQLCA.
SQLCA.Lock = "Committed read"

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 a COMMIT or ROLLBACK:

// Set the SQLCA connection properties.
SQLCA.DBMS = "IN9"
SQLCA.database = "ORDERS"// Connect to the database. 

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
// ROLLBACK insertion of ORDER_HEADER.
	ROLLBACK USING SQLCA;
	end if
end if

// Disconnect from the database. 
DISCONNECT USING SQLCA; 

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 the window contains a DataWindow control dw_1 and 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.

// Set the transaction object properties
// and connect to the database. 
// Set the SQLCA connection properties. 
SQLCA.DBMS = "IN9"
SQLCA.database = "ORDERS"

// Connect to the database. 
CONNECT USING SQLCA;

// Tell the DataWindow which transaction object 
// to use. 
SetTransObject( dw_1, SQLCA ) 

// 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, rollback 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

// Disconnect from the database.
DISCONNECT USING SQLCA;