Session Management

Home | Previous Page | Next Page Creating User-Defined Routines > Developing a User-Defined Routine > Creating UDR Code >

Session Management

Session management is different in a C UDR than in a client LIBMI application. Unlike a client LIBMI application, which can simultaneously connect to several databases, a UDR inherits a particular session context. That is, it is within an existing session and uses a database that is already opened. For information, see Establishing a UDR Connection (Server).

Because a C UDR can establish only a UDR connection, not a client connection, restrictions apply in the following areas:

Sessions
Transactions

Session Restrictions

The following restrictions exist within a UDR with respect to the session:

Session-connection restrictions
A UDR runs within an existing session. It can only obtain a connection to this session after a client application has already begun the session. However, a UDR can obtain more than one connection to the session.
Cursor restrictions
- Cursors opened in one session are only visible in the context of that session. Cursors defined on one UDR are not visible in other UDRs. Therefore, multiple UDRs can use the same cursor names.
- A cursor opened in a session persists until an mi_close( ) or an mi_drop_prepared_statement( ) function, or until the end of the statement that called the UDR (if mi_close( ) was not called). Therefore, cursors last across routine invocations.
Database restrictions
- A UDR cannot connect to a remote database server.
- A UDR uses the default database, which the client application has established. However, it cannot change the database.
Table restrictions
The scope of temporary tables created in a logging database is the current session. Temporary tables created in a database that does not use logging or with a CREATE TABLE statement that includes the WITH NO LOG clause persist until beyond the CLOSE DATABASE statement.
Constraint restrictions
Violations that are associated with the execution of the UDR are added to a violation temporary table. Therefore, if the SET CONSTRAINTS statement sets the constraint mode to IMMEDIATE, constraint checking is done per statement. Otherwise, constraint checking is deferred to the time when the transaction is committed. If the constraint mode is set to IMMEDIATE, the constraint is checked after each statement in the UDR. If you want per-UDR constraint checking, change the constraint mode to DEFERRED at the beginning of the UDR and back to IMMEDIATE at the end of the UDR.

Transaction Management

Against databases that use logging, a UDR inherits the transaction state that is started by the SQL statement that invoked the UDR. All statements in a UDR occur inside a transaction because the UDR is called from within an SQL statement. An SQL statement is always part of a transaction. The type of transaction that the SQL statement is part of is based on the type of database and whether it uses transaction logging, as Table 84 shows.

Table 84. Types of Transactions
Status of Database	Status of SQL Statement	Description
Database is not ANSI-compliant:
Database does not use transaction logging. Database does use transaction logging.	No transactions exist.	The database server does not log changes to the database that SQL statements might make. Any UDRs that are part of the SQL statement are not logged and their actions cannot be rolled back.
	Each SQL statement is within either an explicit transaction or a single-statement transaction:
	Explicit transaction	The client application begins an explicit transaction with the BEGIN WORK statement and ends it with either the COMMIT WORK statement (transaction successful) or the ROLLBACK WORK statement (transaction not successful). Operations within a single cursor (from OPEN to CLOSE) constitute a transaction as well. SQL statements between the BEGIN WORK and COMMIT WORK or ROLLBACK WORK statements (or within a cursor) execute within the explicit transaction. If these SQL statements contain any UDRs, each of the UDRs executes within the explicit transaction.
	Single-statement transaction	The client application begins a single-statement transaction for any SQL statement that is not contained within a BEGIN WORK statement and a COMMIT WORK or ROLLBACK WORK statement. Any UDRs that are part of the SQL statement are within this single-statement transaction. The only exception to this rule is the EXECUTE FUNCTION statement; it does not execute within a transaction.
Database is ANSI-compliant:
Database logging is always in effect.	Each SQL statement executes within an implicit transaction, which is always in effect.	The client application invokes an SQL statement, which begins the implicit transaction, and the transaction ends explicitly with COMMIT WORK or ROLLBACK WORK. Any UDRs within the SQL statement that began the implicit transaction are automatically part of the transaction. In addition, any SQL statements that execute before the COMMIT WORK or ROLLBACK WORK statement ends the transaction are also part of the implicit transaction.

You can obtain the transaction ID of the current transaction with the mi_get_transaction_id( ) function.

As a rule, a C UDR must not issue any of the following SQL transaction statements because they interfere with transaction boundaries:

BEGIN WORK
COMMIT WORK
ROLLBACK WORK

In all databases that use logging, an SQL statement is within a transaction. In such databases, a DML statement (SELECT, INSERT, UPDATE, DELETE) implicitly starts a transaction, if a transaction is not already in effect. If a UDR that executes one of these SQL transaction statements is called from a DML statement, the database server raises an error (-535).

However, the EXECUTE PROCEDURE and EXECUTE FUNCTION statements do not implicitly start another transaction, if they are not already in a transaction. If a UDR is called from an EXECUTE PROCEDURE or EXECUTE FUNCTION statement, the database server only raises an error if the UDR interferes with the current transaction boundaries.

For example, suppose you have a UDR named udr1( ) that uses the mi_exec( ) function to execute two SQL statements:

void udr1(...)
{
   mi_exec(...DML statement 1...);
   mi_exec(...DML statement 2...);
}

Suppose also that you execute this UDR with the EXECUTE PROCEDURE statement, as follows:

EXECUTE PROCEDURE udr1( );

If a transaction has not already been started, this UDR would have two transactions, one for each call to mi_exec( ).

To get a single transaction, you could surround these SQL statements with a begin and end work, as udr2( ) shows:

void udr2(...)
{
   mi_exec(...'begin work'..);
   mi_exec(...DML statement 1...);
   mi_exec(...DML statement 2...);
   mi_exec(...'commit work'...);
}

However, you can only start a transaction within a UDR if you are not already in a transaction. Therefore, you can only invoke a UDR that starts a transaction when the following restrictions are met:

You must invoke the UDR with the EXECUTE PROCEDURE or EXECUTE FUNCTION statement.
Because udr2( ) is a user-defined procedure, you must use EXECUTE PROCEDURE to invoke it, as follows:
```
EXECUTE PROCEDURE udr2( );
```
Suppose you tried to invoke udr2( ) with the following SELECT statement:
```
SELECT udr2( ) FROM tab WHERE x=y;
```
If a transaction had not started, the SELECT operation starts its own implicit transaction. The database server raises an error when execution reaches the first call to mi_exec( ) in udr2( ):
```
mi_exec(...'begin work'..);
```
The UDR calling context must not have already started a transaction.
The following code fragment fails because the EXECUTE PROCEDURE statement is already within a transaction block and udr2( ) attempts to start another transaction:
```
BEGIN WORK;
...
EXECUTE PROCEDURE udr2( );  /* This statement fails. */
...
COMMIT WORK:
```
The database server raises an error when execution reaches the first call to mi_exec( ) in udr2( ):
```
mi_exec(...'begin work'..);
```

Important:

Unless a UDR knows its calling context, it should not issue an SQL transaction statement. If the caller has already begun a transaction, the UDR fails.

You can execute an SQL transaction statement in a UDR that you call directly from a DataBlade API module (not from within an SQL statement). You can also choose whether to commit or rollback the current transaction from within an end-of-statement or end-of-transaction callback function. For more information, see State Transitions in a C UDR (Server).

In a database with logging, the database server creates an internal savepoint before execution of each statement within a UDR that might affect the database state. If one of these statements fails, the database server performs a rollback to this internal savepoint. At this point, the database server does not release table locks. However, the same user can obtain a lock on the same table in the same transaction. The database server releases the table lock when the entire transaction ends (commit or rollback).

Warning:

For databases that do not use logging, no changes to the database that a UDR might make are logged. Therefore, none of these changes can be rolled back. Consider carefully whether you want to use logging for your database.