sessions and accounts in Teradata

Session Modes
There are two session modes available: Teradata and ANSI. Each have a different transaction
protocol behavior and differently handle case sensitivity defaults, collating sequences, data
conversions, and display functions. The same SQL statement in one mode could perform
differently in another mode. For example, when there is an error:

• In ANSI mode, the system rolls back only the request that caused an error and not the
entire transaction. The locks already obtained are not released until COMMIT/ABORT/
ROLLBACK occurs.

• In Teradata Session mode, the system rolls back the entire transaction and all locks are
released.

MSR statements behave as though they are in Teradata session. Even though you run the MSR statements in BTEQ, the statements behave as if there are in BT and ET session. If one of the statements in MSR fails, all transactions would roll back.




on setting the Session Mode. The reasons for this are explained in the following sections.

What is Auto-Commit

Open Access products have a requirement that all statements sent to Teradata must be Auto-Committed. Therefore, every statement that is sent to Teradata must be followed by a "COMMIT WORK". Depending upon the setting of the Session Mode, the provider will have to send the "COMMIT WORK" statement, or the Teradata database will implicitly commit each statement that is processed.

Teradata Session Mode

Commands that are executed when the Session Mode is set to Teradata mode are automatically (implicitly) committed by the Teradata database. The provider does not append a "COMMIT WORK" to the statement.

Advantages

One advantage to this is that the provider does not have to append a "COMMIT WORK to each statement that is sent to Teradata. Another advantage is that the Teradata database will use the Fast Path processing and will directly send the data from the spool to the provider when retrieving the data of a deferred LOB. In contrast, when the Session Mode is set to ANSI, an extra copy of the LOB is made.
For these reasons, specifying Teradata mode will provide the best performance.

ANSI Session Mode

To implement the Auto-Commit feature when the session mode is set to ANSI requires a "COMMIT WORK" to be sent after every statement that is executed. Some open access products implement this by first sending the statement to Teradata, and then sending a "COMMIT WORK".
However, the .NET Provider for Teradata optimizes the handling of Auto-Commit by appending a "COMMIT WORK" to the statement that will be submitted to Teradata when it is possible --some statements cannot be submitted to Teradata as a multi-statement request. This significantly reduces the number of round-trips made from the provider to the Teradata database.

Performance Impact on LOB retrieval

Unfortunately, the Teradata database does the following when a "COMMIT WORK" is appended to the statement used to retrieve the data of a deferred LOB (TdClob or TdBlob):

• Makes an extra copy of the LOB.
• Does not use the Fast Path processing.

Both of these items have a negative effect on the performance of the provider during the retrieval of the LOB's data.

Unconstrained DELETE

Although there is a negative performance impact on LOB processing in ANSI mode, applications will be able to easily take advantage of the Fast Path processing when executing Unconstrained DELETE statements. An Unconstrained DELETE statement contains no constraints in the where clause --all the rows in the table will be deleted.
One of the following conditions must be met in order for Teradata to use the Fast Path processing on an Unconstrained Delete:

• It is the only DML (Data Manipulation Language) statement in the request followed by a "COMMIT WORK".

• It is the last statement in a multi-statement request followed by a "COMMIT WORK"

Because the provider will always append a "COMMIT WORK" to a DELETE statement, an Application can be assured that the Fast Path processing will be used without performing the additional step of appending "COMMIT WORK" to the end of the statement. The Fast Path processing cannot be taken advantage of by other open access products while in ANSI mode because the "COMMIT WORK" is sent after the Unconstrained DELETE has been processed.

named pipe access module in Teradata:

named pipe access module in Teradata:

Note: To invoke Teradata access module check the access module option under perform load in load request

 

Filenames in different operating systems

AXSMOD Name Specifications for Teradata Named Pipes Access Module

Operating System Teradata Parallel Transporter Version Named Pipes Access Module

Linux Shared library file called

np_axsmod.so

UNIX HP-UX PA-RISC Shared library file called

np_axsmod.sl

HP-UX IA64 Not Available

IBM-AIX Shared object file called

np_axsmod.so

Solaris SPARC Shared object file called

np_axsmod.so

Solaris Opteron np_axsmod.so np_axsmod.so

Windows 2000/XP/2003 Dynamic link library file called

np_AXSMOD.dll

Nice link on teradta named pipe access module
http://developer.teradata.com/tools/articles/understanding-named-pipe-access-module-data-transfer

To use Teradata Named Pipes Access Module on UNIX with named pipes:
1) Use the UNIX mknod command with the p option to create a named pipe. In the following
example, /tmp/mypipe is the name of the pipe:
/sbin/mknod /tmp/mypipe p
 
2 Program the writer process to send its output stream to the named pipe, as in the
following FastExport script example:
.EXPORT OUTFILE /tmp/mypipe;
 
3 Program the reader process to read from the named pipe as in the following FastLoad
script example:
axsmod np_axsmod.so “fallback_directory=...”;
define file= /tmp/mypipe;

4 Launch both the writer and the reader processes, as in the following example where
flod.cmds is the name of the FastLoad job script file:
fexp <fexp.cmd & fastload <flod.cmds &
In this example, UNIX connects both processes through the named pipe /tmp/mypipe.

Finding/Retrieving CPU cycles in Teradata

The usual causes for a frequently sumbitted SQL to take longer than usual to respond are

(a). The user's session may have been blocked or delayed.
(b). The system may have been busier than usual.
(c). The volume of data the SQL deals with may have increased.

One source I know of retrieving cpu cycles is from DBC.DQLOGTBL. It has log of all the queries run on TD system.

Below are the some of useful columns

collecttimestamp -- timestamp 
SESSIONID  --- useful if there are multiple sessions for a request
USERNAME --
LOGONSOURCE --- logon source will be unique for each request ( one request  could have multiple sessions. So grouping by this will give the cpu for each request by the user at the same time. process number ( i know for unix) would be captured in the logon string)
For more details click this link
http://developer.teradata.com/doc/connectivity/tdnetdp/13.11/webhelp/LogonSourceFormat.html


AMPCPUTIME -- cpu cycles consumed by each query
TotalIOCount
RequestNum   -- query sequence of all teh queries in the request.
StartTime
FirstStepTime
FirstRespTime
QueryText
StatementType
StatementGroup

-->simple query for calc cpu
Select *
from dbc.qrylog
where username = 'xxx'
and cast(starttime as date) = date;


---> I think
delayed time = starttime - firststepTime
execution time = firstStepTime - FirstResponseTime


Let me know if I have made some wrong assumptions above.

Notes on Teradata timestamp

IF the format of the Timestamp literal is …   THEN the data type is …
YYYY-MM-DD hh:mi:ss                               TIMESTAMP(0).
YYYY-MM-DD hh:mi:sssignhh:mi               TIMESTAMP(0) WITH TIME ZONE.
YYYY-MM-DD hh:mi:ss.ssssss                     TIMESTAMP(n), where n is the number of fractional
                                                                         seconds digits.
YYYY-MM-DD hh:mi:ss.sssssssignhh:mi     TIMESTAMP(n) WITH TIME ZONE, where n is the
                                                                         number of fractional seconds digits.




Example 1: YYYY-MM-DD hh:mi:ss Format
The following example selects all classes from the Classes table that are timestamped
November 23 2006 at 3:30:23 PM.
SELECT *
FROM Classes
WHERE Time_stamp = TIMESTAMP '2006-11-23 15:30:23';


Example 2: YYYY-MM-DD hh:mi:sssignhh:mi Format
SELECT *
FROM Classes
WHERE Time_stamp = TIMESTAMP '2002-01-01 10:37:12-08:00'


Example 3: YYYY-MM-DD hh:mi:ss.ssssss Format
SELECT *
FROM Classes
WHERE Time_stamp = TIMESTAMP '1995-07-31 10:36:02.123456'

Example 4: YYYY-MM-DD hh:mi:ss.sssssssignhh:mi Format
SELECT *
FROM Classes
WHERE Time_stamp = TIMESTAMP '1492-10-27 21:17:35.456123+07:30'

Source: Teradata documentation - SQL data types and literal

Access Rights in Teradata

http://forums.teradata.com/forum/database/access-right-abbreviations

http://www.terateamuk.com/teradata_glossary_of_terms/teradata_terms_a-d/teradata_access_rights.html

http://teradata.uark.edu/research/li/systables.html