We are using Ignition 7.2.9 to monitor production work cells in a medical device MFG factory. We have developed our own version of OEE monitoring using PLC logic, Ignition scripting and MSSQL 2005 Stored Procedures.
All INSERTS and UPDATES are performed by MSSQL Stored Procedures that are triggered by Ignition Transaction Groups.
We recently moved our MSSQL records and Stored Procedures from our Dept. database where they had run well for 1 year to our FDA validated plant database. From May 29 to June 22 over 700,000 duplicate records were unintentionally created. There were some DB anolomies coincident with the beginning of the duplicate records. Multiple old DB tables were merged into a single table and the table identity was reseeded on May 29th. Neither the DBA or I can see how reseeding the ID would create duplicate records over the course of 3 weeks, but the date is coincident. On a normal day 50-150 downtime records are created. On our worst day over 200,000 duplicate records were unintentionally created.
The DBA has discovered that the MSSQL audit log was not configured on this table so we do not have data from MSSQL on how the duplicate records were created. Is there a log in Ignition that will prove how often transaction groups were executed? I need to prove or disprove that Ignition was the root cause of the problem and ensure that we have corrected the root cause of the problem.
Thanks
Ken P.
Hi,
The statistics aren’t persisted anywhere, but by looking at the groups while they run you can see the execution statistics, and see how many times they’ve executed. If you must save some copy of this, I suppose you could also go to Config>Console>Execution in the gateway, which has statistics for all execution units in Ignition, including the groups. HOWEVER, unfortunately there you will only see how many times the group executed, NOT how many times it was actually “triggered” and ran a query against the db.
If all inserts are performed by the stored procedure, it would mean that the group would have to execute multiple times with the same values. You would have to look at the group settings to determine if this was possible (are the groups triggered?) and if so, if it could happen that much. Spread over 24 hours, 200k duplicates means an execution every 434 ms. Of course, I suppose you could mean a variety of different types of duplicates, that would be spread over all of your groups.
Are the insert statements in your stored procedures simple or more complex? If they’re using any kind of joins, it might be possible that they’re duplicating data by producing result sets larger than you would expect.
Anyhow, hope this helps a bit.
Colby,
Thanks for the prompt response. Without any direct evidence I will have to write this off as part of the development process. Yes, the SQL stored procedures have some complex joins, but they are well tested and the massive duplicate entries only occured from 5/29 to 6/22. Without og audit data my best guess is that we had a transaction group trigger mis-configured and it got corrected before we realized we had a data problem correlated with it. I will monitor the DB for a couple of months to see if the behavior repeats.
Thanks
Ken P.