System tables and views, System table Description

System tables and views

Procedure name	Purpose
sp_servercaps	Display information about a remote
	server’s capabilities

System tables and views

Adaptive Server IQ system tables contain all of the information the database server needs to manage your IQ system. The system tables reside in the Catalog Store, and are sometimes called catalog tables. For some system tables there are also views that make it easier to display the information in the table. The SYS user ID owns the system tables.

Among the information in the system tables is:

•Database characteristics

•Table characteristics, including table definitions and information about the size and location of each table

•Information about indexes

•Current settings for database and DBISQL options

System tables include:

System table	Description
DUMMY	A table with exactly one row, useful
	for extracting information from the
	database

SYSARTICLE	Describes an article in a SQL Remote
	publication

SYSARTICLECOL	Describes columns in each article in a
	SQL Remote publication

SYSCOLLATION	Contains the complete collation
	sequences available to Adaptive
	Server IQ

SYSCOLLATIONMAPPINGS	Lists the collation sequences available
	in Adaptive Server IQ and their GPG
	and JDK mappings

SYSCOLUMN	Describes each column in every table
	or view

Page 32

Image 32

Sybase 12.4.2 manual System tables and views, System table Description

Contents

Administration and Performance Guide Adaptive Server IQ Document ID Contents Contents 100 101103 104 Contents Deleting views 131 Views in the system tables 132135 138 Vii Viii Using cursors on Select statements in procedures 253 255273 277 Contents Referential integrity is unenforced 284 285299 309 323 339352 Managing User IDs and Permissions Xii Xiii 448 454459 467 473 476479 481 Xvi Xvii How to use this bookAudience Guide to using this book To learn how to Read this chapter Related documentsRelated 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 Tools for system administration Tools for system administrationTypes of users Granting permissions Database server Catalogs and IQ Adaptive Server Anywhere and Adaptive Server IQ IQ StoreTemporary 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 stored procedures Adaptive Server Enterprise catalog proceduresCatalog procedure Description Procedure name Purpose System table Description System tables and viewsSystem tables and views Sysdomain Syslogin System view Description Sysuserauth Commands and Functions Commands and FunctionsTypes of SQL statements 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 Running the server as a Windows NT serviceUsing command-line switches Running the Unix database server as a daemon Case sensitivity Using configuration files Elora 16M Tcpipport=2367 -gm Gp 4096 path\mydb.db Naming the server and databases Required if you do not supply a databaseWindow Database started Sample database on that server Controlling performance from the command line Setting memory switches Default value of -iqgovernis 2 x the number of CPUs +10 This switch as the number of megabytes of wired memoryUse Concurrent user connections on a particular 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 Starting a server in forced recovery mode Starting a server from DbisqlServer from Dbisql Connections, and Catalog page size Monitoring server activity Starting multiple servers or clients on the same machineCapture server activity in a log file Log file name has this format Monitoring server activity Stopping the database server Stopping the database server PID Who can stop the server? Shutting down operating system sessionsFollowing example stops a server from Dbisql 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 Connection parameters specify connections Connection parameters are passed as connection stringsFollowing form This is equivalent to the following connection string Simple connection examples Connecting to a database from Dbisql Simple connection examplesIf you need to start the sample database, enter 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 Connecting from Adaptive Server IQ utilities Named server, using the default databaseYou may connect to the wrong server Database Working with Odbc data sources DSNs and FILEDSNs Working with Odbc data sources Creating and editing Odbc data sources Creating and editing Odbc data sources You could also use the host network address. For exampleTo create an Odbc User Data Source To test an Odbc Data Source Configuring Odbc data sources Odbc tab Prevent Driver Not Capable errors Selecting this Login tabDatabase tab Network tab Advanced tab Enter any additional switches here Creating a File Data SourceName of the file 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 Not supported in Odbc connections ParametersOr NO, OFF, 0, or False if false Connection parameters and their values are case insensitive Specify database files using DBF Specify database names using DBNConnection parameter priorities 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 Assembling a list of connection parameters Connection fileAutoStop Ignored if the database is already loaded 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 Debugging network communications startup problems Troubleshooting startup, shutdown, and connectionsEnsure that network communication software is running 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 Using Sybase Central to work with database objects Tools for working with database objectsBuilding Your Adaptive Server IQ Databases 100 Step-by-step overview of database setup Using Dbisql to work with database objectsWorking 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 Choosing a block size for your database Enabling Java in the databaseIQ Store size is 8MB and the Temporary Store is 4MB 113 Adding dbspaces 114 115 To create a dbspace in Sybase Central Dropping dbspaces 116 ======================================================= 117 Working with tables Dropping a databaseCreating tables Working with tables 119 Types of tables 120 Table placement Type of table Permitted placement Default placementAutomatic index creation for IQ tables 121 Optimizing storage and query performance Data type of the column and its widthSee the following table for implications of IQ Unique 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 Working with views Table information in the system tables127 To create an unenforced foreign key in Sybase Central Creating views Working with viewsDefinition in the database system tables 128 To create a view in Sybase Central Using views129 Modifying views Permissions on viewsAn existing view definition Alter View statement maintains the permissions on the view 131 Deleting viewsViews in the system tables Working with indexes Introduction to indexesWorking with indexes 132 133 Creating indexesIndexes in the system tables Removing indexes 134 Overview of indexes Adaptive Server IQ index typesIndexing 135 Overview of indexes 136 137 Benefits over traditional indexesAdaptive Server IQ Indexes Creating Adaptive Server IQ indexes Create Index statementCreating Adaptive Server IQ indexes Region column of the salesorder table Creating an index with Sybase Central Creating indexes concurrently139 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 Only the default index supports the following data types Indexing criteria disk space usageData types in the index 143 Adaptive Server IQ index types Combining index typesAdaptive Server IQ index types 144 Default column index LowFast LF index typeProjections on few rows Recommended use HighGroup HG index type Advantages and disadvantages of LowFastComparison with other indexes Additional indexes Advantages and disadvantages of HighGroup Automatic creation of HighGroup index147 HG advantages/disadvantages Advantages Disadvantages 148 HighNonGroup HNG index typeAdvantages and disadvantages of HighNonGroup Optimizing performance for ad hoc joins Comparison to other indexesHighGroup index is also appropriate for an HNG column 149 Criteria to identify Index to select Selecting an index150 Using join indexes Adding column indexes after inserting dataJoin indexes improve query performance 151 When a join becomes ad hoc How join indexes are used for queriesRelationships in join indexes 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 Creating a join index in Sybase Central Types of join hierarchies164 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 Import and export overview Import and export methodsDatabases 171 Input and output data formats Import and export overview172 File Format Description Scheduling database updates Permissions for modifying dataSpecifying an output format for Interactive SQL Moving Data In and Out of Databases Using output redirection Exporting data from a databaseExporting data from a database 174 175 Bulk loading data using the Load Table statementNull value output Bulk loading data using the Load Table statement 176Format ’ascii’ ’binary’ Delimited by string Quotes on OFF Escapes on OFF Escape Character character 177 Binary with Null Byte Prefix 1 2 4 ’delimiter-string’ 178 179 180 181 Block Factor number Block Size numberPreview on OFF ROW Delimited by ’delimiter-string’ Start ROW ID number On default is NATIVE. You can also specify Specifies the byte ordering during reads. ThisEndian platforms like DEC ALPHA, and Windows NT Here is a Windows NT example Table. The default is 0 for no limit Specifies the maximum number of rows to insert intoOptions work together 183 184 Following Windows NT example sets the column delimiter for Carriage return \x0dWith the Limit option, and takes precedence over it 185 186 Interpreting notification messages Memory messageThis message displays memory usage information 187 Main IQ Store blocks messages IQ Temporary Store blocks messageMain buffer cache activity message 188 Temporary buffer cache message Controlling message loggingThese lines display information about the Temp buffer cache See the description for the Main buffer cache message above Using the Insert statement Using the Insert statementInserting specified values row by row 190 Inserting selected rows from the database Complete description of Adaptive Server IQ data typesFollowing example adds 1995-06-09 into the lshipdate column Into the lorderkey column in the lineitem table 192 Inserting from a different databaseInserting selected rows from the database Issue a Commit to commit the insert Lineitem table in the current database193 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 Query Additional columnsInstead of the correct number 199 Start ROW ID option in the second Load Table statement 200 Index 201 202 Converting data on insertionConverting data on insertion 203 Inserting data from pre-Version 12 Adaptive Server IQ Load conversion options204 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 Date Option Substitution of Null or blank charactersSpecifying the Date Format 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 219 Adaptive Server Adaptive Server IQ Enterprise Datatype13 Character data types Adaptive Server Enterprise Datatype IQ Datatype 15 DATE/TIME data types Handling conversion errors on data import220 Tuning bulk loading of data Improving load performance during database definitionOptimizing for the number of distinct values Creating indexes Setting server startup options Adjusting your environment at load timeTuning bulk loading of data 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 Overview of procedures Adaptive Server IQStatements, are also available in batches 229 Benefits of procedures Introduction to proceduresBenefits of procedures 230 231 Creating proceduresUsing Procedures and Batches Calling procedures Dropping proceduresIntroduction to 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 Calling user-defined functions Introduction to user-defined functions236 Fullname empfname, emplname Dropping user-defined functions Permissions to execute user-defined functionsFollowing statement revokes permission to use the function 237 238 Introduction to batchesIntroduction to batches Dbisql and batches Control statements239 Using compound statements Control statements240 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 Passing parameters to procedures Passing parameters to functionsOr as follows 245 Returning results from procedures Returning a value using the Return statementReturning results from procedures 246 Returning results as procedure parameters 247 Declared in the procedure argument list Show the number of orders placed by the customer with IDCustomerID parameter is declared as an in parameter. This Orders variable that is returned to the calling environment Returning result sets from procedures 249 Returning multiple result sets from procedures Returning variable result sets from proceduresProcedure 250 Using cursors in procedures 251 Using cursors in procedures Cursor management overviewCursor positioning 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 Transactions and savepoints in procedures Some tips for writing proceduresCheck if you need to change the command delimiter 265 Remember to delimit statements within your procedure Use fully-qualified names for tables in proceduresSpecifying dates and times 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 IQ Unique constraint on columns Using Check conditions on columnsUsing table and column constraints 278 279 Column Check conditions from user-defined data typesOption Using Check conditions on tables Working with column constraints in Sybase CentralModifying and deleting Check conditions 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 Performance and other aspects of database administration Overview of transactions and versioningIntroduction to transactions Transactions are logical units of work Using transactions Starting transactionsCompleting transactions Overview of transactions and versioning Options in Dbisql Committing a transaction writes data to diskTransactions and Versioning 289 Introduction to concurrency Subdividing transactionsHow concurrency works in IQ 290 Concurrency for backups Why concurrency benefits youIntroduction to versioning 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 Versioning prevents inconsistencies How locking worksLocks for DML operations 299 300 Locks for DDL operationsHow locking works 301 Isolation levels Primary keys and lockingIsolation levels 302 Checkpoints, savepoints, and transaction rollback 303 Checkpoints aid in recovery When checkpoints occurCheckpoints Checkpoints, savepoints, and transaction rollback 305 Savepoints within transactionsReleasing savepoints Rolling back to a savepoint Automatic and user-defined savepointsNaming and nesting savepoints Following scenario System recovery Rolling back transactionsWhat causes a rollback Effect of rollback How transaction information aids recovery 308 Performance implications 309 310 Overlapping versions and deletionsPerformance implications Cursors in transactions 311 Cursors and versioning Cursor sensitivityCursor scrolling Cursors in transactions Cursor command syntax and examples Hold cursorsPositioned operations Controlling message logging for cursors 314 Adaptive Server IQ international features Installation to handle international language issuesIntroduction to international languages and character sets 315 Using the default collation Character set questions and answersIntroduction to international languages and character sets 316 Understanding character sets in software Pieces in the character set puzzleInternational Languages and Character Sets 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 Sorting characters using collations International aspects of case sensitivityFirst-byte collation orderings for multibyte character sets 322 Understanding locales Introduction to localesCase insensitivity of identifiers 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 Setting the Sqllocale environment variable Understanding collationsUnderstanding the locale collation label Displaying collations Supplied collations Following collations are supplied with Adaptive Server IQ329 Collation Label Type Description 330 Ansi or OEM? 331 332 333 334 335 Using multibyte collations Understanding character set translationCharacter translation for database messages 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 Comment lines Title lineCollation internals 340 Specification Description Collation sequence sectionFollowing are some sample lines for a collation 341 Encodings section That will be sorted togetherFollowing is part of the Shift-JIS collation file 342 Properties section 343 Finding the default collation Configuring your character set environmentTo configure your character set environment International language and character set tasks You can determine locale information using system functions Determining locale information345 To determine the locale of a database server Setting locales Creating a database with a named collationYour current machine 346 List the supplied collation sequences 347 Starting a database server using character set translation Using Odbc code page translationTo enable character-set translation on a database server Or on Unix Character set translation for Sybase Central and Dbisql Creating a custom collationSet the Dbisql option CHAROEMTranslation to a value of OFF 349 350 Compatibility issues Creating a database with a custom collation351 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 Resource authority overview Ownership permissions overviewTable and views permissions overview Managing User IDs and Permissions 356 Managing individual user IDs and permissionsGroup permissions overview Changing a password With the following commandCreating new users 357 Granting DBA and resource authority 358 Granting permissions on tables and views 359 Granting users the right to grant permissions Context of groups in Permissions of groups360 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 To create a group with a name and password Managing groupsCreating groups 363 Granting group membership to users Managing groups364 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 Managing the resources connections useHow user permissions are assessed Be modified is strictly defined Temporary Store 373 Users and permissions in the system tables Users and permissions in the system tablesLimits the number of prepared 374 375 Default ContentsViews Default Contents 376 Backup protects your data 377 Types of backups Data in backupsBacking up your database Backing up your database 379 Transaction log in backup, restore, and recoveryBackup and Data Recovery Distribution of backup data Disk backup requirementsTape backup requirements Selecting archive devices Preparing for backup Obtaining DBA privilegesRewinding tapes 381 Retaining old disk backups Two ways to run BackupYou can run Backup in two ways Estimating Media Capacity Concurrency and backups Backup statementTo back up an IQ database, use the following syntax 383 Specifying the type of backup Specifying operator presenceSpecifying archive devices 384 385 386 387 Backup Examples Other backup optionsWaiting for Tape Devices 388 Recovery from errors during backup Checking for backup spaceRecovery attempts 389 After you complete a backup Performing backups with non-Sybase productsDo not specify the Stacker or Size parameters 390 391 Performing system-level backupsShutting down the database Restoring from a system-level backup Performing system-level backupsBacking up the right files 392 For this size database It takes about this long Validating your database393 Interpreting results Validating your database394 Orphaned block statistic Meaning Concurrency issues for spiqcheckdb 395 Before you restore Restoring your databasesRestoring your databases 396 Restore accommodates dbspace changes 397 Restoring disk backup files Restoring tape backup filesSpecifying files for an incremental restore Keeping the database unchanged between restores Restore statement Restoring from a compatible backupTo restore a database, use the following syntax 399 Moving database files 400 401 402 Adjusting data sources and configuration files Restoring in the correct orderDisplaying header information 403 404 Set, and the restore order, are as followsSet must be restored first, and must be in the first device Renaming the transaction log after you restore Dblog command-line utilitySwitch Description 405 Validating the database after you restore Restore requires exclusive write accessTransaction log utility options 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 Locating the backup logGetting information about backups and restores 410 Content of the backup log 411 Maintaining the backup log Viewing the backup log in Sybase CentralRecording dbspace names 412 Determining your data backup and recovery strategy 413 Scheduling routine backups Determining the type of backupDetermining your data backup and recovery strategy 414 Designating Backup and Restore Responsibilities Improving performance for backup and restoreIncreasing the number of archive devices 415 Spooling backup data Increasing memory used during backupEliminating data verification Balancing system load Controlling the size of the Catalog Store 417 418 DBA can tune performance by adjusting resource usage Your hardware and software configurationIntroduction to performance terms Designing for performance Overview of memory use Paging increases available memoryOverview of memory use 420 Utilities to monitor swapping Server memoryManaging System Resources 421 422 Managing buffer cachesDetermining the sizes of the buffer caches 423 Operating system and other applicationsAdaptive Server IQ memory overhead Multi-user database access 424Raw partitions versus file systems Memory requirements for loads Buffer caches and physical memory Adaptive Server IQ main and temp buffer caches425 Example of setting buffer cache sizes 426Other considerations 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 Setting buffer cache size server switches Setting the page sizeNormally you change the buffer cache sizes by setting Specifying page size Block size 430 Decrease buffer cache settings Adjust blocking factor for loadsSaving memory Data compression IQ command line option changes Actively using the databaseOptimizing for large numbers of users Use the following guideline to determine Block Factor Command and set rlimfdmax=4096 in /etc/system IQ Temp spaceSystem parameters 433 Platform-specific memory options 434 Recommendations for small memory configurations Physical size of memory on the systemOptions that can provide more memory 435 436 You must then restart the server with the following command Managing large buffer caches on HPControlling file system buffering 437 438 Options for Java-enabled databasesOther ways to get more memory Process threading model 439 Insufficient threads error IQ options for managing thread usageProcess threading model 440 441 Balancing I/ORaw I/O on Unix operating systems Using disk striping Setting up disk striping on UnixSetting up disk striping on Windows NT Balancing I/O 443 Internal stripingRecommendations for disk striping 444 Disk striping optionDisk striping hints Using multiple dbspaces Temporary data with the Create Dbspace commandTransaction log file Across multiple disks 446 Strategic file locationsMessage log Working space for inserting, deleting, and synchronizing 447 Options for tuning resource use Options for tuning resource useRestricting concurrent queries 448 Limiting a query’s memory use Limiting queries by rows returnedForcing cursors to be non-scrolling 449 Limiting the number of cursors Limiting the number of statementsLowering a connection’s priority Prefetching cache pages Restricting database access Other ways to improve resource useOptimizing for typical usage Disk caching Using RAM disk Indexing tipsPicking the right index type Indexing tips 453 Using join indexesAllowing enough disk space for deletions Managing database size and structure Managing the size of your databaseDenormalizing for performance Managing database size and structure Denormalization has risks Disadvantages of denormalizationPerformance benefits of denormalization 455 Improving your queries Deciding to denormalizeTips for structuring queries 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 Getting information using stored procedures Other chapters of this guide for more tuning hintsViewing the Adaptive Server IQ environment Set to control resource use, see , Managing System Viewing the Adaptive Server IQ environment 466 Monitoring and Tuning Performance Monitoring the buffer cachesStarting the buffer cache monitor 467 Monitoring the buffer caches 468 469 470 471 Stopping the buffer cache monitor Examining and saving monitor results472 Into dummytablename Stop Monitor 473 Examples of monitor resultsBuffer cache 474 -contentionresults for the main cache are -contentionresults for the temp cache areResults for the memory manager are 475 476 Avoiding buffer manager thrashingAvoiding buffer manager thrashing Monitoring paging on Windows NT systems Monitoring paging on Unix systemsHere is an example 477 478 System utilities to monitor CPU use 479 System utilities to monitor CPU use 480 Client/server interfaces to Adaptive Server IQ Data server for client applicationsRestrictions for creating and running these applications 481 Client/server interfaces to Adaptive Server IQ 482 Configuring IQ Servers with Dsedit Interfaces fileAdaptive Server IQ as a Data Server 483 Using the Dsedit utility Opening a Directory Services sessionStarting Dsedit 484 Adding a server entry Adding or changing the server addressInterfaces file sql.ini Select a server entry in the Server box Network Settings, in the Control Panel 486 487 Verifying the server addressRenaming a server entry Sybase applications and Adaptive Server IQ Open Client applications and Adaptive Server IQDeleting server entries 488 Setting up Adaptive Server IQ as an Open Server System requirementsStarting the database server as an Open Server 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