Overview
Information is often stored in databases using different architectures, naming conventions, and formats. To align this data between systems, careful mapping and transformation are required.
eZintegrations provides multiple operations to transform, clean, and map data across databases. This enables users to build reliable and efficient Database-to-Database integrations.
This guide explains how to configure a database as both a source and a target to retrieve, process, and transfer data between systems.
When to Use
Use Database-to-Database integration when data must be synchronized or migrated between two database systems.
- When transferring records between heterogeneous databases
- When consolidating data into a central repository
- When performing scheduled data replication
- When transforming source data before storage
- When handling historical and real-time data loads
How It Works
The integration follows a structured workflow to extract, transform, and load data.
- Configure the source database connection
- Execute SQL queries to retrieve data
- Apply operations for transformation
- Convert records into batch-friendly formats
- Configure the target database
- Insert transformed data into the destination system
How to Configure
Data Source Configuration
Step 1: Select Source Type
Select Database as the source type.
Step 2: Select Storage Name
Choose the required database from the dropdown list.
Example:
- Oracle Database
Step 3: Configure Database Details
After selecting the storage name, configure the following parameters.
Version
Select the database version.
Example: Latest
Database Credentials
Provide the following details:
- Host IP Address
- Port Number
- Schema Name
- Username
- Password
Chunk Size
Defines the number of records streamed in one batch.
- Recommended: 1000
- Maximum: Up to 10,000+ for historical loads
Response Parameters
Response parameters define key-value mappings for different status codes returned by the integration.
They control how responses are interpreted and modified.
SQL Statement
Enter the SQL query in the provided field and use the Execute button to preview results.
Data Operations
Operations are used to transform, clean, and structure data before sending it to the target.
Singleline to Multiline
Converts single-line JSON into multiline format for easier processing.
Value to be passed:
['bizdata_dataset_response']['data']
This converts data stored under data into multiline format.
Append
Append is used to create new key-value pairs and map them to target fields.
Example:
"new_key": "new_value"
Data Aggregation
Data Aggregation groups records into arrays for structured processing.
Parameters
- Agg Data Key: Empty for multiline data, populated for singleline data
- Groupby Key: Unique identifier for grouping
- Array Key: Key for storing grouped data
- Array Key Nested Columns: Comma-separated nested keys
Example:
['bizdata_dataset_response']['items']
Singleline to Tuple
This operation creates comma-separated tuples for bulk database insertion.
Parameters
- Singleline Key: Holds singleline dataset
- Table Headers: Defines tuple column order
- Tuple Key: Stores generated tuple data
Example:
"Order","Customer Name","Product"
Data Target Configuration
Step 1: Select Target Type
Select Database as the target type.
Step 2: Select Storage Name
Choose the destination database.
Step 3: Configure Target Credentials
Provide Host, Port, Schema, Username, and Password.
Step 4: Configure Tuple Key
Provide the tuple key created in the Singleline to Tuple operation.
Example:
['Orderdetail']
Step 5: Configure Batch Size
Batch Size defines how many records are sent per transaction.
- Recommended: 1000
Step 6: Configure SQL Statement
SQL queries must be enclosed in three double quotes.
Example:
"""Insert into table_name (column1,column2) values (?,?)""","column1","column2"
Final Submission
After configuring source, operations, and target:
- Review all parameters
- Click Save and Submit
- Activate the Integration Bridge
Once submitted, the connection between the two databases is established.
Troubleshooting
- Connection Failed: Verify credentials and network access.
- Query Error: Validate SQL syntax.
- No Records Sent: Check tuple generation.
- Batch Failure: Reduce batch size.
- Invalid Mapping: Review Append and Aggregation steps.
Frequently Asked Questions
Can I connect different database types?
Yes. eZintegrations supports heterogeneous database integration.
Is batching mandatory?
Batching is recommended for performance but can be adjusted.
Can I transform data before loading?
Yes. Operations allow extensive data transformation.
Can this integration be scheduled?
Yes. Scheduling is available from the Summary page.
Is preview available before execution?
Yes. The Execute button allows query validation.
Notes
- Always test SQL queries before deployment.
- Secure database credentials.
- Optimize queries for large datasets.
- Monitor execution logs.
- Adjust batch sizes for performance.
This guide enables reliable Database-to-Database integration using eZintegrations with configurable operations and batch processing.
