5 Replies Latest reply on May 27, 2013 1:55 PM by Gregor

    Values older than creation date in RDBMS

    gdigiorgio

      Hello everybody,
      in the configuration query of my RDBMS PI Point, using the condition "Date > ?;" with P1=TS, I obtail, correctly, all the values that belong to the new rows written on the DB. When I create a new PI Point, with this condition I start receiving all the values newer than the creation date. How can I get the previous values? I mean, if I delete the TS condition (with location2 = 1), I would have all the values but, everytime the query would be executed I expect all the values to be written again and again with the same timestamp. What I need is something that allows my new PI Point to collect all the old values just the first time and after, to start collecting only the new values (the values newer than the last written). A possibility should be to create a PI Point with an old creation date, but it seems it is not allowed. Of course, I could backfill the old values, but I would like to find a different solution. Any idea?
      Thanks in advance

        • Re: Values older than creation date in RDBMS

          Hello Giovanni,

           

          Please be sure to visit the following resources at OSIsoft Technical Support regarding preparation for backfilling:

           

          techsupport.osisoft.com/.../Backfilling+with+PIconfig.htm

           

          What you need to do mainly depends on the PI Server version. If the receiving PI Server is 2012 (3.4.390), you do not need to reprocess historical archives and do not need to delete 'Pt. Created' events.

           

          Please note that PI Interface for Relational Database (RDBMS via ODBC) is not included into vCampus. As a hint, please search the Interface manual for "input recovery modes". If you require additional assistance, please consider opening a ticket at OSIsoft Technical Support

          • Re: Values older than creation date in RDBMS
            Bannikov

            Giovanni,

             

            In such cases we simply wipe out PtCreated value from tag, and than this tag will receive all possible history in one call. To prevent from overflowing you can add TOP N clause to your query

              • Re: Values older than creation date in RDBMS
                gdigiorgio

                Sergey,

                 

                your solution works perfectly. Thanks!

                 

                Actually I created an element template that, once used for building an element, creates automatically some PI Point that uses the RDBMS interface to get values. I wonder if it is possible modify somehow this template to let it create PI Point without the "Pt Created" initial value. Probably that is not possible, in that case I will use your method.

                  • Re: Values older than creation date in RDBMS
                    Bannikov

                    We've developed a simple utility that deletes all 'PtCreated' values for specified tags - just for cases like this. I've not heard about is it possible to avoid 'PtCreated' value in PI Server 2010 and earlier, but I've heard something about it in PI Server 2012 - I'm not sure about it, but maybe something was changed (at least you really don't need to reprocess old archives, this is GREAT).

                      • Re: Values older than creation date in RDBMS

                        With PI Server 2012 points are still initialized with 'Pt Created' but ... please see Backfilling with piconfig.

                         

                        If you are not yet using PI Server 2012 and are seeking for an easy way to get rid of 'Pt Created' events, you may find the usage of PI OLEDB (Classic) useful:

                         

                        To verify before deleting:

                         

                         

                         
                        SELECT tag, time, value, DigString(status) as stat FROM picomp2 WHERE tag like 'XY%' AND DigString(status)='Pt Created' AND time between '*-10m' AND '*'
                        

                         To delete 'Pt Created':

                         

                         

                         
                        DELETE FROM picomp2 WHERE tag like 'XY%' AND DigString(status)='Pt Created' AND time between '*-10m' AND '*'
                        

                         

                         

                        Please use with caution! The delete will be executed immediately. No commit is required; no rollback possible.