Manuals / Sybase / Computer Equipment / Server

Sybase 12.4.2 manual Security concerns unrestricted database access

Models: 12.4.2

1 536

Download 536 pages 20.34 Kb

Page 110

Using an integrated login

Setting the attribute ’Integrated=yes’ in an ODBC data source causes database connection attempts using that DSN to attempt an integrated login. If the LOGIN_MODE database option is set to Standard, the ODBC driver prompts the user for a database user ID and password.

Security concerns: unrestricted database access

The integrated login features works by using the login control system of Windows NT in place of the system Adaptive Server IQ uses to control access to the database. Essentially, you pass through the database security if you can log in to the machine hosting the database, and if other conditions outlined in this chapter are met.

If you successfully log in to the Windows NT server as “dsmith”, you can connect to the database without any further proof of identification provided there is either an integrated login mapping or a default integrated login user ID.

When using integrated logins, database administrators should give special consideration to the way Windows NT enforces login security in order to prevent unwanted access to the database.

In particular, be aware that by default a "Guest" user profile is created and enabled when Windows NT Workstation or Server is installed.

Warning! Leaving the user profile Guest enabled can permit unrestricted access to a database being hosted by that server.

If the Guest user profile is enabled and has a blank password, any attempt to log in to the server will be successful. It is not required that a user profile exist on the server, or that the login ID provided have domain login permissions. Literally any user can log in to the server using any login ID and any password: they are logged in by default to the Guest user profile.

This has important implications for connecting to a database with the integrated login feature enabled.

Consider the following scenario, which assumes the Windows NT server hosting a database has a "Guest" user profile that is enabled with a blank password.

90

Image 110

Sybase 12.4.2 manual Security concerns unrestricted database access

Contents

Administration and Performance Guide Adaptive Server IQ Document ID Contents Contents 103 100101 104 135 Contents Deleting views 131 Views in the system tables132 138 Vii Viii 273 Using cursors on Select statements in procedures 253255 277 299 Contents Referential integrity is unenforced 284285 309 352 323339 Managing User IDs and Permissions Xii Xiii 459 448454 467 479 473476 481 Xvi 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 IQ About This Book Xix Related documents Overview of IQ system administration Introduction to Adaptive Server IQ System administration tasks System administration tasks Security overview Overview of Adaptive Server IQ System AdministrationAdministrative tasks Types of users Tools for system administrationTools for system administration Granting permissions Database server Catalogs and IQ Temporary Store Adaptive Server Anywhere and Adaptive Server IQIQ Store Catalog Store Stored procedures Concurrent operations Adaptive Server IQ stored procedures Stored procedures Adaptive Server Enterprise system and catalog procedures Stored Procedures for the IQ Store Procedure name Purpose Adaptive Server Enterprise system procedures System procedure Description Catalog procedure Description Catalog stored proceduresAdaptive Server Enterprise catalog procedures Procedure name Purpose System table Description System tables and viewsSystem tables and views Sysdomain Syslogin System view Description Sysuserauth Types of SQL statements Commands and FunctionsCommands and Functions Functions Fourth line of the file contains version information Message loggingManipulate date and time data for example, TODAY, Datediff Utility database Utility database Compatibility 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 Starting the server on Unix Running Adaptive Server IQ Starting 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 Using command-line switches Using command-line switchesRunning the server as a Windows NT service Running the Unix database server as a daemon Case 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 started Sample database on that server Controlling performance from the command line Setting memory switches Use 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 server Setting switches that affect timing Controlling permissions from the command line Other performance-related switches Setting a maximum Catalog page size Setting up a client/server environment Selecting communications protocols Limiting inactive connections Server from Dbisql Starting a server in forced recovery modeStarting a server from Dbisql Connections, and Catalog page size Capture server activity in a log file Monitoring server activityStarting multiple servers or clients on the same machine Log file name has this format Monitoring server activity Stopping the database server Stopping the database server PID Following 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 it Starting and stopping databases Starting and stopping databases Starting 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 Central Connecting a plug-in Connecting to a plug-in Introduction to connections Introduction to connectionsStopping Sybase Central How connections are established If you want Consider reading Following form Connection parameters specify connectionsConnection parameters are passed as connection strings This is equivalent to the following connection string Simple connection examples If you need to start the sample database, enter Connecting to a database from DbisqlSimple connection examples To connect from a Unix system To 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 Extra cache needed for Java To connect to an embedded database from Dbsql in Windows NT Connecting using a data source To connect using a data source Connecting 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 Database Working with Odbc data sources DSNs and FILEDSNs Working with Odbc data sources Creating 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 Source Configuring Odbc data sources Odbc tab Prevent Driver Not Capable errors Selecting this Login tabDatabase tab Network tab Advanced tab Name of the file Enter any additional switches hereCreating a File Data Source Which the debugging information is to be saved Using Odbc data sources on Unix Using Odbc data sources on Unix Connection parameters Short Parameter Form Argument Description Connection parameters Or NO, OFF, 0, or False if false Not supported in Odbc connectionsParameters Connection parameters and their values are case insensitive Connection parameter priorities Specify database files using DBFSpecify database names using DBN Use the Start parameter to specify cache size How Adaptive Server IQ makes connections Steps in establishing a connection Unix dblib6 with an operating system-specific extension Locating the interface libraryHow Adaptive Server IQ makes connections AutoStop Ignored if the database is already loaded Assembling a list of connection parametersConnection file Parameter Is already running Locating a server Ways by supplying network communication parameters How Adaptive Server IQ makes connections Locating the database Server name caching for faster connections Connecting from other databases Interactive SQL connections Using an integrated login Using an integrated login To 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 Network aspects of integrated logins Creating a default integrated login user Ensure that your files are valid Troubleshooting startup, shutdown, and connectionsWhat to do if you can’t start Adaptive Server IQ Ensure that network communication software is running Debugging network communications startup problemsTroubleshooting startup, shutdown, and connections Check environment variables What 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 Unix Running Adaptive Server IQ Troubleshooting startup, shutdown, and connections Building Your Adaptive Server IQ Databases Designing your database Building Your Adaptive Server IQ Databases Using Sybase Central to work with database objectsTools for working with database objects 100 Working with Database Objects Step-by-step overview of database setupUsing Dbisql to work with database objects 101 102 To set up an IQ databaseScheduling data definition tasks Extending data definition privileges 103 104 Selecting a device typeAllocating space for databases Estimating space and dbspaces required 105 106 Working with databasesWorking with databases Creating a database 107 108 109 110 Choosing an IQ page size 111 112 Specifying the size of your databaseChoosing a Catalog page size IQ Store size is 8MB and the Temporary Store is 4MB Choosing a block size for your databaseEnabling Java in the database 113 Adding dbspaces 114 115 To create a dbspace in Sybase Central Dropping dbspaces 116 ======================================================= 117 Creating tables Working with tablesDropping a database Working with tables 119 Types of tables 120 Automatic index creation for IQ tables Table placementType of table Permitted placement Default placement 121 See the following table for implications of IQ Unique Optimizing storage and query performanceData type of the column and its width 122 Altering tables 123 To drop a table in Sybase Central Dropping tables124 To create a primary key in Sybase Central Creating primary and foreign keys125 126 127 Working with viewsTable information in the system tables To create an unenforced foreign key in Sybase Central Definition in the database system tables Creating viewsWorking with views 128 To create a view in Sybase Central Using views129 An existing view definition Modifying viewsPermissions on views Alter View statement maintains the permissions on the view 131 Deleting viewsViews in the system tables Working with indexes Working with indexesIntroduction to indexes 132 133 Creating indexesIndexes in the system tables Removing indexes 134 Indexing Overview of indexesAdaptive Server IQ index types 135 Overview of indexes 136 137 Benefits over traditional indexesAdaptive Server IQ Indexes Creating Adaptive Server IQ indexes Creating Adaptive Server IQ indexesCreate Index statement Region column of the salesorder table 139 Creating an index with Sybase CentralCreating indexes concurrently To create an index with Sybase Central 140 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 Data types in the index Only the default index supports the following data typesIndexing criteria disk space usage 143 Adaptive Server IQ index types Adaptive Server IQ index typesCombining index types 144 Projections on few rows Default column indexLowFast LF index type Recommended use Comparison with other indexes HighGroup HG index typeAdvantages and disadvantages of LowFast Additional indexes 147 Advantages and disadvantages of HighGroupAutomatic creation of HighGroup index HG advantages/disadvantages Advantages Disadvantages 148 HighNonGroup HNG index typeAdvantages and disadvantages of HighNonGroup HighGroup index is also appropriate for an HNG column Optimizing performance for ad hoc joinsComparison to other indexes 149 Criteria to identify Index to select Selecting an index150 Join indexes improve query performance Using join indexesAdding column indexes after inserting data 151 Relationships in join indexes When a join becomes ad hocHow join indexes are used for queries Join hierarchy overview Columns in the join index 153 154 Join hierarchy in query resolutionOne-to-many relationship 155 Custid Lname Multiple table joins and performance 156 Steps in creating a join index 157 158 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 164 Creating a join index in Sybase CentralTypes of join hierarchies To add a join index in Sybase Central 165 Linear joinsStar joins 166 You can create this table using the following commandsAnother table Modifying tables included in a join index 167 Inserting or deleting from tables in a join index 168 169 Table versioning controls access to join indexesEstimating the size of a join index Estimating the size of a join index 170 Databases Import and export overviewImport and export methods 171 172 Input and output data formatsImport and export overview File Format Description Specifying an output format for Interactive SQL Scheduling database updatesPermissions for modifying data Moving Data In and Out of Databases Exporting data from a database Using output redirectionExporting data from a database 174 175 Bulk loading data using the Load Table statementNull value output Format ’ascii’ ’binary’ Delimited by string Bulk loading data using the Load Table statement176 Quotes on OFF Escapes on OFF Escape Character character 177 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 number Endian 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 example Options work together Table. The default is 0 for no limitSpecifies the maximum number of rows to insert into 183 184 With the Limit option, and takes precedence over it Following Windows NT example sets the column delimiter forCarriage return \x0d 185 186 This message displays memory usage information Interpreting notification messagesMemory message 187 Main buffer cache activity message Main IQ Store blocks messagesIQ Temporary Store blocks message 188 These lines display information about the Temp buffer cache Temporary buffer cache messageControlling message logging See the description for the Main buffer cache message above Inserting specified values row by row Using the Insert statementUsing the Insert statement 190 Following 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 table 192 Inserting from a different databaseInserting selected rows from the database 193 Issue a Commit to commit the insertLineitem table in the current database To insert data directly from Adaptive Server Enterprise 194 195 Importing data interactivelyInserting into tables of a join index 196 Inserting into primary and foreign key columnsInserting into primary and foreign key columns Partial-width insertions 197 198 Partial-width insertion rulesPartial-width insertions Instead of the correct number QueryAdditional columns 199 Start ROW ID option in the second Load Table statement 200 Index 201 202 Converting data on insertionConverting data on insertion 203 204 Inserting data from pre-Version 12 Adaptive Server IQLoad conversion options Conversion options for loading from flat files Data conversions in IQ 205 206 IQ conversions for comparison operations 207 IQ conversions for arithmetic operations 208 Column width issuesUsing the Ascii conversion option 209 Specifying the Date Format Date OptionSubstitution of Null or blank characters 210 211 212 Setting Year specified as Years assumedDatetime conversion option Specifying the format for Datetime conversions 213 10 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 13 Character data types 219Adaptive Server Adaptive Server IQ Enterprise Datatype Adaptive Server Enterprise Datatype IQ Datatype 15 DATE/TIME data types Handling conversion errors on data import220 Optimizing for the number of distinct values Tuning bulk loading of dataImproving load performance during database definition Creating indexes Tuning bulk loading of data Setting server startup optionsAdjusting your environment at load time Adding dbspaces Reducing Main IQ Store space use in incremental loads 223 224 Changing data using UpdateChanging data using Update Deleting data 225 226 Importing data by replicationImporting data by replication 227 228 Statements, are also available in batches Overview of proceduresAdaptive Server IQ 229 Benefits of procedures Benefits of proceduresIntroduction to procedures 230 231 Creating proceduresUsing Procedures and Batches Introduction to procedures Calling proceduresDropping procedures 232 233 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 236 Calling user-defined functionsIntroduction to user-defined functions Fullname empfname, emplname Following statement revokes permission to use the function Dropping user-defined functionsPermissions to execute user-defined functions 237 238 Introduction to batchesIntroduction to batches Dbisql and batches Control statements239 240 Using compound statementsControl statements Control statement Syntax Declarations in compound statements 241 Atomic compound statements 242 243 Structure of proceduresSQL statements allowed in procedures 244 Declaring parameters for proceduresStructure of procedures Or as follows Passing parameters to proceduresPassing parameters to functions 245 Returning results from procedures Returning results from proceduresReturning a value using the Return statement 246 Returning results as procedure parameters 247 CustomerID 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 environment Returning result sets from procedures 249 Procedure Returning multiple result sets from proceduresReturning variable result sets from procedures 250 Using cursors in procedures 251 Cursor positioning Using cursors in proceduresCursor management overview 252 Using cursors on Select statements in procedures 253 254 Errors and warnings in procedures 255 256 Default error handling in proceduresErrors and warnings in procedures 257 Error handling with on Exception Resume 258 259 On Exception Resume Begin Default handling of warnings in procedures 260 Using exception handlers in procedures 261 262 Nested compound statements and exception handlers 263 264 Using the Execute Immediate statement in proceduresFollowing statement executes the InnerProc procedure Check if you need to change the command delimiter Transactions and savepoints in proceduresSome tips for writing procedures 265 Specifying dates and times in procedures Remember to delimit statements within your procedureUse fully-qualified names for tables in procedures Some tips for writing procedures 267 Verifying procedure input argumentsStatements allowed in batches 268 Calling external libraries from proceduresUsing Select statements in batches Creating procedures and functions with external calls 269 270 External function declarationsFollowing form for Microsoft Visual C++ How parameters are passed to the external function 271 Special considerations when passing character types 272 273 Data integrity overviewHow data can become invalid 274 Integrity constraints belong in the databaseData integrity overview How database contents get changed Data integrity tools SQL statements for implementing integrity constraints 276 277 Using table and column constraintsUsing Unique constraints on columns or tables Using table and column constraints Using IQ Unique constraint on columnsUsing Check conditions on columns 278 279 Column Check conditions from user-defined data typesOption Modifying and deleting Check conditions Using Check conditions on tablesWorking with column constraints in Sybase Central 280 Declaring entity and referential integrity 281 282 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 Integrity rules in the system tables 285 Integrity rules in the system tables 286 Introduction to transactions Performance and other aspects of database administrationOverview of transactions and versioning Transactions are logical units of work Completing transactions Using transactionsStarting transactions Overview of transactions and versioning Transactions and Versioning Options in DbisqlCommitting a transaction writes data to disk 289 How concurrency works in IQ Introduction to concurrencySubdividing transactions 290 Introduction to versioning Concurrency for backupsWhy concurrency benefits you Concurrency and IQ Multiplex 292 Table-level versioningOne writer and multiple readers at the table level 293 Only one writer at a time Multiple writers and readers in a database 294 Transactions use committed data 295 Timing of commits on read transactions affects versions 296 Hold cursors span transactions 297 298 How Adaptive Server IQ keeps track of versionsVersioning of temporary tables Locks for DML operations Versioning prevents inconsistenciesHow locking works 299 300 Locks for DDL operationsHow locking works 301 Isolation levels Isolation levelsPrimary keys and locking 302 Checkpoints, savepoints, and transaction rollback 303 Checkpoints Checkpoints aid in recoveryWhen checkpoints occur Checkpoints, savepoints, and transaction rollback 305 Savepoints within transactionsReleasing savepoints Naming and nesting savepoints Rolling back to a savepointAutomatic and user-defined savepoints Following scenario What causes a rollback System recoveryRolling back transactions Effect of rollback How transaction information aids recovery 308 Performance implications 309 310 Overlapping versions and deletionsPerformance implications Cursors in transactions 311 Cursor scrolling Cursors and versioningCursor sensitivity Cursors in transactions Positioned operations Cursor command syntax and examplesHold cursors Controlling message logging for cursors 314 Introduction to international languages and character sets Adaptive Server IQ international featuresInstallation to handle international language issues 315 Introduction to international languages and character sets Using the default collationCharacter set questions and answers 316 International Languages and Character Sets Understanding character sets in softwarePieces in the character set puzzle 317 Database server software messages Applications can cause Language issues in client/server computing318 Code pages in Windows and Windows NT 319 Ansi and OEM code pages in Windows and Windows NT 320 Multibyte character sets 321 First-byte collation orderings for multibyte character sets Sorting characters using collationsInternational aspects of case sensitivity 322 Case insensitivity of identifiers Understanding localesIntroduction to locales 323 324 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 the locale collation label Setting the Sqllocale environment variableUnderstanding collations Displaying collations 329 Supplied collationsFollowing collations are supplied with Adaptive Server IQ Collation Label Type Description 330 Ansi or OEM? 331 332 333 334 335 Character translation for database messages Using multibyte collationsUnderstanding character set translation Understanding character set translation 337 To use character translation for database messages 338 Connection strings and character setsAvoiding character-set translation Language Character set Collation internals339 Collation internals Comment linesTitle line 340 Following are some sample lines for a collation Specification DescriptionCollation sequence section 341 Following is part of the Shift-JIS collation file Encodings sectionThat will be sorted together 342 Properties section 343 To configure your character set environment Finding the default collationConfiguring your character set environment International language and character set tasks 345 You can determine locale information using system functionsDetermining locale information To determine the locale of a database server Your current machine Setting localesCreating a database with a named collation 346 List the supplied collation sequences 347 To enable character-set translation on a database server Starting a database server using character set translationUsing Odbc code page translation Or on Unix Set the Dbisql option CHAROEMTranslation to a value of OFF Character set translation for Sybase Central and DbisqlCreating a custom collation 349 350 351 Compatibility issuesCreating a database with a custom collation To create a database with a custom collation 352 Performance issuesPerformance issues 353 An overview of database permissionsManage user IDs 354 DBA authority overviewAn overview of database permissions Table and views permissions overview Resource authority overviewOwnership permissions overview Managing User IDs and Permissions 356 Managing individual user IDs and permissionsGroup permissions overview Creating new users Changing a passwordWith the following command 357 Granting DBA and resource authority 358 Granting permissions on tables and views 359 360 Granting users the right to grant permissionsContext of groups in Permissions of groups To grant user permission on tables in Sybase Central 361 Granting permissions on proceduresTo grant user permissions on procedures in Sybase Central Revoking user permissions 362 Creating groups To create a group with a name and passwordManaging groups 363 364 Granting group membership to usersManaging groups To create a group in Sybase Central 365 Permissions of groupsReferring to tables owned by groups Groups without passwords 366 367 Database object names and prefixesSpecial groups Database object names and prefixes 368 369 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 How user permissions are assessed How user permissions are assessedManaging the resources connections use Be modified is strictly defined Temporary Store 373 Limits the number of prepared Users and permissions in the system tablesUsers and permissions in the system tables 374 375 Default ContentsViews Default Contents 376 Backup protects your data 377 Backing up your database Types of backupsData in backups Backing up your database 379 Transaction log in backup, restore, and recoveryBackup and Data Recovery Tape backup requirements Distribution of backup dataDisk backup requirements Selecting archive devices Rewinding tapes Preparing for backupObtaining DBA privileges 381 You can run Backup in two ways Retaining old disk backupsTwo ways to run Backup Estimating Media Capacity To back up an IQ database, use the following syntax Concurrency and backupsBackup statement 383 Specifying archive devices Specifying the type of backupSpecifying operator presence 384 385 386 387 Waiting for Tape Devices Backup ExamplesOther backup options 388 Recovery attempts Recovery from errors during backupChecking for backup space 389 Do not specify the Stacker or Size parameters After you complete a backupPerforming backups with non-Sybase products 390 391 Performing system-level backupsShutting down the database Backing up the right files Restoring from a system-level backupPerforming system-level backups 392 For this size database It takes about this long Validating your database393 394 Interpreting resultsValidating your database Orphaned block statistic Meaning Concurrency issues for spiqcheckdb 395 Restoring your databases Before you restoreRestoring your databases 396 Restore accommodates dbspace changes 397 Specifying files for an incremental restore Restoring disk backup filesRestoring tape backup files Keeping the database unchanged between restores To restore a database, use the following syntax Restore statementRestoring from a compatible backup 399 Moving database files 400 401 402 Displaying header information Adjusting data sources and configuration filesRestoring in the correct order 403 404 Set, and the restore order, are as followsSet must be restored first, and must be in the first device Switch Description Renaming the transaction log after you restoreDblog command-line utility 405 Transaction log utility options Validating the database after you restoreRestore requires exclusive write access Maintaining a transaction log or mirror Displaying header information 407 408 Recovery from errors during restoreUsing Symbolic Links Unix Only Unattended backup 409 Getting information about backups and restores Getting information about backups and restoresLocating the backup log 410 Content of the backup log 411 Recording dbspace names Maintaining the backup logViewing the backup log in Sybase Central 412 Determining your data backup and recovery strategy 413 Determining your data backup and recovery strategy Scheduling routine backupsDetermining the type of backup 414 Increasing the number of archive devices Designating Backup and Restore ResponsibilitiesImproving performance for backup and restore 415 Eliminating data verification Spooling backup dataIncreasing memory used during backup Balancing system load Controlling the size of the Catalog Store 417 418 Introduction to performance terms DBA can tune performance by adjusting resource usageYour hardware and software configuration Designing for performance Overview of memory use Overview of memory usePaging increases available memory 420 Managing System Resources Utilities to monitor swappingServer memory 421 422 Managing buffer cachesDetermining the sizes of the buffer caches 423 Operating system and other applicationsAdaptive Server IQ memory overhead Raw partitions versus file systems Multi-user database access424 Memory requirements for loads Buffer caches and physical memory Adaptive Server IQ main and temp buffer caches425 Other considerations Example of setting buffer cache sizes426 Memory available for buffer caches Example 427 Setting buffer cache sizesMethods of adjusting buffer cache sizes To change the buffer cache sizes permanently Setting buffer cache size database options428 Normally you change the buffer cache sizes by setting Setting buffer cache size server switchesSetting the page size Specifying page size Block size 430 Saving memory Decrease buffer cache settingsAdjust blocking factor for loads Data compression Optimizing for large numbers of users IQ command line option changesActively using the database Use the following guideline to determine Block Factor System parameters Command and set rlimfdmax=4096 in /etc/systemIQ Temp space 433 Platform-specific memory options 434 Options that can provide more memory Recommendations for small memory configurationsPhysical size of memory on the system 435 436 Controlling file system buffering You must then restart the server with the following commandManaging large buffer caches on HP 437 438 Options for Java-enabled databasesOther ways to get more memory Process threading model 439 Process threading model Insufficient threads errorIQ options for managing thread usage 440 441 Balancing I/ORaw I/O on Unix operating systems Setting up disk striping on Windows NT Using disk stripingSetting up disk striping on Unix Balancing I/O 443 Internal stripingRecommendations for disk striping 444 Disk striping optionDisk striping hints Transaction log file Using multiple dbspacesTemporary data with the Create Dbspace command Across multiple disks 446 Strategic file locationsMessage log Working space for inserting, deleting, and synchronizing 447 Restricting concurrent queries Options for tuning resource useOptions for tuning resource use 448 Forcing cursors to be non-scrolling Limiting a query’s memory useLimiting queries by rows returned 449 Lowering a connection’s priority Limiting the number of cursorsLimiting the number of statements Prefetching cache pages Optimizing for typical usage Restricting database accessOther ways to improve resource use Disk caching Picking the right index type Using RAM diskIndexing tips Indexing tips 453 Using join indexesAllowing enough disk space for deletions Denormalizing for performance Managing database size and structureManaging the size of your database Managing database size and structure Performance benefits of denormalization Denormalization has risksDisadvantages of denormalization 455 Tips for structuring queries Improving your queriesDeciding to denormalize Improving your queries 457 Planning queriesQuery evaluation options Setting query optimization options 458 459 Network performanceImproving large data transfers 460 Isolate heavy network usersNetwork performance Put small amounts of data in small packets 461 Put large amounts of data in large packets 462 463 Process at the server levelFilter as much data as possible at the server level 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 System Viewing the Adaptive Server IQ environment 466 Starting the buffer cache monitor Monitoring and Tuning PerformanceMonitoring the buffer caches 467 Monitoring the buffer caches 468 469 470 471 472 Stopping the buffer cache monitorExamining and saving monitor results Into dummytablename Stop Monitor 473 Examples of monitor resultsBuffer cache 474 Results for the memory manager are -contentionresults for the main cache are-contentionresults for the temp cache are 475 476 Avoiding buffer manager thrashingAvoiding buffer manager thrashing Here is an example Monitoring paging on Windows NT systemsMonitoring paging on Unix systems 477 478 System utilities to monitor CPU use 479 System utilities to monitor CPU use 480 Restrictions for creating and running these applications Client/server interfaces to Adaptive Server IQData server for client applications 481 Client/server interfaces to Adaptive Server IQ 482 Adaptive Server IQ as a Data Server Configuring IQ Servers with DseditInterfaces file 483 Starting Dsedit Using the Dsedit utilityOpening a Directory Services session 484 Interfaces file sql.ini Adding a server entryAdding or changing the server address Select a server entry in the Server box Network Settings, in the Control Panel 486 487 Verifying the server addressRenaming a server entry Deleting server entries Sybase applications and Adaptive Server IQOpen Client applications and Adaptive Server IQ 488 Starting the database server as an Open Server Setting up Adaptive Server IQ as an Open ServerSystem requirements 489 490 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 Index 495 Index 496 497 WINLATIN1 498 Datetime 499 Odbc Unix 500 501 DDL 502 Pipenotconnected 503 504 See Also Dbisql 505 506 507 Null 508 TCP/IP 509 See Also stored 510 Rawdetect 511 512 513 514 515 516