SQL Remote system tables

Data Replication with SQL Remote
PART 4. Reference
CHAPTER 15. System Objects for Adaptive Server Anywhere

SQL Remote system tables

This section describes the system tables used by SQL Remote to define and manage SQL Remote information.

SYSARTICLE table

Function

Each row describes an article in a SQL Remote publication.

Columns

Column

Data type

Description

publication_id

SMALLINT

The publication of which this article is a part.

table_id

INT

Each article consists of columns and rows from a single table. This column contains the table ID for this table.

where_expr

VARCHAR(128)

For articles that contain a subset of rows defined by a WHERE clause, this column contains the search condition.

subscribe_by_expr

VARCHAR(128)

For articles that contain a subset of rows defined by a SUBSCRIBE BY expression, this column contains the expression.

Column	Data type	Description
publication_id	SMALLINT	The publication of which this article is a part.
table_id	INT	Each article consists of columns and rows from a single table. This column contains the table ID for this table.
where_expr	VARCHAR(128)	For articles that contain a subset of rows defined by a WHERE clause, this column contains the search condition.
subscribe_by_expr	VARCHAR(128)	For articles that contain a subset of rows defined by a SUBSCRIBE BY expression, this column contains the expression.

SYSARTICLECOL table

Function

Each row identifies a column in an article, identifying the column, the table it is in, and the publication it is part of.

Columns

Column

Data type

Description

publication_id

SMALLINT

A unique identifier for the publication of which the column is a part.

table_id

INT

The table to which the column belongs.

column_id

INT

The column identifier, from the SYSCOLUMN system table.

Column	Data type	Description
publication_id	SMALLINT	A unique identifier for the publication of which the column is a part.
table_id	INT	The table to which the column belongs.
column_id	INT	The column identifier, from the SYSCOLUMN system table.

SYSPUBLICATION table

Function

Each row describes a SQL Remote publication.

Columns

Column

Data type

Description

publication_id

SMALLINT

publication_name

VARCHAR(128)

Column	Data type	Description
publication_id	SMALLINT
publication_name	VARCHAR(128)

SYSREMOTETYPE table

Function

Each row describes one of the SQL Remote message types, including the publisher address.

Columns

Column

Data type

Description

type_id

SMALLINT

An identification number for the message type.

type_name

VARCHAR(128)

The message type. There is a separate row for each of the following:

FILE

MAPI

VIM

SMTP

publisher_address

VARCHAR(128)

The publisher's address for the message type type_name.

Column	Data type	Description
type_id	SMALLINT	An identification number for the message type.
type_name	VARCHAR(128)	The message type. There is a separate row for each of the following: FILE MAPI VIM SMTP
publisher_address	VARCHAR(128)	The publisher's address for the message type type_name.

SYSREMOTEUSER table

Function

Each row describes a user ID with REMOTE permissions (a subscriber), together with the status of SQL Remote messages sent to and from that user.

Columns

Column

Data type

Description

user_id

SMALLINT

The user ID of the user with REMOTE permissions.

operation

VARCHAR(10)

consolidate

CHAR(1)

The column contains either an N to indicate a user granted REMOTE permissions, or a Y to indicate a user granted CONSOLIDATE permissions.

type_id

SMALLINT

The ID of the message system used to send messages to this user.

address

VARCHAR(128)

The address to which SQL Remote messages are to be sent. The address must be appropriate for the address_type.

frequency

CHAR(1)

How frequently SQL Remote messages are to be sent.

send_time

DATETIME

The next time messages are to be sent to this user.

log_send

NUMERIC(20, 0)

Messages are sent only to subscribers for whom log_send is greater than log_sent.

time_sent

DATETIME

The time the most recent message was sent to this subscriber.

log_sent

NUMERIC(20, 0)

The log offset for the most recently sent operation.

confirm_sent

NUMERIC(20, 0)

The log offset for the most recently confirmed operation from this subscriber.

send_count

INT

How many SQL Remote messages have been sent.

resend_count

INT

Counter to ensure messages are applied only once at the subscriber database.

time_received

DATETIME

The time the most recent message was received from this subscriber.

log_received

NUMERIC(20, 0)

The log offset in the subscriber's database for the operation most recently received at the current database.

confirm_received

NUMERIC(20, 0)

The log offset in the subscriber's database for the most recent operation for which a confirmation message has been sent.

receive_count

INT

How many messages have been received.

rereceive_count

INT

Counter to ensure messages are applied only once at the current database.

Column	Data type	Description
user_id	SMALLINT	The user ID of the user with REMOTE permissions.
operation	VARCHAR(10)
consolidate	CHAR(1)	The column contains either an N to indicate a user granted REMOTE permissions, or a Y to indicate a user granted CONSOLIDATE permissions.
type_id	SMALLINT	The ID of the message system used to send messages to this user.
address	VARCHAR(128)	The address to which SQL Remote messages are to be sent. The address must be appropriate for the address_type.
frequency	CHAR(1)	How frequently SQL Remote messages are to be sent.
send_time	DATETIME	The next time messages are to be sent to this user.
log_send	NUMERIC(20, 0)	Messages are sent only to subscribers for whom log_send is greater than log_sent.
time_sent	DATETIME	The time the most recent message was sent to this subscriber.
log_sent	NUMERIC(20, 0)	The log offset for the most recently sent operation.
confirm_sent	NUMERIC(20, 0)	The log offset for the most recently confirmed operation from this subscriber.
send_count	INT	How many SQL Remote messages have been sent.
resend_count	INT	Counter to ensure messages are applied only once at the subscriber database.
time_received	DATETIME	The time the most recent message was received from this subscriber.
log_received	NUMERIC(20, 0)	The log offset in the subscriber's database for the operation most recently received at the current database.
confirm_received	NUMERIC(20, 0)	The log offset in the subscriber's database for the most recent operation for which a confirmation message has been sent.
receive_count	INT	How many messages have been received.
rereceive_count	INT	Counter to ensure messages are applied only once at the current database.

SYSSUBSCRIPTION table

Function

Each row describes a subscription from one user ID (which must have REMOTE permissions) to one publication.

Columns

Column

Data type

Description

publication_id

SMALLINT

The identifier for the publication to which the user ID is subscribed.

user_id

SMALLINT

The user ID that is subscribed to the publication.

subscribe_by

VARCHAR(128)

For publications with a SUBSCRIBE BY expression, this column holds the matching value for this subscription.

created

NUMERIC(20, 0)

The offset in the transaction log at which the subscription was created.

started

NUMERIC(20, 0)

The offset in the transaction log at which the subscription was started.

operation

VARCHAR(20)

Column	Data type	Description
publication_id	SMALLINT	The identifier for the publication to which the user ID is subscribed.
user_id	SMALLINT	The user ID that is subscribed to the publication.
subscribe_by	VARCHAR(128)	For publications with a SUBSCRIBE BY expression, this column holds the matching value for this subscription.
created	NUMERIC(20, 0)	The offset in the transaction log at which the subscription was created.
started	NUMERIC(20, 0)	The offset in the transaction log at which the subscription was started.
operation	VARCHAR(20)