Using OMBPlus to collectively change schemas for tables in mappings

Ideally all configuration properties for OWB mappings need not be changed half way into a project. With a large number of mappings, this can be a time consuming job. Once again, OMBPlus can be a big help here.

The example below shows how to collectively change the schema name for a set of mappings to a new one. This example assumes that mapping names are named after their target table, and that the mappings are preceded by a 8 letter prefix (e.g. “map_stg_” for a staging mapping).


OMBCONNECT user/password@fully-qualified-connect-string USE REPOS 'repository'
OMBCC 'your_project'
OMBCC 'your_module'
set mapList [OMBLIST MAPPINGS 'your_mappings']
foreach mapName $mapList {
set operName [string range '$mapName' 9 end]

if {$Val == "old_schema"} {
OMBALTER MAPPING '$mapName' \
MODIFY OPERATOR '$operName' SET PROPERTIES (SCHEMA) VALUES ('new_schema')
puts [concat $mapName ": changed SCHEMA from [old_schema] to [new_schema]" ]
}
}
OMBCOMMIT
OMBDISCONNECT