9 Managing the Control Center and Repository

Starting and Stopping the Control Center Service

A group of commands starts and stops the control center service, displays its current availability, and troubleshoots a range of issues.

This group includes the following commands:

• "service_doctor" displays a variety of OWB repository and Control Center service diagnostics.

• "show_service" displays the status of the Control Center service as either Available or Not Available.

• "start_service" starts a Control Center service.

• "stop_service" stops a Control Center service.

Displaying and Managing Control Center Configuration

Another group of commands displays and modifies the properties of the Control Center, thereby modifying its behavior.

This group includes the following commands:

• "display_platform_property" displays the value of a platform property

• "set_ccs_home" sets the properties associated with a Control Center Service

• "set_platform_property" sets the value of a platform property

• "set_platform_property_from_file" sets the value of a platform property from a specified file location

Managing Control Center Deployment and Execution Jobs

The third group of commands overrides default handling of an OWB deployment or execution requests by the Control Center, deactivates or aborts a job, or expedites handling of a specific request.

This group includes the following commands:

• "abort_exec_request" terminates a run that is currently busy.

• "abort_unit_request" terminates a deployment job at the unit level.

• "deactivate_all" deactivates all incomplete deployment and execution jobs.

• "deactivate_deployment" deactivates a deployment job.

• "deactivate_execution" deactivates an execution job.

• "expedite_exec_request" expedites a running job

• "list_requests" lists the details of any active deployment or execution requests

• "print_exec_details" prints the audit execution hierarchy and details about executions to a text file

• "print_running_exec_details" prints the audit execution hierarchy and details about the incomplete executions to a text file

• "sqlplus_exec_background_template" runs the specified object in the background, as a non-blocking call.

• "sqlplus_exec_template" runs the specified object as a blocking call

OWB Repository and Location Administration Utilities

The OWB repository and locations sometimes require direct manipulation. Also, run-time audit data accumulates in the OWB repository, and some users may want to purge historical run-time audit data, to improve performance.

This fourth group includes the following commands:

• "delete_warehouse_object" deletes a warehouse object from a location in the repository

• "grant_error_table_privileges" enables the Repository Browser to view and purge error tables

• "grant_upgrade_privileges" grants the required roles and privileges for upgrade deployment actions to a specific user

• "UpdateControlCenter" sets the host, port, and service parameters for the Control Center in the OWB Client Repository.

• "UpdateLocation" sets the host, port, and service parameters for the location in the OWB Client Repository.

• "purge_audit_template" purges either deployment or execution audit data

• "reset_repository" resets the registration details of an OWB repository

• "rtrepos_report" displays the details of the Control Center and its registered locations

• "set_repository_password" sets the password for the OWB repository

abort_exec_request

The abort_exec_request.sql script terminates a run that is currently busy.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/abort_exec_request.sql auditId workspace;

• For an auditId that represents a running job, this script aborts the job cleanly. The auditId may be obtained by running the list_requests.sql script; see "list_requests".

• The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

abort_unit_request

The abort_unit_request.sql script terminates a deployment job at the unit level. A deployment unit is a collection of objects that are being deployed to the same location with the same deployment action.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/abort_unit_request.sql auditId workspace;

• For an auditId that represents a deployment unit job, this script aborts the job cleanly. The auditId may be obtained by running the list_requests.sql script; see "list_requests".

• he workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

deactivate_all

The deactivate_all.sql script deactivates all incomplete deployment and execution jobs.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/deactivate_all.sql workspace;

• The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

deactivate_deployment

The deactivate_deployment.sql script deactivates a deployment job.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/deactivate_deployment.sql auditId workspace;

• For an auditId that represents a deployment job, this script deactivates the job and all its unit jobs cleanly. The auditId may be obtained by running the list_requests.sql script; see "list_requests".

• The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

deactivate_execution

The deactivate_execution.sql script deactivates an execution job.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/deactivate_execution.sql auditId workspace;

• For an auditId that represents an execution job, this script deactivates the job cleanly. The auditId may be obtained by running the list_requests.sql script; see "list_requests".

• The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

delete_warehouse_object

The delete_warehouse_object.sql script deletes a warehouse object.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/delete_warehouse_object.sql object location workspace;

• The named object is deleted.

• The location is where the object may be found.

• The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

display_platform_property

The display_platform_property.sql script displays the value of a platform property. These properties control the behavior of the control center service.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/display_platform_property.sql property;

• The script assumes that at most one property with the given name exists. To set the property, see "set_platform_property".

expedite_exec_request

The expedite_exec_request.sql script expedites a running job by moving it to the top of the list of pending jobs.

Connect as workspaceOwner or workspaceUser with execute, deploy, and administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/expedite_exec_request.sql authId returnNumber returnCode workspace;

• For an auditId that represents a deployment job, this script expedites the job cleanly. The auditId may be obtained by running the list_requests.sql script; see "list_requests".

• The returnNumber and returnCode are the result values assigned to the job, and have the following values:

  • 0 = OK

  • 1 = OK_WITH_WARNINGS

  • 3 = FAILURE

• The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

grant_error_table_privileges

The grant_error_table_privileges.sql script enables the Repository Browser to view and purge error tables. It grants SELECT and DELETE privileges on the specified table to the OWBSYS user.

Connect as the user who owns the error table. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/grant_error_table_privileges.sql tableName;

• In this script, tableName is the name of the error table, such as TABLE1_ERR.

grant_upgrade_privileges

The grant_upgrade_privileges.sql script grants the required roles and privileges for upgrading deployment actions to a specific user.

Connect as workspaceOwner or workspaceUser with execute, deploy, and administrative privileges, such as SYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/grant_upgrade_privileges.sql userName;

• In this script, userName is the name of the schema or user who has the new upgrade privileges.

list_requests

The list_requests.sql script lists the details of any active deployment or execution requests. A deployment is a collection of deployment units, which are collections of objects deployed by the same deployment action to the same location. Each deployment or execution is uniquely identified by an audit ID that may be used as a parameter to other SQL scripts, such as "deactivate_deployment", "deactivate_execution", and so on.

Connect as workspaceOwner or workspaceUser with execute, deploy, and administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/list_requests.sql workspace;

• The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

print_exec_details

The print_exec_details.sql script prints the audit execution hierarchy and details about the various executions associated with an auditId to a file called exec_details.txt.

Connect as workspaceOwner or workspaceUser with execute, deploy, and administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/print_exec_details.sql auditId workspace;

• The auditId represents an execution job about which this script print the job details. The auditId may be obtained by running the script list_requests.sql; see "list_requests".

• The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

print_running_exec_details

The print_running_exec_details.sql script prints the audit execution hierarchy and details about the various incomplete executions that were started after the specified date associated with an auditId to a file called exec_details.txt.

Connect as workspaceOwner or workspaceUser with execute, deploy, and administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/print_running_exec_details.sql auditId workspace;

• The auditId represents an execution job about which this script print the job details. The auditId may be obtained by running the script list_requests.sql; see "list_requests".

• The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

purge_audit_template

The purge_audit_template.sql script purges either deployment audit data or execution audit data.

Connect as workspaceOwner or workspaceUser with administrative privileges. The script is of the form:

SQL> @drive:OWB_HOME/owb/rtp/sql/purge_audit_template.sql 
   workspace
   {DEPLOYMENT | EXECUTION}
   {sql_predicate | ALL | DATE_RANGE} [start_date end_date];

• The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

• The audit_type is one of:

  • DEPLOYMENT for deployment audit data; see columns in ALL_RT_AUDIT_DEPLOYMENTS view

  • EXECUTION for execution audit data; see columns in ALL_RT_AUDIT_EXECUTIONS view

• The condition can be one of:

  • sql_predicate, such as "number_script_run_errors > 0"; this must comply with SQL syntax rules

  • ALL, for purging all deployment or execution data

  • DATE_RANGE, for purging data within a specified data range

• If DATE_RANGE is used, start_date is the start of date range (such as TO_DATE('01-Jan-08') and end_date is the end of the date range (such as SYSDATE).

There are several ways to invoke this script, as demonstrated in these examples:

SQL> @drive:OWB_HOME/owb/rtp/sqlpurge_audit_template.sql 
     workspace DEPLOYMENT ALL null null

SQL> @drive:OWB_HOME/owb/rtp/sqlpurge_audit_template.sql
     workspace EXECUTION "number_task_errors > 0" null null

SQL> @drive:OWB_HOME/owb/rtp/sqlpurge_audit_template.sql
     workspace EXECUTION DATE_RANGE TO_DATE('01-Jan-2008') SYSDATE

reset_repository

The reset_repository.sql script resets the registration details for an OWB repository. It also stores the specified password as the password for the repository; see "set_repository_password". Note that after the registration details are reset, you must register the passwords for all previously registered locations.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/reset_repository.sql password

rtrepos_report

The rtrepos_report.sql script displays the details of the Control Center and its registered locations. It provides information before using the location_util.bat script to alter some of the stored credentials.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/rtrepos_report.sql

service_doctor

The service_doctor.sql script displays diagnostics about the OWB repository and its Control Center Service.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/service_doctor.sql

set_ccs_home

The set_ccs_home.sql script sets the properties associated with a Control Center Service to recognize the location of the where to pick up the Instance Number, Version and Server Side Home. These parameters are not included in an OWB Installation when the "Allow Local Control Center Service" option is set.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/set_ccs_home.sql instance version server_home

• The instance is the service node instance that you are updating

• The version is the OWB version that you are using

• The server_home is the OWB Home on the server computer, where the OWB install components are located.

set_platform_property

The set_platform_property.sql script sets the value of a platform property. These properties controls the behavior of the Control Center Service.

Note that the script assumes that the specified property must exist and its value is mutable.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/set_platform_property.sql 
     property property_value

• The property is the name of the property that is changed

• The property_value is the name of the new value of the property

• To return a list of the properties, execute the following query:

  select property_path from wb_rt_platform_properties where is_mutable <> 0
  

set_platform_property_from_file

The set_platform_property_from_file.sql script sets the value of a platform property to the contents of the file_name found in the directory. These properties controls the behavior of the Control Center Service.

Note that the script assumes that the specified property must exist and its value is mutable.

Connect as Control Center owner. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/set_platform_property_from_file.sql property directory file_name

• The property is the name of the property that is changed.

• The directory is the path where the new value for the property can be found.

• The file_name is the name of the file that contains the new value of the property.

set_repository_password

The set_repository_password.sql script sets the password for the OWB repository. This password is used by the Control Center Service at startup time.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/set_repository_password.sql password

• The password is the new password value.

show_service

The show_service.sql script displays the status of the Control Center Service, which provides access to the deployment and execution features of an OWB repository. The valid return values for the status of the Control Service Center are Available or Not Available. To change the status of the Control Center Service, use the SQL scripts start_service and stop_service as appropriate.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/show_service.sql

sqlplus_exec_background_template

The sqlplus_exec_background_template.sql script runs the specified object in the background, not blocking the caller. To get the task status and return result, check public views, of the type ALL_RT_*. Use the script "sqlplus_exec_template" to run the object in the foreground, which blocks the caller until the completion of the task. In its unchanged form the script takes the three keys required to identify the executable task. The task runs in the background with the default parameters configured prior to deployment. The custom_params (defined on the object in the OWB Designer) and system_params values override the default input parameters of the task. The script returns the following values: 1 if task reports SUCCESS, 2 if WARNING, 3 if ERROR.

Connect as workspaceOwner or workspaceUser with execute privilege. The script is of the form:

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_background_template.sql 
   workspace
   location_name 
   {PLSQLMAP | SQLLOADERCONTROLFILE | PROCESSFLOW | ABAPFILE |
      DATAAUDITOR | SCHEDULEDJOB} 
   [parent/]task_name
   system_params
   custom_params

• The workspace is nominated in the parameters, and should be declared as workspaceOwner.workspaceName; if using only workspaceName, workspaceOwner defaults to user.

• The location_name is the physical name of the location to which the task is deployed.

• The task_type has the following options:

  • PLSQLMAP is used for OWB PL/SQL mapping.

  • SQLLOADERCONTROLFILE is used for OWB SQL*Loader mapping.

  • PROCESSFLOW is used for OWB ProcessFlow.

  • ABAPFILE is used for OWB SAP mapping.

  • DATAAUDITOR is used for OWB DataAuditor mapping.

  • SCHEDULEDJOB is used for an OWB scheduled job.

  • CTMAPPING is used for OWB Code Template mapping.

• The task_name is the physical name of the deployed object; it can be optionally qualified by the name of the deployed parent, like the ProcessFlow package name. A module name cannot be used because it is not a deployable object.

• The custom_params (defined on the object in the OWB Designer) and system_params values override the default input parameters of the task

There are several ways to invoke this script; note that system_params and custom_params are not used in these examples:

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_background_template.sql 
     workspace warehouse PLSQL mapping "," ","

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_background_template.sql 
     workspace platform_schema SQL_LOADER load "," ","


SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_background_template.sql 
     workspace workflow PROCESS process_1 "," ","

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_background_template.sql 
     workspace platform_schema SAP sap_1 "," ","

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_background_template.sql 
     workspace warehouse DATA_AUDITOR data_auditor_1 "," ","

Note: The comma character can be escaped using the backslash character (\,); likewise the backslash character can be escaped by using two backlash characters (\\). A single-quote character must be replaced by a sequence of four single-quote characters ('''').

sqlplus_exec_template

The sqlplus_exec_template.sql script runs the specified object and blocks the caller until the task is completed. To get the task status and return result, check public views of the type ALL_RT_*. Use the script "sqlplus_exec_background_template" to run the object in the background, without blocking the caller throughout the completion of the task. The script takes the three keys required to identify the executable task. The task runs with the default parameters configured prior to deployment. The custom_params (defined on the object in the OWB Designer) and system_params values override the default input parameters of the task. The script returns the following values: 1 if task reports SUCCESS, 2 if WARNING, 3 if ERROR.

Connect as workspaceOwner or workspaceUser with execute privilege. The script is of the form:

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_template.sql 
   workspace
   location_name 
   {PLSQLMAP | SQLLOADERCONTROLFILE | PROCESSFLOW | ABAPFILE |
      DATAAUDITOR | SCHEDULEDJOB} 
   [parent/]task_name 
   system_params 
   custom_params

• The workspace is nominated in the parameters, and should be declared as workspaceOwner.workspaceName; if using only workspaceName, workspaceOwner defaults to user.

• The location_name is the physical name of the location to which the task is deployed.

• The task_type has the following options:

  • PLSQLMAP is used for OWB PL/SQL mapping.

  • SQLLOADERCONTROLFILE is used for OWB SQL*Loader mapping.

  • PROCESSFLOW is used for OWB ProcessFlow.

  • ABAPFILE is used for OWB SAP mapping.

  • DATAAUDITOR is used for OWB DataAuditor mapping.

  • SCHEDULEDJOB is used for an OWB scheduled job.

  • CTMAPPING is used for OWB Code Template mapping.

• The task_name is the physical name of the deployed object; it can be optionally qualified by the name of the deployed parent, like the ProcessFlow package name. A module name cannot be used because it is not a deployable object.

• The custom_params (defined on the object in the OWB Designer) and system_params values override the default input parameters of the task

There are several ways to invoke this script; note that system_params and custom_params are not used in these examples:

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_template.sql 
     workspace warehouse PLSQL mapping "," ","

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_template.sql 
     workspace platform_schema SQL_LOADER load "," ","


SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_template.sql 
     workspace workflow PROCESS process_1 "," ","

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_template.sql 
     workspace platform_schema SAP sap_1 "," ","

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_template.sql 
     workspace warehouse DATA_AUDITOR data_auditor_1 "," ","

Note: The comma character can be escaped using the backslash character (\,); likewise the backslash character can be escaped by using two backlash characters (\\). A single-quote character must be replaced by a sequence of four single-quote characters ('''').

start_service

The script start_service.sql starts a Control Center Service that is managed by the database. A Control Center Service, when available, provides access to the deployment and execution features of an OWB repository. To determine if the Control Center Service is available, use the show_service script; the return values are either Available or Not Available. To stop the service, use the stop_service script. When the service is invoked using this script, a monitoring job is created; it restarts the service if it fails, or if the database is brought back online. Alternatively, a Control Center Service can be started in a different OWB_HOME, on another host; the service is available only when that host is available and provided the service has been started and not stopped.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/start_service.sql

stop_service

The script stop_service.sql stops a Control Center Service that is managed by the database. A Control Center Service, when available, provides access to the deployment and execution features of an OWB repository. To determine if the Control Center Service is available, use the show_service script; the return values are either Available or Not Available. To start the service, use the start_service script. When the service is stopped using this script, the monitoring job is removed; the service remains disabled until it is re-started by the start_service script.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/stop_service.sql

UpdateControlCenter

This sql script sets the host:port:service parameters for a control center in the OWB Client Repository. The connection type must be host:port:service .

UpdateLocation

This sql script sets the host:port:service parameters for a location in the OWB Client Repository. The connection type must be host:port:service. The location may be registered or unregistered.