Preventive Service Planning
Abstract
In SMP machines, more than one processor can access the database, allowing the execution of complex SQL requests to be divided among the processors. This intra-partition parallelism is the subdivision of a single database operation into multiple parts, which are then executed in parallel within a single database partition.
Degree refers to the number of query parts that can execute concurrently. If a query is compiled with DEGREE = ANY, the database manager chooses the degree of intra-partition parallelism on the basis of a number of factors, including the number of processors and the characteristics of the query.
The actual degree used at run time is also depends on the amount of activity on the system. DBAs can check the degree of parallelism actually being used at run time.
Content
database manager configuration parameter -
$ db2 get dbm cfg | grep -i parallelism
Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = YES
database configuration parameter -
$ db2 get db cfg for sample | grep -i parallelism
Degree of parallelism (DFT_DEGREE) = ANY
1. Connect to the database and check the current degree
$ db2 connect to sample
$ db2 values CURRENT DEGREE
1
-----
ANY
2. Check the values in monitor elements NUM_ASSOC_AGENTS and ASSOCIATED_AGENTS_TOP
$ db2 list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
MIDUMBHA db2bp 124 *LOCAL.midumbha.160622114657 SAMPLE 1
1 record(s) selected.
$ db2 "select substr(APPL_ID,1,30) APPL_ID, AGENT_ID, NUM_ASSOC_AGENTS from SYSIBMADM.APPLICATIONS where AGENT_ID=124"
APPL_ID AGENT_ID NUM_ASSOC_AGENTS
------------------------------ -------------------- --------------------
*LOCAL.midumbha.160622114657 124 1
1 record(s) selected.
$ db2 "select AGENT_ID, NUM_AGENTS, ASSOCIATED_AGENTS_TOP from sysibmadm.SNAPAPPL where AGENT_ID=124"
AGENT_ID NUM_AGENTS ASSOCIATED_AGENTS_TOP
-------------------- -------------------- ---------------------
124 1 1
1 record(s) selected.
3. Run a query and check the same values for the same monitor elements. You will notice the changed value.
$ db2 "select count(*) from emp1"
1
-----------
1287468
1 record(s) selected.
$ db2 "select substr(APPL_ID,1,30) APPL_ID, AGENT_ID, NUM_ASSOC_AGENTS from SYSIBMADM.APPLICATIONS where AGENT_ID=124"
APPL_ID AGENT_ID NUM_ASSOC_AGENTS
------------------------------ -------------------- --------------------
*LOCAL.midumbha.160622114657 124 3
1 record(s) selected.
$ db2 "select AGENT_ID, NUM_AGENTS, ASSOCIATED_AGENTS_TOP from sysibmadm.SNAPAPPL where AGENT_ID=124"
AGENT_ID NUM_AGENTS ASSOCIATED_AGENTS_TOP
-------------------- -------------------- ---------------------
124 1 3
1 record(s) selected.
4. You can get the same values in application snapshot -
$ db2 get snapshot for application agentid 124 | grep -i associated
Maximum associated agents = 3
Agents associated with the application = 3
5. In query explain, you will find query degree in the begining of the access plan graph as shown in the example below.
Partial output -
Access Plan:
-----------
Total Cost: 14476
Query Degree: 3
Rows
RETURN
( 1)
Cost
I/O
|
Was this topic helpful?
Document Information
Modified date:
23 June 2023
UID
swg21985891