This chapter describes the Ingres Star system catalogs.
|
|
|
Ingres Star catalogs consist of database tables that describe the objects in the distributed database. They are maintained to keep track of these objects. They are primarily for Ingres Star's own use, but you can use them in programs and applications to access (but not update) information about the distributed database.
Each catalog has columns—or attributes—with specific database management functions and rows that reflect different aspects of the database.
There are three types of Ingres Star catalogs:
|
|
|
Ingres Star uses four catalogs in the iidbdb:
|
|
|
The iidatabase catalog in the iidbdb is used to determine if a given database exists in the installation. This catalog has a column dbservice that is used to determine whether or not the given database is distributed, and in the case of a local database, whether it is a coordinator database.
|
|
|
The iistar_cdbs catalog in the iidbdb is used to store the identities and locations of coordinator databases associated with each distributed database.
This catalog contains an entry for the coordinator database associated with each distributed database. It is used by Ingres Star to determine the identity and residence of the associated coordinator database when a distributed database is invoked.
Column Name |
Data Type |
Description |
|---|---|---|
ddb_name |
char(32) |
Name of distributed database |
ddb_owner |
char(32) |
Owner of distributed database |
cdb_name |
char(32) |
Name of coordinator database |
cdb_node |
char(32) |
Name of the coordinator database's node |
cdb_owner |
char(32) |
Owner of the coordinator database |
cdb_dbms |
char(32) |
Server of coordinator database, for example, INGRES, DB2 |
schema_desc |
char(32) |
Reserved for future use |
create_date |
char(25) |
Date when coordinator database was added |
original |
char(8) |
Reserved for future use |
cdb_id |
integer4 |
Contains a unique database identifier corresponding to iidatabase.db_id for the coordinator database entry |
cdb_capability |
integer 4 |
Reserved for future use |
|
|
|
The iistar_cdbinfo catalog provides maps between the distributed database and its underlying coordinator database. It indicates on which node the database was created, who owns it, and when it was created.
This catalog exists only in the iidbdb, not in all distributed databases. This catalog is read-only; you cannot update it.
Column Name |
Data Type |
Description |
|---|---|---|
ddb_name |
char(32) |
Name of distributed database |
ddb_owner |
char(32) |
Owner of distributed database |
cdb_name |
char(32) |
Name of coordinator database |
cdb_node |
char(32) |
Name of the coordinator database's node |
cdb_owner |
char(32) |
Owner of the coordinator database |
cdb_dbms |
char(32) |
Server of coordinator database, for example, INGRES, DB2 |
cdb_create_date |
char(25) |
Date when coordinator database was added |
|
|
|
The iiddb_netcost catalog in the iidbdb is used to weigh the relative network costs of a transaction in order to compute the best query execution plan (QEP). Data in this catalog is used by Ingres Star's distributed optimizer.
Column Name |
Data Type |
Description |
|---|---|---|
net_src |
char(32) |
Name of the source node |
net_dest |
char(32) |
Name of the destination node |
net_cost |
float8 |
Cost of moving one byte from the source node to the destination node as a multiple of 1 DIO (Disk I/O). This field contains a float that is the cost of transferring one byte from the source to the destination site. This cost should be made in terms of DIO units. Network costs are added to DIO costs in order to determine which plan is cheapest. See the example below. |
net_exp1 |
float8 |
Expansion field (should be set to zero) |
net_exp2 |
float8 |
Expansion field (should be set to zero) |
All data transfers are made by first transferring the data to the Star Server from the source site, then transferring data from the Star Server to the destination site. As a result, the only entries in the iiddb_netcost table that will be useful are those that include the Star Server node name as one of the sites.
Note: The StarView utility does not allow users to populate the iiddb_netcost catalog. If your configuration contains greatly differing network costs and you wish to provide network cost information to Ingres Star, you must do so manually.
To make inserts and updates into the iiddb_netcosts catalog, you must be a privileged user and log in as the installation owner. At the operating system prompt, enter:
UNIX:
sql iidbdb '-u$ingres' +U
VMS:
sql iidbdb -u$ingres "+U"
The Star Server must be restarted for the new iiddb_netcosts values to take effect.
When you make changes to the iiddb_netcost catalog, you can analyze differences in query plan strategies by using the set qep statement. The network or N costs are printed in the last line of each node in the query plan.
For further details on query execution plans, see the Database Administrator Guide.
|
|
|
Assume that the Star Server is located on a node named sanfrancisco (using lower case is the default), and that the remote sites are named newyork and washington.
Some of the entries in iiddb_netcost could be:
net_src char(32) |
net_dst char(32) |
net_cost (f8) |
|---|---|---|
sanfrancisco |
newyork |
0.001 |
sanfrancisco |
washington |
0.002 |
sanfrancisco |
sanfrancisco |
0.0002 |
Note that there is an entry in which sanfrancisco is both the source and destination sites. This represents transfers of data from the Star Server to and from local databases on the Star Server site (including the coordinator database), but these costs are relatively low.
Also, typically, the same cost applies for either direction so that if only one row exists between two nodes, the other direction is assumed to be the same cost.
For example, suppose 10000 bytes is to be transferred from newyork to washington. The data is routed through sanfrancisco since the Star Server exists on that node. The cost of transferring from newyork to sanfrancisco is 0.001*10000=10 units, and the cost from sanfrancisco to washington is 0.002*10000=20 units, so the total cost is 30 units. If there were 20 disk I/Os and 2 CPU units involved, the total cost would be 52 units.
|
|
|
The standard catalogs let you get information from the system catalogs, which may not be queried directly. For details on the standard catalogs, see the appendix "Standard Catalog Interface."
Important! Users and user applications may query the standard catalogs but may not update them.
The standard catalogs provided with the current release are identical in specification and function to the Ingres 2.6 standard catalogs, except as noted in the following table:
Catalog Name |
Exceptions to Release 2.6 Catalog |
|---|---|
iialt_columns |
None |
iicolumns |
None |
iidbcapabilities |
Has the additional entries: OWNER_NAME, STANDARD_CATALOG_LEVEL, OPEN/SQL_LEVEL, DB_DELIMITED_CASE, and DB_REAL_USER_CASE. |
iidbconstants |
Has the added column system_owner |
iihistograms |
None |
iiindexes |
None |
iiindex_columns |
None |
iiintegrities iimulti_locations iipermits |
Not used. Ingres Star does not support permits or integrities. For multiple locations, partitioning across several locations is managed by the autonomous local database server. You may query these catalogs, but Ingres Star never populates them with data, so your answer will always be a null set of rows. If you want permit, integrity or location information about a registered object, do a direct connect to the appropriate local database and query the local database's standard catalogs. |
iiphysical_tables |
None |
iiprocedures |
None |
iiregistrations |
None |
iiregistered_objects |
An Ingres Star-only standard catalog |
iistats |
Has the added columns: column_domain, is_complete, stat_version, hist_data_length. |
iitables |
Column location name is 32 characters long, not 24. |
iiviews |
None |
|
|
|
System catalogs store specific information for Ingres Star. These catalogs are used to get the information needed to operate on distributed objects in the database. Users may not query these catalogs directly. The standard catalog interface described in the appendix, "Standard Catalog Interface" lets you access data from the system catalogs.
The table names of some Ingres Star system catalogs can be used as arguments to the sysmod command, but these tables are not supported for any other use. The following Ingres Star system catalogs are legal targets for sysmod:
Catalog |
Description |
|---|---|
iiddb_dbdepends |
Dependency tree for a distributed view |
iiddb_ldbids |
Contains information about each local database known to the distributed database |
iiddb_ldb_columns |
Map of local table's column names if the table is registered with user-supplied aliases to the column names |
iiddb_ldb_dbcaps |
Contains capability data on each local database known to the distributed database |
iiddb_long_ldbnames |
Contains the full local database name (if it exceeds 32 characters) and the alias to Ingres Star's 32-character name |
iiddb_object_base |
Used to generate a unique identifier |
iiddb_objects |
Describes distributed objects known to the distributed database |
iiddb_tableinfo |
Data on the underlying (local database) objects for distributed objects |
iiddb_tree |
Used to store Ingres Star-generated trees, such as view definitions |
The following Ingres Star system catalogs exist in a distributed database, but these catalogs are not legal targets for sysmod:
Catalog |
Description |
iiddb_dxldbs |
List of local databases involved in a Ingres Star two-PC transaction |
iiddb_dxlog |
Log of an Ingres Star two-PC transaction |
iiddb_xdxlog |
Secondary index on iiddb_dxlog |