Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page 1: ...Administration and Performance Guide Adaptive Server IQ 12 4 2 ...

Page 2: ... MainframeConnect Maintenance Express MAP MDI Access Server MDI Database Gateway media splash MetaWorks MySupport Net Gateway Net Library NetImpact ObjectConnect ObjectCycle OmniConnect OmniSQL Access Module OmniSQL Toolkit Open Client Open ClientConnect Open Client Server Open Client Server Interfaces Open Gateway Open Server Open ServerConnect Open Solutions Optima PB Gen PC APT Execute PC DB Ne...

Page 3: ...g Store 6 Concurrent operations 7 Stored procedures 7 Adaptive Server IQ stored procedures 8 Adaptive Server Enterprise system and catalog procedures 9 Catalog stored procedures 11 System tables and views 12 Commands and Functions 16 Types of SQL statements 16 Functions 16 Message logging 17 The utility database 18 Compatibility with earlier versions 19 CHAPTER 2 Running Adaptive Server IQ 21 Star...

Page 4: ...iqdemo database 47 Starting and stopping Sybase Central 48 Connecting a plug in 49 Stopping Sybase Central 50 Introduction to connections 50 How connections are established 51 Connection parameters specify connections 52 Connection parameters are passed as connection strings 52 Connection parameters are passed as connection strings 53 Simple connection examples 53 Connecting to a database from DBI...

Page 5: ...to do if you can t connect to a database 95 Stopping a database server in an emergency UNIX 96 Resolving problems with your DBISQL window on UNIX 96 CHAPTER 3 Working with Database Objects 99 Building Your Adaptive Server IQ Databases 99 Designing your database 99 Tools for working with database objects 100 A step by step overview of database setup 101 Extending data definition privileges 103 Sele...

Page 6: ...dex 143 Combining index types 144 Adaptive Server IQ index types 144 Default column index 145 The Low_Fast LF index type 145 The High_Group HG index type 146 The High_Non_Group HNG index type 148 Optimizing performance for ad hoc joins 149 Selecting an index 150 Adding column indexes after inserting data 151 Using join indexes 151 Join indexes improve query performance 151 How join indexes are use...

Page 7: ...Main buffer cache activity message 188 Temporary buffer cache message 189 Controlling message logging 189 Using the INSERT statement 190 Inserting specified values row by row 190 Inserting selected rows from the database 191 Inserting from a different database 192 Importing data interactively 195 Inserting into tables of a join index 195 Inserting into primary and foreign key columns 196 Partial w...

Page 8: ... Introduction to user defined functions 235 Creating user defined functions 235 Calling user defined functions 236 Dropping user defined functions 237 Permissions to execute user defined functions 237 Introduction to batches 238 Control statements 239 Using compound statements 240 Declarations in compound statements 241 Atomic compound statements 242 The structure of procedures 243 SQL statements ...

Page 9: ...ures and functions with external calls 269 External function declarations 270 How parameters are passed to the external function 271 Special considerations when passing character types 272 CHAPTER 7 Ensuring Data Integrity 273 Data integrity overview 273 How data can become invalid 273 Integrity constraints belong in the database 274 How database contents get changed 275 Data integrity tools 275 S...

Page 10: ... recovery 308 Performance implications 309 Overlapping versions and deletions 310 Cursors in transactions 311 Cursors and versioning 312 Cursor sensitivity 312 Cursor scrolling 312 Hold cursors 313 Positioned operations 313 Cursor command syntax and examples 313 Controlling message logging for cursors 313 CHAPTER 9 International Languages and Character Sets 315 Introduction to international langua...

Page 11: ... collation sequence section 341 The Encodings section 342 The Properties section 343 International language and character set tasks 344 Finding the default collation 344 Configuring your character set environment 344 Determining locale information 345 Setting locales 346 Creating a database with a named collation 346 Starting a database server using character set translation 348 Using ODBC code pa...

Page 12: ...y 370 Using procedures for tailored security 371 How user permissions are assessed 372 Managing the resources connections use 372 Users and permissions in the system tables 374 CHAPTER 11 Backup and Data Recovery 377 Backup protects your data 377 Backing up your database 378 Types of backups 378 Selecting archive devices 380 Preparing for backup 381 Concurrency and backups 383 The BACKUP statement...

Page 13: ...esignating Backup and Restore Responsibilities 415 Improving performance for backup and restore 415 CHAPTER 12 Managing System Resources 419 Introduction to performance terms 419 Designing for performance 419 Overview of memory use 420 Paging increases available memory 420 Utilities to monitor swapping 421 Server memory 421 Managing buffer caches 422 Determining the sizes of the buffer caches 422 ...

Page 14: ...se size and structure 454 Managing the size of your database 454 Denormalizing for performance 454 Denormalization has risks 455 Disadvantages of denormalization 455 Performance benefits of denormalization 455 Deciding to denormalize 456 Improving your queries 456 Tips for structuring queries 456 Planning queries 457 Setting query optimization options 458 Network performance 459 Improving large da...

Page 15: ...ver interfaces to Adaptive Server IQ 481 Configuring IQ Servers with DSEDIT 483 Sybase applications and Adaptive Server IQ 488 Open Client applications and Adaptive Server IQ 488 Setting up Adaptive Server IQ as an Open Server 489 System requirements 489 Starting the database server as an Open Server 489 Configuring your database for use with Open Client 490 Characteristics of Open Client and jCon...

Page 16: ...xvi ...

Page 17: ...rt 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 Familiarity with relational database systems and introductory user level experience with Adaptive Server IQ is assumed How to use this book The following table shows which cha...

Page 18: ...ct Adaptive Server IQ indexes Chapter 4 Adaptive Server IQ Indexes Load data into your database Chapter 5 Moving Data In and Out of Databases Create procedures and batches Chapter 6 Using Procedures and Batches Add users and assign them privileges Chapter 10 Managing User IDs and Permissions Specify constraints on the data in your tables Chapter 7 Ensuring Data Integrity Understand how transaction...

Page 19: ...a very large data warehouse consisting of a write server and multiple query servers Adaptive Server IQ Release Bulletin Read just before or after purchasing Adaptive Server IQ for an overview of new features Read for help if you encounter a problem Note Because Adaptive Server IQ is an extension of the Adaptive Server Anywhere product IQ and Anywhere support many of the same features The IQ docume...

Page 20: ...Related documents xx ...

Page 21: ... systems Adaptive Server IQ is part of the Adaptive Server family that includes Adaptive Server Enterprise for enterprise transaction and mixed workload environments and Adaptive Server Anywhere a small footprint version of Adaptive Server often used for mobile and occasionally connected computing Sybase database architecture Sybase database architecture provides a common code base for Adaptive Se...

Page 22: ...ision support system is a software application designed to allow an organization to analyze data in order to support business decision making All of Adaptive Server IQ s capabilities are designed to facilitate DSS applications A unique indexing system speeds data analysis Query optimization gives you rapid responses even when results include thousands or millions of rows of data Concurrent data ac...

Page 23: ...ueries Chapter 4 Adaptive Server IQ Indexes Load data into your database Chapter 5 Moving Data In and Out of Databases Add users and assign them privileges Chapter 10 Managing User IDs and Permissions Ensure the integrity of data in your tables Chapter 7 Ensuring Data Integrity Understand how transactions impact concurrency Chapter 8 Transactions and Versioning Set up your database for the languag...

Page 24: ... user for objects owned by other users Granting permissions Except for the DBA who can perform any task users must be granted the authority to perform specific tasks For example you need the proper authority to Connect to a database Create database objects such as a database table or index Alter the structure of database objects Insert or delete data Select view data Execute procedures The DBA can...

Page 25: ...f you are not already familiar with these tools you should read about them in the Introduction to Adaptive Server IQ and use the tutorials provided there In addition to these tools Adaptive Server IQ provides a number of stored procedures that perform system management functions See Stored procedures for more information You can also create your own procedures and batches A few administrative task...

Page 26: ...ng and other temporary processing purposes you cannot store your data in them directly The Catalog Store The Catalog Store contains all of the information required to manage an IQ database This information which includes system tables and stored procedures resides in a set of tables that are compatible with Adaptive Server Anywhere These tables contain the metadata for the IQ database Metadata des...

Page 27: ...time while another user inserts or deletes data or backs up the database Changes to the structure of the database such as creating dropping or altering tables temporarily exclude other users from those tables but queries that only access tables elsewhere in the database can proceed Adaptive Server IQ keeps your database consistent during these concurrent operations by maintaining multiple versions...

Page 28: ...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 Note Stored procedures that produce size information assume that the database was created with the default block size as described in Block size If a database was created with a non default block size the output from the ...

Page 29: ...he space needed to create join indexes for the tables you specify sp_iqestdbspaces Estimates the number and size of dbspaces needed for a given total index size sp_iqestspace Estimates the amount of space needed to create a database based on the number of rows in the underlying database tables sp_iqindex Lists indexes and information about them Omitting the parameter lists all indexes in the datab...

Page 30: ...dures The following Adaptive Server Enterprise system procedures are provided in Adaptive Server IQ These stored procedures perform important system management tasks System procedure Description sp_addgroup Adds a group to a database sp_addlogin Adds a new user account to a database sp_addmessage Adds user defined messages to SYSUSERMESSAGES for use by stored procedure PRINT and RAISERROR calls sp...

Page 31: ...ive Server IQ Reference Manual sp_helptext Displays the text of a system procedure or view sp_password Adds or changes a password for a user ID System procedure Description Catalog procedure Description sp_column_privileges Unsupported sp_columns Returns the data types of the specified column sp_fkeys Returns foreign key information about the specified table sp_pkeys Returns primary key informatio...

Page 32: ...bout the size and location of each table Information about indexes Current settings for database and DBISQL options System tables include sp_servercaps Display information about a remote server s capabilities Procedure name Purpose System table Description DUMMY A table with exactly one row useful for extracting information from the database SYSARTICLE Describes an article in a SQL Remote publicat...

Page 33: ...ion on columns in every table or view in the IQ Store SYSIQFILE Lists information on operating system files for the database SYSIQINDEX Lists internal information on indexes in the database SYSIQINFO Lists additional database characteristics SYSIQJINDEX Describes join indexes in the database SYSIQJOINIXCOLUMN Describes columns that participate in join indexes SYSIQJOINIXTABLE Lists the tables that...

Page 34: ...ribes each SQL Remote publication SYSREMOTETYPE Contains information about SQL Remote SYSREMOTEUSER Describes user IDs with REMOTE permissions and the status of their SQL Remote messages SYSSQLSERVERTYPE Contains information relating to compatibility with Adaptive Server Enterprise SYSSUBSCRIPTION Relates each user ID with REMOTE permissions to a publication SYSTABLE Describes one table or view in...

Page 35: ...ation from SYSINDEX and SYSIXCOL SYSOPTIONS Displays option settings contained in the table SYSOPTION SYSPROCPARMS Lists all the procedure parameters from SYSPROCPARM SYSREMOTEUSERS Lists the information in SYSREMOTEUSER SYSTABAUTH Presents table permission information in SYSTABLEPERM SYSUSERAUTH Displays all the information in the table SYSUSERPERM exceptforuser numbers Because it contains passwo...

Page 36: ...e the Adaptive Server IQ Reference Manual Types of SQL statements You use three basic types of SQL statements DDL Data Definition Language statements let you define and modify your database schema and table and index definitions Examples of DDL statements include CREATE TABLE CREATE INDEX ALTER TABLE and DROP DML Data Manipulation Language statements let you query your data and move data into and ...

Page 37: ...ges Status messages Insert notification messages You can examine this file as you would any other text file At the start of the file you see output like the following 2000 03 07 17 20 50 0000 OpenDatabase Completed 2000 03 07 17 20 50 0000 IQ cmd line srv opts 2000 03 07 17 20 50 0000 DB r w Buffs 1913 Pgsz 4096 512blksz 8bpc 2000 03 07 17 20 50 0000 DB Frmt 23F 2T 1P FF 03 18 1999 2000 03 07 17 2...

Page 38: ...is database You need it to do any of these things Start the database server using the START ENGINE command with no database specified Create or drop a database when you have no other database to connect to Start the database server or connect to a database when any other databases you have are either corrupt or unavailable due to media failure Restore a database By default the utility database has...

Page 39: ...w support and better front end support It offers syntactic compatibility with Adaptive Server Anywhere allowing Anywhere users to build on their existing knowledge base as they begin to use IQ It also includes a new more efficient database format These last two features have special implications for users migrating from pre version 12 Adaptive Server IQ When you migrate to version 12 you must Exam...

Page 40: ...Compatibility with earlier versions 20 ...

Page 41: ...twork You may have multiple databases on a given database server Likewise you may be able to connect to more than one database server The server startup and connection options you choose must take into account these factors Starting the database server The first step in running Adaptive Server IQ is to start the database server You can start the server in all of these ways Type a server startup co...

Page 42: ...ches The elements of this command line are as follows server switches include the database server name and other options that control the behavior of the server for all databases that are running on that server database file is the file name of the Catalog Store You can omit this option or enter one or more database file names on the command line Each of these databases is loaded and available for...

Page 43: ...red method Enter the server startup command and the appropriate parameters see below You can also use any of the generic methods described elsewhere in this chapter provided that you set startup parameter defaults for each platform to the settings used in start_asiq These settings are listed in the Adaptive Server IQ Installation and Configuration Guide Normally you should always use the start_asi...

Page 44: ...discussed in the next section This command starts the named server as a background process starts the named database if you specify it and sets all required startup options Once the server starts it sends a message to the window or console where you started the server indicating that the server is running It also displays the version of the Open Client communications library that is in use and pos...

Page 45: ...se file from the asiqsrv12 command and specify a servername For ease of use however it is preferable to start the database and server together by specifying the database name when you start the server The server takes its name from the database name by default or you can specify a different name for the server See Naming the server and databases on page 31 for more information on server and databa...

Page 46: ...ing the server from the NT Start menu The easiest way to start the server on NT is from the Start menu Click Start on the Task bar and select Programs Sybase Adaptive Server IQ 12 From here you can start the sample database Sybase Central Interactive SQL and the ODBC Administrator You can also place databases of your own in the Program group Typing the server startup command You can use a Program ...

Page 47: ...unning the server outside the current session When you log on to a computer using a user ID and a password you establish a session When you start a database server or any other application it runs within that session When you log off the computer all applications associated with the session terminate In a production environment IQ database servers must be available all the time To make this easier...

Page 48: ...e under Windows NT This allows it to keep running even when you log off the machine For details of this and other NT specific features see the Adaptive Server IQ Installation and Configuration Guide Using command line switches You use command line switches to define your Adaptive Server IQ environment This section describes some of the most common command line switches and points out when you may ...

Page 49: ...e them in a configuration file and invoke that file on a server command line Specify switches in the configuration file as you would on the command line with these exceptions You can enter switches on multiple lines You must not include either single or double quotes in a configuration file gp Setting a maximum Catalog page size gr Recovery time gu Controlling permissions from the command line iqg...

Page 50: ...you started the server Using a configuration file to start the server ensures that you will be able to find these parameters when you need them Required command line switches While most of the command line switches described in the sections that follow are optional you must specify the n c gp and gm switches to run Adaptive Server IQ effectively For this release recommended server startup values a...

Page 51: ... not use hyphenated names or reserved words for database names user identifiers or server names even enclosed in quotation marks For example the following are not allowed grant june 1999 prospects foreign For a complete list of reserved words keywords see the Adaptive Server IQ Reference Manual Naming the server and databases You can use the n command line option as a database switch to name the d...

Page 52: ...e even when other databases with the same name have been started on other host systems To allow Adaptive Server IQ to locate the server no matter what character set is in use include only seven bit ASCII lower page characters in the server name For more information on character sets see Chapter 9 International Languages and Character Sets Specifying a server name lets you start a database server w...

Page 53: ...rces for a full discussion of how Adaptive Server IQ uses memory disk and processors the effect of user connections on resource use and options you can set to control resource use Some platform specific tuning suggestions are presented in this guide See also the Adaptive Server IQ Installation and Configuration Guide for your platform Setting memory switches Adaptive Server IQ uses memory for a va...

Page 54: ...arameter See the Adaptive Server IQ Installation and Configuration Guide for your platform for more information There may be times when you want to tune performance for a particular operation by limiting the number of user connections to fewer than your license allows Alternatively you may want to use the iqgovern switch to control query use see Concurrent queries Concurrent queries The iqgovern s...

Page 55: ...e Catalog Store cache size Use the c switch to set the amount of memory in the cache for the Catalog Store The default initial cache size is computed based on the amount of physical memory the operating system and the size of the database files On Windows NT the database server takes additional cache for the Catalog when the available cache is exhausted For many Adaptive Server IQ and Java applica...

Page 56: ...bal operations Starting and stopping databases The gd option allows you to limit the users who can start a database on a running server to those with a certain level of permission in the database to which they are already connected DBA the default Only the DBA can start an extra database ALL Any user can start and stop databases NONE No one can start or stop a database on a running server Sybase r...

Page 57: ...it into a cache page You use the gp option to set the Catalog page size explicitly By setting gp to the maximum size 4096 you maximize the number of columns per table that Adaptive Server IQ can support By default the server page size is the same as the largest page size of the databases on the command line The gp option overrides this default Once the server starts you cannot load a database with...

Page 58: ... is supported on all platforms IPX is supported on Windows NT client and server and Windows 95 client only NetBIOS is supported on Windows NT client and server and Windows 95 client only Named pipes is supported on Windows NT only Named Pipes is provided for same machine communications to and from Windows 3 x client applications using ODBC or Embedded SQL Specifying protocols By default the databa...

Page 59: ...confirm that a connection is intact If the server runs for a liveness timeout period default 2 minutes without detecting a liveness packet the communication is severed The server drops any connections associated with that client There is no warning All activity that falls within any open transaction is rolled back The tl switch on the server sets the liveness timeout in seconds for all clients tha...

Page 60: ...very or to recover leaked storage To start the server with these options see the chapter System Recovery and Database Repair in the Adaptive Server IQ Troubleshooting and Error Messages Guide Starting a server from DBISQL If you are already connected to a running database server you can start a new server from DBISQL Use the START ENGINE command to start a named server from DBISQL Note This method...

Page 61: ...ference Manual Create a data source name in the odbc ini file on UNIX for each server and provide detailed connection information For details see the Adaptive Server IQ Installation and Configuration Guide Use connection strings that specify explicit parameters instead of relying on defaults Confirm connections by issuing the following command SELECT database name is db_name servername_is serverna...

Page 62: ...ps eaf grep asiqsrv12 maryc 24836 25554 0 Feb 09 17 36 asiqsrv12 c 16m gc 6000 gd all gr 6000 gm 10 gp 4096 ti 4400 tl 300 iqmt 450 iqsmem 2560 fnma cfg asiqdemo db janed 28932 38122 0 11 39 24 2 10 asiqsrv12 c 16m gc 6000 gr 6000 gm 10 gp 4096 ti 4400 tl 300 iqsmem 2560 n janedemo gd all iqmt 256 x tcpip port 1872 Use the stop_asiq utility described in the following section which displays all Ada...

Page 63: ... down a server while it is still connected to one or more clients If you try this you get a warning that any uncommitted transactions will be lost Disconnect or close all the clients and try again You can also stop the database server in the following ways At the operating system command line issue the DBSTOP command with appropriate parameters Use the same parameters as when you started the serve...

Page 64: ...s1 qa daily engine new cfg asiqdemo db o express1 qa washburn 28350 Apr 11 0 20 asiqsrv12 gn 25 asiqdemo cfg o express1 users washburn mysybase12 4 0 asiq1 The following 1 server s are owned by janed Owner PID Started CPU_Time 1 janed 2838 15 11 37 0 07 asiqsrv12 c 16m gd all gm 10 gn 25 gp 4096 ti 4400 tl 300 asiqdemo cfg Please note that stop_asiq will shutdown a server completely without regard...

Page 65: ...sions required for users to stop the server The default level of permissions required is DBA but you can also set the value to ALL or NONE If you set it to NONE even the DBA cannot execute STOP ENGINE In a production environment Sybase strongly recommends that only the DBA be allowed to stop the database server Interactively of course anyone at the machine where the server was started can click Sh...

Page 66: ...rence Manual To start and connect to a database from DBISQL or Sybase Central use a data source that specifies the database file See Working with ODBC data sources To start and connect to a database when you start DBISQL from a system command prompt include the parameter DBF db file in the connection parameters See Connecting to a database from DBISQL To start a database from Sybase Central see Ch...

Page 67: ...ing the asiqdemo database You can start the server and the asiqdemo database easily using the configuration file that Adaptive Server IQ provides This configuration file called asiqdemo cfg contains all the parameters necessary to start the sample database To start the server and asiqdemo database on UNIX operating systems From a command line type the following command cd ASDIR demo start_asiq asi...

Page 68: ...ase Central to perform many administrative tasks This guide gives summary instructions for using Sybase Central For more information see the Introduction to Adaptive Server IQ or use the online help available within Sybase Central Starting Sybase Central on UNIX Systems To start Sybase Central change directory to SYBASE sybcentral and type scjview If you have added SYBASE asiq12 bin or SYBASE bin ...

Page 69: ...an also install its Sybase Central plug in When you next start Sybase Central the new product automatically plugs in to Sybase Central and appears in the main window The right panel displays the contents of the container that has been selected in the left panel Connecting a plug in If you do not see the plug in for Adaptive Server IQ in the main Sybase Central window you can connect to it manually...

Page 70: ...a database must establish a connection to that database before any work can be done The connection forms a channel through which all activity from the client application takes place For example your user ID determines permissions to carry out actions on the database and the database server has your user ID because it is part of the request to establish a connection This sounds simple but some clie...

Page 71: ...a JDBC see Chapter 4 Managing Databases with Sybase Central in Introduction to Adaptive Server IQ To create JDBC data sources see the chapter entitled Data Access Using JDBC in the Adaptive Server Anywhere User s Guide Note JDBC provides the link between the execution of Java objects and database operations For a description of Java support in Adaptive Server IQ see Enabling Java in the database o...

Page 72: ...verName host_asiqdemo DatabaseName asiqdemo Representing connection strings This chapter has many examples of connection strings represented in the following form parameter1 value1 parameter2 value2 This is equivalent to the following connection string parameter1 value1 parameter2 value2 A conceptual overview Connection parameters specify connections To create data sources Working with ODBC data s...

Page 73: ... example the following is a typical Collation utility dbcollat command line for Windows NT systems It should be entered all on one line dbcollat c uid DBA pwd SQL dbn asiqdemo c temp asiqdemo col Note DBISQL processes the connection string internally It does not simply pass on the connection parameters to the interface library Do not use Interactive SQL to test command strings from a command promp...

Page 74: ...ne database is started on a server for example you should specify the database name In a network with subnets specify the CommLinks parameter with protocol options including the host number In the odbc ini file you must use the long form of each parameter For example use DatabaseFile instead of DBF If your parameters are incomplete or incorrect you may see an error such as Database name required t...

Page 75: ...inks tcpip is only required if you use TCP IP to connect to the database If you use the shared memory port to connect to a local database you can omit the links parameter however it is always safer and required on some platforms to include complete network parameters To connect to a database on a foreign host you must add the host For example dbisql c uid DBA pwd SQL eng SERV1_asiqdemo links tcpip...

Page 76: ...ng host servername nnnn where servername is the name of your system and nnnn is your port number The default port number is 2638 but if the host was started with a different number use that instead 5 Click OK to connect to the database 6 After you connect to the database the DBISQL window appears The DBISQL window displays the database name user ID and server name for the connection on its title b...

Page 77: ...dbisql c uid DBA pwd SQL eng anotherdb links tcpip host hostname port nnnn Connecting to an embedded database An embedded database designed for use by a single application runs on the same machine as the application and is largely hidden from the application user When an application uses an embedded database the database server is generally not running when the application connects In this case yo...

Page 78: ... a cache size of 8 MB is sufficient Example connecting from DBSQL In this example the sample database is an embedded database within DBSQL To connect to an embedded database from DBSQL in Windows NT 1 Start DBISQL with no databases running You can use either of the following ways From the Windows NT Start menu choose Sybase Adaptive Server Anywhere Interactive SQL Type dbisql at a system command p...

Page 79: ... 0 Interactive SQL 2 Enter DBA as the user ID and SQL as the password 3 Specify the data source On Windows NT you can select from the drop down list of ODBC data sources for the sample database select ASIQ12 Sample On UNIX you must enter it in the ODBC Data Source field 4 For the sample database leave all other fields blank and click OK Adaptive Server IQ starts up and loads the sample database an...

Page 80: ...mple for connecting to a server running elsewhere on a network eng svr_name dbn db_name uid user_id pwd password CommLinks all The client library first looks for a local server of the given name and then looks on the network for a server of the specified name The above example finds any server started using the default port number However you can start servers using other port numbers by providing...

Page 81: ...t number running on different nodes in different subnets This is true because in most situations routers are not programmed to pass broadcast messages between subnets If you are running in a subnetted environment it is always safest to use specific host port numbers and server names to guarantee that you are connecting to the proper server and database This is particularly true when using default ...

Page 82: ...d For more information about default behavior see How Adaptive Server IQ makes connections Connecting from Adaptive Server IQ utilities Adaptive Server IQ database utilities that communicate with the server rather than acting directly on database files do so using Embedded SQL They follow the procedure outlined in How Adaptive Server IQ makes connections when connecting to a database How database ...

Page 83: ...nectivity ODBC interface Microsoft Corporation defines the ODBC interface which is a standard interface for connecting client applications to database management systems in the Windows and Windows NT environments Many client applications including application development systems use the ODBC interface to access a wide range of database systems Although data sources are especially designed for Wind...

Page 84: ...ta Source Name is always stored on a file on all platforms File data sources can be distributed File data sources can easily be distributed to end users so that connection information does not have to be reconstructed on each machine It can be sent via email for example but is not stored automatically in any public place If the file is placed in the default location for file data sources it is pic...

Page 85: ...e or incorrect you may see an error such as Database name required to start engine For a complete list of connection parameters see Chapter 3 Connection and Communication Parameters in Adaptive Server IQ Reference Manual Examples of connection strings using data sources The following connection string specifies an ODBC Data Source Name and a user ID DSN ASIQ sample uid DBA The following connection...

Page 86: ...side the box For example to connect to a server on system PUSHKIN using TCP IP protocol and port 1870 you would click TCP IP and type host pushkin 1870 You could also use the host network address For example host 157 133 66 75 1870 8 Click OK when you have finished defining your data source The ODBC Data Source Administrator returns you to the User DSN tab For details of the ODBC configuration box...

Page 87: ...ized by tab ODBC tab Data source name The Data Source Name is used to identify the ODBC data source You can use any descriptive name for the data source spaces are allowed but it is recommended that you keep the name short as you may need to enter it in connection strings For more information see the DataSourceName connection parameter in the Adaptive Server IQ Reference Manual Description You can...

Page 88: ...e option is unchecked scrollable cursor performance can suffer since scrolling must always take place from the beginning of the cursor to the row requested in order to get the correct bookmark value Prevent Driver Not Capable errors The Adaptive Server Anywhere ODBC driver returns a Driver not capable error code because it does not support qualifiers Some ODBC applications do not handle this error...

Page 89: ... the chapter Connection and Communication Parameters in the Adaptive Server IQ Reference Manual Database tab Server name Provides a place for you to enter the name of the IQ server Start line Enter the server that should be started Only provide a Start Line parameter if a database server is being connected to that is not currently running For example C Program Files Sybase ASIQ12 win32 asiqsrv12 e...

Page 90: ...rence Manual Encrypt all network packets Enables encryption of packets transmitted from the client machine over the network By default network encryption packets is set to OFF Liveness timeout A liveness packet is sent across a client server to confirm that a connection is intact If the client runs for the liveness timeout period without detecting a liveness packet the communication will be severe...

Page 91: ...ayout to initialization files Creating a file data source from the ODBC Administrator On Windows NT systems you can create a file data source using the following procedure To create an ODBC file data source 1 Select Settings Control Panel and then click the ODBC icon to start the ODBC Administrator 2 From the File DSN tab click Add 3 Select Adaptive Server IQ 12 from the list of drivers and click ...

Page 92: ...d by the HOME environment variables 3 The path The database server ignores the ODBC_HOME ODBC_INI and ODBCINI environment variables Note On UNIX systems Adaptive Server IQ installation installs only the ODBC driver and not the driver manager The name of the driver file includes an operating system specific extension for example so for Solaris systems For example on a Sun Solaris system if you are ...

Page 93: ... connections Embedded databases CommAutoStop CAstop Yes No Unload network communications ports as soon as there are no more open connections from the client machine CommBufferSize CBSize Integer Set the maximum size of communication packets in bytes CommBufferSpace CBSpace Integer Specify the amount of space to allocate on startup for network buffers in kilobytes CommLinks Links String Specify net...

Page 94: ...Password ENP Encrypted string Provide a password and store it in an encrypted fashion in a data source Encryption ENC Boolean Encrypt packets transmitted from the client machine over the network EngineName ServerName ENG String Name of the database server FileDataSourceName FILEDSN String Provide a file data source name for the connection Integrated INT Yes No Enable integrated logins For a client...

Page 95: ...composed of characters in the range 1 to 127 of the ASCII character set There is no such limitation on other parameters For more information on the character set issues see Connection strings and character sets The following rules govern the priority of parameters Logfile LOG String Send client error messages and debugging messages to a file Password PWD String Provide a password for the connectio...

Page 96: ...se name using the DBN connection parameter or using the DBS parameter Here are some recommendations and notes for situations where connection parameters conflict Specify database files using DBF You can specify a database file on the Start parameter or using the DBF parameter DBF is recommended Specify database names using DBN You can specify a database name on the Start parameter the DBS paramete...

Page 97: ...applications Many application development systems such as Sybase PowerBuilder and Power belong to this class of application Any client application using Embedded SQL and using the recommended function for connecting to a database db_string_connect The SQL CONNECT statement is available for Embedded SQL applications and in Interactive SQL It has two forms CONNECT AS and CONNECT USING All the databa...

Page 98: ... s driver The ODBC driver manager looks in the supplied data source in the odbc ini file or registry to locate the driver When you create a data source using the ODBC Administrator Adaptive Server IQ fills in the current location for your ODBC driver Embedded SQL interface library location Embedded SQL applications call the interface library by name The name of the Adaptive Server IQ Embedded SQL ...

Page 99: ...a data source and in a connection string the connection string value overrides the data source value Failure Failure at this stage occurs only if you specify in the connection string or in SQLCONNECT a data source that does not exist in the client connection file Common parameters Depending on other connections already in use some connection parameters may be ignored These include the following Au...

Page 100: ...erSpace 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 ...

Page 101: ... name followed by a search over a network If no ENG parameter is supplied the interface library looks for a default server If the interfaces library locates a server it tries to locate or load the required database on that server For information see Locating the database on page 83 Notes For local connections locating a server is simple For connections over a network you can use the CommLinks para...

Page 102: ...CommLinks parameter Since these parameters are used only when the port first starts the interface library ignores any connection parameters specified in CommLinks for a part already started Each attempt to locate a server the local attempt and the attempt for each network port involves two steps First the interfaces library looks in the server name cache to see if a server of that name is availabl...

Page 103: ...mple 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 The default database is the one started with the server ...

Page 104: ...it was found on and its address are saved to a file The server information is saved in a file named asasrv ini in your Adaptive Server IQ executable directory The file contains a set of sections each of the following form Server name Link protocol_name Address address_string How the cache is used When a connection specifies a server name and a server with that name is not found the network library...

Page 105: ...data from distributed heterogeneous sources available to clients With CIS in place you define proxy tables in Adaptive Server Enterprise that represent your Adaptive Server IQ tables You can then query the proxy tables from Adaptive Server Enterprise For details see Component Integration Services User s Guide for Adaptive Server Enterprise and OmniConnect CIS and Adaptive Server IQ offer several o...

Page 106: ...enabled to use integrated logins and a mapping must have been granted between the user profile used to log in to the machine and or network and a database user Using an integrated login is more convenient for the user and permits a single security system for database and network security Its advantages include When connecting to a database using an integrated login the user does not need to enter ...

Page 107: ...tion determines whether the integrated login feature is enabled As database options apply only to the database in which they are found different databases can have a different integrated login setting even if they are loaded and running within the same server The LOGIN_MODE database option accepts one of following three values which are case insensitive Standard This is the default setting which d...

Page 108: ...n Sybase Central or a SQL statement To map an integrated login from Sybase Central 1 Connect to a database as a user with DBA authority 2 Open the Integrated Logins folder for the database and double click Add Integrated Login The Integrated Login wizard is displayed 3 On the first page of the wizard enter the name of the system computer user for whom the integrated login is to be created You can ...

Page 109: ...user profile name matches a integrated login mapping in the database Interactive SQL Examples For example a connection attempt using the following Interactive SQL statement will succeed providing the user has logged on with a user profile name that matches a integrated login mapping in a default database of a server CONNECT USING INTEGRATED yes The following DBISQL statement CONNECT can connect to...

Page 110: ...ed login user ID When using integrated logins database administrators should give special consideration to the way Windows NT enforces login security in order to prevent unwanted access to the database In particular be aware that by default a Guest user profile is created and enabled when Windows NT Workstation or Server is installed Warning Leaving the user profile Guest enabled can permit unrest...

Page 111: ... added security Setting the value of the LOGIN_MODE option for a given database to Mixed or Integrated using the following SQL statement permanently enables integrated logins for that database SET OPTION Public LOGIN_MODE Mixed If the database is shut down and restarted the option value remains the same and integrated logins are still enabled Changing the LOGIN_MODE option temporarily will still a...

Page 112: ...ain the user attempting an integrated login must have domain permissions with the Domain Controller server and be logged in to the network A user profile on the network server matching the user profile on the local machine is not required Creating a default integrated login user A default integrated login user ID can be created so that connecting via an integrated login will be successful even if ...

Page 113: ...eleting the transaction log at the same time This causes the transaction log file to be different from the database and results in an invalid transaction log file The same is true for the IQ Temporary Store file Ensure that you have sufficient disk space for your temporary file Adaptive Server IQ uses a temporary file to store information while running This file is stored in the directory pointed ...

Page 114: ...ing NetBIOS under Windows NT you may want to confirm that the chat or winpopup application is working properly between machines running client and database server software Check environment variables In order to start the server certain environment variables must be set properly On Windows NT the installation procedure sets any environment variables you need automatically On UNIX you must set thes...

Page 115: ...est Connection If that server is running you see a Connection Successful message On UNIX enter the following at the system prompt substituting the name of your database server for asiqdemo ps eaf grep asiqdemo Check that any connection parameters you enter on the command line are correct If you are connecting to a database that is not running check that the server was started with the gd ALL switc...

Page 116: ...siq try the following 1 If possible you should make sure that no users are connected to the database 2 At the UNIX prompt enter the following command kill hup pid where pid is the process id of the database server you are stopping See also Adaptive Server IQ Troubleshooting and Error Messages Guide Resolving problems with your DBISQL window on UNIX The interactive DBISQL utility on UNIX uses chara...

Page 117: ...ing Adaptive Server IQ 97 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 ...

Page 118: ...Troubleshooting startup shutdown and connections 98 ...

Page 119: ...ains how you create both stores and the objects in your IQ Store Tables created in the Catalog Store have the characteristics of Adaptive Server Anywhere tables If you want to create tables in the Catalog Store you need to refer to the Adaptive Server Anywhere documentation Building Your Adaptive Server IQ Databases This section introduces you to the steps involved in creating a database and the t...

Page 120: ... who has access to the data Before using the procedures in this chapter to create an IQ database be sure you understand the relational database and data warehousing concepts described in Introduction to Adaptive Server IQ Tools for working with database objects Adaptive Server IQ includes two utilities for working with database objects Sybase Central and DBISQL In addition Warehouse Architect can ...

Page 121: ...d the database If you use a tool other than DBISQL all the information in this chapter concerning SQL statements still applies DBISQL command file A DBISQL command file is a text file with semicolons placed at the end of commands as shown below CREATE TABLE t1 CREATE TABLE t2 CREATE LF INDEX i2 ON t2 A DBISQL command file usually carries the extension sql To execute a command file either paste the...

Page 122: ...E statement or the Sybase Central table editor See Working with tables on page 118 4 Create indexes for the tables Use the CREATE INDEX statement or the Sybase Central Index Wizard You can also create certain indexes automatically when you create your tables See Chapter 4 Adaptive Server IQ Indexes 5 Load data into the tables Use the LOAD TABLE statement to bulk load data from files or use the INS...

Page 123: ...r a multiplex database you must perform the data definition task on the write server in simplex mode and then shut it down and bring it up in multiplex mode before backing up the database See the Adaptive Server IQ Multiplex User s Guide for details before data definition 4 Back up the database again 5 Allow users to connect to the database Extending data definition privileges In order to perform ...

Page 124: ...he transaction log cannot be on a raw partition Allocating space for databases All Adaptive Server IQ databases are preallocated whether they reside in a file system or a raw partition Each database includes multiple dbspaces A dbspace is a logical name for a database file The Catalog Store the IQ Store and the Temporary Store all consist of dbspaces The first dbspace for each store is created aut...

Page 125: ...atabase and the objects in it Adaptive Server IQ provides stored procedures that you can run to estimate how much space and how many dbspaces your databases will require See the Adaptive Server IQ Reference Manual for syntax and usage notes for each procedure Running the procedures in the sequence that follows can help you avoid running out of space for your objects 1 Run the stored procedure sp_i...

Page 126: ...ize All these calculations are estimates Results vary based on the columns and indexes you create for your database For more information on these stored procedures see the Adaptive Server IQ Reference Manual Working with databases Some application design systems such as Sybase WarehouseArchitect contain facilities for creating database objects These tools construct SQL statements that are submitte...

Page 127: ...her the CREATE DATABASE statement or Sybase Central Once the database is created you can connect to it and build the tables and other objects that you need in the database Before you create your database In order to create a database you must Start the database server Start either Sybase Central or DBISQL To create a database in DBISQL you need to connect to an existing database or else start the ...

Page 128: ...r IQ Multiplex feature See the Adaptive Server IQ Multiplex User s Guide for details Database file compatibility Adaptive Server IQ servers cannot manage databases created with versions prior to Adaptive Server IQ 12 0 likewise old servers cannot manage new databases Using Sybase Central to create an IQ database To create an IQ database in Sybase Central click the Utilities folder in the left pane...

Page 129: ...les see the discussion of this parameter below IQ Temporary Store size is half the IQ size jConnect JDBC driver is enabled JCONNECT ON The collation sequence ISO_BINENG is used The collation order is the same as the order of characters in the ASCII character set In a case sensitive database all uppercase letters precede all lowercase letters for example both A and B precede a Java is enabled JAVA ...

Page 130: ... PATH parameter tells Adaptive Server IQ that you are creating an IQ database not an Anywhere database You specify the location of your IQ Store in this parameter It is preferable to use a relative pathname When you do the IQ Store is created relative to the directory where the server was started which can change the next time the server is started Choose a location for your database carefully Alt...

Page 131: ...company iq Choosing an IQ page size You set a page size for the IQ Store with the IQ PAGE SIZE option This option determines memory and disk use The IQ PAGE SIZE must be a power of 2 from 65536 to 524288 bytes The IQ page size is the same for all dbspaces in the IQ Store To obtain the best performance Sybase recommends the following minimum IQ page sizes 64 KB IQ PAGE SIZE 65536 for databases whos...

Page 132: ...ing system files you can rely on the defaults listed below or specify a value based on the size of your data from the required minimum listed below up to a maximum of 128GB in 1MB increments Table 3 1 Default and minimum sizes of IQ and Temporary Stores Choosing a Catalog page size You can select a page size for the Catalog Store with the PAGE SIZE option You should always use 4096 for this option...

Page 133: ...t use a Java based user defined function within a query to an IQ table but you can use it on Catalog Store tables Windows NT users can also use Java based user defined functions in queries on tables in an Adaptive Server Anywhere database or queries to IQ tables from an Adaptive Server Anywhere database using the remote access capabilities described in the Adaptive Server IQ Installation and Confi...

Page 134: ... dbspaces When you create a new dbspace it has no contents When you create tables and indexes and load them Adaptive Server IQ distributes the data as equally as possible among any existing dbspaces that are not already full This technique optimizes performance Because Adaptive Server IQ fills dbspaces in this way you cannot specify that a particular IQ table be loaded into a particular dbspace Yo...

Page 135: ...most efficient resource use make your dbspaces small enough to fit on your backup media and large enough to fill up the disk Example The following command creates a new dbspace called library in the file library iq in the same directory as the IQ_SYSTEM_MAIN dbspace CREATE DBSPACE library AS library iq Creating a dbspace in Sybase Central To create a dbspace in Sybase Central 1 Connect to the data...

Page 136: ...the last one and can be dropped It must not be one of the four initial dbspaces SYSTEM IQ_SYSTEM_MAIN IQ_SYSTEM_TEMP and IQ_SYSTEM_MSG These dbspaces can never be dropped Because of the way Adaptive Server IQ fills dbspaces with data it is unlikely that you will be able to drop the last dbspace especially if disk striping is in use You also cannot empty a dbspace by truncating the tables in it as ...

Page 137: ...ks 75271680 87404023 mydb_7 DB Blocks 87816960 99949303 mydb_8 Temp Blocks 1 8823288 IQ_SYSTEM_TEMP Temp Blocks 9408960 18232247 mydb_tmp2 Create Time 1999 12 30 19 10 55 231 Update Time 2000 03 14 09 52 13 609 Main IQ Buffers 11174 1400Mb Temporary IQ Buffers 15165 1900Mb Main IQ Blocks Used 43515029 of 97058752 44 331Gb Max Block 95065709 Temporary IQ Blocks Used 610 of 17646576 0 4Mb Max Block ...

Page 138: ...ables When you create a database the only tables in it are the system tables which hold the database schema This section describes how to create alter and delete tables from a database The examples can be executed in DBISQL but the SQL statements are independent of the administration tool you are using You may want to create command files containing the CREATE TABLE and ALTER TABLE statements that...

Page 139: ...QL see Connecting to a database from DBISQL You can create tables with other tools in addition to DBISQL The SQL statements described here are independent of the tool you are using Example The following statement creates a new permanent IQ table to describe qualifications of employees within a company The table has columns to hold an identifying number a name and a type say technical or administra...

Page 140: ...ables Base tables are permanent Base tables are sometimes called main or permanent tables because they are stored in the main IQ Store and are a permanent part of the database until you drop them explicitly Base tables and the data in them are accessible to all users who have the appropriate permissions The CREATE TABLE statement shown in the previous example creates a base table Creating temporar...

Page 141: ...able 3 2 Table placement Join virtual tables A Join Virtual Table is a denormalized table that looks like a regular table it has a name columns rows and indexes Adaptive Server IQ creates Join Virtual Tables as a result of a Create Join Index for internal processing purposes and deletes them when you do a Drop Join Index You cannot create modify or delete Join Virtual Tables but you may see error ...

Page 142: ...ct of IQ UNIQUE Difference between UNIQUE and IQ UNIQUE IQ UNIQUE count gives an approximation of the number of distinct values that can be in a given column Each distinct value can appear many times For example in the employee table a limited set of distinct values could appear in the state column but each of those values could appear in many rows IQ UNIQUE 256 or less IQ UNIQUE 65536 or less IQU...

Page 143: ... to classification ALTER TABLE skill RENAME skill_type TO classification Example 3 The following statement deletes the classification column ALTER TABLE skill DELETE classification Example 4 The following statement changes the name of the entire table ALTER TABLE skill RENAME qualification These examples show how to change the structure of the database The ALTER TABLE statement can change many cha...

Page 144: ...TE statement the DROP statement automatically executes a COMMIT before and after dropping the table This makes permanent all changes to the database since the last COMMIT or ROLLBACK The DROP statement also drops all indexes on the table except if any column in the table participates in a join index If you only want to remove data rows but not the table itself use the TRUNCATE TABLE statement If y...

Page 145: ...w in the table which in this case means that you cannot have more than one row with a given skill_id Each row in a table is uniquely identified by its primary key Columns in the primary key are not allowed to contain NULL You must specify NOT NULL on the column in the primary key Note Adaptive Server IQ does not enforce multi column primary keys You must specify the keyword UNENFORCED when you def...

Page 146: ... so appear in several rows and several employees may possess a given skill so that the skill_id may appear several times The emp_skill table also has two foreign keys The foreign key entries indicate that the emp_id column must contain a valid employee number from the employee table and that the skill_id must contain a valid entry from the skill table A table can only have one primary key defined ...

Page 147: ...ables in a database is held in the system tables The information is distributed among several tables For more information see System Tables in Adaptive Server IQ Reference Manual You can use Sybase Central or DBISQL to browse the information in these tables Type the following command in the DBISQL command window to see all the columns in the SYS SYSTABLE table SELECT FROM SYS SYSTABLE To display t...

Page 148: ...nvoke them Benefits of tailoring access Views are used to tailor access to data in the database Tailoring access serves several purposes Improved security By not allowing access to information that is not relevant Improved usability By presenting users and application developers with data in a more easily understood form than in the base tables Improved consistency By centralizing in the database ...

Page 149: ...e shown above enter employee and dept_ID 5 From the File menu select Execute Script and from the File menu select Close For more information see the Sybase Central online Help Using views When you use views you need to be aware of certain restrictions both on the SELECT statements you can use to create them and on your ability to insert into delete from or update them Restrictions on SELECT statem...

Page 150: ...y a view using the ALTER VIEW statement The ALTER VIEW statement replaces a view definition with a new definition it does not modify an existing view definition The ALTER VIEW statement maintains the permissions on the view Example For example to replace the column names with more informative names in the DepartmentSize view described above you could use the following statement ALTER VIEW Departme...

Page 151: ... GRANT OPTION on the base table s permission s may have been granted to PUBLIC This includes SELECT permission on system tables UPDATE permissions can be granted only on an entire view Unlike tables UPDATE permissions cannot be granted on individual columns within a view Deleting views To delete a view from the database you use the DROP statement The following statement removes the DepartmentSize ...

Page 152: ...is important to choose the right indexes for your data to achieve the greatest performance and to make best use of memory disk and CPU cycles Introduction to indexes All IQ database columns with data need an index When you create a database in an IQ store a default index is created automatically on every column of every table You can also choose from several other index types Four column index typ...

Page 153: ...etails on selecting and creating indexes See the Adaptive Server IQ Reference Manual for command syntax Indexes in the system tables Information on indexes is in the system tables SYSINDEX SYSIQINDEX SYSIXCOL and for join indexes SYSIQJINDEX See the Adaptive Server IQ Reference Manual for a description of these tables See Introduction to Adaptive Server IQ for an explanation of how to browse syste...

Page 154: ... 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 Before you drop a join index see Modifying tables included in a join index for special requirements ...

Page 155: ...esent and store the data so that the data can be used for processing queries This strategy is designed for the data warehousing environment in which queries typically examine enormous numbers of records often with relatively few unique values and in which aggregate results are commonly required Adaptive Server IQ index types When you load data into a table Adaptive Server IQ stores data by column ...

Page 156: ...pter discusses join indexes see Using join indexes for details A default index that optimizes projections is created by Adaptive Server IQ for all columns When a column is designated as either a PRIMARY KEY or UNIQUE Adaptive Server IQ creates a High_Group index for it automatically To achieve maximum query performance however you should choose one or more additional index types for most columns t...

Page 157: ...al index is needed you can always add indexes However it is much faster to create all the appropriate indexes before you insert any data You can drop any optional index if you decide that you do not need it See the DROP INDEX command in the Adaptive Server IQ Reference Manual for more information on dropping indexes You cannot drop automatically created indexes using DROP INDEX The only way to rem...

Page 158: ...r table name column name IN ON dbspace name NOTIFY integer If you do not specify an index type Adaptive Server IQ creates an HG index Several front end tools create an HG index automatically for this reason Examples The first example creates a High_Non_Group HNG index called ship_ix on the ship_date column of the sales_order_items table CREATE HNG INDEX ship_ix ON dbo sales_order_items ship_date T...

Page 159: ...iles of that type with room available The dbspace name option of CREATE INDEX is ignored for IQ indexes and is provided for compatibility with Adaptive Server Anywhere Creating an index with Sybase Central To create a column index using Sybase Central follow these steps To create an index with Sybase Central 1 Connect to the database 2 Select the table in which the column appears 3 Open the Indexe...

Page 160: ... is allowed Choosing an index type The set of indexes you define for any given column can have dramatic impact on the speed of query processing There are four main criteria for choosing indexes Number of unique values Types of queries Disk space usage Data types Use the recommendations for all criteria in combination rather than individually Remember also that all columns are automatically stored ...

Page 161: ...ues Columns that hold marital status will have just a few unique values single married NULL Columns that hold state or province names will have fewer than 100 unique values Columns that hold date data probably have more than 100 but fewer than 65536 unique values Columns that hold account numbers or social security numbers may have thousands or millions of unique numbers Types of queries You shoul...

Page 162: ...also consider the number of unique values and disk space See the other tables in this section Table 4 2 Query type index Note While HNG is recommended in certain cases LF or HG is faster and is often used in place of HNG HNG tends to give consistent performance while the performance of LF or HG with ranges depends on the size of the range selected Type of Query Usage Recommended Index Type In a SE...

Page 163: ... source database or flat file Table 4 3 Index disk space usage For LF and HG indexes the index size depends on the number of unique values The more unique values the more space the index takes Data types in the index Only the default index supports the following data types BIT data Type of index Estimated space versus raw data Comments Default Smaller than or equal to If the number of distinct val...

Page 164: ...o be used in more than one type of query multiple column index types might be appropriate The following table shows which index types make good combinations Table 4 4 Mix of valid indexes Note The High_Group index in Adaptive Server IQ Version 12 0 differs from earlier versions For some columns you may want both High_Group and High_Non_Group previously it did not make sense to have both Adaptive S...

Page 165: ...l number of rows The Low_Fast LF index type This index is ideal for columns that have a very low number of unique values under 1 000 such as sex Yes No True False number of dependents wage class and so on LF is the fastest index in Adaptive Server IQ When you test for equality just one lookup quickly gives the result set To test for inequality you may need to examine a few more lookups Calculation...

Page 166: ...s in the WHERE clause Only when the number of unique values is high should other indexes HG and HNG be considered For range queries with a high number of unique values also consider having an HNG index The High_Group HG index type The High_Group index is commonly used for join columns with integer data types It is also more commonly used than High_Non_Group because it handles GROUP BY efficiently ...

Page 167: ... criteria for a High_Group index may be used in queries where a different type of index may be faster If this is the case create additional indexes for that column Automatic creation of High_Group index Adaptive Server IQ creates a High_Group index by default whenever you issue a CREATE INDEX statement without specifying an index type Adaptive Server IQ automatically creates a High_Group index for...

Page 168: ...than an HG index requires On that basis alone if you do not need to do group operations use an HNG index instead of a HG index Conversely if you know you are going to do queries that a HG index handles more efficiently or if the column is part of a join and or you want to enforce uniqueness use a HG index Note Using the HNG index in place of a HG index may seriously degrade performance of complex ...

Page 169: ...s that may be referenced in WHERE clauses of ad hoc join queries HAVING clause conditions of ad hoc join queries outside of aggregate functions For example SELECT n_name sum l_extendedprice 1 l_discount AS revenue FROM customer orders lineitem supplier nation region WHERE c_custkey o_custkey AND o_orderkey l_orderkey Advantages Disadvantages Due to compression algorithms used disk space requiremen...

Page 170: ...th UNIQUE enforced Identify all columns used in a join predicate and choose the index type depending on the number of unique values HG or LF Identify columns that contain a low number of unique values and do not already use multiple indexes LF Identify columns that have a high number of unique values and that are part of a GROUP BY clause in a select list in a SELECT DISTINCT or DISTINCT COUNT HG ...

Page 171: ...additional index after you have already inserted data This allows you to add the index without having to start over Note Inserting data from an existing index can be slow It is always faster to create all the appropriate indexes before you insert data then insert into all of them at once with either the LOAD TABLE or INSERT statement Using join indexes If you know that certain tables in the same d...

Page 172: ... but a customer has only one sales representative There can be multiple levels of such relationships However you always specify join relationships between two tables or between a table and a lower level join The table that represents the many side of the relationship is called the top table See Join hierarchy overview below for details When a join becomes ad hoc If there is no join index that hand...

Page 173: ... 1 Hierarchy of a join relationship In a join hierarchy A table can occur only once A table can only connect out once one arrow leaving it All tables must be connected Columns in the join index Suppose that you joined Tables A through E in a join index called ABCDE If each table has two columns of data expect the join index to have a total of fourteen columns Adaptive Server IQ creates an addition...

Page 174: ...e join index ABCDEF to resolve any queries that involve The entire relationship Table A to Table D Table A to Table D to Table F Table B to Table D Table B to Table D to Table F Table D to Table F Table C to Table E Table E to Table F Table C to Table E to Table F However Adaptive Server IQ cannot use join index ABCDEF to resolve queries against for example Table E to Table D One to many relations...

Page 175: ...cust_id column So if you join customer id to sales_order cust_id the join relationship is one to many As you can see in the following example for every row in customer there are potentially many matching rows in sales_order select sales_order id sales_order cust_id customer lname from sales_order customer where sales_order cust_id customer id cust_id id lname 2583 101 101 Devlin 2001 101 101 Devli...

Page 176: ...ationship You need to create separate join indexes for each of these relationships A table can appear in the relationship hierarchy only once So for example you cannot predefine a join relationship query where Table A is joined to Table B Table B is joined to Table C and Table C is joined to Table A You can use predefined joins to query on the Table A to Table B and the Table C to Table A relation...

Page 177: ...gn key declarations in the CREATE TABLE commands in step 1 or in ALTER TABLE commands 3 Create column indexes for the tables being joined When Adaptive Server IQ creates a join index between tables the IQ column index types and data types already defined on the single tables are used in the join index 4 Load the data into the tables using the LOAD TABLE command You also can add data to existing ta...

Page 178: ...atabases For complete syntax of the CREATE TABLE ALTER TABLE LOAD TABLE INSERT INTO and SYNCHRONIZE commands see the Adaptive Server IQ Reference Manual The sections that follow give details on other steps in creating a join index Synchronizing join indexes The data in join index tables must be synchronized before you can use a join index Synchronization ensures that the data is loaded in the corr...

Page 179: ...so specify a relationship by relating a table to another join relationship Depending on the relationship you specify it either once or twice Key joins relate the primary key of one table to a foreign key in another table For key joins you must specify a PRIMARY KEY and FOREIGN KEY when you create or alter the underlying tables using the CREATE TABLE or ALTER TABLE command For all joins you specify...

Page 180: ...he sales_order table Therefore you designate cust_id in sales_order as a FOREIGN KEY that references the id column of the customer table The first example creates the customer table with the column id as its primary key To simplify the example other columns are represented here by ellipses CREATE TABLE DBA customer id integer NOT NULL PRIMARY KEY id Then you create the sales_order table with six c...

Page 181: ... FOREIGN KEY that references a PRIMARY KEY If you are joining equivalent values an equijoin in columns from two tables you specify an ON clause These rules conform to ANSI syntax requirements Specifying relationships when creating a join index For non key joins the order in which you specify tables when you create the join index determines the hierarchy of the join relationship between the tables ...

Page 182: ...described here in order to create the complete hierarchy shown in Figure 4 1 you would need to use key joins See Types of join hierarchies for more information When you create a join index a message in the log identifies the top table in the join For example 20691 Join Index join_on_tabletable created from the following join relations 20694 Table Name Relationship 20697 20696 1 join_on_table_a joi...

Page 183: ...omer table Remember that this is a key join based on the foreign key ky_so_customer which relates the cust_id column of sales_order to the primary key id of the customer table You can give the index any name you want This example names it ky_so_customer_join to identify the foreign key on which the key join relies CREATE JOIN INDEX ky_so_customer_join FOR customer FULL OUTER JOIN sales_order Examp...

Page 184: ... Double click the Add Join Index object in the right panel to open the Join Index editor 3 Highlight Unnamed in the Name box and enter a name for the index 4 From the Left Table Name dropdown select a table name Repeat for the Right Table Name 5 Select a Join Type from the dropdown If you select a type other than Natural specify the Join Columns 6 Click Advanced Properties to add a comment 7 If yo...

Page 185: ...th many branches in which each branch is directly related to one table in the middle In Figure 4 1 Tables D F and E form a very simple star join More commonly Table F would be at the center of many tables each of which is joined to Table F In a star join multiple tables are related to one table at the center of the join in a one to many relationship The one table at the center of the join represen...

Page 186: ...r 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 fin_code code UNENFORCED region char 7 NULL sales_rep integer NOT NULL REFERENCES DBA employee emp_id UNENFORCED PRIMARY KEY id As shown in the figure the sales_order table is at the center of the star join Each of its foreign key columns can cont...

Page 187: ... join index Once you have created a join index you are restricted in the types of changes you can make to the join index and its underlying tables and indexes You cannot drop any table that participates in a join index Likewise you cannot use ALTER TABLE to add drop or modify a column that participates in a join index In both cases you must first drop the join index Then you can either drop the ta...

Page 188: ... Inserting or deleting from tables in a join index You always insert or load into or delete from the underlying tables not the join index itself When you first create the join index Adaptive Server IQ synchronizes the joined tables automatically whether or not you have previously loaded data into the tables If you insert into or delete from a table that participates in an existing join index you m...

Page 189: ...ation on versioning see Chapter 8 Transactions and Versioning Estimating the size of a join index Adaptive Server IQ provides a stored procedure sp_iqestjoin to help you estimate the size of a join index You run this procedure for each pair of tables being joined Each time you run the procedure you must supply the following parameters Name of the first table to be joined Number of rows in the firs...

Page 190: ...lts 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 ...

Page 191: ...ive Server IQ table is a logical table it does not contain data All the information needed to resolve queries including data is contained in the Adaptive Server IQ indexes When you insert data into the columns in an IQ table you are not actually adding data to the columns in the table but rather to the column indexes You build indexes by inserting data on a table by table basis Import and export m...

Page 192: ...of values or directly from tables Interactive SQL supports the following output file formats File Format Description ASCII A text file one row per line with values separated by a delimiter String values are optionally enclosed in apostrophes single quotes This is the same as the format used by LOAD TABLE DBASEII DBASE II format DBASEIII DBASE III format DIF Data Interchange Format FIXED Data recor...

Page 193: ...eed INSERT permission for that table or view To delete data you need DELETE permission for that table or view To update data you need UPDATE permission The DBA can insert into or delete from any table The owner of a table has INSERT DELETE and UPDATE permission on it Permissions can be granted to and revoked from individual users groups or the public group For more information on permissions see C...

Page 194: ...on the command The redirection symbol must be followed by a file name In a command file the file name is then followed by the semicolon used as statement terminator The file is placed relative to the directory where DBISQL was started In this example output is redirected to the file empfile SELECT FROM employee empfile Do not enclose the file name in quotation marks Output redirection is most usef...

Page 195: ...ost common reason to extract data is for use in other software products The other software package may not understand NULL values however The DBISQL option NULLS allows you to choose how NULL values are output Alternatively you can use the IFNULL function to output a specific value whenever there is a NULL value For information on setting DBISQL options see SET OPTION statement in Adaptive Server ...

Page 196: ...e is no automatic savepoint If the load succeeds it commits automatically If the load fails it rolls back You cannot roll back a successful load of a Catalog Store table For more information on transaction processing see Chapter 8 Transactions and Versioning Summary of LOAD TABLE syntax The basic form of the LOAD TABLE statement is LOAD TABLE owner table name load specification FROM filename strin...

Page 197: ...lank stripping or blank padding ASCII is also used to fill numeric data time and date time fields In each case the conversion is the same if the value were first inserted as a character field then cast to the data type of the column in the table The input width value is an integer value indicating the fixed width in bytes of the input field in every record Binary fields that use a number of PREFIX...

Page 198: ... be characters at the end of rows or even entire fields in the input files that you do not want to add to the table As with the column spec definition FILLER allows you to specify ASCII fixed length of bytes variable length characters delimited by a separator and binary fields using PREFIX bytes FILLER clause syntax is as follows FILLER filler type filler type input width PREFIX 1 2 4 delimiter st...

Page 199: ...you do not Adaptive Server IQ reports an exception from hos_io Read This issues a PIPE_NOT_CONNECTED error which notifies Adaptive Server IQ that the pipe was shut down in an orderly manner rather than an uncontrolled disconnect See Microsoft documentation for details on these calls Specifying table wide format options You can specify several options that describe the format of input data FORMAT o...

Page 200: ...our input file it treats them as part of the data With quotes off you cannot include column delimiter characters in column values ESCAPES option Currently you must specify ESCAPES OFF The default of ESCAPES ON is provided for compatibility with Adaptive Server Anywhere this option may be supported in a future version With ESCAPES turned on if you omit a column spec definition for an input field ch...

Page 201: ...nd it can affect performance dramatically The default setting for BLOCK FACTOR is 10 000 Higher block factors generally improve the speed of I O operations However consider the following when setting this option If your source is a disk file memory considerations will determine the best setting for your system If your source is a tape either use the same blocking factor that was used when creating...

Page 202: ...ize You cannot specify this option along with the BLOCK FACTOR option The default setting for BLOCK SIZE is 500 000 which is high enough for input from disk files For tape files you should specify the same block size that was used when creating the tape You cannot specify BLOCK SIZE along with BLOCK FACTOR or with any fixed width input fields Example The following UNIX example specifies a BLOCK SI...

Page 203: ...y ASCII 8 FILLER 30 FROM C iq archive mill txt BLOCK FACTOR 1000 PREVIEW ON LIMIT 1000000 NOTIFY option Specifies that you be notified with a message each time the specified number of rows is inserted successfully into the table The default is every 100 000 rows Very frequent notifications can slow down your insert operation To turn off NOTIFY entirely set NOTIFY 0 See Interpreting notification me...

Page 204: ... expected column data type and starting position information to determine if you need to use an insert conversion option on a column and or where and how much filler to use Note PREVIEW ON helps you determine if a load is correct It does not stop the load from occurring ROW DELIMITED BY option Specifies a string up to 4 bytes in length that indicates the end of an input record You can use this opt...

Page 205: ...ewline character followed by the carriage return character You may need to specify this as the delimiter string for either this option or FILLER Example The following Windows NT example sets the column delimiter for the l_orderkey column to tab and the row delimiter to newline x0a followed by carriage return x0d LOAD TABLE mm l_orderkey x09 l_quantity ASCII 4 FILLER 6 l_shipdate DATE YYYY MM DD FR...

Page 206: ...allow NULLs in the data you must specify BINARY WITH NULL BYTE in the column specification You cannot include NULL in the column spec in any other way For the sake of consistency with the data being loaded you can specify BINARY WITH NULL BYTE even when loading into a table column that does not allow NULLs as specified in CREATE TABLE or ALTER TABLE However if you try to load any data into a colum...

Page 207: ...270 n File remote rip tpcd_data scale_1 partsupp tbl Each time it inserts the number of records specified in the NOTIFY load option Adaptive Server IQ sends a message like this 1998 07 28 13 03 49 0002 20897 100000 Records 2 Seconds Mem 469mb M470 Main Blks U63137 6 Buffers U12578 L7 Temporary Blks U273 0 Buffers U1987 L1960 Main I L331224 P22 O D25967 P7805 C D0 Temporary I L25240 P8 O D4749 P0 C...

Page 208: ... M mb The maximum number of megabytes used by this IQ server since it was started Item Description U Number of blocks in use Percentage of database filled Buffers U Number of buffers in use Note This value will grow to maximum number of buffers that fit in the main buffer cache The number increments whenever a buffer is allocated but only decrements when a buffer is destroyed not when it is unlock...

Page 209: ...fault frequency of notification messages during loads or omit these message See the chapter Database Options in the Adaptive Server IQ Reference Manual for details The NOTIFY option in the LOAD command overrides the Notify_Modulus setting Item Description Main I L Number of logical file reads P Number of physical file reads O D Number of times a buffer was destroyed P Number of physical writes C D...

Page 210: ...e VALUES expression Adaptive Server IQ inserts the first value you specify into the first column you specify the second value you specify into the second column and so on If you omit the list of column names the values are inserted into the table columns in the order in which the columns were created the same order as SELECT would retrieve Adaptive Server IQ inserts the row into the table wherever...

Page 211: ...LE statement or in other ways such as using a primary key for example Example The following example adds 1995 06 09 into the l_shipdate column and 123 into the l_orderkey column in the lineitem table INSERT INTO lineitem l_shipdate l_orderkey VALUES 1995 06 09 123 If you are inserting more than a small number of data rows it is more efficient to insert selected rows directly from a database as des...

Page 212: ...you are currently connected to Adaptive Server IQ inserts the data into all indexes for the destination columns The columns in the table in the select list and in the table must have the same or compatible data types In other words the selection s value must be or must be able to be converted to the data type of the table s column See Converting data on insertion for more information about data ty...

Page 213: ... connectivity libraries must be installed on your system and the load library path environment variable for your platform must point to them The Adaptive Server Enterprise server to which you are connecting must exist in the interfaces file on the local machine You must have read permission on the source ASE or pre Version 12 IQ database and INSERT permission on the target IQ 12 database To insert...

Page 214: ...is 1 INSERT INTO lineitem l_shipdate l_orderkey LOCATION detroit asiqdb SELECT l_shipdate l_orderkey FROM lineitem WHERE l_orderkey 1 Note If you use START ROW ID and you select fewer columns than exist in the destination table the columns in remaining rows of the destination table will be NULLs if NULLs are legal values See Partial width insertions for more information Importing data from pre Ver...

Page 215: ... the join index for you automatically It does not matter whether you create the join index before or after loading The order also does not affect performance of the load or synchronization Once you have created a join index however if you insert or load data into any of its underlying tables except the top table in the join hierarchy you must synchronize it explicitly To do so use the SYNCHRONIZE ...

Page 216: ...is a member of the join index it can t be updated until the join index is synchronized For more information on join indexes see Chapter 4 Adaptive Server IQ Indexes For more information on transaction processing see Chapter 8 Transactions and Versioning Inserting into primary and foreign key columns You load or insert data into primary key and foreign key columns just as you would into any other c...

Page 217: ...if you have memory limitations For example you can insert data into a few columns at a time using separate LOAD TABLE or INSERT statements for each group of indexes and using the START ROW ID option to keep the ROW IDs consistent and the memory requirement lower You may want to do this if you are inserting into a very wide table and do not have enough free memory to populate all the indexes at one...

Page 218: ... shading patterns represent data inserted into columns in two separate insert operations Figure 5 1 Using START ROW ID with partial width insertions Note Do not try to perform a partial width insertion using the INSERT VALUES command format Because you cannot specify START ROW ID using this format the problem shown in the figure results Partial width insertion rules Column indexes that are not inc...

Page 219: ...correct insertion of four columns from the file tt t into the indexes on the lineitem table It inserts the first two columns with one LOAD TABLE statement and the second two columns with another LOAD TABLE statement but does not use the START ROW ID option to align the additional columns LOAD TABLE lineitem l_partkey ASCII 4 l_suppkey ASCII 4 FILLER 13 FROM d1 MILL1 tt t PREVIEW ON NOTIFY 1000 LOA...

Page 220: ...lineitem l_partkey ASCII 4 l_suppkey ASCII 4 FILLER 13 FROM d1 MILL1 tt t PREVIEW ON NOTIFY 1000 SELECT rowid lineitem FROM lineitem l_orderkey l_partkey l_suppkey l_quantity rowid lineitem NULL 1 12 NULL 1 NULL 2 37 NULL 2 NULL 3 28 NULL 3 NULL 4 13 NULL 4 NULL 5 9 NULL 5 5 rows affected LOAD TABLE lineitem FILLER 8 l_quantity ASCII 6 l_orderkey ASCII 6 FILLER 1 FROM d1 MILL1 tt t PREVIEW ON NOTI...

Page 221: ...ty and that partsupp is not part of any join index The data for ps_value is calculated from ps_availqty so the ps_availqty column must already contain data Therefore to insert data into the partsupp table do two inserts one for ps_availqty and ps_partkey and then one for ps_value First insert the data for partsupp directly from an ASCII file named tt t LOAD TABLE partsupp ps_partkey ASCII 6 ps_ava...

Page 222: ...anging the START ROW ID value to the new row number 3 Previewing partial width inserts Given the possibility of errors if you do a partial width insert incorrectly it is a good idea to preview these inserts The PREVIEW load option lets you see the layout of input in the destination table This option is available in LOAD TABLE but not in the INSERT command Converting data on insertion The data you ...

Page 223: ...Server IQ data types are fully compatible with Adaptive Server Anywhere and Adaptive Server Enterprise data types of the same name there are some differences For details on compatibility see Matching Adaptive Server Anywhere data types and Matching Adaptive Server Enterprise data types For compatibility among versions a few data types have been defined as synonyms of other data types DECIMAL is a ...

Page 224: ...u must convert certain data types before inserting or loading them For details see Migrating Data from Previous Versions in the Adaptive Server IQ Installation and Configuration Guide Load conversion options The following table lists the conversion options for the LOAD TABLE statement in alphabetical order and gives a brief description of what each option does For a detailed description of each op...

Page 225: ...s whenever you compare or compute values for differing data types The following tables show Which conversions Adaptive Server IQ does implicitly I Which conversions you must do explicitly E Which conversions are unsupported U These conversions apply to data within an Adaptive Server IQ database or coming from an Adaptive Server Anywhere database or any other database connected as a Specialty Data ...

Page 226: ...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 Figure 5 2 IQ conversions for comparison operations ...

Page 227: ... 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 ...

Page 228: ...olumn1 is 15 bytes wide and you do not specify the ASCII conversion option Adaptive Server IQ assumes the input column is 15 bytes wide This means that Adaptive Server IQ reads all of input_column1 plus 5 bytes into the next column in the input file and inserts this value into destination_column1 So the value inserts into destination_column1 and all subsequent columns are incorrect To prevent such...

Page 229: ...e following table illustrates how the ASCII conversion option works with the Adaptive Server IQ data types The example inserts the data from the flat ASCII file shipinfo t into the Adaptive Server IQ table lineitem and summarizes the content and format of the input data and the table Table 5 6 Input file conversion example For the l_shipmode column you insert ASCII data into an ASCII column that h...

Page 230: ... format of the input data The DATE format is used internally to interpret the input it does not affect the storage or output format of the data See the ASCII conversion format for more information Example In this Windows NT example data for the l_shipdate column is converted from the specified format into binary The 1 byte FILLER skips over carriage returns in the input file LOAD TABLE lineitem l_...

Page 231: ...ot store any other characters that might appear in the input data However if the input data contains other characters for example slashes dashes or blanks to separate the month day and year the DATE format must show where those characters appear so they can be ignored mm or MM Represents number of month Always use leading zeros for number of the month where appropriate for example 05 for May If yo...

Page 232: ...IME conversion option to insert ASCII data that is stored in a fixed format into a TIME or TIMESTAMP or DATETIME column This option converts the ASCII data input to binary and specifies the format of the input data The DATETIME format is used internally to interpret the input it does not affect the storage or output format of the data See the ASCII conversion format for more information Note For c...

Page 233: ...LOCK FACTOR 1000 PREVIEW ON In this UNIX example the destination columns contain TIME data but the input data is DATETIME You use the TIME conversion option and use FILLER to skip over the date portion LOAD TABLE customer open_time TIME hh mmaa close_time TIME hh mmaa FILLER 9 FROM d1 MILL1 tt t BLOCK FACTOR 1000 PREVIEW ON Specifying the format for DATETIME conversions Specify the format of the D...

Page 234: ...g zeros for minute where appropriate for example 08 for 8 minutes ss ssssss Represents seconds and fraction of a second aa Represents the a m or p m designation pp Represents the p m designation only if needed This is incompatible with Adaptive Server IQ releases prior to 12 0 previously pp was synonymous with aa hh Adaptive Server IQ assumes zero for minutes and seconds For example if the DATETIM...

Page 235: ...o skip over them To indicate whether a particular value is a m or p m the input data must contain an upper or lowercase a or p in a consistent place To indicate where Adaptive Server IQ should look for the a m or p m designation put a lowercase only aa or pp in the appropriate place in the format specification aa specifies a m p m is always indicated while pp specifies that pm is indicated only if...

Page 236: ...y ASCII 3 l_shipdate date MM DD YY l_suppkey ascii 5 FILLER 1 FROM C MILL1 tt t PREVIEW ON Other factors affecting the display of data Whenever Adaptive Server IQ requires an explicit or implicit conversion from one data type to another during a query or insert it always truncates the results The following describes such situations When you explicitly convert data from a higher scale to a lower sc...

Page 237: ...hich Adaptive Server IQ data types are compatible with Adaptive Server Enterprise data types Here are some general rules Adaptive Server IQ character string types accept any Adaptive Server Enterprise character string type Adaptive Server IQ exact numeric types accept any Adaptive Server Enterprise number types However if the Adaptive Server IQ data type holds a smaller amount of data than the Ada...

Page 238: ...a BIGINT an unsigned integer UNSIGNED INT or an UNSIGNED BIGINT datatype With UNSIGNED INT the last bit is used as part of the value There is no positive or negative indication all numbers are assumed to be positive so the value can go up to 4 294 967 295 numeric DECIMAL or NUMERIC with appropriate precision If the precision of the Adaptive Server IQdatatype you define is too small to store the Ad...

Page 239: ...r Enterprise Datatype Adaptive Server IQ Datatype Notes char CHAR Adaptive Server IQ and Adaptive Server Enterprise character char or CHAR datatypes are the same except that Adaptive Server IQ can handle NULLs If you want an Adaptive Server IQ CHAR column to exactly match an Adaptive Server Enterprise char column specify Adaptive Server IQ column as NOT NULL Adaptive Server IQ default allows NULLs...

Page 240: ...in one 1 000 000th of a second Adaptive Server IQ TIMESTAMP or DATETIME columns maintain date and time of day values in two 4 byte integers 4 bytes for number of days since 1 1 0 and 4 bytes for time of day based on 24 hour clock accurate to within one 10 000th of a second Adaptive Server IQ automatically handles the conversion Adaptive Server IQ also has a separate DATE datatype a single 4 byte i...

Page 241: ...daptive Server IQ optimizes loading of data for a large or small set of distinct values based on parameters you specify when you create your database and tables Parameters that affect load optimization include The UNIQUE and IQ UNIQUE options and the data type and width of the column all specified in the CREATE TABLE or ALTER TABLE command The IQ PAGE SIZE specified in the CREATE DATABASE command ...

Page 242: ...ble Increasing memory can improve load performance See Chapter 2 Running Adaptive Server IQ for command line options that affect performance Adjusting your environment at load time When you load data you can adjust several factors to improve load performance Use the LOAD TABLE command whenever you have access to raw data in ASCII or binary format especially for all loads of over a hundred rows The...

Page 243: ...ta into different tables at the same time concurrent updates can slow performance Schedule major updates for low usage times Although many users can query a table while it is being updated query users require CPU cycles disk space and memory You will want these resources available to make your inserts go faster If you are using the INSERT statement run DBISQL or the client application on the same ...

Page 244: ...d an error message appears For example if one of the values being added is the wrong data type or if it violates a constraint defined for one of the columns or data types involved the update does not take place UPDATE syntax See the Adaptive Server IQ Reference Manual for complete UPDATE syntax A simplified version of the syntax is UPDATE table name SET column_name expression WHERE search conditio...

Page 245: ... SET clause The WHERE clause The WHERE clause specifies the rows to be updated For example the following statement replaces the One Size Fits All Tee Shirt with an Extra Large Tee Shirt UPDATE product SET size Extra Large WHERE name Tee Shirt AND size One Size Fits All The FROM clause You can use a FROM clause to pull data from one or more tables into the table you are updating Deleting data To re...

Page 246: ...cess work you need An operating Replication Server system which includes a special database called the Replication Server System Database RSSD This database contains replication definitions that describe the transactions in the source database and how they should be replicated to the replicate databases A specially prepared replicate database on an Adaptive Server Enterprise database This replicat...

Page 247: ...HAPTER 5 Moving Data In and Out of Databases 227 For information on using Distribution Director and on setting up an appropriate Replication Server environment see the Distribution Director User s Guide ...

Page 248: ...Importing data by replication 228 ...

Page 249: ...ay to build logic into the database than SQL stored procedures For information on JDBC see Data Access Using JDBC in the Adaptive Server Anywhere User s Guide Overview of procedures Procedures store procedural SQL statements in a database for use by all applications Procedures can include control statements that allow repetition LOOP statement and conditional execution IF statement and CASE statem...

Page 250: ...e of the client machines When a procedure is created it is checked for correct syntax and then stored in the system tables The first time it is required by any application it is retrieved from the system tables and compiled into the virtual memory of the server and executed from there Subsequent executions of the same procedure will result in immediate execution since the compiled copy is retained...

Page 251: ... Procedures folder and clicking Add Procedure Function Wizard The Wizard walks you through the process Alternatively click Add Procedure Function Template which places you immediately in the last window of the Wizard the Procedure window in which you enter the code for the procedure If you are using a tool other than DBISQL or Sybase Central follow the instructions for your tool You may need to ch...

Page 252: ...ram or they can be called by other procedures For more information see Adaptive Server IQ Reference Manual The following statement calls the new_dept procedure to insert an Eastern Sales department CALL new_dept 210 Eastern Sales 902 After this call you may wish to check the department table to see that the new department has been added The new_dept procedure can be called by all users who have be...

Page 253: ...Granting permissions on procedures on page 361 Returning procedure results in parameters Procedures can return results to the calling environment in one of the following ways Individual values are returned as OUT or INOUT parameters Result sets can be returned A single result can be returned using a RETURN statement This section describes how to return results from procedures as parameters The fol...

Page 254: ...l parameters procedures can return information in result sets A result set is typically the result of a query The following procedure returns a result set containing the salary for each employee in a given department CREATE PROCEDURE SalaryList IN department_id INT RESULT Employee ID INT Salary NUMERIC 20 3 BEGIN SELECT emp_id salary FROM employee WHERE employee dept_id department_id END If this p...

Page 255: ...res on page 251 Introduction to user defined functions User defined functions are a class of procedures that return a single value to the calling environment This section introduces creating using and dropping user defined functions Creating user defined functions User defined functions are created using the CREATE FUNCTION statement You must have RESOURCE authority in order to create a user defin...

Page 256: ...EDURE statement The following are distinctive differences No IN OUT or INOUT keywords are required as all parameters are IN parameters The RETURNS clause is required to specify the data type being returned The RETURN statement is required to specify the value being returned Calling user defined functions A user defined function can be used subject to permissions in any place that a built in non ag...

Page 257: ...ase DROP FUNCTION fullname Permissions to execute user defined functions A user defined function 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 For example the creator of the function fullname could allow another_user to use fullname with the statement GRANT EXECUTE ON fullna...

Page 258: ...me emp_lname dept_id start_date VALUES 2054 Edward Baer 220 1998 08 15 INSERT INTO sales_order id cust_id order_date fin_code_id region sales_rep VALUES 41880 717 1998 08 24 BU PA 2054 INSERT INTO sales_order id cust_id order_date fin_code_id region sales_rep VALUES 418898 021 1998 08 25 BU PA 2054 COMMIT INSERT INTO department dept_id dept_name VALUES 220 Eastern Sales go UPDATE employee SET dept...

Page 259: ... END in batches Compound statements can include declarations of variables exceptions temporary tables or cursors inside the compound statement The following batch creates a table only if a table of that name does not already exist BEGIN IF NOT EXISTS SELECT FROM SYSTABLE WHERE table_name t1 THEN CREATE TABLE t1 firstcol INT PRIMARY KEY secondcol CHAR 30 ELSE MESSAGE Table t1 already exists END IF ...

Page 260: ...ound statement allows a set of SQL statements to be grouped together and treated as a unit SQL statements within a compound statement should be separated with semicolons 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...

Page 261: ... compound statement The following user defined function illustrates local declarations of variables The customer table includes some Canadian customers sprinkled among those from the USA but there is no country column The user defined function nationality uses the fact that the US zip code is numeric while the Canadian postal code begins with a letter to distinguish Canadian and US customers CREAT...

Page 262: ...3629 15 bad_data END In this example the two INSERT statements are part of an atomic compound statement They must either succeed or fail as one The first INSERT statement would succeed The second one causes a data conversion error since the value being assigned to the ship_date column cannot be converted to a date The atomic compound statement fails and the effect of both INSERT statements is undo...

Page 263: ...ments are allowed within procedures including the following SELECT UPDATE DELETE INSERT and SET VARIABLE The CALL statement to execute other procedures Control statements see SQL statements allowed in procedures on page 243 Cursor statements see Using cursors in procedures on page 251 Exception handling statements see Using exception handlers in procedures on page 261 The EXECUTE IMMEDIATE stateme...

Page 264: ...nd could be given a new value by the procedure Default values can be assigned to procedure parameters in the CREATE PROCEDURE statement The default value must be a constant which may be NULL For example the following procedure uses the NULL default for an IN parameter to avoid executing a query that would have no meaning CREATE PROCEDURE CustomerProducts IN customer_id INTEGER DEFAULT NULL RESULT ...

Page 265: ...TE VARIABLE V1 INT CREATE VARIABLE V2 INT CREATE VARIABLE V3 INT The procedure SampleProc may be called supplying only the first parameter as follows CALL SampleProc V1 in which case the default values are used for var2 and var3 A more flexible method of calling procedures with optional arguments is to pass the parameters by name The SampleProc procedure may be called as follows CALL SampleProc va...

Page 266: ...ack as arguments to the procedure In the latter case results are passed back as result sets Procedures can also return a single value given in the RETURN statement For simple examples of how to return results from procedures see Introduction to procedures For more detailed information see the following sections Returning a value using the RETURN statement A single value can be returned to the call...

Page 267: ...t artificial procedure returns a value in an OUT parameter that is assigned using a SET statement CREATE PROCEDURE greater IN a INT IN b INT OUT c INT BEGIN IF a b THEN SET c a ELSE SET c b END IF END Note The preceding example is artificial generally a function is easier to use than a procedure when only one result is required Using single row SELECT statements Single row queries retrieve at most...

Page 268: ...lowing CREATE PROCEDURE OrderCount IN customer_ID INT OUT Orders INT BEGIN SELECT COUNT DBA sales_order id INTO Orders FROM DBA customer KEY LEFT OUTER JOIN DBA sales_order WHERE DBA customer id customer_ID END You can test this procedure in DBISQL using the following statements which show the number of orders placed by the customer with ID 102 CREATE VARIABLE orders INT CALL OrderCount 102 orders...

Page 269: ...s quantity product unit_price AS NUMERIC 14 2 AS value FROM customer INNER JOIN sales_order INNER JOIN sales_order_items INNER JOIN product GROUP BY company_name ORDER BY value DESC END Type the following CALL ListCustomerValue Notes The number of variables in the RESULT list must match the number of the SELECT list items Automatic data type conversion is carried out where possible if data types d...

Page 270: ...ta types listed in the RESULT list The following procedure lists the names of all employees customers and contacts listed in the database CREATE PROCEDURE ListPeople RESULT lname CHAR 36 fname CHAR 36 BEGIN SELECT emp_lname emp_fname FROM employee SELECT lname fname FROM customer SELECT last_name first_name FROM contact END Notes To test this procedure in DBISQL enter the following statement CALL ...

Page 271: ...ursor for the result set is opened but before any rows are returned in order to get the proper shape of result set For information about the DESCRIBE statement see DESCRIBE statement in Adaptive Server IQ Reference Manual ODBC Variable result set procedures can be used by ODBC applications The proper description of the variable result sets is carried out by the Adaptive Server IQ ODBC driver Open ...

Page 272: ... the WITH HOLD clause will be kept open for subsequent transactions until they are explicitly closed Cursor positioning A cursor can be positioned at one of three places On a row Before the first row After the last row When a cursor is opened it is positioned before the first row The cursor position can be moved using the FETCH command see FETCH statement in Adaptive Server IQ Reference Manual It ...

Page 273: ... variables to hold each company name and its value DECLARE ThisName CHAR 36 DECLARE ThisValue INT 3 Declare the cursor ThisCompany for the query DECLARE ThisCompany CURSOR FOR SELECT company_name CAST sum sales_order_items quantity product unit_price AS INTEGER AS value FROM customer INNER JOIN sales_order INNER JOIN sales_order_items INNER JOIN product GROUP BY company_name 4 Initialize the value...

Page 274: ...he loop The ThisCompany cursor is opened The LOOP statement loops over each row of the query placing each company name in turn into the variables ThisName and ThisValue If ThisValue is greater than the current top value TopCompany and TopValue are reset to ThisName and ThisValue The cursor is closed at the end of the procedure The LOOP construct in the TopCompanyValue procedure is a standard form ...

Page 275: ...status descriptions Whenever a SQL statement is executed a value is placed in special procedure variables called SQLSTATE and SQLCODE That value indicates whether or not there were any unusual conditions encountered while the statement was being performed You can check the value of SQLSTATE or SQLCODE in an IF statement following a SQL statement and take actions depending on whether the statement ...

Page 276: ... in the CREATE PROCEDURE statement the procedure carries on executing after an error resuming at the statement following the one causing the error Default error handling Generally if a SQL statement in a procedure fails the procedure terminates execution and control is returned to the application program with an appropriate setting for the SQLSTATE and SQLCODE values This is true even if the error...

Page 277: ...message window of the server then displays the following Hello from OuterProc Hello from InnerProc No statements following the SIGNAL statement in InnerProc are executed InnerProc immediately passes control back to the calling environment which in this case is the procedure OuterProc No statements following the CALL statement in OuterProc are executed The error condition is returned to the calling...

Page 278: ... refers to the next new statement not a statement part The following statements are considered error handling statements IF SELECT variable CASE LOOP LEAVE CONTINUE CALL EXECUTE SIGNAL RESIGNAL DECLARE The following example illustrates how this works Drop the procedures Remember to drop both the InnerProc and OuterProc procedures before continuing with the tutorial You can do this by entering the ...

Page 279: ...ollowing statement executes the OuterProc procedure CALL OuterProc The message window of the server then displays the following Hello from OuterProc Hello from InnerProc SQLSTATE set to 52003 in OuterProc The execution path is as follows 1 OuterProc executes and calls InnerProc 2 In InnerProc the SIGNAL statement signals an error 3 The MESSAGE statement is not an error handling statement so contro...

Page 280: ...emonstration procedures illustrate default handling of warnings These demonstration procedures are based on those used in Default error handling in procedures on page 256 In this case the SIGNAL statement generates a row not found condition which is a warning rather than an error CREATE PROCEDURE OuterProc BEGIN MESSAGE Hello from OuterProc CALL InnerProc MESSAGE SQLSTATE set to SQLSTATE in OuterP...

Page 281: ...ncountered in a nested compound statement or in a procedure that has been invoked anywhere within the compound statement Drop the procedures Remember to drop both the InnerProc and OuterProc procedures before continuing with the tutorial You can do this by entering the following commands in the command window DROP PROCEDURE OUTERPROC DROP PROCEDURE INNERPROC The demonstration procedures used to il...

Page 282: ...erProc The message window of the server then displays the following Hello from OuterProc Hello from InnerProc Column not found handling SQLSTATE set to 00000 in OuterProc Notes The lines following the SIGNAL statement in InnerProc are not executed instead the EXCEPTION statements are executed As the error encountered was a column not found error the MESSAGE statement included to handle the error i...

Page 283: ...nd exception handlers The code following a statement that causes an error is not executed unless an ON EXCEPTION RESUME clause is included in a procedure definition You can use nested compound statements to give you more control over which statements are and are not executed following an error Drop the procedures Remember to drop both the InnerProc and OuterProc procedures before continuing with t...

Page 284: ...uter compound statement message is printed If an error other than column not found is encountered in the inner compound statement the exception handler executes the RESIGNAL statement The RESIGNAL statement passes control directly back to the calling environment and the remainder of the outer compound statement is not executed Using the EXECUTE IMMEDIATE statement in procedures The EXECUTE IMMEDIA...

Page 285: ...Also all savepoints within an atomic operation are released when the atomic operation completes Some tips for writing procedures This section provides some pointers for developing procedures Check if you need to change the command delimiter You do not need to change the command delimiter in DBISQL or Sybase Central when you are writing procedures However if you are creating and testing procedures ...

Page 286: ..._1 or user_1 must be a member of a group directly or indirectly that is the owner of Table_B If neither condition is met a table not found message results when the procedure is called You can minimize the inconvenience of long fully qualified names by using a correlation name to provide a convenient name to use for the table within a statement Correlation names are described in FROM clause in Adap...

Page 287: ...re Call the procedure CALL MESSAGE_TEST Test Message After calling the procedure on a Windows NT system double click the server icon in the system tray to ensure that the message was passed properly to the server SELECT GLOBALVAR Statements allowed in batches The following statements are not allowed in batches CONNECT or DISCONNECT statement ALTER PROCEDURE or ALTER FUNCTION statement DBISQL comma...

Page 288: ... for the result set is required only in the first SELECT statement as the server uses the first SELECT statement in the batch to describe the result set A RESUME statement is required following each query to retrieve the next result set The following is not a valid batch as the two queries return different result sets IF EXISTS SELECT FROM systable WHERE table_name employee THEN SELECTemp_lname AS...

Page 289: ...Adaptive Server IQ Reference Manual For a full description of the CREATE FUNCTION statement syntax for external calls see CREATE FUNCTION statement in Adaptive Server IQ Reference Manual Note You must have DBA permissions in order to create external procedures or functions This requirement is more strict than the RESOURCE permissions required for creating other procedures or functions Syntax A pro...

Page 290: ...sed to the external function and any parameters modified by the function are returned to the calling environment in OUT or INOUT parameters External function declarations When an external function is called a stack is fabricated with the arguments or argument references in the case of INOUT or OUT parameters and the DLL is called Only the following data types can be passed to an external library C...

Page 291: ...oat xvar Procedure parameters that are IN parameters are passed to the external function by value For example the procedure CREATE PROCEDURE dll_proc IN xvar REAL EXTERNAL NAME function_name library dll has an associated external function parameter declaration of function_name float xvar Character data types are an exception to IN parameters being passed They are always passed by reference whether...

Page 292: ...space If the external function writes beyond the 256 bytes including the ending null character it is writing over data structures in the server When the entry point returns the parameter buffers are translated back into their server data structure string equivalents based on the strlen value of the buffer The external function should be sure to null terminate any output string parameters OUT param...

Page 293: ...usiness rules that maintain the consistency of data between tables Adaptive Server IQ supports stored procedures and JDBC which allow you detailed control over how data gets entered into the database Procedures are discussed in Chapter 6 Using Procedures and Batches See the Adaptive Server Anywhere User s Guide for information on JDBC How data can become invalid Here are a few examples of how the ...

Page 294: ...ecause IQ data typically is entered by only a few users and often loaded directly from other databases IQ databases tend to be less vulnerable than OLTP databases to the kinds of errors that can cause invalid data You should declare any constraints that apply whether Adaptive Server IQ enforces them or not By declaring constraints you ensure that you understand your data requirements and are desig...

Page 295: ...ts actually modify the information in a database An existing row of a table may be deleted using the DELETE statement A new row may be inserted into a table using the INSERT statement Data integrity tools To assist in maintaining data integrity you can use data constraints and constraints that specify the referential structure of the database Constraints You can use several types of constraints on...

Page 296: ... not enforce multi column primary keys Referential integrity Keeps track of the foreign keys that define the relationships between tables All foreign key values either should match a value in the corresponding primary key or contain the NULL value if they are defined to allow NULL Adaptive Server IQ does not enforce foreign keys however For more information about referential integrity see Declarin...

Page 297: ...ed in the table is correct and to provide information to Adaptive Server IQ that boosts performance Using UNIQUE constraints on columns or tables The UNIQUE constraint specifies that one or more columns uniquely identify each row in the table If you apply the UNIQUE constraint to a single column Adaptive Server IQ enforces this condition If multiple columns are required to uniquely identify a row ...

Page 298: ... order to be reasonable or they may be more rigid rules that reflect organization policies and procedures CHECK conditions on individual column values are useful when only a restricted range of values are valid for that column Here are some examples Example 1 You can specify a particular formatting requirement If a table has a column for phone numbers you can specify that they all be entered in th...

Page 299: ...forced CHECK conditions to user defined data types Columns defined on those data types inherit the CHECK conditions A CHECK condition explicitly specified for the column overrides that from the user defined data type When defining a CHECK condition on a user defined data type any variable prefixed with the sign is replaced by the name of the column when the CHECK condition is evaluated For example...

Page 300: ...that two values in a row being entered or modified have a proper relation to each other Column CHECK conditions are held individually in the system tables and can be replaced or deleted individually This is more flexible behavior and CHECK conditions on individual columns are recommended where possible For example in a library database the date_returned column for a particular entry must be later ...

Page 301: ...n new CHECK conditions All CHECK conditions on a table including CHECK conditions on all its columns and CHECK conditions inherited from user defined data types are removed using the ALTER TABLE statement with the DELETE CHECK clause as follows ALTER TABLE table_name DELETE CHECK Deleting a column from a table does not delete CHECK conditions associated with the column that are held in the table c...

Page 302: ...items table in the sample database uses two columns to define a primary key This table holds information about items ordered One column contains an id specifying an order but there may be several items on each order so this column by itself cannot be a primary key An additional line_id columns identifies which line corresponding to the item The two columns id and line_id taken together specify an ...

Page 303: ...tails see the ALTER TABLE statement in Adaptive Server IQ Reference Manual Some application development systems and database design tools allow you to create and alter database tables If you are using such a system you may not have to enter the CREATE TABLE or ALTER TABLE command explicitly the application generates the statement itself from the information you provide For information on creating ...

Page 304: ...reference Example 2 Suppose the database also contained an office table listing office locations The employee table might have a foreign key for the office table that indicates where the employee s office is located The database designer may wish to allow for an office location not being assigned at the time the employee is hired In this case the foreign key should allow the NULL value for when th...

Page 305: ...n 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 SYS SYSFOREIGNKEYS This view presents the foreign key information from the two tables SYS SYSFOREIGNKEY and SYS SYSFKCO...

Page 306: ...Integrity rules in the system tables 286 ...

Page 307: ...failure A crucial aspect of transaction processing is its ability to isolate users from the effect of other users transactions Adaptive Server IQ s approach to transaction processing called snapshot versioning supports the highest level of isolation recognized by ISO Introduction to transactions Transactions are simply groups of SQL statements Each transaction performs a task that changes your dat...

Page 308: ...se contains correct information Note Adaptive Server IQ processes transactions quite differently from the way Adaptive Server Anywhere does when it operates without IQ This chapter describes how Adaptive Server IQ handles transactions If you are working in an Anywhere only database see the Adaptive Server Anywhere User s Guide for information on transactions and locking Using transactions Adaptive...

Page 309: ...MIT otherwise it undoes your uncommitted changes with a ROLLBACK statement Adaptive Server IQ also supports Transact SQL commands such as begin transaction for compatibility with Adaptive Server Enterprise For further information see Transact SQL Compatibility in the Adaptive Server Anywhere User s Guide Committing a transaction writes data to disk When you execute a write operation Adaptive Serve...

Page 310: ...t Adaptive Server IQ s approach to concurrency is designed especially for the data warehouse Typically in a data warehouse environment many users need to read from the database but only the DBA needs to update it However there is often a need to be able to make those updates while other users continue to request and receive query results Adaptive Server IQ allows many simultaneous connections by m...

Page 311: ...eople These people may need frequent access to the information To avoid impeding their work the database server must be able to process many transactions at the same time Moreover many sites also require frequent updates to the database In high availability sites the DBA cannot postpone insertions and deletions to a time when exclusive access is possible Similarly it is important to be able to bac...

Page 312: ...serve system resources A given IQ table may consist of millions of pages of data When you update that table you may be writing to only a small percentage of those pages It would require a vast amount of disk space to maintain a complete copy of each version of an entire table Adaptive Server IQ saves on disk space by allowing table versions to share pages that are not being updated One writer and ...

Page 313: ...e 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 ...

Page 314: ...le 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 At the same time other users can execute transactions that issue queries to both of these tables or to any other tables in the database In an IQ Multiplex database read write users must all connect to the write server whereas re...

Page 315: ...mmitted version of the database as of the time the transaction begins It uses that version until the transaction commits The time a transaction begins is called its Start Timestamp The start timestamp can be any time before the transaction s first read Any insertions and deletions the transaction makes are reflected in the snapshot Thus for the user executing a transaction the image in the snapsho...

Page 316: ... use committed data The data that a writer sees changes only according to the changes he or she makes no other transaction can change what a writer sees until the writer s transaction commits For example in Figure 8 4 User 1 inserts some data then does a query and then deletes some data Those query results reflect the insertions that User 1 has just made Other transactions that begin after User 1 ...

Page 317: ... 1 s transaction began with because that is the latest committed version of the data Figure 8 5 shows what happens in the second instance This time User 2 s first read transaction Tr1 commits after User 1 s write transaction When User 2 s second transaction Tr2 begins it uses a new version that reflects the committed data from User 1 Figure 8 5 Effect of read transaction committing Hold cursors sp...

Page 318: ...atabase is called a global temporary table A global temporary table is accessible to all users with the appropriate permissions Each user has his or her own instance of the table however only one user ever sees a given set of rows By default a global temporary table is deleted at the next COMMIT You can override this default by specifying ON COMMIT PRESERVE ROWS when you create the temporary table...

Page 319: ...perations do not interfere with each other How locking works All Adaptive Server IQ locks occur automatically based on the type of operation a user requests You do not need to request a lock explicitly The transaction that has access to the table is said to hold the lock When a table is locked in Adaptive Server IQ no other transaction can have write access to it but any transaction can have read ...

Page 320: ...sults It ensures for example that a table column does not disappear from the database while you are selecting data from that column CREATE DROP and ALTER commands have the following special properties They cannot start while any other transaction is using the table or index they are modifying They cannot start while any other DDL command is operating in the database However this restriction is in ...

Page 321: ...Versioning of temporary tables for special rules regarding temporary tables When one transaction issues a DDL command on a given table or index any other transaction that began before the DDL transaction commits and that tries to access that table receives an error When this error occurs any additional attempts to read or write to the table in the current transaction will fail If a transaction mod...

Page 322: ...n important aspect of transaction processing is the database server s ability to isolate an operation ANSI standards define four levels of isolation Each higher level provides transactions a greater degree of isolation from other transactions and thus a greater assurance that the database remains internally consistent The isolation level controls the degree to which operations and data in one tran...

Page 323: ...ats the initial read and obtains a different set of data Lost Update In an application that uses cursors Transaction A writes a change for a set of data Transaction B then saves an update that is based on earlier data Transaction A s changes are completely lost Adaptive Server IQ protects you from all of these inconsistencies by ensuring that only one user can modify a table at any given time by k...

Page 324: ... IQ System Administration Adaptive Server IQ is an OLAP database A single OLAP transaction can change thousands or millions of rows of data For this reason Adaptive Server IQ does not wait for a checkpoint to occur to perform physical writes It writes updated data pages to disk after each transaction commits For an OLAP database it is much more effective to write full pages of data to disk than to...

Page 325: ... may write millions of rows of data you may want to limit the amount of data that is committed and thus written to disk to less than a full transaction s worth Setting savepoints allows you to subdivide transactions You can undo all changes after a savepoint using a ROLLBACK TO SAVEPOINT statement For more information on savepoints and rollback see Naming and nesting savepoints Releasing savepoint...

Page 326: ...oints IQ sets an implicit savepoint before and after every DML command The data page versions associated with these savepoints are released when the command completes If you want to retain data page versions beyond the end of a single DML command you need to set your own named savepoints Naming and nesting savepoints Savepoints can be named and they can be nested By using named nested savepoints y...

Page 327: ... is to the most recent commit or rollback Effect of rollback Rollback returns both the main and temporary stores to their former state It also releases locks Transaction rollback releases all locks held by the transaction Rollback to a savepoint releases all locks acquired after that savepoint Rollback of open cursors deletes all cursor information and closes both hold and non hold cursors Transac...

Page 328: ...recover Sybase recommends that you run the stored procedure sp_iqcheckdb after a system failure preferably before allowing users to connect This procedure checks every block in your database and produces statistics that allow you to check the consistency and integrity of your database For details see Adaptive Server IQ Troubleshooting and Error Messages Guide How transaction information aids recov...

Page 329: ... new log is started The checkpoint information is deleted at the next checkpoint Information related to particular savepoints is deleted when the savepoint is released or rolled back For other concurrency issues relating to backing up and restoring databases see Concurrency and backups Performance implications Snapshot versioning should have a minimal impact on performance The flexibility you gain...

Page 330: ...is section see Chapter 12 Managing System Resources Overlapping versions and deletions In order to delete data you may actually need to increase disk space by adding a dbspace to your IQ Store The amount of space you need for a deletion depends on the distribution of the data on data pages more than on the size or number of rows being deleted IQ needs to retain a version of each page that contains...

Page 331: ...ABLE and DROP DATABASE delete the table or database and all data in it without creating any version pages so you do not need to add space to use these commands Cursors in transactions A cursor allows you to return the results of a SELECT in the form of a data type called a cursor A cursor is similar to a table but has the additional property that one row is identified as the present or current row...

Page 332: ...s support for cursors is oriented toward their likely use in DSS applications The following sections discuss specific cursor characteristics with implications for transaction processing Cursor sensitivity A cursor is said to be sensitive if its membership the data rows it returns can vary from the time it is opened until the time it is closed An insensitive cursor has its membership fixed when it ...

Page 333: ...tion the current location of the cursor determines where a read or write operation begins Adaptive Server IQ supports positioned fetches which can be helpful in long query transactions It does not support positioned updates which are intended for shorter insertions and deletions For the most part updates to IQ databases are likely to involve large amounts of data repositioning is a very minor part...

Page 334: ...Cursors in transactions 314 ...

Page 335: ...character set and a sort order for characters in the database Adaptive Server IQ international features Adaptive Server IQ provides two sets of features that are of particular interest when setting up databases for languages Collations You can choose from a wide selection of supplied collations when you create a database By creating your database with the proper collation you ensure proper sorting...

Page 336: ...des optimal performance for IQ databases but not necessarily the most natural sort order For more information see Performance issues on page 352 Note that this differs from Adaptive Server Anywhere which infers the default collation for new databases from the character set in use by the operating system on which the database is created If it is not possible to set up your system in this default ma...

Page 337: ...cters represented in software and Adaptive Server IQ in particular Understanding character sets in software on page 317 What collations does Adaptive Server IQ provide Supplied collations on page 329 How do I ensure that error and informational messages sent from the database server to client applications are sent in the proper language and character set for my application Character translation fo...

Page 338: ... the database The database server processes these strings when responding to requests For example the database server may be asked to supply all the last names beginning with a letter ordered less than N in a table This request requires string comparisons to be carried out and assumes a character set ordering The database server receives strings from client applications as streams of bytes It asso...

Page 339: ...ptions characters 0 to 127 are the same for all the single byte code pages The mapping for this range of characters is called the ASCII character set It includes the English language alphabet in upper and lower case as well as common punctuation symbols and the digits This range is often called the seven bit range because only seven bits are needed to represent the numbers up to 127 or the lower p...

Page 340: ...ages Original Equipment Manufacturer for historical reasons Windows operating systems do not require the line drawing characters that were held in the extended characters of the OEM code pages so they use a different set of code pages These pages are based on the ANSI standard and are therefore commonly called ANSI code pages Adaptive Server IQ supports collations based on both OEM and ANSI code p...

Page 341: ...e of hexadecimal values from x81 to x9F or from xE0 to xEF decimal values 129 159 or 224 239 the character is a two byte character and the subsequent byte called a follow byte completes the character If the first byte is outside this range the character is a single byte character and the next byte is the first byte of the following character The properties of any Shift JIS character can be read fr...

Page 342: ...cter can be indicated For case sensitive databases the default for IQ databases created as of version 12 4 2 the lower and uppercase characters are not treated as equivalent For case insensitive databases the lower and uppercase versions of the character are considered equivalent First byte collation orderings for multibyte character sets A sorting order for characters in a multibyte character set...

Page 343: ...locale is used by the client library when making requests to the database server to determine the character set in which results should be returned If character set translation is enabled the database server compares its own locale with the application locale to determine whether character set translation is needed Different databases on a server may have different locale definitions For informati...

Page 344: ...different collation labels For more information see Understanding the locale collation label on page 328 Understanding the locale language The locale language is an indicator of the language being used by the user of the client application or expected to be used by users of the database server For a list of supported locale languages see Language label values on page 325 For how to find locale set...

Page 345: ...ilable character set labels see Character set labels on page 326 For how to find locale settings see Determining locale information on page 345 The client library or database server determines the character set as follows 1 If the connection string specifies a character set it is used For more information see the CharSet connection parameter in the Adaptive Server IQ Reference Manual 2 ODBC and Em...

Page 346: ...so_1 iso_8859 1 1987 ISO 8859 1 Latin 1 cp850 N A IBM CP850 European code set cp437 N A IBM CP437 U S code set roman8 hp rpman8 HP Roman 8 mac macintosh Standard Mac coding sjis shift_jis Shift JIS no extensions eucjis euc jp Sun EUC JIS encoding deckanji N A DEC Unix JIS encoding euccns N A EUC CNS encoding Traditional Chinese with extensions eucgb N A EUC GB encoding Simplified Chinese cp932 win...

Page 347: ...ic cp862 N A PC Hebrew cp863 N A IBM PC Canadian French code page cp864 N A PC Arabic cp865 N A PC Nordic cp866 N A PC Russian cp869 N A IBM PC Greek cp874 N A Microsoft Thai SB code page cp936 N A Simplified Chinese cp949 N A Korean cp950 N A PC MS Traditional Chinese cp1250 N A MS Windows 3 1 Eastern European cp1251 N A MS Windows 3 1 Cyrillic cp1252 N A MS Windows 3 1 US ANSI cp1253 N A MS Wind...

Page 348: ...ironment variable The SQLLOCALE environment variable is a single string that consists of three semicolon separated assignments It has the following form Charset cslabel Language langlabel CollationLabel colabel where cslabel langlabel and colabel are labels as defined in the previous sections For information on how to set environment variables see the Adaptive Server IQ Reference Manual Understand...

Page 349: ...f any collation that exists on your system use the z option of dbcollat For example to extract collation 850 you could enter dbcollat c uid DBA pwd SQL eng myhost_asiqdemo z 850 demo_col Supplied collations The following collations are supplied with Adaptive Server IQ Collation label Type Description 437LATIN1 OEM Code Page 437 Latin 1 Western 437ESP OEM Code Page 437 Spanish 437SVE OEM Code Page ...

Page 350: ...opean 852CYR OEM Code Page 852 Cyrillic 852POL OEM Code Page 852 Polish 855CYR OEM Code Page 855 Cyrillic 856HEB OEM Code Page 856 Hebrew 857TRK OEM Code Page 857 Turkish 860LATIN1 OEM Code Page 860 Latin 1 Western 861ISL OEM Code Page 861 Icelandic 862HEB OEM Code Page 862 Hebrew 863LATIN1 OEM Code Page 863 Latin 1 Western 865NOR OEM Code Page 865 Norwegian 866RUS OEM Code Page 866 Russian 869ELL...

Page 351: ...IS Multibyte Japanese Shift JIS Encoding SJIS2 Multibyte Japanese Shift JIS Encoding Sybase Adaptive Server Enterprise compatible EUC_JAPAN Multibyte Japanese EUC JIS X 0208 1990 and JIS X 0212 1990 Encoding EUC_CHINA Multibyte Simplified Chinese GB 2312 80 Encoding EUC_TAIWAN Multibyte Taiwanese Big 5 Encoding EUC_KOREA Multibyte Korean KS C 5601 1992 Encoding Johad Code Page 1361 ISO_1 ANSI ISO8...

Page 352: ...r sharp s xDF sorts with the lower case s in Adaptive Server IQ and Adaptive Server Anywhere but after ss in Adaptive Server Enterprise The ligatures corresponding to AE and ae xC6 and xE6 sort after A and a respectively in Adaptive Server IQ and Adaptive Server Anywhere but after AE and ae in Adaptive Server Enterprise The 1252LATIN1 collation This collation is the same as WIN_LATIN1 see below bu...

Page 353: ...e Server Enterprise The ligatures corresponding to OE and oe x8C and x9C sort with O in Adaptive Server IQ and Adaptive Server Anywhere but after OE and oe in Adaptive Server Enterprise The upper case and lower case letter S with caron x8A and x9A sorts with S in Adaptive Server IQ and Adaptive Server Anywhere but is undefined in Adaptive Server Enterprise sorting after x89 and x99 The ISO1LATIN1 ...

Page 354: ...IN1 437 437LATIN1 Belgium Belgian Dutch 850 850LATIN1 437 437LATIN1 Belgium Belgian French 850 850LATIN1 437 437LATIN1 Belarus Belarussian 855 855CYR Brazil Portuguese 850 850LATIN1 437 437LATIN1 Bulgaria Bulgarian 855 855CYR 850 850CYR Canada Cdn French 850 850LATIN1 863 863LATIN1 Canada English 437 437LATIN1 850 850LATIN1 Croatia Croatian 852 852LATIN2 850 850LATIN2 Czech Republic Czech 852 852L...

Page 355: ... 850 850LATIN2 Slovenia Slovenian 852 852LATIN2 850 850LATIN2 Spain Spanish 850 850ESP 437 437ESP Sweden Swedish 850 850SVE 437 437SVE Switzer land French 850 850LATIN1 437 437LATIN1 Switzer land German 850 850LATIN1 437 437LATIN1 Switzer land Italian 850 850LATIN1 437 437LATIN1 Turkey Turkish 857 857TRK 850 850TRK UK English 850 850LATIN1 437 437LATIN1 USA English 437 437LATIN1 850 850LATIN1 Vene...

Page 356: ...upport fixed length multibyte character sets such as 2 byte Unicode UCS 2 or 4 byte Unicode UCS 4 Understanding character set translation Adaptive Server IQ can carry out character set translation among character sets that represent the same characters but at different positions in the character set or code page There needs to be a degree of compatibility between the character sets for this to be ...

Page 357: ...utomatically translates the characters of the messages so that they match the character set used in the database collation To use character translation for database messages Ensure that the collation for your database is compatible with the character set used on your computer and with the character set used in the Adaptive Server IQ language resource library The language resource library differs a...

Page 358: ...t and server machine For maximum compatibility among different machines you should use server names built from ASCII characters 1 to 128 using no punctuation characters Server names are truncated at 40 characters 3 The DatabaseName or DatabaseFile parameter is interpreted in the database server character set 4 Once the database is located the remaining connection parameters are interpreted accordi...

Page 359: ...tch Collation internals This section describes internal technical details of collations including the file format of collation files This section is of particular use if you want to create a database using a custom collation For information on the steps involved see Creating a custom collation on page 349 and Creating a database with a custom collation on page 351 You can create a database using a...

Page 360: ...ement For example the Shift JIS collation file contains the following collation line with label SJIS and name Japanese Shift JIS Encoding Collation SJIS Japanese Shift JIS Encoding Item Description Collation A required keyword label The collation label which appears in the system tables as SYS SYSCOLLATION collation_label and SYS SYSINFO default_collation The label must contain no more than 10 cha...

Page 361: ... sample lines for a collation Sort some special characters at the beginning Argument Description sort position Optional Specifies the position at which the characters on that line will sort Smaller numbers represent a lesser value so will sort closer to the beginning of the sorted set Typically the sort position is omitted and the characters sort immediately following the characters from the previ...

Page 362: ...character is listed in the collation sequence and all characters with the same first byte are sorted together and ordered according to the value of the following bytes For example the following is part of the Shift JIS collation file xfb xfc xfd In this collation all characters with first byte xfc come after all characters with first byte xfb and before all characters with first byte xfd The two b...

Page 363: ... valid multibyte characters Any combination of one byte from the second line followed by one byte from the first is a valid character Therefore x81 x40 is a valid double byte character but x81 x00 is not The Properties section The Properties section is optional and follows the Encodings section If a Properties section is supplied an Encodings section must be supplied also The Properties section li...

Page 364: ...ne the default locale of each computing platform in your environment The default locale is the character set and language of each computer On Windows operating systems the character set is the ANSI code page For how to find locale information see Determining locale information on page 345 2 Decide whether the locale settings are appropriate for your environment For more information see Understandi...

Page 365: ...use on your client machine operating system In the case of Windows operating systems on the client machine choose the ANSI character set For information see Avoiding character set translation on page 338 Determining locale information You can determine locale information using system functions For a complete list see the Adaptive Server IQ Reference Manual To determine the locale of a database ser...

Page 366: ...any action To find out the default locale of your operating system see Determining locale information on page 345 2 If you need to change the locale create a SQLLOCALE environment variable with the following value Charset cslabel Language langlabel CollationLabel colabel where cslabel is a character set label from the list in Character set labels on page 326 langlabel is a language label from the ...

Page 367: ...ropdown menu select Properties and then click the Extended Information tab To specify a database collation when creating a database SQL 1 List the supplied collation sequences SELECT FROM SYS SYSCOLLATIONMAPPINGS The first column of the list is the collation label which you supply when creating the database 437LATIN1 Code Page 437 Latin 1 Western 437ESP Code Page 437 Spanish 437SVE Code Page 437 S...

Page 368: ...n ODBC translation driver This driver converts characters between OEM and ANSI code pages It allows Windows applications using ANSI code pages to be compatible with databases that use OEM code pages in their collations Note If you use an ANSI character set in your database and are using ANSI character set applications you do not need to use this translation driver The translation driver carries ou...

Page 369: ...es the data in the database To turn off character set translation in DBISQL Set the DBISQL option CHAR_OEM_Translation to a value of OFF SET OPTION CHAR_OEM_TRANSLATION OFF For more information on OEM to ANSI character set translation in Interactive SQL see CHAR_OEM_TRANSLATION option in the Adaptive Server IQ Reference Manual Creating a custom collation If none of the supplied collations meet you...

Page 370: ...changed 5 Change the collation definition Make the changes you wish in the custom collation file to define your new collation For information on the collation file contents and format see Collation internals on page 339 6 Convert the file to SQL scripts You do this using the dbcollat command line utility using the d switch For example the following command line creates the mycustmap sql file and m...

Page 371: ...se For instructions on how to create custom collations see Creating a custom collation on page 349 2 Create the new database Use the CREATE DATABASE statement or Sybase Central specifying the name of your custom collation For example the following statement creates a database named newcol db using the custom collation sequence newcol CREATE DATABASE newcol db COLLATION newcol IQ PATH newcol iq Com...

Page 372: ...cter set and collation sequence The disadvantage of these settings is that uppercase characters are always sorted before lowercase ones For example BANANA sorts before apple If you prefer a more natural sort order but still need a case sensitive database and you are willing to sacrifice some degree of performance use the collation ISO_1 instead of the default ISO_BINENG Note When your database use...

Page 373: ...users Database permissions are assigned to user IDs Throughout this chapter the term user is used as a synonym for user ID You should remember however that permissions are granted and revoked for each user ID Setting up individual user IDs Even if there are no security concerns regarding a multiuser database there are good reasons for setting up an individual user ID for each user The administrati...

Page 374: ...s used interchangeably to mean both the DBA user ID and any user ID with DBA authority Adding new users The DBA has the authority to add new users to the database As users are added they are also granted permissions to carry out tasks on the database Some users may need to simply look at the database information using SQL queries others may need to add information to the database and others may ne...

Page 375: ...within the table The DBA has permission to modify any component within the database and so could delete a table created by another user for instance The DBA has all the permissions regarding database objects that the owner of each object has The DBA is also able to create database objects for other users and in this case the owner of an object is not the user ID that executed the CREATE statement ...

Page 376: ...rmissions from each of the groups Managing individual user IDs and permissions This section describes how to create new users and grant permissions to them For most databases the bulk of permission management should be carried out using groups rather than by assigning permissions to individual users one at a time However as groups are simply a user ID with special properties attached you should re...

Page 377: ...ons granted automatically to new users by assigning permissions to the special PUBLIC user group as discussed in Special groups Using a DBISQL command file to set up new users You may want to put commands for setting up new users into a DBISQL command file Command files help you standardize the way you perform processes you repeat over time For details on using command files see the chapter Gettin...

Page 378: ... prevent unauthorized access to your database The following command changes the password for user ID DBA to new_password GRANT CONNECT TO DBA IDENTIFIED BY new_password If you are using DBISQL it is a good idea to put your permission grants into a command file for reference and so that it can be modified and run again if it is necessary to recreate the permissions Granting DBA and resource authori...

Page 379: ...t users These permissions do not apply to views The DELETE INSERT and UPDATE permissions grant the authority to modify the data in a table or view The DELETE INSERT and UPDATE permissions grant the authority to modify the data in a table or view Of these the UPDATE permission may be restricted to a set of columns in the table or view The SELECT permission grants authority to look at data in a tabl...

Page 380: ...to the database 2 Double click the Tables folder for that database to display the tables in the left panel 3 Click the Users and Groups folder and locate the user you want to grant permissions to 4 Drag the user to the table for which you want to grant permissions For more information see the Sybase Central online Help Granting users the right to grant permissions Each of the table and view permis...

Page 381: ...wner of my_procedure procedure 2 Execute the SQL statement GRANT EXECUTE ON my_procedure TO M_Haneef Execution permissions of procedures Procedures execute with the permissions of their owner Any procedure that updates information on a table will execute successfully only if the owner of the procedure has UPDATE permissions on the table As long as the procedure owner does have the proper permissio...

Page 382: ...he command is REVOKE EXECUTE ON my_procedure FROM M_Haneef This command must be issued by the DBA or by the owner of the procedure Permission to delete rows from sample_table can be revoked by issuing the command REVOKE DELETE ON sample_table FROM M_Haneef Warning If you revoke a user s connect privileges any database objects owned by that user are deleted without warning Likewise if you use the s...

Page 383: ...permissions Granting permissions to a group and revoking permissions from a group are done in exactly the same manner as any other user using the commands described in Managing individual user IDs and permissions A group can also be a member of a group A hierarchy of groups can be constructed each inheriting permissions from its parent group A user ID may be granted membership in more than one gro...

Page 384: ...ugh the process For more information see the Sybase Central online Help Granting group membership to users Making a user a member of a group is done with the GRANT statement Membership in a group can be granted either by the DBA or by the group user ID You can grant user M_Haneef membership in a group personnel as follows 1 Connect to the database as a user with DBA authority or as the group user ...

Page 385: ...OURCE permissions Ownership of database objects is associated with a single user ID and is not inherited by group members If the user ID personnel creates a table then the personnel user ID is the owner of that table and has the authority to make any changes to the table as well as to grant privileges concerning the table to other users Other user IDs who are members of personnel are not the owner...

Page 386: ...wnership permissions over any tables in the database created in the name of the group s user ID It is possible to set up a database so that all handling of groups and their database objects is done by the DBA rather than permitting other user IDs to make changes to group membership This is done by disallowing connection as the group s user ID when creating the group To do this the GRANT CONNECT st...

Page 387: ... The PUBLIC group is a member of the SYS group and has read access for some of the system tables and views so that any user of the database can find out information about the database schema If you wish to restrict this access you can REVOKE PUBLIC s membership in the SYS group Any new user ID is automatically a member of the PUBLIC group and inherits any permissions specifically granted to that g...

Page 388: ...tables in the database are created by the company user ID CONNECT USER company IDENTIFIED BY secret CREATE TABLE company Customers CREATE TABLE company Products CREATE TABLE company Orders CREATE TABLE company Invoices CREATE TABLE company Employees CREATE TABLE company Salaries Not everybody in the company should have access to all information Consider two user IDs in the sales department Joe and...

Page 389: ... SELECT FROM Salaries SELECT FROM company Salaries In either case Joe and Sally do not have permission to look at the Salaries table Using views and procedures for extra security For databases that require a high level of security defining permissions directly on tables has limitations Any permission granted to a user on a table applies to the whole table There are many cases when users permission...

Page 390: ...yees in other departments This example describes how to create a user ID for the sales manager create views that provide the information she needs and grants the appropriate permissions to the sales manager user ID 1 Create the new user ID using the GRANT statement from a user ID with DBA authority Enter the following CONNECT DBA IDENTIFIED by SQL GRANT CONNECT TO SalesManager IDENTIFIED BY sales ...

Page 391: ...the views you have created CONNECT SalesManager IDENTIFIED BY sales SELECT FROM DBA emp_sales SELECT FROM DBA order_summary No permissions have been granted to the Sales Manager to look at the underlying tables The following commands produce permission errors SELECT FROM DBA employee SELECT FROM DBA sales_order Other permissions on views The previous example shows how to use views to tailor SELECT...

Page 392: ... carry out a specific action in the following manner 1 If the user ID has DBA permissions the user ID can carry out any action in the database 2 Otherwise permission depends on the permissions assigned to the individual user If the user ID has been granted permission to carry out the action then the action is allowed to proceed 3 If no individual settings have been made for that user permission de...

Page 393: ...ive Server IQ Reference Manual for more information on these options AGGREGATION_CUTOFF Sets the precision level at which Adaptive Server IQ uses a more efficient internal storage type to do calculations on SUM or AVG numeric expressions CURSOR_WINDOW_ROWS Defines the number of cursor rows to buffer LOAD_MEMORY_MB Sets an upper bound for the amount of heap memory that subsequent load operations ca...

Page 394: ...ned by the special user ID SYS It is not possible to connect to the SYS user ID The DBA has SELECT access to all system tables just as to any other tables in the database The access of other users to some of the tables is limited For example only the DBA has access to the SYS SYSUSERPERM table which contains all information about the permissions of users of the database as well as the passwords of...

Page 395: ... UPDATE permission given by the GRANT command SYSDUMMY PUBLIC Dummy table can be used to find the current user ID SYSPROCPERM PUBLIC Each row holds one user granted permission to use one procedure Views Default Contents SYSUSERAUTH DBA only All information in SYSUSERPERM except for user numbers SYSUSERPERMS PUBLIC All information in SYSUSERPERM except for passwords SYSUSERLIST PUBLIC All informati...

Page 396: ...Users and permissions in the system tables 376 ...

Page 397: ...urs when the database file the file system or the device storing the database file becomes unusable After a system failure Adaptive Server IQ can usually recover automatically so that you may not need to restore your database Recovery from system failures is discussed in Adaptive Server IQ Troubleshooting and Error Messages Guide After media failure or if for any reason the data in your database i...

Page 398: ...hree types of backups Full backup makes a complete copy of the database Incremental backup copies all transactions since the last backup of any type Incremental since full backup copies all transactions since the last full backup All three backup types fully back up the Catalog Store In most cases the Catalog Store is much smaller than the IQ Store If the Catalog Store is larger than or nearly as ...

Page 399: ...ecovery Adaptive Server IQ uses the transaction log file during recovery from a system failure It does not use the transaction log to restore an IQ database to recover committed IQ transactions or to restore the Catalog Store for an Adaptive Server IQ database However the restore program does check for the existence of the transaction log For a full restore the transaction log must not exist You m...

Page 400: ...al consistency For a more thorough check you can run the stored procedure sp_iqcheckdb before making a backup See Validating your database on page 393 for details Selecting archive devices You can back up any IQ database onto either disk or magnetic tape Adaptive Server IQ supports backup and restore using multiple tape drives at near device speeds or to multiple disks if disk striping is in use Y...

Page 401: ...fixed length block mode Preparing for backup In order to run BACKUP you must first install and run Adaptive Server IQ You must also make sure that you meet the other requirements described in the sections that follow Obtaining DBA privileges You need DBA privileges on a database to run BACKUP or RESTORE You must either log on as the DBA user or be granted DBA authority by the DBA as described in G...

Page 402: ...ware to create backups Such products can be particularly useful for unattended backups See Unattended backup for details if you want to run backups when no operator is present Note You can run BACKUP from a batch script or procedure as well as from Interactive SQL Estimating Media Capacity Before you do a backup be sure that your archive media has sufficient space When you estimate available space...

Page 403: ...t least 8KB of free disk space before the backup resumes Unattended backup cannot prompt you to provide more space If enough space is not available unattended backup fails BACKUP treats size estimates differently for unattended backups See Unattended backup for details For an operator attended backup to tape BACKUP simply begins the backup If it runs out of room you must mount additional tapes Con...

Page 404: ...ng the type of backup FULL INCREMENTAL INCREMENTAL SINCE FULL specifies the type of backup Choose one FULL causes a full backup of both the Catalog Store and the IQ Store FULL is the default action INCREMENTAL makes a full backup of the Catalog Store and then backs up all changes to the IQ Store since the last IQ backup of any type INCREMENTAL SINCE FULL makes a full backup of the Catalog Store an...

Page 405: ... identify with certainty when you do a backup Also if there is data in other directories along the path you may not have enough room for the backup Positioning tape devices BACKUP does not position tapes for you You must position the tape appropriately before starting your backup and be sure that you do not overwrite any of the backup if you use a rewinding tape device For these reasons Sybase rec...

Page 406: ... the extra backslashes or otherwise misspell a tape device name and write a name that is not a valid tape device on your system Adaptive Server IQ interprets this name as a disk file name For more information about fixed length I O on NT see Tuning backup operations in the Adaptive Server IQ Installation and Configuration Guide Specifying the size of tape backups The SIZE option of the TO clause i...

Page 407: ...ximum size of the backed up data on that stripe in KB If you use the Sybase provided backup either attended or unattended specify SIZE if you need a maximum size other than the default of 2GB When you omit SIZE for a backup to disk BACKUP assumes that each disk file you name as an archive_device can be up to 2GB For example if you specify one archive_device a disk file called janfull and you speci...

Page 408: ...ardware With CRC ON the numbers computed on backup are verified during any subsequent RESTORE operation The default is CRC ON Adding comments WITH COMMENT specifies a string up to 32KB long as part of the header information for the backup archive If you omit this option BACKUP enters a NULL You can view the comment string by executing a RESTORE DATABASE FROM CATALOG ONLY or by displaying the backu...

Page 409: ...hecking for backup space BACKUP uses the STACKER and SIZE parameters to determine whether there is enough space for the backup For disk backups if it decides that you have not provided enough space it fails the backup before actually writing any of the data If it decides that there is enough space to start the backup but then runs out before it finishes for example if your estimate is incorrect or...

Page 410: ...s with non Sybase products Adaptive Server IQ supports backup and restore using a number of third party products The package you use must conform to the Adaptive Server Enterprise Backup Interface Check the documentation for your product to be sure that it supports Sybase databases To perform such a backup or restore you issue the BACKUP or RESTORE statement as if you were using Adaptive Server IQ...

Page 411: ...ikely to cause data loss or corruption either from activity in the database while the system level backup occurred or from missing files Shutting down the database Your IQ database must not be running during a system level backup You must shut down your IQ database before starting the system level backup You must also ensure that no one starts the IQ database until the system level backup is compl...

Page 412: ... updated It is critically important to add to your system backup specification any dbspaces that are added to the database whether they are in SYSTEM IQ_SYSTEM_MAIN or IQ_SYSTEM_TEMP If a dbspace is added several months down the road or after some turnover in your organization you may miss this step To ensure that you are backing up all the files you need use a script for system level backups In t...

Page 413: ...uption in the data it is restoring since the database may not even exist To validate your database issue the following command sp_iqcheckdb You should run sp_iqcheckdb periodically and whenever you suspect a problem with the database The sections that follow provide basic information on using sp_iqcheckdb For full details see the Adaptive Server IQ Troubleshooting and Error Messages Guide This sto...

Page 414: ... If you see a non zero value for any of these statistics your database likely has a serious consistency problem You also see a non zero value if other users are active while sp_iqcheckdb is run see Concurrency issues for sp_iqcheckdb below The following figure shows an excerpt from sp_iqcheckdb output for the sample database You can see the key statistics designated by asterisks Orphaned block sta...

Page 415: ...b 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 Any other concurrent activities on the system will run ...

Page 416: ...ssary Adaptive Server IQ restores the database to its state as of the automatic CHECKPOINT at the start of the backup Before you restore Before you can restore a database make sure that the following conditions are met You must have DBA privileges You must be connected to the utility_db database For information on utility_db and how to set privileges for using it see the Adaptive Server IQ Install...

Page 417: ...quires exclusive access to the database The default database server startup option gd DBA guarantees that only the DBA can start a database To ensure exclusive access start the database server with the gd DBA option set but do not start the database you are restoring RESTORE automatically starts the database in such a way that no other users can connect to it You must restore an entire backup or s...

Page 418: ...the restore as were used to create the backup s you are restoring Specifying files for an incremental restore For an incremental restore files you restore must match in number and size the files they replace for both the IQ and Catalog Stores Keeping the database unchanged between restores If you are doing a set of incremental restores and any user changes the database before you finish restoring ...

Page 419: ...1 database to Adaptive Server IQ 12 0 you must follow the migration procedure described in the Adaptive Server IQ Installation and Configuration Guide RESTORE does not let you restore an Adaptive Server IQ backup to an Adaptive Server Anywhere database The RESTORE statement To restore a database use the following syntax RESTORE DATABASE db_file FROM archive_device FROM archive_device RENAME dbspac...

Page 420: ...pecifying devices Note If you are restoring from tape devices on Windows NT note that you do not need to redouble the backslashes when you specify tape devices for restore as you did for backup Example 1 Restoring to the same location This Windows NT example restores a database to asiquser db The database is restored from two disk files All database files will be restored to their original locatio...

Page 421: ...of that database file In other words files originally created relative to the SYSTEM dbspace which holds the Catalog Store file are restored relative to the Catalog Store file Files originally created relative to the IQ_SYSTEM_MAIN dbspace which holds the main IQ Store file are restored relative to the main IQ Store file If you do not specify a RENAME clause and the file was created using an absol...

Page 422: ...base so that you will know the correct names to include in RENAME clauses Example 3 Moving a user dbspace This example shows how you restore the full and incremental backups in example shown earlier in this chapter In this case media failure has made a UNIX raw partition unusable The user defined dbspace on that raw partition IQ_USER must be moved to a new raw partition dev rdsk c1t5d2s1 No other ...

Page 423: ...ause but omit the RENAME clause Adjusting data sources and configuration files When you move a database you may need to modify your data sources configuration files and integrated logins to reflect the new location of the database Restoring in the correct order When you restore from a full backup every block in use at the time the backup was made is written to disk When you restore from an increme...

Page 424: ... which they were made Within a given backup the order in which you restore tapes is also important In particular you need to keep track of the order of tapes in each backup tape set that is the set of tapes produced in a given backup on a given archive device You must restore the tape set that contains the backup of the Catalog Store first and it must be on the first archive device Within each set...

Page 425: ...k this Although these rules also apply to disk files you are not likely to back up to multiple files on a given disk device Renaming the transaction log after you restore When you rename or move all other files in the database you should also do the same for the log file To move or rename the log file use the Transaction Log utility DBLOG You should run this utility After using RESTORE with a new ...

Page 426: ...ion log If the database is not currently using a transaction log it starts using one If the database is already using a transaction log it changes to using the new file as its transaction log Validating the database after you restore To ensure that tapes have been restored in the correct order you should run the stored procedure sp_iqcheckdb after you finish restoring your database If you are rest...

Page 427: ...g up a database When the DBA runs RESTORE the command automatically starts the database gets the information it needs for the restore and then stops the database At the end of the restore the command starts the database issues a checkpoint and stops it again This procedure ensures that the DBA has exclusive write access throughout a restore When all incremental restores are complete the DBA issues...

Page 428: ... BACKUP Using Symbolic Links UNIX Only If you use symbolic links you may unknowingly cause dbspaces to be created in a different directory from where you may want them to be For example suppose you have created dbspaces in the following files rw r r 1 dkusner sybase 122880000 Feb 26 18 27 asiqdemo db rw r r 1 dkusner sybase 122880000 Feb 26 18 27 asiqdemo iq1 rw r r 1 dkusner sybase 122880000 Feb ...

Page 429: ...ttended and unattended backups Unattended backup tries to detect all possible reasons for a backup failing except tape media failure and report any potential errors before attempting the backup such as available space on disk or tape and consistent size and block factor For unattended backup to disk Adaptive Server IQ first tests whether there is enough free disk space for the backup However it do...

Page 430: ...tore Note To display only the information about a particular backup you can run RESTORE with the CATALOG ONLY option This option displays the header file for a backup from the media rather than from the file so that the DBA can identify what is on the tape or file See Displaying header information Locating the backup log The backup syb file is in ASCII text format Its location depends on the setti...

Page 431: ...mental_since_full or Database File Only for Adaptive Server Anywhere databases only Method Archive or Image Location Comment if entered on the BACKUP command enclosed in single quotes If the comment includes quotes they appear as two consecutive single quotes Here is a sample backup log with ellipses added to show continuation lines BACKUP 1 0 all_types db ASIQ 1998 12 22 16 25 00 000 DBA Full Arc...

Page 432: ...ckup logs Warning Do not edit the backup log while a backup or restore is taking place If you are modifying the file while BACKUP or RESTORE is writing to it you may invalidate the information in the file Viewing the backup log in Sybase Central The backup log contains information in raw unsorted form To see the information in a form that is easier to understand you can view it in Sybase Central N...

Page 433: ...s of IQ dbspaces and file names and add rename syntax including quotes select rename dbspace_name to file_name from SYSFILE where store_type IQ output to file in proper format no delimiters and no additional quotes output to restore tst delimited by quote this produces a file restore tst looking like this rename IQ_SYSTEM_MAIN to dev rdsk c2t0d1s7 rename IQ_SYSTEM_TEMP to dev rdsk c2t1d1s7 rename ...

Page 434: ...p is relatively quick and takes a relatively small amount of space on tape or disk Full backups are relatively slow and require a lot of space Incremental_since_full is somewhere in between It starts out as equivalent to incremental but as the database changes and the number of backups since a full backup increases incremental_since_full can become as time consuming and media consuming as a full b...

Page 435: ...and a full backup once a week On the other hand if your changes tend to be few a full backup once a month with incrementals in between might be fine Designating Backup and Restore Responsibilities Many organizations have an operator whose job is to perform all backup and recovery operations Anyone who is responsible for backing up or restoring an Adaptive Server IQ database must have DBA privilege...

Page 436: ...parameter of the BACKUP command controls the amount of memory used If your backups are slow you may want to increase the value of BLOCK FACTOR for faster backups The effect of BLOCK FACTOR depends on your operating system and on the block size specified when the database was created By default the database block size is 4096 On UNIX the default BLOCK FACTOR is 25 With this combination BACKUP is ab...

Page 437: ...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 non IQ tables in the Catalog Store You can improve IQ backup performance by keeping any non IQ data in a separate Adaptive Server Anywhere only database rather than in the Catalog Store Backup copies only the latest committed versio...

Page 438: ...Determining your data backup and recovery strategy 418 ...

Page 439: ...ually measured in response time and throughput Response time is the time it takes for a single task to complete It is affected by Reducing contention and wait times particularly disk I O wait times Using faster components Reducing the amount of time the resources are needed this is the same as increasing concurrency Throughput refers to the volume of work completed in a fixed time period Throughpu...

Page 440: ...eed to find a way to make more memory available Like any RDBMS software Adaptive Server IQ requires a lot of memory The more memory you can allocate to Adaptive Server IQ the better However there is always a fixed limit to the amount of memory in a system so sometimes operating systems can have only part of the data in memory and the rest on disk When the operating system must go out to disk and r...

Page 441: ...aches are still a crucial aspect of IQ memory management However they now receive a memory allocation from the server memory pool At the operating system level IQ server memory consists of both heap memory and shared memory For the most part you do not need to be concerned with whether memory used by Adaptive Server IQ is heap memory or shared memory All memory allocation is handled automatically ...

Page 442: ...is stored in one of the two caches whenever it is in memory All user connections share these buffer caches Adaptive Server IQ keeps track of which data is associated with each connection Read the sections that follow for in depth information on managing buffer caches For information on how to calculate your memory requirements see Determining the sizes of the buffer caches For information on how t...

Page 443: ...s a higher memory requirement As a minimum you can assume that UNIX systems use 60MB or more while Windows NT systems use 30MB or more In addition other applications that run in conjunction with Adaptive Server IQ such as query tools have their own memory needs See your application and operating system documentation for information on their memory requirements Adaptive Server IQ memory overhead Af...

Page 444: ...database at any one time Memory requirements for loads Adaptive Server IQ also requires a portion of memory separate from the buffer caches to perform loads operations synchronization and deletions This memory is used for buffering I O for flat files Adaptive Server IQ uses memory to buffer a read from disk The size of this read equals the BLOCK FACTOR multiplied by the size of the input record BL...

Page 445: ... database If you have more than one you need to further split the remaining memory among the databases you expect to use It is highly recommended that you start with the general guidelines presented here and watch the performance of Adaptive Server IQ by using its monitor tool described in Monitoring the buffer caches on page 467 and any specific tools described in the Adaptive Server IQ Installat...

Page 446: ...our system and how much remains for your main and temp buffer caches It assumes that the system has 1GB of physical memory no other significant applications on the hardware other than running Adaptive Server IQ and only one active database at a time The table makes a distinction in the storage type raw versus cooked and the way the database is accessed queries or inserts Table 12 1 Memory availabl...

Page 447: ...sical memory Several options and server switches can affect buffer cache sizes Table 12 2 Methods of adjusting buffer cache sizes Memory remaining for the main and temp buffer caches 675 828 397 550 Main_Cache_Memory_Mb setting 60 of memory remaining for buffer caches 405 497 238 330 Temp_Cache_Memory_Mb setting 40 of memory remaining for buffer caches 270 331 159 220 Memory available using raw pa...

Page 448: ...B _of_MB iqmc and iqtc server switches Reset cache sizes when the database is not running Especially useful if cache sizes are larger than your system can accommodate From the time the server is started until it is stopped Setting buffer cache size server switches on page 429 iqsmem and iqwmem server switches Use on some UNIX platforms to provide additionalmemoryforuse as buffer caches From the ti...

Page 449: ...ORY_MB and TEMP_CACHE_MEMORY_MB database options However if you set these parameters to a value that is higher than your system will accommodate you will not be able to open the database If this occurs use the server startup options iqmc and iqtc to change the current buffer cache sizes These parameters only remain in effect while the server is running so you still need to set the buffer cache siz...

Page 450: ...f default block size to page size but it considers other factors also The default block size should result in an optimal balance of I O transfer rate and disk space usage for most systems It does favor saving space over performance however If the default block size does not work well for you you can set it to any power of two between 4096 and 32 768 subject to the constraints that there can be no ...

Page 451: ...ease the buffer caches too much you could make your data loads or queries inefficient or incomplete due to insufficient buffers Decrease memory used for loads You can set the LOAD_MEMORY_MB option to limit the amount of heap memory used for loads and other similar operations See Memory for loads synchronizations and deletions on page 421 Adjust blocking factor for loads Use BLOCK FACTOR to reduce ...

Page 452: ... the total number of connections the server will support Statistically some of these are expected to be connected and idle while others are connected and actively using the database iqgovern Although 500 users can be connected to IQ for best throughput it is recommended that far fewer users are allowed to query at once in order to allow each of them sufficient resources to be productive The iqgove...

Page 453: ...active or may even time out their connection request In this situation the server may appear to be down when it is merely very busy A user getting this behavior should try to connect again This issue will be addressed in a future version Setting operating system parameters To run Adaptive Server IQ with many connections you need to change certain system parameters Solaris Although the Solaris thre...

Page 454: ...On all platforms Adaptive Server IQ uses memory for four primary purposes Main buffer cache Temporary buffer cache Thread stacks Load buffers See Figure 12 1 Buffer caches in relation to physical memory earlier in this chapter for a diagram of IQ memory use The HP UNIX and AIX platforms limit the total amount of memory available to IQ or any other single application You must set IQ server options ...

Page 455: ...ired memory can improve performance To create a pool of wired memory on these UNIX platforms only specify the iqwmem command line switch indicating the number of MB of wired memory The maximum value for iqwmem varies by platform 3800 on Sun 2000 on HP UNIX no real maximum for DEC For example to add 1GB of wired memory you specify iqwmem 1000 Warning Use this switch only if you have enough memory t...

Page 456: ...n Settings in start_asiq The start_asiq startup utility sets iqsmem to a platform specific value automatically On Tru64 Digital UNIX systems start_asiq does not set iqsmem automatically but the range of permissible values is up to 28 000MB See the Adaptive Server IQ Installation and Configuration Guide for your platform for the range of permissible values Impact of other applications and databases...

Page 457: ...ommand start_asiq my_iqserver iqsmem 800 my_iqdb Controlling file system buffering On Solaris UFS file systems and Windows NT file systems only you can control whether file system buffering is turned on or off Turning off file system buffering saves a data copy from the file system buffer cache to the main IQ buffer cache Usually doing so reduces paging and therefore improves performance However y...

Page 458: ...ges leading to excess operating system paging activity and reduced IQ performance NT can bias the paging algorithms to favor applications at the expense of the file system This bias is recommended for IQ performance See Chapter 5 Performance and Tuning Tasks in the Adaptive Server IQ Installation and Configuration Guide for Windows NT for details Other ways to get more memory In certain environmen...

Page 459: ...weight processes LWPs should run on which processor and when It has no knowledge about what the user threads are or how many are active in each process The operating system kernel schedules LWPs onto CPU resources It uses their scheduling classes and priorities Each LWP is independently dispatched by the kernel performs independent system calls incurs independent page faults and runs in parallel o...

Page 460: ... time you issue it If the condition persists you may need to restart the server and specify more IQ threads as described in the next section IQ options for managing thread usage Adaptive Server IQ offers the following options to help you manage thread usage To set the maximum number of threads available for Adaptive Server IQ use set the server startup option iqmt This option is set automatically ...

Page 461: ...artitions are physical subsets of the disk that are accessed separately by the operating system Disk partitions are typically accessed in two modes cooked mode through the UFS file system or raw mode Raw mode sometimes called character mode does unbuffered I O generally making a data transfer to or from the device with every read or write system call The UFS cooked mode is a UNIX file system and a...

Page 462: ...isks The first block is located on the first drive The second block is located on the second drive and so on When all the drives have been used the process cycles back and uses additional blocks on the drives The net effect of disk striping is the random distribution of data across multiple disk drives Random operations against files stored on striped disks tend to keep all of the drives in the st...

Page 463: ...nt then the size of the smallest one is often used and other disk space is wasted Also the speed of the slowest disk is often used In general disks used for file striping should not be used for any other purpose For example do not use a file striped disk as a swap partition Never use the disk containing the root file system as part of a striped device In general you should use disk striping whenev...

Page 464: ...g one disk segment at a time As with all PUBLIC options you must disconnect and then reconnect in order for the change to take effect When disk striping is on you cannot drop dbspaces with the DROP DBSPACE command Since dbspaces are dropped as they are empty from last to first and with this strategy dbspaces are filled partially in a more distributed manner it is unlikely the dbspaces will be empt...

Page 465: ...le drives You can create additional segments for your IQ and temporary data with the CREATE DBSPACE command When to create dbspaces When possible allocate all dbspaces when you create a database If you add dbspaces later IQ stripes new data across both old and new dbspaces Striping may even out or it may remain unbalanced depending on the type of updates you have The number of pages that are turne...

Page 466: ... first user connects to a database By default Adaptive Server IQ logs all messages in the message log file including error status and insert notification messages You can turn off notification messages in the LOAD and INSERT statements Strategic file locations Performance related to randomly accessed files can be improved by increasing the number of disk drives devoted to those files and therefore...

Page 467: ... for proxy tables in other databases such as Adaptive Server Enterprise Place the transaction log and message log on separate disks from the IQ Store Catalog Store and Temporary Store and from any proxy databases such Adaptive Server Enterprise Working space for inserting deleting and synchronizing When you insert or delete data and when you synchronize join indexes Adaptive Server IQ needs some w...

Page 468: ...hem can have a dramatic impact on performance memory use and disk I O Adaptive Server IQ provides several options for adjusting resource use to accommodate varying numbers of users and types of queries Most of these are SET OPTION command options that affect only the current database A few are command line options that affect an entire database server For more information on all of these options i...

Page 469: ...that might otherwise consume too many resources If the query optimizer estimates that the result set from a query will exceed the value of this option it rejects the query with the message Query rejected because it exceed resource Query_Rows_Returned_Limit If you use this option set it so that it only rejects queries that consume vast resources Forcing cursors to be non scrolling When you use scro...

Page 470: ...mber of prepared statements that a connection can use at once Lowering a connection s priority When you set the BACKGROUND_PRIORITY option to ON requests on the current connection have minimal impact on the performance of other connections This option allows tasks for which responsiveness is critical to coexist with other tasks for which performance is not as important Prefetching cache pages The ...

Page 471: ... improve resource use This section describes several ways to adjust your system for maximum performance or better use of disk space Restricting database access For better query performance set the database to read only if possible or schedule significant updates for low usage hours Adaptive Server IQ allows multiple query users to read from a table while you are inserting or deleting from that tab...

Page 472: ...rformance of processes using those files can improve dramatically Primary candidates for RAM disks are programs and temporary files Warning This is not recommended for database files or transaction log files Database integrity may be compromised if these files are placed on RAM disk Indexing tips The following sections give some tips for selecting and managing indexes See Chapter 4 Adaptive Server...

Page 473: ...ion to the default index all columns in this example beside l price and l discount should have an LF or HG index Using join indexes Users frequently need to see the data from more than one table at once This data can be joined at query time or in advance by creating a join index You can usually improve query performance by creating a join index for columns that must be joined in a consistent way B...

Page 474: ...le consider how best to eliminate data rows you no longer need If your IQ database contains data that originated in an Adaptive Server Anywhere database you may be able to eradicate unneeded data by simply replaying Anywhere deletions command syntax is compatible You can do the same with data from an Adaptive Server Enterprise database because Adaptive Server IQ provides Transact SQL compatibility...

Page 475: ...normalization you choose it has the potential for data integrity problems which must be carefully documented and addressed in application design Disadvantages of denormalization Denormalization has these disadvantages Denormalization usually speeds retrieval but can slow updates This is not a real concern in a DSS environment Denormalization is always application specific and needs to be re evalua...

Page 476: ...ny processes compute summaries Should you create join indexes to gain performance Improving your queries This section discusses several ways to improve queries for better performance including Tips on how to structure your queries to avoid operations that may be time consuming Suggestions for using the query plans Adaptive Server IQ provides Options you can set to modify query processing Tips for ...

Page 477: ...query optimizer creates a query plan Adaptive Server IQ helps you evaluate queries by letting you examine and influence the query plan using the options described in the sections that follow For details of how to specify these options see the Adaptive Server IQ Reference Manual Query evaluation options The following options can help you evaluate the query plan All of these options are OFF by defau...

Page 478: ...sking overflows INDEX_PREFERENCE Sets the index to use for query processing The Adaptive Server IQ optimizer normally chooses the best index available to process local WHERE clause predicates and other operations which can be done within an IQ index This option is used to override the optimizer choice for testing purposes under most circumstances it should not be changed JOIN_ALGORITHM_PREFERENCE ...

Page 479: ...with an estimated result under this limit it rejects the query and returns an error The default is 100 000 000 rows ROW_COUNTS Specifies whether the database will always count the number of rows in a query when it is opened Default is OFF Turning on this option guarantees an accurate count but can slow the start of query processing Network performance The following sections offer suggestions for s...

Page 480: ...used to handle network traffic between the highest volume workstations and the server Isolate heavy network users In case A clients accessing two different database servers use one network card That means that clients accessing Servers A and B have to compete over the network and past the network card In the case B clients accessing Server A use a different network card than clients accessing Serv...

Page 481: ...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 ...

Page 482: ...l 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 Figure 12 7 Large data transfers and larger packet sizes ...

Page 483: ...CHAPTER 12 Managing System Resources 463 Process at the server level Filter as much data as possible at the server level Figure 12 8 Work at the server level ...

Page 484: ...Network performance 464 ...

Page 485: ... step in tuning Adaptive Server IQ performance is to look at your environment You have various options Use system monitoring tools each system and site has different tools in place Use one of the stored procedures that displays information about Adaptive Server IQ See the next section for more information Determine appropriateness of index types See Chapter 4 Adaptive Server IQ Indexes for more in...

Page 486: ...se 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 288 Build Time Sat Mar 11 2000 21 39 55 EST File Format 23 on 03 18 1999 Catalog Format 2 Stored Procedure Revision 1 Page Size 131072 8192blksz 16bpp Number of DB Spaces 8 Number of Temp Spaces 2 DB Blocks 1 12132344 IQ_SYSTEM_MAIN DB Blocks 12545280 24677623 mydb_2 DB Blocks 25090560 372229...

Page 487: ...s taking place within Adaptive Server IQ and stores them in a log file Buffer cache performance is a key factor in overall performance of Adaptive Server IQ Using the information the monitor provides you can fine tune the amount of memory you allocate to the main and temp buffer caches If one cache is performing significantly more I O than the other reallocate some of the memory appropriately Real...

Page 488: ... the buffer cache IO Combined physical reads and writes by the buffer cache cache displays activity in detail for the main or temp buffer cache The fields displayed are Finds Find requests to the buffer cache Creats Requests to create a page within the database Dests Requests to destroy a page within the database Dirty Number of times the buffer was dirtied modified HR Percentage of above satisfie...

Page 489: ...onnid main_or_temp suffix If you do not specify a suffix it defaults to iqmon io displays main or temp private buffer cache I O rates and compression ratios The fields displayed are Reads Physical reads performed by the buffer cache Lrd KB Logical kilobytes read in Prd KB Physical kilobytes read in Rratio Compression ratio of logical to physical data read in Writes Physical writes performed by the...

Page 490: ...as locked repeated for the temp cache woTO Number times lock was granted without timeout repeated for the temp cache Loops Number times IQ retried before lock was granted repeated for the temp cache TOs Number of times IQ timed out and had to wait for the lock repeated for the temp cache BWaits Number of Busy Waits for a buffer in the cache repeated for the temp cache IOLock Number of times IQ loc...

Page 491: ...that has ever been in use NThrds Current number of existing threads Resrvd Number of threads reserved for system connection use Free Number of threads available for assignment Locks Number of locks taken on the thread manager Waits Number of times IQ had to wait for the lock on the thread manager interval specifies the reporting interval in seconds The default is every 60 seconds The minimum is ev...

Page 492: ...e monitor The command you use to stop a monitor run is similar to the one you use to start it except that you do not need to specify any options Use this syntax to stop the IQ buffer cache monitor IQ UTILITIES MAIN PRIVATE INTO dummy_table_name STOP MONITOR Examining and saving monitor results The monitor stores results in an ordinary text file This file defaults to dbname connection main iqmon fo...

Page 493: ...8 0 0 163 98 8 2 0 0 0 1 357 100 0 0 0 0 0 0 0 0 4662 99 8 10 0 0 3 7 1740 100 0 0 0 0 7 8 0 0 1386 99 8 3 0 0 0 2 1716 100 0 0 0 0 0 3 0 0 1472 91 2 129 0 0 6 1 1327 100 0 0 0 0 3 2 0 0 1152 99 7 3 0 0 0 8 4137 100 0 0 0 0 2 8 0 0 262 97 7 6 0 0 1 7 1149 100 0 0 0 0 0 4 0 0 1358 98 0 27 0 0 0 1 853 100 0 0 0 0 5 4 0 0 321 80 1 64 0 0 1 9 458 100 0 0 0 0 4 3 0 0 102 82 4 18 0 0 1 9 14 100 0 0 0 0 ...

Page 494: ...put Reads Lrd KB Prd KB Rratio Writes Lwrt KB Pwrt KB Wratio Mn 10 40 34 1 18 14 56 23 2 43 Mn 0 0 0 0 00 21 84 34 2 43 Mn 0 0 0 0 00 7 28 11 2 43 Mn 0 0 0 0 00 22 88 35 2 48 Mn 0 0 0 0 00 63 252 100 2 51 Mn 0 0 0 0 00 54 216 93 2 32 Mn 0 0 0 0 00 64 256 101 2 52 Mn 0 0 0 0 00 62 248 94 2 62 Mn 0 0 0 0 00 73 292 110 2 65 Mn 0 0 0 0 00 105 420 121 3 47 Example of bufalloc option The buffalloc optio...

Page 495: ...Wait HTLock HTWait FLLock FLWait 0 66 0 0 0 0 1 0 5 0 4 0 1 2958 0 0 0 0 160 0 1117 0 6 0 1 1513 0 0 0 1 378 0 2 0 8 0 1 370 0 0 0 0 94 0 2 0 10 0 1 156 0 0 0 0 46 0 2 0 12 0 1 885 0 0 0 0 248 0 2 0 14 0 1 1223 0 0 0 0 332 1 2 0 16 0 1 346 0 0 0 0 66 0 2 0 18 0 The contention results for the temp cache are Temp Cache LRULks woTO Loops TOs BWaits IOLock IOWait HTLock HTWait FLLock FLWait 70 0 0 0 0...

Page 496: ...100 12 62 1 1 10 100 7 12 100 12 62 0 0 10 100 7 12 100 12 58 1 5 10 100 7 12 100 12 58 0 0 Avoiding buffer manager thrashing Operating system paging affects queries that need buffers which exceed the free memory available Some of this paging is necessary especially as you allocate more and more physical memory to your buffer caches However if you overallocate the physical memory to your buffer ca...

Page 497: ...oblem If so then reset your buffer sizes as described in Managing buffer caches Monitoring paging on Windows NT systems Windows NT provides the NT Performance Monitor to help you monitor paging To access it select the object Logical Disk the instance of the disk containing the file PAGEFILE SYS and the counter Disk Transfers Sec This should be on a separate disk from your database files You can al...

Page 498: ...93 0 0 0 3342988 183972 0 17 58 0 0 0 0 276 1051 746 2 4 94 0 0 0 3342860 183632 0 119 314 0 0 0 0 203 1660 529 3 4 94 0 0 0 3342748 182316 2 109 184 0 0 0 0 187 620 488 4 2 95 0 0 0 3342312 181104 2 147 96 0 0 0 0 115 256 260 9 2 89 0 0 0 3340748 179180 0 899 26 0 0 0 0 163 836 531 4 4 92 0 0 0 3328704 167224 0 2993 6 0 0 0 0 82 2195 222 4 7 89 The first line of the above output provides a summar...

Page 499: ...2 0 30 0 0 3251112 15764 0 475 2480 310 4450 0 1432 1498 199 1717 87 13 0 The above output is from even later On the third line of the output it shows that the system has reached its threshold for the amount of free memory it can maintain At this point page outs po field or KB paged out occur and the level of system mode CPU sy field increases accordingly This situation results because physical me...

Page 500: ...System utilities to monitor CPU use 480 ...

Page 501: ...ilities available to IQ users on Windows NT systems For information on remote data access see the Adaptive Server IQ Installation and Configuration Guide for Windows NT Client server interfaces to Adaptive Server IQ This section describes the key concepts of the Adaptive Server IQ client server architecture and provides the conceptual background for the rest of the chapter If you simply wish to us...

Page 502: ...ient libraries and you can connect through either ODBC or JDBC This contrasts with Adaptive Server IQ 11 5 and earlier which required separate Open Client libraries and did not support JDBC Programming Interfaces and application protocols Adaptive Server IQ supports two application protocols An application protocol specific to Adaptive Server IQ and Adaptive Server Anywhere is used for ODBC JDBC a...

Page 503: ...nd servers can communicate with other servers via remote procedure calls In order for products to interact with one another each needs to know where the others reside on the network This network service information is stored in the interfaces file The interfaces file The interfaces file is usually named sql ini on PC operating systems and interfaces or interfac on UNIX operating systems The interf...

Page 504: ...documentation for the DSEDIT utility For more information on DSEDIT see the Utility Programs book for your platform included with other Sybase products Starting DSEDIT The dsedit executable is held in the SYBASE bin directory which is added to your path on installation You can start DSEDIT either from the command line or Windows NT only by double clicking dsedit exe from the Windows Explorer When ...

Page 505: ...ox and click OK to enter the server name The server entry appears in the Server box To specify the attributes of the server you must modify the entry Server entry name need not match server command line name The server name entered here does not need to match the name provided on the Adaptive Server IQ command line The server address not the server name is used to identify and locate the server It...

Page 506: ... appears Select TCP from the Protocol list box and enter a value in the Network Address text box For TCP IP addresses take one of the following two forms computer name port number IP address portnumber The address or computer name is separated from the port number by a comma Machine name The machine on which the server is running is identified by a name or an IP address On Windows and Windows NT y...

Page 507: ... receiving requests on the machine name and port number specified It does not verify anything about database connections To ping a server 1 Ensure that the database server is running 2 Click the server entry in the Server box of the dsedit session window 3 Select Ping Server from the Server Object menu The Ping window appears 4 Click the address that you want to ping Click Ping A message box appea...

Page 508: ...hin an organization allowing users to access multiple data sources without having to know what the data looks like or where it is located In addition OmniConnect performs heterogeneous joins of data across the enterprise enabling cross platform table joins of targets such as DB2 Sybase Adaptive Server Enterprise Adaptive Server Anywhere Oracle and VSAM Using the Open Server interface Adaptive Serv...

Page 509: ... server classes asaodbc and asajdbc To connect to Adaptive Server IQ 11 x use server class asiq Client side requirements In order to use Sybase client applications to connect to an Open Server including Adaptive Server IQ you need the following Open Client components The Open Client libraries provide the network libraries that your application needs to communicate via TDS DSEDIT The Directory Serv...

Page 510: ...db On UNIX you can include this parameter in the start_asiq command Open Client settings To connect to this server the interfaces file at the client machine must contain an entry specifying the machine name on which the database server is running and the TCP IP port it uses For details on setting up the client machine see Configuring IQ Servers with DSEDIT Configuring your database for use with Op...

Page 511: ...ql_environment This procedure sets the following options SET TEMPORARY OPTION TSQL_VARIABLES ON SET TEMPORARY OPTION ANSI_BLANKS ON SET TEMPORARY OPTION TSQL_HEX_CONSTANT ON SET TEMPORARY OPTION CHAINED OFF SET TEMPORARY OPTION QUOTED_IDENTIFIER OFF SET TEMPORARY OPTION ALLOW_NULLS_BY_DEFAULT OFF SET TEMPORARY OPTION AUTOMATIC_TIMESTAMP ON SET TEMPORARY OPTION ANSINULL OFF SET TEMPORARY OPTION CON...

Page 512: ...nnections 1 Create a procedure that sets the database options you want For example you could use a procedure such as the following CREATE PROCEDURE my_startup_procedure BEGIN IF connection_property CommProtocol TDS THEN SET TEMPORARY OPTION QUOTED_IDENTIFIER OFF END IF END This procedure changes only the QUOTED_IDENTIFIER option from the default settings 2 Set the LOGIN_PROCEDURE option to the nam...

Page 513: ... example the following interfaces file excerpt defines two servers live_credit and test_credit live_credit query tcp ether host8832 5555 master tcp ether host 8832 5555 test_credit query tcp ether host8832 7777 master tcp ether host 8832 7777 Start the server s and set up an alias for a particular database The following command sets live_credit equivalent to creditcard db start_asiq n amxcredit_li...

Page 514: ...Characteristics of Open Client and jConnect connections 494 ...

Page 515: ...ons 278 foreign keys 284 ANSI code pages about 320 choosing 331 ASCII conversion on insert 208 conversion option 204 data format 172 ASCII character set about 319 ASCII character sets about 319 asiqdemo database 8 ASTMP environment variable disk space 93 atomic compound statements 242 Autocommit ODBC configuration 68 AUTOMATIC_TIMESTAMP option Open Client 491 Autostop connection parameter ODBC con...

Page 516: ...er space ODBC configuration 70 buffers disabling operating system buffering 437 build number 17 BYTE ORDER option LOAD TABLE statement 182 C c switch 35 cache See Also buffer cache 467 writing to 289 cache pages prefetching 450 cache size setting for Catalog Store 35 CALL statement about 229 examples 232 parameters 245 syntax 239 case sensitivity collations 342 command line 29 database and server ...

Page 517: ...ing 349 custom 339 349 351 definition 317 file format 339 internals 339 ISO_1 332 multibyte 336 OEM 334 WIN_LATIN1 332 column delimiters load format option 179 LOAD TABLE statement 177 column names international aspects 322 column set to during load 213 column width insertion issues 208 columns adding 123 changing 123 deleting 123 retrieving row identifiers 11 command delimiter setting 265 command...

Page 518: ...network 60 overview 50 to database on foreign host 55 troubleshooting 93 using data source 59 constraints effect on performance 274 CONTINUE_AFTER_RAISERROR option Open Client 491 control statements list 239 conversion options DATE 210 DATE format specification 210 DATETIME 212 213 substitution for zero length cells 210 CONVERSION_ERROR database option 220 conversions between Adaptive Server Enter...

Page 519: ...uring loading 204 converting 202 converting between Adaptive Server Enterprise and Adaptive Server IQ 217 creating with sp_addtype 10 dropping user defined 10 FLOAT 218 integer 218 matching Adaptive Server IQ and Adaptive Server Enterprise 217 money 219 REAL 218 retrieving 11 specifying in table creation 120 SQL and C 271 database administrator See Also DBA 354 See DBA database administrator DBA d...

Page 520: ...rview 3 size 111 stopping 47 temporary data 6 unloading 46 utility 18 validating 9 working with objects 99 DatabaseSwitches connection parameter about 73 DataSourceName connection parameter about 73 DATE conversion option 204 load conversion option 210 DATE data type specifying format for conversion 210 date data types matching Adaptive Server Enterprise and Adaptive Server IQ data 219 DATE format...

Page 521: ...sableMultiRowFetch connection parameter about 73 disk cache definition 451 disk caching performance impact 451 disk space allocating 114 indexes 143 saving 312 disk striping Adaptive Server IQ 442 definition 442 internal 443 rules 443 use in loads 443 DLLs calling from procedures 268 DML Data Manipulation Language 16 DMRF connection parameter about 73 Driver Not Capable error ODBC configuration 68...

Page 522: ...XECUTE IMMEDIATE statement procedures 264 exporting data about 174 overview 171 extended characters about 319 external procedures about 268 F failures media 377 system 377 FETCH statement procedures 252 file data source name See FileDSN FileDataSourceName connection parameter See FileDSN FileDSN connection parameter 63 73 creating 71 distributing 64 See Also data sources 64 FileDSN file data sourc...

Page 523: ...147 recommended use 146 HG index additional indexes 147 High_Group index See HG index High_Non_Group index See HNG index HNG index 148 additional indexes 149 advantages 148 comparison to other indexes 149 disadvantages 148 recommended use 148 hold cursors 297 313 I I O performance recommendations 441 identifiers case insensitivity 322 international aspects 322 IF statement syntax 239 importing fro...

Page 524: ...prise and Adaptive Server IQ 218 Integrated connection parameter about 73 integrated logins default user 92 network aspects 92 ODBC configuration 69 operating systems 86 using 89 integrity constraints 275 276 overview 273 Interactive SQL command delimiter 265 See Also DBISQL 101 window problems 96 interface libraries connections 50 interfaces file configuring 483 internal build number 17 INTO clau...

Page 525: ...318 language support about 315 collations 344 multibyte character sets 336 overview 315 LEAVE statement syntax 239 leveness ODBC configuration 70 LF index 145 additional indexes 146 advantages 146 comparison to other indexes 146 disadvantages 146 recommended use 145 libctl cfg file DSEDIT 484 lightweight processes 439 LIMIT option LOAD TABLE statement 183 Links connection parameter about 73 Livene...

Page 526: ...l 34 for Catalog Store cache 35 overhead 423 paging 420 reducing requirements 431 requirements for loads 424 restricting use by queries 449 See Also buffer caches 427 unwired 436 wired 435 memory message load notification messages 187 message log 17 Adaptive Server IQ 446 MESSAGE statement procedures 257 messages dropping 10 language resource library 318 memory notification 187 recorded in message...

Page 527: ...BC data sources 63 driver location 78 initialization file for UNIX 72 translation driver 348 UNIX support 72 ODBC Open Database Connectivity data sources 65 ODBC data sources configuring 67 UNIX 72 ODBC translation driver ODBC configuration 67 OEM code pages about 320 choosing 331 OmniConnect support 488 ON clause joins 163 ON EXCEPTION RESUME clause about 258 not with exception handling 262 ON FI...

Page 528: ...205 monitoring 467 multi user 450 procedures 230 RAM disk use 452 restricting queries with iqgovern 448 performance monitor examples 473 performance tuning introduction 465 permissions command line switches 36 conflicts 372 connect 357 DBA authority 354 external procedures 269 granting passwords 357 group 363 group membership 364 groups 356 365 in Sybase Central 360 361 individual 356 inheriting 3...

Page 529: ...lts 246 247 returning results from 233 savepoints in 265 security 369 See Also stored 7 SQL statements allowed in 243 structure 243 system 7 table names 266 using 230 variable result sets from 250 verifying input 267 warnings 260 writing 265 process threading model 439 protocols switch 38 ps command monitoring CPUs on UNIX 479 PUBLIC group 367 PWD connection parameter about 73 Q qualified object n...

Page 530: ...es renaming files 400 result sets multiple 250 procedures 234 249 variable 250 RETURN statement about 246 REVOKE statement about 362 ROLLBACK statement 305 compound statements 242 procedures 265 ROLLBACK TO SAVEPOINT statement 305 ROW DELIMITED BY option LOAD TABLE statement 184 row id displaying 199 in notification message log 199 ROW_COUNTS option 459 S sample database 8 sar command monitoring C...

Page 531: ...SQLLOCALE environment variable about 328 336 setting 346 SQLSTATE variable introduction 255 Start connection parameter about 73 start line ODBC configuration 69 Start parameter embedded databases 58 START ROW ID option 197 about 198 201 INSERT statement 192 partial width inserts 194 198 start_asiq starting asiqdemo database 47 starting server on UNIX 23 starting 48 StartLine connection parameter a...

Page 532: ...e permissions 374 SYSTABAUTH view permissions 375 SYSTABLE table integrity 285 view information 131 SYSTABLEPERM table permissions 374 system tables 12 about 127 character sets 340 indexes in 133 national languages 340 permissions 374 SYSCOLLATION 340 SYSINFO 340 users and groups 374 views 131 system views integrity 285 permissions 374 SYSUSERAUTH view permissions 375 SYSUSERLIST view permissions ...

Page 533: ...ble size and performance 156 TRACEBACK function 257 transaction log about 445 in system recovery 308 renaming 405 transaction processing about 287 transactions about 287 cursors in 311 definition 287 ending 288 in recovery 307 procedures 265 rolling back 307 savepoints 305 starting 288 subtransactions and savepoints 305 Translation driver ODBC configuration 67 translation drivers ODBC 348 troubles...

Page 534: ...VALUES option INSERT statement 190 VARCHAR data zero length cells 210 variable width character sets about 321 version string 17 versioning about 287 291 at table level 292 cursors and 312 in system recovery 308 isolation levels 302 performance impact 309 temporary tables 298 vertical insertions about 197 views creating 128 deleting 131 differences from permanent tables 128 inserting and deleting 1...

Page 535: ...5 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 ...

Page 536: ...Index 516 ...