iidbdb Catalogs
Ingres Star uses four catalogs in the iidbdb:
• iidatabase
• iistar_cdbs
• iistar_cdbinfo
• iiddb_netcost
The iidatabase Catalog
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
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.
The iistar_cdbinfo Catalog
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.
The iiddb_netcost Catalog
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.
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.
Example: Net_cost
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:
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.
Last modified date: 08/28/2024