Administration and Performance Guide Adaptive Server® IQ 12.4.
DOCUMENT ID: 38152-01-1242-01 LAST REVISED: April 2000 Copyright © 1989-2000 by Sybase, Inc. All rights reserved. This publication pertains to Sybase database management software and to any subsequent release until otherwise indicated in new editions or technical notes. Information in this document is subject to change without notice. The software described herein is furnished under a license agreement, and it may be used or copied only in accordance with the terms of that agreement.
Contents About This Book ........................................................................................................................ xvii CHAPTER 1 Overview of Adaptive Server IQ System Administration ............ 1 Introduction to Adaptive Server IQ ................................................... 1 System administration tasks............................................................. 2 Security overview .............................................................................
Contents Starting the server on Windows NT ............................................... Starting the server from the NT Start menu ............................ Typing the server startup command........................................ Running the server outside the current session ...................... Using command-line switches........................................................ Naming the server and databases ..........................................
Contents Steps in establishing a connection .......................................... Locating the interface library ................................................... Assembling a list of connection parameters............................ Locating a server..................................................................... Locating the database ............................................................. Server name caching for faster connections ...........................
Contents CHAPTER 4 vi Deleting views ....................................................................... Views in the system tables .................................................... Working with indexes ................................................................... Introduction to indexes .......................................................... Creating indexes ................................................................... Indexes in the system tables ..................................
Contents CHAPTER 5 Types of join hierarchies ....................................................... Modifying tables included in a join index ............................... Inserting or deleting from tables in a join index ..................... Table versioning controls access to join indexes .................. Estimating the size of a join index................................................ 164 167 168 169 169 Moving Data In and Out of Databases.......................................
Contents Handling conversion errors on data import ........................... Tuning bulk loading of data .......................................................... Improving load performance during database definition ....... Setting server startup options................................................ Adjusting your environment at load time ............................... Reducing Main IQ Store space use in incremental loads...... Changing data using UPDATE..............................................
Contents CHAPTER 7 Using cursors on SELECT statements in procedures ........... Errors and warnings in procedures .............................................. Default error handling in procedures ..................................... Error handling with ON EXCEPTION RESUME .................... Default handling of warnings in procedures .......................... Using exception handlers in procedures ............................... Nested compound statements and exception handlers ........
Contents Referential integrity is unenforced......................................... 284 Integrity rules in the system tables ............................................... 285 CHAPTER 8 Transactions and Versioning ..................................................... Overview of transactions and versioning ..................................... Introduction to transactions ................................................... Introduction to concurrency ...................................................
Contents CHAPTER 10 Understanding locales.................................................................. Introduction to locales ........................................................... Understanding the locale language....................................... Understanding the locale character set................................. Understanding the locale collation label................................ Setting the SQLLOCALE environment variable .................... Understanding collations............
Contents CHAPTER 11 xii Managing individual user IDs and permissions............................ Creating new users ............................................................... Changing a password............................................................ Granting DBA and resource authority ................................... Granting permissions on tables and views............................ Granting users the right to grant permissions ....................... Granting permissions on procedures ...
Contents CHAPTER 12 The RESTORE statement ..................................................... Restoring in the correct order ................................................ Renaming the transaction log after you restore .................... Validating the database after you restore.............................. Restore requires exclusive write access ............................... Displaying header information............................................... Recovery from errors during restore .........
Contents Working space for inserting, deleting, and synchronizing ..... Options for tuning resource use ................................................... Restricting concurrent queries............................................... Limiting a query’s memory use.............................................. Limiting queries by rows returned ......................................... Forcing cursors to be non-scrolling ....................................... Limiting the number of cursors ..................
Contents CHAPTER 14 Examples of monitor results .................................................. Avoiding buffer manager thrashing .............................................. Monitoring paging on Windows NT systems ......................... Monitoring paging on UNIX systems ..................................... System utilities to monitor CPU use............................................. 473 476 477 477 479 Adaptive Server IQ as a Data Server.........................................
xvi
About This Book This book, Adaptive Server IQ Administration and Performance Guide, presents administrative concepts and procedures and performance tuning recommendations for Sybase Adaptive Server IQ, a high-performance decision support server designed specifically for data warehouses and data marts. Audience This guide is for system and database administrators or for anyone who needs to set up or manage Adaptive Server IQ or understand performance issues.
Related documents Table 1: Guide to using this book To learn how to... Understand the role of an Adaptive Server IQ administrator Start and stop an IQ database server, and set up user connections Create an Adaptive Server IQ database Select Adaptive Server IQ indexes Load data into your database Create procedures and batches Add users and assign them privileges Specify constraints on the data in your tables Read this chapter...
About This Book • Adaptive Server IQ Reference Manual Read for a full description of the SQL language, utilities, stored procedures, data types, and system tables supported by Adaptive Server IQ. • Adaptive Server IQ Troubleshooting and Error Messages Guide Read to solve problems, perform system recovery and database repair, and understand error messages, which are referenced by by SQLCode, SQLState and message text.
Related documents xx
CH A PTE R About this chapter 1 Overview of Adaptive Server IQ System Administration This chapter provides a brief introduction to Adaptive Server IQ and an overview of IQ system administration. Introduction to Adaptive Server IQ Adaptive Server IQ is a high-performance decision support server designed specifically for data warehousing.
System administration tasks Rapid access to many data sources Adaptive Server IQ can integrate data from diverse sources—not just IQ databases, but other databases in the Adaptive Server family, as well as nonSybase databases and flat files. You can import this data into your IQ database, so that you can take advantage of IQ's rapid access capabilities. You can also query other databases directly, using Adaptive Server IQ's remote data access capabilities.
CHAPTER 1 Overview of Adaptive Server IQ System Administration Table 1-1: Administrative tasks If you want to know how to... Install and configure Adaptive Server IQ for your platform Start and stop the database server, and set up user connections Look in...
Tools for system administration Types of users Adaptive Server IQ recognizes three categories of users for each IQ database: • The database administrator, or DBA, has complete authority to perform all operations on that database. This guide is addressed primarily to the DBA, who typically carries out most administrative tasks. • The user who creates a particular database object is its owner, and can perform any operation on that object. • All other users are considered public users.
CHAPTER 1 Overview of Adaptive Server IQ System Administration • Sybase Central is an application for managing Sybase databases. It helps you manage database objects and perform common administrative tasks such as creating databases, backing up databases, adding users, adding tables and indexes, and monitoring database performance. Sybase Central has a Java-based graphical user interface, and can be used with any operating system that allows graphical tools.
Catalogs and IQ • The Catalog Store When you create an IQ database, all three stores are created automatically. You create IQ databases using the procedures described in Chapter 3, “Working with Database Objects”. The IQ Store The IQ Store is the set of Adaptive Server IQ tables. You can have one or more permanent IQ Stores, each in a separate database. Each IQ Store includes a set of tables that organize your data.
CHAPTER 1 Overview of Adaptive Server IQ System Administration Anywhere tables have a different format than IQ tables. While the commands you use to create objects in an Anywhere database are the same as those for an IQ Store, there are some differences in the features you can specify in those commands. Always use the command syntax in this book or the Adaptive Server IQ Reference Manual for operations in the IQ Store. This book explains how you manage your IQ Store and its associated Catalog Store.
Stored procedures You can also create your own stored procedures. See Chapter 6, “Using Procedures and Batches” for details. Note Statements shown in examples generally use the asiqdemo database, a sample database installed as part of Adaptive Server IQ. For a diagram of this database’s structure, see Introduction to Adaptive Server IQ. Adaptive Server IQ stored procedures The following procedures work specifically on the IQ Store. They are owned by the DBA user ID.
CHAPTER 1 Overview of Adaptive Server IQ System Administration Table 1-2: Stored Procedures for the IQ Store Procedure name sp_iqcheckdb sp_iqcommandstats sp_iqdbsize sp_iqdbstatistics Purpose Checks the validity of the current database and repairs indexes Gives statistics on execution of various commands Gives the size of the current database Reports results of the most recent sp_iqcheckdb sp_iqestjoin sp_iqestdbspaces sp_iqestspace sp_iqindex sp_iqindexsize Estimates the space needed to create joi
Stored procedures System procedures are built-in stored procedures used for getting reports from and updating system tables. Catalog stored procedures retrieve information from the system tables in tabular form. Note While these procedures perform the same functions as they do in Adaptive Server Enterprise and pre-Version 12 Adaptive Server IQ, they are not identical. If you have preexisting scripts that use these procedures, you may want to examine the procedures.
CHAPTER 1 Overview of Adaptive Server IQ System Administration System procedure Description sp_helptext Displays the text of a system procedure or view sp_password Adds or changes a password for a user ID Adaptive Server Enterprise catalog procedures Adaptive Server IQ implements all the Adaptive Server Enterprise catalog procedures with the exception of the sp_column_privileges procedure. The implemented catalog procedures are described in the following table.
System tables and views Procedure name Purpose sp_servercaps Display information about a remote server’s capabilities System tables and views Adaptive Server IQ system tables contain all of the information the database server needs to manage your IQ system. The system tables reside in the Catalog Store, and are sometimes called catalog tables. For some system tables there are also views that make it easier to display the information in the table. The SYS user ID owns the system tables.
CHAPTER 1 Overview of Adaptive Server IQ System Administration System table Description SYSDOMAIN Lists the number, name, ODBC type, and precision of each predefined data type Lists operating system files and dbspace names for the database SYSFILE SYSFKCOL Associates each foreign key column with a primary key column SYSFOREIGNKEY Contains general information about each foreign key SYSGROUP Describes a many-to-many relationship between groups and members SYSINDEX SYSINFO Describes indexes in the
System tables and views System table Description SYSLOGIN Lists User Profile names that can connect to the database with an integrated login Lists current SET OPTION settings for all users including the PUBLIC user Describes each procedure in the database Describes each parameter to every procedure in the database SYSOPTION SYSPROCEDURE SYSPROCPARM SYSPROCPERM Lists each user granted permission to call each procedure in the database SYSPUBLICATION Describes each SQL Remote publication Contains info
CHAPTER 1 Overview of Adaptive Server IQ System Administration System view Description SYSCATALOG Lists all tables and views from SYSTABLE Presents column update permission information from SYSCOLPERM Presents a readable version of the table SYSCOLUMN Presents foreign key information from SYSFOREIGNKEY and SYSFKCOL SYSCOLAUTH SYSCOLUMNS SYSFOREIGNKEYS SYSGROUPS Presents group information from SYSGROUP SYSINDEXES Presents index information from SYSINDEX and SYSIXCOL SYSOPTIONS Displays option sett
Commands and Functions Commands and Functions All Adaptive Server IQ commands are SQL statements. SQL stands for Structured Query Language, a language commonly used in database applications. Adaptive Server IQ SQL uses the same syntax as Adaptive Server Anywhere SQL; the only differences are for certain product capabilities that are supported only for IQ or for Anywhere. Adaptive Server IQ SQL also offers a high degree of compatibility with Transact-SQL, the SQL dialect used by Adaptive Server Enterprise.
CHAPTER 1 Overview of Adaptive Server IQ System Administration • Manipulate date and time data (for example, TODAY, DATEDIFF, DATEPART, MINUTES) • Convert retrieved data from one format to another (CAST, CONVERT) Message logging A message log file exists for each database. The default name of this file is dbname.iqmsg. The message log file is created when the database is created.
The utility database The date and time of the software build appears in the version string in ISO datetime format: YYYY-MM-DD hh:mm:ss where YYYY 4-digit year MM DD 2-digit month number (0-12) 2-digit day of month number (0-31) hh 2-digit numbmer of complete hours that have passed since midnight (00-23) 2-digit number of complete minutes that have passed since the start of the hour (00-59) 2-digit number of complete seconds that have passed since the start of the minute (00-59) mm ss The message log
CHAPTER 1 Overview of Adaptive Server IQ System Administration For more information on the utility database, see Chapter 3, “Configuring Client/Server Communications” in your Adaptive Server IQ Installation and Configuration Guide. Compatibility with earlier versions Version 12 of Adaptive Server IQ differs markedly from earlier versions of IQ.
Compatibility with earlier versions 20
CH A PTE R About this chapter 2 Running Adaptive Server IQ Three steps are required for you to start using Adaptive Server IQ: • The database server must be started. • The database must be started. • You must connect to the database. Adaptive Server IQ gives you great flexibility in performing these three steps. This chapter explains various options for accomplishing each of these steps, and gives suggestions for which to choose, depending on your situation.
Server command lines • Start the server and the sample database with a Sybase-provided configuration file. See “Starting the asiqdemo database” on page 47. • Place a server startup command in a shortcut or desktop icon. • Include a server startline in an ODBC data source. See “Creating and editing ODBC data sources” on page 65. • Include a server startline in a utility command. • Issue a SQL command from Interactive SQL to start an additional server. See “Starting a server from DBISQL” on page 40.
CHAPTER 2 Running Adaptive Server IQ You can choose from many command-line options or switches to specify such features as permissions required to start a database or stop the server, and the network protocols to use. The command-line switches are one means of tuning Adaptive Server IQ behavior and performance. There are slight variations in the basic command for different operating systems, as well as a startup utility that runs this command automatically. See the sections that follow for details.
Starting the server on UNIX Note the server starting directory Note what directory you are in when you start the server. The server startup directory determines the location of any database files you create with relative pathnames. If you start the server in a different directory, Adaptive Server IQ cannot find those database files. Any server startup scripts should change directory to a known location before issuing the server startup command.
CHAPTER 2 Running Adaptive Server IQ For an explanation of commonly used startup parameters, see “Using command-line switches” on page 28. Typing the server startup command You can also start the database server by entering the following command at the UNIX prompt: asiqsrv12 [ server-switches ] [ database-file [ database-switches ]] This command starts the specified database: • On the specified server, if one is named in the startup command.
Starting the server on Windows NT Starting the server on Windows NT This section describes methods for starting the database server that are specific to Windows NT systems. You can also use any of the generic methods described elsewhere in this chapter. Note the server starting directory Be sure to make note of what directory you are in when you start the server. The location of any database files you create with relative pathnames depends on the server startup directory.
CHAPTER 2 Running Adaptive Server IQ See “Using command-line switches” on page 28 for a description of commonly used startup parameters. Note To start the server without starting any database, you omit the database file from the asiqsrv12 command and specify a servername. See “Naming the server and databases” on page 31 for a discussion of why it is preferable to include both the database and server in the startup command.
Using command-line switches Running the UNIX database server as a daemon To run the UNIX database server in the background, and enable it to run independently of the current session, you run it as a daemon. Note Do not use ’&’ to run the database server in the background. It will not work. You must instead run the database server as a daemon. ❖ To run the UNIX database server as a daemon: • Use the -ud command-line option when starting the database server. For example: start_asiq -ud asiqdemo.
CHAPTER 2 Running Adaptive Server IQ For this switch See this section -gp “Setting a maximum Catalog page size” “Recovery time” -gr -gu -iqgovern “Controlling permissions from the command line” “Concurrent queries” “Buffer caches and physical memory” “Buffer caches and physical memory” -iqmc -iqtc -iqsmem “Unwired memory” (AIX, Compaq Tru64 UNIX, and HP UNIX only) -iqwmem “Wired memory” (Compaq Tru64 UNIX, HP UNIX, and Sun UNIX only) -n “Naming the server and databases” -p “Other performance-
Using command-line switches For example, the following configuration file starts the database mydb.db, on the database server named Elora, with a Catalog cache size of 16MB, TCP/IP as a network protocol and a specified port number, user connections limited to 10, and a Catalog page size of 4096 bytes. -n Elora -c 16M -x tcpip(port=2367 -gm 10 -gp 4096 path\mydb.db If you name the file mydb.cfg, you could use these command-line options as follows: asiqsrv12 @mydb.
CHAPTER 2 Running Adaptive Server IQ Configuration file for the sample database The asiqdemo.cfg file, which you use to start the sample database, sets startup parameters to the recommended defaults. You can also use this file as a template for your own configuration files. Chapter 3, “Running and Connecting to Servers”, Introduction to Adaptive Server IQ gives an example of the sample database configuration file. This file is found in demo/asiqdemo.cfg in your installation directory.
Using command-line switches asiqsrv12 mydb.db sample.db Naming databases You can name databases by supplying a -n switch following the database file. For example, the following command line starts a database and names it: asiqsrv12 mydb.db -n MyDB Naming a database lets you use a nickname in place of a file name that may be difficult to remember. Naming the server You name the server by supplying a -n switch before the first database file.
CHAPTER 2 Running Adaptive Server IQ You should adopt a set of naming conventions for your servers and databases, as well as for all other database objects, that includes a case specification. Enforcing naming conventions can prevent problems for users. Controlling performance from the command line Several command-line options can affect database server performance.
Using command-line switches To override these settings for the current server session, specify the server startup options -iqmc (main cache size) and -iqtc (temp cache size) to open the database and reset the defaults. The default sizes are 8MB for the main cache and 4MB for the temporary cache, which are too low for any active database use. Concurrent users Your license sets the absolute number of concurrent users. However, you must also set the -gm switch.
CHAPTER 2 Unwired memory Running Adaptive Server IQ The -iqsmem switch creates a memory pool to increase total available memory. This switch is available on all UNIX platforms, but is required in some cases: • On HP systems use -iqsmem if you want to use more than 2GB of memory. The value should be between 500 and 1400MB. • On AIX systems always use -iqsmem. The value for -iqsmem should be between 356 and 2560; otherwise, the server aborts. Specify this switch as the number of megabytes of memory.
Using command-line switches When a database server is running with multiple databases, the checkpoint time specified by the first database started will be used unless overridden by this switch. If a value of 0 is entered, the default value of 60 minutes is used. Recovery time The -gr parameter lets you set the maximum number of minutes that the database server will take to recover from system failure.
CHAPTER 2 • Stopping the server Running Adaptive Server IQ UTILITY_DB—Only those users who can connect to the utility_db database can create and drop databases. See “The utility database” on page 18 for information. The -gk option limits the users who can shut down a server to those with a certain level of permission in the database. • DBA (default) —Only the DBA can stop the server. • ALL (default)—Any user can stop the server.
Using command-line switches Setting up a client/server environment Three switches can help you set up your client/server environment. • -x specifies communication protocol options. • -tl sets the network connection timeout. • -ti sets the client connection timeout. See the sections that follow for details. Selecting communications protocols Any communications between a client application and a database server require a communications protocol.
CHAPTER 2 Running Adaptive Server IQ Additional parameters can be added to tune the behavior of the server for each protocol. For example, the following command line instructs the server to use two network cards, one with a specified port number. This command must be entered all on one line, even though it appears on multiple lines here. asiqsrv12 -x "tcpip(MyIP=192.75.209.12:2367,192.75.209.32)" -gm 10 -gp 4096 path\asiqdemo.
Using command-line switches Setting the default client timeout Adaptive Server IQ disconnects client connections that have not submitted a request for the number of minutes you specify with the -ti switch. By disconnecting inactive connections, this option frees any locks those connections hold. The default is 240 (4 hours).
CHAPTER 2 Running Adaptive Server IQ Starting multiple servers or clients on the same machine In a production environment, it would be unusual to have more than one server running on the same system. In a development environment, however, this situation can occur. If you are running more than one server or client on the same UNIX machine, and shared memory is enabled, you must take certain precautions to prevent users from connecting to the wrong server.
Monitoring server activity demo.001.srvlog demo.002.srvlog janedemo.001.srvlog For information about your most recent session, choose the log with the largest number for the desired server. Issue a tail –f command to view the log contents. For example: % tail -f demo.002.srvlog When you run start_asiq, specify the –z option to enhance the log file with additional information about connections. This will help new users or those troubleshooting connection problems.
CHAPTER 2 Running Adaptive Server IQ Stopping the database server The preferred ways to stop the database server are: • In UNIX, use the stop_asiq utility. For details, see “Example — Stop a server with stop_asiq”. Note that when stop_asiq is used, the following message appears: "Please note that ’stop_asiq’ will shutdown a server completely without regard for users connections or load processes status.
Stopping the database server Example — Stop a server with stop_asiq The following example uses the stop_asiq utility on UNIX systems to shut down an Adaptive Server IQ server and close all user connections to it. When you issue the stop_asiq command, Adaptive Server IQ lists all the servers owned by other users, followed by the server(s) you own. It then asks if you want to stop your server. For example: % stop_asiq Checking system for ASIQ 12 Servers ...
CHAPTER 2 Running Adaptive Server IQ If no running servers were started by your user ID, Adaptive Server IQ displays information about servers run by other users, then a message like the following: There are no servers owned by ’janed’ Example —Stop a server from DBISQL The following example stops a server from DBISQL: STOP ENGINE Ottawa UNCONDITIONALLY The optional keyword UNCONDITIONALLY specifies that the database server will be stopped even if there are connections to it.
Starting and stopping databases Examples of commands that do not stop a server cleanly include: • Stopping the process in the Windows NT Task Manager Processes window. • Using a UNIX kill command. Starting and stopping databases You can start databases when you start the server, or after the server is running. To start a database when you start the server, see “Starting the database server” on page 21 for details. A database server can have more than one database in use at a time.
CHAPTER 2 Running Adaptive Server IQ Permission limitations The -gd server command-line option determines the permission level required to start databases. By default, this option is set to DBA, so that only users with database administrator privileges can start IQ databases. However, you can also set this option to ALL or NONE. ALL means that all users can start a database. NONE means that no users, including the DBA, can start a database.
Starting and stopping Sybase Central where path is your Adaptive Server IQ installation directory, demo\asiqdemo.cfg specifies the configuration file, and demo\asiqdemo.db is the sample database file. The asiqsrv12 command starts the server in a dedicated window. You can start database servers by entering this command at a system command prompt, as described elsewhere in this chapter.
CHAPTER 2 Running Adaptive Server IQ Figure 2-1: The Sybase Central Hierarchy Plug-ins for Sybase Central, such as the Adaptive Server IQ database management system, occupy the first level in the Sybase Central hierarchy after the root level. A plug-in is a graphical tool for managing a particular product. When you install the product, you can also install its Sybase Central plug-in.
Introduction to connections 3 If Adaptive Server IQ (ASIQ) is listed, select Register. If not, select Load. Use the Browse button to find and select the file ASIQPlugin.jar. Click OK. Stopping Sybase Central To stop Sybase Central, select File → Exit. Introduction to connections The remainder of this chapter describes how client applications connect to databases.
CHAPTER 2 Running Adaptive Server IQ How connections are established To establish a connection, the client application calls functions in one of the supported interfaces. Adaptive Server IQ supports the following interfaces: • ODBC — ODBC connections are discussed in this chapter. • Embedded SQL — Embedded SQL connections are discussed in this chapter. • Sybase Open Client — Open Client connections are not discussed in this chapter.
Introduction to connections If you want ... Consider reading ...
CHAPTER 2 Running Adaptive Server IQ You must enter a connection string on a single line, with the parameter settings separated by semicolons. Connection parameters are passed as connection strings Connection parameters are passed to the interface library as a connection string. This string consists of a set of parameters, separated by semicolons.
Simple connection examples For steps in connecting to a database using Sybase Central, see the Introduction to Adaptive Server IQ. For more detailed information on available connection parameters and their use, see “Connection parameters” on page 73. Connecting to a database from DBISQL Many examples and exercises throughout the documentation start by connecting to the sample database from Interactive SQL, also called DBISQL. Here is how to carry out this step.
CHAPTER 2 Running Adaptive Server IQ % cp $SYBASE/asiq12/tix/default.tix This file controls key sequences for DBISQL and improves the command window display. For more information, see Chapter 6, “Getting Started with DBISQL” in Introduction to Adaptive Server IQ. 4 Start DBISQL by entering dbisql -c "uid=DBA;pwd=SQL;eng=servername;links=tcpip" at the command line.
Simple connection examples You can include the -c parameter to specify connection parameters in the dbisql command, as described in the procedure above for connecting to UNIX. If you omit these parameters, the DBISQL logon window appears. 2 Enter the user ID DBA and the password SQL This is the default user ID and password for Adaptive Server IQ databases when they are created. 3 Click the Database tab and type the server name (for example, “hostname_asiqdemo” for the asiqdemo database).
CHAPTER 2 ❖ Running Adaptive Server IQ To connect to a database from DBISQL on UNIX: 1 Start the server and the database by typing at a system command prompt: start_asiq dbname 2 Start DBISQL by typing at a system command prompt: dbisql -c "uid=userID;pwd=password;eng=dbname;links=tcpip" For example, to connect to the sample database you would enter: dbisql -c "uid=DBA;pwd=SQL;eng=asiqdemo;links=tcpip" The –c parameter specifies connection parameters.
Simple connection examples The following connection parameters show how to load the sample database as an embedded database: dbf=path\asiqdemo.db uid=dba pwd=sql where path is the name of your Adaptive Server IQ installation directory. Using the Start parameter The following connection parameters show how you can customize the startup of the sample database as an embedded database.
CHAPTER 2 5 Running Adaptive Server IQ Leave all other fields blank, and click OK. Adaptive Server IQ starts up and loads the sample database, and DBISQL connects to the database. Connecting using a data source You can save sets of connection parameters in a data source. Data sources can be used by ODBC and Embedded SQL applications like DBISQL. You can create data sources from the ODBC Administrator; see “Creating and editing ODBC data sources” for details.
Simple connection examples Connecting to a server on a network To connect to a database running on a network server somewhere on a local or wide area network, the client software must be able to locate the database server. Adaptive Server IQ provides a network library (a DLL or shared library) that handles this task. Network connections occur over a network protocol. Several protocols are supported, including TCP/IP, IPX, and NetBIOS.
CHAPTER 2 Running Adaptive Server IQ The network library searches for a server by broadcasting over the network, which can be a time-consuming process. Once the network library locates a server, the client library stores its name and network address in a file. Users should never need to use this file directly. Adaptive Server IQ reuses this entry for subsequent connection attempts, which can be many times faster than a connection that is achieved by broadcast.
Simple connection examples pwd=password Note Do not use these parameters if more than one local server is running, or you may connect to the wrong server. Default database If more than one server is running, you need to specify which one you wish to connect to. If only one database has been started on that server, you do not need to specify the database name.
CHAPTER 2 Running Adaptive Server IQ 2 Using the SQLCONNECT environment variable settings if any command line values are missing. Adaptive Server IQ database utilities do not set this variable automatically. For a description of the SQLCONNECT environment variable, see Chapter 1, “Environment Variables and Registry Entries,” in Adaptive Server IQ Reference Manual.
Working with ODBC data sources DSNs and FILEDSNs You specify a data source either as a DSN (data source name) or as a FileDSN (file data source name). You can reference a data source in the Windows NT registry using the DSN connection parameter: DSN=my data source You can reference a data source held in a file using the FileDSN connection parameter: FileDSN=mysource.dsn DSNs and FileDSNs differ only in how they are stored, and how you create them.
CHAPTER 2 Running Adaptive Server IQ To create or edit DSNs or File DSNs on UNIX systems, use a text editor. For DSNs you can edit the .odbc.ini file directly. For File DSNs, create a file with the name you choose, using the file extension .dsn. Note Sybase recommends that, to avoid ambiguity, you be as specific as possible in creating ODBC and other data sources, whether you create them using the ODBC Administrator, or by editing odbc.ini, .odbc.ini, or .dsn files directly.
Creating and editing ODBC data sources ❖ To create an ODBC User Data Source: 1 Select Settings → Control Panel → ODBC or Select Programs → Sybase → Adaptive Server IQ → ODBC Administrator 2 In the ODBC Data Source Administrator, click Add on the User DSN tab. 3 Select the Adaptive Server IQ 12 from the list of drivers and click Finish. 4 In the Adaptive Server IQ ODBC Configuration box, type the Data Source Name. 5 Now click the Login tab. Type the User ID and Password for your database.
CHAPTER 2 Running Adaptive Server IQ 3 Click Configure. 4 On the ODBC Configuration dialog box, click Test Connection. If you cannot access the Data Source, check that you have filled out the various tabs with correct file and pathnames. To edit a data source, select one from the list in the ODBC administrator window and click Configure. If you need to access Windows NT across a network in order to create an ODBC data source, see the Adaptive Server IQ Installation and Configuration Guide.
Creating and editing ODBC data sources Microsoft applications (keys in SQL Statistics) Check this box if you wish foreign keys to be returned by SQL statistics. The ODBC specifications states that primary and foreign keys should not be returned by SQL statistics, however, some Microsoft applications (such as Visual Basic and Access) assume that primary and foreign keys are returned by SQL statistics. Check this box to improve performance for Borland Delphi applications.
CHAPTER 2 Running Adaptive Server IQ Login tab Use integrated login Connects using an integrated login. The User ID and password do not need to be specified. To use this type of login users must have been granted integrated login permission. The database being connected to must also be set up to accept integrated logins. Only users with DBA access can administer integrated login permissions. For more information, see “Using integrated logins” on page 87.
Creating and editing ODBC data sources For more information on the parameters in the Database tab, see the EngineName, StartLine, DatabaseName, DatabaseFile, and AutoStop connection parameters in the chapter “Connection and Communication Parameters” in the Adaptive Server IQ Reference Manual.
CHAPTER 2 Display debugging information in a log file Running Adaptive Server IQ The name of the file in which the debugging information is to be saved. Enter any additional switches here. Parameters set throughout the remainder of this dialog take precedence over parameters typed here. Additional connection parameters Creating a File Data Source Data sources are stored in the system registry. File data sources are an alternative, which are stored as files.
Using ODBC data sources on UNIX Using ODBC data sources on UNIX On UNIX operating systems, ODBC data sources are held in a file named .odbc.ini. When creating a .odbc.ini file on any UNIX system, you must use the long form of each identifier, for example: [My Data Source] EngineName=myserver CommLinks=tcpip UserID=DBA Password=SQL Network communications parameters are added as part of the CommLinks parameter. For a complete list, see “Connection parameters” on page 73.
CHAPTER 2 Running Adaptive Server IQ Connection parameters Adaptive Server IQ connection parameters are listed in the following table. For a full description of each of these connection parameters, see Chapter 3, “Connection and Communication Parameters” in the Adaptive Server IQ Reference Manual.
Connection parameters 74 Parameter Short form DatabaseSwitches DBS Argument String DataSourceName ** DSN String Debug DBG Boolean DisableMultiRowFetch DMRF Boolean Turn off multi-record fetches across the network. EngineName ENG String Identify server to connect to EncryptedPassword ENP Encrypted string Provide a password, and store it in an encrypted fashion in a data source.
CHAPTER 2 Parameter Short form Logfile Running Adaptive Server IQ LOG Argument String Description Send client error messages and debugging messages to a file. Password ** PWD String Provide a password for the connection ServerName ENG String StartLine Start String Specify server to connect to Start a database server running from an application (for embedded databases).
Connection parameters • The entries in a connection string are read left to right. If the same parameter is specified more than once, the last one in the string applies. • If a string contains a DSN or FILEDSN entry, the profile is read from the configuration file, and the entries from the file are used if they are not already set.
CHAPTER 2 Running Adaptive Server IQ How Adaptive Server IQ makes connections This section describes how the interface libraries establish connections. Who needs to read this section? In many cases, establishing a connection to a database is straightforward using the information presented in the preceding sections of this chapter.
How Adaptive Server IQ makes connections 3 Locate a server. Using the connection parameters, the ODBC driver or Embedded SQL interface library must locate a database server on your machine or over a network. 4 Locate the database. Once it locates the server, the ODBC driver or Embedded SQL interface library must locate the database you are connecting to. The following sections describe each of these steps in detail.
CHAPTER 2 Running Adaptive Server IQ Assembling a list of connection parameters The following figure illustrates how the interface libraries assemble the list of connection parameters they will use to establish a connection.
How Adaptive Server IQ makes connections • CommBufferSpace Ignored if another connection has already set this parameter. • Unconditional Ignored if the database is already loaded or if the server is already running. The interface library uses the completed list of connection parameters to attempt to connect.
CHAPTER 2 Running Adaptive Server IQ Locating a server The next step in establishing a connection is to attempt to locate a server. If the connection parameter list includes a server name (ENG parameter), the interface library carries out a search first for a database server of that name, followed by a search over a network. If no ENG parameter is supplied, the interface library looks for a default server.
How Adaptive Server IQ makes connections 82 • The network search involves a search over one or more of the protocols that Adaptive Server IQ supports. For each protocol, the network library starts a single port. All connections over that protocol at any one time use a single port. • You can specify a set of network communication parameters for each network port in the argument to the CommLinks parameter.
CHAPTER 2 Running Adaptive Server IQ Locating the database If the interfaces library successfully locate a server, it then tries to locate the database. For example: Notes • If you rely on the DBF parameter, the DBF path must either be an absolute path, or relative to where the server was started, in order for Adaptive Server IQ to find the database it specifies. For example, if you specify ../foo/asiqdemo, it looks in the directory above where the server is, and then in foo.
How Adaptive Server IQ makes connections Server name caching for faster connections The network library looks for a database server on a network by broadcasting over the network using the CommLinks connection parameter. Tuning the broadcast The CommLinks parameter takes as argument a string that lists the protocols to use and, optionally for each protocol, a variety of network communication parameters that tune the broadcast.
CHAPTER 2 Running Adaptive Server IQ Interactive SQL connections The Interactive SQL (DBISQL) utility has a different behavior from the default Embedded SQL behavior when a CONNECT statement is issued while already connected to a database. If no database or server is specified in the CONNECT statement, Interactive SQL connects to the current database, rather than to the default database. This behavior is required for database restarting operations.
Using an integrated login Using an integrated login The integrated login feature allows you to maintain a single user ID and password for both database connections and operating system and/or network logins. This section describes the integrated login feature. Operating systems supported Integrated login capabilities are available for the Windows NT server only. It is possible for Windows 95, Windows 98, and Windows NT clients to use integrated logins to connect to a network server running on Windows NT.
CHAPTER 2 Running Adaptive Server IQ Using integrated logins Several steps must be implemented in order to connect successfully via an integrated login. ❖ To use an integrated login: 1 Enable the integrated login feature in a database by setting the value of the LOGIN_MODE database option to either Mixed or Integrated (the option is case insensitive), in place of the default value of Standard. This step requires DBA authority).
Using an integrated login Example The following SQL statement sets the value of the LOGIN_MODE database option to Mixed, allowing both standard and integrated login connections: SET OPTION "PUBLIC".LOGIN_MODE = Mixed Creating an integrated login User profiles can only be mapped to an existing database user ID. When that database user ID is removed from the database, all integrated login mappings based on that database user ID are automatically removed.
CHAPTER 2 Running Adaptive Server IQ Connecting from a client application A client application can connect to a database using an integrated login in one of the following ways: • Set the INTEGRATED parameter in the list of connection parameters to yes. • Specify neither a user ID nor a password in the connection string or connection dialog. This method is available only for Embedded SQL applications, including the Adaptive Server IQ administration utilities.
Using an integrated login Setting the attribute ’Integrated=yes’ in an ODBC data source causes database connection attempts using that DSN to attempt an integrated login. If the LOGIN_MODE database option is set to Standard, the ODBC driver prompts the user for a database user ID and password. Security concerns: unrestricted database access The integrated login features works by using the login control system of Windows NT in place of the system Adaptive Server IQ uses to control access to the database.
CHAPTER 2 Running Adaptive Server IQ • An integrated login mapping exists between the user dsmith and the database user ID DBA. When the user dsmith connects to the server with her correct login ID and password, she connects to the database as DBA, a user with full administrative rights.
Using an integrated login For more information on using the SET OPTION statement see Chapter 9, “SQL Statements” in Adaptive Server IQ Reference Manual. Network aspects of integrated logins If the database is located on a network server, then one of two conditions must be met for integrated logins to be used: • The user profile used for the integrated login connection attempt must exist on both the local machine and the server.
CHAPTER 2 Running Adaptive Server IQ Troubleshooting startup, shutdown, and connections See the sections that follow for help in resolving problems with your database server, connections, and DBISQL. For other troubleshooting hints, see the Adaptive Server IQ Troubleshooting and Error Messages Guide. What to do if you can’t start Adaptive Server IQ This section describes some common problems when starting the database server.
Troubleshooting startup, shutdown, and connections Ensure that network communication software is running Appropriate network communication software must be installed and running before you run the database server. If you are running reliable network software with just one network installed, this should be straightforward.
CHAPTER 2 Running Adaptive Server IQ What to do if you can’t connect to a database If you are unable to connect to an Adaptive Server IQ database, check the items described below. • Check that you have entered your data source name correctly, or that you have selected the correct server name for a JDBC connection. • Check that your data source (DSN or FILEDSN) contains the correct server name, database, network parameters, and any other connection information you expect.
Troubleshooting startup, shutdown, and connections • Check that all of the files exist for the database you have requested. At a minimum, there must be an IQ Store (dbname.iq), a Catalog Store (dbname.db), an IQ Temporary Store (dbname.iqtmp), a transaction log (dbname.log This may be missing if the database is newly created and has not been modified.), and a message file (dbname.iqmsg). The names shown here in parentheses are the default format; yours may be different.
CHAPTER 2 Running Adaptive Server IQ For help in improving the appearance of DBISQL windows, or if you are unable to use function keys in DBISQL, see the Chapter 6, “Getting Started with DBISQL” in Introduction to Adaptive Server IQ.
Troubleshooting startup, shutdown, and connections 98
CH A PTE R About this chapter 3 Working with Database Objects This chapter describes the mechanics of creating, altering, and deleting database objects such as tables, views, and indexes. The SQL statements for carrying out these tasks are called the Data Definition Language (DDL). The definitions of the database objects form the database schema. You can think of the schema as an empty database.
Building Your Adaptive Server IQ Databases Sybase WarehouseArchitect helps you design your database. WarehouseArchitect is a component of Sybase Warehouse Studio, an integrated platform for designing and managing a data warehouse. No matter what design tool you use, it is generally the database administrator (DBA) who designs the database and defines its contents. To create an effective design, the DBA needs to work with individuals throughout your organization to understand how data will be used.
CHAPTER 3 Working with Database Objects This chapter is concerned with the SQL statements for working with database objects. If you are using Sybase Central, these SQL statements are generated for you. The primary source of information about Sybase Central is the Sybase Central online Help. This chapter gives only brief pointers for tasks that you can carry out using Sybase Central.
Building Your Adaptive Server IQ Databases Multiplex users: The following steps are for creating a non-multiplex database. To create a multiplex database, see the Adaptive Server IQ Multiplex User’s Guide. ❖ To set up an IQ database: 1 Install and configure Adaptive Server IQ. This step creates the database server and the asiqdemo database, and allows you to create your first database when you have no other database to connect to.
CHAPTER 3 Working with Database Objects You will probably want to schedule data definition operations for times when database usage is low. All other users are blocked from reading or writing to a table while you are creating or altering that table, although for a brief time only. If the table is part of a join index, users cannot read or write to any of the tables in the join index until the data definition operation is complete.
Building Your Adaptive Server IQ Databases The DBA and object owners can grant authority to individual users and to groups of users. For complete information, see Chapter 10, “Managing User IDs and Permissions” You can also use the -gu command-line option to set the permission level required to create or delete a database. Selecting a device type You store databases and database objects on devices. On all platforms, these devices can be operating system files.
CHAPTER 3 Working with Database Objects Space requirements for IQ Stores The amount of data, and the number and types of indexes you create, determine how much space you need in your IQ database. If you run out of space when loading or inserting into a database, Adaptive Server IQ prompts you to create another dbspace, and then continues the operation after you add the dbspace.
Working with databases Each time you run sp_iqestjoin, select one of the suggested index sizes. If you know you will always join the tables with exact one-to-one matches, use the “Min Case index_size”. If you anticipate occasional one-to-many joins, use the “Avg Case index_size”. If you anticipate using numerous one-to-many joins, use the “Max Case index_size”. 4 Total the index_sizes you selected for all table pairs.
CHAPTER 3 Working with Database Objects Creating a database When you create a database, the database server creates the following four dbspaces: dbspace name IQ_SYSTEM_MAIN IQ_SYSTEM_TEMP IQ_SYSTEM_MSG SYSTEM Contents Main (permanent) IQ Store file Temporary IQ Store file Message log file Catalog Store file Default operating system file name dbname.iq dbname.iqtmp dbname.iqmsg dbname.db The SYSTEM dbspace contains the system tables, which hold the schema definition as you build your database.
Working with databases Locating and moving database files When you create a database, you specify its location. Before you do so, consider whether you will ever need to move the database. In order to move a database, it is crucial that you have all of the files you need, that they be in a consistent state when you move them, and that no users are connected to the database. Because it is difficult to achieve these conditions, you should avoid copying a database unless absolutely necessary.
CHAPTER 3 Working with Database Objects • Case sensitive (CASE RESPECT).’ABC’ compares NOT EQUAL to ‘abc’. Note that the default login is now user ID DBA and password SQL (uppercase). Passwords are case sensitive for a case-sensitive database, and case-insensitive for a case-insensitive database. Usernames are always case insensitive. • Catalog page size of 2048 bytes (PAGE SIZE 2048).
Working with databases If you omit the directory path, Adaptive Server IQ locates the files as follows: • The Catalog Store is created relative to the working directory of the server. • The IQ Store is created relative to the working directory of the server. • The Temporary Store is created in the same directory as the IQ Store, unless it is on a raw device. (This also occurs if you do not specify any file name.
CHAPTER 3 Working with Database Objects • The Temporary Store is in c:\company\company.iqtmp • The IQ message log file is in c:\company\company.iqmsg CREATE DATABASE ’company.db’ IQ SIZE 20 IQ PATH ’c:\\company\\iqdata\\company.iq’ Example The following statement creates an IQ database called company.db. This database consists of four UNIX files: • The Catalog Store is in company.
Working with databases Adaptive Server IQ stores data on disk in compressed form. It uncompresses the data and moves data pages into memory for processing. The IQ page size determines the amount of disk compression and the default I/O transfer block size for the IQ Store. For most applications, this default value is best.
CHAPTER 3 Working with Database Objects Choosing a block size for your database Example The following statement creates a large database on a UNIX raw partition with a Catalog PAGE SIZE of 4KB, and an IQ PAGE SIZE of 128KB. By default, the IQ Store size is 8MB and the Temporary Store is 4MB. CREATE DATABASE ’company’ IQ PATH ’/dev/rdsk/c2t6d0s3’ PAGE SIZE 4096 IQ PAGE SIZE 131072 Enabling Java in the database By default, Java support is ON for IQ databases. It can be turned off with the JAVA OFF option.
Working with databases Adding dbspaces When you create a database, it has only one file for storing permanent IQ data, one file for storing Catalog data, and one file each for the IQ message log and the Temporary Store. Each of these files is a dbspace, as described in “Creating a database”. Initially, the definitions of all IQ database objects go into the SYSTEM dbspace (the Catalog Store), and all IQ data is placed in the IQ_SYSTEM_MAIN dbspace (the IQ Store).
CHAPTER 3 Working with Database Objects When data is stored on raw partitions, you can have one dbspace per drive. When data is stored in a file system, you can take advantage of striping in the storage system. If you use operating system or hardware striping on a multiuser system, your stripe size should be a minimum of 1MB, or the highest size possible. In any case, your stripe size should be several times your IQ page size.
Working with databases Reserving space for DDL commands In the event that you run out of space to perform an operation, you will see a message telling you to more space. In addition to space for the new dbspace itself, you also need a small amount of space to issue the ADD DBSPACE command. To ensure that you have the space for this and related DDL commands, set the options MAIN_RESERVED_DBSPACE_MB and TEMP_RESERVED_DBSPACE_MB. Do not wait until you have run out of space to set these options.
CHAPTER 3 Name Working with Database Objects Value ======================================================= Adaptive Server IQ (TM) Copyright (c) 1992-2000 by Sybase, Inc. All rights reserved. Version: 12.4.2/(32bit mode)/Sun_svr4/OS 5.6/EBF 0000 Time Now: 2000-03-14 12:05:54.
Working with tables Dropping a database Dropping a database deletes all tables and data from disk, including the transaction log that records alterations to the database. It also drops all of the dbspaces associated with the database. To drop a database, use the following statement: DROP DATABASE dbname You must specify the database name and its pathname exactly as they were specified when the database was created. For example, on a Windows NT system: DROP DATABASE ’c:\sybase\data\mydb.
CHAPTER 3 SQL statement for creating tables Working with Database Objects 2 Click the Tables folder for that database. 3 Double-click Add Table in the right panel. 4 Enter a Name for the table. 5 To create an IQ table, skip this step. To create a table in the Catalog Store, double-click the Advanced Table Properties icon, and select SYSTEM from the DB space dropdown list. 6 Enter the columns you want and their data types and other attributes in the Table Editor. 7 Click OK to create the table.
Working with tables By internally executing the COMMIT statement before creating the table, Adaptive Server IQ makes permanent all previous changes to the database. There is also a COMMIT after the table is created. For a full description of the CREATE TABLE statement, see “CREATE TABLE statement” in the Adaptive Server IQ Reference Manual. For information about building constraints into table definitions using CREATE TABLE, see Chapter 7, “Ensuring Data Integrity”.
CHAPTER 3 Working with Database Objects WHERE l_discount < 0.5 You declare a local temporary table for your connection only, using the DECLARE LOCAL TEMPORARY TABLE statement. A local temporary table exists until the connection ends, or within a compound statement in which it is declared. The table and its data are completely inaccessible to other users. See “Versioning of temporary tables” for versioning information on local temporary tables.
Working with tables If you use the ALTER TABLE command to add a UNIQUE column to an existing table, or to designate an existing column as UNIQUE, an HG index is created automatically. For complete information on IQ indexing, see Chapter 4, “Adaptive Server IQ Indexes” Optimizing storage and query performance When you create a permanent table in an IQ database, Adaptive Server IQ automatically stores it in a default index that facilitates a type of query called a projection.
CHAPTER 3 Working with Database Objects By contrast, when you specify UNIQUE or PRIMARY KEY, each value can occur only once in that column. For example, in the employee table, each value of ss_number, the employee’s social security number, can occur just once throughout that column. This uniqueness extends to NULL values. Thus, a column specified as UNIQUE must also have the constraint NOT NULL. Altering tables This section describes how to change the structure of a table using the ALTER TABLE statement.
Working with tables Altering tables in Sybase Central The property sheets for tables and columns display all the table or column attributes. You can alter a table definition in Sybase Central by displaying the property sheet for the table or column you wish to change, altering the property, and clicking OK to commit the change. Altering tables in a join index You cannot ADD, DROP or MODIFY a base table column that participates in a join condition of a join index.
CHAPTER 3 Working with Database Objects Creating primary and foreign keys The CREATE TABLE and ALTER TABLE statements allow many attributes of tables to be set, including column constraints and checks. This section shows how to set table attributes using the primary and foreign keys as an example.
Working with tables For more information, see the Sybase Central online Help. Note Multi-column primary keys are not enforced, and require the keyword UNENFORCED. Primary key column order is based on the order of the columns during table creation. It is not based on the order of the columns as specified in the primary key declaration.
CHAPTER 3 ❖ Working with Database Objects To create an unenforced foreign key in Sybase Central: 1 Connect to the database. 2 Click the Tables folder for that database. 3 Click the table holding the primary key, and drag it to the foreign key table. 4 When the primary key table is dropped on the foreign key table, the Foreign Key Wizard is displayed, which leads you through the process of creating the foreign key. For more information, see the Sybase Central online Help.
Working with views Similarities between views and base tables Differences between views and permanent tables Benefits of tailoring access Views are similar to the permanent tables of the database (a permanent table is also called a base table) in many ways: • You can assign access permissions to views just as to base tables. • You can perform SELECT queries on views. • You can perform INSERT and DELETE operations on some views. • You can create views based on other views.
CHAPTER 3 Working with Database Objects CREATE VIEW DepartmentSize AS SELECT dept_ID, count(*) FROM employee GROUP BY dept_ID The information in a view is not stored separately in the database. Each time you refer to the view, the associated SELECT statement is executed to retrieve the appropriate data. On one hand, this is good because it means that if someone modifies the employee table, the information in the DepartmentSize view will be automatically up to date.
Working with views CREATE VIEW viewname AS Inserting and deleting on views UPDATE, INSERT, and DELETE statements are allowed on some views, but not on others, depending on their associated SELECT statement.
CHAPTER 3 • Working with Database Objects The user was granted appropriate permission(s) for the operation on the view by a non-DBA user. This user must be either the owner of the view or have WITH GRANT OPTION of the appropriate permission(s) on the view.
Working with indexes You can use DBISQL to browse the information in these tables. Type the following statement in the DBISQL command window to see all the columns in the SYS.SYSVIEWS view: SELECT * FROM SYS.SYSVIEWS To extract a text file containing the definition of a specific view, use a statement such as the following: SELECT viewtext FROM SYS.SYSVIEWS WHERE viewname = ’DepartmentSize’; OUTPUT TO viewtext.
CHAPTER 3 Working with Database Objects Indexes are created on a specified table, or on a set of tables for join indexes. You cannot create an index on a view. Creating indexes You can create column indexes in three ways: • With the CREATE INDEX command • With the Add Index option in Sybase Central • With the UNIQUE or PRIMARY KEY column constraint of CREATE TABLE, which creates a unique index automatically.
Working with indexes Figure 3-1: sp_iqindex results If you omit the table name from the command, sp_iqindex displays this information for all tables in the database. Removing indexes If a column index or join index is no longer required, you can remove it from the database using the DROP statement. You can also drop indexes in Sybase Central by clicking the table name, right-clicking to display options, and clicking the Delete option.
CH A PTE R About this chapter 4 Adaptive Server IQ Indexes This chapter describes the Adaptive Server IQ index types. It explains how you create an index, and provides information to help you decide what index types are best suited for the way you use the data in your database. It also includes performance and resource issues related to indexing. Overview of indexes Indexes are used to improve data retrieval performance. Traditional indexes use a B-tree index strategy to point to the data records.
Overview of indexes The default column storage structure that Adaptive Server IQ creates for each column is actually an index optimized for storing and projecting data. Depending on the size of your database, the disk space available to you, and the type of queries your users submit, you will almost certainly want to supplement this default index with one or more of the Adaptive Server IQ bitwise index types. You can choose from four column index types.
CHAPTER 4 Adaptive Server IQ Indexes To take advantage of the High_Non_Group index types for columns with nonintegral numeric data, use the NUMERIC or DECIMAL data types, which support up to 254 digits to the left or right of the decimal point. Be aware that some index types are incompatible, and that creating indexes you don’t need wastes a lot of disk space. Read the sections that follow for details on how to select an index.
Creating Adaptive Server IQ indexes • Because indexes are compact, more data can be kept in memory for subsequent queries, thereby speeding throughput on iterative analysis. • Tuning is data-dependent, allowing data to be optimized once for any number of ad hoc queries. Creating Adaptive Server IQ indexes You can create a column index explicitly using either the CREATE INDEX statement or Sybase Central. These two methods are discussed in the sections that follow.
CHAPTER 4 Adaptive Server IQ Indexes You can use the keywords BEGIN PARALLEL IQ and END PARALLEL IQ to delimit any number of CREATE INDEX statements that you want to execute as a group at the same time. These keywords can only be used when creating indexes on IQ tables, not temporary tables or Adaptive Server Anywhere tables. Note that, if one of these CREATE INDEX statements fails, all of them roll back. For more information, see the Adaptive Server IQ Reference Manual.
Choosing an index type • Each CREATE INDEX statement can create only one index. • Each connection can create only one index at a time. • If two connections issue CREATE INDEX statements on the same table, the first statement works; the other gets an error saying that only 1 writer is allowed.
CHAPTER 4 Adaptive Server IQ Indexes Number of unique values in the index Adaptive Server IQ indexes are optimized according to the number of unique (distinct) values they include. When this number reaches certain levels, choose indexes according to the recommendations in Table 4-1.
Choosing an index type Often, the type of data in a column gives a good indication how the column will be used. For example, a date column will probably be used for range searches in WHERE clauses, and a column that contains prices or sales amounts will probably be used in the projection as an argument for aggregate functions (SUM, AVG, and so on). Note Adaptive Server IQ can still resolve queries involving a column indexed with the wrong index type, although it may not do so as efficiently.
CHAPTER 4 Adaptive Server IQ Indexes These estimates are generally valid; however, other factors can take precedence: • For range predicates, the number of unique values is a more important factor. • With the set functions COUNT, COUNT DISTINCT, SUM, MIN, MAX, and AVG, in order to use any index other than the default, the entire query must be resolvable using a single table or join index. • BIT data, and VARCHAR data greater than 255 bytes, can only be used in the default index.
Adaptive Server IQ index types • VARCHAR data with more than 255 bytes HNG indexes do not allow FLOAT, REAL, or DOUBLE data. All other data types are supported in all index types. If you try to create an index on a column that contains VARCHAR data greater than 255 bytes, you get this error message: An index cannot be created on a varchar column greater than 255 characters.
CHAPTER 4 Adaptive Server IQ Indexes Default column index For any column that has no index defined, or whenever it is the most effective, query results are produced using the default index. This structure is fastest for projections, but generally is slower than any of the three column index types you define for anything other than a projection.
Adaptive Server IQ index types Advantages and disadvantages of Low_Fast The following table lists advantages and disadvantages of Low_Fast indexes. Table 4-5: LF advantages/disadvantages Advantages This index is fast, especially for single table SUM, AVG, COUNT, COUNT DISTINCT, MIN, and MAX operations. Disadvantages Can only be used for a maximum of 10,000 unique values. Cannot use this index if data in your columns is BIT, or VARCHAR > 255 bytes.
CHAPTER 4 Adaptive Server IQ Indexes Advantages and disadvantages of High_Group The following table lists advantages and disadvantages of High_Group indexes. Table 4-6: HG advantages/disadvantages Advantages Quickly processes queries with GROUP BY. This index facilitates join index processing. It is one of indexes recommended for columns used in join relationships. LF is the other.
Adaptive Server IQ index types However, because multi-column primary keys are always unenforced, the automatically created High_Group index for a multi-column primary key is a phantom index: it includes all of the key columns, but does not contain any data. This structure is used for query optimization, but not for resolving queries. You need to create explicitly an HG (or LF) index on any multi-column primary key columns that will be used in a join predicate.
CHAPTER 4 Adaptive Server IQ Indexes Table 4-7: HNG advantages/disadvantages Advantages Due to compression algorithms used, disk space requirements can be reduced without sacrificing performance. If the column has a high number of unique values, this is the fastest index, with few exceptions described below. Disadvantages This index is not recommended for GROUP BY queries. Index not possible if uniqueness enforced.
Adaptive Server IQ index types AND l_suppkey = s_suppkey AND c_nationkey = s_nationkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = ’ASIA’ AND o_orderdate >= ’1994-01-01’ AND o_orderdate < ’1995-01-01’ GROUP BY n_name HAVING n_name LIKE "I%" AND SUM(l_extendedprice*(1-l_discount)) > 0.50 ORDER BY 2 DESC All columns referenced in this query except l_extendedprice and l_discount should have an LF or HG index.
CHAPTER 4 Adaptive Server IQ Indexes Adding column indexes after inserting data When you create an additional column index, the CREATE INDEX command creates the new index as part of the individual table and as part of any join indexes that include the column. If the existing column indexes in the individual table already contain data, the CREATE INDEX statement also inserts data into the new index from an existing index.
Using join indexes How join indexes are used for queries After you create a join index, its use is determined by the criteria of the SELECT statement. If a join index exists that joins the tables in the FROM clause by the relationship specified in the WHERE clause, or if a join index exists that is based on ANSI join syntax for natural or key joins, the join index is used to speed up queries. Otherwise, ad hoc joins between indexes on the individual tables are performed at query time.
CHAPTER 4 Adaptive Server IQ Indexes Adaptive Server IQ join hierarchies have one table at the top of the tree where the join ends. This table, known as the top table, does not connect to any other tables, although other tables connect to it. The top table always represents the “many” side in a one-to-many relationship.
Using join indexes You can set the frequency of these messages with the NOTIFY_MODULUS option, and override the option value in either the CREATE DATABASE or LOAD TABLE command. For examples of these messages, see “Interpreting notification messages” on page 187.
CHAPTER 4 Adaptive Server IQ Indexes If the join column is made up of more than one column, the combination of the values must be unique on the “one” side. For example, in the asiqdemo database, the id in the customer table and the cust_id in the sales_order table each contain a customer ID. The customer table contains one row for each customer and, therefore, has a unique value in the id column in each row. The sales_order table contains one row for each transaction a customer has made.
Using join indexes 2307,103,103,’Niedringhaus’ Warning! If the one-to-many relationship is incorrect, the join cannot be synchronized until you remove the extra rows from the “one” table. If you try to synchronize, you get a Duplicate Row error, and the transaction rolls back. When you create a join index, you use ANSI FULL OUTER join syntax. Adaptive Server IQ stores the index as a full outer join.
CHAPTER 4 Adaptive Server IQ Indexes Of course, this approach requires more disk space to build an additional join index and more index building time (not to mention increased maintenance). In the case of a subset join index, the additional join index repeats a subset of the information already in the full join index. You must decide whether the query speed or disk space usage of your application is more important for this particular join relationship.
Using join indexes The index remains unavailable until all steps are complete. However, you can adjust the order of some steps, depending on the needs of your site: • You can combine steps 1 and 2 by defining relationships when you create the table. • You can load the data either before or after you create the join index. If you load the data into the underlying column indexes after you create the join index, you must perform the synchronization step.
CHAPTER 4 Adaptive Server IQ Indexes Performance hints for synchronization Synchronization can be time-consuming. To improve performance, try these suggestions: • Schedule synchronization during off-peak hours. • Synchronize join indexes individually rather than all at once. • Synchronize after executing an entire set of insertions and deletions.
Using join indexes Using foreign references Adaptive Server IQ uses foreign keys to define the relationships among columns that will be used in join indexes, and to optimize queries. However, Adaptive Server IQ does not enforce foreign key constraints. For this reason, when you specify a primary key-foreign key relationship, you must include the UNENFORCED keyword. Adaptive Server IQ does not support key join indexes based on multicolumn foreign keys.
CHAPTER 4 Adaptive Server IQ Indexes Alternatively, you could create the table without the REFERENCES clause, and then add the foreign key later, as is done in the following ALTER TABLE statement: ALTER TABLE DBA.sales_order ADD FOREIGN KEY ky_so_customer (cust_id) REFERENCES DBA.customer (id) UNENFORCED Specifying the join type when creating a join index The join type is always FULL OUTER, the keyword OUTER being optional.
Using join indexes • Use parentheses to control the order in which the join relationships are evaluated. Parentheses control evaluation order just as they do in mathematics, that is, innermost pairs are evaluated first. With this method you start with the top table in the outermost set of parentheses, then any intermediate levels, and include the lowest two levels in the innermost parentheses.
CHAPTER 4 Adaptive Server IQ Indexes search-condition: [ ( ] search-expression [ AND search-expression ] [ ) ] Example 1: Key join • The join-clause can be expressed either with or without parentheses. • The ON clause can reference only two tables. One must be the current one, and the other can be any one table in the current join tree. • All join predicates must be equijoins; that is, the search_expression must indicate that the value in column_1 equals the value in column_2.
Using join indexes A natural join between the id columns of sales_order and sales_order_items makes more sense. In this case, the columns with the same name should contain matching values. The command to create a join index based on a natural join between these two tables is: CREATE JOIN INDEX sales_order_so_items_join FOR sales_order NATURAL FULL OUTER JOIN sales_order_items Creating a join index in Sybase Central To create a join index in Sybase Central, follow these steps.
CHAPTER 4 Adaptive Server IQ Indexes Linear joins You can think of a linear join as a tree with no branches. Each table in the hierarchy is related to the table above it, until you reach the top table. In Figure 4-1 on page 153. Tables A, D, and F constitute a linear join hierarchy. Tables C, E, and F form another linear join hierarchy.
Using join indexes Example In the sample database used throughout this book, the sales_order table contains three foreign keys, each of which is related to the primary key of another table. You can create this table using the following commands: CREATE TABLE "DBA"."sales_order"( ( "id" integer NOT NULL, "cust_id" integer NOT NULL REFERENCES "DBA"."customer" ("id") UNENFORCED, "order_date" datetime NOT NULL, "fin_code_id" char(2) NULL REFERENCES "DBA".
CHAPTER 4 Adaptive Server IQ Indexes SELECT sales_rep FROM sales_order WHERE sales_rep = 299 the results show 20 rows with 299 in the sales_rep column. However, if you enter: SELECT emp_id FROM employee WHERE emp_id = 299 the results show only one row with 299 in the emp_id column. Note Query optimizations for star joins rely on the underlying primary key- foreign key relationships.
Using join indexes Adaptive Server IQ automatically applies the changes to the join index at the same time as it changes the base table. You do not need to synchronize the join index after any ALTER TABLE on nonjoined columns. Other restrictions on ALTER TABLE for join indexes include the following: • You cannot rename a column into or out of a NATURAL join condition. • You cannot add a column that would participate in a previously specified NATURAL join.
CHAPTER 4 Adaptive Server IQ Indexes Table versioning controls access to join indexes Any table is only available for write use to a single user at any given time. For join indexes, this means that when one user is updating any table in a join index, no one else can update any of the tables in that index. All the joined tables remain unavailable until the first user’s transaction is committed and you have synchronized the tables with the SYNCHRONIZE command.
Estimating the size of a join index These calculations should give you an idea of how much disk space you need for the join index. The results include the segment size in bytes, and the number of blocks. The procedure also tells you how long it will take to create the join index. If you want to know the actual size of an existing join index, you use a different stored procedure, sp_iqjoinindexsize. See the Adaptive Server IQ Reference Manual for syntax details of all stored procedures.
CH A PTE R About this chapter 5 Moving Data In and Out of Databases This chapter describes several methods of moving data into and out of your database, and explains when you should use each of them. It also discusses conversion issues for data inserted from other types of databases. Import and export overview Adaptive Server IQ lets you import data from flat files, or directly from database tables. You can also enter specified values directly into the database.
Import and export overview • To remove specific rows from a table, use the DELETE statement. • To change existing rows in a table, you can also use the UPDATE statement. From DBISQL you can export data to another database in a variety of formats, or produce a text file as output. See the next section for a list of formats and how to select them. You can also redirect the output of any command. Input and output data formats The LOAD TABLE statement imports text files with one row per line.
CHAPTER 5 Moving Data In and Out of Databases Specifying an output format for Interactive SQL You can set the DBISQL output format in three ways: • Select Command → Options from the DBISQL menu bar, and then choose an Output Format from the dropdown list. To make this the default output format, click Permanent. • Specify the DBISQL option, OUTPUT_FORMAT, to set the default output format. For syntax details see the Adaptive Server IQ Reference Manual.
Exporting data from a database Exporting data from a database This section tells how to export data from an Adaptive Server IQ database. Note To export IQ data from your database in this version of Adaptive Server IQ, Sybase recommends that you use the method described in this chapter. You may also export data by using a front end tool, written by you or a third party, that effectively queries the IQ database and formats the data as desired.
CHAPTER 5 Redirecting output and messages Moving Data In and Out of Databases The >& redirection symbol redirects all output including error messages and statistics for the command on which it appears. For example: SELECT * FROM employee >& empfile Do not enclose the file name in quotation marks. This example outputs the SELECT statement to the file empfile, followed by the output from the SELECT statement and some statistics pertaining to the command.
Bulk loading data using the LOAD TABLE statement Using command files to load data To load large amounts of data, most users create command files. To create a command file, follow the instructions in the chapter entitled “Getting Started with DBISQL” in the Introduction to Adaptive Server IQ. Transaction processing and LOAD TABLE When you issue the LOAD TABLE statement for an IQ table, a savepoint occurs automatically before the data is loaded.
CHAPTER 5 Moving Data In and Out of Databases For each column, you can specify a column-spec. If you omit this option, the format information in the load-options applies to this column. The columnspec and load-options format information tell Adaptive Server IQ what type of data to expect, and how to convert it into a compatible data format if necessary.
Bulk loading data using the LOAD TABLE statement • DATE, TIME, DATETIME or TIMESTAMP string as ASCII characters. You must define the input-date-format or input-datetime-format of the string using one of the corresponding formats for the date and datetime data types supported by Adaptive Server IQ. For information about these, see the Adaptive Server IQ Reference Manual. Note The column-spec is for IQ tables only. If you specify a column-spec for a Catalog Store table, you get an error.
CHAPTER 5 • Moving Data In and Out of Databases If a backslash (\) precedes the characters n, x, or \ it is considered an escape character. For this reason, to indicate directory paths in Windows NT systems, you must represent the backslash character by two backslashes if the next character is any of those listed. (It is always safe to double the backslashes.) Therefore, the statement to load data from the file c:\newinput.dat into the employee table is: LOAD TABLE employee FROM ’c:\\newinput.dat’ ...
Bulk loading data using the LOAD TABLE statement With STRIP turned on (the default), trailing blanks are stripped from values before they are inserted. This is effective only for VARCHAR data. To turn the STRIP option off, enter the clause as follows: STRIP option ...STRIP OFF ... Trailing blanks are stripped only for non-quoted strings. Quoted strings retain their trailing blanks.
CHAPTER 5 Moving Data In and Out of Databases You can specify load options in any order. Syntax for load-options is as follows: [ { BLOCK FACTOR number | BLOCK SIZE number } ] ... [ BYTE ORDER { NATIVE | HIGH | LOW } ] ... [ LIMIT number-of-rows ] ... [ NOTIFY number-of-rows ] ... [ ON FILE ERROR { ROLLBACK | FINISH | CONTINUE} ] ... [ PREVIEW { ON | OFF } ] ... [ ROW DELIMITED BY ’delimiter-string’ ] ... [ SKIP number-of-rows ] ... [ START ROW ID number ] ...
Bulk loading data using the LOAD TABLE statement Only one single-byte character can be used as an escape character. Note Because you must specify ESCAPES OFF in this version of Adaptive Server IQ, the ESCAPE CHARACTER option has no effect. It is provided for compatibility with Adaptive Server Anywhere. WITH CHECKPOINT ON clause If this option is set to ON, a checkpoint is issued when the LOAD TABLE statement completes and is logged.
CHAPTER 5 Moving Data In and Out of Databases LOAD TABLE nn (l_orderkey, l_quantity ASCII(PREFIX 2), FILLER(2), FROM ’C:\\iq\archive\\mill.txt’ BYTE ORDER LOW Specifies the maximum number of rows to insert into the table. The default is 0 for no limit. LIMIT option LIMIT works together with the SKIP option. SKIP indicates where to begin reading from the input file, and LIMIT specifies how many of those rows to insert. SKIP takes precedence over LIMIT.
Bulk loading data using the LOAD TABLE statement • ROLLBACK aborts the entire transaction (the default). • FINISH finishes the insertions already completed and ends the load operation. • CONTINUE returns an error but only skips the file to continue the load operation. You cannot use this option with partial-width inserts. PREVIEW option Displays the layout of input into the destination table including starting position, name, and data type of each column.
CHAPTER 5 Moving Data In and Out of Databases You cannot use this option if any input fields contain binary data. With this option, a row terminator causes any missing fields to be set to NULL. All rows must have the same row delimiters, and it must be distinct from all column delimiters. The row and field delimiter strings cannot be an initial subset of each other.
Bulk loading data using the LOAD TABLE statement Specifies the id number of a row in the table where insertions should begin. This option is used for partial-width insertions, which insert into a subset of the columns in the table. If you are inserting data into an existing row, you must define the format of each input column with a column-spec, and use START ROW ID to identify the row where you want to insert it.
CHAPTER 5 Moving Data In and Out of Databases Interpreting notification messages By default, Adaptive Server IQ displays information about your database during insert and load operations. The statistics in these messages indicate when you need to perform maintenance and optimization tasks, such as adding more dbspaces. The messages also report on the progress of the load. This section explains each notification message.
Bulk loading data using the LOAD TABLE statement Table 5-2: Memory messages Item Mem: # mb M# mb Description Current memory being used by this Adaptive Server IQ server, in megabytes. The maximum number of megabytes used by this IQ server since it was started. Main IQ Store blocks messages This line describes the permanent (main) IQ Store: Main Blks: U63137/6%, Buffers: U12578/L7 Table 5-3: Main blocks Item Description U# #% Number of blocks in use. Percentage of database filled.
CHAPTER 5 Moving Data In and Out of Databases Table 5-4: Main IQ Store file message Item Main: I: L# Description Number of logical file reads. P# O: D# Number of physical file reads. Number of times a buffer was destroyed. P# C: D# Number of physical writes. Buffer manager data compression ratio. This is the total number of bytes eligible for compression minus number of bytes used after compression divided by total number of bytes eligible for compression times 100.
Using the INSERT statement Using the INSERT statement The INSERT statement allows you to insert data without first putting it into a flat file. Using this command, you can either: • Insert a specified set of values row by row • Insert directly from database tables See the sections that follow for details of these two forms of the command. Inserting specified values row by row To add specified values to a table row by row, use the INSERT statement with this syntax: INSERT [ INTO ] [ owner.
CHAPTER 5 • Moving Data In and Out of Databases For DATE, TIME, and TIMESTAMP or DATETIME columns, you must use a specific format. See “Converting data on insertion” for information on data type conversions. See the Adaptive Server IQ Reference Manual for a complete description of Adaptive Server IQ data types. Note The TIMESTAMP and DATETIME data types are identical.
Inserting selected rows from the database SKIP number-of-rows START ROW ID number This form of the INSERT statement lets you insert any number of rows of data, based on the results of a general SELECT statement. For maximum efficiency, insert as many rows as possible in one INSERT statement. To insert additional sets of rows after the first insert, use additional INSERT statements.
CHAPTER 5 Inserting directly from an Adaptive Server Enterprise database Moving Data In and Out of Databases • Tables in either the IQ Store or the Catalog Store of the database you are currently connected to. • Tables in an Adaptive Server Enterprise database. • A proxy table in your current database, that corresponds to a table in a database on a remote server. Adaptive Server IQ's remote data access capabilities are currently supported on Windows NT only.
Inserting selected rows from the database LOCATION ’detroit.asiq11db’ { SELECT l_shipdate, l_orderkey FROM lineitem } Example • The destination and source columns may have different names. • The order in which you specify the columns is important, because data from the first source column named is inserted into the first target column named, and so on. • You can use the predicates of the SELECT statement within the INSERT command to insert data from only certain rows in the table.
CHAPTER 5 Moving Data In and Out of Databases Importing data interactively If you are inserting small quantities of data, you may prefer to enter it interactively through DBISQL, using the INSERT statement For example, you can insert listed values a single row at a time with the following command: INSERT INTO T1 VALUES ( ... ) For more information about the INSERT command, see “Using the INSERT statement”.
Inserting into primary and foreign key columns • The second user's transaction begins before the first user's transaction commits. • The second user tries to update after the first user's transaction commits, but before the join index is synchronized. This problem occurs because Adaptive Server IQ makes a new version of the join index when any of its underlying tables is updated. The new version is not visible to other transactions that have already begun.
CHAPTER 5 Moving Data In and Out of Databases An easy way to enforce the integrity is to create and run stored procedures that roll back any transaction that violates a constraint. You can use an EXISTS clause to specify violations. Partial-width insertions By default, new rows are inserted wherever there is space in the indexes, and each LOAD TABLE or INSERT statement starts a new row. This approach works as long as the data you are inserting is a new row.
Partial-width insertions Use START ROW ID to specify at which row you want to start the insert. This allows you to insert into some of the columns in a row with one partial-width INSERT or LOAD TABLE statement, and insert into the other columns in the same row with additional INSERT or LOAD TABLE statements. If you try to insert into a column that already contains data, you get an error. You must be sure to control the row at which each insertion starts.
CHAPTER 5 2 Moving Data In and Out of Databases For the second and any subsequent partial-width inserts for the same set of rows, use the START ROW ID option to specify the row where the insert started. This number is the record number at the beginning of the insert message log, as in this example: In table ’department’, the full width insert of 3 columns will begin at record 1.
Partial-width insertions NULL NULL NULL 190 215 29 200 59 3 4 5 NULL NULL NULL NULL NULL 28 13 9 NULL NULL NULL NULL NULL NULL NULL NULL 19 2127 1376 119 4 3 4 5 6 7 8 9 10 (10 rows affected) Example 2 The following example shows the correct way to do this operation. Note the START ROW ID option in the second LOAD TABLE statement. LOAD TABLE lineitem (l_partkey ASCII(4), l_suppkey ASCII(4), FILLER(13)) FROM ’/d1/MILL1/tt.
CHAPTER 5 29 200 59 3 4 5 28 13 9 Moving Data In and Out of Databases 1376 119 4 3 4 5 (5 rows affected) To ensure that the data from the second two columns is inserted into the same rows as the first two columns, you must specify the row number in the START ROW ID option on the INSERT command for the next two columns. Using the FILLER Option The FILLER option tells Adaptive Server IQ which columns in the input file to skip.
Converting data on insertion 213 24 NULL NULL 190 215 152.00 172.00 1 2 (2 rows affected) If you later load data from another file into ps_partkey and ps_availqty, insertions begin correctly at the next row, as shown below. LOAD TABLE partsupp (ps_partkey ASCII(6), ps_availqty ASCII(6), FILLER(2)) FROM ’C:\\iq\\archive\\mill2.txt’ ps_partkey ---------213 24 28 211 SELECT *, rowid(partsupp) FROM ps_suppkey ps_availqty ps_value --------------------------NULL 190 152.00 NULL 215 172.
CHAPTER 5 Moving Data In and Out of Databases • When you insert data selected from another database • When you load data from a flat file When an explicit conversion is needed, the way that you specify the conversion depends on whether you are loading from a flat file or inserting selected rows: • In the LOAD TABLE statement, you convert data explicitly by specifying a format in the column-spec.
Converting data on insertion You can use a synonym interchangeably with its standard data type. Data is stored internally as the standard data type, where synonyms exist. In error messages, the standard name appears in place of the synonym. Note By default, Adaptive Server IQ assumes that input data is binary (numeric data) and tries to insert it that way. However, this presumes that the input column length in bytes must match the destination column length in bytes.
CHAPTER 5 Option Adaptive Server IQ Datatypes ASCII CHAR, VARCHAR DATE DATE DATETIME TIMESTAMP (or DATETIME) or TIME TIME TIME NULL all Moving Data In and Out of Databases Action By default, Adaptive Server IQ assumes same column width between source and destination columns, which may cause it to read input file incorrectly. This option lets you specify a different width for the input column. Converts ASCII date input of a fixed format to binary.
Converting data on insertion The first table shows implicit (I), explicit (E), and unsupported (U) conversions when there is no WHERE clause in the SELECT statement, or when the WHERE clause is based on a comparison operation (=, > or <).
CHAPTER 5 Moving Data In and Out of Databases The second table shows implicit (I), explicit (E), and unsupported (U) conversions when the WHERE clause in a SELECT statement is based on an arithmetic operation (+, –, etc.). Figure 5-3: IQ conversions for arithmetic operations Note In arithmetic operations, bit data is implicitly converted to tinyint.
Converting data on insertion Column width issues Adaptive Server IQ assumes the width of the input data is the same as the destination column width and reads the input file accordingly. If they are not the same width, Adaptive Server IQ may read too few or too many bytes of the input file for that column.
CHAPTER 5 Truncation of data for VARCHAR and CHAR columns Moving Data In and Out of Databases If the width of the input column is greater than the width of the destination column, Adaptive Server IQ truncates the data upon insertion. If the width of the input data is less than the width of the destination column, for CHAR or VARCHAR data types Adaptive Server IQ pads the data with spaces in the table upon insertion.
Converting data on insertion Substitution of NULL or blank characters Adaptive Server IQ supports zero-length CHAR and VARCHAR data. If the length of a CHAR or VARCHAR cell is zero and the cell is not NULL, you get a zero-length cell. For all other data types, if the length of the cell is zero, Adaptive Server IQ inserts a NULL. This treatment of zero-length character data is ANSI behavior. If you require non-ANSI behavior, see the Non_Ansi_Null_Varchar option in the Adaptive Server IQ Reference Manual.
CHAPTER 5 Moving Data In and Out of Databases Option Meaning mm or MM Represents number of month. Always use leading zeros for number of the month where appropriate, for example ’05’ for May. If you omit the month from a DATE value, the day is treated as a Julian date. If you enter only the month, for example, ’03’, Adaptive Server IQ applies the default year and day and converts it to ’1900-03-01’. Represents number of day. Default day is 01.
Converting data on insertion • Use any character other than Y, M, J, or D to indicate the separator character you want Adaptive Server IQ to skip over. You can even use blanks. • If a DATE format includes only a year and a day number within the year, Adaptive Server IQ treats the date as a Julian date. For example, 1998-33 is the 33rd day in the year 1998, or February 2, 1998.
CHAPTER 5 Moving Data In and Out of Databases l_quantity ASCII(4), l_shipdate DATETIME(’MM/DD/YY hh:mm:ss’), FILLER(1)) FROM ’/d1/MILL1/tt.t’ BLOCK FACTOR 1000 PREVIEW ON In this UNIX example, the FILLER(1) clause prevents Adaptive Server IQ from inserting a NULL in the next column (VWAP) after the DATETIME column: LOAD TABLE snapquote_stats_base SYMBOL ‘\x09’, snaptime DATETIME('MM/DD/YY hh:mm:ss'), FILLER(1)) VWAP ‘\x09’, RS_DAY ‘\x09’, FROM '/d1/MILL1/tt.
Converting data on insertion The length of the format string is the width of the input column. Table 5-7 describes the date formatting options. The following table describes the time formatting options. Table 5-9: Formatting times Option hh HH nn ss[.ssssss] aa pp hh hh:nn or hh:mm Meaning Represents hour. Hour is based on 24-hour clock. Always use leading zeros for hour where appropriate, for example ’01’ for 1 am. ’00’ is also valid value for hour of 12 am. Represents minute.
CHAPTER 5 Moving Data In and Out of Databases • The input data can include up to nine positions for seconds, including a floating decimal point, to allow for fractional seconds. On input and query, the decimal point floats, so you can specify up to six decimal positions. However, Adaptive Server IQ always stores only six decimal positions with two positions for whole seconds (ss.ssssss). Any more decimal positions are not permitted. • Separators are used between the time elements.
Other factors affecting the display of data • ZEROS indicates that binary zeros convert to NULLS. • literal indicates that all occurrences of the specified literal convert to NULLS. The specified literal must match exactly, including leading and/or trailing blanks, with the value in the input file, for Adaptive Server IQ to recognize it as a match. You can list up to 20 literal values. You may need to use additional conversion options on the same column.
CHAPTER 5 • Moving Data In and Out of Databases When an arithmetic operation results in a higher scale than the predetermined scale, Adaptive Server IQ truncates the results to fit the scale after it has been determined using the rules defined in the Adaptive Server IQ Reference Manual. If your results require rounding of the values instead of truncation, you should use the ROUND function in your command. However, for inserts, the ROUND function can only be part of its query expression.
Matching Adaptive Server Enterprise data types Adaptive Server Enterprise data type equivalents The table below indicates the Adaptive Server Enterprise exact numeric types and the Adaptive Server IQ equivalents. Table 5-11: Integer data types Adaptive Server Enterprise Datatype int Adaptive Server IQ Datatype INT,BIGINT,UNSIGNED INT, UNSIGNED BIGINT, or NUMERIC Notes Adaptive Server IQ does not allow scaled integers, such as INT(7,3).
CHAPTER 5 Adaptive Server Enterprise Datatype Adaptive Server IQ Datatype double precision DOUBLE real REAL Moving Data In and Out of Databases Notes The following table indicates the Adaptive Server Enterprise character data types and the Adaptive Server IQ equivalents.
Matching Adaptive Server Enterprise data types Table 5-15: DATE/TIME data types Adaptive Server Enterprise Datatype Adaptive Server IQ Datatype datetime TIMESTAMP or DATE or TIME Notes Adaptive Server Enterprise datetime columns maintain date and time of day values in 4 bytes for number of days before or after base date of virtual date 0/0/0000 and 8 bytes for time of day, accurate to within one 1,000,000th of a second.
CHAPTER 5 Moving Data In and Out of Databases For information on setting DBISQL database options, see “SET OPTION statement” in the Adaptive Server IQ Reference Manual. Tuning bulk loading of data Loading large volumes of data into a database can take a long time and use a lot of disk space. There are a few things you can do to save time. Improving load performance during database definition The way you define your database, tables, and indexes can have a dramatic impact on load performance.
Tuning bulk loading of data Adding dbspaces If you run out of space while loading data, Adaptive Server IQ prompts you to create another dbspace, and then continues the operation after you add the dbspace. To avoid this delay, make sure that you have enough room for all of the data you are loading before you start the load operation. Use the sp_estspace or sp_iqestdbspaces stored procedure to help you estimate the space you need for the database and its dbspaces.
CHAPTER 5 Moving Data In and Out of Databases • Increase the size of the database cache. Providing enough memory for the load is a key performance factor. Use the SET OPTION command to adjust MAIN_CACHE_MEMORY_MB and TEMP_CACHE_MEMORY_MB. For these options to take effect, you must ensure that no users are using the database where you set the option, and then disconnect from the database. You can then reconnect and allow other users to connect.
Changing data using UPDATE The Append_Load option applies to LOAD, INSERT...SELECT, and INSERT...VALUES statements. For more information on versioning see Chapter 8, “Transactions and Versioning”. Changing data using UPDATE You can use the UPDATE statement, followed by the name of the table or view, to change single rows, groups of rows, or all rows in a table. As in all data modification statements, you can change the data in only one table or view at a time.
CHAPTER 5 Moving Data In and Out of Databases The search condition need not refer to the column being updated. The company ID for Newton Entertainments is 109. As the ID value is the primary key for the table, you could be sure of updating the correct row using the following statement: UPDATE customer SET company_name = ’Einstein, Inc.’ WHERE id = 109 The SET clause The SET clause specifies the columns to be updated, and their new values. The WHERE clause determines the row or rows to be updated.
Importing data by replication Space for deletions When you use the DELETE statement, you may need to add space to your database, due to the way Adaptive Server IQ stores versions of data pages. For details, see “Overlapping versions and deletions”. When you use DROP TABLE or TRUNCATE TABLE, you do not need to add space, as no extra version pages are needed.
CHAPTER 5 Moving Data In and Out of Databases For information on using Distribution Director and on setting up an appropriate Replication Server environment, see the Distribution Director User’s Guide.
Importing data by replication 228
CH A PTE R About this chapter 6 Using Procedures and Batches This chapter explains how you create procedures and batches for use with Adaptive Server IQ. Procedures store procedural SQL statements in the database for use by all applications. They enhance the security, efficiency, and standardization of databases. User-defined functions are one kind of procedure that return a value to the calling environment for use in queries and other SQL statements.
Benefits of procedures Benefits of procedures Procedures are defined in the database, separate from any one database application. This separation provides a number of advantages. Standardization Procedures allow standardization of any actions that are performed by more than one application program. The action is coded once and stored in the database. The applications need only call the procedure to achieve the desired result.
CHAPTER 6 • Using Procedures and Batches Control who has permission to use procedures This section discusses each of these aspects of using procedures, and also describes some of the different uses of procedures. Creating procedures Procedures are created using the CREATE PROCEDURE statement. You must have RESOURCE authority in order to create a procedure.
Introduction to procedures The body of a procedure is a compound statement. The compound statement starts with a BEGIN statement and concludes with an END statement. In the case of new_dept, the compound statement is a single INSERT bracketed by BEGIN and END statements. For more information, see “Using compound statements” on page 240 Parameters to procedures are marked as one of IN, OUT, or INOUT. All parameters to the new_dept procedure are IN parameters, as they are not changed by the procedure.
CHAPTER 6 Using Procedures and Batches Permissions to execute procedures A procedure is owned by the user who created it, and that user can execute it without permission. Permission to execute it can be granted to other users using the GRANT EXECUTE command.
Introduction to procedures 3 Create a variable to hold the procedure output. In this case, the output variable is numeric, with three decimal places, so create a variable as follows: CREATE VARIABLE Average NUMERIC(20,3) 4 Call the procedure, using the created variable to hold the result: CALL AverageSalary(Average) The DBISQL statistics window displays the message "Procedure completed" if the procedure was created and run properly. Look at the value of the output variable Average.
CHAPTER 6 Employee ID Salary 243 247 72995.000 48023.690 Using Procedures and Batches To execute a CALL of a procedure that returns a result set, DBISQL opens a cursor. The cursor is left open after the CALL in case a second result set is returned. The DBISQL statistics window displays the plan of the SELECT query in the procedure and then displays the line: Procedure is executing. Use RESUME to continue.
Introduction to user-defined functions If you are using a tool other than DBISQL or Sybase Central, you may need to change the command delimiter away from the semicolon before entering the CREATE FUNCTION statement. CREATE FUNCTION fullname (firstname CHAR(30), lastname CHAR(30)) RETURNS CHAR(61) BEGIN DECLARE name CHAR(61); SET name = firstname || ’ ’ || lastname; RETURN ( name ); END For a complete description of the CREATE FUNCTION syntax, see Adaptive Server IQ Reference Manual.
CHAPTER 6 Using Procedures and Batches SELECT fullname (’Jane’, ’Smith’); fullname (’Jane’,’Smith’) Jane Smith The fullname function can be used by any user who has been granted EXECUTE permission for the function. Dropping user-defined functions Once a user-defined function is created, it remains in the database until it is explicitly removed. Only the owner of the function or a user with DBA authority can drop a function from the database.
Introduction to batches Introduction to batches A simple batch consists of a set of SQL statements, separated by semicolons. For example, the following set of statements form a batch that adds a new sales representative to the Eastern Sales department, and adds two sales orders for that sales rep.
CHAPTER 6 Using Procedures and Batches DBISQL and batches A list of semicolon-separated statements, such as the above, is parsed by DBISQL before it is sent to the server. In this case, DBISQL sends each statement individually to the server, not as a batch. Unless you have such parsing code in your application, the statements would be sent and treated as a batch. Putting a BEGIN and END around a set of statements causes DBISQL to treat them as a batch.
Control statements Control statement Syntax Compound statements BEGIN [ ATOMIC ] statement-list END Conditional execution: IF IF condition THEN statement-list ELSEIF condition THEN statement-list ELSE statement-list END IF Conditional execution: CASE CASE expression WHEN value THEN statement-list WHEN value THEN statement-list ELSE statement-list END CASE Repetition: WHILE, LOOP WHILE condition LOOP statement-list END LOOP Repetition: FOR cursor loop FOR statement-list END FOR Break: LEAVE LEA
CHAPTER 6 Using Procedures and Batches A command delimiter is required after every statement in a statement list except for the last, where it is optional. Declarations in compound statements Local declarations in a compound statement immediately follow the BEGIN keyword. These local declarations exist only within the compound statement.
Control statements FROM customer WHERE nationality(id) = ’CDN’ Declarations of cursors and exceptions are discussed in later sections. Atomic compound statements An atomic statement is a statement that is executed completely or not at all. For example, a LOAD statement that inserts thousands of rows might encounter an error after many rows. If the statement does not complete, and the default ON FILE ERROR ROLLBACK option is in effect, all changes are undone. This LOAD statement is atomic.
CHAPTER 6 Using Procedures and Batches The structure of procedures The body of a procedure consists of a compound statement as discussed in “Using compound statements” on page 240. A compound statement consists of a BEGIN and an END, enclosing a set of SQL statements. The statements must be separated by semicolons. The SQL statements that can occur in procedures are described in “SQL statements allowed in procedures” on page 243.
The structure of procedures Declaring parameters for procedures Procedure parameters, or arguments, are specified as a list in the CREATE PROCEDURE statement. Parameter names must conform to the rules for other database identifiers such as column names. They must be a valid data types (see “SQL Data Types” in Adaptive Server IQ Reference Manual), and must be prefixed with one of the keywords IN, OUT or INOUT.
CHAPTER 6 Using Procedures and Batches Passing parameters to procedures You can take advantage of default values of stored procedure parameters with either of two forms of the CALL statement. If the optional parameters are at the end of the argument list in the CREATE PROCEDURE statement, they may be omitted from the CALL statement. As an example, consider a procedure with three INOUT parameters: CREATE PROCEDURE SampleProc( INOUT var1 INT DEFAULT 1, INOUT var2 int DEFAULT 2, INOUT var3 int DEFAULT 3 ) ..
Returning results from procedures Name Fran Whitney Matthew Cobb Philip Chin Julie Jordan Robert Breault ... Notes • Default parameters can be used in calling functions. However, parameters cannot be passed to functions by name. • Parameters are passed by value, not by reference. Even if the function changes the value of the parameter, this change is not returned to the calling environment. • Output parameters cannot be used in user-defined functions.
CHAPTER 6 Using Procedures and Batches CREATE VARIABLE returnval INTEGER ; returnval = CALL myproc() ; Returning results as procedure parameters Procedures can return results to the calling environment in the parameters to the procedure. Within a procedure, parameters and variables can be assigned values in one of the following ways: Using the SET statement • The parameter can be assigned a value using the SET statement.
Returning results from procedures When a SELECT statement is executed, the server retrieves the results of the SELECT statement and places the results in the variables. If the query results contain more than one row, the server returns an error. For queries returning more than one row, cursors must be used. For information about returning more than one row from a procedure, see “Returning result sets from procedures”. If the query results in no rows being selected, a row not found warning is returned.
CHAPTER 6 Using Procedures and Batches Returning result sets from procedures If a procedure returns more than one row of results to the calling environment, it does so using result sets. The following procedure returns a list of customers who have placed orders, together with the total value of the orders placed. The procedure does not list customers who have not placed orders.
Returning results from procedures • When testing this procedure, DBISQL opens a cursor to handle the results. The cursor is left open following the SELECT statement, in case the procedure returns more than one result set. You should type RESUME to complete the procedure and close the cursor. Returning multiple result sets from procedures A procedure can return more than one result set to the calling environment.
CHAPTER 6 Using Procedures and Batches If you are not using this feature of variable result sets, it is recommended that you employ a RESULT clause, for performance reasons and to allow front-end tools to discern the columns and data types the procedure will produce without executing it.
Using cursors in procedures Cursor management overview Managing a cursor is similar to managing a file in a programming language. The following steps are used to manage cursors: 1 Declare a cursor for a particular SELECT statement or procedure using the DECLARE statement. 2 Open the cursor using the OPEN statement. 3 Use the FETCH statement to retrieve results one row at a time from the cursor.
CHAPTER 6 Using Procedures and Batches Using cursors on SELECT statements in procedures The following procedure uses a cursor on a SELECT statement. It illustrates several features of the stored procedure language. It is based on the same query used in the ListCustomerValue procedure described in “Returning result sets from procedures”. CREATE PROCEDURE TopCustomerValue ( OUT TopCompany CHAR(36), OUT TopValue INT ) BEGIN -- 1.
Using cursors in procedures -- 7. Close the cursor CLOSE ThisCompany; END Notes The TopCustomerValue procedure has the following notable features: • The "error not found" exception is declared. This exception is used later in the procedure to signal when a loop over the results of a query has completed. For more information about exceptions, see “Errors and warnings in procedures”. • Two local variables ThisName and ThisValue are declared to hold the results from each row of the query.
CHAPTER 6 Using Procedures and Batches AS ThisValue FROM customer INNER JOIN sales_order INNER JOIN sales_order_items INNER JOIN product GROUP BY ThisName DO IF ThisValue > TopValue THEN SET TopCompany = ThisName; SET TopValue = ThisValue; END IF; END FOR CompanyLoop; END Errors and warnings in procedures After an application program executes a SQL statement, it can examine a return code.
Errors and warnings in procedures Default error handling in procedures This section describes how Adaptive Server IQ handles errors that occur during a procedure execution, if you have no error handling built in to the procedure. If you want to have different behavior from that described in this section, you can use exception handlers, described in “Using exception handlers in procedures” on page 261.
CHAPTER 6 Notes Using Procedures and Batches • The DECLARE statement in InnerProc declares a symbolic name for one of the predefined SQLSTATE values associated with error conditions already known to the server. The DECLARE statement does not take any other action. • The MESSAGE statement sends a message to the server window and the dbconsol message window. • The SIGNAL statement generates an error condition from within the InnerProc procedure.
Errors and warnings in procedures Error handling with ON EXCEPTION RESUME If the ON EXCEPTION RESUME clause is included in the CREATE PROCEDURE statement, the procedure checks the following statement when an error occurs. If the statement handles the error, then the procedure does not return control to the calling environment when an error occurs. Instead, it continues executing, resuming at the statement after the one causing the error.
CHAPTER 6 Using Procedures and Batches CREATE PROCEDURE OuterProc() ON EXCEPTION RESUME BEGIN DECLARE res CHAR(5); MESSAGE ’Hello from OuterProc.’; CALL InnerProc(); SELECT @res=SQLSTATE; IF @res=’52003’ THEN MESSAGE ’SQLSTATE set to ’, res, ’ in OuterProc.’; END IF END; CREATE PROCEDURE InnerProc() ON EXCEPTION RESUME BEGIN DECLARE column_not_found EXCEPTION FOR SQLSTATE ’52003’; MESSAGE ’Hello from InnerProc.’; SIGNAL column_not_found; MESSAGE ’SQLSTATE set to ’, SQLSTATE, ’ in InnerProc.
Errors and warnings in procedures Default handling of warnings in procedures Warnings are handled differently from errors. While the default action for errors is to set a value for the SQLSTATE and SQLCODE variables, and return control to the calling environment, the default action for warnings is to set the SQLSTATE and SQLCODE values and continue execution of the procedure. Drop the procedures Remember to drop both the InnerProc and OuterProc procedures before continuing with the tutorial.
CHAPTER 6 Using Procedures and Batches The procedures both continued executing after the warning was generated, with SQLSTATE set by the warning (02000). Using exception handlers in procedures It is often desirable to intercept certain types of errors and handle them within a procedure, rather than pass the error back to the calling environment. This is done through the use of an exception handler.
Errors and warnings in procedures MESSAGE ’Column not found handling.’; WHEN OTHERS THEN RESIGNAL ; END The EXCEPTION statement declares the exception handler itself. The lines following the EXCEPTION statement are not executed unless an error occurs. Each WHEN clause specifies an exception name (declared with a DECLARE statement) and the statement or statements to be executed in the event of that exception.
CHAPTER 6 • Exception handling and atomic compound statements Using Procedures and Batches If the error handling code for the column not found exception is simply a RESIGNAL statement, control is passed back to the OuterProc procedure with SQLSTATE still set at the value 52003. This is just as if there were no error handling code in InnerProc. As there is no error handling code in OuterProc, the procedure fails.
Using the EXECUTE IMMEDIATE statement in procedures END MESSAGE ’Outer compound statement’; END The following statement executes the InnerProc procedure: CALL InnerProc(); The message window of the server then displays the following: Hello from InnerProc Column not found handling Outer compound statement When the SIGNAL statement that causes the error is encountered, control passes to the exception handler for the compound statement, and the Column not found handling message is printed.
CHAPTER 6 Using Procedures and Batches In ATOMIC compound statements, you cannot use an EXECUTE IMMEDIATE statement that causes a COMMIT, as COMMITs are not allowed in that context. Transactions and savepoints in procedures SQL statements in a procedure are part of the current transaction (see Chapter 8, “Transactions and Versioning”). You can call several procedures within one transaction or have several transactions in one procedure.
Some tips for writing procedures Remember to delimit statements within your procedure You should terminate each statement within the procedure with a semicolon. Although you can leave off semicolons for the last statement in a statement list, it is good practice to use semicolons after each statement. The CREATE PROCEDURE statement itself contains both the RESULT specification and the compound statement that forms its body. No semicolon is needed after the BEGIN or END keywords, or after the RESULT clause.
CHAPTER 6 Using Procedures and Batches Verifying procedure input arguments You can verify that input arguments to a procedure are passed correctly in several ways. You can display the value of the parameter on the message window of the server using the MESSAGE statement. For example, the following procedure simply displays the value of the input parameter var: CREATE PROCEDURE message_test (IN var char(40)) BEGIN MESSAGE var; END You can do the following from Interactive SQL: Create the procedure.
Calling external libraries from procedures Using SELECT statements in batches You can include one or more SELECT statements in a batch. Multiple SELECT statements are allowed by Interactive SQL only if they return the same number of columns and each column has the same data type.
CHAPTER 6 Using Procedures and Batches This section describes how to use the external library calls in procedures. Warning! External libraries can corrupt your database. External libraries called from procedures share the memory of the server. If you call a DLL from a procedure and the DLL contains memory-handling errors, you can crash the server or corrupt your database. Ensure that your libraries are thoroughly tested before deploying them on production databases.
Calling external libraries from procedures In these statements, function_name is the name of a function in the dynamic link library, and library.dll is the name of the library. The arguments in the procedure argument list must correspond in type and order to the arguments for the library function; they are passed to the external DLL function in the order in which they are listed. Any value returned by the external function is in turn returned by the procedure to the calling environment.
CHAPTER 6 Using Procedures and Batches How parameters are passed to the external function SQL data types are mapped to their C equivalents as follows: SQL data type INTEGER C data type long (32 bits) SMALLINT REAL short float CHAR( n ) char * These are the only SQL data types you can use: using others produces an error. Procedure parameters that are INOUT or OUT parameters are passed to the external function by reference.
Calling external libraries from procedures Special considerations when passing character types For the character data type (CHAR), Adaptive Server IQ allocates a 255-byte buffer (including one for the null terminator) for each parameter. If the parameter is an INOUT parameter, the existing value is copied into the buffer and null terminated, and a pointer to this buffer is passed to the external function.
CH A PTE R About this chapter 7 Ensuring Data Integrity This chapter describes facilities for ensuring that the data in your database is valid and reliable. These facilities include constraints on tables and columns, and choosing appropriate data types.
Data integrity overview Integrity constraints belong in the database To help ensure that the data in a database are valid, you need to formulate checks that define valid and invalid data and design rules to which data must adhere. The rules to which data must conform are often called business rules. The collective name for checks and rules is constraints.
CHAPTER 7 Ensuring Data Integrity See “Creating tables” for more information on how constraints affect optimization. For more on join indexes and foreign keys, see “Using join indexes”. Constraints and Load Operations Adaptive Server IQ checks during load operations that certain constraints are obeyed: • Adaptive Server IQ ensures that data being loaded is the appropriate data type and length.
Data integrity overview These and other table and column constraints are discussed in “Using table and column constraints”. Column constraints can be inherited from user-defined data types. Entity and referential integrity The information in relational database tables is tied together by the relations between tables. These relations are defined by the primary keys and foreign keys built into the database design.
CHAPTER 7 Ensuring Data Integrity Using table and column constraints The CREATE TABLE statement and ALTER TABLE statement can specify many different attributes for a table. Along with the basic table structure (number, name and data type of columns, name and location of the table), you can specify other features that allow control over data integrity. Warning! Altering tables can interfere with other users of the database.
Using table and column constraints Using IQ UNIQUE constraint on columns The IQ UNIQUE constraint specifies an estimate of the number of distinct values in a column. You can apply the IQ UNIQUE constraint to any column in a table. This constraint helps optimize loading of indexes. For example, in the state column of the employee table, you would specify IQ UNIQUE(50) to indicate that there are only 50 possible values (assuming U.S. states only). Each of the possible values can occur many times.
CHAPTER 7 Ensuring Data Integrity By default, string comparisons are case insensitive unless the database is explicitly created as a case-sensitive database, using the CASE RESPECT option. Example 3 You can specify that a date or number falls in a particular range.
Using table and column constraints Working with column constraints in Sybase Central All adding, altering, and deleting of column constraints in Sybase Central is carried out in the Constraints tab of the column properties sheet. ❖ To display the property sheet for a column: 1 Connect to the database. 2 Click the Tables folder for that database, and click the table holding the column you wish to change.
CHAPTER 7 • Ensuring Data Integrity You can replace a CHECK condition on a column in the same way as you would add a CHECK condition. The following statement adds or replaces an unenforced CHECK condition on the phone column of the customer table: ALTER TABLE customer MODIFY phone CHECK ( phone LIKE ’___-___-____’ ) UNENFORCED There are two ways of modifying a CHECK condition defined on the table, as opposed to a CHECK condition defined on a column.
Declaring entity and referential integrity Enforcing entity integrity When a new row in a table is created, or when a row is updated, the database server ensures that the primary key for the table is still valid: that each row in the table is uniquely identified by the primary key. Note Adaptive Server IQ enforces single-column primary keys only. No action is taken for invalid multi-column primary keys.
CHAPTER 7 Ensuring Data Integrity It is up to the application programmer to decide how to present this information to the user and enable the user to take appropriate action. The appropriate action in this case is usually just to provide a unique value for the primary key.
Declaring entity and referential integrity Example 1 The sample database contains an employee table and a department table. The primary key for the employee table is the employee ID, and the primary key for the department table is the department ID. One of the items of information about each employee is the department ID of the department to which they belong.
CHAPTER 7 Ensuring Data Integrity Integrity rules in the system tables All the information about integrity checks and rules in a database is held in the following system tables and views: System table Description SYS.SYSTABLE CHECK constraints are held in the view_def column of SYS.SYSTABLE. For views, the view_def holds the CREATE VIEW command that created the view. You can check whether a particular table is a base table or a view by looking at the table_type column, which is BASE or VIEW.
Integrity rules in the system tables 286
CH A PTE R About this chapter 8 Transactions and Versioning This chapter describes Adaptive Server IQ’s approach to transaction processing, called snapshot versioning, and its implications for performance and other aspects of database administration. Overview of transactions and versioning Adaptive Server IQ uses transaction processing to allow many users to read from the database while it is being updated. Transaction processing ensures that logically related commands are executed as a unit.
Overview of transactions and versioning Transactions are atomic. In other words, Adaptive Server IQ executes all the statements within a transaction as a unit. At the end of each transaction, changes can be committed to make them permanent. If for any reason all the commands in the transaction do not process properly, then some or all of the intermediate changes can be undone, or rolled back. The user application controls the conditions under which changes are committed or rolled back.
CHAPTER 8 Transactions and Versioning • A ROLLBACK statement undoes all the changes made by the transaction. • A disconnection from a database causes an implicit rollback (the default) or commit, depending on whether the DBISQL option COMMIT_ON_EXIT is set. • A statement with a side effect of an automatic commit is executed. Database definition commands, such as ALTER, CREATE, and DROP all have the side effect of an automatic commit.
Overview of transactions and versioning Subdividing transactions You can identify important states within a transaction and return to them selectively or cause other actions to occur by using savepoints. Savepoints are discussed further in “Savepoints within transactions”. Introduction to concurrency Adaptive Server IQ can execute more than one transaction at the same time. The term concurrency refers to this ability.
CHAPTER 8 Transactions and Versioning Concurrency and IQ Multiplex IQ Multiplex extends Adaptive Server IQ to allow concurrent processing of read transactions on multiple Adaptive Server IQ servers. IQ Multiplex extends snapshot versioning to maintain the consistency of the database across multiple servers. For more information about using IQ Multiplex, see Adaptive Server IQ Multiplex User’s Guide.
Overview of transactions and versioning Unlike a camera, though, IQ does not need to make a copy of the entire object each time the image changes. Instead, it copies only the parts of the image— the database pages—that have changed. Database pages that have not changed are shared among all active versions in the database. IQ takes its snapshot when a transaction begins. Throughout the transaction, a user who reads from the object sees the unchanged image, or snapshot version.
CHAPTER 8 Transactions and Versioning Figure 8-1: Only one writer at a time Meanwhile, other users can read from the customer table at any time. In this way queries can proceed while the database administrator inserts and deletes table data. In Figure 8-2, User 3 and User 4 are able to query the customer table while User 1’s write transaction remains open.
Overview of transactions and versioning Figure 8-2: One writer, multiple readers Multiple writers and readers in a database Within an IQ database that is not multiplex, multiple read-only and read/write users can operate concurrently, as long as the writers are inserting data into (or deleting it from) different tables. So, for example, while User 1’s transaction is inserting and deleting in the customer table, User 2 can begin a transaction that loads data into the employee table, as shown in Figure 8-3.
CHAPTER 8 Transactions and Versioning Figure 8-3: Concurrent insertions to different tables Data definition operations on a single table lock out all other readers and writers from that table. See “Locks for DDL operations” for details. Transactions use committed data Committed data results when a write transaction commits. Every transaction uses the latest committed version of the database as of the time the transaction begins. It uses that version until the transaction commits.
Overview of transactions and versioning In other words, every transaction begins with a snapshot of the data in a reliable state. The snapshot of the data that you see when you issue a query does not change, even if another user is updating the table you are reading. For example, in Figure 8-4, when User 1’s write transaction begins, it uses the customer table version that was committed most recently. User 2’s transaction begins after User 1 has begun writing, but before User 1 commits.
CHAPTER 8 Transactions and Versioning • If a user's read transaction commits before a concurrent write transaction does, and that user begins a new read transaction, the version remains the same. • If a read transaction commits after a concurrent write transaction does, any new transaction, whether read-only or read/write, uses a new version. Figure 8-4 on page 296 is an example of the first instance.
Overview of transactions and versioning How Adaptive Server IQ keeps track of versions Adaptive Server IQ assigns a version identifier to each database object that exists in the metadata, and that has a life span beyond a single command. IQ uses these version identifiers to ensure that writes to any database object are always based on the latest version of the object. It keeps each active version of a database object on disk.
CHAPTER 8 Transactions and Versioning Versioning prevents inconsistencies Without versioning, concurrent read and write operations could cause inconsistencies in the database. The table-level versioning provided by Adaptive Server IQ prevents inconsistencies both by serializing transactions, and by making the table the version level.
How locking works • Read transactions do not block write transactions. • Write transactions do not block read transactions. • A single update user and multiple read-only users can concurrently access a table. • Only a single user can update the data in a given table at one time. The first transaction to open a table in write mode gains access to the table. A second transaction that tries to open the table in write mode receives an error.
CHAPTER 8 Transactions and Versioning Cannot perform DDL command now as a DDL command is already in progress. If a CREATE DBSPACE or DROP DBSPACE command is in progress, and a user explicitly issues a CHECKPOINT command, the checkpoint fails with the message: Run time SQL Error If a CHECKPOINT command is in progress, a user who issues a CREATE DBSPACE or DROP DBSPACE command gets the following message: Cannot perform requested command as there is a CHECKPOINT command in progress.
Isolation levels GRANT, REVOKE, and SET OPTION are not restricted While the commands GRANT, REVOKE, and SET OPTION are also considered DDL operations, they cause no concurrency conflicts, and so are not restricted. GRANT and REVOKE always cause an automatic commit; SET OPTION causes an automatic commit except when it is specified as TEMPORARY. GRANT and REVOKE are not allowed for any user currently connected to the database.
CHAPTER 8 Transactions and Versioning The level of isolation that Adaptive Server IQ provides prevents several types of inconsistencies. The ones most commonly encountered are listed here: • Dirty Reads Transaction A modifies an object, but does not commit or roll back the change. Transaction B reads the modified object. Then Transaction A further changes the object before performing a COMMIT. In this situation, Transaction B has seen the object in a state that was never committed.
Checkpoints, savepoints, and transaction rollback Adaptive Server IQ relies on three transaction-related commands that help you recover a stable set of data in the event of system or media failure. These commands set checkpoints, set and release savepoints, and roll back transactions. Checkpoints A checkpoint marks a significant point in a transaction, when Adaptive Server IQ writes to disk certain information it tracks internally. It uses this information in the event you need to recover your database.
CHAPTER 8 • At the start and end of the backup process. • When the database server is shut down. Transactions and Versioning The CHECKPOINT_TIME is the maximum time that can pass between checkpoints. It is set by default at 60 minutes. You can adjust the checkpoint interval with the SET OPTION command; see the Adaptive Server IQ Reference Manual for details. You probably do not need to adjust the checkpoint time or issue explicit checkpoints, however.
Checkpoints, savepoints, and transaction rollback Rolling back to a savepoint You can undo all changes after a savepoint by issuing a ROLLBACK TO SAVEPOINT. This command rolls back to the savepoint you specify, or to the most recent SAVEPOINT if you do not specify a named savepoint. Rolling back to savepoint n undoes all actions for all savepoints greater than or equal to n. Normally, locks are released only at the end of a transaction.
CHAPTER 8 Transactions and Versioning Rolling back transactions When you roll back a transaction, you undo all of the operations in that transaction. We say that you are rolling back the database, since you are returning the database to an earlier state. What causes a rollback Rollbacks can occur either due to an explicit user request, or automatically. You use a ROLLBACK statement to undo any changes to the database since the last COMMIT or ROLLBACK.
Checkpoints, savepoints, and transaction rollback During Adaptive Server IQ database recovery, any uncommitted transactions are rolled back, and any disk space used for old versions is returned to the pool of available space. At this point, the database contains only the most recently committed version of each permanent table. During recovery from a system failure, Adaptive Server IQ reopens all connections that were active at the time of the failure.
CHAPTER 8 • Transactions and Versioning Additional information about checkpoints that occurred during a transaction. When you need to recover your database, instead of repeating all of the lengthy transactions that have occurred, Adaptive Server IQ restores quickly from the information in the transaction log and the checkpoint information. It uses the information about versions and free space to roll back transactions, and to release the disk space occupied by obsolete versions.
Performance implications • Disk space can sometimes become an issue. Storing overlapping versions has the potential to use a lot of disk space, depending on the number and size of versions in use simultaneously. Metadata and database page versions are retained until they are dropped, either at a RELEASE SAVEPOINT or when the last transaction that can see a given version commits or rolls back. The space is then reclaimed. Delays due to locking are minimal.
CHAPTER 8 Transactions and Versioning Out of disk space After you add space, the deletion resumes. When the delete transaction commits, the space becomes available for other deletions or insertions. If you do not need normally that much space in your database, you can drop the dbspace to regain the extra disk space for other purposes. Be sure you do so before inserting any data, so that you do not lose any data that Adaptive Server IQ might put in the new dbspace.
Cursors in transactions The rows in a cursor, like those in a table, have no order associated with them. The FETCH command steps through the rows, but the order may appear random and can even be inconsistent. For this reason, you will want to impose an order by appending an ORDER BY phrase to your SELECT statement. Cursors and versioning When you use cursors, Adaptive Server IQ needs to be able to manage multiple versions within a single transaction.
CHAPTER 8 Transactions and Versioning Hold cursors Specifying the HOLD option when you open a cursor keeps the cursor open past the end of the transaction, if the transaction ends in a COMMIT. A hold cursor does not remain open across a ROLLBACK in which a cursor is opened. Although the HOLD option is not commonly used in a DSS environment, with long transactions and no positioned updates, it may prove useful in some situations.
Cursors in transactions 314
CH A PTE R About this chapter 9 International Languages and Character Sets This chapter describes how to configure your Adaptive Server IQ installation to handle international language issues. Introduction to international languages and character sets This section provides an introduction to the issues you may face when working in an environment that uses more than one character set, or when using languages other than English.
Introduction to international languages and character sets • Conditions using the LIKE keyword. IQ indexes that hold character data are created based on the database collation. The database also uses collations to identify valid or unique identifiers (column names and so on). • You can set up Adaptive Server IQ to convert data between the character set encoding on your server and client systems, thus maintaining the integrity of your data even in mixed character set environments.
CHAPTER 9 International Languages and Character Sets To answer the question... Consider reading...
Understanding character sets in software • Database servers, which sort characters (for example, list names alphabetically), use a collation. A collation is a combination of a character encoding (a map between characters and hexadecimal numbers) and a sort order for the characters. There may be more than one sort order for each character set; for example, a case-sensitive order and a case-insensitive order, or two languages may sort characters in a different order.
CHAPTER 9 • International Languages and Character Sets The client operating system has text displayed on its interface, and may also process text. Operating system For a satisfactory working environment, all these sources of text must work together. Loosely speaking, they must all be working in the user’s language and/or character set. Code pages in Windows and Windows NT Many languages have few enough characters to be represented in a single-byte character set.
Understanding character sets in software Remember that the code page used by the client system determines both the values that are sent to server for each character you enter, and the characters that are displayed when particular values are sent to the client from the server. The code page used by the server system determines how the server interprets values the client sends.
CHAPTER 9 International Languages and Character Sets Multibyte character sets Some languages, such as Japanese and Chinese, have many more than 256 characters. These characters cannot all be represented using a single byte, but can be represented in multibyte character sets. In addition, some character sets use the much larger number of characters available in a multibyte representation to represent characters from many languages in a single, more comprehensive, character set.
Understanding character sets in software Sorting characters using collations The database collation sequence includes the notion of alphabetic ordering of letters, and extends it to include all characters in the character set, including digits and space characters. Associating more than one character with each sort position More than one character can be associated with each sort position. This is useful if you wish, for example, to treat an accented character the same as the character without an accent.
CHAPTER 9 International Languages and Character Sets The equivalence of upper and lower case characters is enforced in the collation. There are some collations where particular care may be needed when assuming case insensitivity of identifiers. Example In the Turkish 857TRK collation, the lower case i does not have the character I as its upper case equivalent.
Understanding locales • Which language to request from the database. For more information, see “Understanding the locale language” on page 324. • Character set The character set is the code page in use. The client and server both have character set values, and they may differ. If they differ, character set translation may be required to enable interoperability. For machines that use both OEM and ANSI code pages, the ANSI code page is the value used here.
CHAPTER 9 Language label values International Languages and Character Sets The following table shows the valid language label values, together with the equivalent ISO 639 labels: Language label Alternative label ISO_639 language code us_english french english N/A EN FR german spanish N/A N/A DE ES japanese korean N/A N/A JA KO portuguese chinese portugue simpchin PT ZH italian tchinese N/A tradchin IT TW polish norwegian N/A norweg PL NO swedish danish N/A N/A SV DA Understanding
Understanding locales For more information, see “Setting the SQLLOCALE environment variable” on page 328. Open Client applications check the locales.dat file in the Sybase locales directory is used. 3 Character set labels 326 Character set information from the operating system is used to determine the locale: • On Windows operating systems, use the GetACP system call. This returns the ANSI character set, not the OEM character set. • On UNIX, default to ISO8859-1.
CHAPTER 9 International Languages and Character Sets Character set label macgrk2 IANA label Description Macintosh Greek macturk greek8 Macintosh Turkish HP Greek-8 turkish8 koi8 HP Turkish-8 KOI-8 Cyrillic tis620 big5 TIS-620 Thai standard Traditional Chinese (cf. CP950) eucksc EUC KSC Korean encoding (cf.
Understanding collations Understanding the locale collation label Each database has its own collation. The database server determines the collation label as follows: 1 It checks the SQLLOCALE environment variable, if it exists. For more information, see “Setting the SQLLOCALE environment variable” on page 328. 2 Collation label values It uses an internal table to find a collation label corresponding to the language and character set.
CHAPTER 9 International Languages and Character Sets • The collation (ASA_Label) • Case sensitivity (Case) • Blank padding (Blank Padding) if it was specified when the database was created.
Understanding collations 330 Collation label 819SVE Type ANSI Description Code Page 819, Swedish/Finnish 819TRK 850CYR ANSI OEM Code Page 819, Turkish Code Page 850, Cyrillic, Western 850DAN 850ELL OEM OEM Code Page 850, Danish Code Page 850, Greek 850ESP 850ISL OEM OEM Code Page 850, Spanish Code Page 850, Icelandic 850LATIN1 850LATIN2 OEM OEM Code Page 850, Latin 1 Code Page 850, Latin 2, Central/Eastern European 850NOR 850RUS OEM OEM Code Page 850, Norwegian Code Page 850, Russian 850
CHAPTER 9 International Languages and Character Sets Collation label 1250LATIN2 Type ANSI 1250POL ANSI Description Code Page 1250, Windows Latin 2, Central/Eastern European Code Page 1250, Windows Latin 2, Polish 1252LATIN1 SJIS ANSI Multibyte Code Page 1252, Windows Latin 1, Western Japanese Shift-JIS Encoding SJIS2 Multibyte EUC_JAPAN Multibyte EUC_CHINA Multibyte Japanese Shift-JIS Encoding, Sybase Adaptive Server Enterprise-compatible Japanese EUC JIS X 0208-1990 and JIS X 0212-1990 Enco
Understanding collations • When setting up data sources for Windows-based ODBC applications, do choose the Adaptive Server Anywhere or Adaptive Server IQ translation driver in the ODBC data source configuration. The translation driver converts between the OEM code page on your machine and the ANSI code page used by Windows. If the database collation is a different OEM code page than the one on your machine, an incorrect translation will be applied.
CHAPTER 9 The ISO1LATIN1 collation International Languages and Character Sets • The upper case and lower case Icelandic Eth (\xD0 and \xF0) is sorted with D in Adaptive Server IQ and Adaptive Server Anywhere, but after all other letters in Adaptive Server Enterprise. • The upper case and lower case Icelandic Thorn (\xD0 and \xF0) is sorted with T in Adaptive Server IQ and Adaptive Server Anywhere, but after all other letters in Adaptive Server Enterprise.
Understanding collations Notes on OEM collations The following table shows the built-in collations that correspond to OEM code pages. The table and the corresponding collations were derived from several manuals from IBM concerning National Language Support, subject to the restrictions mentioned above. (This table represents the best information available at the time of writing. Due to continuing rapid geopolitical changes, the table may contain names for countries that no longer exist.
CHAPTER 9 International Languages and Character Sets Country Language Primary Code Page Primary Collation Secondary Code Page Secondary Collation New Zealand Norway English 437 437LATIN1 850 850LATIN1 Norwegian 865 865NOR 850 850NOR Peru Poland Spanish Polish 850 852 850ESP 852LATIN2 437 850 437ESP 850LATIN2 Portugal Romania Portuguese Romanian 850 852 850LATIN1 852LATIN2 860 850 860LATIN1 850LATIN2 Russia S.
Understanding character set translation Using multibyte collations This section describes how multibyte character sets are handled. The description applies to the supported collations and to any multibyte custom collations you may create. Adaptive Server IQ provides collations using several multibyte character sets. For a complete listing, see “Understanding collations” on page 328 Understanding collations. Adaptive Server IQ supports variable-width character sets.
CHAPTER 9 International Languages and Character Sets Client application users may see messages from the database as well as data from the database. Some database messages, which are strings from the language library, may include placeholders that are filled by characters from the database. For example, if you execute a query with a column that does not exist, the returned error messages is: Column column-name not found where column-name is filled in from the database.
Understanding character set translation Connection strings and character sets Connection strings present a special case for character set translation. The connection string is parsed by the client library, in order to locate or start a database server. This parsing is done with no knowledge of the server character set or language. The interface library parses the connection string as follows: 1 It is broken down into its keyword = value components.
CHAPTER 9 • International Languages and Character Sets If the code page on your client machine operating system matches that used in the database, no character set translation is needed for data in the database. For example, in many environments it is appropriate to use the 1252LATIN1 collation in your database, which corresponds to the Windows NT code page in many single-byte environments.
Collation internals Collation files may include the following elements: • Comment lines, which are ignored by the database. • A title line. • A collation sequence section. • An Encodings section. • A Properties section. Comment lines In the collation file, spaces are generally ignored. Comment lines start with either the percent sign (%) or two dashes (--).
CHAPTER 9 International Languages and Character Sets The collation sequence section After the title line, each non-comment line describes one position in the collation. The ordering of the lines determines the sort ordering used by the database, and determines the result of comparisons. Characters on lines appearing higher in the file (closer to the beginning) sort before characters that appear later. The form of each line in the sequence is: [sort-position] : character [ [, character ] ...
Collation internals : : : : : : : : : : % : : : : % % : : Other syntax notes ’ ’ _ \xF2 \xEE \xF0 ’,’ ; ’:’ ! Sort some letters in alphabetical order A a A a a A B b B b b B Sort some E’s from code page 850, including some accented extended characters: e e E, \x82 \x82 \x90, \x8A \x8A \xD4 E e E, \x90 \x82 \x90, \xD4 \x8A \xD4 For databases using case-insensitive sorting and comparison (that is, CASE IGNORE was specified when the database was created), the lowercase and uppercase mappings are used to fin
CHAPTER 9 International Languages and Character Sets The Encodings section lists which characters are lead-bytes, for multi-byte character sets, and what are valid follow-bytes. For example, the Shift-JIS Encodings section is as follows: Encodings: [\x00-\x80,\xa0-\xdf,\xf0-\xff] [\x81-\x9f,\xe0-\xef][\x40-\x7e,\x80-\xfc] The first line following the section title lists valid single-byte characters. The square brackets enclose a comma-separated list of ranges.
International language and character set tasks International language and character set tasks This section groups together the tasks associated with international language and character set issues. Finding the default collation If you do not explicitly specify a collation when creating a database, a default collation is used. For IQ databases, the default collation is always ISO_BINENG.
CHAPTER 9 International Languages and Character Sets For more information, see “Creating a database with a named collation” on page 346. When choosing the collation for your database, • Choose a collation that uses a character set and sort order appropriate for the data in the database. It is often the case that there are several alternative collations that meet this requirement, including some that are OEM collations and some that are ANSI collations.
International language and character set tasks SELECT PROPERTY( ’DefaultCollation’ ) The query returns one of the collations listed in “Supplied collations” on page 329. Notes To obtain client locale information, connect to a database server running on your current machine.
CHAPTER 9 ❖ International Languages and Character Sets To specify a database collation when creating a database (Sybase Central): • You can use the Create Database wizard in Sybase Central to create a database. The wizard has a Collation Sequence page where you choose a collation from a list. You can also see the name of your current collation in Sybase Central. Rightclick on the database whose collation you need. In the dropdown menu select Properties, and then click the Extended Information tab.
International language and character set tasks CREATE DATABASE ’mydb.db’ COLLATION ’819ELL’ IQ SIZE 100 IQ PATH ’myiq.iq’ Starting a database server using character set translation Character set translation takes place if the client and server locales are different, but only if you specifically turn on character set conversion on the database server command line. ❖ To enable character-set translation on a database server: • Start the database server using the -ct command-line option.
CHAPTER 9 ❖ International Languages and Character Sets To use the ODBC translation driver: 1 In the ODBC Administrator, choose Add to create a new Adaptive Server IQ data source or Configure to edit an existing Adaptive Server IQ data source. 2 On the ODBC tab of the ODBC Configuration for Adaptive Server IQ window, click Select and choose Adaptive Server Anywhere 6.0 Translator from the list of translation drivers.
International language and character set tasks You do this using the Collation utility. The output is a collation file. For example, the following statement extracts the 1252LATIN1 collation into a file named mycol.col: dbcollat -z 1252LATIN1 mycol.col 3 Edit the custom collation file. Open the collation file (in this case mycol.col) in a text editor. 4 Change the name of the collation. The name of the collation is specified on a line near the top of the file, starting with Collation.
CHAPTER 9 International Languages and Character Sets Stop and restart the database server in order for it to recognize the new collations and insert them into system tables SYSCOLLATION and SYSCOLLATIONMAPPINGS. Creating a database with a custom collation If none of the supplied collations meet your needs, you can create a database using a custom collation. The custom collation is used in indexes and any string comparisons. ❖ To create a database with a custom collation: 1 Create a custom collation.
Performance issues With these options, uppercase characters precede all lowercase characters in the collation sequence. For example, ’XYZ’ sorts before ’abc’ with these options, just as it did in older versions of Adaptive Server IQ. Performance issues Performance for character data is better with a binary character set and collation sequence than with a non-binary one.
CH A PTE R 1 0 About this chapter Managing User IDs and Permissions Each user of a database must be assigned a unique user ID: the name they type when connecting to the database. This chapter describes how to manage user IDs. An overview of database permissions Proper management of user IDs and permissions is essential in a data warehouse. It allows users to carry out their jobs effectively, while maintaining the security and privacy of appropriate information within the database.
An overview of database permissions DBA authority overview When a database is created, a single usable user ID is created. This first user ID is DBA and the password is initially set to SQL. The DBA user ID is automatically given DBA permissions, also called DBA authority, within the database. This level of permission enables the DBA user ID to carry out any activity in the database: create tables, change table structures, create new user IDs, revoke permissions from users, and so on.
CHAPTER 10 Managing User IDs and Permissions RESOURCE authority overview RESOURCE authority is the permission to create database objects, such as tables, views, and stored procedures. Resource authority may be granted only by the DBA to other users. Ownership permissions overview The creator of a database object becomes the owner of that object. Ownership of a database object carries with it permissions to carry out actions on that object.
Managing individual user IDs and permissions Permission Description UPDATE Permission to update rows in a table or view. This may be granted on a set of columns in a table only ALL All the above permissions Group permissions overview Setting permissions individually for each user of a database can be a timeconsuming and error-prone process. For most databases, permission management based on groups, rather than on individual user IDs, is a much more efficient approach.
CHAPTER 10 Managing User IDs and Permissions Creating new users A new user is added to a database by the DBA using the GRANT CONNECT statement. For example: ❖ To add a new user to a database, with user ID M_Haneef and password welcome: 1 From DBISQL, connect to the database as a user with DBA authority. 2 Issue the SQL statement: GRANT CONNECT TO M_Haneef IDENTIFIED BY welcome Only the DBA has the authority to add new users to a database.
Managing individual user IDs and permissions GRANT CONNECT TO userid IDENTIFIED BY password The same command can also be used to add a new user. For this reason, if you inadvertently enter the user ID of an existing user when you mean to add a new user, you are actually changing the password of the existing user. You do not receive a warning because this behavior is considered normal. This behavior differs from pre-Version 12 Adaptive Server IQ.
CHAPTER 10 Managing User IDs and Permissions • You should give users with DBA authority two user IDs, one with DBA authority and one without, so that they connect as DBA only when necessary. • RESOURCE authority allows the user to create new database objects, such as tables, views, indexes, or procedures. Granting permissions on tables and views You can assign a set of permissions on individual tables and views.
Managing individual user IDs and permissions 1 Connect to the database as a user with DBA authority, or as the owner of sample_table. 2 Type and execute the SQL statement: GRANT UPDATE (column_1, column_2) ON sample_table TO M_Haneef Table and view permissions are limited in that they apply to all the data in a table or view (except for the UPDATE permission which may be restricted).
CHAPTER 10 Managing User IDs and Permissions TO M_Haneef WITH GRANT OPTION Granting permissions on procedures There is only one permission that may be granted on a procedure, and that is the EXECUTE permission to execute (or CALL) the procedure. Permission to execute stored procedures may be granted by the DBA or by the owner of the procedure (the user ID that created the procedure).
Managing individual user IDs and permissions 2 Click the Users and Groups folder, and locate the user you want to grant permissions to. 3 Right-click the user, and select Copy from the popup menu. 4 Locate the procedure you want to allow the user to execute, in the Stored Procedures folder. 5 Click the procedure, and choose Edit→Paste from the main menu to grant permissions. For more information, see the Sybase Central online Help.
CHAPTER 10 Managing User IDs and Permissions sp_helptext sp_name_in_question Managing groups Once you understand how to manage permissions for individual users (as described in the previous section) working with groups is straightforward. A group is identified by a user ID, just like a single user, but this user ID is granted the permission to have members.
Managing groups 3 Give the personnel user ID the permission to have members, with the following SQL statement: GRANT GROUP TO personnel The GROUP permission, which gives the user ID the ability to have members, is not inherited by members of a group. If this were not the case, then every user ID would automatically be a group as a consequence of membership in the special PUBLIC group. Creating groups in Sybase Central ❖ To create a group in Sybase Central: 1 Connect to the database.
CHAPTER 10 Managing User IDs and Permissions 2 Double-click the Users and Groups folder for that database, to open it. Groups are displayed in the left panel, and both users and groups are displayed in the right panel. 3 In the right panel, select the users you want to add to a group, and drag them to the group. For more information, see the Sybase Central online Help. Permissions of groups Permissions may be granted to groups in exactly the same way as to any other user ID.
Managing groups If a table employees is owned by the personnel user ID, and if M_Haneef is a member of the personnel group, then M_Haneef can refer to the employees table simply as employees in SQL statements. Users who are not members of the personnel group need to use the qualified name personnel.employees. Creating a group to own the tables It is advisable that you create a group whose only purpose is to own the tables.
CHAPTER 10 Managing User IDs and Permissions Special groups When a database is created, two groups are also automatically created. These are SYS and PUBLIC. Neither of these groups has passwords, so it is not possible to connect to the database as either SYS or as PUBLIC. The two groups serve important functions in the database.
Database object names and prefixes SELECT * FROM "DBA".employee The employee table reference is said to be qualified. (In this case the owner name is enclosed in double quotes, as DBA is a SQL keyword.) In other circumstances it is sufficient to give the object name. This section describes when you need to use the owner prefix to identify tables, view and procedures, and when you do not.
CHAPTER 10 Managing User IDs and Permissions SELECT * FROM company.customers To rectify the situation, make the Sales group a member of the company group. GRANT GROUP TO company; GRANT MEMBERSHIP IN GROUP company TO Sales; Now Joe and Sally, being members of the Sales group, are indirectly members of the company group, and can reference their tables without qualifiers.
Using views and procedures for extra security For information on how to create views, see “Working with views”. Using views for tailored security Views are computed tables that contain a selection of rows and columns from base tables. Views are useful for security when it is appropriate to give a user access to just one portion of a table. The portion can be defined in terms of rows or in terms of columns.
CHAPTER 10 Managing User IDs and Permissions TO SalesManager Exactly the same command is used to grant permission on a view as to grant permission on a table. Example 2 The next example creates a view which allows the Sales Manager to look at a summary of sales orders. This view requires information from more than one table for its definition: 1 Create the view. CREATE VIEW order_summary AS SELECT order_date, region, sales_rep, company_name FROM "DBA".sales_order KEY JOIN "DBA".
How user permissions are assessed Strict security For strict security, you can disallow all access to the underlying tables, and grant permissions to users or groups of users to execute certain stored procedures. With this approach, the manner in which data in the database can be modified is strictly defined. How user permissions are assessed Groups do introduce complexities in the permissions of individual users.
CHAPTER 10 Managing User IDs and Permissions For example, you may wish to prevent a single connection from taking too much of the available memory or CPU resources, so that one connection does not slow down other users of the database. Adaptive Server IQ provides a set of database options that the DBA can use to control resources. These options are called resource governors.
Users and permissions in the system tables • • Sets the maximum size (in bytes) of that part of the memory that is allocated to Java applications on a per connection basis. JAVA_HEAP_SIZE MAX_CURSOR_COUNT Limits the number of cursors for a connection. • MAX_STATEMENT_COUNT Limits the number of prepared statements for a connection.
CHAPTER 10 Managing User IDs and Permissions Table Default Contents SYSUSERPERM DBA only SYSGROUP PUBLIC SYSTABLEPERM PUBLIC SYSCOLPERM PUBLIC SYSDUMMY PUBLIC SYSPROCPERM PUBLIC Database-level permissions and password for each user ID One row for each member of each group All permissions on table given by the GRANT commands All columns with UPDATE permission given by the GRANT command Dummy table, can be used to find the current user ID Each row holds one user granted permission to use one
Users and permissions in the system tables 376
CH A PTE R About this chapter 11 Backup and Data Recovery This chapter explains how to back up your database, and how to recover data when necessary. It tells you why it is important to perform backups on regular basis, and gives recommendations for scheduling backups. Backup protects your data Adaptive Server IQ provides a full set of features that protect you from two types of computer failure, and from database corruption.
Backing up your database Backing up your database You use the BACKUP command to back up your IQ database. Backup includes both the Adaptive Server IQ data (the IQ Store) and the underlying Adaptive Server Anywhere database (the Catalog Store) Backup runs concurrently with read and write operations in the database. By contrast, during a restore no other operations are allowed on that database. You must be connected to a database in order to back it up.
CHAPTER 11 Backup and Data Recovery A second automatic checkpoint occurs at the end of backup. Any data that is committed while the backup is in progress is included in any subsequent backups. However, if a failure occurs between the first and second checkpoints, any work that occurred after the first checkpoint cannot be restored. Adaptive Server IQ backs up only those database blocks actually in use at the time of backup. Free blocks are not backed up.
Backing up your database Distribution of backup data BACKUP always makes a full backup of the Catalog Store on the first archive device, and then backs up the data from the IQ Store in parallel across all of the devices you specify. Blocks are not distributed evenly across archive media. You may have more on one device than others, depending on the processing speed of individual threads.
CHAPTER 11 • 4 mm DDS • 8 mm Backup and Data Recovery Adaptive Server IQ also allows Stacker drives with multiple tapes. Adaptive Server IQ BACKUP does not support jukeboxes or robotic loaders. If you need them, use a third party media manager. Adaptive Server IQ BACKUP does not support fixed-length tape devices on UNIX systems, like Quarter Inch Cartridge (QIC) drives. Note Tape devices on AIX systems can be configured for either fixed- or variable-length block mode.
Backing up your database Retaining old disk backups BACKUP overwrites existing disk files of the same name. If you need to retain a backup, when you create a new backup either use different file or path names for the archive devices, or move the old backup to another location before starting the backup. Two ways to run BACKUP You can run BACKUP in two ways: • Attended. In attended mode, BACKUP assumes that an operator is present, and prompts you to mount the archive media when necessary.
CHAPTER 11 • Backup and Data Recovery For stacker devices that hold multiple tape drives, all tapes for a given device must be the same size. Sybase recommends that you always start a new tape for every backup. Before starting a backup to disk, Adaptive Server IQ first tests whether there is enough disk file space for the backup. For an operator-attended backup to disk, if there is not enough space, BACKUP prompts you to move some files from the disk before it writes any data.
Backing up your database ...TO ’archive_device’ [ SIZE #_of_KB ][STACKER #_of_drives_in_stack ] ... [ WITH COMMENT ’string’ ] Note If you need to back up an Adaptive Server Anywhere database, see the Adaptive Server IQ Reference Manual for additional options. Specifying operator presence ATTENDED ON or OFF controls whether or not human intervention is expected when new tapes or disk files are needed. The default is ON. For unattended backups to disk, BACKUP does not prompt you to add more disk space.
CHAPTER 11 Backup file names for backup to disk Backup and Data Recovery BACKUP always assigns file names to disk backup files by appending a suffix to the archive_device name you specify. The suffix consists of “.” followed by a number that increases by one for each new file. For example, if you specify /iqback/mondayinc as the archive_device, the backup files are /iqback/mondayinc.1, /iqback/mondayinc.2, and so on.
Backing up your database Specifying tape devices on Windows NT Windows NT systems do not specify rewind or no rewind devices and only support fixed-length I/O operations to tape devices. Adaptive Server IQ requires variable-length devices. It does additional processing to accommodate NT’s fixed-length tape I/O. While Windows NT supports tape partitioning, Adaptive Server IQ does not use it, so do not use another application to format tapes for Adaptive Server IQ backup or restore.
CHAPTER 11 • Backup and Data Recovery For unattended backups with SIZE and STACKER specified, Backup tries to open the next tape. If there are no volumes available, or if you did not specify STACKER, you get an error. Any additional tapes do not contain the header information needed for a restore, so you must be careful to mount tapes in order during the restore or your database could become corrupt.
Backing up your database Other backup options Specifying the block factor BLOCK FACTOR specifies the number of IQ blocks to write to the archive device at one time. It must be greater than 0, or BACKUP returns an error message. BLOCK FACTOR defaults to 25 on UNIX platforms. On Windows NT, the default BLOCK FACTOR is based on the block size of your database. For example, if the block size is 512 bytes, BLOCK FACTOR is 120 blocks. If the block size is 32KB, BLOCK FACTOR is 1 block.
CHAPTER 11 Backup and Data Recovery TO ’/dev/rmt/0n’ TO ’/dev/rmt/1n’ WITH COMMENT ’Jan 18 full backup of asiquser’ The Catalog Store is backed up first, to /dev/rmt/0n. The IQ Store is backed up next, to both tapes.
Backing up your database • If backup fails during either the checkpoint at the start of backup or the checkpoint when backup is complete, it performs normal checkpoint recovery. • If backup fails between checkpoints, it rolls back the backup. • If the system fails at any time between the initial and final checkpoint and you must restore the database, you must do so using an older set of backup tapes or disk files.
CHAPTER 11 Backup and Data Recovery The backup program passes vendor information to the third-party program automatically. When you request a third-party backup, it places this information in the backup header file, and writes the header file on the first tape or disk file actually created for each archive_device you specify. Performing system-level backups The BACKUP command is the most reliable method you can use to back up IQ data.
Performing system-level backups Backing up the right files Required files Optional files You must back up the following files: • SYSTEM dbspace file, typically named dbname.db. • The transaction log file, which is required for system recovery, typically named dbname.log • The IQ_SYSTEM_MAIN dbspace file, typically named dbname.iq • The IQ_SYSTEM_TEMP dbspace file, typically named dbname.
CHAPTER 11 Backup and Data Recovery Ensuring that all files exist Before restoring, review the table of contents of the backup to ensure that all files required for IQ are present. The list of files depends on your application. See the discussion of required and optional files in “Backing up the right files” on page 392. Checking ownership and permissions Ensure that ownership and permission levels do not change during the systemlevel restore.
Validating your database Interpreting results The procedure produces a very long list of statistics about your database. Statistics are listed first for the Main IQ Store, then for Temporary Store. For each store, you see three types of statistics: • Dynamic statistics. These are cumulative counts from the time the database server was started, and vary each time you run the procedure. Examples include buffer manager statistics.
CHAPTER 11 Backup and Data Recovery Figure 11-1: sp_iqcheckdb results Concurrency issues for sp_iqcheckdb When you run sp_iqcheckdb, it reads every database page in use. This procedure consumes most of the database server’s time, so that the I/O is as efficient as possible.
Restoring your databases more slowly than usual. If other users are active when you run sp_iqcheckdb, the results you see reflect only what your transaction sees. If another user is doing inserts and deletes, those blocks appear as unowned in the Main IQ Store. To avoid confusion, you should not allow inserts and deletes while sp_iqcheckdb executes. If another user is running queries, Temporary Store blocks used for the queries appear as unowned, and also affect CountMismatch.
CHAPTER 11 • Backup and Data Recovery For a full restore, the Catalog Store (by default the .db file) and the transaction log (by default the .log file) must not exist in the location you are restoring to. If either of these files exists, you must delete it or move it to a different directory before doing the full restore. When a full restore begins, it destroys all old database files and then recreates them.
Restoring your databases Restoring disk backup files If you back up to disk and then move those files to tape, you must move them back to disk files with the same names as when you created the backup. Adaptive Server IQ cannot restore disk files that are moved to tape directly from tape. When you restore using the Sybase-provided backup and restore, you must specify the same number of archive devices (disk files) for the restore as were used to create the backup.
CHAPTER 11 Backup and Data Recovery Database has changed since the last restore Note Adaptive Server IQ does not let you do an incremental restore if the database has changed since the previous restore. However, it does not prevent users from making changes. It is the responsibility of the DBA or system operator to ensure that no changes are made to the database until all restores are complete. Restoring from a compatible backup RESTORE lets you restore database files for Adaptive Server IQ 12.0 and up.
Restoring your databases Just as for backup, each archive_device specifies the API (Sybase or third party) and, for the Sybase API, the physical tape device or disk file name from which you are restoring. For third-party APIs, the content of the archive_device string depends on your vendor. The archive device must not be a raw disk device. When you restore from disk files using the Sybase API, you must supply the same number of archive devices as were specified when this backup was created.
CHAPTER 11 • Backup and Data Recovery To move any other database file, you use the RENAME option. Note The DBTRAN utility is not supported by Adaptive Server IQ because it regenerates only those parts of the transaction log that are specific to Adaptive Server Anywhere. Example 2 — Moving the Catalog Store This example restores the same database as Example 1. In Example 2, however, you move the Catalog Store file and any database files that were created relative it.
Restoring your databases Relative pathnames in the RENAME clause work as they do when you create a database or dbspace: the main IQ Store dbspace, Temporary Store dbspaces, and Message Log are restored relative to the location of db_file (the Catalog Store); user-created IQ Store dbspaces are restored relative to the directory that holds the main IQ dbspace.
CHAPTER 11 Backup and Data Recovery RENAME IQ_SYSTEM_MSG TO ’asiquser.iqmsg’ RENAME IQ_USER TO ’/dev/rdsk/c1t5d2s1’ Note You could also issue these commands with only the last RENAME clause, since only one dbspace is being restored to a new location. Listing all of the files or raw partitions, as shown here, ensures that you know exactly where each will be restored. Displaying header information The CATALOG ONLY option displays the header information for the database.
Restoring your databases To determine the correct order, you need the information about backup files that is stored in the backup log. See “Getting information about backups and restores” for the content and location of this file. Restore backups as follows: • If your database is corrupt, or if you are moving any files to a new location, you must restore a FULL backup.
CHAPTER 11 Backup and Data Recovery Set B Tapes B1 and B2. These must be restored as a set, after Set A, and either before or after Set C. They can be in either the second or third device. Set C Tapes C1, C2, and C3. These must be restored as a set, after Set A, and either before or after Set B. They can be in either the second or third device. The Restore program checks that tapes within each set are in the correct order on a single device.
Restoring your databases Description The DBLOG command line utility allows you to display or change the name of the transaction log or transaction log mirror associated with a database. You can also stop a database from maintaining a transaction log or mirror, or start maintaining a transaction log or mirror. Transaction log utility options This option sets a filename for a new transaction log mirror. If the database is not currently using a transaction log mirror, it starts using one.
CHAPTER 11 Backup and Data Recovery In an IQ Multiplex, you must restore on the write server in simplex mode, and synchronize the query server following the completion of the restore. For more information on multiplex restores, see Adaptive Server IQ Multiplex User’s Guide. This restriction extends to any incremental restores you may need if your system crashes during recovery.
Restoring your databases For an example of the information you see in a header file, see any RESTORE line in the sample backup log in “Content of the backup log”. A RESTORE with CATALOG ONLY produces the information in the same format as the backup log entry for an actual RESTORE. Recovery from errors during restore If an incremental restore fails early in the operation, the database is still usable (assuming it existed and was not corrupt before the restore).
CHAPTER 11 Backup and Data Recovery When you back up the files and restore them with the CATALOG ONLY option, you don’t see anything telling you that these files were links; in fact, this information is not saved. Adaptive Server IQ saves these files as though they were actually present in the directory where the symbolic links reside. When you do the restore, the files are recreated in the directories or on the raw partitions named by the database name.
Getting information about backups and restores If you are using a third-party backup product, the vendor information string needs to convey any information needed for the backup, such as the specification of devices, size of files, and stacker drives. See your vendor’s documentation for details. Note Adaptive Server IQ does not permit unattended restore. Getting information about backups and restores Adaptive Server IQ provides a backup log, .backup.syb, to help you manage your backup media.
CHAPTER 11 • Backup and Data Recovery On Windows NT, the server tries to place it in the following locations, in this order: • The directory specified by the ASLOGDIR environment variable. • The directory that holds the server executable files.
Getting information about backups and restores 20:07:00.000’, ... DBA, InSF, Arch, A_partial2_yes_sf, ’’ Maintaining the backup log It’s a good idea to clean up the backup log after you purge backup media. Use a text editor to do so. Be careful with your edits: once BACKUP or RESTORE records information in this file, it does not check its accuracy. There is only one backup log on a server. The server must be able to read and write this file.
CHAPTER 11 Backup and Data Recovery Keep the results of this query some place other than the disk where the database resides, so that you will have a complete list of dbspace names if you need them. You can also run the following script in DBISQL. This script produces an output file that contains the set of rename clauses you would use if you did not actually change the location of any files. You can substitute any new file locations, and use the resulting file in your RESTORE statement.
Determining your data backup and recovery strategy Scheduling routine backups Make a full backup of each database just after you create it, to provide a base point, and perform full and incremental backups on a fixed schedule thereafter. It is especially important to back up your database after any large number of changes.
CHAPTER 11 Backup and Data Recovery For example, once you have a full backup of your database, in theory you could perform only incremental backups thereafter. You would not want to do this, however, because any future recovery would be intolerably slow, and would require more tape or disk space than doing a full backup periodically. Remember that other users can have read and write access while you do backups, but no one else can use the database while you are restoring it.
Determining your data backup and recovery strategy Eliminating data verification You can also improve the speed of backup and restore operations by setting CRC OFF in the BACKUP command. This setting deactivates cyclical redundancy checking. With CRC ON, numbers computed on backup are verified during any subsequent restore operation, affecting performance of both commands. The default is CRC ON.
CHAPTER 11 Backup and Data Recovery Controlling the size of the Catalog Store An IQ database consists of an IQ Store and an underlying Catalog Store. BACKUP makes a full backup of the Catalog Store at the start of every backup, both full and incremental. Ordinarily the Catalog Store is quite small, containing only the system tables, metadata, and other information Adaptive Server IQ needs to manage your database. However, it is possible to create nonIQ tables in the Catalog Store.
Determining your data backup and recovery strategy 418
CH A PTE R About this chapter 1 2 Managing System Resources This chapter describes the way Adaptive Server IQ uses memory, disk I/O, and CPUs, and the relationships among these factors. It also explains how the DBA can tune performance by adjusting resource usage. The suggestions in this chapter are generic. You need to adjust them to suit your hardware and software configuration.
Overview of memory use Other considerations, such as hardware and network analysis, can locate bottlenecks in your installation.
CHAPTER 12 Managing System Resources To make the maximum use of your physical memory, Adaptive Server IQ uses buffer caches for all reads and writes to your databases. Utilities to monitor swapping You can use the UNIX vmstat command, the UNIX sar command, or the Windows NT Task Manager, to get statistics on the number of running processes and the number of page-outs and swaps. Use this information to find out if the system is paging excessively. Then make any necessary adjustments.
Overview of memory use Killing processes affects shared memory Warning! Killing processes on UNIX systems may result in semaphores or shared memory being left behind instead of being cleaned up automatically. The correct way to shut down an IQ server on UNIX is the stop_asiq utility, described in “Stopping the database server” on page 43. For information on cleaning up after an abnormal exit, see the chapter “Troubleshooting Hints” in Adaptive Server IQ Troubleshooting and Error Messages Guide.
CHAPTER 12 Managing System Resources Figure 12-1: Buffer caches in relation to physical memory The following sections describe each part in more detail and provide guidelines to help you determine how much memory each part requires. Operating system and other applications This amount will vary for different platforms and how the system is used. For example, UNIX “cooked” file systems do more file buffering than UNIX raw partitions, so the operating system has a higher memory requirement.
Overview of memory use Raw partitions versus file systems For UNIX systems, databases using “cooked” file systems rather than raw partitions may require another 30% of the remaining memory to handle file buffering by the operating system. Windows NT file systems do not have the same overhead as UNIX file systems. However, Windows NT and some UNIX systems may benefit from reserving a significant portion of memory for the file system to better handle I/O operations.
CHAPTER 12 Managing System Resources Adaptive Server IQ main and temp buffer caches After determining how much overhead memory Adaptive Server IQ needs, you must decide how to split what’s left between your main IQ and temp buffer caches. The dashed line dividing the two areas in Figure 12-1 indicates that this split may change from one database to another based on several factors.
Overview of memory use Other considerations Adaptive Server IQ buffer cache sizes may differ from one database to the next based on how you use it. For maximum performance, you need to change the settings between inserting, querying the database, and mixed use. In a mixeduse environment, however, it is not always feasible to require all users to exit the database so that you can reset buffer cache options. In those cases, you may need to favor either load or query performance.
CHAPTER 12 Memory Use Memory remaining for the main and temp buffer caches Main_Cache_Memory_Mb setting: 60% of memory remaining for buffer caches Temp_Cache_Memory_Mb setting: 40% of memory remaining for buffer caches a Amount Used Managing System Resources Memory available using raw partitions Memory available using "cooked" file systems Queries 675 Inserts 828 Queries 397 Inserts 550 405 497 238 330 270 331 159 220 Minimum operating system use for Windows NT is 30MB As shown in the tab
Overview of memory use Method -iqmc and -iqtc server switches When to use it Reset cache sizes when the database is not running. Especially useful if cache sizes are larger than your system can accommodate. Use on some UNIX platforms to provide additional memory for use as buffer caches. -iqsmem and -iqwmem server switches Indirectly affects buffer cache size, by controlling the memory that can be used for loads.
CHAPTER 12 3 4 Managing System Resources Make sure all users are disconnected from the database, and stop the database. • If the -ga command-line switch is set, the database shuts down automatically after the last user disconnects. • If -ga is not set, stop the database, by using the stop_asiq utility on UNIX, or by clicking Shutdown on the database server display on Windows NT. Restart the database. The new cache sizes are now in effect.
Overview of memory use Because the Catalog Store accounts for only a tiny fraction of I/O, the page size for the Catalog Store has no real impact on performance. The default value of 4096 bytes should be adequate. The IQ page size determines two other performance factors, the default I/O transfer block size, and the maximum data compression for your database. These factors are discussed in the sections that follow. Block size All I/O occurs in units of blocks.
CHAPTER 12 Managing System Resources Table 12-3: Default block sizes Page Size (KB) 64 128 256 512 Block Size (bytes) 4096 8192 16384 32768 Data compression Adaptive Server IQ compresses all data when storing it on disk. Data compression both reduces disk space requirements and contributes to performance. The amount of compression is determined automatically, based on the IQ page size. Saving memory If your machine does not have enough memory, to save memory you can try the following adjustments.
Overview of memory use Use the following guideline to determine BLOCK FACTOR: record size * BLOCK FACTOR = memory required You need extra memory for this option, in addition to the memory for the buffers. If you have a lot of memory available, or if no other users are active concurrently, increasing the value of BLOCK FACTOR can improve load performance. Optimizing for large numbers of users Adaptive Server IQ, running on Tru64-UNIX, Solaris/32, and Solaris/64, handles up to 500 user connections.
CHAPTER 12 -c Managing System Resources The Catalog Store buffer cache is also the general memory pool for the Catalog Store (the front end of Adaptive Server IQ). Sybase recommends a 64MB minimum for 500 users. Use of the -iqmt option is not required. If -iqmt is set too low for the number of specified connections, the number of threads will be increased to handle the number of requested connections. That is, -gm overrides -iqmt.
Overview of memory use Within the "proc:" section of this file you must update max-threads-per-user. For example: proc: : . max-threads-per-user = 9000 Since this count is on a per user basis (not a per process basis) this number must be high enough to allow for all threads of all processes running under the Sybase user ID.
CHAPTER 12 Managing System Resources Table 12-4: Total available memory by platform Platform Total memory available CompaqTru64 (Digital UNIX) Effectively unlimited HP UNIX 3GBa IBM RS6000 AIX 2GBa SGI IRIX Effectively unlimited Sun Solaris 32-bit 3.3GB Sun Solaris 64-bit Effectively unlimited Windows NTb 2.5GB aYou must specify the -iqsmem option to get this much memory on HP UNIX and AIX. bYou need Windows NT Enterprise to get this much memory.
Overview of memory use Unwired memory pool On AIX, Tru64 (Digital UNIX), and HP UNIX systems, you can create an unwired (swappable) memory pool to increase total available memory. Unwired memory can be paged. To create the unwired memory pool, use the iqsmem command-line switch. Specify this switch as the number of MB of unwired memory. The maximum value for -iqsmem is platform-specific.
CHAPTER 12 Managing System Resources Managing large buffer caches on HP On HP UNIX, start_asiq sets -iqsmem to 500 by default. This setting allows a total buffer cache size (i.e., main and temp caches combined) of 2GB. If you need more than 2GB for buffer caches, and your system can accommodate a larger value, you must add unwired memory, by specifying -iqsmem. The value you specify on the command line overrides the start_asiq setting.
Overview of memory use • On Solaris systems with more than 4GB of memory, the file system buffer cache competes with IQ's buffer cache less, so you may decrease performance by turning off file system buffering. • On NT systems, under certain loads and configurations, disabling the file system buffer cache can likely improve performance. As of Version 12.4.2, file system buffering is turned off by default for newly created IQ databases.
CHAPTER 12 Managing System Resources The JAVA_NAMESPACE_SIZE option of the SET OPTION command sets the maximum size (in bytes) of that part of the memory that is allocated to Java applications on a per database basis. Per database memory allocations include Java class definitions. As class definitions are effectively read-only, they are shared among connections. Consequently, their allocations come right out of the fixed cache, and this option sets a limit on the size of these allocations.
The process threading model Figure 12-2: Multithreaded architecture Multiple threads improve performance. Even a single-CPU machine gets better performance by using threads. Insufficient threads error When you do not have enough server threads to initiate the query you have issued, you get the error: Not enough server threads available for this query This condition may well be temporary. When some other query finishes, threads are made available and the query may succeed the next time you issue it.
CHAPTER 12 • Managing System Resources To set the maximum number of threads a single user will use, issue the command SET OPTION MAX_IQ_THREADS_PER_CONNECTION. This can be used to control the amount of memory a particular operation consumes. For example, the DBA can set this option before issuing an INSERT or LOAD command. Balancing I/O This section explains the importance of balancing I/O on your system.
Balancing I/O Using disk striping Traditional file management systems allow you to locate individual files on specific disks. Consequently, all file operations occur against a single disk drive. Some operating systems allow you to create logical devices or volumes that span multiple disk drives. Once a file fills the first disk drive, it is automatically continued onto the next drive in the logical volume.
CHAPTER 12 Managing System Resources Recommendations for disk striping Here are some general rules on disk striping: • For maximum performance, the individual disks in a striped file system should be spread out across several disk controllers. But be careful not to saturate a disk controller with too many disks. Typically, most SCSI machines can handle 2–3 disks per controller. See your hardware documentation for more information.
Balancing I/O Disk striping option This section explains how you can use the option Adaptive Server IQ provides to do disk striping, without using third party software. If you already have a disk striping solution through third party software and hardware, you should use it instead. Turning disk striping on or off The syntax you use to turn disk striping on or off is: SET OPTION "PUBLIC".DISK_STRIPING = { ON | OFF } The default is ON for UNIX systems, and OFF for Windows NT systems.
CHAPTER 12 Managing System Resources Figure 12-3: Internal disk striping The example above shows disk drive A has two 2GB partitions (a and b) and two 500MB (or .5GB) partitions (c and d). There are three other 1GB disk drives (E, F, and G). You should create your database on partition a, then add dbspaces for E, c, F, b, G and d. Using multiple dbspaces Using multiple dbspaces allows your IQ and temporary data to be broken down into multiple operating system files or partitions.
Balancing I/O To move or rename the transaction log file, use the Transaction Log utility (DBLOG). For syntax and details, see “The DBLOG command-line utility” on page 405. Warning! The Adaptive Server IQ transaction log file is different than most relational database transaction log files. If for some reason you lose your database files, then you lose your database (unless it's the log file that is lost). However, if you have an appropriate backup, then you can reload the database.
CHAPTER 12 Managing System Resources Figure 12-4: Avoid I/O contention The figure above illustrates how you want to spread access across separate disks to avoid I/O contention. To avoid disk bottlenecks, follow these suggestions: • Keep random disk I/O away from sequential disk I/O. • Isolate IQ database I/O from I/O for proxy tables in other databases, such as Adaptive Server Enterprise.
Options for tuning resource use Ordinarily, as long as you maintain a reasonable percentage of free space in your IQ Store, you will have enough free space. However, for certain deletions, depending on the size of the data and its distribution among database pages, you may need a large amount of working space. In the case where you are deleting a major portion of your database, and the data is distributed sparsely across many pages, you could temporarily double the size of your database.
CHAPTER 12 Managing System Resources Limiting a query’s memory use The QUERY_TEMP_SPACE_LIMIT option of the SET command lets you restrict the amount of memory available to any one query. By default, a query can use 1000MB of memory. When you issue a query, Adaptive Server IQ estimates the temporary space needed to resolve the query.
Options for tuning resource use If scrolling cursors are never used in your application, you should make this a permanent PUBLIC option. It will use less memory and make a big improvement in query performance. Limiting the number of cursors The MAX_CURSOR_COUNT option specifies a resource governor to limit the maximum number of cursors that a connection can use at once. The default is 50. Setting this option to 0 allows an unlimited number of cursors.
CHAPTER 12 Managing System Resources Optimizing for typical usage Adaptive Server IQ tracks the number of open cursors and allocates memory accordingly. In certain circumstances, USER_RESOURCE_RESERVATION option can be set to adjust the minimum number of current cursors that IQ thinks is currently using the product and hence allocate memory from the temporary cache more sparingly. This option should only be set after careful analysis shows it is actually required.
Indexing tips Operating systems use two different methods to allocate memory to disk cache: fixed and dynamic. A preset amount of memory is used in a fixed allocation; usually a 10–15 percent memory allocation is set aside. The operating system usually manages this workspace using a LRU (least recently used) algorithm. For a dynamic allocation, the operating system determines the disk cache allocation as it is running.
CHAPTER 12 Managing System Resources You should create either an LF or HG index in addition to the default index on each column referenced by the WHERE clause in a join query. Adaptive Server IQ cannot guarantee that its query optimizer will produce the best execution plan if some columns referenced in the WHERE clause lack either an LF or HG index. Non-aggregated columns referenced in the HAVING clause must also have the LF or HG index in addition to the default index. For example: SELECT c.name, SUM(l.
Managing database size and structure Managing database size and structure This section offers ideas on improving your database design and managing your data. Managing the size of your database The size of your database depends largely on the indexes you create, and the quantity of data you maintain. You achieve faster query processing by creating all of the indexes you need for the types of queries your users issue. However, if you find that some tables or indexes are not needed, you can drop them.
CHAPTER 12 Managing System Resources Denormalization has risks Denormalization can be successfully performed only with thorough knowledge of the application and should be performed only if performance issues indicate that it is needed. One of the things to consider when you denormalize is the amount of effort it will then take to keep your data up-to-date with changes.
Improving your queries Deciding to denormalize When deciding whether to denormalize, you need to analyze the data access requirements of the applications in your environment and their actual performance characteristics.
CHAPTER 12 Managing System Resources • Join indexes typically cause join queries to execute faster than ad hoc joins, at the expense of using more disk space. However, when a join query does not reference the largest table in a multi-table join index, an ad hoc join usually outperforms the join index. • You can improve performance by using an additional column to store frequently calculated results.
Improving your queries • This option controls the collection of timing statistics on subqueries and some other repetitive functions in the query engine. Normally it should be OFF because for very short correlated subqueries the cost of timing every subquery execution can be very expensive in terms of performance. QUERY_TIMING Setting query optimization options By adjusting the following options you can influence the speed at which queries are processed.
CHAPTER 12 Managing System Resources • Sets the maximum estimated number of rows the query optimizer will consider for a hash algorithm. The default is 125,000 rows. For example, if there is a join between two tables, and the estimated number of rows entering the join from both tables exceeds this option value, the optimizer will not consider a hash join. On systems with more than 50MB per user of TEMP_CACHE_MEMORY_MB, you may want to consider a higher value for this option.
Network performance • If large transfers are common, consider installing better network hardware that is suitable for such transfers. For example: • Token ring–responds better during heavy utilization periods than ethernet hardware. • Fiber optic–provides very high bandwidth, but is usually too expensive to use throughout the entire network. • Separate network–can be used to handle network traffic between the highest volume workstations and the server.
CHAPTER 12 Managing System Resources Figure 12-5: Isolating heavy network users Put small amounts of data in small packets If you send small amounts of data over the network, keep the default network packet size small (default is 512 bytes). The -p server startup option lets you specify a maximum packet size. Your client application may also let you set the packet size.
Network performance Figure 12-6: Small data transfers and small packet sizes Put large amounts of data in large packets If most of your applications send and receive large amounts of data, increase default network packet size. This will result in fewer (but larger) transfers.
CHAPTER 12 Managing System Resources Process at the server level Filter as much data as possible at the server level.
Network performance 464
CH A PTE R About this chapter 1 3 Monitoring and Tuning Performance This chapter describes tools you use to monitor Adaptive Server IQ performance. Use these tools to determine whether your system is making optimal use of available resources.
Viewing the Adaptive Server IQ environment • sp_iqcheckdb checks the validity of your current database • sp_iqdbstatistics reports results of the most recent sp_iqcheckdb • sp_iqdbsize gives the size of the current database • sp_iqstatus displays miscellaneous status information about the database. See the example below. • sp_iqtablesize gives the size of the table you specify. • sp_iqgroupsize lists the members of the specified group.
CHAPTER 13 Monitoring and Tuning Performance Block#: 95065709 Temporary IQ Blocks Used: 610 of 17646576, 0%=4Mb, Max Block#: 0 Main Reserved Blocks Available: 1280 of 1280, 100%=10Mb Temporary Reserved Blocks Available: 1280 of 1280, 100%=10Mb Memory: Current: 3351mb, Max: 3384mb Main IQ Buffers: Used: 11172, Locked: 0 Temporary IQ Buffers: Used: 38, Locked: 0 Main IQ I/O: I: L88043944/P495510 O: C760342/D761393/P736587 D:24753 C:65.
Monitoring the buffer caches PRIVATE starts monitoring of the temp buffer cache, for the Temporary Store of the database you are connected to. You need to issue a separate command to monitor each buffer cache. dummy_table_name can be any IQ table. However, it’s a good idea to create a table that you use only for monitoring. The table name is required for syntactic compatibility with other IQ UTILITIES commands.
CHAPTER 13 • Monitoring and Tuning Performance • Reads/Writes: Physical reads and writes performed by the buffer cache • PF/PFRead: Prefetch requests and reads done for prefetch • GDirty: Number of times the LRU buffer was grabbed dirty and Adaptive Server IQ had to write it out before using it • Pin%: Percentage of pages in the buffer cache in use and locked • Dirty%: Percentage of buffer blocks that were modified -cache_by_type produces the same results as -cache, but broken down by IQ page
Monitoring the buffer caches • 470 • AvPF: Number of currently available buffers for prefetch quota allocation • Slots: Number of currently registered objects using buffer cache quota • PinUser: Number of objects using pin quota • PFUsr: Number of objects using prefetch quota • Posted: Number of objects that are pre-planned users of quota • UnPost: Number of objects that are ad hoc quota users • Locks: Number of mutex locks taken on the buffer allocator • Waits: Number of times a thread ha
CHAPTER 13 • • Monitoring and Tuning Performance • FLWait: Number of times IQ had to wait for the lock on the free list (repeated for the temp cache) • MemLks: Number of times IQ took the memory manager (heap) lock • MemWts: Number of times IQ had to wait for the memory manager lock -threads displays information about processing threads • cpus: Number of CPUs on system • Limit: size of thread manager pool • NTeams: Number of thread teams currently in use • MaxTms: Largest number of teams t
Monitoring the buffer caches • -debug is used mainly to supply information to Sybase Technical Support. It displays all the information available to the performance monitor, whether or not there is a standard display mode that covers the same information. The top of the page is an array of statistics broken down by disk block type.
CHAPTER 13 Monitoring and Tuning Performance When you run the monitor again from the same database and connection number, by default it overwrites the previous results. If you need to save the results of a monitor run, copy the file to another location before starting the monitor again from the same database or use the -append option. Examples of monitor results This section shows sample results using different monitor options.
Monitoring the buffer caches Finds Creats Dests Dirty GDirty Pin% Dirty% Tm: 640 82 57 0 0.0 2.8 Tm: 1139 109 83 0 0.0 5.5 Tm: 6794 754 749 0 0.0 6.1 Tm: 10759 1646 1646 0 0.0 6.1 Example of -io option HR% BWaits ReReads FMiss Cloned Reads/ 99.4 0 4 0 0 Writes PFRead 4/0 0/0 109 100.0 0 0 0 0 0/0 0/0 754 100.0 0 0 0 0 0/0 0/0 1646 100.
CHAPTER 13 Example of -contention option Monitoring and Tuning Performance Note The actual -contention output shows Main Cache, Temp Cache, and Memory Manager on the same line. Because this format is very wide, each of these sets of columns is shown separately here.
Avoiding buffer manager thrashing 2 137 22 203 Example of -threads option 0 0 0 3 The results of the -threads option look like the following: Options string for Main cache: "-threads -file_suffix threads-iqmon -append -interval 10" Threads 2000-01-24 10:59:24 CPUs Limit NTeams MaxTms NThrds Resrvd Free Locks Waits 10 100 4 12 100 13 68 106 590 10 100 6 12 100 12 63 4 6 10 100 6 12 100 12 63 0 0 10 100 7 12 100 12 62 1 1 10 100 7 12 100 12 62 0 0 10 100
CHAPTER 13 Monitoring and Tuning Performance Buffer manager thrashing occurs when the operating system chooses less optimum buffers to page out to disk, which forces the buffer manager to make extra reads from disk to bring those buffers back to memory. Since Adaptive Server IQ knows which buffers are the best candidates to flush out to disk, you want to avoid this operating system interference by reducing the overall number of page outs.
Avoiding buffer manager thrashing 0 0 0 3312376 31840 0 0 0 0 3312376 31484 2 0 0 0 3312368 31116 0 8 3 8 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 297 201 472 82 0 260 169 597 80 0 205 1202 396 67 4 14 3 17 4 29 The above output shows a steady Adaptive Server IQ querying state where the physical memory of the machine has not been overallocated. Little to no system page faulting is occurring. These next set of examples show vmstat output that indicates a problem.
CHAPTER 13 19 0 0 3246612 26748 0 273 1248 0 0 0 0 Monitoring and Tuning Performance 950 394 1180 92 7 0 The above output is from slightly later when the query is underway. This is evident from the user mode CPU level (us field). The buffer cache is not yet full as page-in faults (pi field or KB paged in) are still occurring and the amount of free memory is still going down.
System utilities to monitor CPU use 480
CH A PTE R About this chapter 1 4 Adaptive Server IQ as a Data Server Adaptive Server IQ supports client application connections through either ODBC or JDBC. This chapter describes how to use Adaptive Server IQ as a data server for client applications. With certain limitations, Adaptive Server IQ may also appear to certain client applications as an Open Server.This chapter also briefly describes the restrictions for creating and running these applications.
Client/server interfaces to Adaptive Server IQ Open Clients and Open Servers Members of the Adaptive Server family act as Open Servers. Client applications communicate with Open Servers using the Open Client libraries available from Sybase. Open Client includes both the Client Library (CTLibrary) and the older DB-Library interfaces.
CHAPTER 14 Adaptive Server IQ as a Data Server At the other end of the client/server connection, while many Open Servers use the Sybase Open Server libraries to handle the interface to TDS, some applications have a direct interface to TDS of their own. Sybase Adaptive Server Enterprise and Adaptive Server IQ both have internal TDS interfaces. They appear to client applications as an Open Server, but do not use the Sybase Open Server libraries.
Client/server interfaces to Adaptive Server IQ Using the DSEDIT utility The DSEDIT utility is an Open Client utility that allows you to configure the interfaces file (sql.ini or interfaces). The following sections explain how to use the DSEDIT utility to configure the interfaces file. You must be the owner of the Sybase home directory ($SYBASE on UNIX or %SYBASE% on Windows NT) in order to run DSEDIT. These sections describe how to use DSEDIT for those tasks required for Adaptive Server IQ.
CHAPTER 14 • ❖ Adaptive Server IQ as a Data Server The interfaces file (sql.ini). To open a session: • Select Interfaces Driver from the DS Name box and click OK. Note The DSEDIT utility uses the SYBASE environment variable to locate the libtcl.cfg file. If the SYBASE environment variable is not set correctly, DSEDIT cannot locate the libtcl.cfg file. You can add, modify, or delete entries for servers, including Adaptive Server IQ servers, in this window.
Client/server interfaces to Adaptive Server IQ 2 Select the Server Address in the Attributes box. 3 Double-click on the Server Address or right click and choose Modify Attribute from the popup menu. The Network Address Attribute window appears, showing the current value of the address. If you have no address entered, the box will be empty. 4 Click Add. The Network Address for Protocol window appears. Select TCP from the Protocol list box and enter a value in the Network Address text box.
CHAPTER 14 Adaptive Server IQ as a Data Server to identify the current machine. Port Number The port number you enter must match the port specified on the Adaptive Server IQ database server command line, as described in “Starting the database server as an Open Server”. The default port number for Adaptive Server IQ servers is 2638. The following are valid server address entries: elora,2638 123.85.234.
Client/server interfaces to Adaptive Server IQ 3 Type a new name for the server entry in the Server Name box. Click OK to make the change. Deleting server entries You can delete server entries from the dsedit session window. ❖ To delete a server entry: 1 Click a server entry in the Server box. 2 Choose Delete from the Server Object menu.
CHAPTER 14 Adaptive Server IQ as a Data Server Setting up Adaptive Server IQ as an Open Server This section describes how to set up an Adaptive Server IQ server to receive connections from Open Client applications. System requirements There are separate requirements at the client and server for using Adaptive Server IQ as an Open Server.
Setting up Adaptive Server IQ as an Open Server asiqdemo.db On UNIX, you can use the start_asiq utility in place of asiqsrv12. The first command line uses both TCP/IP and IPX protocols, of which TCP/IP is available for use by Open Client applications. The second line uses only TCP/IP. The server can serve other applications through the TCP/IP protocol or other protocols using the Adaptive Server IQ-specific application protocol at the same time as serving Open Client applications over TDS.
CHAPTER 14 Adaptive Server IQ as a Data Server Characteristics of Open Client and jConnect connections When Adaptive Server IQ is serving applications over TDS, it automatically sets relevant database options to values that are compatible with Adaptive Server Enterprise default behavior. These options are set temporarily, for the duration of the connection only. They can be overridden by the client application at any time.
Characteristics of Open Client and jConnect connections SET TEMPORARY OPTION SET TEMPORARY OPTION HH:NN:SS.SSS’; SET TEMPORARY OPTION SET TEMPORARY OPTION SET TEMPORARY OPTION DATE_FORMAT=’YYYY-MM-DD’; TIMESTAMP_FORMAT=’YYYY-MM-DD TIME_FORMAT=’HH:NN:SS.SSS’; DATE_ORDER=’MDY’; ESCAPE_CHARACTER=’OFF’ Note Do not edit the sp_tsql_environment procedure yourself. It is for system use only. Options that are not supported by Adaptive Server IQ are ignored.
CHAPTER 14 Adaptive Server IQ as a Data Server Servers with multiple databases Using Open Client Library, you can now connect to a specific database on a server with multiple databases. • Set up entries in the interfaces file for each server. • Use the -n parameter on the start_asiq command to set up a shortcut for the database name. • Specify the -S database_name parameter with the database name on the isql command. This parameter is now required whenever you connect.
Characteristics of Open Client and jConnect connections 494
Index Symbols & UNIX command line 28 A Access ODBC configuration for 67 ad hoc joins performance 149 Adaptive Server Enterprise inserting from 193 Adaptive Server IQ buffer caches 427 matching data types with Adaptive Server Enterprise 217 monitor syntax 467 aggregates 145 AGGREGATION_ALGORITHM_ PREFERENCE option 458 AGGREGATION_CUTOFF option 458 ALL permissions 359 ALLOW_NULLS_BY_DEFAULT option Open Client 491 alphabetic characters defined 343 ALTER INDEX statement 197 ALTER permissions 359 ALTER statem
Index recovering from errors 389 responsibilities 415 scheduling 414 specifying tape devices on NT 386 third party 390 unattended 382, 409 wait time 388 base tables 120 batches about 229, 238 control statements 239 data definition statements 239 SQL statements allowed 267 BIT data converting 207 indexes allowed in 143 blanks converting to NULLs 215 BLOCK FACTOR BACKUP statement option 388 load option 181, 431 BLOCK SIZE LOAD TABLE option 182 block size 113 relationship to IQ page size 430 buffer cache moni
Index avoiding translation 338 choosing 344 definition 317 encoding 315, 317 fixed width 321 Interactive SQL 349 multibyte 321, 336 single-byte 319 Sybase Central 349 translation 348 Unicode 336 variable width 321 Windows 320 characters alphabetic 343 digits 343 white space 343 CHECK conditions columns 278 deleting 280 modifying 280 tables 280 user-defined data types 279 checkpoints about 304 automatic and explicit 304 in recovery 304 in system recovery 308 CLOSE statement procedures 252 code pages ANSI 320
Index backups 291, 383 data definition 300 in Adaptive Server IQ 290 insertions, deletions, and queries 299 read and write 292 configuration files using 29 configuring ODBC data sources 67 CONN connection parameter about 73 connect permission 357 connecting character sets 338 connection name ODBC configuration 70 connection parameters about 73 case sensitivity 75 conflicts 76 data sources 63 default 61 embedded databases 76 in connection strings 52 location of 79 priority 75 table of 73 connection strings
Index limiting number of 450 ODBC configuration 68 on SELECT statements 253 procedures 251 custom collations about 339 creating 339 creating databases 351 D daemon database server as 28 data duplicated 273, 274 exporting 171, 174 importing 171 in transactions 295 input and output formats 172 invalid 273 loading 171 data definition concurrency rules 300 data definition language about 99 data integrity constraints 276 overview 273 rules in the system tables 285 data modification permissions 173 data source d
Index about 73 DatabaseName connection parameter about 73 databases Adaptive Server Anywhere 6 Adaptive Server IQ data 6 benefits of denormalizing 455 block size 113 character set 336 checking consistency 393 choosing a location 110 connecting to 50, 77 creating 106 custom collations 351 default characteristics 108 denormalizing for performance 454 designing 99 displaying status information 9 displaying validation results 9 dropping 118 estimating space requirements 9 initializing 107 listing size 9 manage
Index about 481 DBLOG utility 405 DBN connection parameter about 73 DBS connection parameter about 73 dbspaces creating 114 definition 104 dropping discouraged 116 estimating space requirements 9 locating for best performance 445 DDL about 99 DDL (Data Definition Language) 16 Debug connection parameter about 73 DECLARE statement compound statements 241 procedures 252, 257 default index about 145 defaults connection parameters 61 DELETE permissions 359 DELIMITED BY option 179 Delphi ODBC configuration for 68
Index ODBC configuration 69 EncryptedPassword connection parameter about 73 encryption network packets 70 Encryption connection parameter about 73 ENG connection parameter about 73 EngineName connection parameter about 73 ENP connection parameter about 73 entity integrity about 276 enforcing 281 environment variables SQLCONNECT 62 error handling ON EXCEPTION RESUME 258 error messages character set translation 336 PIPE_NOT_CONNECTED 179 redirecting to files 175 errors data conversion 220 insertions and dele
Index types of 16 G global temporary tables about 120 gm switch 34 effect on recovery 308 GRANT statement creating groups 363 DBA authority 358 group membership 364 new users 357 passwords 357 permissions 359 procedures 361 RESOURCE authority 358 WITH GRANT OPTION 360 without password 366 GROUP BY clause impact on index choice 141 GROUP permissions not inheritable 363 groups adding with sp_addgroup 10 changing membership 10 creating 363 dropping 10 managing 363 membership 364 permissions 356, 365 PUBLIC 3
Index creating in Sybase Central 139 disk space usage 143 displaying size 9 dropping 134 in system tables 133 introduction 132 listing 9 parallel creation 139 selecting an index type 150 insert conversion options 204 INSERT LOCATION statement 193 INSERT permissions 359 INSERT statement 151 about 190 and integrity 275 incremental 192 partial-width insert 198 performance 192 VALUES option 190 inserting column width issues 208 from Adaptive Server Enterprise database 193 from older versions 204 from other dat
Index TDS 482 JDBC connections 51 join columns 159 join hierarchy 152 join indexes about 151 altering columns 124 columns in tables 153 creating 157 creating in Sybase Central 164 estimating size 169 estimating space requirements 9 inserting into 195 join hierarchy 152 join relationships 159 listing size 9 modifying underlying tables 168 performance impact 453 synchronizing 158 join relationships defining 159 specifying 161 JOIN_ALGORITHM_PREFERENCE option JOIN_MAX_HASH_ROWS option 459 JOIN_OPTIMIZATION opt
Index local temporary tables about 120 locale character sets 336 language 324 locales about 323 setting 346 localhost machine name 485 locking tables 299 LOG connection parameter about 73 Logfile connection parameter about 73 LOGIN_MODE database option integrated logins 87 logins integrated 86, 87 LOOP statement in procedures 253 syntax 239 Lotus format 172 Low_Fast index See LF index lower code page about 319 LTO connection parameter about 73 M main database buffer cache size 427 MAIN_CACHE_MEMORY_MB opt
Index N named pipes 179 national language support about 315 collations 344 multibyte character sets 336 overview 315, 317 natural joins 163 NEAREST_CENTURY option 212 network communications troubleshooting startup 94 network protocol specifying 60 network protocols ODBC configuration 70 networks large transfers 459 performance suggestions 459 settings 459 NOT NULL constraint 275 notification messages 187 NOTIFY insert option 153 NOTIFY option LOAD TABLE statement 183 NULL 213 conversion option 204, 215 con
Index P page size 111 Catalog 37 switch 37 paging effect on performance 420 memory 420 monitoring on UNIX 477 monitoring on Windows NT 477 Parallel CREATE INDEX 139 partial-width insertions about 197 rules 198 START ROW ID option 194 partial-width inserts examples 199 partitions definition 441 password default 354 Password connection parameter about 73 passwords changing 11, 357 ODBC configuration 69 pathnames for databases 109 performance ad hoc joins 149 balancing I/O 441 benefits of denormalizing databa
Index LOAD TABLE statement 184 primary keys creating 125 entity integrity 283 inserting data 196 order of columns 126 retrieving information 11 unenforced multi-column 126 priority lowering 450 privileges defining database objects 103 for inserting and deleting 173 procedures about 229 benefits of 230 calling 232 command delimiter 265 creating 231 cursors 251 cursors in 253 dates and times 266 default error handling 256 dropping 232 error handling 255 exception handlers 261 EXECUTE IMMEDIATE statement execu
Index memory use 424 RAWDETECT disk striping option 444 REAL data type matching Adaptive Server Enterprise and Adaptive Server IQ data 218 recovery system 307 transaction log in 308 transactions in 307 redirecting output to files 174 REFERENCES permissions 359 referential integrity declaring 283 enforcing 281 permissions 173 RELEASE SAVEPOINT statement 305 renaming database files 400 Replication Server support 488 RESIGNAL statement about 262 RESOURCE authority about 355 granting 358 not inheritable 363 re
Index asasrv.
Index column constraints 280 creating dbspaces 115 creating groups 364 creating tables 118 creating users 357 creating views 129 dropping views 131 foreign keys 126 introduction 100 permissions 361 primary keys 125 stopping 50 system tables 127 SYBASE environment variable DSEDIT 484 synchronizing about 158 SYS group 367 SYSCOLAUTH view permissions 375 SYSCOLLATION table collation files 340 SYSCOLUMN table integrity 285 SYSDUMMY table permissions 374 SYSFOREIGNKEY table integrity 285 SYSGROUP table permissi
Index locking 299 owner 355 permissions 355 qualified names 365, 367 See Also information in system tables 12 tabular data stream (TDS) about 481 tape devices for backup 385 TCP/IP addresses 485 Open Server 489 TDS about 481 TEMP environment variable disk space 93 TEMP_CACHE_MEMORY_MB option 427 temporary storage option to save space 312 Temporary Store about 6 buffer cache size 427 temporary tables about 120 loading 120 versioning 298 terminators LOAD TABLE statement 177 The 439, 450 threads management opt
Index using 224 using join operations 225 upper code page about 319 user accounts adding with sp_addlogin 10 user ID ODBC configuration 69 user IDs creating 357 default 354 deleting 362 listing 374 managing 353 user-defined data types CHECK conditions 279 user-defined functions calling 236 creating 235 dropping 237 execution permissions 237 external 268 parameters 245 using 235 Userid connection parameter about 73 users adding to groups 364 adding with sp_adduser 10 creating in Sybase Central 357 creating
Index defined 343 WIN_LATIN1 collation about 332 wired memory 435 setting iqwmem switch 34 WITH GRANT OPTION clause 360 Y year 2000 conversion options 212 Z -Z option database server 94 zeros converting to NULL 215 515
Index 516
