IBM DB2 Content Manager Library Server Database ICMNLSDB linking table for custom reports

Tested on IBM DB2 Content Manager version 8.3 and 8.4.

IBM DB2 Content Manager's database ICMNLSDB can be used to fulfill different requirement like custom reporting or to change item links manually.

Though it is not recommended but, still, if a requirement occurs, feel free to use the database directly. (at your own risk).

ICMSTLINKS001001 is the name of the table that contains information about the heirarchical linking of the items.

Example:
Suppose there are two item types.
1. MyFolder       (Folder)
2. MyItem         (Folder or Document)

- An item is created having type MyFolder.
- Another item is created having type MyItem.
- MyFolder contains MyItem i.e. MyItem(Folder or Document) is inside MyFolder(Folder).

Assuming Item Types are simple and not complex.
- One table will be created for each Item Type. (something like ICMUXXXXXXX ... not important)
- One view will also be created for each Item Type. (like MyFolder001 and MyItem001, important)
- Each view will have a column named ITEMID


- ICMSTLINKS001001 table have two important columns TARGETITEMID and SOURCEITEMID
- A join can be made as below

select * from MyFolder001 folder
inner join ICMSTLINKS001001 link
on folder.ITEMID = link.SOURCEITEMID
inner join MyItem001 item
on link.TARGETITEMID = item.ITEMID

 
where
- MyFolder001 is source
- MyItem001 is target


I tried to keep this post as simple as possible. Ofcourse, there are other things to consider like version, link type e.t.c.

java.lang.IllegalArgumentException: FMC38009E MQSeries Workflow API Error : API Return Code : 142 Error Origin : FmcProfile.java:182 Error Message : FMC00142E Profile not found for configuration ''default''.

java.lang.IllegalArgumentException:
FMC38009E MQSeries Workflow API Error :
    API Return Code  : 142
    Error Origin     : FmcProfile.java:182
    Error Message    : FMC00142E Profile not found for configuration ''default''. 
    Nested Exception : None
    at com.ibm.workflow.api.Agent.getAgentImplClass(Agent.java:406)
    at com.ibm.workflow.api.Agent.setLocator(Agent.java:274)

Reason:
The fmcrc file cannot be found.

Solution:
fmcrc file and MQ library files should be in the same classloader.
e.g. If you have MQ library files in WEB-INF/lib folder, then fmcrc should be in WEB-INF/classes. If you are using Rational Application Developer (RAD) then keep it in src (Java Source) folder, RAD will copy it in WEB-INF/classes when you'll build the project.

com.ibm.workflow.client.util.FmcApiException: FMC38009E MQSeries Workflow API Error : API Return Code : 13 Error Origin : FmcSession.java:504 Error Message : FMC00013E Communication error - Failing Action: put, Reason Code: 0, Failing Object: .

com.ibm.workflow.client.util.FmcApiException:
FMC38009E MQSeries Workflow API Error :
    API Return Code  : 13
    Error Origin     : FmcSession.java:504
    Error Message    : FMC00013E Communication error - Failing Action: put, Reason Code: 0, Failing Object: .
    Nested Exception : None
    at com.ibm.workflow.client.api.FmcSession.CallSync(FmcSession.java:504)
    at com.ibm.workflow.client.api.FmcSession.Logon(FmcSession.java:999)
    at com.ibm.workflow.client.api.FmcExecutionService.logon2(FmcExecutionService.java:800)


Scenario:
- Using Rational Application Developer 7.5 with Websphere Application Server 7.0.
- MQ APIs are in project lib directory.
- The same use to work fine using Rational Application Developer 6.0 with Webpshere Application Server 5.1



Solution:
Remove the following rar file
IBM\SDP\runtimes\base_v7\profiles\profileName\installedConnectors\wmq.jmsra.rar
 

Reason:
Websphere Application Server 7.0 comes packaged with MQ APIs.


Note: This is one of the main reasons for this error.

java.lang.ClassCastException: com.ibm.mm.sdk.internal.sql.db2.PDB2ConnectionICM incompatible with com.ibm.mm.sdk.internal.sql.PConnectionICM

java.lang.ClassCastException: com.ibm.mm.sdk.internal.sql.db2.PDB2ConnectionICM incompatible with com.ibm.mm.sdk.internal.sql.PConnectionICM
    at com.ibm.mm.sdk.server.DKDatastoreICM.connect(DKDatastoreICM.java:3451)
    at com.ibm.mm.sdk.common.DKDatastorePool._createNewConnection(DKDatastorePool.java:1085)
    at com.ibm.mm.sdk.common.DKDatastorePool.access$000(DKDatastorePool.java:253)
    at com.ibm.mm.sdk.common.DKDatastorePool$DatastorePooledObjectManager.create(DKDatastorePool.java:1224)
    at com.ibm.mm.sdk.internal.PSimplePool._createPooledObject(PSimplePool.java:850)
    at com.ibm.mm.sdk.internal.PSimplePool.reserve(PSimplePool.java:553)
    at com.ibm.mm.sdk.common.DKDatastorePool.initConnections(DKDatastorePool.java:841)


Cause: In Rational Application Developer 7.5 using Websphere Application Server 7.0, when project is republished instead of server restart.

Solution: Create a shared library for these two files, so that they are loaded only once.
cmbsdk81.jar
icmsdk81.jar


To create a shared library, use the following link
http://www.redbooks.ibm.com/abstracts/tips0216.html?Open

com.ibm.mm.sdk.common.DKDatastoreAccessError: DGL0394A: Error in ::DriverManager.getConnection; cmbicmenv.ini (The system cannot find the file specified.); [SERVER = ICMNLSDB, USERID = ]

com.ibm.mm.sdk.common.DKDatastoreAccessError: DGL0394A: Error in ::DriverManager.getConnection; cmbicmenv.ini (The system cannot find the file specified.); [SERVER = ICMNLSDB, USERID = ]
    at com.ibm.mm.sdk.server.DKDatastoreICM.connect(DKDatastoreICM.java:3387)
    at com.ibm.mm.sdk.common.DKDatastorePool._createNewConnection(DKDatastorePool.java:1085)
    at com.ibm.mm.sdk.common.DKDatastorePool.access$000(DKDatastorePool.java:253)
    at com.ibm.mm.sdk.common.DKDatastorePool$DatastorePooledObjectManager.create(DKDatastorePool.java:1224)
    at com.ibm.mm.sdk.internal.PSimplePool._createPooledObject(PSimplePool.java:850)
    at com.ibm.mm.sdk.internal.PSimplePool.reserve(PSimplePool.java:553)
    at com.ibm.mm.sdk.common.DKDatastorePool.initConnections(DKDatastorePool.java:841)


One of the reasons is missing db2java.zip file

In Webpshere Application Server 7.0 Admin Console, make sure you add path for db2java.zip at

Application servers > serverName > Process definition > Java Virtual Machine > Configuration >Classpath

com.ibm.mm.sdk.common.DKUsageError: DGL7332A: Library server representation type was not found. Please make sure that a valid library server and representation type has been specified in the cmbicmsrvs.ini file or to the Content Manager connector. : ICMNLSDB

com.ibm.mm.sdk.common.DKUsageError: DGL7332A: Library server representation type was not found. Please make sure that a valid library server and representation type has been specified in the cmbicmsrvs.ini file or to the Content Manager connector. : ICMNLSDB
    at com.ibm.mm.sdk.server.DKDatastoreICM.connect(DKDatastoreICM.java:1659)
    at com.ibm.mm.sdk.common.DKDatastorePool._createNewConnection(DKDatastorePool.java:1085)
    at com.ibm.mm.sdk.common.DKDatastorePool.access$000(DKDatastorePool.java:253)
    at com.ibm.mm.sdk.common.DKDatastorePool$DatastorePooledObjectManager.create(DKDatastorePool.java:1224)
    at com.ibm.mm.sdk.internal.PSimplePool._createPooledObject(PSimplePool.java:850)
    at com.ibm.mm.sdk.internal.PSimplePool.reserve(PSimplePool.java:553)
    at com.ibm.mm.sdk.common.DKDatastorePool.initConnections(DKDatastorePool.java:841)


This happens when CM changes from 8.3 to 8.4 version.
One of the reasons is the CM library Files.

cmbsdk81.jar          Size:12,284 KB
icmsdk81.jar           Size:10,304 KB

make sure that these files are latest and are of the size as mentioned above.