 |
» |
|
|
|
Suppose you want to access three DBEnvironments (PartsDBE, SalesDBE, and
AccountingDBE) to simultaneously display related information from each. Depending on your coding environment, you could display data from each DBEnvironment in a separate window or in a specific location in the same window. You choose single-transaction mode because just one transaction at a time must be active. Since this is a display only application, you decide that all transactions
are to be established with the read committed isolation level. You also decide on appropriate timeout values for each transaction and how you want to respond to each possible timeout condition. Your goal is to prevent long waits due to locks held or due to the maximum transaction limit being reached. The following pseudocode illustrates this scenario:
Define and initialize host variables for DBEnvironment and connection names.
.
.
.
Put single-transaction mode in effect. Note that although single-transaction mode is the default, it is good coding practice to specify the transaction mode.
SET MULTITRANSACTION OFF
DECLARE SalesCursor
CURSOR FOR
SELECT PartNumber, InvoiceNumber, SalesDate, SalesAmount, CustomerNumber
FROM Owner.Sales
WHERE PartNumber = :PartNumber
AND SalesDate BETWEEN '1991-01-01' AND '1991-06-30'
Connect to three DBEnvironments specifying a connection name for each connection. Set a timeout value following each current connection.
CONNECT TO :PartsDBE AS :Parts
Note that the following statement sets the Parts connection timeout to the maximum specified in the DBECon file. If the maximum is set to NONE (infinity), no timeout can occur. Here we'll assume that it is set to 300 seconds.
SET USER TIMEOUT MAXIMUM
CONNECT TO :SalesDBE AS :Sales
SET USER TIMEOUT 30 SECONDS
CONNECT TO :AccountingDBE AS :Accounting
SET USER TIMEOUT 30 SECONDS
Set the current connection to Parts.
SET CONNECTION :Parts
Begin a transaction that accesses PartsDBE. This transaction displays parts data for a range of part numbers. Here, for clarity, the range is hard coded. You could, however, use host variables to prompt the user for the lower and upper limits. Another alternative would be to use dynamic processing, possibly with dynamic parameters.
BEGIN WORK RC
BULK SELECT PartNumber, PartName, SalesPrice
INTO :PartsArray, :StartIndex, :NumberOfRows
FROM PurchDB.Parts
WHERE PartNumber BETWEEN 20000 AND 21000
Test the sqlcode field of the sqlca. If it equals -2825, a timeout has occurred, and the transaction was rolled back. Display a message and gracefully exit the application.
Otherwise, end the transaction.
COMMIT WORK
Set the current connection to Sales.
SET CONNECTION :Sales
Prompt the user for a part number in the displayed range and accept the response into a host variable named PartNumber.
OPEN SalesCursor
Begin a second transaction that accesses SalesDBE. This transaction displays sales data for the first six months of 1991 based on the PartNumber entered by the user. Here, for clarity, the range is hard coded. You could, however, use host variables to prompt the user for the lower and upper limits of a date range. Another alternative would be to use dynamic processing, possibly with dynamic parameters.
BEGIN WORK RC
BULK FETCH SalesCursor
INTO :SalesArray, :StartIndex2, :NumberOfRows2
Test the sqlcode field of the sqlca. If it equals -2825, a timeout has occurred, and the transaction was rolled back. Display a message and prompt the user to try again or exit the application.
If they choose to try again, re-execute the transaction. If they choose to exit the application, do so gracefully.
If no timeout error (or other error) occurred, continue.
COMMIT WORK
Set the current connection to Accounting.
SET CONNECTION :Accounting
Prompt the user for an invoice number, and accept the response into a host variable named InvoiceNumber.
Begin a third transaction accessing AccountingDBE. This transaction displays accounting data for a part number and an invoice number based on the user entered part number and invoice number. Again you could use dynamic processing, possibly with dynamic parameters.
BEGIN WORK RC
BULK SELECT InvoiceNumber, PartNumber, InvoiceDate, DueDate, DatePaid
INTO :AccountingArray, :StartIndex3, :NumberOfRows3
FROM Owner.Accounting
WHERE InvoiceNumber = :InvoiceNumber
AND PartNumber = :PartNumber
Test the sqlcode field of the sqlca. If it equals -2825, a timeout has occurred, and the transaction was rolled back. Display a message and prompt the user to try again or exit the application.
If they choose to try again, re-execute the transaction. If they choose to exit the application, do so gracefully.
If no timeout error (or other error) occurred, continue.
COMMIT WORK
At this point you could loop back to ask the user for either another invoice number for the same part number, another part number from the range already selected in the first transaction, or a new range of part numbers. Or you could issue the DISCONNECT ALL statement and exit the application.
|
|
