Using a Distributed Database

This chapter covers statements you use when you want to:

This chapter tells you how to use Ingres Star with:

You use your distributed database the same way you use a single local database. There are, however, some statements you can use only with a distributed database and some statements you cannot use with a distributed database. For a summary of both types of statements, see the appendix "SQL Statement Summary."

Previous Topic

Next Topic

Connecting Directly to a Local Database

While using Ingres Star, you can access an Ingres or Enterprise Access local DBMS directly. This is useful in some processing situations, for example, if you want to modify a table's storage structure, create secondary indexes, or grant other users access to a table.

Once you are in this directly connected or pass-through mode, Ingres Star merely passes through all statements and returns all responses. Statements are sent to the local database, whose server accepts or rejects the statement. When in this pass-through mode, Ingres Star does no syntax checking on the statement.

You can access a local DBMS directly with either of the following statements:

Previous Topic

Next Topic

Direct Connect Statement

The direct connect statement allows you to connect to a local database using Ingres Star in a pass-through mode. You remain in this pass-through mode until you issue a direct disconnect statement.

When you issue a direct connect statement, the Communications Server determines the login account on the remote node connection based on the netutil entries that have been set up. On a direct connect statement, your connection to the local DBMS will be made as the user authorized to access the remote node by netutil. For instance, if user Harry has a private authorization entry that defines Sally as the user for connections to node Italy, when Harry establishes a connection to node Italy from a distributed database, the local user will be Sally. This behavior also occurs when Ingres Star accesses a remote database's data or catalogs.

The direct connect does not always require that a new connection be opened between Ingres Star and the local DBMS. If the session has already caused Ingres Star to open a connection with the local DBMS, then Ingres Star uses that same connection for the direct connect.

You can connect to an Ingres or an Enterprise Access local DBMS with this statement, but not to another Ingres Star DBMS.

If you are within a transaction, you first must commit or rollback your transaction before you can issue a direct connect.

Previous Topic

Next Topic

Direct Connect Syntax

The direct connect statement has the following format:

direct connect
       [with
       [node = node_name,
       database = database_name]
       [, dbms = server_class]]

Previous Topic

Next Topic

Direct Disconnect Statement

The direct disconnect statement enables you to leave the pass-through mode enabled by your previous direct connect.

If you use direct disconnect before committing an active local DBMS SQL transaction, Ingres Star commits the transaction and sends the commit to the local DBMS.

Direct disconnect does not close Ingres Star's session with the local DBMS. Any state that you set up while directly connected remains in place and may cause side effects in Ingres Star's session with the local DBMS. Therefore, you must reset any session parameters (such as set statements) established during direct connect mode before issuing the direct disconnect. For example, if you issue a set autocommit on statement during a direct connect session, you must issue a set autocommit off before you issue the direct disconnect.

Previous Topic

Next Topic

Direct Disconnect Syntax

The direct disconnect statement has the following format:

direct disconnect

Previous Topic

Next Topic

Example: Direct Connect and Direct Disconnect

To place an integrity on an Ingres Star-level table, use the direct connect statement to define the integrity at the local level since an Ingres Star-level table actually resides in a local database.

create table employee (name char(100),
 dept integer, salary money)
   with node=remote1, database=mydb;
commit;
direct connect with node=remote1,
 database=mydb;
create integrity on employee is salary>0;
direct disconnect;

Previous Topic

Next Topic

Direct Execute Immediate Statement

Use the direct execute immediate statement to send a local DBMS-specific statement to the local DBMS.

Ingres Star assumes that the statement being sent is an update operation to the local database. Ingres Star uses the two-phase commit protocol if the transaction involves an update to at least one other site.

The direct execute immediate statement has the following format:

direct execute immediate 'string_constant'
 [with
 [node = node_name,
 database = database_name]
 [, dbms =server_class]]

The with clause enables you to specify the node, database, and type of server to which you want to connect. No other with clauses are allowed when presented to Ingres Star.

Previous Topic

Next Topic

Example: Direct Execute Immediate

The following example illustrates how you send a create integrity statement to a local DBMS to be executed using direct execute immediate:

create table employee (name char(100),
 dept integer, salary money)
   with node=remote1, database=mydb;
direct execute immediate 'create integrity
 on employee is salary>0'
   with node=remote1, database=mydb;

Previous Topic

Next Topic

Direct Execute Immediate Statement Process

When the direct execute immediate statement is presented to Ingres Star, it strips off the direct keyword, the with clauses, and the quotes around the string_constant and sends the following statement to the local DBMS:

execute immediate string_constant

The local DBMS, possibly an Enterprise Access, strips off the execute immediate and then parses the query represented by string_constant.

If the query is a legal query for the execute immediate statement, it is executed. If the query is illegal, an error is returned.

Previous Topic

Next Topic

Illegal Direct Execute Immediate Statements

The following statements are not allowed for direct execute immediate:

Previous Topic

Next Topic

Avoiding Execute Errors During Two-Phase Commit

It has already been pointed out that the transaction statements commit and rollback are illegal to use for direct execution in an Ingres Star session. These statements must not be passed through Ingres Star for execution on a remote non-distributed database or Enterprise Access because these statements can interfere with two-phase commits. This means that you must not attempt to implement remotely through Ingres Star:

Ingres Star, under the latest release of Ingres, detects such errors and returns an error message without performing the operation. However, earlier versions of Ingres serving the remote non-distributed database or Enterprise Access may not detect such errors.

Important! If a user's commit or rollback statement is passed through Ingres Star and executed by an earlier release of Ingres during two-phase commit, the two-phase commit protocol may be disrupted and could corrupt the database.

Previous Topic

Next Topic

Direct Connect and Direct Execute Immediate Compared

Although the direct connect and direct execute immediate statements both access local DBMSs, they operate in different ways.

The direct connect statement:

The direct execute statement:


© 2007 Ingres Corporation. All rights reserved.