Best practices for optimizing DMO downtime during S/4HANA 1909 upgrade with large demand planning tables

ERP SAP S/4HANA
, , , , , , , ,
1

We’re planning a DMO upgrade to S/4HANA 1909 for our supply chain environment with extensive demand planning data. Initial test runs show downtime of 42 hours, primarily driven by 8 massive demand planning tables (largest is 380GB with 2.1 billion records spanning 5 years of forecast data). Standard DMO configuration with automatic table splitting isn’t achieving acceptable performance. We’ve analyzed UPGANA.XML and see these tables dominating the critical path. Considering manual table splitting strategies, but documentation is sparse on optimal approaches for time-series data. Has anyone successfully optimized DMO for similar demand planning scenarios? Specifically interested in experiences with range partitioning by date, parallel R3load tuning beyond defaults, and whether iterative DMO execution helps with tables this large.

system 2

DMO Downtime Optimization for Large APO/DP Tables — S/4HANA 1909

Pre-Upgrade Checks

Before touching split parameters, validate these against your landscape:

  • Confirm HANA target sizing: 380GB uncompressed can compress 5–10x on HANA columnar store, but peak DMO memory during load can spike to 2–3x raw size. Verify your target HANA host has sufficient memory headroom.
  • Check R3load version: The R3load binary shipped with your DMO stack (SUM) matters. Older binaries have known throughput ceilings on HANA targets — verify in your version that you’re using the R3load bundled with the SUM tool, not a separately installed one.
  • Analyze /usr/sap/<SID>/SUM/abap/log/UPGANA.XML for the DDLORA/DDLHDB split hints already auto-generated. Cross-reference with transaction SE16N to get accurate live row counts — UPGANA estimates can drift from stats.
  • Source system: Ensure APO demand planning tables (/SAPAPO/MSDP_*, MDVM, /SAPAPO/TS* series) have current statistics via BRCONNECT or DB-specific stat refresh before DMO starts. Stale stats cause poor automatic split decisions.
  • Disable online users and batch: Any active DP planning runs or transaction /SAPAPO/MC8D jobs competing for table locks will corrupt split boundary detection.

Optimization Sequence

  1. Extract the auto-generated .str files from SUM/abap/load/ for your eight critical tables after the first aborted or completed test run. These become your baseline for manual override.

  2. Define manual WHERE-clause splits by date key for time-series tables. For /SAPAPO/MSDP_* tables carrying TSID or PERIODID columns, split on date ranges rather than row-number offsets. Example .str override for a table with a BUDAT-equivalent period column:

EXTRACT
WHERE PERIODID <= '20210101'
...
EXTRACT
WHERE PERIODID > '20210101' AND PERIODID <= '20230101'
...
EXTRACT
WHERE PERIODID > '20230101'

Place overrides in SUM/abap/load/<TABLE>.str. SUM will use manual .str files over auto-generated splits when present — verify in your version that your SUM release supports manual .str injection without checksum errors.

  1. Tune R3load parallel jobs: Edit SUM/abap/bin/RUNSTATS.xml or the equivalent SUM parallelism config. Increase maxParallelJobs beyond the default (typically 4–8) to match available CPU cores on the target HANA host, not the source. For a 380GB table, test with 16–32 parallel R3load writers. Each split package should target 20–40GB per chunk for optimal HANA bulk insert performance.

  2. Enable R3load --fast mode and confirm DBSL bulk insert is active. Check SUM logs for BULK INSERT confirmation — row-by-row insert mode is a silent killer for tables this size.

  3. Run iterative DMO (Downtime-Minimized option) if SUM version supports it. The uptime phase pre-migrates historical data (older period partitions) while the source is live, leaving only delta/current periods for the downtime phase. This alone can cut downtime by 40–60% for historical forecast tables.

  4. Post-load: Run column store delta merge on HANA target for all migrated DP tables before cutover validation. Unmirged delta store inflates memory and slows initial DP queries.

Rollback Procedure

DMO rollback from 1909 target back to source release (assumed ECC 6.0 EHP7 or S/4 1709):

  1. Stop SUM immediately — do not allow XPRAS or post-processing phases to complete; rollback is only clean before that boundary.
  2. Restore source DB from the pre-DMO snapshot/backup taken at SUM phase PREP_STOP.
  3. Re-import source transport directory (/usr/sap/trans) if any objects were exported during uptime phase.
  4. Validate source system with transaction SM21 and SICK before re-enabling users.
  5. Document the exact SUM phase at failure — partial R3load completions leave orphaned HANA tables that must be dropped manually before retry.

This draft is based on general SAP S/4HANA knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

We faced similar challenges with a 1909 upgrade last year. For demand planning tables with time-series data, date-based range partitioning is definitely the way to go. We manually split our largest table (/SAPAPO/MATLOC - 280GB) into 24 ranges by month, which reduced downtime by 18 hours. The key is balancing parallelization against I/O bottleneck. With 8 massive tables, you need careful sequencing to avoid saturating disk throughput. Also, default R3load parallelization (8 processes) is way too conservative for modern hardware. We ran 32 parallel R3load processes with custom split configuration and saw nearly linear scaling up to that point.

4

Manual table splitting for demand planning data requires understanding your data distribution. Don’t just split by equal date ranges - analyze record density. We had a similar 2.1B record table where 60% of data was in the most recent 18 months. Our approach: 6 monthly splits for recent data, quarterly splits for year 2-3, and annual splits for older data. This gave us 14 balanced ranges instead of naive 12 monthly splits. Use transaction DMOSTATISTICS to validate split quality before execution. Also, UPGANA.XML analysis should focus on the ‘actual’ vs ‘estimated’ times - if estimates are way off, your split strategy needs refinement.

5

Great insights on data-driven splitting. How do you actually implement the manual splits in DMO? Is this done through DMOPREPEXTRACT or directly modifying split configuration files? And for R3load parallelization - you mentioned 32 processes. What’s the configuration point for that? I see R3_PARALLEL_PROCS in DMOCONFIGURATION but it’s set to 8 and documentation warns against changing it significantly. Did you modify this parameter or use a different approach?

6

Manual splits are defined in table DMOSPLITCONFIG before DMO execution. You create entries specifying table name, split column (date field), and range boundaries. For R3load parallelization, R3_PARALLEL_PROCS is just the starting point. The real tuning happens with R3_LOAD_PROC_FACTOR and proper hardware allocation. We use factor of 2-4 depending on CPU/disk capability, which multiplies effective parallelization. But here’s the critical part for demand planning tables: consider iterative DMO execution. Run a preliminary DMO for your 8 massive tables only, analyze actual performance metrics, then adjust splits and re-run. DMO supports checkpoint restart, so you’re not starting from zero. This iterative optimization can save days of downtime in production.

7

One aspect not mentioned yet: consider data archiving before DMO. For 5 years of demand planning forecast data, do you really need all of it in the live system post-upgrade? We archived 3+ year old forecast data using APO archiving objects before our 1909 upgrade. This reduced our largest table from 420GB to 180GB, which had a massive downstream impact on DMO duration. Yes, it adds a pre-upgrade step, but the archiving itself only took 8 hours and saved us 15+ hours in DMO downtime. The archived data remained accessible through archive access if needed for historical analysis.

8

There’s also a hybrid approach worth considering. Instead of pure manual splits, use DMO’s automatic splitting with custom thresholds. In DMOCONFIGURATION, parameter SPLIT_THRESHOLD_SIZE controls when tables get auto-split. Default is 50GB, but for your scenario, lower it to 20GB. This triggers more granular automatic splitting. Then supplement with manual splits only for the most problematic tables where automatic logic fails. This reduces manual configuration effort while still achieving good parallelization. We’ve used this on several large upgrades and it hits a good balance between automation and control.

9

Let me synthesize the discussion and add some battle-tested recommendations for optimizing DMO with large demand planning datasets.

Manual Table Splitting for Demand Planning Data: The consensus on date-based range partitioning is correct, but implementation details matter significantly. For your 380GB table with 5 years of data, start by analyzing distribution with this query in HANA: examine record counts by month/quarter to identify density patterns. As mentioned, recent data is typically denser - this is especially true for demand planning where active forecasts concentrate in near-term periods.

Implement splits through table DMOSPLITCONFIG with these guidelines:

  • Recent 12 months: Monthly splits (12 ranges)
  • Months 13-24: Bi-monthly splits (6 ranges)
  • Years 3-5: Quarterly splits (12 ranges) This gives you 30 ranges for your largest table, enabling high parallelization while keeping individual range sizes manageable (12-15GB each).

For the other 7 large tables, apply similar logic but adjust granularity based on size. Tables under 150GB can use coarser splits (15-20 ranges), while 200GB+ tables benefit from finer granularity (25-35 ranges).

Parallel R3load Tuning Configuration: The R3_PARALLEL_PROCS parameter of 8 is indeed conservative for modern systems. Here’s the tuning strategy we’ve successfully used:

  1. Calculate your hardware capacity: (CPU cores × 0.75) / 2 = baseline parallel processes. For a typical 32-core system, this gives you 12 baseline.

  2. Set R3_PARALLEL_PROCS to your baseline value (12 in this example).

  3. Configure R3_LOAD_PROC_FACTOR to 2.5-3.0, which multiplies effective parallelization during the load phase. This leverages the fact that R3load is often I/O-bound rather than CPU-bound.

  4. Monitor disk I/O during test runs. If you’re not saturating disk throughput (check with iostat or OS-level monitoring), increase R3_LOAD_PROC_FACTOR incrementally.

For your 8 massive tables with 30 ranges each, you theoretically have 240 parallel work units. With R3_PARALLEL_PROCS=12 and factor=3, you’ll have 36 concurrent R3load processes, which should keep the pipeline full without overwhelming the system.

UPGANA.XML Performance Analysis: This is your primary feedback mechanism for optimization. After each test run, focus on these UPGANA.XML sections:

  1. Table runtime statistics: Compare ‘estimated’ vs ‘actual’ durations. If actual exceeds estimate by >50%, your splits need rebalancing.

  2. Critical path analysis: Identify which tables are on the critical path (those whose completion determines overall duration). For your scenario, all 8 large tables are likely critical path candidates.

  3. Parallel efficiency: Look at the ‘parallel’ section to see how many processes were active vs idle over time. Low utilization indicates split imbalance or insufficient parallelization.

  4. Phase distribution: Verify that EU_IMPORT and TABIM phases consume the bulk of time. If REPRO or other phases dominate, you have different optimization opportunities.

Use this analysis to iteratively refine your split configuration. A 10% improvement in split balance can translate to hours of downtime reduction.

Range Partitioning by Date Strategy: Beyond the split granularity discussed above, consider these technical details:

  1. Split column selection: Use the primary date field that determines data lifecycle (forecast date, creation date, etc.). Ensure this field has an index to avoid full table scans during split processing.

  2. Boundary alignment: Align split boundaries with natural data boundaries (month-end, quarter-end) to avoid splitting logical data units.

  3. NULL handling: If your date column allows NULLs, create a dedicated split range for NULL values rather than including them in a catch-all range.

  4. Future dates: For demand planning, you likely have forecasts extending into future years. Create a dedicated range for dates beyond current + 2 years to isolate this typically smaller dataset.

Iterative DMO Execution Optimization: This is perhaps the most powerful technique for achieving optimal downtime. Here’s the proven approach:

Iteration 1 (Test System):

  • Configure initial splits using data analysis
  • Execute DMO with aggressive parallelization
  • Analyze UPGANA.XML to identify bottlenecks
  • Document actual vs estimated times for all major tables
  • Duration: Accept longer runtime to gather comprehensive data

Iteration 2 (Test System):

  • Rebalance splits based on iteration 1 data
  • Focus on tables that exceeded estimates
  • Adjust R3load parallelization if I/O headroom exists
  • Execute DMO again
  • Compare results to iteration 1
  • Duration: Should show 20-30% improvement

Iteration 3 (Pre-Production or Final Test):

  • Fine-tune remaining imbalances
  • Validate that critical path is optimized
  • Confirm hardware utilization is optimal
  • This becomes your production blueprint
  • Duration: Target is 85-90% of theoretical minimum

For your 42-hour baseline, this iterative approach typically achieves 24-28 hour final downtime - a substantial improvement that justifies the iteration effort.

Additional Optimization Considerations:

  1. Data archiving pre-processing: The suggestion to archive 3+ year old data is excellent. Even if you need historical data accessible, moving it out of DMO scope provides immediate benefits. Calculate ROI: 8 hours archiving vs 15+ hours DMO savings is compelling.

  2. Hardware optimization: Ensure your HANA system has sufficient I/O capacity. DMO performance is often disk-bound. Consider temporary storage expansion or faster disk tiers for the upgrade window.

  3. Network throughput: If source and target systems are on different hardware, network bandwidth can become a bottleneck. Monitor network utilization during test runs.

  4. Memory allocation: Increase R3load memory allocation through R3_MEMORY parameter. Default is often too conservative. For large tables, 2-4GB per R3load process improves performance.

  5. Post-processing optimization: Don’t forget that DMO downtime includes post-import processing (index creation, statistics update). For demand planning tables with many indexes, this can be significant. Consider dropping non-critical indexes before DMO and recreating them post-upgrade outside the downtime window.

In summary, achieving optimal DMO performance for large demand planning datasets requires a multi-faceted approach: intelligent manual splitting based on data distribution, aggressive but controlled R3load parallelization, iterative optimization using UPGANA.XML feedback, and consideration of pre-processing options like archiving. Following this methodology, your 42-hour baseline should be reducible to 24-28 hours, making the upgrade window much more manageable for supply chain operations.