|
Problem IC65727
|
Status: Closed |
CONCURRENT LOADS INTO DIFFERENT DPF DBPARTITIONS OF THE SAME TABLE (E.G DATASTAGE) WITH A CONSTRAINT DEFINED MAY DEADLOCK |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
In a DPF environment, if multiple invocations of the Load
utility are run concurrently into the same table (this is
possible when each invocation targets a different dbpartition by
using the DB2_PARTITIONEDLOAD_DEFAULT=no registry variable, or
the 'partitioned db config output_nodes (n)' option), and the
target table has a constraint defined, then these Loads may
deadlock with each other when one of the Loads attempts to turn
off constraints.
If the deadlock monitor is enabled, the deadlock statement
history will contain the following two statements:
1. SET INTEGRITY FOR "CCSI "."HGN_PKG_CUST_LNK_W01" OFF NO
ACCESS CASCADE DEFERRED
2. SELECT TYPE, STATUS, TBSPACEID, PROPERTY FROM SYSCAT.TABLES
WHERE TABSCHEMA = ? AND TABNAME = ?
(Note that this Abinitio Datastage application parallelizes data
insertion by invoking multiple concurrent loads into the same
table targetting different dbpartitions). |
| Problem Summary: |
In a DPF environment, if multiple invocations of the Load
utility are run concurrently into the same table (this is
possible when each invocation targets a different dbpartition by
using the DB2_PARTITIONEDLOAD_DEFAULT=no registry variable, or
the 'partitioned db config output_nodes (n)' option), and the
target table has a constraint defined, then these Loads may
deadlock with each other when one of the Loads attempts to turn
off constraints.
If the deadlock monitor is enabled, the deadlock statement
history will contain the following two statements:
1. SET INTEGRITY FOR "CCSI "."HGN_PKG_CUST_LNK_W01" OFF NO
ACCESS CASCADE DEFERRED
2. SELECT TYPE, STATUS, TBSPACEID, PROPERTY FROM SYSCAT.TABLES
WHERE TABSCHEMA = ? AND TABNAME = ?
(Note that this Abinitio Datastage application parallelizes data
insertion by invoking multiple concurrent loads into the same
table targetting different dbpartitions). |
| Local Fix: |
Prior to invoking the concurrent Loads into the same table, you
can determine if the target table has constraints defined
(SELECT CONSTNAME FROM SYSCAT.COLCHECKS where
TABSCHEMA='<schemaname>' and TABNAME='<tablename>'), and turn
them off manually (SET INTEGRITY FOR <schemaname>.<tablename>
OFF NO ACCESS CASCADE DEFERRED) which avoids the Load utility
from having to turn them off and risk deadlock. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Defect => wsdbu00609507
Fixed in => V9.7 + FP2
Module => ENGN_SQU |
| Workaround |
Prior to invoking the concurrent Loads into the same table, you
can determine if the target table has constraints defined
(SELECT CONSTNAME FROM SYSCAT.COLCHECKS where
TABSCHEMA='<schemaname>' and TABNAME='<tablename>'), and turn
them off manually (SET INTEGRITY FOR <schemaname>.<tablename>
OFF NO ACCESS CASCADE DEFERRED) which avoids the Load utility
from having to turn them off and risk deadlock. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 20.01.2010
10.06.2010
10.06.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
