All Forums Database
eejimkos 73 posts Joined 01/12
18 Apr 2014
Identify Set - Multiset Tables in TD 14

Hello,
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.

Raja_KT 1246 posts Joined 07/09
18 Apr 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Adeel Chaudhry 773 posts Joined 04/08
18 Apr 2014

Can you export the DDLs and then do a search on it?

-- If you are stuck at something .... consider it an opportunity to think anew.

krishaneesh 140 posts Joined 04/13
21 Apr 2014

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).

eejimkos 73 posts Joined 01/12
23 Apr 2014

Hello,
 
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.

dnoeth 4628 posts Joined 11/04
23 Apr 2014

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.

Dieter

eejimkos 73 posts Joined 01/12
24 Apr 2014

Thanks Dietter ,
I was remembering that indexes where included but i am wrong.
 

aarunsun 1 post Joined 10/12
28 Jul 2014

We have users creating a lot of tables using
1) CT
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.

Raja_KT 1246 posts Joined 07/09
28 Jul 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

tomnolan 594 posts Joined 01/08
29 Jul 2014

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.

ToddAWalter 316 posts Joined 10/11
29 Jul 2014

It is much more likely that resources will be consumed by poor choices, or lack of choice, of Primary Index on user created tables. It is worth some education to the users on how to create them appropriately. Generally user created tables are small to medium where the difference between SET and MULTISET is not generally signifcant. A badly skewed table from a poor PI will have a much greater impact.

You must sign in to leave a comment.