Understanding Ingres Star Catalogs

This chapter describes the Ingres Star system catalogs.

Previous Topic

Next Topic

Ingres Star 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:

Previous Topic

Next Topic

iidbdb Catalogs

Ingres Star uses four catalogs in the iidbdb:

Previous Topic

Next Topic

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.

Previous Topic

Next Topic

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

Previous Topic

Next Topic

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

Previous Topic

Next Topic

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.

Previous Topic

Next Topic

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.

Previous Topic

Next Topic

Standard Catalogs

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

Previous Topic

Next Topic

System Catalogs

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


© 2007 Ingres Corporation. All rights reserved.