Previous Topic

Next Topic

Register as Link with Refresh StatementóRefresh Registration

Schema information in local tables can change often. Such information may relate to storage structure, unique keys, secondary indexes, number of columns, and statistics.

When schema or related table information changes in a local database that is part of a distributed database, the Ingres Star catalogs must be updated. Except for row and page counts, this is not done automatically. It must be done with the register as link with refresh statement.

For Ingres Star to take advantage of performance tuning at the local database level, tables affected should be refreshed using the register as link with refresh statement. This allows Ingres Star to use the latest table structure and secondary indexes when determining distributed query execution plans.

The register as link with refresh statement has the following format:

register object_name as link with refresh

Previous Topic

Next Topic

Register as Link with Refresh Restrictions

Register as link with refresh refreshes all the information about a table or view in the Ingres Star catalogs with information from the local database.

Views built on top of the table or view are unaffected. The table or view retains the same table_id or view_id.

The register with refresh statement is not allowed in the following circumstances (an error message is generated):

Register with refresh may be used on objects registered by the register statement or implicitly registered by Ingres Star. A table or view refreshed in this way retains the same table_subtype value in the iitables catalog, that is, it remains registered native if originally registered native. (A native object means the object was created through Ingres Star rather than merely registered through Ingres Star.)

Previous Topic

Next Topic

Effects of Register as Link with Refresh

Several situations may arise that will require you to refresh the registration of a local DBMS object.

Where the old schema of the local table is identical to, or is a subset of, the new schema (for example, when a new column is added to the old local table and the table's creation time remains unchanged), register as link with refresh has the following effects:

Where the local table has been dropped and recreated (shows a new creation time) using the same schema, possibly with new columns at the end, register as link with refresh has the following effect:

Where the old schema of the local table is different from the new schema, for example, a column has been dropped or a column type has been redefined, then register as link with refresh is not allowed. An error is returned to the user.

Previous Topic

Next Topic

Example: Register as Link with Refresh

The following example shows the use of register as link with refresh:

register west_prospects as link
from prospects;

create view leads as
select * from west_prospects;

/* Initiate connection to coordinator database */
direct connect;
drop prospects;

/* recreate with the same schema but with an additional column */
create table prospects;

/* return control to Ingres Star */
direct disconnect;

register west_prospects as link with refresh;

/* view created above */
select * from leads;

The result set from the view selection will show the original schema of the prospects table. The original target from the view is used for selection, and that target list contains only information from the old schema. However, a select on the registered table west_prospects results in a set of rows under the new schema.

For detailed descriptions of the direct connect/disconnect statements, see Connecting Directly to a Local Database.

© 2007 Ingres Corporation. All rights reserved.