When you specify the JDBC connection parameter TMODE=ANSI, then the CREATE TABLE command's default behavior is to create a MULTISET table.
When you specify the JDBC connection parameter TMODE=TERA, then the CREATE TABLE command's default behavior is to create a SET table.
In either transaction mode, you can override the CREATE TABLE command's default behavior by specifying the SET or MULTISET attribute explicitly.
Then what is the difference between GENERIC JBDC and Teradata Database connections. What changes does it bring when i create a user depending on these connections.
Could plz help me understand the difference with a suitable example?
Are you referring to Teradata Studio's "New Connection Profile" dialog box, with "Generic JDBC" and "Teradata Database" options in the list?
You may be confused about the purpose of that list in the "New Connection Profile" dialog box. That list is intended to permit you to select a JDBC driver. It is not intended for selecting the transaction mode for Teradata Database connections.
To connect to the Teradata Database, you should choose "Teradata Database" from that list.
The "Generic JDBC" option is intended for using some other database vendor's JDBC driver that is not present in the list.
Assuming that you have chosen "Teradata Database" from the list, then you can subsequently choose the transaction mode by clicking on the "Next" button and going to the "New Teradata Connection Profile" page of the dialog box. You specify the transaction mode with the TMODE property in the "JDBC Connection Properties" section of the "New Teradata Connection Profile" page of the dialog box.
There are many differences in behavior between ANSI mode and legacy Teradata mode. The documentation for the differences is available on www.info.teradata.com / Teradata Database Reference / SQL Request and Transaction Processing / Chapter 9 Locking and Transaction Processing / sections: "ANSI Session Mode" and "Teradata Session Mode".