Clarity

Hi ,

  I have strange issue .

  I have a process which has several steps with multiple type of actions  System actions , Scripts .

 all the steps were executing well without errors .

System actions were reflecting in GUI & DB .

there are few gel scripts with DB Update statements , these were always showing executed without errors .  but the these script changes were only reflected in DB & GUI some times and were not reflecting sometimes .

is there any instance that only DB statements were lost in communication in Clarity PPM . This is happening randomly during US office hours .

any one saw this type of issue earlier , how to trace the exact problem .

we didn't see any errors in application Logs  , DB  .

In beacon we are seeing couple of errors , java.nio.channels.ClosedChannelException , java.nio.channels.AsynchronousCloseException  . not sure these are related to this issue . but these were seen in other environments also where this issue is not there .

Hi Ravinder,

If any updates are not showing in PPM GUI where the update is committed in PPM can be a potential problem of multicast, with the Beacon errors it can correlate that in your system beacon is not able to communicate with the servers in the cluster. The beacon communicates through RMI ports and we can see the error.

On another note you should never do direct db updates even via Gel scrips.

Regards
Suman Pramanik 

Hi Suman,

  will this multicast issue occurs only for DB Update Statements through Gel scripts .

  there are some other System actions like make project inactive in the same step of the process  , they were executing well .

do we need to convert these update statments to procedures and run through a job ?

we are maintaining a system which several process with such update statements , any other possible solutions ?

thanks for very quick response .

Hi Ravinder,

Multicast can play part in every part of the application in UI, due to this a user makes a change in UI and another user logged in on different app on cluster might not see the changed data but cache will not be consistent across the cluster if multicast is not working.

Also the update statements needs to be executed via xog and not direct DB updates in order to maintain the data integrity. Changing the update to xog will not fix multicast but you need to ensure your multicast is fixed and enabled in your network and then fix the direct updated. 

Below technical documents will help you to understand multicast better.

https://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.tec494158.html 

https://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.tec594209.html 

https://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.tec490093.html 

https://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.tec511524.html 

Regards

Suman Pramanik 

GEL scripts which issue direct SQL updates need to explicitly trap (i.e. you need to code into the GEL) exceptions - otherwise if the SQL fails (for whatever reason) the GEL might well just carry on executing (as if it were successful) and the details of the error (why the SQL has failed) are lost to you.

i.e. if you don't have any SQL error-handling coded into your GEL script, then you could just be getting SQL errors and not seeing the error output anywhere.

(and while I agree fully with Suman that updates should be done in GEL via XOG, I don't think that "multicast issues" come into this for direct SQL updates since the GEL is communicating directly with the database and not via the application)

Hi David

The cause for multicast could be, the updates are done but when app is trying to fetch the information it's not necessary to pick every time from database and reads from cache layer, my thoughts were based on the beacon RMI port errors and when you see the value in database and not see in app its 99% multicast(provided you have access rights) 

And the moment you restart the app you will see the value and thats the indication of multicast problem. Hence I said converting direct updates to xog will not overcome this problem and my focus was purely on multicast based on the symptoms I see.

Regards

Suman Pramanik 

Right, but if an update that we think has been applied to the DB is not visible in the DB then the update didn't work (or wasn't committed). If a SQL statement works "sometimes" and doesn't work other times then this is often due to data-related errors, which if there is no error trapping around the SQL would go un-reported.

Its all a very good argument for NOT doing direct-SQL in GEL of course, since its "unsupported" there is little built-in error handling!

Hi ,

 I do have catch block to get the error in case of failures .

here is my gel script .. for the same project it is not updating sometimes and updating well some times .

wondering if any n/w issue or DB issue .. checked with DBA he didn't see any issue at DB end .

<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <gel:setDataSource dbId="niku"/>
  <!-- SET PROJECT FINISH DATE TO TODAY -->
  <core:catch var="errorCatch">
  <sql:update escapeText="0"
      var="results">
    UPDATE inv_investments SET schedule_finish = sysdate
    WHERE id = ?
    AND schedule_finish &gt; sysdate
    <sql:param value="${gel_objectInstanceId}"/>
  </sql:update>
</core:catch>
  <core:if test="${errorCatch != null}">
    <gel:log level="ERROR">Error: ${errorCatch}</gel:log>
  </core:if>
</gel:script>

That ^ sort of SQL update is exactly the sort of SQL update that you should NOT be doing via SQL. There are application "rules" that control that field which you are potentially breaking.

In your script, I can see that variable 'results' holds the number of rows effected by the update statement. Can you just log the 'results' variable after the update statement and see if it is returning 1 row as expected?

Hi David,

What I understood that value was updated in DB it was not showing in app and then the suspect was multicast.

Regards

Suman Pramanik

I think we are interpreting the statement "but the these script changes were only reflected in DB & GUI some times and were not reflecting sometimes" differently then - I am interpreting that as saying that sometimes we see the expected changes (in the DB and the GUI) and sometimes we do not (in neither the DB nor the GUI).

I don't think he is saying that sometimes the DB is updated but the GUI does not show the same?

Not sure I have anything to add now that we know the SQL update is an "unsupported" one though - there are various reasons (functional rather than technical) why we might not be able to set a schedule-finish date to a particular value (and discussions around those reasons could be found elsewhere in the community).

Thanks David .

your understanding of problem is correct .

I am interpreting that as saying that sometimes we see the expected changes (in the DB and the GUI) and sometimes we do not (in neither the DB nor the GUI).

and this is happening in peak hours ( US office hours ) .  same version is there in production in 13.2 working fine .

this problem exists in dev system which is now upgraded to 14.2 .

not able to classify this problem related to n/w  or 14.2 .

as this is coming randomly in peak hours , at this point of time we can see this as n/w issue .

but wondering how come statements given to DB were missing without any errors even it is peak hours .

will try rewrite the process . the process is for closing the project and setting the project finish , team allocation dates to current date . these dates were currently were updating through update statement , but project status , financial status were updated through system actions as these are Boolean and can be done through system actions .

sorry .. 

dev is upgraded to 14.3 in dev we have this issue

and currently our production is in 14.2 , in production there is no issue .

Hi Ravinder,

If this problem is intermittent then you need to put an exception in your gel script to trap the exception. Also you are updating PPM core tables, and if there is a lock held at that table its bound to fail. You can put a trace also to have more errors logged and you should be able to find out on what scenario its failing.

Regards

Suman Pramanik