Subset of Data

[RogerCruse]

Hi,
Does anybody have any suggestions to this problem -

We want to copy all the table definitions (for selected databases) from one box on another smaller box but only copy a subset of the data.

I know I could setup a backup / restore process for all the tables from a given database and aviod the need to change this script everytime a new table is added or changed.

But can I extract a just subset?

Could I extract data from one AMP?

If I could then I would have an easy to manage, easy to understand subset of data on my smaller box (OK, I might need to review the Primary Indexes to aviod skewed data, but that failry easy to deal with)

Thanks in advance.

 

[BillDHS]

Have you looked at TSET?

 

[RogerCruse]

Bill,
As they sing in the film Grease - tell me more, tell me more!

Roger...

 

[BillDHS]

We are on V2R3 so I have had no opportunity to use TSET, it is a tool to test different environment designs in a "test" environment. I believe you may be able to copy your structure and a subset of data using this tool. Look up "Teradata System Emulation Tool" in your TTU documentation.

 

[TeraOz]

I pretty sure TSET doesn't actually move data - I've used it to copy table definitions/statistics from production to test systems for troubleshooting and prototyping purposes.

How you might do this depends on what you are attempting to achieve.

If you simply want to do explains on your smaller system pretending it is a larger one, use TSET - that's what it is for. If you want to setup a demo/test environment for applications you will have to do it another way.

If you want to preserve data integrity (relationships) in your copy you will probably have to code a bunch of SQL scripts to cut down the data, then use arc or FEXP/FLOAD to move the data across.

Typically this involves setting up a number of "driver" tables containing a subset of your reference table keys (e.g. 10% of all customer_ids from customer table), then join these "driver" tables (one or more of them) to the relevant reference and transaction tables containing the driver table's keys. The resultant dataset is insert/selected into a copy of the table which is then transferred to the smaller system.

If you use FEXP/FLOAD to move the data you do not really have to insert into a seperate table first which can good if space is tight.

Obviously you need to know something about the data model to be able to do this, so I doubt you'll find a tool to manage it automagically.

If you DON'T care about the relationships, you should be able to generate a bunch of insert selects which include the 'SAMPLE' keyword to get a subset of the data. Once again you would then copy the inserted tables across to the smaller system via arc of FEXP/FLOAD.

Regards,
TeraOz