|
Problem IC65727
|
Status: Geschlossen |
CONCURRENT LOADS INTO DIFFERENT DPF DBPARTITIONS OF THE SAME TABLE (E.G DATASTAGE) WITH A CONSTRAINT DEFINED MAY DEADLOCK |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problembeschreibung: |
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-Zusammenfassung: |
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. |
| verfügbare FixPacks: |
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
|
| Lösung |
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. |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 20.01.2010
10.06.2010
10.06.2010 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.7.
|
| Problem behoben lt. FixList in der Version |
| 9.7.0.2
|
 |
