Previous Topic

Next Topic

Register as Link Statement—Define Database Objects to Ingres Star

The register as link statement defines a distributed database component name to Ingres Star as an alias of a local or remote table or view. The statement is stored in the iiregistrations catalog.

Use the register as link statement to define to Ingres Star:

Note: To register non-SQL data, you first must identify the data to the non-SQL Enterprise Access with the register as import statement. For information on how to use this statement, see the guide for your specific Enterprise Access.

The register as link statement has the following format:

register object_type object_name
       [(col_name {, col_name})]
       as link
       [from [local_owner_name.]local_object_name]
              [with
                     [node = node_name,
                     database = database_name]
                     [, dbms = server_class]]

Previous Topic

Next Topic

Register Table as Link Statement—Define Table to Ingres Star

The register table as link statement defines an already existing local DBMS table to Ingres Star. No new table is created. All secondary indexes associated with the table are registered automatically when you register a table in your distributed database if the table is in a local database.

The register table as link statement has the following format:

register [table] table_name
       [(col_name {, col_name})]
       as link
       [from [local_owner_name.]local_table_name]
       [with
              [node = node_name,
              database = database_name]
              [, dbms = server_class]]

The elements that are common to all three register as link statements are described in Register as Link Statement.

Previous Topic

Next Topic

Examples: Register Table as Link

Consider the following database configurations, used in examples in this and following sections:

The following statement issued in a session with the distributed database corporateddb registers the prospects table from the coordinator database in corporateddb:

register prospects as link;

This statement registers prospects in corporateddb under the name west_prospects:

register west_prospects as link
  from prospects;

This statement registers the table, sales from the database west_usa on the nod, reno, giving it the name usa_sales and referring to its columns as customer, inv_no and amount:

register table usa_sales
  (customer, inv_no, amount) as link
  from john.sales
  with node = reno, database = west_usa;

If the distributed database corporateddb is case sensitive and you wish to create registrations with case-sensitive names, or if you wish to include special characters in the registration name, you would use a delimited identifier to specify the registration. The following example shows how you would register a table named usa sales into corporateddb:

register table "usa sales"
  (customer, "inv no", amount) as link
  from john.sales
  with node = reno, database = west_usa;

If the west_usa database allows mixed-case identifiers, or if the local table name includes mixed-case or special characters, you would use a delimited identifier in the from clause. For example, john's table may be named Sales.

register table usa_sales
  (customer, inv_no, amount) as link
  from john."Sales"
  with node = reno, database = west_usa;

Note: You can use a delimited identifier on the from clause only if the case-translation semantics of both the distributed database and the local database are compatible. Ingres Star rejects the above statement if corporateddb does not support mixed-case identifiers and west_usa does support them. The reason for this is that the identifier Sales will be case converted by the Star Server, and any mixed-case characters will have been converted before the registration is processed. In this instance, you would use single quotes. The Star Server does not translate singly-quoted strings. For example:

register table usa_sales
  (customer, inv_no, amount) as link
  from john."Sales"
  with node = reno, database = west_usa;

Previous Topic

Next Topic

Register View as Link Statement—Define View to Ingres Star

The register view as link statement defines an already existing local DBMS view to Ingres Star. No new view is created.

The register view as link statement has the following format:

register [view] view_name

         [(col_name {, col_name})]

         as link

         [from [local_owner_name.]local_view_name]

         [with

                  [node =node_name,

                  database =database_name]

                  [, dbms = server_class]]

The elements that are common to all three register as link statements are described in Register as Link Statement.

Previous Topic

Next Topic

Example: Register View as Link

This statement registers the view follow_ups from the database west_usa on the node reno, and gives it the name usa_visits:

register view usa_visits as link

  from follow_ups

  with node = reno, database = west_usa;

Previous Topic

Next Topic

Register Procedure as Link Statement—Define Procedure to Ingres Star

The register procedure as link statement defines an already existing local DBMS database procedure to Ingres Star. No new database procedure is created.

The register procedure as link statement has the following format:

register procedure procedure_name

         as link
         [from [local_owner_name.]local_procedure_name]
         [with
                  [node = node_name,
                  database = database_name]
                  [, dbms = server_class]]

The elements that are common to all three register as link statements are described in Register as Link Statement.

Previous Topic

Next Topic

Examples: Register Procedure as Link

This statement registers LDB1 procedure p2 (on node node_A) using the same name in Ingres Star:

register procedure p2 as link
  with node=node_A, database=LDB1;

This statement registers LDB1 procedure p3 using the name proc2 in Ingres Star:

register procedure proc2 as link from p3
  with node=node_a, database=LDB1;

Previous Topic

Next Topic

Catalogs for the Register Statement

The text for the register as link statement is stored in the iiregistrations standard catalog.

Previous Topic

Next Topic

Execute Immediate Statement--Execute Register as Link Statement Dynamically

You can execute the register statement through dynamic SQL by the execute immediate statement.

For example, the following command registers the table customers from the database pacific on the node tokyo:

exec sql execute immediate
  'register customers as link
  with node = tokyo, database = pacific';

Note: Do not use the prepare statement for executing the register as link statement. Use execute immediate as described here.

© 2007 Ingres Corporation. All rights reserved.