4. Database Link-Related SQL Statements#

This chapter discusses in detail the SQL statements and REMOTE functions provided for controlling database links, and how to use database links.

CREATE DATABASE LINK#

Syntax#

create_database_link ::=

Prerequisites#

Only the SYS user or the user granted the CREATE DATABASE LINK system privilege can create a database link object.

Description#

The user can create a database link object with the CREATE DATABASE LINK statement. A database link object can have only one remote server as its target.

PUBLIC|PRIVATE#

Specifies the PUBLIC|PRIVATE attribute of the database link object to be created. If set to PUBLIC, all users have access to the created database link object; If set to PRIVATE, only the user who created the database link object and the SYS user have access to it. On omission, the database object link is created as PUBLIC.

dblink_name#

Specifies the name of the database link object to be created. Refer to Chapter 1: “Rules for Object Name” in SQL Reference for more information on specifying names.

user_id/password#

Specifies the user name and user password of the database of the remote server to be connected. The user specified for this clause must be granted access privileges on the target object of the remote server accessed through the database link. If not, a privilege-related error will be raised.

Since the user name and password are saved in upper case by default, it is necessary to encase case-sensitive letters and special characters in quotation marks(").

target_name#

Specifies the name of a server among the remote servers defined in the TARGETS property in the AltiLinker properties file.

For more detailed information on how to specify the TARGETS property in the AltiLinker properties file, please refer to “Configuration of the AltiLinker Properties File” of Chapter 3.

Example#

\<Create 1> Create a database link which connects to the remote database server specified ‘AltibaseHDB’ in the AltiLinker properties file, with the user name and user password: applys/applys. Create the database link object, ‘link1’, which only the creator can access.

CREATE PRIVATE DATABASE LINK link1 
CONNECT TO applys IDENTIFIED BY applys 
USING Altibase;

\<Query 2> Create a database link which connects to the remote database server specified ‘AltibaseHDB’ in the AltiLinker properties file, with the user name and user password: applys/applys. Create the database link object, ‘link2’, which all users of the system can access. However, the user name and password are in lower case.

CREATE PUBLIC DATABASE LINK link2 
CONNECT TO "applys" IDENTIFIED by "applys" 
USING Altibase;
or
CREATE DATABASE LINK link2 
CONNECT TO "applys" IDENTIFIED by "applys" 
USING Altibase;

DROP DATABASE LINK#

Syntax#

drop_database_link ::=

Prerequisites#

Only the SYS user or the user granted the DROP DATABASE LINK system privilege can remove a database link object.

Description#

Drops a database link object.

dblink_name#

Specifies the name of the database link object to be removed.

Notes#

If the database link object to be dropped is currently in use, it cannot be dropped. It can only be removed when no queries are being executed through the given database link object. An error will be raised if the database object link is being queried.

Examples#

\<Query 1> Drop the PRIVATE database link object, 'dblink'.

DROP PRIVATE DATABASE LINK dblink1;

\<Query 2> Drop the PUBLIC database link object, ‘dblink1’.

DROP PUBLIC DATABASE LINK dblink1;
or
DROP DATABASE LINK dblink1;

ALTER DATABASE LINKER#

Syntax#

alter_database_linker ::=

Prerequisites#

This statement can be executed by the SYS user connected in sysdba administrator mode.

Description#

Starts or terminates the AltiLinker.

START#

Starts the AltiLinker, provided that no running AltiLinker process exists.

STOP#

Stops the AltiLinker. However, for AltiLinker shutdown to succeed, there should be no transactions using the database link. If there is a transaction using the database link, this statement will fail.

STOP FORCE#

Forcefully stops the AltiLinker process, regardless of there being a transaction using the database link.

DUMP#

If this statement is executed while the AltiLinker process is running, operations which are currently being executed by the threads within the AltiLinker process are output to a file. The output file is fixed as $ALTIBASE_HOME/trc/altibase_lk_dump.log and is overwritten every time the ALTER DATABASE LINKER DUMP statement is executed.

Examples#

iSQL(sysdba)> ALTER DATABASE LINKER START;
Alter success.
iSQL(sysdba)> ALTER DATABASE LINKER STOP;
Alter success.
iSQL(sysdba)> ALTER SESSION LINKER STOP FORCE;
Alter success.
iSQL(sysdba)> ALTER SESSION LINKER DUMP;
Alter success.

ALTER SESSION#

Syntax#

close_database_link ::=

Prerequisites#

All users can execute this statement.

Description#

Terminates the linker session on the Altibase server, which is connected to AltiLinker.

LINK ALL#

Terminates all linker sessions.

LINK dblink_name#

Terminates only the linker sessions that are associated with the database link object of a specified name.

Examples#

ALTER SESSION CLOSE DATABASE LINK ALL; 
ALTER SESSION CLOSE DATABASE LINK link1;

SELECT#

The user can either use the location descriptor(‘@’) or the pass-through style to SELECT a remote database object with Altibase Database Link.

We recommend to use the REMOTE_TABLE keyword of the pass-through method with Altibase Database Link. The location descriptor(‘@’) is supported for compatibility with older versions.

Both methods can only use database links in the FROM clause.

Refer to the General Reference Chapter 1. Display of Character strings for further information on display usage of character strings used in the SELECT clause.

Location Descriptor#

Statements that use the location descriptor ‘@’ are supported to maintain compatibility with older versions.

The location descriptor ‘@’ can be used as shown below, to indicate that the given object exists in the remote server:

SELECT * FROM t1@link1;

Queries that include location descriptors retrieve all records in the table of the remote server to the local server, as for the previous version. Therefore, the use of the REMOTE_TABLE keyword can enhance the processing speed, according to the characteristics in a query.

Since queries using location descriptors, as the one below, retrieve all records of the remote table to the local server, they are accompanied with the additional burden of network costs, local server operational costs and, when necessary, disk I/O costs. However, the use of the REMOTE_TABLE keyword delivers better performance; with the REMOTE_TABLE keyword, the query is processed on the remote server and the local server retrieves only one record as the result.

Table name: T1
Table schema:
C1 VARCHAR(1024),
C2 VARCHAR(1024),
C3 VARCHAR(1024),
...
C50 INTEGER,
...
C100 VARCHAR(1024),
Total number of records: 1,000,000 (1 record has the c50 column value of 50)

Using the location descriptor:

SELECT c50, c100 FROM t1@link1 WHERE c50 = 50;

Using the REMOTE_TABLE keyword:

SELECT * FROM REMOTE_TABLE( link1, 'select c50, c100 from t1 where c50 = 50' );

In conclusion, the use of the REMOTE_TABLE keyword, rather than the location descriptor, is recommended for queries retrieving the same results using database links.

Examples#

\<Query 1> SELECT all of table t1 which exists in the remote server, pointed to by link1, using the location descriptor.

SELECT * FROM t1@link1;

\<Query 2> Using the location descriptor, SELECT columns a1, a2 in the remote table.

SELECT a1, a2 FROM ( SELECT * FROM t1@link1 );

REMOTE_TABLE#

With the REMOTE_TABLE keyword of the pass-through style, the SELECT statement can be executed on the remote server.

When a query that contains the REMOTE_TABLE keyword is executed on the remote server, the query result is written to a memory buffer, which is passed to the query processor, and then dropped. A query that needs to repeatedly access dropped results (e.g., join operations) must be re-executed on the remote server

The syntax for the REMOTE_TABLE keyword is as follows:

REMOTE_TABLE (
    dblink_name     IN VARCHAR,
    statement_text  IN VARCHAR )

dblink_name: the name of the database link object.

statement_text: the SELECT statement to be executed on the remote server.

Example#

\<Query 1> Using the REMOTE_TABLE keyword, SELECT all the rows from table t1 which exists in the remote server.

SELECT * FROM REMOTE_TABLE( link1, 'select * from t1' );

\<Query 2> Using the REMOTE_TABLE keyword, SELECT columns a1, a2 from the remote table.

SELECT * FROM REMOTE_TABLE( link1, 'select a1, a2 from t1' );

REMOTE_TABLE_STORE#

Altibase stores the results of queries that contain the REMOTE_TABLE keyword and have been executed on the remote server into disk temporary tables to allow repeated access.

WHERE Clause#

When querying the remote server using database links, the WHERE clause can be used in the same manner as when querying with the SELECT statement on a local server.

Since the use of the location descriptor or the REMOTE_TABLE keyword is invalid in the WHERE clause, an alias which is specified in the FROM clause should be used in the WHERE clause.

Examples#

\<Query 1> SELECT a row with a value larger than 100, from column a1 in table t1 of the remote server pointed to by link1.

SELECT * FROM REMOTE_TABLE( link1, 'select * from t1 where a1 > 100' );
or
SELECT * FROM REMOTE_TABLE( link1, 'select * from t1' ) t1_alias
               WHERE t1_alias.a1 > 100;
or
SELECT * FROM t1@link1 WHERE a1 > 100;

\<Query 2> SELECT the name of the employee working for the department ‘rnd', from all employees stored in the table emp2 of the remote server and the table emp1 of the local server pointed to by link1.

SELECT emp_name
FROM ( SELECT emp_no, emp_name FROM emp1
    UNION ALL
    SELECT emp_no, emp_name FROM REMOTE_TABLE( link1, 'select emp_no, emp_name from emp2' ) ) v1,
    dept
WHERE v1.emp_no = dept.emp_no AND dept.dept_name = 'rnd';
or
SELECT emp_name
FROM ( SELECT emp_no, emp_name FROM emp1
    UNION ALL
    SELECT emp_no, emp_name FROM emp2@link1 ) v1,
    dept
WHERE v1.emp_no = dept.emp_no AND dept.dept_name = 'rnd';

Other SELECT Statement Features#

Database links support the use of joins, subqueries, set operators and aggregation functions in SELECT statements. Also, when a SELECT statement exists in a DDL or DML statement in the form of a subquery, database links can also be used in the subquery statement.

Examples#

\<Query 1> SELECT the unduplicated values in column a1 in table t1 of the remote server pointed to by link1.

SELECT * FROM REMOTE_TABLE( link1, 'select distinct a1 from t1' );
or
SELECT DISTINCT a1 FROM t1@link1;

\<Query 2> Find the departments of all employees by joining tables t_member and t_dept of the remote server pointed to by link1, and group the departments with an ID value of 0 or larger by department ID, and return the number of employees and their average age for each department.

SELECT t1.dept_id, COUNT(*), AVG(age)
FROM REMOTE_TABLE( link1, 'select * from t_member' ) t1,
   REMOTE_TABLE( link1, 'select * from t_dept' ) t2,
WHERE t1.dept_id = t2.dept_id
GROUP BY t1.dept_id
HAVING t1.dept_id >= 0;
or
SELECT t1.dept_id, COUNT(*), AVG(age)
FROM t_member@link1 t1,
   t_dept@link1 t2
WHERE t1.dept_id = t2.dept_id
GROUP BY t1.dept_id
HAVING t1.dept_id >= 0;

\<Query 3> Find all employees by joining tables t_member and t_dept of the remote server pointed to by link1, and select three employees under the age of 30, in descending order of ID, and return their names, ages, and the sum of their ages.

SELECT t1.name, t1.age
    ( SELECT * FROM REMOTE_TABLE( link1, 'select sum(age) from t_member' ) ) sum
FROM REMOTE_TABLE( link1, 'select dept_id, member_id, dept_name, age from t_member where age < 30' ) t1,
   REMOTE_TABLE( link1, 'select dept_id, dept_name, from t_dept' ) t2
WHERE t1.dept_id = t2.dept_id AND t1.age < 30
ORDER BY t1.member_id DESC LIMIT 3;
or
SELECT t1.name, t1.age
    ( SELECT SUM(age) FROM t_member@link1 ) sum
FROM t_member@link1 t1,
    ( SELECT dept_name, dept_id FROM t_dept@link1 ) t2
WHERE t1.dept_id = t2.dept_id AND t1.age < 30
ORDER BY t1.member_id DESC LIMIT 3;

\<Query 4> Retrieve the name and age in table t2 of the remote server, pointed to by link1, and insert the values into table t1 of the local server.

INSERT INTO t1 SELECT * FROM REMOTE_TABLE( link1, 'select name, age from t2' );
or
INSERT INTO t1 SELECT name, age FROM t2@link1;

REMOTE_EXECUTE_IMMEDIATE#

The REMOTE_EXECUTE_IMMEDIATE procedure executes the input SQL statement on the remote server through the database link. Excluding the SELECT statement, DML, DDL and DCL statements are executable. SQL statements including parameter markers cannot be executed with this procedure.

Syntax#

REMOTE_EXECUTE_IMMEDIATE (
    dblink_name     IN VARCHAR,
    statement_text  IN VARCHAR );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_text	Input	VARCHAR	The SQL statement to be executed on the remote server

Return Value#

Because it is a procedure, there is no return value.

Example#

\<Query 1> Create table remote_t in the remote server, pointed to by link1, and after inserting a record, delete the table.

exec REMOTE_EXECUTE_IMMEDIATE( 'link1', 'create table remote_t( c1 integer )' ); 
exec REMOTE_EXECUTE_IMMEDIATE( 'link1', 'insert into remote_t values (10)' ); 
exec REMOTE_EXECUTE_IMMEDIATE( 'link1', 'insert into remote_t values (20)' ); 
exec REMOTE_EXECUTE_IMMEDIATE( 'link1', 'insert into remote_t values (30)' ); 
exec REMOTE_EXECUTE_IMMEDIATE( 'link1', 'drop table remote_t' );

REMOTE Functions Supportive of Binding#

This section explains pass-through REMOTE functions that support the binding of parameters.

REMOTE_ALLOC_STATEMENT#

This function prepares a SQL statement to be executed on the remote server. This function can only be used within stored procedures and stored functions.

Syntax#

BIGINT REMOTE_ALLOC_STATEMENT (
    dblink_name     IN VARCHAR,
    statement_text  IN VARCHAR );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_text	Input	VARCHAR	The SQL statement to be executed on the remote server.

Return Value#

If the function executes successfully, the statement ID is returned; if not, a negative integer is returned.

Example#

\<Query 1> Create a stored procedure which returns the value of column c2 of the record where the value of column c1 is 20 from table t1 of the remote server, pointed to by link1. Use parameter binding for the search condition of the value of the column c1.

EXEC REMOTE_EXECUTE_IMMEDIATE('link1', 'CREATE TABLE t1(c1 INTEGER, c2 FLOAT(38))');

EXEC REMOTE_EXECUTE_IMMEDIATE('link1', 'INSERT INTO t1 VALUES(20, 30.001)');

CREATE OR REPLACE PROCEDURE proc1()
AS
    statement_id    BIGINT;
    row_cnt         INTEGER;
    result          INTEGER;
    col_value       FLOAT(38);

BEGIN
    statement_id  := REMOTE_ALLOC_STATEMENT('link1', 'SELECT * FROM t1 where c1 = ?');

    result := REMOTE_BIND_VARIABLE( 'link1', statement_id, 1, '20' );

    IF result >= 0 THEN
        result := REMOTE_EXECUTE_STATEMENT('link1', statement_id );

        LOOP
            result := REMOTE_NEXT_ROW( 'link1', statement_id );
            EXIT WHEN result < 0;

            col_value := REMOTE_GET_COLUMN_VALUE_FLOAT( 'link1', statement_id, 2, 38 );
            SYSTEM_.PRINTLN(col_value);
        END LOOP;

        result := REMOTE_FREE_STATEMENT('link1', statement_id);
        IF result < 0 THEN
            SYSTEM_.PRINTLN('Free failed');
        END IF;
    END IF;
END;
/

REMOTE_BIND_VARIABLE#

This function binds values to parameter markers in SQL statements. This function can only be used within stored procedures and stored functions.

Syntax#

INTEGER REMOTE_BIND_VARIABLE (
    dblink_name        IN VARCHAR,
    statement_id       IN BIGINT,
    parameter_number   IN VARCHAR,
    value              IN VARCHAR );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID
parameter_number	Input	INTEGER	The numerical order of the parameters in SQL statements to which values are bound. Starts from 1.
value	Input	VARCHAR	The value to be bound

Return Value#

The returner of a negative number indicates failure; otherwise, success.

Example#

Please refer to the examples of REMOTE_ALLOC_STATEMENT.

REMOTE_EXECUTE_STATEMENT#

This function executes the prepared SQL statement on the remote server. This function can only be used within stored procedures and stored functions

Syntax#

INTEGER REMOTE_EXECUTE_STATEMENT (
    dblink_name        IN VARCHAR,
    statement_id       IN BIGINT );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	Statement ID

Return Value#

The returner of a negative number indicates failure. If the returner is a non-negative number on the execution of a DML statement, the non-negative number indicates the number of affected rows; on the execution of a DDL or SELECT statement, this indicates success.

Example#

Please refer to the examples of REMOTE_ALLOC_STATEMENT.

REMOTE_FREE_STATEMENT#

This function releases the statements that have completed execution. This function can only be used within stored procedures and stored functions

Syntax#

INTEGER REMOTE_FREE_STATEMENT (
    dblink_name        IN VARCHAR,
    statement_id       IN BIGINT );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	Statement ID

Return Value#

The returner of a negative number indicates failure; otherwise, success.

Example#

Please refer to the examples of REMOTE_ALLOC_STATEMENT.

REMOTE_GET_COLUMN_VALUE_CHAR#

This function returns the CHAR type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.

Syntax#

CHAR(char_size) REMOTE_GET_COLUMN_CHAR (
    dblink_name        IN VARCHAR,
    statement_id       IN BIGINT,
    column_number      IN INTEGER,
    char_size          IN INTEGER );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID to be executed
column_number	Input	INTEGER	The order of columns within the row. Starts from 1.
char_size	Input	INTEGER	The size of the CHAR type to be returned

Return Value#

The value of the specified column is returned.

REMOTE_GET_COLUMN_VALUE_VARCHAR#

This function returns the VARCHAR type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.

Syntax#

VARCHAR(char_size) REMOTE_GET_COLUMN_VARCHAR (
    dblink_name       IN VARCHAR,
    statement_id      IN BIGINT,
    column_number     IN INTEGER,
    varchar_size      IN INTEGER );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID to be executed
column_number	Input	INTEGER	The order of columns with the row. Starts from 1.
varchar_size	Input	INTEGER	The size of the VARCHAR type to be returned

Return Value#

The value of the specified column is returned.

REMOTE_GET_COLUMN_VALUE_FLOAT#

This function returns the FLOAT type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.

Syntax#

FLOAT(precision) REMOTE_GET_COLUMN_FLOAT (
    dblink_name       IN VARCHAR,
    statement_id      IN BIGINT,
    column_number     IN INTEGER,
    precision         IN INTEGER );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID to be executed
column_number	Input	INTEGER	The order of columns within the row. Starts from 1.
precision	Input	INTEGER	The precision of the FLOAT type to be returned

Return Value#

The value of the specified column is returned.

Example#

Please refer to the examples of REMOTE_ALLOC_STATEMENT.

REMOTE_GET_COLUMN_VALUE_SMALLINT#

This function returns the FLOAT type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.

Syntax#

SMALLINT REMOTE_GET_COLUMN_SMALLINT (
    dblink_name       IN VARCHAR,
    statement_id      IN BIGINT,
    column_number     IN INTEGER );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID to be executed
column_number	Input	INTEGER	The order of columns within the row. Starts from 1.

Return Value#

The value of the specified column is returned.

REMOTE_GET_COLUMN_VALUE_INTEGER#

This function returns the INTEGER type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.

Syntax#

INTEGER REMOTE_GET_COLUMN_INTEGER (
    dblink_name       IN VARCHAR,
    statement_id      IN BIGINT,
    column_number     IN INTEGER );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID to be executed
column_number	Input	INTEGER	The order of columns within the row. Starts from 1.

Return Value#

The value of the specified column is returned.

REMOTE_GET_COLUMN_VALUE_BIGINT#

This function returns the BIGINT type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.

Syntax#

BIGINT REMOTE_GET_COLUMN_BIGINT (
    dblink_name       IN VARCHAR,
    statement_id      IN BIGINT,
    column_number     IN INTEGER );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID to be executed
column_number	Input	INTEGER	The order of columns within the row. Starts from 1.

Return Value#

The value of the specified column is returned.

REMOTE_GET_COLUMN_VALUE_REAL#

This function returns the REAL type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.

Syntax#

REAL REMOTE_GET_COLUMN_REAL (
    dblink_name       IN VARCHAR,
    statement_id      IN BIGINT,
    column_number     IN INTEGER );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID to be executed
column_number	Input	INTEGER	The order of columns within the row. Starts from 1.

Return Value#

The value of the specified column is returned.

REMOTE_GET_COLUMN_VALUE_DOUBLE#

This function returns the DOUBLE type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.

Syntax#

DOUBLE REMOTE_GET_COLUMN_DOUBLE (
    dblink_name       IN VARCHAR,
    statement_id      IN BIGINT,
    column_number     IN INTEGER );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID to be executed
column_number	Input	INTEGER	The order of columns within the row. Starts from 1.

Return Value#

The value of the specified column is returned.

REMOTE_GET_COLUMN_VALUE_DATE#

This function returns the DATE type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.

Syntax#

DATE REMOTE_GET_COLUMN_DATE (
    dblink_name       IN VARCHAR,
    statement_id      IN BIGINT,
    column_number     IN INTEGER );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID to be executed
column_number	Input	INTEGER	The order of columns within the row. Starts from 1.

Return Value#

The value of the specified column is returned.

REMOTE_NEXT_ROW#

This function moves the row position to the next position in the result set when the SELECT statement has been executed with the REMOTE_EXECUTE_STATEMENT function. This function can only be used within stored procedures and stored functions.

Syntax#

INTEGER REMOTE_NEXT_ROW (
    dblink_name        IN VARCHAR,
    statement_id       IN BIGINT );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID to be executed

Return Value#

The returner of a negative number indicates failure; otherwise, success.

Example#

Please refer to the examples of REMOTE_ALLOC_STATEMENT.

REMOTE functions Supportive of Batch#

This section will elucidate REMOTE functions and other related functions in order to facilitate Altibase database link to execute batch process . The remote functions supportive of batch can be used within stored procedures, and the following sequence is recommended when executing a query in the remote server with batch processing.

REMOTE_ALLOC_STATEMENT_BATCH
REMOTE_BIND_VARIABLE_BATCH
REMOTE_ADD_BATCH
REMOTE_EXECUTE_BATCH
REMOTE_GET_RESULT_COUNT_BATCH
REMOTE_GET_RESULT_BATCH
REMOTE_FREE_STATEMENT_BATCH

IS_ARRAY_BOUND#

This function confirms if array variables are bound or not.

Syntax#

BOOLEAN variable: = IS_ARRAY_BOUND ();

Parameters#

None

Return Value#

TRUE is returned if array variables are bound, but otherwise it FALSE is returned.

Example#

\<Query> Create a stored procedure inserting values to table t1 that the remote server link1 indicates. Use the parameter binding when inserting.

iSQL> CREATE OR REPLACE PROCEDURE PROC1( A1 IN INTEGER, A2 IN INTEGER )
AS
    result INTEGER;
    i      INTEGER;
    count  INTEGER;
    sum    INTEGER := 0;
BEGIN
    PRINTLN('--- 0 ---');
    IF IS_ARRAY_BOUND() = TRUE THEN

    PRINTLN('--- 1 ---');
    /* First bound */
    IF IS_FIRST_ARRAY_BOUND() = TRUE THEN
    pkg1.stmtID := REMOTE_ALLOC_STATEMENT_BATCH( 'link1', 'insert into t1 values(?, ?)');
    PRINTLN('ALLOC BATCH');
    END IF;

    PRINTLN('--- 2 ---');
    /* Main Task */
    /* INSERT INTO T1 VALUES (A1, A2); */

    result := REMOTE_BIND_VARIABLE_BATCH( 'link1', pkg1.stmtID, 1, A1 );
    result := REMOTE_BIND_VARIABLE_BATCH( 'link1', pkg1.stmtID, 2, A2 );
    result := REMOTE_ADD_BATCH( 'link1', pkg1.stmtID );

    /* Last bound */
    IF IS_LAST_ARRAY_BOUND() = TRUE THEN
    result := REMOTE_EXECUTE_BATCH( 'link1', pkg1.stmtID );

    count := REMOTE_GET_RESULT_COUNT_BATCH( 'link1', pkg1.stmtID );
    FOR i IN 1 .. count LOOP
    result := REMOTE_GET_RESULT_BATCH( 'link1', pkg1.stmtID, i );
    sum := sum + result;
    END LOOP;
    PRINTLN('SUM is' || sum);

    result := REMOTE_FREE_STATEMENT_BATCH( 'link1', pkg1.stmtID );
    PRINTLN('FREE BATCH');
    END IF;
    PRINTLN('--- 3 ---');
    ELSE
    PRINTLN('NORMAL?');
    END IF;
END;
/

IS_FIRST_ARRAY_BOUND#

This function verifies whether array variables have been firstly bound or not.

Syntax#

BOOLEAN variable: = IS_FIRST_ARRAY_BOUND ();

Parameters#

None

Return Values#

If array variables were bound and firstly executed, TRUE is returned, but otherwise FALSE is return

Example#

Please refer to IS_ARRAY_BOUND function.

IS_LAST_ARRAY_BOUND#

This function verifies whether array variables have been lastly bound or not.

Syntax#

BOOLEAN variable: = IS_LAST_ARRAY_BOUND ();

Parameters#

None

Return Values#

The function returns TRUE if array variables have been lastly bound, but otherwise it returns FALSE.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_ADD_BATCH#

This function is used to add a SQL statement which will be executed with batch processing.

Syntax#

INTEGER REMOTE_ADD_BATCH (
    dblink_name              IN VARCHAR,
    statement_id             IN BIGINT);

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID

Return Value#

Returning negative numbers indicates failure, otherwise it indicates success of the execution.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_ALLOC_STATEMENT_BATCH#

This function is used for batch processing as well as retrieving an ID of the remote statement (Statement ID) in order to execute the database SQL statement.

Syntax#

BIGINT REMOTE_ALLOC_STATEMENT_BATCH (
    dblink_name        IN VARCHAR,
    statement_text     IN VARCHAR);

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_text	Input	VARCHAR	The SQL statement to execute on a remote server

Return Value#

If succeeded, the statement ID is returned, but if it fails, negative numbers are returned.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_BIND_VARIABLE_BATCH#

This function associates with batch processing, and it binds values to the parameter marker in the SQL statement.

Syntax#

INTEGER REMOTE_BIND_VARIABLE_BATCH (
    dblink_name              IN VARCHAR,
    statement_id             IN BIGINT,
    parameter_number         IN VARCHAR,
    value                    IN VARCHAR);

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of database link object
statement_id	Input	BIGINT	The statement ID
parameter_number	Input	VARCHAR	The parameter number which will be bound in the SQL statement. It begins with 1.
Value	Input	VARCHAR	The binding value

Return Value#

If negative numbers are returned, it indicates the execution has failed, but otherwise it indicates success of the execution.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_EXECUTE_BATCH#

This function is used to actually execute the SQL statement with batch processing in the remote server by using variables of the remote statement ID obtained by the REMOTE_ALLOC_STATEMENT_BATCH function.

Syntax#

INTEGER REMOTE_STATEMENT_BATCH (
    dblink_name              IN VARCHAR,
    statement_id             IN BIGINT );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID

Return Value#

Returning negative numbers indicates failure; otherwise, it indicates success.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_FREE_STATEMENT_BATCH#

This function frees up the executed statement through the batch processing.

Syntax#

INTEGER REMOTE_FREE_STATEMENT_BATCH (
    dblink_name              IN VARCHAR,
    statement_id             IN BIGINT );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID

Return Value#

Returning negative numbers indicates failure, but otherwise it indicates success.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_GET_RESULT_COUNT_BATCH#

This function verifies the number of SQL statements executed by the batch process after implementing the REMOTE_EXECUTE_BATCH function.

Syntax#

INTEGER REMOTE_GET_RESULT_COUNT_BATCH (
    dblink_name              IN VARCHAR,
    statement_id             IN BIGINT);

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID

Return Value#

Returning negative numbers indicates failure; otherwise, it indicates success.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_GET_RESULT_BATCH#

This function confirms the result of SQL statement update executed by batch processing after executing the REMOTE_EXECUTE_BATCH.

Syntax#

INTEGER REMOTE_GET_RESULT_BATCH (
    dblink_name              IN VARCHAR,
    statement_id             IN BIGINT,
    index                    IN INTEGER);

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID
index	Input	INTEGER	The sequence number (index) of SQL statements which are batch processed.

Return Value#

If a negative number is returned, it indicates failure; otherwise, execution result (row count) of a SQL statement indicated by the input parameter index is returned.

Example#

Please refer to IS_ARRAY_BOUND function.

COMMIT FORCE DATABASE LINK#

The occurrence of a network failure on a global transaction can result in a COMMIT failure of a transaction on a remote server. In such occasions, the COMMIT FORCE DATABASE LINK statement forces the available server to COMMIT the transaction, instead of processing the global transaction as a COMMIT failure.

When the DBLINK_GLOBAL_TRANSACTION_LEVEL property is set to '2' (two-phase commit), executing this statement is same as executing COMMIT.

Syntax#

COMMIT FORCE DATABASE LINK;

Example#

iSQL> COMMIT FORCE DATABASE LINK;

ROLLBACK FORCE DATABASE LINK#

The occurrence of a network failure on a global transaction can result in ROLLBACK failure of a transaction on a remote server. In such occasions, the ROLLBACK FORCE DATABASE LINK statement forces the available server to ROLLBACK the transaction, instead of processing the global transaction as a ROLLBACK failure.

When the DBLINK_GLOBAL_TRANSACTION_LEVEL property is set to '2' (two-phase commit), executing this statement is same as executing ROLLBACK.

Syntax#

ROLLBACK FORCE DATABASE LINK;

Example#

iSQL> ROLLBACk FORCE DATABASE LINK;