Choosing a block size for your database, 113

CHAPTER 3 Working with Database Objects

Choosing a block size for your database

Example	The following statement creates a large database on a UNIX raw partition with
	a Catalog PAGE SIZE of 4KB, and an IQ PAGE SIZE of 128KB. By default, the
	IQ Store size is 8MB and the Temporary Store is 4MB.
	CREATE DATABASE ’company’ IQ PATH ’/dev/rdsk/c2t6d0s3’
	PAGE SIZE 4096
	IQ PAGE SIZE 131072

Enabling Java in the database

By default, Java support is ON for IQ databases. It can be turned off with the

JAVA OFF option. With Java ON:

•You can write a Java procedure that accesses tables in the Catalog Store or the IQ Store. These queries are processed like any other query.

•You cannot use a Java-based user-defined function within a query to an IQ table, but you can use it on Catalog Store tables.

•Windows NT users can also use Java-based user-defined functions in queries on tables in an Adaptive Server Anywhere database, or queries to IQ tables from an Adaptive Server Anywhere database, using the remote access capabilities described in the Adaptive Server IQ Installation and Configuration Guide.

•You cannot store Java data in an IQ table. If you attempt to create an IQ column of type Java, you receive an error, and you may cause the database server to fail.

Additional support for Java will be provided in a subsequent release. For information on Java support in Adaptive Server Anywhere, see Part 3, “Java in the Database” in the Adaptive Server Anywhere User’s Guide. The chapter entitled “Data Access Using JDBC” is particularly relevant for IQ users.

113

Page 133

Image 133

Sybase 12.4.2 manual Choosing a block size for your database, Enabling Java in the database, 113

Contents

Adaptive Server IQ Administration and Performance Guide Document ID Contents Contents 101 100103 104 132 Contents Deleting views 131 Views in the system tables135 138 Vii Viii 255 Using cursors on Select statements in procedures 253273 277 285 Contents Referential integrity is unenforced 284299 309 339 323352 Managing User IDs and Permissions Xii Xiii 454 448459 467 476 473479 481 Xvi Audience How to use this bookXvii Related documents Guide to using this book To learn how to Read this chapterRelated documents Documentation for Adaptive Server IQ Xix About This Book Related documents Introduction to Adaptive Server IQ Overview of IQ system administration System administration tasks System administration tasks Administrative tasks Overview of Adaptive Server IQ System AdministrationSecurity overview Tools for system administration Tools for system administrationTypes of users Granting permissions Catalogs and IQ Database server IQ Store Adaptive Server Anywhere and Adaptive Server IQTemporary Store Catalog Store Concurrent operations Stored procedures Stored procedures Adaptive Server IQ stored procedures Stored Procedures for the IQ Store Procedure name Purpose Adaptive Server Enterprise system and catalog procedures System procedure Description Adaptive Server Enterprise system procedures Adaptive Server Enterprise catalog procedures Catalog stored proceduresCatalog procedure Description Procedure name Purpose System tables and views System tables and viewsSystem table Description Sysdomain Syslogin Sysuserauth System view Description Commands and Functions Commands and FunctionsTypes of SQL statements Functions Manipulate date and time data for example, TODAY, Datediff Message loggingFourth line of the file contains version information Utility database Utility database Compatibility with earlier versions Compatibility with earlier versions Starting the database server Server command lines Server command linesGeneral form for the server command line is as follows Running Adaptive Server IQ Starting the server on Unix To start the server on Unix using the startup utility Using the startup utilityStarting the server on Unix Typing the server startup command Starting the server from the NT Start menu Starting the server on Windows NTStarting the server on Windows NT 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 daemon Case 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 started Sample database on that server Setting memory switches Controlling performance from the command line This 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 server Setting switches that affect timing Other performance-related switches Controlling permissions from the command line Setting a maximum Catalog page size Selecting communications protocols Setting up a client/server environment Limiting inactive connections Starting a server from Dbisql Starting a server in forced recovery modeServer from Dbisql Connections, and Catalog page size Starting multiple servers or clients on the same machine Monitoring server activityCapture server activity in a log file Log file name has this format Monitoring server activity Stopping the database server PID Stopping the database server Shutting 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 it Starting and stopping databases Starting and stopping databases Command that executes when you perform these steps is From a command line, type the following commandStarting the asiqdemo database Starting and stopping Sybase Central Starting and stopping Sybase Central Connecting to a plug-in Connecting a plug-in Stopping Sybase Central Introduction to connectionsIntroduction to connections If you want Consider reading How connections are established Connection parameters are passed as connection strings Connection parameters specify connectionsFollowing form This is equivalent to the following connection string Simple connection examples Simple connection examples Connecting to a database from DbisqlIf you need to start the sample database, enter To connect from a Unix system To connect from a Windows NT system Password Connecting to other databases from DbisqlEnter the user ID Start Dbisql by typing at a system command prompt Connecting to an embedded databaseTo connect to a database from Dbisql on Unix To connect to an embedded database from Dbsql in Windows NT Extra cache needed for Java To connect using a data source Connecting using a data source Connecting 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 Database Working with Odbc data sources Working with Odbc data sources DSNs and FILEDSNs Creating 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 Source Odbc tab Configuring Odbc data sources Prevent Driver Not Capable errors Database tab Login tabSelecting this Advanced tab Network tab Creating a File Data Source Enter any additional switches hereName of the file Which the debugging information is to be saved Using Odbc data sources on Unix Using Odbc data sources on Unix Short Parameter Form Argument Description Connection parameters Connection parameters Parameters Not supported in Odbc connectionsOr NO, OFF, 0, or False if false Connection parameters and their values are case insensitive Specify database names using DBN Specify database files using DBFConnection parameter priorities Use the Start parameter to specify cache size Steps in establishing a connection How Adaptive Server IQ makes connections How Adaptive Server IQ makes connections Locating the interface libraryUnix dblib6 with an operating system-specific extension Connection file Assembling a list of connection parametersAutoStop Ignored if the database is already loaded Parameter Is already running Ways by supplying network communication parameters Locating a server How Adaptive Server IQ makes connections Locating the database Server name caching for faster connections Interactive SQL connections Connecting from other databases Using an integrated login Using an integrated login Enabling the integrated login feature Using integrated loginsTo use an integrated login To map an integrated login from Sybase Central Creating an integrated loginTo map an integrate login using a SQL statement Following Dbisql statement Connecting from a client applicationCan connect to a database if all the following are true Security concerns unrestricted database access Setting temporary public options for added security Creating a default integrated login user Network aspects of integrated logins What to do if you can’t start Adaptive Server IQ Troubleshooting startup, shutdown, and connectionsEnsure that your files are valid Troubleshooting startup, shutdown, and connections Debugging network communications startup problemsEnsure that network communication software is running Check environment variables What 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 Unix Running Adaptive Server IQ Troubleshooting startup, shutdown, and connections Designing your database Building Your Adaptive Server IQ Databases Tools for working with database objects Using Sybase Central to work with database objectsBuilding Your Adaptive Server IQ Databases 100 Using Dbisql to work with database objects Step-by-step overview of database setupWorking with Database Objects 101 Scheduling data definition tasks To set up an IQ database102 103 Extending data definition privileges Allocating space for databases Selecting a device type104 105 Estimating space and dbspaces required Working with databases Working with databases106 107 Creating a database 108 109 110 111 Choosing an IQ page size Choosing a Catalog page size Specifying the size of your database112 Enabling Java in the database Choosing a block size for your databaseIQ Store size is 8MB and the Temporary Store is 4MB 113 114 Adding dbspaces To create a dbspace in Sybase Central 115 116 Dropping dbspaces 117 ======================================================= Dropping a database Working with tablesCreating tables Working with tables 119 120 Types of tables Type of table Permitted placement Default placement Table placementAutomatic index creation for IQ tables 121 Data type of the column and its width Optimizing storage and query performanceSee the following table for implications of IQ Unique 122 123 Altering tables 124 Dropping tablesTo drop a table in Sybase Central 125 Creating primary and foreign keysTo create a primary key in Sybase Central 126 Table information in the system tables Working with views127 To create an unenforced foreign key in Sybase Central Working with views Creating viewsDefinition in the database system tables 128 129 Using viewsTo create a view in Sybase Central Permissions on views Modifying viewsAn existing view definition Alter View statement maintains the permissions on the view Views in the system tables Deleting views131 Introduction to indexes Working with indexesWorking with indexes 132 Indexes in the system tables Creating indexes133 134 Removing indexes Adaptive Server IQ index types Overview of indexesIndexing 135 136 Overview of indexes Adaptive Server IQ Indexes Benefits over traditional indexes137 Create Index statement Creating Adaptive Server IQ indexesCreating Adaptive Server IQ indexes Region column of the salesorder table Creating indexes concurrently Creating an index with Sybase Central139 To create an index with Sybase Central Choosing an index type Choosing an index type140 Types of queries Number of unique values in the index141 Query type/index 142Type of Query Usage Recommended Index Type Indexing criteria disk space usage Only the default index supports the following data typesData types in the index 143 Combining index types Adaptive Server IQ index typesAdaptive Server IQ index types 144 LowFast LF index type Default column indexProjections on few rows Recommended use Advantages and disadvantages of LowFast HighGroup HG index typeComparison with other indexes Additional indexes Automatic creation of HighGroup index Advantages and disadvantages of HighGroup147 HG advantages/disadvantages Advantages Disadvantages Advantages and disadvantages of HighNonGroup HighNonGroup HNG index type148 Comparison to other indexes Optimizing performance for ad hoc joinsHighGroup index is also appropriate for an HNG column 149 150 Selecting an indexCriteria to identify Index to select Adding column indexes after inserting data Using join indexesJoin indexes improve query performance 151 How join indexes are used for queries When a join becomes ad hocRelationships in join indexes Join hierarchy overview 153 Columns in the join index One-to-many relationship Join hierarchy in query resolution154 Custid Lname 155 156 Multiple table joins and performance 157 Steps in creating a join index Privileges needed to create a join index Synchronizing join indexes158 Performance hints for synchronization Defining join relationships between tables159 Examples of join relationships in table definitions Using foreign references160 Specifying relationships when creating a join index Specifying the join type when creating a join index161 Issuing the Create Join Index statement Parameters of this command are162 163 Types of join hierarchies Creating a join index in Sybase Central164 To add a join index in Sybase Central Star joins Linear joins165 Another table You can create this table using the following commands166 167 Modifying tables included in a join index 168 Inserting or deleting from tables in a join index Estimating the size of a join index Table versioning controls access to join indexes169 170 Estimating the size of a join index Import and export methods Import and export overviewDatabases 171 Import and export overview Input and output data formats172 File Format Description Permissions for modifying data Scheduling database updatesSpecifying an output format for Interactive SQL Moving Data In and Out of Databases Exporting data from a database Using output redirectionExporting data from a database 174 Null value output Bulk loading data using the Load Table statement175 176 Bulk loading data using the Load Table statementFormat ’ascii’ ’binary’ Delimited by string Quotes on OFF Escapes on OFF Escape Character character Binary with Null Byte Prefix 1 2 4 ’delimiter-string’ 177 178 179 180 Block Factor number Block Size number 181Preview on OFF ROW Delimited by ’delimiter-string’ Start ROW ID number Specifies 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 example Specifies the maximum number of rows to insert into Table. The default is 0 for no limitOptions work together 183 184 Carriage return \x0d Following Windows NT example sets the column delimiter forWith the Limit option, and takes precedence over it 185 186 Memory message Interpreting notification messagesThis message displays memory usage information 187 IQ Temporary Store blocks message Main IQ Store blocks messagesMain buffer cache activity message 188 Controlling message logging Temporary buffer cache messageThese 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 Complete 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 table Inserting selected rows from the database Inserting from a different database192 Lineitem table in the current database Issue a Commit to commit the insert193 To insert data directly from Adaptive Server Enterprise 194 Inserting into tables of a join index Importing data interactively195 Inserting into primary and foreign key columns Inserting into primary and foreign key columns196 197 Partial-width insertions Partial-width insertions Partial-width insertion rules198 Additional columns QueryInstead of the correct number 199 200 Start ROW ID option in the second Load Table statement 201 Index Converting data on insertion Converting data on insertion202 203 Load conversion options Inserting data from pre-Version 12 Adaptive Server IQ204 Conversion options for loading from flat files 205 Data conversions in IQ IQ conversions for comparison operations 206 IQ conversions for arithmetic operations 207 Using the Ascii conversion option Column width issues208 209 Substitution of Null or blank characters Date OptionSpecifying the Date Format 210 211 Datetime conversion option Setting Year specified as Years assumed212 213 Specifying the format for Datetime conversions Formatting times 21410 Datetime format options Working With Nulls Where Blanks indicates that blanks convert to Nulls215 Other factors affecting the display of data Other factors affecting the display of data216 Unsupported Adaptive Server Enterprise data types Matching Adaptive Server Enterprise data types217 Matching Adaptive Server Enterprise data types Adaptive Server Enterprise data type equivalents218 Adaptive Server Adaptive Server IQ Enterprise Datatype 21913 Character data types Adaptive Server Enterprise Datatype IQ Datatype 220 Handling conversion errors on data import15 DATE/TIME data types Improving load performance during database definition Tuning bulk loading of dataOptimizing for the number of distinct values Creating indexes Adjusting your environment at load time Setting server startup optionsTuning bulk loading of data Adding dbspaces 223 Reducing Main IQ Store space use in incremental loads Changing data using Update Changing data using Update224 225 Deleting data Importing data by replication Importing data by replication226 227 228 Adaptive Server IQ Overview of proceduresStatements, are also available in batches 229 Introduction to procedures Benefits of proceduresBenefits of procedures 230 Using Procedures and Batches Creating procedures231 Dropping procedures Calling proceduresIntroduction to procedures 232 Returning procedure results in parameters Permissions to execute procedures233 234 Returning procedure results in result setsEmployee ID Salary Creating user-defined functions Introduction to user-defined functions235 Introduction to user-defined functions Calling user-defined functions236 Fullname empfname, emplname Permissions to execute user-defined functions Dropping user-defined functionsFollowing statement revokes permission to use the function 237 Introduction to batches Introduction to batches238 239 Control statementsDbisql and batches Control statements Using compound statements240 Control statement Syntax 241 Declarations in compound statements 242 Atomic compound statements SQL statements allowed in procedures Structure of procedures243 Structure of procedures Declaring parameters for procedures244 Passing parameters to functions Passing parameters to proceduresOr as follows 245 Returning a value using the Return statement Returning results from proceduresReturning results from procedures 246 247 Returning results as procedure parameters Show 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 environment 249 Returning result sets from procedures Returning variable result sets from procedures Returning multiple result sets from proceduresProcedure 250 251 Using cursors in procedures Cursor management overview Using cursors in proceduresCursor positioning 252 253 Using cursors on Select statements in procedures 254 255 Errors and warnings in procedures Errors and warnings in procedures Default error handling in procedures256 257 258 Error handling with on Exception Resume On Exception Resume Begin 259 260 Default handling of warnings in procedures 261 Using exception handlers in procedures 262 263 Nested compound statements and exception handlers Following statement executes the InnerProc procedure Using the Execute Immediate statement in procedures264 Some tips for writing procedures Transactions and savepoints in proceduresCheck if you need to change the command delimiter 265 Use fully-qualified names for tables in procedures Remember to delimit statements within your procedureSpecifying dates and times in procedures Some tips for writing procedures Statements allowed in batches Verifying procedure input arguments267 Using Select statements in batches Calling external libraries from procedures268 269 Creating procedures and functions with external calls Following form for Microsoft Visual C++ External function declarations270 271 How parameters are passed to the external function 272 Special considerations when passing character types How data can become invalid Data integrity overview273 Data integrity overview Integrity constraints belong in the database274 Data integrity tools How database contents get changed 276 SQL statements for implementing integrity constraints Using Unique constraints on columns or tables Using table and column constraints277 Using Check conditions on columns Using IQ Unique constraint on columnsUsing table and column constraints 278 Option Column Check conditions from user-defined data types279 Working with column constraints in Sybase Central Using Check conditions on tablesModifying and deleting Check conditions 280 281 Declaring entity and referential integrity If a client application breaches entity integrity Enforcing entity integrity282 Declaring referential integrity Primary keys enforce entity integrity283 Referential integrity is unenforced How you define foreign keys284 285 Integrity rules in the system tables 286 Integrity rules in the system tables Overview of transactions and versioning Performance and other aspects of database administrationIntroduction to transactions Transactions are logical units of work Starting transactions Using transactionsCompleting transactions Overview of transactions and versioning Committing a transaction writes data to disk Options in DbisqlTransactions and Versioning 289 Subdividing transactions Introduction to concurrencyHow concurrency works in IQ 290 Why concurrency benefits you Concurrency for backupsIntroduction to versioning Concurrency and IQ Multiplex One writer and multiple readers at the table level Table-level versioning292 Only one writer at a time 293 294 Multiple writers and readers in a database 295 Transactions use committed data 296 Timing of commits on read transactions affects versions 297 Hold cursors span transactions Versioning of temporary tables How Adaptive Server IQ keeps track of versions298 How locking works Versioning prevents inconsistenciesLocks for DML operations 299 How locking works Locks for DDL operations300 301 Primary keys and locking Isolation levelsIsolation levels 302 303 Checkpoints, savepoints, and transaction rollback When checkpoints occur Checkpoints aid in recoveryCheckpoints Checkpoints, savepoints, and transaction rollback Releasing savepoints Savepoints within transactions305 Automatic and user-defined savepoints Rolling back to a savepointNaming and nesting savepoints Following scenario Rolling back transactions System recoveryWhat causes a rollback Effect of rollback 308 How transaction information aids recovery 309 Performance implications Performance implications Overlapping versions and deletions310 311 Cursors in transactions Cursor sensitivity Cursors and versioningCursor scrolling Cursors in transactions Hold cursors Cursor command syntax and examplesPositioned operations Controlling message logging for cursors 314 Installation to handle international language issues Adaptive Server IQ international featuresIntroduction to international languages and character sets 315 Character set questions and answers Using the default collationIntroduction to international languages and character sets 316 Pieces in the character set puzzle Understanding character sets in softwareInternational Languages and Character Sets 317 318 Language issues in client/server computingDatabase server software messages Applications can cause 319 Code pages in Windows and Windows NT 320 Ansi and OEM code pages in Windows and Windows NT 321 Multibyte character sets International aspects of case sensitivity Sorting characters using collationsFirst-byte collation orderings for multibyte character sets 322 Introduction to locales Understanding localesCase insensitivity of identifiers 323 Understanding locales Understanding the locale language324 325 Understanding the locale character setAlternative Language label Label ISO639 language code 326 Equivalent Iana labels and a descriptionCharacter set Label Iana label Description 327 Understanding collations Setting the Sqllocale environment variableUnderstanding the locale collation label Displaying collations Following collations are supplied with Adaptive Server IQ Supplied collations329 Collation Label Type Description 330 331 Ansi or OEM? 332 333 334 335 Understanding character set translation Using multibyte collationsCharacter translation for database messages Understanding character set translation To use character translation for database messages 337 Avoiding character-set translation Connection strings and character sets338 339 Collation internalsLanguage Character set Title line Comment linesCollation internals 340 Collation sequence section Specification DescriptionFollowing are some sample lines for a collation 341 That will be sorted together Encodings sectionFollowing is part of the Shift-JIS collation file 342 343 Properties section Configuring your character set environment Finding the default collationTo configure your character set environment International language and character set tasks Determining locale information You can determine locale information using system functions345 To determine the locale of a database server Creating a database with a named collation Setting localesYour current machine 346 347 List the supplied collation sequences Using Odbc code page translation Starting a database server using character set translationTo enable character-set translation on a database server Or on Unix Creating a custom collation Character set translation for Sybase Central and DbisqlSet the Dbisql option CHAROEMTranslation to a value of OFF 349 350 Creating a database with a custom collation Compatibility issues351 To create a database with a custom collation Performance issues Performance issues352 Manage user IDs An overview of database permissions353 An overview of database permissions DBA authority overview354 Ownership permissions overview Resource authority overviewTable and views permissions overview Managing User IDs and Permissions Group permissions overview Managing individual user IDs and permissions356 With the following command Changing a passwordCreating new users 357 358 Granting DBA and resource authority 359 Granting permissions on tables and views Context of groups in Permissions of groups Granting users the right to grant permissions360 To grant user permission on tables in Sybase Central To grant user permissions on procedures in Sybase Central Granting permissions on procedures361 362 Revoking user permissions Managing groups To create a group with a name and passwordCreating groups 363 Managing groups Granting group membership to users364 To create a group in Sybase Central Referring to tables owned by groups Permissions of groups365 366 Groups without passwords Special groups Database object names and prefixes367 368 Database object names and prefixes Following command will now work Using views and procedures for extra security369 Using views and procedures for extra security Using views for tailored security370 Grant permission for the Sales Manager to examine this view Using procedures for tailored security371 Managing the resources connections use How user permissions are assessedHow user permissions are assessed Be modified is strictly defined 373 Temporary Store Users and permissions in the system tables Users and permissions in the system tablesLimits the number of prepared 374 Views Default Contents Default Contents375 376 377 Backup protects your data Data in backups Types of backupsBacking up your database Backing up your database Backup and Data Recovery Transaction log in backup, restore, and recovery379 Disk backup requirements Distribution of backup dataTape backup requirements Selecting archive devices Obtaining DBA privileges Preparing for backupRewinding tapes 381 Two ways to run Backup Retaining old disk backupsYou can run Backup in two ways Estimating Media Capacity Backup statement Concurrency and backupsTo back up an IQ database, use the following syntax 383 Specifying operator presence Specifying the type of backupSpecifying archive devices 384 385 386 387 Other backup options Backup ExamplesWaiting for Tape Devices 388 Checking for backup space Recovery from errors during backupRecovery attempts 389 Performing backups with non-Sybase products After you complete a backupDo not specify the Stacker or Size parameters 390 Shutting down the database Performing system-level backups391 Performing system-level backups Restoring from a system-level backupBacking up the right files 392 393 Validating your databaseFor this size database It takes about this long Validating your database Interpreting results394 Orphaned block statistic Meaning 395 Concurrency issues for spiqcheckdb Restoring your databases Before you restoreRestoring your databases 396 397 Restore accommodates dbspace changes Restoring tape backup files Restoring disk backup filesSpecifying files for an incremental restore Keeping the database unchanged between restores Restoring from a compatible backup Restore statementTo restore a database, use the following syntax 399 400 Moving database files 401 402 Restoring in the correct order Adjusting data sources and configuration filesDisplaying header information 403 Set must be restored first, and must be in the first device Set, and the restore order, are as follows404 Dblog command-line utility Renaming the transaction log after you restoreSwitch Description 405 Restore requires exclusive write access Validating the database after you restoreTransaction log utility options Maintaining a transaction log or mirror 407 Displaying header information Using Symbolic Links Unix Only Recovery from errors during restore408 409 Unattended backup Locating the backup log Getting information about backups and restoresGetting information about backups and restores 410 411 Content of the backup log Viewing the backup log in Sybase Central Maintaining the backup logRecording dbspace names 412 413 Determining your data backup and recovery strategy Determining the type of backup Scheduling routine backupsDetermining your data backup and recovery strategy 414 Improving performance for backup and restore Designating Backup and Restore ResponsibilitiesIncreasing the number of archive devices 415 Increasing memory used during backup Spooling backup dataEliminating data verification Balancing system load 417 Controlling the size of the Catalog Store 418 Your hardware and software configuration DBA can tune performance by adjusting resource usageIntroduction to performance terms Designing for performance Paging increases available memory Overview of memory useOverview of memory use 420 Server memory Utilities to monitor swappingManaging System Resources 421 Determining the sizes of the buffer caches Managing buffer caches422 Adaptive Server IQ memory overhead Operating system and other applications423 424 Multi-user database accessRaw partitions versus file systems Memory requirements for loads 425 Adaptive Server IQ main and temp buffer cachesBuffer caches and physical memory 426 Example of setting buffer cache sizesOther considerations Memory available for buffer caches Example Methods of adjusting buffer cache sizes Setting buffer cache sizes427 428 Setting buffer cache size database optionsTo change the buffer cache sizes permanently Setting the page size Setting buffer cache size server switchesNormally you change the buffer cache sizes by setting Specifying page size 430 Block size Adjust blocking factor for loads Decrease buffer cache settingsSaving memory Data compression Actively using the database IQ command line option changesOptimizing for large numbers of users Use the following guideline to determine Block Factor IQ Temp space Command and set rlimfdmax=4096 in /etc/systemSystem parameters 433 434 Platform-specific memory options Physical size of memory on the system Recommendations for small memory configurationsOptions that can provide more memory 435 436 Managing large buffer caches on HP You must then restart the server with the following commandControlling file system buffering 437 Other ways to get more memory Options for Java-enabled databases438 439 Process threading model IQ options for managing thread usage Insufficient threads errorProcess threading model 440 Raw I/O on Unix operating systems Balancing I/O441 Setting up disk striping on Unix Using disk stripingSetting up disk striping on Windows NT Balancing I/O Recommendations for disk striping Internal striping443 Disk striping hints Disk striping option444 Temporary data with the Create Dbspace command Using multiple dbspacesTransaction log file Across multiple disks Message log Strategic file locations446 447 Working space for inserting, deleting, and synchronizing Options for tuning resource use Options for tuning resource useRestricting concurrent queries 448 Limiting queries by rows returned Limiting a query’s memory useForcing cursors to be non-scrolling 449 Limiting the number of statements Limiting the number of cursorsLowering a connection’s priority Prefetching cache pages Other ways to improve resource use Restricting database accessOptimizing for typical usage Disk caching Indexing tips Using RAM diskPicking the right index type Indexing tips Allowing enough disk space for deletions Using join indexes453 Managing the size of your database Managing database size and structureDenormalizing for performance Managing database size and structure Disadvantages of denormalization Denormalization has risksPerformance benefits of denormalization 455 Deciding to denormalize Improving your queriesTips for structuring queries Improving your queries Query evaluation options Planning queries457 458 Setting query optimization options Improving large data transfers Network performance459 Network performance Isolate heavy network users460 461 Put small amounts of data in small packets 462 Put large amounts of data in large packets Filter as much data as possible at the server level Process at the server level463 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 System 466 Viewing the Adaptive Server IQ environment Monitoring the buffer caches Monitoring and Tuning PerformanceStarting the buffer cache monitor 467 468 Monitoring the buffer caches 469 470 471 Examining and saving monitor results Stopping the buffer cache monitor472 Into dummytablename Stop Monitor Buffer cache Examples of monitor results473 474 -contentionresults for the temp cache are -contentionresults for the main cache areResults for the memory manager are 475 Avoiding buffer manager thrashing Avoiding buffer manager thrashing476 Monitoring paging on Unix systems Monitoring paging on Windows NT systemsHere is an example 477 478 479 System utilities to monitor CPU use 480 System utilities to monitor CPU use Data server for client applications Client/server interfaces to Adaptive Server IQRestrictions for creating and running these applications 481 482 Client/server interfaces to Adaptive Server IQ Interfaces file Configuring IQ Servers with DseditAdaptive Server IQ as a Data Server 483 Opening a Directory Services session Using the Dsedit utilityStarting Dsedit 484 Adding or changing the server address Adding a server entryInterfaces file sql.ini Select a server entry in the Server box 486 Network Settings, in the Control Panel Renaming a server entry Verifying the server address487 Open Client applications and Adaptive Server IQ Sybase applications and Adaptive Server IQDeleting server entries 488 System requirements Setting up Adaptive Server IQ as an Open ServerStarting the database server as an Open Server 489 Setting up Adaptive Server IQ as an Open Server Configuring your database for use with Open Client490 491 Characteristics of Open Client and jConnect connectionsOption Set to To change the option settings for TDS connections Characteristics of Open Client and jConnect connections492 To connect to the livecredit server, use this syntax Servers with multiple databases493 494 495 Index 496 Index WINLATIN1 497 Datetime 498 Odbc Unix 499 500 DDL 501 Pipenotconnected 502 503 See Also Dbisql 504 505 506 Null 507 TCP/IP 508 See Also stored 509 Rawdetect 510 511 512 513 514 515 516

CHAPTER 3 Working with Database Objects

Choosing a block size for your database

IQ Store size is 8MB and the Temporary Store is 4MB.

Enabling Java in the database

113