Chapter 16
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.
Table of Contents Chapter 16 – HELP and SHOW
– Determining the Release of your Teradata System
– HELP SQL
– SHOW Table command for Table DDL
– SHOW View command for View Create Statement
– SHOW Macro command for Macro Create Statement
– SHOW Trigger command for Trigger Create Statement
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 pull 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>.* ; HELP COLUMN <table-name>.<column-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> . |
| The above chart shows HELP commands for information on database tables and sessions, as well as SQL and SPL commands: | |
HELP DATABASE
HELP DATABASE SQL_Class ;
| Table/View/Macro name | Kind | Comment |
| Tstamp_Macro | M | ? |
| Subscribers | T | ? |
| Student_Table | T | ? |
| Student_Course_Table | T | ? |
| Stats_Table | T | ? |
| Services | T | ? |
| Sales_Table | T | ? |
| Providers | T | ? |
| Order_Table | T | ? |
| Names_View | V | ? |
| Job_Table | T | ? |
| Hierarchy_Join_Index | I | ? |
| Employee_Table | T | ? |
A complete list of KIND
| A | Aggregate function |
| B | Combined aggregate/analytical function |
| E | External stored procedure |
| F | Function |
| G | Trigger |
| H | Method |
| I | Join index |
| J | Journal table |
| M | Macro |
| N | Hash index |
| P | Procedure |
| Q | Queue table |
| R | Table function |
| S | Ordered analytical function |
| T | Table |
| U | UDT |
| V | View |
| X | Authorization |
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 |
A complete list of KIND
| A | Aggregate function |
| B | Combined aggregate and analytical function |
| E | External stored procedure |
| F | Function |
| G | Trigger |
| H | Method |
| I | Join index |
| J | Journal table |
| M | Macro |
| N | Hash index |
| P | Procedure |
| Q | Queue table |
| R | Table function |
| S | Ordered analytical function |
| T | Table |
| U | UDT |
| V | View |
| X | Authorization |
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 ;
| Table/View/Macro name | Kind | Comment |
| Tstamp_Macro | M | ? |
| Subscribers | T | ? |
| Student_Table | T | ? |
| Student_Course_Table | T | ? |
| Stats_Table | T | This table holds Stats |
| Services | T | ? |
| Sales_Table | T | ? |
| Providers | T | ? |
| Order_Table | T | ? |
| Names_View | V | ? |
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 ;
| Table/View/Macro name | Kind | Comment |
| Addresses_v | V | ? |
| Claims_v | V | ? |
| Course_v | V | ? |
| Customer_v | V | ? |
| Department_v | V | ? |
| Employee_v | V | No Salary is shown |
| Emp_Job_v | V | ? |
| Hierarchy_v | V | ? |
| Job_v | V | ? |
| Names_v | V | ? |
The above syntax will place a comment on the View.
SELECT SESSION
SELECT Session;
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’ ;
| DBS SQL Commands | ||
| ABORT | ALTER TABLE | BEGIN LOGGING |
| BEGIN TRANSACTION | CHECKPOINT | COLLECT STATISTICS |
| COMMIT | COMMENT | CREATE DATABASE |
| CREATE INDEX | CREATE MACRO | CREATE TABLE |
| CREATE USER | CREATE VIEW | DATABASE |
| DELETE | DELETE DATABASE | DELETE USER |
| DBS SQL Functions | ||
| ABS | ADD_MONTHS | AVERAGE |
| CHARACTERS | CAST | CHAR2HEXINT |
| COUNT | CORR | COVAR_POP |
| CSUM | EXP | EXTRACT |
| FORMAT | INDEX | HASHAMP |
| HASHBKAMP | HASHBUCKET | HASHROW |
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.