Oracle MetaBase Plus Oracle Database Tips by Donald Burleson

While Oracle has made great headway with the GUI interfaces in Oracle 10g Enterprise Manager, senior Oracle professionals still feel the need to use more-robust scripting environments. A GUI can't do it all, and advanced operations require more flexibility and sophisticated job-control logic.  Oracle has two tools for complex job management, dbms_scheduler and Oracle MetaBase for Warehouse Builder.  These tools allow:

• Scheduled execution - Fire-off job streams at pre-determined times, with pre-defined prerequisite conditions. Ensure that a "missed" job is re-scheduled.

• Conditional execution - execute this task based on the status of completed tasks.

• Error alerts and job stream validation - The Oracle DBA needs to define the scope of everything that might go-wrong and create user-exits to pause execution until important issues are resolved.

Oracle MetaBase (OMB) is an extension of the Tcl language (pronounced "tickle") and it has all of the robust features of any programming language, including complex Boolean operators, variable support, and loops (FOR, WHILE, REPEAT-UNTIL). 

Oracle Warehouse Builder offers OMB as a way to integrate all Extract, Transform and Load (ETL) activities by providing built-in tools that provide error-checking, validation and data mappings.  When combined with OMB, it's sort of like a TP monitor (CICS) for the OWB environment.

The Oracle warehouse Tcl extensions are customized into OMB, so you can perform complex data mappings and program job streams. Like Java, OMB is platform independent and it requires no changes to run MetaBase on a Mainframe or a Macintosh.

OMB has two classes of commands:

• Metadata Definition Language (MDL) - Like DDL, it allows you to create and drop OMB objects.  MDL object types include projects, modules, tables, mappings, and workflow processes.
   

• Metadata Manipulation Language (MML) - Analogous to Oracle DML, the MML commands allow us to alter (add, update, delete) named objects. 

The basic OMB commands include:

• OMBALTER - This is used to alter the metadata for a Warehouse Builder component.
• OMBCREATE - use this command to create a component in OWB, which might be a project, module, table, mapping, or workflow processes.
• OMBDROP - Used to drop named objects from the repository.
• OMBCC - This is the "change context" command and it is used like the UNIX and DOS "cd" command to change OWB directories.
• OMBCOMMIT - Commits a transaction
• OMBCOPY - This is a handy command for cloning a section of use this command to copy one or more objects of the same type.
• OMBDEPLOY - This deploys an object to a specified database via the OWB runtime tables.
• OMBLIST - the OMBLIST command is like a directory listing command (ls or dir) and it lists all OWB objects under a specific hierarchical tree.
• OMBRECONCILE - This is used to reconcile the target OWB metadata definition with the target database metadata definition.

Dr. Tim Hall has an excellent book on the job stream management titled "Oracle Job Scheduling", a comprehensive reference for performing complex job executions in an Oracle environment.