I'm planning a new rdbms interface setup to transfer data between two Oracle databases via 2 PI servers (pi-to-pi between the PI servers, RDBMS instance running on each PI Server), using STRING tags .
What I'm struggling to get my head around is timestamps. The SQL query will return multiple records, which all need to be written to a single tag (shouldn't be more than 15 records). It is further complicated by the fact that these records will all have the same timestamp in the sql table.
My reasoning for using a single PI tag for these records is because this will allow me to only use roughly 400 tags. If I were to create a tag for each test+area, I'm looking at around 3000 tags which I'd like to avoid.
|2017/02/14 13:00||12LN||a||0,2||2017/02/14 16:03||acceptable|
|2017/02/14 13:00||12LN||b||0,1||2017/02/14 16:03||acceptable|
|2017/02/14 13:00||12LN||n||40||2017/02/14 15:20||A little high|
We don't really care what timestamp is used when storing the data in PI, as long as the OUTPUT rdbms interface can insert the records into the destination db with the cloned data.
The above example records would be return by the SQL Query, but I'd concatenate the column values with a | delimiter. On the output side I'll insert the records via a Stored Procedure which can split the string and insert into the desired table/fields.
On the RDBMS to PI interface, would setting Location5 = 2 work, and, on the Output side of the fence, will the interface process the multiple 'records' at the same PI timestamp.
I'm in the process of setting up a test environment, but thought I'd pose this question in the interim.