On 8/25/2014 1:45 PM, Bill Howie wrote:
I'm hoping the awesome folks here on the MIDRANGE-L board can help me.
I'm working on a program whose goal is to copy a subset of data out of an
ERP system.
-snip-
Hopefully this isn't completely confusing. Any input would be much
appreciated. Thanks!
I was a bit confused at what the end result needs to be. If you need to
extract a subset of your production data for testing purposes, then you
need the parent and all child records.
1 customer master record
3 customer email records
227 customer purchase history records
12 customer service tickler records
...and so on. All of the tables having data that refers to customer
number, but only the rows that actually refer to the customers in your
'test' customer master file.
There's a whole industry based on automating this. I don't have the
budget, so I roll my own. I use a combination of the catalogue tables
SYSCOLUMNS and SYSTABLES as well as CRTDUPOBJ and hard coding. My
database has been around for decades, and the relations between tables
are not all known to DB2. Column names are 6 character gibberish (cust,
cust#, custno, cstnbr, ncust, numb, mast#, custid) and sometimes not
even the same data type (char/numeric). So I have a CL program that
'knows' which child files I need to extract data from in order to get a
'sane' subset of my production database.
It all hinges around the idea of:
insert into test.daughter
values(select * from prod.daughter)
where cust# in (select cust from test.custmast)
I load the customers I want from prod to test CUSTMAST and let the CLP
drag all the child rows along for the ride. If, like me, you have a
standard 'stable' of test customers, you can put that list into a table
and use them to populate your test CUSTMAST:
insert into test.custmast
values(select * from prod.custmast)
where cust in (select cust#t from test.testcust)
Hope this gives you some ideas to work with.
--buck
midrange.com
