Data Dictionary Tables

This appendix lists the tables in the data dictionary. The system catalogs are listed in alphabetical order, followed by the base, archive, shadow tables, and internal tables.

System Catalogs

The system catalog tables in the data dictionary as as follows:

dd_cdds
dd_databases
dd_db_cdds
dd_distrib_queue
dd_input_queue
dd_paths
dd_mail_aler
dd_regist_columns
dd_regist_paths
dd_servers

dd_cdds Table

The dd_cdds table defines each Consistent Distributed Data Set (CDDS) in the replicated environment:

Column Name	Data Type	Description
cdds_no	smallint not null	Numeric identifier assigned by the user to the CDDS. The range is 0-32,767.
cdds_name	char(32) not null	The name assigned by the user to uniquely identify the CDDS.
collision_mode	smallint not null with default	Indicates the method used to handle collision conflicts in the CDDS. Values are: 0 = PassiveDetection 1 = ActiveDetection 2 = BenignResolution 3 = PriorityResolution 4 = LastWriteWins
error_mode	smallint not null with default	Indicates the method used to handle errors detected while transmitting replicated information within the CDDS. Values are: 0 = SkipTransaction 1 = SkipRow 2 = QuietCDDS 3 = QuietDatabase 4 = QuietServer
control_db	smallint not null with default	Reserved for future use.

dd_databases Table

The dd_databases table defines each database in the replicated environment:

Column Name	Data Type	Description
database_no	smallint not null	Numeric identifier assigned by the user to uniquely identify the database in the replicated environment. The range is 1-32,767.
vnode_name	char(32) not null	Ingres Net virtual node where the database is located.
database_name	char(32) not null	The name of the database.
database_owner	char(32) not null with default	The name of the owner of the database.
dbms_type	char(8) not null with default	The type of database management system servicing the database, for example, Ingres, Datacom/DB, DB2.
security_level	char(2) not null with default	The security level implemented in the database. Values are Blank, C2, and B1.
local_db	smallint not null with default	Indicates this is the local database. Only one row in this table can have a 1 in this column.
config_changed	char(25) not null with default	The last date and time that a configuration change was made that affected this database.
remark	varchar(80) not null with default	Comments about the database.

dd_db_cdds Table

The dd_db_cdds table defines which CDDSs are present in which database:

Column Name	Data Type	Description
database_no	smallint not null	Identifies a database in the replicated environment.
cdds_no	smallint not null	Identifies a CDDS present in the database.
target_type	smallint not null	Indicates the expected replication behavior of the CDDS in the database. Values are: 1 = Full peer 2 = Protected read-only 3 = Unprotected read-only
is_quiet	smallint not null with default	Indicates that transmission of replicated information to the CDDS or the database is temporarily disabled, typically because of collisions or errors encountered.
server_no	smallint not null with default	Identifies the Replicator Server, if any, that propagates changes to the CDDS at the database.

dd_distrib_queue Table

The dd_distrib_queue table contains the queue of operations (insert, update, delete) that are to be replicated to target databases. The queue is populated by distribution threads and manipulated by one or more Replicator Servers:

Column Name	Data Type	Description
targetdb	smallint not null	Identifies the database to which the transaction must be replicated.
sourcedb	smallint not null	Identifies the database where the transaction originated.
transaction_id	integer not null	Identifier given by the DBMS Server to the original transaction.
sequence_no	integer not null	The sequence of the operation (insert, update, delete) within the original transaction.
trans_type	smallint not null	Indicates whether this operation is an: 1 = insert 2 = update 3 = delete
table_no	smallint not null	Identifies the table changed by the original transaction.
old_sourcedb	smallint not null	Identifies the database that originated the transaction that manipulated the row before this transaction.
old_transaction_id	integer not null	Identifier given by the DBMS Server to the transaction that manipulated the row before this transaction.
old_sequence_no	integer not null	The sequence of the operation (insert, update, delete) within the transaction that manipulated the row before this transaction.
trans_time	date with null	The date and time when the transaction that manipulated the row was committed in the originating database.
cdds_no	smallint not null with default	The numeric identifier assigned to the CDDS. This can come directly from dd_regist_tables or indirectly from a lookup table.
dd_priority	smallint not null with default	The user-assigned priority to resolve collisions. This can be 0 or have been derived from a lookup table.

dd_input_queue Table

The dd_input_queue table contains the queue of operations (insert, update, delete) that are entering this database. The operations can come from a user interface on this database or from a Replicator server on another database:

Column Name	Data Type	Description
sourcedb	smallint not null	Identifies the database where the transaction originated.
transaction_id	integer not null	Identifier given by the DBMS Server to the original transaction.
sequence_no	integer not null	The sequence of the operation (insert, update, delete) within the original transaction.
table_no	smallint not null	Identifies the table changed by the original transaction.
old_sourcedb	smallint not null	Identifies the database that originated the transaction that manipulated the row before this transaction.
old_transaction_id	integer not null	Identifier given by the DBMS Server to the transaction that manipulated the row before this transaction.
old_sequence_no	integer not null	The sequence of the operation (insert, update, delete) within the transaction that manipulated the row before this transaction.
trans_time	date with null	The date and time when the transaction that manipulated the row was committed in the originating database.
trans_type	smallint not null	Indicates whether this operation is an: 1 = insert 2 = update 3 = delete
cdds_no	smallint not null with default	The numeric identifier assigned to the CDDS. This can come directly from dd_regist_tables or indirectly from a lookup table.
dd_priority	smallint not null with default	The user-assigned priority to resolve collisions. This can be 0 or can have been derived from a lookup table.

dd_mail_alert Table

The dd_mail_alert table lists the users to be alerted by e-mail if a Replicator server in this database reports an error:

Column Name	Data Type	Description
mail_username	varchar(80) not null	The e-mail address of a user that needs to be notified by e-mail.

dd_paths Table

The dd_paths table defines the paths to be taken by replicated information in a CDDS from the originating (source) database to a final target database. On each database, this table specifies where to propagate a change, based on the database where it originated:

Column Name	Data Type	Description
cdds_no	smallint not null	Identifies the CDDS.
localdb	smallint not null	Identifies the current database in the propagation path.
sourcedb	smallint not null	Identifies the database where the change originated.
targetdb	smallint not null	Identifies the next database to which the change must be propagated.
final_target	smallint not null	Indicates that the target database is the final target in this path. This column is reserved for future use.

dd_regist_columns Table

The dd_regist_columns table defines the columns of the tables that have been registered for replication:

Column Name	Data Type	Description
table_no	smallint not null	Numeric identifier assigned to the table by the configuration process.
column_name	char(32) not null	The name of the column.
column_sequence	integer not null	Indicates the sequence of the column in the base table. If the column is not replicated, its sequence is 0 (zero).
key_sequence	smallint not null with default	The order of the column in the unique key used for replication. If the value is 0, this column is not part of the key.

dd_regist_tables Table

The dd_regist_tables table shows the tables that have been registered with the local database:

Column Name	Data Type	Description
table_no	integer not null	Numeric identifier assigned to the table by the configuration process.
table_name	char(32) not null	The name of the user table.
table_owner	char(32) not null	The table owner's user name.
columns_registered	char(25) not null with default	The last date and time the column or key registration was changed.
supp_objs_created	char(25) not null with default	The last date and time support objects were created.
rules_created	char(25) not null with default	The last date and time that change recording was activated.
cdds_no	smallint with null	Identifies the CDDS to which this table belongs.
cdds_lookup_table	char(32) not null with default	The name of the lookup table to be used for partitioning this table horizontally.
prio_lookup_table	char(32) not null with default	The name of the priority lookup table to be used for resolving collisions by priority.
index_used	char(32) not null with default	The name of the storage structure (primary or secondary index) containing the unique key columns used for replication.

dd_servers Table

The dd_servers table defines the Replicator Servers:

Column Name	Data Type	Description
server_no	smallint not null	The numeric identifier for a server.
server_name	varchar(24) not null	The name of the server.
pid	varchar(12) not null with default	The operating system process identifier for a running server.

Base Table

The base table is an example of a replicated table that documents the replicated fields:

Column Name	Data Type	Description
column1	integer not null	Primary key of a replicated table.
column2	varchar(20) not null	Other attribute of a replicated table.

Archive Table

The archive table contains all the replicated columns in the base table, and the replicated transaction key, which is made up of the following information:

Database number of the originating database
Transaction ID
Transaction sequence number

Each row in the archive table reflects a row that was in the shadow table.

This is an example of a replicated archive table:

Column Name	Data Type	Description
column1	integer not null	Primary key of a replicated table.
column2	varchar(20) not null	Other attribute of a replicated table.
sourcedb	smallint not null	Identifies the database where the transaction originated.
transaction_id	integer not null	Identifier given by the DBMS Server to the original transaction.
sequence_no	integer not null	The sequence of the operation (insert, update, delete) within the original transaction.

Shadow Table

A shadow table has all the primary key columns of the base replicated table plus other Ingres Replicator columns. This table contains information that is needed for the replication—the timestamp indicating when the row was manipulated and a priority number indicating the processing priority of the row in the event of collision:

Column Name	Data Type	Description
column1	integer not null	Primary key of a replicated table.
sourcedb	smallint not null	Identifies the database where the transaction originated.
transaction_id	integer not null	Identifier given by the DBMS Server to the original transaction.
sequence_no	integer not null	The sequence of the operation (insert, update, delete) within the original transaction.
trans_time	date with null	The date and time when the transaction that manipulated the row was committed in the originating database.
distribution_time	date with null	The time of final distribution to a local database.
in_archive	integer1 not null with default	Indicates whether this row is in the archive table.
cdds_no	smallint not null with default	Identifies the CDDS.
trans_type	smallint not null	Indicates if this operation is an: 1 = insert 2 = update 3 = delete
dd_priority	smallint not null with default	The user-assigned priority to resolve collisions. This can be 0 or have been derived from a lookup table.
new_key	smallint not null with default	This field is set to one for an update or delete if a key had to be created for the original record.
old_sourcedb	smallint not null with default	Identifies the database that originated the transaction that manipulated the row before this transaction.
old_transaction_id	integer not null with default	Identifier given by the DBMS Server to the transaction that manipulated the row before this transaction.
old_sequence_no	integer not null with default	The sequence of the operation (insert, update, delete) within the transaction that manipulated the row before this transaction.

Internal Tables

Ingres Replicator currently uses the following tables internally:

dd_dbms_types
dd_flag_values
dd_events
dd_last_number
dd_option_values
dd_server_flags
dd_support_tables