Archives of the TeradataForum

Message Posted: Thu, 06 Nov 2003 @ 15:02:25 GMT

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

We had two production systems here for a while, one in ANSI and one in Teradata (it was a pain), but we've gone back to one system in Teradata mode. As far as we could tell, there is no mechanism to change your session mode once you're logged in. However, you can set up two DSNs with transaction modes and login as needed.

From the "ODBC Driver Setup for Teradata RDBMS" panel, select options. Go to the "Session Mode" list box and select the mode you want (ANSI, System Default, Teradata), then click OK. By the way, the list box is badly designed: three items and you have to scroll to see them all. I got in the habit of creating matched pairs of DSNs with explicit session mode just to make sure I didn't make any mistakes. TPROD and APROD, TDEV and ADEV, etc.

A warning. People setting up batch streams don't seem to have any trouble with ANSI mode, but interactive users find it non-intuitive. When people enter commands interactively, they expect them to be complete when they get the command prompt back. But ANSI mode does effectively complete the commands until they enter COMMIT WORK.

I can't tell you how many times we had an interactive user edit a production table in ANSI mode and then go home for the day, forgetting the COMMIT WORK in their haste. If they shutdown their PC, the session would abort and rollback leaving them confused ("Your computer lost my data!").

If they left the PC on overnight, though, the session would continue to hold the locks. All night. Second shift users, overnight batch jobs, and dumps would all block on the interactive session. It was so bad, we finally made everyone set Teradata Mode explicitly on their DSNs.

A second warning. DELETE < table > ALL is a command that administrators frequently enter interactively when they're doing application maintenance. It's very fast as a standalone statement in Teradata mode because the system marks the table empty and drops the blocks (nothing goes in the transaction journal).

However, if you do the DELETE < table > ALL in ANSI mode (or within a Teradata mode BT/ET), the system *must* copy the entire table to the transaction journal to allow for ROLLBACK WORK. We had user do exactly that on a 100M row table, and he couldn't figure out why the delete was still running after an hour, and we couldn't figure out why we were running out of space in DBC. A very hectic morning.

A long winded answer to your question...

/dave hough

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