teradata-database.pptx
TRANSCRIPT
© 2010 Wipro Ltd - Confidential
TeraData Database
© 2009 Wipro Ltd - Confidential2 © 2010 Wipro Ltd - Confidential2 © 2009 Wipro Ltd - Confidential2 © 2010 Wipro Ltd - Confidential2
A data mart (DM) is the access layer of the Dataware House(DW) environment that is used to get data out to the users. The DM is a subset of the DW, usually oriented to a specific business line or team.
Data Marts
© 2009 Wipro Ltd - Confidential3 © 2010 Wipro Ltd - Confidential3 © 2009 Wipro Ltd - Confidential3 © 2010 Wipro Ltd - Confidential3
Data marts are often categorized into three different types:
Independent data marts Dependent data marts: Logical data marts
© 2009 Wipro Ltd - Confidential4 © 2010 Wipro Ltd - Confidential4 © 2009 Wipro Ltd - Confidential4 © 2010 Wipro Ltd - Confidential4
Independent data marts:
Independent data marts are isolated entities, entirely separate from the Enterprise Dataware House.
An independent data mart has neither a relationship with the enterprise data warehouse nor with any other data mart.
These independent data marts are sometimes referred to as data basements.
Teradata strongly discourages their use.
© 2009 Wipro Ltd - Confidential5 © 2010 Wipro Ltd - Confidential5 © 2009 Wipro Ltd - Confidential5 © 2010 Wipro Ltd - Confidential5
Dependent data marts are derived from the enterprise data warehouse.
Dependent data marts can be built in one of two ways:
Where a user can access both the data mart and the complete data warehouse, depending on need, (or)
Where access is limited exclusively to the data mart.
The latter approach is deprecated and the type of data mart it produces is sometimes referred to as a data junkyard
Dependent Data Marts
© 2009 Wipro Ltd - Confidential6 © 2010 Wipro Ltd - Confidential6 © 2009 Wipro Ltd - Confidential6 © 2010 Wipro Ltd - Confidential6
The logical mart is a form of dependent data mart that is constructed virtually from the physical data warehouse.
Data is presented to users of the mart using a series of SQL views that make it appear that a physical data mart underlies the data available for analysis.
Logical Data Marts
© 2009 Wipro Ltd - Confidential7 © 2010 Wipro Ltd - Confidential7 © 2009 Wipro Ltd - Confidential7 © 2010 Wipro Ltd - Confidential7
Facilitates to design multiple user or department specific virtual data marts, that provide the same sort of highly tailored information a physical data mart would without the need for massive data loads, cleansing, and other necessary transformations.
Advantages of Logical Data Marts
© 2009 Wipro Ltd - Confidential8 © 2010 Wipro Ltd - Confidential8 © 2009 Wipro Ltd - Confidential8 © 2010 Wipro Ltd - Confidential8
Pictorial Representation:
DATAWARE HOUSE
OPERATIONAL DATA STORE
INDEPENDENT DATA MART
DEPENDENT DATA MART
DEPENDENT DATA MART
LOGICAL DATA MART
© 2009 Wipro Ltd - Confidential9 © 2010 Wipro Ltd - Confidential9 © 2009 Wipro Ltd - Confidential9 © 2010 Wipro Ltd - Confidential9
Teradata Customer Management provides more relevant conversations with your customers, which lead to more profitable, satisfying and longer-lasting relationships by harnessing a powerful combination of advanced analytics and marketing communication features.
Customer Relationship Management (CRM):
© 2009 Wipro Ltd - Confidential10 © 2010 Wipro Ltd - Confidential10 © 2009 Wipro Ltd - Confidential10 © 2010 Wipro Ltd - Confidential10
Features of CRM:
CRM offers these critical components of marketing effectiveness :
Campaign Management Marketing Resource Management Offer Management Active Analytics Integrated Web Intelligence Interaction Management Communication Management
© 2009 Wipro Ltd - Confidential11 © 2010 Wipro Ltd - Confidential11
Teradata Database
What is Teradata Database ?
• The Teradata Database is a relational database management system (RDBMS) that drives a company's data warehouse.
• The Teradata Database's scalability allows the system to grow as the business grows, from gigabytes to terabytes and beyond.
© 2009 Wipro Ltd - Confidential12 © 2010 Wipro Ltd - Confidential12
TeraData
• The Teradata Database is an open system, compliant with ANSI standards.
• The Teradata Database is a large database server that accommodates multiple client applications making inquiries against it concurrently
• The ability to manage large amounts of data is accomplished using the concept of parallelism.
© 2009 Wipro Ltd - Confidential13 © 2010 Wipro Ltd - Confidential13
TeraData
• TeraData Database is an ideal foundation for many applications like,
• Enterprise Data warehousing
• Active Data warehousing
• CRM
• E-Businesses
• Data Marts
© 2009 Wipro Ltd - Confidential14 © 2010 Wipro Ltd - Confidential14
TeraData
What makes TeraData DB Unique ?
• Single Data Store
• Scalability
• Unconditional Parallelism (Parallel Architecture)
• Ability to Model the business
• Mature, parallel-aware optimizer
© 2009 Wipro Ltd - Confidential15 © 2010 Wipro Ltd - Confidential15
Teradata - Single Data Store
Teradata Database acts as a single data store, with multiple and different type of clients can access the data base concurrently.
Teradata stores the data once and use it for all applications.
Teradata supports the entry level system as it does for massive enterprise data warehouse.
© 2009 Wipro Ltd - Confidential16 © 2010 Wipro Ltd - Confidential16
TeraData - Scalability
Teradata Database can scale from 100 GB to over 100 TB of data on a single system without losing any performance capability.
Teradata DB provides automatic data distribution and no need for reorganizations of data.
Teradata is able to perform large aggregations.
© 2009 Wipro Ltd - Confidential17 © 2010 Wipro Ltd - Confidential17
Teradata – Unconditional Parallelism
Teradata Database more faster than a non-parallel system.
Multi processor work together to process a task quickly.
The Teradata Database's parallelism does not depend on limited data quantity, column range constraints, or specialized data models -- The Teradata Database has "unconditional parallelism."
© 2009 Wipro Ltd - Confidential18 © 2010 Wipro Ltd - Confidential18
Teradata – Ability to model the Business
Teradata Database's logical architecture, companies can get a cohesive view of their operations across various functional areas like Supply chain - from initial manufacturing, to inventory, to sale, to delivery, to maintenance, to customer satisfaction. Relationship - between results of different departments. Service – Various levels of service based on a customer's profitability.
A Teradata Database allows the data to represent a business model, with data organized according to what it represents, not how it is accessed, so it is easy to understand. The data model should be designed without regard to usage and be the same regardless of data volume
© 2009 Wipro Ltd - Confidential19 © 2010 Wipro Ltd - Confidential19
Teradata - Mature, Parallel-Aware Optimizer
The Teradata Database Optimizer is the most robust in the industry, able to handle:
Multiple complex queries Multiple joins per query Unlimited ad-hoc processing
The Optimizer is parallel-aware, meaning that it has knowledge of system components (how many nodes, vprocs, etc.). It determines the least expensive plan (time-wise) to process queries fast and in parallel. The Optimizer is further explained in the next module.
© 2009 Wipro Ltd - Confidential20 © 2010 Wipro Ltd - Confidential20
Teradata - Architecture
Figure shows the typical architecture of the Teradata database.
Main Components of Teradata Database are,1] Parsing Engine (PE)2] Banyan Network (BYNET)3] Access Module Process (AMP)4] Disk (Storage)
© 2009 Wipro Ltd - Confidential21 © 2010 Wipro Ltd - Confidential21
Teradata – Architecture – Parsing Engine
PE logically consists of four components 1] Session Control2] Parser3] Optimizer4] Dispatcher
Parsing Engine (PE) is the virtual processor that communicates with the client on one side and with the AMPs (Via BYNET) on the other side.
Session Control - Provides user session management suchas establishing and terminating sessions
© 2009 Wipro Ltd - Confidential22 © 2010 Wipro Ltd - Confidential22
Teradata – Architecture – Parsing Engine
Parser - Interprets the SQL statement received from the application. Verifies SQL requests for the proper syntax and evaluates them semantically. Consults the Data Dictionary to ensure that all objects exist and that the user has authority to access them.Optimizer - develops the least expensive plan, fastest among the alternatives is chosen. Having knowledge of the system components.
Dispatcher - controls the sequence in which the steps are executed and passes the steps received from the optimizer onto the BYNET for execution by the AMPs
© 2009 Wipro Ltd - Confidential23 © 2010 Wipro Ltd - Confidential23
Teradata – Architecture – BYNET
Banyan Network (BYNET) -
BYNET is a bus that loosely couples all the Symmetric Multi Processing (SMP) nodes into a multi node system.
BYNET possess high speed logic arrays that provide bidirectional broadcast.
Normally two BYNET will be provided for Multi node system.When BYNET traffic becomes particularly heavy, the two BYNETs can handle separate (rather than redundant) traffic using Loading – balancing software to optimize the process.
© 2009 Wipro Ltd - Confidential24 © 2010 Wipro Ltd - Confidential24
Teradata – Architecture – AMP
Access Module Process(AMP) is the heart of the Teradata RDBMS. AMP is a virtual processor that provides a BYNET interface and performs many database and file management tasks.
AMPs do work associated with generating an output including sorting, aggregating, formatting and converting.
Each AMP being assigned to a virtual disk and consists of a DB manager subsystem. DB manager subsystem manages the query operations like • Lock databases and tables. • Create, modify, or delete definitions of tables. • Insert, delete, or modify rows within the tables. • Retrieve information from definitions and tables. • Return responses to the Dispatcher.
© 2009 Wipro Ltd - Confidential25 © 2010 Wipro Ltd - Confidential25
Teradata – Architecture - Disk
Disk Arrays –
A disk array is a matrix of independent but interconnected physical disk storage units.
For the Teradata RDBMS, the disks are organized as a Redundant Array of Independent Disks (RAID).
RAID consists of 5 types, among these five types one technology will be chosen by RAID manager.
Each array typically consists of from one to four ranks of disks, with up to five disks per rank.
© 2009 Wipro Ltd - Confidential26 © 2010 Wipro Ltd - Confidential26
Teradata – Architecture - Node
Processor Node – A hardware assembly containing several, tightly coupled central processing units (CPUs).
Note : An MPP is a configuration of two or more loosely coupled SMP nodes with shared SCSI access to multiple disk arrays.
Comprises an SMP configuration with a single processor node connected to one or more disk arrays with the following installed on the node: Database software, Client interface software, Operating system, Multiprocessor shared memory, processors and Failsafe power provisions
© 2009 Wipro Ltd - Confidential27 © 2010 Wipro Ltd - Confidential27
Teradata
Cliques – The clique is a feature of multinode systems that physically groups nodes together by multi ported access to common disk array units. Figure illustrates the four node Clique
© 2009 Wipro Ltd - Confidential28 © 2010 Wipro Ltd - Confidential28 © 2009 Wipro Ltd - Confidential28 © 2010 Wipro Ltd - Confidential28
A journal is a record of some kind of activity. The Teradata Database supports several kinds of journaling:
Down AMP recovery Transient Journel Permanent Journel
Journaling:DATA PROTECTION
© 2009 Wipro Ltd - Confidential29 © 2010 Wipro Ltd - Confidential29 © 2009 Wipro Ltd - Confidential29 © 2010 Wipro Ltd - Confidential29
Is active during an AMP failure only
Journals fallback tables only
Is used to recover the AMP after the AMP is repaired, then is discardedThis occurs always.
Down AMP Recovery Journel:
© 2009 Wipro Ltd - Confidential30 © 2010 Wipro Ltd - Confidential30 © 2009 Wipro Ltd - Confidential30 © 2010 Wipro Ltd - Confidential30
Transient Journel:
Logs BEFORE images for transactions Is used by system to roll back failed transactions aborted either by the user or by the system Captures: Begin/End Transaction indicators "Before" row images for UPDATE and DELETE statements Row IDs for INSERT statements Control records for CREATE, DROP, DELETE, and ALTER statements Keeps each image on the same AMP as the row it describes Discards images when the transaction or rollback Completes Occurs always
© 2009 Wipro Ltd - Confidential31 © 2010 Wipro Ltd - Confidential31 © 2009 Wipro Ltd - Confidential31 © 2010 Wipro Ltd - Confidential31
Permanent Journel:
Is available for tables or databases Can contain "before" images, which permit rollback, or after images, which permit roll-forward, or both before and after images Provides roll-forward recovery Provides rollback recovery Provides full recovery of non-fallback tables Reduces need for frequent, full-table archives Occurs as specified by the user.
© 2009 Wipro Ltd - Confidential32 © 2010 Wipro Ltd - Confidential32 © 2009 Wipro Ltd - Confidential32 © 2010 Wipro Ltd - Confidential32
“Redundant Array of Independent Disks (RAID)”
It protects against a disk failure. Non-array storage cannot use RAID technology.
Common levels of RAID in the data storage industry are: RAID-1 RAID-5
RAID
© 2009 Wipro Ltd - Confidential33 © 2010 Wipro Ltd - Confidential33 © 2009 Wipro Ltd - Confidential33 © 2010 Wipro Ltd - Confidential33
RAID-1:
RAID-1 is the most common level.
Also called MIRRORING.
With RAID-1, each primary disk has a mirror image, or an exact copy of all its data on another disk. The contents of both disks are identical.
© 2009 Wipro Ltd - Confidential34 © 2010 Wipro Ltd - Confidential34 © 2009 Wipro Ltd - Confidential34 © 2010 Wipro Ltd - Confidential34
RAID-5:
RAID 5 protection protects data from being lost because of a disk unit failure or because of damage to a disk. RAID 5 protection protects against a one disk unit failure.
RAID5 protects data from single-disk failures with a 25 percent increase in disk storage to provide parity.
© 2009 Wipro Ltd - Confidential35 © 2010 Wipro Ltd - Confidential35 © 2009 Wipro Ltd - Confidential35 © 2010 Wipro Ltd - Confidential35
Disadvantages of RAID:
RAID1 provides better performance and data protection than RAID5, but is more expensive.
Even though RAID disk array technology may provide access to data even when you have not specified fallback,
neither RAID-1 nor RAID-5 provides the same level of protection as fallback does.
Backups need to be done with arcmain for best data-protection.
© 2009 Wipro Ltd - Confidential36 © 2010 Wipro Ltd - Confidential36 © 2009 Wipro Ltd - Confidential36 © 2010 Wipro Ltd - Confidential36
Fallback:
The ability of the Teradata RDBMS to maintain an extra copy of every row of a table in different AMPs.
During normal processing, reads are performed using only the primary copy, while updates are performed on both copies.
If a failure renders the primary copy inaccessible, the fallback copy is used for reads and updates.
The database maintains a log of changes missed byinaccessible copies and automatically applies the
missed changes to synchronize the table copy when it becomes accessible.
© 2009 Wipro Ltd - Confidential37 © 2010 Wipro Ltd - Confidential37 © 2009 Wipro Ltd - Confidential37 © 2010 Wipro Ltd - Confidential37
Costs and Benefits of Fallback:
The costs of using Fallback include requiring twice the disk space for storage, and twice the I/O for inserts, updates, and deletes (all fallback I/Os are performed in parallel to the primary I/O).
The benefits of Fallback are protecting your data from hardware failure (VPROC or DSU), protecting your data from software failure, and automatic recovery after repair is complete, minimum recovery time after repairs are complete, and requires no extra I/O for the SELECT SQL function.
© 2009 Wipro Ltd - Confidential38 © 2010 Wipro Ltd - Confidential38
Channel Attached Client
• Channel attached system is accessing the Teradata through mainframe
• Special hardware is required to access the system,
that why it is called channel attached system,
• Traffic and communications between client applications on the mainframe and the Teradata database are managed by the Teradata Director Program (TDP) software.
© 2009 Wipro Ltd - Confidential39 © 2010 Wipro Ltd - Confidential39
Network Attached Client
• In Network attached system Teradata is accessible from Unix/windows OS, through simple LAN network.
• Traffic and communication between network-attached client applications and the Teradata database are managed by either: ODBC or CLIv2.
© 2009 Wipro Ltd - Confidential40 © 2010 Wipro Ltd - Confidential40
Channel Attached Client and N/w Attached
© 2009 Wipro Ltd - Confidential41 © 2010 Wipro Ltd - Confidential41
SMP – Symmetric Multi processing
• Stands for symmetric multi processing.
• A computing where many CPUs are available for individual processes simultaneously.
• CPU attempts to access the memory at once, so there is hold up.
• Does suffer from bottleneck when all CPUs attempt to access memory once.
• Easier to program.
• Smp supports limited parallelism i.e 64 processors .
© 2009 Wipro Ltd - Confidential42 © 2010 Wipro Ltd - Confidential42
MPP – Massive Parallel Processing
Stands for Massive parallel processing.
A computing that uses many CPUs in parallel to execute a single program.
CPU has its own memory which prevents hold up.
Does not suffer from bottleneck when all CPUs attempt to access memory once.
Difficult to program as applications must be divided to communicate with each other.
MPP can support N number of nodes or processors [high performance].
© 2009 Wipro Ltd - Confidential43 © 2010 Wipro Ltd - Confidential43
Teradata – Components
1. BTEQ2. CLI3. Fast Load4. Multi Load5. Fast Export6. Tpump7. Teradata Parallel Transporter (TPT)8. Teradata Manager
© 2009 Wipro Ltd - Confidential44 © 2010 Wipro Ltd - Confidential44
Teradata – BTEQ
• BTEQ – Basic Teradata Query
– Is a front-end utility for submitting SQL.
– Operates under all host systems and local area networks (LANs).
– In batch mode, it obtains input from a host file (a "BTEQ script").
– Can be used in interactive mode*
– Has import/export features*
– Has report writing features*
.
© 2009 Wipro Ltd - Confidential45 © 2010 Wipro Ltd - Confidential45 © 2009 Wipro Ltd - Confidential45 © 2010 Wipro Ltd - Confidential45
Logon
Starts a BTEQ Session. Every user, application, or utility must
LOGON to Teradata tosession. establish a
Syntax :
BTEQ .logon LOCALTD/<Database Name>,<Password>
Example :
BTEQ .logon LOCALTD/DBC,DBC
Logon, logoff and exit
© 2009 Wipro Ltd - Confidential46 © 2010 Wipro Ltd - Confidential46 © 2009 Wipro Ltd - Confidential46 © 2010 Wipro Ltd - Confidential46
Log off
The LOGOFF command ends the current RDBMS sessions without exiting BTEQ. If
you execute a LOGOFF command while a transaction is in progress, BTEQ aborts
the transaction and backs out any changes that had been made to the database.
Syntax :.logoff
Exit
Immediately end the current session or sessions and exit BTEQ.
Syntax :
.exit
© 2009 Wipro Ltd - Confidential47 © 2010 Wipro Ltd - Confidential47 © 2009 Wipro Ltd - Confidential47 © 2010 Wipro Ltd - Confidential47
Prepare scripts or macros, and then submit them to BTEQ for processing.
Batch mode :
BTEQ operates in both batch and interactive modes, as follows:
Start a BTEQ session, and submit commands to the database as needed.
Interactive mode :
How to run a batch file
© 2009 Wipro Ltd - Confidential48 © 2010 Wipro Ltd - Confidential48 © 2009 Wipro Ltd - Confidential48 © 2010 Wipro Ltd - Confidential48
To run a batch file
Run Command Used to Execute Teradata SQL requests and
BTEQ commands from a specified run file, Prepare scripts or macros in
txt file and save Some ware in Hard Disk
Syntax :
.run file = <file path>
© 2009 Wipro Ltd - Confidential49 © 2010 Wipro Ltd - Confidential49 © 2009 Wipro Ltd - Confidential49 © 2010 Wipro Ltd - Confidential49
In Teradata user itself is database. As a user you will have access to your own user database and its objects.
The user name you used to logon with is usually your default database.
For Example if you log on as:
.logon user01 Pswd abc, in BTEQ command
The user01 will be your Default Database.
Changing Database
© 2009 Wipro Ltd - Confidential50 © 2010 Wipro Ltd - Confidential50 © 2009 Wipro Ltd - Confidential50 © 2010 Wipro Ltd - Confidential50
The DATABASE command used to change your default database.
database database name;
To check whether the database has changed or not, we will use
help database database name;
© 2009 Wipro Ltd - Confidential51 © 2010 Wipro Ltd - Confidential51 © 2009 Wipro Ltd - Confidential51 © 2010 Wipro Ltd - Confidential51
Teradata SQL Assistant
Teradata SQL Assistant
Data Source
Main Window
Windows
Teradata SQL Assistant is an information discovery tool designed for
Windows XP and Windows 2000. Teradata SQL Assistant retrieves data
from any ODBC-compliant database server and allows you to manipulate
and store the data on your desktop PC.
Teradata SQL Assistant
© 2009 Wipro Ltd - Confidential52 © 2010 Wipro Ltd - Confidential52 © 2009 Wipro Ltd - Confidential52 © 2010 Wipro Ltd - Confidential52
Teradata SQL Assistant
Key features of Teradata SQL Assistant
Teradata SQL Assistant Test Database
Teradata SQL Assistant
Teradata SQL Assistant enables you to combine the data retrieved from
ODBC databases with desktop applications such as Excel to create consolidated
reports, or to analyze the merged data.
Teradata SQL Assistant records all SQL activity, complete with source
identification, timings, row counts and your notes. This is especially useful for data
mining because you can use the historical record to build scripts from the SQL that
produced positive results.
© 2009 Wipro Ltd - Confidential53 © 2010 Wipro Ltd - Confidential53 © 2009 Wipro Ltd - Confidential53 © 2010 Wipro Ltd - Confidential53
Key features of Teradata SQL Assistant
• Create reports from any RDBMS that provides an ODBC interface
• Export data from the database to a file on a PC
• Import data from a PC file directly to the database
• Use an import file to create many similar reports (query results or Answer sets).
• Send queries to any ODBC database or the same query to many different databases
• Create a historical record of the submitted SQL with timings and status information such
as success or failure
• Use SQL syntax examples that enable you to tailor statements to best fit your needs
• Use the Database Explorer Tree to easily view database objects
• Use a procedure builder that gives you a list of valid statements for building the logic of a
stored procedure
• Limit data returned to prevent runaway queries
© 2009 Wipro Ltd - Confidential54 © 2010 Wipro Ltd - Confidential54 © 2009 Wipro Ltd - Confidential54 © 2010 Wipro Ltd - Confidential54
During the installation process, the options screen gives you a choice to
install a test database (Microsoft Access) on your PC. To use this test
database, you must:
Teradata SQL Assistant Test Database
Have approximately 200KB of additional disk space
Install the ODBC desktop driver for Microsoft Access
Use the ODBC Administrator program to define a data
source
© 2009 Wipro Ltd - Confidential55 © 2010 Wipro Ltd - Confidential55 © 2009 Wipro Ltd - Confidential55 © 2010 Wipro Ltd - Confidential55
ODBC Driver
Starting Teradata SQL Assistant
Connecting to and Disconnecting from a Data Source
Changing the Database Password
Data Source
ODBC Driver
Before using Teradata SQL Assistant to access the data in your RDBMS, you
must first install an ODBC driver.
Each RDBMS requires a driver that is designed specifically for that database
system.
© 2009 Wipro Ltd - Confidential56 © 2010 Wipro Ltd - Confidential56 © 2009 Wipro Ltd - Confidential56 © 2010 Wipro Ltd - Confidential56
Starting Teradata SQL Assistant
After you have install, start Teradata SQL Assistant before you define an ODBC
data source.
Connecting to a Data Source
Select Tools > Connect. Select a data source and click OK
In the Teradata Database Connect dialog box:
Do one of the following:
• Select Use Integrated Security
• Enter the Mechanism and Parameter
• Enter your Userid and Password.
Optionally, enter a Default Database or an Account String.
Click OK.
© 2009 Wipro Ltd - Confidential57 © 2010 Wipro Ltd - Confidential57 © 2009 Wipro Ltd - Confidential57 © 2010 Wipro Ltd - Confidential57
Disconnecting from a Data Source
Select Tools > Disconnect
The status bar, appearing across the bottom of the main window, confirms the
disconnect:
Disconnect successful.
Changing the Database Password
Select Tools > Change Password.
In the Change Database Password dialog box, enter your current password.
Enter your new password (each character you type is displayed as an asterisk).
Re-enter the same new password to confirm it.
Click OK.
© 2009 Wipro Ltd - Confidential58 © 2010 Wipro Ltd - Confidential58 © 2009 Wipro Ltd - Confidential58 © 2010 Wipro Ltd - Confidential58
The Teradata SQL Assistant main window appears with a blank Query
window on the top and a History window on the bottom.
Main Window
© 2009 Wipro Ltd - Confidential59 © 2010 Wipro Ltd - Confidential59 © 2009 Wipro Ltd - Confidential59 © 2010 Wipro Ltd - Confidential59
Do one of the following:
From the Query window, click the right mouse button.
Press Shift-F10.
Press Context Menu on your keyboard.
To display the Query Window Shortcut Menu
Query Window
The Query window is where you enter and execute a query. The results from your
query are placed into one or more Answerset windows. Alternatively, the results
may be written directly to a file.
Windows
© 2009 Wipro Ltd - Confidential60 © 2010 Wipro Ltd - Confidential60 © 2009 Wipro Ltd - Confidential60 © 2010 Wipro Ltd - Confidential60
The Answerset window is a table that displays the results from a statement. You can
sort the output in a number of ways and print as bitmaps in spreadsheet format.
Individual cells, rows, columns, or blocks of columns may be formatted to change the
background and foreground color as well as the font style, name, and size. You can
make other modifications such as displaying or hiding gridlines and column headers.
Answerset window
Do one of the following:
• From any table inside the Answerset window, click the right mouse button.
Press Shift+F10
To display the Answerset Window Shortcut Menu
© 2009 Wipro Ltd - Confidential61 © 2010 Wipro Ltd - Confidential61 © 2009 Wipro Ltd - Confidential61 © 2010 Wipro Ltd - Confidential61
Do one of the following:
Click the right mouse button anywhere within the History window,
Press Shift-F10 or the Context Menu Key
To display the History Window Shortcut Menu
The History window is a table that displays your past queries and related processing
attributes. The past queries and processing attributes are stored locally in a Microsoft
Access 95 or 2000 database. This allows the flexibility to work with previous SQL
statements in the future.
History window
© 2009 Wipro Ltd - Confidential62 © 2010 Wipro Ltd - Confidential62
Teradata – CLI
Teradata Call-Level Interface is a collection of callable service routines that provide the interface between applications and the Teradata Gateway. Gateway is the interface between CLI and the Teradata Database
© 2009 Wipro Ltd - Confidential63 © 2010 Wipro Ltd - Confidential63
Teradata – Fast Load
Teradata Fastload is a command-driven utility you can use to quickly load large amounts of data in an empty table on a Teradata Database.
Data is loaded from
• Disk or tape files on a channel-attached client system• Input files on a network-attached workstation• Special input module (INMOD) routines you write to select,
validate, and Preprocessinput data• Any other device providing properly formatted source data• Can be run either through batch mode or Interactive mode
© 2009 Wipro Ltd - Confidential64 © 2010 Wipro Ltd - Confidential64
Teradata – Fast Load
What it Does ?
• Log you on to the Teradata Database for a specified number of sessions, using your username, password, and tdpid/acctid information
• Load the input data into the Teradata Fastload table on the Teradata Database.
• Log you off from the Teradata Database.• If the load operation was successful, return the following
information about the Teradata Fastload operation and then terminate:
• Total number of records read, skipped, and sent to the Teradata Database
• Number of errors posted to the Teradata Fastload error tables• Number of inserts applied• Number of duplicate rows
© 2009 Wipro Ltd - Confidential65 © 2010 Wipro Ltd - Confidential65
Teradata – Fast Load
Limitations
Since FastLoad utility works only on empty tables. You can not use the FastLoad on the below secnarios
· Insert additional data rows into an existing table· Update individual rows of existing tables· Delete individual rows from existing tables· Load data into multiple tables
© 2009 Wipro Ltd - Confidential66 © 2010 Wipro Ltd - Confidential66
Teradata – Multi Load
MultiLoad is a command-driven utility for fast, high-volume maintenance on multiple tables and views of a Teradata Database
A single MultiLoad job performs a number of different import and delete tasks on databasetables and views
• Each MultiLoad import task can do multiple data insert, update, and delete functions on up to five different tables or views.
• Each MultiLoad delete task can remove large numbers of rows from a single table.
• Supports up to 5 tables
© 2009 Wipro Ltd - Confidential67 © 2010 Wipro Ltd - Confidential67
Teradata – Multi Load
What it Does ? -- It performs loading in 5 phases
© 2009 Wipro Ltd - Confidential68 © 2010 Wipro Ltd - Confidential68
Teradata – Multi Load
Mlscript (2).txt
Limitations
MultiLoad doesn’t support the following conditions.
• Concatenation of data files• Aggregate operators,exponential operators,arithmetic
functions• Data retrieval from the Teradata Database with the
SELECT statements
• Foreign key references• Unique secondary indexes• Maximum number of columns is limited to 2048
© 2009 Wipro Ltd - Confidential69 © 2010 Wipro Ltd - Confidential69
Teradata – Tpump
Tpump “Continuous Data Loading”
Teradata TPump is a highly parallel utility designed to continuously move data from data sources into Teradata tables without locking the affected table
TPump provides near-real-time data into your data warehouse,allowing you to maintain fresh,accurate data for up-to-the-moment decision making
Tpump is used to insert, update, upsert, and delete datain the Teradata Database, particularly forenvironments where batch windows are shrinking and warehouse maintenanceoverlaps normal working hours
© 2009 Wipro Ltd - Confidential70 © 2010 Wipro Ltd - Confidential70
Teradata Database – Fast Export
FastExport is a command-driven utility that uses multiple sessions to quickly transfer large amounts of data from tables and views of the Teradata Database to a client-based application
When FastExport is invoked, the utility executes the FastExport commands and Teradata SQLstatements in the FastExport job script. These direct FastExport to
. Log on to the Teradata Database for a specified number of sessions, using username,password, and tdpid/acctid information . Retrieve the specified data from the Teradata Database, in accordance with format andselection specifications.Export the data to the specified file or OUTMOD routine on a client system.Log off the Teradata Database
© 2009 Wipro Ltd - Confidential71 © 2010 Wipro Ltd - Confidential71
Teradata – Teradata Parallel Transporter (TPT)
Teradata Parallel Transporter(TPT) is a load / update / export tool that enables data extraction, transformation, and loading processes common to all data ware houses.
TPT combines the functionality of the Teradata utilities (Fast Load, Multi Load, Fast Export and Tpump) in a single parallel environment.
There is a set of open APIs (Application Programmer Interface) to add third party or custom data transformation to Teradata Parallel Transporter scripts.
Using multiple, parallel tasks, a single TPT script can load data from disparate sources into the Teradata DB in the same job.
© 2009 Wipro Ltd - Confidential72 © 2010 Wipro Ltd - Confidential72
Teradata – Teradata Parallel Transporter (TPT)
A single Teradata Parallel Transporter job can load data from multiple disparate sources into the Teradata Database, as indicated by the green arrow.
© 2009 Wipro Ltd - Confidential73 © 2010 Wipro Ltd - Confidential73
Teradata – Teradata Manager
Teradata Corporation offers Teradata Manager as the command center for monitoring the Teradata Database and visualizing real-time performance and historical trends
Teradata Manager, which operates from a single graphical user interface, collects, analyzes, and displays workload performance and database utilization information in either report or graphicformat without impacting database users
Some Key features of Teradata Manager• Performance Analysis andProblem Resolution• Workload Management• Database Management• Operational Control
© 2009 Wipro Ltd - Confidential74 © 2010 Wipro Ltd - Confidential74
Access Considerations
• When choosing the primary index for a table, there are two essential factors to keep in mind:
uniform distribution of the data optimal access
With respect to optimal data access, consider the following factors: Choose the primary index on the most frequently used access path.
© 2009 Wipro Ltd - Confidential75 © 2010 Wipro Ltd - Confidential75
For example: If rows are generally accessed by a range query, consider defining a PPI on
the table that creates a useful set of partitions. If the table is frequently joined with a specific set of tables, consider
defining the primary index on the column set that is typically used as the join condition.
Primary index operations must provide the full primary index value. Primary index retrievals on a single value are always one-AMP operations
Access Considerations
© 2009 Wipro Ltd - Confidential76 © 2010 Wipro Ltd - Confidential76
Additional Indexes
• Teradata Database provides four different index types:
Primary index
Secondary index
Join index (JI)
Hash index
© 2009 Wipro Ltd - Confidential77 © 2010 Wipro Ltd - Confidential77
Primary indexes
•All Teradata Database tables require a primary index because the system distributes tables on their primary indexes.
•It helps for the uniform data distribution
•Primary indexes can be:
Unique or Non-unique
Partitioned or Non-partitioned
© 2009 Wipro Ltd - Confidential78 © 2010 Wipro Ltd - Confidential78
Secondary index
It helps for the efficient data access
Secondary indexes can be
Unique or Non-unique.
© 2009 Wipro Ltd - Confidential79 © 2010 Wipro Ltd - Confidential79
Unique index
Unique index
A unique index, has a unique value for each row in a table. Teradata Database defines two different types of unique index.
• Unique primary index (UPI) UPIs provide optimal data distribution and are typically assigned to the
primary key for a table.
• Unique secondary index (USI) USIs guarantee that each complete index value is unique, while ensuring
that data access based on it is always a two-AMP operation
© 2009 Wipro Ltd - Confidential80 © 2010 Wipro Ltd - Confidential80
Non-Unique index
Non-Unique index:
A non-unique index does not require its values to be unique
© 2009 Wipro Ltd - Confidential81 © 2010 Wipro Ltd - Confidential81
Partitioned and Non-partitioned Primary Indexes
Partitioned and Non-partitioned Primary Indexes
Non-partitioned primary index (NPPI)
• This is the traditional primary index by which rows are assigned to AMPs.
© 2009 Wipro Ltd - Confidential82 © 2010 Wipro Ltd - Confidential82
Partitioned Primary index
Partitioned primary index (PPI)
It allows rows to be partitioned, based on some set of columns, on the AMP to which they are distributed, and ordered by the hash of the primary index columns within the partition
APPI can improve query performance through partition elimination. A PPI provides a useful alternative to an NPPI for executing range queries
against a table, while still providing efficient access, join, and aggregation strategies on the primary index
© 2009 Wipro Ltd - Confidential83 © 2010 Wipro Ltd - Confidential83
Join Indexes
Join Indexes
A join index is an indexing structure containing columns from one or more
base tables and is generally used to resolve queries and eliminate the need to
access and join the base tables it represents.
© 2009 Wipro Ltd - Confidential84 © 2010 Wipro Ltd - Confidential84
Hash Indexes
• Hash indexes are used for the same purposes as are single-table join indexes.
© 2009 Wipro Ltd - Confidential85 © 2010 Wipro Ltd - Confidential85
Teradata SQL Extensions & Features
Table Creation
Use of column aliases
Select from a dummy table
Qualify
Delete Command
Additional Features
© 2009 Wipro Ltd - Confidential86 © 2010 Wipro Ltd - Confidential86
• Qualify
Teradata offers a new SQL clause "qualify“.
Oracle doesn't have the Qualify clause altogether. However, Oracle's ability to
do nested query comes to rescue
• Select from a Dummy table
Sometimes when you are working with SQL you may need an extra row or
two. Oracle has its infamous DUAL table that could be used for that:
Teradata exhibits similar behavior without table name
© 2009 Wipro Ltd - Confidential87 © 2010 Wipro Ltd - Confidential87
Table Creation
When creating tables. we have format and default clauses specified in this
table definition. DEFAULT is a standard clause and does what you would
expect. The FORMAT only works when one uses Teradata BTEQ tool as
opposed to a Teradata SQL Assistant.
Use of column aliases
Teradata allows use of column aliases in WHERE conditions
Whereas in Oracle doesn’t permit the use of aliases in where condition
© 2009 Wipro Ltd - Confidential88 © 2010 Wipro Ltd - Confidential88
Delete Command
In Oracle, Syntax for Deleting Table
DELETE from table_name;
Teradata, Syntax for Deleting Table
DELETE from table_name ALL;
© 2009 Wipro Ltd - Confidential89 © 2010 Wipro Ltd - Confidential89
Additional Features
Additional Features:
SHOW Command :
Gives definition(DDL) of the database object.
HELP Command :
Gives the contents of the database object.
MACRO : Package of pre-written, frequently run SQL statements. Helpful for reusable queries
© 2009 Wipro Ltd - Confidential90 © 2010 Wipro Ltd - Confidential90
Macros
• It is a package used to execute SQL statements.• Encapsulation of Mulitple SQL statements .• Pre-written SQL statements are there in it.• Actual text of Macros is stored in a Global Repository called
Data Dictionary.• Macros are Database objects and thus they belong to
specific users or database.• Frequently Run SQL statements
© 2009 Wipro Ltd - Confidential91 © 2010 Wipro Ltd - Confidential91
Commands Related To Macro
• CREATE Macro• EXEC Macro• SHOW Macro• REPLACE Macro• DROP Macro• EXPLAIN Macro
© 2009 Wipro Ltd - Confidential92 © 2010 Wipro Ltd - Confidential92 © 2009 Wipro Ltd - Confidential92 © 2010 Wipro Ltd - Confidential92
CREATE MACRO NewEmp (name VARCHAR(12),number INTEGER NOT NULL,dept INTEGER DEFAULT 100)AS (INSERT INTO Employee (Name,EmpNo,DeptNo)VALUES (:name,:number,:dept);
Create a Macro:
© 2009 Wipro Ltd - Confidential93 © 2010 Wipro Ltd - Confidential93 © 2009 Wipro Ltd - Confidential93 © 2010 Wipro Ltd - Confidential93
Execute a Macro:
You can use Execute command to Insert data into table.
EXECUTE Macro name(‘Goldsmith H’, 10015, 600);
Delete a Macro:
DROP MACRO Macro name;
© 2009 Wipro Ltd - Confidential94 © 2010 Wipro Ltd - Confidential94
Parameterized Macro
• Parameterized Macros allow substituteable variables supplied at runtime.
• Example: Create Macro Dept_list(dept int) AS
(Select last_name from employee where department_number=:dept);
Exec Dept_list(301);
© 2009 Wipro Ltd - Confidential95 © 2010 Wipro Ltd - Confidential95
• Macros may have more than One Parameters.
• Example Create Macro emp_check(dept integer, sal_amount
dec(9,2) AS (select emp_number from employee where depart_number=:dept and sal_amount <:sal_amount);
Exec emp_check(201,5000);
© 2009 Wipro Ltd - Confidential96 © 2010 Wipro Ltd - Confidential96 © 2009 Wipro Ltd - Confidential96 © 2010 Wipro Ltd - Confidential96
Teradata provides one of the user friendly featured like HELP commands.
Teradata HELP command
HELP SESSION: This command is used to display the user name, account name,
logon date and time, current database name, collation code set and character set being used and also , transaction semantics, time zone and character set data.
HELP DATABASE <database-name> ;This command is used to display the names of all the tables (T), views
(V), macros (M), and triggers (G) stored in a database and table comments
© 2009 Wipro Ltd - Confidential97 © 2010 Wipro Ltd - Confidential97 © 2009 Wipro Ltd - Confidential97 © 2010 Wipro Ltd - Confidential97
HELP TABLE <table-name>; This command is used to display the column names,
type identifier, and any user written comments on the columns within a table.
HELP VIEW <view-name> ; This command is used to display the column names, type
identifier, and comments on the columns within a VIEW
HELP MACRO <macro-name> ; This command is used to display the characteristics of
parameters passed to it at execution time.
HELP INDEX <table-name> ; This command is used to display the indexes and their
characteristics like unique or non-unique and the column or columns involved in the index. This information is used by the Optimizer to create a plan for SQL.
© 2009 Wipro Ltd - Confidential98 © 2010 Wipro Ltd - Confidential98 © 2009 Wipro Ltd - Confidential98 © 2010 Wipro Ltd - Confidential98
HELP STATISTICS <table-name> ; This command is used to display values associated with the data demographics collected on
the table. This information is used by the Optimizer to create a plan for SQL.
HELP CONSTRAINT <table-name>.<constraint-name> ; This command is used to display the checks to be made on the data when it is inserted or
updated and the columns are involved.
HELP 'SQL'; This command is used to display a list of all available SQL commands and functions.
HELP 'SQL <command>'; This command is used to display the basic syntax and options for the SQL command used in
place of the <command>.
© 2009 Wipro Ltd - Confidential99 © 2010 Wipro Ltd - Confidential99 © 2009 Wipro Ltd - Confidential99 © 2010 Wipro Ltd - Confidential99
HELP 'SPL'; This command is used to display a list of available SPL
commands.
HELP 'SPL <command>'; This command is used to display the basic syntax and options for
the SPL command used in place of the <command>.
© 2009 Wipro Ltd - Confidential100 © 2010 Wipro Ltd - Confidential100 © 2009 Wipro Ltd - Confidential100 © 2010 Wipro Ltd - Confidential100
Teradata SHOW command
SHOW CONTROLS:
Purpose Returns the current settings of the formatting command options. Displays all of the BTEQ control command options currently configured.
Uses Use the SHOW CONTROLS command to verify the BTEQ format
settings that users control. The command SHOW, used without any options, displays the last SQL
statement. You can use the SHOW CONTROLS command in a Teradata SQL macro. Optionally, the SHOW CONTROLS command can be used at the settings
level. SHOW CONTROLS command displays the output at the settings level.
© 2009 Wipro Ltd - Confidential101 © 2010 Wipro Ltd - Confidential101 © 2009 Wipro Ltd - Confidential101 © 2010 Wipro Ltd - Confidential101
SHOW ERRORMAP
PurposeDisplays the contents of the tables that map error codes to severity levels.
Uses
We can use SHOW ERRORMAP command when you want to display the return code associated with a specific error.
SHOW VERSIONS
Purpose
Returns the current level of each BTEQ software
Uses We can use the SHOW VERSIONS command to determine which version of BTEQ is running. We can use the SHOW VERSIONS command in a Teradata SQL macro..
© 2009 Wipro Ltd - Confidential102 © 2010 Wipro Ltd - Confidential102
Fastload Commands - Sleep
Purpose• The SLEEP command specifies the number of minutes that
Teradata Fastload pauses before retrying a logon operation when the maximum number of load operations is already running on the Teradata Database.
• Syntax: SLEEP minutes Syntax Element Description:• minutes Number of minutes that Teradata Fastload pauses
before retrying the logon operation.• The minutes specification must be greater than zero. If you
enter zero, Teradata Fastload responds with an error message, and terminates.
• The Teradata Fastload default, if you do not use the SLEEP command, is 6 minutes.
© 2009 Wipro Ltd - Confidential103 © 2010 Wipro Ltd - Confidential103
Tenacity
• Tenacity: Purpose• The TENACITY command specifies the number of hours that
Teradata Fastload continues trying to log on when the maximum number of load operations is already running on the Teradata Database.
Syntax: TENACITY hours• Syntax Element hours: Number of hours that Teradata
Fastload continues trying to log on.• The hours specification must be greater than zero. If you
enter zero, Teradata• Fastload responds with an error message and terminates.
© 2009 Wipro Ltd - Confidential104 © 2010 Wipro Ltd - Confidential104
Checkpoints
• Checkpoints The CHECKPOINT option defines points in a job where Teradata Fastload pauses to record that the Teradata Database has processed a specified number of input records. When you use checkpoints, you do not have to rerun the entire Teradata Fastload job if it stops before completion.
• Teradata Fastload uses the checkpoint information in the restart log table to determine the restart location.
• When specifying the integer value for the CHECKPOINT option:
If you enter zero as the value, Teradata Fastload processes the BEGINLOADING command as if you did not enter a CHECKPOINT value.If you do not enter a value, or if you enter a value that is not an integer, the Teradata Database returns a syntax error.
© 2009 Wipro Ltd - Confidential105 © 2010 Wipro Ltd - Confidential105
Optimizer & Collecting Statistics
Optimizers are used for choosing the Optimal Executional Plan which consumes less resources.
Optimizer is helpful in case of: Multiple complex queries. Multiple joins per query. Unlimited ad-hoc processing.
Two types of Optimizers: Role Based Optimizer (RBO) Cost Based Optimizer (CBO)
© 2009 Wipro Ltd - Confidential106 © 2010 Wipro Ltd - Confidential106
Collecting Statistics
This facilitates the periodic updation of Data Dictionary with the recent
information's about the Database Objects.
The Syntax is:• COLLECT STATISTICS/• COLLECT STATS/• COLLECT STAT
© 2009 Wipro Ltd - Confidential107 © 2010 Wipro Ltd - Confidential107
EXPLAIN Facility
It gives which indexes if any will be used in the query. Whether individual steps with in the query may execute in parallel. An estimate of numbers , row which will be processed. An estimate of the cost of the query. It does not execute query but explains what DB resources will be used in
processing a request.
EXPLAIN SELECT emplyee_id, emplyee_name,
employee_department from EMPLOYEE;
© 2009 Wipro Ltd - Confidential108 © 2010 Wipro Ltd - Confidential108
Invoking Fast load and Multi load in Batch mode
Fast and Multi load commands can be run using the batch mode by just passing both the input file and output file as the parameters to the load utility
Syntax for invoking fast and multi commands are as follows.
Fastload/multiload <Options> < Infile > Outfile
Infile is a Multi/fast Load job script file which includes all MultiLoad commands and SQL statements. The outfile is the output stream file.
The below mentioned options can be used while invoking the load utilites-b /* only print the brief info which can beused to determine success or failure. */-c characterset_name /* the name can be ASCII ( 255 )-e filename /* specifies a file for error messages */-r ‘MultiLoad Command’ /* the MultiLoad Command may be RUN FILE, so you can run a job script */-M /* maximal number of sessions */-N /* minimal number of sessions */
© 2009 Wipro Ltd - Confidential109 © 2010 Wipro Ltd - Confidential109
Rank Function
Returns an ordered ranking of rows based on the value_expression in the ORDER BY clause.
Syntax
© 2009 Wipro Ltd - Confidential110 © 2010 Wipro Ltd - Confidential110
Rank Function
This example ranks salespersons by sales region based on their sales.
SELECT sales_person, sales_region, sales_amount,RANK() OVER (PARTITION BY sales_region ORDER BY sales_amount DESC)FROM sales_table;
© 2009 Wipro Ltd - Confidential111 © 2010 Wipro Ltd - Confidential111
Total, Sub total’s
Teradata WITH and WITH BY clauses can be used to calculate totals and subtotals.The below example is used determine the sub total of the salary by department wise and also the grand total of the salary.
SELECT first_name,last_name,dept_no,salary FROM employeeWITH SUM(salary) (TITLE ‘Department SubTotal:’)BY dept_no -–To calculate subtotal by dept wise WITH SUM (salary) (TITLE ‘Total Salary:’); -- To calculate grand total of the salary
first_name last_name dept_no Salary--------- --------- -------- ---------Micheal Reed 111 46000 ------ Department SubTotal: 46000Larry King 112 60000Philip Josheph 112 65000 ------- Department SubTotal: 125000John Row 113 55000 ------- Department SubTotal: 55000Read Vitamin 114 62000Robert Howard 114 65000 ------- Department SubTotal: 127000Tony Peter 115 72000 ------- Department SubTotal: 72000 ------- Total Salary: 425000
© 2009 Wipro Ltd - Confidential112 © 2010 Wipro Ltd - Confidential112
Sample Function
Sample function is used to randomly display the records from the table.
Sql Syntax:
select * from tablename sample n
n – represents the number of records to be displayed or also could be percentage of the records to be displayed
Example
Select * from emp sample 2 – This will display 2 records
© 2009 Wipro Ltd - Confidential113 © 2010 Wipro Ltd - Confidential113 © 2009 Wipro Ltd - Confidential113 © 2010 Wipro Ltd - Confidential113
A data type conversion modifies the data type or attributes of an expression and can be either
implicit or explicit. Explicit conversions can be made using the CAST function or Teradata
conversion syntax.
Forms of Data Type Conversions
The Teradata Database supports the following forms of data conversion: Implicit Explicit using the CAST function Explicit using Teradata conversion syntax
Data type And Conversions
© 2009 Wipro Ltd - Confidential114 © 2010 Wipro Ltd - Confidential114 © 2009 Wipro Ltd - Confidential114 © 2010 Wipro Ltd - Confidential114
Implicit Type Conversions
Teradata Database permits the assignment and comparison of some types without requiring the types to be explicitly converted.
Teradata Database also performs implicit type conversions on some argument types passed to macros, stored procedures, and SQL functions such as SQRT.
Supported Data Types
Byte Numeric Date Character
© 2009 Wipro Ltd - Confidential115 © 2010 Wipro Ltd - Confidential115 © 2009 Wipro Ltd - Confidential115 © 2010 Wipro Ltd - Confidential115
Implicit Type Conversion During Assignment
CREATE TABLE T1(Fname VARCHAR(25),Fid INTEGER,Yrs CHARACTER(2));CREATE TABLE T2(Wname VARCHAR(25),Wid INTEGER,Age SMALLINT);
Consider the following tables:
In the following statement, Teradata Database implicitly converts the character string in T1.Yrs to a numeric value
UPDATE T2 SET Age = T1.Yrs + 5;
This is not evident in the syntax of the source statement, but becomes evident when the dictionary information for tables T1 and T2 is accessed.
© 2009 Wipro Ltd - Confidential116 © 2010 Wipro Ltd - Confidential116 © 2009 Wipro Ltd - Confidential116 © 2010 Wipro Ltd - Confidential116
Implicit Type Conversion During Comparison
In the following statement, Teradata Database implicitly converts both operands of the comparison operation to FLOAT values before performing the comparison:
SELECT Fname, Fid FROM T1WHERE T1.Yrs < 55;
Consider the table T1 in Implicit Type Conversion During Assignment.
Implicit Type Conversion in Parameter Passing Operations
Consider the SQRT system function that computes the square root of an argument.
In the following statement, Teradata Database implicitly converts the character argument to FLOAT type
SELECT SQRT('13147688');
© 2009 Wipro Ltd - Confidential117 © 2010 Wipro Ltd - Confidential117 © 2009 Wipro Ltd - Confidential117 © 2010 Wipro Ltd - Confidential117
CAST in Explicit Data Type Conversions
Purpose
Converts an expression of a given data type to a different data type or the same data type with a different format.
Teradata SQL supports two different syntaxes for CAST functionality, only one of which is ANSI SQL-2003-compliant.
Syntax
----CAST---- (expression----AS---- ansi_sql_data_type------)
data_type_list
Expression an expression with known data type to be cast as a different data
type.ansi_sql_data_type
the new data type for expression.data_type_list
the new data type or data attributes or both for expression.
© 2009 Wipro Ltd - Confidential118 © 2010 Wipro Ltd - Confidential118 © 2009 Wipro Ltd - Confidential118 © 2010 Wipro Ltd - Confidential118
CAST does not convert the following data type pairs:
Numeric to character, if the server character set is GRAPHIC. Character expressions having different server character sets.
© 2009 Wipro Ltd - Confidential119 © 2010 Wipro Ltd - Confidential119 © 2009 Wipro Ltd - Confidential119 © 2010 Wipro Ltd - Confidential119
Teradata Conversion Syntax in Explicit DataType Conversions
Teradata conversion syntax is defined as follows
---expression------(data_type_list)------
Expression
The data expression to be converted to the new definition defined by data_type_list
Data_type_list
A data type declaration or data attributes or both.List elements must be separated by commas.
© 2009 Wipro Ltd - Confidential120 © 2010 Wipro Ltd - Confidential120
April 18, 2023 © 2008 Wipro Ltd - Confidential 120