AlistairFrith

Replacing Oracle DB with PI+AF - Migrating existing connections

Discussion created by AlistairFrith on Jan 16, 2014
Latest reply on Jan 20, 2014 by AlistairFrith

Hi, we are starting a project to replace a somewhat wasteful and inefficient Oracle system with PI+AF and one thing we need to do is update the systems that currently get their data from Oracle to get it from PI/AF instead.

 

They have one table in the Oracle DB which contains everything the client systems need:

  • Tag Name / ID
  • Extended Descriptor + various other PIPoint attributes 
  • Timestamp
  • Value
  • Status
  • Various other fields
  • A 'Copied' flag

They have a number of systems that use SQLServer Linked Tables to connect to this Oracle table and have SQL Server Stored Procedures that grab everything they are interested in for all the 'un-copied' records, and then set the records to 'copied'. I can change these stored procedures but they still need to get the same data from the new system.

 

They have one other system that uses another Oracle database. That database also links to the same table (but I don't know via what protocol) and uses the same general algorthm to get its data. Again, I can change the stored procedure but it must still get the same data.

 

I am given to understand that AF and PI support SQL Server Linked Tables through OLEDB Enterprise but what about this connection from another Oracle database? Can I do the same thing there?

 

Also, how would I go about modifying queries like this: 

 
/* Pull data from Mart_Data_Archive  */
set @sql = N'INSERT INTO UV.dbo.MART_DATA_ARCHIVE_load
               ([DM_MARTNAME],
                [DM_ID],
                [DM_TAGNAME],
                [DM_EXDESC],
                [TIME],
                [VALUE],
                [STATUS],
                [ENGUNITS],
                [READING_FROZEN],
                [SOURCE_SYSTEM_ID],
                [SOURCE_SYSTEM_RANK],
                [DATE_LOADED],
                [COPIED_TO_MART],
                [CHANGE_PENDING])
     SELECT * FROM OPENQUERY(LinkMDAP,
               ''SELECT DM_MARTNAME,
               DM_ID,
               DM_TAGNAME,
               DM_EXDESC,
               TIME,
               VALUE,
               STATUS,
               ENGUNITS,
               READING_FROZEN,
               SOURCE_SYSTEM_ID,
               SOURCE_SYSTEM_RANK,
               DATE_LOADED,
               COPIED_TO_MART,
               CHANGE_PENDING
     FROM MDASYS.MART_DATA_ARCHIVE
     WHERE (DM_MARTNAME = ''''UV'''') AND (COPIED_TO_MART = ''''X'''')
       '')'

There are the obvious PI value attributes here: tagname, timestamp, value and status. But there is also the extended descriptor, and a few other values that might be best stored or calculated in AF Attributes. Should I try to build up one query that grabs it all, or several queries that I then build into the final dataset within the stored procedure?

 

Any advice would be gratefully received!

 

Alistair

 

 

Outcomes