1.Is there any way to identify the set-multiset tables in TD14?(except the like expression on request text)?
2.Still the syntax create table as... , does not include the constraints?
Thank you very much.
Did you try with CheckOpt ,dbc.tables? Also you can get with these conditions:
where tablekind = 't'
and requesttext like '%create%'
and requesttext like '% set%'
Not ableto understand your second question clearly. Do you mean to say that your create table... the constraints defintions got dropped off? If so even when uou include WITH DATA AND STATS
The dbc.tables gives the last statement which modified the table but does not truly reflect the table definition. Instead as Adeel suggested, export the show table and do a search. I am not pretty sure if the below is correct or not but have heard of this. There is a column checkopt in the dbc.tables which if the value is 'Y' indicates the table to be a multiset but not having any unique index. But if the checkpopt column has a 'N', then it indicates the table to be a set table or a multiset table with unique indexes(PI or SI).
Thank you very much for your time and answers .
I was checking the 14 documentation and i was hopping that something was change on V 14.
I will make a select statement , but there is a lot of search , i have to check almost 1000 tables.
Furthermore, if the table has constraints (primary index , soft RI , constraint on columns ) , with TD syntax create table as... with data and stats , we do not transfer them to the new DDL syntax
Thanks once more.
CREATE TABLE AS existing_table copies everything (including SET/MULTISET, [NOT] NULL, FORMAT, indexes and checks) but Foreign Keys and Triggers.
But when you do CREATE TABLE AS SELECT everything is lost and reverts back to defaults.
This never changed, it's the same in TD14.
We have users creating a lot of tables using
2) create table as..
The tables get created as SET tables in the above cases.
Is there a way to change the settings to get the tables created as MULTISET instead of a SET.
i.e even if the users use CT or CREATE TABLE as
the tables should be created as MULTISET internally.
Is there a setting that can be changed to have all the tables created as MULTISET
The idea is to save resources by creating Multiset instead of SET.
Please let me know if there are any settings that can be changed to have this feature enabled.
In td mode, it will be set table. If you want to change to multiset by default, then it has to be changed to ANSI mode. You may need to MODIFY in dbs control settings in GENERAL , if my memory does not fail, it is 8. Before changing , verify all end to end.
As Raja noted, you can change the default session mode in dbscontrol from Teradata to ANSI. That is dbscontrol General setting number 8.
A couple of things to be aware of...
You have to be very careful about changing that setting, because you may break existing applications. For example, a stored procedure created in one session mode cannot be called in the other mode. So if you applications currently call Teradata-mode stored procedures, they will fail if the application is run in ANSI mode.
Changing the dbscontrol General setting 8 will only affect applications that do not explicitly specify a transaction mode at logon, or in their connection parameters. In other words, dbscontrol General setting 8 is only a default that is used when an application doesn't specify a transaction mode. There is no setting that enforces a particular session mode.
If you are concerned about users creating tables incorrectly, you may need to revoke create table privilege.