19 Replies Latest reply on Feb 10, 2015 3:03 PM by sdriscoll

    Push data from PI to DB2 periodically

    ilyas

      Hello Everyone,

       

      I have one requirement on to push data from PI to DB2 periodically.

      PI is storing 1 sec data and I want to push 10min avg data to DB2 using any module of Data Access Suite.

       

      Which is the most efficient method (consuming less resources and time) to push data into DB2?

      What are different methods to do it?

      Can I do it use ACE or PI WebService etc.?

       

      Plz suggest.

      Thanks

        • Re: Push data from PI to DB2 periodically
          Eugene Lee

          Hi Ilyas,

           

          I would recommend ACE because the ACE engine will be able to take care of the scheduling for you. Most of the other products in the Data Access Suite do not have built in scheduling functionalities. Therefore, you will have to build the scheduling functionalities yourself. Here is also a KB article that describes the features of our Data Access Suite should you choose not to use ACE.

           

          https://techsupport.osisoft.com/Troubleshooting/KB/KB01046

          1 of 1 people found this helpful
          • Re: Push data from PI to DB2 periodically
            gachen

            One way you could do this is by using the PI RDBMS interface. If you have the 10 minute average calculated in a PI tag, this can be the source tag for an output tag that writes back to DB2 via the RDBMS interface automatically when it receives a new value. This setup would probably be the easiest to configure and to maintain.

             

            If you want to use something in the Data Access Suite, your best bet would probably be either PI OLEDB Provider, or PI ODBC driver.

            • Re: Push data from PI to DB2 periodically
              xwang

              Hi llyas,

               

              Do you wish to store the avg value into PI Server and DB2 or just in DB2 please?  If wish to store the value into 2 servers (PI Server and DB2), the above methods from Eugene and Gavin are the best solution.  However, if just wishes to store the data into DB2, not in PI Server, you could use PI AF SDK, PI Web Services, PI OLEDB Provider, or PI ODBC driver.  This will depend on which function package you wishes to do on your project, or could be said which platform or language you are prefer.  Expect your reply for the deep disussion.

               

              Xi Wang

              PI Developer Club

                • Re: Push data from PI to DB2 periodically
                  ilyas

                  Hi All,

                   

                  I want to store avg data only is DB2 and not in PI.

                  Can ACE handle around 80k tags pushing 10 min avg data to DB2.

                  Which is easy to handle and maintain for scalability?

                   

                  Thanks

                    • Re: Push data from PI to DB2 periodically
                      Eugene Lee

                      Hi Ilyas,

                       

                      If you have high load, then this is where ACE will shine because you can scale it to multiple machines. From the ACE user manual:

                       

                      Scaling ACE Schedulers to Multiple Machines

                       

                      ACE provides the ability to configure multiple ACE Schedulers running on multiple machines to simultaneously execute calculations stored in the PI Module Database (MDB). You can use this feature to distribute resource-intensive calculations across multiple machines. This is helpful in avoiding resource constraints that can result in skipped calculations or degraded performance.

                        • Re: Push data from PI to DB2 periodically
                          ilyas

                          Hi,

                           

                          Any sample ACE code to connect thro odbc and oledb?

                          and pushing data into it.

                          Thanks

                            • Re: Push data from PI to DB2 periodically
                              Dan Fishman

                              ACE is typically written in VB.NET so we can provide many sample generic codes such as this from Microsoft. You probably want to do some performance testing to understand how long 10 minute tag averages take.   Quite a few approaches to solve this problem.  You can try to use all AF or PI SDK within your calculation, search for the tags and then obtain the average or you can use ACE functions to calculate the average. Using ACE built in equations if the tags are used as inputs ACE will actually cache the inputs so your calculations will be faster than using pure SDK; however, a disadvantage is you will need a very large PI Module Database.  Using AF SDK, you can actually do bulk calls within ACE if you are using the most current PI Server.

                               

                              Other options are custom code using the AF DataCache or a SQL server job using PI OLEDB Provider. 

                            • Re: Push data from PI to DB2 periodically
                              sdriscoll

                              Eugene,

                              Not sure I completely agree. By that logic pretty much any of our Data Access products can be deployed to multiple servers to "scale" up.  The plus to using ACE would be the ability to manage all instances from one Ace Manager. The other plus, as mentioned before, is less overhead in getting up and running.

                               

                               

                              Ilyas,

                              I want to make a slight distinction, at this point using ACE you will mainly dealing with VB.NET code as opposed to "ACE code". I don't know the proper terminology but ACE is basically a module within VS to provide an easier mechanism for using PI tags and setting up scheduling, but you program everything in VB.

                               

                              As for a sample code, here is C# code for connecting to the PI ODBC driver which you can extrapolate to VB and connecting to another driver,

                              string connectionString = null;
                              OdbcConnection cnn;
                              OdbcTransaction transaction = null;
                              OdbcCommand command = new OdbcCommand();
                              
                              connectionString = "Driver={PI ODBC Driver}; DAS=SDSQL; Trusted_Connection=Yes; Data Source Type=PIOLEDBEnt; Data Source=<PIServer>; Data Source Options={Integrated Security=SSPI;}";
                              
                              
                              cnn.Open();
                              transaction = cnn.BeginTransaction();
                              command.Connection = cnn;
                              command.Transaction = transaction;
                              command.CommandText = "SELECT ID, name FROM [PIEFGen_PBSUPPORT1].[EventFrame].[EventFrameTemplate]";
                              OdbcDataReader reader = command.ExecuteReader();
                              

                               

                               

                              1 of 1 people found this helpful
                        • Re: Push data from PI to DB2 periodically
                          sdriscoll

                          I'm going to nix the SQL-based products as an option. At this point your just routing a LOT of data and there are too many layers and transitions occuring to make this worth while, unless its situationally dependent.

                           

                          ACE could work but I would again nix that because it will most likely be the limiting factor for transfering data.

                           

                          Let's take a step back and look at the picture as a whole. You have 3 potential bottlenecking areas: PI Server, DB2, and the transfer mechanisms. The main question is where is the bottleneck likely to occur.

                           

                          First, consider the two servers, how do they handle data throughput and how dependent on the transfer mechanism are they. I can't speak to non-PI products but for PI Server you can assume it will not be the limiting factor so long as you are using atleast PI Server 2012, have good memory, and are generally not having performance issues already. Also consider that this is a pretty large load since you have 80k tags, at 1sec storage rates, averaged over 10 min. That's 48mil events, not inlcuding the fact that you're performing a calculation on them and not just pulling the data. You may want to consider doing the caluclations client-side. Also make sure to space them out if possible.

                           

                          Second, the data transfer mechanism. For a job such as this I have to go with AF SDK as my choice. Indeed you can perform this with any of the products but you have more control by creating a custom application using AF SDK. Only issue is the time overhead it will take to get something running. Most of our other Data Access products use AF SDK under-the-hood anyway, but with more overhead for conversions and such. You can also implement multi-threading, bulk calls, and caching at your whim...as Dan Fishman pointed out.

                           

                          Hope that helps!

                          1 of 1 people found this helpful
                            • Re: Push data from PI to DB2 periodically
                              ilyas

                              Hi,

                               

                               

                              Which method give best performance for data pushing for large data points?

                              Is it AF SDK or PI SDk or somethig else custom app?

                               

                              Thanks for all your responses.

                                • Re: Push data from PI to DB2 periodically
                                  sdriscoll

                                  We're venturing into a lot of murky water here.

                                   

                                  The question you posed, and indeed all performance questions, generally aren't as simple as which method/protocol/architecture is better. It really is going to be situationaly dependent based on many factors. The only sure-fire way of knowing is performing tests in-house on your own system.

                                   

                                  I'll try to be specific and to the point with my next response. PI SDK and AF SDK essentially use the same mechanisms for accessing the PI Data Archive. Indeed, older versions of AF SDK (pre 2.6 I believe) actually used PI SDK to communicate with the PI Data Archive. From that point of view you will see similar performance. However, the benefits of AF SDK are the ability to run in parallel or use bulk calls, as opposed to PI SDK running in serial. In either case you will need to write a custom app to use PI SDK or AF SDK.

                                   

                                  AF SDK is kind of a misnomer. AF SDK is our latest SDK and is slowly replacing PI SDK. We generally try to recommend AF SDK or PI SDK, and I'm honestly not certain what reasons they are to use PI SDK over AF SDK, but I know there are some.

                                   

                                  Maybe a user who has more real-world experience will have a better opinion on the matter.

                                   

                                  You may find the following links useful for a little perspective,

                                  Re: AF SDK performance, serial vs. parallel vs. bulk

                                  Re: Performance of retrieving values vis PI SDK APIs versus PiOleDB..

                                    • Re: Push data from PI to DB2 periodically
                                      ilyas

                                      Thanks All and Sean for your comprehensive and specific response.

                                      My important concern was performance b'coz I was looking for scalable as well as effective solution since tag count is going to increase so the solution shud be able to handle.

                                      I'm okay with customised PI AF SDK solution and thanks to replies. This has given me some direction to start my development.

                                        • Re: Push data from PI to DB2 periodically
                                          bshang

                                          Just wanted to add a few cents to the already great discussion going on here.

                                           

                                          A great presentation for learning how to optimize AF SDK applications is this presentation from vCampus Live 2013. It compliments well the first link that Sean posted above. There is a natural progression of strategies as your AF SDK application becomes more intensive.

                                          1. Use bulk and/or parallel data access calls.

                                          2. Sign up for events using AFDataPipe.

                                          3. Cache events via AFDataCache.

                                          4. Pre-calculate using AF Analytics.

                                           

                                          For example, for 80k tags and 1s data rates, you may need to watch the memory footprint. Bulk calls and AFDataPipe are configured to do so, by delaying the instantiation of AF objects and list creation until the client requests them. See slide 24 in the presentation for example code on how to use this with AFDataPipe and the observer pattern. Reducing the memory footprint here is not about avoiding the paging file, running out of memory, etc. but the additional CPU time that is required by the .NET GC to maintain large intermediately-lived objects in memory. Many of the AF SDK calls are optimized to reduce the memory load/improve performance and is one of the many reasons that for highly scalable applications, AF SDK will be the way to go.

                                          1 of 1 people found this helpful
                                            • Re: Push data from PI to DB2 periodically
                                              Dan Fishman

                                              I like Barry's post!  I wouldn't bother signing up for events using the AFDataPipe since you then have manage the events yourself. Might be much simpler to use the AFDataCache since it takes care of cache for you!  Most people use ACE since it is a great way to schedule and manipulate PI data.  Probably not best for this type of extraction. 

                                               

                                              Pre-calculate if you need to store these in the PI System, clients need to often view the averages over large time-spans, you trigger alarms off the averages or do calculations such as finding the maximum 10 minute average over a number of days. 

                                    • Re: Push data from PI to DB2 periodically
                                      pthivierge

                                      Hello Ilyas,

                                       

                                      There was a lot of technical things discussed so far in this conversation.  But we did not talk about the very basic things, such as compression and why you need to store this data in another database, this is very important.

                                      A lot of our customers come to us after they implemented solutions to manipulate PI Data like this, and unfortunately if you do not start with a good base that can scale you may have problems reaching the performances you want to achieve.

                                       

                                      Let me ask some questions:

                                      • Is compression enabled on your tags that receives data every seconds? This is a big factor that can be a game changer for performances. 1 second data un-compressed would make a lot of data to retrieve from disk to memory and would take time.  With compression you'd have much less data to get from disk. People often think that you can lose data with compression, this is not true, but you need to configure it properly.  More info on compression : OSIsoft: Exception and Compression Full Details - YouTube , KB00699 - Compression Explained

                                       

                                      • Why do you need to send average data to the DB2 Database?  How it is used after that?  By knowing this we maybe able to propose you PI Alternatives if applicable, it will help us understanding what you are trying to achieve and better guide you, with proper solutions. ( have you realized that this creates duplicated data outside of its original data source?, In some circumstances,i.e. when a data collection interface cannot send data because of a network outage, you could have a situation where your average needs to be recalculated, how will you deal with that? )


                                      • Do you really need 80k tags averages? What it is used for?


                                      • What is the time interval of the average that you need?


                                      • Will you need to perform other calculations than the average later?


                                      With this information we will be in a better situation to make recommendations that will help your project to succeed on the long term.