Tech2Tech
Applied Solutions 2
Keeping Track
Query banding provides detailed workflow information.
by Debbie Galeazzi
Scientists will often band the legs of birds with devices to track their flight paths. Monitoring and analyzing the data retrieved via the bands provides critical information about the species.
The same process holds true for DBAs who need more information about a query than what is typically available. Metadata—such as the name of the requesting user, the work unit and the application name—is important for, among other purposes, workload management, tracking the use of the data warehouse and query troubleshooting.
The sort of details provided through metadata can be linked to the query using the query banding feature in the Teradata Database. A query band contains any number of name/value pairs that use reserved or custom-defined names to set, for instance, the initiating user’s corporate ID, department name and location, the name and version of the application, and the time the initiating thread of execution started. These query identifiers can be included in workload management rules and in applications and then captured in the Database Query Log (DBQL), where they are used to analyze the work flowing through the system (See figure 1).
Click to enlarge
Tag queries
Query banding is especially valuable when applications use connection pools to send queries to the database. Because each query session in a connection pool is tied to the database with the same user ID, all standard metadata is identical. Adding a query band that stores the detailed metadata enables various departments and units to identify the unique attributes of each request. For instance, DBAs can track the user or application of each request for chargeback purposes and associate users with requests for security measures.
Teradata Dynamic Workload Management also uses query banding to identify on a more specific level the queries that should receive a higher or lower priority. Concurrency levels are adjusted by assigning different workload rules. System administrators use query banding for troubleshooting to identify queries that are running too long or those that have created a system backlog. The information typically available in queries does not offer this sort of refinement.
Determine usage needs
Before query banding can be applied, users must first strategize how it will be employed, then design the query band names and values. Query bands may be set with the specific values (indicated in parentheses) and used to:
- Identify the origin of application requests made via connection pools (ClientUser, Group, ApplicationName, Source and Action)
- Associate all requests in a job (JobID, JobLen and JobSeq)
- Adjust the priority of a job (Importance level)
The standard set of query band names is defined for use by Teradata enterprise applications, as well as by the applications of customers and partners. (See table.) The list of reserved and optional query band names will provide additional consistency.
Examples of query band name/value pairs are:
– ApplicationName=InventoryApp;
– Version=01.00.00.00;
– ClientUser=dg120444;
– JobID=998;
Banding
The query band is set by using the “SET QUERY_BAND” SQL statement:
SET QUERY_BAND = ‘ApplicationName=InventoryApp; Version=01.00.00.00;’ FOR SESSION;
The session query band is stored in the session table and applied to each request. It remains set for the duration of the session or until the query band is replaced by another. In case of a system reset, the query band is recovered.
A query band can also be applied to all of the requests within a transaction. When the application requests a connection from the pool to perform a service for a client user—for instance, saving data to the database—it sets the transaction query band with attributes specific to that service request, as follows:
SET QUERY_BAND = ‘ClientUser=dg120444; Group=Sales; JobID=998;’ FOR TRANSACTION;
When the transaction completes, the transaction query band is automatically discarded. Therefore, no cleanup is required before reusing the connection for another service request.
Click to enlarge
Takeoff
As mentioned earlier, query banding provides an additional means to prioritize jobs. Before each request is submitted, the application sets the query band with the appropriate “Importance” value. Teradata Dynamic Workload Management uses this value to assign the requests into the corresponding workloads. The workload defines the request’s priority, concurrency values, exception handling, throttle limits and goals.
Teradata Dynamic Workload Management then analyzes the query band to determine whether the conditions are favorable to initiate a complex query in the database. Using established filter rules, the database will reject requests based on query band attributes. Examples of these filter rules are:
- Reject requests running between 8 a.m. and 5 p.m. that have an estimated processing time greater than five seconds and a query band containing the Group=Accounting name/value pair.
- Reject requests running between noon and 2 p.m. that have an estimated number of rows greater than 1 million and a query band containing ApplicationName=Inquiry.
- Reject requests between midnight and 5 a.m. with a query band containing ApplicationName=Sales.
If the query is OK to run, Teradata Dynamic Workload Management uses the query band to determine which workload will run the request.
Because applications that use connection pools have the same standard metadata for each session, they will be assigned to the same workload. By adding specific query band classifications, different types of queries will be directed to different workloads. For example, the application can set a query band to assign interactive requests to a high-priority workload and set a different query band that will assign background requests to a low-priority workload. Adding priority levels to query bands consists of:
- Creating a high-priority workload in Teradata Dynamic Workload Management that classifies requests with a query band of “Importance=OnlineTactical”
- Establishing a second workload that runs at a low priority and classifies requests with an “Importance=StrategicBatch” query band
Applications also use query bands to direct requests to workloads with different throttle limits. This is useful when some types of queries are more resource-intensive than others.
In flight
Teradata Manager enables DBAs to view a display of the query band along with all other session details and track the request’s progress. (See figure 2.) This is helpful for troubleshooting problems. For instance, if requests are blocking other jobs or consuming a large amount of system resources, the DBA can identify the users through the query band and send them notification that the requests will be aborted, or notify the developer that an application needs modification.
The query band values are extracted from the Teradata Database along with the data used in applications. The Teradata Database provides Library functions to perform:
- FNC_GetQueryBand. Retrieves the current query band
- FNC_GetQueryBandValue. Obtains the value for a specified name in the current query band
- FNC_GetQueryBandPairs. Returns the current query band names and values in an array
These tasks enable organizations to write customized user-defined functions (UDFs) and external stored procedures (XSPs) using logic based on the query band values for:
- E-mailing a job completion status to the submitting user identified by the ClientUser value
- Writing a row to a log table based on the Group value so that each group has its own separate log table
- Extracting JobID, JobLen and JobSeq values and writing a row to a queue table so that a job scheduler program is able to retrieve the row and schedule the next job in the sequence
Click to enlarge
Landing
DBQL is used to record query processing activity. When Query Logging is enabled, the query band for each request is written to the DBQlogTbl table.
For chargeback, accounting and other types of system management, administrators find it useful to have the additional data that is provided with the query band, such as corporate user ID, department and report name.
If an application has set the query band with ClientUser name, for example, users can extract resource usage reports from DBQL based on this information:
SEL SUM(AMPCPUTIME), SUM(t1.ParserCPUTime)from dbc.qrylog t1 where
t1.QueryBand is NOT NULL AND GetQueryBandValue(t1.queryband, 0,
‘ClientUser’) = ‘MW_DCollins’;
SUM(AMPCPUTime) SUM(ParserCPUTime)
-------------- -------------------
0.016 0.062
In addition, if the JobID is set in the query band, users can extract information from all requests for a specific job. This will enable users to calculate the total resources used or compare before- and-after performance statistics when making changes.
Delivered value
Query banding provides an extensible mechanism to enable users to delve deeper into the details of an application’s queries. The valuable data gained through query banding will help improve the database management, system resource accounting and workload management of the Teradata Database.
Debbie Galeazzi has been a software engineer at Teradata and NCR for more than 28 years.