SAP SQL Anywhere

Question

SELECT * FROM sa_transactions();

connection_num transaction_id start_time                start_sequence_num     end_sequence_num committed version_entries 
-------------- -------------- ----------------------- -------------------- -------------------- --------- --------------- 
             1     1461861072 2015-01-29 15:06:49.610           1393639117               (NULL)    (NULL)               0 

SELECT Number, Value FROM sa_conn_properties() WHERE PropName = 'TransactionStartTime';

     Number Value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
----------- ------------------------
         46                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
         45                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
         44                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
         43                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
         42                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
         41                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
          1 2015-01-29 15:06:49.610

What is "transaction_id" and where does it come from?

What is a "transaction sequence number" and where does it come from?

Is "start_time" the only useful column? It is the only column that appears in an example in the Help, and it appears to behave the same as CONNECTION_PROPERTY ( 'TransactionStartTime' ): When a transaction is committed, the row in sa_transactions() disappears at the same time TransactionStartTime is set back to empty.

SELECT min( start_time )
FROM sa_transactions()

min(sa_transactions.start_time) 
------------------------------- 
2015-01-29 15:06:49.610         

SELECT MIN ( Value ) 
  FROM sa_conn_properties() 
 WHERE PropName = 'TransactionStartTime'
   AND COALESCE ( Value, '' ) <> '';

MIN(sa_conn_properties.Value)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
-----------------------------
2015-01-29 15:06:49.610

Are "end_sequence_num" and "committed" ever non-NULL? The rows seem to disappear as soon as the transactions are committed.

What is "version_entries" and where does it come from?

Accepted Answer

If you think more in terms of snapshots some of this will be clearer; or at least easier to provide examples for.

This stored procedure reports of both kinds of 'transactions' in the system. As you may have already surmised version_entries relates to the number of rows in the (snapshot) version store.

In the same vein, snapshots can hang around after a commit (say one with a With-Hold cursor open on it) and it is in those cases the values "end_sequence_num" and "committed" can have non-null values. {see sa_snapshots( ) and other sections on snapshots for some other aspects about snapshots}

There may be a chance that (with an extremely busy server fighting for resources) where information about normal transactions may not be cleaned up immediately (a pure guess on my part) but that should be temporary and transitory. I can honestly say I have never seen that myself.

And "transaction_id" it's just a unique value maintained by the current server instance. You can think of it as the primary key of a system-specific virtual table. Though there is no guarantee that the value won't can get reused {much like a pid}

Ditto for the sequence numbers (unique and progressive though they are) ... but may be of little informational value outside the tracking/mapping of snapshots.

Help any?

Answer 2

I use it to generate my own Transaction ID that I can reference in long running procedures. Reason to use it is to write changes made by a procedure, functions and trigger to a audit table. In this audit table I can identify all records that have been created during a transaction. The SA Transaction ID changes when you commit.

--
-- Returns a unique transaction id
--
RETURNS char(12)
NOT DETERMINISTIC
BEGIN
    declare cKey            REPL_ID; -- the generated Replication-ID
    declare TransactionID   integer; -- Current Transaction ID

    -- Test if the Session Variables are existing
    if VarExists( 'TTK_TransactionID' ) = 0 then
        create variable TTK_TransactionID integer ;
        create variable TTK_LastTransKey  REPL_ID ;
    end if;

    select  Trans.TRANSACTION_ID
    into    TransactionID
    from    SA_TRANSACTIONS() as Trans
    where   Trans.CONNECTION_NUM = connection_property( 'Number' );

    if TransactionID is not null then
        if TTK_TransactionID is null or TTK_TransactionID != TransactionID then
            set TTK_TransactionID = TransactionID;
            set TTK_LastTransKey = GetTableKey( left(cTableName,20) + '_TRANSACTION' );
        end if;
        set cKey = TTK_LastTransKey;
    end if;
    return cKey
END;

GetTableKey is a Function to build a unique primary key in my project.

I think that can by handy sometimes. If somebody knows a simpler solution let me know.

SAP SQL Anywhere

What is sa_transactions() for?

Follow this question

Related questions