Manuals / Sybase / Computer Equipment / Server

Sybase 12.4.2 manual Interpreting notification messages, Memory message, 187

Models: 12.4.2

1 536

Download 536 pages 20.34 Kb

Page 207

Image 207

CHAPTER 5 Moving Data In and Out of Databases

Interpreting notification messages

By default, Adaptive Server IQ displays information about your database during insert and load operations.

The statistics in these messages indicate when you need to perform maintenance and optimization tasks, such as adding more dbspaces. The messages also report on the progress of the load. This section explains each notification message.

At the start of the insert is a description of the operation, such as this one:

In table ’partsupp’, the full width insert of 5 columns will begin at record 1.

1998-07-28 13:03:47 0002 Insert Started:

1998-07-28 13:03:47 0002 partsupp

1998-07-28 13:03:48 0002 [20270]:

=n*** File: /remote/rip/tpcd_data/scale_1/partsupp.tbl

Each time it inserts the number of records specified in the NOTIFY load option,

Adaptive Server IQ sends a message like this:

1998-07-28 13:03:49 0002 [20897]: 100000 Records, 2

Seconds

Mem: 469mb/M470

Main	Blks: U63137/6%, Buffers: U12578/L7
Temporary Blks: U273/0%, Buffers: U1987/L1960
Main	I: L331224/P22 O: D25967/P7805 C:D0

Temporary I: L25240/P8 O: D4749/P0 C:D0

The first line shows how many rows Adaptive Server IQ has read since the last notification message, and the number of seconds taken reading them. Even if Adaptive Server IQ reads the same number of messages each time, the amount of time will vary depending on the data read (for example, how many data conversions are required). Reported time intervals smaller than 1 second are usually reported as “0 Secs”.

Memory message

This message displays memory usage information:

Mem: 469mb/M470

187

Page 207

Image 207

Sybase 12.4.2 Interpreting notification messages, Memory message, This message displays memory usage information, 187

Contents

Adaptive Server IQ Administration and Performance GuideDocument ID Contents Contents 104 100101 103138 Contents Deleting views 131 Views in the system tables132 135Vii Viii 277 Using cursors on Select statements in procedures 253255 273309 Contents Referential integrity is unenforced 284285 299Managing User IDs and Permissions 323339 352Xii Xiii 467 448454 459481 473476 479Xvi How to use this book AudienceXvii Documentation for Adaptive Server IQ Guide to using this book To learn how to Read this chapterRelated documents Related documentsXix About This BookRelated documents Introduction to Adaptive Server IQ Overview of IQ system administrationSystem administration tasks System administration tasksOverview of Adaptive Server IQ System Administration Administrative tasksSecurity overview Granting permissions Tools for system administrationTools for system administration Types of usersCatalogs and IQ Database serverCatalog Store Adaptive Server Anywhere and Adaptive Server IQIQ Store Temporary StoreConcurrent operations Stored proceduresStored procedures Adaptive Server IQ stored proceduresStored Procedures for the IQ Store Procedure name Purpose Adaptive Server Enterprise system and catalog proceduresSystem procedure Description Adaptive Server Enterprise system proceduresProcedure name Purpose Catalog stored proceduresAdaptive Server Enterprise catalog procedures Catalog procedure DescriptionSystem tables and views System tables and viewsSystem table Description Sysdomain Syslogin Sysuserauth System view DescriptionFunctions Commands and FunctionsCommands and Functions Types of SQL statementsMessage 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 Running Adaptive Server IQ Starting the server on UnixUsing 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 Running the Unix database server as a daemon Using command-line switchesRunning the server as a Windows NT service Using command-line switchesCase sensitivity Using configuration files Elora 16M Tcpipport=2367 -gm Gp 4096 path\mydb.db Database started Naming the server and databasesRequired if you do not supply a database WindowSample database on that server Setting memory switches Controlling performance from the command lineConcurrent user connections on a particular server Default value of -iqgovernis 2 x the number of CPUs +10This switch as the number of megabytes of wired memory UseSetting switches that affect timing Other performance-related switches Controlling permissions from the command lineSetting a maximum Catalog page size Selecting communications protocols Setting up a client/server environmentLimiting inactive connections Connections, and Catalog page size Starting a server in forced recovery modeStarting a server from Dbisql Server from DbisqlLog file name has this format Monitoring server activityStarting multiple servers or clients on the same machine Capture server activity in a log fileMonitoring server activity Stopping the database server PID Stopping the database serverWill be stopped even if there are connections to it Who can stop the server?Shutting down operating system sessions Following example stops a server from DbisqlStarting 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 to a plug-in Connecting a plug-inIntroduction to connections Stopping Sybase CentralIntroduction to connections If you want Consider reading How connections are establishedThis is equivalent to the following connection string Connection parameters specify connectionsConnection parameters are passed as connection strings Following formSimple connection examples To connect from a Unix system Connecting to a database from DbisqlSimple connection examples If you need to start the sample database, enterTo 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 To connect to an embedded database from Dbsql in Windows NT Extra cache needed for JavaTo connect using a data source Connecting using a data sourceConnecting to a server on a network Using default connection parameters Database Connecting from Adaptive Server IQ utilitiesNamed server, using the default database You may connect to the wrong serverWorking with Odbc data sources Working with Odbc data sources DSNs and FILEDSNsCreating and editing Odbc data sources To test an Odbc Data Source Creating and editing Odbc data sourcesYou could also use the host network address. For example To create an Odbc User Data SourceOdbc tab Configuring Odbc data sourcesPrevent Driver Not Capable errors Login tab Database tabSelecting this Advanced tab Network tabWhich the debugging information is to be saved Enter any additional switches hereCreating a File Data Source Name of the fileUsing Odbc data sources on Unix Using Odbc data sources on UnixShort Parameter Form Argument Description Connection parametersConnection parameters Connection parameters and their values are case insensitive Not supported in Odbc connectionsParameters Or NO, OFF, 0, or False if falseUse the Start parameter to specify cache size Specify database files using DBFSpecify database names using DBN Connection parameter prioritiesSteps in establishing a connection How Adaptive Server IQ makes connectionsLocating the interface library How Adaptive Server IQ makes connectionsUnix dblib6 with an operating system-specific extension Parameter Assembling a list of connection parametersConnection file AutoStop Ignored if the database is already loadedIs already running Ways by supplying network communication parameters Locating a serverHow Adaptive Server IQ makes connections Locating the database Server name caching for faster connections Interactive SQL connections Connecting from other databasesUsing 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 Creating a default integrated login user Network aspects of integrated loginsTroubleshooting startup, shutdown, and connections What to do if you can’t start Adaptive Server IQEnsure that your files are valid Check environment variables Debugging network communications startup problemsTroubleshooting startup, shutdown, and connections Ensure that network communication software is runningWhat to do if you can’t connect to a database Stopping a database server in an emergency Unix Resolving problems with your Dbisql window on UnixRunning Adaptive Server IQ Troubleshooting startup, shutdown, and connections Designing your database Building Your Adaptive Server IQ Databases100 Using Sybase Central to work with database objectsTools for working with database objects Building Your Adaptive Server IQ Databases101 Step-by-step overview of database setupUsing Dbisql to work with database objects Working with Database ObjectsTo set up an IQ database Scheduling data definition tasks102 103 Extending data definition privilegesSelecting a device type Allocating space for databases104 105 Estimating space and dbspaces requiredWorking with databases Working with databases106 107 Creating a database108 109 110 111 Choosing an IQ page sizeSpecifying the size of your database Choosing a Catalog page size112 113 Choosing a block size for your databaseEnabling Java in the database IQ Store size is 8MB and the Temporary Store is 4MB114 Adding dbspacesTo create a dbspace in Sybase Central 115116 Dropping dbspaces117 =======================================================Working with tables Working with tablesDropping a database Creating tables119 120 Types of tables121 Table placementType of table Permitted placement Default placement Automatic index creation for IQ tables122 Optimizing storage and query performanceData type of the column and its width See the following table for implications of IQ Unique123 Altering tablesDropping tables 124To drop a table in Sybase Central Creating primary and foreign keys 125To create a primary key in Sybase Central 126 To create an unenforced foreign key in Sybase Central Working with viewsTable information in the system tables 127128 Creating viewsWorking with views Definition in the database system tablesUsing views 129To create a view in Sybase Central Alter View statement maintains the permissions on the view Modifying viewsPermissions on views An existing view definitionDeleting views Views in the system tables131 132 Working with indexesIntroduction to indexes Working with indexesCreating indexes Indexes in the system tables133 134 Removing indexes135 Overview of indexesAdaptive Server IQ index types Indexing136 Overview of indexesBenefits over traditional indexes Adaptive Server IQ Indexes137 Region column of the salesorder table Creating Adaptive Server IQ indexesCreate Index statement Creating Adaptive Server IQ indexesTo create an index with Sybase Central Creating an index with Sybase CentralCreating indexes concurrently 139Choosing 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 143 Only the default index supports the following data typesIndexing criteria disk space usage Data types in the index144 Adaptive Server IQ index typesCombining index types Adaptive Server IQ index typesRecommended use Default column indexLowFast LF index type Projections on few rowsAdditional indexes HighGroup HG index typeAdvantages and disadvantages of LowFast Comparison with other indexesHG advantages/disadvantages Advantages Disadvantages Advantages and disadvantages of HighGroupAutomatic creation of HighGroup index 147HighNonGroup HNG index type Advantages and disadvantages of HighNonGroup148 149 Optimizing performance for ad hoc joinsComparison to other indexes HighGroup index is also appropriate for an HNG columnSelecting an index 150Criteria to identify Index to select 151 Using join indexesAdding column indexes after inserting data Join indexes improve query performanceJoin hierarchy overview When a join becomes ad hocHow join indexes are used for queries Relationships in join indexes153 Columns in the join indexJoin hierarchy in query resolution One-to-many relationship154 Custid Lname 155156 Multiple table joins and performance157 Steps in creating a join indexSynchronizing 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 To add a join index in Sybase Central Creating a join index in Sybase CentralTypes of join hierarchies 164Linear joins Star joins165 You can create this table using the following commands Another table166 167 Modifying tables included in a join index168 Inserting or deleting from tables in a join indexTable versioning controls access to join indexes Estimating the size of a join index169 170 Estimating the size of a join index171 Import and export overviewImport and export methods DatabasesFile Format Description Input and output data formatsImport and export overview 172Moving Data In and Out of Databases Scheduling database updatesPermissions for modifying data Specifying an output format for Interactive SQL174 Using output redirectionExporting data from a database Exporting data from a databaseBulk loading data using the Load Table statement Null value output175 Quotes on OFF Escapes on OFF Escape Character character Bulk loading data using the Load Table statement176 Format ’ascii’ ’binary’ Delimited by stringBinary with Null Byte Prefix 1 2 4 ’delimiter-string’ 177178 179 180 Start ROW ID number 181Block Factor number Block Size number Preview on OFF ROW Delimited by ’delimiter-string’Here is a Windows NT example On default is NATIVE. You can also specifySpecifies the byte ordering during reads. This Endian platforms like DEC ALPHA, and Windows NT183 Table. The default is 0 for no limitSpecifies the maximum number of rows to insert into Options work together184 185 Following Windows NT example sets the column delimiter forCarriage return \x0d With the Limit option, and takes precedence over it186 187 Interpreting notification messagesMemory message This message displays memory usage information188 Main IQ Store blocks messagesIQ Temporary Store blocks message Main buffer cache activity messageSee the description for the Main buffer cache message above Temporary buffer cache messageControlling message logging These lines display information about the Temp buffer cache190 Using the Insert statementUsing the Insert statement Inserting specified values row by rowInto the lorderkey column in the lineitem table Inserting selected rows from the databaseComplete description of Adaptive Server IQ data types Following example adds 1995-06-09 into the lshipdate columnInserting from a different database Inserting selected rows from the database192 To insert data directly from Adaptive Server Enterprise Issue a Commit to commit the insertLineitem table in the current database 193194 Importing data interactively Inserting into tables of a join index195 Inserting into primary and foreign key columns Inserting into primary and foreign key columns196 197 Partial-width insertionsPartial-width insertion rules Partial-width insertions198 199 QueryAdditional columns Instead of the correct number200 Start ROW ID option in the second Load Table statement201 IndexConverting data on insertion Converting data on insertion202 203 Conversion options for loading from flat files Inserting data from pre-Version 12 Adaptive Server IQLoad conversion options 204205 Data conversions in IQIQ conversions for comparison operations 206IQ conversions for arithmetic operations 207Column width issues Using the Ascii conversion option208 209 210 Date OptionSubstitution of Null or blank characters Specifying the Date Format211 Setting Year specified as Years assumed Datetime conversion option212 213 Specifying the format for Datetime conversions214 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 Adaptive Server Enterprise Datatype IQ Datatype 219Adaptive Server Adaptive Server IQ Enterprise Datatype 13 Character data typesHandling conversion errors on data import 22015 DATE/TIME data types Creating indexes Tuning bulk loading of dataImproving load performance during database definition Optimizing for the number of distinct valuesAdding dbspaces Setting server startup optionsAdjusting your environment at load time Tuning bulk loading of data223 Reducing Main IQ Store space use in incremental loadsChanging data using Update Changing data using Update224 225 Deleting dataImporting data by replication Importing data by replication226 227 228 229 Overview of proceduresAdaptive Server IQ Statements, are also available in batches230 Benefits of proceduresIntroduction to procedures Benefits of proceduresCreating procedures Using Procedures and Batches231 232 Calling proceduresDropping procedures Introduction to proceduresPermissions 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 Fullname empfname, emplname Calling user-defined functionsIntroduction to user-defined functions 236237 Dropping user-defined functionsPermissions to execute user-defined functions Following statement revokes permission to use the functionIntroduction to batches Introduction to batches238 Control statements 239Dbisql and batches Control statement Syntax Using compound statementsControl statements 240241 Declarations in compound statements242 Atomic compound statementsStructure of procedures SQL statements allowed in procedures243 Declaring parameters for procedures Structure of procedures244 245 Passing parameters to proceduresPassing parameters to functions Or as follows246 Returning results from proceduresReturning a value using the Return statement Returning results from procedures247 Returning results as procedure parametersOrders variable that is returned to the calling environment Declared in the procedure argument listShow the number of orders placed by the customer with ID CustomerID parameter is declared as an in parameter. This249 Returning result sets from procedures250 Returning multiple result sets from proceduresReturning variable result sets from procedures Procedure251 Using cursors in procedures252 Using cursors in proceduresCursor management overview Cursor positioning253 Using cursors on Select statements in procedures254 255 Errors and warnings in proceduresDefault error handling in procedures Errors and warnings in procedures256 257 258 Error handling with on Exception ResumeOn Exception Resume Begin 259260 Default handling of warnings in procedures261 Using exception handlers in procedures262 263 Nested compound statements and exception handlersUsing the Execute Immediate statement in procedures Following statement executes the InnerProc procedure264 265 Transactions and savepoints in proceduresSome tips for writing procedures Check if you need to change the command delimiterSome tips for writing procedures Remember to delimit statements within your procedureUse fully-qualified names for tables in procedures Specifying dates and times in proceduresVerifying procedure input arguments Statements allowed in batches267 Calling external libraries from procedures Using Select statements in batches268 269 Creating procedures and functions with external callsExternal function declarations Following form for Microsoft Visual C++270 271 How parameters are passed to the external function272 Special considerations when passing character typesData integrity overview How data can become invalid273 Integrity constraints belong in the database Data integrity overview274 Data integrity tools How database contents get changed276 SQL statements for implementing integrity constraintsUsing table and column constraints Using Unique constraints on columns or tables277 278 Using IQ Unique constraint on columnsUsing Check conditions on columns Using table and column constraintsColumn Check conditions from user-defined data types Option279 280 Using Check conditions on tablesWorking with column constraints in Sybase Central Modifying and deleting Check conditions281 Declaring entity and referential integrityEnforcing 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 285 Integrity rules in the system tables286 Integrity rules in the system tablesTransactions are logical units of work Performance and other aspects of database administrationOverview of transactions and versioning Introduction to transactionsOverview of transactions and versioning Using transactionsStarting transactions Completing transactions289 Options in DbisqlCommitting a transaction writes data to disk Transactions and Versioning290 Introduction to concurrencySubdividing transactions How concurrency works in IQConcurrency and IQ Multiplex Concurrency for backupsWhy concurrency benefits you Introduction to versioningTable-level versioning One writer and multiple readers at the table level292 Only one writer at a time 293294 Multiple writers and readers in a database295 Transactions use committed data296 Timing of commits on read transactions affects versions297 Hold cursors span transactionsHow Adaptive Server IQ keeps track of versions Versioning of temporary tables298 299 Versioning prevents inconsistenciesHow locking works Locks for DML operationsLocks for DDL operations How locking works300 301 302 Isolation levelsPrimary keys and locking Isolation levels303 Checkpoints, savepoints, and transaction rollbackCheckpoints, savepoints, and transaction rollback Checkpoints aid in recoveryWhen checkpoints occur CheckpointsSavepoints within transactions Releasing savepoints305 Following scenario Rolling back to a savepointAutomatic and user-defined savepoints Naming and nesting savepointsEffect of rollback System recoveryRolling back transactions What causes a rollback308 How transaction information aids recovery309 Performance implicationsOverlapping versions and deletions Performance implications310 311 Cursors in transactionsCursors in transactions Cursors and versioningCursor sensitivity Cursor scrollingControlling message logging for cursors Cursor command syntax and examplesHold cursors Positioned operations314 315 Adaptive Server IQ international featuresInstallation to handle international language issues Introduction to international languages and character sets316 Using the default collationCharacter set questions and answers Introduction to international languages and character sets317 Understanding character sets in softwarePieces in the character set puzzle International Languages and Character SetsLanguage issues in client/server computing 318Database server software messages Applications can cause 319 Code pages in Windows and Windows NT320 Ansi and OEM code pages in Windows and Windows NT321 Multibyte character sets322 Sorting characters using collationsInternational aspects of case sensitivity First-byte collation orderings for multibyte character sets323 Understanding localesIntroduction to locales Case insensitivity of identifiersUnderstanding 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 Displaying collations Setting the Sqllocale environment variableUnderstanding collations Understanding the locale collation labelCollation Label Type Description Supplied collationsFollowing collations are supplied with Adaptive Server IQ 329330 331 Ansi or OEM?332 333 334 335 Understanding character set translation Using multibyte collationsUnderstanding character set translation Character translation for database messagesTo use character translation for database messages 337Connection strings and character sets Avoiding character-set translation338 Collation internals 339Language Character set 340 Comment linesTitle line Collation internals341 Specification DescriptionCollation sequence section Following are some sample lines for a collation342 Encodings sectionThat will be sorted together Following is part of the Shift-JIS collation file343 Properties sectionInternational language and character set tasks Finding the default collationConfiguring your character set environment To configure your character set environmentTo determine the locale of a database server You can determine locale information using system functionsDetermining locale information 345346 Setting localesCreating a database with a named collation Your current machine347 List the supplied collation sequencesOr on Unix Starting a database server using character set translationUsing Odbc code page translation To enable character-set translation on a database server349 Character set translation for Sybase Central and DbisqlCreating a custom collation Set the Dbisql option CHAROEMTranslation to a value of OFF350 To create a database with a custom collation Compatibility issuesCreating a database with a custom collation 351Performance issues Performance issues352 An overview of database permissions Manage user IDs353 DBA authority overview An overview of database permissions354 Managing User IDs and Permissions Resource authority overviewOwnership permissions overview Table and views permissions overviewManaging individual user IDs and permissions Group permissions overview356 357 Changing a passwordWith the following command Creating new users358 Granting DBA and resource authority359 Granting permissions on tables and viewsTo grant user permission on tables in Sybase Central Granting users the right to grant permissionsContext of groups in Permissions of groups 360Granting permissions on procedures To grant user permissions on procedures in Sybase Central361 362 Revoking user permissions363 To create a group with a name and passwordManaging groups Creating groupsTo create a group in Sybase Central Granting group membership to usersManaging groups 364Permissions of groups Referring to tables owned by groups365 366 Groups without passwordsDatabase object names and prefixes Special groups367 368 Database object names and prefixesUsing 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 Be modified is strictly defined How user permissions are assessedManaging the resources connections use How user permissions are assessed373 Temporary Store374 Users and permissions in the system tablesUsers and permissions in the system tables Limits the number of preparedDefault Contents Views Default Contents375 376 377 Backup protects your dataBacking up your database Types of backupsData in backups Backing up your databaseTransaction log in backup, restore, and recovery Backup and Data Recovery379 Selecting archive devices Distribution of backup dataDisk backup requirements Tape backup requirements381 Preparing for backupObtaining DBA privileges Rewinding tapesEstimating Media Capacity Retaining old disk backupsTwo ways to run Backup You can run Backup in two ways383 Concurrency and backupsBackup statement To back up an IQ database, use the following syntax384 Specifying the type of backupSpecifying operator presence Specifying archive devices385 386 387 388 Backup ExamplesOther backup options Waiting for Tape Devices389 Recovery from errors during backupChecking for backup space Recovery attempts390 After you complete a backupPerforming backups with non-Sybase products Do not specify the Stacker or Size parametersPerforming system-level backups Shutting down the database391 392 Restoring from a system-level backupPerforming system-level backups Backing up the right filesValidating your database 393For this size database It takes about this long Orphaned block statistic Meaning Interpreting resultsValidating your database 394395 Concurrency issues for spiqcheckdb396 Before you restoreRestoring your databases Restoring your databases397 Restore accommodates dbspace changesKeeping the database unchanged between restores Restoring disk backup filesRestoring tape backup files Specifying files for an incremental restore399 Restore statementRestoring from a compatible backup To restore a database, use the following syntax400 Moving database files401 402 403 Adjusting data sources and configuration filesRestoring in the correct order Displaying header informationSet, and the restore order, are as follows Set must be restored first, and must be in the first device404 405 Renaming the transaction log after you restoreDblog command-line utility Switch DescriptionMaintaining a transaction log or mirror Validating the database after you restoreRestore requires exclusive write access Transaction log utility options407 Displaying header informationRecovery from errors during restore Using Symbolic Links Unix Only408 409 Unattended backup410 Getting information about backups and restoresLocating the backup log Getting information about backups and restores411 Content of the backup log412 Maintaining the backup logViewing the backup log in Sybase Central Recording dbspace names413 Determining your data backup and recovery strategy414 Scheduling routine backupsDetermining the type of backup Determining your data backup and recovery strategy415 Designating Backup and Restore ResponsibilitiesImproving performance for backup and restore Increasing the number of archive devicesBalancing system load Spooling backup dataIncreasing memory used during backup Eliminating data verification417 Controlling the size of the Catalog Store418 Designing for performance DBA can tune performance by adjusting resource usageYour hardware and software configuration Introduction to performance terms420 Overview of memory usePaging increases available memory Overview of memory use421 Utilities to monitor swappingServer memory Managing System ResourcesManaging buffer caches Determining the sizes of the buffer caches422 Operating system and other applications Adaptive Server IQ memory overhead423 Memory requirements for loads Multi-user database access424 Raw partitions versus file systemsAdaptive Server IQ main and temp buffer caches 425Buffer caches and physical memory Memory available for buffer caches Example Example of setting buffer cache sizes426 Other considerationsSetting buffer cache sizes Methods of adjusting buffer cache sizes427 Setting buffer cache size database options 428To change the buffer cache sizes permanently Specifying page size Setting buffer cache size server switchesSetting the page size Normally you change the buffer cache sizes by setting430 Block sizeData compression Decrease buffer cache settingsAdjust blocking factor for loads Saving memoryUse the following guideline to determine Block Factor IQ command line option changesActively using the database Optimizing for large numbers of users433 Command and set rlimfdmax=4096 in /etc/systemIQ Temp space System parameters434 Platform-specific memory options435 Recommendations for small memory configurationsPhysical size of memory on the system Options that can provide more memory436 437 You must then restart the server with the following commandManaging large buffer caches on HP Controlling file system bufferingOptions for Java-enabled databases Other ways to get more memory438 439 Process threading model440 Insufficient threads errorIQ options for managing thread usage Process threading modelBalancing I/O Raw I/O on Unix operating systems441 Balancing I/O Using disk stripingSetting up disk striping on Unix Setting up disk striping on Windows NTInternal striping Recommendations for disk striping443 Disk striping option Disk striping hints444 Across multiple disks Using multiple dbspacesTemporary data with the Create Dbspace command Transaction log fileStrategic file locations Message log446 447 Working space for inserting, deleting, and synchronizing448 Options for tuning resource useOptions for tuning resource use Restricting concurrent queries449 Limiting a query’s memory useLimiting queries by rows returned Forcing cursors to be non-scrollingPrefetching cache pages Limiting the number of cursorsLimiting the number of statements Lowering a connection’s priorityDisk caching Restricting database accessOther ways to improve resource use Optimizing for typical usageIndexing tips Using RAM diskIndexing tips Picking the right index typeUsing join indexes Allowing enough disk space for deletions453 Managing database size and structure Managing database size and structureManaging the size of your database Denormalizing for performance455 Denormalization has risksDisadvantages of denormalization Performance benefits of denormalizationImproving your queries Improving your queriesDeciding to denormalize Tips for structuring queriesPlanning queries Query evaluation options457 458 Setting query optimization optionsNetwork performance Improving large data transfers459 Isolate heavy network users Network performance460 461 Put small amounts of data in small packets462 Put large amounts of data in large packetsProcess at the server level Filter as much data as possible at the server level463 464 Set to control resource use, see , Managing System Getting information using stored proceduresOther chapters of this guide for more tuning hints Viewing the Adaptive Server IQ environment466 Viewing the Adaptive Server IQ environment467 Monitoring and Tuning PerformanceMonitoring the buffer caches Starting the buffer cache monitor468 Monitoring the buffer caches469 470 471 Into dummytablename Stop Monitor Stopping the buffer cache monitorExamining and saving monitor results 472Examples of monitor results Buffer cache473 474 475 -contentionresults for the main cache are-contentionresults for the temp cache are Results for the memory manager areAvoiding buffer manager thrashing Avoiding buffer manager thrashing476 477 Monitoring paging on Windows NT systemsMonitoring paging on Unix systems Here is an example478 479 System utilities to monitor CPU use480 System utilities to monitor CPU use481 Client/server interfaces to Adaptive Server IQData server for client applications Restrictions for creating and running these applications482 Client/server interfaces to Adaptive Server IQ483 Configuring IQ Servers with DseditInterfaces file Adaptive Server IQ as a Data Server484 Using the Dsedit utilityOpening a Directory Services session Starting DseditSelect a server entry in the Server box Adding a server entryAdding or changing the server address Interfaces file sql.ini486 Network Settings, in the Control PanelVerifying the server address Renaming a server entry487 488 Sybase applications and Adaptive Server IQOpen Client applications and Adaptive Server IQ Deleting server entries489 Setting up Adaptive Server IQ as an Open ServerSystem requirements Starting the database server as an Open ServerConfiguring 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 495 Index496 IndexWINLATIN1 497Datetime 498Odbc Unix 499500 DDL 501Pipenotconnected 502503 See Also Dbisql 504505 506 Null 507TCP/IP 508See Also stored 509Rawdetect 510511 512 513 514 515 516