NM DMS Incremental Update Training

1. NM DMS Incremental Update Training ABB Annual Users’ Group Meeting August 27 – 31, 2006

2. Update the as-operated model with the as-built model Designed to preserve database integrity for any type of failure As-Operated changes(temporary states) are never lost IU Basics GIS extract as-built model Production as-operated model

3. Compare Update Rollback Complete IU Basics

4. Four methods are available Full - Comparison uses a full GIS extract Sub_id – Comparison uses a subset of the full GIS extract Most common Transactions (add/delete) – relies on GIS to provide all add/delete transactions Transactions (add/delete/update) – relies on GIS to provide all add/delete/update transactions Simplest, in terms of processing, but only those customers using ABB Data Maintenance tool can use this method IU Basics - Compare

5. All IU processing, but especially the Compare stage, is dependent on the ABB_INT_ID columns These values are the link between the GIS system and the NM-DMS database Values should never be reused Actually, they can, but must be careful Values must be unique within a table, but can be duplicated in different tables ie. Device.abb_int_id values must all be unique, but they can overlap values in the line table. IU Basics - Compare

6. Typical detection of an Add transaction Abb_int_id value 10 exists in the GIS extract, but not in the master Therefore, it needs to be added to the master Special care is needed when writing the GIS extraction tool Often, there is not an exact one to one match between GIS and NM-DMS objects ABB_INT_IDs must be generated consistently between extracts IU Basics – ABB_INT_IDs

7. Typical detection of an Add transaction Abb_int_id value 10 exists in the GIS extract, but not in the master Therefore, it needs to be added to the master Special care is needed when writing the GIS extraction tool Often, there is not an exact one to one match between GIS and NM-DMS objects ABB_INT_IDs must be generated consistently between extracts IU Basics – ABB_INT_IDs

8. IU Basics – ABB_INT_IDs node Node/line 1 5 4 3 1 5 4 3 2 2 node GIS NM-DMS

9. IU Compare - Full • 3 4 • 5 6 7 1 2 3 4 CSF Master

10. IU Compare – SUB ID 1 2 3 4 5 6 7 8 1 3 4 10 8 9 CSF Master

11. IU Compare – SUB ID 1 2 3 4 5 6 7 8 1 3 4 10 8 9 8 12 CSF Master

12. IU Compare – SUB ID • Only compare against what is in the CSF • If you delete an entire SUB_ID value, the extractor must create an entry in the AFFECTED_SUBID table • Rare! • Boundary objects – Tie points, may be extracted for feeder 1 or feeder 2 • Objects can move between sub_id sets, via updates • Again, MUST be extracted consistently

13. IU Compare – Add/Delete transactions • Add/Delete transactions are explicitly specified in the CSF.csf_trans table • No detection of add/deletes is done by the compare process • Updates are still detected • Including link changes • Including path points

14. IU Compare – Add/Delete/Update transactions • Add/Delete/Update transactions are explicitly specified in the CSF.csf_trans table • No detection of add/deletes/updates is done by the compare process • Updates are still detected • Including link changes

15. IU Compare – Path Points • Path Points require special IU rules, as individual path points do not have abb_int_id values. • Brute force comparison during line update detection • For each line • Each set of path points, both geographic and schematic are compared • Any difference in X,Y coordinates, or in number of path points, trigger an update • The update is the complete replacement of the list of path points

16. IU Compare – Transactions Types • Four transaction types • Add • Delete • Update • Temporary ID Change • Link Change – now modeled as a delete/add pair of transactions

17. IU Compare – Link Changes • Link changes are changes in internal object dependencies • Ex. • Device -> Line • Line -> Node 1 • Line -> Node 2 • Load -> Node • Source -> Node • Capacitor -> Node • Mpoint -> Node • Site -> Node

18. IU Compare – Link Changes • Special case for lines • If a line has a node link change(either node 1 or 2) • A delete/add pair is created for the line • A delete/add pair is also created for each device on the line

19. IU Compare – Circular ID changes • Object character IDs must remain unique • Problems occur when IDs are swapped • Device ID #1 is changed to Device ID #2 • Device ID #2 is changed to Device ID #1 • Cannot be performed directly, as it will violate unique restriction • This “Circular” dependency is detected, and a temporary ID transaction is injected • Device ID #1 is changed to dummy value • Device ID #2 is changed to Device ID #1 • Device ID #1 is changed to Device ID #2 • No unique ID violation

20. IU Compare – Updates • Objects are matched using their abb_int_id values • Columns are compared, based on csf_column_comp table

21. IU Compare – Security Checks • Delete object with Tag • Delete node with lines/jumpers attached • Delete node with temporary generator • Delete line with line cut attached • Delete node/line with phase jumper attached • Delete device, load, source that is part of current outage • Unique ID reference • No options available to prevent unique ID violation

22. IU Update - Basics • Updates are done atomically – so that model is not corrupted in the event of database or hardware failure • Updates also save all necessary data for the rollback stage

23. IU Update – Order of updates • Check SHM for available space • Ways to increase space • ADM_DATABASE.SHMBUF • Manipulate XXX_FREE tables • Deletions • Additions • Updates

24. IU Update – Order of deletions • Node objects • Line device • Line • Node • Memo • Feeder • Substation • ATO • Node objects • Load • Capacitor • Site • Source • Mpoint

25. IU Update – Order of additions • Node • Line • ATO • Line device • Node objects • Memo • Feeder • Substation • Node objects • Load • Capacitor • Site • Source • Mpoint

26. IU Update – Order of updates • Line device • Path Point • Line • Node objects • Node • Memo • Feeder • Substation • ATO • Node objects • Load • Capacitor • Site • Source • Mpoint

27. Typical Delete transaction processing • Create csf_reversal record • Create CHG_XXXXX record • Delete from base table • Delete from memory • Send message

28. Typical Add transaction processing • Create csf_reversal record • Insert into base table, after getting next free fpos • Lookup any reference fpos values (link ids) • Insert into memory • Update csf_reversal record with new fpos • Send message • NO CHG_xxx table entry for additions

29. Add transaction processing, for a link change • Steps are as for a regular add • Additional step to lookup the saved status value in the CHG_XXX table • Stored there during the delete

30. Looking up link ids • FPOS values vary between CSF and master • Example – Adding line with abb_int_id 50 • It has no_key_1, no_key_2 references of 15,20 in CSF • These are the FPOS values, not abb_int_id values • Must query CSF to determine abb_int_id value of node where fpos = 15, 20 • Then, when inserting into master db, must use these node abb_int_id values to lookup the correct fpos value

31. Looking up link ids CSF Master Line – fpos 99 abb_int_id 50 no_key_1 1200 no_key_2 1501 Node – fpos 1200 abb_int_id 100 Node – fpos 1501 abb_int_id 101 Line – fpos 30 abb_int_id 50 no_key_1 10 no_key_2 15 Node – fpos 10 abb_int_id 100 Node – fpos 15 abb_int_id 101

32. Looking up link ids • The FPOS to abb_int_id translation is also performed when creating the CHG_XXX table entries • The CHG_XXX tables are exact copies of their matching object tables, with some additional columns • These columns support the fpos-abb_int_id lookup values, and sequencing of updates • Common cause of failure during the update process

33. Typical Update transaction processing • Create csf_reversal record • Create chg_XXX record • Lookup up record from CSF • Update fields in master db • Update in memory • Send message • Path points are slightly different. • All path points for a line (geo/schem) are replaced, even if only one has changed

34. Updates – temporary states • Temporary (as-operated) status is not modified • EXCEPT FOR • When built phases change, closed phases are adjusted accordingly • Ex. • Device is built ABC, closed ABC • Built phases are changed to BC • Closed phases are modified to BC

35. Rollback • Simply reverses all applied transactions • Update processing, in reverse • Based on csf_reversal table

36. Rollback – Order of transactions • Insertions • Deletions • Updates

37. Rollback – Reversing insertions, order • Line device • Line • Node objects • Node • ATO • Memo • Feeder • Substation • Node objects • Load • Capacitor • Site • Source • Mpoint

38. Rollback – Reversing deletions, order • Node • Node objects • Line • ATO • Line device • Memo • Feeder • Substation • Node objects • Load • Capacitor • Site • Source • Mpoint

39. Rollback – Reversing updates, order • Line device • PathPoints • Line • Node objects • Node • ATO • Memo • Feeder • Substation • Node objects • Load • Capacitor • Site • Source • Mpoint

40. Complete • Cleanup all records needed for rollback • Csf_reversal • Chg_xxxx • Modifies CSF status in ADM_DATABASE table

41. ADM_DATABASE Status values • 1 – Indicates a master database • 2 – A valid case study, ready for compare • 3 – Casestudy, but corrupted – failed on creation • 4 – Update attempt failed • 5 – Rollback failed • 6 – Successful completed • 7 – Casestudy, but not valid for the IU process • 8 – Update successful

42. External script hooks • Each script takes arguments of • Astatus id, astatus password, astatus dbname(TNS connect) • Scripts are references using the environment variables below • PRE_DIFF_CHECK_SCRIPT • Called before performing the Compare • POST_DIFF_CHECK_SCRIPT • Called after the Compare completes • POST_INCR_SCRIPT_NON_COMPLETE • Called after Update is complete • POST_ROLLBACK_SCRIPT • Called after Rollback is complete • POST_INCR_SCRIPT • Called after Complete is complete

43. External script hooks • Why use these? • Auto restart outageEngine after update or rollback • Auto run nodeLoc program, to keep node_location table correctly populated • Update customer counts on loads • Any other processing you need

44. Some options • PathPoints in memory • If enough memory is available, read all the CSF path points into memory at once, instead of line by line during the compare • Cut roughly 25% off Chicago compare times • Lookup Link IDs • As discussed before, many database queries are needed to lookup abb_int_id link values from the CSF during the update • If the abb_int_id values(In the CSF only) are used in place of the minimized FPOS values, these lookup steps can simply be skipped. • Still able to load the CSF for viewing using the GUI • Not able to run opmanager, outageEngine, server apps

45. Some options • FeederSub compare and update • If set, include feeder and substation tables in the IU process • Can use sub_id or full method • The ID column is both tables is treated as the abb_int_id value

46. IU Troubleshooting • The previous slides contain everything you need to know to immediately troubleshoot any IU problems • HEHEHE • IU is one of the most complicated processes we have

47. IU Troubleshooting • So it failed, now what do you do? • First, check the error log, usually located in the /tmp directory • Btw, this location can be changed by using the TMPDIR env variable • Also, check the output of the incrUpd process itself

48. IU Troubleshooting – Common errors • Corrupt data in the master database • Fails when trying to create chg_xxx records • Ex. • Device -> li_key is 0, or points to a non-existent line • When trying to lookup the abb_int_id value to save in the chg_xxx table, failure

49. IU Troubleshooting – Common errors • Solution • Simply delete the offending object • Because the link data is bad, its not loaded into the GUI, or the server SHM • Once deleted, its detected as an ADD transaction • Detection of these problems can be done before hand using the pre diff check script

50. IU Troubleshooting – Common errors • Tag added after compare stage • Jumper, generator added after compare stage • Unique ID violations, temporary objects