4 ORASE Common Functionality

This chapter addresses functionality that is common throughout ORASE and contains the following sections:

• Service Manager

• Restart and Recovery

• Debugging and Message Logging

• As Is Aggregates

• Maintenance

Service Manager

Service Manager offers a single framework that provides a consistent way of interacting with a service and of queuing multiple requests for a service. Any service that needs a preprocessing step, a processing step, and a postprocessing step, for up to two layers of tasks, can be handled via this service framework.

Process Flow

Service Manager works in such a way that the invoking application invokes a package to assist with the scheduling and invocation of a specified MBA service. The service is then executed and the results are stored in the MBA staging tables wherever the service implementation needs to store them.

Service Implementation

There are several components involved in an MBA Service implementation. They include database tables, packages, and extendable object types. The implementation provides provisions for a calling application to request by registering in the RSE_Srvc_Config table and interact with a service for execution and the retrieval of results. It also provides ways of building an execution queue of service requests, which are invoked by a service manager database package RSE_Srvc_mgr.

The MBA DB Service allows the creation of multiple services that you can interact with in a consistent manner. Additionally, each of these services should be able to use parallel processing of multiple service requests.

Services implementation is a two-step process.

1. Set up the service: Queuing the service request so that it can be executed later.

2. Process the queue: Executing the service queue that was set up.

   Setting up a service can be done in the following way:

   begin  rse_srvc_mgr.batch_pre_process(<ServiceType>,<ServiceName>);end;
   

   Processing the queue can be done in the following way:

   begin  rse_srvc_mgr.process_queue(<ServiceType>);end;
   

   <ServiceType> and <ServiceName> are the valid Service Types and Service Names configured in the Service configuration table RSE_SRVC_CONFIG.

Service Objects

This section defines the database tables used to implement the Service Manager and the routines used by the Service Manager.

Service Tables

Table 4-1 defines the database tables being used to implement the service manager feature.

Table 4-1 Service Manager Database Tables

Name	Description
RSE_SRVC_TYPE	This table contains a list of service types, to which a specific service implementation can belong. This allows grouping of related service requests. Example service types are ARM and BL.
RSE_SRVC_CONFIG	This table contains the service configurations to be used for the different MBA services and is configured as a part of installation. Though this table allows for reconfiguring services in order to implement different service implementation, this is generally not intended for customer configuration.
RSE_SRVC_REQ_STATUS	This table defines the status of the different service requests. The contents of this table are implemented as rows in this table, and also as constants with the RSE_SRVC_MGR package.
RSE_SRVC_REQ_QUEUE	This table contains a queue of service requests that are to be executed by the Service Management database package. This table allows the storing of large number of requests, so that they can be executed in parallel threads and in a defined order.
RSE_SRVC_REQ_QUEUE_PROP	This table contains the various property key and values that are used for the execution of a queued service request.

Service Package: RSE_Srvc_Mgr

Service Manager uses a database package RSE_Srvc_Mgr that contains various helper routines, listed in Table 4-2, to assist with the setup, execution, and clean up of services and any service requests.

Table 4-2 Service Package Routines

Name	Input Parameters and Types	Description
batch_pre_process	p_srvc_type VARCHAR2,p_srvc_name VARCHAR2, p_cancel_failed_srvc_scope VARCHAR2 DEFAULT 'TYPE'	Helper routine to retrieve an MBA Service, set up the service for execution, and add it to the service queue.
get_srvc	p_srvc_type VARCHAR2, p_srvc_name VARCHAR2	Routine to retrieve a service based on the service identification that is provided.
process_queue	p_srvc_type VARCHAR2, p_srvc_name VARCHAR2	Routine to initiate processing of a queue of service requests.
cancel_srvc	p_srvc_type VARCHAR2, p_srvc_name VARCHAR2,p_srvc_req_grp VARCHAR2, p_srvc_id NUMBER	Cancels the execution of either an entire service queue, a service request group, or an individual service request.
cancel_failed_srvc_req	p_srvc_type VARCHAR2, p_srvc_name VARCHAR2	Cancels any failed service requests that relate to the provided parameters.
pause_srvc	p_srvc_type VARCHAR2, p_srvc_name VARCHAR2,p_srvc_req_grp VARCHAR2, p_srvc_id NUMBER	Pauses the execution of either an entire service queue, a service request group, or an individual service request.
resume_srvc	p_srvc_type VARCHAR2, p_srvc_name VARCHAR2,p_srvc_req_grp VARCHAR2, p_srvc_id NUMBER	Resumes the execution of either an entire service queue, a service request group, or an individual service request.
retry_srvc	p_srvc_type VARCHAR2, p_srvc_name VARCHAR2	Retries the execution of any failed service requests within an entire service queue, a service request group, or an individual service request.

Restart and Recovery

The services invoke two routines in which one of them initializes the process queue by invoking rse_srvc_mgr.batch_pre_process and the other one executes the process queue by invoking rse_srvc_mgr.process_queue. If the process fails during any of the setup process queue, it may be restarted. There is no adverse effect to re-running the setup process queue that has already failed until the process queue gets executed.

However, if a failure occurs during the process execution stage, once the underlying cause of that failure has been resolved, the execute process may be restarted, and it will restart any failed processes. It is important to note that while resolving a failure in the process execution step, the process queue initialization step should not be rerun. Doing that would leave transient tables, and would end up reprocessing previously completed steps, which may have already been written to the staging tables. This in turn would cause unique constraint errors when those processes are rerun.

Debugging and Message Logging

Various levels of compilation options are available in the code being installed. They can be enabled or disabled in any desired combination. None of these options must be enabled, but if additional debugging information is required, these settings can help:

• DEBUG_MODE provides various debugging information and logging for a routine.

• DEBUG_DETAIL provides detailed debugging information. This type of information is more verbose.

• DEBUG_DATA allows retention of any transient data objects that a process creates in order to enable a closer review of the data that the process creates.

• DEBUG_TRACE captures the start and end times of most routines, along with the parameters used for the routine, in the log table.

Not all objects support all debugging options, but enabling an option that is not used will produce no adverse effects.

An example of enabling all of the above compilation options is:

alter type MBA_ARM_RTL_TOP10_T compile PLSQL_CCFLAGS = 'DEBUG_MODE:TRUE, DEBUG_DATA:TRUE, DEBUG_DETAIL:TRUE, DEBUG_TRACE:TRUE';

An example of disabling all of the above options:

alter type MBA_ARM_RTL_TOP10_T compile PLSQL_CCFLAGS = 'DEBUG_MODE:FALSE, DEBUG_DATA:FALSE, DEBUG_DETAIL:FALSE, DEBUG_TRACE:FALSE';

An example of enabling only DEBUG_MODE:

alter type MBA_ARM_RTL_TOP10_T compile PLSQL_CCFLAGS = 'DEBUG_MODE:TRUE';

Logging Table

The error messages for errors in the processing and the debug messages due to the enabling one of the compilation option are stored in the table RSE_LOG_MSG. The following information is available in the table.

• Debug or Error Logging Message

• Program Units and Routine Name associated

• Logging Level to identify if the message is due to debug compilation options or errors

• Date and Time when the message was registered

Transient Tables

Transient tables that are created internally during processing, which are named with the prefix TMP$ internally, are generally deleted once the process gets finished. When the application enables the DEBUG_DATA compilation option to facilitate the capturing of any transient objects, the tables are archived into a DEBUG$ table of a similar name without the identifying prefix of the temporary tables. These tables become partitions within the matching DEBUG$ table, where the partition ID is equal to the service request ID prefixed with 'P_', which created the transient data.When enabled, DEBUG_DATA retains transient data and occupies space in the database, so it should be used with caution. This option should never be used when running in a weekly production batch schedule. The option should be enabled only for special runs of the process in order to diagnose any issues with a process and in cases where the issues cannot be found out without retaining the transient data. It should be disabled as soon as the process to be diagnosed is finished. Keeping DEBUG_DATA option enabled will produce adverse DB Sizing effects.

For example, if there is a problem with the MBA's ARM Service ANC_SC and debugging with retaining the data is required to analyze the data, enabling debug on the generic ARM Service, the TOP10 Service, and the ANC_SC Service are needed since all three are involved. Refer to Figure 7-4, "Market Basket Analysis ARM Services Object Types Hierarchy" when enabling DEBUG_DATA for a service so that all levels have consistent support. After running the services, disable debug on these services, perform whatever data review is required to determine the issue, and clean up the temporary tables.

To enable the DEBUG_DATA for ANC_SC Service:

alter type MBA_ARM_RTL_ANC_SC_T compile PLSQL_CCFLAGS = 'DEBUG_DATA:TRUE';

alter type MBA_ARM_RTL_TOP10_T compile PLSQL_CCFLAGS = 'DEBUG_DATA:TRUE';

alter type MBA_ARM_SRVC_T compile PLSQL_CCFLAGS = 'DEBUG_DATA:TRUE';

To disable DEBUG_DATA for ANC_SC Service:

alter type MBA_ARM_RTL_ANC_SC_T compile PLSQL_CCFLAGS = 'DEBUG_DATA:FALSE';

alter type MBA_ARM_RTL_TOP10_T compile PLSQL_CCFLAGS = 'DEBUG_DATA:FALSE';

alter type MBA_ARM_SRVC_T compile PLSQL_CCFLAGS = 'DEBUG_DATA:FALSE';

As Is Aggregates

All of the ARM processing is based on product hierarchy base aggregation results such as Class, Subclass and Department. When the process runs each week, it uses the copies of the product hierarchy as it exists right now.

Baseline service does everything based on as is results. However, as the Baseline processing is at the product level and not hierarchy based, it does not really matter.

Maintenance

This section contains information about ORASE DB maintenance.

PROTO$ Tables

ORASE has prototype tables that are created during installation, which are named with the prefix PROTO$. These help to keep only the structure of a database table and in the optimization of the database objects. For example, during processing, MBA reads the PROTO$ tables and dynamically creates permanent or temporary tables exactly like PROTO$ tables structure.Under normal circumstances, PROTO$ tables do not need to be altered. Other than modifying column properties, any of the table properties such as storage parameters (PCTUSED, PCTFREE, TABLESPACE) or parallel options (PARALLEL) can be modified in the PROTO$ tables if needed to improve MBA processing. Such modifications can impact the temporary or permanent tables that are created based on the PROTO$ tables. Columns should not be modified for any reason. The options that are most suitable for modification include changing the table space, the compression option, and the parallel degree option.For example, if you change the table PROTO$RSE_CUR_CUST_CUSTSEG_D with PCTUSED=50, PCTFREE=10, TABLESPACE=ETL_DATA_TS with option PARALLEL, the permanent table RSE_CUR_CUST_CUSTSEG_D will be created with PCTUSED=50, PCTFREE=10 in the table space ETL_DATA_TS with PARALLEL option during ETL processing.Any PROTO$ tables that have partitions already should not have their partitioning strategy changed. For example, as the tables PROTO$MBA_BL_OUTPUT, PROTO$MBA_BL_RANGE_SLS_A, PROTO$RSE_CUR_CUST_CUSTSEG_D already have partitions, their partition strategy should not be changed.Any PROTO$ tables that are not already partitioned can be altered to have partitioning if partitioning will improve the database performance. However, it is important to note that if debugging is supported using the DEBUG_DATA compilation option, then the corresponding DEBUG$ table must be altered so that it is sub-partitioned using the same partition strategy in the PROTO$ table.For example, if you add a hash partition on txn_id in the table PROTO$ARM_WK_SLS_TXN you should create a hash sub-partition on txn_id in the existing partition, which is based on srvc_req_id on the table DEBUG$ARM_WK_SLS_TXN.

Service Maintenance Package: RSE_Srvc_Maint

MBA uses the service of the Service Maintenance Package RSE_Srvc_Maint to archive and clean up transient, debug, and log data and ODM Models.

Archiving Log Data: archive_log_data

This routine helps when moving data partition from regular/online log tables such as RSE_LOG_MSG, RSE_SRVC_REQ_QUEUE and RSE_SRVC_REQ_QUEUE_PROP to historical versions of those same tables. This can reduce the amount of data so that active use of the tables can be more efficient while still allowing retention of the data for historical analysis. Only full or completed weeks and the partitions older than the number of days to preserve (p_days_to_preserve ) is archived to historical table. The current or active week is not considered for archiving.

Table 4-3 Archiving Log Data

Input Parameters	Type	Description
p_source_table	VARCHAR2	Name of the log table that requires some partitions archived.
p_archieve_table	VARCHAR2	Name of the historical log table receiving the partitions. Parameter can be null, in which case the default is the first 25 characters from the p_source_table concatenated with string _HIST.
p_days_to_preserve	VARCHAR2	Number of day's worth of log data that should remain in the MBA log table. Defaults to a constant value defined in the package spec using the parameter C_DAYS_TO_PRESERVE.

Example: To archive the data partitions of the table RSE_LOG_MSG.

begin
  rse_srvc_maint.archive_log_data('RSE_LOG_MSG');
end;

Purging Log Data: purge_log_data

This routine helps in cleaning up the old data from the historic log tables by allowing it to retain the desired amount of historic data. Only full or completed weeks and the partitions older than the number of days to preserve (p_days_to_preserve) are purged, and the current or active week is not considered for purging.

Table 4-4 Purging Log Data

Input Parameters	Type	Description
p_source_table	VARCHAR2	Name of the table that requires some partitions removed.
p_days_to_preserve	VARCHAR2	Number of day's worth of log data that should remain in the MBA History log table. Defaults to a constant value defined in the package spec using the parameter C_DAYS_TO_PRESERVE.

Example: To remove old data partitions of the RSE_LOG_MSG_HIST table:

begin
  rse_srvc_maint.purge_log_data('RSE_LOG_MSG_HIST');
end;

Archiving MBA Logs: archive_rse_logs

This routine archives old log data partitions from the online tables to the _HIST versions. Only full or completed weeks and the partitions that are older than the number of days to preserve (p_days_to_preserve) are moved.

Table 4-5 Archiving MBA Logs

Input Parameters	Type	Description
p_hist_tbl_ptrn	VARCHAR2	This parameter accepts a pattern that is used to identify the historical tables to archive. Default is 'RSE%_HIST'.
p_days_to_preserve	VARCHAR2	Number of day's worth of log data that should remain in the History log table. Defaults to a constant value defined in the package spec using the parameter C_DAYS_TO_PRESERVE.

Example: To archive old log data partition with the default values.

begin
  rse_srvc_maint. archive_rse_logs;
end;

Purging Debug Tables: purge_debug_tables

The purge_debug_tables routine is helpful in removing debug tables once the data analysis is performed to resolve issues due to enabling the compilation option feature debug_data. This routine drops DEBUG partitions corresponding to srvc_req_id older than the number of days to preserve p_days_to_preserve and also eliminates any DEBUG partitions that no longer have a corresponding srvc_req_id.

Table 4-6 Purging Debug Tables

Input Parameters	Type	Description
p_dbg_tbl_ptrn	VARCHAR2	This parameter accepts a starting pattern that is used to identify the debug tables to purge.
p_days_to_preserve	VARCHAR2	Number of days worth of debug requests data that should remain in the MBA DEBUG tables. Defaults to a constant value defined in the package spec using the parameter C_DAYS_TO_PRESERVE.

Example: To purge a debug table with the starting pattern 'ARM'

begin
  rse_srvc_maint.purge_debug_tables('ARM');
end;