Parameter Wednesday: DBM CFG – INTRA_PARALLEL
DB2 Version This Was Written For
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
$ 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.
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.
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.
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.
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
DBM CFG: MAX_QUERYDEGREE – http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000140.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