Pi Odbc Manual

PI ODBC PI ODBC Driver User's Guide

software version 1.x most recent printing September 2001

©

1997-2001 OSI Software, Inc. All rights reserved RESTRICTED RIGHTS LEGEND

Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of the Rights in Technical Data and Computer Software clause at DFARS 252.227-7013

OSI SOFTWARE, INC. 777 Davis Street, Street, Suite 250, 250, San Leandro, CA 94577 Unpublished -- rights reserved under the copyright laws of the United States.

How to Contact Us #09-06 Gateway East Singapore 189721

OSI SOFTWARE, INC. 777 Davis St., Suite 250 San Leandro, CA 94577 USA or PO Box 272 San Leandro, CA 94577 USA (01) 510-297-5800 (main phone) (01) 510-357-8136 (fax) (01) 510-297-5828 (support phone) [email protected]

(65) 391-1811 (65) 295-2488 (fax)

OSIsoft Canada ULC 8150, Metropolitain Blvd. East, Suite 200 Anjou (QC) H1K 1A1 Canada (01) 514-493-0663 (01) 514-493-0980 (fax)

New Zealand Joint Venture OSI SOFTWARE, LTD Level 5 / 393 Khyber Pass Rd. Newmarket Auckland 1003 New Zealand or PO Box 8256 Symonds Street Auckland 1035 New Zealand (64) 9-522-5900 (64) 9-522-5901 (fax)

European Joint Venture OSI SOFTWARE GmbH Hauptstrasse 30 D 63674 Altenstadt, Germany (49) 6047-2770 (49) 6047-6687 (fax)

OSI SOFTWARE ASIA, Pte Ltd. 152 Beach Road

For additional information, please see our Web site: http://www.osisoft.com

Table of Contents CHAPTER 1 INTRODUCTION TO ODBC.................................. ODBC .................................. 1 About This Manual ......................... ....................................... ........................... .......................... ........................... ........................... ................ ... 1 History History of ODBC................................. ODBC.............................................. .......................... .......................... .......................... ...........................2 ..............2 Structured Query Language .......................... ........................................ ........................... ........................... ....................... .........2 2 Portable Data Access Tools ......................... ...................................... .......................... .......................... .........................2 ............2 Callable Interfaces ......................... ....................................... ........................... .......................... .......................... .........................3 ............3 Standard Call Level Interfaces............ Interfaces ......................... .......................... .......................... ......................... ..................... .........3 3 Open Database Connectivity Concept ......................... ...................................... ........................... ...................... ........4 4 ODBC Architectur Architecturee ......................... ...................................... .......................... .......................... ......................... ......................... .................. ..... 5 Driver Manager........................ Manager..................................... .......................... .......................... .......................... .......................... ................... ......6 6 Vendor Drivers .......................... ....................................... ........................... ........................... .......................... ........................... ................ .. 7 Levels of ODBC API Compliance.................. Compliance............................... ......................... ......................... .......................7 ..........7 Levels of ODBC SQL Conformance ......................... ...................................... .......................... ........................8 ...........8 ODBC Standard Error Reporting....................... Reporting.................................... .......................... .......................... ................... ......8 8 ODBC Applications.. Applications............... ........................... ........................... .......................... .......................... .......................... .......................... ............... 9 Obligations of ODBC Compliant Applications ........................ ..................................... ......................9 .........9 Response to Errors .......................... ....................................... ........................... ........................... .......................... ........................9 ...........9 Single-Database Orientation of ODBC Connections .......................... ....................................10 ..........10

CHAPTER 2 PI–ODBC DRIVER FOR PI SYSTEM DATA....... DATA ....... 11 PI–ODBC PI–ODBC Architecture Architecture ......................... ...................................... .......................... .......................... .......................... ...................... .........11 11 PI–ODBC PI–ODBC Driver......................... Driver...................................... .......................... ........................... ........................... .......................... ................... ...... 12 PI-SQL .......................... ....................................... ........................... ........................... .......................... ........................... ...........................13 .............13 PI–ODBC DLLs ......................... ...................................... .......................... .......................... .......................... .......................... ............... 13 PI Server ......................... ...................................... .......................... .......................... .......................... .......................... .......................... .................... ....... 13 September 2001

iii

Table of Contents Compliance with Standards ............................................................................... 14 Levels of ODBC API Compliance................................................................14 Levels of ODBC SQL Conformance ............................................................14 Error Reporting ............................................................................................. 14 Features Specific to PI-ODBC ...........................................................................15 SQL_SUCCESS vs. SQL_SUCCESS_WITH_INFO ..................................15 Use of pipc.log .............................................................................................. 15 Time Synchronization...................................................................................16 Synchronization Error Messages...................................................... 16 Subsecond Digits of Timestamps.................................................................. 17

CHAPTER 3 INSTALLING PI–ODBC....................................... 19 Overview .............................................................................................................. 19 PI System Requirements for Using PI-ODBC .............................................. 19 Where Files Will Be Installed.............................................................................19 ODBC Driver Manager.................................................................................20 PILOG32 DLL..............................................................................................20 Microsoft Access Sample File ......................................................................21 Installing the PI-ODBC Driver from CD-ROM...............................................21 Installing the PI-ODBC Driver from a Downloaded Kit.................................22 Summary of Files ................................................................................................ 22

CHAPTER 4 CONFIGURING PI–ODBC DATA SOURCES ..... 25 Configuration Procedure.................................................................................... 25

CHAPTER 5 TROUBLESHOOTING......................................... 33 Validating Connections....................................................................................... 33 Log Files on Your PC ...................................................................................33 Connections to PI for OpenVMS.................................................................. 34 Connections to PI for Windows NT and UNIX ............................................ 35

iv

.

Viewing SQL Statements.................................................................................... 36 PI System for OpenVMS ..............................................................................36 PI System for Windows NT and UNIX ........................................................ 36 Windows NT ....................................................................................36 UNIX................................................................................................ 37 Understanding SQL Logs .............................................................................37 ODBC Troubleshooting Tools............................................................................38 Tracing ODBC Calls.....................................................................................38 Determining ODBC Driver Characteristics .................................................. 40 Using the iPISQL Utility..................................................................................... 40 Submitting Queries .......................................................................................41 ipisql Options ................................................................................................41 Clearing Expensive Query Problems.................................................................43 PI System for OpenVMS ..............................................................................43 Identifying Clients of PI Server Processes ....................................... 44 UCX.............................................................................................................. 45 Process Software Multinet ............................................................................45 Attachmate Pathway .....................................................................................46 Process Software TCPware........................................................................... 46 PI System for Windows NT and UNIX ........................................................ 46

APPENDIX A PI-SQL TABLES AND SYNTAX........................ 49 PI-SQL .................................................................................................................49 PI Tables .............................................................................................................. 50 PIpoint...........................................................................................................51 PIcomp ..........................................................................................................54 PIinterp .........................................................................................................55 Aggregate Tables ..........................................................................................55 PIbatch .......................................................................................................... 58 PIalias ........................................................................................................... 58 PI-SQL Functions ............................................................................................... 59 Scalar Functions............................................................................................59 SQL Standard Numeric Functions.................................................... 60 SQL Standard Time and Date Functions ......................................................60 SQL Standard String Functions .................................................................... 61

PI ODBC Driver User's Guide

v

Table of Contents PI-SQL Functions .........................................................................................61 Aggregate Functions .....................................................................................62 Calling PI-PE Functions from PI-SQL .........................................................63 PI-PE Tag and Timestamp Data Types.........................................................63 PI-PE Character String and Number Data Types..........................................63 Single Archive Value Queries ............................................................................64 Querying PIcomp..........................................................................................64 Retrieving a Current Value...............................................................64 Retrieving Time, Status, Value from PIcomp ..................................64 Querying PIinterp .........................................................................................65 Retrieving Status, Value................................................................... 65 Retrieving Time, Status, Value ........................................................ 65 Retrieving Time, Digital State String ...............................................65 Retrieving Aggregates ..................................................................................66 Retrieving a Single Aggregate .........................................................66 Using TimeStep to Retrieve Several Aggregates ............................. 67 Using the ANSI Standard AVG Function ........................................ 68 Multiple Archive Value Queries ........................................................................69 Querying PIcomp..........................................................................................69 Retrieving Time, Status, Value ........................................................ 69 Querying PIinterp .........................................................................................70 Retrieving Data without Using TimeStep ........................................ 70 Retrieving Data Using TimeStep...................................................... 70 Join Queries ......................................................................................................... 71 Correlation Names ........................................................................................71 Joining PIcomp and PIinterp......................................................................... 72 Joining PIinterp with Itself ...........................................................................74 Joining PIinterp with Aggregate Tables .......................................................74 Joining PIcomp with Aggregate Tables ........................................................ 75 Joining PIpoint with Archive Tables ............................................................77 Querying PI-Batch Information ........................................................................ 78 Querying PIBatch .........................................................................................78 Querying PIalias ........................................................................................... 79 Joining PIbatch and PIalias...........................................................................79 Supplementing Batch Information with Process Data ..................................80

vi

.

Understanding WHERE Clause Execution ...................................................... 81 Identifying Non-Conjunctive Queries...........................................................82 OR Keyword .................................................................................... 82 No "Target" ......................................................................................82 Multiple Inequalities.........................................................................82 Avoiding Data Type Mismatches ................................................................. 83 TAG Column....................................................................................83 TIME Column .................................................................................. 83 TIMESTEP Column .........................................................................84 Inserting Data into PI .........................................................................................84 INSERT with VALUES List ........................................................................84 INSERT with SELECT.................................................................................85 Providing Key Values ...................................................................................85 Default Column Values in INSERT Statements ...........................................86 Integer Values as STATUS ..............................................................86 Integer Values as VALUE................................................................87 Inserting Digital Events ................................................................................ 87 Status is Zero or Positive.................................................................. 87 Status is Negative ............................................................................. 87 Summary of PI-SQL Syntax .............................................................................. 88 Select statement ............................................................................................89 Insert statement ............................................................................................. 89 Column-List ..................................................................................................89 Select-List .....................................................................................................89 Table-Reference-List ....................................................................................89 Value-List .....................................................................................................89 PI-PE-Expression.......................................................................................... 89

APPENDIX B SETTING PI-SQL OPTIONS.............................. 91 PI-SQL Initialization File................................................................................... 91 Hierarchy of PI-SQL Option Settings ...............................................................92 Setting Lengths of String Attributes .................................................................92 Setting Tag Length........................................................................................92 Setting Digital State String Length ...............................................................93 Setting Point Source String Length...............................................................93 Setting Mode for the INSERT Keyword...........................................................93 PI ODBC Driver User's Guide

vii

Table of Contents Boolean Options .................................................................................................. 94 Improving Query Optimization .........................................................................95 NUMTAGS Setting in the [PIPOINT] Section............................................. 95 DAYSONLINE Setting in the [PICOMP] Section .......................................96 ESTIMATE_MARGIN Setting in the [PICOMP] Section...........................96 Timeout and Execution Control ........................................................................96 TIMEOUT Setting in the [EXECUTION] Section .......................................96 SUBSET Setting in the [OPTIONS] Section ................................................97 Interaction between TIMEOUT and SUBSET .............................................98 NUMVALUES Setting in the [EXECUTION] Section ................................99 Trade-Off Between NUMVALUES And TIMEOUT Settings.....................99 Trapping Expensive Queries (The EXECSAFE Option) ................................99 SUBSET Setting in [OPTIONS] Section.................................................... 100 MAXROWS Setting in [EXECUTION] Section........................................ 100 MAXTIMERANGE Setting in [EXECUTION] Section............................ 100 Summary of Options.........................................................................................101

APPENDIX C PI-SQL ERROR CODES.................................. 103 Execution Statistics ........................................................................................... 103 Error Reporting ................................................................................................ 104 ANSI Standard SQL Errors ........................................................................104 PI API Errors ..............................................................................................105 PI-SQL Errors .............................................................................................105

INDEX....................................................................................... 113

viii

.

Chapter 1

Introduction to ODBC Open Database Connectivity (ODBC) is the name given to a set of standards that allow a client application to access data in any relational database without having to write vendor-specific code. Instead, the client application sends queries to a standardized interface called an ODBC Driver, which is provided by the vendor. The ODBC Driver implements a set of subroutine calls which retrieve data from a particular database. The PI–ODBC Driver from OSI Software is used to bring process data into any ODBC-compliant client application. The PI–ODBC Driver is especially useful for applications that combine PI System data with data from other sources, such as cost accounting or laboratory systems.

About This Manual The PI–ODBC Driver User’s Manual describes the features and functions of the PI–ODBC Driver and explains how to install it. Chapter 1 introduces the general concepts necessary to understand ODBC client/server applications. Chapter 2 describes the PI–ODBC Driver. Chapter 3 tells how to install the software and Chapter 4 explains how to configure PI–ODBC data sources. Appendix A includes PI-SQL Tables and Syntax. Appendix B discusses setting the options included in the basic driver. Appendix C lists PI-SQL Error Codes.

September 2001

1

Chapter 1

Introduction to ODBC

History of ODBC Originally, applications and databases were built together. However, in the client/server world, applications to manipulate data are built separately from the databases containing the information. The applications require a standardized method for retrieving data that works without regard to database structure. Before the development of data access standards, access to relational database data was limited to tools offered by specific vendors. Each relational database could be accessed only by certain tools.

Structured Query Language Structured Query Language (SQL) was proposed by IBM as a “non-procedural” language, that is, SQL described what data was required from a data source, not how to go about getting it. Early versions of SQL were weak in a number of important areas, such as security, table and index creation, and time/date support. Many vendors filled the void by creating their own extensions to the language. SQL is used by report writers, fourth-generation languages, and other application development tools to access data from a wide variety of commercially available databases. In the past, most databases which used SQL for access were relational in nature.

Portable Data Access Tools Eventually, attempts were made to build portable data access tools. Standards groups defined access mechanisms, such as the ANSI standards for Embedded SQL and Dynamic SQL. In Embedded SQL, a program that is to interact with a database is written using special directives to indicate the SQL statements. A special program provided by the vendor called a precompiler is used to translate the directives into subroutine calls. A standard language compiler is used to compile the program. Dynamic SQL is similar, except that the SQL statements are not known at the time the program is compiled. Instead, the embedded directives are used to parse an SQL statement from a character string, determine the type of statement, and execute it. 2

.

In theory, you could use this technique to create an application that would work with more than one vendor's database by switching to another vendor's precompiler and rebuilding. However, this approach has significant shortcomings: •

Accesses only one vendor database at a time

•

No means to accommodate ‘non-standard’ vendor extensions to SQL.

Callable Interfaces Some vendors developed what were termed “callable interfaces” (CLIs), that is, application programming interfaces (APIs). These were specified sets of subroutines that developers could use in their applications to pass and execute SQL statements. Sybase Client-Library (formerly called DB-Library) and the Oracle Callable Interface (OCI) are well-known examples. Callable interfaces eliminate the need for precompilers, but they are not standard. Individual vendors publish the description of their own APIs for interacting with their database products. The names and semantics of the subroutines are not common among vendors.

Standard Call Level Interfaces Two groups endeavored to develop standards for call level interfaces, that is, a standard for a set of subroutines that could be used together in an application to interact with a database. The objective was to create an API that would set specific names and semantics for each subroutine. X/Open is an independent organization of information systems suppliers. Its goal is to generate standards in support of a Common Applications environment that covers all aspects of computing. X/Open proposed a standard for call level interfaces, X/Open Document P303. ANSI is the American National Standards Institute. Their proposed standard, which is essentially identical, is ANSI CLI X3H2-93-082, ISO Working Draft - SWL Call Level Interface (CLI) (Addendum 1 to SQL-92).


3

Chapter 1


The proposed standards covered database connection and disconnection, statement parsing and execution, and passing database data to host language variables. The standards included a standard set of error codes and a common representation of both database and host language data types. The CLI standards omitted a number of important topics, however. They did not cover catalog information, which means that there were no subroutines proposed that could generate a list of tables in a database or a list of columns in a given table. The standard committees did not address implementation of the API on specific computing platforms. This means that they did not, for example, even suggest a name for an object code library on UNIX or VMS, or suggest commands required for linking application programs. The proposed CLI standard did not divide the subroutines into groups to facilitate implementation. Application developers and database vendors frequently find it convenient to divide the CLI into essential and optional routines. Also, the CLI standards did not address the SQL language itself, only the means of parsing and executing it. The standards committees have also worked to re-define a “standard” SQL. The most recent publication of an SQL standard is the International Standard for the Database Language SQL, ISO 9075:1992. Vendors are working to implement the latest additions to the language while maintaining their specific extensions to the language.

Open Database Connectivity Concept At this juncture, Microsoft decided to implement the recommendations of ANSI and X/Open, and to expand them so that data access was standardized and query building was simplified. Thus, application development efforts could be focused solely on delivering functionality. The new concept was called Open Database Connectivity, usually abbreviated ODBC. It is based on the proposed Standard Call-Level Interface. Microsoft also outlined a specific implementation for Windows 3.1 and Windows NT, involving the use of a Microsoft “Driver Manager” and a vendor-provided

4

.

“driver.” Both the Driver Manager and the vendor driver are dynamic link libraries (DLLs). Microsoft also addressed the non-standard nature of the many vendor implementations of SQL. They proposed their own standard and suggested that vendors implementing the ODBC API be able to parse Microsoft's recommended SQL.

ODBC Architecture As noted above, ODBC-compliant client applications are developed without reference to specific databases. An ODBCcompliant client application may use data from several databases at once. Between the databases and the applications are at least two additional layers of software, the Driver Manager and the Vendor Driver layer.


5

Chapter 1


This architecture is shown graphically as follows:

Client Application

ODBC API here...

ODBC Driver Manager and here... t c e n n o C L Q S

ODBC Driver Vendor A

Local database Vendor A

t c e n n o C L Q S

ODBC Driver Vendor B

Network

database server

Remote database Vendor B

Driver Manager The Driver Manager is a standard Windows component that must be present in any ODBC installation. Its role is to act as an intermediary between the ODBC client application and a vendorspecific driver. It has the capability to connect to more than one driver at a time. The Driver Manager does some error checking of the data passed to ODBC by a client and will ensure in many cases that the ODBC routines are called in the correct sequence. If errors are detected by the Driver Manager, it formats appropriate error messages and returns them to the caller. See the discussion of ODBC Standard Error Reporting later in this chapter.

6

.

Microsoft allows vendors of ODBC drivers to distribute the core ODBC components royalty-free. These components are added to your computer by installing the provided Microsoft Data Access Components (MDAC) kit. This kit installs the ODBC Driver Manager, ODBC control panel applet and other data access tools. The control panel applet is used to define data sources for use by client applications. The MDAC kit can be found on the PIODBC installation and Data Access Package CDs.

Vendor Drivers Vendor Drivers receive standard calls across the ODBC-API, translate standard SQL calls into SQL calls for the vendor’s specific database, and return data. ODBC makes no distinction between so-called “desktop” databases and remote databases. It does not define the means by which ODBC routines are executed once beyond the Driver Manager. Every vendor is free to choose network communications layers and proprietary programming APIs to perform the requested functions. Vendor drivers are classified as one-tier or two-tier, depending on whether the vendor database is local to the client application or located elsewhere on the network. In a one-tier driver, the database is a file that is resident on either the user’s own PC or on a file server. A two-tier driver implies the existence of a database server process running on a remote computer.

Levels of ODBC API Compliance For vendor drivers, Microsoft has defined three levels of compliance to the API: •

Core, the basic level, a minimum group of calls necessary to connect, parse SQL statements, get results and disconnect.

•

Level 1, which contains the Core routines, database catalog routines and functions for obtaining information about the database's capabilities.

•

Level 2, which contains the Core and Level 1 routines and support for advanced methods of returning results from the database to a program.


7

Chapter 1


Most commercial ODBC client applications require Level 1 compliance to the ODBC API.

Levels of ODBC SQL Conformance Microsoft defines three levels of conformance with its SQL standard: • • •

Minimum Core Extended

An ODBC client application can determine the SQL conformance level of a database by using one of the ODBC information subroutines.

ODBC Standard Error Reporting The ODBC API Standard permits three elements to be returned for each error, an SQL State Code, an error message, and a vendor code. The SQL State Code is a 5-character string that shows the nature of the error. For example, “08S01” indicates that the application’s connection to the database has failed. The error message includes a prefix and a message text. The prefix for a one-tier driver includes vendor identifier and ODBC component (usually driver). In this example, the vendor identifier is Microsoft and the driver is ODBC dBase Driver: [Microsoft][ODBC dBase Driver]Unable to allocate sufficient memory.

The prefix for a two-tier driver includes vendor identifier, ODBC component (usually driver), and data source name. In this example, OSI is the vendor identifier, PI–ODBC is the ODBC driver, and PI is the data source name. [OSI][PI–ODBC][PI]message

The ODBC standard permits the vendor to return an integer error code specific to the vendor’s API, termed a “native error.” The client application may or may not display this code to the user.

8

.

Error messages that are returned by the Driver Manager have a vendor prefix and ODBC component only. The ODBC component is the Driver Manager itself. For example: [Microsoft][ODBC DLL]Invalid argument value: SQLDataSources.

ODBC Applications Obviously, the appeal of ODBC capability is that within a client application, a vendor need implement only one programmatic interface to access a great variety of external databases. Microsoft, being the main proponent of ODBC, has added ODBC capability to many of its products, including Visual Basic, Access, and Excel. In addition, other vendors, such as Crystal Decisions, have developed ODBC capability for their applications.

Obligations of ODBC Compliant Applications The requirements on programmers of ODBC applications are not extensive. Generally, the ODBC API routines must be called in the correct order, and disconnection and freeing of resources must take place when an application exits. A programmer should avoid calling ODBC routines that are not supported by a particular driver. Informational ODBC routines are available to discover the exact capabilities of drivers. For example, an application should be aware that some drivers close database cursors when a transaction is committed while others do not. The ODBC Programmers Reference and SDK Guide, available from Microsoft, gives complete details on the definition and usage of the ODBC API.

Response to Errors Every ODBC driver is obligated to indicate when an ODBC API routine has failed and to format an error message if requested by a client application. A client application is not required to obtain the error message and display it to the user. It is, however, good programming


9

Chapter 1


practice to do so. Many client applications do not report informational messages to the user when an ODBC API routine indicates that it has completed successfully, but has supplementary information.

Single-Database Orientation of ODBC Connections The ODBC Driver Manager is capable of connecting to multiple databases at one time. The ODBC API itself, however, is designed around a “connection handle”, which may be connected to only one database at a time. This means that an ODBC driver does not have any knowledge of other ODBC drivers. Joins across vendor boundaries are possible, but the details of the interaction must be handled by the application. Each database driver may see queries directed at its database only. Applications such as Microsoft Access support joins across vendor boundaries. For this feature to function, you must have ODBC drivers and any required supporting software for all databases with which you wish to connect.

10

.

Chapter 2

PI–ODBC Driver for PI System Data The PI–ODBC Driver meets Microsoft ODBC standards and allows a client application to extract process data from the PI System. You will need one copy of the driver for each PC that will be using an ODBC-client application that requires PI System data. The actual processing of SQL statements takes place on the PI Server. This product is not related to the features of PI-ProcessBook, which uses other ODBC drivers to access and display data from foreign databases.

PI–ODBC Architecture Requests for PI data from client applications will be routed by the Driver Manager to the PI–ODBC Driver. The driver is a twotier driver, meaning that some of its functions occur on the PC and others on the PI Server.

September 2001

11

Chapter 2

PI-ODBC Driver for PI System Data

This architecture is shown graphically as follows:

Client Application

ODBC API here...

ODBC Driver Manager and here... t c e n n o C L Q S

PI-ODBC Driver in PC

Network

PI-API here...

PI server

PI data

PI–ODBC Driver The PI–ODBC Driver has been written by OSI Software to facilitate accessing and retrieving data in the PI System. This driver will implement the calls passed from the client application by the Driver Manager. In other words, when its routines are called by the ODBC Driver Manager upon request by a client application, the PI–ODBC Driver executes the appropriate functions to obtain data from the PI System. The PI–ODBC Driver is a two-tier driver because it receives requests through the Driver Manager and sends them across the 12

.

network to a PI System Home or PINet node for processing. Results are sent back to the PC so that the PI–ODBC Driver can return them to the application.

PI-SQL PI-ODBC driver sends SQL statements directly to the PI Server for processing. The PI Server returns results of the completed query to the PI-ODBC driver. Use of SQL requires that PI System data be accessible as if that data resided in tables. Processing of SQL statements is done using PI-SQL, OSI Software's implementation of SQL, on the PI Server. PI-SQL provides a relational database view of the PI System. Consult Appendix A for an outline of the tables in the PI System that can be accessed using SQL statements.

PI–ODBC DLLs The PI–ODBC Driver is a dynamic link library (.DLL) that implements all subroutines in the ODBC API standard. After running PI-ODBC Setup, you will see two new files in the PIPC\ODBC directory: piodbc32.dll and pioset32.dll. The Setup program also adds three DLLs to the WINNT\SYSTEM32 directory: piapi32.dll, pilog32.dll and sdkreg.dll. Under Windows 95 and 98, the system directory would be WINDOWS\SYSTEM , instead of WINNT/SYSTEM32. The ODBC Driver Manager ( odbc32.dll) and ODBC Installer (odbccp32.dll) must also be present. If they are not, you must first install the Microsoft Data Access Components (MDAC) kit, which can be found in the piodbc\mdac directory on the PIODBC or Data Access Package CDs. The PI-ODBC Setup program will tell you if you need to install MDAC.

PI Server “PI Server” is a generic term that refers all OSI Software data servers. The server can be either PI for OpenVMS or PI Universal Data Server for Windows NT and UNIX. All OSIsoft client applications can connect to either server type. The PI


13

Chapter 2


Server processes SQL queries and returns results upon request by any ODBC client application. The minimum version of PI for OpenVMS required to support SQL processing is 2.1.1. Version 2.1.2 is required for support of SQL aggregate functions and access to PI Batch data using SQL. The minimum version of PI Universal Data Server for Windows NT and UNIX required to support SQL processing is PI 3.2 Build 332.

Compliance with Standards As noted in Chapter 1, the ODBC standard defines levels of compliance for the ODBC API itself, for SQL language support and for error reporting.

Levels of ODBC API Compliance The PI–ODBC Driver meets the Level 1 criteria for compliance with the Microsoft standard ODBC API.

Levels of ODBC SQL Conformance The PI–ODBC Driver adheres to the Core ODBC SQL conformance level for the SQL language for data manipulation. Data definition language statements ( i.e., CREATE TABLE or CREATE INDEX) are omitted because it is not possible to create user-defined tables with the PI System.

Error Reporting Since the PI–ODBC Driver is two-tier, its error messages conform to the ODBC two-tier standard. The prefix format includes vendor, driver, and server name. [OSI][PI–ODBC][PI]message

Most, but not all, client applications will return the native error code to the user. For PI-ODBC, the native error code will contain either the PI-SQL or the PI-API error. See Appendix C for details on error messages and codes.

14

.

Features Specific to PI-ODBC This section details some behaviors that are specific to the PI-ODBC. None of these features compromise adherence with ODBC standards.

SQL_SUCCESS vs. SQL_SUCCESS_WITH_INFO PI-ODBC adheres to the ODBC standard of returning SQL_SUCCESS_WITH_INFO if any ODBC API routine has succeeded, but additional information is available. The information is in the form of an ODBC-standard message that can be obtained with the ODBC API routine SQLError. Most ODBC client applications are aware of the difference between the above two function return codes and will obtain the additional information if available. The message may be displayed or logged depending on the design of the client application. Most ODBC client applications use the ODBC API routine SQLDriverConnect to connect to the database server. The PIODBC Driver implementation of SQLDriverConnect usually returns an additional message and will return SQL_SUCCESS_WITH_INFO whenever this is true.

Use of pipc.log There is no ODBC standard for the use of a log file in the implementation of a driver. The PI-ODBC Driver uses the PI-API, which includes support for the error message logging in the pipc.log file in your pipc\dat directory. If PI-ODBC writes a message to this file, it will be prefixed with: PIODBC32.DLL(DSN )>

Where DSN is the ODBC Data Source Name in use. Errors in connection to the PI Server are most likely to be accompanied by additional messages in pipc.log. An ODBC connection request results in several PI-API calls; PI-ODBC will log failure in any of them.


15

Chapter 2


Time Synchronization Some ODBC client applications do not allow the use of PIspecific functions in SQL query text. One of the problems this can create is in the specification of current time in a query. For example, PI-SQL supports the use of the function date(‘*’) in an SQL query to refer to current time. This function will be evaluated as current time on the PI Server. By contrast, Microsoft Access does not allow the use of vendorspecific functions, unless pass-through queries are used. You must use the Now() function instead. This function is evaluated on your workstation. In this case, the PI Server receives an absolute time in ODBC-standard timestamp format and must determine whether the timestamp is intended to represent current time. If the system time on your workstation and PI Server are close, this determination is usually accurate. If there is a significant time difference between your workstation and the PI Server, errors in retrieving snapshot data may result: •

If the workstation time is ahead of the server time, snapshot data may not be returned since the server will receive a request for data in the future.

•

If the workstation time is behind the server time, you may retrieve an archive data value instead of the snapshot value.

PI-ODBC can help you avoid this problem by synchronizing the time in your workstation to the time on the PI Server. If this option is selected, times will be synchronized whenever a new connection is made. Time synchronization will not occur if the workstation and server times differ by more than 10 minutes.

Synchronization Error Messages If there is any time difference at all between your workstation and the PI Server, PI-ODBC will record it in your pipc.log file when the connection is first completed. This occurs whether or not time synchronization is selected: 03-Apr-01 07:36:27 PIODBC32.DLL(PI)> Time on this workstation is 57 seconds behind the PI System

16

.

If the time synchronization option is selected, a message will be generated if the synchronization succeeds or fails. A message is written to your pipc.log and is returned as the informational message when SQLDriverConnect returns SQL_SUCCESS_WITH_INFO. If the time difference between your workstation and the PI Server is more than 10 minutes, synchronization will not occur. A sample message is: Time not synchronized with PI Server “liana"; maximum time difference of 600 seconds exceeded

If synchronization succeeds, the message is: Workstation time synchronized with PI Server “liana"

If synchronization fails, it usually indicates insufficient privileges for your workstation account. The most common message is: Error synchronizing workstation time to PI Server "liana": [1314] A required privilege is not held by the client.

See Chapter 4, “Configuring PI-ODBC Data Sources” for details on configuring ODBC Data Sources that include time synchronization option.

Subsecond Digits of Timestamps The ODBC standard allows applications to determine the level of subsecond timestamp support in ODBC drivers. Drivers may support any number of subsecond digits from 0 through 9. This value determines the number of digits to the right of the decimal point in time strings returned an ODBC driver. ODBC standard time strings are of the format: yyyy-mm-dd hh:mm:ss.ffffff

Each letter ‘f’ represents a subsecond digit; there may be 0 through 9 of them. If there are no subsecond digits specified, the trailing decimal point does not appear.


17

Chapter 2


The PI-ODBC Setup dialog box has a field that allows you to specify the number of subsecond digits. The field is initially blank, which means that a default value of 6 will used. A subsecond digit value of 6 means a timestamp precision of one microsecond. The maximum subsecond time precision for PI Universal Data Server for Windows NT and UNIX is 1/65535, or about 15 microseconds. Values in this field larger than 6 will not increase the precision of returned timestamps. PI for OpenVMS does not support subsecond times. Subsecond portions of timestamps from PI for OpenVMS will always be zero.

18

.

Chapter 3

Installing PI–ODBC Overview PI-ODBC is installed by running a setup program on the PIODBC CD-ROM or from a self-extracting executable downloaded from OSI Software. Some modification of the Windows Registry also occurs. This modification is needed to define PI-ODBC as an available ODBC driver. PI-ODBC is also distributed as part of the OSIsoft Data Access Package. All directory paths given in this chapter are correct for both the PI-ODBC and Data Access Package CD-ROMs.

PI System Requirements for Using PI-ODBC The minimum version of PI for OpenVMS required to support SQL processing is 2.1.1. Version 2.1.2 or later is required for support of SQL aggregate functions and access to PI Batch data using SQL. The minimum version of PI Universal Data Server for Windows NT and UNIX required to support SQL processing is PI 3.2 Build 332.

Where Files Will Be Installed To keep the installation instructions simple, all examples in this section assume that the CD-ROM drive is disk E:, that the

September 2001

19

Chapter 3

Installing PI-ODBC

workstation Windows directory is C:\WINDOWS and that the directory for OSI Software products is C:\PIPC . It is possible to override the default install directory location, C:\PIPC , when running the setup program. If the install directory does not yet exist, it will be created during the setup process.

ODBC Driver Manager If you are running Windows NT or Windows 2000, or if another ODBC Driver has already been installed, the ODBC Driver Manager will already be in place. If it is not present on your Windows system, you must install the Microsoft Data Access Components (MDAC) kit, which can be found in the piodbc\mdac directory on the PI-ODBC CD. The PI-ODBC Setup program will tell you if you need to install the Microsoft Data Access Components kit. Start the installation by running mdac_typ.exe.

PILOG32 DLL If you already have other OSI Software products on your PC, a PILOG32 DLL will be present on your system: •

On Windows NT, C:\WINNT\SYSTEM32\PILOG32.DLL

•

On Windows 95 and 98, C:\WINDOWS\SYSTEM\PILOG32.DLL If pilog32.dll is not already present, it will be loaded automatically during the installation. This file is used to record names and network port numbers of all PI Servers accessible from your PC. The PILOG32 DLL records information about available PI Systems in a file called C:\PIPC\DAT\PILOGIN.INI . If this file is not found when PI-ODBC is installed, Setup will prompt you for the name of a PI Server to use as the default. You do not need to define a default PI System when running Setup. You can do this when defining an ODBC Data Source using the PI-ODBC driver (see Chapter 4), or by using another OSI Software product.

20

.

Microsoft Access Sample File The PI-ODBC CD includes a Microsoft Access example database called pi.mdb. There are 2 different versions provided: the directory piodbc\samples\access\97 contains pi.mdb for Microsoft Access 97, and the directory piodbc\samples\access\2000 contains pi.mdb for Microsoft Access 2000. The sample Microsoft Access databases may be copied from the PI-ODBC CD to your workstation at any time. PI-ODBC Setup does not copy the database files for you. The sample file requires an ODBC Data Source called “PI”. Setup will check to see if this Data Source name exists. If not, you will be asked if you want Setup to create the “PI” Data Source. You do not need to create this Data Source while running Setup. You can do this when defining an ODBC Data Source using the PI-ODBC driver. See Chapter 4 for details.

Installing the PI-ODBC Driver from CD-ROM 1. Insert either the PI-ODBC of Data Access Package CD into your CD-ROM drive. An HTML page should be displayed by your Internet browser automatically. Locate the Install or Upgrade PI-ODBC link and click it. If the HTML page does not open, start PI-ODBC Setup using either method below: •

From the Start Run menu, start the program E:\piodbc\setup.exe, or

•

In Windows Explorer, doubleclick on E:\piodbc\setup.exe.

2. You will be prompted for the location of your PIPC directory tree. If you already have one or more OSIsoft products installed, the directory path displayed will be your current PIPC location. If you do not, the proposed PIPC path will be C:\Program Files\PIPC . The DAT directory under


21

Chapter 3

Installing PI-ODBC

the PIPC root will contain the odbcinst.log file, which records the installation steps performed by PI-ODBC Setup.

Installing the PI-ODBC Driver from a Downloaded Kit 1. Your product download must be authorized by OSIsoft Technical Support. You will receive a link to the download location by E-mail. 2. Open the E-mail and locate the product URL and click it. You will be taken to the PI-ODBC product page. Click Download. You will be asked to fill out a form with your name, company name, site name and E-mail address. When you submit this information, you will be asked whether you wish to download the product or run it from its current location. You may do either, but it is recommended that you download the self-extracting executable in case you need to re-run PI-ODBC Setup. 3. When you run the kit, you will be prompted for a file directory to hold the extracted files. The PI-ODBC Setup and supporting files will be extracted to your location and Setup will start. Proceed as in the previous subsection. 4. When Setup completes, the extracted files will not be deleted. You may need them in order to work with the provided Microsoft Access sample databases.

Summary of Files When installation is complete, the following files will appear in your hard drive, typically in the locations shown. The files indicated by rel*.txt are release note files. They are numbered with PI-ODBC build numbers, such as rel117.txt . PI-ODBC Driver C:\PIPC\ODBC\PIOSET32.DLL C:\PIPC\ODBC\PIODBC32.DLL

22

.

C:\PIPC\ODBC\PIODBC.INI C:\PIPC\ODBC\INFO32.EXE C:\PIPC\ODBC\IPISQL.EXE C:\PIPC\ODBC\REL*.TXT C:\PIPC\LIBRARY\PILOGIN.HLP C:\WINNT\SYSTEM32\PILOG32.DLL C:\WINNT\SYSTEM32\PIAPI32.DLL C:\WINNT\SYSTEM32\SDKREG.DLL

NOTE: These system directory names come from Windows NT and 2000. The Windows 95 and 98 directory structure is similar to that of Windows NT, except that the ‘WINDOWS’ typically replaces ‘WINNT.’


23

Chapter 4

Configuring PI–ODBC Data Sources An ODBC Data Source is the name used to associate a vendor’s ODBC Driver with a particular database. All ODBC applications use the Data Source name when connecting to the database. In configuring a PI-ODBC Data Source, a number of options are specified through your workstation. Other options require editing of the PISQL.INI file on the PI System. If there is a difference in settings between a PI–ODBC Data Source configuration and the PISQL.INI file, the Data Source settings will be used. For additional descriptions of PI-SQL options, see Appendix B. Each Data Source Name (DSN) defines a connection to a single PI Server. If your client application is accessing more than one PI system, you will need to go through the configuration process once for each Data Source. ODBC Data Sources are in two categories: User DSN and System DSN. User Data Sources are available only to the user who created them. System Data Sources are available to anyone using your workstation.

Configuration Procedure Creation and editing of ODBC Data Sources is done using the ODBC Data Sources applet in your Control Panel.

September 2001

25

Chapter 4

Configuring PI–ODBC Data Sources

After starting the ODBC control panel applet, a window showing currently defined ODBC Data Sources will be displayed. The driver identifier for each Data Source is shown next to the name.

When the applet is started, the User DSN tab is displayed. To create a Data Source accessible only to the currently logged in user, click the Add button. To create a system DSN, click the System DSN tab first, then the Add button. In either case, a list of available ODBC drivers is displayed:

26

.

Select PI–ODBC from the list and click Finish. The PI ODBC Data Source Setup dialog box will be displayed. This dialog box, shown below, allows you to create a new ODBC Data Source. The configuration will be saved when you click the OK button. You can edit your Data Source configuration at any time by choosing your Data Source name from the ODBC applet’s main window (in either the User DSN or System DSN tabs), and clicking Configure.


27

Chapter 4


Data Source Name: Fill in a recognizable name for your data source, such as “PI.” This is a required field. Description: Add a description of your data source. This is an optional free-form text field. PI Server Name: The drop-down box will reveal a list of PI Servers configured on your workstation. If the PI Server you would like to access is not listed, you can click the Add button to bring up the Define Connection dialog box. Enter the name of the new PI Server, the default username and the TCP/IP port number (5450 for PI Universal Data Server for Windows NT and UNIX, 545 for PI for OpenVMS).

You must select a PI Server name in order to be able to save your Data Source definition. Integer Values as: If STATUS is selected, integer values will be returned in the Status column of PI-SQL data tables such as picomp and piinterp. This resembles the behavior of the PI-API and is the default. If you choose VALUE, integer values will be

28

.

coerced to floating-point values and will be returned in the Value column. Digital values from both numeric and digital points are returned in the Status column in all cases. Sync Time with PI Server If this option is selected, time on your workstation will be synchronized to the time on your PI Server whenever a new connection is made. Time synchronization will not occur if the workstation and server times differ by more than 10 minutes.

If you do not wish to have the clock on your workstation synchronized to the time on the PI Server, uncheck this box. NOTE:PI-ODBC records the time difference in \pipc\dat\pipc.log , whether or not time synchronization is requested. You can check messages in this log file at any time. On Windows NT/2000, your user account must have sufficient privileges to change the system time. If it does not, an informational message will be returned to your ODBC client application.

Default Timestep: The relative time string in the Timestep field defines the default time interval between data rows retrieved from the piinterp table. Initially the field will be blank. You can type in a relative time string or select one from the drop-down list. If you leave it blank, Timestep will default to the value defined on the PI Server. Subsecond Digits: The value in this field determines the number of digits to the right of the decimal point in time strings returned by PI-ODBC. If the field is left blank, a default value of 6 is used. Values allowed for this field are between 0 and 9. NOTE: A subsecond digit value of 6 means a precision of one microsecond. The maximum subsecond time precision for PI Universal Data Server for Windows NT and UNIX is 1/65535, or about 15 microseconds. Values in this field larger than 6 will not increase the precision of returned timestamps. PI for OpenVMS does not support subsecond times. Subsecond portions of timestamps from PI for OpenVMS will always be zero.

Aggregate Timestamp: Select one radio button to indicate the timestamp used by PI, either Start of Interval or End of Interval. The default is End of Interval. For example, if you


29

Chapter 4


select an average from the PIavg table between 11:00 and 12:00, the timestamp of the result will be 12:00 by default. Selecting the Start of Interval radio button will make the timestamp 11:00. For more information, see Appendix B. Timeout: This is an elapsed number of seconds after which the PI Server will stop processing a query. Initially the Timeout box will be blank and will default to a value defined on the PI Server. You may wish to use the Timeout box to set a maximum processing time for queries. Setting this time will adjust the PIAPI timeout value as needed so that the PI-API does not time out your request before the PI Server does. Trap Expensive Queries: Initially the Trap Expensive Query box will be blank and will default to the value on the PI Server. If this box is checked, client queries will be assessed for excessive use of resources before execution. If the query is too expensive, it will not be executed. NOTE: Use of this feature is highly recommended during query development. If you do not, it is possible that your ODBC client application may send an incomplete query, or a query that returns too many results, to PI. Since PI-ODBC gives you access to a data archive that holds a huge number of events, incomplete queries may cause the PI System to consume large amounts of CPU and memory, and will cause the client application to time out.

If you generate such a “runaway” query, refer to Chapter 5, “Troubleshooting,” for techniques to use to abort query processing on the PI System. Determination of whether or not a query is considered expensive is done by comparing the query’s estimated number of returned rows against some limits. These limits can be adjusted. See Appendix B for details. If Timeout or Expensive: This box will be grayed out unless you add a value in the Query Control box. Click a radio button to indicate how you would like PI–ODBC to respond to timeouts, either to Abort the query altogether or to Return a Subset of the answer.

See Appendix B for more discussion on setting these options.

30

.

When the Setup dialog is complete, click OK. Your new Data Source will appear in the list of defined ODBC Data Sources.


31

Chapter 5

Troubleshooting This chapter covers tools and techniques that are available in the event that problems occur when using the PI-ODBC Driver with ODBC client applications. Errors may occur during connection to the PI Server, or when processing SQL statements.

Validating Connections This section outlines the information available for troubleshooting if the PI-ODBC driver fails to connect to the PI System.

Log Files on Your PC Problems associated with connecting to the PI System are always logged in the pipc.log file, which is normally found in the directory c:\pipc\dat . Messages generated by the PI-ODBC driver are always labelled with the PI-ODBC driver DLL name ( piodbc32.dll) and the name of the ODBC Data Source in use. If the connection to PI is made normally, a message like the following will appear in pipc.log: 28-Jul-00 10:59:41 PIODBC32.DLL(datasourcename)> Time on this workstation is 4 minutes 39 seconds behind the PI System “PIServerName”

September 2001

33

Chapter 5

Troubleshooting

If the PI-ODBC data source is configured to synchronize time on your workstation with the time on the PI System, one additional message will appear. If synchronization has succeeded, the message is: 28-Jul-00 11:04:20 Workstation time synchronized with PI Server “PIServerName”

The timestamp of the message reflects the updated workstation time. If synchronization has failed, the message is: 28-Jul-00 11:04:20 Error synchronizing workstation time to PI Server “PIServerName”: [1314] A required privilege is not held by the client.

The last portion of the message reflects the Windows NT/2000 error code and corresponding message. This example shows the most common reason for time synchronization failure: insufficient privileges to change your workstation’s system time.

Connections to PI for OpenVMS A successful connection by the PI-ODBC driver to the PI System will cause a PI Server process to start on the VAX or Alpha. The PI Server creates a log file whose name depends on the supplier of the network software on your server: For DECnet, Pathworks: PISysExe:netserver.log For DEC TCP/IP Services for OpenVMS: PISysExe:ucxpiserver.log For Process Software Multinet: PISysExe:mtnpiserver.log For Attachmate Pathway : PISysExe:ptwpiserver.log For Process Software TCPware: PISysExe:tcppiserver.log If the connection is successful, the log file will contain the following information:

34

.

UCXPISERVER> Version 2.1.2 (07 May 1997) Server Protocol Version: 00010007 UCXPISERVER> Security: UserDB [PI] Default [RW] "seabass" Session [RW] UCXPISERVER> Process Name [SEABAS:piad09D0] Device Name [_BG4612:]

The first line notes the PI Server version, revision date and the PI network protocol version. The second line logs the user database in use, the default access privilege code and the privilege in use for the current connection from your workstation. The third line logs the PI Server process name and the name of the network device allocated by the system. NOTE: For systems using Process Software Multinet and Attachmate Pathway, the PI Server log file will not be readable until the client session disconnects from the PI System.

If the PI Server log file is absent, there may be some information in the PI System message log file PISysDat:PIMessLog.Txt . If the message log contains no information, make sure that the PI Server is correctly configured; see the file PIBuild:PIServer.Txt for information on configuring PI Servers for your vendor’s network software.

Connections to PI for Windows NT and UNIX The PI System for Windows NT and UNIX logs all incoming client connections to the system message log. Use the utility pigetmsg in the \pi\adm directory to check for connections. Typical output from pigetmsg might read: 0 pinetmgr 28-Sep-98 23:17:56 >> PInet accepted TCP/IP connection, cnxn ID 13 Hostname: localhost, 127.0.0.1 0 ISQLE 28-Sep-98 23:17:56 >> New Pinet 1 connection: ISQLE Protocol: 00010008


35

Chapter 5

Troubleshooting

Viewing SQL Statements For troubleshooting purposes, it is possible to direct the PI Server to write every SQL query received from a client to a log file. The technique for doing this depends on the PI System type.

PI System for OpenVMS To enable logging of incoming SQL statements, edit the file PISysDat:pisql.ini on the PI System. Create the following section and entry: [PISERVER] trace=1

All client sessions subsequently connecting to PI will have logging enabled. Output will be logged to the PI Server log file on your server. See the previous section for the file names.

PI System for Windows NT and UNIX To enable logging of incoming SQL statements, shut down the PI-SQL Subsystem. When restarting it, provide the “trace” command line option. The technique for doing this varies with operating system type:

Windows NT If you are running the PI-SQL Subsystem in a command window, starting the subsystem with the trace option enable is done from the \pi\bin directory as follows: start pisqlss –o trace

The only way to pass command line arguments to pisqlss running as an NT service is to open the Services applet in the Control Panel and type them into the Startup Parameters window at the bottom of the dialog box. To cause them to be read by the NT Service Manager, you need to stop the service, make sure the arguments are in place while you select the service name from the list, and then hit Start again.

36

.

This example shows a system manager about to restart the PI-SQL Subsystem while setting the default timestep to 7200 seconds and turning on “TRACE” mode. NOTE: This works one time only. If you close the Services applet, then reopen and reselect your service, you will not see your command line arguments from the last run. This method cannot be used to provide command line parameters to services started automatically when your Windows NT system boots.

Refer to the chapter on the “PI-SQL Subsystem” in the PI Data Archive for NT and UNIX Manual.

UNIX Set your working directory to the /pi/bin directory. Issue the command: nohup pisqlss –o trace 1> /pi/log/pisqlss.log 2>&1 &

Understanding SQL Logs When an SQL statement is parsed, the log file will contain a PISQL “handle” number to uniquely identify the SQL statement, the error code and finally, the SQL query itself. For example: Prepare[006560E0]>[0][0.01500s] select * from picomp where tag = ? and time > DATE("today")


37

Chapter 5

Troubleshooting

Note that this example shows a query that includes a run-time parameter, designated by the question mark (“?”). The value to substitute for this parameter is provided by the ODBC application when the statement is executed. When the statement is executed, the log file will show the PISQL handle, the error code, the elapsed time in seconds, and the numbers of run-time parameters, columns and rows returned by the query: Execute[006560E0]>[0] [0.01500s] Parameters: 1 Columns: 4 Rows: 5 P00 [char 009] SINUSOID

On PI for OpenVMS, you may disable trace mode by setting the value of the [PISERVER] entry in PISysDat:pisql.ini to “0”, or by deleting the section and entry completely. On PI for Windows NT and UNIX, you must shut down the PI-SQL Subsystem and then restart it without command line options to turn off SQL tracing.

ODBC Troubleshooting Tools This subsection outlines tools that are available to monitor ODBC calls made by applications.

Tracing ODBC Calls The ODBC Administrator is capable of generating a log file containing all of the ODBC API calls made by any application. This file is frequently helpful when debugging SQL queries issued by ODBC client applications. To enable this feature, start the ODBC control panel applet. The following dialog box is displayed:

38

.

Click the Tracing tab. The dialog box changes to:

Click the Start Tracing Now button. By default, the log file will be called SQL.LOG in the root directory of your system disk. To


39

Chapter 5

Troubleshooting

change the location of the file, edit the filename in the Log file Path window before starting the trace.

Determining ODBC Driver Characteristics Users may find that a given ODBC application works correctly with another vendor’s database and ODBC driver, but does not work with PI-ODBC. In order to determine the specific ODBC driver characteristics that account for this, the INF032 utility is provided in the C:\PIPC\ODBC directory. To use INFO32: •

Start the application by locating info.exe using Windows Explorer and double-clicking.

•

You are presented with a File Open box. Choose a file name and location and choose OK. If you choose Cancel, results will be printed to the displayed window, but will not be recorded in a file.

•

You are presented with a list of available ODBC Data Sources. Choose the Data Source you wish to investigate.

The application will connect to the selected Data Source, prompting you for username and password if necessary. It will proceed to obtain information about the driver and write it to the log file if a file was defined. When the application completes, it will notify you with a message box. Choose File Exit from the menu to terminate the application.

Using the iPISQL Utility This utility is an interactive program that executes SQL statements directed at the PI System. It uses the PI-API to connect to the PI System. It does not use the PI-ODBC driver, or the Microsoft ODBC Driver Manager. It is useful for troubleshooting SQL queries without using an ODBC client application. The ipisql utility can be found in the \pipc\odbc\ directory. Start the utility by typing its name at a command prompt. The utility

40

.

will connect to the default PI System, write version information to the screen, and then prompt for input: D:\pipc\odbc\ipisql Connecting to default PI System...Done iPISQL Copyright (c) 1993-2000, OSI Software, Inc. All rights reserved. PI-API Version 1.3.4 PINet Protocol Version 00011101 Connected to PI 3.3 Build 361.43 on node "ray" PISQL>

Submitting Queries SQL statements can be typed at the prompt and terminated with a semi-colon (“;”). This causes the query to be sent to PI. The query can span multiple lines. The prompt for subsequent lines looks like: _PISQL>

You can use as many lines as you like. You can also process queries stored in a text file using the FILE command: PISQL>file myquery.txt;

A query in a file need not be terminated with a semi-colon; the query will be processed when the end of the file is reached. A query file may contain more than one query. If this is the case, a semi-colon must be used to separate the queries. Query files may contain the FILE command.

ipisql Options The ipisql utility supports several options that can be specified on the command line. The full list of command line arguments are supported by ipisql is as follows:


41

Chapter 5

42

Troubleshooting Command Line Argument

Description

-csv

Write results to standard output in comma-separated variable format suitable for importing into a spreadsheet. The query text, column headings, row count and error information are written to standard error, also in comma-separated variable format. No command prompts are displayed.

-f

Identifies a query file. If this argument is used, ipisql executes the query in the specified file and exits. A command prompt will not be displayed.

-node

Identifies the PI System node to which to connect. The name to use is the PI System computer’s network name. When connecting to a PI System for Windows NT and UNIX, you must either suffix “:5450” to this name, or specify “-port 5450”.

-o

Options. The options are specified in a comma-separated list of tokens. The interpretation of the tokens is not case-sensitive. See the table below for a list of supported options.

-p0...-pn

SQL parameter values. The first parameter is –p0 ( i.e., zero), the second is –p1, and so on. You may specify as many SQL parameters as you like, and need not specify all of them; ipisql will prompt for any additional parameter values needed. The SQL parameter values are in effect throughout the entire ipisql session.

-password

Password. This argument is interpreted only if the “-username” argument is supplied. If “-username” is provided, but “-password” is not, ipisql will prompt you for a password.

-port

TCP\IP port number. Default value is 545. You may choose to add “:portnum” to the end of the node name when providing the “node” argument.

-t

SQL query timeout, in seconds. If this time expires, the PI System will cause the query to return a timeout error.

-ts

Default value of the timestep column in the PIINTERP table. This value can be overridden for any query by specifying a timestep constraint in your SELECT statement.

-username

Username. If this argument is not present, ipisql will not attempt to validate your identity; default access rights will apply.

.

For example, to execute query in the file myquery.txt against the PI 3.2 System “larry” using a default timestep of 2 minutes, issue the command: ipisql –ts 120 –f myquery.txt –node larry:5450

The following table contains a list of tokens that can be specified after the ipisql “-o” argument: Option Token

Description

AGGRTIMESTART

Causes all queries of the aggregate tables to use the time at which the interval starts to identify the aggregate; the default is to use the time at which the aggregate period ends.

EXECSAFE

If set, the query will not execute if the PI-SQL determines that a query is too unspecific and would take too long to execute.

SUBSET

If a query times out while this option is in effect, pisqlss will return a subset of the actual results with no error.

TRACE

Logs a summary of every Prepare (that is, query parsing) and Execute operation by PI.

Clearing Expensive Query Problems As noted in Chapter 4, it is possible that your ODBC client application may send an incomplete query, or a query that returns too many results, to PI. If this occurs, your client application’s connection to PI will probably time out. You are then free to reconnect to PI to continue your query development. In some cases, however, execution of the “runaway” query will continue on the PI System. This section outlines techniques for identifying and clearing runaway queries on the PI System. The technique to use varies with the PI System type.

PI System for OpenVMS Problems with expensive queries in this PI System type normally clear themselves. Since each client connection creates a unique


43

Chapter 5

Troubleshooting

PI Server process, query problems in a single client connection rarely affect other users. When a client connection times out on your PC, the PI Server process is not aware of this until it attempts to return either results or an error to the client. When it does, it will discover that the client has dropped the connection. The PI Server program will exit, which causes all resources in use (such as memory) to be released. When attempting to process expensive queries, the most common reason for the PI Server process to exit is an error indicating that it has run out of memory. If VMS process working set sizes are configured to be high, it may take some time before the PI Server process exits for lack of memory. Processes in this state can usually be detected by their consistently high CPU. System managers can usually locate these processes by executing the DCL command: monitor process/topcpu

If an offending process is located, it can be aborted using the DCL stop command. Before doing this, you may wish to confirm that a given PI Server process is processing an expensive query.

Identifying Clients of PI Server Processes It is frequently difficult for the VAX or Alpha system manager to identify clients running PI client applications. This section outlines the available techniques for identifying these clients. The procedure involves obtaining a listing of currently connected clients using utilities provided by your TCP/IP network software vendor. The TCP/IP network libraries supported by PI for OpenVMS are: •

DEC TCP/IP Services for VMS (UCX)

•

Process Software Multinet (formerly TGV)

•

Attachmate Pathway

•

Process Software TCPware

When a client connection starts a PI Server process on the VAX or Alpha, the process name is set to: 44

.

CCCCCC:UUUUNNNN

where: CCCCCC

is the client node name

UUUU is the user name on the client NNNN

is 4 hexadecimal digits to guarantee uniqueness.

For TCP/IP clients, the client node name is available only if the client node is in the VMS system’s host table, or if a Domain Name Server (DNS) is configured at the site. If the name is not available, PIserver will use the "network device", which consists of a character prefix followed by digits. For UCX, the character prefix is "BG". The other three vendors use "INET.” The PIserver process name and network device in use are written to the log file at PIserver startup. Unfortunately, log files for Process Software Multinet and AttachmatePathway are not readable until the PIserver process exits. For TCP/IP clients, the 4 hexadecimal digits (i.e "NNNN" above) are the TCP/IP port number on the client node. This will guarantee uniqueness. To use this number to identify the client, follow these steps. First, translate the hexademical number to decimal. DCL is the quickest way to do this. For example, if the last 4 digits of the process name are "050E", issue the following: $ a = %x050E $ show sym a A = 1294 Hex = 0000050E

Octal = 00000002416

The first number, “1294” in this example, is equal to hexadecimal "050E.” Next, run the vendor-specific utility to examine current connections:

UCX $ UCX SHOW DEVICE

Process Software Multinet $ MULTINET SHOW


45

Chapter 5

Troubleshooting

Attachmate Pathway $ @TWG$COMMON:[NETDIST.MISC]USERLOGIN VMS $ NETSTAT

Process Software TCPware $ @TCPWARE:TCPWARE_COMMANDS $ NETSTAT

Each of these utilities will show the service (usually PISERVER), the local port (545), the client node (name if available, otherwise full IP address) and the port number on the client. The number obtained from the last 4 digits of the PIserver process name matches the client port number.

PI System for Windows NT and UNIX This PI System type handles processing of SQL statements for all client connections through a single PI-SQL Subsystem, or pisqlss. If the subsystem is left to process an unreasonably large query when the client has timed out and disconnected, it tends to consume large amounts of virtual memory and can consume a large amount of CPU time. When running as a service on Windows NT, the PI-SQL Subsystem can be directed to abort processing the current statement and to release the virtual memory it has acquired without shutting down. To do this, you must send a pause command to the pisqlss. There are three techniques available for doing this: 1. Start the Services control panel applet. Select the PI-SQL Subsystem from the list and click the “Pause” button. 2. From a command line prompt, issue the command: net pause pisqlss

3. From a command line prompt, issue the command: \pi\bin\pisqlss –pause

46

.

A message will be written to the message log indicating that the pisqlss has been paused. The query in progress when this command is issued will return immediately with an error –10743 ( i.e., RPC resolver temporarily off-line). Attempting execution of any new query when the subsystem is in this state will also return this error. To resume normal operation, you must send a continue command to the pisqlss. There are three techniques available for doing this: 1. Start the Services control panel applet. Select the PI-SQL Subsystem from the list and click the “Continue” button. 2. From a command line prompt, issue the command: net continue pisqlss

3. From a command line prompt, issue the command: \pi\bin\pisqlss –continue

A message will be written to the message log indicating that the pisqlss has been “continued.” Shutting down and restarting the subsystem can be done at any time and is equally effective. This is the only technique currently available for aborting expensive queries on UNIX.


47

Appendix A

PI-SQL Tables and Syntax This appendix outlines SQL language concepts as they relate to the retrieval of data from the PI System. It discusses the design of the tables that are available to SQL and also includes sample queries.

PI-SQL The term PI-SQL is used to refer to the SQL language as implemented by the PI System. Parsing and execution of SQL statements takes place on the server in both PI for OpenVMS and PI Universal Data Server. The server translates standard SQL queries into internal requests for data from the snapshot, archive and point database. ANSI standard SQL capabilities such as filtering and the processing of joins between tables are supported. PI-SQL works directly with the data in the PI archive. It does not move data into any other intermediate format, such as vendorspecific tables or ASCII files. With PI-ODBC, SQL processing is supported across a network. The ODBC client application need not reside on the same node as the PI Server. In PI for OpenVMS, PI-SQL supports both long and short tag names. PI-SQL supports the following types of PI data retrieval: • • •

September 2001

single value selection rows of compressed data rows of interpolated data (evenly-spaced samples in time)

49

Appendix A

PI-SQL Tables and Syntax • •

aggregate data (totals, averages, minima, maxima, ranges, standard deviations) tag attributes (descriptor, engineering units, span, etc.)

PI Tables The terms relation and table are interchangeable. They refer to the organization of data as rows of data, each having a fixed organization. The columns or fields of a table row will generally have different data types. The PI Archive appears to SQL clients as several tables. There is one table for compressed data, one for interpolated data, and one for each of the possible types of aggregate, or summary, data. These tables are strictly definitions; the data for all of these tables reside in the PI data archive. All of these "virtual" tables make reference to the PI Archive, except PIpoint, PIbatch and PIalias. These represent the PI point database, PI Batch history table and equipment alias table, respectively. Long tag names are supported by PI-SQL. Although the maximum length of tag names is 80 characters, it is possible to configure PI-SQL so that the width of the Tag column in all tables is a site-specific number less than 80. Documentation on how to do this is found in Appendix B of this manual. The names of the supported tables are: Table

50

Description

PIpoint

The PI point database. The column names in PIpoint are the same as the keywords used by PIDIFF.

PIcomp

The PI System archive. Each event is viewed as a single row.

PIinterp

The PI System archive viewed as consisting of rows with evenly spaced times.

PIavg

The archive viewed as consisting of time weighted averages. Since an average implies a period of time, a time interval must be specified when querying this table.

.

Table

Description

PImean

The archive viewed as consisting of arithmetic averages.

PItotal

The archive viewed as consisting of totals.

PImin

The archive viewed as consisting of minima.

PImax

The archive viewed as consisting of maxima.

PIrange

The archive viewed as ranges of data for each tag.

PIstd

The archive viewed as consisting of standard deviations.

PIbatch

The PI-BA batch history table.

PIalias

The PI-BA equipment alias table.

Table A-1

List of PI-SQL Tables

The rest of this section outlines the PI-SQL tables in detail.

PIpoint This is a view of the PI point database, which resembles a relational database table. The fields of this table are the same as the keywords used by PIDIFF. See Chapter 3 of the PI Data Archive Manual for more information on these keywords. The fields of PIpoint are listed in the following table. The boldface type on the Tag column indicates that this is a key field: Name Tag Archiving ChangeDate Changer CompDev CompDevPercent1 CompMax CompMin Compressing Convers CreationDate Creator


Type Character Integer Date Character Float Float Integer Integer Integer Float Date Character

51

Appendix A

PI-SQL Tables and Syntax Name

52

Type

DataAccess1

Character

DataGroup1

Character

DataOwner 1

Character

Descriptor

Character

DigNumber

Integer

DigStartCode

Integer

DisplayDigits1

Integer

EngUnits

Character

ExcDev

Float

ExcDevPercent1

Float

ExcMax

Integer

ExcMin

Integer

ExDesc

Character

FilterCode

Float

InstrumentTag1

Character

Location1

Integer

Location2

Integer

Location3

Integer

Location4

Integer

Location5

Integer

PointAccess1

Character

PointClass1

Character

PointGroup1

Character

Pointid

Integer

PointNumber

Integer

PointOwner 1

Character

PointSource

Character

PointType

Character

PointTypeX1

Character

RecordType

Integer

Res

Integer

Scan

Float

Shutdown1

Integer

SourceTag1

Character

Span

Float

.

Name

Type

SquareRoot

Integer

Step1

Integer

TotalCode

Integer

TypicalValue

Float

UserInt11

Integer

UserInt21

Integer

UserReal11

Float

UserReal21

Float

Zero Table A-2

Float

PIpoint

NOTE 1: These columns are available when querying PI-UDS 3.3 or later.

The PointSource column in the PIpoint table acts as a secondary key. It will be used if the Tag field does not appear in the WHERE clause of a SELECT statement. Most of these fields are in the same format as they are stored in the PI point database. Note the following: •

The exception and compression deviation specifications ( ExcDev and CompDev) are converted from scaled units to engineering units.

•

Tags and engineering unit strings are not case sensitive in the PI System so they will not be case sensitive in SQL statements. String constants used in the WHERE clause of SELECT statements may be upper or lower case. Returned results will be upper case.


53

Appendix A

PI-SQL Tables and Syntax

PIcomp This table is one of the views of the PI Data Archive. The boldface type on the Tag and Time columns indicates that they are key fields. The columns in the PIcomp table are: Name

Type

Tag

Character

Time

Date

Flags

Character

Status

Integer

SValue

Character

Value

Float

Table A-3

PIcomp

There is one "row" in the PIcomp table for every data value stored in the PI Archive. This means that the time field of the data rows do not have evenly spaced values. The Value field is not nullable. This means that if a PI value is bad, the Value field will be set to zero. The programmer must check the value of the Status field to determine whether or not Value is useful. If the value requested from the archive is a digital state, its digital state code is returned in Status. To view the digital state string, the programmer must apply the function DIGSTRING to the column, as in: SELECT DIGSTRING(status) FROM PIcomp WHERE tag = 'SI:NUSOID.AL' AND TIME >= DATE('yesterday') AND TIME <= DATE('today')

This technique could also be used to obtain the digital state string for a real value extraction if the value is bad.

54

.

PIinterp This is a view of the PI Archive as if it consisted of rows with evenly spaced timestamps. It has the following fields: Name

Type

Tag

Character

Time

Date

Status

Integer

SValue

Character

TimeStep

Reldate

Value

Float

Table A-4

PIinterp

The values in this table do not have a one-to-one correspondence with values in the PI data archive. The values are interpolated values such as would be shown on a PI trend display. The values in this table are derived as they are needed - they do not exist on disk. Data rows are generally extracted from PIinterp by specifying a time range. The TimeStep column can be used in a WHERE clause in an SQL SELECT statement to specify the time interval between rows for data retrieval. The data type Reldate in Table A-4 indicates a period of time, rather than an absolute date. The default value is 1 hour, if TimeStep is not specified. Examples of the use of this field are shown under the topic “Multiple Archive Value Queries” later in this Appendix. The TimeStep field is not needed if this table is joined with PIcomp. In this case, the timestamp values from the PIcomp table will be used as the time field values for extraction of data from PIinterp.

Aggregate Tables These tables are views of the PI Archive as if they consisted of aggregate, or summary data. In PI-SQL, the aggregates are: average, total, minimum, maximum, range and standard deviation.


55

Appendix A


In ANSI standard SQL, an aggregate function is applied to the SELECT list, that is, to the names of the columns to be returned by the query. For example: SELECT AVG(value) FROM PIcomp WHERE tag = 'SI:NUSOID.' AND TIME >= DATE('yesterday') AND TIME <= DATE('today')

This syntax implies that the data matching the conditions in the WHERE clause are to be extracted, and that the average function is to be applied to the result. Note the use of the single quotes around character strings in the above example. PI-SQL allows the use of either single or double quotes, as long as they are used consistently. Quotes are always required around character string constants used in WHERE clauses. This is not the most efficient way to extract data from PI. Since PI has internal routines to perform aggregates directly, it is more efficient to use these routines than to extract all matching values and perform the average calculation within PI-SQL. This leads to the creation of aggregate tables, that imply the use of the these aggregate routines. Using the aggregate table PIavg, the above query becomes: SELECT value FROM PIavg WHERE tag = 'SI:NUSOID.' AND TIME >= DATE('yesterday') AND TIME <= DATE('today')

All PI-SQL aggregate tables have the same columns. The PctGood field returns the percentage of the specified time range during which the value of the tag was good.

56

.

Name

Type

Tag

Character

Time

Date

TimeStep

Reldate

PctGood

Float

Value

Float

Table A-5

PI Aggregates

The names of the aggregate tables supported are listed in Table A-1. Note that the aggregate tables also support the TimeStep field, but its interpretation is slightly different from its use in the PIinterp table. If the TimeStep column is not used in a query of an aggregate table, a single summary value over the time interval will be returned. For example, the query: SELECT value FROM PIavg WHERE tag = "SI:NUSOID." AND TIME > DATE("-1d")

returns a single data row containing the average value of "SI:NUSOID." over the last day. The TimeStep column allows the user to calculate several aggregates within a requested interval of time. For example, the query: SELECT value FROM PIavg WHERE tag = "SI:NUSOID." AND TIME > DATE("-1d") AND timestep = RELDATE("1h")

returns 24 one-hour averages for each hour of the last day.


57

Appendix A


PIbatch This table represents the batch information table maintain by the PI-BA module. Entries are made into the table by the PI BatchMonitor whenever a new batch starts. The table has the following fields: Name

Type

Batchid

Character

Starttime

Date

Endtime

Date

Product

Character

Unit

Character

Table A-6

PIbatch

The Endtime column will have a value of zero (or null, if your application supports nulls) if the batch is active. When a batch ends, the PI BatchMonitor will update the table record with the actual batch end time.

PIalias The PI-BA system supports a cross-reference scheme that allows system administrators to refer to tags using an alias name associated with a specific process unit. This facilities building of generalized batch queries that can be used for summarizing batches in different units. For example, the administrator can identify a temperature tag “01:TIC001” by generating an entry for unit “REACTOR1” and alias “TEMP” in the batch configuration database. If the administrator makes a corresponding entry for the temperature tag in “REACTOR2”, then queries need not be constructed with any PI tags. Queries can be run against either process unit by changing only the unit name.

58

.

This cross-reference appears to PI-SQL as the PIalias table. It has the following fields: Name

Type

Unit

Character

Alias

Character

Tag

Character

Table A-7

PIalias

This table can be joined with the PIbatch table and with the PI data tables to generate batch summaries for any process unit.

PI-SQL Functions There are two categories of functions supported by PI-SQL: scalar functions and aggregate functions.

Scalar Functions Scalar functions take one or more arguments and return a single value as a result. If used in select list, a scalar function does not change the number of rows returned by the query. For example, if the query: SELECT status FROM PIcomp WHERE tag = 'SI:NUSOID.' AND TIME >= DATE('today')

returns 12 rows, then the query: SELECT DIGSTRING(status) FROM PIcomp WHERE tag = 'SI:NUSOID.' AND TIME >= DATE('today')

will also return 12 rows. The only difference is that the second query will return digital state strings instead of digital state codes. PI-SQL includes scalar functions that can be used in the select list and WHERE clause of SQL statements. Scalar functions are


59

Appendix A


documented in two groups: ANSI SQL standard functions and functions specific to PI-SQL.

SQL Standard Numeric Functions ANSI SQL Function

Returns...

Description

ABS(number )

Float

Returns the absolute value of the argument

ACOS(number )

Float

Returns the arccosine of the argument in radians

ASIN(number )

Float

Returns the arcsine of the argument in radians

COS(number )

Float

Returns the cosine of the argument in radians

EXP(number )

Float

Returns the exponential value of the argument

LOG(number )

Float

Returns the natural logarithm of the argument

LOG10(number )

Float

Returns the base 10 logarithm of the argument

SIN(number )

Float

Returns the sine of the argument in radians

TAN(number )

Float

Returns the tangent of the argument in radians

SQL Standard Time and Date Functions ANSI SQL Function

60

Returns...

Description

HOUR( date )

Float

Returns the hour in the argument in the range 0-23

MINUTE(date )

Float

Returns the minute in the argument in the range 0-59

MONTH( date )

Float

Returns the month in the argument in the range 1-12

SECOND(date )

Float

Returns the second in the argument in the range 0-59

.

ANSI SQL Function

Returns...

YEAR(date )

Float

Description Returns the year in the argument as an 4 digit number

SQL Standard String Functions ANSI SQL Function

Returns...

Description

CONCAT(string1, string2,...)

String

Concatenates any number of string arguments (NOTE ANSI SQL specifies only 2 arguments for this function)

LCASE(string )

String

Converts all characters in the argument to lower case

LEFT(string, count )

String

Returns the leftmost ‘count’ characters of ‘string’

LENGTH(string)

Integer

Returns the length of the argument

RIGHT( string, count )

String

Returns the rightmost ‘count’ characters of ‘string’

UCASE(string )

String

Converts all characters in the argument to upper case

PI-SQL Functions PI-SQL Function

Returns...

DATE(string )

Date

Converts a PI absolute or relative time string to an internal timedate format

DIGCODE( string )

Integer

Converts a digital state string to a state code

DIGSTRING( int )

String

Converts a digital state code to a state string

RELDATE(string )

Reldate

Converts a PI relative time string to an internal relative time

TAGNAME(int or string )

String

Converts a PI point id to a string, or reformats a passed tag name

TAGNUM(string )

PI-PE ‘Tag’ type1

Converts a PI tag name to a Performance Equation ‘Tag’ type


Description

61

Appendix A

PI-SQL Tables and Syntax NOTE: While this return type appears as an integer to PI-SQL, it is actually a tag data type to PI-PE. This distinction is important when calling PI-PE functions from PI-SQL.

Aggregate Functions Aggregate functions may be used in a select list only; they cannot be used in a WHERE clause. These functions are used to summarize results of several returned rows of data and return a single result. For example, of the above query were modified to: SELECT COUNT(*) FROM PIcomp WHERE tag = 'SI:NUSOID.' AND TIME >= DATE('today')

then the query would return a single integer row containing the value 12. PI-SQL includes the following aggregate functions that can be used only in the select list of SQL statements. The expression passed the function argument must contain the name of a column: PI-SQL Function

Returns...

Description

AVG(int or float )

Float

Returns the arithmetic average of the passed expression over the returned rows.

COUNT( any type )

Integer

Returns the number of rows returned by the query. The argument may be of any type, or “(*)”; it is ignored.

MAX(int or float )

Float

Returns the smallest value of the passed expression over the returned rows.

MIN(int or float )

Float

Returns the largest value of the passed expression over the returned rows.

SUM(int or float )

Float

Returns the sum of the passed expression over the returned rows.

Users should be aware that the aggegate functions return arithmetic, not time-weighted results since they act only on the

62

.

values returned by the SQL query. To obtain direct access to the PI System’s time-weighted aggregates, formulate your queries to access the aggregate tables: PIavg, PItotal, PImin, PImax.

Calling PI-PE Functions from PI-SQL It is possible to call standard PI Performance Equation (PI-PE) functions from PI-SQL, including user-defined formulae. Function names are case-insensitive in both PI-PE and PI-SQL. Users should be aware of the more strict data typing of PI-PE to avoid getting mismatch errors from the PI-PE parser.

PI-PE Tag and Timestamp Data Types For example, obtaining the last event before midnight for the tag “sinusoid” in a performance equation would be coded as: PrevVal (‘sinusoid’, ‘today’)

An equivalent statement in PI-SQL, SELECT PrevVal(‘sinusoid’, ‘today’)

would fail due to data type mismatching: the PI-SQL parser would report that the ‘PrevVal’ function requires a tag and a timestamp, and was called instead with two character strings. The solution is to use PI-SQL functions to explicitly change the data types of the arguments, as in: SELECT PrevVal(TAGNUM(‘sinusoid’), DATE(‘today’))

This query returns the expected result.

PI-PE Character String and Number Data Types The implementation of character strings and numbers is the same for both PI-PE and PI-SQL. While PI-SQL makes a distinction between Real and Integer data types, both are acceptable as the PI-PE Number data type.


63

Appendix A


Single Archive Value Queries The sample queries in this section retrieve a single row of data from one of the virtual tables.

Querying PIcomp The most recent value for any tag, that is, the snapshot value, can be retrieved by querying the PIComp (compressed data event) table.

Retrieving a Current Value This sample query retrieves the current value of the "Sinusoid" tag: SELECT time, value, status FROM picomp WHERE tag = "sinusoid" AND time = DATE("*")

Note that the current time is denoted by the asterisk ("*"), as it is in standard PI displays.

Retrieving Time, Status, Value from PIcomp This query obtains a single value from PIcomp, if one exists in the archive. SELECT FROM WHERE AND

time,status,value picomp tag = 'VD:F1002' time = DATE('12-Aug-88 12:08:16')

This query might return a time of "12-Aug-88 12:08:16", a status of 0, and a value of 12.763. If there had been no event in the archive at the exact time given in the WHERE clause, the query would have returned no rows at all. The PIcomp table is most useful if a list of data is requested. For examples, see “Multiple Archive Value Queries” and “Join Queries.”

64

.

Querying PIinterp Sample queries in this section are queries on the PIinterp table alone.

Retrieving Status, Value The simplest SQL queries retrieve a single value from the PI data archive. If there is no value at a particular time, an interpolated value will be returned for tags with resolution code 1, 2, or 3, and the previous value will be returned for resolution code 4. The query shown below retrieves a value for a particular time: SELECT FROM WHERE AND

status,value piinterp tag = 'VD:F1002' time = DATE('12-Aug-88 12:00')

This query might return a status of 0 and a value of 12.763.

Retrieving Time, Status, Value The following query retrieves the PI status code and floating point value for the specified tag and time stamp: SELECT FROM WHERE AND

time,status,value piinterp tag = 'VD:F1002' time = DATE('12-Aug-88 12:00')

This query might return a time of "12-Aug-1988", a status of 0, and a value of 12.763.

Retrieving Time, Digital State String The digital state string for a bad value or a digital tag is returned in the string field: SELECT FROM WHERE AND

time, DIGSTRING(status) piinterp tag = 'VD:M101' time = DATE('12-Aug-88 6:00')

This query might return a time of "12-Aug-1988 6:00" and a string of "Cascade".


65

Appendix A


Retrieving Aggregates Aggregates are summaries of several rows of data. The result of an aggregate calculation is always a single number. Queries in this section demonstrate two different methods of calculating aggregates. One method is to use the PI aggregate tables. The second is to apply an ANSI standard aggregate function.

Retrieving a Single Aggregate The query in this section retrieves an 8-hour average for the specified tag. Queries to the PIavg table will return a time weighted average. If an arithmetic average is required, issue a query to the table PImean. This type of average may be suitable for calculating the average of laboratory samples which are not evenly spaced in time due to missing measurements. All queries that do not reference the TimeStep column retrieve a single value from the aggregate tables. For example, SELECT FROM WHERE AND AND

status,value,pctgood piavg tag = 'VD:F1002' time >= DATE('8-dec-88 7:00') time <= DATE('8-dec-88 15:00')

This query might return a status of 0, a value of 14.280, and a pctgood of 100. By default, the timestamp used to identify the aggregate is the time at the end of the time period, in this case '8dec-88 15:00'. PISQL allows this default to be overridden, so that the start time of an interval is used as the timestamp of the aggregate. Refer to Appendix B for details.

66

.

Using TimeStep to Retrieve Several Aggregates To generate a list of aggregates in the time range specified, apply a constraint to the TimeStep field as follows: SELECT FROM WHERE AND AND AND

status,value,pctgood piavg tag = 'VD:F1002' time >= DATE('8-dec-88 7:00') time <= DATE('8-dec-88 15:00') timestep = RELDATE('1h')

This query will return a list of one hour averages for each hour between 7:00 a.m. and 3:00 p.m. By default, the timestamp used to identify each aggregate is the time at the end of each hour. This means that the average of the values between 11:00 and 12:00 is identified with 12:00 as its timestamp. For all averages in the above query, the time intervals covered and the timestamps associated with them can be pictured as follows:

6:00 Time > = 7:00 8:00 9:00 10:00

7:00 average 8:00 average

This 7:00 average covers a period of time BEFORE the start time.


11:00 12:00 13:00 14:00 Time < = 15:00

15:00 average

The interval surrounded by the dashed line above represents a special case. This is an average of the values between 6:00 and 7:00. It is included in the set of query results because the timestamp of the resulting average is 7:00, even though the times in the first interval are before 7:00.


67

Appendix A


It is possible to override the default setting of the aggregate timestamp (see Appendix B, Boolean Settings). If the aggregate timestamp is set to the start of each interval, the time intervals covered and the timestamps associated with them can be pictured as follows:

7:00 Time > =

8:00 9:00


10:00 11:00 12:00 13:00 14:00 15:00 Time < = 16:00


This 15:00 average covers a period of time AFTER the end time.

Here, the interval surrounded by the dashed line appears at the end of the time range. This is an average of the values between 15:00 and 16:00. It is included in the set of query results because the timestamp of the resulting average is 15:00, even though the times in the last interval are after 15:00.

Using the ANSI Standard AVG Function This query follows the ANSI standard SQL syntax for the calculation of the average of several data rows: SELECT FROM WHERE AND AND

labavg = AVG(value) picomp tag = 'P1:DIRT' time >= DATE('8-dec-88 7:00') time <= DATE('9-dec-88 7:00')

This query gets compressed data from the PI Archive using the PI API and performs the average within the calling PI-SQL routine. This means that the final result will be an arithmetic average of the returned rows, that is, the sum of the value on each returned row divided by the number of rows. This is equivalent to querying

68

.

PImean. Most process applications require a time weighted average, as returned when querying the PIavg table.

Multiple Archive Value Queries Queries in this section are coded to return several rows of data. The only change to the queries is that they use a range of times, rather than a single or default time.

Querying PIcomp The query in this section retrieves several rows of data from the virtual table PIcomp.

Retrieving Time, Status, Value This query retrieves several rows of data for a single tag: SELECT FROM WHERE AND AND

time,status,value picomp tag = 'SQ:F100' time >= DATE('19-Aug-88 0:00') time <= DATE('19-Aug-88 7:00')

This query might produce the following list: |time |19-aug-1988 |19-aug-1988 |19-aug-1988 |19-aug-1988

|19-aug-1988 |19-aug-1988 |19-aug-1988 |19-aug-1988


00:04:12 00:07:30 00:12:06 00:21:48 . . . 06:31:12 06:35:54 06:41:00 06:57:18

|status|value | | 0| 13.236| | 0| 12.963| | 0| 12.774| | 0| 12.265|

| | | |

0| 0| 0| 0|

9.012| 9.843| 9.121| 9.926|

69

Appendix A


Querying PIinterp The queries in this section retrieve several rows of data from the virtual table PIinterp.

Retrieving Data without Using TimeStep If the TimeStep column is not used in the query, PI-SQL will use the default time interval of 1 hour to space the times on the returned rows: SELECT FROM WHERE AND AND

time,status,value piinterp tag = 'SQ:F100' time >= DATE('19-Aug-88 0:00') time <= DATE('19-Aug-88 6:00')

This query might produce the following list: |time |19-aug-1988 |19-aug-1988 |19-aug-1988 |19-aug-1988 |19-aug-1988 |19-aug-1988 |19-aug-1988

00:00:00 01:00:00 02:00:00 03:00:00 04:00:00 05:00:00 06:00:00

|status|value | | 0| 13.339| | 0| 13.006| | 0| 12.970| | 0| 12.819| | 0| 9.576| | 0| 9.911| | 0| 9.985|

Retrieving Data Using TimeStep If TimeStep is specified, the number of values is the time range divided by the TimeStep (plus one if the TimeStep divides evenly). SELECT FROM WHERE AND AND AND

time,status,value piinterp tag = 'SQ:F100' timestep = '+10m' time >= DATE('19-Aug-88 0:00') time <= DATE('19-Aug-88 6:00')

This query might produce the following list:

70

.

|time |19-aug-1988 |19-aug-1988 |19-aug-1988 |19-aug-1988

|19-aug-1988 |19-aug-1988 |19-aug-1988 |19-aug-1988

00:00:00 00:10:00 00:20:00 00:30:00 . . . 05:30:00 05:40:00 05:50:00 06:00:00

|status| | 0| | 0| | 0| | 0|

| | | |

0| 0| 0| 0|

value | 13.339| 13.006| 12.970| 12.819|

9.576| 9.702| 9.911| 9.985|

If specified in the SELECT list, the TimeStep column contains the value entered in the WHERE clause of the query. The column contains the default value if TimeStep was not specified. For example: SELECT FROM WHERE AND AND

time, timestep, value piinterp tag = 'SQ:F100' time >= DATE('19-Aug-88 0:00') time <= DATE('19-Aug-88 3:00')


00:00:00 01:00:00 02:00:00 03:00:00

|timestep|value | | 1 h| 13.339| | 1 h| 13.006| | 1 h| 12.970| | 1 h| 12.819|

Join Queries Joins between virtual tables are possible using ANSI standard join syntax. The interpretation of the results is the same as in standard SQL.

Correlation Names Join queries have more than one table in the FROM clause of the SELECT statement. Since many PI-SQL tables have column names in common, a technique is necessary to reference columns in specific tables in the select list and the WHERE clause.


71

Appendix A


The SQL syntax to solve this problem involves the use of correlation names. A correlation name is a label for a table. It is defined in the FROM clause, and can be used in the select list and the WHERE clause, as in: SELECT FROM WHERE AND AND

c.time, c.value, pt.descriptor picomp c, pipoint pt c.tag = 'sinusoid' c.time = DATE('*') c.tag = pt.tag

You can omit the correlation name in the select list or WHERE clause if the column name is found in only one table in the FROM clause. In the above example, the pt.descriptor in the select list could have been written as simply descriptor. Correlation names can be used for single table queries: SELECT FROM WHERE AND

c.time, c.value, c.status picomp c c.tag = 'sinusoid' c.time = DATE('*')

A correlation name can be the name of a table to which it refers. In this case, you do not need to define the correlation name in the FROM clause, although you can if you wish: SELECT FROM WHERE

pipoint.tag, pipoint.descriptor pipoint pipoint pipoint.pointsource = 'R'

Correlation names cannot be reserved keywords. This means you cannot use SQL language keywords, PI-SQL function names or PI-PE function names as correlation names. In addition, you cannot use the following letters: d, h, m and s. These letters are keywords in the PI-PE language.

Joining PIcomp and PIinterp In this example, the table PIcomp is joined with PIinterp, without the use of the TimeStep column in PIinterp. To PI-SQL, this means that the timestamps found in PIcomp (that is, actual times in the PI Archive) are to be used as the values of the Time field in PIinterp.

72

.

The following query therefore finds good values of the tag 'SQ:A200' that are less than 15 at the exact times for which events are recorded for 'SQ:F100': SELECT FROM WHERE AND AND AND AND AND AND

i.time, i.status, i.value picomp c, piinterp i c.time = i.time c.tag = 'SQ:F100' i.tag = 'SQ:A200' i.status = 0 i.value < 15 c.time >= DATE('19-Aug-88 0:00') c.time <= DATE('19-Aug-88 6:00')


|19-aug-1988 |19-aug-1988 |19-aug-1988 |19-aug-1988

00:04:12 00:07:30 00:12:06 00:21:48 . . . 04:37:36 04:39:48 05:41:00 05:57:18

|status|value | | 0| 13.236| | 0| 12.963| | 0| 12.774| | 0| 12.265|

| | | |

0| 0| 0| 0|

11.012| 11.843| 9.552| 9.899|

The conditions in the WHERE clause can be used to perform the equivalent of the filtered retrievals in the PI Analysis Package.


73

Appendix A


Joining PIinterp with Itself Evenly spaced lists of values for several tags can be obtained by joining the PIinterp table with itself one or several times. For example: SELECT x.time, "Sine"=x.value, "Snap"=a.value, Frac"=b.value, "PE"=c.value FROM piinterp x, piinterp a, piinterp b, piinterp c WHERE x.tag = "sinusoid" AND a.tag = "sy:snp001" AND b.tag = "sy:frac01" AND c.tag = "sy:peq001" AND x.time >= DATE("today") AND x.timestep = RELDATE("1h") AND a.time = x.time AND b.time = x.time AND c.time = x.time

This query uses the SQL standard capability of assigning names to output columns. The query obtains values for four tags, evenly spaced at one hour intervals, starting at the most recent midnight ("today"). The value of the TimeStep column must be constrained once, as shown. If the TimeStep constraint is applied to each of the four tables in the query, PI-SQL will not see the time lists as equivalent and will return a Cartesian product of rows in the four tables. A Cartesian product in SQL means that a given row in one table is matched with every row in the next table.

Joining PIinterp with Aggregate Tables It is frequently valuable to obtain a list of evenly spaced data values from the PI Archive, along with summary information on each interval. Such a list can be obtained by joining PIinterp with one or several aggregate tables, for example:

74

.

SELECT a.tag, a.time, "Sine"=a.value, "Average"=b.value, b.pctgood, "StdDev"=c.value, c.pctgood FROM piinterp a, piavg b, pistd c WHERE a.tag = "sinusoid" AND b.tag = a.tag AND c.tag = a.tag AND a.time >= DATE("yesterday") AND a.time <= DATE("today") AND a.timestep = RELDATE("1h") AND b.time = a.time AND c.time = a.time

This query returns a list of hourly averages and standard deviations for yesterday's date. By default, the first hourly average, timestamped yesterday at midnight, is the average of the hour ending at midnight. If the aggregate time is set to be the start of the interval, then the last interval becomes the time from midnight today until 1:00 a.m. this morning. In both cases, each list of aggregates includes one interval that is outside the time range.

Joining PIcomp with Aggregate Tables Joining PIcomp with aggregate tables yields results comparable to those in the previous sub-section. However, since the temporal spacing of the rows of PIcomp varies, the resulting aggregates are not evenly spaced. Aggregates are calculated over the time interval between rows of PIcomp. For example: SELECT a.time, b.timestep, "Sine"=a.value, "AVG"=b.value, b.timestep FROM picomp a, piavg b WHERE a.tag = "sinusoid" AND b.tag = a.tag AND a.time >= date("today") AND b.time = a.time

If this query were to be executed at exactly 4:00 a.m. on the 15th of November, 1993, the results might appear as follows: TIME 15-Nov-93 00:04:12 15-Nov-93 00:29:42 15-Nov-93 00:55:12


Sine 51.817375 61.494797 71.172211

AVG 46.879482 56.656082 66.333504

TIMESTEP 1530s 1530s 1530s

75

Appendix A

PI-SQL Tables and Syntax 15-Nov-93 15-Nov-93 15-Nov-93 15-Nov-93 15-Nov-93 15-Nov-93 15-Nov-93

01:20:42 01:41:12 02:06:42 02:32:12 02:48:42 03:14:12 03:54:12

80.849632 88.628807 92.831200 97.036652 99.755852 97.717216 94.515823

76.010925 84.739220 90.730003 94.933929 98.396248 98.736526 95.095680

1530s 1230s 1530s 1530s 990s 1530s 870s

Again, the setting of the aggregate timestamp influences the results. The default setting of this option assigns the time at the end of each interval. In this case, the first aggregate covers the interval between the first matching PIcomp value and the value before the start of the time range. This can be pictured this way:

23:57:12 00:00:00

00:04:12 00:29:42 00:55:12

00:04:12 average 00:29:42 average 00:55:12 average

03:14:12 03:39:42 03:54:12

03:39:42 average 03:54:12 average

04:00:00 Note that PI-SQL finds the first interval before midnight (“23:57:12” in this example), calculates the average between this time and “00:04:12” just after midnight and assigns it the timestamp of “00:04:12”, which is the time at the end of the interval. If the aggregate timestamp had been set to the start of the time interval at the time this query was run, then the interpretation of the results would be somewhat different. In the case, the last aggregate would cover the time between the last matching

76

.

PIcomp value and the first value after the end of the time range. This can be pictured this way:

00:00:00

00:04:12 00:29:42 00:55:12

00:04:12 average 00:29:42 average 00:55:12 average

03:54:12 04:00:00

04:06:12

03:54:12 average

Here, PI-SQL finds the first interval after 4:00 (“04:06:12” in this example), calculates the average between “03:54:12” and this time and assigns it the timestamp of “03:54:12”, which is the time at the start of the interval.

Joining PIpoint with Archive Tables A query can join the PIpoint table with any of the archive tables to define a set of tags of interest. For example, the following query finds the current values of all tags monitored by the interface whose point source is "X": SELECT p.tag, p.time, p.value, p.status FROM PIcomp p, PIpoint t WHERE p.tag = t.tag t.tag AND t.pointsource = 'X' AND p.time = DATE('*') DATE('*')

This query retrieves the most recent value for each tag matching the restriction on the PointSource field of PIpoint . Similar queries could be constructed to find, for example, all points from a particular point source that reported values with a bad status in the last eight hours.


77

Appendix A


The user may apply constraints to any other fields in the PIpoint table in the WHERE clause.

Querying PI-Batch Information Queries in this section demonstrate methods for obtaining batch information tracked by the PI-Batch module using the PIbatch and PIalias tables. This section also includes examples of obtaining summary process data for batches by including PI archive tables in the SQL join.

Querying PIBatch Information about batches can be obtained from the PIbatch table, for example: SELECT * FROM pibatch WHERE starttime >= DATE(“yesterday”)

This query might return the results: BATCHID BA TCHID

UNIT

PRODUC T

STARTTIME

ENDTIME

LGR032

FERMENT1

LAGER1

25-Apr-96 10:21:26

LGR013

FERMENT1

LAGER2

25-Apr-96 15:05:00

ALE5

VAT23

ALE52

25-Apr-96 16:21:26

PILS21

VAT15

PILSXL

25-Apr-96 22:24:26

LGR031

FERMENT2

LAGER2

26-Apr-96 08:00:26

ALE6

VAT23

ALE51

26-Apr-96 10:21:26

25-Apr-96 14:50:26 25-Apr-96 18:24:26 26-Apr-96 00:24:26 26-Apr-96 01:24:26 26-Apr-96 10:24:26 01-Jan-70 00:00:00

Note the endtime value of the last row of results. This is a “zero” timestamp that indicates that the batch is still running. If your SQL query application is capable of handling SQL null return values, then the value of this column would be returned as a null value. Constraint values may be applied to any of the columns of PIbatch using the WHERE clause. You should be aware that the

78

.

PI Batch module maintains indices on starttime, endtime and batchid . Queries that do not apply constraints to at least one of these columns will force a scan of the entire PIbatch table. An example of such a query would be: SELECT * FROM pibatch WHERE unit = "FERMENT1"

Querying PIalias An alias is the name of a process data value for a specific process unit. It is used by the PI Batch module as an alternate method of identifying tags in the PI System. The PIalias table provides access to this cross-reference using SQL statements, for example: SELECT * FROM pialias WHERE unit = "FERMENT1"

This query returns all alias names and corresponding tags associated with the FERMENT1 unit. The PI Batch module maintains indices on both the unit and alias columns.

Joining PIbatch and PIalias It is frequently useful to generate a list of tags associated with particular batches. This can be obtained by joining the PIbatch and PIalias tables, for example: SELECT b.batchid, b.starttime, a.unit, a.alias, a.tag FROM pibatch b, pialias a WHERE a.unit = b.unit AND a.unit = "FERMENT1" AND b.starttime > DATE("-1h") AND b.endtime <= DATE("*")

This query might return results such as: BATCHID

STARTTIME

LGR032 LGR032 LGR032 LGR031

25-Apr-96 25-Apr-96 25-Apr-96 26-Apr-96


10:21:26 10:21:26 10:21:26 08:00:26

UNIT

ALIAS

TAG

FERMENT1 FERMENT1 FERMENT1 FERMENT1

TEMP PRESS LEVEL TEMP

01:TI0011. 01:PI0201. 01:LL3001. 01:TI0011.

79

Appendix A

PI-SQL Tables and Syntax BATCHID

STARTTIME

UNIT

ALIAS

TAG

LGR031 LGR031

26-Apr-96 08:00:26 26-Apr-96 08:00:26

FERMENT1 FERMENT1

PRESS LEVEL

01:PI0201. 01:LL3001.

Query results are more useful when combined with process data from the PI System archive, which will be outlined in the next section.

Supplementing Batch Information with Process Data The PIinterp table can be used to obtain process data values at either the start or the end of the batch. As an example, the following query obtains the values of all tags for which an alias exists on the unit FERMENT1 at the start of the batch: SELECT b.batchid, b.starttime, a.tag, i.value, i.status FROM pibatch b, pialias a, piinterp i WHERE a.unit = b.unit AND a.unit = "FERMENT1" AND b.starttime > DATE("-1h") AND b.endtime <= DATE("*") AND a.tag = i.tag AND b.starttime = i.time

This query might return results such as: BATCHID

STARTTIME

TAG

VALUE

STATUS

LGR032

25-Apr-96 10:21:26

01:TI0011.

125.3

0

LGR032

25-Apr-96 10:21:26

01:PI0201.

75.4

0

LGR032

25-Apr-96 10:21:26

01:LL3001.

14.0

0

LGR031

26-Apr-96 08:00:26

01:TI0011.

119.7

0

LGR031

26-Apr-96 08:00:26

01:PI0201.

70.1

0

LGR031

26-Apr-96 08:00:26

01:LL3001.

13.3

0

It is important to use the PIinterp table instead of the PIcomp table for this query, since the former will always return a result for any time. Joining with the PIcomp table will tend to generate no rows of data, since the likelihood of the PI System archive recording a value at the exact time of batch start is low.

80

.

Summary data can be obtained by joining PIbatch and PIalias with one of the PI-SQL aggregate tables, for example: SELECT b.batchid, b.starttime, a.tag, x.value, x.status FROM pibatch b, pialias a, piavg x WHERE a.unit = b.unit AND a.unit = "FERMENT1" AND b.starttime > DATE("-1h") AND b.endtime <= DATE("*") AND a.tag = x.tag AND x.time >= b.starttime AND x.time <= b.endtime BATCHID

STARTTIME

LGR032 LGR032 LGR032 LGR031 LGR031 LGR031

25-Apr-96 25-Apr-96 25-Apr-96 26-Apr-96 26-Apr-96 26-Apr-96

TAG

10:21:26 10:21:26 10:21:26 08:00:26 08:00:26 08:00:26

01:TI0011. 01:PI0201. 01:LL3001. 01:TI0011. 01:PI0201. 01:LL3001.

VALUE

PCTGOOD

125.3 75.4 14.0 119.7 70.1 13.3

100.0 100.0 94.6 99.0 100.0 100.0

Note the manner in which the starttime and endtime values are used as constraints for the PIavg table. This causes PI-SQL to generate time-weighted averages for each process variable for each batch.

Understanding WHERE Clause Execution Most of the queries in this document are examples of simple conjunctive queries, that is, queries whose WHERE clauses consist of equating column names to constants and joining all constraint conditions with the AND keyword. PI-SQL is designed to take advantage of this common type of query. If a query is a simple conjunction, then all required constraint information is extracted from the WHERE clause and is applied to the referenced tables. The WHERE clause, which is an executable Boolean expression, is not evaluated. In certain cases, however, the simple conjunction conditions are not met and execution of the WHERE clause using a set of proposed column values is required. When this occurs, users must be careful that all variables in the WHERE clause have consistent data types. PI ODBC Driver User's Guide

81

Appendix A


Identifying Non-Conjunctive Queries PI-SQL determines that a query is not a simple conjunction if any of the following are found in the WHERE clause:

OR Keyword If this keyword is used anywhere, the WHERE clause will be executed.

No "Target" A target is a simple column on one side of an equality or inequality sign, such as: ...WHERE a.tag = "sinusoid" AND...

This common construct makes it convenient for PI-SQL to extract constraint information. If functions are applied to constants or column names on both sides of an equal sign, then it is not possible to extract sufficient constraint information. For example: ...WHERE TAGNAME(a.tag) = TAGNAME(b.tag) AND...

If this pattern is found, the WHERE clause will be executed.

Multiple Inequalities If more than one inequality constraint is found on the upper or lower bound of a column value, WHERE clause execution will be required. The following example shows two upper bounds on a.value: WHERE a.value < 50 AND a.value < 75

One of the upper bounds is redundant. Note that the presence of one upper and one lower bound does not violate the rule, as in this common example: WHERE a.time >= DATE("yesterday") AND a.time <= DATE("today")

82

.

Avoiding Data Type Mismatches If the WHERE clause is to be executed, all values to be compared must have matching data types. In addition, character strings must format information in the same manner. This section outlines common examples of data type mismatch and how to avoid them.

TAG Column The data type of the TAG column is a 12 character string. This means that the statement: TAG = "sinusoid"

does not represent a data type mismatch. It does, however, represent a difference in data formatting. The format of the TAG column is upper case characters, including PI tag delimiters (":" and "." in a standard PI configuration) and internal spaces. To create a conjunction that would evaluate to TRUE, the above expression would need to be written: TAG = "SI:NUSOID. "

It is more convenient to use the TAGNAME function so that PISQL does the formatting, as in: TAG = TAGNAME("sinusoid")

It is not necessary to apply the TAGNAME function if a join condition is specified: A.TAG = B.TAG

since the two columns are identical in both data type and formatting.

TIME Column The data type of a PI TIME column is a PI internal time, which is a 4-byte integer representing the number of seconds since "1Jan-70 00:00:00". This means that any attempt to constrain its value using a time string is technically incorrect, as in: TIME = "1-Oct-93 22:00:00"


83

Appendix A


This syntax is accepted by PI-SQL, but is useful only if the WHERE clause will not be executed. To avoid a data type mismatch, use the DATE function: TIME = DATE("1-Oct-93 22:00:00")

This function converts a passed character string to a PI internal time. A calculation error occurs if the character string argument does not represent a valid PI time string.

TIMESTEP Column This document describes the data type of the TimeStep column as "Reldate", meaning relative date/time. This is the time difference between two times, and is not to be confused with an absolute time. The construct: TIMESTEP = "+2h"

although technically a comparison between a relative time and a character string, will be processed correctly if WHERE clause execution can be avoided. Note that a "+" or "-" sign may be present; the exact sign used does not matter. Applying the DATE function will not correct the data type mismatch, since this function converts a character string to an absolute time. Instead, use the RELDATE function: TIMESTEP = RELDATE("2h")

Inserting Data into PI If your PI Server is running PI-UDS 3.3 or later, you can put new data into PI using SQL statements. Data can be added using the INSERT keyword on the picomp table only. Two forms of the INSERT keyword are supported:

INSERT with VALUES List INSERT INTO picomp (col1, col2, col3,…) VALUES (val1, val2, val3,…)

84

.

The list in the first pair of parentheses consists of valid column names in the picomp table. The list in the second pair of parentheses are values to be put into the respective columns of the new data row. The data types of the items in the second list must match the data types of the columns in the first list. In PISQL, you may specify a quoted string containing an absolute or relative time string for a column of Date datatype. The SQL standard specifies that all items in the VALUES list must be constants. Function calls, even with constant arguments, cannot be used.

INSERT with SELECT INSERT INTO picomp (col1, col2, col3,…) SELECT col1, col2, col3 FROM …

As above, the list in the first pair of parentheses are valid column names in the picomp table. What follows is a complete SQL SELECT statement. You are responsible for making sure that the data types of the columns in the SELECT list match the data types of the columns in the INSERT list.

Providing Key Values When inserting values into PI, you need not specify all column names in most cases. Default values, documented in the next subsection, will be provided. This is not true for the two key values of the picomp table: tag and time. You must provide values for both of these columns in all INSERT statements. For example, you cannot write this INSERT statement: INSERT INTO picomp (tag, value) VALUES (“sinusoid”, 50) (INVALID!)

If your intention is to timestamp the new value with current time, you should write instead: INSERT INTO picomp (tag, time, value) VALUES (“sinusoid”, “*”, 50)


85

Appendix A


Default Column Values in INSERT Statements As noted above, values for the tag and time column must be provided in every INSERT statement. There are no defaults. The default value for the flags column is blank. This column may be safely ommitted from any INSERT statement. The default value for the svalue column is blank. If a value is provided for this column, a string-valued event is constructed. If values are provided for both the value and svalue columns in the same INSERT statement, then value will be ignored. Providing the value column and no svalue column for a string point generates an error. Omitting value or status depends on the value of the status column and your choice for handling integer values. The PIODBC Setup dialog box allows you to choose whether to return integer values in the status column, or coerced to floating-point values in the value column. The tables below show PI’s behavior for possible values of the status column, which are listed across the tops of the tables. The status column may be absent, present with a value of 0 and present with a non-zero value. The value column may be present or absent; these states are shown down the left-hand edge of the tables.

Integer Values as STATUS Value\Status

86

Status Absent

Status 0

Status Non-Zero

Value Present

OK

OK

OK

Value Absent

Digital Event ‘SetToBad’ (-247)

Error for Real and String points. OK for Integer and Digital points.

OK

.

Integer Values as VALUE Value\Status

Status Absent

Status 0

Status Non-Zero

Value Present

OK

OK

OK

Value Absent

Digital Event ‘SetToBad’ (-247)

OK for Digital points. Error for all others.

OK

Inserting Digital Events Digital events can be inserted into the picomp table for all point types. In all cases, this involves providing an appropriate value for the status column in an SQL INSERT statement. Values of the status column may be zero, positive or negative.

Status is Zero or Positive If the status column value is zero or positive, status is interpreted as an offset into the digital state set defined for a digital point. For non-digital points, a zero or positive status is interpreted as an offset into the system digital set. The only exception to this rule occurs when the "Integer Value as" radio button in the PIODBC Setup dialog box is set to STATUS and you are inserting an event into the picomp table for an integer point. In this case, a zero or positive value is considered a normal data value for the point. If you provide an offset which is out of range for a digital point, the error returned when the INSERT statement is executed by PIUDS is -10702, which means "STATE Not Found."

Status is Negative If the status column value is negative, it must contain the digital state number shifted left 16 bits plus the offset, all negated. Status column values are returned in this format when querying data in the picomp table. If the encoded set number does not match the digital set defined for a digital point, the error returned when the INSERT


87

Appendix A


statement is executed by PI-UDS is -10701, which means "SET Not Found." For non-digital points, the set number is zero. This means that you can insert a digital value using either a positive or negative number. For example, to add a Bad Input digital value to point 'sinusoid', you could write: INSERT INTO picomp (tag,time,status) VALUES ("sinusoid", "*", 255)

which would be interpreted as an offset into the system state set (which is number zero), or: INSERT INTO picomp (tag,time,status) VALUES ("sinusoid", "*", -255)

which would be interpreted directly as a digital state. The result is the same. If you attempt to add a negative value with an encoded digital set number other than zero to the status column of a non-digital point, an error will be returned when the INSERT statement is executed by PI-UDS. The exact error code depends on your setting of the "Integer Value as" radio button in the PI-ODBC Setup dialog box: •

If your choice is STATUS, the error returned is -9, which means "Illegal Status Value or Integer Value."

•

If your choice is VALUE, the error returned is -15031, which means "PIvalue type is not digital."

If the "Integer Value as" radio button is set to VALUE and you are inserting a value into the picomp table for a non-digital point which is positive but larger than the size of the system set, the error returned when the INSERT statement is executed by PIUDS is -15031, which means "PIvalue type is not digital."

Summary of PI-SQL Syntax This section summarizes the SQL language as implemented by PI Servers.

88

.

The first two subsections defined the SQL statements supported. Syntax elements in italics are expanded in later subsections.

Select statement SELECT [DISTINCT] select-list [FROM table-reference-list] [WHERE PI-PE-expression]

Insert statement INSERT INTO picomp (column-list) VALUES (value-list) INSERT INTO picomp (column-list) Select-statement

Column-List column-list ::= column-name [ , column-name, … ]

Select-List select-list ::= * | select-list-item [, select-list-item, …] select-list-item ::= [[column-label | “column-label”] = ] PI-PE-expression

Table-Reference-List table-reference-list ::= table-reference [ , table-reference, … ] table-reference ::= table-name [ correlation-name]

Value-List value-list ::= value-item [ , value-item, … ] value-item ::= [numeric-constant | string-constant]

PI-PE-Expression An expression in PI Performance Equation syntax. All variable names are column names in PI-SQL tables. Names can


89

Appendix A


unqualified column names, or names prefixed with a correlation name, such as c.tag. All select-list-items are PI-PE expressions. The WHERE clause is a PI-PE expression returning a boolean result. All PI-PE syntax is supported, with the addition of the following: •

IS NULL and IS NOT NULL are supported for nullable columns. The only nullable column in the PI-SQL tables is the endtime column in the pibatch table.

•

The SQL IN keyword is supported. An example is:

...WHERE tag IN (‘sinusoid’, ’cdf144’, ’sqf100’)

90

.

Appendix B

Setting PI-SQL Options The term PI-SQL is used to refer to the SQL language as implemented by the PI Server. All PI Servers can parse, optimize and execute SQL statements. Actual retrieval of data from the point database, snapshot and archive is performed on the server. Completed SQL result sets are conveyed to client applications. Using settings in an initialization file on the PI Server, it is possible to customize the processing of SQL statements. This section refers to error codes returned by PI-SQL when certain errors occur during processing. The codes and the means by which they are returned to the user are discussed in Appendix C. This section outlines the customization of PI-SQL processing using an initialization file.

PI-SQL Initialization File It is possible to establish some SQL processing defaults using an initialization file that resides on the server. If present, the initialization file resides in the standard location for data and configuration files for the PI System: For VMS:

PISysDat:pisql.ini

For Windows NT:

\pi\dat\pisql.ini

For UNIX:

/pi/dat/pisql.ini

The options established by the initialization file represent defaults for all users of PI-SQL query processing on a PI Server.

September 2001

91

Appendix B

Setting PI-SQL Options

Usually, these options can be overridden by individual users using commands available in the client application. Refer to the specific product manuals for details. The structure of the initialization file is identical to the files used by Microsoft Windows. It consists of section names surrounded by square brackets ("[ ]"). Entries are listed in each section, as follows: [Section1] entry1=value1

The remainder of this section discusses the options available. All PI-SQL options are summarized in a table at the end of this Appendix.

Hierarchy of PI-SQL Option Settings It is possible to influence PI-SQL settings using more than one technique, and some of the settings may be in conflict. The actual value of settings employed follows this priority scheme: •

Initialization settings,

•

General server settings,

•

Individual user settings.

Individual user settings are established for an ODBC Data Source using the PI-ODBC Setup Dialog box.

Setting Lengths of String Attributes Several point attributes and PI-SQL functions are defined as character strings. Each has a default string length. It is possible to override some of these default lengths.

Setting Tag Length PI-SQL supports long tag names in the PI System for OpenVMS. For all PI System types, the default size of the TAG column for every PI-SQL table is 80 characters. If you wish to restrict the length of the TAG column to a smaller value, you may do this in

92

.

the initialization file on the server. For example, to set the maximum length of the TAG column data values to 25, enter the following: [TAG] length=25

Settting the length of the TAG column also affects the SOURCETAG and INSTRUMENTTAG point attributes.

Setting Digital State String Length The default length of a digital state string returned by the DIGSTRING function is 32 characters. An example of overriding this default is: [DIGSTRING] length=64

Setting Point Source String Length The default length of the POINTSOURCE point attribute is 32 characters in PI Universal Data Server for Windows NT and UNIX, and 1 in PI for OpenVMS. An example of overriding this default is: [POINTSOURCE] length=64

Setting Mode for the INSERT Keyword When the PI-SQL INSERT is processed, a determination must be made of how to add a new data value to the PI Server. By default, the addition mode used is called noreplace, which is also the default for the pisn_putsnapshot routine in the PI-API. This means that a new value always updates the snapshot if the timestamp is newer than the snapshot time, but if the new value is older than the snapshot, it will not overwrite an existing value with the same timestamp in the archive. This can mean that an INSERT statement may succeed, but the new value will not appear in the archive.


93

Appendix B


This choice of INSERT mode can be overridden. An example of overriding this default is: [INSERT] mode=replace

The replace mode will add a new event into the archive, but will update any value already present at the same timestamp. The append mode will add a new value in all cases, even if archive values exist at the same time.

Boolean Options These are options that can be switched on or off by setting a Boolean value in the initialization file. These settings appear under the [OPTIONS] section. Settings must be “1” to set (or, turn on) an option, or “0” to clear (or, turn off) an option; other Boolean values such as "TRUE" or "FALSE" are not recognized. The available Boolean options are as follows:

Option Name

94

Description

AGGRTIMESTART (default = 0)

If set, causes all queries of the aggregate tables to use the time at which the interval starts to identify the aggregate. If clear, the time at which the aggregate period ends is used.

ANSISQLVAL (default = 0)

If set, causes values of integer points to be coerced to floating point values and returned in the VALUE column of any data table. This is equivalent to selecting “Interger Value as VALUE” in the PI-ODBC Setup dialog box.

EXECSAFE (default = 0)

If set, the query will not execute if the PI-SQL determines that a query is too unspecific and would take too long to execute. If clear, execution will proceed regardless of the nature of the WHERE clause1.

QEP (default = 0)

If set, causes the server to dump a query execution plan to a file in the startup directory of the server 2.

.

Option Name

Description

SUBSET (default = 0)

If set, causes the generation of a subset of the actual results if an expensive query is submitted, or if the timeout period expires1.

SHORTTAG (default = 0)

If set, causes PI-SQL to return only short tag names for all values in the TAG column for all tables. If clear, a long tag name is returned if one is defined. This option is supported by PI for OpenVMS only.

Table B-1

Boolean Options

Notes:

1. See Section on "Timeout and Execution Control" in this chapter. 2. The directory in which the query execution plan file appears varies with the PI Server platform. In PI for OpenVMS, check for pisql.qep in PISysExe. In PI Universal Data Server for Windows NT and UNIX, check for pisql_nn.qep in \pi\log. The digits ‘nn’ refer to an SQL handle number.

Improving Query Optimization Since it is possible to place limits on the estimated number of data rows returned by a query, it is important to provide PI-SQL with estimates of the size of the point database and PI archive data. Good estimates of these values improve the estimate of the number of rows to return. There are three items in the initialization file that can be set to influence the estimation process:

NUMTAGS Setting in the [PIPOINT] Section This is an estimate of the approximate number of defined tags in the PI point database . This is different from the total point count for a PI System, since the point database is usually not completely full. If this setting is missing, zero or negative, the number 5000 is used.


95

Appendix B


DAYSONLINE Setting in the [PICOMP] Section This is an estimate of the number of archive days on line . It need not be precise. If this setting is missing, zero or negative on a PI System for OpenVMS, PI-SQL will attempt to find the value from the PI Archive itself if the PI Server is running on a PI Home Node . If running on any other networked system, or on PI for Windows NT and UNIX, the estimate used is 90 days.

ESTIMATE_MARGIN Setting in the [PICOMP] Section When extracting data from the PIcomp table, PI-SQL preallocates resources internally to hold the results . To do this, PISQL estimates the number of values between two times by assuming that the “basic” tag is scanned once per minute and that approximately 1 value in 20 is actually stored in the archive. The resulting estimate is multiplied by the estimate margin which has a default value of 1.2 . This number can be altered by making an entry for it in the initialization file.

Timeout and Execution Control Two options of PI-SQL are used to avoid excessive query runtimes, “Timeout” and “Trap Expensive Queries:” •

Timeouts will allow PI-SQL to abort processing of a query if execution time exceeds a predefined limit.

•

The Trap Expensive Queries option will prevent a query from being attempted if the query execution plan shows that the query would be too time consuming or inefficient. Invoking this option also enables several other run-time execution checks.

If a query either times out or is declared unsafe, it is possible to direct PI-SQL to build a subset of the actual results.

TIMEOUT Setting in the [EXECUTION] Section The need for a timeout option is frequently an issue when the WHERE clause in a SELECT statement is not constrained sufficiently. This usually occurs either by accident, or when an 96

.

automated query development tool attempts to test-execute a partially completed query to check syntax. The best known example is: SELECT * FROM picomp

This query will attempt to return all data for all points. It will eventually abort from a memory allocation failure, but renders your application unusable in the interim. Obviously, such a query would benefit from a timeout limit. To limit execution time, sample entries under the [EXECUTION] heading are shown below: [EXECUTION] timeout=30 numvalues=1024

In the above example, timeout is set to 30 seconds . This means that execution of the query should cease when the elapsed time of the execution step exceeds 30 seconds . [The setting for NUMVALUES is discussed later in this chapter.] If the SUBSET option is set (see the next subsection), PI-SQL can be directed to return a subset of the actual results instead of returning a timeout error. The timeout option takes effect regardless of other option settings. To execute without a limit, set TIMEOUT to zero. This is the PI-SQL default.

SUBSET Setting in the [OPTIONS] Section In SQL, it is generally not possible to return a warning to the user. If an error condition exists, SQL requires that an error code be returned. If a set of results is returned, there can be no error. This limitation of SQL makes it difficult to warn the user that a query is syntactically correct, but would be expensive to execute. The subset option makes it possible to return a subset of the actual results and a warning to the at the same time . This can be useful during the development of a query. A Boolean option (for example: 0=off, 1=on) appears in the


97

Appendix B

Setting PI-SQL Options [OPTIONS] section of the initialization file. For example: [OPTIONS] subset=0

If the TIMEOUT entry in the [EXECUTION] section were to be non-zero while the SUBSET option is off, the query should return no results and a PISQL_TIMEOUT error code if the query timeout expires. If the SUBSET option had been set to 1 ( i.e., ON), then PI-SQL would be directed to return a subset of the actual results and no PI-SQL error . This would allow the developer of a query to complete the work without encountering an error . Users must be aware that the completed query will not reliably represent actual results until the SUBSET option is turned off.

Interaction between TIMEOUT and SUBSET The SUBSET option does not take effect until the TIMEOUT period has expired . In many cases, this will cause the elapsed time of the query to exceed the TIMEOUT setting. For example, consider the expensive query: SELECT * FROM picomp a, piinterp b WHERE a.tag = b.tag AND a.time = b.time

The query execution planner of PI-SQL will determine that the values from PIcomp should be retrieved first . Assume that the query timeout is set to 30 seconds. PI-SQL will have retrieved many values from PIcomp when the 30 second limit expires . If ‘subset’ were off, the query would abort with an error at this point . If SUBSET were on, PI-SQL would stop retrieving PIcomp data and would retrieve a smaller amount of data ( i.e., a single block of data of size NUMVALUES; see below) from PIinterp before exiting. When it does exit, PI-SQL will not return an error in this case.

98

.

NUMVALUES Setting in the [EXECUTION] Section This entry is the number of compressed or interpolated values to read from the PI Archive in a single PI-API call. If present, this setting takes effect independent of other options and settings. If the NUMVALUES setting is less than the number of values between two time constraints, the PI-API call is called again until the time range has been covered. This setting affects only data retrievals for compressed and interpolated data, that is, for SELECT statements against tables PIcomp and PIinterp. Summary values ( i.e., queries against tables such as PIavg and PItotal) are obtained singly in every case, so there are more opportunities to check for timeout. If the initialization file is missing, or the NUMVALUES setting is zero, the default value of 2048 values per PI-API call is used.

Trade-Off Between NUMVALUES And TIMEOUT Settings The NUMVALUES setting interacts with the TIMEOUT setting . It is not possible to check elapsed execution time at any other time than between PI-API calls . This means that a query might run slightly longer than the TIMEOUT value if PI-SQL is in the middle of an archive read when the TIMEOUT period expires. There is a trade-off between NUMVALUES and TIMEOUT settings . Setting NUMVALUES too large reduces the frequency at which PI-SQL can check for timeouts . Small values of NUMVALUES makes for inefficient access to the Archive . The NUMVALUES setting should not be reduced below 1000.

Trapping Expensive Queries (The EXECSAFE Option) This option disables query execution if the query is determined at optimization time to be overly time consuming or inefficient. It can trap expensive queries quickly since PI-SQL will make its determination without attempting to read any data. This option is a Boolean option called EXECSAFE in the [OPTIONS] section of the initialization file.


99

Appendix B


The action PI-SQL takes when it discovers that a query would be time consuming can be configured. By default, a PISQL_NOTSAFE error is returned. If desired, a subset of the actual result set can be generated. See the discussion of the SUBSET option in the next subsection.

SUBSET Setting in [OPTIONS] Section As noted in the previous section, this option causes PI-SQL to generate a subset of the actual result set if a query times out. This option will also cause PI-SQL to generate a subset if a query is determined to be too expensive at optimization time . The advantage is that it will begin building a subset immediately, rather than waiting for a timeout period to expire. The SUBSET option takes effect if either the TIMEOUT setting is non-zero, or if the EXECSAFE option is set.

MAXROWS Setting in [EXECUTION] Section This is the limit of the total data rows to return . Note that this is a limit of the estimated number of rows. After generation of the query execution plan, the estimated number of rows to return is compared against this limit. •

If the limit is exceeded and the SUBSET option is not set, the query will abort with a PISQL_NOTSAFE error.

•

If the SUBSET option is set, PI-SQL will generate a subset of the actual results without reporting an error.

This setting is ignored if the EXECSAFE option is not set.

MAXTIMERANGE Setting in [EXECUTION] Section This value is the largest time range, in number of days, that can be read from any PI data table. If the entry is 0 or a negative number, there is no time range limit. This entry is ignored if the EXECSAFE option is not set.

100

.

Summary of Options This table summarizes the options and settings available in the initialization file: Section/Entry

Description

Default

[OPTIONS] AGGRTIMESTART

Either 1 (On) or 0 (Off). If On, causes all queries of the aggregate tables to use the time at which the interval starts to identify the aggregate. If Off, the time at which the aggregate period ends is used.

Off

[OPTIONS] ANSISQLVAL

Either 1 (On) or 0 (Off). If On, causes values Off of integer points to be coerced to floating point values and returned in the VALUE column of any data table. This is equivalent to selecting “Interger Value as VALUE” in the PI-ODBC Setup dialog box.

[OPTIONS] EXECSAFE

Either 1 (On) or 0 (Off). If On, enables the check of the estimated number of rows (see maxrows) and the time range check (see maxtimerange).

Off

[OPTIONS] QEP

Either 1 (On) or 0 (Off). If On, causes the server to dump a query execution plan to a file in the startup directory of the server.

Off

[OPTIONS] SHORTTAG)

Either 1 (On) or 0 (Off). If On, causes PI-SQL to return only short tag names for all values in the TAG column for all tables. If Off, a long tag name is returned if one is defined. Supported on PI for OpenVMS only.

Off

[OPTIONS] SUBSET

Either 1 (On) or 0 (Off) . If On, causes the generation of a subset of actual results if the query times out (see [EXECUTION] TIMEOUT) or if declared unsafe at optimization time (see [EXECUTION] MAXROWS).

Off

The maximum number of rows to return from a query. This number is checked against the estimated number of rows as determined at optimization time only.

No limit if missing, zero or negative

[EXECUTION] MAXROWS


101

Appendix B

Setting PI-SQL Options Section/Entry

Description

Default

[EXECUTION] MAXTIMERANGE

The largest difference between two times to process, in days. If missing or negative, there is no limit.

No limit if missing or negative.

[EXECUTION] TIMEOUT

The execution elapsed time limit, in seconds. If subset is On when timeout is exceeded, PISQL will proceed to build a subset of the actual results. If missing, zero or negative, there is no limit.

No limit if missing, zero or negative.

[EXECUTION] NUMVALUES

The largest number of values to extract from No limit if PI in a single PI-API call. Larger numbers missing, zero or increase efficiency of archive access. Smaller negative. numbers increase the frequency of timeout checks. If subset is On, this is the largest number of values to extract from each table.

[INSERT] MODE

The archive addition mode to use when processing new rows with the INSERT keyword. The choice of mode governs how new data values interact with events already in the archive. Choices are: noreplace, replace and append.

noreplace

[PIALIAS] NUMROWS

Estimated number of rows in the PIalias table.

50

[PIBATCH] NUMROWS

Estimated number of rows in the PIbatch table.

1000

[PIPOINT] NUMTAGS

Estimated number of tags defined in the PI point database.

5000

[PICOMP] DAYSONLINE

Approximate number of days of archive data on line.

Determined from PI Archive if on a PI for OpenVMS Home Node, otherwise 90 days.

[PICOMP] Factor multiplying the estimated number of ESTIMATE_MARGIN rows of picomp between two times.

Table B-2

102

If missing or negative, 1.2 is used.

Summary of Options

.

Appendix C

PI-SQL Error Codes This appendix outlines how to obtain execution statistics and error information.

Execution Statistics During the execution of a query, PI-SQL maintains several internal counters to track the number and types of PI-API calls made, and other computing resources used. The following table lists the short name and description of each of the internal PI-SQL counters. Several of these counters are exposed as Windows NT/2000 Performance Counters in PI Universal Data Server 3.3 or later. See the PI Universal Data Server System Management Manual for details. Name

Description

EXECTIME

Cumulative execution time, in seconds.

PROGCALL

Number of calls to progress routine (called back by pisql_execute ).

AMICOMPVAL

Number of calls to PISQL compressed values routine.

APICOMPVAL

Number of calls to PI-API compressed values routine (piar_compvalues ). If this number is frequently different from "AMICOMPVAL", please contact OSI Software.

AMIWILD

Number of calls to PISQL wildcard search routine.

APIWILD

Number of calls to PI-API wildcard search routine (pipt_wildcardsearch ).

September 2001

103

Appendix C

PI-SQL Error Codes

Name

Description

APIVALUE

Number of calls to PI-API archive value retrieval routine (pipt_value ).

APIGETSNAP

Number of calls to PI-API snapshot value retrieval routine (pisn_getsnapshot ).

EVAL

Number of internal symbol or constant evaluation requests.

WHEREVAL

Number of times the WHERE clause was executed.

APITIMEDVAL

Number of calls to PI-API timed archive value retrieval routine (pisn_timedvalues ).

APISUMM

Number of calls to PI-API archive summary routine ( piar_summary ).

DOTEVAL

Number of evaluations of a column value.

FUNCEVAL

Number of scalar function evaluations.

APINEXTPTSRC

Number of calls to PI-API database point source search routine (pipt_getnextptwsource ).

TIMELSTCOPY

Number of times a time list was copied from one table to another.

Table C-1

Internal Counters

Error Reporting This section outlines the errors reported by PI-SQL. Errors may be caused by system errors, PI-API errors, or PI-SQL processing errors. For ODBC programmers, the subroutine SQLError is available to obtain a text error message suitable for display in an application. Failures in PI-API or PI-SQL routines are formatted into text messages which can be retrieved by calling this ODBC routine.

ANSI Standard SQL Errors In general, error reporting follows the SQL error code standard. This means that the absence of any error condition causes a PISQL routine to return zero. Syntax and data extraction errors are negative numbers. The only positive return code supported by the ANSI standard is 100, which means that no data rows were

104

.

returned (by a SELECT) or affected (by an UPDATE or DELETE).

PI API Errors The PI API follows a standard that is equivalent. API routines return 0 when processing has completed normally. Negative return codes indicate an error in data access or in the parameter list of the routine. Positive numbers indicate a system error. The actual positive values returned are platform-specific. The same is true for PI-SQL errors. Errors specific to SQL processing are in the range –251 to –275.

PI-SQL Errors This section outlines errors reported by PI-SQL itself. The middle column, Error Name, gives a symbolic name for the error code. Error Value

Error Name

Descript ion

100

NOMOREROWS

No rows were affected by the SQL statement. This means that a SELECT statement returned no rows, an INSERT added no rows, and UPDATE and DELETE altered no rows.

-251, -280

MEMERR

Out of memory (symbolic names are PISQL_MEMERR (-251) and PIGRID_MEMERR (-280).

-252

SYNERR

SQL syntax error.

-253

INVSTMT

Invalid SQL statement.

-254

INTERR

Internal error. If this occurs, please report the problem to OSI Software.

-255

NOSUPPORT

No support. This (valid) SQL statement is not currently supported.

-256

TIMERR

Time error. The passed character string does not represent a valid PI time string.

-257

TAGERR

Tag error. A passed tag constraint string is not found in the PI System.

-258

TYPERR

Data type mismatch.


105

Appendix C

PI-SQL Error Codes

Error Value Value

Error Name Name

-259

CALCERR

Calculation error. The evaluation of either the SELECT list or WHERE clause failed.

-260

INVWHERE

WHERE clause is invalid.

-261

COLERR

Column error. Insufficient number of data columns allocated to receive returned data.

-262

NOSELECT

Statement is not a SELECT.

-263

SYSTEMERR

A system error occurred. Consult the PI Server's log file.

-264

TIMESTEPERR

Timestep error. The constraint applied to a TimeStep column in the WHERE clause was not an equality.

-265

NOQUERY

The query string is empty.

-266

USERABORT

User-defined abort occurred.

-267

INVARG

Invalid argument to a function.

-268

NOTSAFE

The query, although syntactically correct, would be excessively expensive to execute.

-269

NODEFAULT

A modification statement was issued that did not reference a required column.

Table C-2

106

Descript Descript ion

PI-SQL PI-SQL Errors Error s

.

Index Aggregate functions, 64 Aggregate Tables, 57 American National Standards Institute, 4 ANSI Standards, 2, 4 SQL Errors, 111 API Compliance Levels of, 16 APIs, 3 Architecture of ODBC, 5 of PI-ODBC, 13 Archive Tables, 80 Attachmate Pathway, 36, 48 AVG Function, 71 Boolean Options, 98 Call Level Interfaces, 4 Callable Interfaces, 3 CLI Standards, 3, 4 Clients Identifying, 46 Compliance to ODBC API, 7 Configuring PI–ODBC Data Sources, 27 Conformance to ODBC SQL, 8 Connection handle, 10 Connection problems, 35 Current Value Retrieving a, 66 Data Access Package, 7, 15, 21, 23 Data Access Tools, 2 Data Sources, 27 Data Type, 52, 65, 66 Mismatches, 86 TAG Column, 86 timestep, 87 databases "desktop" and remote, 7 DAYSONLINE Setting, 100

September 2001

DB-Library, 3 DEC TCP/IP Services for OpenVMS, 36 DECnet, 36 DLLs, 15 Driver two-tier, 7, 9 Driver Manager definition of, 6 installing, 22 Dynamic SQL, 3 Embedded SQL, 3 Error Codes, 109 Error message prefix and text, 9 Error Reporting, 8, 110 PI-ODBC, 17 Errors, 10 ANSI Standard SQL, 111 PI-API, 111 PI-SQL, 111 ESTIMATE_MARGIN Setting, 100 EXECSAFE Option, 104 Execution Control, 101 Execution Statistics, 109 Expensive Queries, 104 clearing, 45 PI for OpenVMS, 46 PI for Windows NT and UNIX, 48 UNIX, 49 History of ODBC, 2 INSERT, 87 Integer Values as STATUS, 90, 91 Integer Values as VALUE, 90, 92 key values, 88 status negative, 91 status zero or positive, 90 with SELECT, 88 with VALUES list, 88 Install directory location, 22

113

Index Installed Files List, 25 Installing PI–ODBC, 21 iPISQL Utility, 42 options for, 43 Submitting Queries, 43 Joins, 74 PIbatch and PIalias, 82 PIcomp and PIinterp, 75 PIcomp with Aggregate Tables, 77 PIinterp with Aggregate Tables, 77 PIinterp with Itself, 76 PIpoint with Archive Tables, 80 Levels of ODBC API Compliance, 16 Levels of ODBC SQL Conformance, 16 Long tag names, 52, 97 MAXROWS Setting, 105 MAXTIMERANGE Setting, 105 MDAC, 7, 15, 22 Microsoft Access example database, 23 Microsoft Data Access Components, 7, 15, 22 Multiple databases, 10 Multiple Inequalities, 85 Multiple Values, 71, 81 Non-Conjunctive Queries, 85 Numeric Functions, 62 NUMTAGS Setting, 100 Numvalues And Timeout Settings Trade Off Between, 104 NUMVALUES Setting, 103 OCI, 3 ODBC API Compliance Levels, 7 Applications, 9 architecture, 5 Compliant, 5 definition of, 5 Introduction to, 1 SQL Conformance Levels, 8 Standard Error Reporting, 8 ODBC Compliance, 10 ODBC Control Panel applet, 28 ODBC Driver determining characteristics, 42 Open Database Connectivity Concept, 5 Optimization, 99 Options Summary of, 106 OR Keyword, 85

114

Oracle Callable Interface, 3 Pathworks, 36 Performance Equations, 65 PI API Errors, 111 PI for OpenVMS access to PIbatch, 16, 21 aggregate functions, 16, 21 connections to, 36 DAYSONLINE setting, 100 expensive queries, 46 long tag names, 97 minimum version, 16, 21 SHORTTAG option, 99, 106 TCP/IP port, 30 TCP/IP vendors, 36, 46 trace mode, 38, 40 PI for Windows NT and UNIX connections to, 37 DAYSONLINE setting, 100 expensive queries, 48 minimum version, 13, 22 TCP/IP port, 30 trace mode, 38 PI Point Database, 53 PI Server, 16 PI Server Clients Identifying, 46 PI Tables, 52 PIalias, 60 PIbatch, 60 PIcomp, 56 PIDIFF, 53 PIinterp, 57 PILOGIN.DLL, 22 PI–ODBC Data Sources, 27 DLLs, 15 Installing, 21 PI–ODBC Driver, 13, 14 PI-ODBC Install from a Downloaded Kit, 24 from CD-ROM, 23 PI-PE functions, 65 PIpoint, 53 PI-SQL, 15 definition, 51 Error Codes, 109, 111 Functions, 61, 63 Initialization File, 95 joins, 74

.

Pi Odbc Manual

Recommend Documents