OMB+ 101: 2012

Wednesday, May 2, 2012

OMB+/OWB Revoke a privilege

OWB has the habbit of only revoking or granting priviliges ont creation of a role or object. If you change a role you have to manually change them. Here is a script to revoke privileges:

###########################################################
#                                                            #
# (C) Ciber Nederland John Minkjan    OMB101.blogspot.com        #
#        V001    Init Version                                #
#        V002    Validated against OWB11gR2                    #
#                                                            #
############################################################
#OMBCONNECT rep_owner/rep_owner@localhost:1521:orcl USE WORKSPACE 'My_Workspace';

set OMBPROMPT ON;

set RoleList [list EVERYONE]
set ProjectList [list ENECO_DWH]
set RevokeList [list COMPILE EDIT FULL_CONTROL]

foreach Project $ProjectList {
OMBCC '/$Project';

set OracleModuleList [OMBLIST ORACLE_MODULES]
foreach OracleModule $OracleModuleList {
put $OracleModule
foreach Role $RoleList {

foreach Revoke $RevokeList {

OMBREVOKE OBJECT_PRIVILEGE $Revoke ON ORACLE_MODULE '$OracleModule' CASCADE FROM ROLE '$Role' ;


}
}
OMBCOMMIT;
OMBSAVE;
}
}

Till Next Time

OWB/OMB Granting a privilege

OWB has the habbit of only revoking or granting priviliges ont creation of a role or object. If you change a role you have to manually change them. Here is a script to grant privileges:

###########################################################
#                                                            #
# (C) Ciber Nederland John Minkjan    OMB101.blogspot.com        #
#        V001    Init Version                                #
#        V002    Validated against OWB11gR2                    #
#                                                            #
############################################################
#OMBCONNECT rep_owner/rep_owner@localhost:1521:orcl;

set ProjectList [list ENECO_DWH]
set GrantList [list READ]
set RoleList [list EVERYONE READ_ONLY]

foreach Project $ProjectList {
OMBCC '/$Project';

set OracleModuleList [OMBLIST ORACLE_MODULES]
foreach OracleModule $OracleModuleList {
put $OracleModule
foreach Role $RoleList {

foreach Grant $GrantList {
    OMBGRANT OBJECT_PRIVILEGE $Grant \
    ON ORACLE_MODULE '$OracleModule' \
    CASCADE TO ROLE '$Role' ;
}
}
OMBCOMMIT;
OMBSAVE;
}
}

Till Next Time

Wednesday, April 25, 2012

OMB+/OWB create, register, delete en unregister an OWB Location:

How to create, register, delete en unregister an OWB Location:

#####
# CREATE LOCATION
#####

OMBCREATE LOCATION 'SH_LOC' \
SET PROPERTIES \
(BUSINESS_NAME, DESCRIPTION, TYPE, VERSION, CONNECTION_TYPE, USER_NAME,HOST,PORT,SERVICE_NAME) \
VALUES \
('SH_LOC','Sales History on localhost','ORACLE_DATABASE', '11.2' , 'HOST_PORT_SERVICE','SH','localhost','1521','orcl.world');

OMBCOMMIT;

OMBCONNECT CONTROL_CENTER;

OMBCOMMIT;

OMBALTER LOCATION 'SH_LOC' SET PROPERTIES (PASSWORD) VALUES ('password');

OMBCOMMIT;

OMBREGISTER LOCATION 'SH_LOC';

OMBCOMMIT;

#####
# DELETE LOCATION
#####

OMBUNREGISTER LOCATION 'SH_LOC';

OMBCOMMIT;

OMBDROP LOCATION 'SH_LOC';

OMBCOMMIT;

Till Next Time

Tuesday, April 24, 2012

OWB/OMB+ connect to oracle database

Some handy function if you want to query an oracle database directly from OMB+

###########################################################
#                                                         #
# Oracle DataBase Connection                              #
#    Functions to execute select statements on a           #
#   Oracle DB using OMB+ / tcl                            #
#                                                         #
# (C) Ciber Nederland John Minkjan    OMB101.blogspot.com    #
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
#                                                         #
###########################################################
# based on orginal code from Michael Broughton aka ZEPPO
# file: OMB_ORCL_MASTER.tcl

package require java

OMB OWB TCL Sorting table partitions

When migrating from OWB10.1 to 11gR2 the partition order (sometimes) gets scrambeld and the table will not validate. This scripts sets the partitions back in (alphabetical) order .

[script]
###########################################################
#                                                         #
# Sort Table Partitions                                   #
#                                                          #
# Note: When migrating from OWB10.1 to 11gR2 the          #
#       partition order gets scrambeld and the table      #
#       will not validate                                 #
#       This scripts sets the partitions back in          #
#       alphabetical order                                #
# (C) Ciber Nederland John Minkjan    OMB101.blogspot.com    #
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
#                                                         #
###########################################################

set ProjectList [list ********]
foreach Project $ProjectList {
    OMBCC '/$Project'
    set OracleModuleList [OMBLIST ORACLE_MODULES]
        foreach OracleModule $OracleModuleList {
        puts $OracleModule
        OMBCC '/$Project/$OracleModule';
        set TableList [OMBLIST TABLES];
        foreach Table $TableList {
            set PartitionList [OMBRETRIEVE TABLE '$Table' GET PARTITIONS];
            if { $PartitionList != ""} {
                puts "-- $Table "
                set PartitionListS [lsort $PartitionList]
                if { $PartitionList != $PartitionListS} {
                    set partno 1
                    foreach Partition $PartitionListS {
                        OMBALTER TABLE '$Table' MODIFY PARTITION '$Partition' MOVE TO POSITION $partno
                        puts "$OracleModule - $Table - $Partition - $partno"
                        set partno [expr $partno + 1 ]
                    }
                    OMBCOMMIT;
                }
            }
        }
    }
}
[\script]

Thursday, March 1, 2012

OMB OWB TCL Mapping set maximum number of errors

By default the maximum number of errors in a mapping is 50..... Here is a script to change it to 0:
############################################################
#                                                          #
# Change Maximum Number Of Errors                          #
#                                                          #
# (C) Ciber Nederland John Minkjan                         #
#                     http:\\OMB101.blogspot.com           #
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
# orignal idea: Borkur Steingrimsson                       #
#                                                          #
############################################################
#Disconnect if connected
catch {
    OMBDISCONNECT;
    };

OMBCONNECT OWB_OWN/******@***********:1521:DWH_A2
set OMBPROMPT ON

set projectList [OMBLIST PROJECTS]
foreach projectName $projectList {
    OMBCC '$projectName'
    puts "Project: $projectName"
    set moduleList [OMBLIST ORACLE_MODULES]
    foreach moduleName $moduleList {
        puts "Process: $moduleName"
        OMBCC '$moduleName'
        set mappingList [OMBLIST MAPPINGS]
        foreach mappingName $mappingList {
            puts "Altering: $moduleName/$mappingName"
            OMBALTER MAPPING '$mappingName' SET PROPERTIES (MAXIMUM_NUMBER_OF_ERRORS) VALUES ('0')
        };
    OMBCC '..'
    };
    OMBCC '..'
};
(Kudos to Borkur: http://www.rittmanmead.com/2007/07/a-little-bit-of-omb-goes-a-long-way/)

Till Next Time

Friday, February 17, 2012

OMB single quote in tcl

The other day I was fighting to get a single quote, a double quote and a single qoute within a string. After trying a number of escape sequences David Allen handed me the solution on OTN:
https://forums.oracle.com/forums/thread.jspa?threadID=2347623&tstart=0
Just use two single quotes for one ....

Till Next Time

Wednesday, February 15, 2012

OMB OWB TCL Repair NULL.

During the migration constants with the value NULL where migration as 'NULL' (with quotes). I create a script to replace them trough the whole repository with TO_CHAR(NULL).
############################################################
#                                                          #
# Set Expression OWB Repair NULL                           #
#                                                          #
# During Migration from OWB10.1 to 11gR2 an Expression     #
# NULL is migrated as 'NULL' making it a VARCHAR(4)        #
# This script repairs it for all Operators called:         #
# - C_STANDAARD                                               #
#                                                          #
# (C) Ciber Nederland John Minkjan                         #
#                     http:\\OMB101.blogspot.com           #
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
#                                                          #
############################################################

#Disconnect if connected
catch {
    OMBDISCONNECT;
    };

# Connect
OMBCONNECT ****/*******@*******:1521:OWB_POC \
USE WORKSPACE '*******';

# Set OMBPROMPT
set OMBPROMPT ON;

# Set ProjectList
set ProjectList [list ********];
foreach Project $ProjectList {
    OMBCC '$Project'
    set ModuleList [OMBLIST ORACLE_MODULES]
    #set ModuleList [list DM_DNI]
    foreach Module $ModuleList {
        OMBCC '$Module';
        set MappingList [OMBLIST MAPPINGS]
        foreach Mapping $MappingList {
            set OperatorList [OMBRETRIEVE MAPPING '$Mapping' GET OPERATORS];
            foreach Operator $OperatorList {
                    if {$Operator == "C_STANDAARD"} {
                        puts "$Module - $Mapping - $Operator - ";
                        set GroupsList [OMBRETRIEVE MAPPING '$Mapping' \
                                            OPERATOR '$Operator' \
                                            GET GROUPS];
                        foreach Group $GroupsList {
                            puts "$Module - $Mapping - $Operator - $Group ";
                            set AttributeList [OMBRETRIEVE MAPPING '$Mapping' \
                                                OPERATOR '$Operator' \
                                                GROUP '$Group' \
                                                GET ATTRIBUTES]
                            foreach Attribute $AttributeList {
                                puts "$Module - $Mapping - $Operator - $Group - $Attribute ";
                                set Expression [OMBRETRIEVE MAPPING '$Mapping' \
                                                OPERATOR '$Operator' \
                                                GROUP '$Group' \
                                                ATTRIBUTE '$Attribute' \
                                                GET PROPERTIES (EXPRESSION)]
                                puts "Current Expression: $Expression"
                                if {$Expression == "\'NULL\'"} {
                                    puts "BINGO"
                                    puts [OMBALTER MAPPING '$Mapping' \
                                            MODIFY ATTRIBUTE '$Attribute' \
                                            OF GROUP '$Group' \
                                            OF OPERATOR '$Operator' \
                                            SET PROPERTIES (EXPRESSION) VALUES('TO_CHAR(NULL)')]
                                    set Expression [OMBRETRIEVE MAPPING '$Mapping' \
                                                    OPERATOR '$Operator' \
                                                    GROUP '$Group' \
                                                    ATTRIBUTE '$Attribute' \
                                                    GET PROPERTIES (EXPRESSION)]
                                    puts "Current Expression: $Expression"
                                    OMBCOMMIT;
                                }
                                };
                            };
                        };
                    };
            };
        OMBCC '..';
        };
    OMBCOMMIT;
    OMBCC '..';
    };
OMBSAVE;

Download found here: Repair NULL

Till Next Time

Wednesday, February 8, 2012

OMB OWB “CURRENT” Constants

Some nice constants to use in your scripts:

$OMB_CURRENT_PROJECT: Stores the name of the current project.
$OMB_CURRENT_SESSION: Stores the identifier of the current session.
$OMB_CURRENT_USER: Stores the user ID of the current user.

Till Next Time

OMB Set Tablespaces

############################################################
#                                                          #
# Set Tablespace OWB TABLES                                #
#                                                          #
# (C) Ciber Nederland John Minkjan                         #
#                     http:\\OMB101.blogspot.com           #
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
#                                                          #
############################################################
# use connection file
source C:\\OMB_CONNECT.tcl

# Switch ON Prompt
set OMBPROMPT ON;

set projectList [list ********];

foreach project $projectList {
    OMBCC '$project';
    set moduleList [OMBLIST ORACLE_MODULES];
    puts $moduleList;
    foreach module $moduleList {
        OMBCC '$module'
        set tableList [OMBLIST TABLES];
        foreach table $tableList {
        set alterTableCommand [OMBALTER TABLE '$table' SET PROPERTIES (TABLESPACE) VALUES ('USERS')];
        puts alterTableCommand;
        };
        puts $tableList;
        OMBCC '..';
    };

    OMBCC '..';
}

OMBCOMMIT;
OMBDISCONNECT;

File can be found here:

https://sites.google.com/site/filesof101/omb101/OMB_CHANGE_LOCATION.tcl?attredirects=0&d=1

Till Next Time

OMB Change Location

############################################################
#                                                          #
# Change Location Properties                               #
#                                                          #
# (C) Ciber Nederland John Minkjan                         #
#                     http:\\OMB101.blogspot.com           #
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
#                                                          #
############################################################

# use connection file
source C:\\OMB_CONNECT.tcl

# Switch ON Prompt
set OMBPROMPT ON;

set LocationList [OMBLIST LOCATIONS];

foreach Location $LocationList {
    if [catch {
    set GetLocationType [OMBRETRIEVE LOCATION '$Location' GET PROPERTIES (CONNECTION_TYPE)];
    } errmsg] { set GetLocationType 'UNKNOWN'};
    if {$GetLocationType == "HOST:PORT:SERVICE"} {
        if {$Location != "OWB_REPOSITORY_LOCATION"} {
            set alterLocationCommand [OMBALTER LOCATION '$Location' SET PROPERTIES (HOST) VALUES ('HOSTNAME')];
            set alterLocationCommand [OMBALTER LOCATION '$Location' SET PROPERTIES (PORT) VALUES ('1521')];
            set alterLocationCommand [OMBALTER LOCATION '$Location' SET PROPERTIES (SERVICE_NAME) VALUES ('DWH_POC')];
            set alterLocationCommand [OMBALTER LOCATION '$Location' SET PROPERTIES (VERSION) VALUES ('11.2')];

            set alterLocationCommand [OMBALTER LOCATION '$Location' SET PROPERTIES (USER_NAME) VALUES ('$Location')];
            set alterLocationCommand [OMBALTER LOCATION '$Location' SET PROPERTIES (PASSWORD) VALUES ('********')];
            # PASSWORD isn't always accepted, has to be checked manualy

            OMBCOMMIT;
        }
    };
};
OMBCOMMIT;
OMBDISCONNECT;

Fill can be downloaded here: https://sites.google.com/site/filesof101/omb101/OMB_CHANGE_LOCATION.tcl?attredirects=0&d=1

Till Next Time

Wednesday, May 2, 2012

Wednesday, April 25, 2012

Tuesday, April 24, 2012

Friday, March 16, 2012

Thursday, March 1, 2012

Friday, February 17, 2012

Wednesday, February 15, 2012

Wednesday, February 8, 2012