Relational Database(rdbms via odbc) Interface

PI Batch Database Replication with Module Database

PI SDK divides the PI Batch Database into several object collections. The simplified object model is shown in the following figure:

A more detailed description of each object can be found in the PI SDK Manual.

The RDBMSPI Interface currently replicates these objects from the three main collections found in the PI Batch Database. These collections are:

• 
  PIBatchDB stores PIBatch objects
  
• 
  PIUnitBatches stores PIUnitBatch objects
  
• 
  PISubBatches stores PISubBatch objects
  

Each aforementioned object has a different set of properties. Moreover, it can reference its parent object (object from the superior collection) via the GUID (Global Unique Identifier) – 16 byte unique number. This GUID can be used as a key in RDB tables to relate e.g. the PIUnitBatch records to their parent PIBatch(es) and PISubBatches to their parent PIUnitBatch(es). The structure of the RDB table is determined by the available properties on a given object. In the following tables list the description of the properties of each PI SDK object and the corresponding data type that can be used in an RDB table. The third column defines the corresponding placeholder required for the INSERT statement:

PI Batch Object

Property	RDB Data Type	Placeholder
Batch ID	Character string up to 1024 bytes	BA.ID
Product	Character string up to 1024 bytes	BA.PRODID
Recipe	Character string up to 1024 bytes	BA.RECID
Unique ID	Character string 16 bytes	BA.GUID
Start Time	Timestamp	BA.START
End Time	Timestamp	BA.END

PIUnitBatch Object

Property	RDB Data Type	Placeholder
Batch ID	Character string up to 1024 bytes	UB.ID
Product	Character string up to 1024 bytes	UB.PRODID
Procedure Name	Character string up to 1024 bytes	UB.PROCID
Unique ID	Character string 16 bytes	UB.GUID
PI Unit	Character string up to 1024 bytes	UB.MODID
PI Unit Unique ID	Character string 16 bytes	UB.MODGUID
Start Time	Timestamp	UB.START
End Time	Timestamp	UB.END

PISubBatch Object

Property	RDB Data Type	Placeholder
Name	Character string up to 1024 bytes	SB.ID
PI Heading	Character string up to 1024 bytes	SB.HEADID
Unique ID	Character string 16 bytes	SB.GUID
Start Time	Timestamp	SB.START
End Time	Timestamp	SB.END

PI Batch Database Replication Details

As stated above, the interface scans the PI Batch Database in timely manner. After each scan (that is, after an execution of that many INSERTs as there were newly arrived entries into the PI Batch Database since the last scan) the number of successfully inserted rows is written into the Managing Tag. The interface determines what was the most recent timestamp sent to RDB and therefore allows for safe restarts/temporary interface stops (that is, after restart the interface begins to replicate the not-yet-stored batches in RDB).

The PI SDK provides two search functions for filtering the PI Batch Database entries. The search criteria can be defined through keywords, which have the same syntax as the corresponding placeholders, but are prefixed with slashes '/'. The summary of all Batch related keywords can be found in section PI Point Configuration.

Note: Both PIBatch and PIUnitBatch objects must be closed. This means they must have the non-empty 'End Time' property. The interface will not store the open PIBatches or PIUnitBatches. Exceptions to this rule are PISubBatches. PISubBatches are always sent to RDB at the time when their parent PIUnitBatch gets an 'End Time'.

Three tables are required for the data extracted from the PI Batch database.

Example of RDB Tables Needed for PI Batch Database Replication

Table Structure for PIBatch objects	Table Structure for PIUnitBatch Objects
BA_START (SQL_TIMESTAMP)	UB_START (SQL_TIMESTAMP)
BA_END (SQL_TIMESTAMP)	UB_END (SQL_TIMESTAMP)
BA_ID (SQL_VARCHAR)	UB_ID (SQL_VARCHAR)
BA_PRODUCT (SQL_VARCHAR)	UB_PRODUCT (SQL_VARCHAR)
BA_RECIPE (SQL_VARCHAR)	UB_PROCEDURE (SQL_VARCHAR)
BA_GUID (SQL_CHAR[37])	BA_GUID (SQL_CHAR[37])
	UB_MODULE (SQL_VARCHAR)
	UB_GUID (SQL_CHAR[37])
Table Structure for PISubBatch Objects
SB_START (SQL_TIMESTAMP)	SB_HEAD (SQL_VARCHAR)
SB_END (SQL_TIMESTAMP)	UB_GUID (SQL_CHAR[37])
SB_ID (SQL_VARCHAR)	SB_GUID (SQL_CHAR[37])

The arrows show the keys that form the relationship between these three tables. PISubBatches can form their own tree structure allowing for a PISubBatch object to contain the collection of another PISubBatch. To express this hierarchy in one table, the interface constructs the PISubBatch name in a way that it contains the above positioned PISubBatches divided by a backslashes '\' (an analogy with the file and directory structure). In our case the SB_ID column will contain items like:

…

PIUnitBatch_01\SB_01\SB_101

PIUnitBatch_01\SB_01\SB_102

…

PIUnitBatch_01\SB_01\SB_10n

…

Because sub-batches have different properties than their parent objects – unit-batches, an independent INSERT is needed. Moreover, the unit-batch Managing Tag needs to know the sub-batch Managing Tag name. A special keyword /SB_TAG ='subbatch_managing_tag' must therefore be defined in the Extended Descriptor of the unit-batch Managing Tag. At the time the unit-batch is closed, the interface replicates the related unit-batch properties, and also replicates the underlying sub-batches.

Refer to these examples that replicate all batches, unit-batches plus their sub-batches over the period of last 10 days:

• 
  Example 5.2a – Batch Export (Module Database required)
  
• 
  Example 5.2b – UnitBatch Export (Module Database required)
  
• 
  Example 5.2c – SubBatch Export (Module Database required)
  

11.