Parameter Wednesday: DBM CFG – INTRA_PARALLEL

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

http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000146.html

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

You may also like...

7 Responses

  1. Brian says:

    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.

    • Ember Crooks says:

      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.

  2. Norman Wong says:

    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.

  3. rafia says:

    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.

    • Ember Crooks says:

      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.

      • rafia says:

        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?

Leave a Reply

Your email address will not be published. Required fields are marked *