Manuals / Sybase / Computer Equipment / Server

Sybase 12.4.2 manual 502, Pipenotconnected

Models: 12.4.2

1 536

Download 536 pages 20.34 Kb

Page 522

Image 522

Index

ODBC configuration 69 EncryptedPassword connection parameter

about	73
encryption
network packets			70
Encryption connection parameter
about	73
ENG connection parameter
about	73
EngineName connection parameter
about	73
ENP connection parameter
about	73
entity integrity
about	276
enforcing		281
environment variables
SQLCONNECT			62
error handling
ON EXCEPTION RESUME 258
error messages
character set translation				336
PIPE_NOT_CONNECTED 179
redirecting to files			175
errors
data conversion			220
insertions and deletions				300
procedures		255
transaction processing				300
ESCAPE CHARACTER option
LOAD TABLE option				181
euro symbol
1252LATIN1 collation				332, 333
exception handlers
procedures		261
exceptions
declaring		257
EXECUTE IMMEDIATE statement
procedures		264
exporting data
about	174
overview		171
extended characters
about	319
external procedures
about	268

failures media 377 system 377

FETCH statement procedures 252

file data source name See FileDSN

FileDataSourceName connection parameter

See FileDSN
FileDSN
connection parameter	63, 73
creating 71
distributing 64
See Also data sources	64

FileDSN (file data source name) See FileDSN

files

locating for best performance 446 redirecting output to 174

FILLER option	201
FIXED format	172

fixed width character sets about 321

flat files

load conversion options 204 loading from 175

FLOAT_AS_DOUBLE option

Open Client		491
follow bytes
about	321
FOR statement
syntax	239
foreign keys
creating	126
inserting data		196
optional	284
referential integrity			284
retrieving information			11
unenforced		126
FoxPro format		172
FROM clause
join indexes		152
UPDATE statement			225
functions
external	268

502

Page 522

Image 522

Sybase 12.4.2 manual 502, Pipenotconnected

Contents

Administration and Performance Guide Adaptive Server IQDocument ID Contents Contents 103 100101 104135 Contents Deleting views 131 Views in the system tables132 138Vii Viii 273 Using cursors on Select statements in procedures 253255 277299 Contents Referential integrity is unenforced 284285 309352 323339 Managing User IDs and PermissionsXii Xiii 459 448454 467479 473476 481Xvi How to use this book AudienceXvii Related documents Guide to using this book To learn how to Read this chapterRelated 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 Types of users Tools for system administrationTools for system administration Granting permissionsDatabase server Catalogs and IQTemporary Store Adaptive Server Anywhere and Adaptive Server IQIQ 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 procedure Description Catalog stored proceduresAdaptive Server Enterprise catalog procedures Procedure name PurposeSystem tables and views System tables and viewsSystem table Description Sysdomain Syslogin System view Description SysuserauthTypes of SQL statements Commands and FunctionsCommands and Functions 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 Using command-line switchesRunning the server as a Windows NT service Running the Unix database server as a daemonCase sensitivity Using configuration files Elora 16M Tcpipport=2367 -gm Gp 4096 path\mydb.db Window Naming the server and databasesRequired if you do not supply a database Database startedSample database on that server Controlling performance from the command line Setting memory switchesUse Default value of -iqgovernis 2 x the number of CPUs +10This switch as the number of megabytes of wired memory 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 Server from Dbisql Starting a server in forced recovery modeStarting a server from Dbisql Connections, and Catalog page sizeCapture server activity in a log file Monitoring server activityStarting multiple servers or clients on the same machine Log file name has this formatMonitoring server activity Stopping the database server Stopping the database server PIDFollowing example stops a server from Dbisql Who can stop the server?Shutting down operating system sessions 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 readingFollowing form Connection parameters specify connectionsConnection parameters are passed as connection strings This is equivalent to the following connection stringSimple connection examples If you need to start the sample database, enter Connecting to a database from DbisqlSimple connection examples 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 You may connect to the wrong server Connecting from Adaptive Server IQ utilitiesNamed server, using the default database DatabaseWorking with Odbc data sources DSNs and FILEDSNs Working with Odbc data sourcesCreating and editing Odbc data sources To create an Odbc User Data Source Creating and editing Odbc data sourcesYou could also use the host network address. For example To test an Odbc Data SourceConfiguring Odbc data sources Odbc tabPrevent Driver Not Capable errors Login tab Database tabSelecting this Network tab Advanced tabName of the file Enter any additional switches hereCreating a File Data Source 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 Or NO, OFF, 0, or False if false Not supported in Odbc connectionsParameters Connection parameters and their values are case insensitiveConnection parameter priorities Specify database files using DBFSpecify database names using DBN 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 AutoStop Ignored if the database is already loaded Assembling a list of connection parametersConnection file 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 Ensure that network communication software is running Debugging network communications startup problemsTroubleshooting startup, shutdown, and connections 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 databaseBuilding Your Adaptive Server IQ Databases Using Sybase Central to work with database objectsTools for working with database objects 100Working with Database Objects Step-by-step overview of database setupUsing Dbisql to work 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 IQ Store size is 8MB and the Temporary Store is 4MB Choosing a block size for your databaseEnabling Java in the database 113Adding dbspaces 114115 To create a dbspace in Sybase CentralDropping dbspaces 116======================================================= 117Creating tables Working with tablesDropping a database Working with tables119 Types of tables 120Automatic index creation for IQ tables Table placementType of table Permitted placement Default placement 121See the following table for implications of IQ Unique Optimizing storage and query performanceData type of the column and its width 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 127 Working with viewsTable information in the system tables To create an unenforced foreign key in Sybase CentralDefinition in the database system tables Creating viewsWorking with views 128Using views 129To create a view in Sybase Central An existing view definition Modifying viewsPermissions on views Alter View statement maintains the permissions on the viewDeleting views Views in the system tables131 Working with indexes Working with indexesIntroduction to indexes 132Creating indexes Indexes in the system tables133 Removing indexes 134Indexing Overview of indexesAdaptive Server IQ index types 135Overview of indexes 136Benefits over traditional indexes Adaptive Server IQ Indexes137 Creating Adaptive Server IQ indexes Creating Adaptive Server IQ indexesCreate Index statement Region column of the salesorder table139 Creating an index with Sybase CentralCreating indexes concurrently 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 Data types in the index Only the default index supports the following data typesIndexing criteria disk space usage 143Adaptive Server IQ index types Adaptive Server IQ index typesCombining index types 144Projections on few rows Default column indexLowFast LF index type Recommended useComparison with other indexes HighGroup HG index typeAdvantages and disadvantages of LowFast Additional indexes147 Advantages and disadvantages of HighGroupAutomatic creation of HighGroup index HG advantages/disadvantages Advantages DisadvantagesHighNonGroup HNG index type Advantages and disadvantages of HighNonGroup148 HighGroup index is also appropriate for an HNG column Optimizing performance for ad hoc joinsComparison to other indexes 149Selecting an index 150Criteria to identify Index to select Join indexes improve query performance Using join indexesAdding column indexes after inserting data 151Relationships in join indexes When a join becomes ad hocHow join indexes are used for queries 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 164 Creating a join index in Sybase CentralTypes of join hierarchies 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 170Databases Import and export overviewImport and export methods 171172 Input and output data formatsImport and export overview File Format DescriptionSpecifying an output format for Interactive SQL Scheduling database updatesPermissions for modifying data Moving Data In and Out of DatabasesExporting data from a database Using output redirectionExporting data from a database 174Bulk loading data using the Load Table statement Null value output175 Format ’ascii’ ’binary’ Delimited by string Bulk loading data using the Load Table statement176 Quotes on OFF Escapes on OFF Escape Character character177 Binary with Null Byte Prefix 1 2 4 ’delimiter-string’178 179 180 Preview on OFF ROW Delimited by ’delimiter-string’ 181Block Factor number Block Size number Start ROW ID numberEndian platforms like DEC ALPHA, and Windows NT On default is NATIVE. You can also specifySpecifies the byte ordering during reads. This Here is a Windows NT exampleOptions work together Table. The default is 0 for no limitSpecifies the maximum number of rows to insert into 183184 With the Limit option, and takes precedence over it Following Windows NT example sets the column delimiter forCarriage return \x0d 185186 This message displays memory usage information Interpreting notification messagesMemory message 187Main buffer cache activity message Main IQ Store blocks messagesIQ Temporary Store blocks message 188These lines display information about the Temp buffer cache Temporary buffer cache messageControlling message logging See the description for the Main buffer cache message aboveInserting specified values row by row Using the Insert statementUsing the Insert statement 190Following example adds 1995-06-09 into the lshipdate column Inserting selected rows from the databaseComplete description of Adaptive Server IQ data types Into the lorderkey column in the lineitem tableInserting from a different database Inserting selected rows from the database192 193 Issue a Commit to commit the insertLineitem table in the current database 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 Instead of the correct number QueryAdditional columns 199Start ROW ID option in the second Load Table statement 200Index 201Converting data on insertion Converting data on insertion202 203 204 Inserting data from pre-Version 12 Adaptive Server IQLoad conversion options 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 Specifying the Date Format Date OptionSubstitution of Null or blank characters 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 13 Character data types 219Adaptive Server Adaptive Server IQ Enterprise Datatype Adaptive Server Enterprise Datatype IQ DatatypeHandling conversion errors on data import 22015 DATE/TIME data types Optimizing for the number of distinct values Tuning bulk loading of dataImproving load performance during database definition Creating indexesTuning bulk loading of data Setting server startup optionsAdjusting your environment at load time 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 Statements, are also available in batches Overview of proceduresAdaptive Server IQ 229Benefits of procedures Benefits of proceduresIntroduction to procedures 230Creating procedures Using Procedures and Batches231 Introduction to procedures Calling proceduresDropping 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 236 Calling user-defined functionsIntroduction to user-defined functions Fullname empfname, emplnameFollowing statement revokes permission to use the function Dropping user-defined functionsPermissions to execute user-defined functions 237Introduction to batches Introduction to batches238 Control statements 239Dbisql and batches 240 Using compound statementsControl statements Control statement SyntaxDeclarations in compound statements 241Atomic compound statements 242Structure of procedures SQL statements allowed in procedures243 Declaring parameters for procedures Structure of procedures244 Or as follows Passing parameters to proceduresPassing parameters to functions 245Returning results from procedures Returning results from proceduresReturning a value using the Return statement 246Returning results as procedure parameters 247CustomerID parameter is declared as an in parameter. This Declared in the procedure argument listShow the number of orders placed by the customer with ID Orders variable that is returned to the calling environmentReturning result sets from procedures 249Procedure Returning multiple result sets from proceduresReturning variable result sets from procedures 250Using cursors in procedures 251Cursor positioning Using cursors in proceduresCursor management overview 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 Check if you need to change the command delimiter Transactions and savepoints in proceduresSome tips for writing procedures 265Specifying dates and times in procedures Remember to delimit statements within your procedureUse fully-qualified names for tables 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 table and column constraints Using IQ Unique constraint on columnsUsing Check conditions on columns 278Column Check conditions from user-defined data types Option279 Modifying and deleting Check conditions Using Check conditions on tablesWorking with column constraints in Sybase Central 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 286Introduction to transactions Performance and other aspects of database administrationOverview of transactions and versioning Transactions are logical units of workCompleting transactions Using transactionsStarting transactions Overview of transactions and versioningTransactions and Versioning Options in DbisqlCommitting a transaction writes data to disk 289How concurrency works in IQ Introduction to concurrencySubdividing transactions 290Introduction to versioning Concurrency for backupsWhy concurrency benefits you 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 Locks for DML operations Versioning prevents inconsistenciesHow locking works 299Locks for DDL operations How locking works300 301 Isolation levels Isolation levelsPrimary keys and locking 302Checkpoints, savepoints, and transaction rollback 303Checkpoints Checkpoints aid in recoveryWhen checkpoints occur Checkpoints, savepoints, and transaction rollbackSavepoints within transactions Releasing savepoints305 Naming and nesting savepoints Rolling back to a savepointAutomatic and user-defined savepoints Following scenarioWhat causes a rollback System recoveryRolling back transactions Effect of rollbackHow transaction information aids recovery 308Performance implications 309Overlapping versions and deletions Performance implications310 Cursors in transactions 311Cursor scrolling Cursors and versioningCursor sensitivity Cursors in transactionsPositioned operations Cursor command syntax and examplesHold cursors Controlling message logging for cursors314 Introduction to international languages and character sets Adaptive Server IQ international featuresInstallation to handle international language issues 315Introduction to international languages and character sets Using the default collationCharacter set questions and answers 316International Languages and Character Sets Understanding character sets in softwarePieces in the character set puzzle 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 321First-byte collation orderings for multibyte character sets Sorting characters using collationsInternational aspects of case sensitivity 322Case insensitivity of identifiers Understanding localesIntroduction to locales 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 Understanding the locale collation label Setting the Sqllocale environment variableUnderstanding collations Displaying collations329 Supplied collationsFollowing collations are supplied with Adaptive Server IQ Collation Label Type Description330 Ansi or OEM? 331332 333 334 335 Character translation for database messages Using multibyte collationsUnderstanding character set translation Understanding character set translation337 To use character translation for database messagesConnection strings and character sets Avoiding character-set translation338 Collation internals 339Language Character set Collation internals Comment linesTitle line 340Following are some sample lines for a collation Specification DescriptionCollation sequence section 341Following is part of the Shift-JIS collation file Encodings sectionThat will be sorted together 342Properties section 343To configure your character set environment Finding the default collationConfiguring your character set environment International language and character set tasks345 You can determine locale information using system functionsDetermining locale information To determine the locale of a database serverYour current machine Setting localesCreating a database with a named collation 346List the supplied collation sequences 347To enable character-set translation on a database server Starting a database server using character set translationUsing Odbc code page translation Or on UnixSet the Dbisql option CHAROEMTranslation to a value of OFF Character set translation for Sybase Central and DbisqlCreating a custom collation 349350 351 Compatibility issuesCreating a database with a custom collation 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 Table and views permissions overview Resource authority overviewOwnership permissions overview Managing User IDs and PermissionsManaging individual user IDs and permissions Group permissions overview356 Creating new users Changing a passwordWith the following command 357Granting DBA and resource authority 358Granting permissions on tables and views 359360 Granting users the right to grant permissionsContext of groups in Permissions of groups To grant user permission on tables in Sybase CentralGranting permissions on procedures To grant user permissions on procedures in Sybase Central361 Revoking user permissions 362Creating groups To create a group with a name and passwordManaging groups 363364 Granting group membership to usersManaging groups 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 How user permissions are assessedManaging the resources connections use Be modified is strictly definedTemporary Store 373Limits the number of prepared Users and permissions in the system tablesUsers and permissions in the system tables 374Default Contents Views Default Contents375 376 Backup protects your data 377Backing up your database Types of backupsData in backups Backing up your databaseTransaction log in backup, restore, and recovery Backup and Data Recovery379 Tape backup requirements Distribution of backup dataDisk backup requirements Selecting archive devicesRewinding tapes Preparing for backupObtaining DBA privileges 381You can run Backup in two ways Retaining old disk backupsTwo ways to run Backup Estimating Media CapacityTo back up an IQ database, use the following syntax Concurrency and backupsBackup statement 383Specifying archive devices Specifying the type of backupSpecifying operator presence 384385 386 387 Waiting for Tape Devices Backup ExamplesOther backup options 388Recovery attempts Recovery from errors during backupChecking for backup space 389Do not specify the Stacker or Size parameters After you complete a backupPerforming backups with non-Sybase products 390Performing system-level backups Shutting down the database391 Backing up the right files Restoring from a system-level backupPerforming system-level backups 392Validating your database 393For this size database It takes about this long 394 Interpreting resultsValidating your database Orphaned block statistic MeaningConcurrency issues for spiqcheckdb 395Restoring your databases Before you restoreRestoring your databases 396Restore accommodates dbspace changes 397Specifying files for an incremental restore Restoring disk backup filesRestoring tape backup files Keeping the database unchanged between restoresTo restore a database, use the following syntax Restore statementRestoring from a compatible backup 399Moving database files 400401 402 Displaying header information Adjusting data sources and configuration filesRestoring in the correct order 403Set, and the restore order, are as follows Set must be restored first, and must be in the first device404 Switch Description Renaming the transaction log after you restoreDblog command-line utility 405Transaction log utility options Validating the database after you restoreRestore requires exclusive write access 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 Getting information about backups and restoresLocating the backup log 410Content of the backup log 411Recording dbspace names Maintaining the backup logViewing the backup log in Sybase Central 412Determining your data backup and recovery strategy 413Determining your data backup and recovery strategy Scheduling routine backupsDetermining the type of backup 414Increasing the number of archive devices Designating Backup and Restore ResponsibilitiesImproving performance for backup and restore 415Eliminating data verification Spooling backup dataIncreasing memory used during backup Balancing system loadControlling the size of the Catalog Store 417418 Introduction to performance terms DBA can tune performance by adjusting resource usageYour hardware and software configuration Designing for performanceOverview of memory use Overview of memory usePaging increases available memory 420Managing System Resources Utilities to monitor swappingServer memory 421Managing buffer caches Determining the sizes of the buffer caches422 Operating system and other applications Adaptive Server IQ memory overhead423 Raw partitions versus file systems Multi-user database access424 Memory requirements for loadsAdaptive Server IQ main and temp buffer caches 425Buffer caches and physical memory Other considerations Example of setting buffer cache sizes426 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 Normally you change the buffer cache sizes by setting Setting buffer cache size server switchesSetting the page size Specifying page sizeBlock size 430Saving memory Decrease buffer cache settingsAdjust blocking factor for loads Data compressionOptimizing for large numbers of users IQ command line option changesActively using the database Use the following guideline to determine Block FactorSystem parameters Command and set rlimfdmax=4096 in /etc/systemIQ Temp space 433Platform-specific memory options 434Options that can provide more memory Recommendations for small memory configurationsPhysical size of memory on the system 435436 Controlling file system buffering You must then restart the server with the following commandManaging large buffer caches on HP 437Options for Java-enabled databases Other ways to get more memory438 Process threading model 439Process threading model Insufficient threads errorIQ options for managing thread usage 440Balancing I/O Raw I/O on Unix operating systems441 Setting up disk striping on Windows NT Using disk stripingSetting up disk striping on Unix Balancing I/OInternal striping Recommendations for disk striping443 Disk striping option Disk striping hints444 Transaction log file Using multiple dbspacesTemporary data with the Create Dbspace command Across multiple disksStrategic file locations Message log446 Working space for inserting, deleting, and synchronizing 447Restricting concurrent queries Options for tuning resource useOptions for tuning resource use 448Forcing cursors to be non-scrolling Limiting a query’s memory useLimiting queries by rows returned 449Lowering a connection’s priority Limiting the number of cursorsLimiting the number of statements Prefetching cache pagesOptimizing for typical usage Restricting database accessOther ways to improve resource use Disk cachingPicking the right index type Using RAM diskIndexing tips Indexing tipsUsing join indexes Allowing enough disk space for deletions453 Denormalizing for performance Managing database size and structureManaging the size of your database Managing database size and structurePerformance benefits of denormalization Denormalization has risksDisadvantages of denormalization 455Tips for structuring queries Improving your queriesDeciding to denormalize 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 Viewing the Adaptive Server IQ environment Getting information using stored proceduresOther chapters of this guide for more tuning hints Set to control resource use, see , Managing SystemViewing the Adaptive Server IQ environment 466Starting the buffer cache monitor Monitoring and Tuning PerformanceMonitoring the buffer caches 467Monitoring the buffer caches 468469 470 471 472 Stopping the buffer cache monitorExamining and saving monitor results Into dummytablename Stop MonitorExamples of monitor results Buffer cache473 474 Results for the memory manager are -contentionresults for the main cache are-contentionresults for the temp cache are 475Avoiding buffer manager thrashing Avoiding buffer manager thrashing476 Here is an example Monitoring paging on Windows NT systemsMonitoring paging on Unix systems 477478 System utilities to monitor CPU use 479System utilities to monitor CPU use 480Restrictions for creating and running these applications Client/server interfaces to Adaptive Server IQData server for client applications 481Client/server interfaces to Adaptive Server IQ 482Adaptive Server IQ as a Data Server Configuring IQ Servers with DseditInterfaces file 483Starting Dsedit Using the Dsedit utilityOpening a Directory Services session 484Interfaces file sql.ini Adding a server entryAdding or changing the server address Select a server entry in the Server boxNetwork Settings, in the Control Panel 486Verifying the server address Renaming a server entry487 Deleting server entries Sybase applications and Adaptive Server IQOpen Client applications and Adaptive Server IQ 488Starting the database server as an Open Server Setting up Adaptive Server IQ as an Open ServerSystem requirements 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

Index

PIPE_NOT_CONNECTED 179

502