Administering Informix Dynamic Server on Windows NT

Read an Excerpt

PREFACE: Preface
Since you are reading this book, you are either a Database Administrator (DBA) or responsible for maintaining one or more Informix Dynamic Server database environments-- what I call a Dynamic Server Administrator (DSA). What I don't know is whether you are new to Informix database engines or, if not, how much experience you have with them. Perhaps you've had experience with the UNIX ports of the products but now find yourself having to install and administer the engine on Windows NT. In doing so, you find that it's a whole new world. Or this could be the first time you've used an industrial strength database having upgraded from some semifeatured, PC-oriented products. Now that you need some serious reliability, functionality, and speed you're installing Informix Dynamic Server and wondering what to do next. This, too, will be a new world for you. If you are like me, opening the box containing the software distribution and documentation was both exciting and a little daunting. I was excited to get my hands on what is widely regarded as the fastest and best architected, general-purpose relational database engine on the market today. I wanted to try it out, kick the tires a little bit, see how well it performed. At the same time I was intimidated by the amount of documentation that came with it. A quick look revealed 25 books and about 7200 pages-- and that's just for the core engine product without any additional options to support extensible data types with the Universal Data Option or advanced support for data warehouses with the Advanced Decision Support Option to name just two. Was the engine so complicated that it required this muchexplanation?

The answer to that question is both a yes and a no. Yes, Informix Dynamic Server is more sophisticated than previous versions of Informix engines and, as a result, more complicated to maintain and tune. You do not, on the other hand, have to ingest the documentation to run or tune the engine. The documentation is there to explain, often down to the bit and byte level, what the engine does so that you can be a better DSA or DBA. To Informix's credit, their documentation is fairly complete, easy to understand, and extensive with regard to explaining and illustrating the concepts involved in setting up and administering databases and the environments they exist in.

Book Structure
In this book, I try to take the dry technical details in the documentation and put them in the context of daily life. I cover topics in what I think are their logical order of occurrence when working with a database environment. First you design the environment and then build and populate it. You create backups on a regular basis as well as monitor and tune as necessary. There are other responsibilities and functions, but these are the most important. I used this approach to build the subjects covered in each chapter.

This book is not intended to cover every single feature or mechanism of the Informix Dynamic Server engine. Instead, it was designed to help you through the process of starting up and running database environments. You'll find that I cover all the important and most commonly used features of the engine, as well as some others I think you should know about and be able to use.

Intended Audience
This book was written at a high to medium level in terms of technical detail and is focused toward those either new to Informix Dynamic Server or converting from earlier versions of the engine. I have purposely avoided the bits and bytes stuff as much as possible. For that level of understanding, consult the Administrator's Guide for Informix Dynamic Server and the other reference material that accompanies the software.

Because much of what is covered in this book is explained somewhere in the documentation which came with the software, do not assume that this book is intended to replace Informix's documentation. Nor should you assume that this book is simply an overview of what Informix provides. Recommendations and guidance are given here that are the result of a number of years of personal experience with Informix engines in general, and the Informix Dynamic Server engine in specific. This hands-on experience is not reflected in Informix's documentation.

I do make one major assumption in writing this: that you have a good understanding of the relational database model and the concepts of tables, columns, and other components of a relational database.

Book Summary

Chapter 1-- Introduction to Informix Dynamic Server
This chapter covers the general design of the Informix Dynamic Server engine. Terms and keywords that will be used extensively throughout the rest of the book are introduced and defined.

Chapter 2-- Preparing for Initialization
In this chapter, I cover many of the topics that need to be addressed from a design perspective when planning for the implementation of an Informix Dynamic Server environment. For the most part, it is very general in scope because there are very few hard and fast rules to follow when building a database environment. Where rules do exist, they are stated. At the close of the chapter, all the required environment variables, files, and Registry hive entries are explained in detail, as well as how to set them up.

Chapter 3-- Installing and Initializing the Server
In this chapter, I cover all the steps and configuration parameters for creating an Informix Dynamic Server database environment or instance. Specific recommendations are given on most, if not all, the parameters discussed. This chapter is very detail oriented, as opposed to the first two chapters.

Chapter 4-- The IECC and Basic Administrative Tasks
In this chapter, most of the general day-to-day, or occasional, instance-oriented administrative tasks are explained. These include adding or dropping disk space, starting up or shutting down the instance, and killing user threads in the instance. The Informix Enterprise Command Center (IECC), a graphical administration tool that can be installed on Windows NT and Windows9x desktops, is introduced as an interface for accomplishing many of these tasks. The various components for administering databases and tables, instances, backup and recovery, and executing SQL commands are explained.

Chapter 5-- Building a Database Environment
In this chapter, I trade my DSA's hat for that of a DBA and cover building and populating databases in Informix Dynamic Server instances. Covered in this chapter are features such as fragmenting tables, indexes, and constraints, database and table population utilities, and a collection of Informix Dynamic Server-specific SQL statements that I think are interesting to know about and use.

Chapter 6-- Backing Up and Restoring
One of the least glamorous but still important functions of operating a database engine is backing up what's on disk to tape. In this chapter, I cover a couple of backup strategies and their relative strengths and weaknesses. I explain how Informix can execute moment-in-time backup and restore operations with the database environment on-line and fully functioning. The process of executing backup and restore operations using the ontape backup and restore utility, as well as the ON-Bar utility suite in conjunction with the Informix Storage Manager (ISM) is covered in great detail.

Chapter 7-- Monitoring the Instance
Throughout the book I will make reference to, and include illustrations of, output generated by the various Informix Dynamic Server monitoring commands. In this chapter I focus exclusively on those commands and some of the more commonly monitored activities of a database environment. I will focus primarily on the available command-line utilities since, unlike the UNIX ports, there are no graphical monitoring tools available on the Windows NT ports of the products.

Chapter 8-- Enhancing Performance
This is the last chapter in the more or less linear process of building and maintaining an Informix Dynamic Server environment. The remaining chapters cover other important features or mechanisms of the database engine. Many of you reading this book will probably turn straight to this chapter because performance is not something you can ever have too much of. I cover the key components related to overall environment performance such as the query optimizer, updating data statistical and distribution information, virtual processors, and the Parallel Data Query (PDQ) functionality of the engine. I do not, however, provide a specific "do this/don't do this" section. That information is covered throughout the book in the section to which it best applies. I believe this will help you understand the recommendations better as they will be given in their proper context. In addition, the effect of the recommendation can be used to explain better the concept itself.

Chapter 9-- Providing High Availability and Reliability
In this chapter I cover the High-Availability Data Replication (HADR) utility. HADR configuration parameters, initialization, and the various recovery processes are explained in some detail.

Chapter 10-- Recovering from a Crash
There comes a time when, regardless of what you try to do to prevent it, the database environment, or the server it resides on, falls over and fails. In this chapter I explain some of the safeguards built into the engine to prevent data corruption from occurring as well as what the engine does to recover from a failure situation. I also touch on the technical support services Informix provides and the responsibilities you have as a database or Informix Dynamic Server administrator.

Chapter 11-- Distributed Transactions
While not necessarily new to Informix Dynamic Server, data from separate database environments can be used by a single application. In this chapter I cover, from the database perspective, the issues you need to address to ensure a successful implementation of what are called "global transactions."

Chapter 12-- Using the Windows High-Performance Loader
In this chapter, I introduce and explain a new utility to the Windows NT ports of the engine. Called the High-Performance Loader (HPL), this utility performs bulk load and unload operations to tables. It is extremely useful in data warehouse or data mart environments as well as for performing focused, table-oriented backup operations.

Chapter 13-- Scripts to Help Get the Job Done
This chapter contains documentation on a collection of software I have either created or borrowed from others that I have found to be helpful in administering either an Informix Dynamic Server instance or a database. The software itself is included on the accompanying media.

Conventions Used in This Book
I used the following conventions when preparing this book:

• Each chapter begins with a general list of topics to be covered.
• Each chapter ends with a brief summary of the most important points you should remember as well as an introduction to the topics to be covered in the following chapter.
• Reserved words in source code examples are not capitalized. I find source code loaded with capitalized words makes it more difficult to read and find important points of interest the developer might have wanted me to see easily. Personally, I only use capitalized words in source code comments to call attention to an important word or instruction. This is an easier and less time-consuming way to highlight this information than building some sort of window box.
• Flags for command-line utilities are most often listed in a bulleted list.
• Examples of commands and the output they generate are shown in a fixed-width font, for example,
  

  onstat -s -L 0 -U ar_system
  

Read More