PI SQL Subsystem architecture

Statement handles

Most interactions between PI clients and Data Archive do not require the server to maintain any record of the client's operation. Any request for point information or archive data can be serviced using only the information provided by the client in the request itself.

The processing of SQL statements is different. When an SQL statement is processed, the Data Archive server must maintain a record of the statement's status to be able to perform subsequent operations. This is done by having PI SQL Subsystem allocate a statement handle when a client initiates the processing of an SQL statement. The client retains the handle's identifier and provides it to the server with every request.

The details of handle allocation and deallocation are managed internally by a PI API based client application, such as the PI ODBC Driver.

If connection between the client and Data Archive is lost, PI SQL Subsystem retains any statement handles allocated by the client. These handles become orphaned and cannot be accessed again. The handles are de-allocated when PI SQL Subsystem shuts down. During shutdown, pisqlss reports the total number of handles allocated during its run, and the number of orphaned handles that were aborted.

Concurrency

PI SQL Subsystem handles SQL processing for all client connections to Data Archive.

Operations such as parsing an SQL statement and fetching results are relatively inexpensive. Execution, however, might take time and system resources as data are obtained from other subsystems.