Chapter 23 – HELP and SHOW
“The whole secret of existence is to have no fear. Never fear what will become of you, depend on no one. Only the moment you reject all help are you freed.
- Buddha
Determining the Release of your Teradata System
SELECT * FROM DBC.DBCINFO;
InfoKey |
InfoData |
RELEASE |
13.00.00.12 |
VERSION |
13.00.00.12 |
LANGUAGE SUPPORT MODE |
Standard |
The above query pulls information from the Data Dictionary in USER DBC. Some companies don’t allow users to see this information, but if you have the access rights then you can run the above query.
Basic HELP Commands
| HELP DATABASE <database-name> ; | Displays the names of all the tables (T), views (V), macros (M), and triggers (G) stored in a database and user-written table comments |
| HELP USER <user-name> ; | Displays the names of all the tables (T), views (V), macros (M), and triggers (G) stored in a user area and user-written table comments |
| HELP TABLE <table-name> ; | Displays the column names, type identifier, and any user- written comments on the columns within a table. |
| HELP VOLATILE TABLE ; | Displays the names of all Volatile temporary tables active for the user session. |
| HELP VIEW <view-name> ; | Displays the column names, type identifier, and any user- written comments on the columns within a view. |
| HELP MACRO <macro-name> ; | Displays the characteristics of parameters passed to it at execution time. |
| HELP PROCEDURE <procedure-name> ; | Displays the characteristics of parameters passed to it at execution time. |
| HELP TRIGGER <trigger-name> ; | Displays details created for a trigger, like action time and sequence. |
| HELP COLUMN <table-name>.* ; HELP COLUMN <view-name>.* ; |
Displays detail data describing the column level characteristics. |
Other HELP Commands
| HELP INDEX <table-name> ; | Displays the indexes and their characteristics like unique or non-unique and the column or columns involved in the index. This data is used by the Optimizer to create a plan for SQL. |
| HELP STATISTICS <table-name> ; | Displays values associated with the data demographics collected on the table. This data is used by the Optimizer to create a plan for SQL. |
| HELP CONSTRAINT <table-name>.<constraint-name> ; | Displays the checks to be made on the data when it is inserted or updated and the columns are involved. |
| HELP SESSION; | Displays the user name, account name, logon date and time, current database name, collation code set and character set being used, transaction semantics, time zone and character set data. |
| HELP ‘SQL’; | Displays a list of available SQL commands and functions. |
| HELP ‘SQL <command>’; | Displays the basic syntax and options for the actual SQL command inserted in place of the <command>. |
| HELP ‘SPL’; | Displays a list of available SPL commands. |
| HELP ‘SPL <command>’; >, .. ; | Displays the basic syntax and options for the actual SPL command inserted in place of the <command>. |
HELP DATABASE
Not all columns in the HELP Database SQL_Class were shown, just the important ones. The HELP DATABASE command will show you the objects in your database.
HELP USER
HELP USER DBC;
Table/View/Macro name |
Kind |
AccessLog |
V |
AccessLogV |
V |
AccessRights |
T |
AccLogRule |
M |
AccLogRules |
V |
AccLogRulesV |
V |
AccLogRuleTbl |
T |
AccLogRuleTbl_TD12 |
T |
AccLogTbl |
T |
AccLogTbl_TD12 |
T |
AccLogTbl_V2R6 |
T |
AccountInfo |
V |
AccountInfoV |
V |
Not all columns in the HELP USER were shown, just the important ones. The HELP USER command will show you the objects in a USER. USER is a keyword!
HELP TABLE
HELP Table SQL_Class.Employee_Table ;
Not all columns in the HELP TABLE were shown, just the important ones. The HELP TABLE command will show you information about a table.
Adding a Comment to a Table
COMMENT ON TABLE SQL_Class.Stats_Table 'This table holds Stats' ;
Help Database SQL_Class ;
The above syntax will place a comment on the table.
Adding a Comment to a View
COMMENT ON View SQL_Views.Employee_V 'No Salary is shown' ;
Help Database SQL_Views ;
The above syntax will place a comment on the View.
SELECT SESSION
SELECT Session;
Session
8692
The SELECT Session command will show you the SESSION Number you received when you logged on to Teradata. The Parsing Engine assigned to manage your session tracks you by this session number.
USER Information Functions
SELECT Account
,Database
,Session
,USER
The Teradata RDBMS (Relational DataBase Management System) has incorporated into it functions that provide data regarding a user who has performed a logon connection to the system. The following functions make that data available to a user for display or storage. Notice the keyword USER.
HELP SESSION
Help Session;
The HELP Session command will show information about your SESSION. Not all columns were shown above, just the most important ones.
HELP SQL
Help 'SQL' ;
The HELP ‘SQL’ will show you a list of SQL Commands and another list of Functions supported by Teradata. Not all commands or functions are shown above.
A HELP SQL Example
Help 'SQL CSUM' ;
Syntax:
CSUM(value_expression, sort_expression_list)
Computes a running or cumulative total of a column value.
The HELP ‘SQL CSUM’ will show you the syntax for the CSUM command. This HELP command will work for each and every SQL Statement.
Show Commands
| SHOW TABLE <table-name> ; | Displays the CREATE TABLE statement needed to create this table. |
| SHOW VIEW <view-name> ; | Displays the CREATE VIEW statement needed to create this view. |
| SHOW MACRO <macro-name> ; | Displays the CREATE MACRO statement needed to create this macro. |
| SHOW TRIGGER <trigger-name> ; | Displays the CREATE TRIGGER statement needed to create this trigger. |
| SHOW PROCEDURE <procedure-name> ; | Displays the CREATE PROCEDURE statement needed to create this stored procedure. |
| SHOW <SQL-statement> ;, .. ; | Displays the CREATE TABLE statements for all tables/views referenced by the SQL statement. |
SHOW Table command for Table DDL
SHOW Table SQL_Class.Employee_Table ;
CREATE SET TABLE SQL_CLASS.Employee_Table ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Employee_No INTEGER,
Dept_No SMALLINT,
Last_Name CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
First_Name VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
Salary DECIMAL(8,2))
UNIQUE PRIMARY INDEX ( Employee_No )
INDEX ( Last_Name )
INDEX ( Dept_No );
The above syntax will show the Table DDL (Data Definition Language). It is the table CREATE Statement plus any additional changes, such as adding an Index.
SHOW View command for View Create Statement
SHOW View SQL_Views.Employee_v ;
CREATE VIEW SQL_VIEWS.Employee_v
(Emp_No, Lname, Fname, Sal, Dept) AS
SELECT Employee_No,
Last_Name,
First_Name,
Salary,
Dept_No
FROM SQL_CLASS.Employee_Table;
The above syntax will show the View’s CREATE Statement.
SHOW Macro command for Macro Create Statement
SHOW MACRO MY_Mac ;
CREATE MACRO MJL01.MY_Mac
(INPARM1 INTEGER, INPARM2 CHAR(10))
AS
(SELECT DEPT, DAY_OF_WEEK, AVG(SAL)
FROM SYS_CALENDAR.CALENDAR SC, MYTABLE
WHERE CALENDAR_DATE = :INPARM2
(DATE, FORMAT 'YYYYMMDD')
AND DEPT = :INPARM1
GROUP BY 1,2; );
The above syntax will show the Macro’s CREATE Statement.
SHOW Trigger command for Trigger Create Statement
SHOW TRIGGER AVG_SAL_T ;
CREATE TRIGGER MJL.AVG_SAL_T
AFTER UPDATE OF (SALARY) ON MJL.EMPLOYEE
REFERENCING OLD AS OLDROW
NEW AS NEWROW
FOR EACH ROW
WHEN (NEWROW.SALARY >
(SELECT AVG(BUDGET) * .10 (DECIMAL(10,2))
FROM MJL01.DEPARTMENT ) )
(INSERT INTO MJL01.GREATER_10_PERCENT
(EMP_NUM ,SAL_DATE ,OLDSAL ,NEWSAL
,PERC_OF_BUDGET)
VALUES (NEWROW.EMP_NBR ,CURRENT_DATE
,OLDROW.SALARY ,NEWROW.SALARY); ) ;
The above syntax will show the Trigger’s CREATE Statement.