11 Replies Latest reply on Nov 16, 2015 1:59 PM by aguzu

    RDBMS Interface Vs PI_STATUS

    PierreMarcil

      Hello, i try to figure how to input a digital state "shutdown" within RDBMS query.  reading doc i see I might be able to use PI_STATUSx, but I can t make it work.

       

      I have been trying something like this just for testing :

       

      select cast('2013-05-07' as datetime) PI_TIMESTAMP, 'BCQ:PTRM:TEST' PI_TAGNAME1, 0 as PI_VALUE1, 'shutdown' PI_STATUS1;

       

      Any tip/help/documentation would be appreciate, there is not much information on this in "Relational Database(RDBMS via ODBC) Interface, Version 3.19.1.x revision A"

        • Re: RDBMS Interface Vs PI_STATUS
          mhamel

          @Pierre: You need to use the digital state code for the status like 0 for 'good' and 254 for 'shutdown'. I was surprised that this information is not part of the manual.

            • Re: RDBMS Interface Vs PI_STATUS
              PierreMarcil

              select cast('2013-05-04' as datetime) PI_TIMESTAMP, 'BCQ:PTRM:TEST' PI_TAGNAME1, 4 as PI_VALUE1, 254 as PI_STATUS1

               

              This is giving me "Bad input"

               

              Seem I aint using right way to do it..

                • Re: RDBMS Interface Vs PI_STATUS
                  PierreMarcil

                  Juste did figured it, state is neg value :

                   

                  select cast('2013-05-03' as datetime) PI_TIMESTAMP, 'BCQ:PTRM:TEST' PI_TAGNAME1, 3 as PI_VALUE1, -254 as PI_STATUS1

                   

                  that way it work now

                    • Re: RDBMS Interface Vs PI_STATUS
                      mhamel

                      @Pierre: I am glad you figured out. Yes you are right, status values must be passed as negative value. Negative values interpreted as System Digital Set,  positive as Bad Input and 0 as Good value.

                        • Re: RDBMS Interface Vs PI_STATUS
                          PierreMarcil

                          Well i did apply live and... dang!  My interface is set to overwrite data when more than one record is receive on a timestamp, option "no null" and "read before overwirte" are enabled. Location 5 on my Pi Point is set to 1.  it do work well when one value have to be updated with a new value on the same timestamp, and end up with only one result at this particular timestamp.  but now, with "shutdown" status send, if my query run 5-10x per day, it generate 5-10x "shutdown" on the same timestamp.  Is there a way to end up with only 1 "shutdown" on a timestamp?

                            • Re: RDBMS Interface Vs PI_STATUS
                              mhamel

                              @Pierre: I think you will need to "manage" this on the archive side by activating the compression on the PI Point configuration. If you enable the setting Compressing to 1, this will remove any duplicate values.

                               

                              The /rbo and /Ignore_NULLS switches won't have a lot effect as the data relates to a new event in time which is set to 'shutdown' and it is not a repeated value returned due to the query itself.

                                • Re: RDBMS Interface Vs PI_STATUS
                                  PierreMarcil

                                  Compression is enabled already.  Been doing some testing since last week on this. Might have find why this occure, seem to happen only when tehre a good value already feed previously on the same timestamp where shutdown is sent.  Doing some more testing this moring to duplicate this problem.  Got a ticket also open with OsiSoft help desk (480218)

                                    • Re: RDBMS Interface Vs PI_STATUS
                                      mhamel

                                      @Pierre: I have taken a look at the open ticket on your problem. I think the best would be to wait and get more details from the log files taken out from the interface.

                                        • Re: RDBMS Interface Vs PI_STATUS
                                          PierreMarcil

                                          I m calling back Techsupport in next few minute, just found a way to reproduce it.  Here an exemple of the RDBMS query :

                                           

                                          select cast('2013-05-23' as datetime) PI_TIMESTAMP, 'BCQ:PTRM:TEST' PI_TAGNAME1, 10 as PI_VALUE1, -254 as PI_STATUS1

                                           

                                          union

                                           

                                          select cast('2013-05-24' as datetime), 'BCQ:PTRM:TEST' ,6, 0

                                           

                                          union

                                           

                                          select cast('2013-05-25' as datetime), 'BCQ:PTRM:TEST' ,7, 0

                                           

                                          union

                                           

                                          select cast('2013-05-26' as datetime), 'BCQ:PTRM:TEST' ,4, -254;

                                           

                                          run it once, then change value for 24 and 25, then rerun it, and the shutdown from the 26 going to be reinserted, it ll happen everytime you update value for 24 and 25.

                                            • Re: RDBMS Interface Vs PI_STATUS
                                              PierreMarcil

                                              ***27-May-13 10:07:06 dump of 4 events

                                               

                                              ---------------------------------------------

                                               

                                              pointid,recno,mode,timestamp,type,bits: value

                                               

                                              966661,885683,replace,23-May-2013 00:00:00,14,S,O,A,S,Q [0,0,0,1,0]: 0, -254

                                               

                                              966661,885683,replace,24-May-2013 00:00:00,12,S,O,A,S,Q [0,254,0,0,0]: 0, 0

                                               

                                              966661,885683,replace,25-May-2013 00:00:00,12,S,O,A,S,Q [0,0,0,0,0]: 3, 3

                                               

                                              966661,885683,precomp_arc,26-May-2013 00:00:00,12,S,O,A,S,Q [0,254,0,0,0]: 0, 0

                                               

                                              ........................................

                                               

                                              ***27-May-13 10:07:53 dump of 4 events

                                               

                                              ---------------------------------------------

                                               

                                              pointid,recno,mode,timestamp,type,bits: value

                                               

                                              966661,885683,replace,23-May-2013 00:00:00,14,S,O,A,S,Q [0,0,0,0,0]: 0, -254

                                               

                                              966661,885683,replace,24-May-2013 00:00:00,12,S,O,A,S,Q [0,0,0,0,0]: 2, 2

                                               

                                              966661,885683,replace,25-May-2013 00:00:00,12,S,O,A,S,Q [0,0,0,0,0]: 5, 5

                                               

                                              966661,885683,precomp_arc,26-May-2013 00:00:00,12,S,O,A,S,Q [0,254,0,0,0]: 0, 0

                                               

                                              ....................................

                                              • Re: RDBMS Interface Vs PI_STATUS
                                                aguzu

                                                We confirmed this is an issue with the PI Buffer Subsystem. It no longer occurs with PI Buffer Subsystem 4.4