Clarity

Dear All,

We have MS Sql server database in Ca Clarity PPM. As we know that whenever any attribute is locked then one entry is inserted in ODF_LOCKED_ATTRIBUTES table. When i lock any attribute via System Action then it inserts an entry in ODF_LOCKED_ATTRIBUTES table with some ID value. How Clarity is getting this ID value?

I have checked the OOTB sequence table CMN_SEQ_ODF_LOCKED_ATTRIBUTES but it looks like Clarity is not using this table for ID value. For confirmation, i have locked few attributes and checked that no change in sequence value in CMN_SEQ_ODF_LOCKED_ATTRIBUTES. The ID with some values were inserted in ODF_LOCKED_ATTRIBUTES table and attributes also locked. So then question is that these value comes from where? How Clarity is generating these ID values?

When i inserts the manual entry in ODF_LOCKED_ATTRIBUTES as per our requirements then i am using sequence value from CMN_SEQ_ODF_LOCKED_ATTRIBUTES as ID and increasing sequence value via running following procedure.

EXEC CMN_ID_SP 'ODF_LOCKED_ATTRIBUTES', @P_NEXT_ID_RESULT OUTPUT;

Here @P_NEXT_ID_RESULT stores next Id value.

As Clarity system Action lock and Manual lock is not taking Id from same table so sometimes it produces an error while inserting because of Primary constraint set up on ID field of ODF_LOCKED_ATTRIBUTES .

so Any suggestion on this Issue?

The supported answer is that you should never be "manually" creating entries in that table (for exactly the reasons that you are encountering - you are "breaking" the application's data integrity and getting those errors). Rather you should lock attributes via a process triggered by some update on the relevant object.

( the sequences that the application uses are often cached by the application in memory, which can be confusing as well as the inconsistent way sequences seem to be handles ; some are on that table, some generated by database sequences and some just from data )

If you search for ODF_LOCKED_ATTRIBUTES on these boards you'll find some other discussions where people were manually inserting records though.

Hello David,

I have gone through the almost all discussions on this topic and found the only appropriate answer to increase sequence by running procedure : CMN_ID_SP but it is also not helpful. 

As you said the Clarity uses often cached by the application in memory and not from sequence table. But this issue did not happen in Oracle Database and ID value exactly matches with the sequence table value. I have not seen this issue when using Oracle DB so i am wondering that why this issue is occurring with Sql server database. 

In Oracle , you can use ODF_LOCKED_ATTRIBUTES_S1.NEXTVAL 

Google says its equivalent in SQL Server is NEXTVAL(' ODF_LOCKED_ATTRIBUTES_S1')

Hello,

there is no such (NEXTVAL(' ODF_LOCKED_ATTRIBUTES_S1')) function or procedure in SQL server DB. 

As I said, ODF_LOCKED_ATTRIBUTES_S1 is the sequence. You may explore how to use it in SQL server.

How about NEXT VALUE FOR ODF_LOCKED_ATTRIBUTES_S1 ?

ODF_LOCKED_ATTRIBUTES_S1  does not exist in Sql server. Whatever the concept of auto generating sequence number in oracle is not applicable in Sql Server.

In that case, probably finding it using Action tracing may help.

Went through the CMN_ID_SP in SQL Server, and the logic seems very similar to the implementation in Oracle, except for the coding to deal with Identity Columns. But I did notice the behaviour that you are referring to here. The CMN_SEQ tables do not get updated every time a insert happens to the corresponding Primary Key holding table.

But the increments in the primary key holding tables are consistent. So, provided that there are no caching mechanisms that seem to be coded for in the SP, I think there is some factor outside of it, that is dealing with the increments once a maximum value is determined for a session. And since flushing application cache by going into security.caches, forces the CMN_SEQ tables to be updated, I think that the increments are somehow handled either as a function of the SQL Server database, or may be in the Java code.

So, if you are trying to insert directly into the ODF_LOCKED_ATTRIBUTES table, then finding the maximum ID in that table, and determining that this ID is less than the LAST_ID in CMN_SEQ table, should be enough to determine the next ID. If however, the LAST_ID in the CMN_SEQ table is less than the maximum ID, then call the CMN_ID_SP, and get the next value from it.

Thanks Jeevan for your comments. But Issue is not with Manual insertion for locking attribute that we can handle at our level. The issue comes when locking attributes via system action and at that time what and how Id value is generating by Clarity that we don't know and sometimes it conflicts with existing ID value and raises error. Once we know the concept of generating ID value by Clarity then we can handle all such issues. Hope you got the actual issue.

I disagree; the actual issue is that by inserting "manually" you have broken the application logic:(

If there were manual inserts already done, then there is definitely a possibility of the application picking up a number that has already been used, and thus causing this problem. So, what specific error does the application throw, when it tries to lock attributes via system actions? I would expect some sort of Unique Constraint getting logged in the app-ca or the bg-ca..

Yes, Jeevan Error is related to Unique Constraint in bg-ca.log file. That's why i am trying to understand the concept of generating Id values by Clarity.