How to Ingest a table of xml data from on-prem DB2 warehouse to ASDLGen2 using ADF

Question

How to Ingest a table of xml data from on-prem DB2 warehouse to ASDLGen2 using ADF

Nhan, Phuong 0

Hi,

I am using ADF to incrementally load a table containing 2 XML columns from on-prem DB2 warehouse to ADSLGen2 in parquet format. In Copy activity, I use DB2 connector at source and parquet dataset at sink; I also use Query with XMLSERIALIZE for these 2 columns to convert and compress the data; I also explicitly mapped these 2 columns to String. This setup works for a small amount of records; if the record counts are more than 2 thousands, ADF just hang for hours and stuck. What should I change in the ADF pipeline to make it work, please?

Thank you

Himaja Y 550 Reputation points Microsoft External Staff Moderator

2026-09-17T16:18:09.3666667+00:00

Hi @Nhan, Phuong ,

I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.

1 answer

Your answer

Himaja Y 550 Reputation points Microsoft External Staff Moderator

2026-09-17T16:18:09.3666667+00:00

Hi @Nhan, Phuong ,

I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.

Answer 1

Himaja Y 550 Microsoft External Staff Moderator

Hi @Nhan, Phuong ,

Thank you for reaching out to the Microsoft Q&A.

The issue is likely related to processing large XML data rather than the ADF mapping itself. Since the load works for small datasets but hangs when processing more than 2,000 records, consider the following:

Check the execution time of the XMLSERIALIZE query directly in DB2.
Enable source partitioning and parallel copy in ADF.
Increase DIUs and parallel copy settings.
Validate whether some XML records are significantly larger than others.
As a test, load the data to CSV first instead of Parquet to determine if the bottleneck is in Parquet conversion.

Please share:

XML column size (average/max)
Azure IR or Self-Hosted IR
Copy activity monitoring metrics

This will help determine whether the bottleneck is in DB2 query execution, network transfer, or Parquet generation.

Nhan, Phuong 0 Reputation points

2026-09-17T19:34:50.6166667+00:00
Thank you for your responses.

Originally, with parquet at target, we did not use XMLSERIALIZE and the transfer was all good but... we ended up with a few broken XML records (like missing a closing bracket...). Then we tried it with XMLSERIALIZE and parquet; it works for a small set with a few records and hangs with a large set.

XML column size (average/max): 32MB max (I do not have the average though)

Azure IR or Self-Hosted IR: we use SHIR

Copy activity monitoring metrics as below One thing. We have just tried again with XMLSERIALIZE and Json at target. The pipeline was hanging again.
Himaja Y 550 Reputation points Microsoft External Staff Moderator

2026-09-22T16:25:36.2166667+00:00
Hi @Nhan, Phuong ,

Thank you for providing the additional details.

Based on the behavior observed, the issue does not appear to be related to the sink format, since the pipeline hangs when using both Parquet and JSON outputs. The common factor is the use of XMLSERIALIZE on large XML columns (up to 32 MB per record) through the Self-Hosted Integration Runtime.

The monitoring results indicate that the activity spends most of its time reading data from the DB2 source, while virtually no time is spent writing to ADLS. This suggests that the bottleneck is occurring during XML serialization and source data retrieval rather than during Parquet or JSON generation.

We recommend:

Testing the XMLSERIALIZE query directly in DB2 to measure its execution time for larger datasets.

Splitting the extraction into smaller batches using key or date ranges.

Enabling source partitioning and increasing parallel copy settings.

Monitoring CPU and memory utilization on the Self-Hosted Integration Runtime machine during execution.

If possible, extracting the XML data without XMLSERIALIZE and performing any transformation after landing the data.

Given that the pipeline succeeds for small datasets but hangs as volume increases, the evidence points to source-side XML processing and SHIR resource constraints rather than an ADLS or Parquet limitation.
Himaja Y 550 Reputation points Microsoft External Staff Moderator

2026-09-24T15:37:13.3466667+00:00

Hi @Nhan, Phuong ,

I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.
Nhan, Phuong 0 Reputation points

2026-09-24T19:55:28.83+00:00
Hi Himaja Y,

Please see my answers to your suggestions below:

Testing the XMLSERIALIZE query directly in DB2 to measure its execution time for larger datasets. -> Phuong: Yes, I tried that and no issue at all; the query return is fast enough.

Splitting the extraction into smaller batches using key or date ranges.-> Phuong: I am ingesting 30 minutes of data at a time

Enabling source partitioning and increasing parallel copy settings. -> Phuong: would you show me how, please?

Monitoring CPU and memory utilization on the Self-Hosted Integration Runtime machine during execution. -> Phuong: Good point. I will try that

If possible, extracting the XML data without XMLSERIALIZE and performing any transformation after landing the data. ->Phuong: yes, that was our original extraction and we ended up with a few broken xml data (like an open bracket without a close bracket in the middle of the record). I fail to reproduce the broken records. Broken records happen intermittently.
Himaja Y 550 Reputation points Microsoft External Staff Moderator

2026-09-28T14:49:23.6333333+00:00
Hi @Nhan, Phuong ,

Thank you for the update & apologies for the delayed response.

Since the XMLSERIALIZE query performs well directly in DB2 and the issue occurs with both Parquet and JSON sinks, the bottleneck is unlikely to be the sink format. The behavior points more toward handling and transferring large XML payloads (up to 32 MB per record) through the Self-Hosted Integration Runtime (SHIR).

We recommend:

Monitor CPU, memory, and network utilization on the SHIR machine while the pipeline is running.

Enable source partitioning and increase parallel copy settings in the Copy Activity to improve throughput.

Test with smaller batches to determine whether the issue is related to the volume of large XML records being processed.

Continue using XMLSERIALIZE if it is required to prevent XML corruption, but consider landing the data first and performing additional processing downstream if feasible.

Based on the information provided, this appears more likely to be a SHIR resource or large XML processing limitation rather than a DB2 query performance issue. Please share the SHIR resource utilization metrics during the execution for further analysis.