Instruction/ maintenance manual of the product 12.4.2 Sybase
Go to page of 536
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.
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 .
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 .
Conte nts v Steps in establishing a connection .......................................... 77 Locating the interface library ................................................... 78 Assembling a list of connection parameters ............................ 79 Locating a server .
Contents vi Deleting views ....................................................................... 131 Views in the system tables .................................................... 131 Working with 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 .
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 .
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 .
Contents x Referential integrity is unenforced ......................................... 284 Integrity rules in the system tables ............................................... 285 CHAPTER 8 Transac tions and Versioning ....... ............. ........
Conte nts xi Understanding locales .................................................................. 323 Introduction to locales ........................................................... 323 Understanding the locale language .......................
Contents xii Managing individual user IDs and permissions ............................ 356 Creating new users ....... ........................................................ 357 Changing a password ....................................................
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 .
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 .
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 .
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 .
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.
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 .
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.
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.
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.
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 .
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.
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.
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.
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.
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 st.
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 .
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.
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.
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 .
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.
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 .
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.
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 .
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.
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 .
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.
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.
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.
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 .
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 .
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 .
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.
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 .
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.
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 follo.
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.
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.
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 .
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.
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.
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 .
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.
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 .
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.
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 .
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.
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 .
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 .
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.
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.
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.
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.
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 .
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).
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 .
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.
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.
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.
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.
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 .
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.
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 .
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 .
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.
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 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 .
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.
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.
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.
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.
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.
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 .
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.
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.
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.
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.
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 .
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.
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.
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 .
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 .
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.
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.
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.
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).
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.
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 .
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.
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 .
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.
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.
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”.
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.
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.
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.
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 .
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 .
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.
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.
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 ” .
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.
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.
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.
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.
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.
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.
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.
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 .
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.
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.
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.
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.
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.
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.
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.
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.
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 .
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.
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 .
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.
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.
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.
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.
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.
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.
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.
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.
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 .
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 .
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 .
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.
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.
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.
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.
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.
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 >.
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.
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 .
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.
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.
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.
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.
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 .
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.
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.
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.
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.
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.
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.
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 .
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 .
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".
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.
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.
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.
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.
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 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.
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.
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 .
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.
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 .
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 .
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.
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.
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: .
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 ] .
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.
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 .
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.
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.
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.
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.
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: U.
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 .
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.
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 .
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.
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 .
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 .
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 ( .
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.
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.
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.
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.
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.
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.
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.
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 .
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.
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.
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 <).
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.
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.
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.
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.
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.
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.
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 exam.
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.
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 .
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.
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 .
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.
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.
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.
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.
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.
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 .
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”.
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.
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”.
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.
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.
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.
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.
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.
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 VAR.
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.
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.
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.
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.
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 .
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.
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.
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 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.
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.
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.
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.
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 .
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.
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 .
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.
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.
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.
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.
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 To.
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.
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.
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.
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.
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 .
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 ).
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.
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 .
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.
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.
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.
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.
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.
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 .
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 .
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 .
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.
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.
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”.
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.
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.
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.
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.
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.
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 CH.
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 .
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 .
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.
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 .
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 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.
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.
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”.
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.
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.
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.
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.
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.
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.
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 .
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.
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.
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.
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 expli.
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.
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.
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.
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.
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 .
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.
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.
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 .
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 .
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.
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.
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.
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 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 ).
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.
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.
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 .
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 .
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.
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.
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.
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 .
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.
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.
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.
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 .
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.
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 .
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.
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.
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.
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.
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 Po.
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.
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.
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 .
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.
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.
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.
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, .
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.
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.
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.
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.
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.
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 .
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.
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.
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.
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.
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.
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.
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 .
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.
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 .
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 .
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.
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 (colu.
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.
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 .
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 .
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.
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 .
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.
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.
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.
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 .
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 .
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 .
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.
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 .
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 .
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.
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.
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.
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.
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.
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.
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.
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 .
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.
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 .
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.
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.
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.
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 .
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 .
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 .
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.
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.
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.
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.
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.
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.
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 .
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.
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.
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.
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 seve.
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.
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 .
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 pe.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
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 .
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.
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 .
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 .
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 .
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
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 Res.
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.
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 • GDir.
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 • Pin.
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 .
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.
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.
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.
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.
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.
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 .
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 20.
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 .
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.
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.
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.
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.
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.
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”.
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 .
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.
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.
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 .
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.
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 .
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 matchi.
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.
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 .
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 our.
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.
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 checki.
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 equir.
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 E.
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 password.
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 .
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 e.
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 Log.
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.
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 nse.
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 .
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 .
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 arac.
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 .
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.
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.
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 9.
Index 516.
An important point after buying a device Sybase 12.4.2 (or even before the purchase) is to read its user manual. We should do this for several simple reasons:
If you have not bought Sybase 12.4.2 yet, this is a good time to familiarize yourself with the basic data on the product. First of all view first pages of the manual, you can find above. You should find there the most important technical data Sybase 12.4.2 - thus you can check whether the hardware meets your expectations. When delving into next pages of the user manual, Sybase 12.4.2 you will learn all the available features of the product, as well as information on its operation. The information that you get Sybase 12.4.2 will certainly help you make a decision on the purchase.
If you already are a holder of Sybase 12.4.2, but have not read the manual yet, you should do it for the reasons described above. You will learn then if you properly used the available features, and whether you have not made any mistakes, which can shorten the lifetime Sybase 12.4.2.
However, one of the most important roles played by the user manual is to help in solving problems with Sybase 12.4.2. Almost always you will find there Troubleshooting, which are the most frequently occurring failures and malfunctions of the device Sybase 12.4.2 along with tips on how to solve them. Even if you fail to solve the problem, the manual will show you a further procedure – contact to the customer service center or the nearest service center
