Select (SHOW sometable) into volatile table

UDA
pxr
N/A

Select (SHOW sometable) into volatile table

hi,

i am a tester. After changes in some object (or in some source object etc) the first thing we have to test is to compare old and new object whether only the changes documented are done. I would like to somehow make this process automatic.

To compare two sometables (prod and test) we just manually compare the output of "SHOW sometable" from different environments. Is it somehow possible to select the results of this command? I would just select then the results to some volatile tables and create sql to compare the two tables.
3 REPLIES

Re: Select (SHOW sometable) into volatile table

Hi,
The task you are requesting is not a straight forward implementation in Teradata. But what you could do is as follows:
1. Use BTEQ and get the Structure of the tables fromthe 2 environments in 2 seperate files. Please note that Formatting of the output is very important.
2. In a Shell Script/File compare, compare these 2 files for inconsistencies.
3. Document the difference results and this will be your script output which you can then refine manually.
Hope that helps. You can call multiple BTEQs and Shell commands from a single script, hence you can finally write just one Shell Script which does all of the above stated or break them down in to multiple tasks having a script for each.
N/A

Re: Select (SHOW sometable) into volatile table

It depends what differences you are looking for.

DBC.Columns contains details of every column, DBC.Indices contains details of the indices.
You can do a relatively simple compare based on these.

The only thing it is not so easy to get is the SET/ Multiset propery if the table - I am sure it is in DBC.TVM, but do not know where.

This only works for Global Temp and permanent tables - volatile tables dont go into the dictionary.

Let me know if you need more.
pxr
N/A

Re: Select (SHOW sometable) into volatile table

thanks jimm, that was really helpfull