The Teradata Database offers several types of help using an interactive client. For convenience, this reduces or eliminates the need to look information up in a hardcopy manual or on a CD-ROM. Therefore, using the help and show operations in this chapter can save you a large amount of time and make you more productive. Since Teradata allows you to organize database objects into a variety of locations, sometimes you need to determine where certain objects are stored and other detail information about them.
Figure 3-1
To see the database objects stored in a Database or User area, either of the following HELP commands may be used:
HELP DATABASE My_DB ; Or
HELP USER My_User ; 4 Rows Returned
Table/View/Macro
name Kind Comment
employee T T = Table
with 1 row per employee
employee_v V V = View
for accessing Employee Table
Employee_m1 M M = Macro
to report
Table/View/Macro
Since Teradata considers a database and a user to be equivalent, both can store the same types of objects and therefore, the two commands produce similar output.
Now that you have seen the names of the objects in a database or user area, further investigation displays the names and the types of columns contained within the object. For tables and views, use the following commands:
HELP TABLE My_Table ;
Column
The above outputs display the column name and the kind, which equates to the data type and any comment added to a column. Notice that a view does not know the data type of the columns from a real table. Teradata provides a COMMENT command to add these
comments on tables and columns.
The following COMMENT command adds a comment to a table:
COMMENT ON TABLE <table-name> 'This is the new table comment'
;
This COMMENT command adds a comment to a column:
COMMENT ON COLUMN <table-name>.<column-name>
'This is the new column comment' ;
The above column information is helpful for most of the column types, such as INTEGER (I), SMALLINT (I2) and DATE (DA) because the size and the value range is a constant.
However, the lengths of the DECIMAL (D) and the character columns (CF, CV) are not shown here. These are the most common of the data types. See chapter 18 (DDL) for more details on data types.
The next HELP COLUMN command provides more details for all of the columns:
HELP COLUMN My_Table.*
;
6 Rows Returned Column
Name Type Nullable Format Max
Len
First_name CV Y X(12) 12 ?
Grade_Pt D Y ----.99 4 5
Last_name CF Y X(20) 20 ?
Student_ID I Y −(10)9 4 ?
Decimal Range UpperCase
Column
Name Type Nullable Format Max
Len gth
Decimal Total Digits
Class_code CF Y X(2) 2 ?
First_name CV Y X(12) 12 ?
Fractional Digits Range Low
High
? ? ? U
? ? ? N
2 ? ? N
? ? ? N
? ? ? N
Table/View? Indexed? Unique? Primary? Title Column
Constra int
Char Type
T Y N S ? ? 1
T N ? ? ? ? 1
T N ? ? ? ? ?
T Y N S ? ? 1
T Y Y P ? ? ?
The above output has been wrapped to show all the detail information available on the columns of a table.
The next chart shows HELP commands for information on database tables and sessions, as well as SQL and SPL commands:
Figure 3-2
The above chart does a pretty good job of explaining the HELP functions. These functions only provide additional information if the table object has one of these characteristics defined on it. The INDEX, STATISTICS and CONSTRAINT functions will be further discussed in the Data Definition Language Chapter (DDL) because of their relationship to the objects.
At this point in learning SQL, and in the interest of getting to other SQL functions, one of the most useful of these HELP functions is the HELP SESSION.
The following HELP returns information on the session from the PE:
HELP SESSION ;
1 Row Returned (columns wrapped for viewing) User Name Account
Name Logon
Date Logon
Time Current
Database Collation
DBC DBC 99/12/12 11:45:13 Personnel ASCII
Transaction
The above output has been wrapped for easier viewing. Normally, all headings and values are on a single line.
The current date form, time zone and everything that follows them in the output are new with the V2R3 release of Teradata. These columns have been added to make their reference here, easier than digging through the Data Dictionary using SQL.
When using a tool like BTEQ, the line is truncated. So, for easier viewing, the .SIDETITLES and .FOLDLINE commands show the output in a vertical display.
The next sequence of commands can be used within BTEQ:
.sidetitles on
Set
To reset the display to the normal line, use either of the following commands:
.DEFAULTS or
.SIDETITLES OFF .FOLDLINES OFF
In BTEQ, any command that starts with a dot (.) does not have to end with a semi-colon (;).
The next HELP command returns a list of the available SQL commands and functions:
HELP 'SQL';
DELETE DELETE
DATABASE DELETE USER
DROP
DATABASE DROP INDEX DROP MACRO
DROP TABLE DROP VIEW DROP
STATISTICS
ECHO END LOGGING END
TRANSACTION.
•
•
• DBS SQL
FUNCTIONS:
ABS ADD_MONTHS AVERAGE
CHARACTERS CAST CHAR2HEXINT
COUNT CORR COVAR_POP
CSUM EXP EXTRACT
FORMAT INDEX HASHAMP
HASHBKAMP HASHBUCKET HASHROW
KURTOSIS LN LOG
MAVG MAXIMUM MCHARACTERS
MDIFF MINDEX MINIMUM
MLINREG MSUBSTR MSUM
NAMED NULLIFZERO OCTET_LENGTH
QUANTILE REGR_INTERCEPT REGR_SLOPE
RANDOM RANK SKEW
SQRT STDDEV_POP STDDEV_SAMP
SUBSTR SUM TITLE
TRIM TYPE UPPER
VARGRAPHIC VAR_POP VAR_SAMP
ZEROIFNULL
The above output is not a complete list of the commands. The three dots in the center represent the location where commands were omitted so it fit onto a single page. All commands are seen when performed on a terminal.
Once this output has been used to find the command, than the following HELP command provides additional information on it:
HELP 'SQL END TRANSACTION' ; 5 Rows Returned
On-Line Help
{ END TRANSACTION } { };
{ET }
Since the terminal is used most of the time to access the database, take advantage of it and use the terminal for your HELP commands.
Tools like Queryman also have a variety of HELP commands and individual menus. Always look for ways to make the task easier.