Db2

* Teradata-Extraction-Utility

This document gives a detailed description of the features of the Teradata extraction script and how to run the script. Current extraction script supports tables and views extraction.
This utility contains three (3) files:
list-all-objects.sh - List all tables and views in your database
dcw-teradata-extract.sh – Extractions all tables and views in your database
filter.txt – This is a filter file that helps to extract specific objects in your database


To get help on various command options to run the Teradata extraction script enter:
./dcw-teradata-extract.sh -h or ./dcw-teradata-extract.sh help


* Extraction Script Command Options (dcw-teradata-extract.sh)
There are four (4) command options needed to execute the Teradata extraction script. “-o”, “-d”, “-f”, “-p”

- “-o” is a command option that allows the user to define a directory where all generated extracted file and log file is stored.
- “-d” is a command option that allow users to do two things:

1. Extract all objects from a one or more databases (-d db1 db2 db3)
2. Extract all objects from all user databases excluding system objects (-d ALL_OBJECTS)

- “-f” is a file input where the contents of the file contain one database name per line
- “-p” is a command option that allows you to filter specific objects from databases specified by -d or -f options

Similarly, one auto generated directory “Logs” is created in the user specified output directory during the execution of the extraction script.

./dcw-teradata-extract.sh -o output_directory [ -d | -f ] [<Db_name> | <filename.txt>] [-p filter.txt]


* Pre-Extraction Database Objects List Script (list-all-objects.sh)
If unsure about what tables or views exist within your user defined databases in Teradata, run the list-all-objects.sql.sh script to generate both all_objects.sql and all_user_schemas.sql files. To run this pre-extraction script, please run:

./list-all-objects.sh -o output_directory


* Object Filtering (filter.txt)
Object filtering provides an option where you can select specific objects (table or View) from one or more databases. It allows you to filter by either listing table names or filtering by pattern.

SAMPLE CASE 1
I need all tables that start with ‘Temp’ from ‘zeus’ and ‘migration_test’ databases. Edit the filter.txt file as follows:

##### Filtering File #####
TABLE_LIKE_PATTERN:Temp%
TABLE_NOT_LIKE_PATTERN:

##### START_TABLES #####
##### END_TABLES #####

VIEW_LIKE_PATTERN:
VIEW_NOT_LIKE_PATTERNS:

##### START_VIEWS #####
##### END_VIEWS #####

Run extraction as follows: 
./dcw-teradata-extract.sh -o output_directory -d zeus migration_test -p filter.txt

NOTE: When specifying multiple databases, if an object exist in one of the databases but not the others, the database not containing the object will return an empty SQL file. 

In the Sample case 1 above if migration_test database does not have a table starting with `Temp`. Then migration_test_tables.sql file will be empty.


SAMPLE CASE 2
I need to extract some `tables` and `views` from `zeus` database. Edit the filter.txt file as follows:

##### Filtering File #####
TABLE_LIKE_PATTERN:
TABLE_NOT_LIKE_PATTERN:

##### START_TABLES #####
Table1
Table2
##### END_TABLES #####

VIEW_LIKE_PATTERN:
VIEW_NOT_LIKE_PATTERN:

##### START_VIEWS #####
View1
View2
##### END_VIEWS #####

Run extraction as follows: 
./dcw-teradata-extract.sh -o output_directory -d zeus -p filter.txt


SAMPLE CASE 3
All want to extract all tables that begin with `Temp` and an employee table from database `zeus`. Edit the filter.txt file as follows:

##### Filtering File #####
TABLE_LIKE_PATTERN:Temp%
TABLE_NOT_LIKE_PATTERN:

##### START_TABLES #####
Employee
##### END_TABLES #####

VIEW_LIKE_PATTERN:
VIEW_NOT_LIKE_PATTERNS:

##### START_VIEWS #####
##### END_VIEWS #####

Run extraction as follows: 
./dcw-teradata-extract.sh -o output_directory -d zeus -p filter.txt