DBA_2PC_PENDING is a view that shows the information of pending XA transaction branches.

Querying for XA transaction branches is shown below. In the example, the branch to be committed or rolled back with the XID and FAIL_TIME information is selected.

SQL> SELECT LOCAL_TRAN_ID, XID, STATUS FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID
-------------------------------------
XID
-------------------------------------
STATUS         
---------------
2.16.18
1.1000.1000
PREPARED       


1 selected.

The DBA can commit the desired XA transaction branch. Then, the resources that the branch had locked will be unlocked and the transaction will be committed.

SQL> commit force '2.16.18';

Commit succeeded.            

The DBA can force a rollback with the following command:

SQL> rollback force '2.16.18';

Rollback succeeded.            

Because the transaction was committed by the DBA instead of the TM, information about the corresponding XA transaction branch will remain.

The time when the commit was made by the DBA will be logged in FORCE_TIME as shown below:

SQL> SELECT LOCAL_TRAN_ID, XID, STATUS FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID
-------------------------------------
XID
-------------------------------------
STATUS         
---------------

1.1000.1000
FORCED_COMMIT  


1 selected            

Information about the XA transaction branch will be deleted when the TM judges that the information is no longer needed after using xa_forget.

The RM (Resource Manager) will not delete this information until it receives a request from the TM.

Creating and removing DBLinks is different depending on access privileges.

Public DBLinks

Public DBLinks can be used by the creator and other users.

To create a public DBLink, the user should have the privilege create public database link.

Creating a public DBLink is shown below:

create public database link public_tibero using 'remote_2';

In the example above, 'remote_2' in the using clause indicates the name of the database to connect to, which must be saved in the tbdsn.tbr file.

Removing a public DBLink is shown below. To remove a DBLink, the user should have the privilege drop public database link.

drop public database link public_tibero;

Private DBLinks

Only the user who created a private DBLink is allowed to use it.

To create a private DBLink, the user should have the privilege create database link.

Creating a private DBLink is shown below:

create database link remote_tibero using 'remote_1';

In the example above, 'remote_1' in the using clause creates a DBLink named 'remote_tibero'. Because the DBLink is a private DBLink, it can only be used by the user who created it.

Removing a private DBLink is shown below. Only the user who created the private DBLink can remove it.

drop database link remote_tibero;

There are two ways to specify the account for connecting to a remote database.

The account used to connect to the remote database should have the privilege to use CREATE SESSION. Because the local user can gain the authority of the account used to connect to the remote database through a DBLink, use caution when assigning privileges, especially to public DBLinks, because all local users have permissions in the remote database.

Creating a DBLink using a specified account is shown below:

create database link remote_tibero connect to user1
identified by 'password' using 'remote_1';

Creating a DBLink using the currently connected account is shown below:

create database link remote_tibero using 'remote_1';

When executing queries through a DBLink and the target of the DBLink is a DBMS other than Tibero, the DBLink can use a gateway for each DBMS.

Tibero server sends queries that target another DBMS to a gateway. The gateway connects to the remote DBMS, executes the queries received from the Tibero server, and sends the results to the Tibero server. To use the DBLink function of another DBMS, the DBMS's gateway binary and a configuration file are needed.

This section describes the types of gateways for each DBMS vendor and the cases of when the gateway and the Tibero are on the same machine and when they are not. Logging and the options provided by gateways are also described.

Gateways for Each DBMS Vendor

The following table shows the DBMS types and their gateway binary names which support DBLinks in Tibero .

Because gateway binaries can be different for each DBMS version, using the appropriate gateway is recommended.

Create Gateway Processes

A general way to use a gateway. This method cannot be used in the Windows environment.

The gateway needs a DBMS-provided library. If the library can be used in the location where the Tibero server is installed, it is possible to execute the DBLink function on the same machine with the Tibero server by creating a gateway process. The created gateway process is terminated when the session that uses the DBLink is closed.

Specifying the tbdsn.tbr file to use a DBLink that connects to an Oracle server is shown below:

<tbdsn.tbr>

ora_dblink=(
            (GATEWAY=(PROGRAM=gw4orcl)
                     (TARGET=orcl)
                     (TX_MODE=GLOBAL))
)

Specifying the tbdsn.tbr file to use a DBLink that connects to a DB2 server is shown below:

<tbdsn.tbr>

db2_dblink=(
            (GATEWAY=(PROGRAM=gw4db2)
                     (TARGET=sample)
                     (TX_MODE=GLOBAL))
)

Multithreaded Server Method

A user can start a gateway located on the same machine as Tibero server or on a remote machine with the multithreaded server method. Tibero server sessions communicate with the gateway via TCP/IP and connection information from the tbdsn.tbr file. When the gateway receives a request from a Tibero session, one of the working threads handles the request. In particular, the gateway that uses the Java programming language only supports the multithreaded server method.

The following example specifies the tbdsn.tbr file should to use DBLink to connect to an ORACLE server.

<tbdsn.tbr>

ora_link_remote=(
                 (GATEWAY=(LISTENER=(HOST=12.34.56.78)
                                    (PORT=9999))
                          (TARGET=orcl)
                          (TX_MODE=GLOBAL))
)     

The following example specifies the tbdsn.tbr file to use DBLink to connect to DB2 server.

<tbdsn.tbr>

db2_link_remote=(
                 (GATEWAY=(LISTENER=(HOST=12.34.56.78)
                                    (PORT=9999))
                          (TARGET=sample)
                          (TX_MODE=GLOBAL))
)     

The following example specifies the tbdsn.tbr file to use DBLink to connect to MS-SQL SERVER.

<tbdsn.tbr>

mssql_link_remote=(
                   (GATEWAY=(LISTENER=(HOST=12.34.56.78)
                                      (PORT=9093))
                            (TARGET=12.34.56.87:1433:master)
                            (TX_MODE=LOCAL))
)

The following example specifies the tbdsn.tbr file to use DBLink to connect to Sybase ASE.

<tbdsn.tbr>

ase_link_remote=(
                 (GATEWAY=(LISTENER=(HOST=12.34.56.78)
                                    (PORT=9093))
                          (TARGET=12.34.56.87:5000:master)
                          (TX_MODE=LOCAL))
)

The following example specifies the tbdsn.tbr file to use DBLink to connect to GREENPLUM.

<tbdsn.tbr>

gp_link_remote=(
                 (GATEWAY=(LISTENER=(HOST=12.34.56.78)
                                    (PORT=9093))
                          (TARGET=12.34.56.87:5432:mydb)
                          (TX_MODE=LOCAL))
)

If the character set of the TARGET server is EUCKR and the character set of the Tibero server is ASCII, characters excluded from the ASCII character set are displayed as '?' when executing a SELECT statement. However, if BYTES_CHARSET=EUCKR is set, characters are processed as EUCKR characters regardless of the gateway ENCODING or the Tibero server character set. Therefore, normal EUCKR characters are displayed when performing a SELECT statement.

To use a remote gateway, first start the gateway.

The following is the command used for starting an Oracle server gateway remotely.

$ gw4orcl

The following is the command used for starting an MS-SQL server gateway remotely.

$ tbgw

Gateway Related Directory Structure (ORACLE, DB2)

Gateways read configuration files and write log files using the environment variable TBGW_HOME.

If the TBGW_HOME variable is not specified, the default value is ${TB_HOME}/client/gateway. In Windows, the default value is %TB_HOME%\client\gateway.

The directory structure for configuration files and log files used by gateways is as follows:

$TBGW_HOME
    |--- DBMS Vendor Name
            |--- config
            |      |--- tbgw.cfg
            |--- log
                  |--- Gateway Log File

In the directory structure above, the root $TBGW_HOME is different depending on the system environment.

Gateway Configuration (ORACLE, DB2)

Gateway-related settings can be changed by modifying values of initialization parameters in the tbgw.cfg file.

Modifying values of initialization parameters is shown below:

<tbgw.cfg>

LOG_DIR=${TBGW_HOME}/{DBMS vnedor name}/log
LOG_LVL=2
LISTENER_PORT=9999
MAX_LOG_SIZE=20k
FETCH_SIZE=32k

The following table shows the options for using the gateway in listener mode.

Gateway Related Structure (MS-SQL SERVER, Sybase ASE, GREENPLUM)

A user must copy the tbJavaGW.zip file in ${TB_HOME}/client/bin and decompress it. Since JDBC driver files (Sybase: jconn3.jar, MS-SQL: sqljdbc.jar, GREENPLUM: postgresql-8.4-701.jdbc3.jar) are not provided by default, the user must obtain the JDBC driver and copy it to the LIB directory. The JDBC driver can generally be downloaded from the DBMS vendor web site.

The following is the directory structure of the Java gate configuration and log files.

Installation Directory
    |--- tbJavaGW
            |--- tbgw
            |--- jgw.cfg
            |--- jgwlog.properties
            |--- jgw_service.bat
            |--- prunsrv.exe
            |--- lib
            |     |--- tbgateway.jar
                  |--- commons-collections.jar
                  |--- commons-daemon-1.0.6.jar
                  |--- commons-pool.jar
                  |--- log4j-1.2.15.jar
                  |--- jconn3.jar
                  |--- sqljdbc.jar
                  |--- postgresql-8.4-701.jdbc3.jar
            |--- log
                  | --- A gateway's log files

Gateway Configuration (MS-SQL SERVER, Sybase ASE, GREENPLUM)

Settings related to a gateway can be modified by specifying values for initialization parameters in jgw.cfg.

Specifying a gateway is shown below:

<jgw.cfg>

DATABASE=SQL_SERVER
LISTENER_PORT=9093
INIT_POOL_SIZE=10
MAX_POOL_SIZE=1000
ENCODING=MSWIN949
MAX_LONGVARCHAR=4K
MAX_LONGRAW=4K     

Java Binary Versions

The following command checks the gateway binary version.

$ gw4orcl -v

tbGateway for oracle : Release 4 Trunk (Build 31190)

Linux Tibero_Linux 2.6.22-16-generic #1 SMP Mon Nov 24 17:50:35
   GMT 2008 x86_64 GNU/Linux version (little-endian)

The following database objects can be accessed through DBLink.

To access LONG columns in Oracle tables through DBLink, they must be the last column. SQL statements that can use DBLink are SELECT, INSERT, UPDATE, and DELETE. However, SELECT statements cannot use the FOR UPDATE clause.

Global Consistency is guaranteed for distributed transactions which consist of homogeneous DBLinks. To provide global consistency, users need to synchronize the TSNs of databases that participate in the distributed transaction.

TSN synchronization is processed when messages are exchanged between databases. When committed, the commit TSN of all nodes must be synchronized.

Processing a transaction in the pending state is similar to that of XA.

This section describes the differences between DBLink and XA in terms of the two-phase commit mechanism.

Commit Point Sites

For distributed transactions that use DBLink, select a 'commit point site' among the nodes that are participating in the transaction.

The commit point site is selected when the two-phase commit starts. Follow up the session tree and select the node that has the largest commit point strength. Each database has a commit point strength, and this value can be specified with the initialization parameter COMMIT_POINT_STRENGTH.

The commit point site is not prepared in the two-phase commit's prepare phase. Instead, the commit point site is committed after all nodes are prepared. Afterward, all other nodes will process the commit phase.

DBLink uses this kind of modified two-phase commit because it needs to decrease the overhead that occurs when preserving global consistency. In order to preserve global consistency in DBLink, the commit TSN of all nodes should be the same. Select the largest TSN among the nodes that finished the prepare phase and then commit using the TSN from the commit phase.

The commit TSN is unknown before the commit phase. Therefore, if other transactions should be allowed to see the information that was modified in the transaction cannot be determined. If a transaction tries to access information that was modified by a prepared transaction, the following error will occur:

TBR-21019: lock held by in-doubt distributed transaction.

If the prepared state is changed to the pending state when using DBLink, it is impossible to access the relevant data, which causes a problem for the in-doubt transaction.

To reduce this kind of problem, one of the participating nodes in the transactions is set to skip the prepare phase. This allows the commit point site to access the data even if the transaction enters an in-doubt state. Therefore, when choosing the commit point strength for the database, make sure to assign a larger value if the database requires more data accessibility.

Tibero provides the static views below to support viewing DBLink data:

V$DBLINK

The dynamic view V$DBLINK shows information about database links that are connected to a remote database in the current session.

If a user makes a query through a DBLink, a connection to the remote database is made and it remains even after the query or transaction finishes. This reduces the connection overhead needed to use the same DBLink later. The created connection remains until the session is terminated or until the connection is explicitly ended.

Querying connection data using V$DBLINK after executing a SELECT statement through a DBLink named remote_tibero is shown below:

SQL> select * from employee@remote_tibero;

 ID     NAME
--- ---------
  1      KIM
  2      LEE
  3     HONG

3 rows selected.

SQL> select * from V$DBLINK;

DB_LINK         OWNER_ID   OPEN_CURSORS   IN_TRANSACTION  HETEROGENEOUS
-------------  ----------  ------------   --------------  -------------
REMOTE_TIBERO         15             0    YES             NO

COMMIT_POINT_STRENGTH
---------------------
                  1

1 row selected.

The owner ID of remote_tibero is 15, there is no open cursor, and a transaction is active. It exists on the Tibero server, and the commit point strength of the remote database is 1.

Terminating a DBLink is shown below:

SQL> alter session close database link remote_tibero;
TBR-12056: database link is in use.                       ... (1) ...

SQL> commit;                                              ... (2) ...
Commit succeeded.

SQL> alter session close database link remote_tibero;     ... (3) ...
Session altered.

SQL> select * from V$DBLINK;                              ... (4) ...

DB_LINK          OWNER_ID   OPEN_CURSORS   IN_TRANSACTION  HETEROGENEOUS
--------------  ----------  ------------   --------------  -------------

COMMIT_POINT_STRENGTH
---------------------

0 row selected.     

(1) The attempt to close the DBLink did not succeed because a transaction that uses the DBLink is currently active.

(2) Complete the transaction using the commit command.

(3) Try to close the DBLink again.

(4) The DBLink is terminated normally and no data is in the V$DBLINK view.