5 Replies Latest reply on Sep 10, 2013 6:15 AM by hanyong

    Bulk UnitBatch and Batch Deletion

    vwitzel

      Hi all,

       

      I am facing a situation where a module containing PIUnits was deleted from the MDB prior to all child UnitBatches having been deleted. What is the best way to resolve this situation? My understanding is that I could potentially write a macro using the PI SDK to find all of these problem UnitBatches, or potentially use the PI SQL Commander (part of the PI OLEDB Provider) to do the same. Could anybody share their experiences and/or code / queries with me to accomplish this?

       

      Much appreciated!

       

      -Vincent

        • Re: Bulk UnitBatch and Batch Deletion
          hanyong

          Hi Vincent,

           

          Just to share with you what I know by quickly testing a scenario where the module of unitbatch and batch is deleted. First of all, once the module is deleted, The unitbatch will no longer show up is searches for PIUnitBatch objects. This behavior is observed in both PI OLEDB Provider and PI SDK (both PIModuleDB.PIUnitBatchSearch or PIModule.PIUnitBatchSearch). 

           

          The unitbatch is still accessible if it is part of a batch. Through PI SDK we can search for the batch (PIBatchDB.PIBatchSearch method), and getting the associated unitbatches (PIBatch.UnitBatches property). If the module is deleted, accessing the PIUnitBatch.PIUnit will throw an exception "The target server database failed to load.[-16001] Module does not exist.". I guess you can catch the exception and remove the unitbatch if the module is no longer accessible. Something like:

           
          PISDK.PISDK myPISDK = new PISDK.PISDK();
          PISDK.Server myPIServer = myPISDK.Servers.DefaultServer;
          PISDK.PIBatchDB myPIBD = myPIServer.PIBatchDB;
          
          PISDK.PIBatchList myPIBL = myPIBD.PIBatchSearch("*-1d", "*");
          
          // loop through all the batches in search result
          foreach (PISDK.PIBatch myPIBatch in myPIBL)
          {
              // loop through all the unitbatches associated with each batch
              foreach (PISDK.PIUnitBatch ub in myPIBatch.PIUnitBatches)
              {
                   try
                   {
                       PISDK.PIModule m = ub.PIUnit;
                   }
                   catch (Exception ex)
                   {
                       if (ex.Message == "The target server database failed to load.[-16001] Module does not exist.")
                       {
                           // delete the ub
                           myPIBatch.PIUnitBatches.Remove(ub);
                       }
                   }
              }
          }
          

          ps: Sorry the code snippet is in C# instead of VB.

           

          The code snippet is not tested extensively, I would still recommend that you test this before using it to delete all the unitbatches with no reference to a module.

           

          I don't think It is possible to do the same via PI OLEDB Provider, because the way batch and unitbatch are associated. pibatch..piunitbatch table has a batchuid column that allows us to get the associated batch that a unitbatch is part of but there is no information exposed in the pibatch..pibatch table for a reverse association. 

            • Re: Bulk UnitBatch and Batch Deletion
              vwitzel
              Hi Han,
              Thanks for sharing. I followed your explanation and code, but unfortunately I do not have access to Visual Studio to try to compile C# code. My experience with C# is limited, so I may be off base in terms of that requirement. Please let me know if that is the case. 
              In terms of removing the UnitBatches, the approach we had come up with in the meantime was doing a comparison of:
              i) The tags of ptclass "base" and descriptor "*unitbatch*"
              ii) The ModuleUniqueID of all remaining PIUnit's
              Those tags which do not exist in the list of ModuleUniqueID's correspond to the deleted PIUnit's, and it is our understanding that we could delete these tags to delete the associated UnitBatches. Please advise if you see that differently.
              In terms of deleting the stray batches - we wrote a VBA macro to search for all batches and UnitBatches across the time range in question. For those batches which do not have a a PIUnit in their results, we would pull their UniqueID and delete it from the BAE9... tag using a piconfig script. Do you see any issues with this approach? Also, do you see any reason why we should use a PIAsyncStatus object with the batch search? In a previous VBA code I had seen, this was used in conjunction with a sub routine to basically pause execution until the search is complete. Do you think this is necessary? For example:
              Set ubl = srv.PIModuleDB.PIModules.Item(strPath).PIUnitBatchSearch("*-20y", "*", , , , , asy)
              AsyWait asy
              
              ...
              
              Public Sub AsyWait(asy As PISDKCommon.PIAsynchStatus)
                  While asy.ProgressPercent < 100
                      DoEvents
                      Sleep 100
                  Wend
              End Sub
              

               Thanks again :)

                • Re: Bulk UnitBatch and Batch Deletion
                  hanyong

                  To be honest, because the unitbatches will no longer be found if the module or PIUnit is deleted. And If you are already going to go through all batches to find orphan unitbatches. Removing them from the batches should effectively avoid any issues of finding these batches and potentially causing any errors with applications using these information. It would seem like upside of removing the internal tags from the PI System is is really to save some disk space that the tag takes up or to reduce the used tag count, is that what you are concerned about?

                   

                  If so, yes you can attempt to remove the base tags that are used to store unitbatch information for the PIUnit. However, this is the internal mechanism of the batch database you are fiddling with, so it is not strictly a supported scenario. If you think it is not necessary to delete the tags, I would say just leave the tags and remove reference to the orphan unitbatches from the batches to prevent any application errors. 

                   

                  My code snippet basically highlights the logic and methods that you can use with PI SDK to go through batches for a time range and removing references to orphan unitbatches. Since you have the same logic in mind, you probably could write it in VB by yourself as well. It would be something like:

                   
                  Sub DeleteOrphanUnitBatches()
                      Dim piserver As PISDK.Server
                      Set piserver = PISDK.Servers.DefaultServer
                      
                      Dim myPIBD As PISDK.PIBatchDB
                      Set myPIBD = piserver.PIBatchDB
                      
                      Dim myPIBL As PISDK.PIBatchList
                      Set myPIBL = myPIBD.PIBatchSearch("*-1d", "*", "*", "*", "*", Nothing, Nothing)
                      
                      Dim myPIBatch As PIBatch
                      Dim myPIUnitBatch As PIUnitBatch
                      Dim myPIModule As PIModule
                      Dim i, j As Integer
                      For i = 1 To myPIBL.Count
                          Set myPIBatch = myPIBL.Item(i)
                          For j = 1 To myPIBatch.PIUnitBatches.Count
                              On Error GoTo errMyErrorHandler
                  
                              Set myPIUnitBatch = myPIBatch.PIUnitBatches.Item(j)
                              Set myPIModule = myPIUnitBatch.PIUnit
                  NextUnitBatch:
                          Next j
                      Next i
                      Exit Sub
                  errMyErrorHandler:
                      If Err.Description = "The target server database failed to load.[-16001] Module does not exist." Then
                          myPIBatch.PIUnitBatches.Remove (myPIUnitBatch)
                      End If
                      Resume NextUnitBatch
                  End Sub
                  

                  Again, I haven't tested this extensively, please try it out in a test environment before using it.

                   

                  AsynchStatus object as a parameter for the search method is optional. So you can actually leave it out, or set it to Nothing in some cases (like how I am doing it for batch search in the code snippet above). Doing that will run the call synchronously, which is pretty much the same effect that you have now, based on your code snippet.

                   

                  Usually we would only use it to run the call asynchronously so that the main program thread and handle other tasks while the call is running, like you want to do something between calling PIUnitBatchSearch method and AsyWait subroutine. Or you want to run multiple asynchronous calls concurrently to improve overall application performance.

                    • Re: Bulk UnitBatch and Batch Deletion
                      vwitzel

                      Hi Han,

                       

                      Thanks for elaborating and clarifying the use of the asynchronous object. Our client has expressed an interest in having the PIUnitBatch tags removed, hence our approach of deleting those. They are an EA customer, so they aren't concerned about tag counts, they just found it to be cleaner. If, of course, you think something could be broken as a result of deleting the PIUnitBatch tags relating to no longer existing PIUnits, please let me know.

                       

                      I was curious about your code, so I tried to get it to work on my machine, where I had dummied up a few problem scenarios (e.g., deleting Units without prior deletion of their UnitBatches). Unfortunately, I ran into an "automation error" (error code 800405e0) on line 16. I thus added another error handler to go to line 22, but I continued having errors on subsequent iterations of the For loops.

                       

                      Just out of curiosity, how / in what tag does PI relate a PIUnitBatch to a PIBatch?

                       

                      Thanks,

                       

                      -Vincent

                        • Re: Bulk UnitBatch and Batch Deletion
                          hanyong

                          To be honest, how batch and unitbatch information are hashed and stored as PI tags is not exposed. I do not have 100% idea how it works exactly as well. Hence deleting the unitbatch tags is not a supported scenario. 

                           

                          My recommendation would be to leave the tags alone, after the unitbatches has been removed from the batches, since they will no longer show up.

                           

                          As for the error with the code, thats why I have been saying that you should be testing it before using it =) Anyway I've updated the code in the previous post. You can check if that will work better for you.