Create Work Table

1. Purpose of the Task:
• The Create Work Table task is used to create the loading table in the target database. This table is used temporarily during the data integration process to hold data before it is transformed and moved to the final target tables.
2. Execution of the Task:
• This task is always executed as part of the initial steps in the loading phase. It prepares the loading table that will hold the data extracted from the source system before transformation and final loading.
3. Dynamic Table Name Generation:
• The getTable method with the COLL_NAME property is used to dynamically generate the name of the loading table. This ensures that the correct table name is used during the creation process based on the mapping context.
• The table name is referenced as:

o   <%=snpRef.getTable("L", "COLL_NAME", "A")%>

• Here, "L" represents the loading table, "COLL_NAME" refers to the collection name, and "A" can be a reference to the alias or specific table version.
3. Table Structure:
• The table is created with the following structure:

o   create table <%=snpRef.getTable("L", "COLL_NAME", "A")%> (

o     <%=snpRef.getColList("", "[CX_COL_NAME]\t[DEST_WRI_DT] " + snpRef.getInfo("DEST_DDL_NULL"), ",\n\t", "","")%>

o   )

• Field Definitions:
• CX_COL_NAME: Represents the column names in the loading table.
• DEST_WRI_DT: Represents the write date or timestamp field, typically used to track when the data is loaded.
• The getColList method is used to dynamically generate the column list for the loading table, ensuring the correct attributes are included.
• DEST_DDL_NULL is used to define whether columns should allow NULL values based on the target database's requirements.
4. Key Points:
• Always executed: The task runs at the beginning of the loading phase to set up the loading table.
• Dynamic table and column names: The table and its structure are dynamically generated based on the mapping context using getTable and getColList methods.
• Prepares the staging area: This task ensures that the target staging area has the required structure for holding source data during the integration process.
5. Command Explanation:
• The CREATE TABLE SQL statement is dynamically constructed using the ODI functions:
• getTable: Retrieves the table name.
• getColList: Dynamically generates the column list and their definitions (including data types and nullability).
• getInfo: Provides additional metadata such as whether a column can have NULL values.