Optimizing Database Performance: Database Links

You can utilize RDBMS functionality to configure links between the RDBMS servers that host the IDB schemas and the RDBMS server that hosts the Info Mart database schema. If database links are not configured, the extraction job first moves data from IDB into Genesys Info Mart memory, and then from Genesys Info Mart memory into the Info Mart database. When database links are configured, the extraction job can copy data directly from the IDB into the Info Mart database.

Genesys Info Mart used to recommend that you use database links so as to improve performance of the extraction job. However, with the increased processing capacity and reduced cost of modern hardware, it might no longer be worth investing time and effort in configuring database links.

Important

Genesys Info Mart does not support database links on PostgreSQL.

This page provides information about:

• Creating Linked Servers — Microsoft SQL Server
• Creating Database Links — Oracle
• Verifying Use of Database Links

Creating Linked Servers — Microsoft SQL Server

The following procedure describes how to use SQL Server Management Studio to configure links between Microsoft SQL Server databases.

Procedure: Creating Linked Servers for Microsoft SQL Server

Purpose: To configure links between the IDB and Info Mart database RDBMS servers to improve performance of the extraction job.

Steps

1. Log in to the SQL Server Management Studio, using administrator credentials.
2. Connect Object Explorer to the Info Mart database.
3. Open Object Explorer and select Server Objects > Linked Servers.
4. Follow Microsoft instructions to create Linked Servers that link to the IDBs from which Genesys Info Mart will extract data.
   1. On each linked server, create the “Info Mart” user account and grant the SELECT permission on IDB (for example, you could include the user in the db_datareader role in IDB).
   2. On the Info Mart database server, create a linked server and map the local “Info Mart” user account to the “Info Mart” user account on each linked server.
   For illustrations of the elements in the database access point (DAP) configuration that are used in the Linked Servers definition, see Related DAP Configuration Example.
5. Log in to the Info Mart database, using the Info Mart user ID.
6. In SQL Server Management Studio, verify that you can access IDB tables and views from the Linked Servers. For example, execute a SQL statement such as:
   select * from [linked-server].[IDB].dbo.G_DB_PARAMETERS;

Next Steps

See Verifying Use of Database Links.

Related DAP Configuration Example

The following Figures illustrate the relationship between the components that are referenced in the Linked Servers definition and the properties of the Genesys Info Mart extraction DAP, for non-JDBC and JDBC DAPs, respectively. For full information about configuring the DAPs that Genesys Info Mart requires, see Configuring Required DAPs.

Linked Servers Configuration Example — Non-JDBC DAP

Linked Servers Configuration Example — JDBC DAP

Creating Database Links — Oracle

Important

In Oracle Real Application Cluster (RAC) deployments with database links, node synchronization activity is significant and can lead to performance issues. Genesys recommends that you do not use database links if you are using Oracle RAC.

You can configure either public or private links between Oracle databases. The only requirement is that Genesys Info Mart can access the database link.

The following command configures a public link between Oracle databases:

CREATE PUBLIC DATABASE LINK <Link Name>
connect to <IDB User Name>
identified by <IDB User Password>
USING '<IDB Connect_String>';

where:

• <Link Name> is any valid database link name.
• <IDB User Name> is the User Name that is specified on the DB Info tab of the DAP that enables connection to the IDB.
• <IDB User Password> is the Password that is specified on the DB Info tab of the DAP that enables connection to the IDB.
• <IDB Connect_String> is the service name of the remote IDB database. For Oracle, this is the Transparent Network Substrate (TNS) name.
  Important

  If you specify only the database name, Oracle implicitly appends the database domain to the connect string to create a complete service name. Therefore, particularly if the IDB and Info Mart databases are not in the same database domain, ensure that you specify the complete service name.

Execute the command on the server that hosts the Info Mart database schema. Repeat the command for each IDB from which Genesys Info Mart might need to extract data, using database connection information that matches the DAP that Genesys Info Mart uses to extract data from that IDB.

The following Figures illustrate the relationship between the parameters in the database-link command and the properties of the Genesys Info Mart extraction DAP, for non-JDBC and JDBC DAPs, respectively.

Linked Servers Configuration Example — Non-JDBC DAP

Linked Servers Configuration Example — JDBC DAP

Verifying Connection Information in Database Links

After the database links have been created, Genesys recommends that you verify that Oracle has stored the connection strings correctly. To do so, execute the following statement:

SELECT * FROM ALL_DB_LINKS

Then verify the connection information that is stored in the ALL_DB_LINKS.HOST column.

See also Verifying Use of Database Links.

Additional Configuration Considerations

If you use database links in an Oracle deployment, ensure that you configure the limit for open links to provide sufficient connections for concurrent processing. Genesys recommends that, at a minimum, you set the value of the open_links Oracle initialization parameter to the value of the Genesys Info Mart extract-data-thread-pool-size configuration option.

Verifying Use of Database Links

At the start of each extraction cycle, Genesys Info Mart logs the DAPs and associated database links that will be used during that extraction cycle. Review the logs to verify the use of configured database links during runtime. The following Figure shows an excerpt from a sample log.