The developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this blog will no longer be available. More details available on our FAQ.
Why DB2 LUW online reorg is getting paused on it's own ?
A common question comes about why an online reorg is getting paused even if nobody manually tried to pause it. There is a manual way to pause and resume online reorg with the help of commands, reorg table <tab-name> inplace pause reorg table <tab-name> inplace resume When an online table reorg operation is paused, one cannot run a new reorganization of that table. One must either resume or stop the paused operation before beginning a new reorganization process ... [More]
|
Distribution statistics for Federated tables.
Statistical data for DB2 Federated tables or Nicknames is gathered using the SYSPROC.NNSTATS procedure, documented here for V10.5 of DB2 : http://www.ibm.com/support/knowledgecenter/en/SS2K5T_10.5.0/com.ibm.swg.im.iis.fed.query.doc/topics/tfpsts03.html The statistics that are gathered for nicknames is not the same as for regular DB2 tables. See also : http://www.ibm.com/support/knowledgecenter/en/SS2K5T_10.5.0/com.ibm.swg.im.iis.fed.query.doc/topics/cfpsts01.html But the statistics most notably do not contain distribution information (... [More]
Tags: ibm-blog |
DB2 The basic rules of DB2 archival logging.
The basic rules: 1. When a transaction log is full and/or closed db2 will archive the log (assuming an archiving method is defined in LOGARCHMETH1). 2. When a transaction log no longer contains an open unit of work (i.e. it's extent number is less than the First Active Log) and it has been archived successfully it can be renamed and reused. 3. As a general rule, the minimum number of logs allocated at database activation is LOGPRIMARY. The maximum number of logs that will be allocated to handle a large transaction is LOGPRIMARY + LOGSECOND.... [More]
|
DB2 reorg table command: Subtle syntax has huge effect on how reorg is performed
The primary reason to perform a reorg of a table is to release unused space from a table back to the tablespace. This applies to both SMS and DMS tablespaces. With a SMS tablespace the free space will be returned to the filesystem. With a DMS tablespace the free space will be returned to the tablespace as unused extents. There are 3 main types of reorgs 1) offline reorg: db2 reorg table t1 Summary: During an offline reorg all non deleted rows will be copied to a shadow table in the same... [More]
Tags: table db2 reorg |
DB2 DPF rollforward with overflow log path
I have found that there have been some questions lately about how to apply the logs that you have extracted from your partitioned restore (using the LOG TARGET parameter), so I wanted to provide a simple example. During a DPF restore you can specify the LOG TARGET in the individual partition restore commands (Example: /test_backup/test_restore_logs) and then use those paths for your rollforward command at the end. Here is a simple example of a rollforward command for a simple 9 partition (catalog + 8 data partitions) environment with... [More]
Tags: rollforward overflow dpf |
DB2 Backup performance: how named pipes can help us if compression is the bottleneck?
We all know how important it is to have the data spread across few tablespaces in order to speed up the backup. DB2 utilizes IO parallelism during the backup by processing multiple tablespaces at the same time (BACKUP ... PARALLELISM <N>). E.g. if you would have 10 tablespaces and go with parallelism 5, DB2 will spawn 5 buffer manipulator EDUs (db2bm) that will read 5 tablespaces in parallel (with the help of prefetchers). Once given db2bm is done with a tablespace, it will pick up the next one from those, which were not backed up yet.... [More]
Tags: db2 backup compression ibm-blog |
Why there are exclusive locks on system tables after changing value of INTRA_PARALLEL
The Problem: After changing INTRA_PARALLEL from YES to NO and restarting the server, execlusive locks are observed, like: lock_mode lock_type schema.table #locks ------------------------------------------------------------- Intention Exclusive Lock TABLE_LOCK SYSIBM.SYSPLAN 1 Intention Exclusive Lock TABLE_LOCK... [More]
Tags: performance wait contention intra_parallel lock |
Why DB2 returns inaccurate SUM result on double column?
Question: Why the last query as below returns +6.60619999999999E+002 instead of +6.60620000000000E+002? $ db2 "create table tmp_day_act( avge_amount double)" $ db2 "insert into tmp_day_act values(-1020)" $ db2 "insert into tmp_day_act values(-19053.67)" $ db2 "insert into tmp_day_act values(29869.35)" $ db2 "insert into tmp_day_act values(-258.62)" $ db2 "insert into tmp_day_act values(-8876.44)" $ db2 "select sum(avge_amount) from tmp_day_act" 1... [More]
Tags: decfloat float inaccurate sum double |
What's a sustained trap?
In some cases, you may see an EDU traps (e.g. with signal 11), FODC_Trapxxxxx folder is created in db2dump, the corresponding application receives SQL1224N indicates its connection is forced off, but the subsequent application(s) can still make connections to the database. From db2diag.log, you can find: 1. Trap Sustainability Criteria Checking = true 2016-06-02-11.57.40.871392+480 I5041137A562 LEVEL: Severe PID :... [More]
|
Understanding the scope of db2trc command in DPF environment
1. By default, that is to say turning on db2trc without any location-options, the scope is the current host on which the db2trc command is issued, accordingly db2trc is turned on all partitions in the current host. 2. To turn on/off db2trc for all partitions including the partitions on remote host(s), you can use rah: rah "db2trc on" rah "db2trc off" You can also use location-option '-global' to reach the same goal: db2trc on -global Note that option '-global' is deprecated in DB2 Version 9.7 Fix Pack... [More]
|
How to find out which member a row exists?
You can use the DBPARTITIONNUM scalar function to find out which member a row exists. Example 1: Find out which member a row with EMPNO='000050' exists: $ db2 "select dbpartitionnum(EMPNO) partition_number from employee where EMPNO='000050'" PARTITION_NUMBER ---------------- 3 Example 2: Find out the member on which MICHAEL THOMPSON's employee data is stored: db2 "select dbpartitionnum(EMPNO)... [More]
Tags: member partition row dbpartitionnum |
How to find record length in a table in DB2 LUW
One way to find record length in a specific table in a specific schema is to run following query, $ db2 "select tabname, sum(length) from syscat.columns where tabschema='<schema>' and tabname='<table-name>' group by tabname" Example with a DB2 LUW sample database table : $ db2 describe table db2inst1.org... [More]
Tags: record db2 length |
Why a tablespace rebalance is not initiating ?
Altered a tablespace to add space and then trying to rebalance the tablespace with command like, db2 alter tablespace <tablespace> rebalance But, the "db2 list utilities show detail" or, "db2pd -utilities" does not show any rebalance is running. The tablespace shows the status to be, Tablespace State = 0x'10000000' Detailed explanation: DMS rebalancer is active Following two command run successfully, ALTER TABLESPACE tablespace_name... [More]
|
When is space from a deleted row reused by DB2
Space from a deleted row is eligible for reuse as soon as the transaction with the delete statement is committed. When that space is reused for future inserts depends on how DB2 searches for free space. DB2 searches for free space by taking advantage of Free Space Control Records (FSCR). Each FSCR tracks the free space for 500 pages. By default DB2 will search 5 FSCR records. That means a total of 2500 pages will be searched before giving up the search for free space and just appending the row to the... [More]
Tags: reused db2 fscr delete |
Why IIS worker process w3wp.exe is experiencing "Access is denied." in DB2 ?
One example of the access denied issue from w3wp.exe could be experienced with following db2diag.log messages, 2016-06-02-02.35.36.707000-300 E391509H442 LEVEL: Error (OS) PID : 6404 TID : 6984 PROC : w3wp.exe INSTANCE:... [More]
Tags: createdirectory db2 -2097151995 w3wp.exe |
Identifying Subsection Bottlenecks in DB2 DPF Queries
Queries in partitioned (DPF) environments generally have multiple subsections executing concurrently, with each subsection executing on one or more database partitions. As a result when a DPF query is performing poorly it can be important to identify which subsection or subsections may be holding up the progress of the query. For long running DPF queries, explain (db2exfmt) information and global application snapshots provide very useful information which can be used for this purpose. These... [More]
Tags: subsection db2 performance query dpf |
Measuring latching overhead with monitoring functions in Db2
what is a latch? Db2 is a multi threaded application written using the imperative programming model. Like any other multi threaded application, Db2 has its own mechanism to protect the common address base shared among the threads. It can be explained by the below given diagram: - In the diagram, two threads are updating the same structure in the memory but the consistency is kept by latches. Next question for any Db2 DBA would be, what is different than lock/wait? It is an internal implementation of Db2 to protect... [More]
Tags: latches db2concurrency |
Cardinality underestimation in the DB2 Query Compiler due to overloaded dimension tables
In a star schema the fact table links to a number of dimension tables. The row in the fact table will have a column with an identifier mapping to a specific row in one of the dimensions. This column will be used to join back to the dimension table in SQL statements. When the DB2 SQL Query Compiler, a.k.a. the optimizer, determines an estimated result set for a join, it will consider the cardinality of the join columns in each of the tables that are joined. The cardinality of a column is defined as the number of unique values for that column... [More]
Tags: underestimation view db2exfmt db2caem cardinality ibm-blog statistical |
How to check the LOB column I/O performance
Have you ever been curious about the way to verify if a LOB column I/O with a table uses file system cache or NOT ? There are common two perception points regarding LOB. 1. LOB uses file system cache. That means it does NOT utilise the buffer pool. 2. By setting with inline LOB, it uses buffer pool.. Above things may or may not be true. For example, one of my friend had the following doubt. "Does LOB I/O utilize O/S File system caching even though tablespace definition for LOB has 'NO FILESYSTEM CACHE'." ? The reason behind... [More]
Tags: performance lob ibm-blog db2 |
Database Manager and Database configuration parameters affecting query plan
The performance of the query in DB2 can be affected by many thing like the indexes created, runstats used, reopt bind options etc.. There are also a number of Database Manager and Database configuration parameters that affect the optimizer choosing the optimal plan in DB2. Some are listed below - Database Manager configuration parameters - 1) Parallelism - The first one we will consider is Parallelism. When a number of tasks are run in parallel we say parallelism is enabled. This can improve the performance of a system highly. Parallelism can... [More]
Tags: http://www.ibm.com/suppor... |
