LKM SQL to SQL

1. Purpose of LKM SQL to SQL:
• LKM SQL to SQL is used in the loading phase of the data integration process. It facilitates transferring data from a source SQL database to a target SQL database using Oracle Data Integrator (ODI).
2. How LKM SQL to SQL Works:
• The LKM SQL to SQL method typically uses the ODI agent to execute the data load. It performs the data extraction and loading operations based on predefined SQL queries.
3. Typical Flow of LKM SQL to SQL:
• Step 1: Connection Setup:
• The connection to both the source and target SQL databases is established. This can be done using the ODI Studio where you define the source and target data servers.
• Step 2: Data Extraction from Source:
• The LKM SQL to SQL module generates the necessary SQL SELECT queries to extract data from the source SQL database. It retrieves the required data set based on the mapping or transformation logic.
• Step 3: Data Loading into Target:
• After extracting the data from the source, the LKM generates INSERT statements or equivalent commands to load the data into the target SQL database. The ODI agent performs the actual loading of data.
• Step 4: Execution of the SQL Commands:
• The ODI agent executes the SQL commands on the source and target databases. This involves fetching the result set from the source and inserting it into the corresponding target table(s).
• Step 5: Clean-Up:
• Once the data is loaded into the target, any temporary objects or staging tables created during the process are dropped. The LKM SQL to SQL will ensure that unnecessary data structures are removed after the operation to maintain clean database environments.
4. Key Features of LKM SQL to SQL:
• SQL-based Data Transfer: Uses SQL SELECT to extract data and SQL INSERT to load it into the target database.
• ODI Agent: The data transfer happens through the ODI agent, which manages the execution and data flow.
• Efficient for RDBMS Systems: This method is most suitable when both source and target databases are SQL-based (e.g., Oracle, SQL Server, MySQL).
5. Sample SQL Commands in LKM SQL to SQL:
• The specific SQL commands (such as SELECT for extraction and INSERT for loading) used in this LKM can vary based on the configuration. These commands can be reviewed and modified by accessing the Knowledge Module in ODI Studio for more control.