IBM Content Manager, Version 8.5.0.3

ICMSTRI (Reference Integrity Table)

The ICMSTRInnnsss table (where nnn is LibraryID and sss is SysSegmentID) maintains the referential attributes for the content manager.

Column Name	Data Type	Attribute
SourceItemID	CHAR(26)	NOT Null
SourceItemTypeID	INTEGER	NOT Null
SourceCompID	CHAR(18)	NOT Null
SourceCompTypeID	INTEGER	NOT Null
SourceVersionID	SMALLINT	NOT Null
TargetItemID	CHAR(26)	NOT Null
TargetItemTypeID	INTEGER	NOT Null
TargetCompID	CHAR(18)	NOT Null
TargetCompTypeID	INTEGER	NOT Null
TargetVersionID	SMALLINT	NOT Null
RRIID	CHAR(26)	NOT Null
Status	SMALLINT	NOT Null

Primary Key

(RRIID)

Indexes

Index = (SourceItemID, SourceCompID, SourceVersionID, SourceCompTypeID)
Index = (TargetItemID, TargetVersionID, TargetCompID, TargetCompTypeID)
Index = (TARGETITEMID,  TARGETITEMTYPEID, TARGETCOMPID, TARGETCOMPTYPEID,
     SOURCEITEMTYPEID,  SOURCECOMPTYPEID)
Index =  (SOURCECOMPTYPEID, SOURCEVERSIONID, SOURCECOMPID)
z/OS only:
Unique Index = (RRIID ascending)

Referential Constraints

None

Column Definitions

SourceItemID

Identifies the Item ID of the source row.

SourceItemTypeID

Identifies the ItemType ID of the source row.

SourceCompID

Identifies the Component ID of the source row.

SourceCompTypeID

Identifies the Component Type ID of the source table.

SourceVersionID

Identifies the version ID information of a source row

TargetItemID

Identifies the Item ID of the target row.

TargetItemTypeID

Identifies the ItemType ID of the target row.

TargetCompID

Identifies the Component ID of the target row.

TargetCompTypeID

Identifies the Component Type ID of the target table

TargetVersionID

Identifies the version ID information of a target row.

RRIID

Uniquely identifies a reference.

Status

The status of the RI row. The valid values are:

0: Active RI row
1: Inactive RI row which the source row has been deleted
2: Inactive RI row which the reference of the source row has been set to null.

Sample SQL Statements

Given a source item ID ('A1234567890123456789012345') and version ID (1), get all user data of the target items:

select distinct targetcomptypeid partcomptypeid, targetitemid partitemid
from icmstri001001
where sourceitemid = 'A1234567890123456789012345' and
  sourceversionid = 1 
order by targetcomptypeid

For each target component type ID from the previous step, get the ICMUT name by ICMUTnnnnn001:
```
get part ICMUT name by ICMUTnnnnn001
```
where nnnnn is 5 digits component type ID.

For each target item ID from the previous step:

select *
from ICMUTnnnnn001
where itemid = 'A0987654321098765432109876'

Given a document item ID ('A1234567890123456789012345'), get the part item IDs for this document:

Note that the document itemID represents the source itemID, and part itemID represents the target itemID.

select targetitemid, targetcompid, targetversionid, 
  targetitemtypeid, targetcomptypeid
from icmstri001001
where sourceitemid = 'A1234567890123456789012345' and
  sourceversionid = 1

Given a part item ID ('A05B01C01047I154400010s100'), find the owning document:

select k.keywordname, r.sourceitemid r.sourceitemtypeid, 
  r.sourcecompid, r.sourcecomptypeid, r.sourceversionid 
from icmstri001001 r, icmstnlskeywords k 
where k.keywordclass = 2 and 

k.keywordcode = r.sourceitemtypeid and 
r.targetitemid = 'A05B01C01047I154400010s100' and 
  r.targetversionid = 1

Given a document item ID ('A1234567890123456789012345'), get all the current work packages:

Note that the work package represents the source componentID and the document itemID represents the target itemID.

select wp.*
  from ICMUT00204001 wp
where rtargetitemid = 'A1234567890123456789012345'

select *
  from icmstri001001
where targetitemid = 'A1234567890123456789012345'

Given a work package ('A12345678901234567'), get all document item IDs:

Note that the work package represents the source componentID and the document itemID represents the target itemID.

select wp.rtargetitemid
from ICMUT00204001 wp
  where componentid = 'A12345678901234567'
      
select targetitemid 
from icmstri001001
where sourcecompid = 'A12345678901234567'

Given a work node ('A1234567890123456789012345'), get all document item IDs:

Note that the work node represents the source itemID and the document itemID represents the target itemID.

select wp.rtargetitemid
from ICMUT00204001 wp
where itemid = 'A1234567890123456789012345'

select targetitemid 
from icmstri001001
where sourceitemid = 'A1234567890123456789012345'