xa_bqual

Returns the binary version of the bqual component of an ASCII XA transaction ID.

Syntax

xa_bqual(xid, 0)

Parameters

xid – is the ID of an SAP ASE transaction, obtained from the xactname column in systransactions or from sp_transactions.
0 – is reserved for future use

Examples

Example 1 – Returns “0x227f06ca80”, the binary translation of the branch qualifier for the SAP ASE transaction ID “0000000A_IphIT596iC7bF2#AUfkzaM_8DY6OE0”. The SAP ASE transaction ID is first obtained using sp_transactions:

1> sp_transactions

xactkey                        type     coordinator starttime           state      connection dbid  spid  loid  failover    srvname  namelen  xactname
------------------------------ -------- ----------- ------------------- ---------- ---------- ----- ----- ----- ----------- -------  -------  ---------------------------------------
0x531600000600000017e4885b0700 External XA          Dec 9 2005  5:15PM In Command  Attached     7   20    877  Resident Tx  NULL         39  0000000A_IphIT596iC7bF2#AUfkzaM_8DY6OE0

1> select xa_bqual("0000000A_IphIT596iC7bF2#AUfkzaM_8DY6OE0", 0)
2> go

...
--------------------------------------------------------------------
0x227f06ca80

Example 2 – xa_bqual is often used together with xa_gtrid. This example returns the global transaction IDs and branch qualifiers from all rows in systransactions where its coordinator column is the value of “3”:

1> select gtrid=xa_gtrid(xactname,0),
    bqual=xa_bqual(xactname,0) 
    from systransactions where coordinator = 3
2> go

        gtrid

        bqual

--------------------------------------------------------------------------
         0xb1946cdc52464a61cba42fe4e0f5232b

         0x227f06ca80

Usage

If an external transaction is blocked on the SAP ASE server and you are using sp_lock and sp_transactions to identify the blocking transaction, you can use the XA transaction manager to terminate the global transaction. However, when you execute sp_transactions, the value of xactname it returns is in ASCII string format, while XA Server uses an undecoded binary value. Using xa_bqual thus allows you to determine the bqual portion of the transaction name in a format that can be understood by the XA transaction manager.

xa_bqual returns:

The translated version of this string that follows the second “_” (underscore) and preceeds either the third “_” or end-of-string value, whichever comes first.
NULL if the transaction ID cannot be decoded, or is in an unexpected format.

Note: xa_bqual does not perform a validation check on the xid, but only returns a translated string.

See also sp_lock, sp_transactions in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can use xa_bqual.