Questions regarding Oracle/Db2 to Postgres validation

[JamalQasem]

We (ab)use DVT in a customer project to validate our migration of z/OS Db2 tables to Postgres. To access the z/OS Db2 tables we use a connection via Oracle Heterogeneous Services.

When evaluating DVT for our use-case we noticed two major problems:

• the -validate -row option for tables is prone to concat collisions, i.e. the concatenation of "12" and "3" would give the same result and therefore the same hash as the concatenation of "1" and "23".
• partitioning large tables depends on the sort order of key columns, which is different in our case due to Heterogeneous Services inheriting z/OS Db2 EBCDIC sort order.

To resolve these problems we imagined two approaches:

• generating/manipulating DVT YAML configuration files
• implementing suitable custom SQL queries

We decided to go for the second option due to the sparse documentation of the internal configuration and expecting the auditing of SQL statements to be easier for third parties.

To combat concat collisions in custom queries we format the columns to a fixed sized format. To work around the sort order issue we use hash partitioning, i.e. the MD5 hash of the key modulo the desired partition count.
We suspect this approach to somehow collide with implementation details of DVTs -hash option.

Is there anything we missed and/or do you see any room for improvement?

[nj1973]

Hi @JamalQasem ,

For the concat collision problem you are correct, collisions are possible. You could supplement the row validations with min/max/sum column validations to reduce the likelihood of missed issues. There would still be a risk of value combinations that can slip through but the probability would be small.

Regarding the sort order for partitioning. I just want to make sure I understand the issue and proposed solution. I believe this is only an issue when one or more primary key columns are strings, is that correct? And if that is correct I don't follow how a modulo solution helps because that would only apply to numerics. I think I need a bit more detail on this problem if that's okay.

[nj1973]

You might also be interested in this branch: https://github.com/GoogleCloudPlatform/professional-services-data-validator/tree/1630-db2-zos-support

We are currently exploring enhancing DVT Db2 support to include z/OS.

So anything you've learned from your experimentation might be worth sharing?

[JamalQasem]

Thanks for your support!

Regarding the sort order for partitioning. I just want to make sure I understand the issue and proposed solution. I believe this is only an issue when one or more primary key columns are strings, is that correct?

Yes.

And if that is correct I don't follow how a modulo solution helps because that would only apply to numerics. I think I need a bit more detail on this problem if that's okay.

We compute the MD5 hash of the (concatenated) character key(s) and convert the resulting hash to an integer to apply the modulo operation.

We are currently exploring enhancing DVT Db2 support to include z/OS.

I looked into the branch and it seems ibm_db_sa is able to directly connect to Db2 on z/OS without installing additional IBM software components? That would be new information for us.

So anything you've learned from your experimentation might be worth sharing?

Hash-partitioning is rather ineffective for larger tables without persisting the partition. This is exacerbated in our case due to optimization issues when accessing Db2 via Oracle database links. For larger tables we therefore resort to a sample based custom validation script (using Postgres TABLE_SAMPLE) due to the fact that DVT custom-query doesn't support sampling.