SharePlex can connect to a database that supports Open Database Connectivity (ODBC).
These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.
For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.
To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.
See SharePlex Administration Guide for more information about column mapping with SharePlex.
You need to setup SharePlex and the database on the Oracle source system. For detailed setup steps, see Configure SharePlex on the source.
Configure SharePlex and the database on the target system as follows.
Note: Make certain that the database setup meets all of the requirements in Open target checklist .
Make certain to download the correct ODBC driver for your database.
On the target system, configure ODBC connection information for use by Post to connect to the target database. You have the following options for configuring this connection information:
On Windows, create a user or system DSN (Data Source Name) by using Data Sources (ODBC) in the Administrative Tools section of the Windows control panel. See the Windows documentation or your system administrator. If using a DSN, you must set the Post user name and password for the target database with the connection comand. See Set connection information with the connection command.
On Unix and Linux, you can do either of the following:
Configure a user or system DSN on the target system according to the instructions provided with the database. Test the DSN by using it to connect to the target database. If the connection is successful, copy the ODBC configuration files to the odbc subdirectory of the SharePlex variable-data directory. Set the LD_LIBRARY_PATH environment variable to the location of the database ODBC driver.
or...
Set the ODBC connection information in the Post configuration. See Set connection information with the connection command.
Use the connection command to:
To set connection information:
Execute the connection command with the set option, once for each keyword.
connection r.database_name set keyword=value
| Keyword | Value to enter |
|---|---|
|
user |
The database user assigned to SharePlex |
|
password |
The password for the SharePlex user |
|
dsn |
The DSN of the database. IMPORTANT! user, password, and dsn are the only required keywords if a DSN exists. |
| Keyword | Value to enter |
|---|---|
|
user |
The database user assigned to SharePlex |
|
password |
The password for the SharePlex user |
|
port |
The database port number. |
|
server |
The name or IP address of the database server. |
|
driver |
The full path to the ODBC driver on the database server. |
| Keyword | Value to enter |
|---|---|
|
user |
The database user assigned to SharePlex |
|
password |
The password for the SharePlex user |
| connect_string | A user-defined connection string. When using your own connection string, make certain it includes all of the required elements to make a successful ODBC connection, but omit the user name and password. Use the connection command with the user and password options to supply user information. |
connection r.mydb set user=myuser
connection r.mydb set password=mypassword
connection r.mydb set dsn=mydsn
connection r.mydb set user=myuser
connection r.mydb set password=mypassword
connection r.mydb set port=1234
connection r.mydb set server=server1
connection r.mydb set driver=/database/ODBC/lib/databasedriver.so
connection r.mydb set user=myuser
connection r.mydb set password=mypassword
connection r.mydb set connect_string=”driver=/database/ODBC/lib/databasedriver.so;server=server1;port=1234;uid=myuser;pwd=mypassword”
Use connection with the reset option to remove SharePlex connection settings.
To remove a specific connection value:
connection r.database_name reset keyword
To remove all connection values:
connection r.database_name reset
Examples:
connection r.mydb reset port
connection r.mydb reset
Use connection with the show option to view SharePlex connection settings.
To view connection values for a database:
connection r.database_name show
To view connection settings for all local databases:
connection show all
To support the accurate posting of replicated Oracle ALTER TABLE commands to the correct data types in an Open Target target, SharePlex maps Oracle data types to default data types for the target database. To view and change this mapping, use the typemap command.
Note: For DML operations, SharePlex queries the target database to determine the appropriate data type mapping for replicated data.
For more information about the typemap command, see SharePlex Reference Guide.
This chapter contains instructions for configuring SharePlex to replicate from Oracle to a different type of cloud storage targets. These instructions highlight specific tasks that are pertinent to the flow of data between source and target.
SharePlex can post replicated Oracle data to a file formatted as Parquet. This data is written as a sequential series of operations as they occurred on the source, which can then be posted in sequential order to a target database or consumed by an external process or program.
These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.
For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.
PRE-REQUISITEs:
To enable efficient processing of Parquet files by columnar query engines, the Update and Delete operations must include image data for all columns. Therefore, it is recommended to set the parameters SP_OCT_REDUCED_KEY and SP_OPX_REDUCED_KEY to 0. This ensures that SharePlex captures all data written to the Oracle redo log, excluding LONG and LOB data.
Oracle supplemental logging for all columns must be enabled to ensure that complete column data is written to the redo log.
On the source, create a SharePlex configuration file that specifies capture and routing information. The structure that is required in a configuration file varies, depending on your replication strategy, but this shows you the required syntax for routing data to a Parquet file.
| Datasource:o.SID | ||
| src_owner.table | !file[:tgt_owner.table] | host |
where:
src_owner.table is the owner and name of the source table.
Note: For more information, see Configure SharePlex to Replicate Data in the SharePlex Administration Guide.
The following example replicates the parts table in schema PROD from Oracle instance ora112 to a file on target system sysprod.
Datasource:o.ora112
PROD.parts !file sysprod
To generate data in Parquet format, use the target command as shown below.
To generate data in Parquet format:
Issue the following required target commands to output the records in Parquet.
Note: Use all lower-case characters.
target x.file [queuequeuename] set format record=Parquet
where:queuequeuename constrains the action of the command to the SharePlex Post process that is associated with the specified queue.
To refer to the configuration settings and other properties for using the Parquet record format as a target on the cloud, See the Configure Parquet record format as target section under Configure Cloud storage targets for SharePlex in the SharePlex Administration Guide.
To view current property settings for output to a file, use the following command:
target x.file show
To change a setting, use the following target command.
target x.file [queuequeuename] set [category] property=value
Post writes to a series of files. The active working file is prepended with the label of current_ and is stored in the opx/current subdirectory of the variable-data directory.
| Output Format | Name of Current File |
|---|---|
| Parquet | current_prodsys.parquet |
Important: Do not open or edit the current_ file.
Post uses the max_records, max_size and max_time parameters to determine the point at which to start a new active file. When this switch occurs, Post moves the processed data to the configured cloud storage location. The file names include the name of the post queue, the time and date, and an incrementing ID.
For Oracle to Parquet replication, a separate Parquet file will be created at the target end for each table in replication. This file will reside in a newly created directory named after it at the configured location. The file format for the consumable file will be:
<schema.table>/<seq_no>_<timestamp>_<queue_name>.parquet
Parquet files:
Microsoft_Azure_Storage -> parquetpocblobs01/parquetsamples
0000000000_20140305130858_prodsys.Parquet
0000000001_20140305131130_prodsys.Parquet
0000000002_20140305131212_prodsys.Parquet
0000000003_20140305133835_prodsys.Parquet
0000000004_20140305134028_prodsys.Parquet
To force a file switch:
The current file cannot be viewed or consumed without stopping Post. To access the data in the current file, you can use the target command with the switch option to move the data to a sequenced file, from which it can then be consumed or viewed. After issuing this command, the switch occurs after Post processes a new record.
target x.file [queuequeuename] switch
