Extending IBM CM Database View to show current Process Step Name

In CM8, whenever a new Item Type is created, it creates a number of tables in the Library Server database in order to support the Item Type classification selected for the Item Type.

The tables created have dynamic name assigned to them, so it is not possible to preempt the names. However for every Item Type there is a view generated in the database automatically, with the same name as the Item Type with 001 suffixed, exceptions to these are when the Item Type name is bigger than 16 chars (I guess, do not recollect it that well). This view provides columns for all the attributes defined for the Item Type.

These views are of great help when generating reports as you get to work at the DB layer and query the required data knowing that the performance would be the best without any additional overhead that API's have. 

NOTE: It is advised to use these views for read-only operation, no updates should be done directly at the DB layer without fully understanding the impact that it might have on the stability of the system.

However such views do not have any information related to the process (Document Routing). Many a times when generating the reports you would require to know what is the current step at which the work item is at for a given Item of an Item Type. By following the below procedure you would be able to expose the current workflow step for all the Items in an Item Type view.

The following example uses the NOINDEX Item Type, but same procedure applies for other Item Type as well.

Procedure

• Connect to the Library Server database
• Get the (existing) view definition for the Item Type (NOINDEX in this example)

db2 select text from syscat.views where viewname='NOINDEX001'

-----------------------------------------------------------------------------

CREATE VIEW ICMADMIN.NOINDEX001 (COMPCLUSTERID, COMPONENTID, ITEMID, VERSIONID, ACLCODE, SEMANTICTYPE, EXPIRATIONDATE, COMPKEY, CREATETS, CREATEUSERID, LASTCHANGEDTS, LASTCHANGEDUSERID, SOURCE, USER_ID, TIMESTAMP, ICMMANAGEDRECORD) AS SELECT COMPCLUSTERID, COMPONENTID, ITEMID, VERSIONID, ACLCODE, SEMANTICTYPE, EXPIRATIONDATE, COMPKEY, CREATETS, CREATEUSERID, LASTCHANGEDTS, LASTCHANGEDUSERID, ATTR0000001000, ATTR0000001001, ATTR0000001002, ICMMANAGEDRECORD FROM ICMADMIN.ICMUT01000001 WHERE   (EXISTS ( SELECT 1  FROM ICMADMIN.ICMSTCOMPILEDACL ICMC, ICMADMIN.ICMSTUSERS U WHERE (ICMC.ACL=1 OR ICMC.ACL=-1) AND ((U.USERID=USER AND ICMC.UNUM=U.UNUM) ) AND ICMC.RPRIV='1') )

• The above view need to be rewritten to include 2 additional tables, one for the work nodes (ICMUT00202001) and the other for the work packages (ICMUT00204001) table. Give a new name to the view, an alias to the Item type table as 'IT' and prefix attributes columns with the 'IT' prefix. The rewritten query would be as below

CREATE VIEW ICMADMIN.NOINDEXWF001 (COMPCLUSTERID, COMPONENTID, ITEMID, VERSIONID, ACLCODE, SEMANTICTYPE, EXPIRATIONDATE, COMPKEY, CREATETS, CREATEUSERID, LASTCHANGEDTS, LASTCHANGEDUSERID, SOURCE, USER_ID, TIMESTAMP, ICMMANAGEDRECORD, STEP_NAME) AS SELECT IT.COMPCLUSTERID, IT.COMPONENTID, IT.ITEMID, IT.VERSIONID, IT.ACLCODE, IT.SEMANTICTYPE, IT.EXPIRATIONDATE, IT.COMPKEY, IT.CREATETS, IT.CREATEUSERID, IT.LASTCHANGEDTS, IT.LASTCHANGEDUSERID, IT.ATTR0000001000, IT.ATTR0000001001, IT.ATTR0000001002, IT.ICMMANAGEDRECORD, WN.WORKNODENAME FROM ICMADMIN.ICMUT01000001 AS IT, ICMADMIN.ICMUT00202001 AS WN, ICMADMIN.ICMUT00204001 AS WP WHERE (WP.RTARGETITEMID = IT.ITEMID AND WP.ITEMID = WN.ITEMID) AND  (EXISTS ( SELECT 1  FROM ICMADMIN.ICMSTCOMPILEDACL ICMC, ICMADMIN.ICMSTUSERS U WHERE (ICMC.ACL=1 OR ICMC.ACL=-1) AND ((U.USERID=USER AND ICMC.UNUM=U.UNUM) ) AND ICMC.RPRIV='1') )

The above query has a join on the Item Type table and the Work package table for getting documents with processes and another join on the Work package and Work node table for getting the step name.

The procedure is now complete. 

The output of a query using the existing view would be as below

db2 "SELECT ITEMID FROM NOINDEX001"

ITEMID

--------------------------

A1001001A07C26C10637B19967

A1001001A13F29A61324A04886

A1001001A13F29C05550E23937

The output using the newly created view with the STEP_NAME would be as below, only Items having a active workflow is shown

db2 "SELECT ITEMID, STEP_NAME FROM NOINDEXWF001"

ITEMID                     STEP_NAME

-------------------------- --------------------------------

A1001001A13F29C05550E23937 INDEX