Using Below all parameters we can understand the Knowledge Modules.
ODI Substitution Parameters L,W,D,P,S usage and meanings
Parameter pMode
L:
use the local object mask to build the complete path of the object.
R:
Uses the object mask to build the complete path of the object.
use the remote object mask to build the complete path of the object.
Note: When using the remote object mask, getObjectName always resolved the object name using the default physical schema of the remote server.
A:
Automatic: Defines automatically the adequate mask to use.
Parameter Location
W:
Returns the complete name of the object in the physical catalog and the "work" physical schema that corresponds to the specified tuple (context, logical schema)
D:
Returns the complete name of the object in the physical catalog and the data physical schema that corresponds to the specified tuple (context, logical schema)
A:
Lets Oracle Data Integrator determine the default location of the object. This value is used if pLocation is not specified.
P:
Qualify object for the partition provided in pPartitionName
S:
Qualify object for the sub-partition provided in pPartitionName
Parameter pProperty:
ID:Datastore identifier.
TARG_NAME:Full name of the target datastore. In actions, this parameter returns the name of the current table handled by the DDL command. If partitioning is used on the target datastore of an interface, this property automatically includes the partitioning clause in the datastore name.
RES_NAME:Physical name of the target datastore. In actions, this parameter returns the name of the current table handled by the DDL command. This property does not include the partitioning information.
COLL_NAME:Full name of the loading datastore.
INT_NAME:Full name of the integration datastore.
ERR_NAME:Full name of the error datastore.
CHECK_NAME: Name of the error summary datastore.
CT_NAME: Full name of the checked datastore.
FK_PK_TABLE_NAME:Full name of the datastore referenced by a foreign key.
JRN_NAME:Full name of the journalized datastore.
JRN_VIEW:Full name of the view linked to the journalized datastore.
JRN_DATA_VIEW:Full name of the data view linked to the journalized datastore.
JRN_TRIGGER:Full name of the trigger linked to the journalized datastore.
JRN_ITRIGGER:Full name of the Insert trigger linked to the journalized datastore.
JRN _UTRIGGER:Full name of the Update trigger linked to the journalized datastore.
JRN_DTRIGGER:Full name of the Delete trigger linked to the journalized datastore.
SUBSCRIBER_TABLE:Full name of the datastore containing the subscribers list.
CDC_SET_TABLE: Full name of the table containing list of CDC sets.
CDC_TABLE_TABLE:Full name of the table containing the list of tables journalized through CDC sets.
CDC_SUBS_TABLE:Full name of the table containing the list of subscribers to CDC sets.
CDC_OBJECTS_TABLE:Full name of the table containing the journalizing parameters and objects.
<flexfield_code>: Flexfield value for the current target table.
One Example For Substitution Methods
Procedure Details for Loading Data from a Remote SQL Database
| Source Technology | Oracle |
| Source Logical Schema | SOURCE |
| Source Command | select ENAME V_ENAME,EMPNO V_EMPNO from <%=odiRef.getObjectName("L","SCOTT","D")%> |
| Target Technology | Teradata |
| Target Logical Schema | TERADATA_DWH |
| Target Command | insert into PARTS (ENAME,EMPNO) values (:V_ENAME,:V_EMPNO) |
Oracle Tools Send Mail Example
Procedure Details for Sending Multiple Emails
| Source Technology | Oracle |
| Source Logical Schema | ORACLE |
| Source Command | Select FirstName FNAME, EMailaddress EMAIL From <%=odiRef.getObjectName("L","USERS","D")%> |
| Target Technology | ODITools |
| Target Logical Schema | None |
| Target Command | OdiSendMail -MAILHOST= tgrtechnologies.com -FROM=admin@tgrtechnologies.com “-TO=#EMAIL” “-SUBJECT=Job Failure” Dear #FNAME, This is sample program in TGR Technologies, because session <%=snpRef.getSession(“SESS_NO”)%> has just started! -Admin |
Delete Target Table
This task deletes the data from the target table. This command runs in a transaction and is not committed. It is executed if the DELETE_ALL Knowledge Module option is selected.
Command on Target
delete from <%=odiRef.getTable("L","INT_NAME","A")%>
Drop Work Table
This task drops the loading table. This command is executed if the DELETE_TEMPORARY_OBJECTS knowledge module option is selected. This option will allow to preserve the loading table for debugging.
Command on Target
drop table <%=snpRef.getTable("L", "COLL_NAME", "A")%>
Delete Errors from Controlled Table
This task removed from the controlled table (static control) or integration table (flow control) the rows detected as erroneous.
This task is always executed and has the Remove Errors option selected.
Command on Target (Oracle)
delete from <%=odiRef.getTable("L", "CT_NAME", "A")%> T
where exists (
select 1
from <%=odiRef.getTable("L","ERR_NAME", "W")%> E
where ODI_SESS_NO = <%=odiRef.getSession("SESS_NO")%>
and T.rowid = E.ODI_ROW_ID
)
The following Action Call Methods are available for Actions:
addAKs(): Call the Add Alternate Key action for all alternate keys of the current table.
dropAKs(): Call the Drop Alternate Key action for all alternate keys of the current table.
addPK(): Call the Add Primary Key for the primary key of the current table.
dropPK(): Call the Drop Primary Key for the primary key of the current table.
createTable(): Call the Create Table action for the current table.
dropTable(): Call the Drop Table action for the current table.
addFKs(): Call the Add Foreign Key action for all the foreign keys of the current table.
dropFKs(): Call the Drop Foreign Key action for all the foreign keys of the current table.
enableFKs(): Call the Enable Foreign Key action for all the foreign keys of the current table.
disableFKs(): Call the Disable Foreign Key action for all the foreign keys of the current table.
addReferringFKs(): Call the Add Foreign Key action for all the foreign keys pointing to the current table.
dropReferringFKs(): Call the Drop Foreign Key action for all the foreign keys pointing to the current table.
enableReferringFKs(): Call the Enable Foreign Key action for all the foreign keys pointing to the current table.
disableReferringFKs(): Call the Disable Foreign Key action for all the foreign keys pointing to the current table.
addChecks(): Call the Add Check Constraint action for all check constraints of the current table.
dropChecks(): Call the Drop Check Constraint action for all check constraints of the current table.
addIndexes(): Call the Add Index action for all the indexes of the current table.
dropIndexes(): Call the Drop Index action for all the indexes of the current table.
modifyTableComment(): Call the Modify Table Comment for the current table.
AddColumnsComment(): Call the Modify Column Comment for all the columns of the current table.
getObjectName(“L”, “MY_OBJECT”, “D”)
All KMs and procedures
The target datastore getTable(“L”, “TARG_NAME”, “A”) LKM, CKM, IKM, JKM
The “I$” datastore getTable(“L”, “INT_NAME”, “A”) LKM, IKM
The “C$” datastore getTable(“L”, “COLL_NAME”, “A”) LKM
The “E$” datastore getTable(“L”, “ERR_NAME”, “A”) LKM, CKM, IKM
The checked datastore getTable(“L”, “CT_NAME”, “A”) CKM
The datastore referenced by a foreign key
getTable(“L”, “FK_PK_TABLE_NAME”, “A”) CKM
No comments:
Post a Comment