Question

Unloading specific records in a foreign key, cascade format.

DB2 Administration, OS/390, DB2, Query Management Facility, QMF, JCL

Hi,

We have DB2 version 8, os/390. I know how to use QMF and JCL.

I would like to unload, to files, specific records that I would enter, by the key, in a where clause. The record would then cascade down to unload ALL tables, with a specified TBCREATOR name based on foreign keys. I would like the records to be unloaded to their own files.

For Example:

There are 5 (actually there could be 100 or more, but for this purpose, I'll just use 5 for brevity) tables: TBA.ONE, TBA.TWO, TBA.THREE, TBB.ONE, TBC.ONE, TBA.FOUR, TBA.FIVE

I would use the where clause on the TBA.ONE to specify a key. It would load that record to a data set. Then it would take the foreign key of TBA.ONE, use it to get the record from TBA.TWO and put it to a data set. It would use the foreign key of TBA.THREE and get the record and put it in a data set. etc...

Would there be an issue with creating duplicate data sets when more than one table has the same foreign key?

The problem is, we have a production region where a customer says they have an issue with a specific record. Usually, we don't have that exact record in the Test Region. In order to resolve the program problem, we need the exact same data in the test region. How does one transfer all applicable records, from all applicable tables, from the one region to the other?

Dear DonnaW,

Once upon a time, Princeton Softech had the perfect solution for you; namely RelTools.

Recently however, Princeton Softech was acquired by IBM. Princeton Softech's toolset is now presented under a different name:

Test Data Management
Enterprise data management includes capabilities for streamlining the total application testing process to speed deployment while controlling costs. Improving the way you manage test data can deliver dramatic results - faster time-to-market, reduced development costs and higher quality. In short, enterprise data management helps you do more with less.

IBM® Optim™ offers comprehensive test data management capabilities for creating realistic, right-sized test databases, protecting data privacy in vulnerable development and testing environments and validating test results. You eliminate costly cloning processes and correct defects early in the development process – where they are cheapest and easiest to fix. Most importantly, Optim scales to meet your development and testing requirements, across applications, databases, operating systems and hardware platforms.

Computer Associates also has a data extract product that pulls data from tables related by RI or by user defined relationships.

Trying to build this type of tool yourself will take more than 2 FTE years and would still not be as fool proof as IBM's or CA's products.

Perhaps you may want to consult the Gartner Group.