[AD} [DB2-L] DB2 for z/OS "Microdatabases"

Robert Galambos

[AD} [DB2-L] DB2 for z/OS "Microdatabases"
See below



The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Wednesday, February 02, 2011 10:52 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS "Microdatabases"

I probably should put AD on the subject line, but since Mr. Phil Sevetson already mentioned the products, I just want to add a minor note.

No worries

If you want to copy RI-related (whether this is DB2 RI or user defined RI), you can look at CA RC/Extract, FileAid/RDX from Compuware - and like Phil mentioned - Princeton was acquired by IBM a few years ago and the product was renamed to Optim (there are more vendors out there).

Yes. But for at least Compuware File-AID RDX we can handle relationships that are NOT DB2 as well. MVS files and IMS db can be included within the same step/process if need be.

If you don't care about RI - there are many more options to copy subset of data. If you're really on the hunt - it could be worthwhile to ask your preferred vendors what they can offer based on your requirements.

Could not have said it better myself.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Matt Crowley
Sent: Wednesday, February 02, 2011 9:27 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS "Microdatabases"

Does anyone know of and have experience with products that would create what I'll call "microdatabases" -- copies of tables and ideally populate them with a subset of data from the full tables? Ideally it could follow and account for foreign keys to know all the tables (and related data) that would be needed to create a logically consistent copy of the tables and subset of data.

We are in a DB2 for z/OS shop so we'd be interested in existence of and experience with products that would work on that platform.

Thanks,
Matt

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Avram Friedman

Re: DB2 for z/OS "Microdatabases"
(in response to Robert Galambos)
Please allow me to make a short case for the following 2 items.

1. Unit test and system test are not the same.
2. Duplication of test to production.

1. Unit test and system test are not the same. Universal richness of the testing environment is a system test requirement (make sure all the related parts work together). For unit test the limiting factor is developer time and developers tend to be concerned with code development, not the richness of entire systems. They tend to be very quailfied to create unit test data or exployt the system test environment.

2. Duplication of test to production. The question about "Microdatabases" is valid as well as the reference to complexity with the single example "RI". Unfortunetly RI is an example of complexity but not even a scratch on the surface of the entire list. There are many challanges to overcome in the design of system test datae best. Special cases abound. The best way to insure that all known cases are included in the test data (and this does ensure all cases are tested) is to include all data.
The common argument against this is cost. Just like the critical sucess factor for test data changes (is not always RI) so do the critical factors for cost. Sometimes its CPU cycles, Sometimes developer time, Sometimes test window, Sometimes Client (tester) time, floor space & etc etc. Today a common buzz phrase is "DASD is cheap". Personally I have seen a lot of software solutions to the problem of testing and test data end up in the bit bucket.

Avram Friedman



On Wed, 2 Feb 2011 10:26:43 -0500, Matt Crowley <[login to unmask email]> wrote:

>Does anyone know of and have experience with products that would create what I'll call "microdatabases" -- copies of tables and ideally populate them with a subset of data from the full tables? Ideally it could follow and account for foreign keys to know all the tables (and related data) that would be needed to create a logically consistent copy of the tables and subset of data.
>
>We are in a DB2 for z/OS shop so we'd be interested in existence of and experience with products that would work on that platform.
>
>Thanks,
>Matt
>
>_____________________________________________________________________
>* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
>* If you are going to attend only one conference this year, this is it! *
>** DB2 certification -> no additional charge
>** Meet fellow DB2 users and leading DB2 consultants
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Maja Markotic

Re: DB2 for z/OS "Microdatabases"
(in response to Avram Friedman)
Please don't get me going! :-)

What about industry (such as PCI DSS) and other standards which pretty much prevent you from simply copying a subset of production data to test environment, the ability to go back (to repeat a test case in exactly the same conditions as the first time around) and forth (to test for date-dependent conditions) in time, statistical models which require at least 18 months of historical data.........?

... and of course, NOTHING is cheap these days, not even DASD.

Maja


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Avram Friedman
Sent: Wednesday, February 02, 2011 5:19 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS "Microdatabases"

Please allow me to make a short case for the following 2 items.

1. Unit test and system test are not the same.
2. Duplication of test to production.

1. Unit test and system test are not the same. Universal richness of the testing environment is a system test requirement (make sure all the related parts work together). For unit test the limiting factor is developer time and developers tend to be concerned with code development, not the richness of entire systems. They tend to be very quailfied to create unit test data or exployt the system test environment.

2. Duplication of test to production. The question about "Microdatabases" is valid as well as the reference to complexity with the single example "RI". Unfortunetly RI is an example of complexity but not even a scratch on the surface of the entire list. There are many challanges to overcome in the design of system test datae best. Special cases abound. The best way to insure that all known cases are included in the test data (and this does ensure all cases are tested) is to include all data.
The common argument against this is cost. Just like the critical sucess factor for test data changes (is not always RI) so do the critical factors for cost. Sometimes its CPU cycles, Sometimes developer time, Sometimes test window, Sometimes Client (tester) time, floor space & etc etc. Today a common buzz phrase is "DASD is cheap". Personally I have seen a lot of software solutions to the problem of testing and test data end up in the bit bucket.

Avram Friedman



On Wed, 2 Feb 2011 10:26:43 -0500, Matt Crowley <[login to unmask email]> wrote:

>Does anyone know of and have experience with products that would create what I'll call "microdatabases" -- copies of tables and ideally populate them with a subset of data from the full tables? Ideally it could follow and account for foreign keys to know all the tables (and related data) that would be needed to create a logically consistent copy of the tables and subset of data.
>
>We are in a DB2 for z/OS shop so we'd be interested in existence of and experience with products that would work on that platform.
>
>Thanks,
>Matt
>
>_____________________________________________________________________
>* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
>* If you are going to attend only one conference this year, this is it! *
>** DB2 certification -> no additional charge
>** Meet fellow DB2 users and leading DB2 consultants
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Disclaimer:
Ova elektronička poruka je povjerljiva i može sadržavati povlaštene informacije. Ako niste naznačeni primatelj, niste ovlašteni čitati, printati, pohraniti, obrađivati ili priopćavati ovu poruku. Ako ste ovu poruku primili greškom, molimo Vas da o tome odmah obavijestite pošiljatelja i izbrišete ovu poruku, njene privitke i kopije. Zagrebačka banka d.d. ne preuzima nikakvu odgovornost s obzirom na bilo koju moguću netočnost bilo kojeg podatka koji je sadržan u ovoj poruci ako takav podatak nije povezan s registriranim predmetom poslovanja Zagrebačke banke d.d. Stajališta izražena u ovoj poruci ne odražavaju nužno službena stajališta Zagrebačke banke d.d. Hvala!
This e-mail is confidential and may also contain privileged information. If you are not the intended recipient, you are not authorised to read, print, save, process or disclose this message. If you have received this message by mistake, please inform the sender immediately and delete this e-mail, its attachments and any copies. Zagrebacka banka d.d. does not take any responsibility with regards to any possible inaccuracy of any data contained in this e-mail if such data do not relate to the registered operations of Zagrebacka banka d.d. The opinions expressed in this e-mail do not necessarily reflect the official positions of Zagrebacka banka d.d. Thank you!

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Michael Bruzdzinski

Re: DB2 for z/OS "Microdatabases"
(in response to Maja Markotic)
TestBase comes to mind as does using FILE-AID RDX for DB2

Michael Bruzdzinski
Social Security Administration
OESAE/DDBS/DBSB
[login to unmask email]
410-966-7185 (v)
443-379-2282 (Blackberry)


"Nobody knows what a DBA does but everybody has to have one, because nobody can afford two." (from "The IT Menagerie", IBM Systems Journal, circa 1990)


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Matt Crowley
Sent: Wednesday, February 02, 2011 10:27 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS "Microdatabases"

Does anyone know of and have experience with products that would create what I'll call "microdatabases" -- copies of tables and ideally populate them with a subset of data from the full tables? Ideally it could follow and account for foreign keys to know all the tables (and related data) that would be needed to create a logically consistent copy of the tables and subset of data.

We are in a DB2 for z/OS shop so we'd be interested in existence of and experience with products that would work on that platform.

Thanks,
Matt

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Avram Friedman

Re: DB2 for z/OS "Microdatabases"
(in response to Michael Bruzdzinski)
"
... and of course, NOTHING is cheap these days, not even DASD.

Maja
"

While the above statement may be true so is the following

And of course, NOTHING is cheap these days, espically DBAs (who get the load requests)
As the 80s system journal line goes
Every one needs a DBA and no one can afford two


Ultimatly the solution to protection being required for production data is to apply the same rules procedures and controls to test systems as are applied to production. If a shop can do DR it can do testing. In deed one of the more effective system test stratagies I have worked with is DASD mirrored locally and remotely. Testing every day at 9am, one of the mirrors broken, mirror system IPLed and made availale for system testing. An hour later every thing was back to normal. Of course extended testing windows could be arranged for.

Please recall the original question
Some one looking for a microdatabase solution for testing that must include RI support.
It did not strike me as well thought out.
Did not say must include objects created under DB2 V3 (I have had weird failures with newly created test systems to test a production system that contains very old objects) Failures included a Sev 1 outage on Newyears eve when ALTERS were applied to the old objects and caused problems. The Alters worked flawlessly on the new test copies in pre implelentation testing.
Did not include objects that are approaching physical DB2 limits and are expected to exceed them. indeed the plan is to make the test environment smaller not bigger ... and historically everyone expects growth not shrinkage. It pays to test for what one expects.

I just proposed what might be a better solution. duplicate production.
I am surprized no one proposed a solution that may be valid for some shops espically those that have never done testing before and hence the need for a trick ... Do what you have always done, test in production. Production defined by some as that activity that interfears with testing.

Regards
Avram Friedman

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

[login to unmask email]

Re: DB2 for z/OS "Microdatabases"
(in response to Avram Friedman)
Well I think that I need to chime in now.

There are a number of different tests that should be done in any QA process.

1) Production data will NOT useful for 'testing for failure' test. In other words how will the application behave if there is something 'wrong'. Production data by its nature, (hopefully) will not have any data that will fail the edits.

2) Production data will force the application to take production times for testing. So instead of being able (within as an example an 8 HOUR window) to test two or times the same application (small batch/online process) you may be only have a chance to run a test only once. This will not allow more extensive testing.

3) Production data will not allow for Volume/response testing. (Sort off). One would need, as an example, test volumes of data that represents grow of the business within a certain period of time. If 'we open 3 stores' will our systems handle the increase?

4)Production data will not allow for parallel testing. How many different. Testing can be done at the same time. If there is a need to two different teams testing at the same time, but with only one copy of the data (how many copies of production sized data can one have). This increases a chance of one testing interfering with another.

5) Privacy issues. Production volumes require (sometimes by regulations) to have PII (personnel identifiable information) removed. Larger volumes, more time it will take prep the data.

As you can see, I feel that using production data, is generally not the best way to go. (Though there are test that you may need to). And if one does something, it should be done the right.


If you want, feel free in contacting me about this
-----------Original Message-----------
From: Avram Friedman
To: IDUG DB2-L
ReplyTo: IDUG DB2-L
Date: Feb 03 20:11:34
Subject: Re: [DB2-L] DB2 for z/OS "Microdatabases"


"
... and of course, NOTHING is cheap these days, not even DASD.

Maja
"

While the above statement may be true so is the following

And of course, NOTHING is cheap these days, espically DBAs (who get the load requests)
As the 80s system journal line goes
Every one needs a DBA and no one can afford two


Ultimatly the solution to protection being required for production data is to apply the same rules procedures and controls to test systems as are applied to production. If a shop can do DR it can do testing. In deed one of the more effective system test stratagies I have worked with is DASD mirrored locally and remotely. Testing every day at 9am, one of the mirrors broken, mirror system IPLed and made availale for system testing. An hour later every thing was back to normal. Of course extended testing windows could be arranged for.

Please recall the original question
Some one looking for a microdatabase solution for testing that must include RI support.
It did not strike me as well thought out.
Did not say must include objects created under DB2 V3 (I have had weird failures with newly created test systems to test a production system that contains very old objects) Failures included a Sev 1 outage on Newyears eve when ALTERS were applied to the old objects and caused problems. The Alters worked flawlessly on the new test copies in pre implelentation testing.
Did not include objects that are approaching physical DB2 limits and are expected to exceed them. indeed the plan is to make the test environment smaller not bigger ... and historically everyone expects growth not shrinkage. It pays to test for what one expects.

I just proposed what might be a better solution. duplicate production.
I am surprized no one proposed a solution that may be valid for some shops espically those that have never done testing before and hence the need for a trick ... Do what you have always done, test in production. Production defined by some as that activity that interfears with testing.

Regards
Avram Friedman

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv


Sent from my BlackBerry device

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Avram Friedman

Re: DB2 for z/OS "Microdatabases"
(in response to galambos@ROGERS.COM)
Please note
I did not suggest testing be done in production.

The tone of my note was that it sounded like a shop, new to testing that for some unspecified reason wanted to purchace a product to build microdatabases that supported maintaining RI relationships.
Strange very strange.
My first response to the question dealt with what sort of testing was being planned.
Unit test that is not usually done on live or extracted data.
System test that is best done on a copy of production.

I went on to point out requirements and critical factors change over time.
historically the problem was saving machine cycles
today some say dasd is cheap.

some one responded that having real values in test data can be problemmatic
and dasd is not cheap

i once again reviewed the details of this claim and once again pointed out that the term icrodatabases revealed a lot about the question.

This question is being asked by someone so new to mainframe DB2 that he/she invented a new word to discribe his objective and that word has very little to do with DB2 data. Microdatabase. Did anyone notice this in the subject line?
Database in DB2 (or sql variations) means something quite diffrent than most other DBMS In most DBMS database means an orderly collection of data sometimes together with utilities and application aids. In DB2 / SQL its the unit of operation, tablespace the unit of recovery, table the unit of application access.

The original question is from a person with money burning a hole in his / her pocket who wants to buy something but is not a DB2 for z/os tester or even a person who supports them. the words are wrong, not missspelled, not bad grammer, but flat out from left field

So it is fair to ask, what is the real question
System test or unit test?
Testing at all?
Any previous testing background or does the shop use the famous, common, effective but problem riden and dangerios technique of testing in production today?

There have been dozens of responses to the original question and we have not heard from the original poster about what he really ment.

To toot my own horn so far I think I am the only person who did not jump to conclusions and think this peron required a sales pitch but rather needed a chance to explore DB2 and testing options.
I do not merit the flames that I am suggesting testing in production (I didn't) or cost is no problem (I did not say this either)

Best wishes
Avram Friedman

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv