User Tools

Site Tools


replicator_agent_database_information

Perspectium Replicator Database Information

Dubnium

The Perspectium SQL Subscriber Handler is capable of communication with any Relational Database Management System (RDBMS) that supports the JDBC 2.0 specification.

Perspectium Supported/Tested Databases

Agent will support integration with the following databases:

  • MSSQL
  • Oracle
  • SQL Server
  • MySQL
  • SAP Hana
  • Amazon Redshift
  • HP Vertica
  • IBM DB2
  • PostgreSQL

Configuration

Perspectium uses the newest version of each of the associated JDBC driver. In a nutshell, the Perspectium SQL subscriber is configured to specify the following:

  1. The IP address or fully qualified domain name of the Database Server
  2. The Port which the database server is listening on
  3. A user which has been configured to access and perform the required database operations
  4. The password associated with the configured database user
  5. The name of the database which will contain the replicated tables
  6. Optionally, the SID if it's an Oracle database and a SID is required

Database User Permissions

The following are the permissions that that user configured within the Replicator Agent requires, followed by an explanation for each.

Initial Run/Install

  • Permission to create a new database

Ongoing Permissions

  • Permission to create tables within defined databases
  • Permission to query database and associated tables
  • Permission to insert data into tables
  • Permission to update data in existing tables
  • Permission to alter a table
  • Permission to delete a table row

Create a New Database

After installation has been performed and the Replicator Agent is started, the agent establishes a connection to the database server using the credentials configured as outlined above.

This task must be completed successfully before the agent will proceed with subsequent processing. Once the connection has been established, the agent will attempt to create the configured database. This step requires that the configured user has the proper permissions to create a new database. If this is not the case then an error will occur. As an alternative, the required database can be created by something or someone other than the agent. When this approach is taken you add the configuration directive <skip_database_creation/> to the configuration and the agent will not attempt to create the database since this tell the agent that the database already exists.

Create Tables

Once replicated data is available, the agent will determine if the table associated with the replicated data exists, and if it's not it will create it.

This means that the configured user must have the permissions required to create tables within the defined database.

Insert

Once the required table has been created the agent will perform an insert in order to populate the table with the replicated row. This means that the agent must have the permissions to perform an insert into the table.

Query and Update

When you share out data to be consumed by the Replicator Agent, you'll typically specify whether the data is being shared in bulk or dynamically. When the data is shared in bulk and based on how the agent has been configured, it will typically perform a query in order to determine if the row already exists in the database. The agent must therefore have permission to query the database and the associated tables. If the agent determines that the row already exist then it performs an update, otherwise it will insert the row. The agent must have permission to perform both these actions.

Alter Table

Additionally, the agent wants to ensure that all data is replicated. It's possible that the structure of the source table has changed.

For example, an additional column has been added to the table or the size of a column has been increased. The agent performs the analysis required to make this determination and by default will alter the table in order to align it with the source table. Currently, Perspectium supports adding a column and increasing the size of a text or character type column to it's maximum. Additionally, if the database supports it, a text or character type column can be modified to a larger type of text or character column. Currently Perspectium does not support altering from one type to another such as a text or character type being modified to an integer or an integer to a float etc.. The agent must therefore have the permissions to alter the table.

It is possible to constrain the agent from altering tables by including the <skip_alter/> directive within the SQL Subscribers <task> definition.

Delete

Lastly, in order to support the deletion of rows from a table, the database user must also have permission to delete a table row.

replicator_agent_database_information.txt · Last modified: 2018/10/29 11:19 by timothy.pike