I’m currently building a warehouse using ODI, so I’ll share some findings as it goes. And there’s be Cognos BI on top of it and EP is one of the sources, so I’m still close to the roots )
Disclaimer: I’m Oracle & ODI n00b — there are (?) better ways of accomplishing these tasks and if you can point in right direction, i’d be eternally grateful.
So the task is to drop all indexes on a table before loading data (loading gets significantly faster, as they write in 1st grade textbooks these days). And not to specify every index to drop manually, since index are built by separate package and their names are not passed to loading part.
It can be done rather easy:
1 Get all table indexes from ALL_INDEX table
2 Form a drop command for every index
3 Execute command
Couldn’t figure a nice&clear way of doing it via sql procedure, but jython solution is easy. Table_Name is a procedure option telling what table’s indexes to drop.
You can put such a step in Knowledge Module and get the option (Drop_Indexes) and use a substitution method getJDBCConnection(Target) instead of specifically writing out connection string to database. And beware of tables with same names — it’s not checked here.
Jython Procedure Code:
import java
# Set your connection. If including this as a step in IKM, you'd better use JDBCConnection substition method instead of 2 next steps
driver, url, user, passwd = ('oracle.jdbc.driver.OracleDriver','jdbc:oracle:thin:@server:port:instance','user','password')
# Register Driver
java.lang.Class.forName(driver)
# Create a Connection Object
myCon = java.sql.DriverManager.getConnection(url, user, passwd)
# Create a Statement
selectStmt = myCon.createStatement ()
# Run a Select Query and get a Result Set
selectRs = selectStmt.executeQuery ("select INDEX_NAME from ALL_INDEXES WHERE table_name = '<%=odiRef.getOption( "Table" )%>'")
# Loop over the Result Set
dropStmt = myCon.createStatement ()
while (selectRs. next ()):
dropStmt.executeQuery ( "DROP INDEX " + "%s" %(selectRs. getString ("INDEX_NAME")))
myCon.close()