Archives of the TeradataForum

Message Posted: Sun, 02 Dec 2001 @ 18:24:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Dieter,

I think that you've got a mistake someplace. I created a table and was able to modify a column name in the data dictionary and then have that change appear in a SHOW TABLE. Here's what I did:

    create set table tmp.jjh_1
       ( col_1 integer
       , col_2 integer
       , col_3 integer
       )

       primary index col_1;

A SHOW TABLE gave the following:

     CREATE SET TABLE tmp.jjh_1 ,FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           col_1 INTEGER,
           col_2 INTEGER,
           col_3 INTEGER)
      PRIMARY INDEX ( col_1 );

I then ran the following UPDATE (after the appropriate DIAGNOSTIC statement):

     update dbc.tvfields set fieldname='col_3a'
        where tableid = dbc.tvm.tvmid
             and dbc.tvm.tvmname='jjh_1'
             and fieldname='col_3';

     *** Update completed. One row changed.
     *** Total elapsed time was 1 second.

Finally I ran another SHOW TABLE:

    CREATE SET TABLE tmp.jjh_1 ,FALLBACK ,
         NO BEFORE JOURNAL,
         NO AFTER JOURNAL
         (
          col_1 INTEGER,
          col_2 INTEGER,
          col_3a INTEGER)
     PRIMARY INDEX ( col_1 );

As you can see, the change made in the UPDATE is reflected in the new SHOW TABLE ('col_3' changed to 'col_3a').

I can't explain your result. Although I think that it was just a typo, I did notice in your note you said that the column name change you attempted was done to DBC.TVM - the column names are kept in DBC.TVFIELDS. The one thing that did occur to me was that you might have gotten different results if you had issued a 'DIAGNOSTIC SPOIL;' statement (to spoil the cache) after your UPDATE and before you did your SHOW TABLE. It's just a guess - I thought that I would have to do one myself, but it proved to be unnecessary when I ran my test.

So I maintain my position: The results of a SHOW TABLE are reconstructed from both the Data Dictionary and the Table Header. Going back to the original question, it really doesn't matter where it comes from or how it's done, you can't get all the details of the creation of a table without performing a SHOW TABLE and then parsing the results of that SHOW TABLE.

That was a good job noticing the behaviour of CHECKOPT (in DBC.TVM) and a unique PI. I had originally thought that CHECKOPT indicated whether a table was SET or MULTISET - it doesn't. What it does seem to indicate is whether duplicate row checking will be performed:

- For a SET TABLE, duplicate row checking will be performed.

- For a MULTISET TABLE with a non-unique PI, no duplicate row checking will be performed.

- For a MULTISET TABLE with a unique PI, duplicate row checking will be performed.

The CHECKOPT setting for a MULTISET TABLE with a unique PI initially threw me until I realized that there isn't a difference between a MULTISET TABLE with a unique PI and a SET TABLE: After all, each row must have a unique PI - that prohibits the existence of a duplicate row. Whether a table is SET or MULTISET must be a flag in the Table Header.

	<Prev	Next>		<<First	<Prev	Next>	Last>>