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.
Note: Ingres Star does not support ALTER TABLE RENAME or RENAME TABLE statements for renaming tables and columns. If a table is registered in Star and it is renamed in the local database, any SELECT statements from the old table name will fail with error E_US0845 table does not exist. Similarly, if a column of a registered table is renamed in the local database, any SELECT statements involving that table column will fail with error E_US0834 table does not contain the column specified. Table and column names are recorded in the iiregistrations catalog, which you can access with the StarView utility. If you renamed a table or column registered in Ingres Star using one of these commands, you must drop the old table and register the new table name with the Ingres Star database using StarView.
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]]
table
Is an optional object type identifier. Whether or not you specify table, Ingres Star queries the local DBMS and determines the object type.
If you specify table, Ingres Star issues an error if the local DBMS type is not a table.
table_name
Is the Ingres Star name of the local DBMS table you are registering.
It can be delimited with double quotes to preserve case and allow special characters. However, if the Ingres Star database does not support mixed-case delimited identifiers and the local DBMS or Enterprise Access product does support mixed-case delimited identifiers, you should use a single-quote delimiter in order for Ingres Star to preserve the case.
This name must follow Ingres naming conventions. It will appear in the Ingres Star catalogs as a table. In the table_subtype column of the iitables standard system catalog, it will have a subtype of L (registered as Link). For information, see the chapter "Understanding Ingres Star Catalogs."
local_table_name
Is the name of the table in the local DBMS.
It can be delimited with double quotes to preserve case and allow special characters. However, if the Ingres Star database does not support mixed-case delimited identifiers and the local DBMS or Enterprise Access product does support mixed-case delimited identifiers, you should use a single-quote delimiter in order for Ingres Star to preserve the case.
The default is to use the distributed database table_name you specify as the name of the local DBMS table that you are registering.
Enter this name if you are registering the table in your distributed database with a name different from its name in the local DBMS or if the table name in the local DBMS does not follow naming conventions and/or is case sensitive or is owned by another user.
Note: This table name must be the actual base table name, not a synonym.
The elements that are common to all three register as link statements are described in
Register as Link Statement--Define Database Objects to Ingres Star.
Examples: Register Table as Link
Consider the following database configurations, used in examples in this and following sections:
• The distributed database corporateddb resides on the london node with its coordinator database corporate. The table, prospects, has been created locally in corporate.
• A remote database pacific is on a node tokyo and contains a table, customers.
• A second remote database west_usa resides on the node reno and contains a table, sales and a view, follow_ups. The table, sales belongs to user john and has columns customer, invoice_number and total.
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;