Setting Up Partitions

This chapter covers the following topics:

Overview

Partitions are an RDBMS concept to subdivide a table or index. When a table is partitioned, any index on that table is also partitioned. Partitions are used in VCP to segregate data. This improves overall performance because the data can be manipulated separately by partition. VCP uses partitions for some of the data that is needed by the Operational Data Store (ODS) and the Planning Data Store (PDS). The same tables are generally used for both ODS and PDS data and are segregated by the plan ID and by the instance ID. Each instance defined in the Instances form gets assigned an Instance partition. Each plan defined in a Plan Names form is assigned a plan partition.

The default install creates one instance partition and five plan partitions. This allows users to proceed with standard setups for connecting to a single EBS source instance and create five plans in the application. The information below is required knowledge for understanding how to manage connections when cloning instances and connecting to different EBS source instances. The DBA and VCP Super User MUST understand these concepts and be able to query and understand the use of partitions to manage the post cloning steps, manage connections to different EBS source and Legacy instances, clean up old instance data, and initiate connections to new, different EBS sources.

The ODS is the destination of collected data. After the data collections have completed successfully, the collected data is inserted into the ODS partition for that instance. These rows have a PLAN_ID = -1. If several Oracle instances are collected, instance data is stored in individual ODS instance partitions. In addition, the tables contain a column called SR_INSTANCE_ID or INSTANCE_ID that identifies the ERP source instance where the data originated.

The PDS is the destination of ASCP planning data. After an ASCP plan has completed successfully, the data is inserted into the PDS plan partition and other tables that are not partitioned. These rows have a PLAN_ID > 0. In the default setup, each plan has its own plan partition. The tables also contain a column called SR_INSTANCE_ID or INSTANCE_ID that reveals the EBS source instance where the data originated.

The default installation of VCP creates one instance partition and five plan partitions. They can be viewed using the following SQL:

SELECT 
  table_name,
  partition_name,
  num_rows,
  high_value,
  sample_size,
  last_analyzed,
  global_stats
FROM 
  all_tab_partitions
WHERE 
  table_name like 'MSC%'   
  order by  substr(partition_name,instr(partition_name,'_',-1,1)+1)
      -- To check the partition count of each table use order by table_name, partition_name

The following table shows the default installation for one table -- MSC_SYSTEM_ITEMS. (use where TABLE_NAME like 'MSC_SYSTEM_ITEMS' for the above SQL). For a full list of tables, see Appendix F -- List of APS Partitioned Tables.

TABLE_NAME PARTITION_NAME Comments
MSC_SYSTEM_ITEMS SYSTEM_ITEMS_0 The Template partition – NEVER DELETE THIS PARTITION
MSC_SYSTEM_ITEMS SYSTEM_ITEMS__1 Instance Partition #1
MSC_SYSTEM_ITEMS SYSTEM_ITEMS_1 Plan Partition #1
MSC_SYSTEM_ITEMS SYSTEM_ITEMS_2 Plan Partition #2
MSC_SYSTEM_ITEMS SYSTEM_ITEMS_3 Plan Partition #3
MSC_SYSTEM_ITEMS SYSTEM_ITEMS_4 Plan Partition #4
MSC_SYSTEM_ITEMS SYSTEM_ITEMS_5 Plan Partition #5

Note: If there is a drastic mistake made during the manipulation of ODS/PDS data, keep in mind that this data is either source data collected from the ERP source, as in the case of the ODS, or plan data, as in the case of the PDS. The ODS data can be recollected and the PDS data can be reproduced by submitting the ASCP plan.

Create APS Partition

This concurrent program is run from the System Administrator responsibility. Alternatively, the concurrent program may have been assigned to All MSC Reports, which means it can be run from either Advanced Supply Chain Planner OR Advance Planning Administrator. The parameters are:

Note: There should not be many extra partitions created. This can cause performance issues. For plan partitions, there should not be more than two to three extra plan partitions on the system. For instance partitions, there should not be any extra instance partitions on the system.

ODS Tables and Instance Partitions

To add one new ODS partition, submit the request Create APS Partitions by setting the concurrent program parameters plan_partition_count=0 and inst_partition_count=1.

The ODS partitioned tables have __nn (double underscore and the instance ID) as shown below. Appendix F -- List of APS Partitioned Tables has a complete list of the tables.

TABLE_NAME PARTITION_NAME NUM_ROWS
MSC_SUPPLIES SUPPLIES__21 14700

ODS partitions are created using the sequence MSC_APPS_INSTANCES_S and are visible in the table MSC_INST_PARTITIONS.

SQL #2

-- Free_flag 
   1 = Free
   2 = In use in the MSC_APPS_INSTANCES table
select      instance_id,
            free_flag,
            creation_date,
            last_update_date            
from        msc_inst_partitions;
INSTANCE_ID FREE_FLAG CREATION_DATE LAST_UPDATE_DATE
21 2 5/8/2000 0:40 5/8/2000 0:40
61 2 1/25/2002 11:28 1/25/2002 11:28
81 2 10/14/2002 13:44 10/14/2002 13:44
101 1 9/17/2003 23:34 9/17/2003 23:34

In the above table, instance_id 101 has been created and still has free_flag = 1, which means it can be assigned to a new instance for data collections. Once a partition has been assigned to an ERP source instance using the Application Instance Setup form, the FREE_FLAG = 2 and a line is inserted into MSC_APPS_INSTANCES.

SQL #3

-- ST_STATUS
     0 = No source pull or collection is in process
     1 = Pull and collection process has begun
     2 = Collection has ended and waiting for load to begin
     3 = Load has begun
     4 = Load has ended and staging tables are being purged
select      instance_code,
instance_id,
apps_ver,
a2m_dblink,
m2a_dblink,
st_status        
from msc_apps_instances
INSTANCE_CODE INSTANCE_ID APPS_VER A2M_DBLINK M2A_DBLINK ST_STATUS
TST 21 3     0
LEG 61 -1     0
V86 81 3 APS visus86 0

Notes about the above table:

PDS and Plan Partitions

The partitioned tables also have partitions for each plan and they have names ending in _nn (single underscore and the plan ID) as shown below. Appendix F -- List of APS Partitioned Tables has a complete list of the tables.

TABLE_NAME PARTITION_NAME NUM_ROWS
MSC_SUPPLIES SUPPLIES_347 900

The PLAN_ID and PARTITION_NUMBER are controlled by the sequence MSC_PLANS_S. The PDS PLAN_ID is identified by selecting the PLAN_ID from MSC_PLANS.

SQL #4:

Plan Type valid values:

select
mp.COMPILE_DESIGNATOR "Plan Name",
mp.PLAN_ID "Plan ID",
mp.SR_INSTANCE_ID "Instance ID",
mtp.ORGANIZATION_CODE "Owning Org",
mp.PLAN_COMPLETION_DATE "Last Run Date",
decode (mp.PLAN_TYPE, 1, 'Manufacturing MRP', 2, 'Production MPS',
                      3, 'Master MPP',4,'IO Plan', 5, 'Distribution DRP' ,
                      7, 'PS Production Schedule', 6, 'SNO Schedule',
                      8, 'Service Parts SPP', 9,'Service IO Plan',
                      101,'Rapid Plan MRP',102,'Rapid Plan MPS',103,'Rapid Plan MPP',plan_type)"Plan Type",
decode (md.PRODUCTION,1, 'Yes', 2, 'No', NULL, 'No') "Production Flag",
decode (md.LAUNCH_WORKFLOW_FLAG,1, 'Yes', 2, 'No', NULL, 'No') "Launch Workflow",
decode (md.INVENTORY_ATP_FLAG,1, 'Yes', 2, 'No', NULL, 'No') "ATP Plan",
mp.CURR_START_DATE "Start Date",
mp.CUTOFF_DATE "End Date"
from
msc_designators md,
msc_plans mp,
msc_trading_partners mtp
where
md.designator=mp.compile_designator and
md.sr_instance_id=mp.sr_instance_id and
mtp.sr_instance_id=mp.sr_instance_id and
md.sr_instance_id=mtp.sr_instance_id and
mtp.sr_tp_id=md.organization_id and
mtp.sr_tp_id=mp.organization_id and
mp.organization_id=md.organization_id
and mtp.partner_type = 3
ORDER BY "Plan Name";

the picture is described in the document text

SQL #5

MSC_PLAN_PARTITIONS shows how planning partitions are used when the profile MSC: Shared Plan Partition = No

select      plan_id, 
            plan_name,
            free_flag,
            partition_number
from        msc_plan_partitions
PLAN_ID PLAN_NAME FREE_FLAG PARTITION_NUMBER
347 DG-APS1 2 347
391 PRN_SCP 1 391
411 411 1 411

Observations about the above table:

ODS and PDS Data

Data within the ODS and PDS is identified by SR_INSTANCE_ID (or INSTANCE_ID) and PLAN_ID. Rows with a PLAN_ID = -1 belong to the ODS and if more than one EBS source instance is being used, the SR_INSTANCE_ID identifies the other EBS source instance. Rows with a PLAN_ID > 0 belong to the PDS and can also be isolated by using SR_INSTANCE_ID.

To count the number of rows in a specific table, use the following SQL: All rows within MSC_SYSTEM_ITEMS, which includes all partitions.

select   count(*)
from     msc_system_items;
Count
6876

In a multiple source situation, the following SQL shows the rows and the PLAN_ID as well as the SR_INSTANCE_ID to indicate the origin of each row:

select   count(*),
         sr_instance_id,
         plan_id
from     msc_system_items
group by sr_instance_id, 
         plan_id
COUNT(*) SR_INSTANCE_ID PLAN_ID
3132 21 -1
2931 81 -1
56 81 147
1 81 148
10 21 149
36 21 181
2 81 183
2 81 184
5 21 221
1 81 222
10 21 224
43 21 263
108 21 283
6 21 303
376 21 323
115 21 325
24 21 347
9 81 347
9 81 349

From the queries above, data has been collected from the TST and V86 instances, INSTANCE_ID 21 and 81 respectively. This ODS data resides under PLAN_ID –1. We have not yet loaded any legacy data for INSTANCE_CODE LEG, INSTANCE_ID 61. Additionally, several plans have been run and items populated according to those plans.

Note: PLAN_ID 347 was run against the ODS data collected from both instances, therefore this PLAN_ID is listed twice in this SQL showing the rows which belong to each SR_INSTANCE_ID.

Using Drop Partition Request

Sometimes, you may need to reorganize your partitions by dropping a partition. For example, you may need to clean up after data corruption or cloning. The following procedures provide instructions for setting security to allow the dropping of plan partitions, as well as specific instructions for dropping both plan and instance partitions.

Important: Never drop the _0 Partition – this template partition is used to create new partitions. This will cause the instance to fail and it will have to be restored from a backup, a clone or reinstalled.

When to Use this Request

  1. You are using ATP based on collected data and not running any VCP plans. Then, the five plan partitions that were created during installation should be removed. In this case, you can proceed to run the request using the parameters described below.

  2. A user has created too many plan partitions or instance partitions. These partitions have never been used and need to be removed from the system to prevent performance issues. In this case, you can proceed to run the request using the parameters described below. SQL #3 -- Show Instance Partitions Created and Assigned to an EBS Source Instance and SQL #5 -- Show Plans and Associated Plan Partitions will show the partitions on the system and which ones are free (free_flag=1).

  3. You have old ASCP plans and now you want to remove them and reduce the number of plan partitions on the system. SQL #5 -- Show Plans and Associated Plan Partitions will show which plan partitions are free and which are in use. Before dropping a plan partition that is in use (msc_plan_partitions.free_flag=2), the plan must first be purged via the Names form. Once the plan is purged, then you will see that free_flag=1 and you can drop the plan partition.

  4. You have changed/cloned your EBS Source instance and need to purge ALL the old ODS instance and PDS instance data to clean up the system and prepare to use a new EBS Source instance. In this case, all plans for this old instance need to be purged via the Names form prior to dropping the instance partition -- see Instance Cleanup and Reset below.

Setting Security to Allow Dropping Partitions

The Drop Partition request must be added to the All MSC Reports request group as follows:

  1. Using the System Administrator responsibility, navigate to Security, then Responsibility, and then Request.

  2. Query for the 'Group'%MSC% to retrieve 'All MSC Reports'.

  3. Highlight a line in the form and use /File/New.

  4. Enter the following:

    • Type: Program

    • Name: Drop Partition

  5. Save your work. The program will now be available for responsibilities that use this request group such as Advanced Supply Chain Planner.

Parameters

Instance Cleanup and Reset

Check your setups for instance partitions via SQL #3 -- Show Instance Partitions Created and Assigned to an EBS Source Instance and the related plans via SQL #4 -- Show Plans Created in the System. Then do the following:

  1. Purge all plans in the instance partition before dropping the partition:

    • Navigate to the Plan Names form.

    • Choose your organization (if prompted).

    • Highlight the plan name, then use /edit/delete. Acknowledge the message in the form, then Save. This launches the Purge Designator request. (Note: this request is designed to be launched via the form only.)

    • Change orgs as required to purge all plans that exist for that instance.

  2. Drop the partition relating to the previous EBS Source instance using the Drop Partition Request and set the parameter Plan = No.

  3. In the EBS Source instance, remove the line from MRP_AP_APPS_INSTANCES_ALL using SQL #10 -- Check MRP_AP_APPS_INSTANCES Table on the EBS Source Instance and Delete Line.

  4. Dropping the instance partition does not remove all the data that has been collected for that INSTANCE_ID. Use SQL #6 -- Delete All Tables Where INSTANCE_ID Column Appears and SQL #7 -- Delete All Tables Where SR_INSTANCE_ID Column Appears to delete all the VCP destination instance data. For instances with Oracle Demand Planning (ODP) data, also see SQL #13 -- Delete All Tables Where the INSTANCE_ID Column Appears for ODP Data, SQL #14 -- Delete All Tables Where the INSTANCE_ID Column Appears for ODP Data, and SQL #14B -- Delete All Tables Where the Instance Column Appears for ODP Data.

  5. Run Clean up Instance Partitions with parameter Mode -- Repair to clean up any partitions in the staging table associated with the old instance.

  6. For connecting to a new EBS Source instance, you need one instance partition with free_flag=1. If none exists, then run Create APS partitions with instance partition = 1 and Plan partition = 0. Once completed, then you can begin setup to connect the new EBS source to this APS destination.

Clean Up Instance Partition Request

This request is designed to check the partitions created for the MSC ST Staging tables and if there are any errant partitions or missing partitions, then it can remove or create as required. This program has a single parameter -- Mode: