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
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)
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
- 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
Dr. Tim Hall has an excellent book on the job stream management
Job Scheduling", a comprehensive reference for performing
complex job executions in an Oracle environment.