Manuals / Sybase / Computer Equipment / Server

Sybase 12.4.2 manual =======================================================, 117

Models: 12.4.2

1 536

Download 536 pages 20.34 Kb

Page 137

Image 137

CHAPTER 3 Working with Database Objects

NameValue

=======================================================

Version:	12.4.2/(32bit mode)/Sun_svr4/OS 5.6/EBF 0000
Time Now:		2000-03-14 12:05:54.288
Build Time:		Sat Mar 11, 2000 21:39:55 EST
File Format:		23 on 03/18/1999
Catalog Format:		2
Stored Procedure Revision:		1
Page Size:		131072/8192blksz/16bpp
Number of DB Spaces:		8
Number of Temp Spaces:		2
DB Blocks: 1-12132344		IQ_SYSTEM_MAIN
DB Blocks: 12545280-24677623		mydb_2
DB Blocks: 25090560-37222903		mydb_3
DB Blocks: 37635840-49768183		mydb_4
DB Blocks: 50181120-62313463		mydb_5
DB Blocks: 62726400-74858743		mydb_6
DB Blocks: 75271680-87404023		mydb_7
DB Blocks: 87816960-99949303		mydb_8
Temp Blocks: 1-8823288		IQ_SYSTEM_TEMP
Temp Blocks: 9408960-18232247		mydb_tmp2
Create Time:		1999-12-30 19:10:55.231
Update Time:		2000-03-14 09:52:13.609
Main IQ Buffers:		11174, 1400Mb
Temporary IQ Buffers:		15165, 1900Mb
Main IQ Blocks Used:		43515029 of 97058752, 44%=331Gb, Max
Block#: 95065709
Temporary IQ Blocks Used:		610 of 17646576, 0%=4Mb, Max Block#: 0
Main Reserved Blocks Available:		1280 of 1280, 100%=10Mb
Temporary Reserved Blocks Available:		1280 of 1280, 100%=10Mb
Memory:		Current: 3351mb, Max: 3384mb
Main IQ Buffers:		Used: 11172, Locked: 0
Temporary IQ Buffers:		Used: 38, Locked: 0
Main IQ I/O:		I: L88043944/P495510 O:
C760342/D761393/P736587 D:24753 C:65.9
Temporary IQ I/O:		I: L16515025/P1222153 O:
C2609951/D3838862/P1228941
D:2609913	C:46.3
Old Versions:		1 = 59Gb
Active Txn Versions:		0 = C:0Mb/D:0Mb

117

Page 137

Image 137

Sybase 12.4.2 manual =======================================================, 117

Contents

Adaptive Server IQ Administration and Performance GuideDocument ID Contents Contents 101 100103 104132 Contents Deleting views 131 Views in the system tables135 138Vii Viii 255 Using cursors on Select statements in procedures 253273 277285 Contents Referential integrity is unenforced 284299 309339 323352 Managing User IDs and PermissionsXii Xiii 454 448459 467476 473479 481Xvi Xvii How to use this bookAudience Related documents Guide to using this book To learn how to Read this chapterRelated documents Documentation for Adaptive Server IQXix About This BookRelated documents Introduction to Adaptive Server IQ Overview of IQ system administrationSystem administration tasks System administration tasksSecurity overview Overview of Adaptive Server IQ System AdministrationAdministrative tasks Tools for system administration Tools for system administrationTypes of users Granting permissionsCatalogs and IQ Database serverIQ Store Adaptive Server Anywhere and Adaptive Server IQTemporary Store Catalog 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 proceduresAdaptive Server Enterprise catalog procedures Catalog stored proceduresCatalog procedure Description Procedure name PurposeSystem table Description System tables and viewsSystem tables and views Sysdomain Syslogin Sysuserauth System view DescriptionCommands and Functions Commands and FunctionsTypes of SQL statements FunctionsFourth line of the file contains version information Message loggingManipulate date and time data for example, TODAY, Datediff Utility database Utility databaseCompatibility with earlier versions Compatibility with earlier versions Starting the database server General form for the server command line is as follows Server command linesServer command lines Running Adaptive Server IQ Starting the server on UnixStarting the server on Unix Using the startup utilityTo start the server on Unix using the startup utility Typing the server startup command Starting the server on Windows NT Starting the server on Windows NTStarting the server from the NT Start menu Running the server outside the current session Running the server as a Windows NT service Using command-line switchesUsing 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 Required if you do not supply a database Naming the server and databasesWindow Database startedSample database on that server Setting memory switches Controlling performance from the command lineThis switch as the number of megabytes of wired memory Default value of -iqgovernis 2 x the number of CPUs +10Use Concurrent user connections on a particular serverSetting 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 Starting a server from Dbisql Starting a server in forced recovery modeServer from Dbisql Connections, and Catalog page sizeStarting multiple servers or clients on the same machine Monitoring server activityCapture server activity in a log file Log file name has this formatMonitoring server activity Stopping the database server PID Stopping the database serverShutting down operating system sessions Who can stop the server?Following example stops a server from Dbisql Will be stopped even if there are connections to itStarting and stopping databases Starting and stopping databasesStarting the asiqdemo database From a command line, type the following commandCommand that executes when you perform these steps is Starting and stopping Sybase Central Starting and stopping Sybase CentralConnecting to a plug-in Connecting a plug-inIntroduction to connections Introduction to connectionsStopping Sybase Central If you want Consider reading How connections are establishedConnection parameters are passed as connection strings Connection parameters specify connectionsFollowing form This is equivalent to the following connection stringSimple connection examples Simple connection examples Connecting to a database from DbisqlIf you need to start the sample database, enter To connect from a Unix systemTo connect from a Windows NT system Enter the user ID Connecting to other databases from DbisqlPassword To connect to a database from Dbisql on Unix Connecting to an embedded databaseStart Dbisql by typing at a system command prompt 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 Named server, using the default database Connecting from Adaptive Server IQ utilitiesYou may connect to the wrong server DatabaseWorking with Odbc data sources Working with Odbc data sources DSNs and FILEDSNsCreating and editing Odbc data sources You could also use the host network address. For example Creating and editing Odbc data sourcesTo create an Odbc User Data Source To test an Odbc Data SourceOdbc tab Configuring Odbc data sourcesPrevent Driver Not Capable errors Selecting this Login tabDatabase tab Advanced tab Network tabCreating a File Data Source Enter any additional switches hereName of the file Which the debugging information is to be savedUsing Odbc data sources on Unix Using Odbc data sources on UnixShort Parameter Form Argument Description Connection parametersConnection parameters Parameters Not supported in Odbc connectionsOr NO, OFF, 0, or False if false Connection parameters and their values are case insensitiveSpecify database names using DBN Specify database files using DBFConnection parameter priorities Use the Start parameter to specify cache sizeSteps in establishing a connection How Adaptive Server IQ makes connectionsUnix dblib6 with an operating system-specific extension Locating the interface libraryHow Adaptive Server IQ makes connections Connection file Assembling a list of connection parametersAutoStop Ignored if the database is already loaded ParameterIs 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 loginTo use an integrated login Using integrated loginsEnabling the integrated login feature To map an integrate login using a SQL statement Creating an integrated loginTo map an integrated login from Sybase Central Can connect to a database if all the following are true Connecting from a client applicationFollowing Dbisql statement Security concerns unrestricted database access Setting temporary public options for added security Creating a default integrated login user Network aspects of integrated loginsEnsure that your files are valid Troubleshooting startup, shutdown, and connectionsWhat to do if you can’t start Adaptive Server IQ Troubleshooting startup, shutdown, and connections Debugging network communications startup problemsEnsure that network communication software is running Check environment variablesWhat 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 DatabasesTools for working with database objects Using Sybase Central to work with database objectsBuilding Your Adaptive Server IQ Databases 100Using Dbisql to work with database objects Step-by-step overview of database setupWorking with Database Objects 101102 To set up an IQ databaseScheduling data definition tasks 103 Extending data definition privileges104 Selecting a device typeAllocating space for databases 105 Estimating space and dbspaces required106 Working with databasesWorking with databases 107 Creating a database108 109 110 111 Choosing an IQ page size112 Specifying the size of your databaseChoosing a Catalog page size Enabling Java in the database Choosing a block size for your databaseIQ Store size is 8MB and the Temporary Store is 4MB 113114 Adding dbspacesTo create a dbspace in Sybase Central 115116 Dropping dbspaces117 =======================================================Dropping a database Working with tablesCreating tables Working with tables119 120 Types of tablesType of table Permitted placement Default placement Table placementAutomatic index creation for IQ tables 121Data type of the column and its width Optimizing storage and query performanceSee the following table for implications of IQ Unique 122123 Altering tablesTo drop a table in Sybase Central Dropping tables124 To create a primary key in Sybase Central Creating primary and foreign keys125 126 Table information in the system tables Working with views127 To create an unenforced foreign key in Sybase CentralWorking with views Creating viewsDefinition in the database system tables 128To create a view in Sybase Central Using views129 Permissions on views Modifying viewsAn existing view definition Alter View statement maintains the permissions on the view131 Deleting viewsViews in the system tables Introduction to indexes Working with indexesWorking with indexes 132133 Creating indexesIndexes in the system tables 134 Removing indexesAdaptive Server IQ index types Overview of indexesIndexing 135136 Overview of indexes137 Benefits over traditional indexesAdaptive Server IQ Indexes Create Index statement Creating Adaptive Server IQ indexesCreating Adaptive Server IQ indexes Region column of the salesorder tableCreating indexes concurrently Creating an index with Sybase Central139 To create an index with Sybase Central140 Choosing an index typeChoosing an index type 141 Number of unique values in the indexTypes of queries Type of Query Usage Recommended Index Type 142Query type/index Indexing criteria disk space usage Only the default index supports the following data typesData types in the index 143Combining index types Adaptive Server IQ index typesAdaptive Server IQ index types 144LowFast LF index type Default column indexProjections on few rows Recommended useAdvantages and disadvantages of LowFast HighGroup HG index typeComparison with other indexes Additional indexesAutomatic creation of HighGroup index Advantages and disadvantages of HighGroup147 HG advantages/disadvantages Advantages Disadvantages148 HighNonGroup HNG index typeAdvantages and disadvantages of HighNonGroup Comparison to other indexes Optimizing performance for ad hoc joinsHighGroup index is also appropriate for an HNG column 149Criteria to identify Index to select Selecting an index150 Adding column indexes after inserting data Using join indexesJoin indexes improve query performance 151How join indexes are used for queries When a join becomes ad hocRelationships in join indexes Join hierarchy overview153 Columns in the join index154 Join hierarchy in query resolutionOne-to-many relationship Custid Lname 155156 Multiple table joins and performance157 Steps in creating a join index158 Synchronizing join indexesPrivileges needed to create a join index 159 Defining join relationships between tablesPerformance hints for synchronization 160 Using foreign referencesExamples of join relationships in table definitions 161 Specifying the join type when creating a join indexSpecifying relationships when creating a join index 162 Parameters of this command areIssuing the Create Join Index statement 163 Types of join hierarchies Creating a join index in Sybase Central164 To add a join index in Sybase Central165 Linear joinsStar joins 166 You can create this table using the following commandsAnother table 167 Modifying tables included in a join index168 Inserting or deleting from tables in a join index169 Table versioning controls access to join indexesEstimating the size of a join index 170 Estimating the size of a join indexImport and export methods Import and export overviewDatabases 171Import and export overview Input and output data formats172 File Format DescriptionPermissions for modifying data Scheduling database updatesSpecifying an output format for Interactive SQL Moving Data In and Out of DatabasesExporting data from a database Using output redirectionExporting data from a database 174175 Bulk loading data using the Load Table statementNull value output 176 Bulk loading data using the Load Table statementFormat ’ascii’ ’binary’ Delimited by string Quotes on OFF Escapes on OFF Escape Character characterBinary with Null Byte Prefix 1 2 4 ’delimiter-string’ 177178 179 180 Block Factor number Block Size number 181Preview on OFF ROW Delimited by ’delimiter-string’ Start ROW ID numberSpecifies the byte ordering during reads. This On default is NATIVE. You can also specifyEndian platforms like DEC ALPHA, and Windows NT Here is a Windows NT exampleSpecifies the maximum number of rows to insert into Table. The default is 0 for no limitOptions work together 183184 Carriage return \x0d Following Windows NT example sets the column delimiter forWith the Limit option, and takes precedence over it 185186 Memory message Interpreting notification messagesThis message displays memory usage information 187IQ Temporary Store blocks message Main IQ Store blocks messagesMain buffer cache activity message 188Controlling message logging Temporary buffer cache messageThese 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 190Complete description of Adaptive Server IQ data types Inserting selected rows from the databaseFollowing example adds 1995-06-09 into the lshipdate column Into the lorderkey column in the lineitem table192 Inserting from a different databaseInserting selected rows from the database Lineitem table in the current database Issue a Commit to commit the insert193 To insert data directly from Adaptive Server Enterprise194 195 Importing data interactivelyInserting into tables of a join index 196 Inserting into primary and foreign key columnsInserting into primary and foreign key columns 197 Partial-width insertions198 Partial-width insertion rulesPartial-width insertions Additional columns QueryInstead of the correct number 199200 Start ROW ID option in the second Load Table statement201 Index202 Converting data on insertionConverting data on insertion 203 Load conversion options Inserting data from pre-Version 12 Adaptive Server IQ204 Conversion options for loading from flat files205 Data conversions in IQIQ conversions for comparison operations 206IQ conversions for arithmetic operations 207208 Column width issuesUsing the Ascii conversion option 209 Substitution of Null or blank characters Date OptionSpecifying the Date Format 210211 212 Setting Year specified as Years assumedDatetime conversion option 213 Specifying the format for Datetime conversions10 Datetime format options 214Formatting times 215 Where Blanks indicates that blanks convert to NullsWorking With Nulls 216 Other factors affecting the display of dataOther factors affecting the display of data 217 Matching Adaptive Server Enterprise data typesUnsupported Adaptive Server Enterprise data types 218 Adaptive Server Enterprise data type equivalentsMatching Adaptive Server Enterprise data types Adaptive Server Adaptive Server IQ Enterprise Datatype 21913 Character data types Adaptive Server Enterprise Datatype IQ Datatype15 DATE/TIME data types Handling conversion errors on data import220 Improving load performance during database definition Tuning bulk loading of dataOptimizing for the number of distinct values Creating indexesAdjusting your environment at load time Setting server startup optionsTuning bulk loading of data Adding dbspaces223 Reducing Main IQ Store space use in incremental loads224 Changing data using UpdateChanging data using Update 225 Deleting data226 Importing data by replicationImporting data by replication 227 228 Adaptive Server IQ Overview of proceduresStatements, are also available in batches 229Introduction to procedures Benefits of proceduresBenefits of procedures 230231 Creating proceduresUsing Procedures and Batches Dropping procedures Calling proceduresIntroduction to procedures 232233 Permissions to execute proceduresReturning procedure results in parameters Employee ID Salary Returning procedure results in result sets234 235 Introduction to user-defined functionsCreating user-defined functions Introduction to user-defined functions Calling user-defined functions236 Fullname empfname, emplnamePermissions to execute user-defined functions Dropping user-defined functionsFollowing statement revokes permission to use the function 237238 Introduction to batchesIntroduction to batches Dbisql and batches Control statements239 Control statements Using compound statements240 Control statement Syntax241 Declarations in compound statements242 Atomic compound statements243 Structure of proceduresSQL statements allowed in procedures 244 Declaring parameters for proceduresStructure of procedures Passing parameters to functions Passing parameters to proceduresOr as follows 245Returning a value using the Return statement Returning results from proceduresReturning results from procedures 246247 Returning results as procedure parametersShow the number of orders placed by the customer with ID Declared in the procedure argument listCustomerID parameter is declared as an in parameter. This Orders variable that is returned to the calling environment249 Returning result sets from proceduresReturning variable result sets from procedures Returning multiple result sets from proceduresProcedure 250251 Using cursors in proceduresCursor management overview Using cursors in proceduresCursor positioning 252253 Using cursors on Select statements in procedures254 255 Errors and warnings in procedures256 Default error handling in proceduresErrors and warnings in procedures 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 handlers264 Using the Execute Immediate statement in proceduresFollowing statement executes the InnerProc procedure Some tips for writing procedures Transactions and savepoints in proceduresCheck if you need to change the command delimiter 265Use fully-qualified names for tables in procedures Remember to delimit statements within your procedureSpecifying dates and times in procedures Some tips for writing procedures267 Verifying procedure input argumentsStatements allowed in batches 268 Calling external libraries from proceduresUsing Select statements in batches 269 Creating procedures and functions with external calls270 External function declarationsFollowing form for Microsoft Visual C++ 271 How parameters are passed to the external function272 Special considerations when passing character types273 Data integrity overviewHow data can become invalid 274 Integrity constraints belong in the databaseData integrity overview Data integrity tools How database contents get changed276 SQL statements for implementing integrity constraints277 Using table and column constraintsUsing Unique constraints on columns or tables Using Check conditions on columns Using IQ Unique constraint on columnsUsing table and column constraints 278279 Column Check conditions from user-defined data typesOption Working with column constraints in Sybase Central Using Check conditions on tablesModifying and deleting Check conditions 280281 Declaring entity and referential integrity282 Enforcing entity integrityIf a client application breaches entity integrity 283 Primary keys enforce entity integrityDeclaring referential integrity 284 How you define foreign keysReferential integrity is unenforced 285 Integrity rules in the system tables286 Integrity rules in the system tablesOverview of transactions and versioning Performance and other aspects of database administrationIntroduction to transactions Transactions are logical units of workStarting transactions Using transactionsCompleting transactions Overview of transactions and versioningCommitting a transaction writes data to disk Options in DbisqlTransactions and Versioning 289Subdividing transactions Introduction to concurrencyHow concurrency works in IQ 290Why concurrency benefits you Concurrency for backupsIntroduction to versioning Concurrency and IQ Multiplex292 Table-level versioningOne writer and multiple readers at the table level 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 transactions298 How Adaptive Server IQ keeps track of versionsVersioning of temporary tables How locking works Versioning prevents inconsistenciesLocks for DML operations 299300 Locks for DDL operationsHow locking works 301 Primary keys and locking Isolation levelsIsolation levels 302303 Checkpoints, savepoints, and transaction rollbackWhen checkpoints occur Checkpoints aid in recoveryCheckpoints Checkpoints, savepoints, and transaction rollback305 Savepoints within transactionsReleasing savepoints Automatic and user-defined savepoints Rolling back to a savepointNaming and nesting savepoints Following scenarioRolling back transactions System recoveryWhat causes a rollback Effect of rollback308 How transaction information aids recovery309 Performance implications310 Overlapping versions and deletionsPerformance implications 311 Cursors in transactionsCursor sensitivity Cursors and versioningCursor scrolling Cursors in transactionsHold cursors Cursor command syntax and examplesPositioned operations Controlling message logging for cursors314 Installation to handle international language issues Adaptive Server IQ international featuresIntroduction to international languages and character sets 315Character set questions and answers Using the default collationIntroduction to international languages and character sets 316Pieces in the character set puzzle Understanding character sets in softwareInternational Languages and Character Sets 317Database server software messages Applications can cause Language issues in client/server computing318 319 Code pages in Windows and Windows NT320 Ansi and OEM code pages in Windows and Windows NT321 Multibyte character setsInternational aspects of case sensitivity Sorting characters using collationsFirst-byte collation orderings for multibyte character sets 322Introduction to locales Understanding localesCase insensitivity of identifiers 323324 Understanding the locale languageUnderstanding locales Alternative Language label Label ISO639 language code Understanding the locale character set325 Character set Label Iana label Description Equivalent Iana labels and a description326 327 Understanding collations Setting the Sqllocale environment variableUnderstanding the locale collation label Displaying collationsFollowing collations are supplied with Adaptive Server IQ Supplied collations329 Collation Label Type Description330 331 Ansi or OEM?332 333 334 335 Understanding character set translation Using multibyte collationsCharacter translation for database messages Understanding character set translationTo use character translation for database messages 337338 Connection strings and character setsAvoiding character-set translation Language Character set Collation internals339 Title line Comment linesCollation internals 340Collation sequence section Specification DescriptionFollowing are some sample lines for a collation 341That will be sorted together Encodings sectionFollowing is part of the Shift-JIS collation file 342343 Properties sectionConfiguring your character set environment Finding the default collationTo configure your character set environment International language and character set tasksDetermining locale information You can determine locale information using system functions345 To determine the locale of a database serverCreating a database with a named collation Setting localesYour current machine 346347 List the supplied collation sequencesUsing Odbc code page translation Starting a database server using character set translationTo enable character-set translation on a database server Or on UnixCreating a custom collation Character set translation for Sybase Central and DbisqlSet the Dbisql option CHAROEMTranslation to a value of OFF 349350 Creating a database with a custom collation Compatibility issues351 To create a database with a custom collation352 Performance issuesPerformance issues 353 An overview of database permissionsManage user IDs 354 DBA authority overviewAn overview of database permissions Ownership permissions overview Resource authority overviewTable and views permissions overview Managing User IDs and Permissions356 Managing individual user IDs and permissionsGroup permissions overview With the following command Changing a passwordCreating new users 357358 Granting DBA and resource authority359 Granting permissions on tables and viewsContext of groups in Permissions of groups Granting users the right to grant permissions360 To grant user permission on tables in Sybase Central361 Granting permissions on proceduresTo grant user permissions on procedures in Sybase Central 362 Revoking user permissionsManaging groups To create a group with a name and passwordCreating groups 363Managing groups Granting group membership to users364 To create a group in Sybase Central365 Permissions of groupsReferring to tables owned by groups 366 Groups without passwords367 Database object names and prefixesSpecial groups 368 Database object names and prefixes369 Using views and procedures for extra securityFollowing command will now work 370 Using views for tailored securityUsing views and procedures for extra security 371 Using procedures for tailored securityGrant permission for the Sales Manager to examine this view Managing the resources connections use How user permissions are assessedHow user permissions are assessed Be modified is strictly defined373 Temporary StoreUsers and permissions in the system tables Users and permissions in the system tablesLimits the number of prepared 374375 Default ContentsViews Default Contents 376 377 Backup protects your dataData in backups Types of backupsBacking up your database Backing up your database379 Transaction log in backup, restore, and recoveryBackup and Data Recovery Disk backup requirements Distribution of backup dataTape backup requirements Selecting archive devicesObtaining DBA privileges Preparing for backupRewinding tapes 381Two ways to run Backup Retaining old disk backupsYou can run Backup in two ways Estimating Media CapacityBackup statement Concurrency and backupsTo back up an IQ database, use the following syntax 383Specifying operator presence Specifying the type of backupSpecifying archive devices 384385 386 387 Other backup options Backup ExamplesWaiting for Tape Devices 388Checking for backup space Recovery from errors during backupRecovery attempts 389Performing backups with non-Sybase products After you complete a backupDo not specify the Stacker or Size parameters 390391 Performing system-level backupsShutting down the database Performing system-level backups Restoring from a system-level backupBacking up the right files 392For this size database It takes about this long Validating your database393 Validating your database Interpreting results394 Orphaned block statistic Meaning395 Concurrency issues for spiqcheckdbRestoring your databases Before you restoreRestoring your databases 396397 Restore accommodates dbspace changesRestoring tape backup files Restoring disk backup filesSpecifying files for an incremental restore Keeping the database unchanged between restoresRestoring from a compatible backup Restore statementTo restore a database, use the following syntax 399400 Moving database files401 402 Restoring in the correct order Adjusting data sources and configuration filesDisplaying header information 403404 Set, and the restore order, are as followsSet must be restored first, and must be in the first device Dblog command-line utility Renaming the transaction log after you restoreSwitch Description 405Restore requires exclusive write access Validating the database after you restoreTransaction log utility options Maintaining a transaction log or mirror407 Displaying header information408 Recovery from errors during restoreUsing Symbolic Links Unix Only 409 Unattended backupLocating the backup log Getting information about backups and restoresGetting information about backups and restores 410411 Content of the backup logViewing the backup log in Sybase Central Maintaining the backup logRecording dbspace names 412413 Determining your data backup and recovery strategyDetermining the type of backup Scheduling routine backupsDetermining your data backup and recovery strategy 414Improving performance for backup and restore Designating Backup and Restore ResponsibilitiesIncreasing the number of archive devices 415Increasing memory used during backup Spooling backup dataEliminating data verification Balancing system load417 Controlling the size of the Catalog Store418 Your hardware and software configuration DBA can tune performance by adjusting resource usageIntroduction to performance terms Designing for performancePaging increases available memory Overview of memory useOverview of memory use 420Server memory Utilities to monitor swappingManaging System Resources 421422 Managing buffer cachesDetermining the sizes of the buffer caches 423 Operating system and other applicationsAdaptive Server IQ memory overhead 424 Multi-user database accessRaw partitions versus file systems Memory requirements for loadsBuffer caches and physical memory Adaptive Server IQ main and temp buffer caches425 426 Example of setting buffer cache sizesOther considerations Memory available for buffer caches Example427 Setting buffer cache sizesMethods of adjusting buffer cache sizes To change the buffer cache sizes permanently Setting buffer cache size database options428 Setting the page size Setting buffer cache size server switchesNormally you change the buffer cache sizes by setting Specifying page size430 Block sizeAdjust blocking factor for loads Decrease buffer cache settingsSaving memory Data compressionActively using the database IQ command line option changesOptimizing for large numbers of users Use the following guideline to determine Block FactorIQ Temp space Command and set rlimfdmax=4096 in /etc/systemSystem parameters 433434 Platform-specific memory optionsPhysical size of memory on the system Recommendations for small memory configurationsOptions that can provide more memory 435436 Managing large buffer caches on HP You must then restart the server with the following commandControlling file system buffering 437438 Options for Java-enabled databasesOther ways to get more memory 439 Process threading modelIQ options for managing thread usage Insufficient threads errorProcess threading model 440441 Balancing I/ORaw I/O on Unix operating systems Setting up disk striping on Unix Using disk stripingSetting up disk striping on Windows NT Balancing I/O443 Internal stripingRecommendations for disk striping 444 Disk striping optionDisk striping hints Temporary data with the Create Dbspace command Using multiple dbspacesTransaction log file Across multiple disks446 Strategic file locationsMessage log 447 Working space for inserting, deleting, and synchronizingOptions for tuning resource use Options for tuning resource useRestricting concurrent queries 448Limiting queries by rows returned Limiting a query’s memory useForcing cursors to be non-scrolling 449Limiting the number of statements Limiting the number of cursorsLowering a connection’s priority Prefetching cache pagesOther ways to improve resource use Restricting database accessOptimizing for typical usage Disk cachingIndexing tips Using RAM diskPicking the right index type Indexing tips453 Using join indexesAllowing enough disk space for deletions Managing the size of your database Managing database size and structureDenormalizing for performance Managing database size and structureDisadvantages of denormalization Denormalization has risksPerformance benefits of denormalization 455Deciding to denormalize Improving your queriesTips for structuring queries Improving your queries457 Planning queriesQuery evaluation options 458 Setting query optimization options459 Network performanceImproving large data transfers 460 Isolate heavy network usersNetwork performance 461 Put small amounts of data in small packets462 Put large amounts of data in large packets463 Process at the server levelFilter as much data as possible at the server level 464 Other chapters of this guide for more tuning hints Getting information using stored proceduresViewing the Adaptive Server IQ environment Set to control resource use, see , Managing System466 Viewing the Adaptive Server IQ environmentMonitoring the buffer caches Monitoring and Tuning PerformanceStarting the buffer cache monitor 467468 Monitoring the buffer caches469 470 471 Examining and saving monitor results Stopping the buffer cache monitor472 Into dummytablename Stop Monitor473 Examples of monitor resultsBuffer cache 474 -contentionresults for the temp cache are -contentionresults for the main cache areResults for the memory manager are 475476 Avoiding buffer manager thrashingAvoiding buffer manager thrashing Monitoring paging on Unix systems Monitoring paging on Windows NT systemsHere is an example 477478 479 System utilities to monitor CPU use480 System utilities to monitor CPU useData server for client applications Client/server interfaces to Adaptive Server IQRestrictions for creating and running these applications 481482 Client/server interfaces to Adaptive Server IQInterfaces file Configuring IQ Servers with DseditAdaptive Server IQ as a Data Server 483Opening a Directory Services session Using the Dsedit utilityStarting Dsedit 484Adding or changing the server address Adding a server entryInterfaces file sql.ini Select a server entry in the Server box486 Network Settings, in the Control Panel487 Verifying the server addressRenaming a server entry Open Client applications and Adaptive Server IQ Sybase applications and Adaptive Server IQDeleting server entries 488System requirements Setting up Adaptive Server IQ as an Open ServerStarting the database server as an Open Server 489490 Configuring your database for use with Open ClientSetting up Adaptive Server IQ as an Open Server Option Set to Characteristics of Open Client and jConnect connections491 492 Characteristics of Open Client and jConnect connectionsTo change the option settings for TDS connections 493 Servers with multiple databasesTo connect to the livecredit server, use this syntax 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

CHAPTER 3 Working with Database Objects

=======================================================

IQ_SYSTEM_TEMP

117