Sybase 12.4.2 manual

Administratio n and Performance Gu ide Adaptive Server® IQ 12.4.2[...]

DOCUMENT ID: 38152-01-12 42-01 LAST REVISED: April 2000 Copyright © 19 89-2000 by Sybase, I nc. All righ ts reserved. This publication pertains to Sybase database management soft ware and to any subsequent release until otherwise i ndicated in new editions or technical notes. Information in this document is subject to change without notice. The so[...]

Content s iii About This B ook ........................................................................................................................ xvii CHAPTER 1 Overview of Adaptiv e Server IQ System Administr ation ............ 1 Introduction to Adaptive Server IQ ................................................... 1 System administration ta[...]

Contents iv Starting the server on Windows NT ............................................... 26 Starting the server from the NT Start menu ............................ 26 Typing the server startup command ........................................ 26 Running the server outside the current session ...................... 27 Using command-line switches[...]

Conte nts v Steps in establishing a connection .......................................... 77 Locating the interface library ................................................... 78 Assembling a list of connection parameters ............................ 79 Locating a server ..................................................................... 81 Locat[...]

Contents vi Deleting views ....................................................................... 131 Views in the system tables .................................................... 131 Working with indexes ................................................................... 132 Introduction to indexes ..............................................[...]

Conte nts vii Types of join hierarchies ....................................................... 164 Modifying tables included in a join index ............................... 167 Inserting or deleting from tables in a join index ..................... 168 Table versioning controls access to join indexes .................. 169 Estimating the size of a[...]

Contents viii Handling conversion errors on data import ........................... 220 Tuning bulk loading of data .......................................................... 221 Improving load performance during database definition ....... 221 Setting server startup options ................................................ 222 Adjusting your enviro[...]

Conte nts ix Using cursors on SELECT statements in procedures ........... 253 Errors and warnings in procedures .............................................. 255 Default error handling in procedures ..................................... 256 Error handling with ON EXCEPTION RESUME .................... 258 Default handling of warnings in procedures [...]

Contents x Referential integrity is unenforced ......................................... 284 Integrity rules in the system tables ............................................... 285 CHAPTER 8 Transac tions and Versioning ....... ............. ................... ............. . 287 Overview of transactions and versioning ...........................[...]

Conte nts xi Understanding locales .................................................................. 323 Introduction to locales ........................................................... 323 Understanding the locale language ....................................... 324 Understanding the locale character set ................................. 325 U[...]

Contents xii Managing individual user IDs and permissions ............................ 356 Creating new users ....... ........................................................ 357 Changing a password ............................................................ 357 Granting DBA and resource authority ................................... 358 Granting p[...]

Conte nts xiii The RESTORE statement ..................................................... 399 Restoring in the correct order ................................................ 403 Renaming the transaction log after you restore .................... 405 Validating the database after you restore .............................. 406 Restore requires exclu[...]

Contents xiv Working space for inserting, deleting, and synchronizing ..... 447 Options for tuning resource use ................................................... 448 Restricting concurrent queries ............................................... 448 Limiting a query’s memory use .............................................. 449 Limiting queries[...]

Conte nts xv Examples of monitor results .................................................. 473 Avoiding buffer manager thrashing .............................................. 476 Monitoring paging on Windows NT systems ......................... 477 Monitoring paging on UNIX systems ..................................... 477 System utilities to mon[...]

xvi[...]

xvii About This Book This book , Adaptive Ser ver IQ Admini stration and Performa nce Guide , presents administrative co ncep ts and p roced ures an d performance tun ing recommendations for Syb ase Adaptive Server IQ, a high-perform ance decision support server design ed specifically for data warehouses and data marts. Audienc e This g uide is fo [...]

Related documents xviii T able 1: Guide to using this bo ok Related docum ent s Documentation fo r Adaptive Server IQ: • Introd uct ion to Ad apti ve Serv e r IQ Read and try the hand s-on exercises if you are unfamiliar with Adap tive Server IQ, with the Sybase Cen tral datab ase managemen t tool, or with Interactive SQL. T o learn h ow to... Re[...]

About This Book xix • Adaptive Server IQ Reference Manual Read for a full description of the SQL language , ut ilities, stored procedures, data typ es, and system tab les supported by Adaptive Server IQ. • Adaptive Ser ver IQ Tro ubleshoot ing and Error Me ssages Gu ide Read to sol ve probl ems, perf orm syst em recove ry and datab ase repair ,[...]

Related documents xx[...]

1 CHAPTER 1 Overview of Adaptive Server IQ System Administration About thi s chapter This chapter prov ides a brief introd uction to Adaptive Server IQ and an overview of IQ system administration. Introduction to Adaptive Server IQ Adaptive Server IQ is a high -performance d ecision support serv er design ed specifically f or data wareho using. Thi[...]

System admini stration tasks 2 Rapid acce ss to m any data s ou rce s Adaptive Server IQ can integrate data from d iverse sources—not just IQ databases, but othe r datab ases in the Adapti ve Server family , as well as non - Sybase databases and flat files. Y ou can im port this data into yo ur IQ database, so that you can take advantage of IQ&ap[...]

CHAPTER 1 Overview of Adaptive Server IQ Sys tem Administration 3 T able 1- 1: Administrativ e tasks Security overview The DBA is responsib le for maintaining datab ase security . Adaptive Server IQ provides security controls by me ans of the privileges you can assign to users. If you want to kn ow how to ... Look in ... Install and configure Ad ap[...]

Tools for system admi nistra tion 4 T ypes of users Adaptive Server IQ r ecognizes three categories of user s for each IQ database: • The dat abas e adm inistr ator, or DBA , has complete autho rity to perform all operations on that datab ase. This guide i s addressed prim arily to the DBA , who typically carries ou t most administrative tasks. ?[...]

CHAPTER 1 Overview of Adaptive Server IQ Sys tem Administration 5 • Syb ase Cent ral is an app licatio n for managi ng Sybase dat abases. I t helps you manage d atabase objects and perform common ad ministr ative t asks such as creati ng databases , backing up databas es, addin g users, addi ng tables and indexes, and monitoring datab ase perform[...]

Catalogs and IQ 6 • Th e Catalo g S tore When you create an IQ database, all three stores are created autom at ically . Y ou create IQ datab ases usi ng the proced ures described in Chapter 3, “W orking with Data base Ob ject s”. The IQ Store The IQ S tore is the set of Adaptive Server I Q tables. Y ou can have o ne or more permanent I Q St o[...]

CHAPTER 1 Overview of Adaptive Server IQ Sys tem Administration 7 Anywhere tables have a dif feren t format than IQ tables. While the commands you use to create objects in an Anywhere database are the same as those for an IQ Stor e, there are some dif ferences in the features you can specify in those commands. Always use the command syntax in this [...]

Stored procedures 8 Y ou can also create y our own stor ed procedures. See Chapter 6, “Using Procedures an d Batches” for deta ils. Note S tatements shown in examples gen erally use the asiqdemo database, a sample database installed as part of Ad aptive Serv er IQ. For a diagram of this database’ s structure, see Introduc tio n to Adaptive Se[...]

CHAPTER 1 Overview of Adaptive Server IQ Sys tem Administration 9 T able 1- 2: S tored Procedures for the IQ S tore Adaptive Server Ent erprise system and cat alog procedures Adaptive Server E nterprise provides system an d catalog procedu res to carry out many admin istrative func tions and to obtain sy stem information. Adaptive Server IQ has imp[...]

Stored procedures 10 System procedur es are built-in stored procedures used for getting reports from and updating system tables. Catalog stored procedures r etrieve information from the system tables in tabular form. Note While these procedu res perform th e same functions as they do in Adaptive Server Enter prise and pre-V ersion 1 2 Adaptive Serv[...]

CHAPTER 1 Overview of Adaptive Server IQ Sys tem Administration 11 Adaptive Server Enterprise cat alog procedures Adaptive Server IQ implemen ts all the Adaptive Server Enterprise catalog procedures with the exception of the sp_colum n_privileg es pr ocedure. The implemented catalog pro cedures are described in the following table. The following li[...]

System tables and vi ews 12 System t ab les and views Adaptive Server IQ system tables cont ain all of the information the d atabase server needs to man age your IQ system. The system tabl es reside in the Catalog S tore, and are sometimes called catalog tables. For some system tables there are also views that make it easier to display the informat[...]

CHAPTER 1 Overview of Adaptive Server IQ Sys tem Administration 13 SYSDOMAIN Lists the number , name, ODBC type, and precisio n of each predefine d data type SYSFILE L ists operating system files and dbspace nam es for the da tabase SYSFKCOL Associates each foreig n key column wit h a prim ary key co lumn SYSFOREIGNKEY Contains general info rmation[...]

System tables and vi ews 14 System views present the information from their corresponding system tables in a more read able format. In some cases, they omit password info rmation so that they can be accessible to all users. System views include: SYSLOGIN Lists User Profile name s that can connect to the data bas e with an integrate d log in SYSOP T[...]

CHAPTER 1 Overview of Adaptive Server IQ Sys tem Administration 15 For a complete description o f system tables and views and their con tents, see the Adaptive Server IQ Reference Manual . System vie w Descrip tion SYSCA T ALOG Lists all tables and views from SYST A BLE SYSCOLAUTH Presents column update pe rmission information from SYSCOLP ERM SYSC[...]

Commands and Functions 16 Commands an d Function s All Adaptive Server IQ com mands are SQL statements. SQL stands for S tructured Quer y Language , a language commonly used in data base applications. Adaptive Server I Q SQL uses the same syntax as Adaptive Server Anywhere SQL; the on ly d ifferences are for certain product capab ilities that are s[...]

CHAPTER 1 Overview of Adaptive Server IQ Sys tem Administration 17 • Manipulate date and time data (for example, TODA Y , DA TE DIFF , DA TEP ART , MINUTES ) • Conver t retrieved dat a from o ne format to ano ther ( CAST , CON VERT ) Message lo gging A message log file exists for each database. Th e default name of this file is dbname.iqmsg . T[...]

The utility database 18 The date and time of the software build ap pears in the version string in ISO datetime format: YYYY-MM-DD hh:mm:ss wher e The message log co ntinues to exist until yo u drop th e database. If your messag e log ever becomes too lar ge, yo u can archive it while no users are connected to the database, and then create a new , e[...]

CHAPTER 1 Overview of Adaptive Server IQ Sys tem Administration 19 For more inform ation on the utility database , see Chapte r 3, “Conf igurin g Client/Server Comm u nications” in y our Adaptive Server IQ Ins tallation and Configu ration Guid e . Comp atibility with earlier versio ns V ersion 12 of Adaptive Server IQ differs markedly from earl[...]

Compat ibility w ith earlier v ersio ns 20[...]

21 CHAPTER 2 Running Adaptive Server IQ About thi s chapter Three steps are required for you to start using Adaptive Se rver IQ: • The database se rver mu st be start ed. • The database mu st be s tarted. • Y ou must conn ect to the database. Adaptive Server IQ gives you great flexibilit y in performing these th ree steps. This chapter explai[...]

Server command lines 22 • S tart the server and the samp le database with a Syb ase-provided config uration fi le. See “Starting the asiqdemo database” on pa ge 47. • Place a server sta rtup command in a shortcut or desk top icon. • Include a server startline in an ODBC data source. See “Crea ting a nd editing ODBC data sources” on pa[...]

CHAPTER 2 Running Adaptive Server IQ 23 Y ou can choose from many co mmand-line options or switches to specify such features as permissions required to start a d atabase or stop the serv er , and the network protoco ls to use. The command-line switches are on e means of tuning Adaptive Server IQ behavior and p erformance. There are slight variation[...]

Starting the server on UNIX 24 Note t he server starti ng dir ecto ry Note what directory you are in when you start the server . The server startup directory determ ines the location of any datab ase files you create wit h relative pathnames. If y ou start the server in a dif f erent directory , Adaptive Server IQ cannot find th os e databas e file[...]

CHAPTER 2 Running Adaptive Server IQ 25 For an explanation o f commonly used startup parameters, see “Using command-line switches” on page 28. T yping the s erver st artup command Y ou can also start the database server by entering the following co mmand at the UNIX prompt: asiqsrv12 [ server-switches ] [ database-file [ database-switches ]] Th[...]

Starting the server on Windows NT 26 S t artin g the server on Wind ows NT This section describes methods for starting the database server that are specific to W indows NT systems. Y ou can also u se any of the generic method s described elsewhere in this chap ter . Note t he server starti ng dir ecto ry Be sure to make note of what d irectory you [...]

CHAPTER 2 Running Adaptive Server IQ 27 See “Using command-line sw itches” on page 28 for a des cription of commonly used start up parameter s. Note T o start th e server with out starting an y d atabase, you o m it the database file from the asiqsrv12 command an d specify a servername. See “Nami ng the server and da tabases” on page 31 for[...]

Using command-li ne switches 28 Running the UNIX dat abase server as a daemon T o run the UNIX databas e server in the backgr ound, and en able it to r un independen tly of the current session, you run it as a daemon . Note Do not use ’ &’ to run the database server in the b ackground. It will not work. Y ou must inste ad run the database s[...]

CHAPTER 2 Running Adaptive Server IQ 29 Some of the values you can set with command-line switches can also b e changed with the SET OPTION command . For details of this command and its opt i on s, an d for a complete li st of command-line sw itch es and full r efer ence information on them, see the Ad aptive Server IQ Reference Manual . Displa yi n[...]

Using command-li ne switches 30 For example, the f ollowing configur ation file starts the database mydb.db , on the database server named Elora, with a Catalog cache size of 16MB, TCP/IP as a network protocol and a specified port number , user connections limited to 10, and a Catalog page size o f 4096 bytes. -n Elora -c 16M -x tcpip(port=2367 -gm[...]

CHAPTER 2 Running Adaptive Server IQ 31 Configu ration fil e for the sa mple dat abase The asiqdemo.cfg file, which yo u use to start t he sample database, s ets startup parameters to the recommen ded defaults. Y ou can also use this file as a template for your own con figuration files . Chapter 3, “Running and Connecting to Servers”, Introd uc[...]

Using command-li ne switches 32 asiqsrv12 mydb.db sample.db Nami ng dat abases Y ou can nam e databas es by su pplying a -n switch follow ing the database file. For example, the f ollowing command line starts a d atabase and names it: asiqsrv12 mydb.db -n MyDB Naming a database lets you use a nickname in place of a file na me that may be difficult [...]

CHAPTER 2 Running Adaptive Server IQ 33 Y ou should adopt a s e t of naming co nven tions for yo ur s erv ers and data bas es , as well as for all other database objects, that inclu des a case specification. Enforcing nami ng conven tions can preven t problems for users . Controlling performanc e from the comm and line Several command-line options [...]

Using command-li ne switches 34 T o override these settings for the curr ent server session, specify the server startup optio ns -iqm c (main cache size) and -iqtc (temp cache size) to open the database and reset the def aults. The default sizes are 8MB fo r the main cache and 4MB for the temporary cache, which are too low f or any active database [...]

CHAPTER 2 Running Adaptive Server IQ 35 Unwired memo ry The -iqsme m switch creates a memory pool to increase total available m emory . This switch is available on all UNIX platforms, but is required in some cases: • On H P syst ems u se -iqs mem if you want to use more than 2GB of memory . The va lue sh ou ld be b etween 500 an d 1400M B. • On[...]

Using command-li ne switches 36 When a database server is running with multiple database s, the checkpoint time specified by the fir st database started will be used unless overridden by this switch. If a value of 0 is entered, th e defaul t value of 60 minutes is u sed. Reco very ti me The -gr parameter let s you set the maximum nu mber of mi nute[...]

CHAPTER 2 Running Adaptive Server IQ 37 • UTILITY_D B —Only those users who can connect to the utility_d b database can create and drop da tabases. S ee “The utility database” on page 18 for information. S topp ing th e server The -gk op tio n limits the users who can sh ut down a server to tho se with a certain level of permiss ion in the [...]

Using command-li ne switches 38 Setting up a client/server environment Three swi tches can h elp yo u set up your clie nt/server environmen t. • -x specifies communication protocol o ptions. • -tl sets the netwo rk co nnection ti meo ut. • -ti sets the client conn ectio n timeout. See the sections that follo w for details. Selecting communica[...]

CHAPTER 2 Running Adaptive Server IQ 39 Additional parameters can be ad ded to tune t he behavior of the server for each protocol. For ex ample, th e followin g command line instructs the server to use two networ k cards, on e with a s pecified port numb er . This co mmand must be entered all on one line, even th ough it appears on mult iple lines [...]

Using command-li ne switches 40 Setting t he defa ult client timeo ut Adaptive Server IQ di sconnects client connections that have not subm itted a request for the number of min utes you specify with the -ti s witch. By disconn ectin g inactive conn ections, this optio n frees any locks tho se connec tions hold . The defau lt is 24 0 (4 hours ). Ra[...]

CHAPTER 2 Running Adaptive Server IQ 41 St arting mu ltiple servers or clients on the same machine In a produ ction environ ment, it wo uld be unusual to have more th an one server running on the sam e system. In a develo p ment environment, ho wev er , this situation can occur . If you are running more than one server or client on the same UNIX ma[...]

Monitoring server activity 42 demo.001.srvlog demo.002.srvlog janedemo.001.srvlog For information about your most recent session, choose the log with the largest number for the desired server . Issue a tail –f command to view the log contents. For example: % tail -f demo.002.srvlog When you run start_asiq , specify the –z option to enhance th e[...]

CHAPTER 2 Running Adaptive Server IQ 43 Stopping the dat abase server The preferred ways to sto p the database server are: • In UNIX, use the stop_asi q utility . For detail s, see “Exam p le — Sto p a server with stop_asiq”. Note that when stop_a siq is used, the following message app ears: "Please note that ’stop_asiq’ will shutd[...]

Stopping the database server 44 Exampl e — S top a server with sto p_a si q The following ex ample uses the stop_asiq uti lity on UNIX system s to shut down an Adap tive Server IQ server and close all user con nections to it. When you issue the stop_a siq command, Adaptive Server IQ lists all the servers owned by ot her users , follo wed by the s[...]

CHAPTER 2 Running Adaptive Server IQ 45 If no run ning ser vers were start ed by your user ID, Adapti ve Server I Q displays information about servers run by other users, then a message like the following: There are no servers owned by ’janed’ Exam ple —Stop a server fro m DBISQL The following examp le stops a server from DBISQL: STOP ENGINE [...]

Starting and stopping databas es 46 Examples of comm ands that do not st op a server cleanly includ e: • S toppin g the pro cess in th e W indows NT T ask Manager Pro cesses window . • Using a UNIX ki ll command. S t arting and stopping dat abases Y ou can start d atabases when you start the server , or after the serv er is running. T o start a[...]

CHAPTER 2 Running Adaptive Server IQ 47 Permissi on lim it ations The -gd server comm and-line option determines the p ermission leve l required to start databas es. By default, th is option is set to DBA , so that onl y us ers wi th database ad ministra tor privi leges can start IQ database s. However , you can also set thi s option to ALL or NO N[...]

Starting and stopping Sy base Central 48 where pat h is your Adaptive Serv er IQ installation director y , demoasiqdemo.cfg specifies the configu ration file, and demo asiq demo.db is the sample database file. The asiqsrv12 command starts the server in a dedicated window . Y ou can start database servers by enter ing this command at a system comm[...]

CHAPTER 2 Running Adaptive Server IQ 49 Figure 2-1: Th e Sybase Central Hiera rchy Plug-ins for Sybase Central, such as the Adaptive Server IQ database management system, occupy the first level in the Syb ase Central hierarch y after the root level. A plug-in is a graphical tool for managing a p articular product. When you ins tall the pr oduct, yo[...]

Introduction to connections 50 3 If Adaptive Server IQ (ASIQ) is listed, select Register . If not, select Load. Use the Browse butto n to find and select th e file ASIQPlugin.jar . Click OK. Stopping Sybase Central T o st op S ybase Cent ral, selec t Fi le → Exit. Introduction to con nections The remainder of this chapter describes how client app[...]

CHAPTER 2 Running Adaptive Server IQ 51 How connections are est ablished T o establish a conn ection, the client applicatio n calls functions in one of th e supported interfaces. Adaptive Server IQ supports the following interfaces: • ODBC — ODBC connections are discussed in this chapter . • Embedded SQL — Em bedded SQL connections ar e dis[...]

Introduction to connections 52 Connection parameters specify connections When an application co nnects to a database, it uses a set of co nnection parameters to def ine the connection. C onnection parameters inclu de information such as the server name, the database name, an d a user ID. A keyword-val ue pair , of the form parameter=val ue , specif[...]

CHAPTER 2 Running Adaptive Server IQ 53 Y ou mu st enter a connection string on a sing le line, with the parame ter settings separ ated by semic o lons. Connection parameters are p assed as connec tion strings Connection parameters are p assed to the interface library as a connection str ing . This s tring cons ists of a set of paramet ers, separa [...]

Simple connection ex amples 54 For steps in connecting to a datab ase using Sy base Central , see the Int roducti on to Adaptive Server IQ . For mo re d etailed info rmation on available connection parameters and their use, see “Connection parame ters” on page 73. Connecting to a database from DBISQL Many examples and exercises throughout the d[...]

CHAPTER 2 Running Adaptive Server IQ 55 % cp $SYBASE/asiq12/tix/default.tix This file contr ols key sequ ences for DBISQL and impro ves the comm and window display . For more inform ation, see Chap ter 6, “Getting S tarted with DBISQL” in Introduction to Ad aptive Server IQ . 4 Sta rt DBISQL by en tering dbisql -c "uid=DBA;pwd=SQL;eng= ser[...]

Simple connection ex amples 56 Y ou can include th e -c parameter to specify conn ection parameters in the dbisql command, as described in the procedure abo ve for connecting to UNIX. If you omit th ese parameters, the DBISQL logon wi ndow appears. 2 Enter the user ID DBA and the password SQL This is the defaul t user ID and password for A daptive [...]

CHAPTER 2 Running Adaptive Server IQ 57 ❖ T o connec t to a dat abase from DBISQL on UNIX: 1 St art the s e rver and t h e dat abas e by ty ping at a sy st em co mmand pro mpt : start_asiq dbname 2 Sta rt DBISQL by t yping at a system comm and prompt: dbisql -c "uid=userID;pwd=password;eng= dbname ;links=tcpip" For example, to con nect [...]

Simple connection ex amples 58 The following connection p arameters sh ow how to load the sample databa se as an embedded database: dbf=pathasiqdemo.db uid=dba pwd=sql where path is the name of your Adaptive Server IQ installat ion d irectory . Usin g the S tart para meter The following con nection parameters show how you can customize the startup[...]

CHAPTER 2 Running Adaptive Server IQ 59 5 Leave all other fie lds blank, and click OK. Adap tive Server IQ starts up and loads the sample database, an d DBISQL connects to the datab ase. Connecting using a da t a source Y ou can save sets of connecti on paramet ers in a data source. Data sour ces can be used by ODBC and E mbedded SQL applications l[...]

Simple connection ex amples 60 Connecting to a server on a network T o connect to a database running on a network server somewhere on a local or wide area network, the client software must be able to locate the database server . Adaptive Server I Q provides a network library (a DLL or shared library) that hand les th is task. Network co nnections o[...]

CHAPTER 2 Running Adaptive Server IQ 61 The network library searches for a server by broadcasting over th e network, which can be a time-consu ming proc ess. Once th e networ k library locates a server , the client library stores its name and network address in a file. Users should never need to use this file directly . Adaptive Server IQ reuses th[...]

Simple connection ex amples 62 pwd= password Note Do not use these parameters if m ore than one local server is runn ing, or you may co nnect to the wrong server . Default d atabas e If more than one server is ru nnin g, you need to sp ecify whic h one you wish to connec t to. If o nly one database h as been started on that server , you d o not nee[...]

CHAPTER 2 Running Adaptive Server IQ 63 2 Using the SQLCONNECT env ironment variable settings if any command line values are miss ing. Adaptive Serv er IQ database utilities do no t set this variable au tomatically . Fo r a description of the SQLCONNECT environment var iable, see Chapter 1, “Env ironment V ar iables and Registry En tries,” in A[...]

Working with ODBC data sources 64 DSNs and FILEDSNs Y ou specify a data source either as a DSN (data sour ce name) or as a FileDSN (file data source name). Y ou can ref erence a data source in the W indows NT registry using th e DSN connectio n parameter: DSN= my data source Y ou can ref erence a data sour ce held in a file using the FileDSN conne [...]

CHAPTER 2 Running Adaptive Server IQ 65 T o create or edit DSNs or File DSNs on UNIX systems, use a text editor . For DSNs you can edit the .odbc. ini file directly . For File DSNs, create a file with the name you ch oose, using the file e xtension .dsn . Note Sybase recommends that, to avoid am biguity , you be as specific as possible in creating [...]

Creating and editing ODBC data sources 66 ❖ T o crea te an OD BC User Da t a Source: 1 Select Settings → Control P anel → OD BC o r Se l e c t P r og r a m s → Sybas e → Adaptive Server IQ → ODBC Administrator 2 In the ODBC Data Source Administrator , click Add on the User DSN tab. 3 Select the Adaptive Server IQ 12 from the list of d r[...]

CHAPTER 2 Running Adaptive Server IQ 67 3 Click Con figure. 4 On the OD BC Conf ig urati on dia log bo x, cli ck T e st Conn ectio n. If you cannot access the Data Source, check that you have filled out the various tabs wi th correct file and pathnames. T o edit a data source, select one from the list in the ODBC adm inistrator window and click Con[...]

Creating and editing ODBC data sources 68 Microsoft applicati ons (keys i n SQL St atistics) Check this box if yo u wish foreign keys to be returned b y SQL statistics. The OD BC specifi cat ions states that primary and foreign keys sh ould not b e returned by SQL statistics, however , some Microsoft applications (such as V isual Basic and Access) [...]

CHAPTER 2 Running Adaptive Server IQ 69 Login t ab Use integrated login Connects using an integrated login . The User ID and password do not need to be sp ecified. T o use this ty pe of login users mus t have been grante d integrated l ogin pe rmission. The database bein g connected to must also be set up to accept integr ated logins. Only users wi[...]

Creating and editing ODBC data sources 70 For more information on the parameters in the Database tab, see th e EngineNam e, Start Line, Datab aseName, Databas eFile, and Au toS top connection par ameters in the chapter “Connection and Co mmunication Par am eter s” in t he Adaptive S erver IQ Reference Manua l . Network t ab Select the network p[...]

CHAPTER 2 Running Adaptive Server IQ 71 Display debu gging info rmation in a log file The name of the file in which th e debuggin g inform ation is to be sav ed. Additional con nection p arameters Enter any additional swit ches here. Parameters set througho ut the remainder of this dialog take pr ecedence over parameters ty ped here. Creating a Fil[...]

Using ODBC data sources on UNIX 72 Using ODBC dat a sources on UNIX On UNIX operating systems, ODBC data sources are held in a file named .odbc.i ni . When creating a .od bc.ini file on any UNIX system, you must use the long form of each identifie r , for example: [My Data Source] EngineName=myserver CommLinks=tcpip UserID=DBA Password=SQL Network [...]

CHAPTER 2 Running Adaptive Server IQ 73 Connectio n p arameters Adaptive Server IQ connection parameters are listed in the following table. Fo r a full description of each of these connection parameters, see C hapter 3, “Connection and Commun ication Parameters” in the Adaptive Server IQ Reference Manu al . Parameter S hort form Argumen t Descr[...]

Connection parameters 74 DatabaseSwitches DBS Strin g Prov id e da ta ba s e - spe cif ic s wit ches wh en starting a d ataba se. DataSourceName ** DSN Strin g T ell the ODBC driver manager where to loo k in odbc.ini to find ODBC data source information. Debug DBG Boolea n Prov id e dia g no s tic information on communi cations li n ks on sta rtup.[...]

CHAPTER 2 Running Adaptive Server IQ 75 * Not su pported i n ODBC conne ctio ns ** V erbose form of keyword n ot s upporte d in DSN and FI LEDSN connec tion parameters Notes • Boolean (tr ue or false) ar guments are either YES, ON, 1, or TRUE if true, or NO, OFF , 0, or F ALSE if false. • Connection par ameters and their values are case insensi[...]

Connection parameters 76 • The entries in a connection string are read left to right. If the same parameter is specified mor e than once, the last one in the string applies. • If a s tring contai ns a DSN or FILE DSN entry , the pr ofile is read f rom the configuration file, and the en tries from the file are u sed if they are not already set. [...]

CHAPTER 2 Running Adaptive Server IQ 77 How Adap tive Server IQ makes connec tions This section describes how the interface libr aries establish connections. Who needs to read this secti on? In many cases, est ablishing a connection to a database is straightforward using the information presented in the preceding sections of this chapter . However [...]

How Adaptive Server IQ makes connections 78 3 Locate a server . Using the connection p arameters, the ODBC driver or Embedded SQL interf ace library must loc ate a database server on your machine or o ver a network. 4 Locate the data base . Once it locates the server , the ODBC driver or Embedded SQL interf ace library must loc ate the database you[...]

CHAPTER 2 Running Adaptive Server IQ 79 Assembling a list of connection parameters The following figure illu strates how the in terface libraries assemble the lis t of connection param eters they will use to estab lish a connectio n. Notes Key points from the f igure are as follows: • Pr ecedence — Parameters held in more than one pl ace are su[...]

How Adaptive Server IQ makes connections 80 • CommBufferSpace Ignored if ano ther connection h as already set this param eter . • Unconditiona l Ignored i f the da tabase is already loaded or if the ser ver is already run ning. The interface library uses the co mpleted list of connection pa rameters to attempt to connect.[...]

CHAPTER 2 Running Adaptive Server IQ 81 Locating a server The next step in establishing a connection is to attempt to locate a server . If the connection parameter list includes a server name (E NG parameter), the interface library carries ou t a search first for a database server of that name, followed by a search over a network. If no ENG paramet[...]

How Adaptive Server IQ makes connections 82 • The networ k search involves a sear ch over one or mo re of the proto cols that Adaptive Server I Q supports. For each protocol, th e network library starts a single po rt . All connectio ns over th at prot ocol at any one time u se a sing le por t. • Y ou ca n specify a set of n etwork communicatio[...]

CHAPTER 2 Running Adaptive Server IQ 83 Locating the database If the interfaces libr ary successfully locate a server , it then tries to locate the database. For example: Notes • If you rely on the DBF pa rameter , the DBF path must either be an abso lute path, or relative to where the server was started, in ord er for Adaptive Server IQ to find [...]

How Adaptive Server IQ makes connections 84 Server name caching for faster connection s The network library lo ok s for a database serv er on a netw ork by bro adcas ti n g over th e networ k using t he CommLinks connection parameter . T uni ng the b roadc ast Th e CommLink s parameter takes as argument a string th at lists the p rotocols to use an[...]

CHAPTER 2 Running Adaptive Server IQ 85 Interactive SQL connections The Interactive SQL (DBISQL) utility h as a differ ent behavior from the default Embedded SQL behavior when a CONNECT statement is issued w hile already connected to a database. If n o database or server is specified in the CONNECT statement, Interactive SQL connects to the cu rren[...]

Using an integrated login 86 Using an integ rated log in The integrated log in feature allows you to m aintain a single user ID an d password for both da ta bas e conn ect io ns and o perat ing sy stem and/ or network logins . This s ection de scribes the integr ated logi n feature. Operati ng sy stem s support ed Integrated login capabi lities are[...]

CHAPTER 2 Running Adaptive Server IQ 87 Using integrated login s Several steps must be implemented in or der to connect successfully via an integrated lo g in. ❖ T o use an integrated log in: 1 Enabl e the integr ated login feature in a database b y settin g the value of th e LOGIN_MODE database op tion to either M ixed or Inte grated ( the op ti[...]

Using an integrated login 88 Exampl e The following SQL statement sets the value of the LOGIN_MODE database option to Mix ed , allowing both standard and integrated login conn ections: SET OPTION "PUBLIC".LOGIN_MODE = Mi xed Creating an inte grated login User profiles can only be mapped to an existing database user ID. When that database [...]

CHAPTER 2 Running Adaptive Server IQ 89 Connecting from a client appl ication A client application can connect to a database using an integrated login in one of the fo ll o w in g ways: • Set the INTEGRA TED parameter in the list of conn ection parameters to yes . • Specify neither a user ID nor a password in the connection string or connection[...]

Using an integrated login 90 Setting the attribu te ’ Integrated=yes’ in an O DBC data source cau ses database connection attem p ts using that DSN to attemp t an integrated log in. If the LOGIN_MODE dat abase op tion is set to S tandard , the ODBC dr iv er pro m pts the user for a d atabase user ID and passwor d. Security concerns: unres trict[...]

CHAPTER 2 Running Adaptive Server IQ 91 • An integrated lo gin mapping ex ists between th e user dsmith and the database user ID DBA . When the u ser dsmi th connects to the server with her correct login ID and password, she connects to the database as DBA , a user with full admini strative righ ts. • But anyone else attempting to connect to th[...]

Using an integrated login 92 For mo re infor mation on usi ng the SET OPTION statement see Chapter 9 , “SQL S tatements” in Adaptive Server IQ Reference Manual . Network aspects of integrated logins If the database is located on a network server , then one of two cond itions must be met for integrated login s to be used: • The user prof ile u[...]

CHAPTER 2 Running Adaptive Server IQ 93 T ro ubleshoo ting st artup, shutdown, and con nections See the secti ons that follow for h elp in resolv ing prob lems with y our database server , connections, and DBISQL. For other troub leshooting hints, see the Adaptive Ser ver IQ Tro ubleshoot ing and Error Me ssages Gu ide . What to do if you can’t s[...]

Troubleshooting startup, shutdown, and c onnections 94 Ensure that network communication soft ware is running Appropri ate networ k communi cation sof tware must be instal led and ru nning before you run the databas e server . If you are r unning reliable n etwork so ftware with just one net work installed, this sh ould be straigh tforward. If you [...]

CHAPTER 2 Running Adaptive Server IQ 95 What to do if you can’t connect to a dat abase If you are unable to connect to an Adaptive Server IQ database, check the items described bel ow . • Check that you have en te red your d ata s ou rce name co rrect ly , or that you have selected the cor rect server name for a JDBC connection. • Check that [...]

Troubleshooting startup, shutdown, and c onnections 96 • Check that all of the files exist for the database you have requested. At a minimum, there must be an IQ S tore ( db name. iq ), a Catal og S tore ( dbname.db ), an IQ T emporary S tore ( dbname.iqt mp ), a transaction log ( dbname.log This m ay be mis sing if t he database is newly created[...]

CHAPTER 2 Running Adaptive Server IQ 97 For help in improv ing the appearance of DBISQL windows, or if y ou are unable to use function keys in DBISQL, see the Chapter 6, “Getting S tarted with DBISQL” in Introduction to Ad aptive Server IQ .[...]

Troubleshooting startup, shutdown, and c onnections 98[...]

99 CHAPTER 3 W orking with Dat abase Object s About thi s chapter This chapter describes the mechanics of creating, alterin g, and deleting database objects such as tables, views, and in dexes. The SQL statements for carrying out these tasks are called the Data Definition La nguage (DDL). The defi nitions o f the d atabase ob jects form the dat aba[...]

Building Your Adapti ve Server IQ Databases 100 Sybase W arehouseArchitect helps you d esig n your dat abase. W arehouseArchitect is a component of Sybase W arehouse St udio, an integrat ed platform for desi gning and man aging a data warehouse. No matter what design to ol you use, it is generally th e database admini strator (DBA) who design s the[...]

CHAPTER 3 Working with Database Objects 101 This chapter is concerned with the SQL statements for working with database objects. If you are using Sybase Central, these SQL statements are gener ated for you. The primary sour ce of information abo ut Sybase Cen tral is the Sybase Central online Help . This ch apter giv es only brief poi nters f or ta[...]

Building Your Adapti ve Server IQ Databases 102 Multiplex users: The following steps are for creating a non-multiplex database. T o create a multiplex database, see the Adapti ve Server IQ Multiplex User’s Guide . ❖ T o set up an IQ da taba se: 1 Install and configure Adap tive Server IQ. This step creates the database server and the asiqdemo d[...]

CHAPTER 3 Working with Database Objects 103 Y ou wil l probably want to sch edule data defin ition operat ion s for times when database usage i s low . All other users are blocked fr om readi ng or writ ing to a table while you are creat ing or alteri ng that table, al though for a bri ef time only . If the table is part of a join index, users cann[...]

Building Your Adapti ve Server IQ Databases 104 The DBA and object owne rs can grant au thority to indivi dual users and to groups of users. For complet e informatio n, see Chapter 10, “Managing U ser IDs and Pe rmissions ” Y ou can also use the -gu command-li ne option to set the permission level requ ired to create or delete a database. Selec[...]

CHAPTER 3 Working with Database Objects 105 Space r equi rem ents for IQ S tores The amount of data, and the number and typ es of indexes you create, determine how much sp ace you need in y our IQ da tabase. If you run out of space when loading or inserting into a d atabase, Adap tive Server IQ promp ts you to create another dbspace, an d then cont[...]

Working with databases 106 Each time you run sp_iqestjoin , select one of the suggested index sizes. If you know you will always join the tab les with exact one-to-one matches, use the “Min Case index_size”. If you anticipate occasional o ne-to-many joins, use the “A vg Case index_size”. If y ou anticipate using numerous one-to-many joins, [...]

CHAPTER 3 Working with Database Objects 107 Creating a database When you create a database, th e database server creates the following four dbspac es: The SYSTEM dbspace contains the system tables, which hold the schema definitio n as you bu ild your database. It als o holds a s eparate checkp oint log, rollback log, and optionally a write f ile, t[...]

Working with databases 108 Locat ing and mov ing dat a bas e file s When you create a d atabase, you specify its location. B efore you d o so, consider whether yo u will ever need to move the da tab ase. In order to move a database, it is crucial that you have all of the fi les you ne ed, that they be in a consistent state when yo u move them, and [...]

CHAPTER 3 Working with Database Objects 109 • Case sensitive ( CA SE RES PEC T ).’ABC’ compares NOT EQUAL to ‘abc’. Note that the default login is n ow user ID DB A and pas sword SQL (uppercase). Passwords are case sensitive for a case-sensiti ve database, and case-ins ensitive for a case-inse nsitive databas e. Usernames are always case [...]

Working with databases 11 0 If you omit the directory path, Adaptive Server IQ locates the files as follows: • The Catalog S tore is created relative to the working director y of the server . • The IQ S t ore is created relative to the work ing directory of the server . • The T emporary St ore is created in the same directory as the IQ S tore[...]

CHAPTER 3 Working with Database Objects 111 • The T emporary S tore is i n c:companycompany .iqtmp • The IQ message log f ile is in c:company company .iqmsg CREATE DATABASE ’company.db’ IQ SIZE 20 IQ PATH ’c:companyiqdatacompany.iq’ Exam ple The following statement creates an IQ database called company .db . This database cons[...]

Working with databases 11 2 Adaptive Serv er IQ stores d ata on disk in compress ed form. It un compresses the data an d moves data pages int o memory fo r processing . The IQ page size determines the amount of disk compres sion and the defau lt I/O tran sfer blo ck size for the IQ Store. For most applications, this d efault value is best. For a co[...]

CHAPTER 3 Working with Database Objects 11 3 Choosing a blo ck size for your dat abase Exam ple The following statement creates a large database on a UNIX raw partition with a Catalog P AGE SIZ E of 4KB, and an IQ P AGE SIZE of 128 KB. By de fault, the IQ Stor e size is 8MB and the T emporary S tore is 4MB. CREATE DATABASE ’company’ IQ PATH ’[...]

Working with databases 11 4 Adding dbspaces When you create a database, it has only one file for stor ing permanent IQ data, one file for storing Catalog data, an d o ne file each for the IQ message log and the T empo rary S tore. Each of these files is a d bspace, as described in “Creat ing a data bas e ” . Initially , the d e fi niti ons of a[...]

CHAPTER 3 Working with Database Objects 11 5 When data is stored on raw partitions, you can have one dbspace per drive. When data is stored in a file system, y ou can take advantage of stripin g in the storage syst em. If you use operatin g system or hard ware striping on a m ultiuser system, your st ripe size s hould be a minimum of 1MB, or the hi[...]

Working with databases 11 6 Reser vin g space f or DDL comman ds In the event that you run o ut of sp ace to p e rform an ope rat ion, you will see a message telling you to more space. In addition to space for the new dbspace itself, you also need a small amount of space to issue the ADD D BSP ACE command. T o ensure that you have the space f or th[...]

CHAPTER 3 Working with Database Objects 11 7 Name Val ue ======================================================= Adaptive Server IQ (TM) Copyright (c) 1992-2 000 by Sybase, Inc. All rights reserved. Version: 12.4.2/(32bit mode)/Sun_svr4/OS 5.6/E BF 0000 Time Now: 2000 -03-14 12:05:54.288 Build Time: Sat Mar 11, 2000 21:39:55 EST File Format: 23 o n[...]

Working with tables 11 8 Dropping a dat aba se Droppin g a data base delete s all ta bles and d ata from di sk, in cluding t he transaction log th at record s alterations to the d atabase. It also drops all of the dbspaces associated with the database. T o drop a database, use the following statement: DROP DA T ABASE dbname Y ou mus t specif y the [...]

CHAPTER 3 Working with Database Objects 11 9 2 Click the T ables fo lder for th at database. 3 Double -click Add T able in th e right panel. 4 Enter a Name for the tab le. 5 T o create an IQ table, skip this step. T o create a table in the Catalog Store, double-click the Adv anced T able Prop erties icon, and select SYSTEM from the DB space dr opdo[...]

Working with tables 120 By internally executing the COMMI T statement before creating the table, Adaptive Server IQ makes perm anent all previous chang es to the database. There is also a COMMIT after the table is created. For a full descrip tion of th e CREA TE T ABLE statement, see “CREA TE T ABLE statement” in the Adaptive S erver IQ Referen[...]

CHAPTER 3 Working with Database Objects 121 WHERE l_discount < 0.5 Yo u declare a local temporary table fo r your co nnection o nly , us ing th e DECLARE LOCAL TEMPORARY T A BLE statement. A local temporary table exists until th e connection en ds, or within a com pound statement in which it is declared. The table and its data are co mpletely in[...]

Working with tables 122 If you u se t he AL TER T ABLE command to add a UNIQUE column to an exist ing table, or to designate an existing column as UNIQUE , an HG index is created automatically . For complete in formation on IQ indexing , see Cha pter 4, “Adaptive Server IQ Indexes” Optimizing storage and query performance When you create a p er[...]

CHAPTER 3 Working with Database Objects 123 By contrast, when you specify UNIQUE or PRIM ARY KEY , each value can occur only once in that co lumn. For example, in the employee table, each value of ss_numbe r , the employee’ s social security numb er , can oc cur just once throughout th at column. This u niqueness extends to NULL v alues. Thus, a [...]

Working with tables 124 Altering tab les in Sybase Central The property sheets for tables and column s display all the table or co lumn attributes. Y ou can alter a table definiti on in Sybase Cent ral by displayin g the property sheet for the table or column yo u wish to change, alterin g the property , and clicking OK to commit the ch ange. Alter[...]

CHAPTER 3 Working with Database Objects 125 Creating primary and foreign keys The CREA TE T ABLE and AL TER T A BLE statements allow many attributes of tables to be set, including colum n constraints and checks. Th is section shows how to set tab le attri butes usi ng the pr imary and f oreign key s as an exam ple. Creating a primary key The follow[...]

Working with tables 126 For more information, see th e Sy base Central onlin e Help . Note Multi-column primary keys are n ot enforced, and requ ire the keywor d UNENFORCE D . Primary k ey col umn order i s based on the o rder of the column s during table creation. It is not based on the order of the columns as specified in the primary k ey declara[...]

CHAPTER 3 Working with Database Objects 127 ❖ T o create an u nenforced foreign key in Sybase Central: 1 Connect to the da tabase. 2 Click the T ables fo lder for th at database. 3 Click th e table holding th e primary key , and drag it to t he foreign key table. 4 When the pri mary key tabl e is dropped on the f oreign key t able, the Foreign Ke[...]

Working with views 128 Simila rities bet we en view s and bas e t ables V i ews are similar to the permanent tables of the database (a permanent table is also called a base table ) in many ways : • Y ou can assign access permissions to views just as to base tables. • Y ou can perform SELECT querie s on vie w s. • Y ou can perform INSERT and D[...]

CHAPTER 3 Working with Database Objects 129 CREATE VIEW DepartmentSize AS SELECT dept_ID, count(*) FROM employee GROUP BY dept_ID The information in a view is no t stored separately in the databa se. Each ti me you refer to the view , the associated SE LECT statement is executed to retrieve the appropriate data. On one hand, this is go od because i[...]

Working with views 130 CREATE VIEW viewname AS Insertin g and deleti ng on view s UPDA TE , INSERT , and DELETE s tatements are allowed on some views, but not on ot he rs, d epen di ng on their assoc iated SELE CT statement. Y ou ca nnot update, insert into or delete from vi ews in the follow ing cases: • V iews containing aggregate fun ctions, s[...]

CHAPTER 3 Working with Database Objects 131 • The user was gran ted approp riate perm ission(s ) for th e operati on on th e view by a non-DBA user . This user must be either the owner of the view or have WI TH GRANT OP TION of the approp riate permi ssion(s ) on the view . The owner o f the view must be either: • a DBA, or • a non-DB A, but [...]

Working with indexes 132 Y ou can use DB ISQL to browse the info rmation in these tables. T ype the following statement in the DB ISQL command window to see all the columns in the SYS.SYSVIEW S view : SELECT * FROM SYS.SYSVIEWS T o extract a text file containing th e definition of a specific view , use a statement such as the following: SELECT view[...]

CHAPTER 3 Working with Database Objects 133 Indexes are created on a specified table, or on a set of tab les for join indexes. Y ou cannot create an index on a view . Creating indexes Y ou can create column indexes in thr ee ways: • W ith the CREA TE INDEX command • W ith the Add Ind ex option in Sybase C entral • W ith the UNIQU E or PRIMARY[...]

Working with indexes 134 Figure 3-1: sp_iqind ex resul ts If you o mit the tabl e name from t he command, sp_iq index displ ays t his information for all tables in the database. Removing indexes If a col umn index or joi n index is no longer req uired, you can remove it fro m the database us ing the DROP statement. Y ou can also drop indexes in Syb[...]

135 CHAPTER 4 Adaptive Server IQ Indexe s About thi s chapter This chapter de scribes the Adaptiv e Server IQ index types. It explains how you crea te an ind ex, and p rovid es infor mation to help you decide what index types are b est sui ted for the way y ou use the d ata in your database. It also includes perf ormance and resour ce issues relate[...]

Overview of indexes 136 The default column storag e structure that Adap tive Ser ver IQ creates for each column is actuall y an in dex optimized f or st oring an d project ing data. Depending on the size of your da tabase, the disk space available to you, and the type of queries your us ers submit, you will alm o st certainly want to supplement thi[...]

CHAPTER 4 Adaptive Server IQ Indexes 137 T o take advantage o f the High_Non _Group in dex ty pes for colu mns with nonintegr al nume ric data , use th e NUMER IC or DECIMAL data types, which support up to 254 digi ts to the left or rig ht of the d eci mal poi nt. B e aware t hat some ind ex types are incompat ible, a nd that creating in dexes you [...]

Creating Adaptive Server IQ indexes 138 • Because indexes are co mpact, more data can b e kept in mem ory for subsequent queries, ther eby speeding thro ughput on iterative an alysis. • T uning is data-depend ent, allowing data to be o ptimized once for any number of ad hoc queries. Creating Adaptive Server IQ ind exes Y ou can create a column [...]

CHAPTER 4 Adaptive Server IQ Indexes 139 Y o u c an us e the keyw o rds BEGIN P A RALLEL IQ and END P ARALLEL IQ to delimit an y number of CR EA TE INDE X statements that you want to execute as a group at the same time. These keywords can only be used when creating indexes on IQ t ables, not tempor ary tables o r Adaptiv e Server Anywhere tables. N[...]

Choosing an index type 140 •E a c h CREA TE INDEX statement can create only one index. • Each con nection can create only one index at a time. • If t wo connectio ns issue CREA TE INDEX stat ements on the same tab le, the first statement works; the other g ets an error saying that on ly 1 writer is allowed. • If t wo connecti ons is sue CRE[...]

CHAPTER 4 Adaptive Server IQ Indexes 141 Number of unique val ues in the index Adaptive Server IQ indexes are optim ized according to the n umber of unique (distinct) values they include. When this number reaches certain levels, choose indexes accordin g to the recommend ations in T able 4-1. T able 4- 1: Consideration order Columns fo r which you [...]

Choosing an index type 142 Often, the t ype of data in a column g ives a go od indicatio n how the column will be used. For example, a da te column will prob ably be used for range searches in W HERE clauses, and a co lumn that contain s prices or sales amounts will probably b e used in the projection as an ar gument for ag gregate functio ns ( SUM[...]

CHAPTER 4 Adaptive Server IQ Indexes 143 These estimates are generally valid; however , other factors can take precedence: • For ra nge predicates, t he number o f unique values is a mor e impor tant factor . • W ith the set function s COUNT , C OUNT DISTIN CT , SUM , MIN , MAX , and AV G , in order to use any index other than the default, the [...]

Adaptive Server IQ index ty pes 144 • V ARCHAR data wit h mor e than 255 bytes HNG indexes d o not all ow FLOA T , RE AL , or DOUBLE data. All oth er data types are s upport ed in all i ndex types . If you try to create an index on a column that contains V ARCHAR data greater than 255 bytes, yo u get this error message: An index cannot be created[...]

CHAPTER 4 Adaptive Server IQ Indexes 145 Default column index For any column that has no index d efined, or whenev er it is the most ef fective, query results are produced using the default index. This structure is fastest for projectio ns, but generally is slower than any o f the th ree colu mn index types you define for anything ot her than a pro[...]

Adaptive Server IQ index ty pes 146 Advant ages and disadvant ages of Low_Fast The foll owi ng table lists ad vant ages an d disadv anta ge s of L ow_Fas t index es. T able 4-5: LF a dvantages/d isadva ntages Compari son with ot her indexes HNG / HG The main factor to co nsider is the number of unique v alues within a column. Use LF if the number i[...]

CHAPTER 4 Adaptive Server IQ Indexes 147 Advant ages and disadvant ages of Hi gh_Group The following table li sts advantages and disadvan tages of High_Group indexes. T able 4- 6: HG advant ages/disadv antages Comp arison with other ind exes LF The determinin g factor is the number of un ique valu es. Use High _Group if the number of un ique values[...]

Adaptive Server IQ index ty pes 148 However , because multi-column pr imary keys are always unenf orced, the automatical ly created High_ Group ind ex for a mul ti-column pr imary k ey is a phantom i ndex: it i ncludes all o f the key co lumns, but does not co ntain any data. Thi s struct ure is u sed for qu ery optimi zation, but not fo r reso lvi[...]

CHAPTER 4 Adaptive Server IQ Indexes 149 T able 4- 7: HNG advanta ges/disadv antages Comp arison to other indexe s • HNG need s less disk space than HG but can't perfo rm GROUP BY efficiently . • In choo sing betw een LF and HNG , the determining facto r is the number of unique values. U se HNG when the number of u nique values is greater [...]

Adaptive Server IQ index ty pes 150 AND l_suppkey = s_supp key AND c_nationkey = s_nati onkey AND s_nationkey = n_nati onkey AND n_regionkey = r_regi onkey AND r_name = ’ASIA’ AND o_orderdate >= ’1994- 01-01’ AND o_orderdate < ’1995- 01-01’ GROUP BY n_name HAVING n_name LIKE "I%" AND SUM(l_extendedprice*(1-l_di scount)) [...]

CHAPTER 4 Adaptive Server IQ Indexes 151 Adding co lumn indexes after in serting dat a When you create an additional column index, the CREA TE INDEX command creates the new index as p art of the individu al table and as part o f any join indexes that includ e the column. If the existing column indexes in the individual table already contain data, t[...]

Using join i ndexes 152 How join indexes are use d for queries After you create a join index, its use is determined by the cr iteria of the SELECT statement . If a join index exists that jo ins the tables in th e FROM clause by the relationship sp ecified in the WHERE clause, or if a join index exists th at is based on ANSI join synt ax for na tura[...]

CHAPTER 4 Adaptive Server IQ Indexes 153 Adaptive Server IQ join hierar chies have one table at the top of the tree where the join ends . This table, known as t he top tabl e , does not con nect to any other tables, although other tables connect to it. The top table always represents the “man y” side in a one -to-m a ny rela tionshi p. Dependin[...]

Using join i ndexes 154 Y ou can set the fr equency of these messages with the NOTIFY_MO DULUS option, and o v erride the option value in eith er the CR EA TE DA T ABASE or LOAD T AB LE command. For examples of these messages, see “Interpreting notification messages” on page 187. The join hierarchy in que ry resolution Adaptive Server IQ can us[...]

CHAPTER 4 Adaptive Server IQ Indexes 155 If the join colum n is made up of more than one column, the combinat ion of th e values must be un ique on the “one” side. For example, in the asiqdemo database, the id in the customer table and the cust_id in the sales_order table each contain a customer I D. The c ustomer table contains one row for eac[...]

Using join i ndexes 156 2307,103,103,’Niedringhaus’ W arnin g! If the one-to-many relationship is incorrect, the jo in cannot be synchronized until you r e move th e extra rows fro m the “on e” tabl e. If y ou try to synchronize, you get a Duplicate Ro w error , and the transaction rolls back. When you create a jo in index, you use ANSI FUL[...]

CHAPTER 4 Adaptive Server IQ Indexes 157 Of course, this approach requ ires more disk space to build an additional join index and mor e index buil ding tim e (not to mention increased mai ntenance). In the case of a subset join index, th e additional join index repea ts a subset of the information already in the full join index . Y ou must decide w[...]

Using join i ndexes 158 The index remains unavailable un til all steps are complete. However , you can adjust the order o f some steps, d epending on the need s of your site: • Y ou can combine steps 1 and 2 by definin g relati onships when you creat e the table. • Y ou can load the data either before or after you create the join index. If you [...]

CHAPTER 4 Adaptive Server IQ Indexes 159 Performance hint s for synchroni zation Synchron ization can be time-co nsuming . T o im prove per formance, tr y these suggest ions: • Schedu le synchr onization during o ff-peak hours . • Synchronize join ind exes individually rather than all at on ce. • Synchronize after ex ecuting an entire set o f[...]

Using join i ndexes 160 Using foreign ref erences Adaptive Server IQ u ses foreign keys to define the relation ships among columns that will b e used in join in dexes, and to op tim ize q ueries. However , Adaptive Server IQ d oes not enforce fo reign key constraints. For this reason, when you sp ecify a primary k ey-foreign key relationsh ip, you [...]

CHAPTER 4 Adaptive Server IQ Indexes 161 Alternatively , you could create the table without the REF ERENCES clause, and then add the foreign key later , as is done in the followin g AL TER T ABLE statement: ALTER TABLE DBA.sales_order ADD FOREIGN KEY ky_so_customer (cust_id) REFERENCES DBA.customer (id) UNENFORCED Specifying t he join t ype when cr[...]

Using join i ndexes 162 • Use parentheses to contro l th e order in which the join relatio ns hips are evaluate d. Parent heses control eval uation or der just as they do in mathematics, that is, innermost pairs are ev aluated first. With this method you st art with th e top tab le in the outermos t set of par enthes es, then any intermediate lev[...]

CHAPTER 4 Adaptive Server IQ Indexes 163 search-condition : [ ( ] search-expression [ AND search-expression ] [ ) ] •T h e join-cla use can be expr essed either with or without parenth eses. •T h e ON clause can reference only two tables. One must be the current one, and the other can be any one table in the current join tree. • All join pr e[...]

Using join i ndexes 164 A natural join between the id columns of sales_order and sales_ order_ite ms makes more sense. I n this case, the columns with the same n ame should contain match ing values . The command t o create a jo in ind ex based on a natur al jo in between these two tabl es is: CREATE JOIN INDEX sales_order_so_it ems_join FOR sales_o[...]

CHAPTER 4 Adaptive Server IQ Indexes 165 Linear joins Y ou can think of a linear join as a tr ee with no branches. Each table in th e hierarchy is related to the tab le above it, un til you reach the top table. In Fig u re 4-1 o n pa g e 153. T ables A, D, and F co nstitute a linear join hierarchy . T ables C, E, and F for m another linea r join hi[...]

Using join i ndexes 166 Exampl e In the samp le databas e used thro ughout th is book , the sales_or der table contains three for eign keys, each of which is related to the pr imary key o f another table. Y ou can create th is table using the following commands: CREATE TABLE "DBA"."sales_order"( ( "id" integer NOT NULL[...]

CHAPTER 4 Adaptive Server IQ Indexes 167 SELECT sales_rep FROM sales_order WHERE sales_rep = 299 the results sho w 20 rows with 29 9 in the sales_rep colu mn. However , if you enter: SELECT emp_id FROM employee WHERE emp_id = 299 the results show on ly one row with 299 in the emp_id column. Note Query op timizations for star joi ns rely on the unde[...]

Using join i ndexes 168 Adaptive Server IQ automatically app lies the changes to the jo in index at the same t ime as it ch anges th e base table. Y ou do not need to s ynchronize the joi n index after any AL T ER T ABLE on nonj oined colu mns. Other res trictio ns on AL T ER T A BLE for joi n indexes includ e the following: • Y ou ca nnot rename[...]

CHAPTER 4 Adaptive Server IQ Indexes 169 T able versi oning controls access to join indexes Any table is only available for write use to a single user at any given time. For join index es, this means that wh en one us er is up dating any tab le in a join index, no one else can upda te any of the tables in that ind e x. All th e joined tables remain[...]

Estimating the size of a joi n index 170 These calculations should give you an idea of how much disk space you need for the join index. T he results include the segment size in bytes, and the number of blocks. The procedu re also tells you ho w long it will take to create the join index. If you wan t to know the ac tual size of an existing join ind[...]

171 CHAPTER 5 Moving Dat a In and Out of Dat abases About thi s chapter This c ha p ter de s cri be s seve r al meth ods of moving data into and o ut of your database, an d explains when you should use each of them. It also discuss es conversio n issues f or data inse rted from ot her types of data base s. Import and ex port overview Adaptive Serve[...]

Import and export overview 172 • T o remove specific rows from a table, use the DELETE statement. • T o change existing rows in a table, you can also use the UPDA TE statement. From DBIS QL you can export dat a to anot her database in a vari ety of fo rmats, or produce a tex t file as output. See the next section fo r a list of formats and how [...]

CHAPTER 5 Moving Data In and Out of Databases 173 Specifying an output for mat for Interacti ve SQL Y ou can set the DBISQL output format in three ways: • Select Command → Opti ons from the DBI SQL menu bar , and then choose an Output Format from the dr opdown list. T o make this th e default outp ut format, click Permanent. • Specify the DBI[...]

Exporting data from a database 174 Exporting dat a from a dat abase This section tells h ow to exp ort data from an Adaptiv e Server IQ database. Note T o export IQ data from your database in this version of Adaptive Server IQ, Sybase recommends that you use the method described in this chapter . Y ou may also export data by using a front end too l[...]

CHAPTER 5 Moving Data In and Out of Databases 175 Redirec ting outpu t and mes sages The >& redirection symbol red irects all output includ ing error messages an d statistics for the command on which it appears. For example: SELECT * FROM employee >& empfile Do not enclose the file name in qu otation marks. This example outputs the SE[...]

Bulk loadi ng data using the LOAD TABLE statemen t 176 Usin g comma nd file s to l oad dat a T o load larg e amounts of data, most u sers create command files. T o create a command file, follow th e ins tructi ons in the chapter entitled “Gettin g Started with DBISQL” in the Introd uction to Adaptive Server IQ . T ransacti on proc es si ng and [...]

CHAPTER 5 Moving Data In and Out of Databases 177 For each column, y ou can specify a colu mn-spec . If you omit this optio n, the format information in th e load- option s applies to this column . The column- spec and load -optio ns format inform ation tell Adap tiv e Server IQ what type of data to expect, and ho w to convert it into a com p atibl[...]

Bulk loadi ng data using the LOAD TABLE statemen t 178 • DA TE , TIME , DA T ETIME or TIMES T AMP string as ASCII characters. Y ou must define th e input-date-format or input- dateti me-format of the strin g using on e of the co rresponding formats for the date and datetime data ty pes supported by Adaptive Server IQ. For information ab out these[...]

CHAPTER 5 Moving Data In and Out of Databases 179 • If a backslash () precedes the characters n , x , or it is considered an escape character . For this reason, to indicate directory p aths in W indows NT systems, you must rep resent the backslash character by two backslashes if the next character is any of those listed. (It is always safe to [...]

Bulk loadi ng data using the LOAD TABLE statemen t 180 STRIP option Wi t h STRIP turned on (the de fault), tr ailin g blanks are stripped from v alues before they are inserted. This is ef fective only f or V ARCHA R data. T o turn the S TRI P option of f, enter the clause as follo ws: ...STRIP OFF ... T rai lin g blanks are s tr i pped only for non[...]

CHAPTER 5 Moving Data In and Out of Databases 181 Y ou can specify load op tions in any order . Syntax fo r load-option s is as follows: [ { BLO CK F ACTOR number | BLOCK SIZ E number } ] ... [ B YTE ORDE R { NA TIVE | HIG H | LOW } ] ... [ LIMIT number-of-rows ] ... [ N OTIFY number-of-rows ] ... [ O N FILE ERRO R { ROLLBACK | FI NISH | CONTINU E [...]

Bulk loadi ng data using the LOAD TABLE statemen t 182 Only one single-byte char acter can be used as an escape character . Note Because you mu st specify ESCAPES OFF in this version of Adap tive Server IQ, the ESCAPE CH ARACTE R option has no ef fect. It is p rovided for compatibility with Adaptive Server Anyw here. WITH CHECKPOINT ON clause If th[...]

CHAPTER 5 Moving Data In and Out of Databases 183 LOAD TABLE nn (l_orderkey, l_quantity ASCII(PREFIX 2), FILLER(2), FROM ’C:iqarchivemill.txt’ BYTE ORDER LOW LIMIT option Specifies the maximum n umb er of rows to in sert into the table. The default is 0 for n o limit . LIMIT works together w ith the SKIP option. SKIP indicates where to beg[...]

Bulk loadi ng data using the LOAD TABLE statemen t 184 • ROLLBACK aborts the entire transaction (the default). • FINISH finishes the insertions already completed and end s the load opera tion. • CONTINUE return s an error but only skips the file to continue the load operation. Y ou cann ot use this op tion with pa rtial-wid th inserts. PREVIE[...]

CHAPTER 5 Moving Data In and Out of Databases 185 Y ou cannot us e this op tion if any input fields c ontain b inary data . W ith t his option, a row terminator causes any missing f ields to be set to NULL. All rows must have the sam e row delimiters, and it mu st be distinct from all colum n delimiters. The row and field de lim iter strings cannot[...]

Bulk loadi ng data using the LOAD TABLE statemen t 186 ST ART ROW ID opti on Specifies the id number of a row in the table where inse rtions shoul d begin. This opt ion is used f or parti al-widt h inser tions , which insert into a subset of the columns in th e table. If you are inserting dat a into an existing ro w , you must define the format of [...]

CHAPTER 5 Moving Data In and Out of Databases 187 Interpreting notificatio n messages By default, Adaptiv e Server IQ displays information ab out your database during in sert and load o perations. The statistics in these messages indicate when you need to p erform maintenance and op timization tasks, such as add ing more dbspaces. The messages also[...]

Bulk loadi ng data using the LOAD TABLE statemen t 188 T able 5-2: Me mory me ssages Main IQ Store blocks messages This line describes the permanent (main ) IQ S tore: Main Blks: U63137/6%, Buffers: U12578/L7 T able 5-3: Ma in blocks IQ T emporary Store blocks mes sage This message provides similar information to the Main IQ S tore Blocks message e[...]

CHAPTER 5 Moving Data In and Out of Databases 189 T able 5- 4: Main IQ St ore file mess age In general, assu ming the bu f f er cache is full, you shou ld hav e b etween 10 and 1000 logical read s per physical read. A lower value indicates excessive thrashing in the buf fer man ager . More than 1000 times lar ger can indicate that you may be o vera[...]

Using the INSERT statement 190 Using the INSERT st atemen t The INSERT s tatement allows you to insert data without first p utti ng it into a flat file. Using this comm and, you can either: • Insert a specified set of values row by row • Insert directly from database tables See the sections that follo w for details of these two forms o f the co[...]

CHAPTER 5 Moving Data In and Out of Databases 191 • F o r DA TE , TI ME , and TIMEST AM P or DA TETIME columns, you must use a specific fo rmat. See “Converting data on insertion” for inf ormation on data type conversions. See the Ada ptive Server IQ Reference Manual for a complete description o f Adaptive Server IQ data types . Note The TIME[...]

Inserting selected rows from the database 192 SKIP number-of-rows ST ART RO W ID number This form o f the INSERT statement lets you ins ert any number of rows of data, based on the results o f a general SELECT statement. For maximum ef ficiency , insert as many rows as possib le in one INSERT statement. T o insert additi onal sets of rows after the[...]

CHAPTER 5 Moving Data In and Out of Databases 193 • T ables in either the IQ S t ore or the Catalog Store o f the database you are currently connected to. • T ables in an Adaptive Server Enterprise da tabase. •A prox y tab le in your current database, that corresponds to a table in a database on a remote server . Adaptive Server IQ's rem[...]

Inserting selected rows from the database 194 LOCATION ’detroit.asiq11db’ { SELECT l_shipdate, l_orderkey FROM lineitem } • The des tination and source co lumns may hav e dif ferent names . • The or der in which you sp ecify the columns is imp ortant, because data from the first s ource column named is inserted in to the first target colum [...]

CHAPTER 5 Moving Data In and Out of Databases 195 Importing dat a interactiv ely If you are inserting small quant ities of data, you may prefer to enter it interactively through DBISQL, using the INSE RT statement For example, you can insert listed valu es a single row at a time with the following command: INSERT INTO T1 VALUES ( ... ) For more inf[...]

Inserting into primary and foreign key c olumns 196 • The second user's transaction begins before the fir st user's transaction commits. • The second user tries to u pdate after the first user's transaction commits, but before th e joi n in dex is syn chro nized. This problem occurs becau se Adaptive Server IQ makes a new v ersio[...]

CHAPTER 5 Moving Data In and Out of Databases 197 An easy way to enforce the integrity is to create and run s tored procedur es that roll back any transaction that vio lates a constraint. Y ou can use an EXISTS clause to specify violations. Partial-wid th insertions By default, new rows are inserted wherever ther e is space in the index es, and eac[...]

Partial-width insertions 198 Use ST ART ROW ID to specify at which row you want to start the insert. This allows you to insert in to some of the co lum ns in a row with on e p artial-widt h INSERT or LOAD T ABL E statement, and insert in to the other columns in th e same row with additi onal INS ERT or LOAD T AB LE statements. If you try to insert [...]

CHAPTER 5 Moving Data In and Out of Databases 199 2 For the sec ond an d any s ubseq uent partial- width inser ts for th e same set of rows, use the ST ART ROW ID option to specify the row where the insert started. This n umber is the record n umber at the beginn ing of the insert message log, as in this example: In table ’department’, the full[...]

Partial-width insertions 200 NULL 3 28 NULL 3 NULL 4 13 NULL 4 NULL 5 9 NULL 5 190 NULL NULL 19 6 215 NULL NULL 2127 7 29 NULL NULL 1376 8 200 NULL NULL 119 9 59 NULL NULL 4 10 (10 rows affected) Exampl e 2 The fo llowing example shows the correct way to do this operation. Note the ST ART ROW ID option in the second LOAD T ABLE statement. LOAD TABL[...]

CHAPTER 5 Moving Data In and Out of Databases 201 29 3 28 1376 3 200 4 13 119 4 59 5 9 4 5 (5 rows affected) T o ensure that th e data from the secon d two colum ns is inserted in to the sam e rows as the first t wo colum ns, you must s pecify the row numb er in the ST AR T ROW ID optio n on the INSERT command for the next two columns. Using the FI[...]

Converting data on insertion 202 213 NULL 190 152.00 1 24 NULL 215 172.00 2 (2 rows affected) If you later load data from another file into ps_partkey and ps_availqty , insertio ns begin correctly at the next row , as shown below . LOAD TABLE partsupp (ps_partkey ASCII(6), ps_availqty ASCII(6), FILLER(2)) FROM ’C:iqarchivemill2.txt’ SELEC[...]

CHAPTER 5 Moving Data In and Out of Databases 203 • When yo u insert d ata selected from another d atabase • When yo u load data f rom a flat fi le When an explicit conv ersion is needed, the way that you specify the conversion depends on whether yo u are l o adi ng from a flat f ile o r ins ert ing s el ected ro ws: •I n t h e LOAD T ABLE st[...]

Converting data on insertion 204 Y ou can use a synonym in terchangeably with its s tand ard data type. Data is stored internally as the standar d data type, where syno nyms exist. In er ror messages, the stand ard name appears in p lace of the syno nym. Note By default, Adaptive Server IQ assum es that input da ta is b inary (numeric data) and tri[...]

CHAPTER 5 Moving Data In and Out of Databases 205 Note When loading from a flat file, u se binary data if y ou have a choice of using bi nary or char acter data. Using binar y input can improve performance by eliminating con version costs. Dat a conversions in IQ When you use the INSERT statement to insert data directly from a database rather than [...]

Converting data on insertion 206 The first table sho ws implicit (I), explicit (E), and un supported (U) conversion s 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 con versions for co mparison operations[...]

CHAPTER 5 Moving Data In and Out of Databases 207 The second table shows im plicit (I), explicit (E) , and unsu pported ( U) conversions when the WHERE clause in a SELEC T statement is based on an arithmetic operation (+, –, etc.). Figure 5-3: IQ conver sions for arithm etic opera tions Note In arithmetic operation s, bit da ta is im plicitly con[...]

Converting data on insertion 208 Column width issues Adaptive Server IQ assumes the width of the input data is the same as the destination column width and reads the input f ile accordingly . If they are not the same width, Adaptive Server IQ may read too few or too many bytes of the input file for that column. The result is that the read fo r that[...]

CHAPTER 5 Moving Data In and Out of Databases 209 T runca tion of data fo r V ARCHAR and CHAR column s If the width of the input column is greater than the wid th of the destination column, Adaptive Server IQ truncates the data upon insertio n. If the width of the input d a ta is less than the widt h of the destin atio n column, for CHA R or V ARCH[...]

Converting data on insertion 210 Substitut ion of NULL or blank characters Adaptive Server IQ suppo rts zero-length CHAR and V ARCHA R data. If the length of a CHAR or V AR CHAR cell is zero and the cell is not NULL, you get a zero-length cell. For all other data types , if the leng th of the cell is zero, Adaptive Serv er IQ inserts a NULL. This t[...]

CHAPTER 5 Moving Data In and Out of Databases 21 1 On input, the case th e format code is igno red. On output, the case of the fo rmat code has the following ef fect: • Mixed case ( for example, “Dd”) means do not pad with zeroes. • Same case (for exam ple, “DD” or “dd” means d o pad with zeroes. For exampl e, a tim e as 17:23 :03.7[...]

Converting data on insertion 212 • Use any char acter other than Y , M, J, or D to indicate the separato r character you want Adaptive Server IQ to skip over . Y ou can even use blanks. •I f a DA TE format includes only a y ear and a day n umber within the year , Adaptive Server IQ treats the date as a Julian date. For example, 1998-33 is the 3[...]

CHAPTER 5 Moving Data In and Out of Databases 213 l_quantity ASCII(4), l_shipdate DATETIME(’MM/DD/YY hh:mm:ss’), FILLER(1)) FROM ’/d1/MILL1/tt.t’ BLOCK FACTOR 1000 PREVIEW ON In this UNIX example, the FILLER(1) clause prev ents Adaptive Ser ver IQ from inserting a NULL in th e next column (VW AP) after the DA TETIME column: LOAD TABLE snapq[...]

Converting data on insertion 214 The leng th of t he format st ring is the width o f the in put col umn. T a ble 5-7 describes the date format tin g options. The follo wing table describes the tim e formatting opt ions. T able 5-9: Formatting times The fol lowing table sho w s exampl es of how time in put data may loo k and how to specify the for m[...]

CHAPTER 5 Moving Data In and Out of Databases 215 • The inp ut da ta can inc lud e up to ni n e posit ions for s econd s, incl udin g a floating decimal po int, to allow fo r fractional secon ds. On inpu t and query , the decimal po int floats, so you can s pecify up to six decim al positio ns . However , Adapti ve Server IQ always stores only si[...]

Other factors affecting the displ ay of data 216 • ZEROS indicates that binary zeros convert to NULLS. • literal indicates that all occurrences of the specified literal convert to NULLS. The specified literal must match ex actly , including leading and/or trailing b lanks, with th e value in the inpu t file, for Adaptive Server IQ to recognize [...]

CHAPTER 5 Moving Data In and Out of Databases 217 • When an arithmetic op eration results in a higher scale than the predetermined scale, Adaptive Server IQ trunca tes the results to fit the scale after it has been determined us ing the rules defined in the Adaptive Server IQ Refe rence Manual . If your result s require roundi ng of the values in[...]

Matching Adaptiv e Server Enterprise data types 218 Adaptive Server Enterpris e dat a type equivalents The table belo w indicates th e Adaptiv e Serv er Enterpr ise exact numeric types and the Adaptive Server IQ equiva lents. T able 5-1 1: Integer data ty pes The following table indicates the Adaptive Server Enterprise approximate data types and th[...]

CHAPTER 5 Moving Data In and Out of Databases 219 The following table ind icates the Adaptive Server Enterprise char acter data types and the Adaptive Server IQ eq uivalents. T able 5- 13: Char acter da ta types The following table indicates the Adaptive Serv er Enterprise money data ty pes and the Adaptive Server IQ equ ivalents. T able 5- 14: Mon[...]

Matching Adaptiv e Server Enterprise data types 220 T able 5-15: DA TE/TIME da ta types Since the follo wing Adaptive Server Enterp rise data types are no t supported, you must om it col umns with th ese data types: • varbinary • image • nchar This also applies to an y custom Adaptiv e Server Ent erprise data type. Handling conversion error s[...]

CHAPTER 5 Moving Data In and Out of Databases 221 For information on sett ing DBISQL datab a se o ptions, see “SET OP TION statement” in the Adaptive Server IQ Reference Manual . T uning bulk loading of dat a Loading lar ge volumes o f data into a database can tak e a long time and use a lot of disk space. There ar e a few things you can do to [...]

Tuning bulk loading of data 222 Adding dbsp aces If you run out of space while loading data, Adaptive Server IQ prompts you to create another db space, and then con tinues the operation after y ou add the dbspace. T o avo id this delay , make sure that you have eno ugh room for all of the data you ar e loading before you start the load operation. U[...]

CHAPTER 5 Moving Data In and Out of Databases 223 • Increase the size of the database cache. Providing enough memory for the load is a key p erformance factor . Use the SET OPTION command to adjust MAIN_CAC HE_MEM ORY_MB and TEM P_CACH E_MEMORY_MB . For these options to take ef fect, you mu st ensure that no users are using the database wh ere yo[...]

Changing data using UPDATE 224 The Append_ Load option app lies to LOAD , I NSERT ...SELE CT , and INSERT ...V A LUES statements. For mor e information on versi oning see Chapter 8, “T ransactions and V ersioning”. Changing d at a using UPDA TE Y ou can use the UPDA T E statement, followed by the nam e of the table o r view , to change sing le [...]

CHAPTER 5 Moving Data In and Out of Databases 225 The search condition need not refe r to the column being updated. The compan y ID for Newton Entertainme nts is 109. As the ID value is the pri mary ke y for the table, you could be sure of updating t he correct row u sing the f ollowing statement: UPDATE customer SET company_name = ’Einstein, Inc[...]

Importing data by replicati on 226 Spac e for de letions When you use the DELETE statement, you may need to add space to your databa se, due to the way A dapt ive Serv er IQ sto res ver sions of data pa ges. For details, see “O verlapping versions and deletions”. When you use DROP T A BLE or TRU NCA TE T AB LE , you do not need to add space, as[...]

CHAPTER 5 Moving Data In and Out of Databases 227 For inf ormat ion on us ing Di stribu tion D irect or and on setting up an ap propr iate Replication Server environmen t, see the Distribution Dir ect or User’ s Guide .[...]

Importing data by replicati on 228[...]

229 CHAPTER 6 Using Procedures and Batches About thi s chapter This chapter explains how you create procedures and batch es for use with Adaptive Server IQ. Procedures store procedural SQL statements in the database fo r use by all applications. They enhance the security , efficiency , and standardization of databases. User- defin ed fun ctions are[...]

Benefits of procedures 230 Benefit s of procedures Procedures are defined in the d atabase, separate fr om any one database applicati on. This sep aration provides a nu mber of advan tages. S tandardi zation Procedur es allow standardization of any actions that are performed by more than one appl ication progr am. The action is co ded once and stor[...]

CHAPTER 6 Using Procedures and Batches 231 • Contr ol who ha s per m issio n to use pr ocedu res This section discusses each of these aspects of using procedures, and also describes some of the dif ferent uses of pr ocedures. Creating procedures Procedures are created using the CREA TE PROCEDURE statement. Y ou must have RESOURCE authority in ord[...]

Introduction to procedures 232 The body of a pro cedure i s a compound statement . The compou nd statement starts with a BEGIN statement and concludes with an END statement. In the case of new_dept , the compound statement is a single INSER T bracketed by BEGIN and END statements. For more information, see “ Using com pound statements” on page [...]

CHAPTER 6 Using Procedures and Batches 233 Permissions to exe cute procedures A procedure is owned by the user who cre ated it, and that user can execute it without perm issio n. Permission to execute it can be grant ed to other us ers using the GRANT EXECUTE command. For example, the owner of the procedure new_dep t could allow another_us er to ex[...]

Introduction to procedures 234 3 Create a variable to hold the procedure o utput. In this case, the ou tput variable is numeric, with three decim al places, so create a variable as follows: CREATE VARIABLE Average NUMERIC(20, 3) 4 Call the procedure, using the created variable to hold the result: CALL AverageSalary(Average) The DBISQL statis tics w[...]

CHAPTER 6 Using Procedures and Batches 235 T o execute a CALL of a procedure that r eturns a result set, DBISQL op ens a cursor . The cursor is left open after the CALL in case a second r esult set is returned. The DBISQL statis tics wind ow displays the plan of the SELECT qu ery in the procedure and then displays the line: Procedure is executing. [...]

Introduction to user-defined functions 236 If you are us ing a tool other t han DBISQL or Sybase Cen tral, you may need to change the com mand delimiter away from the semicolon before e ntering the CREA TE F UNCTION statement. CREATE FUNCTION fullname (firstname CHAR(30), lastname CHAR(30)) RETURNS CHAR(61) BEGIN DECLARE name CHAR(61); SET name = f[...]

CHAPTER 6 Using Procedures and Batches 237 SELECT fullname (’Jane’, ’Smith’); The fullname function can be u sed by any user who has been granted EXECUTE permission fo r the functio n. Dropping user-defined functions Once a user-defined function is created, it remains in the databas e until it is explicitly remo v ed. Only the ow ner of th [...]

Introduction to batches 238 Introduction to batc hes A simple batch consi sts of a set of SQL statements , separated by semicolo ns . For example, the following set of statements form a batch that adds a new sales represent ative to t he Eastern Sal es department , and adds two sales o rders for that sales rep. INSERT INTO employee (emp_id, emp_fna[...]

CHAPTER 6 Using Procedures and Batches 239 DBISQL and ba tches A list of semicolon-separated statements, such as the above, is parsed by DBISQL before it is sent to the server . In this case, DBISQL sends each statement individually to the server , not as a batch. Unless you have such parsing code in y our app lication, the statements wo uld be sen[...]

Control statements 240 For complete descriptions of each, see the entries in “SQL S tatements” in Adaptive S erver IQ Reference Manual . Using compound statement s A compound stateme nt starts with the keyword BEGIN and ends with the keywor d EN D . The body o f a procedure is a compound statement . Com pound statements can also be used in batc[...]

CHAPTER 6 Using Procedures and Batches 241 A command delimiter is required after every statement in a statement list except for the last, where it is op tional. Declarations in com pound st atements Local declarations in a comp ound statement immediately follow the BEGI N keyword. These local d eclarations exist only within the compound statement. [...]

Control statements 242 FROM customer WHERE nationality(id) = ’CDN’ Declarations of cursors and exceptions are discussed in later sections. Atomic compound statement s An atomic statement is a statement that is execu ted completely or not at all. For example, a LOAD statement that inserts thousands of rows might encounter an error af ter many ro[...]

CHAPTER 6 Using Procedures and Batches 243 The structure of proce dures The body of a pr ocedure consists of a compoun d statement as discussed in “Using compound statemen ts” on page 240. A compound statemen t cons ists of a BEGIN and an END, en closing a set of SQL statements. The statements must be separated b y semicol ons. The SQL statemen[...]

The structure of procedures 244 Declaring parameters for procedures Procedure par ameters, or arg uments, are specified as a list in the CREA TE PROCEDURE statement. Parameter nam es must conform t o the rules for other database identifiers su ch as column names. They must be a valid data type s (see “SQL Data T ypes” in Ada ptive Server IQ Ref[...]

CHAPTER 6 Using Procedures and Batches 245 Passing parameters to procedures Y ou can take advantage of default values of s tored procedur e parameters with either of two forms of the CALL statement. If the optional par ameters are at the end of th e argum ent list in the CREA TE PROCEDURE statem ent, they may be omitted fro m the CA LL statement. A[...]

Returning results from procedures 246 Notes • Default para meters c an be used in calling func tions. However, parameter s cannot be passed to fun cti o ns by name. • Parameter s are passed by value, no t by referen ce. Even if the func tion changes the value of th e paramet er, this change is not returned to the calling envi ron ment. • Outp[...]

CHAPTER 6 Using Procedures and Batches 247 CREATE VARIABLE returnval INTEGER ; returnval = CALL myproc() ; Returning results as procedure parameters Procedures can return resu lts to the calling env iron m ent in the parameters to the procedure. W ith in a p roced ure, param eters and variables can be assigned valu es in one of the followin g ways:[...]

Returning results from procedures 248 When a SELEC T statement is executed, the server retrieves the results of the SELECT statement and places the results in the variables. If th e query results contain more than on e row , the server returns an error . For queries returning more than one ro w , cursors must be used. For informati on about returni[...]

CHAPTER 6 Using Procedures and Batches 249 Returning result sets from procedures If a procedure retur ns more than one row of results to the calling envir onment, it does so using resu lt sets . The following pro cedure returns a list of customer s who have placed orders, together with the total value of the ord ers placed. The p rocedure does not [...]

Returning results from procedures 250 • When tes ting this pro cedure, DBISQL open s a cursor to handle the results . The cursor is left open fo llowing the SELECT statement, in case the procedure returns more than one result set. Y ou should ty pe RES UME to complete the pro cedure and close the cur sor . Returning multiple result s et s from pr[...]

CHAPTER 6 Using Procedures and Batches 251 If you are not using this feature of variable result sets, it is recommended that you employ a RESUL T clause, for performance reasons and to allo w front-end tools to discern the colum ns and data types the pr ocedure will pr oduce without executing it. For example, the fo llowing procedure return s two c[...]

Using cursors in p rocedures 252 Cursor management overv iew Managing a cursor is similar to managing a file in a prog ramming la nguage. The following steps are used to man age cursors: 1 Declare a cursor f or a particular SELECT statement or procedure us ing the DECLARE statement. 2 Open the cursor using the OPEN statement. 3 Use the FETCH statem[...]

CHAPTER 6 Using Procedures and Batches 253 Using cursors on SE LECT st atements in procedures The following procedur e uses a cursor on a SELECT statement. It illustrates several features of the stor ed procedure langu age. It is based on the same query used in the ListCus tomerV alue procedure descri bed in “Returning result sets from procedure [...]

Using cursors in p rocedures 254 -- 7. Close the cursor CLOSE ThisCompany; END Notes The T op Custome rV alue procedure has the fo llowing notable features: • The "erro r not found" exception is declared. This exceptio n is used later in the procedu re to signal when a lo op over the results of a query has completed. For mo re informati[...]

CHAPTER 6 Using Procedures and Batches 255 AS ThisValue FROM customer INNER JOIN sales_order INNER JOIN sales_order_items INNER JOIN product GROUP BY ThisName DO IF ThisValue > TopValue THEN SET TopCompany = ThisName; SET TopValue = ThisValue; END IF; END FOR CompanyLoop; END Errors and warnings in procedures After an application program execute[...]

Errors and warnings in procedures 256 Default error handling in pr ocedures This section describes how Adaptive Server IQ handles errors that occur during a procedure execution, if you have n o error handling built in to the procedure. If you wan t to have di f ferent behavi or from that described in thi s secti on, y ou can use exc eption h andler[...]

CHAPTER 6 Using Procedures and Batches 257 Notes • The DECLARE statement in InnerProc declares a symbolic name for one of the predefined SQLST A TE values associated wit h error conditions already known to the server . The DECLARE statement does not take any other acti on. • The MESSAGE statement sends a message to the server window and the dbc[...]

Errors and warnings in procedures 258 Error handling with ON EX CEPTION RESUME If the ON EXCEP TION RESUME clause is included in the CREA TE PROCEDURE statement, the pr ocedu re checks the following statement wh en an error occur s. If the statement handles the error , then the proced ure does not return contr ol to the calling env ironment when an[...]

CHAPTER 6 Using Procedures and Batches 259 CREATE PROCEDURE OuterProc() ON EXCEPTION RESUME BEGIN DECLARE res CHAR(5); MESSAGE ’Hello from OuterProc.’; CALL InnerProc(); SELECT @res=SQLSTATE; IF @res=’52003’ THEN MESSAGE ’SQLSTATE set to ’, res, ’ in OuterProc.’; END IF END; CREATE PROCEDURE InnerProc() ON EXCEPTION RESUME BEGIN DEC[...]

Errors and warnings in procedures 260 Default handling of warning s in procedures W arnings are handled differently from errors. While the d e fault acti on for errors is to set a value for the SQLST A TE and SQLCODE variables, and return control to the calling environment , the de fault action for warnings is to se t the SQLST A TE and SQLCODE val[...]

CHAPTER 6 Using Procedures and Batches 261 The procedures b oth continued executing af ter the warning was generated, with SQLST A TE set by the warning (02000 ). Using exception han dlers in procedur es It is often desirable to intercept certain types o f errors and handle them within a procedure, rather than pass the error back to the calling en [...]

Errors and warnings in procedures 262 MESSAGE ’Column not found handling. ’; WHEN OTHERS THEN RESIGNAL ; END The EXCEP TION statement declares the exception handler itself. The lines following the EXCEP TION statement are not executed unless an error occurs. Each WHEN clause specifies an exception name (declared with a DEC LARE statement) and t[...]

CHAPTER 6 Using Procedures and Batches 263 • If the error handling code fo r the column not found exception is sim ply a RESIGNAL statement, control is passed back to the OuterP roc procedure with SQLST A TE still set at the valu e 52003 . This is just as if there were no error hand l ing co de i n InnerProc . As there is no error handlin g code [...]

Using the EXECUTE IMMEDIATE statement i n procedures 264 END MESSAGE ’Outer comp ound statement’; END The following statement executes the InnerProc procedure: CALL InnerProc(); The message window of the server then di splays the follo wing: Hello from InnerProc Column not found handling Outer compound statement When the SIGNAL statement that c[...]

CHAPTER 6 Using Procedures and Batches 265 In A TOMIC compound statements, y ou canno t use an EXEC UTE IMMEDIA TE statement that causes a COMMIT , as COMMIT s are no t allowed in that context. T ran sactions an d savepoints in procedures SQL statements in a procedure are part of the curr ent transaction (see Chapter 8, “Transactions and V ersion[...]

Some tips for writing procedures 266 Remember to delimit statement s within your procedur e Y ou should terminate each statement within the procedure with a semicolon. Although you can leave off semicolons for the last statement in a s tatement list, it is good practice to use semicolons after each statement. The CREA TE PROCEDURE statement itself [...]

CHAPTER 6 Using Procedures and Batches 267 V erifying procedure input arguments Y ou can verify that input ar guments to a p rocedure are passed cor rectly in several ways. Y ou can display the value of the p arameter on the message windo w of the server using the MESSAGE statement. For examp le, the following p rocedure simply di splays the va lu [...]

Calling external libraries from procedures 268 Using SELECT statement s in batches Y ou can include one or more SELECT statements in a batch. Multiple SELECT statements are allowed by Interactive SQL only if they return the same number of columns and each column has the same data type. The fo llowin g is a va lid bat ch: IF EXISTS(SELECT * FROM sys[...]

CHAPTER 6 Using Procedures and Batches 269 This section describes how to use the exter nal library calls in procedu res. W arning! External libr aries can corrup t your database. External libraries called from procedures sh are the memory of the ser ver . If you call a DLL from a pro cedure and the DLL co ntains memory-hand ling errors, you can cra[...]

Calling external libraries from procedures 270 In these statements, functio n_name is the name of a function in the d ynamic link library , and libr ary .dl l is the name of the library . The arguments in the procedure ar gument list must correspond in type and order to the ar guments for the library function ; they are passed to the external DLL f[...]

CHAPTER 6 Using Procedures and Batches 271 How parameters are p assed to the external fun ction SQL data types are map ped to th eir C equivalents as follows: These are the on ly SQL data types y ou can use: using others prod uces an err or . Procedure parameters that ar e INOUT or OUT param eters are passed to the external function b y reference. [...]

Calling external libraries from procedures 272 Special considerations w hen p assing character ty pes For the character d ata type (CHAR), Adaptive Server IQ allocates a 255-byte buf fer (including one for th e null terminator) for each parameter . If the parameter is an INOUT parameter , the exis ting value is copied into the buffer and null termi[...]

273 CHAPTER 7 Ensuring Dat a Integrity About thi s chapter This chapter describ es facilities for ens uring that the dat a in your datab ase is valid and reliable. Th ese facilities include con straints on tables and columns , and choos ing appr opriat e data t ypes. The SQL statements in this chapter u se the CREA TE T ABLE statement and AL TER T [...]

Data integrity overvi ew 274 Integrity constraints belong in the database T o help ensure that the data in a datab ase are valid, you need to formu late checks that define val id and inv a lid data and design rules to which data m us t adhere. The r ules to which data must confo rm are often calle d business ru les. The collective name for ch ecks [...]

CHAPTER 7 Ensuring Data Integrity 275 See “Creati ng tables” for more information on ho w constraints af fect optimi zation. For more on join inde xes and for eign k eys, see “Using joi n indexes”. Constra ints a nd Loa d Operatio ns Adaptive Server IQ checks during load oper ations that certain constraints are obeyed: • Adaptive Server I[...]

Data integrity overvi ew 276 These and other table and column constraints are discussed in “Using table and column constraints”. Column con straint s can be in herited f rom user -defined data types. Entity a nd refer ential integ rity The in formation in rel a tion al database tabl es is tied toget her by the relations betwee n tables. Th ese [...]

CHAPTER 7 Ensuring Data Integrity 277 Using table and column con straint s The CREA TE T ABLE statement and AL TER T A BLE statement can specify many dif ferent attributes for a table. Along with the basic table structure (number , name and dat a type o f colum ns, name an d locati on of the t able) , you can specify other features that allow contr[...]

Using table and column cons traints 278 Using IQ UNIQUE constrai nt on columns The IQ UNIQU E constraint specifies an estimate of the nu mber of distinct values in a column. Y ou can apply the IQ UNIQUE constraint to any column in a table. This constraint h elps optimize load ing of indexes. For example, in the state column of the employee table, y[...]

CHAPTER 7 Ensuring Data Integrity 279 By default, string comparisons are case insens itive unless the databas e is explicitly creat ed as a case-sen sitive database, us in g the CASE RESP ECT optio n. Exam ple 3 Y ou can specify that a d ate or nu mber falls in a particular rang e. Fo r ex ample, you ma y wan t t o req ui re th at the st art_date c[...]

Using table and column cons traints 280 Working with column constraints in Sybase Central All addi ng, alter ing, and deletin g of colu mn const raints i n Sybase C entral is carried out in the Constraints tab of the column prop erties sheet. ❖ T o disp lay the property sheet for a c olumn: 1 Connect t o the databas e. 2 Click the T ables folder [...]

CHAPTER 7 Ensuring Data Integrity 281 • Y ou can replace a CHECK cond ition on a column in the same way as you would add a CHECK cond ition. The following statement adds or replaces an unenforced CHECK con dition on the phone column of the customer table: ALTER TABLE customer MODIFY phone CHECK ( phone LIKE ’___-___-____’ ) UNENFORCED There a[...]

Declaring entity and referential integrity 282 Enforcing entity integrity When a new row in a table is created, or when a ro w is updated, the database server ensures that the primary key for the table is still valid: that each row in the table is uniqu ely identified by th e primary key . Note Adaptive Server IQ enforces single-column primary keys[...]

CHAPTER 7 Ensuring Data Integrity 283 It is up to the application programm er to decide how to present this information to the user and enab le the user to take appro priate action. The appr opriate action in this cas e is usually ju st to provide a u nique value for th e primary key . Primary keys enfor ce entity integrity Once the primary key for[...]

Declaring entity and referential integrity 284 Exampl e 1 The sample d atabase contains an employee table and a d epartment table. The primary key for the emplo yee table is the employee I D, and the primar y key for the depa rtment table is the department ID. One of the items of in formation about each emp loyee is the department ID o f the depa r[...]

CHAPTER 7 Ensuring Data Integrity 285 Integrity rules in th e system t ables All the information abou t integrity checks and rules in a database is h eld in the following s ystem tables and views: For a description of the contents of each system table, see “System T ables” in the Adaptive Server IQ Reference Manual . Y o u c an us e Syb ase C e[...]

Integrity rules in the system tables 286[...]

287 CHAPTER 8 T ransactions and V ersioning About thi s chapter This chapter de scribes Adaptive Server IQ’ s approach to tr ansaction processing, called snapsh o t versioning, and its im plications for perfor mance and ot her aspects of database administ ratio n. Overview o f transactions and versioning Adaptive Server IQ uses t rans act ion pr [...]

Overview of transactions and versi oning 288 T ransactions are atomic . In other words, Adaptive Serv er IQ executes all the statements within a transaction as a unit. At the end of each transa ction, changes can be committed to make them p ermanent. If for any reason all the commands in the tr ansaction do not process proper ly , then some or all [...]

CHAPTER 8 Transactions and Versioni ng 289 •A ROLLBAC K statement undoes all the changes made by the transaction. • A disconn ection from a datab ase causes an im plicit rollback (th e default) or commit, dep ending on whether the DBISQL option COMMIT_ON_EXIT is set. • A statement with a side ef fect of an automatic co mmit is executed. Datab[...]

Overview of transactions and versi oning 290 Subdividing t ransactions Y ou can identify impo rtant states within a tran sactio n and return to them selectively or cause other action s to occur by using savepoints. Savepoints are discus sed fu rth er in “Save points within transac tions”. Introduction to concurrency Adaptive Server IQ can execu[...]

CHAPTER 8 Transactions and Versioni ng 291 Concurrency and I Q Multiplex IQ Multiplex extends Adap tive Server IQ to allow concurrent p rocessing of read transactions on multiple Adap tive Server IQ servers. IQ Multiple x extends snapshot versio ning to maintain the consis tency of the databas e across multiple servers. For more information about u[...]

Overview of transactions and versi oning 292 Unlike a camera, though, IQ does not need to make a copy of the entire o bject each time the image changes. In stead, it copies only the parts of the image— the datab ase pages—t hat have changed . Database pa ges that have not chang ed are shared amon g all active versions in the database. IQ tak es[...]

CHAPTER 8 Transactions and Versioni ng 293 Figure 8-1: O nly one w riter at a tim e Meanwhile, other user s can read from the c ustomer table at any time. In this way queries can proceed while the d atabase administrator inserts and deletes table data. In Figure 8-2, User 3 and User 4 are able to query the c ustomer table while User 1’ s write tr[...]

Overview of transactions and versi oning 294 Figure 8-2: One writer , mul tiple read ers Multiple writers a nd readers in a dat abase W ithin an IQ database that is no t multiplex, m ultiple read-onl y and read /write users can operate con currently , as long as the writers a re inserting data into (or deleting it from) different tables. So, for ex[...]

CHAPTER 8 Transactions and Versioni ng 295 Figure 8-3: Conc urrent inser tions to dif ferent tables Data definition operations on a single tab le lock out all other readers and writers from th at table. See “Locks for DDL operations” for deta ils. T ransactions use committed dat a Committed da ta res ult s wh en a write transaction com mi ts. E[...]

Overview of transactions and versi oning 296 In other words, every tran saction begins with a snapshot of the data in a reliable state. The snapshot of the data that you see when yo u issue a query does not change, ev en if anot her user is updating th e table you ar e reading. For example, in Figure 8-4, when User 1’ s write transacti on begin s[...]

CHAPTER 8 Transactions and Versioni ng 297 • If a user's read transaction commits before a concurrent write tran saction does, and t hat u ser be gins a new read t r ansa ctio n, th e v e rsio n remai ns the same . • If a read transaction commits after a concurrent write transaction does, any new transaction, whether read-on ly or read/wri[...]

Overview of transactions and versi oning 298 How Adaptive Server IQ keep s track of versions Adaptive Server IQ assigns a version iden tifier to each database object that exists in the metadata, and that has a life span bey ond a single command. IQ uses these version identifiers to ensure that writes to any database o bject are always based on the [...]

CHAPTER 8 Transactions and Versioni ng 299 V ersion ing prevent s inconsiste ncies W i thout versio ning, con current read and wri te operat ions coul d cause inconsisten c ies in the database. The table-level versi onin g provided by Adaptive Server IQ prevents in consistencies both by seria lizing transactions, and by maki ng the tab le the ver s[...]

How locking works 300 • Read transa ctions do not block wr ite transactions. • W rite trans actions do no t block read trans actions. • A single update user and multiple read-onl y users can concurrently access a table. • Only a single user can update the data in a given table at one time. The first transaction to open a table in write mode[...]

CHAPTER 8 Transactions and Versioni ng 301 Cannot perform DDL command now as a DDL comm and is already in progress. If a CREA T E DBSP A CE or DROP DBSP A CE command is i n progress, a nd a user explicitly iss ues a CHECKPOINT command, the check point fails with the message: Run time SQL Error If a C HECKPOINT command is in progress, a user who iss[...]

Isolation leve ls 302 GRANT , REVOKE, and S ET OPTIO N ar e not restric ted While the command s GRANT , REV OKE , and SET OPTION are als o considered DDL operations, they cause no concurr ency conflicts, and so are not restricted. GRANT and REVOKE always cause an auto matic commit; SET OPTION causes an automatic commit except when it is specified a[...]

CHAPTER 8 Transactions and Versioni ng 303 The level of is olation that Adapti ve Server IQ pro vides prevents several types of inconsistencies. The ones most com monly encountered are listed her e: • Dirty Reads T ransacti on A modifies an object, but does not commit or roll back the chang e. T ransaction B reads the modified ob ject. Then T ran[...]

Checkpoints, savepoi nts, and transaction rollback 304 Adaptive Server IQ relies on three transaction-related commands that help you recover a stable set of d ata in the event of system or media failure. These commands set checkpo ints, set and release savepoints, and roll back transactions. Checkpoints A checkpoint marks a significan t point in a [...]

CHAPTER 8 Transactions and Versioni ng 305 • At the start and end of the backup p rocess. • When t he databas e server is shut down. The CHECKP OINT_TIME is the maximum time that can pass between checkpoints. It is set by default at 60 minutes. Y ou can adjust the ch eckpoint interval with the SE T OPTION command; see the Adaptive Server IQ Ref[...]

Checkpoints, savepoi nts, and transaction rollback 306 Rolling back to a savepoint Y ou can un do all changes af ter a sa vepoint by issuin g a ROL LBACK TO SA VEPOI NT . This command ro lls back to the sav epoint you s pecify , or to the most recent SA VEP OINT if you do not s p ecify a named s avepoint . Rollin g back to savep oint n und oes all [...]

CHAPTER 8 Transactions and Versioni ng 307 Rolling back transac tions When you r oll back a transacti on, you un do all of the oper ations in that transaction. W e say that you are rolling back the database, since you are returning the database to an earlier state. What causes a rollback Rollbacks can occur either du e to an explicit user requ est,[...]

Checkpoints, savepoi nts, and transaction rollback 308 During Adaptive Server IQ database recovery , any uncommitted transactions are rolled back, and any disk space used for old versions is retur ned to the pool of available space. At this point , the database contains only th e most recently committed version of each perman ent table. During reco[...]

CHAPTER 8 Transactions and Versioni ng 309 • Additional informatio n about checkpoints that occu rred during a transaction. When you n eed to recov er your dat abase, instead o f repeating all of t he lengthy transactions that have occurred, Adap tive Server IQ restores qu ickly fro m the information in the transact ion log and th e check point i[...]

Performance implic ations 310 • Disk space can sometimes beco me an issue. S toring overla pping version s has the po tential to use a lot of d isk space, dependin g on the numbe r and size of vers ions in us e simultaneou sly . Metadata and dat abase page versions are retained until they are dropped, either at a RELEASE SA VEPOI NT or when the l[...]

CHAPTER 8 Transactions and Versioni ng 31 1 Out of disk space After you add space, the deletion resumes. When the delete transaction commits, the space becomes available f or other deletion s or insertions. I f you do not need n ormally that much space in your database, you can dro p the dbspace t o regain the e xtra disk spac e for ot her purpos e[...]

Cursors in transactions 312 The rows in a cursor , like those in a table, have no order associated with them. The FE TCH command steps through the rows, but the order may app ear random and can even be inco nsistent. For this reason, you will want to impo se an order b y appendi ng an ORDER BY phrase to your SELECT statement. Cursors and versioning[...]

CHAPTER 8 Transactions and Versioni ng 313 Hold cursors Specifying the HOLD option when you open a cursor keeps the cursor o pen past the end of the transactio n, if the transaction ends in a C OMMIT . A hold cursor does not remain op en across a ROLLBACK in which a cu rsor is opened. Althoug h the HOLD option is not commonly us ed in a DSS enviro [...]

Cursors in transactions 314[...]

315 CHAPTER 9 International Languages and Character Set s About thi s chapter This ch apter descr ibes how to configur e your Adapt ive Server IQ installation to handl e international language iss ues. Introduction to international lang uages and cha racter set s This section provides an in troduction to the issues you may f ace when working in an [...]

Introduction to international languages an d character sets 316 • Con ditions us ing th e LIKE keywor d. IQ indexes th at hold character data are created based on the database collation. The d atabase also us es collations to identify valid or u niq u e identi fiers (colu mn names and so on ). • Charact er set tr anslation Y ou can set up Adap [...]

CHAPTER 9 International Languages and Character Sets 317 Underst anding character sets in software This sec tion provi des g eneral infor mation ab out sof tware i ssues relate d to international languages and char acter sets. Pieces in the chara cter set puzzle There are several distinct aspects to character storage and display by computer softwar[...]

Understanding character sets in software 318 • Database servers, which sor t characters (for example, list names alphabetically), use a collation . A collation is a com b ination of a character encoding (a map between chara cters and hexadecimal num bers) and a sort order for the characters. There may be more than one sort order for each characte[...]

CHAPTER 9 International Languages and Character Sets 319 • Operating s ystem The client op erat ing system has text di sp l ayed on its interface, and may also pr ocess text. For a satisfactory working en vironment, all these sources of text must wor k together . Loosely speaking , they must all be wor king in the user’ s language and/or charac[...]

Understanding character sets in software 320 Remember that th e code page used by the client system determines bo th the values that are sent to server for each ch aracter you enter , and the characters that are displayed when particular v alues are sent to the client from the server . The code pag e u sed b y the server system determ ines h ow th [...]

CHAPTER 9 International Languages and Character Sets 321 Multibyte character s et s Some lang uages, such as Jap anese and Chinese , have many more than 256 characters. These characters can not all be r epresented using a single byte, but can be represented in m ultibyte character sets. In addition, some character sets use the much lar ger number o[...]

Understanding character sets in software 322 Sorting characters using collations The database collation sequence in cludes the notion of alphab etic ordering of letters, and extends it to include all char acters in the character set, including digits and space characters. Associ ating more th an one c hara cte r wit h each so rt po sitio n More tha[...]

CHAPTER 9 International Languages and Character Sets 323 The equivalence of u pper and lower case character s is enforced in the co llation. There are so me collations where particular care may be needed when ass uming case insens itivity of identifiers. Exam ple In the Tu rkish 857 TRK collation, th e lower case i does not have the character I as [...]

Understanding locales 324 • Which language to request from the database. For mor e informat ion, s ee “Understanding the locale language” on page 324. • Char acter se t The character set is the code pag e in use. The client and server both h ave character set values, and they may dif fer . If they dif fer , character set translation may be [...]

CHAPTER 9 International Languages and Character Sets 325 Language label values The following table shows the vali d language label values, to gether with the equivalent ISO 639 label s: Understanding the locale character set Both application and server locale definitions have a char acter set. The application uses its character set when r equesting[...]

Understanding locales 326 For more information, see “Setti ng th e SQLL OCAL E env iron ment variable” on page 328 . Open Client applications check the locales.dat file in the Sybase locales directory is used. 3 Character set inform ation fro m the operatin g system is used to d etermine the locale: • On W indows operating systems, use the Ge[...]

CHAPTER 9 International Languages and Character Sets 327 macgrk2 <N/A> Macintos h Greek mactur k <N/A> Macint osh Turkish greek8 <N/A> HP Greek-8 turkis h8 <N/A> HP T urk ish-8 koi8 <N /A> KOI-8 Cyrillic tis620 <N/A> TIS-620 Th ai standard big5 <N/A> T raditional Chinese (cf. CP950) eucksc <N/A> EUC K[...]

Understanding collations 328 Underst anding the locale c ollation label Each database has its own collatio n . The database server det ermines the col lation label as fol lows: 1 It checks the SQLLOCALE env ironment variable, if it ex ists. For more information, see “Setti ng th e SQLL OCAL E env iron ment variable” on page 328 . 2 It uses an i[...]

CHAPTER 9 International Languages and Character Sets 329 • The collation (ASA_Label) • Case sensitivity (Case) • Blank padding (Blan k Padd ing) if i t was speci fied when th e database was created. Y ou can also see the collation of your current database by u sing the dbcollat utility to write th e collation into a file: dbcollat -c " c[...]

Understanding collations 330 819SV E ANSI Code Pa ge 81 9, Swe dish/F innish 819T RK ANSI Code Pag e 819, T urkish 850CYR OE M Code Page 85 0, Cyrillic , W estern 850DA N OE M Code Pag e 850, Da nish 850E LL OEM Code Page 850, G reek 850E SP OEM Code Pa ge 85 0, Sp anish 850IS L OEM Code Page 850, Icel andic 850L A TIN1 OEM Code Pag e 850, Latin 1 [...]

CHAPTER 9 International Languages and Character Sets 331 ANSI or OEM? Adaptive Server IQ collations are based on code pages that are design ated as either ANSI or OEM. In most cas es, use of an ANSI co de page is recommended. If you choo se to us e an ANSI code p age, you must not use the ODBC translation driver in the ODBC data source configuratio[...]

Understanding collations 332 • When sett ing u p dat a sour ces for W indows-based ODBC ap plicat ions , do choose the Adaptive Serv er Anywhere or Ad aptive Server IQ translation driver in the ODBC dat a source configu r ation. The translation driver converts between the OEM cod e page on your machine and the ANSI code page u sed by W indows. If[...]

CHAPTER 9 International Languages and Character Sets 333 • The uppe r case and lower case Icelandi c Eth (xD0 and xF0) is sorted with D in Adaptive Server IQ and Adap tive Server Anywher e, but after all other letters in Adaptive Server Enterprise. • The upp er case and lower case I celandic Thorn (xD0 and x F0) is sorted with T in Adaptive[...]

Understanding collations 334 Notes on OEM collations The following table sh ows the built-in collations that correspon d to OEM code pages. Th e table and t he correspondi ng collat ions were deri ved from s everal manuals fro m IBM concerni ng National Language Sup port, subject to t he restrictions mentioned abo ve. (This table represents the bes[...]

CHAPTER 9 International Languages and Character Sets 335 New Zealand Engli sh 437 437LA TIN 1 850 850LA TIN1 Nor way N orweg ian 865 865NO R 850 850NO R Peru Sp anish 850 850E SP 437 437ESP Polan d Polish 852 852L A TIN2 850 850LA TIN2 Portug al Port ugue se 850 850 LA TIN 1 86 0 860LA TI N1 Roma nia Roma nian 852 852L A TIN2 850 85 0LA TI N2 Rus s[...]

Understanding character set translation 336 Using multibyte collations This section describes how multibyte character sets are handled. The description applies to the su pported collatio ns and to any multib y te custom collations you may create. Adaptive Server IQ provides collati on s using several mu ltib yte character sets. For a complete listi[...]

CHAPTER 9 International Languages and Character Sets 337 Client application users may see messages from the database as well as data from the database. Some database messages, which are strings from the language library , may include placeholders that are filled by characters from the database. For ex ample, if y ou execute a q uery with a column t[...]

Understanding character set translation 338 Connection strings and cha racter sets Connection strings present a special case for character set translation. The connection strin g is parsed by the client library , in ord er to locate or start a databas e server . Thi s parsing is done wit h no kn owledge of the serv er character set or language. The[...]

CHAPTER 9 International Languages and Character Sets 339 • If the cod e page on you r client machine operating sy stem matches that used in the database, no character set translation is n eeded for data in th e database. For example, in many environments it is appropriate to u se the 1252LA TIN1 collation in your database, wh ich cor responds to [...]

Collation internals 340 Collation fil e s m ay include the follo win g elements: • Comment lines, which are ignored b y the d atabase. • A title line. • A collation sequen ce section. • An Enco dings s ection. • A Properties section . Comment lines In the collation file, spaces are generally ignored. Commen t lines start with either the p[...]

CHAPTER 9 International Languages and Character Sets 341 The collation sequen ce section After the title line, each non-comment line describes one position in the collation. The orderi ng of the lines determin es th e sort ordering used by the database, and determines the r esult of comparison s. Characters on lines appearing higher in the file (cl[...]

Collation internals 342 : ’ ’ : _ : xF2 : xEE : xF0 : - : ’,’ : ; : ’:’ : ! % Sort some letters in alphabetical order : A a A : a a A : B b B : b b B % Sort some E’s from code page 850, % including some accented extended characters: : e e E, x82 x82 x90, x8A x8A xD4 : E e E, x90 x82 x90, xD4 x8A xD4 Other syntax n otes [...]

CHAPTER 9 International Languages and Character Sets 343 The Encoding s sect ion lists wh ich ch aracters are lead-bytes, for multi-byte character sets, and what are valid f ollow-bytes. For example, the Shift-JIS Encoding s section is as follows: Encodings: [x00-x80,xa0-xdf,xf0-xff] [x81-x9f,xe0-xef][x40-x7e,x80-xfc] The first line f[...]

International language and charac ter set tasks 344 Internatio nal language a nd characte r set t asks This section gr ou ps together the tas ks associated with int ernationa l lang uage and character set issues. Finding the default collation If you do not ex plicitly specify a collation when creating a database, a d efault collation is used. Fo r [...]

CHAPTER 9 International Languages and Character Sets 345 For more inform ation, see “Creating a database with a named collation” on page 346. When choos ing the col lation for you r database, • Choose a collation that uses a character set and sort or der appropr iate for the data in the database. It is often the case that there are several al[...]

International language and charac ter set tasks 346 SELECT PROPERTY( ’DefaultCollation’ ) The query returns one of the collat ions listed in “Supplied collations” on page 329. Notes T o obtain client locale information , con nect to a database server run ning on your cur rent machine. T o obtain the ch aracter set for an individual database[...]

CHAPTER 9 International Languages and Character Sets 347 ❖ T o specif y a dat abas e collation whe n creating a d at abase (Sy base Central): • Y ou can use the Create Data base wizard in Sybase Central to create a database. The wizard has a Collation Sequen ce page where you choose a collation from a list. Y ou can also see the name of y our c[...]

International language and charac ter set tasks 348 CREATE DATABASE ’mydb.db’ COLLATION ’819ELL’ IQ SIZE 100 IQ PATH ’myiq.iq’ St arting a database server using charac ter set translation Character set translation takes place if the client and server locales are diff erent, but only if you specifically turn on char acter set conversion [...]

CHAPTER 9 International Languages and Character Sets 349 ❖ T o use the O DBC translation driv er: 1 In the ODBC Administrator , choose Add to create a new Adaptive Server IQ data source or Conf igure to edit an existing Adaptive Server IQ data source. 2 On the ODBC tab o f the ODBC Configuratio n for Adaptive Server IQ window , click Select and c[...]

International language and charac ter set tasks 350 Y ou do thi s using the Co llation uti lity . The output is a collation file. For example, the following statement extracts the 1252LA TIN1 collation into a file named mycol.col : dbcollat -z 1252LATIN1 mycol.col 3 Edit the custo m collation file. Open the collation file (in th is case mycol.col )[...]

CHAPTER 9 International Languages and Character Sets 351 Stop and restar t th e databa se server i n order fo r it to r ecognize the new collations and in sert th em into system tables SY SCOLLA TION and SYSCOLLA TIONMAPPINGS. Creating a database with a custom collation If none of the supplied collations meet y our needs, you can create a datab ase[...]

Performance issues 352 W ith these options, uppercase characters precede all lowercase characters in the collation sequence. For example, ’ XYZ’ sorts before ’ abc’ with these options, jus t as it did in older versions of Adaptive Server IQ. Perform ance issues Performance fo r character data is be tter with a bina ry character set and coll[...]

353 CHAPTER 10 Managing User IDs and Permissions About thi s chapter Each user of a database must be assigned a uniq ue user ID: the name they type when connecting to the databas e. This chapter describes ho w to mana g e us er IDs . An overview of dat abase permission s Proper m anagement of user IDs and per missions i s essenti al in a data wareh[...]

An overview of database permi ssions 354 DBA authority overview When a database is created, a single u sable user ID is created. This first user ID is DBA and the pas sword is initially set to SQL . The DBA user ID is automatically given DBA permission s, also called DBA authorit y , within the database. This level of p e rmis sion enables the DBA [...]

CHAPTER 10 Managing User IDs a nd Permissions 355 RESOURCE authority overview RESOURCE authority is the permission to create database ob jects, such as tables, views, an d stor ed proc edures. Res ource aut hority may be gran ted onl y by the DBA to other users . Ownership permissions overview The creator of a database object becomes the owner o f [...]

Managing indiv idual user IDs and permissions 356 Group permissions overv iew Setting permissions individually f or each user of a database can be a time- consuming and error -prone process. F or most data bases, per mission manageme nt based on g roups, rathe r than on individ ual user IDs, i s a much more ef ficient approach. Y ou can as sign per[...]

CHAPTER 10 Managing User IDs a nd Permissions 357 Creating new users A new user is ad ded to a d atabase by the DBA using the GRANT CONNECT statement. For example: ❖ T o add a new user to a dat abase, w ith user ID M_Ha neef and p assword welcome: 1 From DBISQL, connect to the datab ase as a user with DBA authority . 2 Issue the SQL statement: GR[...]

Managing indiv idual user IDs and permissions 358 GRANT CONNECT TO userid IDENTIFIED BY password The same command can also be used to ad d a new user . For this reason , if you inadvertently en ter the user ID of an existing user when you mean to add a new user , you are actually changing the password o f the existing user . Y ou do not receive a w[...]

CHAPTER 10 Managing User IDs a nd Permissions 359 • Y ou sh ould gi ve users with DBA auth ority two user IDs , one with DB A authority and one withou t, so that they connect as DBA only when necessary . • RESOURCE authority allows the user to cr eate new database objects, such as tables, views, indexes, or pro cedures. Granting permission s on[...]

Managing indiv idual user IDs and permissions 360 1 Connect to the database as a user with DBA authority , or as the own er of sample_table . 2 T ype and execute the SQL state ment: GRANT UPDATE (column_1, column_2) ON sample_table TO M_Haneef T able and view permission s are limited in that they ap ply to all the data in a table or v iew (except f[...]

CHAPTER 10 Managing User IDs a nd Permissions 361 TO M_Haneef WITH GRANT OPTION Granting permission s on procedures There is only one p ermission that may be granted on a pr ocedure, and that is the EXECUTE permission to execu te (or CALL) the p rocedure. Permission to execute stor ed procedures may b e granted by the DBA or by the owner of the pr [...]

Managing indiv idual user IDs and permissions 362 2 Click the Users and Grou ps folder , and locate the user you want to grant permi s si on s to. 3 Right-click the user , and select Copy from the popup menu. 4 Locate the procedure you want to allow the user to execute, in the Stored Procedures folder . 5 Click the pr ocedure, and choose Edit → P[...]

CHAPTER 10 Managing User IDs a nd Permissions 363 sp_helptext sp_name_in_question Manag ing gr oup s Once you un ders tand ho w to m anag e perm iss ions for in d ividu al u s e rs (as described in the prev ious se ctio n) working wi th group s is s traightf orward. A group is identified by a user ID, just like a sin gl e user, but this user ID is [...]

Managing groups 364 3 Give the personnel us er ID the permission to hav e members, with the following SQL statement: GRANT GROUP TO personnel The GROUP permission, w hich gives the user ID the ability to have members, is not inherited by members of a gro up. If this were n ot the case, then ev ery user ID would automatically be a group as a consequ[...]

CHAPTER 10 Managing User IDs a nd Permissions 365 2 Double-click the Users and Gro ups folder for that database, to op en it. Groups are d isplayed i n the l eft panel , and bo th users and group s are displayed in the right panel. 3 In th e right pan el, sel ect the us ers you want to add to a group , and drag them to the grou p. For more infor ma[...]

Managing groups 366 If a table employees is owned by the person nel user ID, and if M_Hanee f is a member of the personnel group, then M_Haneef can refer to the e mployees table simply as e mployees in SQL statements. Users who are not members o f the personnel group need to use t he qualifie d name personne l.employees . Creati ng a gr oup to own [...]

CHAPTER 10 Managing User IDs a nd Permissions 367 Special groups When a database is created, two g roups are also automatically created. These are SYS and PUBLIC. Neither of t hese group s has passwords, so it is not possible to connect to the d atabase as either SYS or as PUBLIC. The two groups s erve impor tant fu nctions i n the data base. The S[...]

Database object names and prefixes 368 SELECT * FROM "DBA".employee The employee table refere nce is said to be qualified. (In this case the owner name is encl osed in double q uotes, as DBA is a SQL keyword .) In oth er circumstances it is sufficient to give the o bject name. This sectio n d escribes when you need to use the owner prefix[...]

CHAPTER 10 Managing User IDs a nd Permissions 369 SELECT * FROM company.customers T o rectify the situation, make the Sales group a member of the company group. GRANT GROUP TO company; GRANT MEMBERSHIP IN GROUP company TO Sales; Now Joe and Sally , being members of the Sales group, are in directly members of the company group, and can referen ce th[...]

Using views and procedures for extra security 370 For information on how to create v iews, see “W orking with views” . Using views for tailored security V iews are computed tables that contain a selection of rows an d columns fro m base tables. V iews are useful for security when it is appropriate to giv e a user access to just one portion of a[...]

CHAPTER 10 Managing User IDs a nd Permissions 371 TO SalesManager Exactly the same command is used to gr ant permission on a view as to grant perm ission o n a table. Exam ple 2 The next example creates a view which allows the S ales Manager to look at a summary of sales or ders. Th is view requ ires in formatio n from more than o ne table for its [...]

How user permissions are asses sed 372 S trict securi ty For strict security , you can disallow all access to the under lying ta bles, and grant permissions to users or groups of users to execute cer tain stored procedures. W ith this approach, the manner in wh ich data in the database can be modi fied is s trictly defined. How user permiss ions ar[...]

CHAPTER 10 Managing User IDs a nd Permissions 373 For example, you may wish to prevent a single co nnection from tak ing too much of the available m emory or CPU resources, so that on e con nection does not sl ow down ot her users of the dat abase. Adaptive Server I Q prov ides a set of database o ptions that th e DBA can u se to control resources.[...]

Users and permissions in the sy stem tables 374 • JA V A_HEAP_SIZE Sets the maximum size ( in bytes) of that part of the memory that is allocated to Java applications on a per connection basis. • MAX_CURSOR_COUNT Limits the num ber of cursors for a connectio n. • MAX_ST A TEMENT_COUNT Limits the number of pr epared statements for a connection[...]

CHAPTER 10 Managing User IDs a nd Permissions 375 The following table summarizes the system views containing information about user I Ds, grou ps, and per missions . In addition to these, there are tables and views co ntain ing information about each object in the database. T able Default Contents SYSUSERPERM DBA only Database -level permissions an[...]

Users and permissions in the sy stem tables 376[...]

377 CHAPTER 1 1 Backup and Dat a Recovery About thi s chapter This ch apter expl ains how to back up your da tabase, and how to recover data when necessary . It tells you why it is impor tant to perform back ups on re gu lar ba sis, a nd gi ve s rec omm enda tio ns fo r sch e duling b ack ups. Backup protects your dat a Adaptive Server IQ pr ovides[...]

Backing up y our database 378 Backing up you r dat abase Y ou use the BACKUP command to back up your IQ database. Backup includes both the Adaptive Server I Q data (the IQ St ore) and the und erlying Adaptive Server Any where databas e (the Cata log S tore) Backup runs concu rrently with read and write operations in the d a tab ase. By contrast , d[...]

CHAPTER 11 Backup and Data Recovery 379 A second automatic ch eckpoint occurs at the end o f backup. Any data that is committed while the back up is in pr og ress is includ ed in any sub seq uent backups. However , if a failure occurs between the first and second checkpo ints, any work that occurred after the first checkpoint can not be restored. A[...]

Backing up y our database 380 Distribu tion of ba ckup dat a BACKUP always makes a full backup of the Catalog St ore on the fir st archive device, and then backs up the data from the IQ Store in parallel across all of the devices you specify . Blocks are not distributed eve nly across archive me dia. Y ou may h ave more on on e device than o thers,[...]

CHAPTER 11 Backup and Data Recovery 381 • 4 mm DDS •8 m m Adaptive Server IQ also allows S t acker drives with multiple tapes. Adaptive Server I Q BACK UP does not support jukeboxes or rob otic loaders . If you need them, use a third party med ia manager . Adaptive Server IQ BACKUP does not suppo rt fix ed-lengt h tape devices on UNIX systems, [...]

Backing up y our database 382 Ret aining old disk backup s BACKUP overwrites exis ting dis k files of th e sam e name. If yo u nee d to retai n a backup, when you create a new backup either use dif ferent file or path names for the arch ive devices, or move th e old backup to another location befo re starting the backup. T wo ways to run BACKUP Y o[...]

CHAPTER 11 Backup and Data Recovery 383 • For stacker devices that hold mu ltiple tape drives, all tapes for a giv en device must be the same size. Sybase recommends that you always start a new tape for every backup. Before starting a backup to disk, Adaptive Server IQ first tests whether there is enough disk file space for the backup . For an op[...]

Backing up y our database 384 ...TO ’ archive_device ’ [ SIZE #_of_KB ][ST ACKER #_of_drives_in_stack ] ... [ WITH COMMENT ’ string ’ ] Note If yo u need to back u p an Adaptive Server Anywhere database, see the Adaptive S erver IQ Reference Manual for addi tional o pt ions. Specifying oper ator presence A TTENDED ON or OFF contr o ls wheth[...]

CHAPTER 11 Backup and Data Recovery 385 Backup fi le names for backup to dis k BACK UP always assi g ns fil e n ames t o d i sk back up fi l es by appending a s uf fix to the ar chive_device name you specify . The suffix consists of “.” follow ed by a number that increases by one for each n ew file. For example, if you specify /iqback/mondayinc[...]

Backing up y our database 386 Specify ing ta pe device s on Wi ndo w s NT W indows NT systems do not specify r ewind o r no rewind devices and o nly suppor t fixed-lengt h I/O op erations to tap e devices. Ad aptive Server IQ requires variable-length devices. It does additional processing to accommodate NT’ s fixed-len gth tape I/O. While W indow[...]

CHAPTER 11 Backup and Data Recovery 387 • For unattended back ups with S IZE and ST ACKER specified, Backup tries to open th e next tape. If there are no volumes available, o r if you did not specify ST ACKER , yo u get an error . Any additional tapes do not contain th e header information needed for a restore, so you mus t be carefu l to mo unt [...]

Backing up y our database 388 Other backup options Specify ing the b lock facto r BLOCK FACT OR specifies the number of IQ blocks to write to th e archive device at one time. It must be g reater than 0, or BACKUP returns an error message. BLOCK FACT OR default s to 25 on UNIX platforms . On W indows NT , th e defau lt BLOCK F ACTOR is based on t he[...]

CHAPTER 11 Backup and Data Recovery 389 TO ’/dev/rmt/0n’ TO ’/dev/rmt/1n’ WITH COMMENT ’Jan 18 full backup of asiquser’ The Catalog S tore is backed up first, to /dev/rmt/0n . The IQ Store is back ed up next, to b oth tap es. Exam ple 2 — Incremen tal backup T o make an increment al backup of the same databas e, this time using only o[...]

Backing up y our database 390 • If back up fails during either the chec kpoint at the start of backup or the checkpoint when back up is complete, it performs norm al checkpoint recovery . • If back up fails between checkpo ints, it rolls back th e backup. • If the system fails at any tim e between the in itial and final checkpo int and you m [...]

CHAPTER 11 Backup and Data Recovery 391 The backup pr ogram pas ses vend or inform ation t o the t hird-par ty progr am automatically . When you request a third-party backu p, it places this information in the backup head er file, and writes the header file on the first tape or disk file actually created f or each ar chive_device you specify . Perf[...]

Performing system-lev el backups 392 Backing up the right files Requ ired f iles Y ou must back up the follow ing files: • SYSTEM dbspace file, typ ically named dbname .d b. • The transaction log fil e , which is requ ired for system recovery , typi cally name d dbname .log • The IQ_SYSTEM_MAIN dbspace f ile, typically named dbname .iq • Th[...]

CHAPTER 11 Backup and Data Recovery 393 Ensuring t hat all files exist Bef ore restoring, review the table of contents of the b ackup to ensure that all files required for IQ are present. The list of files depends on your application. See the discussi on of requi red and optional f iles in “Backing up the right files” on page 392. Check ing own[...]

Validating y our database 394 Interpreting results The pr ocedure prod uces a very l ong list o f stat istics about your datab ase. S tatistics are listed first for the Main IQ S tore, then for T emporary Sto re. For each store, you see th ree types of statistics: • Dynamic statistics. These are cumulative co unts from the time the database serve[...]

CHAPTER 11 Backup and Data Recovery 395 Figure 1 1-1: sp_iqcheckdb results Concurrency issues for sp_iqcheckd b When you r un sp_iqcheckdb , it reads every database page in use. Th is procedure consumes most of the database server’ s time, so that the I/O is as efficient as possible. Any oth er concu rrent activities on th e system will run[...]

Restoring your databases 396 more slowly than us ual. If other users are active when you run sp_iqcheck db , the results you see reflect only what your tran saction sees. If anot her user i s doin g inserts and del etes, t hose blocks appear as unowne d in the Main IQ Store. T o avoid confusion, you sho uld not allow inserts and deletes while sp_iq[...]

CHAPTER 11 Backup and Data Recovery 397 • For a full restore, the Catalog Store (by default the .db file) an d th e transaction lo g (by de fault the .log file) must not exist in the locatio n you are restoring t o. If either of these files ex ists, you must d elete it or move it to a diff erent directory before doing the full restore. When a ful[...]

Restoring your databases 398 Restoring disk backup fil es If you back up to disk an d t hen move t h ose fi les to t ape, y ou m us t m ove them back to disk files with the same names as when you created the backup. Adaptive Server IQ cannot r estore disk files that are mov ed to tape directly from tape. When you restore u sing the Sybase-pro vided[...]

CHAPTER 11 Backup and Data Recovery 399 Database has changed since the last restore Note Adaptive Server IQ does not let you do an incremental restore if the database has change d since the pr eviou s resto re. Ho wever , it does not preven t users from maki ng changes. It is the respo ns ib ility of the DBA or system operator to ensure that no cha[...]

Restoring your databases 400 Just as for backup, each ar chive_device specifies the API (Sybase or third party) and, for th e Sybase API, the physical tape devi ce or disk fi le name from which yo u are restori ng. For th ird-party AP Is, the conten t of th e ar chive_device stri ng depends on your vendor . The archive device must not be a raw disk[...]

CHAPTER 11 Backup and Data Recovery 401 • T o move any other dat abase fil e, you use th e RENA ME optio n. Note The DBTRAN utility is not supported by Adaptive Server I Q because it regenerates only those parts of the transaction log that are specific to Adaptive Server Anywher e. Exam ple 2 — Movi ng the C atalo g S tore This example restores[...]

Restoring your databases 402 Relative path names in th e RENAM E clause work as they do when you create a database or dbspace: th e main IQ S tore dbspace, T emporary Store d bspaces, and Message Log are restored relativ e to the location of db_file (the Catalo g S tore); user-cr eated IQ Sto re dbspaces are restored relative to the directory that [...]

CHAPTER 11 Backup and Data Recovery 403 RENAME IQ_SYSTEM_MSG TO ’asiquser.iqmsg’ RENAME IQ_USER TO ’/dev/rdsk/c1t5d2s1’ Note Y ou could also issue these commands with only the last RENAME clause, since only one db space is being restored to a new lo cation. Listing all of the files or raw partitions, as sho wn here, ensures that you k now e[...]

Restoring your databases 404 T o determine the co rrect order , you need the info rmation about back up files that is stored in the backup log. See “G etting inform ation about backups and resto r es ” for the con ten t and location of this file. Resto re bac kups a s fol lows: • If yo ur datab ase is cor rupt, or i f you ar e moving any fi l[...]

CHAPTER 11 Backup and Data Recovery 405 Set B Ta p e s B 1 and B 2 . These must be restored as a set, after Set A, and either before or after Set C. They can be in either the second o r third device. Set C Ta p e s C 1 , C 2 , and C 3 . These must be r estored as a set, after Set A, and either before or after Set B. They can b e in either the secon[...]

Restoring your databases 406 Descriptio n The DBLOG command line ut ility allows you to d isplay or change the n ame of the transaction log or transaction log m irror associated with a database. Y ou can also stop a database from main taining a transaction log or mirror , or start main taining a trans action lo g or mi rror . T ransaction log utili[...]

CHAPTER 11 Backup and Data Recovery 407 In an IQ Multiplex, you m ust restore on the write server in simplex mode, and synchronize the query server following the com pletion of the restore. For more information on multiplex restores, see Adaptive Server IQ Multiplex User’s Guide . This restriction extends to any in cremental restores you may need[...]

Restoring your databases 408 For an example o f the information y ou see in a hea der file, see any RES TORE line in the sample back up log in “Content of the backup log”. A RES TORE with CA T ALOG ONL Y produces the information in the same format as the backup log entry for an actu al RESTORE . Recovery from errors dur ing restore If an increm[...]

CHAPTER 11 Backup and Data Recovery 409 When you back u p the files and r estore them with the CA T ALOG ONL Y option, you don’ t see anything telling you that thes e files were links; in fact, thi s information is no t saved. Adaptive Server IQ saves these files as t hough they were actually present in the directory where the symbo lic lin ks re[...]

Getting information about backups and restores 410 If you ar e using a third- party backu p product , the vend or infor mation s tring needs to convey any information needed for the backu p, such as the specification of devices, size of files, and stacker drives. See you r vendor’ s document ation f or details . Note Adaptive Server IQ does not p[...]

CHAPTER 11 Backup and Data Recovery 41 1 • On W indows NT , the server tries to place it in the following locations, in this order: • The directory specified b y the ASLOGDIR environment var iable. • The directory that h olds the server executable files. Content of the backup log For every backup or restore you p erform, the backu p log conta[...]

Getting information about backups and restores 412 20:07:00.000’, ... DBA, InSF, Arch, A_partial2_yes_sf , ’’ Maint aining the backup log It’ s a good idea to clean up the backup log after you pu r g e backu p media. Use a text editor to do so. Be carefu l with your edits: once BACKUP or RE STORE records infor mation in this file, it does n[...]

CHAPTER 11 Backup and Data Recovery 413 Keep the results of this query some place other than the disk where the database resides, so that you will have a complete list of dbspace names if you need them. Y ou can also r un th e follo wing scri pt in DBIS QL. This scri pt prod uces an output file that con tains the set of rename clau ses you wou ld u[...]

Determining your data backup and recovery s trategy 414 Scheduling routine backups Make a full backu p of each database just after you create it, to provide a b ase point, and perform full and incr emental backups on a fixed schedule thereafter . It is especially important to back up yo ur database after any lar ge number o f changes. Y our backu p[...]

CHAPTER 11 Backup and Data Recovery 415 For exampl e, once you have a ful l backup of yo ur databa se, in theory you could perform only in cremental backup s th ereafter . Y o u wo uld not wa nt to do this, however , because any future recover y would be intolerab ly slow , and would require more tape or disk space than doing a full backu p periodi[...]

Determining your data backup and recovery s trategy 416 Eliminati ng dat a verification Y ou can al so improve the speed of backup and restore operat ions by se tting CRC OFF in th e BACKUP command. This setting deactivates cyclical redundancy checking. W ith CRC ON , number s computed on backup are verifi ed durin g any subs equent res tore oper a[...]

CHAPTER 11 Backup and Data Recovery 417 Controllin g the size of th e Cat alog Store An IQ databas e consists o f an IQ S tore and an underlyi ng Catalog St ore. BACKUP makes a full backu p of the Catalog S tore at the start of ever y backup, both full and incremen tal. Ordin arily the Catalog Store is quite small, containing only the system tables[...]

Determining your data backup and recovery s trategy 418[...]

419 CHAPTER 12 Managing System Resources About thi s chapter This chapter describes the way Adaptive Server IQ u ses memory , disk I/O, and CPUs, and the relationships among these factors. It also explains how the DBA can tune p erformance by ad justing resource usag e. The suggestions in this chap ter are generic. Y ou need to adjust them to suit [...]

Overview of memory use 420 Other considerations, such as hardware and network analysis, can locate bottlenecks in yo ur in stall ation. Overview o f memory use Adaptive Server IQ u ses memory for several pu rposes: • Buf fers f or data read from disk to resolve q ueries • Buf fers f or data read from disk when loading f rom flat files • Overh[...]

CHAPTER 12 Managing System Resources 421 T o make the maximum use of your ph ysical memory , Adaptive Server IQ u ses buff er caches for all reads and writes to y our databases. Utilities to monitor sw apping Y ou can use the UNIX vmstat command, the UNIX sar com mand , or the W indows NT T ask Manag er , to get statistics o n the number of runnin [...]

Overview of memory use 422 Killi ng pr oces se s af fect s sha red memory W arnin g! Killin g proces ses on UNIX sys tems may res ult in semapho res or shared memory being left behind in stead of being cleaned up automatically . The correct way to shut do wn an IQ server on UNIX is the s top_asiq u tili ty , desc ribe d in “Stopping the databa se[...]

CHAPTER 12 Managing System Resources 423 Figure 12-1: Bu ffer c aches in rel ation to p hysical me mory The following sections describe each part in mo re detail and p rovide guidelines to help you d etermine how much mem ory each part requires. Operating system and other appli cations This amount will vary fo r different platforms and how the syst[...]

Overview of memory use 424 Raw p artitions versus file s ystems For UNIX systems, databases using “coo k ed” file systems rather than raw partitions may require another 30% of the remaining memo ry to handle file buf fering b y the operating system. W indows NT file systems do not have the same overhead as UNIX file systems. However , W indows [...]

CHAPTER 12 Managing System Resources 425 Adaptive Server IQ main and temp buffer caches After determ ining ho w much ov erhe ad mem ory Ad aptive Se rver I Q needs, you must decid e how to split what’ s left betw een your mai n IQ and temp buf fer caches. The dashed line di viding the two areas in Figure 12-1 indicates that this split m ay change[...]

Overview of memory use 426 Other con siderations Adaptive Server IQ buf fer cache sizes may dif fer from one datab ase to the next based on how you use it. For m aximum perf ormance, y ou need to chan ge the setti ngs between insert ing, quer ying th e databa se, and mi xed use. In a mixed- use environmen t, however , it is n ot always feasible to [...]

CHAPTER 12 Managing System Resources 427 a Minimum op erating system use for W indows NT is 30MB As shown in th e table, you should have one set of values for your buf fer caches when primarily insertin g into the database, an other set when prim arily querying the database, each dif fering from a typical mixed load of inserting and querying. T o c[...]

Overview of memory use 428 Setting buffer cache siz e dat abase options T o set buffer cache sizes permanently (that is, until you explicitly reset them) use the SET O PTION statement. Follow the procedure b elow . ❖ T o cha nge the bu ffer cache s izes pe rmanently : 1 If the database who se cache sizes you are changing is not already started, s[...]

CHAPTER 12 Managing System Resources 429 3 Make sure all users are discon nected from the datab ase, and stop the database. • If the -ga command-line swit ch is set, the datab ase shuts do wn automatically after the last user disconnects. • If -ga is not set, stop the da tab ase, by usin g th e stop_asiq uti lity on UNIX, or by cl icking Sh utd[...]

Overview of memory use 430 Because the Catalog Store accounts for o n ly a tiny fr action of I/O, the page size for the Catalog S t ore h as no real impact on perform ance. The d efault value of 4096 by tes sho uld be adeq uate. The IQ page s ize determin es two other per formance fac tors, the defaul t I/O transfer blo ck size, and the maximum dat[...]

CHAPTER 12 Managing System Resources 431 T able 12 -3: Default bloc k sizes Dat a compression Adaptive Server IQ compresses all d ata when storing it on disk. Data compression both red uces disk space requirements and contributes to performance. Th e amount of com pression is determined automatically , based on the IQ page size. Saving memory If yo[...]

Overview of memory use 432 Use the following guidel ine to determine BLOCK F ACTOR : record size * BLOCK FACTOR = memory required Y ou need extra memory for this option, in add itio n to the memory for the buf fers. If yo u have a lo t of m emory availabl e, or if no other users are active concurrently , increasing th e value of BLOCK F AC TOR can [...]

CHAPTER 12 Managing System Resources 433 -c The Catalog S t ore buf fer cache is also the general memo ry pool for the Catalog Store (the fron t end o f Adaptive Se rver IQ). Sy base recommend s a 64MB minimum fo r 500 u sers. Use of the -iqmt option is not requi red. If -iqmt is set too lo w for the number of specified connectio ns , the number of[...]

Overview of memory use 434 W ithin the "proc:" section of this file y ou m ust update max-thread s-p er- user . For example: proc: : . max-threads-per-user = 9000 Since this count is on a per user basis (not a per process bas is) this num ber must be high enoug h to allow for all threads of all proces ses ru nning under the Sybase user ID[...]

CHAPTER 12 Managing System Resources 435 T able 12 -4: T otal a vailable m emory by pla tform On 64-bit platforms (T ru64, SGI IRIX, and Solaris 64-bit), th e only limit is the physical size of memory o n the system. For W indows NT systems, see the Adaptive Server IQ Inst all ati on and Configu ration Guid e for more performance t uning hin ts, in[...]

Overview of memory use 436 Unwired mem ory pool On AIX, T ru64 (Digital UNIX), and HP UNIX systems, you can cr eate an unwired (swappable) memory po ol to increase total avai lable memory . Unwired memory can be paged. T o create the unwired mem ory pool, use the - iqsmem command-line switch. Specify th is switch as the number of MB of unwired memo[...]

CHAPTER 12 Managing System Resources 437 Managing large buffer caches on HP On HP UNIX, start_asiq sets -iqsm em to 5 00 by default. This setting allo ws a total buff er cache size (i. e., main and temp cach es combined) of 2 GB. If you need more than 2 GB for buf fer caches, and yo ur system can accommodate a lar ger valu e, you must add u nwired [...]

Overview of memory use 438 • On Solaris systems with more than 4GB of memory , the file system buffer cache competes with IQ's buffer cache less, so you may decrease perfo rmance by turnin g of f fil e system buf fering. • On NT s ys t ems, un der cert ain l oad s and conf igurat ion s, di s abl i ng t he f ile system buf fer cache can lik[...]

CHAPTER 12 Managing System Resources 439 The JA V A_N AMESP AC E_SIZE optio n of the SET OPTION command sets the maximum size (in bytes) of that part of the memo ry that is allocated to Java applications on a p er database basis. Per d atabase m emory allocations include Java class definitions. As class definitions are eff ectively read-only , they[...]

The process threading model 440 Figure 12-2 : Multithreade d architecture Multiple threads im prove perfor mance. Even a single-C PU machine gets better performanc e by using thr eads. Insufficient threads error When you do not have eno ugh server threads to initiate the qu ery you have issued, you get the error: Not enough server threads available[...]

CHAPTER 12 Managing System Resources 441 • T o set the maximum number of th reads a si ngle user will us e, issue the command SET OPTION MAX_IQ_ THREADS_P ER_CON NECTION . This can be used to con trol the amount of memory a particular o peration consumes. For example, the DBA can set this option befor e issuing an INSERT or LOAD command. Balancin[...]

Balancing I/O 442 Using disk striping T radi tional f ile managem ent sys tems allo w you to locat e indivi dual fil es on specific disks. Consequently , all file operations occur against a single disk drive. Some opera tin g systems allow you to cr eate log ical devices o r volumes that span multipl e disk drives. Once a file fills the first disk [...]

CHAPTER 12 Managing System Resources 443 Recommendations for disk st riping Here ar e some ge neral ru les o n disk strip ing: • For maxi mum performan ce, the in dividual disks in a st riped file system shou ld be spre ad ou t acros s seve ral d isk co ntro llers. But be care ful no t to saturate a disk cont roller with too many dis ks. T ypical[...]

Balancing I/O 444 Disk str iping opti on This sect ion explai ns how you can use t he option Adap tive Serv er IQ provi des to do disk str iping, without u sing thi rd par ty software. If you alr eady have a disk stripin g soluti on thr ough third party s oftware and h ardware, yo u should use it instead. T urni ng disk st riping on or o ff The syn[...]

CHAPTER 12 Managing System Resources 445 Figure 12-3: I nternal di sk striping The example ab ove shows disk dr ive A has t wo 2GB p artitio ns (a and b ) and two 500MB (or .5GB) par tition s (c and d) . There are thr ee other 1GB di sk drives (E, F , and G). Y ou should create yo ur database on partition a, then add dbspace s for E, c, F , b, G an[...]

Balancing I/O 446 T o move or rename the transaction lo g file, use the T ransaction Log utility ( DBLOG ). For syntax and details, see “T he DBLOG comman d-line utility” on page 405. W arnin g! The Adaptive Serv er IQ transaction log file is dif f erent than most relational databas e trans a ction log files. If for some reason you lose your da[...]

CHAPTER 12 Managing System Resources 447 Figure 12-4: Av oid I/O con tention The figure above illustrates how you want to spread access across separate disks to avo id I/O cont ention. T o avoid dis k bottlenecks , follo w thes e suggesti ons: • Keep ra ndom disk I /O away from sequen tial disk I/O. • Isolat e IQ database I/O from I/O for proxy[...]

Options for tuning resource u se 448 Ordinarily , as long as you main tain a reasonable percentage o f free space in your IQ S tore, you will have enough free space. However , for certain deletions, dependi ng on th e size of t he data an d its di stributi on among dat abase p ages, you may n eed a lar ge amount of working space. In the case where [...]

CHAPTER 12 Managing System Resources 449 Limiting a query’s memory use The QUERY_TEMP _SP ACE_ LIMIT option of t he SET command lets y ou restrict the amount of memor y available to any one qu ery . By default, a query can use 100 0MB of memory . When you issue a query , Adaptive Server IQ estimates the tempor ary space needed to resolve the q ue[...]

Options for tuning resource u se 450 If scrolling curso rs are never used in you r application, yo u should make this a permanent PUBLIC opti on . It wil l us e less me mory and m a ke a bi g improvement in query performance. Limiting the number of curs ors The MAX_C URSOR_ COUNT option specifies a resource governor to limit the maximum nu mber of [...]

CHAPTER 12 Managing System Resources 451 Optimizing for typical usage Adaptive Server IQ tracks the number of op en cursors and allo cates memory accordingly . In certain circumstances, USER_R ESOUR CE_RESERV A TIO N option can be set to ad just the minimum nu mber of curren t cursors that IQ thinks is cu rren tly using the produc t and hence alloc[...]

Indexing tips 452 Operating systems use two dif ferent methods to allocate memory to disk cache: fixed an d dynamic. A pres et amount o f memory i s used in a fi xed allo cation; usually a 10 –15 percent memory allocation is set aside. The operating system usually manages this workspace using a LRU (least recently used) algorithm . For a dynamic [...]

CHAPTER 12 Managing System Resources 453 Y ou should create either an LF or HG index in addi ti on t o th e defau l t i n dex o n each column referen ced by the WHER E clause in a join query . Adaptive Server IQ cannot guarantee that its query op timizer will produce the best execution plan if some columns referenced in the WHERE clause lack either[...]

Managing database si ze and structure 454 Managing database size and structure This secti on of fers ideas o n improvin g your dat abase design and managin g your dat a. Managing the size of your dat abase The size of your datab ase depen ds lar gely on t he indexes yo u create, and the quantity of da ta you maintain. Y ou achieve faster query p ro[...]

CHAPTER 12 Managing System Resources 455 Denormalization has risks Denormalization can be successfully performed only with thorou gh knowledge of the appli cation and shou ld be performed on ly if performan ce issues indicate that it is needed. One of the things to consider when y ou denormalize is the amount of effort it will then take to keep you[...]

Improving your queries 456 Deciding to denormalize When deciding whether to denor malize, you need to an alyze the data access require ments of the app lications in your en vironmen t and th eir actual performanc e characteristics. Some of the issues to examine when considering denormalization inclu de: • What are the critical qu eries, and what [...]

CHAPTER 12 Managing System Resources 457 • Join indexes ty pically cause join queries to execute faster than ad hoc joins, at the expense of using m ore disk space. However , when a join query does not reference the largest table in a multi -t able jo in index, an ad hoc join usuall y outperforms th e join index. • Y ou can improv e performance[...]

Improving your queries 458 • QUERY_TIMING This op tion cont rols the collection of timing statis tics on subque ri es and some other repetitiv e functions in the query engin e. Normally it should be OFF because for very short co rrelated subqu eries the cost of timin g every subquery ex ecution can be very expensive in terms of p erformance. Sett[...]

CHAPTER 12 Managing System Resources 459 • JOIN_MAX _HASH_ ROWS Sets the maximum estim ated number of rows the query optimizer will consider for a hash algorithm. The default is 125,000 r ows. For example, if there is a join between two tables, and the estimated number of r ows entering the join fr om both tables exceeds this option value, the op[...]

Network performance 460 • If lar ge transfers are common, consider in stalling better network hardware that is suitable for such transfer s. For example: • T oken ring–res ponds bett er durin g heavy ut ilization periods than ethernet hardware. • Fiber optic– provides very hig h bandwidth, but is usually too expensive t o use t hroughou t[...]

CHAPTER 12 Managing System Resources 461 Figure 12-5: I solating heavy network users Put small amounts of dat a in small p ackets If you send small a moun ts of data over the network, keep the defaul t netwo rk packet size small (default is 512 by tes). The -p server startu p opti on lets you specify a maximum packet size. Y our client application [...]

Network performance 462 Figure 12-6 : Small d ata transfers a nd smal l packet siz es Put large amounts of dat a i n large packet s If most o f your applications send and receive lar ge amounts of d ata, increase default network packet size. Thi s will result in fewer (but larger) transfers. Figure 12-7 : Large d ata transfer s and larger pa cket s[...]

CHAPTER 12 Managing System Resources 463 Process at the serv er level Filter as much data as possi ble at th e server level. Figure 12-8: W ork at the server leve l[...]

Network performance 464[...]

465 CHAPTER 13 Moni toring and T uning Performance About thi s chapter This chapter de scribes tools you use to monitor Adaptiv e Server IQ performance. Use these tools to determine wheth er your system is making optimal use of available re sources. T o understand ho w Adaptive Server IQ uses memory , process threads, and disk, and to learn about o[...]

Viewing the Adaptive Server IQ env ironment 466 • sp_iqcheckdb checks the validity of you r current database • sp_iqdbstatistics reports results of the most recent sp_iqcheckdb • sp_iqdbsize gives the size of the current database • sp_iqstatus displays mi scel lan e ou s s tat us i nfor mat ion about th e dat ab ase. See the example below .[...]

CHAPTER 13 Monitoring and Tuning Performance 467 Block#: 95065709 Temporary IQ Blocks Used: 610 of 17646576, 0%=4Mb, Max Block#: 0 Main Reserved Blocks Available: 1280 of 1280, 100%=10Mb Temporary Reserved Blocks Available: 1280 of 1280, 100%=10Mb Memory: Curr ent: 3351mb, Max: 3384mb Main IQ Buffers: Used : 11172, Locked: 0 Temporary IQ Buffers: U[...]

Monitoring the buffer caches 468 PRIV A TE starts monitoring o f the temp b uffer cach e, for the T emporary S tore of the database you are connected to. Y ou need to issue a separate command to monitor each b uffer cache. dummy_tabl e_name can be any IQ table. However , it’ s a good idea to create a table th at you use only fo r monitoring. The [...]

CHAPTER 13 Monitoring and Tuning Performance 469 • Reads/W rites : Physical reads and wr ites per formed by t he buf fer cache • PF/PFRead : Prefetch requests and reads d one for prefetch • GDirty : Number of t imes th e LRU buf fer was g rabbed d irty and Adaptive Server IQ had to write it out before using it • Pin% : Percentage of p ages [...]

Monitoring the buffer caches 470 • Av P F : Number of currently available buf fers for prefetch quota allocation • Slots : Number of currently registered ob jects using buff er cache quota • PinUser : Number of objects using pi n quota • PFUsr : Number of objects using prefetch qu ota • Posted : Number of object s that are pre-planned us [...]

CHAPTER 13 Monitoring and Tuning Performance 471 • FL W ait : Number of times IQ h a d to wait for the lock on the free list (repeated for the temp cache) • MemL ks : Number of times I Q took the mem ory manager (h eap) lock • MemW ts : Number of times IQ had to wait for the memory manager lock • -threads displays inform ation ab out proces[...]

Monitoring the buffer caches 472 • -debug is used ma inly to supply in formation to Sybase T echnical S upport. It displays all the information av ailable to the performance mon itor, whether or not there is a standa rd display mode that co vers the same information. T he top of the page is an array of statistics broken down b y disk block ty pe.[...]

CHAPTER 13 Monitoring and Tuning Performance 473 When you run th e monitor ag ain from the same database and connection number , by default i t overwri tes the prev ious resul ts. If you need to sa ve the results of a monito r run, cop y the file to anothe r locatio n before starting the monitor again fro m the same database or use the -append op t[...]

Monitoring the buffer caches 474 Finds Creats Dests Dirty HR% BWaits ReReads FMiss Cloned Reads/ PF/ GDirty Pin% Dirty% Writes PFRea d Tm: 640 82 57 84 99.4 0 4 0 0 4/0 0/0 0 0.0 2.8 Tm: 1139 109 83 109 100.0 0 0 0 0 0/0 0/0 0 0.0 5.5 Tm: 6794 754 749 754 100.0 0 0 0 0 0/0 0/0 0 0.0 6.1 Tm: 10759 1646 1646 1646 100.0 0 0 0 0 0/0 0/0 0 0.0 6.1 Examp[...]

CHAPTER 13 Monitoring and Tuning Performance 475 Exam ple of -contenti on option Note The actual -contentio n output shows Main Cache, T emp Cache, and Memory Manager on the same line. Because this format is very wide, each of these sets of colu mns is shown separately here. The -contention results for the main cache are: Options string for Main ca[...]

Avoiding buffer manager thrashing 476 2 0 137 0 22 0 203 3 Exampl e of -thread s optio n The results of t he -threads option lo ok lik e the f ollowing: Options string fo r Main cache: "- threads -file_suffix threads-iqmon -append -interval 10" Threads 2000- 01-24 10:59:24 CPUs Limit NTeams MaxTms NThrds Resrvd Free Locks Waits 10 100 4 1[...]

CHAPTER 13 Monitoring and Tuning Performance 477 Buffer man ager thrashing occur s when the oper ating system chooses less optimu m buf fe rs to pag e out t o disk, whic h forces th e buf f er manage r to make extra reads from d isk to bring those buf fers back to memory . Since Adap tive Server IQ kn ows which buffers are the best candidat es to f[...]

Avoiding buffer manager thrashing 478 0 0 0 3312376 31840 0 8 0 0 0 0 0 0 0 0 297 201 472 82 4 14 0 0 0 3312376 31484 2 3 0 0 0 0 0 0 0 0 260 169 597 80 3 17 0 0 0 3312368 31116 0 8 0 0 0 0 0 0 0 0 205 1202 396 67 4 29 The above output sho ws a steady Adaptive Server IQ querying state where the physical memory of the mach ine has not been overal lo[...]

CHAPTER 13 Monitoring and Tuning Performance 479 19 0 0 3246612 26748 0 273 1248 0 0 0 0 950 394 1180 92 7 0 The above output is from slightly later when th e query is underway . This is evident from the us er mode CPU level ( us field). The buf fer cache is not yet full as page-in faults ( pi fiel d or KB pag ed in) are st ill occurr ing and the a[...]

System util ities to monitor CPU use 480[...]

481 CHAPTER 14 Adaptive Server IQ as a Dat a Server About thi s chapter Adaptive Server IQ sup ports client applicatio n connections th rough either ODBC or JDBC. This chapter describes how to use Adaptive Server IQ as a data server for client applications. W ith certain limitations, Adaptiv e Server IQ may also appear to certain client application[...]

Client/server interfaces to Adapti ve Server IQ 482 Open C lient s and Open Servers Members of the Adaptive Server family act as Open Servers . Client applications communicate with Open Servers using the O pen Client libraries available from Syb ase. Open Client includes both the Client L ibrary (CT - Library) and the older DB-Library inter faces. [...]

CHAPTER 14 Adaptive Server IQ as a Data Server 483 At the other en d of the client/server connection , while man y Open Ser vers use the Sybase Open Server librar ies to handle the interface to TDS, some applications have a direct interf ace to TDS of their own. Syb ase Adaptive Server Enterprise and Ad aptive Server IQ both have inter nal TDS inte[...]

Client/server interfaces to Adapti ve Server IQ 484 Using the DSEDIT utility The DSEDIT utility is an Open Cl ient utility th at allo ws you to configu r e the interfaces file ( sql.ini or interfa ces ). The follo wing sec tions expl ain how to use the DSEDIT utility to configure the interfaces file. Y ou must be the owner of the Sybase home direct[...]

CHAPTER 14 Adaptive Server IQ as a Data Server 485 • The interfaces file ( sql.ini ). ❖ T o open a ses sion: • Select Interfaces Driver from the DS Name box and click OK. Note The DSEDIT utility uses the SYBASE environment variab le to locate the libtcl.cfg fil e. If the SYBASE env ironment variable is not set correctly , DSEDIT cannot locate[...]

Client/server interfaces to Adapti ve Server IQ 486 2 Select the Server Address in the Attribu tes box. 3 Double-cl ick on the Se rver Address or righ t click and choose Modify Attribu te from the popu p menu . The Network Address Attr ibute wind ow appears, showing the cu rren t value o f the ad dress. If y ou have n o ad dress entered, the box wi[...]

CHAPTER 14 Adaptive Server IQ as a Data Server 487 to identify the current m achin e. Port Number The port n umber you enter mu st match the port specified on the Adaptive Server IQ database server command line, as described in “Starting the database server a s an Ope n Server”. The default port number f or Adaptive Server IQ servers i s 2638. [...]

Client/server interfaces to Adapti ve Server IQ 488 3 T ype a new name for the server entry in the Server Name box. Click OK to make the chan ge. Deleting serve r entries Y ou can del ete serv er entries fr om the dsedit sess ion win dow . ❖ T o dele te a server e ntry: 1 Click a server entry in the Server box . 2 Choose Delete f rom the Server O[...]

CHAPTER 14 Adaptive Server IQ as a Data Server 489 Setting up Adap tive Serve r IQ as an Open Server This section describes how to set up an Adaptive Server IQ server to receive connections from Open C lient applications. System requiremen t s There are separate requ irements at the client and server fo r using Adaptive Server IQ as an Open Server [...]

Setting up Adaptiv e Server IQ as an Open Server 490 asiqdemo.db On UNIX, you can use the start_asiq utility in place of asiqsrv12 . The fir st command line u ses both TCP /IP and IPX pr otocols, of which TC P/IP is available for use by Open Client applications. The secon d line uses only TCP/ IP . The server can serve ot her applicat ions throu gh[...]

CHAPTER 14 Adaptive Server IQ as a Data Server 491 Characteristics of Open Client and jConnect connectio ns When Adaptive Server IQ is ser ving applications over TDS, it automatically sets relevant databas e options to valu es th at are compatible with Adap tiv e Server Enterprise default behav ior . These o pti ons are set temporarily , for the du[...]

Characteristics of Open Client and jConnect connections 492 SET TEMPORARY OPTION DATE_FORMAT=’Y YYY-MM-DD’; SET TEMPORARY OPTION TIMESTAMP_FORM AT=’YYYY-MM-DD HH:NN:SS.SSS’; SET TEMPORARY OPTION TIME_FORMAT=’H H:NN:SS.SSS’; SET TEMPORARY OPTION DATE_ORDER=’MD Y’; SET TEMPORARY OPTION ESCAPE_CHARACT ER=’OFF’ Note Do not ed it the[...]

CHAPTER 14 Adaptive Server IQ as a Data Server 493 Servers with multipl e dat abases Using Open Client Library , you can now connect to a specific database on a server with multiple d atabas es. • Set up entries in the interfaces file for each server . •U s e t h e -n param eter on th e start_asiq command to set up a shortcut for the database n[...]

Characteristics of Open Client and jConnect connections 494[...]

495 Symbol s & UNIX command line 28 A Access ODBC configuration for 67 ad hoc join s performance 149 Adaptive Server Enterprise inserting fro m 193 Adap t iv e Serv er IQ buffer caches 42 7 matchin g data ty pes with Adaptive S erver Enterprise 217 monitor syntax 467 aggreg ates 145 AGGREGATION_ALGORITHM_ PREFERENCE optio n 458 AGGREGATION_CUTO[...]

Index 496 recovering from errors 3 89 responsibi lities 415 schedu ling 41 4 spe cifying tape d evice s on NT 386 thir d party 3 90 unatte nded 38 2, 409 wait time 388 base tab les 12 0 batches abou t 229, 23 8 co ntro l stat ement s 23 9 data d efinition sta tements 239 SQL statem ents allowed 267 BIT d ata conv erting 20 7 indexe s al lowed i n 1[...]

Index 497 avoid ing t ransla tion 338 choo sing 344 definit ion 317 encod ing 315, 317 fixe d w i dt h 321 Interactive SQL 349 multib yte 321, 336 single -byte 319 Sybas e Centr a l 349 tran slatio n 348 Unicode 336 variabl e wi dt h 321 Wind ows 32 0 characters alphabe tic 343 digits 343 white space 343 CHECK condition s columns 2 78 deleti ng 280[...]

Index 498 backups 291 , 383 data de f initio n 300 in Adaptive Server IQ 290 insertions, deletions, and querie s 299 read and wri t e 292 config ura ti on fi les usin g 29 configuri ng ODBC data s ource s 67 CONN conn ect ion p arame ter about 73 connect permiss ion 357 connectin g character sets 338 connectio n name ODBC conf igurati on 7 0 connec[...]

Index 499 limi ting n umber of 450 ODBC c onfig uratio n 68 on SELECT statemen ts 2 53 procedu res 251 custom c ollations about 33 9 creating 339 crea ting d ataba ses 351 D daemon database se rver as 28 data duplic ated 27 3, 274 expo r tin g 171, 174 impo rtin g 171 in tra nsac tio ns 29 5 input an d ou tput form a t s 172 inva lid 273 load ing 1[...]

Index 500 about 73 DatabaseName con nection parameter about 73 databases Adaptive Server Anywhere 6 Adaptive Server IQ data 6 bene fits of de no rmal iz ing 455 block size 113 character set 336 checking consi s t ency 39 3 choosin g a lo cat ion 11 0 conn ecting to 50, 77 creating 106 custom c ollations 351 default ch aracteristic s 108 denormalizi[...]

Index 501 about 48 1 DBLOG utility 405 DBN connection pa rameter about 73 DBS conne ction pa rameter about 73 dbspaces creating 114 definit ion 104 droppin g discour aged 116 estimating spac e r equire ments 9 locating for best performance 4 45 DDL about 99 DDL (Da ta Defin ition L angua ge) 16 Debug conn ection par ameter about 73 DECLARE statemen[...]

Index 502 ODBC conf igurati on 6 9 Encryp tedPass word conn ection paramet er about 73 encryption network pac kets 70 Encry ption c onnectio n para meter about 73 ENG connec tion para meter about 73 Engine Name c onnecti on para meter about 73 ENP c onn ection para meter about 73 entity in tegrity about 276 enforcing 281 environm ent varia bles SQL[...]

Index 503 typ es of 16 G global te mporar y tabl es about 12 0 gm s witch 34 effect on recovery 3 08 GRANT statement creating groups 3 63 DBA authority 358 group membership 36 4 new users 357 passwords 3 57 per miss ion s 359 procedu res 361 RESOURCE authority 358 WITH GRANT OPTION 360 without passwo rd 366 GROUP BY clause impac t on ind ex choice [...]

Index 504 creatin g in Syb ase Central 139 disk space u sage 143 display ing siz e 9 droppi ng 134 in system tables 13 3 introd uction 13 2 listing 9 paralle l creation 1 39 selectin g an inde x type 150 inse rt c onver si on optio ns 204 INSERT LOCATION statement 193 INSERT permissions 35 9 INS ERT st atem ent 151 about 190 and int egrity 2 75 inc[...]

Index 505 TDS 48 2 JDBC conn ecti ons 51 joi n colu mn s 1 59 join hiera rchy 1 52 join in dexes about 15 1 altering c olumns 124 column s in tables 153 creating 157 creating in Sy base Ce ntral 164 estimatin g size 16 9 estimating spac e r equire ments 9 inserting into 1 95 join hiera rchy 1 52 join rela tionships 159 listing siz e 9 modify ing un[...]

Index 506 local temporary tables about 120 locale character sets 336 langu age 324 locales about 323 setting 346 localhost machine name 485 lock ing tables 29 9 LOG connec tion para meter about 73 Logf ile co nnecti on par ameter about 73 LOGIN_MODE database optio n inte gr ate d login s 87 login s inte grate d 86, 87 LOOP statement in proc edures [...]

Index 507 N named pip es 179 natio nal lan guage s uppor t about 31 5 collatio ns 344 multibyte c haracter sets 336 overview 31 5, 317 natural joi ns 163 NEAREST_CENTURY option 212 network co mmunicat ions trou blesh ooting st artup 94 netw ork p rotoc ol specif ying 6 0 netw ork p rotoc ols ODBC c onfig uratio n 70 networ ks large transfers 459 pe[...]

Index 508 P page size 11 1 Catalo g 37 switch 37 pagi ng effect on performance 420 memory 420 monito ring o n UNI X 477 monit orin g on W indow s NT 477 Parallel CREATE INDEX 139 partia l -width i nser t ions about 197 rul es 1 98 START ROW ID o ption 194 partial-width inserts examples 19 9 partiti ons defin ition 441 password default 35 4 Password[...]

Index 509 LOAD TABLE stat eme nt 184 prim a ry ke ys creating 125 entity in tegrity 283 inserting data 196 order of columns 12 6 retr ievi ng inf orma tio n 11 unenfo rced m ulti-c olumn 126 priority loweri ng 450 privile ges definin g databas e obje cts 1 03 for insertin g and deleting 173 procedu res about 22 9 benefi ts o f 230 callin g 232 comm[...]

Index 510 memory use 424 RAWDETECT disk stri ping op tio n 44 4 REAL data t ype matchi ng Adaptiv e Server E nterprise and Adaptive Server IQ data 218 rec ove ry system 307 transa ction log in 308 tr ansa cti ons in 307 redire cting outpu t to files 174 REFERENCES p ermissions 359 referent ial integrit y declarin g 283 enforcing 281 permiss ions 17[...]

Index 51 1 asasrv.ini fi le 84 server n ame ODBC c onfig uratio n 69 Ser verNa me co nnecti on pa ramet er about 73 servers multiple databases o n 493 SET clause UPDATE statement 225 seven-bit ch aracters about 31 9 shut dow n data base 47 trou blesh ooting 93 SIGNAL statement procedu res 257 single- byt e characte r set s about 31 9 snapshot versi[...]

Index 512 column const r aints 280 creating dbs paces 1 15 creating groups 36 4 creating t ables 11 8 creating users 357 creating v iews 12 9 droppin g views 1 31 fore ign keys 126 introd uction 10 0 permiss ions 361 prim ary keys 12 5 stop ping 50 system tables 127 SYBASE environment variable DSEDIT 484 sync hroniz ing about 158 SYS grou p 367 SYS[...]

Index 513 lock ing 29 9 owne r 355 per miss ion s 355 qualifi ed nam es 365, 3 67 See Also informatio n in system ta bles 12 tabular data stream (TDS) about 48 1 tape d e vi ces for backup 385 TCP/ IP addresses 4 85 Open Server 48 9 TDS about 48 1 TEMP environ men t variable disk space 93 TEMP_CACHE_MEMORY_MB op tion 427 tem pora ry st orag e optio[...]

Index 514 usin g 224 usin g join o perations 225 upper cod e page about 319 user accounts addi ng with sp_addl ogin 10 user ID ODBC conf igurati on 6 9 user IDs creating 357 default 35 4 deletin g 362 listin g 374 managing 353 user-de fined da ta types CHECK conditio ns 279 user-defined func tions calli ng 236 creating 235 droppi ng 237 exe cu tio [...]

Index 515 defin ed 343 WIN_LAT IN1 collatio n about 33 2 wired mem ory 435 setting iqwmem switch 34 WITH GRANT OPTION clause 360 Y year 20 00 conv ersi on o ption s 212 Z -Z optio n database se rver 94 zeros conver ting to NULL 215[...]

Index 516[...]