FPSINPUT.SYSMNTR_OOC_AND_REPEATED

>

> FPSINPUT.WIP_WAFER_HIST_LOOP

View FPSADMIN.SYSMNTR_OOC_AND_REPEATED

This view has two parts which are in the same view because they use the exact same data so it is faster to run together. PART 1: OUT OF CONTROL FROM YESTERDAY This checks for extreme shifts in the number of updates/deletes/inserts made by each load object as well as an extreme shift in the duration of each load object to run. This view will highlight problems with loads that run without error but which have significant changed. Obviously this change might be expected after a DWH change but if not then something is probably wrong despite no actual errors. One example of this was MHS_CARRIERS that was updating about 50 rows per load. Then we made a change to the trigger and we immediately jumped to updating 2000 rows per load. This jump was not expected and indicated a problem with how the change was implemented. This problem did not cause the trigger to error so we would not have found it without individual QA except for this view. Another likely scenario would be an object that consistently updates 50 rows per load and then starts updating 0 rows. Even though there are no errors this means that the load is likely not working. Of course it might also mean a shutdown or a company meeting but we should know about this and be able to ignore this warning when appropriate. This view also checks for the duration of the load. We had an issue with ADM_FIX_WIP_STEP_FUTURE where it was consistently taking around 5 seconds per run and then after a change it jumped to 90 seconds per run. This was pure "sailboat racing" where Oracle just parsed the new query in a way that was not optimal but the procedure still worked without errors so only this view will find this change automatically. PART 2: REPEATED ATTEMPTS This checks for loads that attempted the same number of updates or deletes repeatedly. Sometimes these are just random. For example, a table might update 2 rows on three consecutive runs and make it into this view. But this often indicates that the queries are not successful. For example, a table might show that it deleted exactly 16 rows on every run for a long time. This means that it attempted the same 16 deletes each time and they failed to delete. Note there is no error it just "successfully" deletes 0 rows. Also this failure might occasionally delete more like 16-16-16-25-16-16-30-16. The same 16 rows failed but 9 other rows successfully deleted on the 4th run and 14 on the 7th run. If you look at the ADM_LOAD_HIST table, you can see the number of rows updated/deleted on each run and it is usually clear whether it is random or a failure. Note that failed inserts always result in an error so we do not need to check repeated inserts. Previewing the APF report on the Preview Here block usually shows the cause of the failure which is usually either: 1) Foreign characters or other illegal characters that APF interprets differently that SQL so the where clause doesn't match. - The solution is to use German_chars or Bad_chars macro to translate 2) The where clause in the APF update_query or delete_qery block does not match the primary/unique key of the table. This happens when we make table changes and forget to change the report -- or in this case where we have a unique key which could be null instead of primary key. - The solution here is to simply fix the query in the report. Use BLD_TABLE_QUERiES view unless the table has UK instead of PK.