Manuals / Sybase / Computer Equipment / Server

Sybase 12.4.2 manual Index, 496

Models: 12.4.2

1 536

Download 536 pages 20.34 Kb

Page 516

Index

recovering from errors				389
responsibilities 415
scheduling		414
specifying tape devices on NT 386
third party		390
unattended		382, 409
wait time		388
base tables	120
batches
about	229, 238
control statements			239
data definition statements					239
SQL statements allowed					267
BIT data
converting		207
indexes allowed in			143
blanks
converting to NULLs				215
BLOCK FACTOR
BACKUP statement option					388
load option		181, 431
BLOCK SIZE
LOAD TABLE option				182
block size	113
relationship to IQ page size					430
buffer cache
monitoring		467
buffer cache monitor			467
examples		473
buffer caches		427
determining sizes			422
example	426
setting sizes		427
buffer manager
thrashing		476
buffer size
ODBC configuration				70
buffer space
ODBC configuration				70

buffers

disabling operating system buffering 437 build number 17

BYTE ORDER option

LOAD TABLE statement 182

C

-cswitch 35 cache

See Also buffer cache 467

writing to		289
cache pages
prefetching		450
cache size
setting for Catalog Store					35
CALL statement
about	229
examples		232
parameters		245
syntax	239
case sensitivity
collations		342
command line			29
database and server names					32
international aspects				322
CASE statement
syntax	239
CATALOG ONLY
RESTORE option				407
Catalog Store
about	6
setting cache size				35
CBSize connection parameter
about	73
CBSpace connection parameter
about	73
CHAINED option
Open Client		491
CHAR data
zero-length cells			210
character data types
matching Adaptive Server Enterprise and Adaptive
Server IQ data				219
character set
application		325
determining		325
server	325
character set translation
about	348
error messages			336
character sets
about	315

496

Image 516

Sybase 12.4.2 manual Index, 496

Contents

Administration and Performance Guide Adaptive Server IQDocument ID Contents Contents 100 101103 104Contents Deleting views 131 Views in the system tables 132135 138Vii Viii Using cursors on Select statements in procedures 253 255273 277Contents Referential integrity is unenforced 284 285299 309323 339352 Managing User IDs and PermissionsXii Xiii 448 454459 467473 476479 481Xvi How to use this book AudienceXvii Guide to using this book To learn how to Read this chapter Related documentsRelated documents Documentation for Adaptive Server IQAbout This Book XixRelated documents Overview of IQ system administration Introduction to Adaptive Server IQSystem administration tasks System administration tasksOverview of Adaptive Server IQ System Administration Administrative tasksSecurity overview Tools for system administration Tools for system administrationTypes of users Granting permissionsDatabase server Catalogs and IQAdaptive Server Anywhere and Adaptive Server IQ IQ StoreTemporary Store Catalog StoreStored procedures Concurrent operationsAdaptive Server IQ stored procedures Stored proceduresAdaptive Server Enterprise system and catalog procedures Stored Procedures for the IQ Store Procedure name PurposeAdaptive Server Enterprise system procedures System procedure DescriptionCatalog stored procedures Adaptive Server Enterprise catalog proceduresCatalog procedure Description Procedure name PurposeSystem tables and views System tables and viewsSystem table Description Sysdomain Syslogin System view Description SysuserauthCommands and Functions Commands and FunctionsTypes of SQL statements FunctionsMessage logging Manipulate date and time data for example, TODAY, DatediffFourth line of the file contains version information Utility database Utility databaseCompatibility with earlier versions Compatibility with earlier versions Starting the database server Server command lines Server command linesGeneral form for the server command line is as follows Starting the server on Unix Running Adaptive Server IQUsing the startup utility To start the server on Unix using the startup utilityStarting the server on Unix Typing the server startup command Starting the server on Windows NT Starting the server from the NT Start menuStarting the server on Windows NT Running the server outside the current session Using command-line switches Running the server as a Windows NT serviceUsing command-line switches Running the Unix database server as a daemonCase sensitivity Using configuration files Elora 16M Tcpipport=2367 -gm Gp 4096 path\mydb.db Naming the server and databases Required if you do not supply a databaseWindow Database startedSample database on that server Controlling performance from the command line Setting memory switchesDefault value of -iqgovernis 2 x the number of CPUs +10 This switch as the number of megabytes of wired memoryUse Concurrent user connections on a particular serverSetting switches that affect timing Controlling permissions from the command line Other performance-related switchesSetting a maximum Catalog page size Setting up a client/server environment Selecting communications protocolsLimiting inactive connections Starting a server in forced recovery mode Starting a server from DbisqlServer from Dbisql Connections, and Catalog page sizeMonitoring server activity Starting multiple servers or clients on the same machineCapture server activity in a log file Log file name has this formatMonitoring server activity Stopping the database server Stopping the database server PIDWho can stop the server? Shutting down operating system sessionsFollowing example stops a server from Dbisql Will be stopped even if there are connections to itStarting and stopping databases Starting and stopping databasesFrom a command line, type the following command Command that executes when you perform these steps isStarting the asiqdemo database Starting and stopping Sybase Central Starting and stopping Sybase CentralConnecting a plug-in Connecting to a plug-inIntroduction to connections Stopping Sybase CentralIntroduction to connections How connections are established If you want Consider readingConnection parameters specify connections Connection parameters are passed as connection stringsFollowing form This is equivalent to the following connection stringSimple connection examples Connecting to a database from Dbisql Simple connection examplesIf you need to start the sample database, enter To connect from a Unix systemTo connect from a Windows NT system Connecting to other databases from Dbisql PasswordEnter the user ID Connecting to an embedded database Start Dbisql by typing at a system command promptTo connect to a database from Dbisql on Unix Extra cache needed for Java To connect to an embedded database from Dbsql in Windows NTConnecting using a data source To connect using a data sourceConnecting to a server on a network Using default connection parameters Connecting from Adaptive Server IQ utilities Named server, using the default databaseYou may connect to the wrong server DatabaseWorking with Odbc data sources DSNs and FILEDSNs Working with Odbc data sourcesCreating and editing Odbc data sources Creating and editing Odbc data sources You could also use the host network address. For exampleTo create an Odbc User Data Source To test an Odbc Data SourceConfiguring Odbc data sources Odbc tabPrevent Driver Not Capable errors Login tab Database tabSelecting this Network tab Advanced tabEnter any additional switches here Creating a File Data SourceName of the file Which the debugging information is to be savedUsing Odbc data sources on Unix Using Odbc data sources on UnixConnection parameters Short Parameter Form Argument DescriptionConnection parameters Not supported in Odbc connections ParametersOr NO, OFF, 0, or False if false Connection parameters and their values are case insensitiveSpecify database files using DBF Specify database names using DBNConnection parameter priorities Use the Start parameter to specify cache sizeHow Adaptive Server IQ makes connections Steps in establishing a connectionLocating the interface library How Adaptive Server IQ makes connectionsUnix dblib6 with an operating system-specific extension Assembling a list of connection parameters Connection fileAutoStop Ignored if the database is already loaded ParameterIs already running Locating a server Ways by supplying network communication parametersHow Adaptive Server IQ makes connections Locating the database Server name caching for faster connections Connecting from other databases Interactive SQL connectionsUsing an integrated login Using an integrated loginUsing integrated logins Enabling the integrated login featureTo use an integrated login Creating an integrated login To map an integrated login from Sybase CentralTo map an integrate login using a SQL statement Connecting from a client application Following Dbisql statementCan connect to a database if all the following are true Security concerns unrestricted database access Setting temporary public options for added security Network aspects of integrated logins Creating a default integrated login userTroubleshooting startup, shutdown, and connections What to do if you can’t start Adaptive Server IQEnsure that your files are valid Debugging network communications startup problems Troubleshooting startup, shutdown, and connectionsEnsure that network communication software is running Check environment variablesWhat to do if you can’t connect to a database Resolving problems with your Dbisql window on Unix Stopping a database server in an emergency UnixRunning Adaptive Server IQ Troubleshooting startup, shutdown, and connections Building Your Adaptive Server IQ Databases Designing your databaseUsing Sybase Central to work with database objects Tools for working with database objectsBuilding Your Adaptive Server IQ Databases 100Step-by-step overview of database setup Using Dbisql to work with database objectsWorking with Database Objects 101To set up an IQ database Scheduling data definition tasks102 Extending data definition privileges 103Selecting a device type Allocating space for databases104 Estimating space and dbspaces required 105Working with databases Working with databases106 Creating a database 107108 109 110 Choosing an IQ page size 111Specifying the size of your database Choosing a Catalog page size112 Choosing a block size for your database Enabling Java in the databaseIQ Store size is 8MB and the Temporary Store is 4MB 113Adding dbspaces 114115 To create a dbspace in Sybase CentralDropping dbspaces 116======================================================= 117Working with tables Dropping a databaseCreating tables Working with tables119 Types of tables 120Table placement Type of table Permitted placement Default placementAutomatic index creation for IQ tables 121Optimizing storage and query performance Data type of the column and its widthSee the following table for implications of IQ Unique 122Altering tables 123Dropping tables 124To drop a table in Sybase Central Creating primary and foreign keys 125To create a primary key in Sybase Central 126 Working with views Table information in the system tables127 To create an unenforced foreign key in Sybase CentralCreating views Working with viewsDefinition in the database system tables 128Using views 129To create a view in Sybase Central Modifying views Permissions on viewsAn existing view definition Alter View statement maintains the permissions on the viewDeleting views Views in the system tables131 Working with indexes Introduction to indexesWorking with indexes 132Creating indexes Indexes in the system tables133 Removing indexes 134Overview of indexes Adaptive Server IQ index typesIndexing 135Overview of indexes 136Benefits over traditional indexes Adaptive Server IQ Indexes137 Creating Adaptive Server IQ indexes Create Index statementCreating Adaptive Server IQ indexes Region column of the salesorder tableCreating an index with Sybase Central Creating indexes concurrently139 To create an index with Sybase CentralChoosing an index type Choosing an index type140 Number of unique values in the index Types of queries141 142 Query type/indexType of Query Usage Recommended Index Type Only the default index supports the following data types Indexing criteria disk space usageData types in the index 143Adaptive Server IQ index types Combining index typesAdaptive Server IQ index types 144Default column index LowFast LF index typeProjections on few rows Recommended useHighGroup HG index type Advantages and disadvantages of LowFastComparison with other indexes Additional indexesAdvantages and disadvantages of HighGroup Automatic creation of HighGroup index147 HG advantages/disadvantages Advantages DisadvantagesHighNonGroup HNG index type Advantages and disadvantages of HighNonGroup148 Optimizing performance for ad hoc joins Comparison to other indexesHighGroup index is also appropriate for an HNG column 149Selecting an index 150Criteria to identify Index to select Using join indexes Adding column indexes after inserting dataJoin indexes improve query performance 151When a join becomes ad hoc How join indexes are used for queriesRelationships in join indexes Join hierarchy overviewColumns in the join index 153Join hierarchy in query resolution One-to-many relationship154 155 Custid LnameMultiple table joins and performance 156Steps in creating a join index 157Synchronizing join indexes Privileges needed to create a join index158 Defining join relationships between tables Performance hints for synchronization159 Using foreign references Examples of join relationships in table definitions160 Specifying the join type when creating a join index Specifying relationships when creating a join index161 Parameters of this command are Issuing the Create Join Index statement162 163 Creating a join index in Sybase Central Types of join hierarchies164 To add a join index in Sybase CentralLinear joins Star joins165 You can create this table using the following commands Another table166 Modifying tables included in a join index 167Inserting or deleting from tables in a join index 168Table versioning controls access to join indexes Estimating the size of a join index169 Estimating the size of a join index 170Import and export overview Import and export methodsDatabases 171Input and output data formats Import and export overview172 File Format DescriptionScheduling database updates Permissions for modifying dataSpecifying an output format for Interactive SQL Moving Data In and Out of DatabasesUsing output redirection Exporting data from a databaseExporting data from a database 174Bulk loading data using the Load Table statement Null value output175 Bulk loading data using the Load Table statement 176Format ’ascii’ ’binary’ Delimited by string Quotes on OFF Escapes on OFF Escape Character character177 Binary with Null Byte Prefix 1 2 4 ’delimiter-string’178 179 180 181 Block Factor number Block Size numberPreview on OFF ROW Delimited by ’delimiter-string’ Start ROW ID numberOn default is NATIVE. You can also specify Specifies the byte ordering during reads. ThisEndian platforms like DEC ALPHA, and Windows NT Here is a Windows NT exampleTable. The default is 0 for no limit Specifies the maximum number of rows to insert intoOptions work together 183184 Following Windows NT example sets the column delimiter for Carriage return \x0dWith the Limit option, and takes precedence over it 185186 Interpreting notification messages Memory messageThis message displays memory usage information 187Main IQ Store blocks messages IQ Temporary Store blocks messageMain buffer cache activity message 188Temporary buffer cache message Controlling message loggingThese lines display information about the Temp buffer cache See the description for the Main buffer cache message aboveUsing the Insert statement Using the Insert statementInserting specified values row by row 190Inserting selected rows from the database Complete description of Adaptive Server IQ data typesFollowing example adds 1995-06-09 into the lshipdate column Into the lorderkey column in the lineitem tableInserting from a different database Inserting selected rows from the database192 Issue a Commit to commit the insert Lineitem table in the current database193 To insert data directly from Adaptive Server Enterprise194 Importing data interactively Inserting into tables of a join index195 Inserting into primary and foreign key columns Inserting into primary and foreign key columns196 Partial-width insertions 197Partial-width insertion rules Partial-width insertions198 Query Additional columnsInstead of the correct number 199Start ROW ID option in the second Load Table statement 200Index 201Converting data on insertion Converting data on insertion202 203 Inserting data from pre-Version 12 Adaptive Server IQ Load conversion options204 Conversion options for loading from flat filesData conversions in IQ 205206 IQ conversions for comparison operations207 IQ conversions for arithmetic operationsColumn width issues Using the Ascii conversion option208 209 Date Option Substitution of Null or blank charactersSpecifying the Date Format 210211 Setting Year specified as Years assumed Datetime conversion option212 Specifying the format for Datetime conversions 213214 Formatting times10 Datetime format options Where Blanks indicates that blanks convert to Nulls Working With Nulls215 Other factors affecting the display of data Other factors affecting the display of data216 Matching Adaptive Server Enterprise data types Unsupported Adaptive Server Enterprise data types217 Adaptive Server Enterprise data type equivalents Matching Adaptive Server Enterprise data types218 219 Adaptive Server Adaptive Server IQ Enterprise Datatype13 Character data types Adaptive Server Enterprise Datatype IQ DatatypeHandling conversion errors on data import 22015 DATE/TIME data types Tuning bulk loading of data Improving load performance during database definitionOptimizing for the number of distinct values Creating indexesSetting server startup options Adjusting your environment at load timeTuning bulk loading of data Adding dbspacesReducing Main IQ Store space use in incremental loads 223Changing data using Update Changing data using Update224 Deleting data 225Importing data by replication Importing data by replication226 227 228 Overview of procedures Adaptive Server IQStatements, are also available in batches 229Benefits of procedures Introduction to proceduresBenefits of procedures 230Creating procedures Using Procedures and Batches231 Calling procedures Dropping proceduresIntroduction to procedures 232Permissions to execute procedures Returning procedure results in parameters233 Returning procedure results in result sets 234Employee ID Salary Introduction to user-defined functions Creating user-defined functions235 Calling user-defined functions Introduction to user-defined functions236 Fullname empfname, emplnameDropping user-defined functions Permissions to execute user-defined functionsFollowing statement revokes permission to use the function 237Introduction to batches Introduction to batches238 Control statements 239Dbisql and batches Using compound statements Control statements240 Control statement SyntaxDeclarations in compound statements 241Atomic compound statements 242Structure of procedures SQL statements allowed in procedures243 Declaring parameters for procedures Structure of procedures244 Passing parameters to procedures Passing parameters to functionsOr as follows 245Returning results from procedures Returning a value using the Return statementReturning results from procedures 246Returning results as procedure parameters 247Declared in the procedure argument list Show the number of orders placed by the customer with IDCustomerID parameter is declared as an in parameter. This Orders variable that is returned to the calling environmentReturning result sets from procedures 249Returning multiple result sets from procedures Returning variable result sets from proceduresProcedure 250Using cursors in procedures 251Using cursors in procedures Cursor management overviewCursor positioning 252Using cursors on Select statements in procedures 253254 Errors and warnings in procedures 255Default error handling in procedures Errors and warnings in procedures256 257 Error handling with on Exception Resume 258259 On Exception Resume BeginDefault handling of warnings in procedures 260Using exception handlers in procedures 261262 Nested compound statements and exception handlers 263Using the Execute Immediate statement in procedures Following statement executes the InnerProc procedure264 Transactions and savepoints in procedures Some tips for writing proceduresCheck if you need to change the command delimiter 265Remember to delimit statements within your procedure Use fully-qualified names for tables in proceduresSpecifying dates and times in procedures Some tips for writing proceduresVerifying procedure input arguments Statements allowed in batches267 Calling external libraries from procedures Using Select statements in batches268 Creating procedures and functions with external calls 269External function declarations Following form for Microsoft Visual C++270 How parameters are passed to the external function 271Special considerations when passing character types 272Data integrity overview How data can become invalid273 Integrity constraints belong in the database Data integrity overview274 How database contents get changed Data integrity toolsSQL statements for implementing integrity constraints 276Using table and column constraints Using Unique constraints on columns or tables277 Using IQ Unique constraint on columns Using Check conditions on columnsUsing table and column constraints 278Column Check conditions from user-defined data types Option279 Using Check conditions on tables Working with column constraints in Sybase CentralModifying and deleting Check conditions 280Declaring entity and referential integrity 281Enforcing entity integrity If a client application breaches entity integrity282 Primary keys enforce entity integrity Declaring referential integrity283 How you define foreign keys Referential integrity is unenforced284 Integrity rules in the system tables 285Integrity rules in the system tables 286Performance and other aspects of database administration Overview of transactions and versioningIntroduction to transactions Transactions are logical units of workUsing transactions Starting transactionsCompleting transactions Overview of transactions and versioningOptions in Dbisql Committing a transaction writes data to diskTransactions and Versioning 289Introduction to concurrency Subdividing transactionsHow concurrency works in IQ 290Concurrency for backups Why concurrency benefits youIntroduction to versioning Concurrency and IQ MultiplexTable-level versioning One writer and multiple readers at the table level292 293 Only one writer at a timeMultiple writers and readers in a database 294Transactions use committed data 295Timing of commits on read transactions affects versions 296Hold cursors span transactions 297How Adaptive Server IQ keeps track of versions Versioning of temporary tables298 Versioning prevents inconsistencies How locking worksLocks for DML operations 299Locks for DDL operations How locking works300 301 Isolation levels Primary keys and lockingIsolation levels 302Checkpoints, savepoints, and transaction rollback 303Checkpoints aid in recovery When checkpoints occurCheckpoints Checkpoints, savepoints, and transaction rollbackSavepoints within transactions Releasing savepoints305 Rolling back to a savepoint Automatic and user-defined savepointsNaming and nesting savepoints Following scenarioSystem recovery Rolling back transactionsWhat causes a rollback Effect of rollbackHow transaction information aids recovery 308Performance implications 309Overlapping versions and deletions Performance implications310 Cursors in transactions 311Cursors and versioning Cursor sensitivityCursor scrolling Cursors in transactionsCursor command syntax and examples Hold cursorsPositioned operations Controlling message logging for cursors314 Adaptive Server IQ international features Installation to handle international language issuesIntroduction to international languages and character sets 315Using the default collation Character set questions and answersIntroduction to international languages and character sets 316Understanding character sets in software Pieces in the character set puzzleInternational Languages and Character Sets 317Language issues in client/server computing 318Database server software messages Applications can cause Code pages in Windows and Windows NT 319Ansi and OEM code pages in Windows and Windows NT 320Multibyte character sets 321Sorting characters using collations International aspects of case sensitivityFirst-byte collation orderings for multibyte character sets 322Understanding locales Introduction to localesCase insensitivity of identifiers 323Understanding the locale language Understanding locales324 Understanding the locale character set 325Alternative Language label Label ISO639 language code Equivalent Iana labels and a description 326Character set Label Iana label Description 327 Setting the Sqllocale environment variable Understanding collationsUnderstanding the locale collation label Displaying collationsSupplied collations Following collations are supplied with Adaptive Server IQ329 Collation Label Type Description330 Ansi or OEM? 331332 333 334 335 Using multibyte collations Understanding character set translationCharacter translation for database messages Understanding character set translation337 To use character translation for database messagesConnection strings and character sets Avoiding character-set translation338 Collation internals 339Language Character set Comment lines Title lineCollation internals 340Specification Description Collation sequence sectionFollowing are some sample lines for a collation 341Encodings section That will be sorted togetherFollowing is part of the Shift-JIS collation file 342Properties section 343Finding the default collation Configuring your character set environmentTo configure your character set environment International language and character set tasksYou can determine locale information using system functions Determining locale information345 To determine the locale of a database serverSetting locales Creating a database with a named collationYour current machine 346List the supplied collation sequences 347Starting a database server using character set translation Using Odbc code page translationTo enable character-set translation on a database server Or on UnixCharacter set translation for Sybase Central and Dbisql Creating a custom collationSet the Dbisql option CHAROEMTranslation to a value of OFF 349350 Compatibility issues Creating a database with a custom collation351 To create a database with a custom collationPerformance issues Performance issues352 An overview of database permissions Manage user IDs353 DBA authority overview An overview of database permissions354 Resource authority overview Ownership permissions overviewTable and views permissions overview Managing User IDs and PermissionsManaging individual user IDs and permissions Group permissions overview356 Changing a password With the following commandCreating new users 357Granting DBA and resource authority 358Granting permissions on tables and views 359Granting users the right to grant permissions Context of groups in Permissions of groups360 To grant user permission on tables in Sybase CentralGranting permissions on procedures To grant user permissions on procedures in Sybase Central361 Revoking user permissions 362To create a group with a name and password Managing groupsCreating groups 363Granting group membership to users Managing groups364 To create a group in Sybase CentralPermissions of groups Referring to tables owned by groups365 Groups without passwords 366Database object names and prefixes Special groups367 Database object names and prefixes 368Using views and procedures for extra security Following command will now work369 Using views for tailored security Using views and procedures for extra security370 Using procedures for tailored security Grant permission for the Sales Manager to examine this view371 How user permissions are assessed Managing the resources connections useHow user permissions are assessed Be modified is strictly definedTemporary Store 373Users and permissions in the system tables Users and permissions in the system tablesLimits the number of prepared 374Default Contents Views Default Contents375 376 Backup protects your data 377Types of backups Data in backupsBacking up your database Backing up your databaseTransaction log in backup, restore, and recovery Backup and Data Recovery379 Distribution of backup data Disk backup requirementsTape backup requirements Selecting archive devicesPreparing for backup Obtaining DBA privilegesRewinding tapes 381Retaining old disk backups Two ways to run BackupYou can run Backup in two ways Estimating Media CapacityConcurrency and backups Backup statementTo back up an IQ database, use the following syntax 383Specifying the type of backup Specifying operator presenceSpecifying archive devices 384385 386 387 Backup Examples Other backup optionsWaiting for Tape Devices 388Recovery from errors during backup Checking for backup spaceRecovery attempts 389After you complete a backup Performing backups with non-Sybase productsDo not specify the Stacker or Size parameters 390Performing system-level backups Shutting down the database391 Restoring from a system-level backup Performing system-level backupsBacking up the right files 392Validating your database 393For this size database It takes about this long Interpreting results Validating your database394 Orphaned block statistic MeaningConcurrency issues for spiqcheckdb 395Before you restore Restoring your databasesRestoring your databases 396Restore accommodates dbspace changes 397Restoring disk backup files Restoring tape backup filesSpecifying files for an incremental restore Keeping the database unchanged between restoresRestore statement Restoring from a compatible backupTo restore a database, use the following syntax 399Moving database files 400401 402 Adjusting data sources and configuration files Restoring in the correct orderDisplaying header information 403Set, and the restore order, are as follows Set must be restored first, and must be in the first device404 Renaming the transaction log after you restore Dblog command-line utilitySwitch Description 405Validating the database after you restore Restore requires exclusive write accessTransaction log utility options Maintaining a transaction log or mirrorDisplaying header information 407Recovery from errors during restore Using Symbolic Links Unix Only408 Unattended backup 409Getting information about backups and restores Locating the backup logGetting information about backups and restores 410Content of the backup log 411Maintaining the backup log Viewing the backup log in Sybase CentralRecording dbspace names 412Determining your data backup and recovery strategy 413Scheduling routine backups Determining the type of backupDetermining your data backup and recovery strategy 414Designating Backup and Restore Responsibilities Improving performance for backup and restoreIncreasing the number of archive devices 415Spooling backup data Increasing memory used during backupEliminating data verification Balancing system loadControlling the size of the Catalog Store 417418 DBA can tune performance by adjusting resource usage Your hardware and software configurationIntroduction to performance terms Designing for performanceOverview of memory use Paging increases available memoryOverview of memory use 420Utilities to monitor swapping Server memoryManaging System Resources 421Managing buffer caches Determining the sizes of the buffer caches422 Operating system and other applications Adaptive Server IQ memory overhead423 Multi-user database access 424Raw partitions versus file systems Memory requirements for loadsAdaptive Server IQ main and temp buffer caches 425Buffer caches and physical memory Example of setting buffer cache sizes 426Other considerations Memory available for buffer caches ExampleSetting buffer cache sizes Methods of adjusting buffer cache sizes427 Setting buffer cache size database options 428To change the buffer cache sizes permanently Setting buffer cache size server switches Setting the page sizeNormally you change the buffer cache sizes by setting Specifying page sizeBlock size 430Decrease buffer cache settings Adjust blocking factor for loadsSaving memory Data compressionIQ command line option changes Actively using the databaseOptimizing for large numbers of users Use the following guideline to determine Block FactorCommand and set rlimfdmax=4096 in /etc/system IQ Temp spaceSystem parameters 433Platform-specific memory options 434Recommendations for small memory configurations Physical size of memory on the systemOptions that can provide more memory 435436 You must then restart the server with the following command Managing large buffer caches on HPControlling file system buffering 437Options for Java-enabled databases Other ways to get more memory438 Process threading model 439Insufficient threads error IQ options for managing thread usageProcess threading model 440Balancing I/O Raw I/O on Unix operating systems441 Using disk striping Setting up disk striping on UnixSetting up disk striping on Windows NT Balancing I/OInternal striping Recommendations for disk striping443 Disk striping option Disk striping hints444 Using multiple dbspaces Temporary data with the Create Dbspace commandTransaction log file Across multiple disksStrategic file locations Message log446 Working space for inserting, deleting, and synchronizing 447Options for tuning resource use Options for tuning resource useRestricting concurrent queries 448Limiting a query’s memory use Limiting queries by rows returnedForcing cursors to be non-scrolling 449Limiting the number of cursors Limiting the number of statementsLowering a connection’s priority Prefetching cache pagesRestricting database access Other ways to improve resource useOptimizing for typical usage Disk cachingUsing RAM disk Indexing tipsPicking the right index type Indexing tipsUsing join indexes Allowing enough disk space for deletions453 Managing database size and structure Managing the size of your databaseDenormalizing for performance Managing database size and structureDenormalization has risks Disadvantages of denormalizationPerformance benefits of denormalization 455Improving your queries Deciding to denormalizeTips for structuring queries Improving your queriesPlanning queries Query evaluation options457 Setting query optimization options 458Network performance Improving large data transfers459 Isolate heavy network users Network performance460 Put small amounts of data in small packets 461Put large amounts of data in large packets 462Process at the server level Filter as much data as possible at the server level463 464 Getting information using stored procedures Other chapters of this guide for more tuning hintsViewing the Adaptive Server IQ environment Set to control resource use, see , Managing SystemViewing the Adaptive Server IQ environment 466Monitoring and Tuning Performance Monitoring the buffer cachesStarting the buffer cache monitor 467Monitoring the buffer caches 468469 470 471 Stopping the buffer cache monitor Examining and saving monitor results472 Into dummytablename Stop MonitorExamples of monitor results Buffer cache473 474 -contentionresults for the main cache are -contentionresults for the temp cache areResults for the memory manager are 475Avoiding buffer manager thrashing Avoiding buffer manager thrashing476 Monitoring paging on Windows NT systems Monitoring paging on Unix systemsHere is an example 477478 System utilities to monitor CPU use 479System utilities to monitor CPU use 480Client/server interfaces to Adaptive Server IQ Data server for client applicationsRestrictions for creating and running these applications 481Client/server interfaces to Adaptive Server IQ 482Configuring IQ Servers with Dsedit Interfaces fileAdaptive Server IQ as a Data Server 483Using the Dsedit utility Opening a Directory Services sessionStarting Dsedit 484Adding a server entry Adding or changing the server addressInterfaces file sql.ini Select a server entry in the Server boxNetwork Settings, in the Control Panel 486Verifying the server address Renaming a server entry487 Sybase applications and Adaptive Server IQ Open Client applications and Adaptive Server IQDeleting server entries 488Setting up Adaptive Server IQ as an Open Server System requirementsStarting the database server as an Open Server 489Configuring your database for use with Open Client Setting up Adaptive Server IQ as an Open Server490 Characteristics of Open Client and jConnect connections 491Option Set to Characteristics of Open Client and jConnect connections To change the option settings for TDS connections492 Servers with multiple databases To connect to the livecredit server, use this syntax493 494 Index 495Index 496497 WINLATIN1498 Datetime499 Odbc Unix500 501 DDL502 Pipenotconnected503 504 See Also Dbisql505 506 507 Null508 TCP/IP509 See Also stored510 Rawdetect511 512 513 514 515 516