Office Soon | free documents download

Administrator's Guide for IBM Informix Dynamic Server ...

Administrator’s Guidefor IBM Informix Dynamic
ServerVersion 9.3
August 2001
Part No. 000-8324iiAdministrator’s Guide for IBM Informix Dynamic Server© Copyright International Business Machines Corporation 2001. All rights reserved.TrademarksAIX; DB2;DB2 Universal Database; Distributed Relational Database Architecture;NUMA-Q;OS/2,OS/390, andOS/400;IBMInformix;C-ISAM; Foundation.2000TM;IBMInformix4GL;IBMInformixDataBladeModule;ClientSDKTM; CloudscapeTM; CloudsyncTM;IBM Informix Connect;IBM InformixDriver forJDBC; DynamicConnectTM;IBM InformixDynamic Scalable ArchitectureTM (DSA);IBM Informix Dynamic ServerTM;IBMInformix Enterprise Gateway Manager (Enterprise Gateway Manager);IBM InformixExtended ParallelServerTM; i. Financial ServicesTM; J/FoundationTM; MaxConnectTM; Object TranslatorTM; Red BrickDecision ServerTM;IBM InformixSE;IBM InformixSQL; InformiXMLTM; RedBack; SystemBuilderTM;U2TM;UniData; UniVerse; wintegrate are trademarks or registered trademarks of International BusinessMachines Corporation.Java and all Java-based trademarks and logos are trademarks or registered trademarks of Sun Microsystems,
Inc. in the United States and other countries.Windows, WindowsNT, and Excel are either registered trademarks or trademarks of Microsoft Corporation inthe United States and /or other countries.UNIXis a registered trademark in the United States and other countries licensed exclusively throughX/OpenCompany Limited.Other company, product, and service names used in this publication may be trademarks or service marks of
others.Documentation Team: Karen Goldman Smith, Diane Kirsten-Martin, Cynthia NewtonList of ChaptersList ofChaptersSection IThe Database ServerChapter 1Installing and Configuring the Database ServerChapter 2Configuration ParametersChapter 3Client/Server CommunicationsChapter 4Initializing the Database ServerSection IIDisk, Memory, and Process ManagementChapter 5Virtual Processors and ThreadsChapter 6Managing Virtual ProcessorsChapter 7Shared MemoryChapter 8Managing Shared MemoryChapter 9Data StorageChapter 10Managing Disk SpaceivAdministrator’s Guide for IBM Informix Dynamic ServerSection IIILogging and Log AdministrationChapter 11LoggingChapter 12Managing Database-Logging ModeChapter 13Logical LogChapter 14Managing Logical-Log FilesChapter 15Physical Logging, Checkpoints, and Fast RecoveryChapter 16Managing the Physical LogSection IVFault ToleranceChapter 17MirroringChapter 18Using MirroringChapter 19High-Availability Data ReplicationChapter 20Using High-Availability Data ReplicationChapter 21Consistency CheckingList of ChaptersvSection VDistributed DataChapter 22Multiphase Commit ProtocolsChapter 23Recovering Manually from Failed Two-Phase CommitviAdministrator’s Guide for IBM Informix Dynamic ServerTable of ContentsTable ofContentsIntroductionIn This Introduction. . . . . . . . . . . . . . . . .3About This Manual . . . . . . . . . . . . . . . . . .3Types of Users . . . . . . . . . . . . . . . . . .3Software Dependencies . . . . . . . . . . . . . . .4Assumptions About Your Locale. . . . . . . . . . . .5Demonstration Database. . . . . . . . . . . . . .5New Features in Dynamic Server, Version 9.3. . . . . . . .6Database Server Usability Enhancements . . . . . . . . .6Extensibility Enhancements . . . . . . . . . . . . .7Performance Enhancements . . . . . . . . . . . . .7SQL Enhancements . . . . . . . . . . . . . . . .8Other Significant Changes in Version 9.3 . . . . . . . . .8Features from Dynamic Server 9.21 . . . . . . . . . . .9Organizational Changes to This Manual Since Version 9.2. . . .9Documentation Conventions. . . . . . . . . . . . . .10Typographical Conventions . . . . . . . . . . . . .10Icon Conventions . . . . . . . . . . . . . . . . .11Sample-Code Conventions . . . . . . . . . . . . . .13Additional Documentation . . . . . . . . . . . . . . .14Related Reading . . . . . . . . . . . . . . . . . . .17Compliance with Industry Standards. . . . . . . . . . .17Informix Welcomes Your Comments . . . . . . . . . . . .17viiiAdministrator’s Guide for IBM Informix Dynamic ServerSection IThe Database ServerChapter 1Installing and Configuring the Database ServerIn This Chapter . . . . . . . . . . . . . . . . . . .1-5Planning for the Database Server . . . . . . . . . . . . .1-6Considering Your Priorities . . . . . . . . . . . . .1-6Considering Your Environment. . . . . . . . . . .1-7Configuring the Operating System . . . . . . . . . . . .1-8Configuring Windows Memory. . . . . . . . . . .1-8Modifying UNIX Kernel Parameters . . . . . . . . . .1-8Allocating Disk Space. . . . . . . . . . . . . . . .1-9Creating Chunk Files on UNIX . . . . . . . . . . . .1-9Providing NTFS Partitions in Windows . . . . . . . . .1-10Setting Permissions, Ownership, and Group . . . . . . .1-10Creating Standard Device Names . . . . . . . . . . .1-11Setting Environment Variables. . . . . . . . . . . . .1-11Setting GLS Environment Variables . . . . . . . . . .1-13Setting Environment Variables on UNIX . . . . . . . . .1-13Setting Environment Variables on Windows . . . . . . .1-14Configuring Connectivity . . . . . . . . . . . . . . .1-15The sqlhosts File on UNIX. . . . . . . . . . . . .1-15The sqlhosts Registry on Windows. . . . . . . . . . .1-16Configuring Connectivity Using ISA . . . . . . . . . .1-16Configuring the Database Server . . . . . . . . . . . . .1-17Preparing the ONCONFIG Configuration File. . . . . .1-17Using Server Setup in ISA to Customize YourConfiguration. . . . . . . . . . . . . . .1-19Using Informix Server Administrator to Update theONCONFIG File . . . . . . . . . . . . . .1-19Using the Instance Manager to Create a New DatabaseServer Instance . . . . . . . . . . . . . .1-20Configuring Java Support . . . . . . . . . . . . . .1-20Starting and Administering the Database Server. . . . . . .1-21Starting the Database Server and Initializing Disk Space . . .1-21Preparing for Automatic Startup . . . . . . . . . . .1-22Preparing to Connect to Applications. . . . . . . . .1-24Creating Storage Spaces and Chunks . . . . . . . . . .1-24Setting Up Your Backup System and Storage . . . . . . .1-25Table of ContentsixPerforming Routine Administrative Tasks . . . . . . . . .1-26Changing Database Server Modes . . . . . . . . . .1-26Backing Up Data and Logical-Log Files. . . . . . . .1-27Monitoring Database Server Activity . . . . . . . . .1-27Checking for Consistency . . . . . . . . . . . . .1-27Performing Additional Administrative Tasks . . . . . . . .1-28Using Mirroring. . . . . . . . . . . . . . . .1-28Managing Database-Logging Status . . . . . . . . . .1-28Managing the Logical Log . . . . . . . . . . . . .1-29Managing the Physical Log . . . . . . . . . . . . .1-29Managing Shared Memory . . . . . . . . . . . . .1-29Managing Virtual Processors . . . . . . . . . . . .1-30Managing Parallel Database Query . . . . . . . . . .1-30Using Data Replication . . . . . . . . . . . . . .1-31Using Auditing . . . . . . . . . . . . . . . . .1-31Using Distributed Queries . . . . . . . . . . . . .1-31Monitoring Database Server Activity . . . . . . . . . . .1-32Event Alarms. . . . . . . . . . . . . . . . .1-33Informix Server Administrator (ISA). . . . . . . . .1-33Message Log . . . . . . . . . . . . . . . . . .1-33ON-Monitor . . . . . . . . . . . . . . . . . .1-34oncheck Utility . . . . . . . . . . . . . . . . .1-35onperf Tool . . . . . . . . . . . . . . . . . .1-35onstat Utility . . . . . . . . . . . . . . . . . .1-35SMI Tables. . . . . . . . . . . . . . . . . .1-36System Console . . . . . . . . . . . . . . . . .1-36UNIX Operating-System Tools. . . . . . . . . . .1-36Windows Event Viewer . . . . . . . . . . . . . .1-37Windows Performance Monitor . . . . . . . . . . .1-37Windows NT and Windows 2000 Utilities. . . . . . . .1-39xAdministrator’s Guide for IBM Informix Dynamic ServerChapter 2Configuration ParametersIn This Chapter . . . . . . . . . . . . . . . . . . .2-3Database Server Identification Parameters. . . . . . . . .2-3Disk-Space Parameters . . . . . . . . . . . . . . . .2-4Root Dbspace . . . . . . . . . . . . . . . . . .2-4Mirror of Root Dbspace. . . . . . . . . . . . . .2-5Other Space-Management Parameters. . . . . . . . .2-5Logging Parameters . . . . . . . . . . . . . . . . .2-6Logical Log . . . . . . . . . . . . . . . . . . .2-6Physical Log Parameters . . . . . . . . . . . . . .2-7Backup and Restore Parameters. . . . . . . . . . . .2-8Message-Log Parameters. . . . . . . . . . . . . . .2-8Shared-Memory Parameters. . . . . . . . . . . . . .2-9Shared-Memory Size Allocation. . . . . . . . . . .2-9Shared-Memory Space Allocation . . . . . . . . . . .2-10Shared-Memory Buffer Control . . . . . . . . . . . .2-11SQL Statement Cache Usage . . . . . . . . . . . . .2-11Decision-Support Parameters . . . . . . . . . . . . . .2-12Database Server Process Parameters. . . . . . . . . . .2-12Virtual Processor Parameters. . . . . . . . . . . . .2-13Time Intervals . . . . . . . . . . . . . . . . . .2-14Restore Parameters. . . . . . . . . . . . . . . . .2-14High-Availability Data-Replication Parameters . . . . . . . .2-15Event-Alarm Parameters. . . . . . . . . . . . . . .2-15Dump Parameters . . . . . . . . . . . . . . . . . .2-16Specialized Parameters . . . . . . . . . . . . . . . .2-17Auditing Parameters. . . . . . . . . . . . . . .2-17Optical Media Parameters. . . . . . . . . . . . . .2-17UNIX Parameters. . . . . . . . . . . . . . . .2-18Monitoring Configuration Information. . . . . . . . . .2-18Table of ContentsxiChapter 3Client/Server CommunicationsIn This Chapter . . . . . . . . . . . . . . . . . .3-3Client/Server Architecture. . . . . . . . . . . . . .3-3Network Protocol . . . . . . . . . . . . . . . .3-4Network Programming Interface . . . . . . . . . . .3-5Windows Network Domain. . . . . . . . . . . .3-5Database Server Connection . . . . . . . . . . . .3-6Multiplexed Connection . . . . . . . . . . . . . .3-7Connections That the Database Server Supports . . . . . . .3-8Local Connections . . . . . . . . . . . . . . . . .3-10Shared-Memory Connections . . . . . . . . . . . .3-10Stream-Pipe Connections . . . . . . . . . . . . .3-11Named-Pipe Connections . . . . . . . . . . . . .3-11Local-Loopback Connections . . . . . . . . . . . .3-12Communication Support Services . . . . . . . . . . . .3-12Connectivity Files. . . . . . . . . . . . . . . . .3-13Network-Configuration Files . . . . . . . . . . . .3-13Network Security Files . . . . . . . . . . . . . .3-17Simple Password Communication Support Module . . . .3-19The sqlhosts File and the SQLHOSTS Registry Key. . . .3-22The sqlhosts Information . . . . . . . . . . . . . . .3-26Connectivity Information . . . . . . . . . . . . .3-27Group Information. . . . . . . . . . . . . . .3-42Alternatives for TCP/IP Connections . . . . . . . . .3-42ONCONFIG Parameters for Connectivity . . . . . . . . .3-47DBSERVERNAME Configuration Parameter. . . . . . .3-48DBSERVERALIASES Configuration Parameter . . . . . .3-48NETTYPE Configuration Parameter. . . . . . . . . .3-49Environment Variables for Network Connections. . . . . .3-50Examples of Client/Server Configurations . . . . . . . . .3-50Using a Shared-Memory Connection . . . . . . . . .3-51Using a Local-Loopback Connection. . . . . . . . .3-52Using a Network Connection . . . . . . . . . . . .3-53Using Multiple Connection Types. . . . . . . . . .3-54Accessing Multiple Database Servers . . . . . . . . .3-56Using Informix MaxConnect . . . . . . . . . . . . . .3-58xiiAdministrator’s Guide for IBM Informix Dynamic ServerChapter 4Initializing the Database ServerIn This Chapter . . . . . . . . . . . . . . . . . . .4-3Types of Initialization. . . . . . . . . . . . . . . .4-3Initializing Disk Space. . . . . . . . . . . . . . . .4-4Initialization Steps . . . . . . . . . . . . . . . . . .4-5Process Configuration File. . . . . . . . . . . . .4-6Create Shared-Memory Portions. . . . . . . . . . .4-7Initialize Shared-Memory . . . . . . . . . . . . . .4-8Initialize Disk Space. . . . . . . . . . . . . . . .4-8Start All Required Virtual Processors . . . . . . . . . .4-8Make Necessary Conversions. . . . . . . . . . . .4-9Initiate Fast Recovery . . . . . . . . . . . . . . .4-9Initiate a Checkpoint. . . . . . . . . . . . . . .4-9Document Configuration Changes . . . . . . . . . . .4-9Create the oncfg_servername.servernum File . . . . . . .4-10Drop Temporary Tblspaces . . . . . . . . . . . . .4-10Set Forced Residency If Specified . . . . . . . . . . .4-10Return Control to User . . . . . . . . . . . . . . .4-11Create sysmaster Database and Prepare SMI Tables . . . . .4-11Create the sysutils Database . . . . . . . . . . . . .4-12Monitor Maximum Number of User Connections. . . . .4-12Database Server Operating Modes . . . . . . . . . . . .4-13Changing Database Server Operating Modes. . . . . . . .4-14Users Permitted to Change Modes . . . . . . . . . . .4-15ISA Options for Changing Modes . . . . . . . . . . .4-16On-Monitor Options for Changing Modes . . . . . . . .4-16Command-Line Options for Changing Modes . . . . . . .4-17Table of ContentsxiiiSection IIDisk, Memory, and Process ManagementChapter 5Virtual Processors and ThreadsIn This Chapter . . . . . . . . . . . . . . . . . .5-3Virtual Processors. . . . . . . . . . . . . . . . .5-3Threads. . . . . . . . . . . . . . . . . . .5-4Types of Virtual Processors . . . . . . . . . . . . .5-6Advantages of Virtual Processors. . . . . . . . . .5-9How Virtual Processors Service Threads. . . . . . . . .5-13Control Structures . . . . . . . . . . . . . . . .5-14Context Switching . . . . . . . . . . . . . . . .5-14Stacks . . . . . . . . . . . . . . . . . . . .5-16Queues. . . . . . . . . . . . . . . . . . . .5-17Mutexes . . . . . . . . . . . . . . . . . . .5-19Virtual-Processor Classes . . . . . . . . . . . . . . .5-20CPU Virtual Processors . . . . . . . . . . . . . .5-20User-Defined Classes of Virtual Processors . . . . . . .5-25Java Virtual Processors . . . . . . . . . . . . . .5-27Disk I/O Virtual Processors. . . . . . . . . . . .5-28Network Virtual Processors. . . . . . . . . . . .5-33Communications Support Module Virtual Processor . . . .5-40Optical Virtual Processor. . . . . . . . . . . . .5-40Audit Virtual Processor . . . . . . . . . . . . . .5-41Miscellaneous Virtual Processor . . . . . . . . . . .5-41Chapter 6Managing Virtual ProcessorsIn This Chapter . . . . . . . . . . . . . . . . . .6-3Setting Virtual-Processor Configuration Parameters. . . . .6-3Setting Virtual-Processor Parameters with a Text Editor . . .6-4Setting Virtual-Processor Parameters with ISA . . . . . .6-5Setting Virtual-Processor Parameters with ON-Monitor . . .6-5Starting and Stopping Virtual Processors. . . . . . . . .6-6Adding Virtual Processors in Online Mode . . . . . . .6-6Dropping CPU and User-Defined Virtual Processors . . . .6-8Monitoring Virtual Processors . . . . . . . . . . . . .6-9Monitoring Virtual Processors with Command-LineUtilities . . . . . . . . . . . . . . . .6-9Monitoring Virtual Processors with SMI Tables . . . . . .6-12xivAdministrator’s Guide for IBM Informix Dynamic ServerChapter 7Shared MemoryIn This Chapter . . . . . . . . . . . . . . . . . . .7-5Shared Memory. . . . . . . . . . . . . . . . . .7-5Shared-Memory Use . . . . . . . . . . . . . . . . .7-6Shared-Memory Allocation . . . . . . . . . . . . .7-7Shared-Memory Size. . . . . . . . . . . . . . .7-9Action to Take If SHMTOTAL Is Exceeded . . . . . . . .7-10Processes That Attach to Shared Memory . . . . . . . . . .7-11How a Client Attaches to the Communications Portion. . .7-11How Utilities Attach to Shared Memory . . . . . . . . .7-12How Virtual Processors Attach to Shared Memory . . . . .7-12Resident Shared-Memory Segments . . . . . . . . . . . .7-17Resident Portion of Shared Memory. . . . . . . . . . .7-17Shared-Memory Header . . . . . . . . . . . . . .7-18Shared-Memory Buffer Pool . . . . . . . . . . . . .7-18Logical-Log Buffer . . . . . . . . . . . . . . . .7-21Physical-Log Buffer . . . . . . . . . . . . . . . .7-23High-Availability Data-Replication Buffer . . . . . . . .7-23Lock Table . . . . . . . . . . . . . . . . . . .7-24Virtual Portion of Shared Memory . . . . . . . . . . . .7-25Management of the Virtual Portion of Shared Memory . . . .7-25Components of the Virtual Portion of Shared Memory . . . .7-26Data-Distribution Cache . . . . . . . . . . . . . .7-32Communications Portion of Shared Memory. . . . . . . .7-35Virtual-Extension Portion of Shared Memory. . . . . . . .7-35Concurrency Control . . . . . . . . . . . . . . . . .7-36Shared-Memory Mutexes . . . . . . . . . . . . . .7-36Shared-Memory Buffer Locks. . . . . . . . . . . .7-37Database Server Thread Access to Shared Buffers . . . . . . .7-38LRU Queues . . . . . . . . . . . . . . . . . .7-38Configuring the Database Server to Read Ahead . . . . . .7-43Database Server Thread Access to Buffer Pages . . . . . .7-44Flushing Data to Disk. . . . . . . . . . . . . . . .7-44Flushing Buffer-Pool Buffers . . . . . . . . . . . . .7-45Flushing Before-Images First . . . . . . . . . . . . .7-45Flushing the Physical-Log Buffer . . . . . . . . . . .7-45Synchronizing Buffer Flushing . . . . . . . . . . . .7-46Describing Flushing Activity. . . . . . . . . . . . .7-46Flushing the Logical-Log Buffer. . . . . . . . . . . .7-48Table of ContentsxvBuffering Large-Object Data . . . . . . . . . . . . . .7-50Writing Simple Large Objects . . . . . . . . . . . .7-50Accessing Smart Large Objects. . . . . . . . . . .7-52Memory Use on 64-Bit Platforms . . . . . . . . . . . .7-54Chapter 8Managing Shared MemoryIn This Chapter . . . . . . . . . . . . . . . . . .8-3Setting Operating-System Shared-Memory ConfigurationParameters. . . . . . . . . . . . . . . .8-4Maximum Shared-Memory Segment Size . . . . . . . .8-5Shared-Memory Lower-Boundary Address . . . . . . .8-6Semaphores . . . . . . . . . . . . . . . . . .8-6Setting Database Server Shared-Memory ConfigurationParameters. . . . . . . . . . . . . . . .8-7Setting Parameters for Resident Shared Memory . . . . .8-7Setting Parameters for Virtual Shared Memory . . . . . .8-8Setting Parameters for Shared-Memory Performance . . . .8-9Setting Shared-Memory Parameters with a Text Editor . . .8-10Setting Shared-Memory Parameters with ISA . . . . . .8-10Setting Shared-Memory Parameters with ON-Monitor . . .8-11Setting SQL Statement Cache Parameters. . . . . . . . .8-12Reinitializing Shared Memory . . . . . . . . . . . . .8-13Turning Residency On or Off for Resident Shared Memory . . .8-13Turning Residency On or Off in Online Mode . . . . . .8-13Turning Residency On or Off When Restarting theDatabase Server. . . . . . . . . . . . .8-14Adding a Segment to the Virtual Portion of Shared Memory . . .8-14Monitoring Shared Memory . . . . . . . . . . . . . .8-15Monitoring Shared-Memory Segments . . . . . . . . .8-15Monitoring the Shared-Memory Profile and Latches . . . .8-15Monitoring Buffers. . . . . . . . . . . . . . .8-17Monitoring Buffer-Pool Activity . . . . . . . . . . .8-21xviAdministrator’s Guide for IBM Informix Dynamic ServerChapter 9Data StorageIn This Chapter . . . . . . . . . . . . . . . . . . .9-5Physical and Logical Units of Storage . . . . . . . . . . .9-5Chunks. . . . . . . . . . . . . . . . . . . . .9-6Disk Allocation for Chunks . . . . . . . . . . . . .9-7Offsets . . . . . . . . . . . . . . . . . . . .9-9Pages. . . . . . . . . . . . . . . . . . . . . .9-10Blobpages . . . . . . . . . . . . . . . . . . . . .9-11Sbpages. . . . . . . . . . . . . . . . . . . . .9-13Extents . . . . . . . . . . . . . . . . . . . . . .9-14Dbspaces . . . . . . . . . . . . . . . . . . . . .9-16Control of Where Data Is Stored. . . . . . . . . . .9-16Root Dbspace . . . . . . . . . . . . . . . . . .9-18Temporary Dbspaces . . . . . . . . . . . . . . .9-19Blobspaces. . . . . . . . . . . . . . . . . . . .9-20Sbspaces . . . . . . . . . . . . . . . . . . . . .9-21Advantages of Using Sbspaces . . . . . . . . . . . .9-21Sbspaces and Enterprise Replication . . . . . . . . . .9-22Metadata, User Data, and Reserved Area. . . . . . . .9-22Control of Where Data Is Stored. . . . . . . . . . .9-23Storage Characteristics of Sbspaces. . . . . . . . . .9-25Levels of Inheritance for Sbspace Characteristics . . . . . .9-29More Information About Sbspaces . . . . . . . . . . .9-30Temporary Sbspaces . . . . . . . . . . . . . . . . .9-32Comparison of Temporary and Standard Sbspaces . . . . .9-33Temporary Smart Large Objects. . . . . . . . . . . .9-34Extspaces . . . . . . . . . . . . . . . . . . . . .9-35Databases . . . . . . . . . . . . . . . . . . . . .9-35Tables . . . . . . . . . . . . . . . . . . . . . .9-37Table Types for Dynamic Server . . . . . . . . . . . . .9-39Standard Permanent Tables . . . . . . . . . . . . .9-40RAW Tables. . . . . . . . . . . . . . . . . .9-40Temp Tables. . . . . . . . . . . . . . . . . .9-41Properties of Table Types . . . . . . . . . . . . . .9-41Temporary Tables. . . . . . . . . . . . . . . .9-43Tblspaces . . . . . . . . . . . . . . . . . . . . .9-46Maximum Number of Tblspaces in a Table . . . . . . . .9-47Table and Index Tblspaces. . . . . . . . . . . . .9-47Extent Interleaving . . . . . . . . . . . . . . . .9-49Table of ContentsxviiTable Fragmentation and Data Storage . . . . . . . . . .9-50Amount of Disk Space Needed to Store Data . . . . . . . .9-50Size of the Root Dbspace. . . . . . . . . . . . .9-50Amount of Space That Databases Require. . . . . . . .9-53Disk-Layout Guidelines. . . . . . . . . . . . . . .9-53Dbspace and Chunk Guidelines . . . . . . . . . . .9-54Table-Location Guidelines . . . . . . . . . . . . .9-55Sample Disk Layouts. . . . . . . . . . . . . . . .9-56Logical-Volume Manager . . . . . . . . . . . . . . .9-61Chapter 10Managing Disk SpaceIn This Chapter . . . . . . . . . . . . . . . . . .10-5Allocating Disk Space . . . . . . . . . . . . . . . .10-6Specifying an Offset . . . . . . . . . . . . . . .10-6Allocating Cooked File Spaces on UNIX . . . . . . . .10-8Allocating Raw Disk Space on UNIX . . . . . . . . .10-9Creating Symbolic Links to Raw Devices . . . . . . . .10-10Allocating NTFS File Space on Windows . . . . . . . .10-10Allocating Raw Disk Space on Windows . . . . . . . .10-11Specifying Names for Storage Spaces and Chunks . . . . . .10-12Specifying the Maximum Size of Chunks . . . . . . . .10-13Specifying the Maximum Number of Chunks andStorage Spaces . . . . . . . . . . . . . .10-13Backing Up After You Change the Physical Schema . . . . . .10-13Managing Dbspaces. . . . . . . . . . . . . . . .10-14Creating a Dbspace. . . . . . . . . . . . . . .10-14Creating a Temporary Dbspace . . . . . . . . . . .10-16What to Do If You Run Out of Disk Space . . . . . . . .10-17Adding a Chunk to a Dbspace or Blobspace . . . . . . .10-17Managing Blobspaces . . . . . . . . . . . . . . . .10-19Creating a Blobspace . . . . . . . . . . . . . . .10-19Preparing Blobspaces to Store TEXT and BYTE Data . . . .10-21Determining Blobpage Size. . . . . . . . . . . . .10-21Managing Sbspaces . . . . . . . . . . . . . . . . .10-23Creating an Sbspace . . . . . . . . . . . . . . .10-23Sizing Sbspace Metadata. . . . . . . . . . . . .10-25Adding a Chunk to an Sbspace . . . . . . . . . . .10-25Altering Storage Characteristics of Smart Large Objects . . .10-26Creating a Temporary Sbspace. . . . . . . . . . .10-27xviiiAdministrator’s Guide for IBM Informix Dynamic ServerDropping a Chunk. . . . . . . . . . . . . . . . . 10-28Verifying Whether a Chunk Is Empty . . . . . . . . . . 10-29
Dropping a Chunk from a Dbspace with onspaces . . . . . 10-29
Dropping a Chunk from a Blobspace . . . . . . . . . . 10-29
Dropping a Chunk from an Sbspace with onspaces . . . . . 10-30Dropping a Storage Space . . . . . . . . . . . . . . . 10-31Preparing to Drop a Storage Space . . . . . . . . . . . 10-31
Dropping a Mirrored Storage Space . . . . . . . . . . 10-32
Dropping a Storage Space with onspaces. . . . . . . . 10-32Dropping a Dbspace or Blobspace with ON-Monitor . . . . 10-33
Backing Up After Dropping a Storage Space . . . . . . . 10-33Managing Extspaces . . . . . . . . . . . . . . . . . 10-34Creating an Extspace. . . . . . . . . . . . . . . 10-34Dropping an Extspace . . . . . . . . . . . . . . . 10-35Skipping Inaccessible Fragments . . . . . . . . . . . . . 10-35Using the DATASKIP Configuration Parameter . . . . . . 10-35
Using the Dataskip Feature of onspaces . . . . . . . . . 10-36
Using onstat to Check Dataskip Status . . . . . . . . . 10-36
Using the SQL Statement SET DATASKIP . . . . . . . . 10-36
Effect of the Dataskip Feature on Transactions . . . . . . . 10-37
Determining When to Use Dataskip . . . . . . . . . . 10-38
Monitoring Fragmentation Use . . . . . . . . . . . . 10-39Displaying Databases. . . . . . . . . . . . . . . . 10-40Using SMI Tables . . . . . . . . . . . . . . . . . 10-40
Using ISA . . . . . . . . . . . . . . . . . . . 10-40
Using ON-Monitor . . . . . . . . . . . . . . . . 10-40Monitoring Disk Usage . . . . . . . . . . . . . . . . 10-41Monitoring Chunks . . . . . . . . . . . . . . . . 10-41
Monitoring Tblspaces and Extents . . . . . . . . . . . 10-48
Monitoring Simple Large Objects in a Blobspace . . . . . . 10-49
Monitoring Sbspaces. . . . . . . . . . . . . . . 10-53Loading Data Into a Table . . . . . . . . . . . . . . . 10-61Table of ContentsxixSection IIILogging and Log AdministrationChapter 11LoggingIn This Chapter . . . . . . . . . . . . . . . . . .11-3Database Server Processes That Require Logging. . . . . .11-3Transaction Logging. . . . . . . . . . . . . . . .11-6Logging of SQL Statements and Database Server Activity. . .11-6Activity That is Always Logged . . . . . . . . . . .11-7Activity Logged for Databases with Transaction Logging . .11-9Activity That is Not Logged. . . . . . . . . . . .11-10Database-Logging Status . . . . . . . . . . . . . . .11-11Unbuffered Transaction Logging . . . . . . . . . . .11-11Buffered Transaction Logging . . . . . . . . . . . .11-12ANSI-Compliant Transaction Logging . . . . . . . . .11-12No Database Logging. . . . . . . . . . . . . . .11-13Databases with Different Log-Buffering Status . . . . . .11-13Database Logging in an X/Open DTP Environment . . . .11-13Settings or Changes for Logging Status or Mode . . . . . . .11-14Chapter 12Managing Database-Logging ModeIn This Chapter . . . . . . . . . . . . . . . . . .12-3Changing Database-Logging Mode. . . . . . . . . . .12-4Modifying Database-Logging Mode with ondblog . . . . . .12-5Changing Buffering Mode with ondblog . . . . . . . .12-5Canceling a Logging Mode Change with ondblog . . . . .12-6Ending Logging with ondblog. . . . . . . . . . . .12-6Making a Database ANSI Compliant with ondblog . . . .12-6Changing the Logging Mode of an ANSI-Compliant Database12-6Modifying Database Logging Mode with ontape . . . . . . .12-7Turning On Transaction Logging with ontape . . . . . .12-7Ending Logging with ontape . . . . . . . . . . . .12-8Changing Buffering Mode with ontape. . . . . . . .12-8Making a Database ANSI Compliant with ontape . . . . .12-8Modifying Database Logging Mode with ISA . . . . . . . .12-9Modifying Database Logging Mode with ON-Monitor . . . . .12-9Modifying the Table-Logging Mode . . . . . . . . . . .12-10Altering a Table to Turn Off Logging. . . . . . . . .12-10Altering a Table to Turn On Logging. . . . . . . . .12-10xxAdministrator’s Guide for IBM Informix Dynamic ServerMonitoring Transactions. . . . . . . . . . . . . . . 12-11Monitoring the Logging Mode of a Database. . . . . . . . 12-11Monitoring the Logging Mode with SMI Tables . . . . . . 12-11
Monitoring the Logging Mode with ON-Monitor. . . . . 12-12Monitoring the Logging Mode with ISA . . . . . . . . . 12-12Chapter 13Logical LogIn This Chapter . . . . . . . . . . . . . . . . . . .13-3What Is the Logical Log?. . . . . . . . . . . . . . .13-3Location of Logical-Log Files . . . . . . . . . . . . . .13-4Identification of Logical-Log Files. . . . . . . . . . . .13-5Status Flags of Logical-Log Files . . . . . . . . . . . . .13-6Size of the Logical Log. . . . . . . . . . . . . . . .13-7Number of Logical-Log Files . . . . . . . . . . . . .13-7Performance Considerations . . . . . . . . . . . . .13-8Dynamic Log Allocation. . . . . . . . . . . . . . .13-9Freeing of Logical-Log Files. . . . . . . . . . . . . . 13-10Action If the Next Logical-Log File Is Not Free. . . . . . 13-10Action if the Next Log File Contains the Last Checkpoint . . . 13-11Logging Blobspaces and Simple Large Objects . . . . . . . . 13-11Switching Log Files to Activate Blobspaces . . . . . . . . 13-12
Backing Up Log Files to Free Blobpages . . . . . . . . . 13-12
Backing Up Blobspaces After Inserting or Deleting TEXT andBYTE Data . . . . . . . . . . . . . . . . 13-13Logging Sbspaces and Smart Large Objects . . . . . . . . . 13-13Using Sbspace Logging. . . . . . . . . . . . . . 13-13Using Smart-Large-Object Log Records . . . . . . . . . 13-16
Preventing Long Transactions When Logging Smart-Large-Object Data. . . . . . . . . . . . . . . 13-16Logging Process. . . . . . . . . . . . . . . . . . 13-16Dbspace Logging . . . . . . . . . . . . . . . . . 13-17
Blobspace Logging . . . . . . . . . . . . . . . . 13-17Table of ContentsxxiChapter 14Managing Logical-Log FilesIn This Chapter . . . . . . . . . . . . . . . . . .14-3Estimating the Size and Number of Log Files . . . . . . . .14-4Estimating the Log Size When Logging Smart Large Objects .14-5Estimating the Number of Logical-Log Files . . . . . . .14-6Backing Up Logical-Log Files. . . . . . . . . . . . .14-6Backing Up Blobspaces . . . . . . . . . . . . . .14-7Backing Up Sbspaces . . . . . . . . . . . . . . .14-7Switching to the Next Logical-Log File . . . . . . . . . .14-8Freeing a Logical-Log File. . . . . . . . . . . . . .14-9Deleting a Log File with Status D. . . . . . . . . . .14-9Freeing a Log File with Status U . . . . . . . . . . .14-9Freeing a Log File with Status U-B or F. . . . . . . . .14-10Freeing a Log File with Status U-C or U-C-L . . . . . . .14-10Freeing a Log File with Status U-B-L. . . . . . . . .14-11Monitoring Logging Activity. . . . . . . . . . . . .14-11Monitoring the Logical Log for Fullness . . . . . . . .14-11Monitoring Temporary Logical Logs. . . . . . . . .14-13Using SMI Tables . . . . . . . . . . . . . . . .14-14Using ON-Monitor. . . . . . . . . . . . . . .14-14Monitoring Log-Backup Status. . . . . . . . . . .14-14Allocating Log Files . . . . . . . . . . . . . . . . .14-15Adding Logs Dynamically . . . . . . . . . . . . .14-15Adding Logical-Log Files Manually . . . . . . . . . .14-17Dropping Logical-Log Files . . . . . . . . . . . . . .14-19Changing the Size of Logical-Log Files . . . . . . . . . .14-21Moving a Logical-Log File to Another Dbspace . . . . . . .14-21Changing Logging Configuration Parameters . . . . . . . .14-23Using ON-Monitor to Change LOGFILES . . . . . . . .14-24Displaying Logical-Log Records. . . . . . . . . . . .14-25Monitoring Events for Dynamically Added Logs. . . . . .14-25Setting High-Watermarks for Rolling Back Long Transactions . .14-27Long-Transaction High-Watermark (LTXHWM). . . . .14-28Exclusive Access, Long-Transaction High-Watermark(LTXEHWM). . . . . . . . . . . . . .14-28Adjusting the Size of Log Files to Prevent Long Transactions .14-29Recovering From a Long Transaction Hang . . . . . . .14-29xxiiAdministrator’s Guide for IBM Informix Dynamic ServerChapter 15Physical Logging, Checkpoints, and Fast RecoveryIn This Chapter . . . . . . . . . . . . . . . . . . .15-3Critical Sections. . . . . . . . . . . . . . . . . .15-4Physical Logging . . . . . . . . . . . . . . . . . .15-4Fast Recovery Use of Physically-Logged Pages. . . . . .15-4Backup Use of Physically-Logged Pages . . . . . . . . .15-5Database Server Activity That Is Physically Logged . . . . .15-5Size and Location of the Physical Log . . . . . . . . . . .15-7Specifying the Location of the Physical Log. . . . . . . .15-7Estimating the Size of the Physical Log . . . . . . . . .15-7Configuring the Size of the Physical Log. . . . . . . . .15-9Details of Physical Logging. . . . . . . . . . . . . . 15-10Page Is Read into the Shared-Memory Buffer Pool . . . . . 15-11
A Copy of the Page Buffer Is Stored in the Physical-Log Buffer . 15-11
Change Is Reflected in the Data Buffer . . . . . . . . . 15-11
Physical-Log Buffer Is Flushed to the Physical Log . . . . . 15-11
Page Buffer Is Flushed . . . . . . . . . . . . . . . 15-12
When a Checkpoint Occurs . . . . . . . . . . . . . 15-12
How the Physical Log Is Emptied . . . . . . . . . . . 15-12Checkpoints . . . . . . . . . . . . . . . . . . . . 15-12Full Checkpoint . . . . . . . . . . . . . . . . . 15-13
Fuzzy Checkpoint . . . . . . . . . . . . . . . . 15-13
Events That Initiate a Fuzzy Checkpoint . . . . . . . . . 15-15
Events That Initiate a Full Checkpoint. . . . . . . . . 15-15Sequence of Events in a Checkpoint . . . . . . . . . . 15-16
Backup and Restore Considerations . . . . . . . . . . 15-19Fast Recovery. . . . . . . . . . . . . . . . . . . 15-19Need for Fast Recovery. . . . . . . . . . . . . . 15-19Situations When Fast Recovery Is Initiated . . . . . . . . 15-20
Details of Fast Recovery After A Full Checkpoint. . . . . 15-20Details of Fast Recovery After A Fuzzy Checkpoint . . . . . 15-24Table of ContentsxxiiiChapter 16Managing the Physical LogIn This Chapter . . . . . . . . . . . . . . . . . .16-3Changing the Physical-Log Location and Size . . . . . . . .16-3Preparing to Make the Changes . . . . . . . . . . .16-4Checking For Adequate Contiguous Space . . . . . . .16-4Using a Text Editor to Change Physical-Log Location or Size .16-5Using onparams to Change Physical-Log Location or Size . .16-5Using ON-Monitor to Change Physical-Log Location or Size .16-6Monitoring Physical and Logical-Logging Activity . . . . . .16-6Sample onstat -l Output . . . . . . . . . . . . . .16-8Monitoring Checkpoint Information . . . . . . . . . . .16-8Forcing a Full Checkpoint . . . . . . . . . . . . .16-9Forcing a Fuzzy Checkpoint . . . . . . . . . . . .16-10Using SMI Tables . . . . . . . . . . . . . . . .16-11Section IVFault ToleranceChapter 17MirroringIn This Chapter . . . . . . . . . . . . . . . . . .17-3Mirroring. . . . . . . . . . . . . . . . . . . .17-3Benefits of Mirroring . . . . . . . . . . . . . . .17-4Costs of Mirroring . . . . . . . . . . . . . . . .17-4Consequences of Not Mirroring . . . . . . . . . . .17-5Data to Mirror . . . . . . . . . . . . . . . . .17-5Alternatives to Mirroring . . . . . . . . . . . . .17-6Mirroring Process. . . . . . . . . . . . . . . . .17-7Creation of a Mirrored Chunk . . . . . . . . . . . .17-7Mirror Status Flags. . . . . . . . . . . . . . .17-8Recovery . . . . . . . . . . . . . . . . . . .17-9Actions During Processing . . . . . . . . . . . . .17-9Result of Stopping Mirroring . . . . . . . . . . . .17-11Structure of a Mirrored Chunk. . . . . . . . . . .17-11xxivAdministrator’s Guide for IBM Informix Dynamic ServerChapter 18Using MirroringIn This Chapter . . . . . . . . . . . . . . . . . . .18-3Preparing to Mirror Data. . . . . . . . . . . . . . .18-3Enabling the MIRROR Configuration Parameter. . . . . . .18-4Changing the MIRROR Parameter with ON-Monitor . . . .18-4Allocating Disk Space for Mirrored Data . . . . . . . . . .18-5Linking Chunks . . . . . . . . . . . . . . . . .18-5Relinking a Chunk to a Device After a Disk Failure . . . . .18-5Using Mirroring. . . . . . . . . . . . . . . . . .18-6Mirroring the Root Dbspace During Initialization. . . . .18-7Changing the Mirror Status . . . . . . . . . . . . .18-7Managing Mirroring . . . . . . . . . . . . . . . . .18-8Starting Mirroring for Unmirrored Storage Spaces . . . . .18-8Starting Mirroring for New Storage Spaces . . . . . . . .18-9Adding Mirrored Chunks . . . . . . . . . . . . . . 18-10
Taking Down a Mirrored Chunk. . . . . . . . . . . 18-10Recovering a Mirrored Chunk . . . . . . . . . . . . 18-11
Ending Mirroring. . . . . . . . . . . . . . . . 18-11Chapter 19High-Availability Data ReplicationIn This Chapter . . . . . . . . . . . . . . . . . . .19-3High-Availability Data Replication . . . . . . . . . . . .19-4Type of Data Replicated . . . . . . . . . . . . . .19-4Advantages of Data Replication. . . . . . . . . . . .19-4How HDR Works . . . . . . . . . . . . . . . . . .19-8How Data Initially Replicates. . . . . . . . . . . .19-8Reproduction of Updates to the Primary Database Server . . .19-9Threads That Handle HDR . . . . . . . . . . . . . 19-14
Checkpoints Between Database Servers . . . . . . . . . 19-15
How Data Synchronization Is Tracked. . . . . . . . . 19-15HDR Failures. . . . . . . . . . . . . . . . . . . 19-16HDR Failures Defined . . . . . . . . . . . . . . . 19-16
Detection of HDR Failures. . . . . . . . . . . . . 19-17Actions When an HDR Failure Is Detected . . . . . . . . 19-17
Considerations After HDR Failure . . . . . . . . . . . 19-18Table of ContentsxxvRedirection and Connectivity for Data-Replication Clients. . .19-20Designing Clients for Redirection. . . . . . . . . .19-20Directing Clients Automatically with DBPATH . . . . . .19-21Directing Clients with the Connectivity Information . . . .19-22Directing Clients with INFORMIXSERVER . . . . . . .19-26Handling Redirection Within an Application. . . . . .19-27Comparing Different Redirection Mechanisms . . . . . .19-30Designing HDR Clients. . . . . . . . . . . . . . .19-31Setting Lock Mode to Wait for Access to PrimaryDatabase Server. . . . . . . . . . . . .19-31Designing Clients to Use the Secondary Database Server. .19-32Chapter 20

refer page:-------http://www.officesoon.com/doc/200703-administrator-s-guide-for-ibm-informix-dynamic-server