User ManualTable of ContentsOverview of Adaptive Server IQ System Administration1Introduction to Adaptive Server IQ1System administration tasks2Security overview3Types of users4Granting permissions4Tools for system administration4The database server5Catalogs and IQ5The IQ Store6The Temporary Store6The Catalog Store6Concurrent operations7Stored procedures7Adaptive Server IQ stored procedures8Catalog stored procedures11System tables and views12Commands and Functions16Types of SQL statements16Functions16Message logging17The utility database18Compatibility with earlier versions19Running Adaptive Server IQ21Starting the database server21Server command lines22Starting the server on UNIX23Using the startup utility24Typing the server startup command25Starting the server on Windows NT26Starting the server from the NT Start menu26Typing the server startup command26Running the server outside the current session27Using command-line switches28Naming the server and databases31Controlling performance from the command line33Controlling permissions from the command line36Setting a maximum Catalog page size37Setting up a client/server environment38Starting a server in forced recovery mode40Starting a server from DBISQL40Starting multiple servers or clients on the same machine41Monitoring server activity41Stopping the database server43Who can stop the server?45Shutting down operating system sessions45Starting and stopping databases46Starting the asiqdemo database47Starting and stopping Sybase Central48Connecting a plug-in49Stopping Sybase Central50Introduction to connections50How connections are established51Connection parameters specify connections52Connection parameters are passed as connection strings52Connection parameters are passed as connection strings53Simple connection examples53Connecting to a database from DBISQL54Connecting to other databases from DBISQL56Connecting to an embedded database57Connecting using a data source59Connecting to a server on a network60Using default connection parameters61Connecting from Adaptive Server IQ utilities62Working with ODBC data sources63DSNs and FILEDSNs64Creating and editing ODBC data sources65Configuring ODBC data sources67Creating a File Data Source71Using ODBC data sources on UNIX72Connection parameters73Connection parameter priorities76How Adaptive Server IQ makes connections77Steps in establishing a connection77Locating the interface library78Assembling a list of connection parameters79Locating a server81Locating the database83Server name caching for faster connections84Interactive SQL connections85Connecting from other databases85Using an integrated login86Using integrated logins87Security concerns: unrestricted database access90Setting temporary public options for added security91Network aspects of integrated logins92Creating a default integrated login user92Troubleshooting startup, shutdown, and connections93What to do if you can’t start Adaptive Server IQ93What to do if you can’t connect to a database95Stopping a database server in an emergency (UNIX)96Resolving problems with your DBISQL window on UNIX96Working with Database Objects99Building Your Adaptive Server IQ Databases99Designing your database99Tools for working with database objects100A step-by-step overview of database setup101Extending data definition privileges103Selecting a device type104Allocating space for databases104Working with databases106Creating a database107Adding dbspaces114Dropping dbspaces116Dropping a database118Working with tables118Creating tables118Altering tables123Dropping tables124Creating primary and foreign keys125Table information in the system tables127Working with views127Creating views128Using views129Modifying views130Permissions on views130Deleting views131Views in the system tables131Working with indexes132Introduction to indexes132Creating indexes133Indexes in the system tables133Removing indexes134Adaptive Server IQ Indexes135Overview of indexes135Adaptive Server IQ index types135Benefits over traditional indexes137Creating Adaptive Server IQ indexes138The CREATE INDEX statement138Creating an index with Sybase Central139Creating indexes concurrently139Choosing an index type140Number of unique values in the index141Types of queries141Indexing criteria: disk space usage143Data types in the index143Combining index types144Adaptive Server IQ index types144Default column index145The Low_Fast (LF) index type145The High_Group (HG) index type146The High_Non_Group (HNG) index type148Optimizing performance for ad hoc joins149Selecting an index150Adding column indexes after inserting data151Using join indexes151Join indexes improve query performance151How join indexes are used for queries152Relationships in join indexes152When a join becomes ad hoc152Join hierarchy overview152Columns in the join index153The join hierarchy in query resolution154Multiple table joins and performance156Steps in creating a join index157Synchronizing join indexes158Defining join relationships between tables159Issuing the CREATE JOIN INDEX statement162Creating a join index in Sybase Central164Types of join hierarchies164Modifying tables included in a join index167Inserting or deleting from tables in a join index168Table versioning controls access to join indexes169Estimating the size of a join index169Moving Data In and Out of Databases171Import and export overview171Import and export methods171Input and output data formats172Permissions for modifying data173Scheduling database updates173Exporting data from a database174Using output redirection174NULL value output175Bulk loading data using the LOAD TABLE statement175Interpreting notification messages187Memory message187Main IQ Store blocks messages188IQ Temporary Store blocks message188Main buffer cache activity message188Temporary buffer cache message189Controlling message logging189Using the INSERT statement190Inserting specified values row by row190Inserting selected rows from the database191Inserting from a different database192Importing data interactively195Inserting into tables of a join index195Inserting into primary and foreign key columns196Partial-width insertions197Partial-width insertion rules198Converting data on insertion202Inserting data from pre-Version 12 Adaptive Server IQ204Load conversion options204Column width issues208Using the ASCII conversion option208The DATE Option210The DATETIME conversion option212Working With NULLS215Other factors affecting the display of data216Matching Adaptive Server Enterprise data types217Unsupported Adaptive Server Enterprise data types217Adaptive Server Enterprise data type equivalents218Handling conversion errors on data import220Tuning bulk loading of data221Improving load performance during database definition221Setting server startup options222Adjusting your environment at load time222Reducing Main IQ Store space use in incremental loads223Changing data using UPDATE224Deleting data225Importing data by replication226Using Procedures and Batches229Overview of procedures229Benefits of procedures230Introduction to procedures230Creating procedures231Calling procedures232Dropping procedures232Permissions to execute procedures233Returning procedure results in parameters233Returning procedure results in result sets234Introduction to user-defined functions235Creating user-defined functions235Calling user-defined functions236Dropping user-defined functions237Permissions to execute user-defined functions237Introduction to batches238Control statements239Using compound statements240Declarations in compound statements241Atomic compound statements242The structure of procedures243SQL statements allowed in procedures243Declaring parameters for procedures244Passing parameters to procedures245Passing parameters to functions245Returning results from procedures246Returning a value using the RETURN statement246Returning results as procedure parameters247Returning result sets from procedures249Returning multiple result sets from procedures250Returning variable result sets from procedures250Using cursors in procedures251Cursor management overview252Cursor positioning252Using cursors on SELECT statements in procedures253Errors and warnings in procedures255Default error handling in procedures256Error handling with ON EXCEPTION RESUME258Default handling of warnings in procedures260Using exception handlers in procedures261Nested compound statements and exception handlers263Using the EXECUTE IMMEDIATE statement in procedures264Transactions and savepoints in procedures265Some tips for writing procedures265Check if you need to change the command delimiter265Remember to delimit statements within your procedure266Use fully-qualified names for tables in procedures266Specifying dates and times in procedures266Verifying procedure input arguments267Statements allowed in batches267Using SELECT statements in batches268Calling external libraries from procedures268Creating procedures and functions with external calls269External function declarations270How parameters are passed to the external function271Special considerations when passing character types272Ensuring Data Integrity273Data integrity overview273How data can become invalid273Integrity constraints belong in the database274How database contents get changed275Data integrity tools275SQL statements for implementing integrity constraints276Using table and column constraints277Using UNIQUE constraints on columns or tables277Using IQ UNIQUE constraint on columns278Using CHECK conditions on columns278Working with column constraints in Sybase Central280Using CHECK conditions on tables280Modifying and deleting CHECK conditions280Declaring entity and referential integrity281Enforcing entity integrity282If a client application breaches entity integrity282Primary keys enforce entity integrity283Declaring referential integrity283How you define foreign keys284Referential integrity is unenforced284Integrity rules in the system tables285Transactions and Versioning287Overview of transactions and versioning287Introduction to transactions287Introduction to concurrency290Introduction to versioning291Versioning prevents inconsistencies299How locking works299Locks for DML operations299Locks for DDL operations300Primary keys and locking302Isolation levels302Checkpoints, savepoints, and transaction rollback303Checkpoints304Savepoints within transactions305Rolling back transactions307System recovery307How transaction information aids recovery308Performance implications309Overlapping versions and deletions310Cursors in transactions311Cursors and versioning312Cursor sensitivity312Cursor scrolling312Hold cursors313Positioned operations313Cursor command syntax and examples313Controlling message logging for cursors313International Languages and Character Sets315Introduction to international languages and character sets315Adaptive Server IQ international features315Using the default collation316Character set questions and answers316Understanding character sets in software317Pieces in the character set puzzle317Language issues in client/server computing318Code pages in Windows and Windows NT319Multibyte character sets321Sorting characters using collations322International aspects of case sensitivity322Understanding locales323Introduction to locales323Understanding the locale language324Understanding the locale character set325Understanding the locale collation label328Setting the SQLLOCALE environment variable328Understanding collations328Displaying collations328Supplied collations329ANSI or OEM?331Notes on ANSI collations332Notes on OEM collations334Using multibyte collations336Understanding character set translation336Character translation for database messages336Connection strings and character sets338Avoiding character-set translation338Collation internals339Comment lines340The title line340The collation sequence section341The Encodings section342The Properties section343International language and character set tasks344Finding the default collation344Configuring your character set environment344Determining locale information345Setting locales346Creating a database with a named collation346Using ODBC code page translation348Creating a custom collation349Creating a database with a custom collation351Compatibility issues351Performance issues352Managing User IDs and Permissions353An overview of database permissions353DBA authority overview354RESOURCE authority overview355Ownership permissions overview355Table and views permissions overview355Group permissions overview356Managing individual user IDs and permissions356Creating new users357Changing a password357Granting DBA and resource authority358Granting permissions on tables and views359Granting users the right to grant permissions360Granting permissions on procedures361Revoking user permissions362Managing groups363Creating groups363Granting group membership to users364Permissions of groups365Referring to tables owned by groups365Groups without passwords366Special groups367Database object names and prefixes367Using views and procedures for extra security369Using views for tailored security370Using procedures for tailored security371How user permissions are assessed372Managing the resources connections use372Users and permissions in the system tables374Backup and Data Recovery377Backup protects your data377Backing up your database378Types of backups378Selecting archive devices380Preparing for backup381Concurrency and backups383The BACKUP statement383Backup Examples388Recovery from errors during backup389After you complete a backup390Performing backups with non-Sybase products390Performing system-level backups391Shutting down the database391Backing up the right files392Restoring from a system-level backup392Validating your database393Interpreting results394Concurrency issues for sp_iqcheckdb395Restoring your databases396Before you restore396The RESTORE statement399Restoring in the correct order403Renaming the transaction log after you restore405Validating the database after you restore406Restore requires exclusive write access406Displaying header information407Recovery from errors during restore408Using Symbolic Links (UNIX Only)408Unattended backup409Getting information about backups and restores410Locating the backup log410Content of the backup log411Maintaining the backup log412Viewing the backup log in Sybase Central412Recording dbspace names412Determining your data backup and recovery strategy413Scheduling routine backups414Designating Backup and Restore Responsibilities415Improving performance for backup and restore415Managing System Resources419Introduction to performance terms419Designing for performance419Overview of memory use420Paging increases available memory420Utilities to monitor swapping421Server memory421Managing buffer caches422Determining the sizes of the buffer caches422Setting buffer cache sizes427Specifying page size429Saving memory431Optimizing for large numbers of users432Platform-specific memory options434Other ways to get more memory438The process threading model439Insufficient threads error440IQ options for managing thread usage440Balancing I/O441Raw I/O (on UNIX operating systems)441Using disk striping442Internal striping443Using multiple dbspaces445Strategic file locations446Working space for inserting, deleting, and synchronizing447Options for tuning resource use448Restricting concurrent queries448Limiting a query’s memory use449Limiting queries by rows returned449Forcing cursors to be non-scrolling449Limiting the number of cursors450Limiting the number of statements450Lowering a connection’s priority450Prefetching cache pages450Optimizing for typical usage451Other ways to improve resource use451Restricting database access451Disk caching451Using RAM disk452Indexing tips452Picking the right index type452Using join indexes453Allowing enough disk space for deletions453Managing database size and structure454Managing the size of your database454Denormalizing for performance454Denormalization has risks455Disadvantages of denormalization455Performance benefits of denormalization455Deciding to denormalize456Improving your queries456Tips for structuring queries456Planning queries457Setting query optimization options458Network performance459Improving large data transfers459Isolate heavy network users460Put small amounts of data in small packets461Put large amounts of data in large packets462Process at the server level463Monitoring and Tuning Performance465Viewing the Adaptive Server IQ environment465Getting information using stored procedures465Monitoring the buffer caches467Starting the buffer cache monitor467Stopping the buffer cache monitor472Examining and saving monitor results472Examples of monitor results473Avoiding buffer manager thrashing476Monitoring paging on Windows NT systems477Monitoring paging on UNIX systems477System utilities to monitor CPU use479Adaptive Server IQ as a Data Server481Client/server interfaces to Adaptive Server IQ481Configuring IQ Servers with DSEDIT483Sybase applications and Adaptive Server IQ488Open Client applications and Adaptive Server IQ488Setting up Adaptive Server IQ as an Open Server489System requirements489Starting the database server as an Open Server489Configuring your database for use with Open Client490Characteristics of Open Client and jConnect connections491Servers with multiple databases493Index495Size: 1.58 MBPages: 536Language: EnglishOpen manual
