Home

Informix Embedded SQL TP/XA Programmer`s Manual

1. cc xa_open open_str 120 TMNOFLAGS printf xa_open returned d sqlcode is d n cc sqlca sqlcode if sqlca sqlcode 0 exit 1 printf Rm id is d n get_rmid cc xa_close 0 TMNOFLAGS The xalocal h file contains the following lines tdefine RMID 1 define bcopy src dest len memcpy dest src len extern struct xa_switch_t infx_xa_switch macros to make XA calls easier define xa_open i r f infx_xa_switch xa_open_entry i r f define xa_close i r f infx_xa_switch xa_close_entry i r f define xa_start x r f infx_xa_switch xa_start_entry x r f idefine xa_end x r f infx_xa_switch xa_end_entry x r f define xa_rollback x r f infx_xa_switch xa_rollback_entry x r f define xa_prepare x r f infx_xa_switch xa_prepare_entry x r f define xa_commit x r f infx_xa_switch xa_commit_entry x r f define xa_recover x c r f infx_xa_switch xa_recover_entry x c r f define xa_forget x r f infx_xa_switch xa_forget_entry x r f itdefine xa_complete h ret r f infx_xa_switch xa_complete_entry h ret r f Programming in an X Open Environment 3 13 Writing Server Programs for an X Open DTP Environment Writing Server Programs for an X Open DTP Environment This section contains the following information about writing server programs m Programming considerations when you use SQL in an X Open DTP application program m Steps for buil
2. R Related reading Intro 16 Release notes location of Intro 15 program item Intro 16 Resource manager RM as participant 1 20 closing 1 21 definition 1 12 1 14 explicit local transactions 3 6 handling a local transaction 1 16 in two phase commit 1 20 2 11 Informix DBMS as 1 14 1 27 interface to AP See AP to RM interface AP RM interface to TM See XA interface making a heuristic decision 1 22 2 13 name 2 5 native RM API 1 23 1 24 opening 1 21 transaction commitment and recovery 2 10 RM See Resource Manager RM rofferr utility Intro 14 ROLLBACK logical log record 2 11 2 14 ROLLBACK WORK statement 1 8 3 17 3 18 S Sample program client program 3 25 server program 3 29 upstock 3 21 Sample code conventions Intro 13 Server program benefits 1 7 building 3 19 communicating with client 1 18 definition 1 6 3 4 in an X Open AP 1 15 in DTP model 1 6 linking in AP RM library 3 20 linking in AP TM 1 24 1 26 J K L MN OP QRS TU VW X Y Z linking in XA library 3 20 sample program 3 29 server ec program 3 29 Service definition 1 6 3 4 opening and closing cursors from within 3 14 using temporary tables in 3 15 Service request 1 6 1 13 1 15 SET CONNECTION statement 3 16 SET EXPLAIN statement 3 16 3 18 SET ISOLATION statement 3 16 3 18 SET LOCK MODE statement 3 17 3 19 SET LOG statement 3 17 3 19 Shared memory request queue 1 20 Software dependencies Intro 4 sqexplain out file 3 16 3 18 SQL
3. get stock_num stock_num atoi stockin convert stock_num to integer prompt for of increase printf tEnter Percent whole number scanf f amp prcnt get of price increase prent 1 prent 100 0 convert to multiplier Place data in message buffer void strcpy audv gt u_manu_code manu_code audv gt u_stock_code stock_num audv gt u_percent prcent End of placing data in message buffer Display column headings printf nStock tDescription tUnit Price Pseudocode that connects with server program and requests the QUERYSTO service to query the stock unit price The returned results will be put in audv message buffer Programming in an X Open Environment 3 27 A Sample DTP ESQL C Application Program 3 28 if OLTPCALL QUERYSTO char audv sizeof struct audv char amp audv amp audv_len 0 1 Invoke OLTPGET CALL to get info from audv message buffer If request fails print the error and free up the buffer fprintf stderr s service failed n amp s s n QUERYSTO audv gt u_errmsg OLTPFREE char audv exit 1 If QUERYSTO service successful invoke routines to get info from message buffer stock_num audv gt u_stock_code void strcpy description audv gt u_stock_des void strcpy amp unit_price amp audv gt u_unit_price rfmtdec amp unit_price format decdsply Forma
4. unit_price is NULL n exit 1 Move one row of data into the message buffer ah audv gt u_stock_code stock_no void strcpy audv gt u_stock_des description void strcpy amp audv gt u_unit_price amp unit_price Return item s stock_num description and unit_price to client program Programming in an X Open Environment 3 31 A Sample DTP ESQL C Application Program 3 32 KI A OLTPRETURN OLTPSUCESS transb gt data The UPDATESTO service performs an update of the unit_price column of the stock table UPDATESTO transb OLTPSVCINFO transb struct app_buffer transv Setup transv to point to the message buffer char pre_manu_codel 4 short pre_stock_no char pre_description 16 decimal pre_unit_price EXEC SQL BEGIN DECLARE SECTION dec_t unit_price EXEC SQL END DECLARE SECTION Setup the pointer to point to OLTPSVCINFO data buffer transv struct app_buffer transb gt data Get the stock row info from message buffer and store the info for later comparsion This ensures that the data the client wants to update is current pre_stock_no audv gt u_stock_code void strcpy description audv gt u_stock_des void strcpy amp pre_unit_price amp audv gt u_unit_price EXEC SQL declare upcurs cursor for setup cursor for update select stock_num description unit_price from stock where manu_code manu_code and stock_n
5. Figure 1 2 shows a local transaction that contains only one service request involving only one database management system DBMS This DBMS manages two databases Database A and Database B Figure 1 2 DTP application Database A A Local Transaction custno custname Surrounded by Server 12 eau BEGIN WORK and 1 8 Database 1235 XSPORTS COMMIT WORK S oim Y management Statements system Database B custno custname 1234 XYZ LTD 1235 XSPORTS Service request BEGIN WORK Service 1 Database A COMMIT WORK An application marks the start and end of a local transaction with only local database transaction commands such as the SQL statements BEGIN WORK COMMIT WORK and ROLLBACK WORK For more information on these SQL statements see the Informix Guide to SQL Syntax For more information on local transactions see page 1 16 When the service request involves more than one server program or database the application must use a global transaction To support such transactions the DTP model must keep track of the following types of information m Which services are in which server program m Which service request to send to which server process m From which client process a given service request originated Without this information the DTP model cannot ensure the integrity of the databases in the event of a system or server failure Informix Embedded SQL TP XA Program
6. Link scripts that facilitate building client and server programs with access to the AP TM library For more information refer to your TM documentation Informix Software for the Resource Manager You need the following Informix products to create an RM in the X Open DTP environment The Informix database server serves as the DBMS system with which the server program communicates The Informix SQL API products ESQL C and ESQL COBOL and the TP XA library support creation of the server programs that access database server INFORMIX ESQL COBOL is not available with Dynamic Server with UD Option Tip You need the TP XA library only if the application uses global transactions 1 26 Informix Embedded SQL TP XA Programmer s Manual Informix Software for the Resource Manager Figure 1 8 shows a sample X Open DTP model that uses TP XA and the Informix DBMS Figure 1 8 The Informix Solution for the X Open DTP Model XA interface eae Informix and the X Open Distributed Transaction Processing Model 1 27 Informix Software for the Resource Manager 1 28 The Informix Database Server as a Resource Manager The Informix database server is a multithreaded database server that provides a flexible threading architecture for the on line transaction processing environment You can use the database server as part of an RM in an X Open DTP environment by performing the following actions m Define the database
7. Informix Embedded SQL TP XA Programmer s Manual Version 9 2 December 1998 Part No 000 5193 Published by INFORMIX Press Informix Corporation 4100 Bohannon Drive Menlo Park CA 94025 1032 1998 Informix Corporation All rights reserved The following are trademarks of Informix Corporation or its affiliates Answers OnLine 7C ISAM Cyber Planet Data Director DataBlade Dynamic Scalable Architecture Dynamic Server Dynamic Server Developer Edition Dynamic 6 Server with Advanced Decision Support Option Dynamic Server with Extended Parallel Option Dynamic Server with MetaCube ROLAP Option Dynamic Server with Universal I Data Option Dynamic Server with Web Integration Option Dynamic gt Server Workgroup Edition 4GL for ToolBus If you can imagine it you can manage it Illustra INFORMIX Informix Data Warehouse Solutions Turning Data Into Business Advantage INFORMIX Enterprise Gateway with DRDA INFORMIX 4GL InformixLink MetaCube NewEra is ON Bar OnLine D namie Server OnLine for NetWare OnLine Secure Dynamic 5 Server OpenCase Regency Support Solution Design Labs Solution Design Program SuperView Universal Web Connect ViewPoint The Informix logo is registered with the United States Patent and Trademark Office Documentation Team Bob Berry Evelyn Eldridge Barbara Nomiyama GOVERNME
8. The following two sections of onstat output are useful for monitoring global transactions m The Userthreads section is generated by the u option m The Transactions section is generated by the x option In an X Open DTP environment the following relationships among users transactions and locks must exist m Locks are owned by a transaction branch Transactions can be associated with an RM database server thread A transaction can exist without being associated with an RM database server thread For more information on how the database server performs locking refer to your Administrator s Guide Integrating the Database Server and TP XA into the X Open DTP Model 2 7 The Userthreads Section The Userthreads Section To generate the Userthreads section output use the u option of the onstat utility This option produces a profile of user activity and it refers to the actual database threads Figure 2 2 shows the headers of output information that appear when you use the u option of onstat Figure 2 2 onstat u Output RSAM Version 9 10 UC1 On Line Up 00 06 16 528 Kbytes Userthreads address flags sessid user tty wait tout locks nreads nwrites The flags column in the Userthreads section refers to the status of a thread The following table describes the XA related flags Position Code Description 1 T Waiting for a transaction In an X Open DTP environment multiple database server threads can acce
9. U Unbuffered logging 3 17 Universal Server RM See INFORMIX Universal Server UNLOCK TABLE statement 3 17 3 19 Users types of Intro 3 W Warning icons Intro 8 X XA interface close string 2 5 2 6 definition 1 23 1 24 open string 2 5 2 6 switch table 2 5 2 6 XA interface library extensions to 3 7 get_rmid 3 11 is_xaopened 3 10 xa h header file 2 5 xa_close 1 21 2 6 A 3 xa_commit 1 21 2 11 2 12 A 3 xa_complete A 5 xa_end 3 14 A 5 xa_forget A 6 xa_open 3 8 xa_open 1 21 2 6 A 7 xa_prepare 1 21 2 11 2 12 A 8 xa_recover A 9 xa_rollback 1 21 2 11 2 12 A 10 xa_start 3 14 A 11 XA interface standard ensuring ACID test 1 24 transactions 1 16 what INFORMIX TP XA supports 1 29 XA Routine Library Name 2 5 XAPREPARE logical log record 2 11 2 12 2 14 2 15 xa h header file 2 5 xa_close XA interface routine 1 21 2 6 A 3 xa_commit XA interface routine 1 21 2 11 2 12 A 3 xa_complete XA interface routine A 5 xa_end XA interface routine 3 14 A 5 xa_forget XA interface routine A 6 xa_open 3 8 xa_open XA interface routine 1 21 2 6 A 7 xa_prepare XA interface routine 1 21 2 11 2 12 A 8 xa_recover XA interface routine A 9 xa_rollback XA interface routine 1 21 2 11 2 12 A 10 xa_start XA interface routine 3 14 A 11 xa_switch_t structure 2 5 XID See Transaction identifier XID X Open compliance level Intro 16 X Open DTP model Applicati
10. action The following table describes the XA related flags Position Code Description 1 A Transaction is owned by a user thread 1 S A global transaction is suspended A suspension occurs when a user thread is no longer associated with a transaction though most likely it will be associated again The suspension ensures that the transaction branch does not reach the precommit phase 1 C The TM is waiting for a rollback to be performed 3 X Prepare state This transaction is prepared to commit for XA 5 G A global transaction is in effect For transactions that are attached to a thread the transaction state flags position 2 are the same in the Userthreads u option and Transactions sections x option If a transaction appears in the Transactions section but not in the Userthreads section the transaction is detached from a thread Integrating the Database Server and TP XA into the X Open DTP Model 2 9 Transaction Commitment and Recovery 2 10 Transaction Commitment and Recovery In the X Open DTP environment the TM manages global transactions Because a global transaction spans more than one RM you can have database server RMs and other RMs working together on a global transaction The TM tracks which RMs are involved in the transaction Although the TM manages the start end and recovery process of a global transaction the RM performs the actual work commitment by managing its transaction branch That is when
11. in time and effort of returning the database to a consistent state by either rolling back or committing the transaction is too high Recovering from a Database Inconsistency If you cannot leave the database in an inconsistent state use the database server logical log to determine how you want to recover from the inconsistent state You must determine which of the following actions to take for the transaction in question m Roll back abort the effects of the global transaction wherever it was committed m Commit the effects of the global transaction wherever it was rolled back To determine which of these actions to take perform the following steps 1 Obtain the local XID from the HEURTX logical log record at the database server RM where the transaction rolled back The transaction that rolled back has a HEURTX record associated with it The HEURTX record contains the local transaction identification number local XID You can use the local XID to locate all associated log records that rolled back as part of this task 2 Look for an XAPREPARE logical log record for the local XID and obtain the GTRID Integrating the Database Server and TP XA into the X Open DTP Model 2 15 The Database Server and Heuristic Decisions 3 Ateach possible participating database server RM site search logical logs for the GTRID associated with the heuristic completion 4 Use the records and your knowledge of the application to construct
12. 1 4 is_xaopened 3 10 is_xaopened 3 10 L Local transaction as transaction mode 3 5 definition 1 7 1 16 3 5 examples of 3 5 explicit 3 6 handled by RM 1 16 handled by TM 1 16 1 20 implicit 3 5 return behavior of SQL statements 3 18 Locale Intro 4 LOCK TABLE statement 3 16 3 18 Locking in an X Open DTP environment 1 28 2 7 3 15 Logical log record COMMIT 2 11 HEURTX 2 14 2 15 purpose of 2 10 ROLLBACK 2 11 2 14 using to recover from database inconsistency 2 15 XAPREPARE 2 11 2 12 2 14 2 15 Long transaction LTX 2 14 LTXEHWM configuration parameter 2 14 LTXHWM configuration parameter 2 14 LTX See Long transaction JK L MN OP QRS TU VW X Y Z M Machine notes Intro 15 Message file for error messages Intro 14 Modularity as benefit of client server model 1 7 N New features of this product Intro 6 O OLTP and distributed transaction processing 1 5 and two phase commit 1 11 and X Open XA specification 1 11 with TP XA 1 29 On line help Intro 14 On line manuals Intro 14 onstat utility monitoring global transactions 2 7 transactions section 2 9 u option 2 8 users sections 2 8 x option 2 9 Open string 2 5 2 6 3 16 P Participant 1 10 1 14 1 20 Platform icons Intro 9 Postdecision phase 1 11 2 11 Precommit phase 1 10 1 22 2 11 Printed manuals Intro 14 Product icons Intro 9 Program group documentation notes Intro 16 release notes Intro 16 Index 3 A BC D E F GH
13. In This Chapter In an X Open distributed transaction processing DTP environment the Informix database server acts as a database management system DBMS in an RM When you use the database server in an X Open DTP environment you must attend to additional setup requirements as well as database administration and configuration issues This chapter covers the following topics m Installing software for an X Open DTP environment Monitoring global transactions Administering the database server in the event of an aborted transaction This chapter assumes that you are familiar with the information in your Administrator s Guide Installing Software for an X Open DTP Environment To use the XA interface that the TP XA library defines as an interface between the database server and your transaction manager you must install the following products A TM that supports the X Open specification as described in the document Distributed Transaction Processing The XA Specification by X Open Company Limited February 1992 The Informix database server INFORMIX ESOL C Integrating the Database Server and TP XA into the X Open DTP Model 2 3 Installing the Transaction Manager Installing the Transaction Manager Although Informix implements the database portion of the XA standard the TM configures coordinates and controls the X Open DTP environment The individual TM software supplier configures and manages the system You cannot use
14. and code sets All culture specific information is brought together in a single environment called a GLS Global Language Support locale The examples in this manual are written with the assumption that you are using the default locale en_us 8859 1 This locale supports U S English format conventions for dates times and currency In addition this locale supports the ISO 8859 1 code set which includes the ASCII code set plus many 8 bit characters such as and fi If you plan to use nondefault characters in your data or your SQL identifiers or if you want to conform to the nondefault collation rules of character data you need to specify the appropriate nondefault locale For instructions on how to specify a nondefault locale additional syntax and other considerations related to GLS locales see the Informix Guide to GLS Functionality Informix Embedded SQL TP XA Programmer s Manual Demonstration Databases Demonstration Databases The DB Access utility which is provided with your Informix database server products includes one or more demonstration databases that contain information about a fictitious wholesale sporting goods distributor You can create and populate these demonstration databases with command files that are included with the database server Many examples in Informix manuals are based on these databases For a complete explanation of how to create and populate the demonstration databases refer to yo
15. 25 1 26 1 26 1 29 2 10 2 10 2 13 Appendix A XA Routine Return Codes Index Table of Contents v Introduction About This Manual Types of Users Software Dependencies Assumptions About Your Locale Demonstration Databases New Features Documentation Conventions Typographical Conventions Icon Conventions Comment Icons Feature Product and Platform leons Compliance Icons Command Line Conventions How to Read a Command Line agen Sample Code Conventions Additional Documentation On Line Manuals Printed Manuals On Line Help saa Error Message Documentation Documentation Notes Release Notes Marline Noles Related Reading Compliance with Industry Standards Informix Welcomes Your Comments Oo OND BD OFF BF WW 2 Informix Embedded SQL TP XA Programmer s Manual In This Introduction This Introduction provides an overview of the information in this manual and describes the conventions it uses About This Manual This manual describes the features and the use of the TP XA library As part of INFORMIX ESQL C the TP XA library facilitates communication between a third party transaction manager TM and an Informix database server for the purpose of distributed transaction processing DTP in a multivendor database setting This library allows the database server to operate as a database management system DBMS in a Resource Manager RM of an X Open distributed
16. Access you must delimit multiple statements with semicolons If you are using an SQL API you must use EXEC SQL at the start of each statement and a semicolon or other appro priate delimiter at the end of the statement Tip Ellipsis points in a code example indicate that more code would be added in a full application but it is not necessary to show it to describe the concept being discussed For detailed directions on using SQL statements for a particular application development tool or client product see the manual for your product Additional Documentation For additional information you might want to refer to the following types of documentation On line manuals Printed manuals On line help Error message documentation Documentation Notes Release Notes Machine Notes Related reading Introduction 13 On Line Manuals UNIX On Line Manuals An Answers OnLine CD that contains Informix manuals in electronic format is provided with your Informix products You can install the documentation or access it directly from the CD For information about how to install read and print on line manuals see the installation insert that accompanies Answers OnLine Printed Manuals To order printed manuals call 1 800 331 1763 or send email to moreinfo informix com Please provide the following information when you place your order m The documentation that you need m The quantity that you need m Your name addres
17. Manager Figure 1 5 shows the X Open version of Figure 1 3 on page 1 9 Both figures show several service requests that involve updates for three databases Figure 1 5 Routing an X Open Global Transaction Database A custno custname 1234 XYZLID Server 1235 XSPORTS management sevice DBMS Database B system custno custname 1234 XYZLTD 1235 XSPORTS Service requests Begin global transaction Call service 1 Database A Server II Database C Call service 2 Database C i A 5 custno custname Commit global transaction Service 2 T234 XYZLID Service 3 1235 XSPORTS Figure 1 5 shows the roles of the RM and TM in an X Open global transaction The TM performs the central coordination of global transactions It also coordinates the work that the RMs perform in their transaction branches Figure 1 5 shows that the global transaction has two transaction branches Assigning Transaction Identifiers The TM uniquely identifies each transaction that it manages by assigning it an XID Each XID identifies both a global transaction and a specific transaction branch as follows m The part of the XID that uniquely identifies the global transaction is called a global transaction identifier GTRID m The part of the XID that uniquely identifies the transaction branch is called a branch qualifier Informix and the X Open Distributed Transaction Processing Model 1 17 The T
18. Server Programs 3 16 Figure 3 1 Behavior of SQL Statements in an X Open DTP Environment Statement Special Behavior CLOSE DATABASE If you issue a CLOSE DATABASE statement in an X Open DTP environment you receive an error CONNECT The current database for a group of servers is set by the open string If you issue a CONNECT statement in an X Open DTP environment you receive an error CREATE If you issue a CREATE DATABASE statement in an X Open DATABASE DTP environment you receive an error DATABASE The current database for a group of servers is set by the open string If you issue a DATABASE statement in an X Open DTP environment you receive an error DISCONNECT If you issue a DISCONNECT statement in an X Open DTP environment you receive an error LOCK TABLE If you issue a LOCK TABLE statement in a global trans action it remains in effect until the completion of the transaction branch SET CONNECTION If you issue a SET CONNECTION statement in an X Open DTP environment you receive an error SET ISOLATION The current isolation level of a database remains in effect unless you change the isolation level in a service When you change an isolation level the change remains in effect until the next modification or until the transaction branch ends SET EXPLAIN The default setting for SET EXPLAIN is off Once you issue a SET EXPLAIN ON statement all subsequent query access procedures are written to a file sqexplain out unti
19. a compensating transaction that either rolls back the committed effects of the transaction or commits the work that was rolled back Tip TM logging information is probably the best source of information when determining which RMs participated in a global transaction For information about how to recover from a mixed or unknown transaction state refer to your TM documentation 2 16 Informix Embedded SQL TP XA Programmer s Manual Programming in an X Open Environment Preparing to Program in an X Open DTP Environment Designing Programs for an X Open DTP Environment Identifying the Transaction Mode Local Transactions Global Transactions ESQL C Extensions to the XA Interface xa_open is_xaopened get_rmid Example Writing Server Programs for an X Open DTP Environment Programming Considerations for Server Programs Using Cursors Using Temporary Tables Using Locking pare Using SQL Statements Building Servers for an X Open DTP Environment Sample ESQL C Programs A Non DTP ESQL C Program A Sample DTP ESQL C Application ere Sample Client Program Sample Server Program 3 3 3 4 3 5 3 5 3 6 3 7 3 10 3 11 3 12 3 14 3 14 3 14 3 19 3 21 3 21 3 24 3 25 3 29 3 2 Informix Embedded SQL TP XA Programmer s Manual In This Chapter To help you create an application program for the X Open DTP environment this chapter discusses the following topics
20. a larger loop You can specify size no more than three 8 p pecuy times in this statement segment 2 of 2 How to Read a Command Line Diagram Figure 1 shows a command line diagram that uses some of the elements that are listed in the previous table Figure 1 Example of a Command Line Diagram setenv INFORMIXC i compiler a pathname To construct a command correctly start at the top left with the command Follow the diagram to the right including the elements that you want The elements in the diagram are case sensitive Figure 1 illustrates the following steps 1 Type setenv 2 Type INFORMIXC 3 Supply either a compiler name or a pathname After you choose compiler or pathname you come to the terminator Your command is complete 4 Press RETURN to execute the command 12 Informix Embedded SQL TP XA Programmer s Manual Sample Code Conventions Sample Code Conventions Examples of SQL code occur throughout this manual Except where noted the code is not specific to any single Informix application development tool If only SQL statements are listed in the example they are not delimited by semicolons For instance you might see the code in the following example CONNECT TO sales_demo DELETE FROM customer WHERE customer_num 121 COMMIT WORK DISCONNECT CURRENT To use this SQL code for a specific product you must apply the syntax rules for that product For example if you are using DB
21. a list of XIDs that the RM has prepared or heuristically completed xa_rollback Tells the RM to roll back a transaction branch xa_start Starts a transaction branch Figure A 1 on page A 3 lists the xa_ routines and their return values Each return code occurs because of one or more circumstances listed in the Reason for Return Codes column The XA interface establishes the following naming conventions used in these return values m Error codes negative values are constants whose names begin with the string XAER_ m Status codes non negative values are constants whose names begin with the string XA_ In addition to the error information listed in this appendix the database server always returns a result code when you execute an SQL statement This result code along with other information about the operation is returned in a global variable called SOLSTATE and in a data structure known as the SQL Communication Area SQLCA For further information about SQLCA consult the Informix Guide to SQL Tutorial Informix Embedded SQL TP XA Programmer s Manual XA Function xa_close xa_commit Return Code XAER_INVAL XAER_PROTO XAER_RMERR XAER_ASYNC XAER_INVAL XAER_PROTO Figure A 1 XA Functions and Their Return Values Reason for Return Code Invalid arguments were specified for this routine The xa_info argument is a null pointer Execution was between an xa_start function and an xa_end fu
22. code Intro 13 SQL statements in an X Open DTP environment 3 15 Switch table 2 5 2 6 System administration information needed by the TM 2 4 recovering from database inconsistency 2 15 setting the open string parameter 2 5 tracking global transactions 2 7 System requirements database Intro 4 software Intro 4 T Table temporary creating ina service 3 15 tbstat utility See onstat utility Thread 2 7 Tip icons Intro 8 TM See Transaction manager TM Transaction aborting 1 22 2 13 ACID test 1 4 1 5 4 Informix Embedded SQL TP XA Programmer s Manual branch See Transaction branch definition 1 4 determining ownership 2 7 determining state of 1 22 2 9 2 13 distributed See Global transaction explicit local 3 6 global See Global transaction heterogeneous 1 25 1 29 implicit local 3 5 local See Local transaction long See Long transaction LTX relationship to a thread 2 7 transaction modes 3 5 Transaction branch See also Global transaction Transaction branch aborting 1 22 and locking 1 28 2 7 3 16 3 17 branch qualifier 1 17 committing 1 11 1 22 2 13 identifier See Transaction identifier XID Informix implementation of 1 28 multiple branches 1 10 preparing to commit 1 10 rolling back 1 11 Transaction identifier XID 1 14 1 17 tracking 1 28 using to analyze database inconsistency 2 15 Transaction management commitment and recovery 2 10 of global transactions 1 20 recovering from d
23. column in an accounts table Informix Embedded SQL TP XA Programmer s Manual Identifying the Transaction Mode For more information on clients servers and services see Client and Server Programs on page 1 6 and Managing Client Server Communication on page 1 18 For information on how to build server processes see Building Servers for an X Open DTP Environment on page 3 19 Identifying the Transaction Mode Your programming adjustments depend largely on the transaction mode in which your program or service is designed to execute A transaction mode can be either global or local Once you know the transaction mode you can write a program that is designed to execute in a global transaction a local transaction or both Tip Regardless of the transaction mode used the service program does not need to include the XA interface routines These routines are used only by the TM to communicate with the RMs The next sections describe each type of transaction Local Transactions A local transaction accesses a single database and a single service Local transactions do not invoke the XA interface A program written for an Informix transaction processing environment uses local transactions either implicit or explicit This type of program if it is to continue using local transactions involves little change to run in an X Open DTP environment If you choose to use the TM to handle client server communication pag
24. from the message buffer The information returned by QUERYSTO appears and the user sees the prompt Do you want to update this record If the user chooses to update the client program calls OPTPCALL again this time to request the UPDATESTO service to update the stock unit price Important In this example the OLTPCALLQ OLTPGET and OLTPFREE functions are pseudofunctions In an actual application use the appropriate AP TM calls of your TM interface to perform these tasks Programming in an X Open Environment 3 25 A Sample DTP ESQL C Application Program Figure 3 4 shows the sample client program client c Figure 3 4 Sample Pseudocode for Client Program Called client c client c The following client program prompts the user for the manufacturer code stock number and the percent of the price increase and then passes the data to the server program that requests the QUERYSTO and UPDATESTO services f include lt stdio h gt include lt ctype h gt include lt decimal h gt include decimal type structure struct decimal short dec_exp exponent base 100 short dec_pos sign l pos O neg 1l null short dec_ndgts number of significant digit short dec_dgts actual digit base 100 hs Pseudocode to include any OLTP header files that are needed dHinclude lt OLTPXA h gt include upstock h struct app_buffer char u_manu_code 4 int u_stock_code char u_stoc
25. of the involved RMs excluding those that responded with a read only status It then calls the xa_commit XA interface routine for each RM to ask it to commit its transaction branch The TM forgets about the transaction branch after all xa_commit routines are complete For information on how the database server participates in the two phase commit see The Database Server and the Two Phase Commit Protocol on page 2 10 For more information on the XA interface see The XA Interface on page 1 24 Before it can call any other XA routine the TM must first call the xa_open XA interface routine to initialize the RM When the communication finishes the TM calls xa_close to close the channel For more information on opening and closing RMs see Distributed TP The XA Specification by X Open Company Limited Informix and the X Open Distributed Transaction Processing Model 1 21 The Transaction Manager 1 22 Heuristic Decisions If an RM aborts the work it is doing for a transaction branch during the precommit phase phase one the TM aborts the transaction during the postdecision phase phase two When the transaction is aborted all RMs are in a consistent state However an RM might make an heuristic decision during the postdecision phase That is an RM that is prepared to commit a transaction branch can decide to commit or abort its work independently of the TM If this occurs when the TM tells the RM to complete
26. server see your Performance Guide m High availability The TM software usually has several built in features to enhance system availability For example when the transaction manager TUXEDO System T detects that a database server aborted abnor mally System T creates a new instance of the failed database server and sends a message to the client The high availability features of the Informix database server include on line archiving incremental archiving mirroring and automatic fast recovery Informix and the X Open Distributed Transaction Processing Model 1 29 Integrating the Database Server and TP XA into the X Open DTP Model Installing Software for an X Open DTP Environment Installing the Transaction Manager Installing the Informix Software Integrating the Database Server with the Tanerion Manager es Describing the Dabas abet Reor Manage Database Logging in an X Open DTP Environment Monitoring Global Transactions The Userthreads Section The Transactions Section Transaction Commitment and Recovery The Database Server and the Two Phase Commit Protocol The Database Server and Heuristic Decisions Causing the Database Server to Make a Heuristic Decision Determining Consistency of an Informix Database Taking Actions to Handle Database Inconsistency 2 4 2 4 2 4 2 5 2 7 2 7 2 8 2 9 2 10 2 10 2 13 2 14 2 14 2 14 2 2 Informix Embedded SQL TP XA Programmer s Manual
27. server to the TM system For more information see Chapter 2 Integrating the Database Server and TP XA into the X Open DTP Model m Use embedded SQL statements from the server program For more information see Chapter 3 Programming in an X Open Environment m Link the TP XA library in your application program For more information on the TP XA library see The TP XA Library as the Server XA Interface which follows this section A TM performs its responsibilities according to the individual TM software designer As a result the Informix database server does not know and cannot predict what tasks the TM is processing An Informix database server RM knows only about the work it does for a transaction branch whereas the TM might be processing many other tasks In an XA environment the database server RM performs the following tasks m Responds to the XA requests that it receives from the TM m Tracks GTRIDs for the TM Important In the Informix implementation of transaction branches each branch is treated as a separate transaction No two transaction branches even if they belong to the same transaction can share locks For more information on locking see your Administrator s Guide The TP XA Library as the Server XA Interface TP XA is a library of functions that you link to your SQL API server program This library has the XA routines that must be present for the server program in the RM to commu
28. services the user needs performed To initiate a service the client program sends a service request to the server program that offers that particular service Important In the DTP model the definitions of the terms client and server differ from their definitions in the Informix client server architecture In the DTP model a client is not an entire application it is only that part of the application that handles the user interface In the DTP model a server is not a database server but the second part of the application the part that handles the database communication Figure 1 1 shows the relationship between the client program the server program and the service requests Each instance of the application program contains one client program and at least one server program Figure 1 1 DTP application Application Program Showing Client and Server Programs Service request The client and server are distinct programs The client program does not perform the database tasks Instead it sends a service request to a server The server program then initiates the execution of the desired service a specific operation performed by a code module that is embedded ina server A server program accepts requests and dispatches them to the appropriate service Tip For an example of client and server programs see A Sample DTP ESQL C Q Application Program on page 3 24 1 6 Informix Embedded SQL TP XA Programmer s Manua
29. than it does not exist or it has no logging 5 of 10 XA Routine Return Codes A 7 A 8 XA Function Informix Embedded SQL TP XA Programmer s Manual Return Code xa_prepare XAER INVAL XAER_PROTO XAER_RMFAIL XAER_ASYNC XAER_RMERR XA_RBTRANSIENT Reason for Return Code Invalid arguments were specified for this routine This routine was called before a call to xa_open A transaction was active or suspended The rmid argument is not the same as that passed to xa_open The transaction was already prepared for commit The database server failed The database server encountered an error while implementing asynchronous opera tions with the TMASYNC flag passed in the flags argument An internal communication error occurred between the application development tool and the database server The database server could not find deferred constraint information probably because of memory corruption The database server encountered an error while freeing the shared memory trans action entry The entry is only freed if the prepare fails and the transaction is rolled back or the was read only Execution was between an xa_start function and an xa_end function on another transaction when this call occurred The database server was unable to save the state of the current transaction to perform the prepare 6 of 10 XA Function xa_recover Return Code XA_RBINTEGRITY XAE
30. the TM tells the RM to commit the RM commits the work that it performed for the transaction branch The Database Server and the Two Phase Commit Protocol Consider a global transaction that consists of a withdrawal from a savings database and a deposit into another savings database that another instance of the database server manages Because neither database server RM knows about the other the TM must ensure that the same decision either to commit or abort the transaction applies to both RMs RM product vendors decide how their RMs handle transaction commitment and recovery The following sections focus on how the database server interacts with the TM during transaction commitment and recovery When the database server acts as part of an RM in an X Open DTP environment it relies on the following elements Communication Communication among the TM and participating database server RMs occurs through the XA routines that control the two phase commit protocol m Logical log records Logical log records of the transaction must be stored on stable media to ensure data integrity and consistency if a failure occurs at a participating database server RM Informix Embedded SQL TP XA Programmer s Manual The Database Server and the Two Phase Commit Protocol Transaction information Transaction information must be stored in shared memory at each participating database server RM This requirement is important for tracking open tbls
31. the XA specification describes The TM uses these routines to manage global transactions The names of these TP XA routines as defined by the XA specification begin with the string xa_ The xa_ routines must be supported by all RMs and the TM operating in the X Open DTP environment When a global transaction occurs the TM through the XA interface ensures that the transaction meets all the require ments of the ACID test For a description of the ACID test requirements see Transaction Processing on page 1 4 To successfully meet these requirements the TM uses the routines of the XA interface to accomplish the following tasks m Central coordination of global transactions When an application program calls a TM to start a global transaction the TM uses the XA interface to inform RMs of their transaction branches m Transaction commitment and recovery using two phase commit protocol After the application program uses the native RM API through the AP to RM interface to work in support of the global transaction the TM uses the XA interface to commit or abort branches The xa_ routines of the XA interface are listed in Appendix A Informix Embedded SQL TP XA Programmer s Manual Software Products and the X Open DTP Model Software Products and the X Open DTP Model Although the database server supports a form of DTP it does not provide support for the following types of DTP Distributed transactions across database
32. the database server RM writes a ROLLBACK log record to a logical log Integrating the Database Server and TP XA into the X Open DTP Model 2 11 The Database Server and the Two Phase Commit Protocol Figure 2 4 shows the flow of information between the TM and the database server Figure 2 4 Flow of Information Between the TM and Database Server During a Two Phase Commit TM asks xa_prepare Database server asks p gt z Prepared to Commit Able to Commit ae oe 6 XAPREPARE ROLLBACK WORK logical log record logical log record Database server notifies TM that Database server rolls back transaction can be committed transaction and notifies TM Voie TM decides Commit all RM can commit Abort one or more RMs cannot commit weon rt Database server If this RM is not the one causing the commits transaction rollback it rolls back its transaction COMMIT ROLLBACK logical log record logical log record Database server notifies TM of Database server notifies TM of successful commit unsuccessful commit ae a TM commits or aborts global transaction according to notifications from RMs 2 12 Informix Embedded SQL TP XA Programmer s Manual The Database Server and Heuristic Decisions The Database Server and Heuristic Decisions When the database server RM makes a heuristic decision and aborts the transaction during the commit phase independently of the TM instructions to the database server RM y
33. MIGRATE flag is passed in the flags argument but transaction migration is not supported The rmid argument is not the same as that passed to xa_open This routine has been called in an improper context The database server failed The database server encountered an error while implementing asynchronous opera tions when the TMASYNC flag was passed in the flags argument The database server encountered failure on suspension of the transaction probably because of a memory allocation error 3 of 10 XA Routine Return Codes A 5 XA Function Return Code Reason for Return Code An internal communication error occurred between the application development tool and the database server The database server encountered a failure while attempting to save deferred constraint information a resource allocation error XA_RBOTHER The TMSUCCESS flag was passed in the flags argument and this is a suspended transaction The implicit resumption of the transaction failed The database server has marked the trans action for rollback only probably due to a long transaction XA_RBROLLBACK The TMFAIL flag was passed in the flags argument XAER_NOTA The database server could not find the transaction indicated by the xid argument xa_forget XAER_INVAL Invalid arguments were specified for this routine XAER_PROTO This routine was called before a call to xa_open The rmid argument is not the same as that passed to xa_
34. NT LICENSE RIGHTS Software and documentation acquired by or for the US Government are provided with rights as follows 1 if for civilian agency use with rights as restricted by vendor s standard license as prescribed in FAR 12 212 2 if for Dept of Defense use with rights as restricted by vendor s standard license unless superseded by a negotiated vendor license as prescribed in DFARS 227 7202 Any whole or partial reproduction of software or documentation marked with this legend must reproduce this legend Informix Embedded SQL TP XA Programmer s Manual Table of Contents Introduction In This Introduction 3 About This Manual 3 Types of Users 3 Software Dependencies 4 Assumptions About Your Locale 4 Demonstration Databases 5 New Features goa 6 Documentation Conventions 6 Typographical Conventions 7 Icon Conventions bape 8 Command Line Conventions 10 Sample Code Conventions 13 Additional Documentation 13 On Line Manuals 14 Printed Manuals 14 On Line Help 14 Error Message Documentation 14 Documentation Notes Release Notes Machine Notes 15 Related Reading i 3 16 Compliance with Industry Sistidends 16 Informix Welcomes Your Comments 17 Chapter 1 Informix and the X Open Distributed Transaction Processing Model In This Chapter 2 lt 2 gee Boa Se ee a e ae pe a a E Distributed Transaction Processing 14 Transaction Processing 2 ew we ee 614 Features ofa DTP Sys
35. Open Environment 3 11 Example Example To compile the following example program you must first set the THREADLIB environment variable to an appropriate value For example on a Solaris UNIX system you would set THREADLIB to SOL Then you would use the following command to compile the program esql thread xa_ex ec 1thxa For more information about the THREADLIB environment variable and the thread and lthxa command line options refer to the INFORMIX ESQL C Programmer s Manual The following example illustrates the usage of the xa_open is_xaopened and get_rmid functions include lt stdio h gt EXEC SQL include sqlca EXEC SQL include xa h EXEC SQL include xalocal h main char server 100 getenv p char open_str 257 TNtace 0 memset server 0100 stcopy server p getenv INFORMIXSERVER if server 0 printf Set INFORMIXSERVER and run again n exit 1 Note that you can use any server name defined in your sqlhosts file sprintf open_str DB new RM s CON conl server cc xa_open open_str 0 TMNOFLAGS printf xa_open returned d sqlcode is d n cc sqlca sqlcode if sqlca sqlcode 0 exit 1 cc is_xaopened if cc 1 printf We have executed xa_open n 3 12 Informix Embedded SQL TP XA Programmer s Manual Example else printf We have an XA session n sprintf open_str new s server Another format of xa_open
36. P Environment Statement Local Transaction In a Global Transaction In a Local Transaction SET LOCK MODE SET LOCK MODE takes SET LOCK MODE takes effect only when the time out effect when issued period is shorter than the period that the TM software specifies SET LOG Returns an error Returns an error UNLOCK TABLE Returns an error Returns an error 2 of 2 For a full description of the SQL statements listed in Figure 3 2 on page 3 18 refer to the Informix Guide to SQL Syntax Building Servers for an X Open DTP Environment You can build a server program in one of the following ways m Use the commands and options that your TM product provides to create a server for an X Open DTP environment For instructions on how to link the TP XA and AP TM libraries to your server program refer to your TM documentation Also refer to the TP XA documen tation notes m You can build an ESQL C server program manually using the ESQL C preprocessor esql This section describes the second method of building a server process To build an ESQL C server process manually you must first preprocess the server program and then link the object o files with the TP XA library as follows 1 Execute the esql command with the c command line option as the following example shows esql c srvrprog ec The c option is not recognized by esq so it is passed through to the C compiler cc by default This option suppresses the link phase of the comp
37. R_NOTA XA_RBOTHER XA_RDONLY XA_RBROLLBACK XAER_INVAL XAER_PROTO XAER_RMFAIL XAER_ASYNC XAER_RMERR Reason for Return Code The database server encountered a constraint error while checking deferred constraints The database server could not find the transaction indicated by the xid argument The database server has marked this trans action as rollback only probably because of a long transaction This transaction was read only and has been committed The database server rolled back the trans action because of a failure during write of the prepare log record Invalid arguments were specified for this routine The xid argument is null and the count argument is greater than 0 The count argument is less than 0 This routine was called before a call to xa_open The rmid argument is not the same as that passed to xa_open This routine has been called in an improper context The database server failed The database server encountered an error while implementing asynchronous opera tions when the TMASYNC flag was passed in the flags argument The database server encountered a memory allocation error 7 of 10 XA Routine Return Codes A 9 A 10 XA Function Return Code Informix Embedded SQL TP XA Programmer s Manual xa_rollback XAER_INVAL XAER_PROTO XAER_RMFAIL XAER_ASYNC XAER_RMERR XA_RBTRANSIENT XAER_NOTA Reason for Return Code The number of XIDs retur
38. SQL C Programs The actual programming changes that you make when you create programs with INFORMIX ESQL C in an X Open DTP environment are minor However the way that you design the program is quite different than the way that you would design it for anon X Open DTP environment For more information see Designing Programs for an X Open DTP Environment on page 3 4 This section takes an ESQL C program and shows one way to redesign the program for an X Open DTP environment It contains the following two ESQL C programs m Anon DTP ESQL C program that performs both user interactions and database interactions m An ESQL C AP for the X Open DTP environment that includes two programs a client program and a server program Each program lets you update the unit price of a product for a chosen manufacturer using the stores7 database For information about the stores7 database refer to the Informix Guide to SQL Reference A Non DTP ESQL C Program The upstock ec program is a small non DTP application It is not divided into a client and server process instead it performs user and database interaction in the same ESQL C program The program prompts the user for the manufacturer code the stock number and the percentage that the user wants to increase the unit price of the product With this information it takes the following actions m Retrieves the row from the stock table that matches the specified manufacturer code and stock numb
39. T WORK m TheT An explicit local transaction is identical to a transaction under the control of an RM except that the application makes a call to the trans action manager to begin and commit the work For the specific statements to begin and commit a local transaction under the control of your TM consult your TM manual Global Transactions A global transaction is a transaction that can span more than one service database server and RM Global transactions are identified and controlled by the TM which uses the XA interface to communicate with each participating RM When you write a program that has the potential to execute as part of a global transaction it must adhere to certain guidelines to execute success fully in an X Open DTP environment Informix Embedded SQL TP XA Programmer s Manual ESQL C Extensions to the XA Interface A program written for an Informix transaction processing environment does require some modification to use global transactions in an X Open DTP environment It must contain the appropriate AP TM calls to perform the following tasks m Mark the start and end of the global transaction m Handle client server communication However you do not need to modify the program to use the XA interface because the XA routines that allow the RM and servers to communicate are never called directly by the server program ESQL C Extensions to the XA Interface Informix supports the following extensions to the X Ope
40. TION char manu_code 4 short stock_num char description 16 dec_t unit_price EXEC SQL END DECLARE SECTION char manu_codel 4 Informix Embedded SQL TP XA Programmer s Manual A Sample DTP ESQL C Application Program short stock_no char errmsg 400 The QUERYSTO service selects the data in the stock table for the specified manufacturer code and stock number Row information is received in the message buffer Ef QUERYSTO transb OLTPSVCINFO transb Setup transv pointer to point to the message buffer af struct app_buffer transv Setup the pointer to point to OLTPSVCINFO data buffer Kj transv struct app_buffer transb gt data Pseudo Code to get the manu_code from the message buffer if OLTPGETBUF transb manu_codel 1 call routines to check the data TYPE OLTPRETURN OLTPFAIL 0 transb gt data 0L 0 Return error message Pseudo Code to get the stock_num from the message buffer if OLTPGETBUF transb stock_no 1 call routines to check the data TYPE OLTPRETURN OLTPFAIL 0 transb gt data 0L 0 Return error message Retrieve one row of data no cursor is needed xI EXEC SQL select stock_num description unit_price into stock_num description unit_price from stock where manu_code manu_codel and stock_num stock_no if risnull CDECIMALTYPE amp unit_price Skip if price is NULL fprintf stderr
41. ags argument and the transaction was not suspended by this user TMRESUME was not passed in the flags argument and the transaction was suspended by this user The rmid argument is not the same as that passed to xa_open This routine has been called in an improper context The database server failed The database server encountered an error while implementing asynchronous opera tions when the TMASYNC flag was passed in the flags argument Resume of the transaction failed The database server could not find the suspended context probably because of memory corruption An internal communication error occurred between the application development tool and the database server The database server could not find deferred constraint information probably because of memory corruption 9 of 10 XA Routine Return Codes A 11 A 12 XA Function Return Code Informix Embedded SQL TP XA Programmer s Manual XAER_OUTSIDE XAER_DUPID XAER_NOTA XA_RBOTHER Reason for Return Code The database server could not allocate a new transaction because it ran out of entries in the transaction table The begin work operation for this transaction failed A local transaction is open and must be committed or rolled back before calling xa_start The database server was expecting to allocate a new transaction but the XID already exists Neither TMJOIN nor TMRESUME flag has been passed in as part of the flags argum
42. anager 1 14 m m The Resource Manager In an X Open DTP environment the RM manages a set of shared resources For a database application the most commonly accessed shared resources are databases and these databases are managed by a DBMS A single DBMS might manage several independent databases The RM includes the following parts m A DBMS that supports the following tasks Understands how to commit or roll back a transaction Communicates with the server program in support of a service through the native RM API Recognizes a global transaction accepting a transaction identifier XID from the TM and mapping it to an RM specific XID For more information on global transaction identifiers GTRIDs see Assigning Transaction Identifiers on page 1 17 Acts as a participant in the two phase commit and recovery acting on the XA requests it receives from the TM For more information see Controlling the Two Phase Commit on page 1 20 The DBMS software is usually provided by a third party vendor such as Informix In the Informix implementation of an RM the Informix database server is the DBMS To establish the Informix database server as the RM DBMS you must provide certain information to the TM For more information see Chapter 2 Integrating the Database Server and TP XA into the X Open DTP Model Informix Embedded SQL TP XA Programmer s Manual The Transaction Manager m The server pro
43. atabase inconsistency 2 15 responsibility for 1 15 tracking 2 7 Transaction manager TM as coordinator 1 15 1 20 assigning XIDs 1 17 communicating with RMs 1 21 definition 1 12 1 15 ensuring ACID test 1 24 explicit local transactions 3 6 handling global transaction 1 17 2 10 A BC D E F GH handling heuristic decisions 1 22 handling local transaction 1 16 1 20 in client server communication 1 18 in two phase commit 1 20 2 10 INFORMIX TP XA requirements 1 29 installing 2 4 integrating the database server 2 4 interface to AP See AP to TM interface AP TM interface to RM See XA interface managing client server communication 1 18 managing transactions 1 16 shared memory request queue 1 20 switch table 2 5 transaction commitment and recovery 2 10 Transaction mode 3 5 Transaction processing ACID test properties 1 4 1 5 definition 1 4 required capabilities 1 4 X Open DTP model 1 3 See also Distributed transaction processing DTP Transactions section of onstat utility 2 9 Two phase commit protocol coordinator 1 10 1 15 1 20 definition 1 10 flow of information between transaction manager and resource manager 1 6 1 15 2 12 goal of 1 11 making a heuristic decision 1 22 2 13 participant 1 10 1 14 1 20 postdecision phase 1 11 2 11 precommit phase 1 10 1 22 2 11 recovering from database inconsistency 2 15 role of the database server 2 10 XA interface requirement 1 24 J K L MN OP QRS TU VW X Y Z
44. ations for Server Programs Once a service is exited any cursors in that service cannot be used If you attempt to use a cursor in a service other than the one in which it was declared the database server returns an error For details about XA interface routines see Distributed Transaction Processing The XA Specification by X Open Company Limited February 1992 Using Temporary Tables Temporary tables can be used in any transaction mode However you must create and use the temporary table in a single service The temporary table is dropped when you exit from a service Using Locking You can use locking in any transaction mode However an important restriction exists on locking Informix implements each transaction branch as a separate transaction Therefore no two transaction branches can share locks This restriction applies even when the transaction branches belong to the same global transaction For more information on how the database server performs locking refer to your Administrator s Guide Using SQL Statements You can use almost all SOL statements in an X Open DTP environment in either local or global transaction mode However some statements might behave differently than if they were in a non X Open DTP environment see Figure 3 1 or they might return an error when they are involved in a global transaction see Figure 3 2 on page 3 18 Programming in an X Open Environment 3 15 Programming Considerations for
45. ce The following sections show a sample client and server program for the upstock program Most of the code for these two programs is pseudocode because the actual AP TM calls and the way client server connections are implemented are TM specific Lines of code prefixed with OLTP are intended for a TM To enable your AP to communicate with a TM embed in your appli cation the AP TM calls that the TM software provides Important These sample client and server programs handle a local implicit transac tion and therefore do not make use of the XA interface Informix Embedded SQL TP XA Programmer s Manual A Sample DTP ESQL C Application Program For specific information on how to write client service and server programs refer to your TM manual Sample Client Program The client program client c is a C program that performs the user interface portion of the upstock ec program For example the Enter Mfr Code prompt or the Update unit_price prompt is user supplied information that is included in the client program As Figure 3 4 on page 3 26 shows client c performs the following tasks 1 It prompts the user for the manufacturer code stock number and percentage of price increase It connects to the server program by calling the OLTPCALL function requesting the QUERYSTO service to query the stock unit price It receives the information from the server program and uses the OLTPGET function to retrieve this information
46. ch RM made a heuristic decision For more information on how to determine whether an RM has made a heuristic decision see The Database Server and Heuristic Decisions on page 2 13 Informix Embedded SQL TP XA Programmer s Manual The Model Interfaces The Model Interfaces For two parts of the X Open DTP model to communicate they must use an interface An interface is a series of functions linked into the sending and receiving programs so that each program can send and receive data The X Open DTP model has three paths of communication which result in the following three interfaces m The AP to RM interface shown in Figure 1 8 on page 1 27 as AP RM allows the application program to call an RM to request work that involves neither coordination of a global transaction nor management of the TM m The AP to TM interface shown in Figure 1 8 on page 1 27 as AP TM lets the application program call the TM to request management of the transaction m The XA interface allows two way communication between an RM and the TM The XA interface implements the two phase commit protocol see page 1 20 between the RMs and the TM Figure 1 8 on page 1 27 shows the interfaces of the X Open DTP model The AP to RM Interface The AP RM enables the application program to communicate with the RM A library called the native RM API contains functions that these two programs use to communicate Link this native RM API into your server program s
47. cumentation types of documentation notes Intro 15 error message files Intro 14 machine notes Intro 15 on line help Intro 14 on line manuals Intro 14 printed manuals Intro 14 related reading Intro 16 release notes Intro 15 DTP See Distributed Transaction Processing DTP Durability ACID test property 1 4 E Environment variables Intro 7 en_us 8859 1 locale Intro 4 Error message files Intro 14 esql command 3 19 Explicit local transaction 3 6 Extension to SQL symbol for Intro 10 F Feature icons Intro 9 Features of this product new Intro 6 Find Error utility Intro 15 finderr utility Intro 14 Function library get_rmid 3 11 is_xaopened 3 10 xa_open 3 8 G get_rmid 3 11 Global Language Support GLS Intro 4 2 Informix Embedded SQL TP XA Programmer s Manual Global transaction as transaction mode 3 6 committing 2 13 creating long transaction 2 14 definition 1 7 1 16 3 6 heuristic decisions 1 22 2 13 identifier See Global Transaction Identifier GTRID locks in 1 28 2 7 3 16 3 18 management of 1 20 2 10 monitoring with onstat 2 7 return behavior of SQL statements 3 18 with two phase commit protocol 1 10 XA interface requirements 1 24 See also Local transaction Transaction branch Global Transaction Identifier GTRID after a heuristic decision 1 22 interpreted by RM 1 15 using to analyze database inconsistency 2 15 using to recover from database inconsistency 2 15 Global tra
48. d so forth Convention Meaning KEYWORD All primary elements in a programming language statement keywords appear in uppercase letters in a serif font italics In text new terms and emphasized words appear in italics In italics syntax and code examples variable values that you are to italics specify appear in italics boldface Names of program entities such as classes events and tables boldface environment variables file and pathnames and interface elements such as icons menu items and buttons appear in boldface monospace Information that the product displays and information that you monospace enter appear in a monospace typeface KEYSTROKE Keys that you are to press appear in uppercase letters in a sans serif font This symbol indicates the end of one or more product or platform specific paragraphs This symbol indicates a menu item For example Choose ToolsOptions means choose the Options item from the Tools menu Tip When you are instructed to enter characters or to execute a command immediately press RETURN after the entry When you are instructed to type the text or to press other keys no RETURN is required Introduction 7 Icon Conventions Icon Conventions Throughout the documentation you will find text that is identified by several different types of icons This section describes these icons Comment Icons Comment icons identify three types o
49. ding a server program so that it links in the appropriate XA AP TM and AP RM interfaces m Sample ESQL C programs that demonstrate the changes needed to convert a non DTP ESQL C program to a program for an X Open DTP environment Programming Considerations for Server Programs In general programming a server program involves creating ESQL programs or services to access a specific database server The following sections list the differences that you should be aware of when you use Informix products in an X Open DTP environment Using Cursors Database cursors can be used in any transaction mode However you must declare open and close the cursor in a single service In programming terms a single service can be defined as follows m A single service starts with a call to the xa_start XA interface routine which passes a TMJOIN flag or no flag at all TMNOFLAGS m A single service ends with a call to the xa_end XA interface routine which passes either the TMSUCCESS or TMFAIL parameters but not TMSUSPEND This allows you to have any number of xa_end TMSUSPEND and xa_start TMRESUME calls in a program provided you delimit them with an xa_start and xa_end In addition you are guaranteed that any cursors or temporary tables associated with the single service can survive until you call xa_end with TMSUCCESS or TMFAIL to end the service 3 14 Informix Embedded SQL TP XA Programmer s Manual Programming Consider
50. e 1 18 you must add the appropriate message routines from the AP TM library to the server program The two types of local transactions are implicit and explicit Implicit Local Transactions The database server RM defines an implicit local transaction for each SQL statement that modifies the database but is not preceded by a BEGIN WORK statement and followed by a COMMIT WORK statement For example the following statement is an implicit local transaction INSERT INTO manufact VALUES BBS Big Boy Sports Programming in an X Open Environment 3 5 Identifying the Transaction Mode 3 6 In this case if this single SQL statement is successful the database server RM commits the transaction saving the new manufact row If this INSERT fails the database server RM rolls back the transaction and the new manufact row is not saved Explicit Local Transactions An explicit local transaction can be executed under the control of either of the following managers m The RM An explicit local transaction is an SQL statement or set of statements preceded by a BEGIN WORK statement and followed by a COMMIT WORK statement These SQL statements mark transaction bound aries For example the following SOL statements are considered to be a single explicit local transaction BEGIN WORK LOCK TABLE stock UPDATE stock SET unit_price unit_price 1 10 WHERE manu_code KAR DELETE FROM stock WHERE description baseball bat COMMI
51. e 1 4 In addition the DTP system must also support communication and cooperation among shared resources that are installed at different physical sites and are connected over a network Databases from different vendors or on different computers are called heterogeneous databases On line transaction processing OLTP applications are often run in a DTP environment For a definition of OLTP applications see your Administrator s Guide The following table lists features of a DIP system that are useful to OLTP applications DTP Feature OLTP Application Feature For More Information DTP client and server A large volume of well defined Client and Server Programs on programs application requests page 1 6 Small well defined interactions between user and database Transaction Emphasis on system response Local and Global Transactions management on page 1 7 Global transactions and Heavy database use of large shared Two Phase Commit Protocol two phase commit databases or other resources on page 1 10 e Informix and the X Open Distributed Transaction Processing Model 1 5 Features of a DTP System Client and Server Programs In the DTP model an application is divided into the following parts m A server program provides one or more services A service is a single function in the server program It performs one database task for the application m Aclient program handles the user interface It determines which
52. e X Qpen DTP Model 1 12 The X Open DTP Model The X Open DTP model is a DTP system that the X Open Company Limited specifies in the document Distributed TP The XA Specification This XA specification describes a uniform way to structure a DTP system Although other DTP systems provide the same set of features as this model they use proprietary methods and interfaces The X Open DTP model is an open model based on the XA specification When an application conforms to this model it can use global transactions that include multivendor database servers A TP XA library and the database server enable an application developer to build OLTP applications that conform to the X Open XA specification The X Open DTP model consists of the following parts m Application program The application program defines the boundaries of a transaction and specifies the actions that constitute a transaction m Resource Manager The Resource Manager RM provides access to a shared resource Usually an RM is a database server or file access system with one or more server programs that access the database server or file access system m Transaction Manager The Transaction Manager TM manages the routing and transaction processing control of service requests It manages global transac tions coordinating their resolution and any failure recovery Transaction manager software also lets you establish communication links among the client and serv
53. ent The database server could not find the transaction indicated by the xid argument The database server has marked this trans action as rollback only probably due to a long transaction 10 of 10 ABC DEF GH I JK L MNO PQRS TU VW X Y Z Index A ACID test properties 1 4 1 5 1 24 ANSI compliance icon Intro 10 level Intro 16 Application Program AP definition 1 12 1 13 interface to RM See AP to RM interface AP RM interface to TM See AP to TM interface AP TM sample client program 3 25 sample server program 3 24 3 29 using transaction modes 3 5 Application programming creating databases 2 7 creating temporary tables 3 15 designing programs for X Open environment 3 4 3 14 preparing for X Open environment 3 3 using cursors 3 14 using SQL statements 3 15 AP RM See AP to RM interface AP RM AP TM See AP to TM interface AP TM AP to RM interface AP RM definition 1 23 including 3 20 AP to TM interface AP TM definition 1 23 1 24 including 3 19 sample calls 3 24 uses of 3 4 3 7 AP See Application Program AP Atomicity ACID test property 1 4 BEGIN WORK statement 1 8 1 16 3 17 3 18 Boldface type Intro 7 c Client program benefits 1 7 communicating with server 1 18 definition 1 6 3 4 in an X Open AP 1 13 in DTP model 1 6 linking in AP TM 1 24 1 26 sample program 3 25 client c program 3 25 Client server architecture for DTP model 1 6 CLOSE DATABASE statemen
54. er m Displays the stock number description and unit price of the stock item m Calculates the new unit price for the stock item m Asks the user whether it should update the unit price Programming in an X Open Environment 3 21 A Non DTP ESQL C Program 3 22 Figure 3 3 shows the upstock ec program Figure 3 3 Sample ESQL C Program That Updates the Unit Price in the stock Table Ti upstock ec The following program fetches rows from the stock table for a chosen manufacturer and allows the user to selectively update the unit_price by a specified percent The program prompts the user for the manufacturer code and stock number and then the percent of the price increase a include lt stdio h gt include lt ctype h gt finclude lt decimal h gt EXEC SQL include sqltypes h fdefine LCASE c isalpha c isupper c tolower c c c char decdsply 20 char formatL amp 8 amp EXEC SQL BEGIN DECLARE SECTION short stock_num char description 16 dec_t unit_price EXEC SQL END DECLARE SECTION char errmsg 400 main EXEC SQL BEGIN DECLARE SECTION char manu_code 4 EXEC SQL END DECLARE SECTION char stockin 4 dec_t dprent float prent char ans EXEC SQL connect to stores7 open the stores7 database err_chk CONNECT TO stores7 EXEC SQL declare upcurs cursor for setup cursor for update select stock_num description unit_price from stock wh
55. er programs Each part is discussed in more detail in the following sections For more information on how these parts communicate see The Model Interfaces on page 1 23 Informix Embedded SQL TP XA Programmer s Manual The Application Program Figure 1 4 shows a conceptual version of the X Open DTP model Figure 1 4 The X Open DTP Application program Model 0 oN Resource gt Transaction Manager Manager XA Interface The Application Program In the X Open DTP model the application program contains the client program of the DTP client server structure see Client and Server Programs on page 1 6 The application program is written by the DIP application developer and performs the following tasks m Receives a user request and creates the appropriate service requests m Handles the user interface by accepting and displaying data for the user m Defines the global transactions by making the appropriate transaction management commands see The Transaction Manager on page 1 15 to mark the start and end of a transaction m Performs any local transactions through the native RM API interface to start and end the transaction as well as to execute the actual transaction operations For more information on how to build the application program see Chapter 3 Programming in an X Open Environment Informix and the X Open Distributed Transaction Processing Model 1 13 The Resource M
56. ere manu_code manu_code and stock_num stock_num for update of unit_price err_chk DECLARE upcurs Informix Embedded SQL TP XA Programmer s Manual A Non DTP ESQL C Program Accept user inputs Mfr code stock_num amp percent ys printf n tEnter Mfr code prompt for Mfr code gets manu_code enter Mfr code rupshift manu_code Mfr code to upper case printf n tEnter stock_num prompt for stock_num gets stockin enter stock_num stock_num atoi stockin convert to int printf n tEnter Percent whole number prompt for of increase scanf f amp prent enter of price increase prent 1 prent 100 0 convert to multiplier deccvdbl prent amp dprcnt convert to DECIMAL type EXEC SQL open upcurs open cursor err_chk OPEN upcurs Display column headings printf nStock tDescription tUnit Price EXEC SQL fetch upcurs into stock_num description unit_price if terr_chk FETCH upcurs printf n n t Row not found n exit if risnull CDECIMALTYPE char amp unit_price Skip if price NULL printf unit_price is NULL exit rfmtdec amp unit_price format decdsply Format unit_price Display item s stock_num description and unit_price Xf printf n t d t 15s t s stock_num description decdsply Calculate the new unit_
57. f information as the following table describes This information always appears in italics Icon Label Description Warning Identifies paragraphs that contain vital instructions j cautions or critical information Important Identifies paragraphs that contain significant information about the feature or operation that is being described Tip Identifies paragraphs that offer additional details or shortcuts for the functionality that is being described 8 Informix Embedded SQL TP XA Programmer s Manual Icon Conventions Feature Product and Platform Icons Feature product and platform icons identify paragraphs that contain feature specific product specific or platform specific information Icon AD XP Description Identifies information or syntax that is specific to Informix Dynamic Server with Advanced Decision Support and Extended Parallel Options a e Identifies information that relates to the Informix Global Language Support GLS feature Identifies information that is specific to Informix Dynamic Server and Informix Dynamic Server Workgroup Edition IDS UD Identifies information that is specific to Informix Dynamic Server with Universal Data Option IDS amp IDS UD Identifies information that is specific to Informix Dynamic Server and Informix Dynamic Server with Universal Data Option Identifies information that is specific to INFORMIX SE Identifies informatio
58. goal of a transaction processing system is to carry out this transaction in an efficient reliable and coordinated way The success of any transaction processing system is measured against four critical objectives Together these objectives are known as the ACID test m Atomicity Are all operations in a transaction performed on an all or nothing basis m Consistency If a transaction must be aborted is the data returned to its previous valid state m Isolation Are the results of a transaction invisible to other transactions until the transaction is committed m Durability Will the results of a transaction survive subsequent system failures Informix Embedded SQL TP XA Programmer s Manual Features of a DTP System In addition to these fundamental objectives a transaction processing system should have the following capabilities m Performance The transaction processing system must be able to handle a large number of users without a corresponding performance loss m Resiliency The transaction processing system must be able to recover in the event of a system or computer failure Features of a DTP System A distributed transaction processing DTP system is a form of transaction processing in which transactions are distributed among different computers or among databases from different vendors A DTP system must support all features of the general transaction processing system including the ACID test properties see pag
59. gram that performs the following tasks a Defines the services that the client application program needs that are supported by the associated DBMS a Communicates with the DBMS in support of a service through a native RM API The DTP application developer writes the server program in a language that supports the native RM API In the Informix implementation of an RM the server program is written in one of the following SQL APIs working through the TP XA library a INFORMIX ESQL C a INFORMIX ESQL COBOL INFORMIX ESQL COBOL is not available with Dynamic Server with UD Option The Transaction Manager The Transaction Manager TM performs the following transaction management tasks m Manages local and global transactions m Assigns XIDs m Routes and queues service requests from a client process to the appropriate server process m Acts as the coordinator in two phase commit and recovery The TM must know about all computers application programs clients services and RMs servers and DBMS systems on its network that are involved in global transactions This knowledge enables the TM to coordinate the activity among these entities Informix and the X Open Distributed Transaction Processing Model 1 15 The Transaction Manager 1 16 Managing Transactions In an X Open DTP environment a single transaction can span one or several RMs The TM can manage both types of transactions local one RM and global seve
60. grate the database server as part of an RM you must provide the TM with the information shown in Figure 2 1 Figure 2 1 Information That the TM Needs to Work with a Database Server RM Information TM Needs Information You Must Provide For More Information XA switch table name infx_xa_switch The Switch Table RM name Name of Informix database server For The RM Name on page 2 6 example Informix Dynamic Server XA routine library name libinfxxa a The XA Routine Library on page 2 6 Open string Name of database to open The Open and Close Strings on page 2 6 Close string null string a string in which the first character is null For information on how to provide this information to the TM refer to your TM documentation The Switch Table The XA interface defines a structure called the switch table which lists the names of the xa_ routines as they are implemented in the RM For more information on the xa_ routines see The XA Interface on page 1 24 In the XA interface this structure is called xa_switch_t and is defined in the xa h header file To be integrated into the X Open DTP model each RM must identify the name of its switch table so that the TM can find the names of the xa_ routines The name of the database server switch table is infx_xa_switch You must provide this switch table name to the TM so that it can locate the database server xa_ routines This switch table is defined in
61. ialize the close string to a null string For more information on how to use the xa_open and xa_close routines refer to Distributed Transaction Processing The XA Specification by X Open Company Limited February 1992 The XA Routine Library The XA routine library is the name of the library that contains the xa_ routines and the switch table that the database server RM defines This library is called libinfxxa a and is one of the libraries that the TM must link to the server process of an application program AP Informix Embedded SQL TP XA Programmer s Manual Monitoring Global Transactions Database Logging in an X Open DTP Environment Databases in the X Open DTP environment must use unbuffered logging Unbuffered logging ensures that the database server logical logs are always in a consistent state and can be synchronized with the TM If a database created with buffered logging is opened in an X Open DTP environment the database status automatically changes to unbuffered logging The database server supports ANSI compliant databases as well as databases that are not ANSI compliant Monitoring Global Transactions During execution of your AP you use the onstat utility to track the status of global transactions that the database server handles This utility is described in the Administrator s Guide The information in this section describes specific flag settings that indicate the status of XA related global transactions
62. ific information that appears in one or more paragraphs in a section Command Line Conventions This section defines and illustrates the format of commands that are available in Informix products These commands have their own conventions which might include alternative forms of a command required and optional parts of the command and so forth Each diagram displays the sequences of required and optional elements that are valid in a command A diagram begins at the upper left corner with a command It ends at the upper right corner with a vertical line Between these points you can trace any path that does not stop or back up Each path describes a valid form of the command You must supply a value for words that are in italics Informix Embedded SQL TP XA Programmer s Manual Command Line Conventions You might encounter one or more of the following elements on a command line path Element command Description This required element is usually the product name or other short word that invokes the product or calls the compiler or preprocessor script for a compiled Informix product It might appear alone or precede one or more options You must spell a command exactly as shown and use lowercase letters variable A word in italics represents a value that you must supply such as a database file or program name A table following the diagram explains the value flag A flag is usually an abbreviation for a f
63. ilation Programming in an X Open Environment 3 19 Building Servers for an X Open DTP Environment 2 Use the libs option to obtain the list of Informix API libraries that esq links to create an ESQL C program as the following example shows esql libs 3 Link the object file created in step 1 with the TP XA library called INFORMIXDIR lib esql libinfxxa as the following example shows cc 0 srvrprog srvrprog o INFORMIXDIR 1ib esql libinfxxa a LIBS In the preceding cc command LIBS represents the other ESQL C libraries required to create an executable ESQL C program You obtained this list of libraries in step 2 On some computers you can preprocess the server program and link it in one step as the following example shows esql srvrprog ec o srvrprog INFORMIXDIR 1ib esql libinfxxa a Important If you are using ESQL C Version 5 0 or earlier you must link the object EA file ixacursor o in INFORMIXDIR lib esql before the libinfxxa a TP XA library The libraries linked with the server program provide the following interfaces for the server m The INFORMIXDIR lib esql libinfxxa a library provides the server program with the Informix implementation of the XA interface m The LIBS libraries provide the server program with the native AP RM interface so that the server can communicate with the database server RM 3 20 Informix Embedded SQL TP XA Programmer s Manual Sample ESQL C Programs Sample E
64. k_des 100 float u_percent char u_errmsg 100 decimal u_unit_price End of OLTP header files define LCASE c isalpha c isupper c tolower c c c char decdsply 20 char formatL 8 amp decimal unit_price char description 16 char errmsg 400 3 26 Informix Embedded SQL TP XA Programmer s Manual A Sample DTP ESQL C Application Program main char manu_codeL4 char stockin 4 float prent char ans struct app_buffer audv Pointer points to message buffer int audv_len Pseudocode to join application if OLTPINIT 1 fprintf stderr Failure to join application n exit 1 End of pseudocode that joins application Pseudocode that allocates message buffer audv struct app_buffer OLTPALLOCATE TYPE app_buffer sizeof struct audv End of pseudocode that allocates message buffer Prepare and initialize structure void strcpy audv gt u_manu_code audv gt u_stock_code 0 void strcpy audv gt u_stock_des 0 audv gt u_percent 0 void strcpy audv gt u_errmsg 0 End of Prepare and initialize structure system clear Clean up the screen printf n tEnter Mfr code prompt for manu_code gets manu_code get manu_code rupshift manu_code change manu_code to uppercase printf n tEnter Stock number prompt for stock_num gets stockin
65. l Features of a DTP System The following list shows additional benefits of dividing an application into client and server programs When one of the client or server programs fails it does not affect any other client or server processes Server programs can be located on the same computer as the associated database server providing centralized access for client programs Server programs reduce redundant storage of service related coding at the user site Client programs can reside at the user site and can be tailored to the needs of the user Modularity allows extension and reorganization of the client and server programs without rewriting existing code These benefits allow an OLTP application to support a large volume of well defined application requests and to provide small well defined interactions between the user and the database Local and Global Transactions The DTP model supports the following types of transactions A local transaction involves only one service in a single server program and it accesses only one database A global transaction involves several services that might be located in different server programs perhaps on different computers A global transaction is also called a distributed transaction The DTP software must be able to handle both types of transactions in support of the application Informix and the X Open Distributed Transaction Processing Model 1 7 Features of a DTP System
66. l the service ends or aSET EXPLAIN OFF statement is issued The sqexplain out file is stored in the current directory where the database server is running 1 of 2 Informix Embedded SQL TP XA Programmer s Manual Statement SET LOCK MODE SET LOG UNLOCK TABLE BEGIN WORK COMMIT WORK ROLLBACK WORK Programming Considerations for Server Programs Special Behavior The current lock mode and time out period remain constant throughout a service unless it is modified in the service If you change the lock mode the change remains in effect until the next modification or until the service ends The SET LOCK MODE statement takes effect only when the time out period is shorter than the period that the TM software specifies You must create databases in an X Open DTP environment with unbuffered logging If you create a database with buffered logging the database status automatically changes to unbuffered logging when the database is opened in an X Open DTP environment If you issue a SET LOG statement in an X Open DTP environment you receive an error If you issue an UNLOCK TABLE statement from any trans action branch you receive an error If you issue a BEGIN WORK statement in a global trans action you receive an error If you issue a COMMIT WORK statement in a global trans action you receive an error If you issue a ROLLBACK WORK statement in a global transaction you receive an error 2 of 2 Programmi
67. logical log records and heuristic decisions see your Administrator s Guide Taking Actions to Handle Database Inconsistency If you find that your Informix database is in an inconsistent state you have the following options m Leave the database in its inconsistent state m Recover from the inconsistent state As you consider your options remember that no automatic process or utility can perform a rollback of a committed transaction or can commit part of a transaction that is rolled back Although the database server logical log records can show the affected transaction you cannot determine what work transpired from the messages alone It is your responsibility based on your knowledge of your application and global system to determine which option to take Informix Embedded SQL TP XA Programmer s Manual The Database Server and Heuristic Decisions You might consider the TM and the other RMs involved in this particular global transaction to make this decision Recovery in an X Open DTP system not only requires knowledge of the database server RM but also knowledge of how each independent software component is designed to recover from a database inconsistency Leaving the Database in an Inconsistent State You might decide to leave the database in its inconsistent state if the transaction does not significantly affect database data This situation occurs if you decide that the application can remain inconsistent because the price
68. m Preparing to program in an X Open DTP environment m Developing client and server programs in an X Open DTP environment This chapter assumes that you are familiar with the information in the Informix Guide to SQL Syntax and one of the following SQL API manuals m INFORMIX ESQL C Programmer s Manual m INFORMIX ESQL COBOL Programmer s Manual INFORMIX ESQL COBOL is not available with Dynamic Server with UD Option Preparing to Program in an X Open DTP Environment Application programming in an X Open DTP environment is not too different from application programming in any other environment Many of the Informix embedded language statements that you use in programs designed for a non X Open DTP environment remain unchanged In an X Open DTP environment however you must make some programming adjustments Programming in an X Open Environment 3 3 Designing Programs for an X Open DTP Environment 3 4 Designing Programs for an X Open DTP Environment When you design programs for an X Open DTP environment you must create an application that includes client programs services and server programs m lt Aclient program takes user input and sends it in the form of a service request to a server program The client program accesses the services that a server offers Most importantly the client program interfaces with the user For example a client program might request data from the user as inputs to a program or it might ret
69. mail our address is doc informix com The doc alias is reserved exclusively for reporting errors and omissions in our documentation We appreciate your suggestions Introduction 17 Chapter Informix and the X Open Distributed Transaction Processing Model Distributed Transaction Processing Transaction Processing Features of aDTP System Client and Server Programs Local and Global Transactions Two Phase Commit Protocol The X Open DTP Model The Application Program The Resource Manager The Transaction Manager Managing Transactions care Assigning Transaction Identifiers Managing Client Server Communication Controlling the Two Phase Commit The Model Interfaces The AP to RM Interface The AP to TM Interface The XA Interface Software Products and the X Open DTP Model Third Party Transaction Manager Software Informix Software for the Resource Manager The Informix Database Server as a Resource Manager The TP XA Library as the Server XA Interface What TP XA Can Do for You ly tz Me ay 1 4 1 5 1 6 1 7 1 10 1 11 1 13 1 14 1 15 1 16 1 17 1 18 1 20 1 23 1 23 1 24 1 24 1 25 1 26 1 26 1 28 1 28 1 29 1 2 Informix Embedded SQL TP XA Programmer s Manual AD XP In This Chapter Several distinct models for transaction processing have emerged in the evolution of relational databases Each model attempts to meet the changing needs of the business communi
70. mer s Manual Features of a DTP System To handle this information the DTP model requires transaction management software as Figure 1 3 shows Figure 1 3 Routing a Global Transaction Application program Database A custno custname 7234 XYZLID Server 1235 XSPORTS if tion Tesi Service 1 management Database B i custno custname Client st ZIT 1235 XSPORTS l Server II Database C l Servi ce requests Servi ce 2 Database custno custname Begin global transaction Service 3 management 7934 xyz LTD Call service 1 Database A system 1235 XSPORTS Call service 2 Database C Commit global transaction Figure 1 3 shows a general DTP model For the X Open DTP model see Figure 1 8 on page 1 27 To mark the start and end of a global transaction an application program must use special transaction demarcation commands known to the transaction management software These commands replace the database server transaction commands such as the SQL statements BEGIN WORK COMMIT WORK and ROLLBACK WORK For information on global transac tions in the X Open DTP environment see page 1 16 Transactions allow an OLTP application to increase the system response by efficiently grouping database operations Informix and the X Open Distributed Transaction Processing Model 1 9 Features of a DTP System Two Phase Commit Protocol The DTP system must support two phase commi
71. n How do you determine whether an Informix database contains inconsistent data m What actions do you need to perform to bring an Informix database back to a consistent state Integrating the Database Server and TP XA into the X Open DTP Model 2 13 The Database Server and Heuristic Decisions 2 14 Causing the Database Server to Make a Heuristic Decision A likely cause of a heuristic decision by a database server RM is a long transaction LTX An LTX occurs when the logical log fills to the point defined by one of the long transaction high water marks configuration file parameters LTXHWM or LTXEHWM The source of an LTX condition is work being performed for a global transaction For more information about long transactions see your Administrator s Guide Determining Consistency of an Informix Database If database inconsistency is possible because of a heuristic decision by a database server RM check the logical logs for the following combination of logical log records m The XAPREPARE logical log record seen only in an X Open DTP environment indicates the ability of the database server RM to commit the transaction branch when the TM instructs it to do so m The HEURTX logical log record indicates a heuristic decision that the database server RM made to abort its transaction branch m The ROLLBACK logical log record indicates that the database server RM aborted its transaction branch For more information about
72. n The client program creates a service request for Service 2 The client program sends the service request to the TM The TM routes the service request to the server program of the appro priate RM In this case the TM routes the request to the RM that contains Server II where Service 2 is located The server program Server II locates the Service 2 service and executes it In the course of this execution the Service 2 program accesses Database C through the associated DBMS The Server II server program sends the TM a confirmation that the service has executed It then sends to the TM any data that the DBMS returned The TM routes the service confirmation to the appropriate client program the application program If the service has returned any data the TM also routes this data to this client The client program the application program displays any data that is appropriate for the user Informix and the X Open Distributed Transaction Processing Mode 1 19 The Transaction Manager To handle these service requests the TM defines and manages a request queue which stores information about the state of a transaction The request queue shown in Figure 1 7 is a piece of shared memory that the TM defines and manages Figure 1 7 The TM Request Queue Database custno custname 1234 XYZLTD 1235 XSPORTS Request queue Important Because no specification governs the design and implementation of TMs yo
73. n UNIX platforms the following on line files appear in the INFORMIXDIR release en_us 0333 directory On Line File Purpose XADOC_9 2 The documentation notes file for your version of this manual describes topics that are not covered in the manual or that were modified since publication CLIENTS_2 2 The release notes file describes feature differences from earlier versions of Informix products and how these differences might affect current products This file also contains information about any known problems and their workarounds The release notes file for Client SDK includes information about database server compatibility ESQLC_9 2 The machine notes file describes any special actions that you must take to configure and use Informix products on your computer Machine notes are named for the product described Introduction 15 Related Reading The following items appear in the Informix folder To display this folder choose Start gt Programs Informix from the Task Bar Program Group Item Description Documentation Notes This item includes additions or corrections to manuals along with information about features that might not be covered in the manuals or that have been modified since publication Release Notes This item describes feature differences from earlier versions of Informix products and how these differences might affect current products This file also contains information about any known problems and their w
74. n XA interface m xa_open m is_xaopened m get_rmid The following sections describe these functions Programming in an X Open Environment 3 7 Xa_open xa_open The Informix extensions to the xa_open function allow you to connect to the database server and open a database in a single step Syntax Xa_open connect_str char connect_strl connect_str is a string enclosed in quote marks that can take one of the following three formats DB dbname RM serverl1 CON conl USER usr1 PASSWD passwd DB dbname serverl dbname serverl The RM CON USER and PASSWD fields are optional The dbname variable is the name of the database that xa_open is to open The server1 variable is the name of the database server to which xa_open is to connect The con1 variable is the name to assign to the connection For more information refer to the CONNECT statement in Informix Guide to SQL Syntax The usr1 variable is the name of the user making the connection The passwd variable is the password for the user making the connection usr1 3 8 Informix Embedded SQL TP XA Programmer s Manual Xa_open Usage The xa_open function connects to the specified database server and opens the specified database If RM is not specified the program establishes a connection with the database server that the INFORMIXSERVER environment variable specifies If the xa_open does not specify a connection
75. n that is specific to UNIX platforms Identifies information that is specific to Windows NT Windows 95 and Windows 98 environments WIN NT Identifies information that is specific to the Windows NT environment WIN NT 95 Identifies information that is specific to Windows NT and Windows 95 environments These icons can apply to an entire section or to one or more paragraphs in a section If an icon appears next to a section heading the information that applies to the indicated feature product or platform ends at the next heading at the same or higher level A symbol indicates the end of feature product or platform specific information that appears in one or more paragraphs in a section Introduction 9 Command Line Conventions 10 Compliance Icons Compliance icons indicate paragraphs that provide guidelines for complying with a standard Icon Description Identifies information that is specific to an ANSI compliant database XO Identifies functionality that conforms to X Open Identifies information that is an Informix extension to ANSI SQL 92 entry level standard SQL These icons can apply to an entire section or to one or more paragraphs in a section If an icon appears next to a section heading the information that applies to the indicated feature product or platform ends at the next heading at the same or higher level A symbol indicates the end of feature product or platform spec
76. name the database server assigns the name Xacon to the connection Ina multithreaded environment you can establish one connection per thread If xa_open does not specify a connection name the database server assigns the connection name Xacon plus the thread ID If the thread ID is 1 for example the connection name would be Xacon1 When xa_open has already been invoked a call to xa_open in an XA session is ignored Return Values For information on the values that xa_open can return see Appendix A XA Routine Return Codes Programming in an X Open Environment 3 9 is_xaopened is_xaopened The is_xaopened function indicates whether xa_open was called Syntax int is_xaopened Usage The is_xaopened function returns a value of 1 if xa_open was called Otherwise it returns 0 Return Values 0 xa_open was not called 1 xa_open was called 3 10 Informix Embedded SQL TP XA Programmer s Manual get_rmid get_rmid The get_rmid function returns the RM ID if xa_open was called Syntax int get_rmid Usage If the xa_open function was called get_rmid returns the RM ID Otherwise it returns the value FUNCFAIL Return Values RM ID The RM ID if xa_open was called FUNCFAIL If xa_open was not called For the value of FUNCFAIL see Distributed Transaction Processing The XA Specification by X Open Company Limited February 1992 Programming in an X
77. nction for a global transaction branch when this routine was called The rmid argument is not the same as that passed to xa_open The database server failed An internal communication error occurred between the application development tool and the database server The database server encountered failure on close of database The database server encountered an error while implementing asynchronous opera tions when the TMASYNC flag was passed in the flags argument Invalid arguments were specified for this routine This routine was called before a call to xa_open The transaction was active or suspended The rmid argument is not the same as that passed to xa_open The TMONEPHASE flag was passed in the flags argument and the transaction was already prepared for commit 1 of 10 XA Routine Return Codes A 3 XA Function Return Code Reason for Return Code The TMONEPHASE flag was not passed in the flags argument and the transaction has not yet been prepared for commit XAER_RMFAIL The database server failed XAER_ASYNC The database server encountered an error while implementing asynchronous opera tions when the TMASYNC flag was passed in the flags argument XAER_RMERR An internal communication error occurred between the application development tool and the database server The database server could not find deferred constraint information probably because of memory corruption The database ser
78. ned is greater than or equal to 0 Invalid arguments were specified for this routine This routine was called before a call to xa_open The rmid argument is not the same as that passed to xa_open The database server failed The database server encountered an error while implementing asynchronous opera tions when the TMASYNC flag was passed in the flags argument An internal communication error occurred between the application development tool and the database server The database server could not find deferred constraint information probably because of memory corruption The database server encountered an error while freeing the shared memory trans action entry The database server failed to roll back the transaction Execution was between an xa_start function and an xa_end function on another transaction when this call occurred The database server was unable to save the state of the current transaction to perform the rollback The database server could not find the transaction indicated by the xid argument 8 of 10 XA Function xa_start Return Code XA_HEURRB XAER_INVAL XAER_PROTO XAER_RMFAIL XAER_ASYNC XAER_RMERR Reason for Return Code Because of a heuristic decision the database server has already rolled back the transaction Invalid arguments were specified for this routine This routine was called before a call to xa_open TMRESUME was passed in the fl
79. ng in an X Open Environment 3 17 Programming Considerations for Server Programs 3 18 Statement Local Transaction Figure 3 2 summarizes the return behavior of the statements listed in Figure 3 1 on page 3 16 from an X Open DTP global or local transaction Figure 3 2 Return Behavior of SQL Statements in Local and Global Transactions In a Global Transaction In a Local Transaction BEGIN WORK CLOSE DATABASE COMMIT WORK CREATE DATABASE DATABASE LOCK TABLE ROLLBACK WORK SET EXPLAIN SET ISOLATION Returns an error Returns an error Returns an error Returns an error Returns an error Statement remains in effect until the end of the global transaction All locks are released at the end of the transaction Returns an error The file sqexplain out is stored in the current directory on the computer on which the server is running If you change the isolation level in a service the changes remain in effect until the next change or until the service ends Informix Embedded SQL TP XA Programmer s Manual Executes Returns an error Executes Returns an error Returns an error All table locks are released at the end of the global transaction Executes The file sqexplain out is stored in your home directory If you change the isolation level the change remains in effect until the next change or until the program ends 1 of 2 Building Servers for an X Open DT
80. nicate with the TM The TM uses these XA routines to communicate global transaction information to the server program which in turn communicates the information to the database server Informix Embedded SQL TP XA Programmer s Manual What TP XA Can Do for You Through the TP XA library a server program and the database server can act as an RM with any TM that conforms to X Open XA specifications The TP XA library fully supports the required XA interface However it does not support the following optional XA interface features m Asynchronous operations m Dynamic registration m Transaction association migration For information on these optional features refer to Distributed Transaction Processing The XA Specification by X Open Company Limited February 1992 What TP XA Can Do for You Using the TP XA library the Informix SQL API product third party TM software and your Informix database server in your OLTP applications provides the following advantages m Distributed heterogeneous transactions The TM software supervises global transactions that update databases on multiple systems including databases from different vendors as long as they support the XA specification m Tunable response times With a TM you can balance the workload among servers With the database server you can tune the locking data buffers and other performance factors in the database system For more information on tuning the database
81. nsaction identifier GTRID definition 1 17 GTRID See Global Transaction Identifier GTRID H Heuristic decision definition 1 22 determining database inconsistency 2 14 handled by RM 1 22 2 13 handled by the database server 2 14 handled by TM 1 22 HEURTX logical log record 2 14 2 15 A BC D E F GH Icons compliance Intro 10 feature Intro 9 Important Intro 8 platform Intro 9 product Intro 9 Tip Intro 8 Warning Intro 8 Implicit local transaction 3 5 Important paragraphs icon for Intro 8 Industry standards compliance with Intro 16 Informix database server aborting a transaction 2 13 as resource manager 1 14 close string 2 6 configuration parameters 2 14 database creation requirements 2 7 in two phase commit 2 11 integrating with transaction manager 2 4 long transaction 2 14 making a heuristic decision 2 13 2 14 open string 2 6 role in two phase commit 2 10 threads 2 7 Informix DBMS as resource manager 1 29 responding to XA requests 1 28 tracking XIDs 1 28 INFORMIXDIR bin directory Intro 5 Informix TP XA library benefits of using 1 29 building OLTP applications 1 11 in a server program 1 15 1 26 naming 2 6 routine names 1 24 supporting XA interface 1 24 1 28 when needed 1 26 Interface AP RM 1 23 AP RM See AP to RM interface AP RM AP TM See AP to TM interface AP TM definition 1 23 in X Open DTP model 1 23 XA See XA interface ISO 8859 1 code set Intro 4 Isolation ACID test property
82. o that it can send database requests directly to the RM The native RM API is also part of the database server so it can send and receive requests from the application program The server program uses the native RM API when it sends SQL statements to the database server This protocol is independent of the underlying transport or network protocol The preprocessor for Informix SQL API products automatically links the appropriate API libraries into the program Informix and the X Open Distributed Transaction Processing Model 1 23 The Model Interfaces 1 24 The AP to TM Interface The TM communicates with both clients and servers through the AP TM interface see Figure 1 4 on page 1 13 The TM provides these AP TM routines in the form of a library that is linked to both client and server programs of the application The AP TM library supports assignment and prioritization of client service requests It also manages transactions and buffers used for communication in global transactions Ideally the proprietary AP TM interface should adhere to a single specifi cation as TP XA adheres to the X Open XA specification But currently the AP TM for each TM vendor is unique Therefore to program in an X Open environment you need to embed the AP TM calls specific to your vendor s TM in your application The XA Interface The XA interface handles the communication between the RM and TM The interface is a standard library of routines that
83. on program AP 1 12 1 13 behavior of SQL statements in 3 16 building servers 3 19 creating a client 3 4 creating a server 3 4 defining a service 3 4 definition 1 11 global transaction 1 16 2 7 3 6 interfaces 1 23 local transaction 1 16 3 5 resource manager RM 1 12 1 14 software required for 2 3 transaction manager TM 1 12 1 15 1 18 two phase commit protocol 1 20 1 24 using cursors 3 14 using SQL statements in 3 15 with Informix DBMS 1 14 XA interface See XA interface standard Index 5
84. open This transaction was not heuristically completed XAER_RMEFAIL The database server failed XAER_ASYNC The database server encountered an error while implementing asynchronous opera tions when the TMASYNC flag was passed in the flags argument 4 of 10 A 6 Informix Embedded SQL TP XA Programmer s Manual XA Function xa_open Return Code XAER_RMERR XAER_NOTA XAER_INVAL XAER_ASYNC XAER_RMERR Reason for Return Code An internal communication error occurred between the application development tool and the database server The database server encountered a failure on write of end transaction record The database server could not find the transaction indicated by the xid argument Invalid arguments were specified for this routine The xa_info argument is a null pointer The xa_info string is too long greater than MAXINFOSIZE No database name was given or database name is too long Anon XA connection to the database server already exists Database does not exist Database does not have logging The database server encountered an error while implementing asynchronous opera tions when the TMASYNC flag was passed in the flags argument This routine was unable to connect to the database server An internal communication error occurred between the application development tool and the database server The database server was unable to open the database for a reason other
85. orkarounds The release notes file for Client SDK includes information about database server compatibility Machine notes do not apply to Windows environments Related Reading For information on the X Open XA specification consult Distributed Transaction Processing The XA Specification by X Open Company Limited February 1992 Compliance with Industry Standards The American National Standards Institute ANSI has established a set of industry standards for SQL Informix SQL based products are fully compliant with SQL 92 Entry Level published as ANSI X3 135 1992 which is identical to ISO 9075 1992 In addition many features of Informix database servers comply with the SQL 92 Intermediate and Full Level and X Open SQL CAE common applications environment standards 16 Informix Embedded SQL TP XA Programmer s Manual Informix Welcomes Your Comments Informix Welcomes Your Comments Let us know what you like or dislike about our manuals To help us with future versions of our manuals we want to know about any corrections or clarifications that you would find useful Include the following information m The name and version of the manual that you are using m Any comments that you have about the manual m Your name address and phone number Write to us at the following address Informix Software Inc SCT Technical Publications Department 4100 Bohannon Drive Menlo Park CA 94025 If you prefer to send electronic
86. our global system might be in an inconsistent state For general information on heuristic decisions see page 1 22 A heuristic decision does not in itself create a problem for the two phase commit protocol The decision by the database server to roll back a transaction branch becomes a problem only when both of the following conditions are true m The participating database server RM makes the heuristic decision to roll back its transaction after the TM receives notification that this RM can commit its transaction m The TM decides to commit the global transaction and instructs all participating RMs to commit their transactions If a database server RM participating in a global transaction heuristically rolls back a transaction and both of these conditions are true the global transaction processing system is in an inconsistent state That is some participating RMs committed their transactions while at least one database server RM aborted its transaction In this case the TM error messages indicate that the transaction state is unknown or mixed partially committed and partially aborted The system administrator must decide what actions should be taken to return the system to a consistent state The administrator must first determine which RM made a heuristic decision If this RM uses a database server the administrator needs to know the answers to the following questions m Why would a database server RM make a heuristic decisio
87. paces acquired locks and other resources required by the database server RM that works for a global transaction When the application sends a commit message to the TM it initiates the following actions in the two phase commit protocol The precommit phase begins after all inserts updates and deletes included in the global transaction are executed During this phase the TM calls the xa_prepare XA interface routine to ask the database server RM to prepare to commit its transaction a Ifthe database server determines that it can commit the trans action it records this information in a logical log record XAPREPARE log record and notifies the TM that the commit succeeded o Ifthe database server cannot commit the transaction it aborts the transaction records this information in a logical log record ROLLBACK log record and notifies the TM that the commit failed During the postdecision phase the TM commits or aborts the global transaction based on the RM responses from the precommit phase phase one a If all replies are affirmative the TM calls the xa_commit XA interface routine for each RM to ask each RM to commit its branch of the global transaction When this routine is called the database server RM writes a COMMIT log record to a logical log a If at least one negative reply exists the TM aborts the transaction and calls the xa_rollback XA interface routine to abort its trans action When this routine is called
88. price decmul amp unit_price amp dprcnt amp unit_price rfmtdec amp unit_price format decdsply format for display ans ST 7g Update unit_price y es or n o dr te LCASE ans y amp amp ans n Peierls Update unit_price to s y n decdsply scanf 1s amp ans ieee Ty if yes update current row Programming in an X Open Environment 3 23 A Sample DTP ESQL C Application Program 3 24 EXEC SQL update stock set unit_price unit_price where current of upcurs err_chk UPDATE stock err_chk checks sqlca sqlcode and if an error has occurred it uses rgetmsg to display the message for the error number in sqlca sqlcode err_chk name char name if sqlca sqlcode lt 0 rgetmsg short sqlca sqlcode errmsg sizeof errmsg printf n tError d during s s n sqlca sqlcode name errmsg exit 1 1 J return sqlca sqlcode SQLNOTFOUND 0 1 A Sample DTP ESQL C Application Program The upstock ec program as Figure 3 3 on page 3 22 shows performs the client and server tasks It interacts with the user taking the data that the user provides and performs an update on a table in a database To perform the same actions in an X Open DTP environment you need to divide the upstock ec program into the following two programs m The client program handles the user interface m The server program handles the database interfa
89. ral RMs For a general description of local and global transactions see page 1 7 Local Transactions A local transaction involves one service in a single RM A local transaction can occur in one of the following ways m Under the control of the TM The application program uses special transaction management calls provided as part of the AP TM interface to begin and commit the work in the local transaction For more information see The AP to TM Interface on page 1 24 m Under the control of the RM The application program uses the appropriate calls in the AP RM the native RM API to start and end the local transaction When this API is SQL the application program starts the transaction with a BEGIN WORK statement and ends the transaction with a COMMIT WORK statement or it uses single statement transactions implicit transac tions For more information see The AP to RM Interface on page 1 23 Global Transactions In the X Open DTP environment many RMs can operate in support of the same global transaction For example an application program can require updates to several databases in several RMs ina single global transaction The commitment of work in one transaction branch can be contingent on trans action branches that occur at other RMs RMs are typically unaware of the work that other RMs perform A global transaction includes more than one RM Informix Embedded SQL TP XA Programmer s Manual The Transaction
90. ransaction Manager In the case of a global transaction the TM assigns the same GTRID to all trans action branches associated with that transaction The TM informs each participating RM of the existence commitment or abortion of the global transaction The TM sends the GTRID to the RM so that the RM knows to which global transaction its transaction branch belongs The RM might in turn translate this GTRID to its own internal XID while it works on the transaction branch Managing Client Server Communication As discussed on page 1 6 a DTP application consists of a client program and at least one server program which communicate by service requests To coordinate the communication of the service requests an X Open DTP appli cation can use the TM Figure 1 6 shows how the TM manages the routing of service requests Figure 1 6 TM Service Request Routing User request Service request SS cit system Service data Service Service request Confirmation Call service 2 Q Service Database C confirmation custno custname Service 2 Database Da XZD Service 3 server 1235 XSPORTS Service 2 executes accessing Database C 1 18 Informix Embedded SQL TP XA Programmer s Manual The Transaction Manager As Figure 1 6 on page 1 18 shows the TM routes a service request in the following steps Aa wo N The user enters information needed for the transactio
91. rrmsg exit 1 return sqlca sqlcode SQLNOTFOUND 0 1 Programming in an X Open Environment 3 33 XA Routine Return Codes This appendix contains a list of the XA functions and their return codes The XA functions make up the XA interface which allows the TM and the RM to communicate information about global transactions The X Open DTP interface defines these functions to begin with the string xa_ These routines are only templates The actual function names are internal to the RM They are defined in the XA library For more information on the XA interface see Chapter 1 Informix and the X Open Distributed Transaction Processing Model Important This appendix assumes that you are familiar with the Informix Guide to SQL Syntax the INFORMIX ESQL C Programmer s Manual and Distributed Transaction Processing The XA Specification by X Open Company Limited A 2 The following table shows the xa_ routines listed in this appendix XA Function Purpose xa_close Terminates use of an RM by an AP xa_commit Tells the RM to commit a transaction branch xa_complete Tests for completion of an xa_ operation xa_end Dissociates a thread from a transaction branch xa_forget Tells the RM to discard its knowledge of a heuristically completed transaction branch xa_open Initializes an RM for an AP to use xa_prepare Asks the RM to prepare to commit a transaction branch xa_recover Obtains
92. s and telephone number On Line Help Informix provides on line help with each graphical user interface GUI that displays information about those interfaces and the functions that they perform Use the help facilities that each GUI provides to display the on line help Error Message Documentation Informix software products provide ASCII files that contain all of the Informix error messages and their corrective actions To read error messages and corrective actions on UNIX use one of the following utilities Utility Description finderr Displays error messages on line rofferr Formats error messages for printing 14 Informix Embedded SQL TP XA Programmer s Manual Documentation Notes Release Notes Machine Notes To read error messages and corrective actions in Windows environments use the Informix Find Error utility To display this utility choose Start gt Programs Informix from the Task Bar Instructions for using the preceding utilities are available in Answers OnLine Answers OnLine also provides a listing of error messages and corrective actions in HTML format Documentation Notes Release Notes Machine Notes In addition to printed documentation the following sections describe the on line files that supplement the information in this manual Please examine these files before you begin using your database server and client products They contain vital information about application and performance issues O
93. servers from other DBMS vendors heterogeneous distributed transactions Informix database servers support distributed transactions when all the database servers are Informix database servers The X Open model for DTP The native DTP of the database server does not follow the X Open DTP model For more information on the DTP that is native to the database server see your Administrator s Guide To handle distributed transactions in either of these cases you can use the following software products Third party TM software for managing heterogeneous global transactions Informix software for creating an RM that handles the service requests based on data managed by a database server Informix and the X Open Distributed Transaction Processing Model 1 25 Third Party Transaction Manager Software a W Third Party Transaction Manager Software The TM software supervises global transactions that update databases on multiple systems including databases from different vendors as long as they support the XA specification To be used in an X Open DTP environment the third party TM software product must provide the following features Software to provide TM functionality as described in The Transaction Manager on page 1 15 This software must also include support for the XA routines needed to support the TM side of the XA interface An AP TM library to be linked into both the client application program and the server program
94. ss the same transaction but not simultaneously If a request is made for a service for a global transaction but the transaction is busy in another service the first request must finish and detach from the transaction In the meantime the second thread blocks waiting for the transaction This situation could occur if two different services both using the same database server and database tried to work on the same global transaction simultaneously 3 X Transaction is XA prepared the database server is prepared to commit or is currently in the process of doing so If a transaction is associated with a thread the state of the transaction is shown by the flags column in the Userthreads section For more information on the u option of onstat refer to your Administrator s Guide 2 8 Informix Embedded SQL TP XA Programmer s Manual The Transactions Section The Transactions Section To generate the Transactions section output use the x option of the onstat utility This option produces information specific to the X Open environment The output describes the state of the transaction Figure 2 3 shows the headers of output information that appear when you use the x option of onstat Figure 2 3 RSAM Version 9 10 UC1 On Line Up 00 06 16 528 Kbytes onstat x Output Transactions address flags user locks log begin isolation retrys coordinator The flags column in the Transactions section refers to the status of a trans
95. stock ec program For example the SQL statements that access the database become the following services in the server program The QUERYSTO service selects the data in the stock table for the specified manufacturer code and stock number sent in the message buffer The UPDATESTO service performs an update of the unit_price column of the stock table As Figure 3 5 on page 3 30 shows server ec contains the services QUERYSTO and UPDATESTO To retrieve a row from the stock table the QUERYSTO service performs the following tasks 1 It calls the OLTPGETBUF function to obtain the specific manufac turer and stock number of the stock row to select These values are sent into the function in the transb message buffer It performs an embedded SELECT statement that retrieves the stock number description and unit price for the specified row It uses the ESQL C risnull function to verify that the retrieved unit_price value is not null It stores the retrieved values in the message buffer and then calls the OLTPRETURN function to send back to the client the data for the selected stock row To update the row the UPDATESTO service performs the following tasks 1 It calls the OLTPGETBUF function to obtain the original stock row information from the message buffer It uses an update cursor to reselect the row for update locking the row It compares the contents of the row with the original that was passed back to the clien
96. t 3 16 3 18 Close string 2 5 2 6 Code sample conventions for Intro 13 Command line conventions elements of Intro 11 example diagram Intro 12 how to read Intro 12 Comment icons Intro 8 COMMIT logical log record 2 11 COMMIT WORK statement 1 8 A BC D E F GH Compliance icons Intro 10 with industry standards Intro 16 Configuration parameter LTXEHWM 2 14 LTXHWM 2 14 CONNECT statement 3 16 Consistency ACID test property 1 4 Contact information Intro 17 Conventions documentation Intro 6 Coordinator 1 10 1 15 1 20 CREATE DATABASE statement 3 16 3 18 Cursors database in an X Open DTP environment 3 14 D Database ANSI compliant 2 7 as ashared resource 1 14 creation requirements under X Open 2 7 determining if inconsistent 2 14 heterogeneous 1 5 Database Management System DBMS 1 8 1 14 Database server client behavior on Intro 4 DATABASE statement 3 16 3 18 DB Access utility Intro 5 Default locale Intro 4 Demonstration databases Intro 5 Dependencies software Intro 4 DISCONNECT statement 3 16 Distributed transaction processing DTP See also Transaction processing client program 1 6 definition 1 4 1 5 features of 1 5 global transaction 1 7 local transaction 1 7 server program 1 6 two phase commit protocol 1 10 JK L MN OP QRS TU VW X Y Z X Open model See X Open DTP model Distributed transaction See Global transaction Documentation notes location of Intro 15 program item Intro 16 Do
97. t during QUERYSTO If changes were made to that row the update is disallowed and an error is returned to the client Programming in an X Open Environment 3 29 A Sample DTP ESQL C Application Program 3 30 4 Ifno changes were made UPDATESTO updates the unit price for the row by the user specified percentage 5 Finally UPDATESTO uses the OLTPRETURN function to return the update status to the client program Important In this example the OLTPGETBUF and OLTPRETURN functions are pseudo functions In an actual application use the appropriate AP TM calls of your TM interface to perform these tasks Figure 3 5 shows the sample server program server ec Figure 3 5 Sample Pseudocode for Server Program server ec The server program gets the request from the client program and performs the QUERYSTO and UPDATESTO services QUERYSTO Fetches rows from the stock table for a chosen manufacturer and stock_num UPDATESTO Updates the record with the percent of the price increase ff include lt stdio h gt include lt ctype h gt include lt decimal h gt EXEC SQL include sqltypes h Pseudocode to include any OLTP header files that are needed finclude lt OLTPXA h gt include upstock h struct app_buffer char u_manu_code 4 int u_stock_code char u_stock_des 100 float u_percent char u_errmsg 100 decimal u_unit_price J gt End of OLTP header files EXEC SQL BEGIN DECLARE SEC
98. t for display void printf d Zs s n stock_num description decdsply Prompt user to update the item printf nDo you want to update this record n ans Update unit_price y es or n o ai while ans LCASE ans y amp amp ans n Calculate the new unit_price decmul amp unit_price amp dpcrnt amp unit_price rfmtdec amp unit_price format decdsply Format for display printf n t Update unit_price to s y n decdsply scanf ls amp ans Yes I want to update the current row if ans y if yes update current row Make UPDATESTO service call to update the record if OLTPCALL UPDATESTO char audv sizeof struct audv char amp audv amp audv_len 0 1 If service request failed print out the error and free up the buffer fprintf stderr s service failed n s s n UPSTOCKSTO audv gt u_errmsg OLTPFREE char audv exit 1 If update successful return the success call free up the buffer leave the application OLTPFREE char audv if OLTPTERM 1 fprintf stderr Can not terminate the application n else printf Application completed n Informix Embedded SQL TP XA Programmer s Manual A Sample DTP ESQL C Application Program Sample Server Program The server program server ec is an ESQL C program that performs the database interface portion of the up
99. t to provide reliable trans action data The two phase commit protocol governs the order in which a global transaction is committed and provides an automatic recovery mechanism in case a system or media failure occurs during transaction execution Every global transaction has a coordinator and one or more participants defined as follows m The coordinator directs the resolution of the global transaction It decides whether the global transaction should be committed or aborted m Each participant directs the execution of one transaction branch which is the part of the global transaction involving a single local database A global transaction includes several transaction branches in the following situations a Whenanapplication uses multiple processes to work for a global transaction a When multiple remote applications work for the same global transaction 1 10 Informix Embedded SQL TP XA Programmer s Manual Features of a DTP System The two phase commit protocol consists of the following two phases m Phase 1 Precommit phase During this phase the coordinator directs each participant database server or shared resource manager to prepare its transaction branch to commit Every participant notifies the coordinator whether it can commit its transaction branch The coordinator based on the response from each participant decides whether to commit or abort the global transaction It decides to commit only if all participan
100. temi s 2 ww 155 Chapter 2 Chapter 3 The X Open DTP Model The Application Program The Resource Manager The Transaction Manager The Model Interfaces Software Products and the X Opel DTP P Model Third Party Transaction Manager Software Informix Software for the Resource Manager What TP XA Can Do for You Integrating the Database Server and TP XA into the X Open DTP Model In This Chapter Installing Software for an X pen DTP Eae ronet Installing the Transaction Manager Installing the Informix Software Integrating the Database Server with the eon Wianaeet Monitoring Global Transactions The Userthreads Section The Transactions Section Transaction Commitment and Recovery The Database Server and the Two Phase Commit Protocol The Database Server and Heuristic Decisions Programming in an X Open Environment In This Chapter bof ey 3 Preparing to Program in an x Open DTP pavement Designing Programs for an X Open DTP Environment Identifying the Transaction Mode ESQL C Extensions to the XA Interface xa_open is_xaopened get_rmid Example Writing Server Programs for an 1X Open DTP Bavignient Programming Considerations for Server Programs Building Servers for an X Open DTP Environment Sample ESQL C Programs ee A Non DTP ESQL C Program j A Sample DTP ESQL C Application Pieskau iv Informix Embedded SQL TP XA Programmer s Manual 1 12 1 13 1 14 1 15 1 23 1
101. the database server XA library and in the TP XA library Integrating the Database Server and TP XA into the X Open DTP Model 2 5 Integrating the Database Server with the Transaction Manager 2 6 The RM Name The RM name is a string that identifies the RM to the TM This string is stored in the switch table in the name field When you use the database server as the DBMS of an RM initialize this field to the name of your Informix database server For example you would use the following string to specify that Informix Dynamic Server is your database server Informix Dynamic Server The Open and Close Strings The open string and close string are the text passed as an argument to the xa_open and xa_close XA interface routines respectively The TM calls the xa_open routine to initialize an RM to use in the DTP environment The open string contains any specific information that the RM needs The open string is limited to 256 characters and contains no blanks or line feeds When you use the database server as part of an RM the open string specifies the name of the database that a particular database server instance can open The TM calls the xa_close routine to close a currently open RM Once closed the RM cannot participate in global transactions until it is reopened The close string contains any information that is specific to the RM However the database server does not require any close information Therefore you must init
102. the database server in an X Open DTP environment if the TM software is not installed and configured properly For information on how to install and set up your TM product refer to your TM software documentation The TM needs information about the database server to establish its relationship with the database server RM For a list of the information that you must provide to the TM see Integrating the Database Server with the Transaction Manager on page 2 4 Installing the Informix Software To establish the database server as part of an RM in your X Open DTP environment you must install two Informix products m An Informix database server m One of the following Informix SQL API products a INFORMIX ESQL C Q INFORMIX ESQL COBOL up INFORMIX ESQL COBOL is not available with Dynamic Server with UD Option For instructions on how to install the database server and ESQL C refer to the Installation Guide for those products Integrating the Database Server with the Transaction Manager Once you install the Informix software the database server must be integrated as an RM in your X Open DTP environment To do this you must perform the following tasks m Provide information describing the RM to the TM m Set up database logging 2 4 Informix Embedded SQL TP XA Programmer s Manual Integrating the Database Server with the Transaction Manager Describing the Database Server Resource Manager For the TM to be able to inte
103. the transaction branch during the postdecision phase the RM reports that the transaction branch was either committed or aborted When a participating RM makes a heuristic decision and reports its decision to the TM the TM returns an error message to the application The actual text of the error message is TM dependent It is probably similar to one of the following error messages m Because of a heuristic decision the work done for the specified GTRID was aborted m Because of some failure the work done for the specified GTRID might have been heuristically completed m Because of a heuristic decision the work done for the specified GTRID was partially committed and partially aborted In the first case the global transaction was aborted When the state that the RM reports matches the state that the TM requires no problem exists because the transaction branch was completed either aborted or committed by all RMs successfully This response means that the global system is still consistent and no further problem exists In the second and third cases however the TM error messages indicate that the transaction state is unknown or mixed When the state that the RM reports does not match the state the TM wants the global system is now inconsistent The system administrator must bring the system back to a consistent state Because several RMs can be involved in a transaction the first task for the system administrator is to determine whi
104. transaction processing environment Types of Users This manual is written primarily for programmers who are developing applications for a third party transaction manager and an Informix database server The manual assumes that you know the C programming language and have some experience working with relational databases or exposure to database concepts The following users also might be interested in some of the topics in this book m Database server administrators m Performance engineers This manual also assumes that you have a working knowledge of your computer your operating system and the utilities that your operating system provides Introduction 3 Software Deoendencies 4 If you have limited experience with relational databases SQL or your operating system refer to the Getting Started manual for your database server for a list of supplementary titles Software Dependencies In places where this manual presents database server specific information this information applies to one of the following database servers m Informix Dynamic Server m Informix Dynamic Server with Advanced Decision Support and Extended Parallel Options m Informix Dynamic Server with Universal Data Option If you are using a database server that is not listed here see your release notes for information about client behavior on your database server Assumptions About Your Locale Informix products can support many languages cultures
105. ts indicate that they can commit their transaction branches If any participant indicates that it is not ready to commit its transaction branch or if it does not respond the coordinator decides to abort the global transaction The coordinator records its decision in its log m Phase 2 Postdecision phase Based on the decision in the precommit phase the coordinator issues the appropriate message to each participant Each participant then performs the requested action on its transaction branch and notifies the coordinator when it is finished The transaction branch is either committed or aborted by all participants The goal of the two phase commit protocol is to have the coordinator determine the likelihood of success for a global transaction before the participants actually handle their individual transaction branches Once a participant has actually handled its transaction branch it is very difficult to undo the work Some cases can cause a participant to decide how to handle its transaction branch independently of the coordinator For more information about these cases see Heuristic Decisions on page 1 22 For general information on the two phase commit protocol see your Administrator s Guide The two phase commit protocol for global transactions ensures that an OLTP application with heavy database use does not lose data in the event of system failure Informix and the X Open Distributed Transaction Processing Model 1 11 Th
106. ty The Informix solution for distributed transaction processing DTP is based on the X Open DTP model This chapter discusses the following topics m A general introduction to a DTP system m A description of the X Open DTP model m How TP XA and Informix database servers fit into the X Open DTP model The TP XA library is part of the INFORMIX ESQL C product The TP XA library is not available with the following Informix database servers m INFORMIX SE m Dynamic Server with AD and XP Options Informix and the X Open Distributed Transaction Processing Model 1 3 Distributed Transaction Processing 1 4 Distributed Transaction Processing This section provides the following information about DTP m A brief introduction to transaction processing m A description of several features needed to support a DIP environment Transaction Processing A transaction is a unit of work that consists of an application specific sequence of operations A typical transaction in the accounting world for example might be subtracting some amount from the accounts receivable ledger and adding the same amount to the cash ledger The transaction consists of the subtraction and the addition operation taken together A transaction processing system defines and coordinates interactions between multiple users and databases or other shared resources When a transaction includes operations in several databases or other shared resources the
107. um stock_num for update of unit_price if OLTPGETBUF transb unit_price 1 call routines to check the data TYPE OLTPRETURN OLTPFAIL 0 transb gt data 0L 0 Return error message EXEC SQL fetch upcurs into stock_num description unit_price err_chk FETCH udcurs Compare the record retrieved from QUERYSTO and the current query to check for data consistency If data is not consistent stop updating and return error to client else update the record Invoke routines to compare data from QUERYSTO and fetch data where pre is data previously retrieved and is most current data a Informix Embedded SQL TP XA Programmer s Manual K A Sample DTP ESQL C Application Program if COMPARE pre 1 fprintf stderr Data inconsistent update request ignored n OLTPRETURN OLTPFAIL 0 transb gt data 0L 0 Return error message EXEC SQL update stock set unit_price unit_price where current of upcurs err_chk UPDATE upcurs Return update success message to client program ows OLTPRETURN OLTPSUCESS transb gt data err_chk checks sqlca sqlcode and if an error has occurred it uses rgetmsg to display the message for the error number in sqlca sqlcode err_chk name char name if sqlca sqlcode lt 0 rgetmsg short sqlca sqlcode errmsg sizeof errmsg fprintf stder n tError d during s s n sqlca sqlcode name e
108. unction menu or option name or for a compiler or preprocessor argument You must enter a flag exactly as shown including the preceding hyphen ext A filename extension such as sql or cob might follow a variable that represents a filename Type this extension exactly as shown immediately after the name of the file The extension might be optional in certain products Punctuation and mathematical notations are literal symbols that you must enter exactly as shown Single quotes are literal symbols that you must enter as shown Privileges p 5 17 A reference in a box represents a subdiagram Imagine that the subdiagram is spliced into the main diagram at this point When a page number is not specified the subdiagram appears on the same page ALL A shaded option is the default action Syntax in a pair of arrows indicates a subdiagram The vertical line terminates the command 1 of 2 Introduction 11 Command Line Conventions Element Description f OFF A branch below the main path indicates an optional path Any term on the main path is required unless a ON branch can circumvent it A loop indicates a path that you can repeat Punctuation along the top of the loop indicates the separator symbol variable for list items A gate 3 ona path indicates that you can only use that path the indicated number of times even if it is part 3 size of
109. ur DB Access User Manual For a description of the demonstration databases and their contents see your Informix Guide to SQL Reference The scripts that you use to install the demonstration databases reside in the INFORMIXDIR bin directory on UNIX and in the INFORMIXDIR bin directory on Windows Introduction 5 New Features New Features This manual includes information about the following new features for TP XA m Availability for the Windows NT operating system m The following extensions to the X Open XA interface Q xa_open a is_xaopened a get_rmid The Informix extensions to the xa_open function allow you to connect to the database server and open a database in a single step The is_xaopened function enables you to determine whether xa_open has been called and the get_rmid function returns the RM ID if xa_open has been called Documentation Conventions This section describes the conventions that this manual uses These conventions make it easier to gather information from this and other volumes in the documentation set The following conventions are discussed Typographical conventions Icon conventions Command line conventions Sample code conventions 6 Informix Embedded SQL TP XA Programmer s Manual Typographical Conventions Typographical Conventions This manual uses the following conventions to introduce new terms illustrate screen displays describe command syntax an
110. ur TM might not handle server requests as Figure 1 7 describes Controlling the Two Phase Commit In an X Open DTP model the following two parts handle two phase commit protocol m The TM is the coordinator directing the execution of the global transactions m Each RM is a participant directing the execution of one transaction branch For definitions of coordinators and participants see page 1 10 1 20 Informix Embedded SQL TP XA Programmer s Manual The Transaction Manager The TM controls the two phase commit protocol in the following two phases Phase 1 The Precommit Phase 1 The application program initiates the two phase commit by notifying the TM that it wants to commit the global transaction The TM asks each RM if it is prepared to commit its transaction branch To do this the TM calls the xa_prepare XA interface routine If the RM determines that it can commit the transaction branch it records this information in a log and sends an affirmative reply to the TM If the RM cannot commit the transaction branch it aborts it and sends a negative reply to the TM Phase 2 The Postdecision Phase 1 If the TM receives any negative replies or no reply it calls the xa_rollback XA interface routine for each negative reply to ask the other RMs to abort their transaction branches If all RM replies are affirmative the TM first records that it has decided to commit the global transaction along with a list
111. urn data to the user after inputs are processed In an Informix X Open DTP environment a client might be a C program that gets input from the user and marks transaction boundaries using calls to the TM through the AP TM library It can also use AP TM calls to send server requests to a server program m A server is a program that provides one or more services A server receives requests from a client and initiates execution of the appropriate service A service could perform a single database task one service or many database tasks many services In an Informix X Open DTP environment to create a server you can write a module of services in an SQL API such as ESQL C and group related services into a module the server program m A service is a module of SQL API code that performs some database task for the application A service typically accesses a database to perform queries or update information The services can commu nicate with the TM through calls to the AP TM to mark transaction boundaries and to return messages to the client program They also communicate with the RM through SQL statements to access database information Inan Informix X Open DTP environment to create a service you can write a function with one of the Informix SQL APIs For example the service could be a program that is designed to record deposits into bank accounts The service could access an Informix database through a database server RM and update a
112. ver encountered an error while freeing the shared memory trans action entry The database server encountered a failure during write of commit record during two phase commit XA_RBTRANSIENT Execution was between an xa_start function and an xa_end function on another transaction branch when this call occurred The database server was unable to save the state of the current transaction branch to perform the commit XA_RBINTEGRITY The database server encountered a constraint error while checking deferred constraints XAER_NOTA The database server could not find the transaction branch indicated by the xid argument 2 of 10 A 4 Informix Embedded SQL TP XA Programmer s Manual XA Function xa_complete xa_end Return Code XA_HEURRB XA_RBOTHER XA_RBROLLBACK XAER_PROTO XAER_INVAL XAER_PROTO XAER_RMFAIL XAER_ASYNC XAER_RMERR Reason for Return Code Due to a heuristic decision the database server has already rolled back the transaction The TMONEPHASE flag was passed in the flags argument and the transaction was marked for rollback only probably due to a long transaction The TMONEPHASE flag was passed in the flags argument and the commit failed The database server has rolled back the transaction The database server does not support asynchronous operations Invalid arguments were specified for this routine This routine was called before a call to xa_open The TM

Informix Embedded SQL TP/XA Programmer`s Manual

Contents

Download Pdf Manuals

Related Search

Related Contents