This chapter covers the following topics:
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.
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:
Plan Partition Count: The number of new plan partitions to create. Each ASCP plan needs its own plan partition to manage the plan's PDS (output) data.
Instance Partition Count: The number of new instance partitions to create. Each collection instance in the Instances form requires an instance partition.
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.
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:
TST is assigned to the local centralized ERP instance. That is why a2m_dblink and m2a_dblink are NULL.V86 is assigned to an instance on another server and dblinks were created and populated in the Instance Setup form so that the connection could be made for data collections and other operations to be successful. See Defining Source Link for more information on this process.
LEG is for custom collections from a non-Oracle data source (apps_ver = -1). An example is when all data is being populated from an external system using Legacy Collections.
The ODS data can be identified by the PLAN_ID. For ODS data, this is always -1. When more than one instance is being collected, differentiate this data by the SR_INSTANCE_ID or INSTANCE_ID columns in the table. See INSTANCE_ID column above for the number.
All Columns beginning with SR% in the MSC tables refer to a value obtained from the EBS source instance (for example, SR_INSTANCE_ID, SR_INVENTORY_ITEM_ID, etc.).
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:
1 -- Manufacturing Plan (MRP)
2 -- Production Plan (MPS)
3 -- Master Plan (MPP)
4 -- Inventory Plan (IO)
5 -- Distribution Plan (DRP)
6 -- SNO Schedule
7 -- Production Schedule (PS)
8 -- Service Parts Plan (SPP)
9 -- Service Parts IO Plan (SPIO)
101 -- Rapid Plan MRP
102 -- Rapid Plan MPS
103 -- Rapid Plan MPP
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";
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:
DG-APS1 is a plan that is being used (FREE_FLAG = 2).
PRN_SCP is a plan that has been purged, but a new plan has not been defined that uses that partition_number. The plan name is replaced when a new plan and the plan options have been defined.
Plan name 411 is a new partition created by the Create APS Partitions request and has never been used. Therefore, two new plans could be defined in the Names form in ASCP. After the plan options are defined and saved, this table will be updated with the new plan name and the FREE_FLAG changed to 2.
If no rows with FREE_FLAG = 1 are returned, then you may still create the new plan in the Names form, but when attempting to define and save the plan options, an error will be received similar to 'No free partitions available, Contact the DBA to create partitions'. This is because the Names form only inserts into the table MSC_DESIGNATORS. When plan options are defined, then the process checks MSC_PLAN_PARTITIONS and updates the FREE_FLAG and PLAN_NAME. The tables inserted/updated are MSC_PLANS, MSC_PLAN_ORGANIZATIONS, MSC_DESGINATORS and MSC_SUBINVENTORIES. Use the concurrent request Create APS Partitions with parameters Instance Partition Count = 0 and Plan Partition Count = 1 to create one new plan partition.
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.
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.
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.
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).
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.
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.
The Drop Partition request must be added to the All MSC Reports request group as follows:
Using the System Administrator responsibility, navigate to Security, then Responsibility, and then Request.
Query for the 'Group'%MSC% to retrieve 'All MSC Reports'.
Highlight a line in the form and use /File/New.
Enter the following:
Type: Program
Name: Drop Partition
Save your work. The program will now be available for responsibilities that use this request group such as Advanced Supply Chain Planner.
Partition -- [enter number]. Never use 0 [zero], this is the template partition!
Plan -- Yes/No. Use 'Yes' if this is a plan partition. Use 'No' if this is an instance partition.
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:
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.
Drop the partition relating to the previous EBS Source instance using the Drop Partition Request and set the parameter Plan = No.
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.
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.
Run Clean up Instance Partitions with parameter Mode -- Repair to clean up any partitions in the staging table associated with the old instance.
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.
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:
List only: This will list any partition problems discovered, but take no action.
Repair: This will list and then fix any partition problems discovered. This can be run at any time in List mode to check the system. If an Instance partition is dropped using Drop Partition, then this program can be run to ensure that all staging table partitions have been cleaned up.