Pervasive PSQL v9 Service Pack 2

Note The Installation Notes section contains important information to help you install successfully. Please read this section before attempting to install Pervasive PSQL v9 Service Pack 2 or alter your existing configuration.

Installation Notes

After reading these notes, see the portable document format (PDF) book Getting Started with Pervasive PSQL for installation steps.

Compatibility with Other Releases

This release supports the use of older clients from the previous releases Pervasive.SQL V8.7, Pervasive PSQL v9, and Pervasive PSQL v9 SP1 against a new Pervasive PSQL v9 Service Pack 2 Server.

Pervasive recommends that you use client requesters that are the same version as the database engine. If you choose, you may use a client requester that is an older version than the database engine with which it interacts. In some situations, depending on the type of SDK access method used by your application, an older version requester will not work with the database engine. Your application will be unable to communicate with the database engine. For those situations, you must use client requesters that are the same version as the database engine.

Client requesters that are a newer version than the database engine may or may not function correctly. Pervasive does not guarantee that newer versions of client requesters will function correctly with older versions of the engine. Therefore, Pervasive recommends that you avoid the use of newer version client requesters with an older engine.

Using Other Data Management Products

This release supports Pervasive AuditMaster v6.3, DataExchange v2.8, Backup Agent v1.1, and Full Text Search v1.0.

Pervasive PSQL v9 Service Pack 2 will not install in an environment where AuditMaster v6.2, DataExchange v2.7, or Backup Agent v1.0 are installed. The steps below explain how to upgrade Pervasive.SQL V8.x to Pervasive PSQL v9 Service Pack 2 in an environment with prior versions of other Pervasive data management products.

Server and Client Installs in Windows

The Pervasive PSQL Server and Client installations are separate in Pervasive PSQL v9 Service Pack 2. You can no longer install clients from a server installation unless you add the client install manually to your installed Pervasive PSQL v9 Service Pack 2 server installation.

Instead, use the separate Client installation program provided on the installation media or on the download Web page.

To add the client install image to a server install

Windows

Step	Action
1.	Modify the Registry and add a key that will allow the upgrade. For AuditMaster, add this key: [HKEY_LOCAL_MACHINE\SOFTWARE\Pervasive Software\Products\AuditMaster Server\v9_upgrade] For DataExchange, add this key: [HKEY_LOCAL_MACHINE\SOFTWARE\Pervasive Software\Products\Pervasive.SQL Replication\v9_upgrade] For Backup Agent, add this key: [HKEY_LOCAL_MACHINE\SOFTWARE\Pervasive Software\Products\Backup Agent\v9_upgrade] Note: Each product requires its own key. For example, if you are using all three products, add all three keys. If you prefer not to edit the Registry manually, contact Pervasive Support and request a Registry update file (.reg file). The Support representative will advise you on using the file. See www.pervasive.com/company/contact/index.asp for contact information.
2.	Upgrade Pervasive.SQL V8.x to Pervasive PSQL v9 Service Pack 2. See Getting Started with Pervasive PSQL (Server Edition).
3.	Update AuditMaster v6.2 to Pervasive AuditMaster v6.3 Update DataExchange v2.7 to DataExchange v2.8 Update Backup Agent v1.0 to Backup Agent v1.1 These updates are available for download from the Pervasive web site at http://www.pervasive.com/support/updates/

You must have Administrator rights to install this product.

If you are installing a downloaded version of Pervasive PSQL, do not place the setup files in a location that is listed in the PATH environment variables, as this can cause issues with file copying during install. Place the setup files in a location such as the Windows TEMP directory.

You cannot install Pervasive PSQL from a terminal server session. You must be on the console of the server to install.

You need Internet Explorer 4.01 SP2 or later to install the product because of a requirement from Windows Installer. You also need Internet Explorer to view the online documentation in CHM format, and use the Network troubleshooting features of Pervasive System Analyzer (PSA). To determine if your Internet Explorer version is adequate, select Help | About Internet Explorer from a browser window and ensure that the version is 4.72.3612.1713 or higher.

A local security policy in Windows XP Professional “Sharing and security model for local accounts” can adversely affect network logins. Under the default policy, all network logins to local accounts are authenticated as Guest, no matter what credentials were supplied. This causes your authorization for databases using Mixed or Database mode not to match your OS credentials and can prevent data access you intended to authorize. To resolve this condition, ensure that your policy for “Sharing and security model for local accounts” is set to Classic, which authenticates users as themselves. Windows 2003 already has a default setting of Classic. Windows XP Professional has a default setting of Guest, and Windows 2000 and earlier releases do not have this security policy. To find this policy in Windows XP, open Administrative Tools from the Control Panel, then select Local Security Policy. Under the node Security Settings, expand Local Policies then Security Options.

Windows Silent Install

The installation for the Java Runtime Environment (JRE) Standard Edition does not run in silent install mode. If you want to install Pervasive PSQL silently and use Pervasive Control Center or Javahelp documentation, you should first run a JRE installation before installing Pervasive PSQL.

To determine when a silent install has finished, you may use either of the following methods:

Check VersionLevel in the following registry key:

HKEY_LOCAL_MACHINE\Software\Pervasive Software\Products\Product name\InstallInfo\VersionLevel

VersionLevel is 9.5 if the silent install has finished.

Product name is Pervasive.SQL NT Server for the Server product, Pervasive.SQL Workgroup for the Workgroup product, and Pervasive.SQL Client for the client requester.

Create a log file during the silent install and then parse the log file. For example, the following command creates a log file named “psqlinstl.log”:

setup.exe /s /v"/qn /L* \"%WINDIR%\psqlinst.log\""

The log file is in Unicode format on Windows 32-bit platforms and in ASCII format on Windows 9x and ME platforms.

Parse the log file for the string "completed successfully." If this string is found, the silent install has finished.

Using the Registration Page on Windows XP SP2

The default security settings on Windows XP SP2 can prevent Internet Explorer from running scripts within HTML. You may notice this if you attempt to use the Pervasive PSQL registration page that appears at the end of installation.

“To help protect your security, Internet Explorer has restricted this file from showing active content that could access your computer. Click here for options...”

Linux

The specific distributions of Linux supported by Pervasive PSQL v9 Service Pack 2 are listed under Environments Supported.

To use Pervasive PSQL Control Center or the JavaHelp documentation, you must install the Java Runtime Environment (JRE) 1.5.0 or later. You must manually install the JRE, which is available for download from http://java.sun.com.

In general, Pervasive PSQL v9 Service Pack 2 should work on Linux deployments with the following characteristics:

NetWare

NetWare 6.0 includes a previous version of Pervasive PSQL. However, NetWare 6.0 configures the version with very minimal resources. These settings in some cases are much lower than the recommended default settings from Pervasive. We highly recommend that you review all of the settings and adjust them based on the expected usage of your applications. In particular, you should reset the MicroKernel cache size to at least 20% of your total physical memory. If you do not change this setting then you can experience NetWare CPU hog timeout errors.

The NetWare install creates shortcuts that link to utilities on the mapped NetWare drive. Running these utilities from the NetWare shortcut can potentially causes a conflict in the pscore component depending on the Pervasive components on the local machine. If you receive such a conflict message using the License Administrator, run the utility locally and connect to the remote engine.

Running the NetWare install from a Windows 2000 (SP4) machine can produce warning messages from Windows such as “Error 1321.The Installer has insufficient privileges to modify the file.” Despite the warnings, Install should complete normally. If it does not, install your Pervasive PSQL Server for NetWare from a machine running an earlier service pack of Windows 2000.

Running the NetWare install from a Windows 2003 machine can produce Windows security warning messages that reference files install is trying to copy. If you receive such dialogs starting with “Some files can harm your computer.”, click Open and Install will complete normally.

New Features and Enhancements

Pervasive PSQL v9 Service Pack 2 includes the following new features and enhancements.


Feature	Description
Data types	Newly supported datatypes include DATETIME and UNIQUEIDENTIFIER. The relational interface supports a data type called DATETIME, which represents a date and time value in the format YYYY-MM-DD HH:MM:SS.mmm. The UNIQUEIDENTIFIER data type is a 16-byte binary value known as a globally unique identifier (GUID). A GUID is useful when a row must be unique among other rows. The relational data type is called UNIQUEIDENTIFIER. The transactional data type is called GUID. Both UNIQUEIDENTIFIER and GUID require a file format of 9.5. They are not supported in other file formats. Note that UNIQUEIDENTIFIER is not supported by the following: the SDK components ActiveX, JCL, OLEDB, PDAC, or Active Connector for AcuCOBOL the products Pervasive DataExchange, Pervasive AuditMaster, Pervasive Backup Agent, or Pervasive Full Text Search. For additional information on both data types, see “Data Types” chapter in SQL Engine Reference.
File format	The default file version for Pervasive PSQL v9 Service Pack 2 is 9.0. The product also provides an additional file format of 9.5, which is required if you want to use the UNIQUEIDENTIFIER data type. See Data types in this table.
File size	The maximum size of a data file is now 256 GB for a 9.5 file format. For additional information, see the section “File Size” in Advanced Operations Guide chapter 1.
Key segments (transactional interface)	The 9.5 file format supports an increased number of index key segments, based on page size: 1,024 byte page size: 97 segments 2,048 byte page size: 97 segments 4,096 byte page size: 204 segments 8,192 byte page size: 420 segments 16,384 byte page size: 420 segments The maximum number of keys remains at 119. Note: The increased number of key segments applies only to files or indexes created through the transactional interface. The maximum number of key segments remains 119 when you create the segments through the relational interface.
Page sizes	The maximum page size has been increased to 16,384 bytes for a version 9.5 file. Only 5 page sizes are supported for a version 9.5 file: 1,024 bytes 2,048 bytes 4,096 bytes 8,192 bytes 16,384 bytes For a version 9.5 file, if you specify a different page size (such as 512; 1,536; 2,560; 3,072; or 3,584), the database engine rounds up the size to the next supported size. Note that for previous file versions, you may still use the page sizes for that file version. For example, if you are working with a 7.x file, you may use a page size of 3,072, which is a supported size for a 7.x file. For additional information, see the section “File Size” in Advanced Operations Guide chapter 1.
Scalar functions	The relational interface now supports the following additional scalar functions: DATEADD, DATEDIFF, DATENAME, and DATEPART. In addition, numerous time and date functions now support the new DATETIME data type. See “Time and Date Functions” in chapter “Scalar Functions” in SQL Engine Reference. NEWID, which allows you to create a unique value for data type UNIQUEIDENTIFIER. See “System Functions” in chapter “Scalar Functions” in SQL Engine Reference.
SQL Statements	The SELECT statement now supports the following: FOR UPDATE functionality to lock the row or rows within the table that is selected by the query. The record locks are released when the next COMMIT or ROLLBACK statement is issued. See “FOR UPDATE” topic under SELECT in SQL Engine Reference. Hint functionality that allows you to specify which index, or indexes, to use for query optimization. A table hint overrides the default query optimizer used by the database engine. See “Table Hint” topic under SELECT in SQL Engine Reference.
Stored procedure performance	By default, the database engine now creates a memory cache in which to store multiple stored procedures for the duration of the SQL session. Once a stored procedure is executed, its compiled version is then retained in the memory cache. Typically, caching results in improved performance for each subsequent call to a cached procedure. Two new SET statements apply to the memory cache: “SET CACHED_PROCEDURES” – specifies the number of procedures to cache. The default is 50. “SET PROCEDURES_CACHE” – specifies the amount of memory for the cache. The default is 5 MB. See “Memory Caching” section under “CREATE PROCEDURE” in SQL Engine Reference.
Temporary tables	The relational interface now supports local and global temporary tables. Both types can be used in a stored procedure or a user-defined function. (Temporary tables cannot be created within a user-defined function.) Data in a temporary table is destroyed at some point during the SQL session or at the end of the SQL session. The data is not saved in the database. A new system database, TEMPDB, is used to store temporary tables. See “CREATE (temporary) TABLE” in the chapter “SQL Syntax Reference” in SQL Engine Reference.
Terminology changes	Starting with Pervasive PSQL v9, the product name changed from Pervasive.SQL to Pervasive PSQL. The product documentation now reflects this change throughout, where appropriate.
Users and Groups	The relational interface now supports the following statements pertaining to user accounts and groups: CREATE USER ALTER USER DROP USER ALTER GROUP See “Grammar Statements” in SQL Engine Reference.
Utilities	Pervasive PSQL Control Center now allows you to execute multiple selected statements in SQL Editor. Hold down the left mouse button and drag the cursor across the statements that you want to select (and to execute). See “To run selected SQL statements in SQL Editor” under “SQL Editor Tasks” in Pervasive PSQL User’s Guide. The configuration utility bcfg supports a new parameter, -E, which ignores errors when reading an input file or writing to an output file. See “Configuration Through CLI Utility” in “Changing Your Configuration” chapter in Advanced Operations Guide. The pvddl utility supports a new parameter, -stopOnFail. This parameter designates that pvddl stop when the first SQL error is encountered in a command file. Pvddl returns an error code of PS_E_FAIL if an error is encountered (which equates to -2147467259 decimal). The default action is for pvddl to continue after an SQL error is encountered. Certain utility interfaces that previously referred to “data compression” or to “compression” now distinguish between “record compression” and “page compression.” “Record compression” is the same as data compression. “Page compression” is reserved for future use. Status code 41, “The MicroKernel does not allow the attempted operation,” may result if you attempt to use the page compression feature.

Setting the File Format to 9.5

You may set the file format to 9.5 with the Pervasive PSQL Control Center (PCC) or by modifying a registry key. This setting affects only files created after the setting is changed. The setting does not change the file format of existing data files. To rebuild existing files into the new file format, refer to the “Converting Data Files” chapter in Advanced Operations Guide.

Using PCC

Modifying a Registry Key

Environments Supported

Server Engine

The Pervasive PSQL v9 Service Pack 2 Server is supported on the following. Each platform includes the latest service packs, if provided, as of 31 March 2006.

Workgroup Engine

The Pervasive PSQL v9 Service Pack 2 Workgroup is supported on the following. Each platform includes the latest service packs, if provided, as of 31 March 2006.

Clients

The Pervasive PSQL v9 Service Pack 2 client is supported on the following. Each platform includes the latest service packs, if provided, as of 31 March 2006.

Known Issues

All known issues for Pervasive PSQL v9 Service Pack 2 are published in the Knowledge Base on the Pervasive web site. In the Knowledge Base, search on “Pervasive PSQL v9 SP2 Known Issues.”

Documentation Notes

This section provides information regarding the documentation included with the Pervasive PSQL v9 Service Pack 2 product.

Documentation Formats

The documentation is provided in three different formats: JavaHelp, HTMLHelp, and portable document format (PDF).

The PDF files reside on the installation media and are not installed.

On Windows platforms, the JavaHelp and HTMLHelp (CHM) formats are installed by default with the product. The JavaHelp is installed under the Bin\Plugins directory (by default, C:\PVSW\Bin\Plugins). The HTMLHelp is installed under Program Files\Common Files\Pervasive Software Shared\Doc.

Note The HTMLHelp (CHM) files are installed as part of Pervasive System Analyzer (PSA). If you perform a silent install and omit PSA, the CHM files are not installed. The installation Registration page contains links to CHM documentation. These links do not work unless PSA is installed.

On Linux, the JavaHelp is a separate installation from the Server, the Client, or the PCC installation. You must manually install the JavaHelp if you want it. By default, the JavaHelp is installed to /usr/local/psql/bin/plugins/. See “Installing PCC and Documentation on Linux” chapter in Getting Started with Pervasive PSQL (Server Edition).

JavaHelp

The Pervasive PSQL Control Center utility is now a Java application. Help requests from the new utility use JavaHelp. The first time that you invoke help from within a PCC session, you may notice a slight delay as the JavaHelp system instantiates. This delay occurs only for the initial instantiation.

JavaHelp and the Java Runtime Environment

The Java Runtime Environment (JRE) is required to use the Javahelp documentation. Ensure that the system variables on your operating system are correctly set to include the location of the JRE.

Windows

Certain platforms may not include the location of the JRE bin directory in the PATH variable. For example, on Windows 2003 Server 64-bit Edition, you may need to add a location to the PATH variable similar to this: c:\program files (x86)\java\jre1.5.0\bin.

Verify the specific location of the JRE on your operating system before adding the location to the PATH variable.

Linux

The JavaHelp documentation is a separate installation for Linux. To run the Javahelp, you must have a variable set for the following:

If a JAVA_HOME environment variable is set, Pervasive PSQL JavaHelp assumes that the Java executable is at $JAVA_HOME/bin/java. JAVA_HOME is not set by the JRE install. Other vendors require a JAVA_HOME variable so it may be set on your system. If it is, then it takes precedence over any PATH statements.

Without a JAVA_HOME, Pervasive JavaHelp assumes that the PATH environment variable contains the location of your JRE’s bin directory. For example, the PATH would include something similar to the following:

Some Linux distributions include gcj, a GNU compiler for the Java programming language. If your Linux distribution includes the gcj compiler, check your PATH environment variable. Ensure that the path to the Standard Edition JRE appears before the path to the gcj or that you have a JAVA_HOME variable set to the Standard Edition.

Accessing JavaHelp

You can start the JavaHelp by navigating the commands for the Pervasive program on the Start menu.

Windows

Linux

Customizing the JavaHelp Viewer

The JavaHelp viewer contains a preferences file by which you can customize the following:

The preferences.ini file is locate in the com.pervasive.help.ui directory. By default, this directory is located at C:\pvsw\bin\plugins on Windows platforms and at /usr/local/psql/bin/plugins for Linux. Comments on how to customize the INI file are included in the file.

JavaHelp and Internet Explorer

Web links from JavaHelp work correctly only if the location of iexplore.exe can be determined. Certain versions of Windows do not include the path to iexplore.exe in the PATH. If you encounter Web links that do not work, ensure that the path to iexplore.exe is in your PATH environment variable.

HTML Help and Internet Explorer

HTML Help uses components of the Internet Explorer (IE) engine to display help files. A defect in the early versions of IE 5 (and some 5.5 versions) causes display irregularities with modular HTML Help systems. These display conditions may be seen in PVSWDOCS.CHM, the master help file for Pervasive PSQL v9 Service Pack 2 that dynamically loads the other help files. Since this defect affects only the file merging mechanism, you will see no display irregularities when loading individual help files. Some specific symptoms you may encounter are:

If you encounter any of these display conditions, the solution is to upgrade to IE 5.01 or higher. You may download version 5.01 or higher from Microsoft’s web site. You can determine your version by clicking Help | About Internet Explorer from the browser window.

If your Internet Explorer version is one of the following, or a later version, then your HTML Help should function correctly.

Windows XP Professional and XP Home includes IE 6.0. Windows 2000 shipped with IE 5.01 so you should not see the display irregularities on those platforms, nor with older IE 4 versions.

Viewing PDFs in a Browser

In some instances, the Acrobat Reader plugin for browsers does not render the PDF documentation correctly. You may notice gaps on pages or pages that appear to be missing. We recommend that you view the PDFs with Adobe Reader, not in a browser. The Reader is free and available as a download from www.adobe.com.

Ptksetup.ini

The Pervasive PSQL installation programs are designed so that you can perform a variety of basic customizations without having to recompile the programs. You can perform these customizations by editing the file ptksetup.ini.

The keys and sections in the ptksetup.ini file are commented to help you understand their purpose. Please refer to the ptksetup.ini installed with Pervasive PSQL v9 Service Pack 2. The file is installed in your PVSW root directory. For example, if you installed to the default location of C:\PVSW, the INI file location would be C:\pvsw\ptksetup.ini.

The following sections of the ptksetup.ini file are obsolete and changes to these sections are ignored.

SDK Documentation

The documentation for the Pervasive PSQL software development kit (SDK) is installed in JavaHelp format when you install the database engine. You can access the SDK documentation by clicking the “SDK Contents” tab in the JavaHelp viewer.

IE Version	Description
5.00.2919.6307	Internet Explorer 5.01
5.00.3105.0106	Internet Explorer 5.01 with Service Pack 1
6.00.2800.1106	Internet Explorer 6.00 with Service Pack 1

You can access the SDK readme file from the “Overview” topic on the SDK Contents tab, or by clicking this link: SDK Readme.

Technical Support

If you require something other than what Web-based support options provide, contact Pervasive Support by phone:

For community technical support and discussions about Pervasive products in general:

Useful Links

Disclaimer

PERVASIVE SOFTWARE INC. LICENSES THE SOFTWARE AND 
DOCUMENTATION PRODUCT TO YOU OR YOUR COMPANY SOLELY 
ON AN "AS IS" BASIS AND SOLELY IN ACCORDANCE WITH 
THE TERMS AND CONDITIONS OF THE ACCOMPANYING 
LICENSE AGREEMENT.  
PERVASIVE SOFTWARE INC. MAKES NO OTHER WARRANTIES 
WHATSOEVER, EITHER EXPRESS OR IMPLIED, REGARDING 
THE SOFTWARE OR THE CONTENT OF THE DOCUMENTATION; 
PERVASIVE SOFTWARE INC. HEREBY EXPRESSLY STATES AND 
YOU OR YOUR COMPANY ACKNOWLEDGES THAT PERVASIVE 
SOFTWARE INC. DOES NOT MAKE ANY WARRANTIES, 
INCLUDING, FOR EXAMPLE, WITH RESPECT TO 
MERCHANTABILITY, TITLE, OR FITNESS FOR ANY 
PARTICULAR PURPOSE OR ARISING FROM COURSE OF 
DEALING OR USAGE OF TRADE, AMONG OTHERS.  
THIS APPLICATION USES THE FREE unixODBC DRIVER 
MANAGER AS WRITTEN BY PETER HARVEY 
(pharvey@codebydesign.com), MODIFIED AND EXTENDED 
BY NICK GORHAM (nick@easysoft.com), WITH LOCAL 
MODIFICATIONS FROM PERVASIVE SOFTWARE. PERVASIVE 
SOFTWARE WILL DONATE THEIR CODE CHANGES TO THE 
CURRENT MAINTAINER OF THE unixODBC DRIVER MANAGER 
PROJECT, IN ACCORDANCE WITH THE LGPL LICENSE 
AGREEMENT OF THIS PROJECT. THE unixODBC DRIVER 
MANAGER HOME PAGE IS LOCATED AT WWW.UNIXODBC.ORG. 
FOR FURTHER INFORMATION ON THIS PROJECT, CONTACT 
ITS CURRENT MAINTAINER: Nick Gorham 
(nick@easysoft.com)." 
Copyright © 2006 Pervasive Software Inc.  
All Rights Reserved.

Pervasive PSQL v9 Service Pack 2 - README
General Release - May 2006

Contents

General Information

Installation Notes

Compatibility with Other Releases

Using Other Data Management Products

Server and Client Installs in Windows

To add the client install image to a server install

Windows

Windows Silent Install

Using the Registration Page on Windows XP SP2

Linux

NetWare

New Features and Enhancements

Setting the File Format to 9.5

Using PCC

Modifying a Registry Key

Environments Supported

Server Engine

Workgroup Engine

Clients

Known Issues

Documentation Notes

Documentation Formats

JavaHelp

JavaHelp and the Java Runtime Environment

Windows

Linux

Accessing JavaHelp

Windows

Linux

Customizing the JavaHelp Viewer

JavaHelp and Internet Explorer

HTML Help and Internet Explorer

Viewing PDFs in a Browser

Ptksetup.ini

SDK Documentation

Technical Support

Useful Links

Disclaimer

Pervasive PSQL v9 Service Pack 2 - README General Release - May 2006

Contents

General Information

Installation Notes

Compatibility with Other Releases

Using Other Data Management Products

Server and Client Installs in Windows

To add the client install image to a server install

Windows

Windows Silent Install

Using the Registration Page on Windows XP SP2

Linux

NetWare

New Features and Enhancements

Setting the File Format to 9.5

Using PCC

Modifying a Registry Key

Environments Supported

Server Engine

Workgroup Engine

Clients

Known Issues

Documentation Notes

Documentation Formats

JavaHelp

JavaHelp and the Java Runtime Environment

Windows

Linux

Accessing JavaHelp

Windows

Linux

Customizing the JavaHelp Viewer

JavaHelp and Internet Explorer

HTML Help and Internet Explorer

Viewing PDFs in a Browser

Ptksetup.ini

SDK Documentation

Technical Support

Useful Links

Disclaimer

Pervasive PSQL v9 Service Pack 2 - README
General Release - May 2006