Statistics are essential for query optimizer to generate an optimal access plan. The runstats utility collects necessary statistics and optimizer makes use of these statistics during query compilation. In this blog, I would like to briefly describe different ways in which a user can collect statistics.
Manual runstats:
User can execute the runstats command manually or through admin command with appropriate option to collect statistics on a particular table and/or index. Refer to the following link to know various options supported under runstats to gather table and index statistics.
Deciding which statistics to collect for a workload is complex, and maintaining up-to-date statistics is time consuming. Out-of-date or incomplete table or index statistics might lead the optimizer to select a sub optimal plan, which slows down query execution.
To overcome this situation in version V8.2 we introduced a concept called automatic table maintenance, in short we address this as auto runstats.This feature is enabled by default in which, database manager will take care of maintaining up-to-date statistics for tables automatically.
Auto runstats evaluation happens at every 2 hrs,5 min of time interval. It examines each table under active database to consider for statistics collection . If table statistics are missing or stale it will decide to collect statistics during the next maintenance window. By default the maintenance window will be set to 24x7. User has the privilege to set maintenance window depending on the system workload. One can also control statistics collection by setting maintenance policy. For example, If you want to exclude nicknames from automatic statistics collection process, then you can set a policy to accomplish the task.
User can always deactivate auto runstats by setting db cfg parameter AUTO_RUNSTATS=OFF.
RTS (Real Time Statistics):
This feature was first introduced in version v9.5. It will collect statistics synchronously at statement compilation time. This feature is enabled by default when you create a database. Query optimizer determines how statistics are collected based on query need and table activity (amount of update/insert/delete operation). RTS collects statistics during statement compilation there by providing more timely and accurate statistics for the optimizer which results in better query execution plan.
RTS collects statistics via synchronous/fabrication and make it available immediately to optimizer by updating it in statistics cache.
RTS daemon evaluate every 5 min of time interval and process any pending request from previous runstats activity Ex: Writing statistics cache value to disk or collecting statistics asynchronously.
User can always deactivate RTS by setting db cfg parameter AUTO_STMT_STATS=OFF
learn more about automatic table maintenance process (Auto runstats and RTS) in the following link.
User can collect statistics during 'LOAD' by specifying 'STATISTICS USE PROFILE'. To collect statistics via LOAD, one has to create a profile using runstats command before executing LOAD. To know more on how to create runstats profile refer to the link mentioned under 'Manual runstats'.
Similar to LOAD command, user can collect statistics via 'REDISTRIBUTE' by specifying 'STATISTICS USE PROFILE' option. User should first create a runstats profile before executing REDISTRIBUTE command to collect statistics.
DB2 Text Search supports 2 types of server configuration - integrated and stand alone server( DB2 V10 onwards). By default Text Search installs an integrated text server. Text Search also supports a standalone text server which can be configured on an independent machine other than the DB2 server. What this allows customers is, to separate out the resource intensive text index maintenance and free resources on the DB2 server.
From DB2 V105 fp4 onwards Text Search allows users to configure multiple standalone servers for each DB2 instance. This scalability is extremely beneficial especially in cases of a multi-node DB2 setup, where DB2 Text Search customers were limited to use only one TS server for processing the data from all DB2 nodes. With support for multiple standalone servers for Text Search, TS users can decide the server for each of the TS indexes at index creation and willfully distribute the indexes. The users can distribute the TS indexes based on the index usage , index size and machine capability. This flexibility enables TS users also to scale better on a multi-node setup and not limit their text indexing workload of all DB2 nodes on a single TS server.
The below steps will guide to setup DB2 Text Search server with multiple standalone servers.
Install standalone Text Search servers on required systems.
At minimum, configure the following on the standalone Text Search server using the configTool:
configureHTTPListener: Configures the DB2 Text Search server port and host name
generateToken: Generates the authentication token and encryption key
defaultDataDirectory: Configures the parameters for the collection
once standalone server is installed, get the port, token , key details for the standalone server by running configTool
Eg: ./configTool printAll -configPath <pathToConfigFolder>
Update text search server details in SYSIBMTS.TSSERVERS administrative view:
Eg: db2 "insert into SYSIBMTS.TSSERVERS(HOST,PORT,TOKEN,KEY,LOCALE,SERVERTYPE,SERVERSTATUS) values('<hostname>',<portNumber>,'<token>','<key>','<localeName>',2,0)"
The server configuration of the setup is now complete. Repeat the above steps for all standalone servers to be added to the setup.
The create index statement has a new option SERVERID to specify the the server details. The serverid should correspond to a valid standalone server , details of which should be available in SYSIBMTS.SYSTSSERVERS.
Eg: To create an index on serverid as 3 in SYSIBMTS.SYSTSSERVERS issue create index command as below:
db2ts "create index db2ext.index for text on test(text) serverid 3"
NOTE: When no value is specified for serverid option in the create index statement, serverid 1 is the default server and index will be created on this server.
serverid is not required to be specified with the update index or the search statements.
The serverid corresponding to the index can be found in the SYSIBMTS.SYSTSINDEXES.
On Feb 1st, 2012, I gave a seminar on my experience working with IBM Optim Tool. This session was well attended;
In the presentation, I talked about my expriences with the product and the way we customised the tool for our needs. The most intriguing thing about IBM Optim tool is that how well it binds with the backend database. We used DB2 for zOS but I'm sure Optim is as well integrated good enough with other databases.
We were able to implement a comprehensive Archial and Purge Solution in less than 3 months. This is a tremendous achievement keeping in mind that everyday data archival was in the range of about 0.3 million rows!
Do share your thoughts, experiences on IBM Optim tool in this blog.
Do you face issues with automatic runstats? Do you have questions on what criteria automatic runstats evaluates tables? Do you wonder how to get the current UDI (Update/Delete/Insert) counter, catalog cache and statistics cache information? If yes,then you are at the right place. In this blog I will introduce you to different db2pd options which can be used to gather information about TCB (Table Control Block), Statistics Cache and Catalog Cache.
db2pd -tcbstats:
This will generate UDI and RTSUDI counter information. These counters are referred by auto-runstats and RTS to make decision on statistics collection. You can get the TCB information by executing ..
db2pd -d test -tcbstats
db2pd -statisticscache:
Statistics collected by RTS will be stored first in statistics cache before writing into system catalog tables. If you face issues with the RTS causing incorrect stats,memory leak or crash during statistics collection/Hardening (writing the cached values to catalog tables) process, then analyzing the statistic cache will help to narrow down the issue.
Ex:
db2pd -d test -statisticscache
Specify 'detail' clause to get more information which includes formatted packed descriptor output.
Ex:
db2pd -d test -statisticscache detail
db2pd -catalogcache:
Statistics cache is part of catalog cache. Understanding details of catalog cache will sometimes helps to narrow down issues with statistics cache.
Ex:
db2pd -d test -catalogcache
db2pd -catalogcache:
Statistics cache is part of catalog cache. Understanding details of catalog cache will sometimes helps to narrow down issues with statistics cache.
Hi All,
HADR Tutorial Part 10 - Table Space Restore on Stand By Not Working.
New Feature in 11.1 and above, but surprisingly not working.
Did I Miss Anything?
Hi All,
HADR Tutorial Part 9 - LOAD Index ReBuild can solve Global Warming Crisis.
LOAD from NULL Device with Indexing Mode Rebuild is a faster alternative to LOG INDEX BUILD.
Hi All,
How Backup Image bloated with Transaction Log Files
Oldest Application Id - MON_GET_TRANSACTION_LOG()
Corresponding First LSN - db2pd -transactions
Corresponding Log File - db2flsn command
Hi All,
How STANDALONE to ESE is not supported during FixPack Update but supported on Instance Upgrade
Use -k option to Maintain Instance Type
Drop STANDALONE Instance; Re-Create as ESE; Catalog DB;
Hi All,
How Conversion of Regular to Large Tablespace Impacts Recovery
MRT Update impacts PIT Recovery
Establish New Baseline Backup - Database/Tablespace Backup
Hi All,
How To Map Table File Object Id with Table Name in SMS Table Space
Use:
TABLEID Column in SYSCAT.TABLES
TAB_FILE_ID Column in MON_GET_TABLE Function
TAB_FILE_ID Column in SYSIBMADM.ADMINTEMPTABLES View
db2pd -db dbname -tcbstats tbspid
How Archive Log Start End Time in List History Output is counter intuitive
How To Find How Long a Transaction Log File is in Active Log Directory before getting Archived
Hi All,
How Classic Offline Reorganization consumes more Transactional Log Space
Solution:
Provide Additional Transactional Log Space for Classic Clustered Reorganization of Table
Hi All,
How Table Data and Indexes are not allowed in separate SMS Tablespaces
Solution:
Use DMS or Automatic Storage Tablespaces
SMS Tablespaces for storing Table Data is Deprecated
Hi All,
How DMS Temporary Tablespace reduces concurrency of Reorganization
Solution:
Use SMS Temporary Tablespace for Reorganization
Do not use option USE in REORG Command
Hi All,
How To Reorganize Index without using REORG Command
Solution:
Set MINPCTUSED attribute during Index Creation
Truly - Online Index Reorganization
Hi All,
How db2 get instance displays incorrect instance
How to Switch between multiple instances
Solution:
Execute /home/db2inst1/sqllib/db2profile for switching instances
Issue db2 terminate when switching between multiple instances
So you are smart and have a heart of Gold. You want to use your talent for social good but do not know how or where to start.
How about we, give you a chance to showcase your smartness for a social cause and let you win dollars too!
Sounds exciting? Read on.
Natural or man-made, disasters bring chaos. developerWorks gives you a chance to put your coding and testing skills to become a part of the rescue team from miles away. Participate in the contest and help ease the communication and logistics breakdown that happens when disaster stricks. Use your own idea or copy and improvize someone elses.
Hi All,
How Load Utility consumes Huge Transactional Log Space
Solution:
LOAD using valid Data Sets without redundant records
Increase LOGSECOND DB CFG parameter
IMPORT / INGEST with COMMIT COUNT
Hi All,
How To Restrict Load Utility during Online Database Backup
Solution:
Define Work Class for Work Type - LOAD
Define Work Action "Prevent Execution" for LOAD Work Class
Hi All,
How To Restrict Non Unicode clients accessing Unicode Database.
Solution:
Create Connections Table based Event Monitor.
Create Stored Procedure Raising Error for Non Unicode Clients.
Update connect_proc DB CFG parameter.
Hi All,
How To Export Import Catalog Entries from one Instance to Another.
Solution:
Use DB2CFEXP for Export and DB2CFIMP for Import of DB2 Directory,Registry Variables and DBM CFG values.
100
