Database Query Log (DBQL) in Teradata

DBQL provides a series of pre-defined tables that can store, based on rules you specify, historical records of queries and their duration, performance, and target activity.

DBQL is flexible enough to log information on the variety of SQL requests that run on the Teradata Database, from short transactions to longer-running analysis and  mining queries. You begin and end collection for a user, group of users, account, or a list of accounts.

Collection option include:

• Default logging reports for each query with at least the leading SQL characters, the time of receipt, the number of processing steps completed, and the time the first step was dispatched, and the times the packets were returned to the host.
• Summary logging reports at each logging interval the count of all queries that completed processing time within the specified time intervals, I/O criteria, or CPU usage criteria. 
• Threshold logging can log a combination of default and summary data:
• Default data for each query that ran beyond the threshold limit
• Summary counts of all queries that ran within the threshold time
• Use I/O or CPU time as a criterion for detail or summary logging
• Detail logging, which includes:
• Default Data
• Any or all of the following:
• Step level Activity, including parallel steps
• Object Usage per Query
• Full SQL text
• Explain Text

Note: Any DBC database tables and columns used by the system while processing a query are not reflected in the DBQL object rows for that query. This means, for example, that statements like CREATE TABLE or SELECT FROM DBC.xxx will not have objects logged through DBQL because they deal with DBC tables and columns.

Also, DBQL is limited to logging information about base tables and logging direct SQL statements. Macros, Views and triggers are not reported.

The DBQL Components

The DBQL logs are a series of system tables created in database DBC during the Teradata Database installation process. The suite of DBQL components includes a security macro and a view for each table, which are created in database DBC by the DIP utility.

DBQL tables that are being populated include:

• DBQLogTbl  table stores default rows. The foundation of the DBQL feature.
• DBQLObjTbl  table stores information on the target objects of the query being logged. One row is logged for each object referenced in the query.
• DBQLSQLTbl  table logs the full SQL statement text, no matter how large. Multiple rows are generated if necessary.
• DBQLStepTbl  table logs the AMP step-level information of the query being logged
• DBQLExplainTbl  table logs the full unformatted Explain text.

DBQL Data Capture

Like other system tables, the predefined DBQL logs are created as relational tables in database DBC during normal Teradata Database installation. However, while most system tables are populated automatically, you can choose whether you want to populate the DBQL tables.

If you choose not to use the feature, the tables remain empty. If you want to use the feature, simply submit a BEGIN QUERY LOGGING statement, with or without options.

The options enable you to control the volume and detail of the logged data. You can define rules, for instance, that log the first 5,000 characters of any query that runs during a session invoked by a specific user under a specific account. Or you could define a rule to log queries that take more time to complete than the specified time threshold or queries with CPU usage that exceed a specific CPU hundredths of a second.

DBQL is controlled by the Teradata SQL statements BEGIN QUERY LOGGING and END QUERY LOGGING. Only a user with EXECUTE privilege on DBC.DBQLAccessMacro can invoke the statements.

DBQL Log Table

Default rows are stored in DBQLogTbl, the foundation of the DBQL feature.

When you do not specify a LIMIT SUMMARY or LIMIT THRESHOLD option, one default row of query-level information is logged in DBQLogTbl for each query processed during a session that is initiated by any user for whom a query logging rule exists.

If you specify options that result in more detailed information, a default row is still generated in DBQLogTbl (except with the SUMMARY option or a query that completes within the limit specified with the THRESHOLD option), plus one or more additional logs are populated with one or more additional rows.

Note: DBQL is limited to logging information about base tables and logging direct SQL statements. Macros, views, and triggers do not result in complete logging information.

The Default Row

The fields of the default row provide general query information that is usually adequate for investigating a query that is interfering with performance. When no options are specified, a default row includes at least the:

• User ID and user name under which the session being logged was initiated
• Unique ID for the process, session, and host (client) connection
• Account string, expanded as appropriate, that was current when the query completed
• First 200 characters of the query SQL statement
• Times - Start and Last Response
• CPU and I/O statitics
• Default Database name that was current when the query completed
• Numerous other performance metrics

DBQL Object Table

With the OBJECTS option, DBQL logs one row in DBQLObjTbl for each data object referenced by the query. An object can be a database, data table, column, secondary index, join index, or journal table. (If the object is a secondary index, its number is logged rather than a name.) Also logged is a count of the number of times the object was accessed by the optimizer.

Note: Any DBC database tables and columns used by the system while processing a query are not reflected in the DBQL object rows for that query. This means, for example, that statements like CREATE TABLE or SELECT FROM DBC.xxx will not have objects logged through DBQL because they deal with DBC tables and columns.

Only objects from base tables are logged. Objects from views and macros are not logged.

Scenario 1

User runs a SELECT statement that joins two tables owned by the same database:

• One row for the query is logged in DBQLogTbl
• Rows are logged in DBQLObjTbl as follows:
• A row for the database
• One row for each table
• One row for each selected column

Scenario 2

User runs a query that causes the Optimizer to reference the same object twice:

• One row for the object is logged in DBQLObjTbl
• The value in the FreqofUse field is incremented to 2.

DBQL SQL Table

The DBQLSQLTbl is populated if you specify the SQL option in addition to default information. The SQL option logs the full statement text, no matter how large, into DBQLSqlTbl. Multiple rows are generated as necessary.

Note: The full SQL text of a CREATE/ALTER/REPLACE PROCEDURE/ FUNCTION is not logged in DBQLSQLTbl when the statement is submitted with the option to not save the source in the database.

DBQL STEP Table

DBQLStepTbl is populated if you specify the STEPINFO option. When the query completes, the system logs one row for each query step, including parallel steps.

DBQL EXPLAIN Table

This table is populated if the WITH EXPLAIN option is requested in addition to the default information row. If the additional Explain text is greater than 64KB, multiple rows are generated.The DBQLExplainTblis populated with unformatted explain text.

Collect Statistics Recommendations in Teradata TD14

Statistical information is vital for the optimizer when it builds query plans. But collecting statistics can involve time and resources. By understanding and combining several different statistics gathering techniques, users of Teradata can find the correct balance between good query plans and the time required to ensure adequate statistical information is always available.

Collect Full Statistics
•PI of small tables (less than 100 rows per AMP)
•Non-indexed columns used in where or join constraints
•Composite columns that often appear together in conditions with equality
•All NUSIs with an uneven distribution (High Bias Values)
•NUSIs used in Join conditions
•UPIs and USIs used in range constraints
•NUPIs that are more than or less than 75% unique (with no statistics, optimizer assumes 75% unqiueness)

Can rely on Random AMP Sample
•UPIs or USIs only used in equality constraints
•NUSIs with even distribution of values
•NUPIs with even distribution

Option to use USING SAMPLE
• Unique index columns
• Nearly-unique columns or indexes (Columns or indexes which are over 95% unique)

Other Considerations

1. Optimizations such as nested join, partial GROUP BY, and dynamic partition elimination will not be chosen unless statistics have been collected on the relevant columns.

Statistics must be collected on relevant columns to use optimizations such as:
•Nested Join
•Partial Group By
•Dynamic partition elimination

NUPIs that are used in join steps in the absence of collected statistics are assumed to
be 75% unique, and the number of distinct values in the table is derived from that. A
NUPI that is far off from being 75% unique (for example, it’s 90% unique, or on the other
side, it’s 60% unique or less) will benefit from having statistics collected. However, if it
is close to being 75% unique, then random AMP samples are adequate. To determine
what the uniqueness of a NUPI is before collecting statistics, you can issue this SQL
statement:
EXPLAIN SELECT DISTINCT nupi-column FROM table;

2. For a partitioned primary index table, it is recommended that you always collect statistics on:
• PARTITION. This tells the optimizer how many partitions are empty, and how many rows are in each partition. This statistic is used for optimizer costing.
• The partitioning column. This provides cardinality estimates to the optimizer when the partitioning column is part of a query’s selection criteria.

3. For a partitioned primary index table, consider collecting these statistics if the partitioning column is not part of the table’s primary index (PI):
• (PARTITION, PI). This statistic is most important when a given PI value may exist in multiple partitions, and can be skipped if a PI value only goes to one partition. It provides the optimizer with the distribution of primary index values across the partitions. It helps in costing the sliding-window and rowkey-based merge join, as well as dynamic partition elimination.
• (PARTITION, PI, partitioning column). This statistic provides the combined number of distinct values for the combination of PI and partitioning columns after partition elimination. It is used in rowkey join costing.

4. Random AMP sampling has the option of pulling samples from all AMPs, rather than from a single AMP (the default). All-AMP random AMP sampling has these particular advantages:
• It provides a more accurate row count estimate for a table with a NUPI. This benefit becomes important when NUPI statistics have not been collected (as might be the case if the table is extraordinarily large), and the NUPI has an uneven distribution of values.
• Statistics extrapolation for any column in a table will not be attempted for small tables or tables whose primary index is skewed (based on full statistics having been collected on the PI), unless all-AMP random AMP sampling is turned on. Because a random AMP sample is compared against the table row count in the histogram as the first step in the extrapolation process, an accurate random AMP sample row count is critical for determining if collected statistics are stale, or not.
(Set the DBSControl setting for Random AMP samples to All AMPs instead of the default of single AMP to leverage extrapolation on small tables or skewed tables)

How to use partitioned primary index (PPI) Part 1

How to use partitioned primary index (PPI)

This post is about row partitioning and will not discuss columnar.

What is partitioning?

To explain it correctly, let's get back to the basics...
Each Teradata tables (except NoPI type) have "Primary Index", aka. PI, which is not physical index, but rather a logical construction: one or more columns of the table which give the input for hashing method. The hash value determines two things:

• Which AMP will store the record
• Storing order of the records within the AMPs

If the PI is non-partitioned then the records are stored in order of hash value of PI.

If you use (row) partitioning, you define it at the Primary Index.
In this case Teradata will associate a 2bytes or 2/8 bytes (at V14.10) "partition code" to the record*, and the storing order is <partition code>,<hash_value> (aka. RowKey).
That way partitions are not sub-tables or other physical objects, but only influence the record storing order.

* This implies that no more than 64k(2bytes)/9Q(8bytes) partitions can exist. For details read the appropriate Teradata version's documentation.

What is the difference between PPI and NUSI?

NUSI (Non Unique Secondary Index) can serve as similar purposes, but is absolutely different.
NUSI is a separate subtable, with analogue PI to base table, but different (value) ordering.
For details please read Teradata documentation.

How to define?

Non partitioned table:
create table tablePI
(
  Trx_id Integer
, Trx_dt Date
)
PRIMARY INDEX (Trx_id)

Partitioned table:
create table tablePPI (   Trx_id Integer
, Trx_dt Date
)
PRIMARY INDEX (Trx_id, Trx_dt**)
PARTITION BY RANGE_N(Trx_dt BETWEEN DATE '2010-01-01' AND DATE '2013-12-31' EACH INTERVAL '1' DAY , NO RANGE, UNKNOWN) 

Highlights

• **Partitioning key (Trx_dt here) can be part of the PI or not. This is very important, see below.
• Partitioning can be single or multiple (MLPPI) levels***
• RANGE_N or CASE_N functions can be used for determining partition code
• RANGE_N function has constant interval endpoints and partition length.
• NO RANGE and UNKNOWN partitions will store the out-of-intervals and null value records respectively

***MLPPI is a technique when multiple or nested partitioning is defined on the table. Logically it looks like sub-partitions, but in practice it only influences the calculation of partition code values, which is still a linear 2/8 bytes value overall the table.

Pros - Cons of using PPI

PPI is a very useful feature, but not a silver bullet to use it everywhere. Look the trade offs:

• (+) Partition elimination
  Only the relevant partitions are scanned while accessing data
• (+) Interval filtering is supported
• (+) Accelerates INSERTs
  If we load increment data into a populated table. Very likely less data blocks are affected, since few partitions are involved (if date is the partitioning basis) 
• (-) 2 or 8 bytes extra space allocation per record
• (-) Compression is not allowed on PartKey column
• (-) PartKey inclusion problem (see below)
• (-) Partition elimination works only with literals
  Subselects cause full table scans

Design aspects

RANGE_N or CASE_N

These functions are used to define partitioning. RANGE_N is for concentrate date (integer) intervals into partitions, while CASE_N is like a CASE-WHEN-THEN expression, where the outcome is the partition.

Typically RANGE_N is used when we partition a transaction table by its date or timestamp, while CASE_N is popular in special cases like categorizing. You can use more columns in the logical expression, but take care, all of them must be used in filter condition to enable partition elimination.

RANGE_N: what interval size?

It depends on the granularity of the data, granularity of filtering and how long interval should be stored in the table. Usually daily partitioning is ideal.

RANGE_N: interval extension or intervals in advance?

If we load transactional data into our partitioned table, the date column we use as partition key is populated later and later dates, while we have a finite partition range definition.

Partition ranges can be added to RANGE_N definition periodically (depends on version), or we can define partitions in far advance. (365 partitions required for a year, 65k partitions cover ~ 180years, which is more than enough) Note that empty partitions do not allocate space.

One of the methods above should be applied, otherwise the NO RANGE partition will grow extensively, which will cause performance degradation due to less effective partition elimination.

Partitioning Key: include in PI or not?

This is the funny point.
Partitioning key is the column(s) that determines the partition, say used in the RANGE_N/CASE_N definition. We can include it in the Primary Index or not, we decide.

Let's take an example. We have a master-detail pair of tables, nicely "equi-PI"-ed for effective join:

CREATE TABLE ORDER_HEAD
(
  ORDER_NO INTEGER
, ORDER_DT DATE
) UNIQUE PRIMARY INDEX (ORDER_NO);

CREATE TABLE ORDER_ITEM
(
  ORDER_NO INTEGER
, ORDER_ITEM_NO
, PROD_NO INTEGER
) PRIMARY INDEX (ORDER_NO);

We modify ORDER_HEAD's PI:
UNIQUE PRIMARY INDEX (ORDER_NO, ORDER_DT)

PARTITION BY RANGE_N(ORDER_DT BETWEEN DATE '2010-01-01' AND DATE '2013-12-31' EACH INTERVAL '1' DAY , NO RANGE, UNKNOWN)




Should we include ORDER_DT or not? Which is better, what is the difference?

• Not include
  ORDER_HEAD and ORDER_ITEM tables will have similar AMP distribution, but different physical order within the AMPs.
  Each join operation requires sort of the selected ORDER_HEAD records in spool, or ORDER_ITEMS table will be merge joined against each selected non empty partitions of ORDER_HEAD sequentially (called sliding-window merge join)
• Include
  ORDER_HEAD and ORDER_ITEM tables will have different AMP distribution, each join operation requires redistribution.Why do we not use the same PI at ORDER_ITEM? Because we do not have that column there.

Neither of the above is acceptable in many cases. What should we do? In this case I would copy the ORDER_DT to the ORDER_ITEM table also, and use the same "Included" version of PI. Requires some more space, logic in load time, but great gain while accessing data.

Use cases

Filtering

This select will eliminate all partitions except those three:

select * from ORDER_HEAD where order_dt between '2013-12-12' (date) and '2013-12-14' (date);

This select will generate all rows scan:

select * from ORDER_HEAD where cast( order_dt as char(7)) = '2013-12';

This select will generate all rows scan* either (sub-query):

select * from ORDER_HEAD  where order_dt in (select max(calendar_date) from sys_calendar.calendar  where year_of_calendar=2013 and month_of_year=5);

Why? Optimizer has to determine which partitions to be accessed in time of generating execution plan. That time it cannot know what is the result of the subquery. That is it.

* I got a proper comment on this option to double check. Yes, right, this information is a out-of-date. With actual versions of Teradata (V13.10..V14.10) I experienced 3 different results:

• Full scan
  Eg. sub-query contains a "group by"
• Dynamic partition elimination
  Sub-query is simple, indicates "enhanced by dynamic partition elimination" section in the plan
• Plan-time partititon elimination
  Literal condition or very simple sub query. Parsing time evaluation enables PO to determine which partitions to be scanned.  Plan: "...We do an all-AMPs ... step from 3 partitions of...". Do not really know exactly what decides between full scan, dynamic- or plan-time elimination... Explanations welcome.

Join

We join two tables: T1 and T2. The table shows what happens if they are partitioned, not partitioned and the partitioning key is included or not in the PI:

T2 T1	PI:(a)	PI:(a) PART(b)	PI:(a,b) PART(b)
PI:(a)	Join: T1.a=T2.a RowHash match
PI:(a) PART(b)	Join: T1.a=T2.a T1 sorted by hash(a) or Sliding-window MJ	Join: T1.a=T2.a T1&T2 sorted by hash(a) or Sliding-window MJ (NxM combinations) Join: T1.a=T2.a and T1.b=T2.b T1&T2 sorted by RowKey RowKey based MJ
PI:(a,b) PART(b)	Join: T1.a=T2.a T1 Redistributed & sorted by hash(a)	Join: T1.a=T2.a T1 Redistributed by hash(a) T2 sorted by hash(a) and MJ Join: T1.a=T2.a and T1.b=T2.b T2 Redistributed and sorted by RowKey RowKey based MJ	Join: T1.a=T2.a and T1.b=T2.b RowKey based MJ

Insert

Let's take a transaction table like ORDERS. In practice we load it periodically (eg. daily) with the new increment which is typically focused to a short interval of transaction date/time. If the ORDERS table is not partitioned, then the outstanding hashing algorithm will spread them all over the data blocks of the table evenly, therefore Teradata has to modify far more data blocks than the increment was reside in.

But if the ORDERS table is partitioned, then the physical order of the records is primarily determined by the partition key. This means that the increment will reside in very few partitions, close together, and the insert operation requires approx the same number of blocks to be written than the increment was in.

For more details on PPIs please refer the documentation of the appropriate Teradata version.

To be continued...

How to choose partitioned primary index (PPI)

What is the difference between NPPI and PPI?

• NPPI: Non partitioned primary index
  The good old regular PI. The rows are distributed by HASHAMP(HASHBUCKET(HASHROW(PI))), and ordered by HASHROW(PI), nothing special
• PPI: Partitioned primary index
  Distribution is the same, but ordering different: <PartitionID><HASHROW(PI)>. The <PartitionID> is a stored value in each rows, allocating 2 or 8 bytes (see below).

The only difference is the storing order of the records (and the 2/8 bytes overhead).

What is PPI good for?

The partitioning feature - like in many other databases - usually solves some performance issues, say enables to eliminate some needless work in specific situations.

• SELECT
  Eligible "where conditions" result serious partition-elimination, which means that usually only a small fraction of the table should be scanned instead of the whole one.
• INSERT
  Check the storing order of the NPPI tables: the records are in "hash" order, that is if I want to insert a series of records into a Teradata table, they will reside in spreadly distributed data blocks. If the table is big enough, my new eg. 1000 records will get into ~1000 different data blocks, what means 1000 pieces of expensive "random writes". However if my 1000 records got to a PPI table and they will have the same PartitionID, they will get into far less than 1000 data blocks with high probability. In real life situations we often will write to continuous data blocks with much cheaper "sequential write" 
• DELETE
  Same as INSERT
• BACKUP
  Teradata allows archiving only one or more partitions, saving lot of time and tape. Older data in transaction tables usually does not change therefore it is unnecessary to backup them every time

"Good-to-know"s

Costs of partitioning

Like all good things in the world, partitioning has trade-offs also:

• Extra 2/8 bytes per record allocated storage space
  Depending on maximal number of partitions. See "Number of partitions" chapter
• Slower "SAMPLE" scans
  Proper random sampling is more complex, since the physical storing order is in correlation with partitioning value
• Extra sort operations / Sliding window joins
  If joined to a table which has NPPI or PPI with not exactly same definition will result a preparation "sort" step, or leads to a "sliding window merge join", which is technically N x M merge joins between the partitions of TableA and TableB.

Number of partitions

How many partitions should I have?
How many partitions do I have?
How is an empty partition looks like?
They are all interesting questions, let's analyze the implementation of Teradata implementation.

Partition is not an object, it is just a calculated (and stored) value in the record, which will determine the physical storing order of the record. A partition will not allocate space, an "empty partition" technically means that no record exists with that partition's partitionID, nothing else.
How many partitions I have in the table? As many different PartitionID in the existing records occure, which depends on the occurring values of the partitioning column.
How many partitions can I have in the table? It depends on the table definition. One must use the RANGE_N or the CASE_N function to define the PartitionID calculation. Its definition unambiguously determines how many different PartitionID values may occur. In versions up to V13.10 65535 is allowed, from V14.00 we can have as many as 9.2 Quintillion (8 bytes PartitionID). The table definition cannot be altered to switch between 2 and 8 bytes layout.

What is the drawback of having many partition? The sliding-window merge join. Mind including partitioning column into the PI if possible (otherwise PI based filtering will cause as many accesses as many partitions exist).

What happens with the out-of-range records?

We have the clauses NO RANGE and NO CASE in the PPI definition. They mean an ID value for that partition that is out of the defined range or case, those records got into this partition. It can be a hidden trap, if you forget to maintain your date partition definition on a transaction table, and all records got to get into this partition from a moment. And the partition keeps fattening, queries keep go slowing somehow...

Multi level partitioning

This is a good trick. One can define partitioning "hierarchically", which is simply a "Cartesian product" of the partitions at each levels, the result is a single PartitionID. In case of 2 bytes partitioning, the "Cartesian product" should fall below 65535.

What is sensational in the Teradata implementation of multi level PPI? You can filter only lower level partitioning key(s) also, partition elimination will happen. How? It calculates all possible combinations, and produces the PartitionID list to be scanned, excellent.

Partitioning granularity

The next good question is: how fine should I define partitioning?

It depends. Basically I'd branch to two main cases:

• "Temporal" (date) partitioning
  The best partition size is the day. Most of the filtering is on day level, and we have ~365 days a year, not too much partitions for your lifetime. If we partition on monthly units, then the partition elimination ranges are more rough, and we have 12 partitions a year, which is also too much in case of a PI-NPPI join.
• All others
  It really depends. Depends on the goal, and the value demographics. It's good to correlate with the filtering pattern (what is the frequent relevant 'where' condition parcel).

Hope it helped, please ask, if something is missing or confusing.

How to use query banding in Teradata?

What is queryband?

Teradata is a diligent RDBMS that runs sometimes millions of SQLs a day. You will see them in the DBQL (DataBase Query Logging area) - if it is switched on - but it's a hard job to know around in that mess of queries. How can I find a specific query? What did that query run by? If I want to analyze or modify something I need to find the source of the execution as exactly as can be.
Queryband is a labelling possibility to flag the queries to let their source job/report/etc. be easily found.

Who defines the queryband?

Setting the queryband is usually the responsibility of the query runner:

• ETL software or solution that executes it
• OLAP tool that issues it
• Person, who runs it ad-hoc

How to set the queryband?

Technically it is a quite simple stuff: Teradata provides a command to set it:

SET QUERY_BAND = {'<variable1>=<value1>;<variable2>=<value2>;...' / NONE} [UPDATE] for SESSION/TRANSACTION;

, where:

<variable1>=<value1>;

Queryband can consist of arbitrary number of "variable"-"value" pairs. Both are string values. Do not forget to put the semicolon after each variable-value pair!

NONE: clears the queryband 

UPDATE: is specified, then those variables that has been previously defined are updated by the new value, others are added with the given value. Empty value string is a valid content and will not remove the variable. Please note that deleting a value is only possible by redefining the queryband without that specific variable.

SESSION/TRANSACTION: what it says...

Where can I check queryband?

The values are reflected in the dbc.SessionfoX.QueryBand and the dbc.DBQLogtbl.QueryBand. The following example shows its content:

SET QUERY_BAND='PROJECT=TeraTuningBlog;TASK=QB_example;' for session;

(For the logged in session)
SELECT queryband FROM dbc.sessioninfoX WHERE sessionNo=session;----------------------------------------------------
PROJECT=TeraTuningBlog;TASK=QB_example;

(For the formerly ran queries)

SELECT queryband FROM dbc.dbqlogtbl WHERE Queryid=...;----------------------------------------------------
=S> PROJECT=TeraTuningBlog;TASK=QB_example;

(For a specific variable, eg. "PROJECT")
SELECT QB_PROJECT FROM
(
   SELECT CAST((case when index(queryband,'PROJECT=') >0 then substr(queryband,index(queryband,'PROJECT=') ) else '' end) AS VARCHAR(2050)) tmp_PROJECT
     ,CAST( (substr(tmp_PROJECT,characters('PROJECT=')+1, nullifzero(index(tmp_PROJECT,';'))-characters('PROJECT=')-1)) AS VARCHAR(2050)) QB_PROJECT
   FROM dbc.sessioninfoX WHERE sessionNo=session
) x ;

----------------------------------------------------

TeraTuningBlog

(Which queries has been run by the "LoadCustomers" project?)

   SELECT a.*, CAST((case when index(queryband,'PROJECT=') >0 then substr(queryband,index(queryband,'PROJECT=') ) else '' end) AS VARCHAR(2050)) tmp_PROJECT
     ,CAST( (substr(tmp_PROJECT,characters('PROJECT=')+1, nullifzero(index(tmp_PROJECT,';'))-characters('PROJECT=')-1)) AS VARCHAR(2050)) QB_PROJECT
   FROM dbc.dbqlogtbl a WHERE QB_PROJECT="LoadCustomers"

;

Designing querybanding

We know how to set the queryband, it's quite easy to build in / configure in the ETL tool, OLAP software and other query running applications. But what variables should we define, and how should we populate them? I give a best practice, but it is just a recommendation, can be modified due according to your taste.

First of all, some things to mind:

• Use short variable names and values, since they will be logged in each DBQL records
• Define consistent structure in each source systems to easily analyze data
• Record as detailed information as you need, not more, not less. Define unique values for those items you later want to differentiate. Using a lookup/hierarchy table you can easily merge what you need, but never can drill down what is aggregated.

I recommend these variables to be defined:

• SYS: Maximum of 3 characters ID of the system that ran the Query, like INF (Informatica), MST (Microstrategy), SLJ (SLJM), BO (Business Objects), AH (ad-hoc query tool)
• ENV: P (Production) / Tx (Test x) / Dx (Development x), the identifier of environment. x may be neglected, if it does not matter
• JOB: Which job or report contains that specific query (the name of it)
• STP: (Step) Which SQL script, or other sub-structure does the query belong to (name of it)
• VER: Version of the JOB. This will determine the version of the script (if available)

What’s a DBA to do?

http://www.teradatamagazine.com/v09n02/tech2tech/applied-solutions-3-whats-a-dba-to-do/

A DATA WAREHOUSE FROM TERADATA ELIMINATES MANY MANUAL TASKS TYPICAL OF OTHER SYSTEMS.

BY ROGER MANN

What do experienced DBAs with an Oracle, IBM or Microsoft background need to know about managing a Teradata system? Basically, much less than they need to know about the others.

The Teradata Database is a shared-nothing massively parallel processing (MPP) relational database management system (RDBMS), making it the only commercially available RDBMS designed from the ground up for data warehousing.

Parallel processing and the automation of many typical DBA functions were created in the DNA of the Teradata Database. Because of that architecture, many functions that are manual or enhanced by wizards in other vendors’ systems are managed automatically. Consequently, the roles and responsibilities of the DBA are significantly different. Fewer tasks are required, making the system much easier to manage. Understanding those differences and how to exploit them with the RDBMS is key to driving the success of the organization.

The next sections break down how a data warehouse from Teradata differs from other systems, enabling the DBA to focus on more productive work and less on manually maintaining the data warehouse.

SYSTEM INSTALLATION

Data warehouse performance is achieved in a parallel database architecture by the “divide and conquer” method. First, the data is divided into small, equal units. Then independent software modules process those units simultaneously (i.e., in parallel) to conquer the problems (i.e., answer the queries).

The units of work and the hardware resources allocated to each parallel software module must be as equal as possible. Like a chain that is only as strong as its weakest link, the overall job cannot conclude until every unit has completed its processing. This “balanced processing” of workloads in the Teradata Database enables superior query performance.

The DBA’s objective, therefore, is to install a balanced processing platform, operating system (OS), database management software and disk subsystems. In a typical environment, these steps require careful planning with time-consuming analysis of user data and targeted queries. Anxious for a quick return on investment (ROI), however, management too often applies pressure to take shortcuts—which can have disastrous consequences.

With a Teradata purpose-built platform, the OS, database and disk subsystems are installed before system delivery. All that is needed is the size of the raw user data, number of concurrent users and some targeted queries. This information is inputted into a system-sizing calculator and a platform configuration is recommended. The result is a balanced parallel-processing platform that is tailored to the organization’s application requirements.

A pre-configured, purpose-built Teradata platform relieves DBAs of the responsibility of understanding and setting the myriad OS and database parameters and installation options. No longer do DBAs have to spend hours writing programs to analyze the data and determine partitioning and data placement. They are free from the burden of having to understand and be responsible for setting the various options and initialization parameters with both the database and OS.

Those runtime database control parameter settings are critical for performance tuning of transaction processing applications where queries are predetermined and must be tuned. However, a data warehouse is built on the concept that users are able to ask any question of any data at any time. There is no opportunity to know or tune the queries beforehand. This is best left to the Teradata Database to optimize and tune dynamically.

In short, once the Teradata platform is installed, the DBA can immediately define the databases, users and tables and load data. Users can then leverage the data warehouse as it is intended—to run queries that answer their business intelligence (BI) questions.

STORAGE MANAGEMENT

Click to enlarge

Because the Teradata storage subsystem is installed and balanced before delivery, management of the disk subsystem is greatly simplified. The DBA familiar with managing items such as disk groups, logical volumes, node groups, file system, files and tablespaces will find that those entities and concepts are nonexistent. (See table.)

All disk organization is entirely logical, as opposed to physical. (See figure) Initially, all space in the system is allocated to a predefined system database called DBC. Using the “CREATE” DML command, the DBA will define DATABASE and USER entities. The space parameter on the CREATE DML statement is not a physical “allocation” but is simply a size quota. If a database is allocated 5TB of space that is the maximum amount of space the database is allowed to use. Anytime that database attempts to use more than 5TB, an “out of space” message will result. However, the system is not out of space, it just exceeded its space quota.

Click to enlarge

DISK FILE SYSTEM

A database management system (DBMS) is designed for storing and retrieving data. Typical file-system architectures fragment and performance degrades over time as insert, updates and deletes are applied to the data.

Teradata broke all the rules with its file system design. Data is not stored in B-Tree indexes based on data values; rather, the file system is built on raw disk slices. There are no pages, BufferPools, tablespaces, extents, etc. to manage. Row lengths are variable and stored in blocks that can grow or shrink on demand. Maximum block size is configurable with row or block placement managed by the DBMS. Rows can dynamically be moved, space reclaimed or the file system defragmented on the fly. This is a transparent background process that runs continuously using available system resources. Because of this process, the DBA never has to do a re-organization, and performance is optimized on a continual basis and does not degrade with file updates.

USER MANAGEMENT

Defining users is easy with a Teradata system. There are two types: query users with no workspace capability; and power users who have the capability to create and manipulate tables within their own workspace based on whatever limitations the DBA placed on their space usage.

The DBA first adds the users with a CREATE USER DML command, then grants them security rights to database entities. Role-based security is supported for ease of maintenance.

INDEX MANAGEMENT

DBAs need to resist the temptation to over-index the tables. Because of the powerful parallel architecture of the Teradata platform, it is unnecessary to avoid full-table scans. Therefore, far fewer indexes are needed than in other RDBMSs. In fact, as experienced in several organizations, tables having more than 80 billion rows each can be scanned in less than five minutes.

These recommended steps will help determine the number of indexes needed in the Teradata Database:

• The DBA defines a primary index (PI) and a secondary index on any column that will participate as a foreign key in join operations. The PI is for data distribution and keyed access.
• Once the indexes are defined, the query workload is run and the query capture facility logs the query activity.
• The Teradata Index Wizard uses this information to recommend the addition or removal of indexes based on actual query usage.

This process saves the DBA from having to manually analyze the number of indexes.

Special indexes are available for specific performance needs. The partitioned primary index (PPI), for one, can deliver dramatic results. A large transaction file that is accessed with date parameter queries is a good candidate for creating a PPI on transaction date. The Optimizer then can eliminate partitions on any date-sensitive queries with dramatic response-time reductions.

The multi-level PPI, join, aggregate and aggregate join indexes are tools that can turbo-charge certain applications.

WORKLOAD MANAGEMENT

Teradata Active System Management provides the necessary tools for comprehensive workload management; therefore, no outside tools or resources are needed. The product has three components:

Dynamic Query Manager enables the DBA to classify and govern the query before its execution in the database.

Priority Scheduler defines resource partitions where varying workloads can be controlled and monitored.

Database Query Log provides post-execution performance analysis.

Because Teradata tools administer all performance management and tuning needs, the DBA no longer has to be an expert in the OS, database and third-party tools.

SYSTEM EXPANSION

One common characteristic of successful data warehouses is growth. Often, no matter how much detail is put into planning a data warehouse, expansion needs often arise unexpectedly and in unanticipated areas. The scalable Teradata system makes growth an easy process. In fact, Teradata’s Customer Support Team performs the expansion—the DBA’s role is simply as an observer.

Expanding the Teradata system is similar to ordering the initial platform. With assistance from Teradata Professional Services, the DBA determines the amount of raw data on the existing Teradata system and the number of concurrent users, as well as a few critical queries. Then those numbers are added to the anticipated growth in each area. These values are input into a system-sizing calculator, which produces the additional platform requirements.

As with the original Teradata system, the additional platform and software will be pre-configured, delivered, installed and connected to the existing platform. The data redistribution utility is then run, which automatically rebalances the data on the system. The tool relocates any data that belongs on the new platform nodes had it been installed at the time the data was loaded. Once that data is relocated, the utility tool removes it from the old nodes. (No data movement occurs between the original nodes.) Redistributing the data requires downtime on the system, but the process normally takes less than a shift to complete.

A BETTER VALUE

The features of the Teradata system make it ideal for data warehouse applications. The balanced, purpose-built platform arrives ready to deliver the first application generally in days, instead of weeks or months.

With the automated data management features, DBAs are freed from having to micro-manage the file system and can, therefore, engage in other tasks and responsibilities. For instance, instead of the DBA constantly writing and tuning queries, the query optimizer allows the user to ask any question, anytime. The support and freedom provided by a data warehouse from Teradata empowers DBAs to concentrate on working with the user community to deliver greater business value to their organization.

WHAT TERADATA DBAS DON’T DO:

With the automatic features included in a Teradata Database, DBAs have fewer tasks and responsibilities for implementing and maintaining the system. As identified in this partial list of duties, Teradata DBAs have never been required to:

• Install an operating system (OS)
• Understand and set extensive OS tuning parameters
• Install the Teradata Database
• Understand and set extensive Teradata Database parameters
• Write programs/execute utilities that determine how to divide data into partitions
• Determine size and physical location of each table and index partition or simple tablespace
• Code/allocate/format partitions or underlying file structures
• Embed partition assignment into CREATE TABLE statements
• Determine level/degrees of parallelism to be assigned to tables/partitions/databases
• Assign and manage special buffer pools for parallel processing
• Associate tables/queries with parallel degrees
• Code/allocate/format temporary work space