Was this helpful?
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.
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
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
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.
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:
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.
Last modified date: 08/28/2024