Teradata Help - Teradata Database Tips and Tricks

Teradata ROWNUM and ROW_NUMBER

2012-01-26T13:55:00.000-08:00

There is no ROWNUM pseudo column available in Teradata but you may use ROW_NUMBER() aggregation function instead to get a sequence number:

SELECT
item,
date,
ROW_NUMBER() OVER (ORDER BY date DESC)
FROM items

Teradata jobs

2012-01-26T13:51:00.000-08:00

Available Teradata positions

Teradata Corporation:
http://www.teradata.com/careers/

Teradata jobs in the US:
http://jobsearch.monster.com/search/?q=teradata

Teradata jobs in the UK:
http://www.itjobswatch.co.uk/jobs/uk/teradata.do

Teradata jobs worldwide:
http://www.indeed.com/q-Teradata-jobs.html

Teradata DATE

2012-01-26T13:47:00.000-08:00

Teradata ANSI DateTime data types include:
• DATE
• TIME
• TIME WITH TIME ZONE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE

The supported DateTime functions are:
• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• EXTRACT

Teradata ADD_MONTHS function:

Example 1
This statement returns the current date plus 13 years.
SELECT ADD_MONTHS (CURRENT_DATE, 12*13);

Example 2
This statement returns the date 6 months ago.
SELECT ADD_MONTHS (CURRENT_DATE, -6);

Example 3
This statement returns the current TIMESTAMP plus four months.
SELECT ADD_MONTHS (CURRENT_TIMESTAMP, 4);

Example 4
This statement returns the TIMESTAMP nine months from January 1, 1999. Note the literal form, which includes the keyword TIMESTAMP.
SELECT ADD_MONTHS (TIMESTAMP '1999-01-01 23:59:59', 9);

Example 5
This statement adds one month to January 30, 1999.
SELECT ADD_MONTHS ('1999-01-30', 1);
The result is 1999-02-28.

Example 6
The result of the SELECT statement in this example is a date in February, 1996. The result would be February 31, 1996 if that were a valid date, but because February 31 is not a valid date, ADD_MONTHS normalizes the answer. That answer, because the DAY value in the source date is greater than the last DAY value for the target month, is the last valid DAY value for the target month.
SELECT ADD_MONTHS ('1995-12-31', 2);
The result of this example is 1996-02-29. Note that 1996 was a leap year. If the interval were 14 months rather than 2, the result wouldbe '1997-02-28'.

Example 7
This statement performs the converse of the ADD_MONTHS function in “Example 5”. You might expect it to return ‘1999-01-30’, which is the source date in that example, but it does not.
SELECT ADD_MONTHS ('1999-02-28' , -1);
ADD_MONTHS returns the result 1999-01-28. The function performs as designed and this result is not an error, though it might not be what you would expect from reading “Example 5.”

Example 8
You might expect the following statement to return ‘1999-03-31’, but it does not.
SELECT ADD_MONTHS ('1999-02-28' , 1);
ADD_MONTHS returns the result 1999-03-28.

Example 9
You might expect the following statement to return ‘1999-03-31’, but it does not.
SELECT ADD_MONTHS ('1999-04-30' , -1);
ADD_MONTHS returns the result 1999-03-30.

Example 10
You might expect the following statement to return '1999-05-31', but it does not.
SELECT ADD_MONTHS ('1999-04-30' , 1);
ADD_MONTHS returns the result 1999-05-30.

Teradata EXTRACT function:

Example 1
The following example returns the year, as an integer, from the current date.
SELECT EXTRACT (YEAR FROM CURRENT_DATE);

Example 2
Assuming PurchaseDate is a DATE field, this example returns the month of the date value formed by adding 90 days to PurchaseDate as an integer.
SELECT EXTRACT (MONTH FROM PurchaseDate+90) FROM SalesTable;

Example 3
The following returns 12 as an integer.
SELECT EXTRACT (DAY FROM '1996-12-12');

Example 4
This example returns an error because the character literal does not evaluate to a valid date.
SELECT EXTRACT (DAY FROM '1996-02-30');

Example 5
The following returns an error because the character string literal does not match the ANSI SQL:2008 date format.
SELECT EXTRACT (DAY FROM '96-02-15');
If the argument to EXTRACT is a value of type DATE, the value contained is warranted to be a valid date, for which EXTRACT cannot return an error.

Example 6
The following example relates to non-ANSI DateTime definitions. If the argument is a character literal formatted as a time value, it is converted to REAL and processed. In this example, 59 is returned.
SELECT EXTRACT (MINUTE FROM '23:59:17.3');

Example 7
This example returns the hour, as an integer, from the current time.
SELECT EXTRACT (HOUR FROM CURRENT_TIME);
Current time is retrieved as the system value TIME, to the indicated precision.

Example 8
The following example returns the seconds as DECIMAL(8,2). This is based on the fractional
seconds precision of 2 for CURRENT_TIME.
SELECT EXTRACT (SECOND FROM CURRENT_TIME (2));

See more - SQL Functions, Operators, Expressions, and Predicates:
http://www.info.teradata.com/edownload.cfm?itemid=102320046

Download Teradata 13 for free

2012-01-26T13:38:00.000-08:00

Download Teradata 13

Teradata 13 Express for VMware Player:
http://downloads.teradata.com/download/database/teradata-express/vmware

Teradata Index

2012-01-26T12:23:00.000-08:00

An index is a physical mechanism used to store and access the rows of a table. Indexes on
tables in a relational database function much like indexes in books, they speed up information
retrieval.

In general, Teradata Database uses indexes to:
• Distribute data rows.
• Locate data rows.
• Improve performance.
• Ensure uniqueness of the index values.

Introduction to Teradata:
http://www.info.teradata.com/edownload.cfm?itemid=102320022

Teradata Index Wizard:
http://www.info.teradata.com/edownload.cfm?itemid=110590039

What is Teradata

2012-01-26T12:19:00.000-08:00

What is Teradata?

Teradata developers designed Teradata Database from mostly off-the-shelf hardware components. The result was an inexpensive, high-quality system that exceeded the performance of conventional relational database management systems. The hardware components of Teradata Database evolved from those of a simple parallel database computer into those of a general-purpose, massively parallel computer running the database. The architecture supports both single-node, Symmetric Multiprocessing (SMP) systems and multinode, Massively Parallel Processing (MPP) systems in which the distributed functions communicate by means of a fast interconnect structure. The interconnect structure is the BYNET for MPP systems and the boardless BYNET for SMP systems.

Introduction to Teradata:
http://www.info.teradata.com/edownload.cfm?itemid=102320022

Database design:
http://www.info.teradata.com/edownload.cfm?itemid=102320020

Teradata FastLoad

2012-01-26T12:17:00.000-08:00

Teradata FastLoad is a command-driven utility which can be used to quickly load large amounts of data in an empty table on a Teradata Database.

Teradata FastLoad uses multiple sessions to load data. However, it loads data into only one table on a Teradata Database per job. To load data into more than one table in the Teradata Database, multiple Teradata FastLoad jobs must be submitted—one for each table.

Teradata FastLoad Reference:
http://www.info.teradata.com/edownload.cfm?itemid=110590003

Teradata Exadata

2012-01-26T12:13:00.002-08:00

Teradata vs Exadata comparison:

Exadata is still Oracle:
http://www.teradata.com/t/white-papers/Exadata-the-Sequel-Exadata-V2-is-Still-Oracle/?type=WP

Exadata is still more Oracle:
http://www.youtube.com/watch?v=25gV37PfYuY

Oracle Teradata

2012-01-26T12:13:00.000-08:00

Oracle Teradata comparison:

Exadata is still Oracle:
http://www.teradata.com/t/white-papers/Exadata-the-Sequel-Exadata-V2-is-Still-Oracle/?type=WP

Exadata is still more Oracle:
http://www.youtube.com/watch?v=25gV37PfYuY

Teradata to Oracle migration:
http://www.oracle.com/technetwork/database/migration/teradata-089107.html

Oracle to Teradata migration:
http://www.teradata.com/data-warehouse-migration/oracle-migration-services/

Migration from Oracle to Teradata:
http://www.teradata.com/podcasts/Migration-from-Oracle-to-Teradata/

Teradata BTEQ

2012-01-26T12:11:00.000-08:00

BTEQ is an abbreviation of Basic Teradata Query, which is a general-purpose, command based program that allows users on a workstation to communicate with one or more Teradata Database systems, and to format reports for both print and screen output. Using BTEQ you can submit SQL queries to Teradata Database. BTEQ formats the results and returns them to the screen, a file, or to a designated printer.

Teradata BTEQ documentation:
http://developer.teradata.com/sites/all/files/documentation/linked_docs/2414020A_BTEQ-Reference-13.10.pdf

Teradata PDF

2012-01-26T11:49:00.000-08:00

Teradata Studio Express

2012-01-26T11:35:00.000-08:00

Teradata Studio Express is a toolkit for Teradata Database systems and allows the data to be manipulated and stored on the desktop. It is built on the Eclipse Rich Client Platform (RCP).

Teradata Studio Express download:
http://downloads.teradata.com/download/tools/teradata-studio-express

Teradata Studio Express help:
http://developer.teradata.com/tools/articles/teradata-studio-express

Teradata SQL Assistant

2012-01-26T11:33:00.000-08:00

Teradata SQL Assistant download

Teradata SQL Assistant for Windows (Teradata Tools and Utilities - Windows Installation Package):
http://downloads.teradata.com/download/tools/teradata-tools-and-utilities-windows-installation-package

Teradata SQL Assistant Java Edition:
http://developer.teradata.com/tools/articles/sql-assistant-java-edition

Teradata download

2012-01-26T11:28:00.000-08:00

Download Teradata 13.10

Teradata Express for VMware is a free, fully-functional Teradata database.

Teradata Express for VMware Player:
http://downloads.teradata.com/download/database/teradata-express/vmware

VMware Player:
http://www.vmware.com/products/player/

Teradata SQL

2012-01-26T11:21:00.000-08:00

Teradata SQL reference documentation (Teradata 13.10):

SQL Data Manipulation Language:
http://www.info.teradata.com/edownload.cfm?itemid=102320040

SQL Functions, Operators, Expressions, and Predicates:
http://www.info.teradata.com/edownload.cfm?itemid=102320046

SQL Data Definition Language - Detailed Topics:
http://www.info.teradata.com/edownload.cfm?itemid=102320036

SQL Data Definition Language - Syntax and Examples:
http://www.info.teradata.com/edownload.cfm?itemid=102320038

SQL External Routine Programming:
http://www.info.teradata.com/edownload.cfm?itemid=102320044

Connecting to a Teradata database using Teradata JDBC driver

2009-08-22T06:36:00.000-07:00

Teradata JDBC driver enables all Java applications to connect a Teradata database using a well-established industry standard Java Database Connectivity (JDBC) API interface. The latest version of the JDBC driver is release 13.00 and it is available for download on Teradata Download Center together with sample Java code.

The driver is backward compatible and release 13.00 supports Teradata Database V2R6.0, V2R6.1, V2R6.2, 12.00 and 13.00. The full list of new and noteworthy features accompanied with detailed release notes is available in the README file.

A new database connection can be obtained in a standard JDBC way by using java.sql.DriverManager.getConnection method with a proper connection string to get a new instance of java.sql.Connection.

The simplest database connection string would be:

jdbc:teradata://ServerHost/database=MyDatabaseName

There is a number of available connection parameters, all are described in detail in the Teradata JDBC Driver User Guide:

ACCOUNT=account_id
CHARSET={ASCII/LATIN1_0A/KANJISJIS_0S/KANJIEUC_0U/LATIN9_0A/LATIN1252_0A/HANGULKSC5601_2R4/SCHGB2312_1T0/TCHBIG5_1R0/UTF8/UTF16}
COMPAT_DBS=true/false
COMPAT_ISAUTOINC=true/false
COMPAT_ISCURRENCY=true/false
COMPAT_ISSIGNED=true/false
COMPAT_ISSEARCH=true/false
COMPAT_ISREADONLY=true/false
COMPAT_ISWRITABLE=true/false
COMPAT_ISDEFWRIT=true/false
COMPAT_GETSCHEMA=ReturnValue
COMPAT_GETTABLE=ReturnValue
DATABASE=default database name
DBS_PORT=number
ENCRYPTDATA={ON/OFF}
GOVERN={ON/OFF}
LOB_SUPPORT={ON/OFF}
LOB_TEMP_TABLE=tableName or databaseName.tableName
LOG={ERROR/INFO/DEBUG}
LOGDATA=varies according to the LOGMECH selection
LOGMECH={TD1/TD2/KRB5/LDAP}
NEW_PASSWORD=new password string
PARTITION= {DBC/SQL,MONITOR}
SESSIONS=n, where 1 <= n <= number of AMPs
SP_SPL={SPL/NOSPL}
TMODE={ANSI/TERA/DEFAULT}
TSNANO={0...6}
TYPE={DEFAULT/FASTLOAD/FASTEXPORT}
USEXVIEWS={ON/OFF}

In example, to connect to a Teradata database using ANSI session mode and UTF-8 charset the connection string should be built as follows:

jdbc:teradata://ServerHost/database=MyDatabaseName,tmode=ANSI,charset=UTF8

A sample Java code snippet for obtaining a database connection would look as follows:

Class.forName("com.teradata.jdbc.TeraDriver");
String connectionString = "jdbc:teradata://MyDatabaseServer/database=MyDatabaseName,tmode=ANSI,charset=UTF8";
String user = "username";
String passwd = "password";
Connection conn = DriverManager.getConnection(connectionString, user, password);

Please note that to allow the JVM to access the com.teradata.jdbc.TeraDriver class, the driver classes located in terajdbc4.jar and tdgssconfig.jar files (yes, you need both) should be available in the classpath.

In case of any issues please refer to Teradata JDBC FAQ, Teradata JDBC Driver User Guide and online code samples.

Additional reference:

JDBC driver FAQ: http://www.teradata.com/DownloadCenter/Topic9403-98-1.aspx
Teradata JDBC Driver User Guide: http://www.info.teradata.com/eDownload.cfm?itemid=082310004&type=TD
JVM support matrix: http://www.teradata.com/DownloadCenter/Topic9391-98-1.aspx

Teradata SQL Assistant Tips and Tricks

2009-07-13T07:26:00.000-07:00

Teradata SQL Assistant is one of the most commonly used development tools for Teradata database, so just in case you are not familiar with the options and shortcuts listed below that are really basic and greatly improve your daily productivity, please find them below:

Multiple tabs in Teradata SQL Assistant: just press Ctrl-N to open a new query window in a new tab
Query Builder: F2
Format query: Ctrl-Q
Context menu: Shift-F10
Insert current date: Shift-F10, I (like in Insert)
Search and Replace: Ctrl-H
Move the current line UP or DOWN: Alt-Up or Alt-Down
Display line numbers: menu Tools > Options... > tab: Formatting > Display line numbers

Do you use any other shortcuts or options in SQL Assistant on daily basis? You are more than welcome to share them, just drop a comment.

Teradata Express Edition installation on 64-bit Windows

2009-07-12T04:23:00.000-07:00

Teradata Express Edition is a great way to get familiarised with Teradata and develop applications using your plain workstation or laptop.

The main drawback is that the currently available Teradata Express Edition 12.0 supports only 32-bit Windows (the installer will raise an error just after start on 64-bit Windows) and we all have 64-bit machines nowadays, most of them have 64-bit Windows installed with at least 4 GB RAM, so you would have to be crazy to actually downgrade your setup back to 32-bit only to play with Teradata but... this is where the magic of virtualization software come to help.

The solution is easy:

download your favorite virtual machine, ie. VirtualBox or VMware
set up a virtual machine with 32-bit Windows (ie. Windows XP) as a guest system
download Teradata Express Edition 12.0
install Teradata Express on your virtual machine
and... voila, you will have Teradata ready to work on your 64-bit host Windows!

Please also note that:

a virtual image with preinstalled Teradata Express Edition hosted by 32-bit Windows is a convenient way to prepare and spread a single development environment for your development team
it might be convienient to configure the networking in your virtual machine as a bridged networking not a NAT, so your Teradata database will be accessible from other physical machines in your network
you really do not have to worry about performance penalty related to virtualization: Teradata Express Edition is not optimized for performance anyway (and I mean it!), so please use it as a fully functional Teradata version but don't care about the performance figures much

Download Teradata Express Edition 12.0 for free

2009-07-12T03:30:00.000-07:00

Whenever you would like to play with Teradata a bit or set up a development environment for your programmers Teradata Express Edition might be the choice of yours.

The production versions of Teradata are available for:

UNIX SVR4.2 MP-RAS on Teradata (NCR) proprietary hardware (Intel Xeon chips)
Windows Server 2003
Suse Linux Enterprise Server (64-bit Intel)

but if you do not want to pay for those and just install Teradata on your workstation or laptop for evaluation or development you should take a special look at Teradata Express Edition, currently in version 12.0.

The software requirements for the Express Edition are:

Microsoft Windows 2000 Professional with SP4 (32-bit)
Microsoft Windows XP with SP2 (32-bit)
Windows Server 2003 (32-bit)
Microsoft Windows Vista (32-bit)
Microsoft Windows Server 2008 (still... 32-bit)

The 64-bit operating systems are not supported but... you can still run Teradata Express Edition on your 64-bit Windows running a virtual machine like VirtualBox or VMware with 32-bit Windows and Teradata Express Edition on top. This is also recommended even if you all have 32-bit Windows installed as the main advantage of having Teradata preinstalled on a virtual machine image is that you can spread it across your team in a seconds and you will have the same environment ready for development tasks. Please read How-to: Teradata Express Edition on 64-bit Windows as a reference.

The Express Edition is suitable for development and learning, so there are some limitations:

Teradata Express Edition 12.0 is a 2-year license for development, evaluation and training purposes.
Teradata Express Edition 12.0 is not licensed for production use.
Teradata Express Edition 12.0 is restricted to a maximum 4 GB database size whereas the production Teradata 12.0 can scale as large as you like.
There is no upgrade path from the previous demo CD or Express Edition version or from any production version of Teradata Database to Teradata Express Edition.
The Express version uses regular Windows files for the database storage instead of raw disk partitions.
Last but not least: Teradata Express Edition is not optimized for performance (so please do not expect it to be)

But the Express Edition 12 is really a complete functional Teradata edition including a set of additional Teradata tools and utilities:

Teradata Database Express 12.0.0.10
Full Teradata 12.0 feature functionality
Options for installing 256 MB and 4096 MB database size
Teradata Tools and Utilities (TTU) 12.0

Teradata Utility Pak
Teradata ODBC Driver 12.0.0.1
Teradata CLIv2 12.0.0.2
Teradata BTEQ 12.0.0.1
Teradata SQL Assistant 12.0.0.3
Teradata Administrator 12.0.0.2
Teradata Database Management
Teradata Manager 12.0.0.3
Teradata Performance Monitor 12.0
Teradata Query Scheduler 12.0
Teradata Dynamic Workload Manager 12.0.0.2
Teradata Workload Analyzer 12.0.0.1
Teradata Analyst Pak
Teradata Visual Explain 12.0.0.3
Teradata Index Wizard 12.0.0.4
Teradata Statistics Wizard 12.0.0.5
Teradata System Emulation Tool 12.0.0.4
Teradata Parallel Transporter (TPT) 12.0
Load, Export, Update, and Stream Operators
Script Converters
TPT Wizard 12.0.0.1
Teradata Load and Unload Utilities
Teradata TPump 12.0.0.2
Teradata FastLoad 12.0.0.4
Teradata MultiLoad 12.0.0.2
Teradata FastExport 12.0.0.1
Teradata ARC 12.0.0.4
Teradata OLE DB Access Module 12.0
Teradata Query Director 12.0.01
Teradata SQL Assistant/Web Edition 12.0.0.1
Teradata Meta Data Services 12.0.1
Teradata Warehouse Miner 5.1.1

Teradata 12.0 User Documentation
Teradata Database 12.0 User Documentation
Teradata Tools and Utilities 12.0 User Documentation
Teradata Warehouse Miner 5.1 Users Guide

The installation package is available here, you may also want to jump over the registration form right to the Teradata Express Edition 12.0 download link. Complete installation takes about 7.6 GB, download file is nearly 1GB and it requires another 962 MB when unpacked. And in case of problems please refer to Teradata Express Edition 12.0 FAQ for more information.