3 Replies Latest reply on Oct 10, 2018 2:22 PM by W@d3

    Location2 = 1 and Location5=0, issue still archiving duplicates PI for RDBMS

    W@d3

      Greetings,

       

           I have an SQL database.

       

           In this database, the vendors machine will INSERT multiple rows into the sql table at a single time. Hence, the time stamp is the InsertTime and will be equal to the same value for multiple rows in the sql data table that my queries are querying. 

       

           I use InsertTime as my Timestamp.

       

           I also use InsertTime to know what group of rows was inserted together.

       

           I want to make sure I capture every single updated row.

       

           My SQL query looks kind of like the following:

       

           /SQL="SELECT [ValueToBeRecord] AS PI_VALUE, [InsertTime] AS PI_TIMESTAMP, 0 AS PI_STATUS FROM [SupervisorDB].[dbo].[View_Liner_S3_tiles]  WHERE [InsertTime] = (SELECT MAX(InsertTime) FROM [SupervisorDB].[dbo].[View_Liner_S3_tiles]) Order By [Timestamp] asc,[ID_tile] asc;"

       

           This query gets me the most recent group of inserted rows from my table pretty well and in the order that I desire.

       

           (about 15 rows as a time, it gets, based on the limited speed of the production machine.)

       

       

           I have Location2=1, so my query will select multiple rows at a time.

       

           I have Location5=0, so it should not archive the incoming event if the archive already has a value with the same timestamp? (In my case, I use InsertTime as my timestamp)

       

           However, despite location5 being equal to 0, it will archive the same group of rows from the sql data table as it did in the previous scan.

       

           Consequently, I end up with A LOT of duplicate values, especially when the production machine is not running.

       

           Can anybody help me? What am I missing?

        • Re: Location2 = 1 and Location5=0, issue still archiving duplicates PI for RDBMS
          Lal_Babu_Shaik

          Hi William

           

          Request you to check read before override option in optional parameters.  To check duplicate events generated from source query you can enable logs for testing the same.

           

          Debug parameters tab for PI Interface for RDBMS

           

          If you having duplicate values then would recommend location 2=1  and location 5 as follows

           

           

          thanks,

          :Lal

          1 of 1 people found this helpful
          • Re: Location2 = 1 and Location5=0, issue still archiving duplicates PI for RDBMS
            shillman

            Hello William,

             

             

            If I had to guess, your interface is scanning far more often than you are actually getting new data sets inserted into SQL. Thus, the interface is going to see the same query result set over and over again until it finally scans after a new result comes in. Regardless of the settings on the interface and PI point side to alleviate duplicate data, we can actually re-configure your PI points and SQL query to prevent the duplicate data in the first place.

             

            This interface allows you to create dynamic SQL queries. That is, you can use dynamic placeholders within your SQL query that only get a value at the time the query is run. That value is potentially different each time the query runs. In this case, we would want to redesign your SQL query like this and add the additional placeholder definition afterwards:


            /SQL="SELECT [ValueToBeRecord] AS PI_VALUE, [InsertTime] AS PI_TIMESTAMP, 0 AS PI_STATUS FROM [SupervisorDB].[dbo].[View_Liner_S3_tiles]  WHERE [InsertTime] > ? Order By [Timestamp] asc,[ID_tile] asc;"P1=TS

             

            The "TS" placeholder definition will use the current snapshot for this tag at the time of the query to replace the "?" within the SQL query. This way, when the query runs, it will only retrieve data with a timestamp newer than what you have already retrieved. Thus, if there is no new data in SQL, no data is retrieved. This way the interface will simply never encounter the previous result sets so there is no duplicate data to be concerned with.

             

            You can find more information on SQL placeholders in RDBMS in the user manual. Here is a relevant link from LiveLibrary:

             

            PI Interface for Relational Database (RDBMS via ODBC)

            • Re: Location2 = 1 and Location5=0, issue still archiving duplicates PI for RDBMS
              W@d3

              Hey everyone,

               

                   I got on the phone with the tech support and got exactly the help I needed.

               

                   It is no longer a problem.

               

                   This knowledge based article from OSIsoft was the exact answer to my problem. Link below.

               

                   KB01620 - PI RDBMS Interface is either not retrieving all or any of the data or is retrieving duplicate data

               

              Thank you everyone for your input.

              1 of 1 people found this helpful