Data Virtualization Under a Cross-Region 10 Million Row Join
A logistics company uses CP4D Data Virtualization to query a 2 TB Db2 warehouse on premises, a 40 TB S3 data lake of Parquet files, and a 5 TB Oracle ERP. Analysts join all three sources in Data Virtualization; a nightly query joining S3 shipment events to Db2 customer records runs 25 minutes and spills to disk, and a 10 million row join fails with resource exhausted. The S3 bucket is in a different region with 90 ms latency. The company also has DataStage and Data Replication licensed but unused. The CIO wants sub-minute dashboards and a single copy of curated data, while the DBA refuses to allow heavy SQL on Db2 during business hours. Propose an integration and virtualization architecture and explain what not to virtualize.
1. Choose the integration pattern for the S3-to-Db2 join and justify it.
Read the worked answer
Do not make Data Virtualization perform a 10 million row cross-region join. DV is best for federation, pushdown, and small results; its optimizer cannot push a join between S3 and Db2 into either source, so it must move and shuffle data across a 90 ms link. The 25-minute spill and resource exhaustion are expected.
Use DataStage for the batch join: read S3 Parquet, filter by date and partition, aggregate shipments to customer per day, then join to the Db2 extract. Land the curated result in a serving table or partitioned Parquet dataset. Use Data Replication for continuous Db2 changes if the dashboard needs near-real-time; otherwise schedule DataStage off-peak. Keep DV for dimension lookups and ad hoc queries that return limited rows. This creates one curated copy and stops repeated heavy federation.
2. How would you meet sub-minute dashboards while respecting the DBA restriction?
Read the worked answer
Build a serving layer rather than querying sources live. Pre-aggregate the shipment-to-customer metrics at the grain the dashboard needs, partition by date, and publish a data product. If dashboards need sub-minute response, query the small aggregate table, not the 40 TB lake. Schedule DataStage loads outside business hours or use CDC to keep the serving table current. Cache dashboard results or use materialized query tables where the serving engine supports them.
The DBA constraint is valid: do not run the heavy join on Db2 during the day. Offload the Db2 extract to a read replica or schedule it off-peak. Measure freshness requirements; if the business can tolerate 15-minute freshness, CDC plus a small serving table is cheaper than forcing DV. If it needs true real-time, use event streaming, not federation.
3. What DV query and configuration changes would you try before adding infrastructure?
Read the worked answer
Before adding DV pods, fix the query pattern. Push filters and column projections down; remove functions and casts on join keys; use partition pruning on S3; collect statistics; and test a smaller aggregation. Enable DV caching for repeated small queries, not for the 10 million row join. Check whether the optimizer is choosing a nested-loop plan and whether source statistics are missing.
Raise DV memory only after confirming the plan is sound, because a cross-source shuffle will consume whatever you add. Set query timeouts and resource limits so one analyst cannot exhaust the service. If the query still needs to move millions of rows, it is an integration workload, not a virtualization workload. Document the threshold and route such requests to DataStage.