Playground  Resume

Introduction to Teradata Aster Database Administration

This a note for Teradata Aster Basics 6.10 Exam a.k.a TACP(Teradata Aster Certified Professional).

Recommended courses are followings and this note is for the 2nd course.

nc_system schema holds system information.

3 categories of DD views

Replication Factor

Ganglia is a open source, web-based, scalable distributed system monitoring tool.

AMC Status

Aster Database only supports B-tree indexes, cannot enforce referential integrity.
There is no data sharing among Aster databases.

/*Change database*/
beehive=> \connect retails_sales;
retails_sales=>database beehive;


/*List database*/
beehive=> \l

/*Exit database*/
beehive=> \q

/*List schemas*/
beehive=> \dn

/*View tables in the PROD schemas*/
beehive=> \dt prod.*

/*View columns/data types*/
beehive=> \d prod.sales_fact

/*Show current schema*/
show search_path;
ALTER USER beehive SET SEARCH_PATH = 'public', 'mkt';

CONNECT privilege must be given to access the database. USAGE privilege must be given to access the schema.

Data Modeling Quiz

Aster column name rules

Constraint Options

create table stuff
dept varchar DEFAULT 'none',
age smallint CHECK(age >= 18 and age <= 70),
name varchar
distribute by replication

Data Types

Supported data types of distribution

For large tables (> 1million rows, usually Fact table)
For small tables (<= 1million rows, usually Dimension table)

If ASH key and JOIN columns doesn’t match, SHUFFLE will occure.

VACUUM: Converts dead space into usable free space
VACUUM FULL ANALYZE: Physically rearrange the data on disk
NC_RELATIONSTATS: Generate various reports

nCluster loader arguments

Default delimited format is TSV
-B and -E specify script name to execute it

Parallelizing the Load tier

Error logging is turned off by default. This means the load job will abort and rollback the data on encoutering the first error.