Home

User's Guide

1. Modifying Data and Adding Rows The Grid allows you to directly change database data by changing the values in the grid cells You can also add rows to your table with the Grid You must have the proper table permissions to affect the database data See To set preferences for PCC Window Views Grid Tasks and Assigning Permissions Tasks Records Affected and Scrolling TheGrid caches results locally and initially retrieves 100 records The Grid displays as many records as its vertical size permits As you scroll the vertical scroll bar more records are retrieved and made availableto the Grid The number of records retrieved appears in the lower right corner of the main window 27 Fetched 1 315 Rows Once you scroll to the bottom the rows fetched indicator reports the total number of records returned by the SQL statement Text Window View PCC provides a command to execute an individual SQL statement in SQL Editor so that the results appear in the Text view The command is called Executein Text and can beinvoked from the SQL menu or from atoolbar button The Text window view is automatically used for the results of any SQL statement that is not a SELECT statement For example suppose you want to delete some records and havetyped aDELETE statement into SQL Editor If you select the menu command Executein Grid SQL Editor returns the results to the Text window view not to the Grid window view A command also
2. Date fields The only supported format is yyyy mm dd Time fields The only supported format is HH MM SS Timestamp fields The only supported format is yyyy mm dd HH MM SS MS White space and column delimiters No white space must exist between the column delimiters and the data values even if the data values are enclosed in quotes 1 BDU is not aware of default values for a column defined during table creation or update Best Practices If possible run BDU when the database load is minimal or when no concurrent sessions exist on the table being loaded If the table being loaded contains any indexes drop the indexes before using BDU Re create the indexes after the load is complete If thetable being loaded contains any columns with check constraints drop thecheck constraints before using BDU Re specify the constraints after the load is complete Sample Source File The following content may be used to create a sample delimited text file You may use the file to verify the usage examples The examples refer to the sample file as data_file txt Note that because the following content is comma delimited you must specify the t parameter t with BDU The t parameter is required for any delimiter except the TAB character psqlBDUsample 1 12345 psql 101 18446744073709551615 psqlBDUsample 2 12346 psql 102 18446744073709551614 psqlBDUsample 3 12347 psql 103 18446744073709551613 psqlBDUsample 4
3. 3 Next the File New Database dialog box opens and asks you to name the new database Name the database and click Create 37 Using PSQL 4 From the Access menu choose File gt Get External Data gt Link Tables Note You have the option to Import data or Link Tables to the new database When you choose Import you break the link to the ODBC data source immediately following the import procedure Essentially Import creates a static copy of thedata When you choose Link Tables M icrosoft Access keeps the connection open and remains dependent upon the ODBC data source each time the data is accessed This way the data you see reflects any changes to the data at its source Note If you wish to link to afile on a local area network make sure to use a universal naming convention UNC path instead of relying on the drive letter of a mapped network drive in Windows Explorer A drive letter can vary on acomputer or may not always be defined whereas a UNC path isareliableand consistent way for M icrosoft Access to locate the data source that contains the linked table Figure14 Importing External Data Using Access El Microsoft Access Eile Edit View Insert Tools Window Help yes 0 Be Bh to ste O new Ctrl gt Open Ctro Get External Data gt x Rey bed Save Gtrl 5 Sa Brrr Create table in Design view PI Create table by using wizard PI Create table by entering data
4. Caution Indexes must have at least one Column designated If you do not select a Column for the Index the first column remains selected Thedefault sort order is Ascending If you need a descending sort order select Descendingfrom the Sort Order list for the Column you want changed Note Partial Indexes are restricted to columns with a data typeof CH AR or VARCHAR and that are designated as the only or last segment in an Index 6 Continueselecting columns from the list until all the segments are added Note that a column can be selected for an index only once Once selected the column is removed from thelist of choices because the column has already been designated in the index 7 Click File gt Save or El before changing pages within Table Editor See also Creating Indexesin PSQL Programmer s Guide which is part of the PSQL Software Developer s Kit SDK gt To modify an existing index Thedatabase engine creates some indexes such asI DENTITY column indexes and primary key indexes These indexes are read only and cannot be modified Table Editor permits you to include an IDENTITY or aSMALLIDENTITY column in the Indexes list if you have not saved the table However the DBM S returns an error when you attempt to save the table After you deletethel DENTITY or SMALLIDENTITY column from thelist you may then save the table 1 Ensure that the Indexes page of Table Editor is active If required perform the steps
5. DEFAULTDB DEMODATA Master Tables E Billing E Class Grid amp E Course Dept E Nme Prone Nuber Buin Name E Faculty Accounting 5126941000 Bhargava Building E Person Anthropology 5126941100 Billingsley Building Bann Art 5126941200 Budinszky Building 4 Refinethequery to restrict the results to only departments that start with the letter M by altering the query at the top half of the screen with the following statement SELECT FROM Dept WHERE Name LIKE M 5 Click the Executein Grid toolbar button or press F9 to display the results of the revised query shown in Figure 10 Figurel0 Refining Your Query Dept Tablein DEM ODATA select from Dept WHERE Name LIKE M Grid ES Text Dept Name Phone Number Building Name Mathematics 5126942500 Gambill Building Music 5126942600 Garrison Hall You have now obtained data from the remote database engine For information on advanced operating and maintenance tasks including database operations see the Advanced O perations Guide 35 Using PSQL Accessing Data via ODBC From Other Applications This section explains how to access data using Microsoft Access and M icrosoft Excel The examples covered in this section are Accessing Data Using Microsoft Excel a Accessing Data Using Microsoft Access Before You Begin Does the Database
6. Why do I get a log in failed message when I have a Pervasive_Admin group defined or have administrator rights Thesettings for thePSQL services can affect whether or not you have permission to login to themachine where the database engine is running The settings apply whether or not you use a Pervasive Admin group If you changethe Logon as setting for aPSQL serviceto This account you must change the user rights policy Act as part of the operating system for the account Otherwise remote log in fails For example the Monitoring utility requires that you log in to the operating system on the machine where the database engine is running You will receive a message that login failed if the account specified for This account cannot act as part of the operating system Note that even the Administrator account requires that you set the user rights policy for Act as part of the operating system See Services Settings and Log In Authority for a complete discussion and the steps to change the user rights policy User Counts frequently asked questions about user counts How do authorize a User Count Upgrade Refer to the tasks discussed in the chapter License Administration How does a PSQL engine using the concurrent user license model keep track of how many people are accessing the data If people access the data with two engines at the same time what happens With the concurrent user license model each license specifies a user
7. bdu demodata BDU Table C data_file txt f 1 1 15 t 190 Command Line Interface Utility Reference bmon The bmon utility is documented in Advanced O perations Guide See Command Line Interface M onitor 191 Command Line Interface U tilities btadmin Description The btadmin utility is used to create and update the flat file btpasswd which stores user names and passwords for authentication of PSQ L users Users given administrator rights can monitor enginestatus and configure the engine remotely Synopsis btadmin p password a a r username Options p Specify the password If this option is not specified you will be prompted to enter the password at Gives administrator rights for this user a Removes administrator rights for this user r Remove user name from btpasswd file username Creates or updates the username in the btpasswa file If usrname does not exist in this file an entry is added If it does exist the password is changed See Also butil 1 Notes To administer the engine from a remote workstation you must supply a user name and password Upon initial installation of PSQL v12 the supplied default is admin with an empty password Use btadmin to add more administrators btadmin p password a username 192 Command Line Interface Utility Reference This utility creates a record in btpasswa for user username With password password if option p is not used then you
8. gt To stop the database services on a Windows server using PCC See To start or stop services gt To start the database services on a Windows server using Control Panel 1 IntheWindows Control Panel double click Administrative Tools then double click Services A dialog box similar to Figure 1 appears Figurel Services Dialog Box ox Action View I e ala ABS gt Tree Ra Pervasive PSQL Relational Engine Automatic Services Local Ra Pervasive PSQL Transactional Engine Automatic Bs Plug and Play Manages d Started Automatic a Print Spooler Loads files Started Automatic Sy Protected Storage Provides pr Started Automatic Sa QoS RSVP Provides n Manual Sy Remote Access Auto Connection Ma Creates a Manual 4 Remote Access Connection Manager Creates a Manual Sa Remote Procedure Call RPC Provides th Started Automatic X 2 Right click PSQL Transactional Engine then click Start Right click PSQL Relational Engine then click Start gt To stop the database services on a Windows server using Control Panel 1 IntheWindows Control Panel double click Administrative Tools then double click Services A dialog box similar to Figure 1 appears Figure2 Services Dialog Box lolx e View I e aa Ele gt mm Tree Services Local Bs Pervasive PSQL Relational Engine Started Automatic Bs Pervasive PSQL Trans
9. Tip You can also insert a column by clicking an existing column row then pressing Ctrl Insert or clicking 5 Repeating either action inserts a series of columns in succession 4 Click File gt Save or El before changing pages within Table Editor gt To insert a column at the end 1 Ensure that the Columns page of Table Editor is active If required perform the steps for To work with columns 2 Right click anywhere on the empty column row below the last existing column row then click Add s Column or Click the Column Name cell on the empty column row below the last existing column row and start typing a name for the column The default name of the inserted column is columnn where n isa number that automatically increments by one The first column you insert is column0 the second column and so forth Tip You can also insert a column at the end by clicking or pressing Ctrl Insert Repeating either action inserts a series of columns in succession The insert action automatically adds the new column to the end 3 Click File gt Save or El before changing pages within Table Editor gt To select a column or multiple columns 1 Ensure that the Columns page of Table Editor is active If required perform the steps for To work with columns 2 Click the column selection icon on the far left of the column row for the desired column To select multiple columns pres
10. b Go to the restore directory for DEM ODATA An SDF file exists for each table in DEM ODATA c For each SDF file use BDU to load the data into the table For example to load the data for the Billing table usethe following command Thecommais the field delimiter in the file bdu demodata billing billing sdf t Note The t parameter is required The BDU utility returns the number of rows of data that were loaded Miscellaneous dumped Btrieve records to afileand now can t read thefile What happened Howdol run PSQL in trace mode Howdol run PSQL in trace mode Does garbage collection occur in the data files and indexes For example is space from deleted records recovered or reused Isdatabaseshadowingavailable allowing a complete up to date second copy of the databaseto exist on another drive or machine 230 Frequently Asked Questions a What is the mechanism that allows the database to be backed up online What happens if the server goes down in the middle of a backup with many open transactions Installation Frequently asked questions about installation Will I lose my data files if uninstall my existing version of the product or install a new version When you uninstall PSQL or install a new version of PSQL your data files and DDFs are never affected Even when PSQL System Analyzer archives PSQL files or even if you have your data files in the same directory as PSQL files
11. Repeat steps 7 and 8 to match each field listed in the Primary Table Fields column with afield in the Foreign Table Fields column Click the desired referential integrity rule Delete Restrict or Delete Cascade PSQL allows a circular delete cascade on a table that references itself Because of this use delete cascade with caution See Delete Restrict and Delete Cascade both in Advanced Operations Guide Click File gt Save or El before changing pages within Table Editor To modify a foreign key Ensure that the Foreign Keys page of Table Editor is active If required perform the steps for To work with foreign keys Click on the desired foreign key in the Foreign Keys list The Foreign Keys Details displays 146 Table Editor Tasks 4 Asdesired select the primary table match foreign table fields to primary table fields and set the referential integrity rule See steps 5 through 10 in To add a foreign key 5 Click File gt Save or El before changing pages within Table Editor gt To delete a foreign key 1 Ensurethat the Foreign Keys page of Table Editor is active If required perform the steps for To work with foreign keys 2 Click on the desired foreign key in the Foreign Keys list 3 Click Delete 4 Click Yes to confirm the deletion 5 Click File gt Save or E before changing pages within Table Editor SQL View Tasks gt To copy SQL statements 1 Ensurethat the SQL View page of
12. To Display Remaining Authorizations To Authorize a Key a To Deauthorize a Key To Remove Keys Without D eauthorizing To Repair a Key To Display Help Alternative Authorization Tasks a To Authorize a Key Remotely To Deauthorize a Key Remotely a To Authorize a Key Offline To Deauthorize a Key Offline a To Authorize a Key Using the PSQL Phone Authorization Wizard a To Authorize a Key Using the Command line Phone Authorization Utility 110 License Administration T asks Notification Viewer To View Licensing M essages In Logging Repositories GUI Tasks gt To Start License Administrator from PSQL Control Center PCC 1 In PCC click Tools then License Administrator License Administrator accesses the selected server to determine the type of license required If you are not logged in to the server a dialog appears to log in to the operating system not the database engine Log in with a user name that has administrative privileges on the operating system where the database engine is running Once logged in the server name appears in the License Administrator title bar and in the Server Name field Note For servers running a previous version of PSQL use the license utility provided with that version of the product If the server is not the one you want select a different server as described in To Select a Server for License Administration To Start License Administrator as a Stand alone Applica
13. 4 Start PCC if it is not already running See Starting PCC on Windows Expand the Engines and Databases nodes in PSQL Explorer Right click on the database to which you want to add the new table Click New gt Table and type thename for new table Tip For a list of database object lengths and invalid characters see Identifier Restrictions by Identifier Type in Advanced Operations Guide Note that in thesame directory no two files should sharethe samefilenameand differ only in their file name extension For example do not create a table data file Invoice btr and another one Invoice mkd in the same directory This restriction applies because the database engine uses the file name for various areas of functionality while ignoring the file name extension Since only the file name is used to differentiate files files that differ only in their file name extension look identical to the database engine Click Finish To start Table Editor for an existing table Start PCC if it is not already running See Starting PCC on Windows Expand the Engines and Databases nodes in PSQL Explorer Right click on the table that you want to modify then click Edit To work with columns Perform the steps for To start Table Editor for an existing table or To start Table Editor for anew table Click the Columns page tab To work with indexes Perform the steps for To start Table Editor for an existing table or To start Table Editor for anew
14. 5 Inthe Link dialog box in the Files Of Type box select ODBC Databases 6 TheSelect Data Source box lists the defined data sources for any ODBC drivers that areinstalled on your computer Click the Machine D ata Source tab as shown in the next figure 38 Accessing Data via ODBC From Other Applications Figure15 Access Display of ODBC Source List Select Data Source X 1 2 x File Data Source Machine Data Source Data Source Name Type dBASE Files User dBase Files Word User DeluxeCD User demodata System Pervasive ODBC Engine Interface Excel Files User FoxPro Files Word User MS Access Database User NewData System Pervasive ODBC Engine Interface 7 on c M Description rchmardsta aba m Damiaan ANDO Encino Intake New A Machine Data Source is specific to this machine and cannot be shared User data sources are specific to a user on this machine System data sources can be used by all users on this machine or by a system wide service 7 Select the ODBC data source that you want to link If the ODBC data source that you selected requires you to log on enter your user name and password additional information might also be required and then click OK Note To define a new data source for any installed ODBC driver click New and then follow the instructions in the Create New Data Source dialog box and the dialog boxes that follow it before proceeding Tip If you are l
15. Can t retrieve database names You don t have access rights for the operation This error may occur when you are attempting to create a new database on the server The most likely cause is that you are logged in as an operating system user that has neither administrative rights in the server operating system nor membership in the Pervasive Admin group on the server Another likely cause is that you forgot to enter a user name and password Solution Be sureto enter a user name and password for the remote operating system You must have administrative rights on the server or be a member of the Pervasive Admin group in order to create a new database on the server Granting Administrative Rights for the Database Engine explains how to set up the Pervasive Admin group For Windows 32 bit platforms be sure that you are set up as a local user on the system not a network user Network users have a domain name and a back slash preceding the user name such as BOSTON GILBERT Besure that the user who is a member of the Administrators group or Pervasive Admin group is a local user If you have checked permissions and your user login does in fact meet one of the criteria above then you should also check to make surethat you are logged into the correct network You can verify whether you are logged into the correct network by attempting to read or writeto a server that you are certain uses the target operating system Unable to connect to the specified
16. Click the expand icon to the left of an object to see objects under it The expand icon may bea plus sign a black right pointing triangle or some other similar symbol The collapse icon appears after you click an expand icon Click the collapse icon to hide subordinate objects If properties configurable settings apply to an object you can access them by right clicking an object and selecting Properties You can also click an object then press Alt Enter to display properties See also Configuration Reference in Advanced O perations Guide Figure19 Example Objects Shown in PSQL Explorer Ta Pervasive PSQL 5 a PSQL A Local Client of Services a E Engines 4 E vm win7 ent 32 anonymous 4 A Databases DEFAULTDB a DEMODATA 4 3 Tables Billing Class Course Dept Enrolls Faculty Person Room Student Tuition Ez Views Ez Stored Procedures Ez User Defined Functions Ez Triggers Ez Users Ez Groups Ez System Objects B TEMPDB HRS 48 Right click Tasks An Overview of PSQL Control Center In addition to accessing properties numerous other tasks can be invoked from PSQL Explorer by right clicking an object The following table summarizes the tasks Table8 Right click Tasks for Objects in PSQL Explorer Working with Task Right click For information see All Objects View or set object Any obje
17. Indexes page of Table Editor is active If required perform the steps for To work with indexes 2 Click the desired index in the Indexes list The Index Segment Details displays 3 Click the desired segment in the Columns cell then open thelist of columns click Index Editor Indexes Index Segment Details ES index_o1 Ounique O Partial Normal Y Allow Modifications Columns SortOrder Delete Delete Student_ID Y Ascending Student ID Transaction_Number 3 Log 3 Amount_Owed Amount_Paid 4 From theColumnslist click the desired column designated as the index segment Note Some data types such as LON GVARBINARY cannot be used for an index Columns with such data types are not valid choices Thedefault sort order is Ascending If you need a descending sort order select Descending from the Sort Order list for the Column you want changed 5 Continue selecting columns from thelist until you have completed all segment modifications 142 Table Editor Tasks Note that a column can be selected for an index only once Once selected the column is removed from thelist of choices because the column has already been designated in the index 6 Click File gt Saveor El before changing pages within Table Editor gt To delete an index segment Note Each index requires a minimum of one segment To delete an index that has only onesegment delete the index itself 1 Ensure that the
18. Transaction Tracking System and TTS are trademarks of N ovell Inc Oracle Java all trademarks and logos that contain Oracle or Java are trademarks or registered trademarks of Oracle Corporation All other company and product names are the trademarks or registered trademarks of their respective companies Copyright 2014 Actian Corporation All rights reserved Reproduction photocopying or transmittal of this publication or portions of this publication is prohibited without the express prior written consent of the publisher This product includes software developed by Powerdog Industries Copyright 1994 Powerdog Industries All rights reserved This product includes software developed by KeyWorks Software Copyright 2002 KeyWorks Software All rights reserved This product includes software developed by DUN DAS SOFTWARE Copyright 1997 2000 DU N DAS SOFTWARE LTD all rights reserved This product includes software developed by the Apache Software Foundation http www apache org This product uses the free unixOD BC Driver M anager as written by Peter Harvey pharvey codebydesign com modified and extended by Nick Gorham nick easysoft com with local modifications from Actian Corporation Actian Corporation will donate their code changes to the current maintainer of the unixODBC Driver M anager project in accordance with the LGPL license agreement of this project The unixODBC Driver Danager home pageis located at www unixod
19. file is inaccessible when everybody else can get to it What s wrong Use PSQL System Analyzer to be sure that all components from previous versions of Btrieve or PSQL have been archived Then make sure your configuration settings are correct Find the file pvsw log and check for error messages indicating a status code 8505 or 8517 These status codes indicate that attempts 233 Basic Troubleshooting were made to use a local Workgroup engine to read the data files N ote that hexadecimal values in message strings written to pvsw log are preceded by Ox to distinguish them from decimal values Start PCC see Starting PCC on Windowsin PSQL User s Guide Right click on Local Client then click Properties Click Access Ensure that the option UseLocal MicroKernel Engine is not check marked and that Use Remote MicroKernel Engineis check marked have files sitting on the server that are shared and yet PSQL cannot read them What s wrong How arethefiles shared PSQL doesnot support mappinga drive letter using Redirected mapping under Microsoft or using the hidden Admin share C under Windows 32 bit platforms M ake sure that users have appropriate operating system login credentials to access the file server Run PSQL System Analyzer and choose the Network Communications Test to be sure that you have proper connectivity l am using SQL queries to create a definition for an old table The resulting record size is off Why Star
20. right click on the database that you want to delete Click Delete Click Yes to confirm the deletion If the setting Always removeassociated DSN entries is turned off the Confirm DSN Removal dialog appears The following DSN entries reference the DBNAME being removed Select those entries ed I Always remove associated DSN entries If you want to removea DSN associated with the database ensure that the DSN is check marked in the list Note that multiple DSN s can be associated with one database In such cases you may remove the DSN s that you want and retain the ones that you want 69 Using PSQL Control Center 5 Optionally select the option Always remove associated DSN entries if you want PCC to set DSN removal as the default action y Note If you select the Always remove associated DSN entries option PCC no longer prompts you for DSN removal when you delete a database PCC will automatically remove all DSN s associated with the database being deleted If you decide later to turn off the option in PCC click Window expand the PSQL node if it is not already expanded then click General gt To repair a database name Using the tables data files from one database for a newly created database with a different name can result in the inability to open the tables for the new database In certain situations the tables can have the original database name bound to them For example if the original databa
21. you may also need to add proxy_userid and proxy_passwd 2 Authorize PSQL Vx Server using the command line interface License Administrator utility See To Authorize a Key Considerations For No Internet Access If the machine on which you are installing PSQL does not have Internet access or is not connected to a machine with Internet access you may authorize the product key using offline authorization Offline authorization consists of three phases In the first phase an Authorization Request Data file ath is generated on the machine without internet connectivity During the second phase the file is then uploaded to an authorization server from a machine with internet access Upon completion of this transmission an Authorization Key Data file is downloaded in the form of a product key pky file In the final phase the product key fileis authorized on the machine without Internet connectivity and PSQL is authorized The quickest and simplest method for authorizing PSQL is using online or remote authorization Whenever possible try to authorize your PSQL product using one of these methods The PSQL installation for example allows you to authorize online as part of the installation process Key Status or State Thestatus of a product key is displayed in the License Administrator as the state Key states includethe following Active key is authorized and available for use m Inactive key isno longer in use This state i
22. 12348 psql 104 18446744073709551612 psqlBDUsample 5 12349 psql 105 18446744073709551611 188 Command Line Interface Utility Reference psqlBDUsample 6 12350 psql 106 18446744073709551610 psqlBDUsample 7 12351 psql 107 18446744073709551609 psqlBDUsample 8 12352 psql 108 18446744073709551608 psqlBDUsample 9 12353 psql 109 18446744073709551607 psqlBDUsamplel10 12354 psql 110 18446744073709551606 Note that in any data file being used as source input no white space must exist between the column delimiters and the data values even if the data values are enclosed in quotes Examples The following examples assume that a table named BDU_Tableis part of the Demodata sample database To add such a table to Demodata use the following query CREATE TABLE BDU Table Name CHAR 20 NOT NULL CASE PhoneNo INTEGER BuildingName CHAR 25 NOT NULL CASE RoomNo UINT NOT NULL HeadOfDept UBIGINT NOT NULL To run BDU with the default options bdu demodata BDU Table C data_file txt y Note The input data must be TAB delimited to use default options If the input data is not TAB delimited you must specify the delimiter with the t parameter For example to usethe data from Examples which is comma delimited you would run BDU as follows bdu demodata BDU Table C data_file txt t To run BDU for the Billing table in the sample database DEM ODATA and load TAB delimited data from afilethat use
23. 214 PVOD PASS indie wane Ae a Sed ee Pe ea an ee Oe Ss 215 DESCHIDUOM E a tcc ccna tee e cee ra Aes a See G08 TA hh canoe Maes Ry a yess 215 SYNOPSIS O NR 215 OPONE iio a ata iaa io ba 215 Examples datos a Soh a Oe Pe ae A A o E 215 O AR Atheros 216 Description comia o work o anaele tod a Sat 216 SYNOPSIS a taiu id lidia tad 216 OPUS ass Lat A A a cap a io Aten 216 SOO AISO ci sees iia de Se Gunes anda E oi E ia ee cod eae 216 A NE 217 Descrip a E a a a a la ob as 217 SYNOPSIS mica a A A a de 217 A NS ra td et 217 SOG AISO f Scat a Saget Bae aia sas 217 Examples ican sao aii kath we edi Ba od Wat a EE ls 218 RDG dr ao REN a ed Se on Ed Sedan Pade ee aie 219 DiCSCHIPLIOMN Sarit A A 219 SYNOPSIS a aR Uae ae Ht i Carte Ou aha eek 219 ODUONS a rara 2 a 2 A ot MRE Rh aE Bak eS eee aks 219 SAO o Lesa Ped a Mae A eed kee al Pa eye als Goals 219 9 Basic Troubleshooting 4 221 How to Identify and Solve Common Problems General Troubleshooting 0 oo 222 Error MessagesfroMPCC o ooo 226 Frequently Asked Questions o o o o o oo oooo ee 228 Installation di ss os hoa ig a ach ga a 231 PC Gin tartan A O Soe wana a oad ap ee Sp ke 231 SECUN sits autocad ap Barco pach Wy were eh nt a e ot uth ah anh Ma 232 User COUN stich acho atid ed hohe a tots the A od ae ite es hal ts Mee alts Acide bcp td 232 N CLWOPKING sa were eee dda ate a Mest ce nee Bs 233 Difficulty Acc
24. 32 bit and 64 bit applications Refer to the ODBC documentation on the UNIXODBC Web site for a discussion of 64 bit ODBC For example you may find the following information useful http www unixodbc org doc ODBC64 html See Also btadmin dbmaint sql 205 Command Line Interface U tilities isql Description isql is an interactive ODBC test utility that you can use to test your DSNs for their connectivity to databases and to execute SQL statements once you are connected to a database The 32 bit utility is named isql and is installed with PSQL Server 32 bit PSQL Client 32 bit and PSQL 64 bit The 64 bit utility is named isql64 and is installed with PSQL Server 64 bit and with PSQL Client 64 bit Theonly difference between the two utilities is thetype of DSN to which they can connect By default dsnadd creates DSN s that are accessible to both 32 bit and 64 bit applications the DSN s specify the driver description Pervasive O DBC Interface You can test connectivity of such DSNs with sql or isql64 See PSQL ODBC Driver Descriptions for Linux Deprecated legacy style D SN s are accessible only to 32 bit applications Therefore you can test connectivity of such DSN s only with isql See FAQs About ODBC and DSN Support for Linux For example to connect to the DEM ODATA sample database included with PSQL run sql or isql64 with theDSN asthe first parameter isql DEMODATA OF isq164 DEMODATA TheDSN for DEM ODATA speci
25. Department Enrolls Macros Faculty Modules Ea Favorites Person Room 8 8 8 8 808000000 Note If you are linking a table and it does not have an index that uniquely identifies each record then Microsoft Access displays a list of the fields in the linked table Click a field or a combination of fields that will uniquely identify each record and then click OK 40 DeletingDSNs Deleting DSNs The procedures in this section do not delete Data Dictionary Files DDFs or data files By default when you delete a database in the PSQL Control Center the associated DSN entries are removed simultaneously gt To toggle automatic removal of DSN entries when deleting a database in PCC 1 2 3 On the PCC Window menu click Preferences Expand the PSQL nodeif it is not already expanded Click General Clear the option Always remove associated DSN entries and click OK When you delete a database in PCC you will be prompted by the Confirm DSN Removal dialog before deleting it Fa Confirm DSN Removal The following DSN entries reference the DBNAME being removed Select those entries you would like removed DEMODATA Always remove associated DSN entries You can clear those DSNs that you do not want to delete You can turn on automatic removal of DSN entries at anytime by selecting Always remove associated DSN entries in Confirm DSN Removal dialog General screen of the Preferences dialog To Del
26. Failover environment or Can install PSQL in a Clustering environment Canl install PSQL in a Load Balancing environment Canl install PSQL on a server running Btrieve v6 x or earlier How do I keep my Workgroup Engine from starting up automatically when reboot PCC Howdol start PCC on Linux Security When dol login using an operating system user and password and when do login using a database user and password a Why dol get a login failed message when have a Pervasive Admin group defined or have administrator rights User Count Session Count Data In Use Howdo authorizeaUser Count Upgrade How does a PSQL engine using the concurrent user license model keep track of how many people are accessing the data If people access the data with two engines at the same time what happens Does the Workgroup engine use concurrent or per seat licensing am thinking of moving from a PSQL engine with concurrent user licensing to one with capacity based licensing How do determine the appropriate size for my data use license Networking a How do know which protocol am using for communication can see other systems in Network Neighborhood but can t get to my data Difficulty Accessing Data upgraded from Btrieve v6 x or earlier to PSQL v12 Now get error messages telling me that a file is inaccessible when everybody else can get to it What s wrong havefiles sittin
27. GUI Although not strictly a CLI utility it is mentioned here for reference See Notification Viewer in Advanced Operations Guide psc Manipulates PSQL services No Yes Yes No No psregedit Configures PSQL components by No No No Yes Yes editing the PSQL registry psregsvr Register PSQL components No No No Yes Yes 181 Command Line Interface U tilities Utility Description GUI Available Windows Linux Server Client Server Client pvdbpass Specifies user names and passwords PSQL Control Center see Yes Yes Yes Yes for secure databases PSQL Security in Advanced Operations Guide pvddl Processes SQL statements in a PSQL Control Center see Yes Yes Yes Yes command file SQL Editor pvnetpass Specifies user names and passwords No Yes Yes Yes Yes for remote servers rbldcli Rebuilds MicroKernel data files Yes Yes Yes Yes Yes 182 Command Line Interface Utility Reference Command Line Interface Utility Reference This section provides a reference for the following command line interface utilities bcfg a bdu bmon btadmin a butil m clilcadm w64clilcadm clilcadm64 Clipaadm w64clipaadm clipaadm64 a dbmaint dsnadd isql64 a licgetauth w64licgetauth licgetauth64 psc psregsvr pvdbpass pvddl a pvnetpass m rbldcli 183 Command Line Interface U tilities bcfg The c g utility is documented in Advanced O perat
28. Interactions Between Btrieve and Relational Constraints back to Figure 20 Long The database engine supports two versions of metadata referred to as version 1 SQL Grammar Support in Metadata V2 V1 and version 2 V2 V2 metadata is also referred to as long metadata ODBC Guide metadata Metadata version is a property of the database and applies to all tables within that System Tables in SQL database A database cannot use some tables with V1 metadata and others with Engine Reference V2 metadata The DDFs of different metadata versions cannot interact back to Figure 20 Database Specifies the code page property for the database The default is server default Database Code Page Code Page meaning the default operating system code page on the server See Related Material column for the section where this property is discussed fully back to Figure 20 Create 32 bit Engine DSN For ODBC access you must set up a data source name DSN to refer to the database name By default the name of the new DSN is the same as the Database Name Multiple DSNs may point to the same named database The default is to create the DSN with an encoding translation option of None Note The DSN that you are creating is a 32 bit Engine DSN PCC cannot create 64 bit DSNs Use an ODBC Interface utility to create 64 bit DSNs such as ODBC Administrator on Windows Note that DSN names must be unique for their b
29. Mak as ieee eee Tae ee Reto ee a we ad 174 View Modules TASKS cio aR eee ERR ee A NE a a ee 174 Test Activelnstallation Tasks es 175 LOG Files TASKS ia o waht ed ob eves the fo re Aiea SR Marts ha A Lk SAS 177 8 Command Line Interface Utilities lt lt 179 UsingPSQL Command Line Interface Utilities CLI Utilities Overview aoaaa 180 Platformsthat Include CLI Utilities a 180 Whereto Find CLUtHitieS ici a e es eee G e a ee 180 Utilities by Platform and EngineType 1 es 180 Command Line Interface Utility Reference aaau a 183 DCI E te eh teehee hs Neg We EAN tial did A ORO 184 Dit RY A A ih P a antes a a A a NA 185 Descriptio Mags eraa ar E aa ee E i Sit Rada Ge ON E E UE S ea GE Bee EOG 185 SYNOPSIS sho a aa tona aona Maan dave bat DAA a an h h a An a a d tee aed 185 Parameters mi feo So as nde ce it a eria di tl da o cd hed 186 NOS are Ae E AN A A e R gE ae oe ood a TA 187 EXaMPIES ei a ozara E IS SAA E AS A EOE ae aks 189 DOIE aese ata Dr vt tes Maen E a Seagal hah om date a OT he 191 ptadmin at to in aa A see a Ashe ened Uh Med A eS 192 DeSChiptions vai civank reine kok Pa en eae an r a a A PA ae a ae 192 SYNOPSIS pi GR ea eel Ei eee ee Ok Ad eee Gee ee ws 192 OPUS iae 098 2 ut Rs sis la ade ea Bit Bake 192 SOG ASO a atch dt o PK a MRL ft eA Bas Ak Akt 192 NOE eerie get a ds ASA a E ae Oo a ha a ey 192 DU sr Soares aes EIA tenets eae was orp eh Mare 194 DESCHIDUOM s reii i ance dig eaaa a te
30. PSA The following are the sections found in this chapter PSA Concepts PSA GUI Visual Reference a PSA Tasks 169 PSQL System Analyzer PSA PSA Concepts PSQL System Analyzer PSA is a utility that allows you to perform the following actions View the PSQL components on your system with version information usage status size and location m Identify duplicate components on your system Test your network communications to verify connectivity m Test connectivity to the M icroKernel Engine m Test connectivity to the Relational Engine View Modules This option allows you to view all PSQL components and any other files you specify You can add additional components to the search list Their versions usage status file size and location is displayed Duplicate files are identified so you can resolve any potential conflicts View modules scans a machine for PSQL components to determine which ones areloaded into memory Test Active Installation This option allows tests of the network connectivity between aPSQL client and the M icroKernel Engine and Relational Engine If errorsare detected PSA gives you detailed troubleshootinginformation to help you resolve the problem Network TheNetwork Communication tests verify that your client or workstation can communicate with network protocols to reach the machine on which the PSQL database engine is installed For the TCP IP protocols both Pv4 and Pv6 numeric and
31. Parameters continued Parameter Mandatory Default Value Description Optional lt c C gt encoding Optional The system code page or The valid values for encoding are ASCII ifthe system code ASCII page cannot be determined UTF 6 UTF 16LE UTF 16BE The encoding parameter is not case sensitive and does not require quotes See Examples Note Ifa data file contains a byte order mark BOM BDU uses the encoding specified by the BOM That is if a data file uses a BOM to indicate an encoding of UTF 8 UTF 16LE or UTF 16BE BDU uses that encoding regardless of what value you specify for the encoding parameter on the command line If you provide an invalid value for encoding the message returned is Invalid value for command line argument file_encoding lt h H gt Optional Displays the version of BDU and the usage help Notes Configuration Settings You are not required to change any PSQL configuration settings to use BDU BDU loads data into a table using the accelerated mode During the load of data the M icroKernel does not perform transaction logging If you use archival logging back up your data files again Error Logging By default BDU logsall information and error messages to the standard error stream stderr You may specify a log file to which the utility writes the information or error messages Two types of errors are not logged critical and recoverable With critica
32. Pervasive ODBC Interface driver for 32 bit or 64 bit applications Command Line Interface Utility Reference Table19 FAQsAbout ODBC and DSN Support for Linux continued Question Answer Can still create deprecated legacy style Engine DSNs and Client DSNs Yes but you must specify the engdsn or clntdsn option with dsnadd DSNs created with either option support only 32 bit applications If am using ODBC Administrator on a Windows client why do not see my DSNs On 64 bit Windows operating systems 64 bit system DSNs are distinct from 32 bit system DSNs because of the registry design If you are using the 64 bit ODBC Administrator you will not see the 32 bit system DSNs and vice versa What if my application uses DTI to manage DSNs The DTI functions for DSNs manage only 32 bit Engine DSNs Therefore the DTI functions for DSNs are deprecated along with the 32 bit Engine Interface ODBC driver An alternative to using DTI to manage DSNs is to use SQL and ODBC statements For example you could use CREATE DATABASE to create a named database and SQLConfigDatasource to configure the DSNs Going forward is there a recommended strategy for ODBC connections Yes New applications or revised 32 bit applications local or remote should connect to a named database ODBC Header Files Thesql h saltypes h and sqlext h header files for ODBC contain differences for the compilation of
33. Windows Server 2 Substitute PSQL Workgroup Engine as the service name if you use Windows Services control panel Substitute psqlWGE as the service name if you use net start or psc 18 Starting and Stopping the Database Engine Starting and Stopping the Database Engine on Linux In Linux the database engine runs asa daemon The daemon isloaded as part of the installation process and is set to be always available if you followed the complete installation You must be logged in as the root user to start and stop the PSQL v12 daemon process Use the shell script psqi to start and stop the process gt To start the database engine on Linux Enter the following at the command line etc init d psql start For PSQL 64 bit Server this command starts both thetransactional daemon and the relational daemon Therelational daemon is 32 bit only gt To stop the database engine on Linux Enter the following at the command line etc init d psql stop For PSQL 64 bit Server this command stops both the transactional daemon and therelational daemon gt To force stop the database engine on Linux If the database engine does not start or stop correctly using the psql start and stop commands you may need to force stop the engine The force stop removes any shared memory and semaphores created by PSQL and stops the PSQL relational and transactional processes Enter the following at the command line to force stop the database engine etc in
34. You can export a schema for a particular table or tables or at the database level for all tables at once gt To export a table schema for a particular table 1 InPSQL Explorer expand the Tables node for the desired database If you are interested in system tables expand the Tables node under System O bjects 2 Click thetablename for which you want to export its schema If you want to export the schema for additional tables press and hold Shift or Ctrl then click the desired table names In other words use multiple select for the tables you want Right click on the selected table name then click Export Table Schema 4 Typethename and location if you want for the exported file The default file extension is sql 5 Optionally select the IN DICTIONARY or the USING option 6 Click OK 78 M etadata gt To export all table schemas at once 1 2 3 4 al In PSQL Explorer expand the Databases node Right click the database name for which you want to export all of the table schemas Click Export Table Schema Type the name and location if you want for the exported file The default file extension is sql Optionally select the IN DICTIONARY or the USING option Click OK 79 Using PSQL Control Center Triggers Stored Procedures User defined Functions and Views PCC provides a way to create modify and deletetriggers stored procedures user defined functions and views See also SQL Editor Ta
35. a database might consist of the Food Preferences table below and the Phone Book table above With PSQL a database consists of one or more data files and Data Dictionary Files DDFs on your hard disk The DDFs are special data files that contain all the definitions for tables columns and other attributes that define the structure of your database Schema Theterm schema refers to the complete set of definitions that describe the entire structure of a database A typical schema includes definitions for tables columns indexes and many other attributes The DDFs for a database contain the database s schema Table3 Example Table for Food Preferences Food Preferences Table Column 1 Column 2 Column 3 Column 4 Col Names Name Meat Grain Drink Row 1 Fred Black sushi wheat sake Row 2 Jane Doe steak oats beer Row 3 Ann Dean cod bran spring water Remote Theterm remote refers to an object such as afile server or a database that isnot located in the computer you are using now When you connect to a database over the network you are connecting to aremote database Remote is the opposite of local Remote can refer to either the client or the server depending on whether you are currently seated at the server computer or a client computer Remote always refers to an object that is not located on the system you are using Local Theterm local refersto the computer you are using right now or something stored on this c
36. a statement separator at the end of each statement SQL Editor accepts only the pound sign and the semicolon as statement separators See To select an SQL statement separator If you arenot using temporary tables you may use either separator solely or mix their usage within a set of SQL statements That is some statements can end with a pound sign and others with a semicolon if you so choose 150 SQL Editor Concepts Temporary tables begin with or If you use temporary tables the pound sign may not be used as a statement separator Instead set the statement separator to the semicolon Restrictions The following actions and SQL statements are not supported in SQL Editor m Creating a database a Useof dynamic parameters indicated by a question mark a COMMIT and START TRANSACTION n PCC SQL Editor in v12 you cannot edit views stored procedures user defined functions or triggersthat contain characters outsideof the database code page You must first drop them and then reenter them from an edited source file Displaying Statement Results SQL Editor displays the results of running SQL statements in the following PCC window views Grid Text In addition by default an Outline window view displays a list of the SQL statements in SQL Editor typically with a shorter line length For example you may want the Outline view to show only the first 5 words of each SQL statement in SQL Editor To set pr
37. ability of your client to connect to the PSQL database engine through the Relational Engine When you run this test PSA attempts to perform common SQL database operations A dialog box displays during the tests to show progress If your machine passes this test then the following is verified TheRelational Engine relational engine is running a Your client interface components are installed correctly a Thenetwork communication between the client and the database engine is functioning correctly SQL applications running on your computer should function correctly Frequently Asked Questions Listed below are some frequently asked questions about PSA a Whatisthe default log file name Can usea different log file name a Whatisthelocal default location for the log file Whatistheremote default location for the log file a What kind of information is contained in the log file a What happens to theinformation in the log file each time the utility is run Howdol run PSA When would want to use PSA What is the default log file name The default file name for the log file is PSA log Can use a different log file name You can rename thefile but the name applies only for that session of PSA PSA defaults the name to PSA log the next time you execute PSA 171 PSQL System Analyzer PSA What is the local default location for the log file The default local location for the log file is under the application data d
38. applies because the database engine uses the file name for various areas of functionality while ignoring the file name extension Since only the file name is used to differentiate files files that differ only in their file name extension look identical to the database engine Specify the additional options on the dialog as required for the database See Table 10 for a discussion of the options Creating M odifying Deleting and Repairing PSQL Databases gt To modify properties of a database 1 3 In PCC PSQL Explorer right click the database engine for which you want to modify the properties then click Properties B Pervasive SQL fc Local Client E cA Engines E 2 tment A A Databases ER MyDataBase Master Delete Delete Refresh FS Properties On the Properties dialog click the tree nodefor which you want to specify properties Directories General Relational Constraints e Security Set the specific properties as required gt To delete a database You cannot delete the database to which you are currently logged in See To log out from a database engine in PSQL User s Guide If database security is set to mixed or database you must first remove the security You cannot delete a database if security on it is set to mixed or database See To turn off security using PSQL Explorer and To turn off security using SQL 1 2 3 In PCC PSQL Explorer
39. authorize a gt lt key Authorizes the specified key or authorization key data file offline authorization authorization_filename pky gt lt deauthorize d gt key Deauthorizes the specified key from the computer on which the CLI utility is running If the specified key is a permanent key also deauthorizes all increase keys user count session count data in use associated with the permanent key 108 License Administrator Command Line Interface Table 13 License Administrator Command Line O ptions and Parameters continued Options and Parameters Function lt clear c gt key force lt generate g gt key output_filename lt number n gt key Clears from the machine all permanent keys and their associated increase keys for user count session count or data in use If a key is specified only the specific key and its associated increases for user count session count or data in use are cleared If no key is specified all keys and associated increases are cleared Note This command is useful if you are unable to deauthorize a key because of changes in the hardware configuration and you need to authorize another set of keys You could clear the key s and authorize the product with a different key than the one s you cleared If the force option is included a confirmation message is not displayed Note This option only removes a key from the local PSQL server It does not deauthor
40. because they are available to all users on a given computer In theDrivers window select PervasiveO DBC Client Interfacefor a 32 bit DSN or PervasiveO DBC Interface for a 64 bit DSN 31 Using PSQL 4 Specify a Data SourceName DSN for which you want to set up a connection ThisDSN helps you identify the data source Figure5 PervasiveO DBC Interface for Client DSN s Data Source Name Description Pervasive ODBC Client Interface Cancel Connection Attributes Server Name IP f Help Transport Hint Try TCPIP first then SPX gt Advanced Data Options Database Name Create Get List Engine DSN Create Modify GetList Note that on the 64 bit interface the Engines DSN option is not available and is not present on the GUI 5 For Server Name IP specify a machine name or TCP IP address of a machine running the PSQL database engine 6 Select the Transport H int desired 7 Specify aD atabase Nameto which to connect Click Get List to obtain alist of the existing databases on the server listed for Server Name IP For 32 bit DSN sonly you may specify an Engine DSN instead of a databasename Notethat Engine DSNs are deprecated so it is preferable to specify a database name rather than an Engine DSN Click Get List to obtain a list of the existing DSN s on the server listed for Server Name IP Click Modify to modify an existing DSN or Create to create a new DSN 8 Ifyou want a
41. bit version is named w64clilcadm and the Linux 64 bit version is named clilcadm64 gt To Display Information About a Key 14 To display information about all keys enter the following command clilcadm i 2 To display information about a specific key enter the following command clilcadm i key See License Administrator Command Line Options and Parameters for the columnar information returned by the i option gt To Perform a Validation 1 To perform a validation enter the following command clilcadm t we Tip To determine the product key s post validation state and date use the i option to display information about the specified key gt To Display Remaining Authorizations 4 To display the remaining authorizations for all product keys enter the following command clilcadm n 2 To display the remaining authorizations for a specific key enter the following command clilcadm n key Ya f Tip To determine a product key usethe i option to display information about all keys See To Display Information About a Key gt To Authorize a Key 1 Enter the following command clilcadm a key 116 gt 1 Y SS gt License Administration T asks Tip When you authorize an increase key for user count session count or data in use a permanent product key must already exist The vendor ID of the increase key must match the vendor ID of the permanent key To Deauthorize a Key Enter the following
42. characters EUC_JP Extended Unix Code EUC _JP is a variable width encoding used to represent the elements of three Japanese character set standards JIS X 0208 JIS X 0212 and JIS X 0201 Shift_JIS Shift_JIS Japanese Industrial Standards is a character encoding for the Japanese language UTF 8 UTF Universal Character Set Transformation Format 8 is an 8 bit variable width encoding that can represent every character in the Unicode character set lt was designed for backward compatibility with ASCII and to avoid the complications of endianness and byte order marks BOM UTF 8 with BOM Same as UTF 8 except with a byte order mark BOM which is a Unicode character used to signal the endianness byte order written to the file during the save action UTF 16BE big endian A UTF encoding in 16 bit with byte order of big endian UTF 16BE with BOM Same as UTF 16BE except with a BOM written to the file during the save action UTF 16LE little endian A UTF encoding in 16 bit with byte order of little endian 90 Dialogs for File O pen and File Save Table12 Encoding Choices for File Open and File Save Dialogs Encoding Discussion UTF 16LE with BOM Same as UTF 16LE except with a BOM written to the file during the save action Notes By default PCC remembers the encoding of a file that has been opened If you modify the file and save your changes the changes are saved in the original encoding Howeve
43. click Remove To move the tool toward the top of thelist click Up To move the tool toward the bottom of the list click Down 55 Using PSQL Control Center Services on Windows Servers PCC offers a convenient way to work with PSQL servers on Windows machines without having to use the Windows Services control panel You can start stop and set the startup policy from within PCC You must stop the relational and transactional services to completely stop PSQL Stopping just one of the services does not stop the database engine completely This section applies only to Windows platforms not to Linux Note PSQL products other than the database engine also run as services The services of these other products can havea dependency on the database engine services See Services Dependencies for further information gt To start or stop services 1 In PSQL Explorer expand the Services node in the tree 2 Right click on the servicethat you want to stop or start 3 Perform one of the following actions Click Start Service to start the service Click Stop Service to stop the service Click Restart Service to stop then start the service Va i Tip You can also stop or restart all services with a single command Right click the Services nodein the tree then click Stop All Services or Restart All Services gt To set services startup policy 1 In PSQL Explorer expand the Services node in the tree 2 Right click on
44. command clilcadm d key Tip When you deauthorize a product key all increase keys for user count session count or data in use associated with that product key are automatically deauthorized as well To Remove Keys Without Deauthorizing Circumstances can occur for which you need to remove a key and its associated increase keys for user count session count or data in use without deauthorizing the product This action is called clearing a key 1 To clear a specific key enter the following command where key is the individual key you want to Clear clilcadm c key To clear all keys enter the following command clilcadm c With either command you can include the force parameter if you want to suppress the clear confirmation message Example clilcadm c force gt 1 To Repair a Key Enter the following command clilcadm e key The command window displays a message asking you to confirm that this is a genuine copy of the software product Enter A to confirm After the repair is complete the command window displays a message confirming the repair To Display Help Enter either of the following commands clilcadm lt help h gt clilcadm 117 License Administration Alternative Authorization Tasks PSQL provides some alternate ways to authorize and deauthorize your PSQL product If possible however authorize PSQL using online authorization If the machine where PSQL is installed h
45. exists to execute all statements sequentially in the SQL Editor The command is called Execute All SQL Statements and can be invoked from the SQL menu from a toolbar button or from within the Outline view Results from this command always display in the Text view regardless of the statements in SQL Editor 152 SQL Editor Concepts Identifying the Text Window The Text window view showsin atext format the result of running SQL statements You cannot change the data values in the database by changing the text but you can copy the text You may use the Text window view for SELECT statements to show data returned The data returned appears in acolumnar format with each field represented as an underlined heading The data appears as rows below the headings For how to change the font used by the Text window see To set preferences for Text Output click Text Output For PCC running on an operating system set to an English language locale the system selects a default font For non English locales PCC seeks to match the default font or system font if such can be found Otherwiseit will select reasonable font Figure24 Text Window View PaPervasive SOL O E Room as sit Sems E BE 0utine 3 g Pervasive SAL SELECT FROM Room Ales S Local Client E Microkemel Router Room 4 SELECT FROM Room E o Databases E CHECKID H DBASES e DEFAULTDE E DEMODATA Classr
46. for To work with indexes 2 Click the desired index in the Indexes list The Index Segment Details displays Modify the segment details as desired 4 Click File gt Save or El before changing pages within Table Editor 140 Table Editor Tasks gt To delete an index Thedatabase engine creates some indexes such asI DENTITY column indexes and primary key indexes These indexes are read only and cannot be deleted 1 4 5 Ensure that the Indexes page of Table Editor is active If required perform the steps for To work with indexes Click the desired index in the Indexes list With the Index you want to delete selected click Delete in the Indexes list Index Editor Indexes E Student_Trans Add E myindex Click Yes to confirm the deletion Click File gt Save or E before changing pages within Table Editor gt To insert an index segment For detailed information about segments see Segmentation in the PSQL Programmer s Guide which is part of the PSQL Software Developer s Kit SDK Only the database engine can add an index to an IDENTITY or aSMALLIDENTITY column However you can includean IDENTITY or aSMALLIDENTITY column as part of amultiple segment index See also AUTOINC in SQL Engine Reference 1 2 3 4 Ensure that the Indexes page of Table Editor is active If required perform the steps for To work with indexes Click the desired index in the Indexes list Th
47. for each uniqueincoming protocol from the same client computer session If one application uses TCP IP and another application uses SPX IPX two users are counted if both applications run on thesame machine If different address formats of the same protocol are used only one user is counted For example if one application uses Pv4 and another uses Pv6 only one user is counted if both applications run on the same machine Pv4 and I Pv6 arejust different address formats of TCP IP Obtaining a User Count An initial user count is provided as part of the product key A product key is issued by Actian Corporation or by your application vendor if the PSQL database engine is enbedded in an application You may also increase the user count beyond the initial amount provided by the product key See Increasing User Count Capacity based License Model The capacity model shifts the emphasis from how many users to how much work the database server performs The model is based on the process capacity of the engine determined by the amount of data in use Thisis the total size of data files authorized to be opened concurrently 98 License Administration Concepts Data In Use Datain use is defined as the total size of all concurrently open data files A data fileis a file created by an application to provide the data processed by that application Data in use limit is the maximum permitted amount of all concurrently open data files a
48. gt UIDuname PWDpword DBdbname VRI Note On Linux distributions all parameters use the hyphen instead of the slash For example the O parameter for butil copy is O asin butil copy O 194 Command Line Interface Utility Reference Options Maintenance Utility command options are not case sensitive unless the option is a filename If you run but 11 without specifying a command option or with an invalid command option a usage message is printed The usage message indicates that thereis an optional s command line argument to but il This argument is ignored under Linux For a complete discussion of the utility commands options and examples see the section Btrieve Command Line M aintenance Utility butil in Advanced O perations Guide See Also syslogd in Linux man pages Btrieve API Guide which describes the API for the M icroKernel Engine 195 Command Line Interface U tilities clilcadm Description Thecommand line License Administrator utility manages the user count licenses on your engine The Windows and Linux 32 bit command line utility is named clilcadm the Windows the 64 bit utility is named w64clilcadm and the Linux 64 bit version of this utility is named clilcadm64 Note On Linux this utility can only berun by user accounts belonging to group pvsw See Getting Started With PSQL for information on PSQL Linux utilities and user accounts Synopsis clilcad
49. gt Save or El before changing pages within Table Editor gt To set a column collating sequence For additional information about collating sequences see M anipulating Btrieve Data Files with Maintenance in Advanced O perations Guide and Alternate Collating Sequences in the PSQL Programmer s Guide which is part of the PSQL Software Developer s Kit SDK If you usean alternate collating sequence ACS you cannot specify case sensitivity Case sensitivity does not apply if the key uses an ACS 1 5 Ensure that the Columns page of Table Editor is active If required perform the steps for To work with columns Click in the Collate cell for the desired column You can set an alternating collating sequence ACS only for applicable data types If collating sequence is not applicable the grid cell is shaded and you will be unableto edit the Collate cell Delete the existing value if present Type the path and ACS filename you want PSQL supplies an ACS file upper alt in the Samples folder See Where arethe PSQL files installed in Getting Started With PSQL To use this file you would type file path PSQL samples upper alt Upper alt treats upper and lower case letters the same for sorting For example if a database has values abc ABC DEF and Def inserted in that order thesorting with upper alt returns as abc ABC DEF and Def The values abc and ABC and the values DEF and Def are considered duplicates and are re
50. hae ante Dnt th Mtoe rd Wath hae Dice 194 SYMOPSIS oa Sats hth E AA op id 194 OPtlONS vns a ban eed ile dee poe ee 195 SOG ASO isons BS aro de Rk Ge We ened gee aE ee ee ea Ghee ee A 195 AICA ce oss eevee Scarce a oy tore a dich Bed Dh cope th os je eh a ot aw ad A 196 DESChIPUOMN ffi do sack ti Se hd Ae ae Si ik blob od hie be tk od 196 SYNOPSIS gt if tai es ee ti a i lee e ee be ee ea Oe 196 OPIO SSL RR A Soe BER RA SE ee ew eS 196 Se AlS ica it Hale aaa a ad ole ne Bae Aas ors Me 196 clipadd Are deine he an Males dote dial le toi e i lle had cdl the Mind 197 Descriptions aia A aloe alee Berio PAAR eae eh ede eS 197 SYNOPSIS esi SBA a eer ward Ba a ae Eater wee ke ee wea eS 197 OPUONS es tna la Boke OR aut nto we Shoe Mas aR tak Beh ee da RA 197 Se ASO a 4 4 nlite tate bbe a do e ew AE ENE i f 197 CDIMAING iirsp A Oo eg 198 Description anena a aces Gear A RE S E E kD Sean ears E Daehn 198 SYNOPSIS At ie a ES NN 198 vii Contents viii OPULONS sf ies aia dd tad Se Sede mayen aie ae ot Se 198 SOG ASO ri e la Louise dogs Al Gap te ie Lge toh 200 CSN AUC ita wg itn alte rienda Netti ued Dai Wa tether dra Ge tes 201 Descriptions ss ceafa A is aed ean as Pak Oh Sa dae ane ie 201 ST A E en ab ain ce eens hh ae pee 201 OPON Saa a nce ere te PAS sae Tae tote A A ae ance teat ane tee ee aa wives 202 EXOMDICS Ss paid ita ta aa iets E Bt Th ay coated edge ch Peed a 203 NOTES tice hewn Kiki Coe ae Re id ea A cae Ate Sa E o essai
51. icroKernel Engine and connects to a Linux database engine that is configured to use BTPASSWD or PAM authentication the application requires a set of credentials to connect to the database engine See Authentication in Getting Started With PSQL Use pvnetpass to configuretheset s of credentials that the application will use Pvnetpass must be run on every machine that connects to the database engine whether the database engine is local or remote If you have a global and a user entry for the same server the user s entry overrides the global The user nameshould includethefull user context For example in a Windows environment with domain names specify the user as DoMAIN user For a Linux environment usethe user account name and the full machine DNS name For example mymachine mydomain The pvnetpass utility can also be used by Windows clients to change their stored credentials that were saved when using the security login dialog See also Allow Client stored Credentials and Prompt for Client Credentials both in Advanced O perations Guide Synopsis pvnetpass g a r m server u user p pwd pvnetpass d Options a Adds a server entry for a user specified by the u parameter If no user is specified current user is assumed d Displays the list of configured servers The configured servers will display in two groups that are separated by a dashed line The ones above the line are global entries and are only viewable by admin
52. in each record 75 UsingPSQL Control Center 76 Encoding of the exported data For example if you select SO 8859 1 the data is exported using that code page The encoding choices are obtained from the machine on which the utility is running Whether or not to write the names of the columns as the first line of the exported data To export data from a database table For a particular database right click a table name under the Tables node If you are interested in system tables expand the Tables node under System O bjects Click Export Data Provide the export characteristics as discussed above then click Finish M etadata Metadata M etadata is data about data The metadata for a PSQL database is called a schema For a relational database the schema defines the tables the fields in each table and the relationships between fields and tables Schemas are stored as data dictionary files DDFs by PSQL You can export the schema for one or more tables to a text file The exported schema contains the CREATE TABLE SQL statement and CREATE INDEX statement if applicable to create the table and its indexes The exported file has a default file extension of sql and is called an SQL script TheSQL script file can be run executed in SQL Editor See To open an SQL script TheSQL script contains the text Unable to open table if a table cannot be opened For example the error occurs if an owner name is set on a tab
53. index in the Indexes list The Index Segment Details displays 3 Click in the Sort Columns cell then open the list of sort choices click 4 From the Sort Order list click Ascending or Descending The default sort order when an index segment is created is ascending 5 Click File gt Save or El before changing pages within Table Editor gt To allow duplicates in an index For detailed information about duplicates see Duplicatability in the PSQL Programmer s Guide which is part of the PSQL Software Developer s Kit SDK 1 Ensurethat the Indexes page of Table Editor is active If required perform the steps for To work with indexes 2 Click the desired index in the Indexes list The Index Segment Details displays 3 Clear the Unique option in the Index Segment Details area by selecting one of the other options Partial or Normal Note By default indexes are created as Normal allowing duplicates 4 Click File gt Save or E before changing pages within Table Editor gt To specify index as modifiable For detailed information about modifiability see M odifiability in the PSQL Programmer s Guide which is part of the PSQL Software Developer s Kit SDK 1 Ensure that the Indexes page of Table Editor is active If required perform the steps for To work with indexes 2 Click the desired index in the Indexes list The Index Segment Details displays 3 Click Allow Modifications option Index Segme
54. it to allow license authorization Configure the proxy server before you install PSQL or omit product authorization during installation and authorize the product after configuring the proxy server Windows Actian Corporation recommends that you configure proxy servers through Windows Internet Explorer for the best results PSQL license authorization works seamlessly with no modifications needed when proxy servers are configured through Window Internet Explorer If you are currently using a proxy server that was not configured using Windows Internet Explorer we recommend that you reconfigure it through Windows Internet Explorer H owever if using Windows Internet Explorer to configure your proxy server is not an option use the following steps If you are using a 64 bit Windows operating system there are two possible places where the Registry settings for proxy servers can be stored HKEY_LOCAL_MACHINE Software Pervasive Software ELS key HKEY_LOCAL_MACHINE Software Wow6432N ode Pervasive Software ELS key Dependingon thePSQL edition Server Client Vx Server or Workgroup the ELS proxy server settings can be created in either location Licensing manager looks for its proxy server settings first in HKEY_LOCAL_MACHINE Software Wow6432N ode Pervasive Software ELS key If it cannot locate them there it looks in HKEY_LOCAL_MACHINE Software Pervasive Software ELS key Therefore if you are editing Registry settings for proxy servers y
55. m Thevalue for data in use decreases when a data file is closed by the final user to have the file open Since more than one user can access the same data file all opens must be closed before data in use decreases Messages pertaining to datain useare logged to the various PSQL logging repositories For example a message first alerts you when data in use reaches 90 of the data in use limit Corrective Action When Data Use Limit ls Reached The following suggested corrective actions actions may be taken when the data use limit is reached in order to continue working with the engine m Authorize an increase key for data in use An increase key can be authorized at any time and does not require that you close data files or stop the database engine The increase is immediately available See Increasing Data In Use Limit You may try closing some of the currently open data files However this action requires that you know which data files are safe to close without resulting in incorrect data incomplete records or aborted transactions Obtaining an Initial Data In Use Limit Initial data in use limits are provided in the product key The key is issued by Actian Corporation or by your application vendor if the PSQL database engine is embedded in an application You may also increase the data in use beyond the initial amount provided by the product key See Increasing Data In Use Limit License Enforcement Licensing for PSQL is enforced b
56. modules Test active installation I Test network communication Test transactional engine Test relational engine Log File ee J Append to the log file GUI Object Description Related Information View loaded PSQL Displays all current PSQL components and version information in View Modules Tasks modules a table Also allows you to add additional components to the matrix Test active Test the PSQL installation in three ways network connectivity Test Active Installation Tasks installation functionality of the MicroKernel Engine and functionality of the Relational Engine Select the options that correspond to the tests you wish to perform Test network Test the network connectivity to a machine running the database To test your network engine The network tests display detailed information about any problems encountered during the test and provide suggestions on how to remedy the problems If you do not have Internet Explorer installed this test is not available because the test uses an embedded HTML component to display results Test transactional Tests the functionality of the MicroKernel Engine To test the MicroKernel engine transactional Engine Test relational Tests the functionality of the Relational Engine To test the Relational Engine engine Log file Allows you to specify a different log file location than the default To specify a different location for PSA logs detailed info
57. named addresses are supported See also Drive based Formats in Getting Started With PSQL for additional details about Pv6 Using Advanced Settings you can select the protocols to test as well asthenumber of stress test messages to send By default the network test connects using any available protocol that is installed on the system and configured for use in PSQL See Supported Protocols configuration parameter found in Advanced Operations Guide The progress bar will complete for all selected tests The steps involved in testing network communication are 1 Verify Available Protocols Verify Network Client Availability Qualify Target Name Resolve Target Location to network address Verify Server Address Verify Server Connection Nn oO a fF WO ND Run Stress Test 170 PSA Concepts Transactional Engine This test verifies the ability of your client to connect to the PSQL database through the M icroKernel Engine When you run this test PSA attempts to perform basic database operations that are common to most MicroKernel Engine applications If your machine passes this test then the following is verified a TheMicroKernel Engine is responding a Your client interface components are installed correctly Thenetwork communication between the client and the database engine is functioning correctly MicroKernel Engine applications running on your computer should function correctly Relational Engine This test verifies the
58. names ex ownerl owner2 Show owner names Do not ask again for owner names Tables requiring an owner name that is unknown will be skipped Skip The number of tables skipped is reported at the end of the repair operation in a dialog box The names of the tables skipped are written to the PCC Log 71 Using PSQL Control Center Tables Tables are the objects in which databases store data PSQL contains two types of tables data and system Data tables are user created N ewly created ones are empty and must be populated with data System tables are created and populated as required by the PSQ L database management system Data Tables Refer to the chapter Table Editor for a detailed discussion of data tables That chapter also contains the tasks pertaining to tables such as creating one deleting one working with columns foreign keys and so forth Note To create a table in a database database security must be turned off or you must have access rights to create tables System Tables System tables appear in the PSQL Explorer under the System O bjects node You may view properties of them as explained in To view properties of atable Table Properties Table properties provides information about the table Separate tabs let you view general properties columns information and indexes information The following table describes the parameters listed on the General tab Table11 Table Properties on Genera
59. on the page Foreign Keys Page The Foreign Keys page lets you add and modify foreign keys See Foreign Keys Tasks for the tasks that you perform on the page SQL View Page TheSQL View page lets you view the CREATE TABLE and ALTER TABLE statements that apply to the table Notethat SQL View isdisplay only You cannot modify theSQL statements but you can copy them See SQL View Tasks for the tasks that you perform on the page TheSQL View page reflects changes made on the other pages as explained below 123 Table Editor CREATE Statements TheCREATE TABLE statement shows the SQL used to create the table For anew table one that has not yet been saved the CREATE TABLE statement reflects the SQL used to implement edits from the other three tabbed dialogs For example if you were to create a new table named MyNewTable SQL View initially shows the following CREATE statement CREATE TABLE MyNewTable y If you wereto add two CHAR columns to the new table on the Columns page SQL View reflects this in the CREATE statement CREATE TABLE MyNewTable FirstName CHAR 20 LastName CHAR 30 y Ifatable has been saved already exists the CREATE TABLE statement showstheSQL required to create the table For example if you were to edit the Course table provided with the sample database DEM ODATA SQL View shows the following CREATE statements CREATE TABLE Course Name CHAR 7 NOT NULL Description CHAR 5
60. or group for whose account you want to grant the user policy For example you could grant the policy to the Pervasive Admin group 8 Click Add The user name is added to the bottom pane For example the following image shows that the Administrator has been added 23 Using PSQL Name el Administrator Add Check Names 9 ClickOK The user name is added to the settings for local security policy Act as part of the operating system Local Effective Assigned To Policy Setting Policy Setting 3 1 5 21 776561741 764733703 120 2660629 1001 Administrator O 10 Click OK 11 Exit the window for Local Security Settings then exit Administrative Tools Granting Administrator Rights on Linux gt To grant a user administrator rights on Linux A user cannot remotely administer a server engine on Linux unless the user has first been set up asa database user with administrative rights You can perform thistask by using the btadmin utility at the server command line See also PSQL Account M anagement on Linux in Getting Started With PSQL for a complete discussion of configuringthe environment for administrative rights 1 LogintotheLinuxserver aSpsal Or aS root if the prats and 1D LIBRARY PATH Variables havebeen set and exported No other user is permitted to run btadmin 2 Create a new user with administrative rights by running btadmin btadmin p passwd a user_name For example if you wanted to createan administra
61. save action The following set of characters are invalid and cannot be used lt gt To allow afile to be moved between Windows and Linux this set of invalid characters is the same for those operating systems 91 UsingPSQL Control Center File Open Dialog Open SQL Document The following image shows the File Open dialog ready to open the demodata sql sample file provided with PSQL Note the Preview The Document field defaults to the location used for the previous File Open or File Save Open SQL Document Enter SQL document name to open its contents using the specified encoding Document PSQL Demodata restore demodata sq Encoding windows 1252 v Change default encoding Preview drop table billing drop table class drop table course You can access the standard operating system file dialog by clicking Browse File Save Dialog Save SQL Document The following image shows the File Save dialog Preview does not apply The Document field defaults to the location used for the previous File Save or File Open Save SQL Document Enter SQL document name to save the contents using the specified encoding Save To C Users Public Documents Browse Encoding windows 1252 Change default encodin A full path and file name is required to save a SQL document You can access the standard operating system file dialog by clicking Browse Using the Standard Operating System Dialogs If you pre
62. save the changes to the structure of a table if the tableis open in SQL Editor Close the SQL Editor referencing the table then save the changes gt To save changes for all tables being edited Note that you cannot undo or redo changes to tables once the tables have been saved 1 Click File gt SaveAll Note You cannot savethe changes to thestructure of atableif the tableis open in SQL Editor Close the SQL Editors referencing the tables then save the changes gt To undo changes or to redo changes 1 Inthetoolbar click to undo an action click gt to redo an action If multiple actions have occurred since the last save you can repeatedly click the undo or redo toolbar buttons When no more actions are available for undo or redo the toolbar button becomes disabled Note that you cannot undo or redo changes to a table once the table has been saved 130 Table Editor Tasks Columns Tasks gt To insert a column between existing columns 1 Vs ES 7 Ensure that the Columns page of Table Editor is active If required perform the steps for To work with columns Right click on an existing column row above which you want to insert a new column Click Insert Column The new column appears above the existing column row The default name of the inserted column is columnn where n sa number that automatically increments by one The first column you insert is columno0 the second column1 and so forth
63. set a primary key on a column that allows NULLs 1 Ensurethat the Columns page of Table Editor is active If required perform the steps for To work with columns 2 Click E the column selection icon on the far left of the column row for the desired column s To select multiple columns press and hold Shift or Ctrl then click 4 for the desired additional columns 3 Click 4 the primary key icon If the column s is not a primary key the action sets the column s as a primary key Ifthecolumn or if any of the columns when multiple columns are selected is already a primary key the action removes the setting from all columns For example suppose that column 1 isa primary key and you want columns 1 2 and 3 to bethe primary key You press and hold Ctrl then click columns 1 2 and 3 When you click the primary key icon it is removed from column 1 but not added to columns 2 and 3 If you click the primary key icon again then columns 1 2 and 3 are designated as primary keys 4 Click File gt Save or El before changing pages within Table Editor Index Tasks gt To create an index Only the database engine can add an index to an IDENTITY or SMALLIDENTITY column However you can include an IDENTITY or SM ALLIDENTITY column as part of a multiple segment index Table Editor permits you to include an IDENTITY or aSMALLIDENTITY column in the Indexes list if you have not saved the table However the DBM S returns an error when
64. statement last executed in SQL Editor Click Close If Refresh Grid on Exitis enabled the Grid displays therecord s that you just added assuming that the last statement executed in SQL Editor was SELECT FROM Billing 101581226 13 28 1996 4 5100 00 100 00 130312616 101811551 1 3 28 1996 4 3750 00 3250 00 313053054 102123022 1 3 28 1996 4 4000 00 3500 00 729772191 To delete row s of data from the Grid Caution Deleting a row from the Grid removes that record from physical storage No undo feature is available to reclaim the deleted record Click any cell within the row the record that you want to delete You may also select and delete multiple rows To select multiple rows press and hold down the Shift or Ctrl key then click acell in each desired row Click Edit gt Delete or click ES Click OK to confirm the deletion 165 SQL gt 1 Editor To enter a date time or timestamp data type in the Grid using scalar functions Asa convenience you can type the following scalar functions for date time and timestamp in Grid cells Data Type Scalar Function As Typed in Grid Cell Date e now curdate See also NOW and CURDATE both in SQL Engine Reference Time now curtime See also NOW and CURTIME in SQL Engine Reference Timestamp now See also NOW in SQL Engine Reference gt 1 gt 1 166 1 The names are case i
65. table Click the Indexes page tab To work with foreign keys Perform the steps for To start Table Editor for an existing table or To start Table Editor for anew table Click the Foreign Keys page tab To view SQL statements applicable to the table Perform the steps for To start Table Editor for an existing table or To start Table Editor for anew table 129 Table Editor 2 Click the SQL View page tab gt To view table data 14 If the Grid window view is not displayed click Window gt Show View gt Grid 2 Perform the steps for To start Table Editor for an existing table or To start Table Editor for anew table 3 By default the Grid shows all of the data for the table the result of a SELECT FROM table statement If theGrid isempty but the table contains data right click on any row in the Grid then click Refresh Note that the Grid allows you to directly change database data by changing the valuesin the grid cells See Grid Tasks gt To identify tables with changes that have not been saved 1 Observe the Table Editor tab at the top The tab contains the name of the table being created or edited An asterisk precedes the name if any modifications have occurred to columns indexes or foreign keys but not yet saved CE gt To save changes for the table being edited Note that you cannot undo or redo changes to a table once the table has been saved 1 Click File gt Save or click H Note You cannot
66. the Client or Server DSN for the PSQL database you wish to access as shown in the example below Figure12 Excel Display of ODBC Source List IT zx Databases Queries OLAP Cubes lt New Data Source gt dBase Files Word Cancel dBASE Files DeluxeCD Browse Excel Files FoxPro Files Word MS Access Database superdatal Neil o Dahan nat El IV Use the Query Wizard to create edit queries If the database you want does not appear in the ODBC Source list see Before You Begin 4 Click OK You may be prompted to login to the PSQL database If the database is not secure leave the User and Password fields empty Otherwise enter your assigned user name and password 5 TheQuery Wizard opens Simply follow the wizard to select your options such as which tables to query how to filter and sort the data and how you would like Excel to return the PSQ L data to you for your use Accessing Data Using Microsoft Access gt To access data from Microsoft Access 1 Open Microsoft Access 2 From the Access dialog box choose Blank Access database as shown below Click OK N ote that you may also add PSQL tables to an existing Access database Figure13 Createa New Database using M icrosoft Access Create a new database using Blank Access database Access database wizards pages and projects amp Open an existing file E Program Files Northy vind Sample D atabase xl
67. the Grid Both Table Editor and SQL Editor use the Grid See To view table data and Grid Window View for further details Text The Text window view showsin a text format the result of running SQL statements The text is display only You cannot change data values by changing the text but you can copy text SeeText Window View for a detailed discussion 51 Using PSQL Control Center Outline The Outline window view allows you to view the SQL statements in a tree structure The root node of the tree is the same name as the name of the SQL Editor window view See Outline Window View for a detailed discussion Notethat the editor must support an outlineor the Outline window view isnot available Currently only SQL Editor supports an outline view Table Editor Table Editor allows you to add delete or change the characteristics of columns within a table The table may be one newly created or an existing table that you want to edit See Table Editor for a detailed discussion Preferences You can set general preferences for your experiencein PCC You can also set preferences for the window viewsin PCC or for the external tools gt To set general preferences for Grid 1 OnthePCC Window menu click Preferences Expand the PSQL nodeif it is not already expanded 2 Click General The following are the options that can be set in the General Preferences Always remove associated DSN entries see Deleting DSN s Do not pr
68. the service for which you want to set a startup policy 3 Click Properties 4 Click the desired policy Startup Policy Meaning Manual You must manually start the service after the operating system starts Automatic The service automatically starts when the operating system starts Disabled The service is removed from operation and is not affected by starting the operating system 5 Click OK or Apply then OK Services Properties See To set services startup policy by using PCC in Advanced Operations Guide 56 Database Engines Database Engines You can use PCC to work with database engines that areon your machine or with remote server engines To work with a remote server engine you must introduce it to PCC This procedure is called registering the server Your local server is automatically registered to PCC when you install PSQL v12 Thelocal server appears in the PSQL Explorer as the first entry under the Engines node gt To register a remote server engine 1 2 3 In PSQL Explorer right click on the top node in the tree PSQL Click New gt Server Identify the server that you want to register Type the name by which the server is identified on the network or type the IP address of the server Click Finish Theserver should now appear in the PSQL Explorer window of PCC under the Engines node To log out from a database engine These steps do not erase the data or database from the server
69. u joe p password To add user acctadmin with password 88s kE5 to the local server named sles2H R pvnetpass a sles2HR u acctadmin p p88sJkE5 To add user bholly with password peggysue to a remote server named myserver pvnetpass a myserver u bholly p peggysue To verify your entry was accepted use the d option pvnetpass d This command results in Server myserver User bholly Password not displayed To change the password with which you will connect to myserver from your Linux client pvnetpass m myserver u bholly p newpassword To remove the entry for server myserver pvnetpass r myserver To add the default entry for users trying to connect to server myserver when no user specific entry exists pvnetpass g a myserver u admin p adminpassword To add the default server entry in the user context ps_HkEY USER pvnetpass a u admin p adminpassword To add the default server entry in the machine context ps key CONFIG pvnetpass g a u admin p adminpassword To authenticate from a Linux client to a Windows domain server myserver with a domain named mydomain and a user named user1 pvnetpass a myserver u mydomainluserl p userlpassword 218 Command Line Interface Utility Reference rbidcli Description rbldcli is used to rebuild MicroKernel data files on your server Synopsis rbldcli p
70. updated using the Function Executor This is one way to confirm the contents of file ddf or field ddf On somenon standard dictionaries the DDFs file ddf field ddf and or index ddf do not exist Such dictionaries do not work with our products For example if you see a file called x file ddf instead of file ddf you can assume your DDFs are non standard Non standard DDFs are unlikely to work properly with PSQL Control Center or the relational engine Can mix and match DDFs from different databases A complete set of DDF files must be considered a unit No DDF file from one database may be intermixed with DDFs from a different database What happened to DDF Sniffer DDF Sniffer was added to the PSQL product line with the acquisition of Smithwarein 1998 It isno longer available as a separate product Its functionality has since been largely replaced by the PSQL Control Center and DDF Builder 235 Basic Troubleshooting I have two similar Btrieve files and created a DDF for the first one Since they are similar can l use the same DDF on the second Btrieve file Theanswer depends on how similar thefiles are If the two files differ only in thenumber of records you can usethesameD DF file If there are any differences at all in the number order names or types of fields or indexes you cannot use the same DDF In other words you can only use the same DDF if the record structure of the two files is identical I have owner nam
71. use only by 32 bit applications that are already coded to use Client DSNs No longer created by default when host is specified with dsnadd the clntdsn option must be specified 32 bit Client DSNs are deprecated on Linux Pervasive ODBC Interface PSQL Server 64 bit PSQL Client 64 bit Frequently Asked Questions Thefollowingtable answers some frequently asked questions FAQs about ODBC and DSN support for Linux Installation assigns ODBC drivers in odbcinst ini for use by new 32 bit and 64 bit DSNs Connects to a local or remote named database The recommended driver description to use for 32 bit and 64 bit applications Created by default with dsnadd Not visible as Engine DSNs in ODBC Administrator running on a Windows client Table19 FAQsAbout ODBC and DSN Support for Linux Question Answer What do need to do about DSNs if port my 32 bit application to 64 bit If the application uses DSN less connections that connect using Pervasive ODBC Client Interface change the ODBC driver description to Pervasive ODBC Interface If the application uses DSNs you must create new DSNs that connect to a named database What is a so called DSN less connection 204 A DSN less connection is one that connects to a named database using the ODBC driver Pervasive ODBC Client Interface for pre existing 32 bit applications or the
72. website for information about particular issues In addition client tracingis available for troubleshooting certain types of low level problems Generally low level tracing isnot required so this type of tracing is intended for use by trained support staff Your product vendor or PSQL Support will explain how to conduct low level client tracing Does garbage collection occur in the data files and indexes For example is space from deleted records recovered or reused Yes space from deleted records is re used on subsequent inserts Space in files is never de allocated back to disk If index balancing is turned on unused space in index pages is also re used See Rebuild Utility Concepts in Advanced O perations Guide Is database shadowing available allowing a complete up to date second copy of the database to exist on another drive or machine PSQL does not contain specific functionality for this but many customers have successfully used hardware mirrored drive arrays and solutions like Vinca s now acquired by Legato Standby Server to provide this functionality Pervasive SQL 2000i SP3 and later supports the data replication product DataExchange What is the mechanism that allows the database to be backed up online What happens if the server goes down in the middle of a backup with many open transactions Continuous Operations allows you to put a set of data files in a special safe mode so that they can be safely backed up whi
73. will be accessed by a 64 bit ODBC application running on a 64 bit client isql64 acctspay TheDSN is also named acctspay and specifies the ODBC driver description Pervasive ODBC Interface The following example connects to a secured database named payroll as user M aster with a password of j77b99 isql payrollsecdb Master 377b99 207 Command Line Interface U tilities TheDSN is named payrollsecdb and specifies the deprecated legacy style ODBC driver description Pervasive ODBC Engine Interface You could also use sql to test the DSN if the DSN had specified the ODBC driver description Pervasive ODBC Interface The following example shows how to process multiple SQL statements Suppose that you want to run the following two queries against the DEM ODATA sample database select count from billing select count from person Create a file named two queries sql for discussion purposes with the two lines Include a blank line as the last line in the file Run the following command cat two queries sql isql demodata b Theresult is as follows SQLRowCount returns 1 1 rows fetched SQLRowCount returns 1 1 rows fetched Note the use of the b option to suppress the prompting information from the output Without the b option the result is as follows Connected sql statement help tablename quit SQLRowCount returns 1 1 rows fetched S
74. with which you could create the same table The ALTER TABLE panel reflects any editing changesto an existing tablethat you make with Table Editor When you save the table changes the ALTER TABLE panel is cleared and the ALTER statement s becomes part of the CREATE TABLE statement The SQL View page is further explained within the context of the tasks that you can perform See SQL View Tasks 126 Table Editor Tasks Table Editor Tasks This section explains the tasks that you perform with Table Editor The tasks are divided into the following categories Category Description General Tasks Orient you to the overall use of Table Editor Columns Tasks Apply to using the Columns page Indexes Tasks Apply to using the Indexes page Foreign Keys Tasks Apply to using the Statistic page SQL View Tasks Apply to using the SQL View page Note You cannot save the changes to the structure of a table if any queriesin SQL Editor are holding the table open Close the SQL Editor holding open the table then save the changes General Tasks General tasks apply to the overall use of the tool Getting Started To start Table Editor for an existing table To start Table Editor for a new table To work with columns To work with indexes To work with foreign keys To view SQL statements applicable to the table Data To view table data To identify tables with changes that have not been saved To save changes fo
75. you remove locations from the list The locations must be on the same server where the database engine is running Specify the location in the same manner as for the dictionary locations General General contains the following property settings Bound Database a Integrity Enforced a Long Metadata V2 metadata Relational Constraints Bound Database Indicates whether or not the database is bound Binding a database prevents the DD Fs or data files from being used in another database and prevents a data file from having two or more different table definitions within the same database For moreinformation about bound databases refer to Bound Database versus Integrity Enforced Integrity Enforced Specifies whether integrity constraints security RI and triggers are enforced on the database These constraints apply to Btrieve access to the data files as well as ODBC SQL access See Setting Up Referential Integrity and Interactions Between Btrieve and Relational Constraints 64 Databases Long Metadata V2 metadata This property is read only and shows whether V2 metadata was specified when the database was created If it was the Long M etadata option is selected Note that V2 metadata is also referred to as long metadata Relational Constraints Relational Constraints displays a matrix that lists the relational constraints in effect for the database See Interactions Between Btrieve and Relational Constrai
76. your data files are not affected Why do I not see in PCC PSQL Explorer the plug in product that I just installed or upgraded See Situations Requiring That You Clear PCC Cache What type of client install should do If you are not sure always select complete This option performs a standard installation which makes it easier to troubleshoot if problems occur How can I be sure what service pack level of client I am running If you areusing PSQL v12 start M onitor or Maintenance choose Help then About from the menu and check the Build Level Is PSQL supported on a Terminal Server Support for both the Server and Workgroup engines on Terminal Server has been available since Pervasive SQL 2000i SP 4 Pervasive SQL 2000i SP 3 provided support for the Server engine Pervasive SQL 2000i SP 2 provided supports only for the client software Can install PSQL in a Failover environment or Can I install PSQL in a Clustering environment Yes PSQL is cluster compatible with M icrosoft Failover Clustering M icrosoft Cluster Service and Linux Heartbeat See the chapter High Availability Support in Advanced Operations Guide Can I install PSQL in a Load Balancing environment That is not supported at this time Can l install PSQL on a server running Btrieve v6 x or earlier No you cannot run PSQL and Btrieve 6 x on the same computer at the same time How do I keep my Workgroup Engine from starting up automatically when I r
77. 0 Credit Hours USMALLINT Dept Name CHAR 20 NOT NULL 3 CREATE INDEX Course Name ON Course Name CREATE UNIQUE INDEX DeptName ON Course Dept Name ALTER Statements When you are editing an existing table the ALTER TABLE statements show what SQL is used to implement edits from the other three Table Editor pages For example suppose that you edit the Course table provided with the sample database D EM ODATA On the Index page you changethesort order for the Name index segment from ascending to descending SQL View shows the following ALTER statements DROP INDEX Course Course Name CREATE INDEX Course Name ON Course Name DESC Saving a table clears the ALTER TABLE statements because no changes are pending 124 Table Editor Graphical U ser Interface Table Editor Graphical User Interface The Table Editor graphical user interface GUI provides work areas called pages for the following Columns Page Indexes Page Foreign Keys Page SQL View Page Columns Page The Columns page can be considered the primary page of Table Editor just as columns and rows are central to tables The following image shows the Columns page of Table Editor The table below the image describes the GUI objects Click on an area of the image for which you want more information See also Columns Tasks for the tasks that yo
78. 203 SS AA S 205 ls a ges WA a VSR we Wo SE nd E ande e oil e a 206 Descriptio ne sonona od a da Fea eae OE a E k 206 SYNOPSIS eks a ee ean YS aa eo Pee eae A Cog a ty Aen Leds 206 OPUONS 7 radioteatro 7 SEERA a ES as 207 COMMAS saree sees a on nthe WE RE De OE a EANA aa a A 207 NOLS aro rat eae a ANE ee tee Sate E Gait ohn Coe Qe EIA 207 El Vouk a oie ta tack esas Ane a Sate a at be Gi 207 SOG ASO wat siee a ad O stated ates 209 licgetauthi anan Stes a ae hr aks hen is se wegen a ie km id Gente kt i Bae 210 DeSeriptiOn ss 4a bats eae dite adh tes ii A i ech PR i ta 210 SYNOPSIS 4 5 oF Ss RA eas eS he Raed dhe Ee Ba a Bede 210 OPTIONS e se Pac cere ate reas E nse pee PAA ae eae Na Bee AE 210 See Alo Sas Sir cas sce ae Me A a 210 PSC ee ie tcl ea abba ise Nee o op la ene wad 211 Descriptiva visa p84 See da A eh a A Ae eae eas 211 SYNOPSIS ass alte O 211 OPTIONS tat Bt cage t os ie a ia dead Sante de te Saha 211 Return Goes 4 34 4 na ee ob Ew eared 6 ahs dg eee EEE EE peed eae bh 212 pregadit Mein a a Spas a te GEA cae 213 Description ca hat 3 lt 0 Noe aaa ls ees li es pon etna cate oe A 213 SYNOPSIS de sense sc a DS Ea OR ek St ye eee tte 4 213 Optio tea ees st ii io DA ice be 213 A A a eee a a ee 214 Descriptions 203 4 ote s tyra ca aah oid tek enol sa arene eee Shek Ertan Gua A mad ak 214 SYNOPSIS Bee a et Eo whe Soa tre ea eae ea ie aa Me Geeta fk os 214 OPUON Seri sacd hide Nah tee ees es ea ak A ee de i a
79. 59 1 CP437 CP1252 UTF 8 CP1250 CP1251 CP1253 CP1254 CP1255 CP1256 CP1257 CP1258 CP737 CP775 CP850 CP852 CP855 CP857 CP858 CP862 CP866 CP932 or EUCJP To list all database names with full information dbmaint 1 a To modify the security policy of the DefaultD B database to M ixed dbmaint m nDefaultDB sMixed 199 Command Line Interface U tilities See Also dsnadd butil 1 btadmin 1 syslogd 1 smb conf 5 Database Code Page and Client Encoding in Advanced Operations Guide 200 Command Line Interface Utility Reference dsnadd Description Dsnadd simplifies the setup of a new ODBC data source to connect to aPSQL database It modifies the odbc ini file by providing the appropriate properties for the new data source PSQL follows the UNIXODBC standard by using the odbcinst ini file in usr local psql etc to specify a 32 bit and a64 bit ODBC driver DSN sthat referencethe PervasiveO DBC Interface driver description point to the odbcinst ini information from the odbc ini file A single DSN can be used by both a 32 bit application and a 64 bit application See also Notes Optionally dsnadd provides options that let you create legacy style DSN s that specify a 32 bit driver namein odbc ini rather than pointing to odbcinst ini However such DSN s are not accessible to 64 bit applications Synopsis a To createa DSN on the server that connects to a named database dsnadd dsn myDSN db DBname T
80. A and click Next In the Log File field type a path to the PSA logfile or browse to the desired location click If you want alogfilethat containsinformation only about the current PSA session clear the Append to log file option You can also specify a different name for the log file but the name applies only for that session of PSA PSA defaults the name to PSALog txt the next time you execute PSA 177 PSQL System Analyzer PSA gt To view the log file at the completion of PSQL System Analyzer Click View Log File to display the PSA log file A summary of the tasks PSA completed is listed for you 178 chapter Command Line Interface _ Utilities Using PSQL Command Line Interface Utilities This chapter discusses command line interface CLI utilities available for PSQL The chapter contains the following sections a CLI Utilities Overview Command Line Interface Utility Reference 179 Command Line Interface U tilities CLI Utilities Overview In addition to providing GUI utilities PSQL provides anumber of command line interface CLI utilities that you can use In most cases these utilities duplicate functionality you can perform with GUI utilities For default locations of PSQ L files see Where arethePSQL files installed in Getting Started With PSQL Platforms that Include CLI Utilities These utilities are provided in the following installations Windows Server Workgroup and a l
81. A logout only disconnects the communication between the database engine and the PCC on your computer In PSQL Explorer expand the Engines node Right click on the server engine from which you want to log out Click Logout name Name reflects the name of the user currently logged in to the server through PCC Nameis anonymous if no specific user name and password were provided for alogin Any nodes expanded for the database engine are collapsed To reconnect to a database engine In PSQL Explorer expand the node in the tree for the server engine To log in to a database engine Right click on the database namein the PCC PSQL Explorer then click Logout name Name reflects the name of the user currently logged in to the server through PCC By default name is anonymous meaning that no specific user name and password were provided for a login Any nodes expanded for the database engine are collapsed Right click on the database name Click Login Type a User Name and Password You can leave these blank to log in as anonymous Click OK 57 Using PSQL Control Center To delete a remote database engine Certain situations such as amachineno longer in use can necessitate deleting a remote database engine In PSQL Explorer expand the Engines node Right click on the remote database engine that you want to delete Click Delete Note that the deletion occurs immediately without further prompting Database Engine Properti
82. CANNOT_ACCEPT_CTRL 32779 BTI_SERVICE_CONTINUE_PENDING 32772 BTI_SERVICE_DATABASE_LOCKED 32780 BTI_SERVICE_DEPENDENCY_DELETED 32783 BTI_SERVICE_DEPENDENCY_FAIL 32784 BTI_SERVICE_DISABLED 32782 BTI_SERVICE_DOES_NOT_EXIST 32785 BTI_SERVICE_DUP_NAME 32776 BTI_SERVICE_EXISTS 32786 BTI_SERVICE_INVALID_CTRL 32789 BTI_SERVICE_INVALID_NAME 32777 BTI_SERVICE_MARKED_FOR_DELELE 32790 BTI_SERVICE_NOT_ACTIVE 32787 BTI_SERVICE_PAUSE_PENDING 32773 BTI_SERVICE_PAUSED 32774 BTI_SERVICE_REQUEST_TIMEOUT 32788 BTI_SERVICE_RUNNING 32771 BTI_SERVICE_START_PENDING 32769 BTI_SERVICE_STOP_PENDING 32770 BTI_SERVICE_STOPPED 32768 212 Command Line Interface Utility Reference psregedit Description psregedit is used to manage the PSQL Registry on Linux You must be root user or a member of the group pvsw to make changes to the PSQL Registry Synopsis psregedit key keyname r key keyname value valuename set key keyname type type value set key keyname value valuename type type value delete key keynam delete key keyname value valuenam export key keyname file filename import file filename Where keynameisin the form PS HKEY Subkey and PS_H KEY is one of the following PS HKEY_CONFIG PS HKEY_CONFIG_64 PS HKEY_CLASSES PS HKEY_CLASSES 64 or PS HKEY_USER Subkey is a subordinate key under a major key valuename is the name assigned to the Registry value or default typeisPS RE
83. Center PCC is an easy to use graphical tool designed to help you create and manipulate databases and control your DBM S It allows you to access nearly all the functions of the product from one place For a tour of PCC see Chapter 3 Using PSQL Control Center Utilities The PSQL database engines come with a variety of graphical and command line tools designed to provide support for testing configuring and manipulating the many features and options provided by PSQL Mostof theutilities run on Windowsand allow remote function to Linux databaseserver engines Tablel Summary of PSQL Utilities Utility name Supported Description platforms Capacity Usage Viewer Windows and Linux Monitors concurrent sessions and data usage for all database engines See Capacity Usage Viewer in Advanced Operations Guide Configuration property dialogs within PCC Windows and Linux Manipulates settings for PSQL client and server components See Configuration Reference in Advanced Operations Guide DDF Builder Windows Allows you to view create and change PSQL data dictionary files DDFs without modifying the underlying data file See Getting Started with DDF Builder in DDF Builder User s Guide Defragmenter Windows and Linux Monitors statistics on data file fragmentation See Monitoring Data File Fragmentation in Advanced Operations Guide Export Table Schema Windows and Linux Exports a schema for a particular table or t
84. Commands add a Add database name del d Delete database name Vist 1 List database names 198 Command Line Interface Utility Reference Options b Create bound database c codepage Set the database code page Zero specifies the server default c 0 i Create database with relational integrity e Do not create dictionary files for database nDBName Specify database name 1dictpath Specify dictionary path ddatapath Specify datapath a Show detail information about dbnames in database list ssecuritymode Specify Btrieve security policy for database Valid choices are Classic Mixed Database Examples To create a database named TEST with relational integrity dbmaint a i nTEST Note Unless a datapath is specified thenew database will bein the default location PVSW_ROOT data Likewise if a dictionary path is not specified the dictionary will be created in the default location To delete the same database dbmaint d nTEST To create a database named mydbase with a database code page of CP 932 o dbmaint a nmydbase c CP932 For the same database to set the code page to the default operating system code page o dbmaint m nmydbase c 0 To see a list of valid code pages specify an invalid code page and dbmaint returns a list of valid ones a dbmaint m nmydbase c xzy Dbmaint returns something similar to the following Bad code page xyz should be ASCII IS088
85. Course Name IN DICTIONARY ON Course Name CREATE INDEX DeptName IN DICTIONARY ON Course Dept Name See also IN DICTIONARY in SQL Engine Reference USING Clause TheUSING keyword allows you to associate a table with a particular data file 77 UsingPSQL Control Center An exported statement looks similar to the following CREATE TABLE Course USING Course mkd Name CHAR 7 NOT NULL CASE Description CHAR 50 CASE Credit Hours USMALLINT Dept Name CHAR 20 NOT NULL CASE y CREATE UNIQUE INDEX Course Name IN DICTIONARY ON Course Name CREATE INDEX DeptName IN DICTIONARY ON Course Dept Name See USING in SQL Engine Reference Plain Statement The plain statement omitsthelN DICTIONARY clause and the USING clause The plain CREATE TABLE syntax is useful to duplicate an existing table by simply changing the table name or to create the same table in a different database An exported statement looks similar to the following CREATE TABLE Course Name CHAR 7 NOT NULL CASE Description CHAR 50 CASE Credit Hours USMALLINT Dept Name CHAR 20 NOT NULL CASE CREATE UNIQUE INDEX Course Name ON Course Name CREATE INDEX DeptName ON Course Dept Name See CREATE TABLE in SQL Engine Reference Exporting a Schema
86. DSNs new applications or revised 32 bit applications should create a DSN that connects to a named database using db For use with deprecated legacy style Client DSNs Name of an Engine DSN on the PSQL Server The following options are typically used only for application development and testing dsn desc desc drv desc drv path drv odbc ini ini 202 dsnadd provides a default DNS description in odbc ini If you want to override the default description use this option to specify a descriptive string of your choosing If the descriptive string contains the space character you must quote the entire string with double quotes dsnadd handles the driver descriptions based on the absence or presence of the db clntdsn and engdsn options If specified must be one of the following Pervasive ODBC Interface e Pervasive ODBC Client Interface e Pervasive ODBC Engine Interface The path where the driver libraries are located The default is usr local psql lib HOME lib ODBC ini file name for example usr local psql etc odbc ini Command Line Interface Utility Reference Examples Thefollowing example creates a server side DSN named acctingdb that connects to a local database named regionlaccting dsnadd dsn acctingdb db regionlaccting Thefollowing examplecreates aclient sideDSN named USI nvoices that connects to a database named DomesticO rders on a remote server named USInvent
87. Depending on the Linux distribution the appearance of PCC may differ but functionality is the same 44 An Overview of PSQL Control Center Installing PCC On Windows platforms PCC isinstalled by default when you install a database engine or a client See PSQL Optional Features in Getting Started With PSQL On Linux PCC isincluded in the full install See Full Installations in Getting Started With PSQL Starting PCC on Windows Access Control Center from the operating system Start menu or Apps screen You may also run the executable file pcc exe Starting PCC On Linux You start PCC by running the executable script file pec from a command prompt The script fileis located by default installation in the usr local psql bin directory We recommend that you start PCC from a command prompt and not by double clicking the script file using a file browser application See Table 6 Troubleshooting Guide for Running PCC Thefollowing requirements must be met to start PCC on Linux Table5 Requirements for Starting PCC on Linux Requirement Discussion PSQL server or client A compatible PSQL server or client must already be installed on the same machine X Server access The xhost command controls which clients can access X Windows on the current machine By default xhost turns on access control This means that only the user who starts X Windows could start PCC You may turn off X Windows client restrictions by typing xhost at
88. E PCC usesit If you wish to create a new database but use existing data files you must know the location of the data files on the server To create up a DSN on a remote machine you must possess administrator rights on theremote machine that houses the database you wish to access You must have OS system rights to create a System DSN on the local machine Setting Up Database Access on Windows gt To set up database access on Windows 1 Follow the steps listed in To register a remote server engine Existing databases with a DBNAM E on the registered server can then be accessed from PCC 2 Optionally follow the steps listed in To create a new database The new database can then be accessed from PCC gt To set up ODBC database access on Windows 1 Follow the steps listed in To register a remote server engine Existing databases with a DBNAM E and a DSN on the registered server can then be accessed from PCC 2 Optionally follow the steps listed in To create anew database and ensure that the Create 32 bit Engine DSN option is selected By default PCC creates a 32 bit system DSN with the same name as the database name The new database has a DSN associated with it and can be accessed through ODBC gt To set up Engine DSNs using ODBC Administrator Note that the ODBC Interface GU Is for 32 bit and 64 bit Windows operating systems are different The one for 32 bit DSNs allows you to specify an Engine DSN The 64 bit interface driver su
89. Ex Pervasive SQL F Local Client Ep Engines E 2 RASPUTIN anonymous E A Databases H DEFAULTDB OE FDEMODATA Master E3 Tables El Ea Views 3 Stored Procedures 9 User Defined Functions H E Triggers H E System Objects 5 8 PYIDEODB Master 2 Inthe Databases list double click DEM ODATA then double click Tables 3 Double click the Dept table from the Tables list Figure8 Selectingthe Dept Tablein DEM ODATA Grid 8 Dept Pervasive SOL eF Local Client E E Engines Accounting B g RASPUTIN anonymous Anthropology E e Databases At H 6 DEFAULTDB E DEMODATA Master Biology B Tables Chemistry E Billing Communication E Class French E Course German Computer Science rl History By default a SELECT FROM query isrun and thetable results are displayed in an active grid as shown in Figure 9 The data displayed in the active grid that loads is updateable That is changes you make to the data in that grid are stored to the database 34 Accessing Data on a Remote Engine U sing PCC Figure9 Displaying the Dept Tablein DEM ODATA File Edit Tools SQL Window Help B Outline 3 Melel E BOSS ed BNE 8 Dept Ta Pervasive SOL aial E Dept x Execute in Grid select from Dept select from Dept 9 Pervasive SQL F Local Client E Microkemel Router Engines E E RASPUTIN anonymous Databases
90. G_STR or PS REG_UINT32 or PS REG_UINT64 value is the value assigned to valuename filename is the name of a file which may include a path Options key Get the key value If value is not specified then the entire key contents are displayed To view all subkeys specify r for recursive set key Set the key value If value is not specified the default value will be set VALUE must be appropriate to the TYPE specified PS_REG_STR is assumed if type is not specified delete key Delete the specified key or value If value is not specified then the entire key and all subkeys are deleted export key Export the given key including all values and subkeys to standard output or to the named file import file filename Import keys and values from either the standard input or from the named file 213 Command Line Interface Utilities psregsvr Description psregsvr is used to register components in the PSQL registry Synopsis psregsvr s u Options 8 y f file filenme 214 f file filename Silent Do not print any status or error messages Unregister If not specified register is assumed Specifies a text file with PCOM modules listed one per line Specifies a single PCOM module to register Command Line Interface Utility Reference pvdbpass Description pvdbpass allows users to change their passwords for secure databases without administrator intervention Synopsis Theutil
91. H M Additional modules TF Additional locations el winsock dll xX Xx wsock32 dll xl a z m I Include subfolders If In Memory is selected PSA searches for all PSQ L components loaded in memory regardless of whether they are located in the PATH or other specified locations If you want to add components to the search list do the following a Click Additional modules if it is not already selected b Type the file name without a drive letter or path in the text box c Click Add Component 4 d If the component you added is not in the path add the path to the searched locations If you want to add more paths to the search locations do the following a Click Additional locations b Click Add Location J c Browse to the location using the directory selector and select the desired location d Click OK e If you also want all directories below the location to be searched click Include subfolders 4 Click Next 5 View thegrid that shows the components found If files of the same name are detected the multiple occurrences are marked with an icon on the left side E If you see multiple occurrences of a component you can adjust the list so that the occurrences sort together To do this click the Module column heading M ultiple occurrences of a file does not necessarily represent a problem with your configuration Their identification can help you troubleshoot issues with components depending on thesituation For ex
92. Have a DSN Available a f you are connecting from a client workstation or from a Workgroup workstation to a server you must haveaClient DSN defined on your workstation for the given remote database Information on how to create a Client DSN is provided in Setting Up Client Access from a Windows Client m If you havea Workgroup engine installed on your computer you may havea DSN defined on your computer for either local or remote databases Information on how to createa DSN is provided in Setting Up Database Access on Windows Note Theinstructions in this section apply only to PSQL v12 not to previous versions Accessing Data Using Microsoft Excel gt To access PSQL data using Excel Ya Tip You must have the PSQ L client or any version of the PSQL engine installed on the computer where you are using Excel 4 Start Excel 2 From the Data menu choose Get External Data gt New Database Query as shown below Figurell Accessing PSQL Data using M icrosoft Excel i all Eile Edit View Insert Format Tools Data Window Help D a SRY amp Ba 024 sot Filter Subtotals Text to Columns ia PivotTable and PivotChart Report Get External Data gt eey Run Saved Query 36 Accessing Data via ODBC From Other Applications 3 TheChoose Data Source box lists the defined data sources for any ODBC drivers that are installed on your computer From this list click on
93. Indexes page of Table Editor is active If required perform the steps for To work with indexes 2 Click the desired index in the Indexes list The Index Segment Details displays listing all the designated index segments 3 Click the desired index segment 4 With the index segment you want to delete selected click Deletein the Index Segment Details Columnslist Index Segment Details Ounique Partial Normal Allow Modifications Columns SortOrder Delete Building_Name Ascending Number x Capacity Ascending p 5 Click File gt Save or El before changing pages within Table Editor gt To arrange the order of index segments 1 Ensure that the Indexes page of Table Editor is active If required perform the steps for To work with indexes 2 Click on the desired index in the Indexes list The Index Segment Details displays 3 Click the index segment you want to reorder 4 Click Up to movethe segment toward the top of the segment grouping or Down to move the segment toward the bottom 5 Click File gt Save or E before changing pages within Table Editor 143 Table Editor gt To specify a sort order for an index For detailed information about sort order see Sort Order in the PSQL Programmer sGuide which is part of the PSQL Software Developer s Kit SDK 1 Ensure that the Indexes page of Table Editor is active If required perform the steps for To work with indexes 2 Click on the desired
94. Oo a A WO N 3 Caution If your database has security turned on and you change from Classic security policy to M ixed or Database all users are prevented from accessing the database until you create database user accounts and privileges for them 84 Groups U sers and Security gt To use an existing database including the pre defined DefaultDB with your PSQL rk OO N files In PSQL Explorer expand the Engines node then the Databases node Right click on the desired database then click Properties Click Directories then click New Type a path for the PSQL files then click OK If your files are spread over many directories specify a high level directory that they all have in common You can specify a root level if necessary but doing so includes in the database all PSQL files at the root level and its subordinate directories You do not need to enter every directory just the lowest level directory that is common to all Btrieve files you want to include in the database Turn security on for the database as explained in General Tasks Set permissions for groups and users and explained in User and Group Tasks User and Group Tasks gt To create a new group using PSQL Explorer Note that you cannot add a group to another group 1 2 3 4 5 y a fF Y N Turn security on for the database as explained in General Tasks Expand thenodes for the database Right click the Groups node then click New gt G
95. PSQL v12 User s Guide Guide to Using PSQL ifIctian disclaimer trademarks ACTIAN CORPORATION LICENSES THE SOFTWARE AND DOCUMENTATION PRODUCT TO YOU OR YOUR COM PANY SOLELY ON AN ASIS BASISAND SOLELY IN ACCORDANCE WITH THE TERMSAND CONDITIONS OF THE ACCOM PANYING LICENSE AGREEMENT ACTIAN CORPORATION MAKESNO OTHER WARRANTIES WHATSOEVER EITHER EXPRESS OR IM PLIED REGARDING THE SOFTWARE OR THE CONTENT OF THE DOCUMENTATION ACTIAN CORPORATION HEREBY EXPRESSLY STATESAND YOU OR YOUR COM PANY ACKNOWLEDGESTHAT ACTIAN CORPORATION DOESNOT 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 Btrieve Client Server in a Box and Pervasive are registered trademarks of Actian Corporation Built on Pervasive Software D ataExchange M icroKernel Database Engine MicroKernel Database Architecture Pervasive SQL Pervasive PSQL Solution Network Ultralight and ZD BA are trademarks of Actian Corporation Microsoft MS DOS Windows Windows 95 Windows 98 WindowsNT WindowsM illennium Windows 2000 Windows 2003 Windows 2008 Windows 7 Windows 8 Windows Server 2003 Windows Server 2008 Windows Server 2012 Windows XP Win32 Win32s and Visual Basic are registered trademarks of Microsoft Corporation NetWare and Novell are registered trademarks of N ovell Inc NetWare Loadable M odule NLM Novell DOS
96. QL gt SQLRowCount returns 1 1 rows fetched 208 Command Line Interface Utility Reference See Also dsnadd 209 Command Line Interface U tilities licgetauth Description Thelicgetauth utility is used in thesecond phaseof theoffline authorization process used for authorizing product keys This utility is used in conjunction with clilcadm to complete the offline authorization process Synopsis licgetauth exe output filenameath Options output filename ath The name for the authorization output file See Also clilcadm Considerations For No Internet Access 210 Command Line Interface Utility Reference psc Description Psc stands for PSQL service controller The utility retrieves and sets control information about PSQL services You must have administrator authority to run psc Synopsis pse lt start stop restart query getpolicy gt servicename or psc setpolicy servicename lt automatic manual disabled gt Options A service specifies the name of a program routine or process that performs a specific system function to support other programs particularly at a low level close to the hardware Servicename specifies the name given to the service key in the registry Note that service key name may differ and in most cases does differ from the service display name The options described below are case insensitive start Starts a PSQL service running stop T
97. SQL Server or Workgroup PCC contains separate optionsin theTools menu to start the 32 bit or the 64 bit ODBC Administrator If an ODBC Administrator is already open Windows defaults to it That is if the 32 bit ODBC Administrator is open and you attempt to start the 64 bit one Windows displays the 32 bit version and vice versa This is a limitation of the Windows Operating system not PSQL Figures 4 shows possible DSN configurations 26 Setting U p ODBC Database Access Figure4 ExampleDSN Configurations Engine with Local Data Database Engine Computer Client Server Database _ Database Database e Client Client DSN lt gt Name Engine Data Files Client Computer or Remote Server Computer Server Computer with Client Application Servers and Clients PSQL servers are also clients The client components of PSQL are installed with every Server engine or Workgroup engine So you can use your server machine to connect to other servers as a client PSQL clients can connect to remote machines where a PSQL Server engine is installed Data Source Names TheODBC client server architecture calls for the naming of each specific data set so that it can be referred to by a well known name There are generally three ways to create DSN s 1 CreateaDSN from the server console 2 CreateaDSN remotely from a client machine 3 CreateaClie
98. Table Editor is active If required perform the steps for To view SQL statements applicable to the table 2 Position the cursor in the desired statement view CREATE Statement or ALTER Statement Ya With the mouse select the desired text Press and hold the right mouse button then drag across the desired lines Tip You can press Ctrl A to select all of the text Right click then click Copy or press Ctrl C To maximize or restore view of SQL statements Ensurethat the SQL View page of Table Editor is active If required perform the steps for To view SQL statements applicable to the table For thedesired statement view CREATE Statement or ALTER Statement click theicon in the upper right corner of the view Icon Action Maximizes the statement view O Restores the statement view to its size prior to maximizing i 147 Table Editor 148 SQL Editor A Tour of the SQL Editor Thetopics in this chapter include m SQL Editor Concepts Working with Common SQL Objects SQL Editor Used in SQL View Tab of Table Editor SQL Editor Tasks chapter 149 SQL Editor SQL Editor Concepts This section contains the following topics m SQL Editor Concepts Working with Common SQL Objects SQL Editor Used in SQL View Tab of Table Editor SQL Editor Tasks Overview SQL Editor is one of the editors within PSQL Control Center PCC The editor a
99. UIdGs as std was Rabie dg woh EE Ee pe are e 13 Advanced Operations Guide 2 o oo 13 SQL Engine Reference aoaaa eee 13 Status Codes and Messages oo o ee e 13 Additional Informati0N o ooo 13 File System Security 54 02 sa ade et yea ad SORE eee eed BRE SR oe ae ae 14 2 Using PSOL lt a a Busi are ara wes eno E 15 A Walk through of Basic U ser Tasks Starting and Stopping the Database Engine 1 cc ee 16 Starting and Stopping the Server Engine on a Windows Server 16 Starting and Stopping the Workgroup Engineon Windows 18 Starting and Stopping the DatabaseEngineon Linux ce ee ee 19 Granting Administrative Rights for the DatabaseEngine 0 cee ee ee 20 Tasks Requiring Administrative Rights 6 es 20 How Administrative Rights are Granted 6 es 20 Rights Within an Active Directory Environment saaa a ees 20 Rights Provided to non Administrative Users oaaae es 21 Tasks for Granting Administrative Rights aoaaa a 21 Granting Administrative Rights on a Windows Server aoaaa ee eee eee 21 Granting Administrator Rights on LINUX saaa o ooo 24 Loggingin as Administrator on any platform aaa es 24 Setting Up ODBC DatabaseAccessS 1 oaa 26 ODBC Standards siriar a r A eee Oe A E eG EY ere ROO 26 Servers ad Clients oana aaa 27 Data SourceNames 2 1 o ooo 27 Internal DatabaseName oaaae 27 A
100. a terminal window Java Runtime Environment JRE The JRE components required to run PCC are installed as part of PSQL PCC uses the local version of the JRE installed as part of PSQL If you have met the requirements to run PCC and still are having difficulty running the utility refer to the following troubleshooting guide Table6 Troubleshooting Guide for Running PCC Troubleshooting Condition Discussion You receive the error This error typically occurs if you try to start PCC by double clicking the java lang UnsatisfiedLinkError script file using a file browser application Start PCC from a command prompt This error can result if the LD_LIBRARY_PATH variable is not set The PCC script sets this variable for you You may also explicitly set the variable with the following command export LD LIBRARY _PATH LD_LIBRARY_PATH usr local psal lib 45 Using PSQL Control Center Table6 Troubleshooting Guidefor Running PCC continued Troubleshooting Condition Discussion You receive the error SWT no more handles when trying to run PCC as root or as user psql You are not required to log in as user psql or root to run PCC However if you are neither of these users you must be a member of group pvsw The SWT no more handles error is caused by X Server denying a connection to a client Before switching to user psql or root open a console window and type xhost to allow other clie
101. ables or at the database level for all tables at once See Exporting a Schema Function Executor Windows Executes Btrieve operations enabling you to learn how the MicroKernel Engine works or test and debug an application Testing Btrieve Operations in Advanced Operations Guide Gateway Locator Windows Used to configure and maintain gateway locator files for the Workgroup engine See Setting Up a Gateway Configuration in Getting Started With PSQL Import and Export Data Windows and Linux Imports data into or exports data from a table See Importing Data with Import Data Wizard and Exporting Data with Export Data Wizard License Administrator Windows and Linux Manages PSQL license keys See License Administration Maintenance Windows and Linux Performs common PSQL file and data manipulations such as importing and exporting data BUTIL is the command line version See Manipulating Btrieve Data Files with Maintenance in Advanced Operations Guide Monitor Windows and Linux Monitors server engine activity Useful for database administration and programming diagnostics See Monitoring in Advanced Operations Guide Notification Viewer ODBC Administrator PSQL Control Center Windows and Linux Windows Windows and Linux Displays messages logged by the licensing components Notification Viewer provides two interfaces system tray icons and a graphical user interface GUI See Not
102. acity Usage Viewer ziriaren i ai titted A og sd 59 MONO coa tices ues Se Bt ap deri iS rE Goh tig Bgl ee asus a aks Gee a S 60 Defragmenter o oe s aoa a i e a eee 61 Databases Ait hota te tnt Dra ld ld eet thee ed ws 62 Database Properties 3 it AA RE OR Pee e E 62 New Database GUI Reference 2 ee 66 Creating M odifying Deleting and Repairing PSQL Databases cee ee 68 MAD C55 ce Fh A eaten EN Aches toby Math aS ha ete A 72 DataTable ee de Bie a a ed Ao eed ela wean ea a a ela as 72 System ADIOS sis a aos gra e ar E Bra a ley as RATE aly Sy a wee 72 Table Properties ee 72 Creating Importing and Exporting Data o a aaa es 75 Creating Data Through POC i airia aned a a aN a e A a aS A E 75 Importing Data with Bulk Data Utility a a aaa ees 75 Importing Data with Import Data Wizard aoaaa ooo e 75 Exporting Data with Export Data Wizard aoaaa es 75 Metadata aia oo ten ua ee aay Ae eu nts te i eRe a dico 77 IN DICTIONARY Clause a 77 USSIIN Gi C1 AU ee Ge sea atten ge eh jects e Dade tte Se iso tee I ae aud tated 77 Plain Statement ioei o i a i a ee a d 78 Exporting a Schema sites eae a a Ae eee dae i a eed ee ea 78 Triggers Stored Procedures User defined Functions and VieWS a aaa 80 Groups Users and Security aooaa 81 Security Tak Sica a deta te Sa als 81 Configuration acc Ae SY A A dd t 89 Contents Dialogs for File Open and File Save aaa ee 90 Wide Character Data S
103. actional Engine Started Automatic Bs Plug and Play Manages d Started Automatic a Print Spooler Loads files Started Automatic Sy Protected Storage Provides pr Started Automatic Sa QoS RSVP Provides n Manual Sy Remote Access Auto Connection Ma Creates a Manual Sy Remote Access Connection Manager Creates a Manual Sa Remote Procedure Call RPC Provides th Started Automatic 2 Right click PSQL Relational Engine and select Stop Right click PSQL Transactional Engine and select Stop gt To start the database services on a Windows server by using Net Start or PSC 1 Run oneof the following commands at the operating system net start Pervasive SQL lt transactional relational gt psc start Pervasive SQL lt transactional relational gt 17 Using PSQL gt To stop the database services on a Windows server by using Net Stop or PSC 1 Run oneof the following commands at the operating system net stop Pervasive SQL lt transactional relational gt psc stop Pervasive SQL lt transactional relational gt Starting and Stopping the Workgroup Engine on Windows gt To start the Workgroup Engine as an application on Windows These steps assume that the Workgroup Engine was installed as an application See Installing the PSQL Workgroup for Windows in Getting Started With PSQL 1 Click Start Workgroup Engine from the operating system Start menu or Apps
104. airing PSQL Databases Tables Creating Importing and Exporting Data m Triggers Stored Procedures User defined Functions and Views Groups Users and Security Configuration a Dialogs for File Open and File Save a WideCharacter Data Support for Import Data Export Data and Export Schema 43 Using PSQL Control Center An Overview of PSQL Control Center PSQL Control Center PCC is an integrated framework in which users can connect to PSQL engines set up and modify databases and tables query and update data tune engine performance and access the PSQL documentation library PCC uses a file explorer like motif a tree of objects referred to as the PSQL Explorer This tree of objects can be opened or expanded to reveal more detail Examples of objects include engines databases tables and users The following figures illustrate PCC with several window views displayed The PSQL Explorer is the tree view on the left Figure17 PSQL Control Center on Windows Platforms File Edit Tools SQL Window Help BReRaReeF Sl Sle Ta PSQL O Billing 23 A BE Outline 23 a a a PSQL a select from Billing a Es Billing amp Local Client select from Billing 4 amp Services 33 PSQL Relational Engine 83 PSQL Transactional Engine a cl Engines a El vm win7 ent 64 anonymous a of Databases DEFAULTDB q a DEMODATA Master a Tables Billi
105. al database name A database under Databases node To repair a database name and associate copied tables with a new database 49 Using PSQL Control Center Table8 Right click Tasks for Objects in PSQL Explorer 50 node or a group Working with Task Right click For information see Schemas Export database schema A database under Databases To exportall table schemas at Tables node once Export table schema A table under Tables node To export a table schema for a particular table Add table A database under Databases To start Table Editor fora new performs a SELECT from node or table table name query a table under Tables node Open table A table under Tables node To open a table Edit table A table under Tables node To start Table Editor for an existing table Import table A table under Tables node To import data into a database table Export table A table under Tables node To export data from a database table Delete table A table under Tables node To delete a table Users Add user A database under Databases To create a new user using Groups node or PSQL Explorer a user To assign a user to a group using PSQL Explorer Add group A database under Databases To create a new group using PSQL Explorer Delete user or group A user or group To delete a group or user using PSQL Explorer Table8 Right click Tasks for Objects in PSQL Explorer An Overview
106. al information Table 14 Description of Common SQL Objects in PCC Object Description Related Information Triggers A type of stored procedure that are automatically executed when CREATE TRIGGER in SQL Engine data in a table is modified with an INSERT UPDATE or Reference DELETE Common SQL Object Tasks Stored A collection of one or more SQL statements that can take and CREATE PROCEDURE in SQL Engine procedures return user supplied parameters Reference Common SQL Object Tasks User defined A scalar routine that returns a value CREATE FUNCTION in SQL Engine functions Reference Common SQL Object Tasks Views A database object that stores a query and behaves like a table CREATE VIEW in SQL Engine Reference Common SQL Object Tasks 155 SQL Editor SQL Editor Used in SQL View Tab of Table Editor SQL Editor is also used in the SQL View Page of Table Editor See SQL View Page 156 SQL Editor Tasks SQL Editor Tasks This section explains the tasks that you perform with SQL Editor The tasks are divided into the following categories Category Description General Tasks Orient you to the overall use of SQL Editor Execution Tasks Apply to the running of SQL statements Grid Tasks Apply to using the Grid Text View Tasks Apply to using the Text window Outline View Tasks Apply to using the Outline window Common SQL Object Tasks Apply to triggers stored procedures user defined
107. ample if you seetwo PSQL components both marked with the same version you may want to check which is loaded in memory and that the components are located where you expect 6 Click Next 7 Click Finish if you are finished using PSA or click View Log File to view the log Test Active Installation Tasks gt To test your network 1 Start PSA and click Next 2 Onthe Options dialog click Test active installation if it is not already selected 175 PSQL System Analyzer PSA 176 Click Test network communication if it is not already selected Click Next For Target machine type the machine nameor TCP IP address of the machine you wish to test You may also type localhost for the local machine The machine can be the machine where you are currently located or aremote machine If you want to control how the tests are run click Advanced Settings and complete the following steps a By default PSA usesthefirst available protocol You can force PSA to useoneor more protocols by clicking Use Only These Protocols b Select the protocol options that you want PSA to test Note that the NetBIOS protocol is not supported on PSQL Server The SPX protocol is not supported on PSQL Workgroup c Specify the number of stress test messages to send by typing a value for test messages The default is 75 After successfully connecting using any protocol PSA sends stress test messages to ensure that the connectivity is functioning correct
108. an also add users and groups with PCC See Users and Groups in Advanced Operations Guide If security is not important to you you can avoid creating many users and assigning privileges by granting access to PUBLIC which means anyone on your network can access the data You can use this statement GRANT ALL ON my table ownername TO PUBLIC Is there a client side requester for the Relational Engine Thereis no DOS requester support for SQL applications but the PSQL Client software for Windows includes ODBC client components allowing you to connect to aremote server engine Is ODBC the only method of access for PSQL Definitely not In addition to ODBC and the time tested Btrieve API you can also develop applications using our OLE DB provider our JDBC driver our pureJava interface or our ActiveX controls 236 Frequently Asked Questions Is there a single database file housing all the data data definitions stored procedures security table relationships and so on as in some other products No PSQL stores datain separatefiles onefile per relational table definition The meta data such as data definitions user group definitions and so on arestored in a set of DDF files where each fileendsin the extension ddf Does the Relational Engine have scheduler capabilities to run stored procedures or other types of scripts designed to access and affect data The Relational Engine does not have a scheduler Upgrading f
109. anced Operations Guide 180 CLI Utilities Overview Utility Description GUI Available Windows Linux Server Client Server Client btadmin Creates and administers database No No No Yes No user names and passwords butil Repairs and manipulates data files Maintenance Utility see Yes Yes Yes Yes Manipulating Btrieve Data Files with Maintenance in Advanced Operations Guide clilcadm Applies and administers user licenses License Administrator see Yes No Yes No License Administration w64clilcadm Windows 64 bit clilcadm64 Linux 64 bit clipaadm Phone Authorization Utility used to PSQL Phone Authorization Yes Yes Yes Yes i authorize product keys when internet Wizard w64clipaadm connection is unavailable Windows 64 bit Phone Authorization Only clipaadm64 Linux 64 bit dbmaint Creates and administers named No No No Yes No databases dsnadd Creates and administers Engine DSNs No No No Yes Yes on the server isql Allows you to run SQL statements No No No Yes Yes interactively and test connectivity to a isql64 Linux DSN 64 bit licgetauth Transmits Authorization Request Data No Yes Yes Yes Yes and retrieves Authorization Key Data w64licgetauth Windows 64 Offline Authorization Only bit licgetauth64 Linux 64 bit Notification Notification Viewer provides two Yes Yes No Yes No Viewer interfaces system tray icons and a graphical user interface
110. ank truncation is applicable only if the Variable Records statistic is Yes and Data Compression is set to No Record Compression Shows whether record compression is enabled If it is the MicroKernel compresses each record it inserts into the data file See Record and Page Compression in Advanced Operations Guide Key Only File Shows the name of the key only file for the table if any A key only file contains no data records but serves as an index to other Btrieve files Index Balancing Shows whether balanced indexing is enabled FreeSpace Threshold Shows a percentage 5 10 20 or 30 if the data file has a free space threshold The database engine stores the variable length portions of records on their own pages called variable pages separate from the fixed length portions which are stored on data pages The database engine uses the threshold to determine whether to add data to an existing variable page or to create a new one A higher free space threshold reduces fragmentation of variable length records across several pages but uses more disk space Uses Alternate Collating Sequence Shows whether the table uses an alternate collating sequence for sorting System Data Key Shows whether the data file has system data keys enabled Page Compression Shows whether page compression is enabled See Record and Page Compression in Advanced Operations Guide gt To view properties o
111. any temporary licenses that have not expired gt To Determine the Data In Use Limit 1 2 Ensure that the server name in the title bar is the server for which you want to determine a data in use limit If not select a different server as described in To Select a Server for LicenseAdministration For a particular product total the data in use values that appear in the License Information list in the Data In Use GB column Exclude any values for expired temporary licenses For example total all values for the permanent license all data in use increases and any temporary licenses that have not expired The sum is the maximum permitted amount in gigabytes for al concurrently open data files for that particular product Note An easier way to determinethe data in use limit is with M onitor See Monitoring Resource Usage in Advanced O perations Guide The M aximum value for Data in Use MB is the data in use limit for the permanent license all data in useincreaselicenses and any temporary licenses that have not expired Be awarethat License Administrator uses gigabytes GB as the units because that is how data in use is associated with a key Monitor uses megabytes M B as the units for resource usage and kilobytes KB for the size of an individual file The different contexts require units appropriate for each context gt To Refresh the License Information List 1 Ensure that the server name in the title ba
112. apacity requirements select a time period that includes maximum and minimum licensing requirements For example if your business has quarterly cycles include at least 90 days worth of data By reviewing data usage usage over time you can ensure that the license capacity meets or exceeds your peak usage requirements Refer to Capacity Usage Viewer for detailed information about how to access and use Capacity Usage Viewer Note Monitor can also help you estimate session count and data in use See M onitor in this guide and M onitoring in Advanced Operations Guide Behavior When Data Use Limit ls Reached The following summarizes the expected behaviors when the limits are reached for data in use m Thedatabase engine denies requests for new file opens and returns status code 161 to the application Status code 161 is returned if the file to be opened exceeds 110 of the datain use limit A slight buffer above 100 is provided because an increase in data in use can also result from the growth of currently open data files Subsequent opensto an already open data file are permitted That is if a fileis already open and data in use reaches or exceeds its limit other users can still access the open file m Notethat datain use also increases if an open file increases in size Operations on an already open file continue to be permitted even if the size of the open file increases beyond the data in use limit 99 License Administration
113. aphical User Interface m License Administrator Command Line Interface a License Administration Tasks 97 License Administration License Administration Concepts This section discusses the following topics a LicenseModels a License Enforcement Message Logging License Models ThePSQL products use different license models The concurrent user mode limits the user count and the capacity based model places limits on the data in use The license model used is dependent on the product Concurrent User License Model With the concurrent user license model each product key specifies a licensed user count A user count allows the specified number of concurrent connections to the PSQL database engine Users are counted by network address The IP address is used for TCP IP the IPX address is used for SPX IPX Each computer that accesses PSQ L asa client session counts as oneuser M ultipleapplicationson asingle client computer are counted as one user not separate users Internally PSQL assigns each machine a serial number and all connections with the same serial number are recognized as coming from the same machine A machinewith multiple NICs for example isrecognized asthe same machine Each Terminal Server session also counts as one user Collectively all applications that access the database engine usethe same network protocol and address and run on the same machine as the database engine count as one user A user is counted
114. arameter file rbldcli command file Options For a complete discussion of the rebuild utility see Command Line Parameters in Advanced O perations Guide See Also Advanced O perations Guide for more information about the Rebuild utility 219 Command Line Interface Utilities 220 Basic Troubleshooting How to Identify and Solve Common Problems chapter This chapter provides information for troubleshooting and resolving the most commonly encountered problems General Troubleshooting Error Messages from PCC Frequently Asked Questions 221 Basic Troubleshooting General Troubleshooting This section provides some basic troubleshooting procedures to help you rule out possible causes for situations you may encounter This section covers the following topics a get Error 1114 when trying to access my data m getan error about ServerDSN or DBQ was not found in the connection string get a message about Engine components version is different than my client components version can t get to my data on the server engine PCC runsslowly or hangs when retrieving large record sets get Error 1114 when trying to access my data or get an error about ServerDSN or DBQ was not found in the connection string PCC can access remote server data sources DSN s using connections without client DSNs M any desktop applications such as M icrosoft Excel and M icrosoft Access cannot do th
115. as not changed To confirm the change open the X File system table and look at the Xf Loc field for the given user table If you cannot see the system tables in PCC expand System O bjects You can also usethe ALTER TABLE USING statementin SQL to changethe data file used by a particular table Refer to SQL Engine Reference for further information What is the best way to ensure that my data dictionaries DDFs are safe Always keep a backup copy of your DDFs Anytime you make changes to the runtime DDFs be sure to make a backup copy of the DDFs before making changes If you areturning on database security for the first time you should make a backup copy of the dictionaries without security and a backup copy with security How can I tell whether I have non standard DDFs If you can edit your DDFswith a Btrieve utility it means that you do not have standard dictionary files A standard dictionary file does not permit direct Btrieve access This lock out is a safety feature that ensures only the Relational Engine can write to the dictionary DDFs are very special files that must remain synchronized with each other and with the data files at all times Standard dictionaries do not have case sensitive table names or field names That is the column definitions for column Xf Namein file ddf and column Xe Name in field ddf have the Case flag set meaning the values are case insensitive DDFsare Btrieve files and thus can be opened and viewed not
116. as only remote internet access you can authorize remotely Ya Tip See To Authorize a Key for information on online authorization gt To Authorize a Key Remotely Note that the GUI License Administrator can also be used for remote authorization if you connect to a remote database engine 1 Atacommand prompt enter the following command clilcadm a key lt server s gt servername lt username u gt username lt password p gt password If the machine where PSQL isinstalled does not have internet access offline authorization is available gt To Deauthorize a Key Remotely 1 Atacommand prompt enter the following command clilcadm d key lt server s gt servername lt username u gt username lt password p gt password Va Tip When you deauthorize a product key all increase keys user count session count or data in use associated with that product key are automatically deauthorized as well gt To Authorize a Key Offline Only consider offline authorization if the machine on which you are running PSQL does not have internet access Offline authorization is completed in three phases using two machines M achine A does not have connectivity and machine B does have Internet connectivity In this example machine A is the machine that needs to authorize the PSQL product key Phase 1 Generate Authorization Request Data 1 AtmachineA without Internet connectivity enter the following at a command p
117. ation assumes you are familiar with the basics of using a computer such as clicking and dragging opening and saving files If you need assistance with these tasks please consult the documentation that came with your computer and or operating system The viewer for the user documentation is integrated into PSQL Control Center PCC Access the documentation through the PCC interface on the Welcome view in the Help menu by pressing F1 Windows or Shift F1 Linux Thefollowing is a summary of the most commonly used books in the documentation library The library contains other books all of which can be accessed through the PCC interface as explained above Getting Started Getting Started with PSQL helps you get PSQL running with installation setup and troubleshooting information Getting Started With PSQL covers the following topics Preparing to install PSQL v12 a Installing PSQL v12 m Upgrading from previous versions of PSQL or Btrieve a Configuring PSQL v12 Troubleshooting your PSQL v12 installation What s New What s New in PSQL provides an overview of the new features and changed behavior for the current release of PSQL relative to the most recent previous release This book provides summary information on new features and directs you to the locations in the documentation where the new features are fully discussed 12 Using PSQL Documentation User s Guide PSQL User s Guideintroduces PSQL and describes comm
118. bc org For further information on this project contact its current maintainer Nick Gorham nick easysoft com A copy of the GNU Lesser General Public License LGPL isincluded on the distribution media for this product You may also view the LGPL at www fsf org licensing licenses Igpl html User s Guide December 2014 Contents Contents About This Manual o xi Who Should Read This Manual o ooo xii CONVENTIONS ani are e a a ir aac Go ti oS OE eek ik ws wa eed Sah ok Bah oe xiii 1 Introducing PSQL ici a RA EA 1 Understanding PSQL and its Capabilities Understanding the PSQL Database Management System o ooo oo 2 WihatiS a Database ca stats do a l a da ta A hs we ta IES oe 2 What is a Database Management System 2 o o 3 Components of PSOL aur tine s airia A a E Lee 3 Key Concepts e a ee Re a ee a 6 Basic Database Structures and Terms aoaaa a 6 Unique Benefits of PSOL su oae e A Wo Fd a a Ea i a a 9 Understanding the DBM S Products oo oo 10 PSOL Workgroup ay os 2 i eai n aw ara bed be dae Pha oe E ed 10 PSOE SIVE diet a acid nod Spon th iste see e Oe TEA ee Bonne Mave SEE 4 10 PSOE VAS fcr are ra Lacan ee tes at so a a aad ware ee ale aid 10 Crystal ReportsforPSQL aoaaa ooo 11 UsingPSQL Documentation 0 ee 12 Getting Started ind An sean aed ads Macey an a tha Phd Ad es 12 What SNC ia std a awe St Sten a gen ie a e ane ees 12 Users G
119. complete set of online documentation For more information about the documentation see Using PSQL Documentation Introducing PSQL Key Concepts This section explains some basic concepts of databases and some of the key concepts that distinguish PSQL from other database products Basic Database Structures and Terms M ost database management systems in use today share a common set of basic structures This section briefly explains those structures The descriptions that follow refer to the diagram below Table2 ExampleTable for Telephone Directory Phone Book Table Column 1 Column 2 Column 3 Column 4 Col Names Name Address Zip Phone Row 1 Fred Black 643 Oak 12346 555 2345 Row 2 Jane Doe 112 Elm 12345 555 1212 Row 3 John Doe 112 Elm 12345 555 1212 Value The most basic element of a database is a value A valueis one piece of data one characteristic for a specific entity For example in the diagram the name John Doe or the phone number 555 1212 is a value Column or Field Another element is a column or afield A column represents a characteristic with no specific value Columns generally have names that describe the given characteristic For example in thetelephone book Name and Phone are columns They do not have specific values unless you look up a particular person Field is sometimes used to refer to the generic characteristic of a specific row For example someone might point to a specific box
120. count A user count allows the specified number of computers to connect to the PSQL database engine concurrently PSQL identifies 232 Frequently Asked Questions machines by serial number so all connections with the same serial number are recognized as coming from thesamemachine A machine with multiple N ICs for example isrecognized as the same machine Each workstation that accesses PSQL as a client session counts as one user Multiple applications on a single client computer are counted as one user not separate users Each Terminal Server session also counts as one user PSQL uses one user count for each unique incoming protocol from the same client computer session If one application uses TCP IP and another application uses SPX two licenses are counted if both applications run on the same machine If different address formats of the same protocol are used only one user is counted For example if one application uses Pv4 and another uses Pv6 only one user is counted if both applications run on the same machine IPv4 and IP v6 arejust different address formats of the TCP IP Only oneengineis ever permitted to access data files at a time The second engineto try to open thefiles gets locked out because the engines open the data files in exclusive mode non file sharing so that corruption cannot occur Does the Workgroup engine use concurrent or per seat licensing Concurrent Refer to Concurrent User License M odel am think
121. ct and select configuration properties Properties System Register a server to work with The PSQL node To register a remote server a remote database engine engine The local engine is You can right click any object automatically registered to subordinate to the PSQL root PCC when you install PSQL to work with a remote engine Work with PSQL services on The Services node if To start or stop services Windows machines applicable to the installation To set services startup policy Log in out database engine A database engine under To log in to a database engine Engines node To log out from a database engine Monitor concurrent sessions A database engine under To access Capacity Usage and data usage Engines node Viewer in Advanced Operations Guide Monitor certain activities and A database engine under To access Monitor for a attributes Engines node database engine in Advanced Operations Guide Delete a remote database A database engine under To delete a remote database engine Engines node engine Databases Create The Databases node To create a new database This task can also be performed from any object subordinate to the Engines node Delete A database under Databases To delete a database node Log in out database A database under Databases node To log out from and log in toa database See also Groups Users and Security Associate table s with a new database repair database name in tables bound to an origin
122. d copy text from Text view Perform one of the following actions Pressand hold down the left mouse button and drag the cursor across thetext you want to select Right click within the Text view then click Select All Right click then click Copy Outline View Tasks See also To run SQL statements in Outline view gt To minimize maximize or restore Outline view size 1 Click the desired sizing button Button Sizing Action E Minimizes the Outline window Maximizes the Outline window Restores the Outline window to its size before it was minimized Common SQL Objects Tasks Common SQL objects include triggers stored procedures user defined functions and views gt 1 To create a common SQL object In PCC PSQL Explorer expand the Engines node and the registered server nodes to display the available databases Right click on the database for which you want the common SQL object to apply or right click on any of the database s subordinate nodes Click New then one of the following depending on the object that you want to create Function Stored procedure Trigger View 167 SQL 4 5 gt 1 2 3 gt 1 2 3 168 Editor A new SQL Editor is opened that contains a default name for the object on atab Thenameisin the form object_n where object is the name of the object and n is an integer that starts with oneand increments by one For exa
123. ding a database prevents the Interactions Between DDFs or data files from being used in another database and prevents a data file Btrieve and Relational from having two or more different table definitions within the same database Constraints For more information about bound databases refer to Bound Database versus E back to Figure 20 Integrity Enforced Create Specifies whether you want data dictionary files DDF created with the database Dictionary Location dictionary Dictionary files are required for relational SQL access to your data files if the i do ds i By default the dictionary files and data files are created in the same location You back to Figure 20 may specify different locations for these types of files after you create the database Typically the only situation for which you would choose not to create DDF files is when you have an unnamed legacy database for which DDFs already exist and you are now creating a database name for that database Under these circumstances the database engine links the new database name with the pre existing DDFs Table 10 CreateNew Database GUI Elements New Database GU Reference Element Description Related Material Relational Specifies whether integrity constraints security RI and triggers are enforced on Setting Up Referential integrity the database These constraints apply to Btrieve access to the data files as well Integrity enforced as ODBC SQL access
124. does not contain a preview of the literal data Rather it shows an example of what the exported data looks like given the file name encoding and the selected options Write column names in first row and Delimiter The Example inset is updated when a file is chosen and encoding or the options are changed 93 Using PSQL Control Center For example in the following image the dialog is set to export the data for the Course table provided with the DEM ODATA sample database The option Write column names in first row is selected and that Delimiter is set to COLON The Example inset shows this Export Data Course q Enter file name and provide information about the export data Export To C users public export_course sq Browse SQL Query Statement SELECT FROM Course Y Write column names in first row Delimiter COLON gt Encoding windows 1252 mn Change default encoding Example ColumniName Column2Name ColumniRowlData Column2RowlData Column1Row2Data Column2Row2Data Note that the export action trims trailing spaces from the values of the CHAR and NCHAR data types You can access the standard operating system dialog for file selection by clicking Browse See also Exporting Data with Export Data Wizard in PSQL User s Guide Export Schema Dialog Export Schema now allows you to change file encoding and to set a default encoding Note that the Exam
125. dvanced connection attributes click Advanced and make your selections in the dialog box that appears Figure6 Advanced Connection Attributes for Client DSNs Network v Enable Array Fetch Array Fetch Buffer 8 KB TCP IP Port Number 1583 v Default Encoding Translation None OEM ANSI Conversion Automatic See Advanced Connection Attributesin ODBC Guidefor details about the attributes Do not modify the Network or TCP IP Port Number attributes unless you first review the information for them 32 Setting up Database Access with PCC Click OK to accept the attribute selections and to return to the ODBC interface dialog 9 Click OK 10 You can now set up another DSN or exit the ODBC Administrator Setting Up a Client DSN on a Linux Client Use the dsnadd utility See dsnadd 33 Using PSQL Accessing Data on a Remote Engine Using PCC You can use PSQL Control Center to access data on a remote machine on which aPSQL v12 engineis installed a gt Tip You will need to log in as an administrative user on the remote engineto perform most functions This means that you must have full administrator level rights on the remote server or be a member of the Pervasive Admin group defined on the remote machine gt To access data on a remote PSQL engine 1 InthePSQL Control Center double click the remote PSQL Engines node then double click Databases Figure7 Expanding the Databases List for an Engine
126. e clrowner sourceFile O lt owner gt UIDuname PWDpword DBdbname commandFile commandOutputFile copy sourceFile outputFile O lt ownel gt O lt owne2 gt UlDuname PWDpword DBdbname create outputFile descriptionFile lt Y N gt UIDuname PWDpword DBdbname drop sourceFile lt keyNumber SYSKEY gt O lt owner gt UIDuname PWDpword DBdbname endbu lt A sourceFile listFile gt UIDuname PWDpword DBdbname index sourceFile indexFile descriptionFile o lt owne gt UlDuname PWDpword DBdbname load unformattedFile outputFile O lt owner gt UIDuname PWDpword DBdbname recover sourceFile unformattedFile O lt owner gt UIDuname PWDpword DBdbname rollfwd lt sourceFile volume drive listFile gt lt L dumpFile wr dumpFile gt T lt dataLength gt E lt keyLength gt H V O lt ownerList owne gt A UlDuname PWDpword DBdbname save sourceFile unformattedFile Y indexFile N lt keyNumber 1 gt O lt ownerl gt O lt owner2 gt UIDuname PWDpword DBdbname setowner sourceFile O lt owner gt level UlDuname PWDpword DBdbname sindex sourceFile lt descriptionFile SYSKEY gt keyNumber O lt owne gt UIDuname PWDpword DBdbname startbu lt sourceFile listFile gt UIDuname PWDpword DBdbname stat lt sourceFile gt O lt owne
127. e SQL Editor also supports the use of a start end comment block that can span multiple lines 4 Click at the beginning of the line where you want a comment 2 Type or followed by your comment text Thefollowing example shows valid comments SELECT FROM tl This is a valid comment and so is this SELECT FROM t2 This is valid after the sign The following example shows multi line comments SELECT FROM tl single line comment This is a comment block that spans two lines 161 SQL Editor Statements inside this block are ignored SELECT FROM t2 To cancel undo or restore redo typing actions in SQL Editor Perform one of the following actions Click Edit gt Undo or press Ctrl Z to cancel typing actions Click Edit gt Redo or press Ctrl Y to restore typing actions To find text or replace text in SQL Editor Click Edit gt Find Replace or press Ctrl F A dialog appears on which you specify a text string to find or replace To select text in SQL Editor Perform one of the following actions Click Edit gt Select All to select all of the contents of SQL Editor Pressand hold down the left mouse button and drag the cursor across thetext you want to select To perform basic editing functions in SQL Editor Click Edit then click the function you want cut copy paste and so forth Statement Execut
128. e In fact PSQL allows applications to use both engines at the same time to access the same data Engine Access Methods PSQL provides a variety of methods to access data through the engines such as the Btrieve API ADO NET and ODBC Refer to the SDK documentation for all of the access methods Terminology Revisited When the product and the documentation refer to the M icroKernel Engine thetermstable and database are generally not used and data files are referred to directly as such In addition MicroKernel Engine users normally use the terms records and fields rather than rows and columns Introducing PSQL Understanding the DBMS Products PSQL is available in different versions The major differences between the versions are price multiuser features and the licensing model used PSQL Workgroup is the least expensive but it provides support only for small workgroup environments m PSQL Server is designed for maximum scalability in high volume mission critical database applications where there is a dedicated database server The Server engine quickly becomes most economical as you increase the number of users PSQL Vx Server engine has the same capabilities as PSQL Server and its license model is designed to support service bureau software as a service or other multiplexed environments The database engine in all of the versions is fully compatible with any PSQL database and was designed with a common architectu
129. e M aster user password as with all PSQL passwords is case sensitive y Caution If you turn on security besureto specify a password with a significant length Do not leave the password field blank because doing so creates a major security risk for your database See PSQL Security chapter in Advanced O perations Guidefor additional information about security Security Tasks This section contains step by step tasks pertaining to security The tasks are divided into the following categories Category Description General Tasks Orient you to the overall use of security To log out from and log in to a database To turn on security using PSQL Explorer To turn on security using SQL To turn off security using PSQL Explorer To turn off security using SQL Btrieve Security Policy Apply to security policies for the MicroKernel Engine Tasks To set or change the security policy for a database To use an existing database including the pre defined DefaultDB with your PSQL files User and Group Tasks Apply to creating users and groups To create a new group using PSQL Explorer To create a new user using PSQL Explorer To assign a user to a group using PSQL Explorer To delete a group or user using PSQL Explorer To work with groups and users using SQL 81 Using PSQL Control Center Category Description Assigning Permissions Apply to assigning permissions to users and groups Tasks To assign permissions for a grou
130. e 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 typeof SDK access method used by your application an older version requester will not work with thedatabase 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 Actian Corporation does not guarantee that newer versions of client requesters will function correctly with older versions of the engine Therefore Actian Corporation recommends that you avoid the use of newer version client requesters with an older engine 222 General Troubleshooting I can t get to my data on the server engine If you cannot get to data on the server engine your most likely causes are The server computer is down or the network has been interrupted You do not have operating system rights to access the server or you are not logged into the correct network Theclient requester is not enabled The database server engineis not installed or not running The database server is not accepting remote connections The remote database does not have a DSN set up to advertise on the network Thelocal client does not have a DSN to access the server The client or serv
131. e Index Segment Details displays and lists the selected Index Segments Click in the first empty Columns cell then open the Columns list click f Index Editor Indexes Index Segment Details Emyindex Add Ounique O Partial Normal v Allow Modifications Columns SortOrder Building_Name Ascending Number Ascending From thelist click the desired column for the segment 141 Table Editor Note Somedata types such as LON GVARBINARY cannot be used for an index Columns with such data types are not valid choices Thedefault sort order is Ascending If you need a descending sort order select Descending from the Sort Order list for the Column you want changed 5 Continueselecting columns from the list until all the segments are added Note that a column can be selected for an index only once Once selected the column is removed from thelist of choices because the column has already been designated in the index 6 Click File gt Save or El before changing pages within Table Editor gt To modify an index segment For detailed information about segments see Segmentation in the PSQL Programmer s Guide which is part of the PSQL Software Developer s Kit SDK Only the database engine can add an index to an IDENTITY or aSMALLIDENTITY column However you can includean IDENTITY or aSMALLIDENTITY column as part of amultiple segment index See also AUTOINC in SQL Engine Reference 1 Ensurethat the
132. e Set locale to locname mn Limit column display width to n characters v Display verbose explanations for errors and warnings version Display version of unixODBC in use W Wrap results in an HTML table x0xHH Delimit columns with HH where x is in hex For example 0x09 is the tab character Commands Once you arein interactive mode the following commands may be used sql statement A valid SQL statement to execute against the database to which isql or isql64 is connected help tablename Display column information The output is the same as for the dbo fSQLColumns catalog function See dbo fSQLColumns in SQL Engine Reference If tablename is omitted the output is for all tables in the database including system tables quit Exits interactive mode Notes By default isq and isql64 display prompt information when in interactive mode such as Connected sql statement help tablename and quit For redirection and piping of output to afile you may not want the prompt information The b option suppresses the output of the prompt information Isql and isql64 support redirection and piping for input and output In addition both utilities can process a file containing multiple SQL statements Each statement must end with a carriage return line feed The last line of the file must be a blank line See Examples Examples The following example connects to an unsecured database named acctspay that
133. e dialog box that opens confirm that this is a genuine copy of the software product When the repair is done a completion message is displayed To Display License Information Ensure that the server name in the title bar is the server for which you want to administer database licenses If not select a different server as described in To Select a Server for License Administration View the information for the authorized licenses in the License Information list Note You must first authorize a key to display information about the associated license such as user count session count data in use license type expiration date and so forth Once authorized if the license is not what you want you may deauthorize it provided the license it is not a temporary license Temporary licenses expire on their own and cannot be deauthorized See To Authorize a Key and To Deauthorize a Key To Determine a Total User Count Ensure that the server name in the title bar is the server for which you want determine a database user count If not select a different server as described in To Select a Server for License Administration For a particular product total the user count values that appear in the License Information list in the User Count column Exclude any values for expired temporary licenses For example total all values for the PSQL Server product for the permanent license all user count increases and any temporary licenses that hav
134. e list for which you want to create a group or user 4 Click OK 5 In SQL Editor issuethe SQL statement SET SECURITY password where password is the text string you want to use as the password for the M aster user 6 Click SQL gt Executein Text or click in the toolbar See also SET SECURITY in SQL Engine Reference gt To turn off security using PSQL Explorer You must be logged into the computer as an administrator or as a member of the Pervasive Admin Operating system security group y Caution Turning off security allows all operating system users to access the database if database security is Mixed or Database mode Database user names passwords and permissions are retained but not used if security isturn off If security is re enabled the previous user names passwords and permissions take effect again An exception is the M aster user The M aster password is not retained nor re applied _ In PSQL Explorer expand the Engines node then the Databases node Right click on the desired database then click Properties Click Security in the Properties tree Click the Security tab Click Enable Security to clear the option Click OK Database security is now off ao oa A WO N 83 Using PSQL Control Center gt To turn off security using SQL y Caution Turning off security allows all operating system users to access the database if database security is Mixed or Database mode Da
135. e not expired Thesum is the total number of users who can concurrently connect to the database engine on the server Note An easier way to determine the total user count is with M onitor See Monitoring Resource Usage in Advanced Operations Guide The M aximum value for User Count is the total user count for the permanent license all user count increase licenses and any temporary licenses that have not expired To Determine the Session Count Limit Ensure that the server namein the title bar is the server for which you want determine a session count limit If not select a different server as described in To Select a Server for License Administration For a particular product total the session count values that appear in the License Information list in the Session Count column Exclude any values for expired temporary licenses For example total all values for the permanent license all session count increases and any temporary licenses that have not expired The sum isthe total number of sessions that can concurrently connect to the database engine on the server for that particular product the session count limit License Administration T asks Note An easier way to determine the session count limit is with M onitor See M onitoring Resource Usage in Advanced Operations Guide The M aximum value for Session Count is the session count limit for the permanent license all session count increase licenses and
136. eboot You must remove it from the Windows Startup group Check the operating system documentation for Startup PCC frequently asked questions about PCC 231 Basic Troubleshooting How do start PCC on Linux Certain requirements must be met before you can start PCC on Linux See Starting PCC On Linux Security frequently asked questions about security When do I log in using an operating system user and password and when do I log in using a database user and password This may seem confusing at first but in fact there is only one rule use a database login only after you have already successfully connected to the server and are attempting to access a database directly Up until this point you should use an operating system login For example if you run Monitor or Configuration to work with aremote server engine you are prompted for a password In both cases you must supply a user name and password for an operating system account that has administrative permissions on the remote system or an account thatisa member of Pervasive Admin This applies also when you are creating a new database Once you start to work with the data itself then you must supply a database user and password if prompted If database security is turned off then you would never need a database user name or password In this case you would only need an operating system user and password to perform administrative tasks as noted in the preceding paragraph
137. ecific to the graphical user interface GUI Visual Reference The following image shows the GUI The table below the image describes the GUI objects Click on an area of the image for which you want more information GUI Object Description Related Information Server By default shows the name of the server that the License Administrator is To Select a Server for Name accessing License Administration This object is also an entry field into which you can type the name of a server If Return to GUI Dialog you have typed in a server name but not clicked Connect or pressed Enter image the field can contain a server name that differs from the one in the title bar The title bar always shows the server that License Administrator is accessing The Server Name field can also be populated if you click Browse and choose a server Connect Establishes communication between License Administrator and the computer To Select a Server for listed in the Server Name field License Administration Return to GUI Dialog image Browse Allows you to choose a server on which to administer licenses To Select a Server for License Administration Return to GUI Dialog image Key Accepts a key a combination of letters and numbers typed in or pasted License Administration Concepts To Authorize a Key Return to GUI Dialog image Authorize Authorizes the key entered in the Key field ono the specified database engine Authorize a Product T
138. ed 2 Enter the following at a command prompt where authorization_filenamepky is the file created in step 2 of Phase 2 clilcadm a authorization_filename pky PSQL is now authorized gt To Deauthorize a Key Offline Deauthorizing a key offline requiresthat you contact PSQL Support Telephone Authorization In the event that it is not possible to authorize keys online remotely or offline you can authorize PSQL Server PSQL Vx Server or Workgroup using telephone authorization Telephone authorization requires that you have completed a successful installation of PSQL Server or Workgroup and have a valid product key If you do not have a product key you can obtain one from Actian Corporation or your software vendor Telephone authorization is available by calling 1 800 287 4383 toll free in the United States or 00800 1212 3434 toll free in Europe To authorize a key via telephone authorization you can use the command line utility or the PSQL PhoneAuthorization Wizard The PhoneAuthorization Wizard is available on Windows and Linux and guides you through the steps necessary to authorize your PSQL product ga Ao Tip When authorizing the Workgroup Engine you need to elevate the privileges of the database engine before authorizing a key with a licensing utility gt To Authorize a Key Using the PSQL Phone Authorization Wizard 1 Ensure that you have completed a successful installation of PSQL Server or Workgroup and a have a
139. ed by atab on top of the editor Thetab contains thenameof the object Data modified within an editor must be explicitly saved for example with File gt Save Views such as PSQL Explorer can be opened only one at atime Your actions within a view are applied immediately without your having to save them Editor and View Characteristics The following table summarizes the characteristics of the editors and views Table7 Characteristics of PCC Window Views Characteristic PSQL Explorer SQL Editor Grid Text Outline Table Editor Contains icons specific to that view Y Y Y Can be closed Y Y Y Y Y Can be minimized Y Y Y Can be maximized Y Y Y Y Y Can be restored to previous size Y Y Can be opened in new window Y Y Y Y Y Can be rearranged within PCC main window Y Y Y Y Y Can be pulled from PCC and placed on desktop Y Y Y 47 UsingPSQL Control Center PSQL Explorer This view displays a tree of objects of various types and enables you to do many tasks Objects and Their Properties a Right click Tasks Objects and Their Properties Thetree of objects includes a root node named PSQL The root node contains objects such as clients services if applicable database engines databases tables views stored procedures user defined functions triggers groups users and system objects such as system tables M ost objects in PSQL Explorer can be expanded to reveal more detail
140. eferences for SQL Editor click SQL Editor Grid Window View PCC provides a command to execute an individual SQL statement in SQL Editor so that the results appear in the Grid view The command is called Execute in Grid and can be invoked from the SQL menu from a toolbar button or from within the Outline view Note The Grid is also used by Table Editor to show table data when you start Table Editor That is when you right click on a table then click Edit See To view table data Identifying the Grid Window TheGrid window view or Grid for short shows the columns and data in a table The Grid uses a matrix format like a spreadsheet to show theresult of running SQL SELECT statements statements that return data 151 SQL Editor Figure23 Grid Window View ToPervasive SOL 2 01 E Room o BE Outline 33 Ta 5 0 Pervasive SQL E Local Client P Microkemel Router Services 33 Pervasive SQL relat i Pervasive SOL tran E q Engines oy tm Building Name Number Capacity Type Roscart Building Classroom Roscart Building Classroom Wander Stoep Hall Classroom S o Databases CHECKID 1 8 DBASE9 8 DEFAULTDI S DEMODATA Vander Stoep Hall Classroom E E5 Tables Vander Stoep Hall Office El Bilin Vander Stoep Hall Classroom E Clas Wander Stoep Hall Classroom E Cou Vander Stoep Hall Office E Dep Vander Stoep Hall Office a End Woodward Building
141. en a new SQL Editor tab Click OK SQL Editor appears as a new window view in PCC By default PCC names the new document SQLDocn wheren isan integer that starts with 1 and increments by 1 The document name appears in thetab for SQL Editor To start SQL Editor by displaying all records in a table Start PCC if it is not already running See Starting PCC on Windows Expand the Engines and Databases nodes in PSQL Explorer For the desired database expand the Tables node Double click the table for which you want to see all records or right click the table then click Open By default PCC open SQL Editor and executes a SELECT FROM statement for thetable N ote that the SELECT statement can fail depending on user and column level permissions gt To set database context for an SQL query The commands to execute SQL statements are disabled until a database is specified as the context to which the SQL statement applies 1 2 3 Start PCC if it is not already running See Starting PCC on Windows Perform one of the following actions For a new SQL statement click File gt New gt SQL Document or click si The Select D atabase dialog appears Click the database in the list for which you want the SQL document to apply and click OK If SQL Editor already contains SQL statements not associated with a database click g The Select D atabase dialog appears Click the database in the list for which you want the SQL docu
142. enclosed by double quotes and delimited by the TAB character Fred t 22 t 2459 t Sales t TheBDU treats consecutive column delimiters as NULL values If the utility finds consecutive column delimiters it inserts a NULL valueinto the column provided the column is nullable No qualifiers are allowed for a NULL value The following column data inserts a NULL value in the second column Note that qualifiers are not included for that column Fred t t 2459 t Sales t Synopsis bdu database name table name data_file lt e E gt max_errors lt r R gt r get file 185 Command Line Interface Utilities lt f F gt first_row lt 1 L gt last_row lt t T gt fidd_term lt n N gt row_term lt o O gt output file lt u U gt login_id lt p P gt password lt c C gt encoding lt h H gt Note When loading data with BDU into a secured database for which the Btrieve Security policy is set to M ixed the supplied credentials user name and password must match those of a PSQL database user account and an operating system user account Parameters Table17 Bulk Data Utility Parameters Parameter Mandatory Default Value Description Optional database_name Mandatory Database name to connect to the local PSQL engine table_name Mandatory Name of the table to be populated data_file Mandatory Name and location of the delimited text file lt e E gt max_error
143. er network configuration is wrong gt To determine the actual cause of the failure Follow the steps below to rule out certain root causes and narrow down the possible sources of failure 1 From the operating system on a Windows client access the Network information and see if you can find the server computer to which you want to connect If you can see the server you can rule out that the server is down or disconnected from the network 2 Nett try to map a drive to the file server or open a shared file on the server If you can successfully connect to the file server and create a file on the mapped drive then you can rule out lack of operating system rights You can also rule out failure to login to the correct network If you are not logged in to a particular network you cannot access the servers on that network Note If you are trying to create a new database on the server to use M onitor against the remote server engine or to configure the remote server engine you must have administrative rights on the server or bea member of Pervasive Admin A simpledrive mapping or shared file read will not tell you whether you haveadministrativerights This means you may beableto connect to thefileserver but you still may not be able to connect to the database engine with Configuration Monitor or Create Database Wizard 3 Thenext possibility is that the client requester is disabled Start PCC then right click the icon that represent
144. er or member information Each of these isa distinct well defined collection of related information Understanding the PSQL Database M anagement System What is a Database Management System As citizens of the computer age we are surrounded by collections of information databases everywhere we go Unfortunately all this datais of no useto anyone without methods to sort it search it analyzeit and keep it current A database management system or DBM S isa computer program designed to manage large amounts of data and to allow other computer programs and peopleto interact with thedata A DBM Scan also be referred to informally as a database engine or simply an engine A DBMS performs the following tasks Controls access to the data The DBM S can act as a watchdog to prevent the wrong people from using the data Structures the data so it can be interpreted by other applications The DBM S ensures that all the data adheres to the database structure so that other computer programs can work with the data using common methods Keeps the data safe and prevents it from getting garbled or lost The DBM S facilitates backing up the data in case of catastrophic loss and also accesses it in a consistent manner to prevent the data from suffering inadvertent damage Makesit easy to add new information find it update it and delete it The DBM S readily accepts new data and provides tools that you can use to locate update and remove info
145. er s Guide which is part of the PSQL Software Developer s Kit SDK 1 3 gt Ensure that the Columns page of Table Editor is active If required perform the steps for To work with columns Click the option box in the Null cell for the desired column You can allow nulls only for applicable data types A shaded square indicates that null values do not apply to the data type Option State Meaning Nulls specified O Nulls allowed but not specified Ol Nulls do not apply Also see Null Values Click File gt Save or El before changing pages within Table Editor To set case sensitivity for a column Case sensitivity does not apply if the key uses an alternate collating sequence ACS You cannot specify case sensitivity and use an ACS 1 134 Ensure that the Columns page of Table Editor is active If required perform the steps for To work with columns Click the option box in the Case cell for the desired column You can set a collating sequence only for applicable data types A shaded square indicates that case sensitivity does not apply to the data type Option State Meaning Case insensitive O Case sensitive Ol Case does not apply By default PSQL is case sensitive when sorting string keys Uppercase letters are sorted before lowercase letters If you specify case insensitive values are sorted without distinguishing case 3 Table Editor Tasks Click File
146. erminates the running of a PSQL service restart Terminates the running of a PSQL service then starts the service running again query Specifies whether servicename is running or not getpolicy Retrieves the type of startmode automatic manual or disabled associated with servicename setpolicy Sets the type of startmode automatic manual or disabled associated with servicename automatic The service starts automatically when the operating system starts manual The service must be started manually after the operating system starts disabled The service is disabled and does not start after the operating system starts Examples To start the Workgroup Engine service manually psc start psqlWGE To start the Cache Engine service manually psc start psqlCE To stop then restart the PSQL transactional and relational services psc restart psql all 211 Command Line Interface Utilities Note that psql all is a shortcut method available only with the psc utility It affects both the Pervasive SQL transactional service and the Pervasive SQL relational service and can be used with the psc parameters start stop or restart Return Codes Thepsc utility returns the following codes DOS ERRORLEVEL for the state of the service after the psc command has been run String Value BTI_SERVICE_ACCESS_ DENIED 32775 BTI_SERVICE_ALREADY_RUNNING 32778 BTI_SERVICE_
147. es See Configuration 58 Capacity U sage Viewer Capacity Usage Viewer PCC provides Capacity Usage Viewer to monitor concurrent sessions and data usage for all database engines This is especially useful when you are considering migrating between PSQL engines that use different license models See Capacity Usage Viewer in Advanced O perations Guide 59 UsingPSQL Control Center Monitor PCC integrates a M onitor utility which allows you to monitor certain activities and attributes of the database engine The utility can monitor aspects of the M icroKernel Engine and the Relational Engine It presents information organized into a series of tabs that can be rearranged for your convenience with columns of data that can rearranged and sorted It presents a snapshot of a particular moment and can be refreshed either manually or automatically See M onitoring Database State in Advanced O perations Guide 60 Defragmenter Defragmenter PCC provides the Defragmenter utility to enable you to detect data file fragmentation that may be causing loss of engine performance Defragmenter also enables you to correct the fragmentation by rearranging records that have become out of order and removing unused space left by deletions Defragmenting a file does not alter its data in any way and records can be created read updated or deleted while the files that store them are being defragmented You can use Defragmenter features dur
148. es set on my Btrieve files After created a DSN I cannot open the files using ODBC What s wrong If Btrieve files have owner names on them you must use database security for ODBC access Turn on database security in PCC SeeTo turn on security using PSQL Explorer and Owner Names and Security in Advanced O perations Guide lt 3 Caution Do not forget the M aster user password You cannot turn off security or perform administrative tasks within the database without it You may want to makea backup copy of your DDFs before turning security on in case you forget the password Next you must grant the M aster user access to the data files that have owner names defined You can grant the access by issuing this SQL statement for each table that has an owner name GRANT ALL ON my table ownename TO Master When you enter thestatement substitutethe actual nameof your tableand the appropriateowner name for that table as indicated above Remember that each data file corresponds to an ODBC table If you don t know which table corresponds to which data file see use PCC to find out Right click on the table in PCC then click Properties Click Information in the tree The File Name field shows you the file that is referenced by that table definition If security is important then you must create users and assign permissions for all users expected to access the database You do this by using the CREATE GROUP and GRANT statementsin SQL You c
149. es that nulls do not apply to the data type A checked square W indicates that NULL values are Columns Page image allowed for the data type Case Specifies whether the database engine uses case sensitive or To set case sensitivity for a column case insensitive comparisons when searching for character values in the database A checked square W indicates that Columns Page image case insensitive values are used A shaded square indicates that case sensitivity does not apply to the data type Collate Specifies that an alternating collating sequence ACS is used for To set a column collating sequence sorting Contains the path and ACS file name Columns Page image Default Specifies a default value for the column The default value is To set a column default used if you perform an SQL INSERT for a row but do not provide a value for the column Columns Page image Indexes Page Thelndexes page allows you to add and delete indexes The Indexes page is explained within the context of the tasks that you perform for indexes See Indexes Tasks Foreign Keys Page The Foreign Keys page allows you to add and delete foreign keys The Foreign Keys page is explained within the context of the tasks that you perform for foreign keys See Foreign Keys Tasks SQL View Page TheSQL View page displays and allows you to copy the SQL statements used to create or alter thetable TheCREATE TABLE panel displays the SQL
150. essingData o ooo 233 ODBC and DDFS iii of ha Bond el hw baaa 234 Upgradingfrom Btrieve6 15 0 ees 237 Uporadingand Migration Aa dca A eid a Pe A 237 Miscellaneous 22 5 si i A A Ses Boi eh MR ees E ey ia Ee 237 Contents About This M anual This manual introduces you to PSQL utilities for Server and Workgroup products and shows you how to perform the basic tasks necessary to work with the application Topics include starting and stopping the database engine setting up access to a database and accessing data from other applications This manual also gives you a tour of the PSQL Control Center PCC PCC allows you to manage PSQL utilities within a single easy to use framework xi Who Should Read This Manual This manual provides information for users who install and run PSQL client server and Workgroup products Actian Corporation would appreciate your comments and suggestions about this manual As a user of our documentation you arein a unique position to provide ideas that can havea direct impact on future releases of this and other manuals If you havecomments or suggestions for the product documentation post your request at the Community Forum on the PSQL Web site xii Conventions Unless otherwise noted command syntax code and examples use the following conventions CASE Bold Monospaced font variable Commands and reserved words typically appear in uppercase letters Unless yo
151. ete a DSN using ODBC Administrator Windows platforms only In PCC click Tools and select ODBC Administrator In the ODBC Administrator window click the System DSN tab Select the DSN you wish to remove and click Remove You are prompted to confirm removal of the DSN Click Yes After the DSN has been removed click OK to exit ODBC Administrator 41 Using PSQL If you aresimply deleting an unwanted DSN you arefinished If you need to re createthe DSN you should refer to one or more of the following sections If you need to do this Refer to this section Re create an Engine DSN on a Server engine or a Workgroup engine One of Setting Up Database Access on Windows Setting Up Database Access on a Linux Server Re create a Client DSN on a client workstation Setting Up Client Access from a Windows Client 42 chapter Using PSQL Control Center E A Tour of PSQL Control Center PSQL Control Center PCC is a graphical tool for creating and managing databases and controlling your database engine It allows you to access nearly all the functions of the product from one place The following topics lead you on a tour of PCC to learn its interface and common operations An Overview of PSQL Control Center m Services on Windows Servers Database Engines Capacity Usage Viewer Monitor Defragmenter Databases a New Database GU Reference Creating Modifying Deleting and Rep
152. f a table 14 In PSQL Explorer expand the Tables node for a particular database If you are interested in system tables expand the Tables node under System O bjects 2 Right click on the desired table and click Properties Tip You can use the table properties to view a list of the indexed columns for the table gt To open a table 14 In PSQL Explorer expand the Tables node for a particular database If you are interested in system tables expand the Tables node under System O bjects 2 Right click on the desired table and click O pen or Open in New Window Theopen action performsaszLEcT from table name query wheretable_nameis the name of the table that you selected gt To delete a table 14 In PSQL Explorer expand the Tables node for a particular database 73 UsingPSQL Control Center 2 Right click on the desired table and click Delete 74 Creating Importing and Exporting Data Creating Importing and Exporting Data The tables that you create with PCC are initially empty You can add data to them through PCC or by importing data PCC provides a wizard to export data and oneto import data Creating Data Through PCC See To add rows of datato the Grid Importing Data with Bulk Data Utility See bdu in PSQL User s Guide Importing Data with Import Data Wizard Thelmport Data Wizard reads delimited data from a text file and adds the data to a table The wizard allows you to specify t
153. fer you can usethe standard operating system dialogs for FileO pen and File Save For example you may haveno need to work with encodings outside the system code page in which case you may want to use the standard dialogs gt To use the standard operating system dialogs for File Open and File Save 1 In PCC select Window gt Preferences then File Encoding 2 Select the option Do not prompt for encoding during File Open and File Save 3 Click OK Note You can also access the File Encoding preferences from the dialogs for File Open Save File Import Data Export Data and Export Schema Click Change default encoding on the dialog 92 Wide Character Data Support for Import Data Export Data and Export Schema Wide Character Data Support for Import Data Export Data and Export Schema Import Data Export Data and Export Schema now allow you to work with files containing wide character data You can changethefile encoding duringtheimport or export action similar to File O pen and File Save See Encoding Choices The dialog field in which you specify a file path and name defaults to the path location used for the previous data import or export or schema export Import Data Export Data and Export Schema use the same persistence setting A full file path and name is required to enable the import and export actions See also Dialogs for File O pen and File Save Import Data Dialog Import Data now allows you t
154. fies the driver description Pervasive ODBC Interface so either utility can test its DSN connectivity Theutility puts you in an interactive state with the database From that state you can query the database such aS SELECT FROM Department To enable security on a database using sql first connect to the database as the M aster user then use the SET SECURITY statement in SQL to set the M aster user password For example isql DEMODATA Master SET SECURITY password See SET SECURITY in SQL Engine Reference To connect to a secured database passthe user name and password asthe second and third parameters respectively to the sql utility For example to connect to DEM ODATA asuser M aster using password vforge enter isq164 DEMODATA Master vforge Of isql DEMODATA Master vforge Synopsis lt isql isql64 gt DSN UID PWD options Where DSN isthe data source name for the database to which you want to connect Always required UID istheuser nameto connect to the PSQL SQL database enginefor asecured database Required only for asecured database PWD isthe password for UID Required only if UID used options is one or more of the options as defined below 206 Command Line Interface Utility Reference Options b Suppress prompts for batch processing See Notes c Display column names on first row use with d dx Delimit columns with character x 1 locnnam
155. fined by the specific operating system OS such as Windows File Sharing Only the Server engine can enforce OS level file security based on the privileges assigned to the login user name The Workgroup product does not attempt to do this In a small office where Workgroup engines are most common this can be considered a plus because they are usually short on networking experts and the fewer barriers to successful data access the better 14 chapter Using PSQL E A Walk through of Basic U ser Tasks This chapter covers the basic tasks you need to know to work with PSQL databases Included in this chapter are the following sections Starting and Stopping the Database Engine a Granting Administrative Rights for the D atabase Engine Setting Up ODBC Database Access m Setting up Database Access with PCC Accessing Data on a Remote Engine Using PCC Accessing Data via ODBC From Other Applications DeletingDSNs 15 Using PSQL Starting and Stopping the Database Engine Thissection outlines how to start and stop the PSQ L engine Someengine configuration parameters you need to stop and restart the enginein order for a particular change in your configuration to take effect To start and stop the database engine follow the instructions for your engine and platform m Starting and Stopping the Server Engine on a Windows Server a Starting and Stopping the Workgroup Engine on Windows Starting and Stoppin
156. for the desired permission A check mark indicates that the permission applies Click OK gt To assign permissions for a user using PSQL Explorer kh O N 6 Note You cannot assign specific permissions to a user if the user isa member of a group The permissions of the group apply to the user Permissions on the Database tab override permissions on the Table tab Expand the nodes for the desired database Right click on the user name under the Users node then click Properties Click Permissions in the Properties tree Click the tab to access permissions for the desired object database tables and columns stored procedures or views See also Permissions on Views and Stored Proceduresin SQL EngineReference On the tab click the option for the desired permission A check mark indicates that the permission applies Click OK gt To assign permissions to all users using PSQL Explorer hh OO N Note Permissions on the Database tab override permissions on the Table tab Expand the nodes for the desired database Right click on the group PUBLIC under the Groups node then click Properties Click Permissions in the Properties tree Click the tab to access permissions for the desired object database tables and columns stored procedures or views See also Permissions on Views and Stored Proceduresin SQL EngineReference 87 Using PSQL Control Center 88 On the tab click the option for the desired per
157. formation Format 8 is an 8 bit variable width encoding that can represent every character in the Unicode character set lt was designed for backward compatibility with ASCII and to avoid the complications of endianness and byte order marks BOM 53 Using PSQL Control Center Table9 Default Encoding Choices continued Encoding Discussion UTF 8 with BOM Same as UTF 8 except with a byte order mark BOM which is a Unicode character used to signal the endianness byte order written to the file during the save action UTF 16BE A UTF encoding in 16 bit with byte order of big endian BE UTF 16BE with BOM Same as UTF 16BE except with a BOM written to the file during the save action UTF 16LE A UTF encoding in 16 bit with byte order of little endian LE UTF 16LE with BOM Same as UTF 16LE except with a BOM written to the file during the save action Notes By default PCC remembers the encoding of a file that has been opened If you modify the file and save your changes the changes are saved in the original encoding However if you use the PCC File Save As dialog to save your changes the changes are saved to the encoding specified on the dialog PCC automatically detects the encoding of a file if the file contains a BOM and displays that encoding in the PCC File Open dialog If the file does has no BOM and byte order is important you need to select the proper encoding for the save action N
158. functions and views Note If you use SQL Editor to change the structure of table with SQL statements refresh the PSQL Explorer to see the change Right click on the Tables nodein PSQL Explorer then click Refresh General Tasks General tasks orient you to the overall use of SQL Editor To start SQL Editor for anew SQL query To start SQL Editor by displaying all records in a table To set database context for an SQL query To identify editor settings for SQL Editor To create an SQL query or script To open an SQL script To type comments into SQL Editor To cancel undo or restore redo typing actions in SQL Editor To find text or replace text in SQL Editor To select text in SQL Editor To perform basic editing functions in SQL Editor Execution Tasks Execution tasks apply to the running of SQL statements To enable the execution commands and icons for SQL statements To run asingle SQL statement in SQL Editor To run selected SQL statementsin SQL Editor To run all SQL statements in SQL Editor To run SQL statements in Outline view 157 SQL Editor Grid Tasks Grid tasks apply to working with the Grid window To change data within the Grid To add rows of data to the Grid To delete row s of data from the Grid To enter a date time or timestamp data type in the Grid using scalar functions To refresh data in the Grid To copy data from the Grid Text View Tasks Text view tasks apply to working w
159. g on the server that are shared and yet PSQL cannot read them What s wrong amusing SQL queries to create a definition for an old table The resulting record size is off Why 228 Frequently Asked Questions want to convert my data file version from 9 back to file format version 8 7 or 6 How do do this ODBC and DDFs Howcan tell if can use ODBC to access my data files How can a hard coded filepath in a DDF be changed a What is the best way to ensure that my data dictionaries DDFs are safe How can tell whether have non standard DDFs Can mix and match DDFs from different databases What happened to DDF Sniffer a havetwo similar Btrievefiles and created a DDF for the first one Sincethey are similar can use the same DDF on the second Btrieve file have owner names set on my Btrieve files After created a DSN cannot open the files using ODBC What s wrong Istherea client side requester for the Relational Engine ISODBC theonly method of access for PSQL a Isthereasingle database file housing all the data data definitions stored procedures security table relationships and so on asin some other products Doesthe Relational Engine have scheduler capabilities to run stored procedures or other types of scripts designed to access and affect data Upgrading from Btrieve 6 15 a Isthereatool that replaces Xtrieve Upgrading and Migration Upgrading and Migrati
160. g the Database Engine on Linux Starting and Stopping the Server Engine on a Windows Server On Windows server environments PSQL Server runs as services The services are loaded as part of the installation process and are set to be always available if you followed the default installation Services Dependencies Additional PSQL products such as DataExchange also install services DataExchange depends on both PSQL Transactional Engine and PSQL Relational Engine services Thefollowingtablesummarizesthe behavior of the dependent services for start stop and restart actions of the database engine services Notethat the behavior of the dependent servicesisthe sameregardless with which application you start stop or restart the database engine services PCC Windows Services Net Start PSC Table4 Action of Dependent Services Based on Action of Database Engine Services Database Engine Service Start Stop Restart Service Action for Data Exchange PSQL Transactional Engine Y no action Y Stop Y Restart PSQL Relational Engine Y no action Y Stop Y Restart If you start the service for DataExchange the PSQL Relational Engine starts as a prerequisite service Note The dependent services stop without displaying a message that they will be stopped gt To start the database services on a Windows server using PCC See To start or stop services 16 Starting and Stopping the D atabase Engine
161. gt omode lt 0 1 1 4 gt host srv host srv port port translate lt none auto gt Name of the database local or remote to which the DSN is associated The Data Source Name Display syntax and options help for the dsnadd utility List existing DSNs Specify the default file open mode for files opened with the current connection The default is 0 or Normal Can be used only with local connections not remote client connections For more information on file open modes see DSN Open Mode in ODBC Guide Server host name Server port number The default is 1583 See also Changing the Default Communication Ports in Getting Started With PSQL Encoding translation to use for character data The default is none meaning that no character data is translated between the client and server on the assumption that the client and server use the same operating system encoding See Automatic in ODBC Guide The following options are used to create deprecated legacy style DSNs clntdsn engdsn sdsn Create a deprecated legacy style Client DSN with the driver description Pervasive ODBC Client Interface Rather than using Client DSNs new applications or revised 32 bit applications should create a DSN that connects to a named database using db Create a deprecated legacy style Engine DSN with the driver description Pervasive ODBC Engine Interface Rather than using Engine
162. he Server Name field shows the server on which the database engine is runnin Increase User Count or g Data In Use This button is enabled if the key in the Key field is valid and a database engine is running on the specified computer To Authorize a Key Return to GUI Dialog image 105 License Administration Product Server Name Key DEV Connect Authorize J License Information Product Key State Platform License Type UserCount Session Count DatalnUseGB Expiration Date Yendor Application Pervasive PSOL 11 Server n a Active Windows Temporary n a 12 23 2012 106 This button is enabled when a Product name is selected in the License Information list Y Hide Expired Deauthorize Repar Remaining Authorization Help Close GUI Object Description Related Information License Lists the license information for the specified computer License Models Information A L Product The name of the PSQL product such as Server Vx Server or To Display License Workgroup Information Product Key The Product Key number associated with the corresponding product license Return to GUI Dialog State The status of the Product Key Values are Active Inactive Expired image Disabled and Failed Validation A license can be disabled if certain hardware configuration items have changed since the key was applied Platform The combination of PSQL product operating s
163. he following a Text file that contains the data to import a Field delimiter m Encoding of the imported data The encoding must match the encoding that was used to export the data See Exporting Data with Export Data Wizard Whether or not the first line of the exported data contains the column names If the data was exported with column names as the first line it be must imported the same way Restrictions The data must use a field delimiter of the comma colon or tab character A combination of carriage return and line feed must delimit records gt To import data into a database table 1 For a particular database right click a table name under the Tables node This is the table into which you want to import the data If you are interested in system tables expand the Tables node under System O bjects 2 Click Import Data 3 Providetheimport characteristics as discussed above then click Finish Exporting Data with Export Data Wizard The Export Data Wizard exports data from a table to a text file A combination of carriage return and line feed delimits records The wizard lets you specify the following a Nameofthefileto which the datais exported If you includea path with thefile name the directory or directories in the path must already exist SQL statement on which the export is based For example SELECT FROM t1 would export all records from table t1 a Field delimiter character used to separate data items
164. heincreaseisimmediately available when you authorizethekey A restart of the database engine is not required To authorizean increasekey you must already have a permanent product key present on thesystem The software vendor ID number for an increase key must match the vendor ID number for an installed permanent key Increase keys obtained from Actian Corporation are universal That is they are compatible with any permanent key from any vendor Increasing User Count You increase the user count by authorizing a user count increase U Cl key See To Authorize a Key and To Determine a Total User Count UCI keys are provided by Actian Corporation or an application vendor If you deauthorizea permanent product key all user count increase keys associated with that permanent key arealso deauthorized If you deauthorizeaU CI key only the user count increase associated with that UCI key is deauthorized Trial versions of the software include a trial license that have a set user count that cannot be increased 103 License Administration Increasing Data In Use Limit Actian Corporation or an application vendor offers a key to increase the data in use limit You immediately increase the value of the data in use limit when you authorize the key A restart of the database engine is not required A permanent key must be present to authorize an increase key An increase key cannot be authorized onto a temporary key See To Authorize a Key and To Dete
165. his creates a DSN with the description Pervasive ODBC Interface that can be used by both a 32 bit application and a 64 bit application on the server The DSN isnot visible as an engine DSN in ODBC Administrator running on a Windows client a ToaddaDSN on a client that connects to a named database on a server dsnadd dsn MyDSN db DBname host psqlhost This creates a DSN with the description Pervasive ODBC Interface that can be used by both a 32 bit application and a 64 bit application on the client To add a deprecated legacy style Client DSN with the description Pervasive ODBC Client Interface executethe following command dsnadd dsn MyDSN sdsn engineDSN host psqlhost cintdsn Note that the DSN is accessible only to 32 bit applications To add a deprecated legacy style engine DSN on the server with the description Pervasive ODBC Engine Interface dsnadd dsn myDSN db DBname engdsn Note that the DSN is accessible only to 32 bit applications a To list existing DSNs dsnadd 1 Where myDSN sa name you want to assign to the new data source DBname is the name of the named database on the PSQL host psqlhost is the name of the host where your PSQL Server is installed engineD SN is the name of the engine DSN on the PSQL host 201 Command Line Interface U tilities Options The following options are the most commonly used db dsn name dsn help openmode lt 0 1 1 4
166. ies Local Security Policy YI Data Sources ODBC fi Event Viewer Author Telnet Server Admir Run as f access this computer from the net Bact as part of the operating system Everyone Users PO 5 1 5 21 7765617 2 34 Local Policies 12 Add workstations to domain 1 09 Audit Policy R Back up files and directories Backup Operators Ca User Rights Assignment RE Bypass traverse checking Everyone Users Po 3 Security Options RE change the system time Power Users Admini Public Key Policies 88 Create a pagefile Administrators 2 TP Security Policies on Local Machine R Create a token object Re create permanent shared objects E2 Debug programs Administrators 5 Inthepolicy pane right click Act as part of the operating system then click Security 6 Click Add Local Setting RS Access this computer from the network Everyone Users Po Act as part of the operating system 5 1 5 21 7765617 tv Add workstations to domain R Back up files and directories Re Bypass traverse checking FOSS Rg change the system time Power Users Admini 8 Create a pagefile Administrators Help Local Security Policy Setting q 21x e Act as part of the operating system Local Effective Assigned To Policy Setting Policy Setting 1 5 21 776561 741 764733703 120 2660629 1001 Add 7 Inthe Name pane click on the user
167. ification Viewer in Advanced Operations Guide Sets up Data Source Names DSNs for database access See DSN Setup and Connection Strings in ODBC Guide Primary utility for PSQL Lists engines and databases available and allows you to set properties configure objects See Using PSQL Control Center Tablel Summary of PSQL Utilities continued Understanding the PSQL Database M anagement System Utility name Supported Description platforms PSQL System Analyzer Windows Analyzes system components and runs communication tests See PSQL System Analyzer PSA Query Plan Viewer Windows Displays query plans selected by the database engine so you can better determine how to optimize SQL queries See Query Plan Viewer in SQL Engine Reference Rebuild Windows Converts one version of MicroKernel files into another version See Converting Data Files in Advanced Operations Guide Repair Database Name SQL Editor invoked within PCC Table Editor invoked within PCC Windows and Linux Windows and Linux Windows and Linux Associates tables with a new database if the tables are bound to an original database name See To repair a database name Allows you to execute SQL statements interactively and add or edit data in tables See SQL Editor Allows you to add delete or change the characteristics of columns within a table and to create a table See Table Editor Documentation PSQL comes with a
168. imited set on the Client a Linux Server and a limited set on the Client The summary of utilities in the section that follows notes which utilities are present in a server or client install Where to Find CLI Utilities Please note the location based on your platform Windows In Windows the utilities are installed to the BIN directory of your PSQL installation If you installed to the default installation location your utilities are located in file path PSQL bin Since the PSQL installation places your install directory in the PATH these utilities should be available from any command prompt Linux In Linux utilities are installed to usr 1oca1 psql bin Theuser psql has the necessary environment variables to use the utilities If you wish to use utilities from accounts other than psql follow these instructions in Getting Started With PSQL PSQL Account M anagement on Linux Utilities by Platform and Engine Type Thefollowingtables outlinesthe command line utilities the platform on which they are made available as well as if a graphical user interface is available for the utility Utility Description GUI Available Windows Linux Server Client Server Client bcfg Configures PSQL components PSQL Control Center see Yes Yes Yes Yes Configuration Reference in Advanced Operations Guide bdu Imports data into a database No Yes No Yes No bmon Monitors PSQL activity Monitor Utility see Yes Yes Monitoring in Adv
169. in PSQL Supported Data Types and that appear in the Type selection list on the Columns page in Table Editor Null Values Theability to modify the null attribute of a column is subject to the following restrictions Thetarget column cannot havea PRIMARY FOREIGN KEY constraint defined on it Ifconvertingtheold typeto thenew type causes an overflow arithmetic or size the ALTER TABLE operation is aborted Ifanullable column contains NULL values the column cannot be changed to a non nullable column If you must change the data type of a key column you can do so by deleting the index key changing the data type and re adding the key Keep in mind that you must ensure that all associated index key columns in the database remain synchronized For example if you have a primary index key in table T 1 that is referenced by foreign keys in tables T 2 and T3 you must first delete the foreign keys Then you delete the primary key and change all three columns to the same data type and size Finally you must re add the primary key and then the foreign keys For additional information on nulls see the following Rebuild Utility Concepts in Advanced Operations Guide m INSERT in SQL Engine Reference a Null Valuein PSQL Programmer s Guide which is part of the PSQ L Software D eveloper s Kit SDK Indexes Page The Indexes page lets you add and modify indexes and index segments See Indexes Tasks for the tasks that you perform
170. in the table above and ask What is the value of that field Row or Record Another element is called a row or a record A row isa collection of all the values for one particular instance For example one entry in the phone book complete with name address and phone number is one record or row Cell A cell isa column within a specific record You can think of it as the intersection of a row and a column Each cell has a specific value For example you might tell a co worker The value of the cell located at row 2 column 3 s 12345 Key Concepts Table A collection of rows and columns makes up a table A table is a set of data that shares exactly the same structure Tables generally have names that describe the contents of the table For example the table above is called Phone Book With PSQL each table is stored as a separate data file on the hard disk Index An index is an ordered list of all the values in a particular column A table can have zero or moreindexes on it The database engine uses indexes to find specific records in the database without having to step through every record one at a time Creating indexes on columns which will frequently be used in database searches is likely to improve the performance of your database Database A databaseisa collection of oneor moretables The datain thetables does not need to be related among the various tables but usually there are many relations For example
171. index Only the database engine can add an index to an IDENTITY or SMALLIDENTITY column However you can include an IDENTITY or SM ALLIDENTITY column as part of a multiple segment index Table Editor permits you to include an IDENTITY or aSMALLIDENTITY column in the Indexes list if you have not saved the table However the DBM S returns an error when you attempt to save the table After you deletethel DENTITY or SM ALLIDENTITY column from thelist you may then savethe table 1 Ensure that the Indexes page of Table Editor is active If required perform the steps for To work with indexes Click Add The New Index dialog displays Type the name of the new index and click OK Tip For a list of database object lengths and invalid characters see Identifier Restrictions by Identifier Type in Advanced Operations Guide The new index appears in the Indexes list and the Index Segment Details display Note New indexes are created by default as Normal 4 Select Partial in the Index Segment Details area to designate the index as partial Note that the first column is populated into the Columns list 139 Table Editor Index Editor Indexes Elindex_01 Add Index Segment Details Ounique Partial Normal v Allow Modifications SortOrder Ascending Columns Student_ID Student ID Amount_Paid 5 IntheColumns list select the Column to designate for the Index or Index Segment AS
172. ing database engine execution with no need for down time or disruption of business operations in most cases See M onitoring Data File Fragmentation in Advanced Operations Guide 61 Using PSQL Control Center Databases A database is a collection of data stored together N ewly created databases are empty and may be populated with tables Refer to thechapter PSQL Databases in Advanced Operations Guidefor a detailed discussion of databases The properties of a database include such items as file locations referential constraints security and whether the database is bound _ hh OO N Note If you wish to add a database to a Server engine you must have administrative rights on the server operating system If you do not have administrative rights you will not be permitted to add the database To log out from a database engine In PSQL Explorer expand the Engines node Expand the node for the registered server to display the databases on that server Right click on the database from which you want to log out Click Logout name Namereflects the name of the user currently logged in to the database If the database does not have security enabled nameis Master N ame may also be M aster if the current user is logged in as M aster Any nodes expanded for the database are collapsed Database Properties You set properties for a database from a Properties dialog in PCC The dialog contains a tree with the following
173. ing of moving from a PSQL engine with concurrent user licensing to one with capacity based licensing How do determine the appropriate size for my data use license Capacity Usage Viewer was designed for this purpose Becauseit records both peak daily session use and peak daily data usage you can see how much of each you use over time You can use this information to make a reasonable determination of what license capacity size you need Refer to Capacity Usage Viewer for information about how to access and use Capacity Usage Viewer Networking frequently asked questions about networking How do know which protocol am using for communication I can see other systems in Network Neighborhood but I can t get to my data Start PSQL System Analyzer and click Next on the Welcome screen On the following screen check the box Test Network Communications and make sure all the other boxes are not checked Click Next In the following screen cancel the selected protocols that you do not want to test Click Browse to select the drive that you have mapped to the installation directory on the server You must have a mapped drive UNC names are not supported Click Next to run the network tests The results window tells you if there are any significant problems with your networking Difficulty Accessing Data Frequently asked questions about accessing data l upgraded from Btrieve v6 x or earlier to PSQL v12 Now I get error messages telling me that a
174. inking a table select the Save The Login ID And Password check box to store the information for thetablein the current database so that users will not have to enter it each time If you leavethe check box cleared all users must enter thelogon ID and password every timethey open the table with M icrosoft Access in each new session Your network administrator can also choose to disable this check box requiring all users to enter a user nameand password each time they connect to the database If the database you want does not appear in the ODBC Source list seeBefore You Begin 8 TheAccess Link Tables dialog box opens Click each tablethat you want to import or link and then click OK Note M icrosoft Access cannot display morethan 256 columnsin atable If you need to display more than 256 columns you may wish to usea different tool Linking to your PSQL data is complete As shown in the figure below Access presents you with options for designing the new database View the linked tables by double clicking the table name 39 Using PSQL Figurel6 UsingPSQL Data in Microsoft Access El Microsoft Access File Edit view Insert Tools Window Help JO se SR se es o B else e Q g db3 Database 2 open DE Desion iNew x Bo E 8 Tables as Queries Create table in Design view Create table by using wizard Create table by entering data Billing ES Forms Class Reports 3 Pages Course
175. interact with desktop This account Logging On as This Account If you change the Log on as setting to This account you must change the user rights policy Act as part of the operating system for the account Otherwise remote login fails For example the Monitoring utility requires that you log in to the operating system on the machine where the database engineis running You will receive a message that login failed if the account specified for This account cannot act as part of the operating system Note that even the Administrator account requires that you set the user rights policy for Act as part of the operating system You specify This account on the services property sheet Log on as Local System account V Allow service This account Administrator Password Confirm password User Rights Policy Tasks The following tasks explain how to change the user rights policy gt To Set User Rights Policy on Windows 32 bit Platforms 1 Access the operating system Control Panel 2 Doubleclick Administrative Tools 3 Right click Local Security Policy then click Open 22 Granting Administrative Rights for the Database Engine J Administrative Tools File Edit View Favorites Tools Help Back gt gt 87 Asearch Folders Address a Administrative Tools PE Component Services ey Computer Management PRA Services Tree 5 Security Settings Account Polic
176. ion Tasks 162 To enable the execution commands and icons for SQL statements Follow the steps for To set database context for an SQL query To run a single SQL statement in SQL Editor Position the cursor on the statement or select the statement Perform one of the following actions Click SQL gt Executein Grid or SQL gt Executein Text Press F9 or Shift F9 Click EN or E Note SQL Editor automatically uses the Text window view for the results of SQL statements that are not SELECT statements Only SELECT statements use the Grid window view SQL Editor Tasks To run selected SQL statements in SQL Editor Press and hold down the left mouse button and drag the cursor across the statement that you want to run You may select one or more statements Perform one of the following actions Click SQL gt Execute in Text or SQL gt Execute All SQL Statements Press Shift F9 Click or ET Note SQL Editor automatically uses the Text window view for the results of SQL statements that are not SELECT statements Only SELECT statements use the Grid window view gt To run all SQL statements in SQL Editor 1 3 Click SQL gt Execute All SQL Statements press F10 or click ET Ensurethat either no statements are selected or that all statements are selected If you select a portion of the statements in SQL Editor only the selected portion executes To run SQL statements in Outline view To execute al
177. ions Guide See Configuration Through CLI Utility 184 Command Line Interface Utility Reference bdu Description TheBulk Data Utility BDU isa command lineutility that allows you to load data from a delimited text fileinto aPSQL table The table and database must already exist TheBDU the table the database and the PSQL database engine must all be located on the same machine The delimited text file must be locally accessible by the database engine server through a local drive mapped drive mounted folder or shared folder You may use a default delimiter or a user specified delimiter The delimiting character must not be contained in the data itself The following tables list the permissible delimiters Table15 Delimiters for Columns Delimiter Indicated By Tab t default Any single printable character control characters are not A t and so forth printable except null tab new line and carriage return Note PSQL does not support the use of NULL terminator 10 or double quote as column delimiters Table16 Delimiters for Rows Delimiter Indicated By New line character n default Carriage return r Carriage return line feed CR LF r n TheBDU supports only the single quote and the double quote characters as text qualifiers The data file may contain column values enclosed by single quotes or by double quotes For example the following column values are
178. irectory in the logs folder For default locations of PSQ L files see Where arethePSQL files installed in Getting Started With PSQL What is the remote default location for the log file The default location for the log file is always the directory where psawizrd exe is located on a Windows machine What kind of information is contained in the log file Thelog file records any process performed by PSA and includes the associated timestamp What happens to the information in the log file each time the utility is run New information is appended to the log file each time PSA runs if the option Append to log file is selected see Figure 26 If the option is cleared the log file contains only information for that particular execution of PSA How do run PSA See To start PSA When would want to use PSA The following lists the most common situations where you would want to use PSA You are encountering network errors and wish to test your client s connectivity to a machine running a PSQL database engine Your application is not functioning correctly and you wish to test connectivity to the database engine m You wish to view the PSQL components on the system loaded in memory or both 172 PSA GUI Visual Reference PSA GUI Visual Reference The following dialog in PSA provides access to its functionality Click on an area of the figure to learn more about the item Figure26 PSA Main Dialog View loaded Pervasive
179. is You must create a client DSN on your local computer to provide access to data on the server through the remote server DSN To createa client DSN followtheinstructionsin Setting Up Client Access from a WindowsClient You must first make surethat a server DSN exists on the server you want to access get a message about Engine components version is different than my client components version When a client requester first connects to an engine the client requester compares ts internal router version with the value returned from the engine by a Btrieve Version 26 call If the client version is older than the engine a message dialog box is displayed on the client system with the message Engine components Version is different from Client s along with a suggestion to run PSQL System Analyzer PSA The same message is also logged in the client s PVSW LOG file This message is a warning The client is not prevented from connecting to the engine in this situation However Actian Corporation guarantees compatibility between engines and clients only if the clients arethe same version as the database engines When prompted by this message if you choose not to run PSA and archive your old client components and install anewer client you can expect the product to behave unpredictably until the client version is equal to the engine version Note Actian Corporation recommends that you use client requesters that are the same version asth
180. ision Precision specifiesthe number of significant digits for floating point values 1 3 4 5 Ensure that the Columns page of Table Editor is active If required perform the steps for To work with columns Click in the Precision cell for the desired column You can set precision only for applicable data types such as DECIM AL If precision isnot applicable the grid cell is shaded and you will be unable to edit the Precision Delete the existing value Type the precision value you want Click File gt Save or El before changing pages within Table Editor gt To set a column scale Scale specifies the number of significant digits that areto the right ofthe decimal point for floating point values 1 2 Ensure that the Columns page of Table Editor is active If required perform the steps for To work with columns Click in the Scale cell for the desired column 133 Table Editor 4 5 gt For INS You can set a scale value only for applicable data types such as NUM ERIC If scaleis not applicable the grid cell is shaded and you will be unable to edit the Scale Delete the existing value Type the scale value you want Click File gt Save or El before changing pages within Table Editor To set a column to allow or disallow nulls additional information about nulls see Rebuild Utility Concepts in Advanced O perations Guide ERT in SQL Engine Reference and Null Value in the PSQL Programm
181. istrators who are a member of group pvsw The ones below the line are the current user s entries If you have a global and a user entry for the same server the user s entry overrides the global g Manipulates default settings for all users Settings created with g can be overridden by individual users m Modifies a server entry for a user specified by the u parameter If no user is specified current user is assumed p Specifies the password for the user If not provided pvnetpass prompts for a password r Removes a server entry for a user specified by the u parameter If no user is specified current user is assumed server Server local or remote to which you want to add a connection entry Server can be include the single quotes to set the default server entry information This default entry is used when there is no user entry for the server u Specifies the name of the user If u is not specified your current user name will be used See Also Setting Up Client Access from a Windows Client 217 Command Line Interface Utilities Examples From current user to all servers overrides g pvnetpass a p password From current user to oneserver myserver overrides g pvnetpass a myserver p password From all users g to one server myserver using credentials joe password pvnetpass g a myserver u joe p password From all users g to all servers use default credentials joe password pvnetpass g a
182. it d psql force 19 Using PSQL Granting Administrative Rights for the Database Engine This section begins by outlining those PSQL v12 tasks that require administrative level access at the operating system level and those that do not The section then walks you through the steps to grant a user administrative level access for each of the supported operating systems Note This section only applies to the Server engine unless otherwise noted Tasks Requiring Administrative Rights Administrative level rights are required to create and configure named databases and tables view or modify a table design with Table Editor a set engine configuration options view and set engine monitoring values view certain engine configuration settings restarting the engine when running as a service How Administrative Rights are Granted To have administrator level access you must either possess full administrator level rights on the machine on which the database engineis running a domain administrator for example may lack full permissions on certain local machines or beamember of the operating system group Pervasive Admin To modify a table design with Table Editor you must have full administrator rights on the machine on which the database engine is running even if you are a member of the Pervasive Admin group Note For Linux servers administrator level rights can be granted only by using the btadmin utility to add
183. ith the Text window To clear results from Text view To select and copy text from Text view Outline View Tasks Outline view tasks apply to working with the Outline window a To minimize Maximize or restore Outline view size Common SQL Object Tasks Common SQL Object tasks apply to working with triggers stored procedures user defined functions and views To create a common SQL object To modify a common SQL object To delete a common SQL object General Tasks gt To start SQL Editor for a new SQL query 4 Start PCC if it is not already running See Starting PCC on Windows 2 Click File gt New gt SQL Document or click The Select D atabase dialog box appears 3 Click the database in the list for which you want the SQL document to apply or ensure that the option Noneis check marked if the SQL document does not apply to a specific database Note The option None is selected by default if an object other than a database or any of the nodes subordinate to a database is selected in PSQL Explorer Note that the commands to execute SQL statements are disabled if Noneis specified as the context See To set database context for an SQL query 158 _ hh OO N SQL Editor Tasks Select the Set selected database as default for this session option to use the selected database whenever you open a new SQL Editor tab If you leavethis option unselected you will be prompted to select a database each time you op
184. itness A 32 bit DSN and a 64 bit DSN can have the same name because their bitness differs DSN Setup and Connection Strings in ODBC Guide Automatic in ODBC Guide Additional Utilities back to Figure 20 67 Using PSQL Control Center Creating Modifying Deleting and Repairing PSQL Databases The following tasks pertain to databases To create a new database To modify properties of a database To delete a database To repair a database name For conceptual information on named databases see PSQL Database Concepts in Advanced Operations Guide gt 68 To create a new database Note On Linux the owner of the directory where you want to create the database must be psa If not error message 7039 Dictionary path is invalid results Use the chown command to change owner of the directory For example chown psal directoryname In PCC PSQL Explorer right click on the database engine for which you want the new database Click New gt Database The Create N ew Database dialog box appears see Figure 20 Provide a name for the database and a location see Table 1 Identifier Restrictions by Identifier Type The name cannot be the same as an existing DSN Also no two files can share the same file name and differ only in their file name extension if both files are in the same directory For example do not name a data file Invoice btr and another one Invoice mkd in the same directory This restriction
185. itor and To run SQL statements in Outline view gt To select an SQL statement separator 1 OnthePCC Window menu click Preferences Expand the PSQL nodeif it is not already expanded 2 Click SQL Editor 3 Select the desired choices for SQL Statement Separator Sql Statement Separator I Semicolon I Pound Note Based on theseparator option you select PCC looksfor theselected character s and identifies each as the end of astatement It sends each identified statement to the database engine and displays results of that statement before sending the next statement If you use as a separator in a script but do not select the Pound option you will receive an error message when you run the script If you do not select Semicolon as a separator but use a semicolon asa separator in ascript anyway you will not receive an error message if the statements are properly parsed This is because the database engine recognizes semicolons as separators However PCC will not display the results for all the statements It will only display results for one statement probably the first statement As far as PCC is concerned if you don t select a semicolon asa separator statements separated by a semicolon are a single statement gt To type comments into SQL Editor Single line comments are indicated by double dashes or doubleslashes Each comment must be on a separate new line or after the statement separator on an existing lin
186. ity will prompt for the passwords with this syntax pvdbpass database username server name port number This syntax includes the old and new passwords pvdbpass database username password newpassword server name port number Options database Database in which the username is defined this can be a database name or a server DSN username The user whose password will be changed password The current password for the user You must provide the original password in order to modify it You can either provide the password as a parameter or omit it and be prompted newpassword The new password for the user See Identifier Restrictions by Identifier Type in Advanced Operations Guide for password restrictions Note If the new password begins with a non alphabetic character the password must be enclosed in single quotes If the existing password begins with a non alphabetic character do not enclose it in single quotes see examples server name Optional Server name on which the database is defined If you do not specify this option the local machine is assumed port number Optional TCP port on which the SQL engine running on servername is listening If you do not specify this option the default port 1583 is assumed See also Changing the Default Communication Ports in Getting Started With PSQL Examples To change the M aster user s password and be prompted pvdbpass demodata Master To change an existing password to one that doesnot star
187. ivileges for the database engine For Password type the appropriate password for the user name you specified Click Login The server name appears in the License Administrator title bar and in the Server Name field Note For servers running a previous version of PSQL use the license utility provided with that version of the product gt To Authorize a Key Note that the GUI License Administrator can be used for online authorization of a local machine connected to the Internet and for remote authorization of the database engine on a machine not connected to the Internet 1 Ensure that the server name in thetitle bar is the server for which you want to authorize a database engine key If not select a different server as described in To Select a Server for License Administration Type or paste the key into the Key field Lowercase letters are automatically converted to uppercase Press Enter or click the button to authorize the key The Key field is cleared if the key is successfully authorized Verify the new license information that appears in the License Information list If necessary press F5 to refresh thelist Thelicense is now active You do not have to restart the database engine Note When authorizing an increase key for user count session count or datain use a permanent license must already exist The vendor ID of the increase key must match the vendor ID of the permanent key Tip Product authoriza
188. ize the key from the unique machine signature maintained by Actian Corporation That is it does not remove the record of the link between the machine signature and the key Generates authorization request data file for offline authorization The product key and output filename are both required Displays the number of remaining authorizations for the specified key Note If no key is specified remaining authorization information for all product keys is displayed lt servername s gt lt servername gt Allows connection to a specified server if specified If not specified the servername is assumed to be blank This parameter is optional lt validate t gt Performs a license validation on the connected server name and selected key lt username u gt The user name for the server to which you are connecting If not specified the username is assumed to be blank This parameter is optional and not required if a servername is not specified lt password p gt lt password gt The password associated with the user name for the server to which you are connecting If not specified the password is assumed to be blank This parameter is optional and not required if a servername is not specified lt help h gt Displays information about the options and parameters lt repair e gt key Repairs and authorizes a key if repair is possible The Repair action requires the following conditions
189. l Select Unique in the Index Segment Details area to designate the index as unique Selecting Unique disables duplicatability restricting duplicates Note that the first column is populated into the Columns list Index Editor Indexes Index Segment Details Elindex_01 Unique O Partial Normal v Allow Modifications Columns SortOrder Student_ID Y Ascending Student ID A Transaction_Number Log 3 Amount_Owed Amount_Paid he In the Columns list select the Column to designate for the Index or Index Segment Caution Indexes must have at least one Column designated If you do not select a Column for the Index the first column remains selected Thedefault sort order is Ascending If you need a descending sort order select Descending from the Sort Order list for the Column you want changed Table Editor Tasks Note Some data types such as LO N GVARBINARY cannot be used for an index Columns with such data types are not valid choices Continue selecting columns from the list until all the segments are added Note that a column can be selected for an index only once Once selected the column is removed from thelist of choices because the column has already been designated in the index Click File gt Save or El before changing pages within Table Editor See also Creating Indexesin PSQL Programmer s Guide which is part of the PSQL Software Developer s Kit SDK gt To create a partial
190. l Tab Parameter Description Table Name Shows the name of the table as it appears in the database definition Table Location Shows the physical location of the data file associated with the table Dictionary Path Shows where the database s DDF files are located File Version Shows the file format version of the data file Record Length Shows the length of the data file s records Page Size Shows the page size in bytes of the data file The page size determines the maximum number of index segments that can be defined in a table Number or Records Shows the number of records currently contained in the data file Number of Indexes Shows the number of indexes defined for the table Number of Duplicate Pointers Shows the number of linked duplicate indexes that can be added Ptrs Number of Unused Pages Shows the number of pre allocated pages available If pre allocation is enabled the MicroKernel pre allocates a specified number of pages when it creates the data file Pre allocation guarantees that disk space for the data file is available when the MicroKernel needs it 72 Tables Table11 TableProperties on General Tab continued Parameter Description Variable Records Variable Record Blank Truncation Shows whether the data file contains variable length records Shows whether blank truncation is enabled If it is the MicroKernel truncates the blanks in variable length records Bl
191. l errors BDU exits because it cannot perform error recovery For example a missing delimited data file is a critical error With recoverableerrors BDU skipstheerror and continues processing Theutility keeps a count of such skipped errors and exits when it reaches a user specified threshold By default the threshold is set to Zero 187 Command Line Interface Utilities Constraints Thefollowing constraints apply to loading data with BDU Constraint Discussion Any Referential Integrity RI error is considered an RI violation Row is rejected Any unique or primary key violations Row is rejected No value specified for a non NULL column Row is rejected regardless of column s default value No value specified for a nullable column NULL inserted regardless of column s default value Table into which data is being loaded contains insert triggers BDU returns an error and does not attempt to load the table Drop the insert triggers on the table then re run BDU Table into which data is being loaded contains CLOB or BLOB columns relational data types LONGVARCHAR NLONGVARCHAR or LONGVARBINARY BDU does not attempt to load the table and returns the message The target table contains longvarchar nlongvarchar or longvarbinary data types These data types are not supported Order of rows BDU treats the delimited data file as unordered The original order of rows may not be preserved
192. l statements in Outline view right click on the root node then click Execute All Statements To execute one or more statements click on the desired statement s Note that you can select multiple statement by using Ctrl click The statements do not have to be contiguous If multiple statements are selected right click on one of the selected statements then click Execute Selected Statements If a single statement is selected perform one of the following actions Right click on the statement then click Executein Grid or Executein Text e Press F9 or Shift F9 Click EN or E Note SQL Editor automatically uses the Text view for the results of SQL statements that are not SELECT statements Only SELECT statements use the Grid 163 SQL Editor Grid Tasks gt To change data within the Grid 1 Click the Grid cell that contains the value you want to change gt Tip By default the entire contents becomes selected when you click the cell Press Delete or Backspace to deletethe entire contents of the cell 2 Changethe data in the cell 3 Movethecursor outside of the cell for instance press Tab or click outside of the cell y Caution M oving the cursor from the cell automatically saves the data changes to physical storage You cannot explicitly save the changes made to the cell gt To add rows of data to the Grid 4 Click Ey on the Grid TheAdd Rows dialog appears For example the following image sh
193. le or a table has been deleted outside of PCC while PCC is currently running You have three options when exporting a table schema a Includean IN DICTIONARY clause with a USING clause in the statement a Include only the USING clause in the statement Omit both theIN DICTIONARY clause and the USING clause in the statement a plain statement IN DICTIONARY Clause TheIN DICTIONARY clause instructs the database engine to modify only the DDFs which leaves the underlying physical data unchanged Normally PSQL keeps DDFs and data files synchronized but this clause allows you to force table dictionary definitions to match an existing data file Theclause can be useful when you want to create a definition in thedictionary to match an existing data file Another common useis if you want to duplicate an existing database You export all the statements from one database create a new database and then run the exported script against the new database If the SQL script containsIN DICTIONARY clauses note that the data file must already exist when you run the SQL script in SQL Editor ThelN DICTIONARY clause is always paired with aU SING clause An exported statement looks similar to the following CREATE TABLE Course IN DICTIONARY USING Course mkd Name CHAR 7 NOT NULL CASE Description CHAR 50 CASE Credit Hours USMALLINT Dept Name CHAR 20 NOT NULL CASE CREATE UNIQUE INDEX
194. lein use While data files are in Continuous Operations mode they are not modified and special delta files store the results of any database operations After the backup is complete the data files must be removed from Continuous Operations mode at which timethechanges stored in the delta files are rolled into the live files 238 Frequently Asked Questions If the server goes down while files are in continuous operations mode the next time the data file is accessed the database engine detects the existing delta file and rollsin the changes at that time You can put data files into Continuous Operations mode by using the BUTIL STARTBU command or Maintenance utility described in Advanced Operations Guide 239 Basic Troubleshooting 240
195. llows you to run Structured Query Language SQL statements against a PSQL database With SQL statements you may retrieve create change or delete data in a database provided you have the proper database permissions to perform these actions TheSQL statements that you may use with SQL Editor are documented in SQL Engine Reference See especially Grammar Statements Figure22 SQL Editor TgPervasive SOL x Outline 3 El 3 Pervasive SQL AP SELECT FROM Room la S Room Ic Local Client 3 SELECT FROM Room E Microkemel Router EEF Services 33 Pervasive SQL relat 33 Pervasive SQL tran 5 0 Engines E tm E e Databases a CHECKID m DBASES H 8 DEFAULTDI 5 DEMODATA 3 Tables E Bilin E Clas Cour J E 2 Grid amp Text E o E Enc t El Fact Room E Pers Building Name gt Number Capac E Roo Roscart Building 299 E Stuc Roscart Building 205 y Caution Backup all your data definition files DDFs and data files before you perform functions through SQL Editor Thistool gives you the ability to modify your database table definitions and data You could inadvertently change your filesin an irreversible manner Full recovery is possible if you have performed a backup Statement Separators SQL Editor requires a way to differentiate where one statement ends and another begins The way to differentiate statements isto place
196. lumns within a table The table may be one newly created or an existing table that you want to edit To modify tables with Table Editor you must have full administrator rights on the machine on which the database engine is running even if you are a member of the Pervasive Admin group See Granting Administrative Rights for the Database Engine and Database Security in Advanced O perations Guide x Caution Backup all your data definition files DDFs and data files before you perform functions through Table Editor This tool gives you the ability to modify your database table definitions and data If you inadvertently set the options incorrectly or enter incorrect data you could change your files in an irreversible manner Full recovery is possible if you have performed a backup Table Editor Pages Table Editor contains the following pages that you use as work areas Columns Indexes Foreign Keys a SQL View You select a page by clicking its page name tab Note Save your changes before switching pages Columns Page TheColumns page lets you add delete modify columns and set primary keys See Columns Page for a description of the areas on the Columns page See Columns Tasks for the tasks that you perform on the page 122 Table Editor Concepts Data Types Refer to PSQL Supported Data Types in SQL Engine Reference for a list of the data types supported by the database engine You may use any data types listed
197. lution Double check the spelling of the user name and the password M ake sure the user and password have been set up on the remote server operating system Inspect the user s account information on the server M ake sure the operating system is not expecting the user s password to be changed at the next logon M ake sure the user is not also a member of a group that has restricted permissions For Windows 32 bit platforms besurethat the user is set up asa local user on the system not a network user Network users have a domain name and a back slash preceding the user name such as BOSTON GILBERT Besure that the user who is a member of the Administrators group or Pervasive Admin group is alocal user Unknown configuration properties Itis possible but unlikely that PCC may retrieveconfiguration properties from the databaseenginethat are invalid Please contact PSQL Customer Support to report such error conditions 227 Basic Troubleshooting Frequently Asked Questions This section answers some of the questions that customers frequently ask Installation Will lose my data files if uninstall my existing version of the product or install a new version m Whydol not seein PCC PSQL Explorer the plug in product that just installed or upgraded a Whattypeof client install should do How can besure what service pack level of client am running IsPSQL supported on a Terminal Server Can install PSQL in a
198. ly d Click OK to save the changes Click Next to start the test PSA runs a series of tests and displays the results If the test succeeds PSA informs you that all test messages were successfully transmitted If the test fails PSA lists the issues along with tips on how to resolvethem Thetips are also written to the PSA logfile Click Next Click Finish if you are finished using PSA or click View Log File to view the log To test the MicroKernel transactional Engine Start PSA and click Next On the Options dialog click Test active installation if it is not already selected Click Test transactional engine if it is not already selected This test runs a series of transactional operations to simulate your client requester interacting with the database engine Click Next Providea path nameto the samples directory on the machine running the database engine The default path should be correct if the database engineis running on thelocal machine You can type a path or browse to one if you click Select the operations you want to perform for the M icroKernel Engine by default all are selected Create Data File write access required Read Data File Update Data write access required Insert Data write access required Click Next 9 PSA Tasks PSA performs the tests and displays the results A check mark indicates a test succeeds and an x indicates a test fails If all of the tests succeed y
199. ly interpreted by the database engine The default code page is server default meaning the operating system code page on the server where the database engineis running Thelink Change code page provides additional information about the setting and lets you select a specific code page PCC Connection Encoding PCC is itself a client application to the database engine Asa client PCC lets you specify the code page the encoding to use for each database session when PCC reads and inserts metadata and data The default for an existing database is to use the encoding of the machine where PCC isrunning Thisisthe legacy behavior of PCC The default for a new database is to use automatic translation The following explains the interaction between the settings for PCC connection encoding and Database code page PCC Connection Encoding Set to a PCC Connection Encoding Set to Automatic Translation Specific Encoding PCC ignores Database Code Page and PCC and the database automatically establish compatible encoding uses the encoding specified to read and insert data and metadata The database metadata and data are translated from the encoding specified for Database Code Page to the encoding used on the system where PCC is This is the legacy behavior of PCC running Note PCC connection encoding applies only to PCC It has no affect on other client applications When a database has OEM charac
200. m a lt key gt c key force da lt key gt g lt key gt lt filename gt h i key n key s lt servername gt t u lt usename gt p lt password gt w64clilcadm a lt key gt c key force d lt key gt g lt key gt lt filename gt h i key n key s lt servename gt t u lt usename gt p lt password gt cliadm64 a lt key gt c key force d lt key gt g lt key gt lt filename gt h i key n key s lt servername gt t u lt usename gt p lt password gt Options For a complete discussion of the command line options see License Administrator CLI Syntax See Also licgetauth License Administration documents License Administration and the associated utilities in detail 196 Command Line Interface Utility Reference clipaadm Description The clipaadm utility allows you to authorize PSQL viathetelephone in the event that it is not possible to authorize keys online remotely or offline With this utility options are not passed but instead responses are given to prompts the utility displays in order to gather information required to complete product telephone authorization Synopsis clipaadm Options This section detailsthe prompts displayed and information needed to completetelephone authorization 1 Pleas nter the 30 character Enter the product key you want to authorize product key 2 Repeat the authorizati
201. m the list until all the segments are added Note that a column can be selected for an index only once Once selected the column is removed from thelist of choices because the column has already been designated in the index Click File gt Save or El before changing pages within Table Editor See also Creating Indexesin PSQL Programmer s Guide which is part of the PSQL Software Developer s Kit SDK 137 Table Editor gt On To create a unique index ly the database engine can add an index to an IDENTITY or SMALLIDENTITY column However you can includean IDENTITY or SMALLIDENTITY column as part of a multiple segment index Table Editor permits you to include an IDENTITY or aSMALLIDENTITY column in the Indexes list if you have not saved the table However the DBM S returns an error when you attempt to save the table After you deletethe DENTITY or SMALLIDENTITY column from thelist you may then save the table 1 y a 5 138 Ensure that the Indexes page of Table Editor is active If required perform the steps for To work with indexes Click Add The New Index dialog displays Type the name of the new index and click OK Tip For a list of database object lengths and invalid characters see Identifier Restrictions by Identifier Type in Advanced Operations Guide Thenew index appears in the Indexes list and the Index Segment Details display Note New indexes are created by default as Norma
202. ment to apply Click OK Note Select the Set selected database as default for this session option to use the selected database whenever you open anew SQL Editor tab If you leave this option unselected you will be prompted to select a database each time you open anew SQL Editor tab To unselect the default database Access the Select Database dialog by clicking g4 and unselect the Set selected database as default for this session option or Click Windows gt Preferences then click the General node Unselect Do not prompt for new 159 SQL Editor database each time a SQL document is opened Theselected database is not maintained across PCC sessions If you closeand reopen PCC you will have to select a new default database context gt To identify editor settings for SQL Editor 1 Ensure that the cursor is positioned in SQL Editor 2 Observe the information blocks along the bottom of the PCC window O 5E Outine 53 S SqlDoc3 tmentDEMODATA Block Meaning 1 Identifies whether SQL Editor accepts character input Writable 2 Indicates whether the editor is in insert mode or overwrite mode for character input The Insert key toggles the mode Note that the cursor changes shape for each mode 3 Indicates the row and column at which the cursor is positioned The first value represents the row the second the column In the image above the cursor is positioned on the first row a
203. mission A check mark indicates that the permission applies Click OK To assign permissions for a group or user using SQL In the PCC File menu click New gt SQL Document or click in the toolbar The Select D atabase dialog box appears Expand the nodes for the desired database Click OK In SQL Editor create the desired statement for the group or user In SQL Engine Reference see the following GRANT REVOKE SET PASSWORD Click SQL gt Executein Text or click in the toolbar Configuration Configuration Configuration is the process by which you provide settings for database engines and clients You can specify configuration settings with PCC for database engines and the local client In PCC the configuration settings are properties of the engine or client In Advanced Operations Guide see To access configuration settings in PCC for an engine and To access configuration settings in PCC for alocal client In addition refer to the following sections in Advanced Operations Guide for a discussion of configuration settings that can be configured through PCC Services Configuration Parameters Server Configuration Parameters Windows Client Configuration Parameters 89 Using PSQL Control Center Dialogs for File Open and File Save By default PCC now usesits own dialogs for FileO pen and File Save and File Save As The new dialogs aretitled Open SQL Document and Save SQL Document re
204. mote Request is selected You can now rule out the possibility the server is not accepting remote requests Note If your application uses pure Btrieve access only without ODBC then skip this step If everything checks out so far but you still cannot get to the data you want to access make sure a server DSN has been set up for your target data Using PCC expand the Databases node for that server and inspect the databases that are present M ake sure one of the databases represents the data you want to access If so then a server DSN has been created for your data If you do not find the data you want to access but you know it ison the server then most likely you need to set up a DSN for the given data You must have administrative rights on the server or be a member of the Pervasive Admin group to do so Follow the instructions in Setting Up ODBC Database Access to set up a DSN for existing data files You can now rule out the server DSN as the source of the problem Note If your application uses pure Btrieve access only without ODBC then skip this step If you have performed all the steps above and you still cannot get to your data the next possibility islack of a local client DSN for the remote data PCC can access remote server DSN s using connections without client DSNs M any desktop applications such as M crosoft Excel and M icrosoft Access cannot do this You must create a client DSN on your local computer to provide acces
205. mple if you create a new view anew SQL Editor contains a tab named View_1 After you save the object with a name of your choice the tab reflects the saved name Modify the skeletal SQL statement for the common object Tip Hover the mouse cursor on the statement to obtain a tool tip on the syntax which also includes an example Click File gt Save or H To modify a common SQL object In PCC PSQL Explorer click the database in thelist for which you want to modify thecommon SQL object Expand the node for the object function stored procedures user defined functions or triggers that you want to modify Double click the object that you want to modify or right click the object then click Edit A new SQL Editor is opened that contains a tab Thetab name reflects the name by which you saved the object To delete a common SQL object In PCC PSQL Explorer click the database in thelist for which you want to delete the common SQL object Expand the node for the object function stored procedures user defined functions or triggers that you want to delete Click the object that you want to delete then perform one of the following actions e Right click then click Delete Press Delete Click Hg on PSQL Explorer Note that you can select multiple objects for deletion by using Ctrl click or Shift click chapter PSQL System Analyzer PSA 7 Usage Information for the Diagnostic Utility
206. ng a Aes EH Class Billing E ae Student ID Transaction Number Log Amount Owed rl ee 100062607 1 1996 03 28 16 38 52 000 PM azo Fl Faculty 100285859 1 1996 03 28 16 38 52 000 PM 1500 00 FR Person 100371731 1 1996 03 28 16 38 52 000 PM 1875 00 E Room 100822381 1 1996 03 28 16 38 52 000 PM 1500 00 E Student 101042707 1 1996 03 28 16 38 52 000 PM 3750 00 E Tuition 101369010 1 1996 03 28 16 38 52 000 PM 4500 00 Views 101581226 1 1996 03 28 16 38 52 000 PM 5100 00 m r 4 T Figure18 PSQL Control Center on Linux Platforms File Edit Tools SQL Window Help Bajo 2200 4374 PaPervasive PSQL 5 O Spilling X A 52 Outline X la Local Client select from Billing Y Engines Y H thes4rw anonymous Y Databases b DEFAULTDB Y DEMODATA Master Y Tables 2 Billing kal Grid 2 ERA class Billing Course Student_ID Transaction Number Log Amount Owed 2 Dept 100062607 1 3 28 1996 4 38 52 PM 2125 00 enrolls 100285859 1 3 28 1996 4 38 52 PM 1500 00 Faculty 100371731 1 3 28 1996 4 38 52 PM 1875 00 Person 100822381 1 3 28 1996 4 38 52 PM 1500 00 ElRoom 101042707 1 3 28 1996 4 38 52 PM 3750 00 student 101369010 1 3 28 1996 4 38 52 PM 4500 00 EBTuition 101581226 1 3 28 1996 4 38 52 PM 5100 00 gt Views 101811551 1 3 28 1996 4 38 52 PM 3750 00 a T 1 anonnnr 4 30 01 mes soon on
207. ns about miscellaneous topics I dumped Btrieve records to a file and now can t read the file What happened If you usetheBtrieveM aintenanceUtility to save dump therecords theresulting filecontains the binary image of each record Unless the record consists entirely of character data it may not be readable to the human eye The only way that PSQL can dump a record in ASCII readable format is by reading the DDFs to get a description of thetotal contents of therecord Btrieveonly hastherecord length the data typeof indexes 237 Basic Troubleshooting and length of the indexes Btrieve does not have information on how to interpret the entire contents of the record How do I run PSQL in trace mode Server You must have administrator privileges on the machine wherethe engine is located that you want to run in debug mode 4 Using PCC right click on the desired Server engine then click Properties 2 Click Debugging and set the value for Trace Operation to On 3 Click OK You do not need to restart the engine See also Trace Operation in Advanced O perations Guide Note After tracing operations you should turn off Trace O peration making sure to click Edit gt Apply when finished You will notice slower performance if you run PSQL in trace mode Windows Client Run thePSA network connectivity tests to verify network connectivity See Test Active Installation Tasks Also refer to the Knowledge Base available at the PSQL
208. nsensitive NOW and now are equivalent The parentheses are required That is NOW is invalid but NOW is valid Note You can also omit the seconds for a time data type provided that you include AM or PM For example 10 30 AM sa valid entry Time defaults to AM if you omit AM or PM For example 10 30 00 is entered as 10 30 00 AM To refresh data in the Grid Click G9 on theGrid A refresh re executes the statement last executed in SQL Editor and sends the results to the Grid To copy data from the Grid Perform on of the following actions To select the data for an individual cell click in the cell then click 3 or right click and click Copy By default the entire content of the cell is selected To select an entire row right click on any cell then click Copy Text or click You may also select multiple rows To select multiple rows press and hold down the Shift or Ctrl key then click a cell in each desired row Note When you copy an entire row or multiple rows the rows are pasted in the same layout as they appear in the Text window view You can specify the number of characters between the pasted columns Click Window then expand the PSQL nodein the Preferences tree Click Text Output in the tree and set the desired value for Number of spaces between columns SQL Editor Tasks Text Window Tasks To clear results from Text view Click oon the Text view To select an
209. nt DSN on each client machine WhilePSQL tools can access remote databases without a client DSN present on the client machine ODBC based applications such as M icrosoft Excel and M icrosoft Access cannot do so You must create a client DSN on each client computer that needs to access network databases from local ODBC applications Internal Database Name The method used by PSQL to identify a database is an internal Database Name DBNAM E If you are using ODBC to access the database you need to create a Data Source Name DSN entry that refers to one DBNAME You may set up more than one DSN that refers to the same DBNAM E If the physical location of the data files on the server is changed only the DBNAM E needs to be updated All DSNs remain unchanged Applications Using the MicroKernel Engine PSQL databases that are accessed only through the M icroKernel Enginedo not need DSN s H owever the database tables are not visiblein PCC nor can they be manipulated using PCC To view table data with 27 Using PSQL PCC in readable form the table must be defined in the data dictionary files DDFs See DDF Builder User s Guide Also databases created with the PSQL Java utilities do not requireDSNs ThePSQL Control Center for example uses DBC not ODBC 28 Setting up Database Access with PCC Setting up Database Access with PCC You must know the name of the server where the database is located If the database already has a DBNAM
210. nt Details unique O Partial OvNormal Allow Modifications 144 4 Table Editor Tasks A check mark in the box indicates that the index value can be modified Lack of a check mark indicates that the index value cannot be modified Option State Meaning Values can be modified O Values cannot be modified Ol Modifiable does not apply The default for all SQL data types is that the index column is modifiable Click File gt Save or El before changing pages within Table Editor Foreign Keys Tasks gt To add a foreign key Note that at least one table in the database must have a primary key or you cannot add a foreign key 1 2 3 Va 4 5 Ensure that the Foreign Keys page of Table Editor is active If required perform the steps for To work with foreign keys Click Add Type the name that you want for the new foreign key Tip For alist of database object lengths and invalid characters see Identifier Restrictions by Identifier Type in Advanced Operations Guide Click OK Thenew foreign key appears in the Foreign Keys list and the Foreign Keys D etails display Foreign Key Editor Foreign Keys Foreign Key Details To mplkey Add Select Primary Table Fl Delete Primary Table Fields Foreign Table Fields r Referential Integrity Rules Delete Restrict O Delete Cascade To Select primary key table Click y for Select P
211. nts Security Security contains property settings tabbed areas for Database Security and Btrieve Security See the chapter PSQL Security for a complete discussion of security 65 Using PSQL Control Center New Database GUI Reference The following image shows the dialog with which you create a new database The table below the image describes the GUI objects See also To create a new database Click on an area of the image for which you want more information Figure20 Create New Database Dialog O l Database Options CO Bound Create dictionary files if they do not exist O Relational integrity enforced O Long metadata V2 metadata Database Name Location Database code page Server Default Change code page DSN Options O Create 32 bit Engine DSN Table10 CreateNew DatabaseGUI Elements 66 Element Description Related Material Database The name for the database that you want to appear in the database listing in PCC Identifiers and Object Name Names Note The database name cannot be the same as an existing data source name DSN back to Figure 20 Location This location must be on the same server to which you are connected and where Dictionary Location and the database engine is running Location must be formatted as though you are Data Directories working directly at the server machine back to Figure 20 Bound Indicates whether or not the database is bound Bin
212. nts to connect to X Server Now you can switch to user psq l or root Also sometimes the display environment variables needs to be set As user psql or root type the following command at a console window export DISPLAY 0 0 or export DISPLAY localhost 0 0 You want to view the error log file for PCC or redirect the errors to the console window By default the log file of PCC errors is located in a subdirectory of the user s home directory the subdirectory is dir _pcc workspace metadata For troubleshooting you may find it more convenient to redirect the errors to the console window To redirect errors to the console window use the consoleLog option when starting PCC pcc consoleLog You receive the following error message Unable to connect to database engine Make sure the target machine is accessible and an engine is running on the target machine The context of this error occurs if you attempt to administer the local server To administer the local server you must be a member of the pvsw group or be the root user You receive the error GTK IM Module SCIM Cannot connect to Panel then trying to run PCC as a user other than root On some Linux operating systems it is necessary to specify the environment variable GTK_IM_MODULE To resolve this problem before starting PCC run the following command at the console window export GTK_IM MODULE scim bridge Si
213. o change file encoding and to set a default encoding In addition the Preview inset shows a preview of the data based on thefile name file encoding and the options selected For example the following image shows the class sdf table provided with PSQL about to beimported Encoding is set to windows 1252 and Preview shows what the file data looks like based on that encoding with a COM MA delimiter and no column names in the first row Import Data Class gt Enter file name and provide information about the import data Import From PSQL Demodata restore class sdf Browse First row contains column names Delimiter COMMA Encoding windows 1252 y Change default encoding Preview 160 PHY 204 001 30 1995 06 05 11 00 00 13 50 00 Gimlett Building 106 377319605 161 PHY 305 001 25 1995 06 06 08 00 00 10 50 00 Gimlett Building If you import afilewith aBOM theBOM isrecognized and stripped off during theimport In addition the file is imported with the encoding specified by the BOM regardless of what encoding you specify on the dialog Thatis afile BOM overrides the Encoding selection Note that you can access the standard operating system dialog for file selection by clicking Browse See also Importing Data with Import Data Wizard in PSQL User s Guide Export Data Dialog Export Data now allows you to changefile encoding and to set a default encoding Notethat theExample inset
214. o deauthorize a database license If not select a different server as described in To Select a Server for License Administration Click a Product name in the License Information list You can select multiple keys by holding down the Shift or Control keys and clicking the desired product names Click the button to deauthorize the key Note You cannot deauthorize a key in a failed validation state Either rectify the issue before deauthorizing or call PSQL support for assistance Tip When you deauthorize a product key all increase keys for user count session count or data in use associated with that product key are automatically deauthorized as well gt To Repair a Key Ya 3 i Tip After you have applied a product key on a machine changes to the hardware configuration can cause a key to fail validation or become disabled requiring a repair To avoid the need for a repair deauthorize the key before changing the hardware configuration and authorize the key again after the new configuration is complete Ensure that the server name in the title bar is the server for which you want to repair a key If not select a different server as described in To Select a Server for License Administration In the License Information list click the Product name of the product with the key you want to repair Click Repair 113 Licen 4 gt 1 2 gt 1 2 gt 1 2 114 se Administration In th
215. o execute statements from the Outline window view The Outline view allows you to select multiple statements to execute with Ctrl click For example if your Outline view shows three statements asin the figure above you may choose to execute statements 1 and 3 but not 2 See To run SQL statements in Outline view 154 Working with Common SQL Objects Working with Common SQL Objects Some SQL objects are dealt with commonly Asa conveniencefor you PCC shows the following objects in PSQL Explorer and provides commands for their creation and editing a Triggers Stored procedures a User defined functions m Views When you usecommandsto create one of these objects SQL Editor provides SQL syntax for that object to help you get started For example if you chooseto create a new view SQL Editor containsthe syntax CREATE VIEW lt viewname gt AS Ya Tip You can hover the mouse cursor on a SQL statement to obtain a tool tip on the syntax which also includes an example SQL Editor provides the newly created object with a default name of object_n where object isthe name of the object and n is an integer that starts with one and increments by one For example if you create a new view SQL Editor contains anew tab named View_ 1 After you savethe object with anameof your choice the tab reflects the saved name The following table defines the common objects and refers you to SQL Engine Reference for addition
216. oding allows you to work more easily with files containing wide character data This setting provides the following features An option to usethePCC dialogs for File Open and File Save A list of encodings from which to select the default to be used for the following PCC actions PCC File Open PCC File Save e Import Data Export Data Export Schema To set a preference to use PCC dialogs for File Open and File Save In PCC click Window gt Preferences then File Encoding Make sure that the option Do not prompt for encoding during FileO pen and File Save is not selected By default the option is not selected Click OK To set a preference for default encoding In PCC click Window gt Preferences then File Encoding Select an encoding from the Default encoding list and click OK Table9 Default Encoding Choices Encoding Discussion system code page The default is the system code page currently in use For example on Windows platforms in English you commonly see windows 1252 Big5 Big5 is a Chinese character encoding method used for traditional Chinese characters EUC_JP Extended Unix Code EUC _JP is a variable width encoding used to represent the elements of three Japanese character set standards JIS X 0208 JIS X 0212 and JIS X 0201 Shift_JIS Shift_JIS Japanese Industrial Standards is a character encoding for the Japanese language UTF 8 UTF Universal Character Set Trans
217. of PSQL Control Center Working with Task Right click For information see Triggers Create A database under Databases To create a common SQL Stored Procedures node or object User defined Functions a trigger Views stored procedure See also Triggers Stored user defined function or Procedures User defined view Functions and Views This task can also be performed from any object subordinate to the Engines node Edit A database under Databases To modify a common SQL node or object a trigger stored procedure See also Triggers Stored user defined function or Procedures User defined view Functions and Views This task can also be performed from any object subordinate to the Engines node Delete A database under Databases To delete a common SQL node or object a trigger stored procedure See also Triggers Stored user defined function or Procedures User defined view Functions and Views This task can also be performed from any object subordinate to the Engines node SQL Editor SQL Editor allows you to run Structured Query Language SQL statements against a PSQL database See SQL Editor for a detailed discussion Grid The Grid window view shows in a matrix format like a spreadsheet the result of running SQL statements Each field is represented as a column and the data appears in cells within the columns You can change data directly in the Grid cells as well as add additional rows to
218. ols list 9 Ifyou have performed all of the above tasks with no success at accessing your data contact PSQL Support PCC runs slowly or hangs when retrieving large record sets If this problem occurs try increasing the amount of memory availableto PCC during start up The amount of memory you can specify is limited by the physical memory installed on your machine You can specify aminimum and amaximum amount of memory For example to specify aminimum and maximum of 256 megabytes start PCC with the following command pcc exe vmargs Xms256M Xmx256M The parameter vmargs is required if you specify the other parameters Theparameter xms specifiesthe minimum amount of memory to allocateto PCC The parameter xmx specifies the maximum amount of memory to allocate to PCC If you specify the xms parameter you must also specify the xmx parameter 225 Basic Troubleshooting Error Messages from PCC You may receive several different messages when attempting to create or connect to databases in PCC This section explains the likely causes for some of the most common error messages Can t retrieve database names You don t have access rights for the operation Unableto connect to the specified remote server Verify that all of the communication components are loaded on the remote server and that there are available sessions and try again An error was encountered while connecting to the server Unknown configuration properties
219. ompt for new database each time a SQL document is opened see To set database context for an SQL query Select Always remove associated DSN entries to have all DSN entries for any database automatically deleted along with the database without prompting Clear Do not prompt for new database each time a SQL document is opened to be prompted to select a database each time you open aSQL document in the SQL Editor If this option is deselected select it to usethe most recently selected database when you open a SQL document The selected databaseis not maintained across PCC sessions If you close and reopen PCC you will have to select a new default database context Preferences for PCC Window Views You can set preferences for the following PCC window views DataGrid Defragmenter Monitor SQL Editor Table Editor m Text gt To set preferences for PCC Window Views 1 InthePCC Window menu click Preferences and expand the PSQL node if needed 52 An Overview of PSQL Control Center Perform one of the following actions To set preferences for Data Grid click Data Grid To set preferences for Defragmenter click D efragmenter To set preferences for M onitor click Server M onitor To set preferences for SQL Editor click SQL Editor To set preferences for Table Editor click Table Editor To set preferences for Text Output click Text Output File Encoding Preferences The preference setting for File Enc
220. omputer A local database is a database in which the data files are stored on the hard disk of the computer you are Introducing PSQL currently using Local is the opposite of remote Local can refer to either the client or the server depending on whether you are currently seated at the server computer or a client computer Key Concepts Relational Theterm relational refers to the storage of data in the form of related tables The related tables allow relationshipsto be created between sub sets of data For example you can seethat both our exampletables contain theNamecolumn and someofthenames are the same Because we can cross reference the names in the Phone table with the names in the Food table we havethe power to ask and answer such questions as What isthe phone number of someone who likes steak We may also answer such questions as Which consumer profile purchased the most product B after buying product A You can see how powerful relational data access is The Relational Engine of PSQL provides full relational access to your data Join A join refers to an association between columns of related tables Typically a join operation is part of a SELECT query which is used to obtain information from related tables Unique Benefits of PSQL Oneunique feature of PSQL is that it allows applications to access data through either the industry standard Relational Engine or through the ultra high speed M icroKernel Engin
221. on Wherecan find information on migration from earlier product versions to PSQL v12 Where can find migration and compatibility information When createatable using an existing Btrievefile the wizard displays fewer columns than there are in the Btrieve file What s wrong DEMODATA Sample Database How do restore DEM ODATA to its installation defaults 4 Start PCC if it is not already running See Starting PCC on Windows 2 In PCC click File gt New SQL Document or si See To start SQL Editor for anew SQL query The Select Database dialog appears 229 Basic Troubleshooting Select Database Select the database to use with the current editor or check None None JWYNN C8256T anonymous 9 Databases B DEFAULTDB E B DOPE B TEMPOS 3 Click DEMODATA in thelist then click OK SQL Editor appears as a new tab view in PCC 4 Click File gt Open 5 Navigate to the location of the DEM ODATA sql file then click Open For default locations of PSQL files see Where are the PSQL files installed in Getting Started With PSQL 6 Click SQL gt Execute All SQL Statements press F10 or click Gl DEM ODATA sql deletes the existing tables then recreates them using the installation defaults The restored tables are empty no data Any new tables that you have created as part of DEM ODATA arenot affected 7 Usethe Bulk Data Utility BDU to populate the tables with data see bdu a Open a command prompt
222. on at a time can cause a failed validation You can correct what you think is the sole condition yet the key remains in the failed validation state If so refer to the PVSW LOG file for more information That log file contains all of the conditions causing the failed validation See PSQL Event Log PVSW LOG in Advanced Operations Guide If the changes that caused the failed validation are due to intentional hardware upgrades performed without first deauthorizing the key the next step is to repair the key Repair License Administrator provides the functionality to perform a repair on a key that is disabled or has failed validation M oreover it not only repairs the key it automatically deauthorizes the key and then authorizes it again on the same machine You can perform arepair using either the graphical user interface see License Administrator Graphical User Interface or the command line interface see License Administrator Command Line Interface If the problem is more significant than a change in hardware for example the machine on which a key isinstalled no longer boots you cannot repair the key In that case contact support for assistance Increase User Count or Data In Use Situations can arise in which you want to increase user count or data in use from theinitial amount provided by the permanent product key You increase the amount by authorizing an increase key Multiple increase keys can be authorized on a product key T
223. on request If you obtained your product key from Actian Corporation call code to phone support Actian Corporation support and give them the Authorization Request Code displayed in this step Authorization Request Code xxxx xxxx If you did not obtain your product key from Actian Corporation contact the vendor or original equipment manufacturer OEM from whom you obtained the key Provide the vendor or OEM personnel the Authorization Request Code displayed in this step XXXXTXXXXXXXX XXXX XXXXXXXX 3 Enter the authorization key provided Enter the Authorization Key provided by PSQL Support or by the by support XXXXX XXXXX XXXXX XXXXX vendor or OEM personnel to authorize PSQL XXXXXXXXXX See Also License Administration documents License Administration in detail 197 Command Line Interface Utilities dbmaint Description Thedbmaint utility manages named databases Note Thisutility can only berun by user accounts belonging to group pvsw See Getting Started With PSQL for information on PSQL Linux utilities and user accounts Synopsis dbmaint a d 1 m nDbname a b c i e ldictpath ddatapath ssecuritymode add new database name a nD bname b i e Idictpath ddatapath delete database name d nD bname list database names 1 a The a option displays the full information about the dbnames modify database name security policy m nDbname ssecuritymode Options
224. on theleft side of the window that containsa list of machines to which you are connected Note The machines listed in your PSQL Explorer will remain between sessions To remove a machine right click the machine name and click D elete Enter the Server name where the PSQL v12 database engine resides You need to be authenticated on the remote engine and a dialog displays prompting you for a user name and password Enter the user name and password in the appropriate fields and click OK You are now connected to the remote PSQL engine gt To set up a Client DSN using ODBC Administrator 1 2 3 In PCC click Tools and select ODBC Administrator Note Windows 64 bit operating systems contain two different executable files for ODBC Administrator one for 32 bit DSN sand one for 64 bit DSN s Each ODBC Administrator lists the system DSNs that only match its bitness For the 64 bit PSQL Server or Workgroup PCC contains separate options in the Tools menu to start the 32 bit or the 64 bit ODBC Administrator If an ODBC Administrator is already open Windows defaults to it That is if the 32 bit ODBC Administrator is open and you attempt to start the 64 bit one Windows displays the 32 bit version and vice versa This is a limitation of the Windows operating system not PSQL Click the System DSN tab then Add Note PSQL does not support File DSNs You must use User or System DSN ss System DSN s are generally preferred
225. on user tasks The guide discusses the database engine PSQL utilities and other key components the differences between Server and Workgroup engines and the differences between ODBC and Btrieve access PSQL User sGuideprovides you with the basics to work with PSQL successfully Advanced Operations Guide Advanced O perations Guide provides detailed information at the administrativelevel including thesteps to perform common procedures and several new ones Topics include checking database consistency performing periodic backups configuring network protocols and understanding network topologies working with database security basic configuration guidelines configuration options reference moving renaming compacting and rebuilding files SQL Engine Reference SQL Engine Reference gives database programmers a complete reference guide to the SQL relational database language It also covers SQL engine parameters and limitations Status Codes and Messages Status Codes and M essages documents all possible status codes and numbered messages that can be received when using PSQL The Status Codes Quick Reference is also included with your complete documentation set Additional Information You can download the product documentation white papers technical papers and use the online library at the Actian PSQL website 13 Introducing PSQL File System Security ThePSQL engine adheres to the file system security de
226. ons available on the server Check MicroKernel Communication Statistics For Total Remote Sessions if the Peak value and the Maximum value are the same then it is likely that you have run out of sessions 4 Theremote database server is not running Solution M ake sure that the remote database engine is running or ask your network administrator to do so 5 Theremote database server is not accepting client requests Solution Set the properties to ensurethat theremote database engineis configured to accept remote requests You must have administrative permission on the server or membership in the Pervasive Admin group in order to do so In PCC right click on the server name in PSQL Explorer then click Properties Click Access and ensure that the Accept Remote Request option is selected An error was encountered while connecting to the server The most likely cause of this error is using the wrong operating system user name or password in an attempt to connect to the server Other possible causes include Theoperating system may be expecting the user to change his her password on the first logon This situation occurs if in the User M anager you have selected the User Must Change Password at Next Logon checkbox a Iftheuserisamember of another group with lesser permissions thelesser permissions will override the greater permissions A user always has the most restrictive permissions of any group to which the user belongs So
227. oom Office Classroom Classroom Office Office Office Office Woodward Building If execution stops because of an error the Text window view lists the statement that was last run Knowing the last statement run can help you troubleshoot problems Scrolling and Positioning Asaconvenience the Text window view automatically scrolls to the top line of the data returned by the last statement executed For example suppose that you execute the following two statements sequentially in SQL Editor each time sending the results to the Text window view SELECT FROM Class and SELECT FROM Billing TheText window view automatically scrolls to the top of the data returned by SELECT FROM Billing the last statement executed Outline Window View The Outline window view allows you to view the SQL statements in a tree structure The root node of the tree is the same name as the name of the SQL Editor session to which the outline corresponds 153 SQL Editor Figure25 Outline Window View E Room 23 g SELECT FROM Room sels oor SELECT Type FROM ROOM WHERE Type Office SELECT FROM Room SELECT Building Name FROM ROOM WHERE Building Name Holland Hall SELECT Type FROM ROOM SELECT Building Name FROM ROOM Thenumber of words displayed in the Outline window view depends on your preferences setting To set preferences for SQL Editor click SQL Editor You can als
228. ory dsnadd dsn USInvoices db DomesticOrders host USInventory The following example creates a deprecated legacy style Client DSN named bkordercint that references an engine DSN named backordersrv on a machine named JapanSvr2 and uses automatic encoding dsnadd dsn bkorderclnt sdsn backordersrv host JapanSvr2 translate auto clntdsn Thefollowing example creates a deprecated legacy style engine DSN named partsctlg that connects to a database named partscatalog dsnadd dsn partsctlg db partscatalog engdsn Notes On Linux distributions individual ODBC drivers are loaded through the driver manager UNIXODBC Thedriver manager maintains a mapping from Data Source Names DSN s to the specific PSQL ODBC drivers Theinstallation of PSQL Server 64 bit or Client 64 bit leaves user defined preexisting 32 bit DSN s as is meaning they arenot immediately accessiblefrom a 64 bit application For new DSNs theinstallation of both products assigns a 32 bit and a 64 bit ODBC driver in odbcinst ini This assignment allows a single DSN to be used by both a 32 bit application and a 64 bit application If you want pre existing 32 bit DSN sto be accessible to both 32 bit and 64 bit applications you must recreate them as DSN s that connect to a named database ODBC and Data Source Names DSNs Theapplication bitness does not haveto match the bitness of the PSQL Server product For example the 64 bit ODBC dri
229. ote You can also access the File Encoding preference setting from the dialogs for File Open Save File Import Data Export Data and Export Schema Click Change default encoding in the dialog Additional Utilities Some utilities have not yet been tightly integrated within the PCC framework However they may still be started from within PCC by selecting them through the Tools menu ODBC Administrator Separate choices for 32 bit Administrator and 64 bit Administrator on 64 bit operating systems see DSN Setup and Connection Stringsin ODBC Guide Notethat Windows uses whichever ODBC Administrator is open if you try to invoke the other one That is if the 32 bit ODBC Administrator is open and you attempt to start the 64 bit one Windows displays the 32 bit version and vice versa In other words only one version of ODBC Administrator runs at a time This is alimitation of Windows rather than PSQL DDF Builder see Getting Started with DDF Builder in DDF Builder User s Guide Defragmenter see Monitoring Data File Fragmentation in Advanced Operations Guide m Function Executor see Testing Btrieve Operations in Advanced Operations Guide a License Administrator see License Administration Maintenance seeM anipulating Btrieve Data Files with M aintenancein Advanced O perations Guide Monitor see Monitoring in Advanced Operations Guide m PSQL System Analyzer see PSQL System Analyzer PSA Query Plan Viewer see Quer
230. ou first need to find the correct location 1 Begin by looking for aWow6432N ode If it exists edit the proxy server Registry settings in the Pervasive Software ELS key 2 If it doesn t move to the Software directory and edit the proxy server Registry settings in the Pervasive Software ELS key After locating the correct key use the following procedure 1 Modify the Windows Registry and add the following to the Pervasive Software ELS key at the correct node String Value proxy_host DWORD Value proxy_port String Value proxy_userid String Value proxy_passwd Note The proxy_host and proxy_port values are the only ones required by PSQL Depending on the configuration of your proxy server you may also need to add proxy_userid and proxy_passwd 3 Caution Editing the Registry is an advanced procedure If done improperly the editing can cause your operating system not to boot If necessary obtain the services of a qualified technician to perform the editing Actian Corporation does not accept responsibility for a damaged Registry 101 License Administration 2 AuthorizePSQL using License Administrator See To Authorizea Key for the GUI and To Authorize a Key for theCLl Linux 14 Manually edit the ELS INI file to include the following proxy_host proxy_port proxy_userid proxy_passwd Note The proxy_host and proxy_port values are the only ones required by PSQL Depending on the configuration of your proxy server
231. our client requester can communicate with the database engine Click Next Click Finish if you are finished using PSA or click View Log File to view the log To test the Relational Engine Start PSA and click Next On the Options dialog click Test active installation if it is not already selected Click Test relational engine if it isnot already selected This test runs a series of SQL operations to simulate your client requester interacting with the database engine Click Next For Machine Name typethenameor IP address of the machine where the engine data source name DSN resides or browseto the machine click Thename localhost is valid if you are testing the local machine For Engine DSN typethenameof the DSN for the data source you want to test The default demodata uses the DSN for the sample database installed with the database engine Select the operations you want to perform for the Relational Engine by default all are selected Create Table Read Data UpdateData Insert Data Click Next PSA performs the tests and displays the results A check mark indicates a test succeeds and an x indicates a test fails If all of the tests succeed your PSQL Client can communicate with the Relational Engine Click Next 10 Click Finish if you are finished using PSA or click View Log File to view the log Log Files Tasks gt To specify a different location for the PSA log file 1 2 Start PS
232. ows the dialog for the Billing table that is part of the sample database DEM ODATA Add Student_ID UBIGINT Transaction_Number USMALLINT Reset Log 01 11 2005 8 52 10 AM TIMESTAMP Amount_Owed DECIMAL Amount_Paid DECIMAL Registrar_ID UBIGINT Comments CHAR Refresh Grid on erit 2 Click in the Value cell for each Column Name and type the desired value The value must be a data type valid for that column Add Student_ID 998444721 UBIGINT Transaction_Number 1 USMALLINT Reset Log 01 11 2005 9 04 12 AM TIMESTAMP Amount_Owed 1652 50 DECIMAL Amount_Paid 1152 50 DECIMAL RegistrarID 130312616 UBIGINT Comments Check scholarship fund for balance CHAR J Refresh Grid an eri Tip You can copy data from Grid cells and paste it into the Value cells Click on a Grid cell then right dick Click Copy Click on a Value cell on the Add Rows dialog then right click Click Paste Also note that Ctrl C and Ctrl V provide the copy and paste actions respectively 3 Click Add 164 s3 2 3 SQL Editor Tasks The record is added to the table Also note that the option Refresh Grid on Exit becomes enabled If you want to add multiple records you can change values for specific value cells then click Add If you want to clear all of the value cells click Reset Optionally click Refresh Grid on Exit if you want the table data refreshed Hegistrar_ID Comments When you close the Add Rows dialog a refresh re executes the
233. p using PSQL Explorer To assign permissions for a user using PSQL Explorer To assign permissions to all users using PSQL Explorer To assign permissions for a group or user using SQL Encryption Tasks Apply to data encryption See Data Encryption in Advanced Operations Guide General Tasks gt 4 gt To log out from and log in to a database Note As the M aster user logging in as another user can aid you in testing the more restrictive permissions you have assigned this user Right click on the database namein the PCC PSQL Explorer then click Logout name Name reflects the name of the user currently logged in to the database If the database does not have security enabled nameis Master Name may also be M aster if the current user is logged in as M aster Any nodes expanded for the database are collapsed Right click the database name Click Login Type the user name and password then click OK To turn on security using PSQL Explorer If the database resides on a remote machine you must provide a user name and password of an administrator or of amember of the Pervasive Admin group for the remote machine The user name and password isnot required if the database resides on thelocal machineto which you arelogged in and the local machine is not running Terminal Services Turning on security prevents all users from accessing the database unless they login to it using a valid database user name and pa
234. ple inset does not contain a preview of the literal schema Rather it shows an example of what the exported table schema looks like given the selected options The Exampleinset is updated when afileis chosen and when encoding or either option is changed For example in the following image the dialog is set to export the schema for the Faculty table provided with the DEM ODATA sample database Note that the option add IN DICTIONARY clause to CREATE statements is selected and that the Example inset contains an IN DICTIONARY clause Export Table Schema Enter file name to export CREATE TABLE statements Export To C users public faculty_schema_export sq Browse V add IN DICTIONARY clause to CREATE statements add USING clause to CREATE statements Encoding windows 1252 Change default encoding Example CREATE TABLE Faculty IN DICTIONARY USING Faculty mkd Column1 Name INTEGER NOT NULL Column2Name CHAR 50 NOT NULL Notethat you can access the standard operating system dialog for file selection by clicking Browse 94 Wide Character Data Support for Import Data Export Data and Export Schema See also Exporting a Schema in PSQL User s Guide 95 UsingPSQL Control Center 96 License Administration E chapter License M odels and Working with Keys This chapter covers the following topics a License Administration Concepts a License Administrator Gr
235. pplicationsUsingtheMicroKerne Engine aaa ee 27 Setting up Database Access with PCC 1 ee 29 Setting Up DatabaseAccessonWindowS 1 0 ee es 29 Setting Up Database Access on a Linux Server o o e es 30 Setting Up Client Access from aWindowsClient 0 0 eee ee ees 31 Setting Up aClientDSN onaLinuxClientt 0 ee es 33 AccessingDataonaRemoteEngineUsingPCC 1 es 34 Accessing Data via ODBC From Other Applications 0 0 cee ees 36 BeforeYOUBEGIN es 36 AccessingDataUsingMicrosoftExcel 2 o 2 e 36 Accessing Data Using Microsoft ACCESS o ooo ee 37 DAGINGIDSNS a eosi aana A dha ee 41 3 Using PSQL Control Center ee 43 A Tour of PSQL Control Center An Overview of PSQL Control Center aoaaa a o e 44 NristallinG PCC lt A des EO ae oes OE e 45 StartingPCC ON WiNdOWS o o o o 45 Starting PEC ON Linux as nee As la la tna za dale A 45 Situations Requiring That You Clear PCC Cache aoaaa aaa ees 46 Editors and Views Within PCC oaaae ooo 47 PNC o ar e A A Ba eae ae Band areas Sd waa BOs 52 Additional Utilities ee 54 External 00534 rs pte hee ahh be Soe hd ead pd ek Wh ed ty ho a ah RIS pu ded 55 Services on Windows Servers 0 es 56 Services Prop rties zpi a aa E ER GARA aoe eed are SES ek ae EY AAE 56 DatabaseEnginsS 2 ee 57 Database Engine Properties aaa a 58 Cap
236. pports only named databases The 64 bit Client Interface can connect to alocal named database thus replacing the function of the Engine DSN or to aremote named database Connection to an Engine DSN isnot supported Note Because 32 bit Engine DSN shave been deprecated Actian Corporation recommends that new or revised 32 bit applications connect to a named database through a Client DSN or usea DSN less connection by specifying Pervasive ODBC Client Interface See To set up a Client DSN using ODBC Administrator mo In PCC click Tools then ODBC Administrator Click the System DSN tab then Add In thelist click Pervasive OD BC Engine Interface Click Finish hh OO N 29 Using PSQL 8 9 The Pervasive ODBC Engine Interface dialog displays Type a Data Source Name For Database Name select in the list for which you want to create the Engine DSN Click Advanced if you want to specify advanced connection attributes See Advanced Connection Attributes in ODBC Guide Click OK Click OK Setting Up Database Access on a Linux Server gt To set up a named database on a Linux server Database names are created in Linux by using the dbmaint utility at the server For a complete description of domaint see domaint or read the domaint man page 30 Note This utility can only berun by user accounts belonging to group pvsw See PSQL Account Management on Linux in Getting Started With PSQL To create an empt
237. property nodes Code Page Directories General Relational Constraints Security Code Page This section details the property settings for Code Page y 62 Database Code Page PCC Connection Encoding Note The database engine does not validatethe encoding of the data and metadata that an application insertsinto a database The engine assumes that all text data was translated by the client to the encoding of the server or the client database code page as explained in Database Code Page and Client Encoding in Advanced O perations Guide Databases Database Code Page This property specifies the encoding to use for metadata and is stored in DBNAM ES cfg Note that this property applies to the database which means that it potentially affects all client applications that exchange data with that database A compatible encoding must be established between the PSQL database engine and a client application See Database Code Page and Client Encoding in Advanced Operations Guide for the various ways in which this can be accomplished Note Changing the database code page does not convert existing data or metadata in the database To avoid data corruption ensurethat the code page setting matches the current encoding of any pre existing data or metadata in the database Database code page is particularly handy if you need to manually copy PSQL DDFsto another platform with a different OS encoding and still have the metadata correct
238. r if you use the alternate File Save dialog to save your changes the changes are saved to the encoding specified on the dialog PCC automatically detects the encoding of a file if the file contains a BOM and displays that encoding in the alternate File Open dialog If the file does not contain a BOM and byte order is important you need to select the proper encoding for the save action Default Encoding In addition to changing thefile encoding during theopen or saveaction you can set the default encoding to use for opens and saves This is also the default for importing data exporting data and exporting a schema See Importing Data with Import Data Wizard Exporting Data with Export Data Wizard and Exporting a Schema The same default applies for all of these You cannot set different defaults for the Open and save actions for example gt To set a default encoding for File Open and File Save 1 In PCC click Window gt Preferences then File Encoding 2 Select the desired encoding from the Default encoding list The choices are explained in Encoding Choices for File Open and File Save Dialogs 3 Click OK Note You can also access the File Encoding preferences from the dialogs for File Open Save File Import Data Export Data and Export Schema Click Change default encoding in the dialog Invalid File Name Characters The alternate dialogs check for valid characters in a file name before allowing the open or
239. r is the server you want If not select a different server as described in To Select a Server for License Administration 2 Press theF5 key Theinformation for applied licenses is re displayed and any information is cleared from the Key field The Server Name field is cleared then displays the machine name to which License Administrator is currently connected For example suppose License Administrator is connected to PVSW 1 and you attempt to connect to PVSW 2 which does not have a database engine running License Administrator displays a message that it could not connect and PVSW2 is displayed in the Server Name field After you click the F5 key PVSW 1 is displayed in the field gt To Display Help 1 Click Help from the menu bar of the License Administrator The License Administrator Graphical User Interface section opens It enables you to identify and understand the purpose of the objects on the GUI 2 To consult other areas of documentation about the License Administrator access the Table of Contents for the documentation and click the desired area 115 License Administration License Administration Concepts to understand the basics of License Administrator License Administrator Command Line Interface to understand the options and parameters for the CLI e License Administration Tasks to perform activities with License Administrator CLI Tasks Note For all of thefollowing tasks the Windows 64
240. r the table being edited To save changes for all tables being edited To undo changes or to redo changes Columns Tasks Column tasks apply to the Columns page To insert acolumn between existing columns To insert acolumn at the end To select acolumn or multiple columns 127 Table Editor To delete a column m To specify a column name To seta column data type To seta column size m To set column precision To seta column scale To s amp t a column to allow or disallow nulls To set case sensitivity for a column Tosetacolumn collating sequence Tosetacolumn default Toset or remove a column asa primary key Indexes Tasks Index tasks apply to the Indexes page To create an index To create a unique index To create a partial index To modify an existing index m To delete an index TO insert an index segment To modify an index segment To delete an index To arrange the order of index segments To specify a sort order for an index To allow duplicates in an index To specify index as modifiable Foreign Keys Tasks Foreign keys tasks apply to the Foreign Keys page To add a foreign key m To modify a foreign key To delete a foreign key SQL View Tasks SQL view tasks apply to the SQL View page m To copy SQL statements a To maximize or restore view of SQL statements 128 Table Editor Tasks General Tasks gt To start Table Editor for a new table 1 2 3
241. re To upgrade from Workgroup to Server typically requires no changes to your application or to your database Simply install the new version and you are ready to go Because PSQL Vx Server uses a different license model it requires a full installation You cannot upgrade PSQL Server or Workgroup to PSQL Vx Server PSQL Workgroup PSQL Workgroup offers a peer to peer network setup designed for stand alone single user installations up to small workgroups The Workgroup engine is the only engine that offers multiuser access to PSQ L data located on a computer where no database engine is installed A major difference between Workgroup and Server is the Gateway feature of Workgroup When thereis no database engine running on the computer where the data is located normally the first database engine to connect to that data handles all requests from other engines to access that data This feature can be configured so that the same Workgroup engine always services that data or the Gateway designation can beallowed to float based on which Workgroup engineconnectsto thedatafirst during any given work day The Workgroup product uses the concurrent user licensing model See License M odels PSQL Server PSQL Server offers a full client server architecture providing excellent performance and scalability for up to thousands of concurrent users The Server engine can be monitored and configured remotely No additional licenseis required for use with hype
242. remote server Verify that all of the communication components are loaded on the remote server and that there are available sessions and try again You may receive this error when attempting to register a new remote server in PCC There are several reasons you may receive this error 1 You mis typed the server name The database client tried to connect to a server that does not exist Solution D ouble check the name of the server and make sure you can seeit in your Network Neighborhood spelled exactly how you entered it If you know theserver exists but you can t seeit in your Network Neighborhood makesurethat you are logged into the correct network Ask your network administrator for help 2 Theserver user count has expired If you have been using a temporary license you will get this message for connection attempts after the license has expired 226 Error M essages from PCC Solution Run License Administrator to check the status of licenses installed on the server In the window that appears you can see detailed status information on each license that has been applied to your server If your license has expired purchase a permanent license from your reseller or from Actian Corporation 3 Thereareno available sessions on the server If you havea heavy load of users on the server or if you have configured the server with asmall number of sessions you may receive this error Solution Run M onitor to check the usage of sessi
243. rence Changing a column data type sets the defaults for that type on the following size scale precision default and collate 1 Ensurethat the Columns page of Table Editor is active If required perform the steps for To work with columns 2 Click in the Type cell for the desired column Open thelist for data types click y 4 Scroll to the data type you want You may also type the first letter of the desired data type to scroll Repeatedly typing the first letter scrolls to each data type that begins with that letter 132 5 6 Table Editor Tasks Click the data type you want Click File gt Save or El before changing pages within Table Editor Note Changing a data type on a column that contains a default value causes the default valueto be cleared reset the value if necessary gt To set a column size Datain your databaseis truncated if you changethe column to a smaller sizefor the following data types 4 5 CHAR NUMERIC VARCHAR Ensure that the Columns page of Table Editor is active If required perform the steps for To work with columns Click in the Size cell for the desired column You can set asize only for applicable data types such as CHAR If sizeis not applicable the grid cell is shaded and you will be unable to edit the Size Delete the existing size Type the size you want Click File gt Save or El before changing pages within Table Editor gt To set column prec
244. rimary Table to display the list of tables permissible as primary tables 145 Table Editor 6 7 10 11 Foreign Key Editor Foreign Keys Foreign Key Details Po mplkey Add Select Primary Table MyPKT able Delete mytab able MyPRT Primary Table Fields Foreign Table reids Click the desired table in the list only tables with a primary key appear in the list Theprimary field s in thetable appear in Primary Table Fields column Foreign Key Editor Foreign Keys Foreign Key Details Po mplkey Add Select Primary Table MyPKT able X Delete Primary Table Fields Foreign Table Fields Coll PKT able Col2PKT able Match fields in the foreign table with fields in the primary table Click the empty cell in the Foreign Table Fields column for thecorresponding field in the Primary Table Fields then click to displays the list of permissible fields Foreign Key Editor Foreign Keys Foreign Key Details o mpikey Add Select Primary Table MyPKT able El Delete Primary Table Fields Foreign Table Fields Coll PKTable 7 Col2PKT able Coll FRevT able Note The data type and size of the fields must match The list of Foreign Table Fields contains only fields that are the same data type and size as the primary table field being matched Click the desired field in the list
245. rmation as you see fit It verifies that the data inserted fits within defined attributes for the database fields Allowsyou to analyzerelationships among different sets of data The DBM S stores thedatain a way that allows you to examine how any piece of data relates to any other piece of data In summary the DBM S organizes your data keeps it safe and helps you to useit and understand it Components of PSQL ThePSQL DBMS consists of a variety of components designed to help you achieve your data management goals MicroKernel Database Engine TheM icroKernel Engineis the high performance heart of PSQL This engine works directly with the data fileson your computer s hard disk When requested it directly inserts new data deletes unnecessary data and ensures thesafety and integrity of the data files at all times even when people and applications are working with the data Relational Database Engine The Relational Engine interacts with the M icroKernel Engine and the Client described below It provides many powerful features including support for M icrosoft ODBC sophisticated search and analysis capability and security Client also called Requester In client server systems the Client resides on the computer workstation The client interacts with the client application and across the network with both the MicroKernel Engine and the Relational Engine on the server Introducing PSQL PSQL Control Center ThePSQL Control
246. rmation on the tests it performs You can use the PSA log file this log file to review tests at a later time or to forward them to PSQL Technical Support depending on the situation The Append to the log file option adds content to the end of the log file to provide a running history If this option is cleared the log file starts anew and only information from the current PSA session is captured 173 PSQL System Analyzer PSA PSA Tasks General Tasks To start PSA View Modules Tasks To select options for View Modules Test Active Installation Tasks To test your network To test the M icroKernel transactional Engine To test the Relational Engine Log Files Tasks To specify a different location for the PSA logfile To view the log file at the completion of PSQ L System Analyzer General Tasks gt To start PSA 1 Access PSQL System Analyzer from the operating system Start menu or Apps screen or execute psawizrd exe from acommand prompt For default locations of PSQL files see Where are the PSQL files installed in Getting Started With PSQL View Modules Tasks gt To select options for View Modules 1 Start PSA and click Next 2 Click View Loaded PSQL Modules 3 Click Next A dialog appears similar to the following 174 PSA Tasks Figure27 View Modules Section of PSA Module Types Search Locations IV Pervasive engines and requesters M In Memory TF Pervasive utilities MV PAT
247. rmine the Data In Use Limit If you deauthorize the permanent product key all increase keys associated with that permanent key are also deauthorized If you deauthorize an increase key only the data in use increase associated with that key is deauthorized Note that the database engine logs a message to the PSQL logging repositories when the limit for data in use is increased The message contains the current limit and usage See also M essage Logging You can monitor the current peak and maximum values of data in use with the M onitor utilities See Monitoring in Advanced Operations Guide Trial versions of the software include a trial license that have a set value for datain use that cannot be increased Message Logging M essages pertaining to licensing are logged to the various logging repositories used by PSQL such as Notification Viewer See Reviewing M essage Logs in Advanced O perations Guide 104 License Administrator Graphical U ser Interface License Administrator Graphical User Interface The graphical user interface GUI License Administrator runs only on Windows platforms and allows you to authorizeand deauthorizekeys increase user count and datain uselimits monitor session counts and view license information You can start the GUI as a stand alone application or from within PSQL Control Center PCC You can administer licenses for local or remote database engines with the GUI See also GU Tasks for the tasks sp
248. rom Btrieve 6 15 Frequently asked questions about Btrieve 6 15 Is there a tool that replaces Xtrieve Thereisno direct replacement but you should consider using Crystal Reports for Btrieveas an excellent upgrade from Xtrieve for reporting on and querying Btrieve data Upgrading and Migration Frequently asked questions about upgrading and migration When I create a table using an existing Btrieve file the wizard displays fewer columns than there are in the Btrieve file What s wrong Btrieve files contain alimited amount of information about the structure of the file The table creation wizard can figure out some field definitions using the indexes but after the indexes are exhausted data segments may remain that contain morethan one actual field Thewizard hasno way of interpreting the contents You must use your detailed knowledge of therecord structure to split out these fields and build a table definition that matches all the fields in the record The procedure for this task is provided in Advanced O perations Guide Where can find information on migration from earlier product versions to PSQL v12 Where can find migration and compatibility information Getting Started With PSQL contains an entire chapter that provides detailed instructions on how to upgrade If your application uses Scalable SQL or ODBC then you should review theApplication M igration Guide available on the web site Miscellaneous Frequently asked questio
249. rompt clilcadm lt generate g gt product key output filenameath 2 Savethefileto a portable storage device or in a location that can be easily accessed by the machine that has Internet connectivity Ya Tip If the machine with Internet connectivity does not have PSQL installed you must also copy the licgetauth exe file from the PSQL bin folder to the portable storage device or remote location Phase 2 Transmit Authorization Request D ata and Retrieve Authorization Key Data 1 AtmachineB with Internet connectivity connect to the portable storage device or access the location where you saved the Authorization Request Data file 118 License Administration T asks 2 Enter thefollowing at a command prompt where output_filename ath isthe file you created in step 1 of Phase 1 licgetauth exe output_filename ath The above command retrieves the Authorization Key Data used to authorize the key and the product The authorization key data is saved as a pky file to the same location where you saved the authorization request file Ya Tip If you saved the Authorization Request Data and Authorization Key Data files to a hard drive you will need to copy these files to either a portable storage device or aremote location Phase 3 Install Authorization Key Data 1 Back at machineA without Internet connectivity connect once more to the portable storage device or access the location wherethe authorization key data was sav
250. roup Type the name that you want for the group Click Finish To create a new user using PSQL Explorer Turn security on for the database as explained in General Tasks Expand thenodes for the database Right click on the Users node then click New gt User Type the name that you want for the user Type a password for Password and re type it for Confirm Password Passwords are case sensitive For alist of database object lengths and invalid characters see Identifier Restrictions by Identifier Type in Advanced O perations Guide Optionally assign the user to a group Click for Group then click the desired group in the list Click Finish 85 Using PSQL Control Center gt To assign a user to a group using PSQL Explorer Note that a given user cannot bea member of morethan onegroup All usersin a group have exactly the permissions defined for that group You cannot grant or revoke individual permissions for a user who is amember of a group 1 2 3 4 5 6 gt Turn security on for the database as explained in General Tasks If the desired group does not exist create the group as explained in To create a new group using PSQL Explorer Right click on a user name under the Users node then click Properties Click General in the Properties tree Click for Group then click the desired group in the list Click OK To delete a group or user using PSQL Explorer Note that a group can be deleted only if no
251. rvisor features such as live migration failover fault tolerance FT high availability HA and disaster recovery The Server engine must be located on the same computer as the data files it is intended to access PSQL Server uses the concurrent user licensing model See License M odels PSQL Vx Server PSQL Vx Server has the same capabilities as PSQL Server except that its license model supports service bureau software as a service or other environments with multiplexed clients PSQL Vx Server has no restrictions on hosting connection pooling Internet or intranet use PSQL Vx Server uses the capacity based licensing model See License M odels 10 Understanding the DBM S Products Because PSQL V x Server is built on technology used for PSQL Server it shares most of the database functionality and documentation of PSQL Server Throughout the PSQL documentation all topics for PSQL Server also apply to PSQL Vx Server unless differences are explicitly cited Crystal Reports for PSQL Crystal Reportsis an optional product that providesrich capabilities for creating and formatting reports based on PSQL databases Reports can be customized in thousands of ways and published as HTML Microsoft Word document M icrosoft Excel document or other formats For moreinformation about Crystal Reports contact your sales representative or visit the Actian PSQL website 11 Introducing PSQL Using PSQL Documentation All PSQL document
252. s Optional 0 zero The maximum number of errors that the BDU ignores before exiting The BDU exits when the first error is encountered lt r R gt reject_file Optional stderr Name of the file to which to write the rows that failed load The specified directory must exist However if the file does not exist in the specified directory it will be created lt f F gt first_row Optional Row 1 The first row in the delimited text file with which the load begins This parameter allows you to skip a header row For example if your header row is row 1 set first_row to 2 lt I L gt last_row Optional End of the source file Last row in source file row will be included in load The load will stop after the end row has been loaded lt t T gt field_term Optional A character such as a Column delimiter in the source file comma or tab character lt n N gt row_term Optional A new line character Row delimiter in the source file lt o O gt output _file Optional stderr Name of the file to which to write the information and error messages during load The specified directory must exist However if the file does not exist in the specified directory it will be created lt u U gt login_id Optional User name to connect to the Relational Engine lt p P gt password Optional Password to connect to the Relational Engine 186 Command Line Interface Utility Reference Table17 Bulk Data Utility
253. s UTF 16LE encoding bdu demodata Billing D billing data_import txt c UTF 16LE To run the BDU for a database that requires username and password bdu demodata BDU Table C data_file txt u lt username gt p lt password gt To run the BDU with max errors option bdu demodata BDU Table C data_file txt e lt no of errors user wants to allow gt For instance for loading to continue until 100 errors have occurred bdu demodata BDU Table C data_file txt e 100 To run the BDU with a specific column delimiter option bdu demodata BDU Table C data file txt t lt column delimiter gt Example When the source file contains text in which each row is separated by 189 Command Line Interface Utilities bdu demodata BDU Table C data_file txt t To run the BDU with a specific row delimiter option bdu demodata BDU Table C data_file txt n lt row delimiter gt For instance when the source file contains text in which each row is separated by n bdu demodata BDU Table C data_file txt n An To run the BDU with a specific start row option bdu demodata BDU Table C data_file txt f lt line no from which user wants loading to begin gt To run the BDU with a specific end row option bdu demodata BDU Table C data_file txt l lt line no at which user wants loading to end gt You may combine parameters To load the first 15 rows from the source file containing data that is separated by and is enclosed in
254. s and hold Shift or Ctrl then click 4 for the desired additional columns 131 Table Editor gt To delete a column 1 Ensurethat the Columns page of Table Editor is active If required perform the steps for To work with columns 2 Right click anywhere on the desired column row 3 Click Drop Column Ya Tip You can also delete a column by clicking an existing column row then pressing Ctrl D elete or clicking Ei You can also delete multiple columns by selecting multiple columns rows See To select a column or multiple columns 4 Click File gt Save or Hl before changing pages within Table Editor gt To specify a column name 1 Ensurethat the Columns page of Table Editor is active If required perform the steps for To work with columns 2 Clickin the Column Name cell for the desired column 3 Deletethe existing column name 4 Typethename you want Tip For a list of database object lengths and invalid characters see Identifier Restrictions by Identifier Type in Advanced Operations Guide Also asa general rule avoid using reserved words for column names See Reserved Wordsin SQL Engine Reference See also SQL Grammar Support in ODBC Guide 5 Click File gt Save or before changing pages within Table Editor gt To set a column data type The data in your database is converted if you change a column data type For a listing of data types see PSQL Supported Data Types in SQL Engine Refe
255. s granted by a license agreement The value for data in use increases when a data fileis first opened Subsequent opens to an already open data file do not add to the total Data in use also increases if an open file increases in size Operations on an already open file continue to be permitted even if the size of the open file increases beyond the data in use limit The value for data in use decreases when a data file is closed by the final user to have the file open Since more than one user can access the same data file all opens must be closed before data in use decreases M essages pertaining to data in use are logged to the various PSQL logging repositories See M essage Logging Thedatabase engine uses various files for its own internal processes such as PSQL system files metadata files dbnames cfg and default databases Files used for internal processes do not increase the value for data in use Estimating Data In Use You need to determine what size license you need for your business To make that determination you need information about your data usage PSQL provides Capacity Usage Viewer to provide that information Capacity Usage Viewer is a utility that records peak daily data use It renders those figures into a graph that displays your use over time Capacity Usage Viewer enables you to select time periods ranging from All to Last week You can also zoom in to view the data for a customized time period To estimate your c
256. s reserved for situations where the key is incompatible with the current engine Examples would include previous versions of PSQL such as v10 and prior on v11 or a Workgroup key on a Server engine or 32 bit key on a 64 bit engine Expired key is no longer available for use Temporary keys move to the Expired state once their evaluation period has passed Disabled key isno longer available for use If a key is disabled because of a failed validation it can be restored to active by fixing the problem and then performing a validation on the key m Failed Validation key is usable but only for a set number of days See Failed Validation and Failed Validation Period 102 License Administration Concepts Failed Validation and Failed Validation Period If a key fails validation you havea set number of days to remedy any condition that caused it to fail This period of time referred to as the failed validation period is reported in the Expiration Date column of License Administrator as shown in GU Visual Reference If the condition for the failed validation is not corrected by the date the key changes state to disabled After you implement changes to correct the failed validation perform a Validation action See To Perform a Validation The validation among other actions verifies the current machine signature and key combination If the conditions were corrected the state of the key changes to active More than one conditi
257. s to the remote server DSN To create a client DSN follow theinstructionsin Setting Up Client Access from a Windows Client You must first make sure that a server DSN exists on the server you want to access You can now rule out the client DSN as the source of the problem Thefinal task to perform is to ensure that your client and server are communicating on the appropriate network protocols By default PSQL ships with all network protocols enabled so connection time may be slow as it tries all protocols but it should eventually connect Some application vendors disable the protocols that are not typically used by their application s First determine what protocols ought to be used on your network If you havea Linux network or a 100 Microsoft network then your preferred protocol is TCP IP Once you know what the protocol should be you should ensure that your server is using this protocol You must have administrative rights on the server operating system or be a member of Pervasive Admin to perform this task In PCC right click on the server name then click Properties Click Communication Protocols Ensure that the correct protocol is listed in the Supported Protocols list and that TCP IP is selected General Troubleshooting Ensurethat your client isusingthesame protocol Using PCC right click on Local Client then click Properties Click Communication Protocols and ensure that the correct protocol is selected in the Supported Protoc
258. s your local client computer then click Properties Click Access and ensure that Use Remote M icroKernel Engine is selected You can now rule out the requester as the source of the problem 4 Nett verify that PSQL is installed and running on the target server On Windows open Services under Administrative Tools Verify that PSQL Transactional Engine and PSQL Relational Engine have been started If not start these services On Linux typethe following command at the Linux prompt on the server where the database engine is installed ps e egrep mkded 223 Basic Troubleshooting 224 If the output from the command returns at least one line containing the text mkde then PSQL is running If you do not see this line then you need to be logged into the root account and start the database engine by entering etc init d psql start You can now be certain that the server engine is installed and running The next step is to ensure that the server engine is accepting remote communication requests In PCC ensure that the remote database engine is configured to accept remote requests If you are having difficulty accessing a Windows 32 bit server engine remotely then you must check the setting at the server itself You must have administrative permission on the server or membership in the Pervasive Admin group in order to do so In PCC right click on the server then click Properties Click Access and ensure that Accept Re
259. screen gt To stop the Workgroup Engine as an application on Windows These steps assume that the Workgroup Engine was installed as an application See Installing the PSQL Workgroup for Windows in Getting Started With PSQL 1 Click Stop Workgroup Engine from the operating system Start menu or Apps screen Note You will receive a warning message when trying to stop the engine if any of the following is true There are active clients No activity took place since the engine loaded 10 seconds has not elapsed since the last operation took place gt To start the Workgroup Engine as a service on Windows These steps assume that the Workgroup Engine was installed as a service which is the default for a fresh install See Installing the PSQL Workgroup for Windows in Getting Started With PSQL 1 Refer to the various methods described in the section Starting and Stopping the Server Engine on a Windows Server 2 Substitute PSQL Workgroup Engine as the service name if you use Windows Services control panel Substitute psqlWGE as the service name if you use net start or psc gt To stop the Workgroup Engine as a service on Windows These steps assume that the Workgroup Engine was installed as a service which is the default for a fresh install See Installing the PSQL Workgroup for Windows in Getting Started With PSQL 14 Refer to the various methods described in the section Starting and Stopping the Server Engine on a
260. se is set to Bound or if Referential Integrity is being enforced the data files are bound to that database name See also Bound Database versus Integrity Enforced To ensurethat such tables can be opened for the new database you need to repair the database namefor the new database The tables are then associated with the new database 1 In PCC PSQL Explorer expand the Databases node and right click on the database name that you want to repair 2 Click Repair Database Name Thefollowing table explains additional actions if any that are required based on security settings for the database the tables or both See also Security Tasks Security Setting Action Required None None Repair Database Name executes without further interaction required Database Security Btrieve Security Classic You must provide the user name Master and the password for Master See also Database Security in Advanced Operations Guide If the database engine is running on a remote server and your operating system user name and password differ between the local machine and the remote server you must provide the user name and password for the remote server See also Classic in Advanced Operations Guide Btrieve Security Mixed 70 The data directories for the tables must be added to the DEFAULTDB database See The Default Database and the Current Database in Advanced Operations Guide In addition the other a
261. sks These objects appear as nodes in the PSQL Explorer Object Description Related Information Triggers A type of stored procedure that are automatically executed CREATE TRIGGER in SQL Engine Reference when data in a table is modified with an INSERT UPDATE or DELETE Common SQL Object Tasks Stored A collection of one or more SQL statements that can take CREATE PROCEDURE in SQL Engine procedures and return user supplied parameters Reference Common SQL Object Tasks User defined A scalar routine that returns a value CREATE FUNCTION in SQL Engine Reference functions Common SQL Object Tasks Views A database object that stores a query and behaves like a CREATE VIEW in SQL Engine Reference table Common SQL Object Tasks 80 Groups U sers and Security Groups Users and Security Security is a database property that requires a user to provide a user name and password to access the database By default database security is turned off Database security can beturned on through PCC or by executing an SQL statement Once enabled you may create groups and users and assign permissions to them Permissions can include database rights table rights and column rights within tables When you turn security on or off the M aster user must have only one connection open and must bethe only user connected Assoon as you turn security on for the first time only the M aster user can access the database Th
262. spectively because the files that PCC deals with are SQL documents For example File gt N ew creates aSQL document Thedialogs allow you to work with files containing character encodings different from the system encoding more easily than with the standard dialogs for open and save For example if you create a SQL script that contains wide character data saving the file without the proper encoding could result in lost data the system code page in use may not support wide character data for example Note that you can access the standard operating system dialog for file selection by choosing Browse on Open SQL Document or Save SQL Document This section covers the following topics Encoding Choices Default Encoding a Invalid File Name Characters a FileOpen Dialog Open SQL Document a File Save Dialog Save SQL Document m Using the Standard Operating System Dialogs Encoding Choices The dialogs allow you to change the file encoding during the open or save action In addition the File Open dialog showsa preview of the data based on theselected fileencoding Thefollowing table explains the encoding choices Table12 Encoding Choices for File Open and File Save Dialogs Encoding Discussion system code page The default is the system code page currently in use For example on Windows platforms in English you commonly see windows 1252 Big5 Big5 is a Chinese character encoding method used for traditional Chinese
263. spects of Mixed security are enforced such as user names defined in the database corresponding exactly with the user names defined in the operating system See Mixed in Advanced Operations Guide Security Setting Creating M odifying Deleting and Repairing PSQL Databases Action Required Btrieve Security Database If Database Security is turned on Master password specified no further requirements are needed for Database Btrieve Security The privileges for Master are sufficient See also Database in Advanced Operations Guide Owner Name for a table If a table has an owner name Repair Database Name prompts you for the owner name An exception to this is if the option Permit read only access without an owner name is specified for the owner name See also Owner Names in Advanced Operations Guide You can provide the owner name for an individual table or a list of all owner names associated with the set of tables separate each owner name with a comma and a space By default owner names are displayed as asterisks If you want to view the owner name s as plain text select Show owner names You can omit providing an owner name for a specific table with the Skip button To skip all tables with an owner name select Do not ask again for owner names Tables requiring an owner name that is unknown will be skipped then click Skip Table Billing requires an owner name Please provide one or more owner
264. ssword User names and passwords cannot be set up until security is turned on so the database will be inaccessible to each user for the period of time until you have set up a user account for that user Nn oO 0 fF WO N 82 In PSQL Explorer expand the Engines node then the Databases node Right click on the desired database then click Properties Click Security in the Properties tree Click the Security tab Click Enable Security to check mark the option Type the password you want for Master Password then re type it for Confirm Password Click OK Groups U sers and Security Database security is now on and you are logged in as the M aster user For instructions on creating database user accounts see User and Group Tasks gt To turn on security using SQL You must be logged into the computer as an administrator or as a member of the Pervasive Admin Operating system security group Turning on security prevents all users from accessing the database unless they log in to it using a valid database user name and password User names and passwords cannot be set up until security is turned on so the database will be inaccessible to each user for the period of time until you have set up a user account for that user 4 Turn security on for the database as explained in General Tasks 2 Inthe Filemenu of PCC click New gt SQL Document or click si in the toolbar The Select D atabase dialog box appears Click the database in th
265. sword for the user identified by username server servername Name of the server on which the database is defined If you do not specify this option the local machine is assumed You may also specify the IP address of the server port number TCP port number on which the database engine running on servername is listening If you do not specify this option the default port 1583 is assumed Port 1583 is the default port used for the Relational Engine stoponfail Stop when the first SQL error is encountered in commanafile 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 log logfile Write output to a file instead of to standard output stdout Logfile specifies the name of the file to which output is logged and optionally a path to the file If path is omitted ogfile is created in the same directory in which dvddl resides See Also SQL Engine Reference for more information about supported SQL syntax 216 Command Line Interface Utility Reference pvnetpass Description pvnetpassisthePSQL network password utility It isa command line utility used to managethe user IDs and passwords for servers to which your client connects When trying to connect to a server the client looks up the server name in the registry and uses the user name and password set for that server If your application uses the M
266. t Description Related Information Remaining Displays the remaining number of authorizations for all product keys and the To Display Remaining Authorizatio remaining number of repairs that a user can perform on a key in a state of Failed Authorizations ns Authorization or Disabled Return to GUI Dialog This information also displays automatically at the time of authorization and image deauthorization if applicable Help Displays the online help for License Administrator To Display Help Return to GUI Dialog image Close Closes the License Administrator Utility Return to GUI Dialog image 107 License Administration License Administrator Command Line Interface The command line interface CLI runs on all platforms supported by PSQL The CLI is functionally equivalent to the GUI You can administer licenses for local or remote database engines with the CLI TheGUI and the CLI list information about the authorized licenses such as the type of license user count session count data in use limit platform expiration date and so forth License key information is also available from the installation media CLI Syntax TheCLI uses the same syntax on all platforms utility_name option parameters Theutility name is one of the following Clilcadm exe Windows 32 bit a w04clilcadm exe Windows 64 bit a clilcadm Linux 32 bit a clilcadm64 Linux 64 bit The following table describes
267. t the first character position 4 Identifies the computer and the database to which the SQL document applies The computer name is listed first followed by the database name In the image above the computer is tment and the database is DEMODATA Note If None is specified as the database context then the text No Database Selected appears instead of the name of the computer and database The commands to execute SQL statements are disabled if None is specified See To set database context for an SQL query gt To create an SQL query or script By default when you start SQL Editor you may type in SQL statements A script is one or more SQL statements saved as a text file 4 Perform the steps for To start SQL Editor for a new SQL query 2 TypetheSQL statements into SQL Editor Separate SQL statements with a delimiter You can use the pound sign or thesemicolon 3 Optionally click File gt Save As to save the SQL statements as a text file gt To open an SQL script A script isoneor more SQL statements saved as a text file You can execute the statements in SQL Editor after you open ascript in the editor 1 Click File gt Open 160 SQL Editor Tasks 2 Navigate to the location of the text file select the file then click O pen By default the Open dialog looks for files in the PVSW bin directory with a file name extension of SQ L 3 Executethefile See To run all SQL statements in SQL Ed
268. t to create with the older version format Click OK to create the file Close the File Information Editor window but do not exit Btrieve Maintenance Utility From the menu select Data then Copy Enter thename of the source data file and the name of the target data file your newly created file with the older version file format Click Executeto copy the records into the older version file After the copying has finished if you need the new data file to have the same nameas it did previously save your original datafile with a different name then save your new file using the original file name ODBC and DDFs Frequently asked questions about ODBC and dictionary files 234 Frequently Asked Questions How can l tell if I can use ODBC to access my data files There are several ways to find out First look for DDF files where the data files are located If you see them then most likely you can access the database using ODBC Becauseit is possible to have DDF files located in a different directory you should also use PCC to determine whether a database has been created for the data files you want to access Finally you can ask your application vendor whether their application uses ODBC to access the data files How can a hard coded filepath in a DDF be changed In PCC right click on the database to which the table belongs then click Properties Cl ick Directories Change the value for Dictionary Location It may appear that the path h
269. t with an alphabetic character use single quotes pvdbpass demodata Joe oldpassword 123 To change a password on a remote server pvdbpass demodata Joe oldpass newpass server financel 215 Command Line Interface Utilities pvddl Description pvddl is used to execute a series of SQL statements in a command file Synopsis pvddl database commandfile separator character username username password password server servername port number stoponfail log logfile Options database Database against which the SQL statements in commandfile are to be executed this can be a database name or a server DSN commandfile Text file that contains the SQL statements Certain categories of SQL statements such as data definition language are better suited for use in commandfile Contrast this with a data manipulation statement such as SELECT A SELECT statement can be used but the result set is not returned to standard output You need a separator character between each command in your command file See separator character separator character Character used in commandfile to separate SQL statements The valid choices are any printable character However ensure that character does not occur within any of the SQL statements Common character choices include the pound sign semicolon and at sign username username Name of a user defined for a database with security enabled password password Pas
270. tabase ODBC Standard PSQL adheres to the Microsoft standard for ODBC database connections According to the standard applications using ODBC must connect to databases through Data Source N ames DSN s defined in the Operating system Note PSQL does not support File DSNs You must use User or System DSN System DSN s are generally preferred because they are available to all users on a given computer Every PSQL database that you expect to access using an ODBC application must havea DSN that identifies the database A DSN that points to the PSQL database engine is called an Engine DSN Engine DSN sare 32 bit only and are deprecated A 32 bit DSN that points to an Engine DSN or to a named databaseiscalled aClient DSN A DSN on a64 bit operating system is simply referred to asa 64 bit DSN without designation of Engine or Client N ew or revised 32 bit applications local or remote should connect to anamed database instead of using Engine DSNs Alternately applications could use DSN less connections by specifying the PSQL ODBC driver name see PSQL ODBC Driver Names in ODBC Guide Avoiding the use of Engine DSNs positions your application for the future when Engine DSN s will no longer be supported in PSQL Windows 64 bit operating systems contain two different executable files for ODBC Administrator one for 32 bit DSN s and one for 64 bit DSNs Each ODBC Administrator lists the system DSN s that only match its bitness For the 64 bit P
271. tabase user names passwords and permissions are retained but not used if security isturn off If security is re enabled the previous user names passwords and permissions take effect again An exception is the M aster user The M aster password is not retained nor re applied 4 Turn security on for the database as explained in General Tasks 2 InthePCC File menu click New gt SQL Document or click in the toolbar The Select D atabase dialog appears Click the database in the list for which you want to create a group or user Click OK In SQL Editor issue the SQL statenent SET SECURITY NULL Click SQL gt Execute in Text or click in the toolbar See also SET SECURITY in SQL Engine Reference ao a A WwW Btrieve Security Policy Tasks gt To set or change the security policy for a database 3 Caution Changing security policy for a database may prevent current users from accessing the database if security is turned on and the given users do not have equivalent user accounts and rights under the new security policy Turn security on for the database as explained in General Tasks In PSQL Explorer expand the Engines node then the Databases node Right click the desired database then click Properties Click Security in the Properties tree Click the Btrieve Security tab Click the desired policy Classic Mixed or Database Click OK See also the chapter PSQL Security in Advanced Operations Guide Nn
272. tee Sas He eel ce ae ee eee Barn aa ae eed th ee Ee aA Hse chal ee at 127 Indexes Tasks oa A aed A A ea eae BE ew A 128 Foragn Keys Tasks o ia a E ADe aa eke bere Se be ee A Wea he 128 SOL VI Taks 44 44 Ques Shas da BAA a wee BAPE ee R BA RA a 128 6 CSQL EqiHOM cutis a Soar a ee aa ds did ene ya ar de aw apa die An 149 A Tour of theSQL Editor SQL Editor Concepts osia e a i dae Rhd Phe A Sheedy ek 150 VOR VICI Ets La tine oN Farce A E NR gn acca ee ye 150 ROSEFIGELONS 2 mesna eaei he eal asa tas Sate eee ae aN at cee ar ae mee ae ian ae 151 Displaying Statement Results es 151 OutlineWindowView aea a a aa a a E T a ai 153 Working with Common SQL Objects o o es 155 SQL Editor U sed in SQL View Tab of Table Editor oona o ooo oo o 156 SQIEGItOrTaskSy irradia a da dd a ed i 157 General Tasks zina a ide A a Ped Se ie Be 157 EXGCULION MASKS A A TE 157 Grid ASKS has amara a aa a a ia ie a dara 158 TOXEMICW TASKS tito e ti esis ah Paik ke te Ae ee 158 Outline View Tasks iora e ii a a ed eR ee a ee a 158 vi Common SQL Object Tasks ooa Fa aa Se NA A Re eRe De 158 7 PSQL System Analyzer PSA 2 6 ee 169 Usage Information for the Diagnostic Utility PSA PSA CONCEDES sos eis ae Path eo aa ean BU ee ae ee 170 Frequently Asked QUESTIONS 6 ees 171 PSA GUI Visual Reference vaca nd Maca an Wa eek a aA het Bal oli 173 PSA Task Ain We at Dee Bakke aire tint Aes etn a dl rs AE Mk es 174 General
273. telephone authorization Telephone Deauthorization Telephone deauthorization is not available If you authorized PSQL using telephone authorization you must call Actian Corporation to deauthorize Notification Viewer The Notification Viewer is a utility for displaying messages logged by the licensing components The purpose of the utility isto inform you of noteworthy licensing messages in a noticeable but unobtrusive manner Although not part of License Administrator Notification Viewer is mentioned here because of its allied purpose pertaining to licensing gt To View Licensing Messages In Logging Repositories 1 Seethe following topic in Advanced Operations Guide for the repository in which you are interested Notification Viewer Operating System Event Log PSQL Event Log PVSW LOG 120 Table Editor A Tour of PCC Table Editor Thetopics in this chapter include Table Editor Concepts Table Editor Graphical User Interface TableEditor Tasks chapter 121 Table Editor Table Editor Concepts This section contains the following topics Overview Table Editor Pages Data Types a Null Values Overview Table Editor is one of the editor windows within PSQL Control Center PCC Table Editor is a special type of editor that contains multiple pages The pages are represented by tabs across the bottom of the editor The editor allows you to add delete or changethe characteristics of co
274. ter data in it the legacy solution was for the access method such as ODBC usinga DSN to specify OEM ANSI conversion Now it is possible to set the OEM code page for the database and have the access method specify automatic translation See also Automatic in ODBC Guide 63 UsingPSQL Control Center Directories Theproperty settings for Directories specify where certain types of files reside on physical storage a Dictionary Location a Data Directories Dictionary Location This location specifies where the dictionary files DDFs reside on physical storage This location must be on the same server to which you are connected and where the database engine is running The location must be formatted as though you are working directly at the server machine m For Windows operating systems enter a path in the form drive path where driveis a drive letter on the server a For Linux enter the standard Linux path format from root For example if you are at a workstation connected to a Windows server where the database engine is running and you want to create a new database on the CA drive of the server in the folder mydata enter the location as c mydata You would enter it this way even if you havea local network drive for example F mapped to the server s CA drive Data Directories The Data Directories list specifies where the data files resideon physical storage Add locations to thelist by clicking New Remove lets
275. tform Note You must be logged onto the Windows server as a user with full administrator level rights on the server or be amember of the Pervasive Admin group defined on the server In the Windows Control Panel double click Users and Passwords Click the Advanced tab In the Advanced User Management area click Advanced Click the Groups folder From the menu click Action gt New Group kh O N Typein Pervasive Admin asthe group name To add users to this group click Add select user name click Add then OK al Click Createto createthe group 6 Click Close Note If the Logon as setting for the PSQL servicesis not System Account see Services Settings and Log In Authority Services Settings and Log In Authority Certain operating system settings for the PSQL services must bein effect for you to login to the machine running the database engine These settings apply whether or not you use a Pervasive Admin user group 21 Using PSQL The settings apply to the PSQL Server Engine and to the Workgroup Engine if you are running the Workgroup Engine as a service If you are running Workgroup Engine as an application and want to change it to run as a service see Running the Workgroup Engine asa Service in Getting Started With PSQL Default Setting By default installation both the Transactional and Relational services set Log on to Local System Account Log on as Local System account v Allow service to
276. the options and parameters Table 13 License Administrator Command Line Options and Parameters Options and Parameters Function lt interpret i gt key Lists the license information for all keys or for the specified key e Product The name of the PSQL product such as Server Vx Server or Workgroup e Product Key The Product Key associated with the corresponding product license State The status of the Product Key Values are Active Inactive Expired Disabled and Failed Validation A license may be disabled if certain hardware configuration items have changed since the key was applied Platform The combination of PSQL product operating system and bit architecture to which the license applies e License Type The type of license such as permanent temporary or an increase license for user count session count or data in use User Count The user count for the specific license See also Concurrent User License Model e Session Count The session count limit for the specific license Data In Use GB The data in use limit in gigabytes for the specific license See also Capacity based License Model e Expiration Date The date on which a license expires if applicable or n a if not applicable Vendor A unique number identifying the vendor software that installed the license e Application A unique number identifying the application to which the license applies lt
277. ting with Pervasive SQL 2000 fields that allow null values have an additional byte defined at the start of the field This byte is the null indicator byte You can work around this in one of two ways If you are using SQL statements to create a new table definition enter the statement SET TRUENULLCREAT E OFF For theremainder of your current session any tables that you create will use the old record structure without the extra byte for each nullable column If you do not wish to use SQL statements you can get the field sizes to align properly by creating all columns as not nullable I want to convert my data file version from 9 back to file format version 8 7 or 6 How do I do this Ifthe files you wish to convert are serviced by a remote Server or Workgroup engine you must have Administrator permissions on the remote system in order to perform these tasks You must also have a network drive mapped to the remote data files In PCC right click the server name where the data files are located then click Properties Click Compatibility and set Create File Version to the file version to which you want to convert Click OK Click Yes to restart the engines These changes result in new files created to bein the version selected Run the Maintenance utility then click Options then File Information Editor Click Load Information and choose the data file that you want to convert Click Create and specify the name of the new empty data file you wan
278. tion Access License Administrator from the operating system Start menu or Apps screen This step assumes that License Administrator was installed as part of aPSQL default installation A vendor application that uses an embedded database engine may require that you start License Administrator with different menu commands The GUI executable is named guilcadm exe Theutility connects to the local server on which the database engine is running If the server is not the one you want select a different server as described in To Select a Server for License Administration gt To Select a Server for License Administration The database engine must be running on the computer you specify so that License Administrator can connect to the engine 1 In the Server Name field type the name of the server for which you want to administer database licenses Asan alternative to typing a server name you may click Browse to display a list of network servers from which to select Click on the server for which you want to administer database licenses then click OK Click Connect or press Enter License Administrator accesses the specified server to determine the type of license required Ifyou arenot logged in to the server a dialog appears to log in to the operating system not the database engine 111 License Administration On thelogin dialog typethe name of an operating system user for User Name The user must have administrative pr
279. tion is tied to your machine s hardware configuration After you have authorized a product key on amachine changes to certain hardware configuration items could disable the key If you need to change hardware configuration deauthorize the key first Deauthorizing the key disassociates the product key from the unique hardware configuration After you completethe hardware configuration changes you can again authorizethe product key To Display Remaining Authorizations Ensure that the server name in the title bar is the correct server for this operation If not select a different server as described in To Select a Server for License Administration Click Remaining Authorizations Thenumber of remaining authorizations displays for all product keys 112 License Administration T asks To Hide Expired Licenses Ensurethat theserver namein thetitle bar is the server for which you want to hide expired licenses If not select a different server as described in To Select a Server for License Administration Click Hide Expired if it is not already selected The list of licenses changes to exclude all expired licenses Note The Hide Expired checkbox is selected by default and can be selected or cleared as needed gt To Deauthorize a Key 1 3 Note Temporary licenses cannot be deauthorized They expire at the end of their evaluation period Ensure that the server name in the title bar is the server from which you want t
280. tive user tim with password tim56 you would enter the following command btadmin p tim56 a tim Note Users created with btadmin are not related to Linux system users These users are known only to the database engine Logging in as Administrator on any platform gt To connect to a remote PSQL v12 server 1 UsetheMonitor utility to connect to a remote server Refer to the Advanced O perations Guide for a discussion of this utility 24 Granting Administrative Rights for the Database Engine 2 Enter your operating system user name and password and click OK Figure3 Connect to Remote Server Dialog Box Cancel The password is encrypted before being sent over the network using a unique and pre defined encryption key The PSQL v12 engine unpacks and decrypts the user name and password and verifies access It then returns a status code to the client indicating the success or failure of the verification 25 Using PSQL Setting Up ODBC Database Access This section reviews some conceptual information on setting up ODBC access to your database Topics covered include the following basic concepts ODBC Standard a Servers and Clients a DataSource Names a Internal Database Name Applications Using the MicroKernel Engine Note ThePSQL Java utilities do not require DSNs The PSQL Control Center for example uses JDBC not ODBC DSNsarerequired only if your application uses ODBC to access the da
281. to be met The key to be repaired must be compatible with the version of the database engine currently installed The key to be repaired must be in a disabled or a failed validation state A permanent key cannot be in the active state There must be internet access to communicate with the PSQL license server The allowable number of repairs must not have been exceeded For a successful repair the message key has been repaired is returned no option or wrong option Displays information about the options and parameters See CLI Tasks for the tasks pertaining to the CLI 109 License Administration License Administration Tasks This section explains the tasks that you can perform for license administration The majority of tasks involve License Administrator Some tasks such astelephone authorization are accomplished by other means License Administrator GUI Tasks To Start License Administrator from PSQL Control Center PCC To Start License Administrator as a Stand alone Application To Select a Server for License Administration To Authorize a Key To Display Remaining Authorizations To Hide Expired Licenses To Deauthorizea Key TO Repair a Key To Display License Information To Determinea Total User Count TO Refresh the License Information List To Display Help CLI Tasks To Display Information About a Key To Perform a Validation
282. tuations Requiring That You Clear PCC Cache PCC caches certain information to improve efficiency The cache must be cleared after you install or upgrade any other products that interact with PCC Otherwise the installed or upgraded product does not appear in PSQL Explorer For example if you were to install or upgrade DataExchange you would then need to clear the PCC cache The cache can be cleared only by starting PCC with a parameter from the command line gt To clear PCC cache 14 Exit PCC if itis running select File gt Exit 2 Open acommand prompt 3 Changedirectory to the PSQL bin folder in the PSQL installation directory For default locations of PSQL files see Where are the PSQL files installed in Getting Started With PSOL 46 An Overview of PSQL Control Center 4 Enterpcc clean PCC starts and clears its cache The newly installed or upgraded products should then appear in the PSQL Explorer Note Useof the clean parameter when starting PCC provides no advantage under normal usage It is required only if you install or upgrade a plug in product Editors and Views Within PCC ThePCC main window offers various editors and views m PSQL Explorer a SQL Editor Grid a Text Outline Table Editor You can display and work with objects through the different editors and views Multiple editors of the same type such as SQL Editor can be open at the same time Each object being edited is represent
283. turned in the order in which they wereinserted Normal ASCII sorting sequences upper case letters before lower case such that the sorting would return as ABC DEF Def abc Click File gt Save or El before changing pages within Table Editor gt To set a column default The default value is used if you perform an SQL INSERT for a row but do not provide a value for the column 1 Ensure that the Columns page of Table Editor is active If required perform the steps for To work with columns Click in the D efault cell for the desired column You can set a default only for applicable data types If a default is not applicable the grid cell is shaded and you will be unable to edit the Default Delete the existing value if present Type the default value you want 135 Table Editor Thecolumn default can bea scalar function for certain data types Data Type Scalar Function Date now curdate See also NOW and CURDATE both in SQL Engine Reference Time now curtime See also NOW and CURTIME in SQL Engine Reference Timestamp now See also NOW in SQL Engine Reference 1 The names are case insensitive NOW and now are equivalent The parentheses are required That is NOW is invalid but NOW is valid 5 Click File gt Save or El before changing pages within Table Editor gt To set or remove a column as a primary key Note that you cannot
284. u are working with Linux or the manual states otherwise you can enter these items using uppercase lowercase or both For example you can type MY PROG myprog or MYprog Words appearing in bold include the following menu names dialog box names commands options buttons statements and so forth Monospaced font is reserved for words you enter such as command syntax Square brackets enclose optional information as in log_name If information is not enclosed in square brackets it is required A vertical bar indicates a choice of information to enter as in file name file name Angle brackets enclose multiple choices for a required item as in D lt 5 6 7 gt Words appearing in italics are variables that you must replace with appropriate values as in file name An ellipsis following information indicates you can repeat the information more than one time as in parameter The symbol means one item is defined in terms of another For example a b means the item a is defined in terms of b xiii Xiv chapter Introducing PSQL E Understanding PSQL and its Capabilities These topics cover what PSQL is and what it can do for you Understanding the PSQL Database M anagement System a Key Concepts Understanding the DBM S Products Using PSQL Documentation a File System Security Introducing PSQL Understanding the PSQL Database Management System PSQL is a comprehensi
285. u perform on the page GUI Object Figure21 Columns Page Column Name Type Size Precision Scale Null Case Collate Default Columns Indexes Foreign Keys SOL View Description Related Information Column Name Specifies the alpha numeric name for the column To specify a column name Relational Engine Limits in SQL Engine Reference SQL Grammar Support in ODBC Guide Identifier Restrictions by Identifier Type in Advanced Operations Guide Columns Page image Type Specifies the data type of the column PSQL Supported Data Types in SQL Engine Reference To set a column data type Columns Page image Size Specifies how many bytes are permitted for the data type A shaded cell indicates that size does not apply To set a column size Columns Page image Precision Specifies the number of significant digits for floating point values A shaded cell indicates that precision does not apply To set column precision Columns Page image 125 Table Editor GUI Object Description Related Information Scale Specifies the number of significant digits that are to the right of To set a column scale the decimal point for floating point values A shaded cell indicates that scale does not apply Columns Page image Null Specifies whether NULL values are allowed for the data type A To set a column to allow or disallow nulls shaded square indicat
286. upport for Import Data Export Data and Export Schema 93 import Data Dialogi uri raid EER td PR aE td 93 Export Data Dialog ea ica id as A A E ard Ee 93 Export Schema Dialog 0 o o 94 4 License Administration lt 97 License M odels and W orking with Keys License Administration Concepts aoaaa ee 98 License Mod aca e ar i ara Raines Ben A AE ESETE E dae aa E a Gel wees 98 License Enforcement da a ai enie a a a i ai Da D 100 Message LOQgINGs i ty sonei bee a dd bb fet ha hE ah 104 License Administrator Graphical User Interface 2 ee 105 GUI Visual Reference 2 a 105 License Administrator Command Line Interface aao ee 4 108 ESV aa dee Er a o at ad A es Ee Rahal a a da 108 License Administration TakS ou ao a Whe aan LA aa 110 License Administrator oaoa aa o 110 Notification Viewer aaa 111 5 Table Editors 3 4 6 44 em r et a Ad Be oe a I a e a 121 A Tour of PCC TableEditor Table Editor Concepts ooo a ee a ee a A ee a a 122 TE 122 Table Editor PES version a a eee ed EA aw A aw ea 122 Table Editor Graphical User Interface 0 ooo oe 125 COlUMAS Pagen vor a a A e es 125 Indexes Page tata ee aaa Se 126 Foreign KEYS PIDO or A a Pe bo 126 SOL VIEW Pages ica dai dai a a 126 Tal Ed RTS a A AS IA Mh ate hte dy ete US Bees 127 AS A or ste Sail 6 se nee God Bee Boe se hina E T E Aone E G4 ge mae S 127 CONUS ASKS sees
287. users and passwords to the btpasswd file ThePervasive Admin option is offered so that you can grant users administrative rights to the database engine without granting them administrative rights to the operating system where the database engine resides Rights Within an Active Directory Environment You may useone or more Pervasive Admin groups within an Active Directory environment See Active Directory Service in Getting Started With PSQL 20 Granting Administrative Rights for the Database Engine Rights Provided to non Administrative Users Runtime only access enables a user without administrator level rights to perform such functions as a extract alist of DSNs extract a count of DSNs extract information on a DSN a extract information on the location of the DBnames configuration file dbnames cfg connect to databases retrieve update insert and delete data as permitted by database security Tasks for Granting Administrative Rights To grant a user administrative rights follow the instructions for your platform a Granting Administrative Rights on a Windows Server a Granting Administrator Rights on Linux a Loggingin as Administrator on any platform Granting Administrative Rights on a Windows Server Users who are members of Pervasive Admin or of Administrators are permitted to perform administrative tasks on the database engine gt To grant a user database administrator rights on a Windows 32 bit Server Pla
288. users are assigned to it 1 2 3 4 5 86 Expand the nodes for the database Expand the Groups node or Users node Right click the desired group or user name Click Delete Click Yes To work with groups and users using SQL Turn security on for the database as explained in General Tasks In the File menu of PCC click New gt SQL Document or click si in the toolbar The Select D atabase dialog box appears Click the database in the list for which you want to create a group or user Click OK In SQL Editor create the desired statement for the group or user Refer to the following statements in SQL Engine Reference CREATE GROUP ALTER GROUP DROP GROUP CREATE USER ALTER USER e DROP USER GRANT REVOKE SET PASSWORD To execute the statement click SQL gt Executein Text or click ES in thetoolbar Groups U srs and Security Assigning Permissions Tasks gt To assign permissions for a group using PSQL Explorer 1 2 3 4 6 Note Permissions on the Database tab override permissions on the Table tab Expand the nodes for the desired database Right click the group name under the Groups node then click Properties Click Permissions in the Properties tree Click the tab to access permissions for the desired object database tables and columns stored procedures or views See also Permissions on Views and Stored Proceduresin SQL EngineReference On the tab click the option
289. valid product key See also Telephone Authorization 2 Atacommand prompt on the machine where the engine is installed type guipaadm 119 License Administration gt To Authorize a Key Using the Command line Phone Authorization Utility 1 Ensure that you havea valid product key See To Display Information About a Key If you do not have a product key you may obtain onefrom Actian Corporation 2 Atacommand prompt on the machine where the engine is installed type the utility name appropriate for your platform Clipaadm exe Windows 32 bit w64clipaadm exe Windows 64 bit Clipaadm Linux 32 bit Clipaadm64 Linux 64 bit 3 Atthe prompt enter the product key you want to authorize ThePSQL Phone Authorization Utility returns an authorization code you will use to complete teleohone authorization 4 Perform one of the following actions to complete telephone authorization Ifyou obtained your product key from Actian Corporation call 1 800 287 4383 U S toll free or 00800 1212 3434 Europe toll free to complete telephone authorization with Actian Corporation personnel The Actian Corporation staff will provide you with an authorization key data string which you will enter as the final step of authorization Ifyou did not obtain your product key from Actian Corporation contact the vendor or original equipment manufacturer OEM from whom you obtained the key The vendor or OEM personne will assist you to complete
290. ve database management system built around the M icroKernel Database Engine PSQL offers easy installation uncomplicated maintenance and high levels of performance and reliability This section explains the product and its components What is a Database Loosely defined a database is simply a collection of data Generally the data is structured by dividing it into sub sets of information that share the same characteristics Some examples of a database are m A telephone book Each entry in the phone book consists of four characteristics first name last name address and phone number m A collection of digital photographs Each picture on your hard disk has two characteristics a file name and the data within the file that represents the image m A list of orchards and the fruit grown by each Each entry in the orchard list might consist of three characteristics orchard name address and date founded The related list of fruits might have five characteristics orchard name fruit name fruit type McIntosh Fuji and so on fruit price and a taste rating In the particular context of this product a database is a specific well defined collection of related information You can probably find oneor more databases availableon your computer or your network For example you may have a database of information related to vendors from whom you purchase supplies or raw materials and you probably also have a database containing custom
291. ver or the 32 bit ODBC driver can be used to connect to either PSQL Server 64 bit or PSQL Server 32 bit For the PSQL Client however the application bitness does haveto match the bitness of the PSQL Client on the client machine That is if you want to use a 64 bit application on the client the PSQL 64 bit Client must be installed 203 Command Line Interface U tilities PSQL allows DSNs to specify three different ODBC driver descriptions as explained in the following table Table18 PSQL ODBC Driver Descriptions for Linux ODBC Driver Description in INI Files Driver Library Installed With Behavior for All Products Installed With Pervasive ODBC Engine Interface PSQL Server 64 bit PSQL Server 32 bit Installation assigns an ODBC driver in odbc ini for compatibility with pre existing Engine DSNs e Connects to a local named database No longer created by default with dsnadd the engdsn option must be specified e Visible when viewing Engine DSNs in ODBC Administrator running on a Windows client For use by 32 bit applications that are already coded to use Engine DSNs 32 bit Engine DSNs are deprecated on Linux Pervasive ODBC Client Interface PSQL Server 64 bit PSQL Server 32 bit PSQL Client 32 bit Installation assigns an ODBC driver in odbc ini for compatibility with pre existing Client DSNs Connects to a local or remote named database or an Engine DSN For
292. will be asked to enter a password If a user already exists then his password is changed as specified By default a user s created without administration permissions You can usethe a option to give administration rights to the user You can remove this right by using a To removea user record from the password file enter btadmin r username Every time the btpasswa file is changed the previous version is backed up to btpasswa 193 Command Line Interface Utilities butil Description ThePSQL Maintenance Utility or butil isa command line utility that performs command file and data manipulations on a data file The maintenance utility performs the following file and data manipulations Starts and stops continuous operations for use in performing server backups Recovers changes made to a file between the time of the last backup and a system failure m Imports and exports ASCII unformatted and SDF sequential data Copies data between data files m Returns MicroKernel Engine version information Continuous operation is an MicroKernel Engine feature that enables you to back up files while they are in use by PSQL based applications Two maintenance commands startbu and endbu begin and end continuous operation on afile or set of files Synopsis butil clone outputFile sourceFile o lt owne gt pagecompresson pagecompressoff recordcompresson recordcompressoff UIDuname PWDpword DBdbnam
293. y Plan Viewer in SQL Engine Reference m Rebuild see Converting Data Files in Advanced Operations Guide Gateway Locator if PCC isinstalled with PSQL Workgroup see Gateway Configuration in Getting Started With PSQL 54 An Overview of PSQL Control Center Note T hese utilities appear in the Tools menu only on Windows platforms For both Windows and Linux platforms you can add your own custom tools to the Tools menu Refer to the next section External Tools You can add your own software programs to the PCC Tools menu This provides a convenient way to start the programs from PCC gt To add external tools 1 2 3 4 5 kh O N On thePCC Window menu select Preferences Expand the PSQL nodeif it isnot already expanded Click External Tools Click New Type a name for Tool Label that you want to appear in the Tools menu Type the path and file name of the program for Tool Location You can click the ellipsis button E to browse to the file location Optionally type any parameters for Tool Parameters that should be passed to the program when the program starts Click OK Click OK or Apply then OK to close the Preferences dialog To set preferences for external tools On the PCC Window menu click Preferences Expand the PSQL nodeif it isnot already expanded Click External Tools Click the desired tool in the External Tools list Perform one of the following actions Toremovethetool from thelist
294. y database use the following at the command line dbmaint a d 1 b 1 e nDbname ldictpath ddatapath The list of commands for dbmaint include a add database name d delete database name list all database names Options include b create Bound database i create database with Relational Integrity enforced e do not create dictionary files for database NDBName specify database name Dictpath specify dictionary path dDatapath specify data path a show full data in the DBN ames list For example to create DBNameTEST with relational integrity type dbmaint a i nTEST Note Unless datapath is specified the new database is created in the default location PVSW_ROOT data Likewise if dictpath is not specified the dictionary is created in the default location To delete an existing database use the following at the command line dbmaint d nDbname For example to delete the newly created database TEST type 2 Setting up Database Access with PCC dbmaint d nTEST To list all existing databases dbmaint 1 a Set up DSN s using the dsnadd utility See dsnadd Setting Up Client Access from a Windows Client gt To enable client access to a remote PSQL database 1 2 3 4 Access Control Center from the operating system Start menu or Apps screen In the PSQL Explorer pane right click Engines then click New then Server ThePSQL Explorer paneisthecolumn
295. y use of product keys which you manage using the License Administrator utility The utility allows you to authorize and deauthorize keys and view license information It has both a graphical and command line interface You authorize a key for one of two purposes a Authorize a Product Increase User Count or Data In Use Authorize a Product Product authorization is a key validation process that associates certain machine information with the license for a product called the product key This association results in a unique installation identification that ensures the copy of software is legitimate and on the appropriate machine When you authorizea product key the unique installation ID issent to Actian Corporation to verify the authenticity of the key and ensureit is not being used for multiple installations This process authorizes the product for use When the product key is authorized or deauthorized your remaining authorizations are displayed Note that after you have authorized a product key on a machine certain changes to the machine can disable the key If you need to make changes to the machine deauthorize the key first Doing so disassociates the product key from its uniqueinstallation ID After you complete the hardware configuration changes you can again authorize the product key 100 License Administration Concepts Authorization Access Through A Proxy Server If you are using a proxy server you need to configure
296. you attempt to save the table After you deletethel DENTITY or SMALLIDENTITY column from thelist you may then savethetable 136 Ya 4 AS Table Editor Tasks Ensure that the Indexes page of Table Editor is active If required perform the steps for To work with indexes Click Add The New Index dialog displays Type the name of the new index and click OK Tip For a list of database object lengths and invalid characters see Identifier Restrictions by Identifier Type in Advanced Operations Guide The new index appears in the Indexes list and the Index Segment Details display N ote that the first column is populated into the Columns list Note New indexes are created by default as Normal Index Editor Indexes Index Segment Details index _01 Ounique Partial Normal v Allow Modifications Columns SortOrder Student_ID Ascending Amount_Paid In the Columns list select the Column to designate for the Index or Index Segment Caution ndexes must have at least one Column designated If you do not select a Column for the Index the first column remains selected Thedefault sort order is Ascending If you need a descending sort order select Descending from the Sort Order list for the Column you want changed Note Some data types such as LON GVARBINARY cannot be used for an index Columns with such data types are not valid choices Continue selecting columns fro
297. ystem and bit architecture to which the license applies e License Type The type of license such as permanent temporary or an increase license for user count session count or data in use User Count The user count for the specific license e Session Count The session count limit for the specific license Data In Use GB The data in use limit in gigabytes for the specific license Expiration Date The date on which a license expires if applicable or n a if not applicable Vendor A unique number identifying the vendor software that installed the license Application A unique number identifying the application to which the license applies Hide Hides all expired licenses in the list of licenses for the current session of License To Hide Expired Licenses Expired Administrator This checkbox is selected by default Return to GUI Dialog image Deauthorize Deauthorizes the selected key from the specified server If the selected keyisa To Deauthorize a Key permanent key also deauthorizes all increase keys user count session count data in use associated with the permanent key oe to GUI Dialog image This button is enabled when a Product name is selected in the License Information list Repair Repairs deauthorizes and authorizes the selected key on the specified server Repair To Repair a Key Return to GUI Dialog image License Administrator Graphical U ser Interface GUI Objec

User's Guide

Contents

Download Pdf Manuals

Related Search

Related Contents