Senior technical consultant
Teradata 14 Certified Master
Establish Good Habits for Technical Problem Solving
A Teradata 14 Certified Master shares solutions for row inserts in temperature-based block-level compression, secure zones and throttles, statistics on expressions, query demotions and more.
Breaking bad habits is hard to do. And establishing good ones can be just as challenging. Yet habits encompass almost every aspect of our daily work lives, from the beverage we drink in the morning to what we pull up on our computer screen to kick off the day to how we approach solving technical problems.
According to an old adage, “Good habits are formed; bad habits we fall into,” which is why I strive to develop positive behaviors. One advantageous habit I’ve cultivated over the years is retaining interesting technical questions that I have received and answered, and updating them for this column. And I hope you’ve developed the equally excellent routine of reading this column regularly.
Inserting Into Compressed Blocks
Can you tell me how row inserts work with temperature-based block-level compression (TBBLC) when the existing block is compressed? If we are inserting a greater amount of data than the block size, will the compressed block fill up and then create a new block, and if so, will that new block be compressed or uncompressed?
TBBLC, also known as Compress on Cold, was introduced in Teradata® Database 14.0. It works on a cylinder basis: If a cylinder is COLD, compress it; if it is WARM/HOT, uncompress it. If your table is defined with the
AUTOTEMP attribute, then the table will contain both compressed and uncompressed cylinders, depending on their access frequency.
When inserting new data, if the cylinder is compressed, then all data blocks on the cylinder will be compressed. Once a data block is full, another one will be allocated with the same compression setting as the other data blocks on the cylinder. When the cylinder runs out of space, a new cylinder will be allocated, and it will inherit the same temperature as the previous cylinder. Under these circumstances, a query band setting or default temperature will not be honored.
Secure Zones and Throttles
I’m on Teradata Database 15.10 and using SLES 11 virtual partitions and secure zones. Can you explain how utility load slots are allocated across those two features when they are used together?
These are two different features. A virtual partition throttle can be used to manage the combination of utility jobs and requests that are allowed to run at one time in the partition. If the throttles are in place, in order for a load job to run:
- The system-wide utility throttle counter (usually set at 30) must be below its limit.
- Any applicable utility workload throttles or group throttles must have their counters below their respective limits.
- The virtual partition throttle where the workload resides must also be below its limit.
The virtual partition throttle counts both requests and load jobs in combination (a load job counts as one slot), which is different from the utility throttle that manages only utility jobs.
Teradata Secure Zones, on the other hand, focuses only on access rights and access to data, and is not related to throttles. Currently, there is no method to associate virtual partitions or throttles specifically with a secure zone. Security management and workload management are completely separate approaches to managing the environment. (See figure.)
FinalWDID and Query Demotions
The TASM [Teradata Active System Management] Orange Book for SLES 11 and Teradata Database 14.10 mentions that if tactical requests exceed the “per node” thresholds, they can be “running in a different workload on one node (having been demoted), while all other nodes are running the request in the original workload.” What would be logged in
DBQLogTbl if fewer than all nodes demoted the query?
FinalWDID tells you the workload that the request is running in when it completes, so you should always have a
FinalWDID if workloads are active. However, only if TASM makes an exception across all nodes will
FinalWDID be changed to be different from
WDID, the workload in which the query started.
A per node exception is performed by Priority Scheduler—similar to the old query milestones—not by TASM. Because TASM doesn’t recognize a single-node demotion, it will not be reflected in Database Query Log (DBQL) or Monitor Session.
If the “sum across all nodes” value is greater than the [(“per node” setting) x (number of nodes)], then each node could get a per node exception and change the workload. However, that still would not be reflected by the
FinalWDID because it is not updated until the sum across all nodes value is reached.
I suggest setting the per node exception threshold to be the same as the sum across all nodes threshold. That way when a demotion happens on one AMP, it happens across all AMPs, and then TASM will be fully aware of the change. In addition, keeping the two thresholds the same eliminates the oddity of having the request running at different priorities on different nodes.
Statistics on Expressions
We encountered something unexpected when dealing with statistics on expressions. When we run
HELP STATS, the expression statistics are represented only by the name that was given to them. Why is that?
I need to ensure that we can correctly copy the statistics statements between systems (test to production, for example). The only way to get the actual expression value is to look in
DBC.StatsV, which creates additional work for the DBAs.
The reason why only the name that was given for the expression statistic is shown in
HELP STATS output is because expressions can be very long and complex. But it’s easy to see the actual expression using the
SHOW STATS command (or, as you say, by querying
For transferring statistics across systems (such as development to test to production), I recommend using the
SHOW STATS VALUES command, saving the output to a file and submitting it as a BTEQ script to the target system. This process copies the expression stats seamlessly.
This is an example of a statistics collection statement on an expression:
COLUMN (L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS RealPrice ON Lineitem;
Output from the
HELP STATS command:
HELP STATS LINEITEM;
Date Time Unique Values Column Names
15/07/13 11:03:09 59,736,188 *
15/07/13 11:03:09 40,168,593 RealPrice
Output from the
SHOW STATS command:
SHOW STATS ON lineitem;
COLUMN ( (L_EXTENDEDPRICE * (1- L_DISCOUNT ))* (1+ L_TAX )) AS RealPrice
CPU Accessing DBC
I have a SAP® application that frequently accesses the table
DBC.Columns in the data dictionary. The average CPU of the queries that use
DBC.ColumnsV view has increased from 1.75 CPU seconds to 2.25 after moving to Teradata Database 14.10 and SLES 11. Statistics are in place. I expected a slight uptick in CPU usage, but this has me concerned.
—Concerned in California
DBC.ColumnsV view has a broader scope in the 14.10 release. Two fields from the new
ObjectUsage table are now carried in the
Additional CPU is required when using that view because of the additional need to access the
ObjectUsage table and join to the other tables in that view, such as
DBC.TVFields. What you are experiencing is normal and should be expected.