How to collect TSET
This article will provide instructions on exporting data and collecting TSET step-by-step (export & pack)
Note : Please make sure the version of TSET installed on your PC or laptop is compatible with the currently running TDBMS version.
TSET is required to investigate various area of Teradata Database like
- Snapshots in Optimizer
- Snapshots in Generator
- Query performance or any other generic query problem
Please capture TSET using the below steps :
(Note: Please use the user who has all the access rights to the objects used in the query to connect)
- Open TSET application.
- On File menu, click Connect.
- On Tools menu, click Export.
- In Export from Teradata Database window, click Query under Export By.
- Under Export to, type in the ticket number or a unique name for identification in File name.
- Under Export to, choose a directory in In folder
- Click OK.
- In Export By Query window, paste in the query in Query.
- Modify Default Database(if the query is not fully qualified with database name)
- Click Add
- In Export By Query window, under Export Options, select the following options:
- Object Definitions
- Random AMP Samples
- Cost Parameters
- Cost Profiles
- Under Export Options, click on Options.
- In Export Options window, type in the ticket number or give a unique name for identification in RAS tag: and Cost tag:
- In Random AMP Samples(RAS), click on Select Tables
- In Export Random AMP Samples window, select Capture from AMPs under RAS Options.
- Click Select All.
- Click OK.
- In Cost Parameters, click Select Rows
- In Export Cost Parameters window, select Capture Current Costs under Cost Options.
- Click OK
- In Export Options window, click OK.
- In Export by Query window, click Export. (TSET Exporting will start.)
- On the Tools menu, click Pack.
- In the Pack window, locate the’<filename>.mcf’ file in the directory selected in step 6
- Click Pack.
- Send/Attach this pack file “<filename>.pack” for investigation.
- When collecting TSET for 3610 snapshot in optimizer, do not choose ALL in step 11 as it might cause another 3610 with execution plans.
- When collecting TSET for 3610 snapshot in Generator or query performance or any other generic query problem, choose ALL in step 11.
CASE 2: For 3610 in Syntaxer/Resolver :
- Connect to BTEQ or Teradata SQL Assistant client application.
- Use SHOW TABLEor SHOW VIEW to get all the objects definitions (i.e. tables and views) that are used in the query.
- Capture the output which will be used for investigation.
DO NOT use TSET or SHOW DML to get the DDL objects (this will cause another 3610 snapshot dump.)
CASE 3 : For Volatile tables
- In the session where the VOLATILE table is created, create a permanent data table.
(You need to setup the VT to the same state as the time issue occurred meaning populate and collect stats on VT )
CREATE TABLE <PERM TABLE> AS <VOLATILE TABLE> WITH DATA and STATS;
CREATE TABLE database1.perm_table1 as vt1 with data and stats;
<<<< VT1 needs to have the same data/stats value as the time your issue occurred.
- Collect TSET created in step 1 .
Make sure the volatile table name in the problematic query is replaced by the newly created permanent table name.
TSET Limitations :
TSET works on the below types of SQL statements :
- Basic SQL statements such as Inserts, Selects, Updates, and Deletes provided there are no variables and no temporary objects referenced such as volatile tables, global temporary tables.
- Stored procedure (in the latest TSET 15)
- TSET supports only permanent tables.
Note: It is a good idea to run a show qualified on the SQL from BTEQ or SQL Assistant before running TSET. If DDL is returned TSET should be OK.
TSET will not work on the below :
- Variables (usually denoted with a colon) like :name, :location
- Parameterized SQL (usually when you see question marks)
- DML statements like alter table, drop table, create table, create volatile table
- Collect statistics statements ( For collect statistics, use SELECT * FROM <object>;)
- Syntactically incorrect SQL
- UDF Source code needs to be captured outside of TSET
- DDL that user does not have select access on
- SQL that does not reference DDL like select date
- SQL where the default database is not set correctly or fully qualified
- Begin logging statements
- Using clauses. Often when extracted from a dump the using clauses are usually a stored procedure call from a parent request number
For complex queries where multiple join conditions or where clauses are involved, you can collect TSET using SELECT * from all the involved objects.
I hope above information helpful and useful…..