Considerations When Logging DBQL Data | Teradata Vantage - Things to Consider When Logging DBQL Data - Analytics Database - Teradata Vantage
Database Administration
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-10-04
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantageā¢
Introduction
Prerequisites
Changes and Additions
Setting Up Your Administrative Environment: All DBAs
Logging in to the Operating System
Root Logons
tdtrusted Logons
Related Information
User DBC
Setting Up the Database Administrator User
Creating the Database Administrator Profile
Creating the Database Administrator User
Granting Database Privileges to User DBADMIN
Creating a Spool Reserve Database
Guidelines for Reserving Minimum Spool Space
Using Viewpoint Alerts
Setting Up Alert Actions
Creating Alerts
Setting Up Teradata Viewpoint Alerts for Space Usage
Global Default Parameters
Databases and Users in Teradata: All DBAs
Databases and Users
Space Used by Databases and Users
Creating Versus Owning Objects in the Hierarchy
Creating a Database or User
The System Users
System User DBC
System User SYSTEMFE
System User SYSADMIN
Other System Databases and Users
As-A-Service System Users
Recommended Hierarchy
Teradata Secure Zones Overview
Working with Databases: All DBAs
Database Creation
Best Practices for Database Creation
Creating the Tables Database
Creating the Views Database
Working with Table Access Privileges for Views
Dropping a Database or User
Transferring Ownership with GIVE
Working with Tables and Views: Application DBAs
Choosing a Primary Index
Guidelines for Choosing Primary Index Columns
Unique and Nonunique Primary Indexes
Row Partitioning
No Primary Index (NoPI) Tables
Relationship of Primary Indexes, Primary Keys, and Foreign Keys
Creating Tables
Converting Existing Database Tables to Vantage Tables
Considerations for Defining Tables
Creating Tables in Teradata
Copying a Table
Copying Statistics From a Base to a Target Table
Dropping a Table
Recreating a Table
Using INSERT ... SELECT
Specifying Fallback Tables
Working with Views
Creating Views Procedure
Example: Creating a View to Limit User Access
Example: Creating a View to Generate a Report
Related Information
Getting View Column Information
Using BTEQ Scripts to Create Database Objects
Related Information
Working with Stored Procedures and User-defined Functions: Application DBAs
Creating Stored Procedures
Example 1: Compiling and Executing a Stored Procedure with a Cursor
Example 2: Creating a Stored Procedure to Update Accrued Interest
Related Information
Creating User-defined Functions
Example: Creating a User-defined Function To Verify a Date
Related Information
Working with Users, Roles, and Profiles: Operational DBAs
Overview of Establishing Users
User Access Methods
Types of Users
Assessing Database User Needs
Best Practices for Creating Users
Creating User Accounts
Related Information
Creating Account Strings
Determining the Account for a User Session
Creating User Profiles
Profiles
Creating Profiles
Dropping Profiles
Working with Database Users
Types of Users
Creating Users
Creating Temporary Passwords for First Login
Using Roles to Manage User Privileges
User Types and Minimum Required Privileges
Types of Privileges
Privileges That Must Be Explicitly Granted
Creating User Roles
Granting Privileges to a Role
Related Information
Granting User Membership in a Role
Related Information
Defining the Default Role for a User
Related Information
Granting Privileges Directly To Users
Prerequisites
Example: Granting Privileges on Sys_Calendar to All Users
Related Information
Granting Privileges to a User
Automatic Privileges for Creators
Automatic Privileges for Created Users and Databases
Related Information
Viewpoint Monitoring Privileges
Setting Up Teradata Viewpoint Users
Related Information
Configuring User Dashboards
Creating a Dashboard
Loading and Exporting Data: Application DBAs
Teradata Parallel Transporter
Load Operator
Update Operator
Export Operator
Basic Teradata Query for Loading Tables
Differences Between BTEQ and Teradata PT
Updating Tables
Loading Data into Teradata Vantage
Related Information
Restarts and Aborts on BTEQ Jobs with Identity Column
Reading Committed Data While Loading to the Same Table
Cleaning up Load Isolated Tables
FastLoad Utility
FastExport Utility
MultiLoad Utility
Maximum Number of Concurrent FastLoad, MultiLoad, and FastExport Jobs
Utility Management
Teradata Parallel Data Pump
Restarts on TPump Jobs with Identity Column
Loading Geospatial Data
Loading Unicode Data with Unicode Pass Through
Interpreting LOAD Utility Status
Choosing the Best Utility for Your Purpose
Definitions
Guidelines for Inserting Rows
Guidelines for Deleting Rows
Guidelines for Other Batch Operations
Utility Job Performance Analysis and Capacity Planning
Examples of Using the DBQL Utility Job Log Table
Working with Sessions and Accounts: Operational DBAs
Session Modes and Transaction Processing
Transaction Processing in ANSI Session Mode
Transaction Processing in Teradata Session Mode
Setting the Session Mode
Related Information
Checking for Logged On Sessions
Obtaining Session Information
Troubleshooting Problems with Sessions
Working with Accounts
Purpose of System Accounting
Creating Accounts
Working with System Accounting Views
DBC.AccountInfoV
DBC.AMPUsage View
Logging Resource Usage Data with Account String Variables
Enabling Account String Expansion
Setting the Default Account String to Use ASE
Examples
ASE Substitution Variables
Restrictions and Usage Rules
Using ASE With Client Utilities
Managing Space: Operational DBAs
Types of Space
Capacity Planning
Global Space Accounting
Fixing Issues with Space Accounting
FixAllocatedSpace Procedure
FixCurrentSpace Procedure
Identifying and Correcting System-level Space Problems
Using the System Health Portlet to Find System Space Issues
Identifying Space Problems Using Viewpoint Space Usage Portlet
Identifying Space Issues by Querying System Views
Querying the DiskSpaceV View to Find System Space Issues
Querying the TableSizeV View
Use of the AllSpaceV View
Example Requests for Space Information
Transferring Ownership and Permanent Space
Transferring Permanent Space
Reallocating Perm Space to Resolve Space Issues
Increasing the Spool Space Limit for a User
Finding and Fixing Skewed Tables by Querying the TableSizeV View
Example of Finding Skewed Tables by Querying the TableSizeV View
Defining Temporary Space Limits
Protecting Transactions by Reserving Cylinders for Perm Space
Creating a Macro for Space Usage Reporting
Viewing Space Usage Trends for Databases and Users
Other Ways to View Space Utilization
Teradata Studio
The Ferret Utility and Equivalent Macros
Data Space Tool Macros
Managing Data Blocks
Setting Data Block Sizes Using SQL
Setting Data Block Sizes Using the DBS Control Utility
Setting Data Block Merging Limits
Managing Cylinders
Setting Free Space Percent Limits
Finding Loosely Packed Tables with the SHOWFSP Command
Freeing Cylinders Using PACKDISK
Giving One User to Another
Adjusting Perm Space Limits Using SQL
Adjusting Perm Space Limits Using Viewpoint
Maintaining the Database: Operational DBAs
Database Maintenance Tasks
Managing Accumulated Log Data
Database Logs
Methods for Maintaining Database Logs
Setting Up Automated PDCR Log Maintenance
Performance Data Collection Portlet Jobs
Related Information
Deleting Old Log Data with Teradata Viewpoint
Deleting Old Log Data Manually from System Tables
Managing Vproc Status and Initializing AMP Disks with Vproc Manager
Maintaining Data Dictionary Tables
Purging the System Logs
Clearing Out Values Manually in the DBC.Acctg Table
Archiving and Resetting Accumulators and Peak Values
Correcting DBC.DataBaseSpace and DBC.DBase Values
Collecting Statistics on Data Dictionary Tables
Data Dictionary Tables That Require Maintenance
Housekeeping on an Ad-Hoc Basis
Updating Software (Applying Proper Patches)
Archiving, Restoring, and Recovering Data: Operational DBAs
Archive/Restore Utility Support
Managing Database Resources: Operational DBAs
Managing I/O with Cylinder Read
Tracking Cylinder Read Resource Usage
Managing the Database Workload with Teradata Active System Management
Determining Workload Management Needs
Defining a Ruleset
Ensuring a Consistent Response Time for a Workload
Managing Sessions and Transactions with Query Banding
Defining Name-Value Pairs for Session and Transaction Query Bands
Example: Setting a Query Band For a Session
Example: Removing a Query Band From a Session
Example: Setting a Query Band for the Current Transaction
Defining Name-Value Pairs for Profile Query Bands
Example: Creating a Query Band in a Profile
Defining Profile Query Bands with Ignore Query Band Values
Example: Creating a Query Band in a Profile with Ignore Query Band Values
Query Bands and Proxy Users
Finding the Origin of a Query Using Query Bands
Other Uses for Query Bands
Related Information
Tracking Object Use and Table Changes
Objects That Can Be Tracked
Table Changes That Can Be Tracked
Ā Enabling Object Use Counts
Controlling How Often Counts Are Recorded
Views That Report Count Data
Resetting Use Counts
Usage Recommendations
Example: Object Use Counts for Tables
Analyzing Trends with Account String Expansion Variables
Resource Usage Trend Analysis
ResUsage Macros and Tables
Enabling RSS Logging
Managing Queries: Operational DBAs
Redrive Protection for Queries
Enabling Redrive Protection
Disabling Redrive Protection
Setting Redrive Protection for Users, Accounts, and Profiles
Redrive Restrictions
Example: Redrive Settings and Session Behavior
Example: Redrive Recommendations
Related Information
Recommendations for Common Query Problems
Identifying Poorly Performing Queries
Controlling Poorly Performing Queries
Finding and Fixing Skewed Tables
Finding and Resolving Lock Contentions
Lock Contentions
Investigating Query Blocks and Delays
Improving Query Performance Using COLLECT STATISTICS: Application DBAs
Statistics Collection
Required Privileges
Resource Considerations
Automated Statistics Management
When To Collect Statistics
Collecting Statistics
Recommendations for Large Tables
Related Information
Stale Statistics
SUMMARY Statistics
Skipping Unneeded Statistics Recollection with the THRESHOLD Option
Sampling Statistics with the USING SAMPLE Option
Example: User-Specified Sample Percentage
Example: System-Determined Sample Statistics
Related Information
Working with Special Cases
Collecting Statistics on Tables Protected with Row-Level Security
Collecting Statistics on Null
Collecting Statistics on the Data Dictionary
Tracking Query Behavior with Database Query Logging: Operational DBAs
DBQL Overview
DBQL Uses
How to Use DBQL
Overview of Collection Options
Best Fit Rule Hierarchy
Rules Validation
SQL Logging Statements
SQL Logging Considerations
XML Query Plan Logging
Capturing DBQL Through the Account String
SQL Logging by Workload Type
SQL Statements to Control Logging
BEGIN/REPLACE/END QUERY LOGGING Statements
WITH Logging Options
LIMIT Logging Options
Mode Logging Option
Setting DBQL Logging Algorithm
DBQL Macros
DBQL Tables
Querying DBQL Tables
DBQL Views
Shredding the Lock Plan Information in the XML Lock Log Table, DBQLXMLLockTbl
Shredding the Data in the DBQLXMLLockTbl
Output of Shredding
Shredded_LockTbl: Blocking Transaction Lock Shredding Target Table
Investigating Deadlocks: Querying Shredded_LockTbl
Shredded_TransTbl: Blocked Transaction Lock Shredding Target Table
Query Data Storage and Protection
Protection for a Committed Row
Things to Consider When Logging DBQL Data
Logging Overhead
Options for Flushing the DBQL Cache
Flushing the DBQL Caches Manually
Changing DBQL Cache Size
Changing the DBQL Performance Stats Cache Size
Logging Scenarios
Scenarios of Logging Accounts
Scenarios of Detailed Logging
Scenarios of Threshold and Summary Options
Example of OBJECT Data for One Query
Example of STEP Data for One Query
Examining the Logged Data
Comparing CollectTimeStamp Value Between Tables
Using QueryID for Joins on DBQL Tables
Zone-level Query Logging
Query Logging Privileges in a Zoned System
Related Information
Maintaining the Logs
Methods for Minimizing Log Table Size
DBQL Objects You Cannot Change
Archiving DBQL Data
Reviewing or Ending Current Rules
Reviewing Rules
Ending DBQL Logging for Specific Users and/or Accounts
Ending All DBQL Logging
Effects of Dynamically Enabling/Replacing/Disabling Logging on Current Rules
Granting DBQL Administrative Privileges to Other Users
Analyzing Requests and Request Plans: Application DBAs
Using EXPLAIN to Analyze Request Plans
EXPLAIN Request Modifier
Generating an EXPLAIN with Teradata Studio
Generating an EXPLAIN with BTEQ
Capturing Request Plan Steps
Query Capture Facility
Capturing Request Plans Using DBQL
Relationship Between BEGIN QUERY CAPTURE and BEGIN QUERY LOGGING
DBS Control Parameters for Tuning the Workload Cache Limits for Index and Partition Analysis
Query Capture Database
Creating the Query Capture Database Tables
Creating QCD Tables
Procedure Using BTEQ
Dropping Query Capture Database Tables
Dropping QCD Tables
Procedure Using BTEQ
Querying QCD Tables
QCD Request Macros and Views
QCD Macro and View Versions
User Categories
Specific User Category Privileges
Building Baseline Transaction Profiles
Working with System Information and Global Defaults: Operational DBAs
Viewing the Software Release and Version
Reviewing or Changing System-Wide Parameters
International Character Set Settings and Defaults
International Language Support Mode
Default Server Character Sets
Client Character Sets
Changing Character Set Defaults
Changing Collation Defaults
Changing Time Zones, Currencies, and Formats for Date, Time, and Numbers
Changing Time Zone Strings and Rules
Client Configuration Overview
Teradata Director Program for Mainframe-Attached Clients
Workstation-Attached Clients
Communicating with Teradata Vantage
Sessions Defined
Controlling Session Defaults
Client-Server Applications Connectivity
Mainframe Environment
Background
CP and CUA
Software Components
Mainframe Sessions
Session Pools
TDP Functionality
TDP Exits
Memory Management
Using TDP Commands
Network Environment
Functionality
Software Components
The Teradata Gateway
Displaying Network and Session Information
Controlling Network Sessions
Controlling Trace Logs
Related Information
Troubleshooting: Operational DBAs
Tools for Troubleshooting and Administrating
Tools for Troubleshooting Client Connections
Mainframe Connection Tools
Network Connection Tools
Troubleshooting Spool Space Problems
Using the Update Space Utility
Adjusting for Low Available Free Memory
Solving Partitioning and RI Validation Errors
Incident Information Checklist
Monitoring Transaction Recovery
Starting Recovery Manager
Stopping Recovery Manager
Recovery Manager Commands
Resolving Orphan or Phantom Spool Issues
Repairing Data Corruption
Using Maps to Position Table Data across AMPs: All DBAs
Map Overview
Scenarios for Using Maps to Move Table Data
Enabling Teradata MAPS Architecture
Initial Setup for Managing Maps
A Simple Process for Changing the Map for a Table
Viewpoint Portlet for Analyzing and Optimizing Map Placement
Introduction to the Advisor and Mover Tools
Enabling Step Logging To Aid in Map Analysis
Excluding Objects from Map Reassignments
Analyzing Maps
ActionsTbl
Reviewing Recommended Map Actions
Customizing the List of Recommended Map Actions
Renaming Maps after a System Expansion
Sessions Used for Reassigning Maps
Estimating the Time Needed to Reassign Maps
Decisions Required before Reassigning Maps
Reassigning the Map for a Table
Limiting the Resources for Map Reassignments
Stopping Map Reassignments
Stopping the Map Reassignments Gracefully
Stopping Map Reassignments Abruptly
Managing Restarts
Restart Cleanup during the Advisor Process
Restart Cleanup during the Mover Process
Other Cleanup Tasks
Handling a Second Contiguous Map after a System Expansion
Removing Historical Data
Adjusting Space Limits and Skew Settings
Performance Management: All DBAs
Implementing Performance Management
Performance Management Setup
Monitoring the System
Measuring System Conditions
Using Alerts to Monitor the System
Suggested Alerts and Thresholds
Weekly and/or Daily Reports
Collecting Statistics
Guidelines for Collecting Statistics
Collecting Statistics on Skewed Data
Collecting Statistics on Join Index Columns
PARTITION Statistics
Setting Up Database Query Logging
Performance Effects of Query Logging
Performance Information in DBQL Tables
Recommended DBQL Logging Scenarios
DBQL Collection Recommendations
Using DBQL Tables
Collecting Useful DBQL Historical Data
Managing Query Performance
Finding and Fixing Problem Queries
Alternate Query Investigation Methods
Managing Skewed Processing
Skewed Query Processing Across Nodes
Skewed Query Processing Across AMPs
Identifying and Fixing Skewed Tables
Performance Effects of Skewed Row Distribution
Identifying Skewed Distribution of Table Data
System Tables Subject to Skewing
Using Teradata Viewpoint to Find Skewed Tables
Finding Skewed Tables in DBC.TableSizeV
Finding Uneven Distribution Using Hash Functions
Primary Index and Skewed Row Distribution
Identifying and Managing Blocked Queries
Locking Overview
Investigating Query Blocks and Delays
Reducing Deadlocks
Handling Blocked Internal DBQL Requests
Working with Transaction Rollbacks
Effects on Performance
RollbackPriority
Minimizing or Avoiding Rollbacks
Detecting a Rollback in Progress
Example
Canceling Rollbacks
Noticing a Slow Rollback of an ALTER TABLE Statement
Managing the Database Workload
Evaluating Peak Utilization Periods
Assessing a Busy System
Job Scheduling Around Peak Utilization
Using Canary Queries
ASE Impact on PE and AMP Performance
Workload Priority Management
Charge Back
Managing Database Resources to Enhance Performance
Performance and Space Management
Managing System Disk Space
Managing Spool Space
Managing Cylinders
Freeing Space on Cylinders
Managing I/O with Cylinder Read
Managing Free Space
Determining a Value for FSP
PACKDISK and FreeSpacePercent
Data Compression and Performance
Managing Data Block Usage
Performance and Memory Management
Shared Memory
Memory-Consuming Features
Investigating the Need to Add Memory
ResUsage and Cache Hit Rates
Using Memory Effectively
Managing Free Memory
Managing FSG Cache
Using DBS Control Fields to Manage Memory
Periodic Maintenance and Performance
Cleaning Up Logs
DefragLowCylProd
Capacity Planning
Monitoring CPU Usage
Monitoring Disk Utilization
Monitoring Traffic
Performance Considerations when Expanding or Upgrading the Database System
Adding Disk Arrays
Adding Vprocs
Adding Memory
Scaling
Checking the Performance of an Upgrade
Teradata System Limits
System Limits
Database Limits
Session Limits
Handling Teradata Crashdumps: Operational DBAs
Teradata Vital Infrastructure and Crashdumps
TSET File Pack and the DBC.TSETQueryText Table
Crashdumps Overview
Dump Types
Teradata Crashdumps Processes
Teradata Crashdump Formats
Teradata Crashdump Sizes
Viewing Teradata Crashdump Messages
Locating Crashdumps
Configuring the Crashdumps Database
Ad-Hoc Creation of DBC.Crashdumps with DIPCRASH
Checking DBC.Crashdumps Space Allocation and Usage
Allocating More Space to DBC.Crashdumps Database
Automatic Versus Forced Crashdump
Automatic Teradata Crashdumps
Forced Teradata Crashdumps
Commands for Forcing a Teradata Crashdump
Handling Teradata Crashdumps
Save Considerations
Listing Teradata Crashdumps Using CSP
Manually Saving Teradata Crashdumps to the Crashdumps Database
Manually Saving Crashdumps to Another Teradata System
Manually Saving Teradata Crashdumps to Stream Files
Handling an Interrupted Dump Save Operation
Error Logging
Saving Crashdumps to Disk
Sending Crashdumps to Teradata
Debugging Crashdumps
Attaching a Raw Dump and Displaying the Backtrace
Deleting Teradata Crashdumps
Fault Isolation Diagnostics
Troubleshooting Crashdumps
Additional Information
Teradata Links
You must have EXECUTE privilege on the DBC.DBQLAccessMacro macro to log DBQL
data. To grant the EXECUTE privilege to an administrative user, see Granting DBQL Administrative Privileges to Other Users .
Teradata recommends that you use BTEQ script files to enable DBQL. You can define the rules for logging in these scripts. Just be sure to keep the scripts up to date and saved for future use. You can also create files that disable query logging for your convenience.