Chat now with support
Chat with Support

SharePlex 8.6.6 - Reference Guide

About this guide Conventions used in this guide SharePlex commands SharePlex parameters SharePlex utilities Appendix B: SharePlex environment variables

SQL Server Setup (mss_setup)

SharePlex utilities > SQL Server Setup (mss_setup)

Overview

Run the SQL Server Setup program (mss_setup) on a Microsoft SQL Server system to establish SharePlex as a SQL Server database user. This utility creates the following:

  • A SharePlex user account with full DBA privileges
  • Tables and indexes for use by SharePlex and owned by the SharePlex user in a database of your choosing
  • A default database connection.

Supported databases

Microsoft SQL Server on Windows

Guidelines for using SQL Server Setup

  • A DSN (data source name) must exist for the SQL Server database. SharePlex Post uses the DSN to connect to the database through ODBC.
  • Run SQL Server Setup on all SQL Server instances in the SharePlex replication configuration.
  • Within a cluster, run SQL Server Setup on the node to which the shared disk that contains the variable-data directory is mounted.
  • For consolidated replication, run SQL Server Setup for each variable-data directory.

Required privileges to run SQL Server Setup

Review the following requirements to ensure that the setup succeeds.

  • SQL Server Setup must be run as a SQL Server System Administrator in order to grant SharePlex the required privileges to operate on the database and to create the SharePlex database account and objects.
  • (Cloud installations) Common restrictions on privileges in cloud-hosted database services make it difficult for the setup utility to succeed in every possible type of scenario. To ensure that the database setup succeeds, use the setup utility only to set up replication to the following: A new database, to be created by the setup utility, where the SharePlex user is also created by the setup utility.An existing database, where the SharePlex user is an existing user that is either the owner of the database or has access privileges to the database.

Run SQL Server Setup

  1. Shut down any running SharePlex processes and sp_cop on the SQL Server system.
  2. Run the mss_setup utility from the bin subdirectory of the SharePlex product directory.

    Important! If you installed the SharePlex instance on any port other than the default of 2100, use the -p option to specify the port number. For example, in the following command the port number is 9400.

    C:\users\splex\bin> mss_setup -p9400

Table 4: Setup prompts and responses

Prompt Response

Enter the DSN name or connection string [] :

Enter a connection string or a data source name (DSN) that connects to SQL Server.

Enter the Microsoft SQL Server Administrator name :

Enter the name of the SQL Server Administrator. This user will perform the setup work on the SharePlex account and schema.

Enter the password for the Administrator account :

Enter the password of the Administrator.

Enter the name of the database :

Enter the name of the database where you want to install the SharePlex objects.

Database name database does not exist. Would you like to create it? [y] :

If this prompt is displayed, the specified database does not exist. Press Enter to have the setup utility create it for you.

Would you like to create a new SharePlex user [y]:

Press Enter to accept the default to create a new SharePlex database user account, or enter n to use an existing account as the SharePlex database user.

Enter the name of the new SharePlex user:

Enter the name of the existing SharePlex user:

One of these prompts is displayed depending on whether you elected to create a new user or use an existing user. Enter the name of the SharePlex user.

 

Enter the password of the SharePlex user :

Enter the password of the SharePlex user account.

Re-enter the password for the SharePlex user :

Enter the SharePlex password again.

A successful setup terminates with a message similar to the following:

Completed SharePlex for Microsoft SQL Server database configuration

SharePlex User name: splex

Database name: db1

Target specification in SharePlex configuration: r.db1

Teradata Setup (td_setup)

SharePlex utilities > Teradata Setup (td_setup)

Overview

Run the Teradata Setup program (td_setup) on a Teradata system to establish a user account and database for use by SharePlex.

Supported databases

Teradata on supported platforms

Requirements for using Teradata Setup

  • An ODBC Data Source Name (DSN) must exist for SharePlex to use. You will be prompted for this name during setup. See the Teradata ODBC documentation for instructions.
  • Run Teradata Setup on all target Teradata instances in the SharePlex replication configuration.
  • Within a server cluster, run Teradata Setup on the node to which the shared disk that contains the SharePlex variable-data directory is mounted.
  • For consolidated replication, run Teradata Setup for each variable-data directory on the Teradata target.

Required privileges to run Teradata Setup

Teradata Setup must be run as a Teradata Administrator in order to grant SharePlex the required privileges to operate on the database and to create the SharePlex database account.

Run Teradata Setup

  1. Shut down any running SharePlex processes and sp_cop on the target system.
  2. Run the td_setup utility from the bin subdirectory of the SharePlex product directory.

    Important! If you installed the SharePlex instance on any port other than the default of 2100, use the ‑p option to specify the port number. For example, in the following command the port number is 9400.

    $/users/splex/bin> td_setup ‑p9400

Table 5: Teradata Setup Prompts and Responses

Prompt Response

Please enter the full directory path of the Teradata ODBC driver:

Enter the full path to the Teradata ODBC driver library. This step initializes the driver location within the Teradata Setup utility so that the rest of the setup can take place. It requires a second running of Teradata Setup to complete the setup steps.

Please run td_setup again to create the tables, login and user accounts needed to run SharePlex replication.

Run Teradata Setup again, including the port number if you installed SharePlex on any port other than 2100:

$/users/splex/bin> td_setup -p9400

Enter the DSN name or connection string []:

Enter a connection string or a data source name (DSN) that connects to Teradata.

Enter the Teradata Administrator name:

Enter the name of the Teradata Administrator. This user will perform the work on the SharePlex account.

Enter the password for the Administrator account:

Enter the password of the Administrator.

Would you like to create a new SharePlex user [y]:

Press Enter to accept the default to create a new SharePlex database user account or enter n to use an existing SharePlex account.

Enter the name of the new SharePlex user:

(or...)

Enter the name of the existing SharePlex user:

Enter the name of the new or existing SharePlex user, depending on which option you chose in the previous step.

 

Enter the password for new SharePlex user:

(or...)

Enter the password for existing SharePlex user:

Enter the password of the new or existing SharePlex user account.

Re-enter the password for new SharePlex user:

This prompt is only shown if you created a new user. Enter the SharePlex password again.

Enter the name of the database:

Accept the default or enter a different name for the database.

Database name database does not exist. Would you like to create it? [y]:

If this prompt is displayed, the specified database does not exist. Press Enter to have Teradata Setup create it for you.

A successful setup terminates with a message similar to the following:

Completed SharePlex for Teradata database configuration

SharePlex User name: splex

Database name: splex

Target specification in SharePlex configuration: r.splex

License utilities

SharePlex utilities > License utilities

Description

Use the SharePlex license utilities to view and add license keys to hosts in the SharePlex replication environment. Each installation of SharePlex requires a valid license key. There are three types of SharePlex license keys:

  • Temporary license keys (demonstration licenses)
  • Permanent license keys
  • Site license keys

The current license model for SharePlex is to license for a specific host, which depending on edition is licensed by core(s) or socket(s) and specific message repository (i.e. database, JMS/text files) etc. Specifics of license terms should be obtained from your account manager.

If you do not have a valid license key, you may obtain one from Quest Technical Support or your Quest sales representative. Use the appropriate procedure in this documentation to obtain the necessary information to support your license request.

License utilities on Unix and Linux

On Unix and Linux systems, separate utilities are used to:

Gather information to request a license

Use the splex_uname utility to collect information about the local host that is required for Quest to generate a license key. Run this utility from the install sub-directory of the SharePlex product directory.

$ /proddir/install/splex_uname

The information is similar to the following example:

Local system info:
Host Name	 	= irvspxu09
Host ID	 	= 2198894273 (831076C1 HEX)
Operating System	= SunOS
Machine model		= SUNW,UltraAX-i2
CPU Type		= sparc9
CPU Speed	 	= 500
Number of CPUs	 	= 1

Add a license key

Use the splex_add_key utility to add a license key to a machine during the installation of SharePlex or afterward to convert from one type of license to another.

You can use the splex_add_key utility as follows:

  • You can use splex_add_key on the primary node of a cluster to install licenses for all secondary nodes in the cluster, because they all share one variable-data directory.
  • You cannot use splex_add_key to add licenses for non-clustered machines from one machine. It must be run on each non-clustered replication system so that the license information is stored in the variable-data directory on each system.

To run splex_add_key

  1. Log on to the system as the SharePlex Administrator.
  2. Run sp_ctrl on the machine where you want to install a license key.
  3. If SharePlex is running, shut it down.

    sp_ctrl> shutdown

  4. Run splex_add_key from the install sub-directory of the SharePlex product directory.

    $ /proddir/install/splex_add_key

  5. Choose the appropriate option for how to install the key:

    SharePlex License Utility
    1) Read license key from file
    2) Enter license key manually
    3) Add license key for alternate host
    q) Quit License Utility
    Enter option:

    Note: Use option 3 when installing in a cluster.

  6. If you are entering the key manually, type the license key exactly as you received it from Quest, including the SPLEXKEY= string if one prepends the key, any spaces, and any capitalization. Press Enter when finished typing the key.

  7. Enter the SiteMessage text string provided by Quest with the license key.

    Note: If you selected option 3 to install in a cluster, the utility continually prompts for another machine ID to license, until you exit the license utility.

  8. Enter q to exit the utility.
  9. Start SharePlex when you are ready for replication to resume.

View a license key

Use the splex_get_key utility to view the SharePlex license key, license type, and expiration date on a machine. Run this utility from the install sub-directory of the SharePlex product directory.

$ /proddir/install/splex_get_key

The information is similar to the following example:

$ /splex/proddir/install/splex_get_key
Customer Name = Quest
License Key = EANLCF3P56F8M1JB1K6RBJCBB6TBEP0
Product Name = SharePlex Oracle
License type = demo key
Expired on = 01/01/2008 12:00 AM

License utility on Windows

On Windows platforms, all licensing functions are performed through the SpUtils utility, including viewing the machine ID for a system. Launch the SpUtils utility from the shortcut on the Windows desktop.

Gather information to request a license

Select the Information tab to view information about the local host that is required for Quest to generate a license key. In a cluster, run SpUtils on each node.

Add a license key

Use SpUtils to add a license key to a machine during the installation of SharePlex or afterward to convert from one type of license to another. In a cluster, run SpUtils on the primary node.

  1. Log on to Windows as the SharePlex Administrator.
  2. If SharePlex is running, do the following:

    1. Select the SharePlex Services tab.
    2. Select the port number of the SharePlex instance for which you want to add a license key.
    3. Click Stop.
    4. Wait for Current State to display a message that the service stopped.
  3. Select the License Keys tab.
  4. Select the SharePlex port number from the Port list.

  5. Click Add License, then type or paste the information exactly as you received it from Quest, as follows:

    1. License Key: The license key, including any spaces. The key is case-sensitive.
    2. Customer Name: The Site Message text string that was included with the license. The name is case-sensitive.
  6. (Windows Cluster) To add a license for another node in a cluster, select the Add Alternate Host Id check box, then select the machine ID from the CPU ID list box. Repeat this step to add a license to all secondary nodes in the cluster.

  7. Click OK to close the utility.

  8. To start SharePlex, do the following:

    1. Select the SharePlex Services tab.
    2. Select the port number of the SharePlex instance that you licensed.
    3. Click Start.
    4. Wait for Current State to display a message that the service started.

View a license key

Select the License Key tab, then select the port number (if running multiple SharePlex instances) for which you want to view license information.

  • Under License Key, view the actual license key and SiteMessage text string.
  • Under Status, view the type of SharePlex installation, the license type, and the expiration date.

In a cluster, run SpUtils on each node to view the local license.

ots

Description

Use the OTS utility (ODBC Test Suite) to test the ability of SharePlex to connect to an Open Target target database through a selected ODBC driver, and then post test data to different column types.

Notes:

  • The OTS utility uses a generic ODBC connection and datatype handling method to provide a preliminary test of ODBC databases that are not yet Quest SharePlex Certified (see How a database is considered for support by SharePlex ). The OTS utility is not needed for databases that are certified. For a list of certified databases, see the SharePlex Release Notes.
  • This utility is in the beta testing phase. Its results should not be considered as definitive proof of the suitability of an ODBC data store as a SharePlex target. Comprehensive testing of actual data in a replica of the proposed production environment must always be part of the certification process.

Supported databases

Open Target databases being considered for use with SharePlex.

Requirements

  1. Install the ODBC driver that you want to test.

  2. (Linux) Set the following environment variables:

    • Set LD_LIBRARY_PATH to both of the following places:

      • SharePlex lib directory, which contains the ODBC libraries.
      • The location of the ODBC driver.
    • If you configured a data source name (DSN) for the database, set ODBCINI and ODBCSYSINI to point to the directory where OTS is installed. If a DSN does not exist and you will connect with a connection string, these variables do not have to be set. (See How to run OTS.)
  3. OTS creates a table to test INSERT, UPDATE, and DELETE operations. The table is created and owned by the user that connects to the database (see How to run OTS). This user must have sufficient permissions to create and own this table.

How to install a standalone OTS

OTS is available in the SharePlex build as well as a standalone version.

To install the standalone version:

Linux: Download the OTS package SPOodbc_test_suite-version-Beta-db_version-rh-40-amd64-m64.tpm. You are prompted for an installation directory.

Windows: Download the ots.exe file and place it in any directory.

How to run OTS

  1. Run OTS. If you are running it from within an installation of SharePlex or the standalone OTS package on Linux, run it from the util subdirectory. If you are running the Windows standalone version of OTS, run it from the installation directory.

  2. Type one of the following:
    • A data source name (DSN) for the target database. The DSN must already exist. You are then prompted for the name and password.

    • A connection string. The string must have all the correct parameters to establish a connection to the target database.
    OTS requires connection information for the tested database.
    Enter data source name (DSN) or connection string: mydsn
    

How it works

The OTS utility performs the following tests:

  1. Connect to the Open Target target with a name and password or a connection string provided by the person that is running the utility.
  2. Query the target database for a list of supported operations and datatypes, including allowed storage values and whether a column is nullable.
  3. Issue a full set of SharePlex-supported DML and DDL operations for each datatype.
  4. Summarize the results on screen (see How to interpret the OTS results).
  5. Create a report, dump files, and logs to support debugging and support cases.

    Note: The report file is only generated when OTS can connect to the database. If the connection fails, the file is empty.

How to interpret the OTS results

The OTS screen summary provides the following:

  • A summary of the connection information that was used
  • A summary list of datatypes that were tested
  • Details of the test

The detail results are organized into tables, one per datatype category, for example character datatypes or integer datatypes, as shown in the example.

Figure 1: Example detail table for character datatypes

Test: Character Datatypes
--------------------------------------------------------------------------------
Name     Size Nullable Insert Insert Update Delete Add Drop Trunc Pass/Fail
                       Null                        Col Col
--------------------------------------------------------------------------------
char     4096 Y        P      P      P      P      P   P    P     P
nchar    4096 Y        P      P      P      P      P   P    P     P
varchar  4096 Y        P      P      P      P      P   P    P     P
sysname    30 Y        P      P      P      P      P   P    P     P
nvarchar 4096 Y        P      P      P      P      P   P    P     P

The first three columns of each table (Name, Size, Nullable) are taken from a query to the ODBC driver. If the datatype is allowed to contain NULL, the Nullable column shows a Y (yes). Otherwise, it shows an N (no).

Following the query columns are columns that represent the tests that were performed. There is a row for each datatype that was tested. The SharePlex-supported operations that OTS tests are as follows:

  • INSERT with NULL value
  • INSERT
  • UPDATE
  • DELETE
  • ALTER TABLE to ADD COLUMN
  • ALTER TABLE to DROP COLUMN
  • TRUNCATE TABLE

The results also show the maximum and minimum (where applicable) storage values that were used for CHAR and VARCHAR datatypes in each test:

  • For a datatype that has only a maximum value (VARCHAR and CHAR) OTS will insert a data length that is greater than the maximum size to determine whether the ODBC database driver returns an error or automatically truncates the data with no error.
  • For a datatype that has both minimum and maximum values (numerical datatypes) a series of operation tests are run for each value. Numbers less than the minimum value and greater than the maximum value are tested to see if the ODBC database driver returns an overflow error or automatically rounds off and truncates the value.

For each test of a datatype and operation, a P (pass) or F (fail) result is given, indicating whether or not SharePlex was able to perform that specific DML or DDL operation with that specific datatype.

The final column, Pass/Fail, shows at a glance whether the test passed or failed as a whole for a specific datatype. An F for any given operation type across the row triggers an F in the Pass/Fail column, meaning an overall failure of testing for that datatype.

How to use the results

The OTS utility is an initial screening tool for Open Target databases that are not yet Quest SharePlex Certified. Its purpose is to determine whether the types of operations that you want to replicate to a given target, with a given set of metadata, are likely to succeed, without requiring the installation or setup of SharePlex itself. For example, you could assume that SharePlex probably can post to a target if the outcome of the test is the following:

  • All of the DML tests except one passed, but the failed datatype is not defined in your data.
  • All of the DDL failed, but you do not need to replicate DDL.

The OTS utility is only a first step. It is not a substitute for testing a full installation of SharePlex with your actual data, using actual operations that your source Oracle applications generate, and posting those replicated operations to your actual target tables in a test environment. Additionally, a successful test with OTS does not necessarily mean the database will be fully supported by SharePlex. See How to run OTS

How a database is considered for support by SharePlex

To determine whether SharePlex will support a given Open Target database, the following are considered:

  1. Can SharePlex, through OTS, run a full set of transactions through a given ODBC driver for the database?
  2. Does SharePlex Product Management approve the technical and market feasibility of supporting the database?

  3. Have the Quest QA labs verified in testing that SharePlex can support the database?

If the answer to questions 1 and 2 are yes, then the database is ODBC-approved. This means the database is then eligible for testing in the QA labs to answer question 3.

It is not realistic to test every possible edition, version, and platform of a given database. Based on market and technical factors, Product Management will determine which variants of a database will be tested. If those tests are successful, Product Management can then consider assigning the database a designation of Quest SharePlex Certified.

Related Documents