Carrie Ballinger, Senior Technical Consultant, Teradata

Carrie Ballinger
Senior technical consultant
Teradata 14 Certified Master

Tech2Tech

Tech Support

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

Dear Carrie:
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?
—Inquiring Inserter

Dear Inquiring:
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

Dear Carrie:
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?
—In Security

Dear Security:
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

Dear Carrie:
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 FinalWDID in DBQLogTbl if fewer than all nodes demoted the query?
—Threshold Theorist

Dear Theorist:
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

Dear Carrie:
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.
—Expressionless

Dear Expressionless:
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 DBC.StatsV).

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:

COLLECT STATISTICS 
    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;

COLLECT STATISTICS 
	COLUMN ( (L_EXTENDEDPRICE * (1- L_DISCOUNT ))* (1+ L_TAX ))  AS RealPrice 
		ON CAB.lineitem; 

CPU Accessing DBC

Dear Carrie:
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

Dear Concerned:
DBC.ColumnsV view has a broader scope in the 14.10 release. Two fields from the new ObjectUsage table are now carried in the DBC.ColumnsV view: UserAccessCnt and LastAccessTimeStamp.

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.


Your Comment:
  
Your Rating:

Comments
Fuzzy Logix