DB2 Version This Was Written For
9.7
Parameter Name
INTRA_PARALLEL
Where This Parameter Lives
Database Manager Configuration
How To Check Value
$ db2 get dbm cfg |grep INTRA_PARALLEL Enable intra-partition parallelism (INTRA_PARALLEL) = NO
OR
$ db2 "select name, substr(value,1,16) value, value_flags, substr(deferred_value,1,16) deferred_value, deferred_value_flags, substr(datatype,1,16) datatype from SYSIBMADM.DBMCFG where name='intra_parallel' with ur" NAME VALUE VALUE_FLAGS DEFERRED_VALUE DEFERRED_VALUE_FLAGS DATATYPE -------------------------------- ---------------- ----------- ---------------- -------------------- ---------------- intra_parallel NO NONE NO NONE VARCHAR(3) 1 record(s) selected.
Description
Specifies whether or not the database manager can use Intra-partition parallelism. Intra-partition parallelism allows db2 to apply more than one cpu to processing a query. It is most useful for large queries in dw or dss systems where you have both multiple processors and multiple separate I/O paths.
Impact
Can cause performance degradation if you both enable and use intra-partition parallelism on a e-commerce or transaction processing database. It can significantly increase performance in a dw or dss system if you use it properly.
Default
NO
Range/Values
NO (0), YES (1), SYSTEM(-1)
Recycle Required To Take Effect?
Yes, and packages should be re-bound using db2rbind as well.
Can It Be Set To AUTOMATIC?
SYSTEM is different than automatic – it sets the value based only on the hardware DB2 is running on.
How To Change It
db2 update dbm cfg for dbname using INTRA_PARALLEL YES
Rule of Thumb
If you have a data warehouse or decision support system, consider setting this to YES and also tune the database configuration parameter DFT_DEGREE appropriately.
Tuning Considerations
The db2 configuration advisor may change this parameter.
It used to be that this parameter was required to be set to YES for parallelism on index creation. This is no longer the case – parallel index creation can occur even when this parameter is set to NO.
The actual level of parallelism used is specified by the db cfg parameter DFT_DEGREE, the CURRENT DEGREE special register, or by a clause on the SQL clause.
The reason this can cause performance degredation for OLTP systems is because there is overhead associated with using paralellism, and that overhead is extra time for the singleton-row queries that are the focus of performance for OLTP systems – and parallelism does not help with these small queries. In DW or DSS systems, the overhead is made up for by an increase in the performance of the rest of the query.
For mixed systems, you can consider setting INTRA_PARALLEL to YES, and set DFT_DEGREE to 1 so that queries not specifying a query degree will continue to not use parallelism. Then you can specify a higher degree of parallelism for queries or applications that can take advantage of it.
Related Error Messages
This parameter doesn’t have specific associated error messages, though it can change performance.
War Stories From The Real World
I’ve spent the last 4 or 5 years nearly exclusively on e-commerce systems, so I haven’t done much with this parameter lately. Before that, I did have a mixed use system (client/server transaction processing and reporting) that had INTRA_PARALLEL set to YES with DFT_DEGREE at 1, but the developers did not make much use of it.
Link To Info Center
Related Parameters
DBM CFG: MAX_QUERYDEGREE – http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000140.html
DB CFG: DFT_DEGREE – http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000346.html
Special Register: CURRENT DEGREE – http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0005873.html
I’m considering enabling this on an OLTP system (mixed use.. reporting.. batch). If I set the DFT_DEGREE to a fixed value (say 4, system has 24 CPUs), will there still be a hit on the optimizer? I would understand if it were set to ANY, then the optimizer has to figure out which degree to use.
If using it on a mixed-use system, generally the best way to go is to set DFT_DEGREE to 1. This way, queries that you do not specify a degree of parallelism for will get 1. This works best because we are most likely to be unable to change the SQL from OLTP applications, while we are more likely to be able to change those from DSS applications. Also, it is usually better to err on the side of too little parallelism rather than too much parallelism. Usually the performance of the OLTP workload is slightly more critical than the DSS workload. The OLTP workload will absolutely be impacted if it is forced to use a degree of 4 for singleton-row queries. ANY is always an option, but I prefer not to use it for a mixed workload.
There is a change in DB2 V10.1 that allows this to be changed at a connection level either through a call to a System Stored Procedure (admin_set_intra_parallel) or have WLM change it. This allows systems with a mixed workload of OLTP and query to co-exist in the same database. Haven’t tried it myself, but that’s the design goal.
Would be interesting to hear of field experiences.
Thanks for this detail!
I have set INTRA_PARALLEL = yes , DFT_DEGREE=ANY, and MAX_QUERYDEGREE=1, still in the output of db2exfmt it shows None for Intra parallelism, however based on the above parameter settings I think it should show Intra-partition parallelism.
My DB2 version is Express-C V10.5 and running on 4 core machine with 64-bit ubuntu. Also my DB is single partitioned.
Does anybody have any clues why intra-partition is not working?
I’ve also checked with restarting the instance and rebinding all packages.
Have you tried different queries? Not all queries will benefit from parallelism, and the optimizer will choose whether to use it or not for each one.
I’ve tried almost 10 benchmark queries to test this. The weird thing when I ran these queries on different system (i.e. with different RAM and no of cores) then intra partition parallelism was used. Do you have some clue about this behaviour?