April 1998 Newsletter

Volume 8, No. 2

Highlights of This Issue

Informix World Wide User Conference

Forum 98, by Nick Nobbe

Forum 98 Presentations on our Web Site

Washington Area Informix User Group Email List

Exploring the OnLine Command Line Utilities - ONCHECK, by Lester Knutsen

Informix Dynamic Server Personal Edition for Windows 95 & NT

This meeting will host a presentation of a case study on the Bureau of Land Management’s (BLM) implementation of a Web-enabled Data Warehouse project. The BLM Financial Management Information System is designed to provide users in 350 field offices and headquarters easy access to accurate financial information in a single unified system. The warehouse uses Informix Dynamic Server as the database and Brio Technology’s Web enabled query tools. The system was up and running March 31, 1998, on schedule and under budget. It has been nominated for the "Data Warehousing Institute Best Practices Awards," and was featured as a lab at the Reinvention Revolution Conference for Government Agencies. The speakers will include:

• Peter Ertman, Project Manager, BLM - The Project Overview, Goals, Results and Project Demo.
• Lester Knutsen, Database Consultant, ADTC - How BLM developed and implemented a web-enabled data warehouse.
• Marty O'Keefe, Informix Consulting - How BLM used the Informix Superstore to plan the project.
• Risa Freedman, Brio Technology - How to web-enable databases using the Brio Query products.

Date and Time: May 27, 1998, 9:00 a.m. to 12:00 noon

Location: Informix Software Corporation - 8065 Leesburg Pike, Suite 600, Vienna, VA 22182

The meeting is open to everyone. Please RSVP to 703-256-0267, ext. 4, so we can keep an attendance count.

---

$200 Discount for WAIUG Members

Attending the Informix Worldwide User Conference

Advanced DataTools Corporation

Summit Data Group

UCI Consulting, Inc.

Join us at the 1998 Informix Worldwide User Conference and Exhibition (IWUC), where over 5,000 Informix users, partners, and database industry press and analysts from around the world will convene to examine solutions for achieving leadership in today’s global market. This year’s conference will take place from July 21 - 24 at the Washington State Convention & Trade Center in Seattle, WA. Named by USA Today as the number one City of the Future, Seattle stands as a new Mecca for technological innovation.

Uncover in-depth technical information in our popular half-day tutorials. Always a sell-out, these sessions will help you plan for the future while leveraging your current database investment. Choose from five morning and six afternoon sessions. Tutorials include the following:

• Demystifying Data Warehousing
• Developing DataBlade® Modules for Informix Dynamic Server with Universal Data OptionÔ
• Features of Informix Dynamic Server, 7.3
• Informix Dynamic Server on Windows NT-How it’s Different from UNIX
• Monitoring and Tuning for Informix Dynamic Server with Extended Parallel OptionÔ
• Monitoring and Tuning Informix Dynamic Server Performance
• New Features for Informix Dynamic Server, 8.3 with Advanced Decision Support OptionÔ
• Trouble-Shooting Informix Dynamic Server Using Informix Data DirectorÔ with Visual Basic
• Using Informix Dynamic Server with MetaCubeÔ ROLAP Option for Data Warehouses Web Integration Option

Explore the possibilities of using Informix’s uniquely differentiated solutions in on-line transaction processing (OLTP), data warehousing, and Web/content management to achieve competitive advantage. These comprehensive tracks offer invaluable insights into the latest trends and tools to help you use technology to seize new opportunities. Tracks include:

• Business Trends Database Management
• Application Development Product News
• Tips and Tricks IIUG Technical Topics

WAIUG Members are eligible to receive a $200.00 discount off of the registration fee for the Informix Worldwide User Conference. To take advantage of this excellent opportunity, you must request the International Informix User Group (IIUG) Member Discount and have your IIUG member number when you register for the Conference. All WAIUG members as of April 30, 1998 are enrolled in the IIUG. All members requesting the discount will be verified for IIUG membership, so you must join the user group prior to registering for the Conference. Please call John Petruzzi at 703-490-4598 or Linda Knutsen at 703-256-0267 if you need your IIUG member number. More information is available by contacting (800) 784-6580, or at www.informix.com.

Acting as a backup guy at WAIUG’s Forum this year, I had a full, sometimes hectic, but quite rewarding day. I arrived early thinking I was prepared, but sooner than I knew registration lines started backing up. Thanks to spur of the moment volunteers and our registration crew, after a few bumpy moments, the Forum was underway. Approximately 220 people, some from as far afield as Pennsylvania, turned up.

The all-day event was sponsored by Informix and 17 exhibitors in a spacious wing of the Fairview Park Marriot Hotel in Falls Church - the most attractive setting for the Forum to date. The schedule of 14 sessions started with Michael Stonebraker’s entertaining keynote on the evolution of the database industry, paused for lunch with a brief on Informix’s new leadership and directions given by Informix VP Don Hunt, and ended with refreshments and a prize drawing in the exhibit hall.

In between duties and just enjoying the good company and food, I had time to attend a few of the tutorial sessions, sorry as usual about having to miss others. And results from the Forum survey show that the sessions on a whole were well received by other participants: ratings for all sessions had an over 50 percent approval rate (based on my rating method). Eight sessions, including the keynote, had an overall approval rating of 80 percent or better. For the truly skeptical or curious, here are the results, showing responses as Count, and total points as Score. Approval rates are based on a percentage of total possible score (or the maximum, 5 x count).
 

Sessions Overall Scores	Count	Rating	Score
Keynote: Michael Stonebraker	198	800	80.8%
Web Database Development	96	286	59.6%
New Features in IDS 7.3	68	286	84.1%
Architectures of the Future-Rich Data Types	30	116	77.3%
Data Warehouse & Informix Case Study	75	291	77.6%
Monitoring Informix Using IECC	72	292	81.1%
Extending 4GL Apps to a Component-Based Univ	24	81	67.5%
Lunch: Don Hunt	145	563	77.7%
Building Powerful Informix Apps with Java	75	304	81.1%
Tuning Your INFORMIX Databases with AudPorts	65	290	89.2%
Approaches to GUI for Informix 4GL	32	110	68.8%
nformix Performance Tuning	104	479	92.1%
Using Informix-DS on Windows NT	32	136	85.0%
Enterprise Reporting/OLAP	46	205	89.1

One comment we heard during the Forum was "how do I get the presentation slides?" The solution was to put them on our web site. Many thanks to all the speakers who provided the powerpoint slides that I have converted to html. The following is the list of presentations that are currently available.

• Keynote by Dr. Michael Stonebraker, Vice President and Chief Technology Officer
• Architectures of the Future - How to Manage Rich Data Types by Martin Terwilliger
• Building Powerful Informix Applications with Java by Gene Clouse
• Extending Informix 4GL Applications to a Component-Based Universe by Robert Foery
• Informix Performance Optimization by Kevin Fennimore
• Monitoring INFORMIX-Dynamic Server Using the IECC by Lester Knutsen
• New Features in INFORMIX-Dynamic Server 7.3 by Clem Akins
• Tuning Your Informix Databases Using AudPorts by Kirk Collins
• Using INFORMIX-Dynamic Server on Windows NT by Mike Tinius
• Web Database Development by Seth Grimes

The waiug-members mailing list is for announcements and discussions by members of the Washington (DC, USA) Area Informix User Group. The main goal of this email list is to share information among Informix users in the Maryland, Virginia and Washington DC areas. This is a discussion place for user group members. However, you don't have to be a member of the user group to subscribe to this mailing list. The email list is sponsered by the International Informix User Group using the Majordemo mailing list software on the IIUG computer system. Seth Grimes has volunteered to manage the list.

Listed below are some key facts about the list. When the instructions say to use a particular Majordomo command, this means you should send the specified text in the body of an e-mail message to "majordomo@iiug.org". Do not put commands in the Subject: header line, as it is ignored.

SUBSCRIBING -- The list is open to anyone who wishes to subscribe. To subscribe, use the Majordomo command "subscribe waiug-members".

If your address on the list needs to be something other than the one that appears on the "From:" line of your outgoing e-mail, use the Majordomo command "subscribe waiug-members your_address" where "your_address" is replaced by the address to which your list messages should be sent. Note that this request will be routed to the list manager for approval, so a subscription of this type may not take effect immediately.

POSTING MESSAGES -- You must be a subscriber to post to this list. Majordomo enforces this restriction by checking to see that the From: address of the posted message matches one of the subscriber addresses. This restriction may cause problems at a site that subscribes an e-mail alias that explodes list messages to several local users. Individual users at such a site will not be able to post to the list unless they can make their messages appear to come from the local alias.

MESSAGE ARCHIVE -- All messages posted to the waiug-members list are saved in monthly archive files. You must be a subscriber to this list to access the archive.

MORE INFORMATION -- For more information about these and other Majordomo features, use the Majordomo command "help". To contact the list's manager, send e-mail to "owner-waiug-members@iiug.org".

In the last issue I talked about the ONSTAT utility. This article discuss the ONCHECK utility. INFORMIX-DS comes with a set of powerful command line utilities that enable you to monitor, tune, and configure your database server.

ONCHECK is the tool to check and display information about your dbspaces, blobspaces, chunks, tables, indexes, and disk pages. The purpose of this utility is to ensure that your database server disk space has no inconsistencies. I like to think of this as the database version of the UNIX utility ‘fsck’ which checks file systems, or the DOS utility ‘chkdsk’ which checks DOS disk space.

ONCHECK operates in two basic modes with two basic options. Figure 20 has a complete list of all the options. The ‘-c’ list of options perform consistency checks and display a limited amount of information unless there is a problem. The ‘-p’ list of options perform the consistency checks and display much more information about what you selected.

When ONCHECK finds a problem it will provide you with an error message to indicate what the problem is. If the problem is a corrupt index, ONCHECK will prompt you to tell it to fix the index. The only problem ONCHECK can fix is corrupt indexes. However, it may be faster to drop and re-create the index using SQL commands than for ONCHECK to fix it.

ONCHECK will place locks on all tables and databases that it needs to access. In some cases it will place an exclusive lock on a database or table and prevent other users from accessing the data. You must be careful when running ONCHECK to make sure it will not disrupt your users’ work. It is a good idea to run ONCHECK when the OnLine server is in quiescent mode so it does not conflict with other users. This is especially true when using the ‘-c’ option to check and repair data.

Figure 20: ONCHECK syntax 

        -c Options to check consistency

            r Reserved Pages

            e Extents

            c Database catalogs [database]

            i Table indexes database[:[owner.]table[#index]]

            I Table indexes and rowids in index database[:[owner.]table[#index]]

            d TBLspace data rows including bitmaps database[:[owner.]table[,fragdbs]]

            D TBLspace data rows including bitmaps, remainder pages and BLOBs database[:[owner.]table[,fragdbs]]



        -p Options to check and display

            r Reserved Pages

            e Extents report

            c Catalog report [database]

            k Keys in index database[:[owner.]table[#index]]

            K Keys and rowids in index database[:[owner.]table[#index]]

            l Leaf node keys only database[:[owner.]table[#index]]

            L Leaf node keys and rowids database[:[owner.]table[#index]]

            d TBLspace data rows database[:[owner.]table[,fragdbs]] [rowid]

            D TBLspace data rows including bitmaps, remainder pages and BLOBs database[:[owner.]table[,fragdbs]] [page number]

            t TBLspace report database[:[owner.]table[,fragdbs]]

            T TBLspace disk utilization report database[:[owner.]table[,fragdbs]]

            p Dump page for the given database[:[table[,fragdbs]] and rowid | TBLspace and page #]

            P Dump page for the given chunk num and page num[chunk # and page #]

            B BLOBspace utilization for given table(s) database[:[owner.]table[,fragdbs]]

        -q Quiet mode - print only error messages

        -n Answer NO to all questions

        -y Answer YES to all questions

The first 12 pages of the rootdbs contain crucial information OnLine needs to operate. If these pages are damaged, your database server cannot operate. The command to check and print out the Reserved Pages is ‘oncheck -pr’. This is a useful command to run every night or at regular scheduled intervals to make sure everything is all right. Figure 21 shows the first part of the output from ‘oncheck -pr’.

Figure 21: Output from the oncheck -pr command to print Reserved Pages 

lester@merlin >oncheck -pr

Validating INFORMIX-OnLine reserved pages - PAGE_PZERO

    Identity                     INFORMIX-OnLine Copyright(C) 1986-1995 Infor

    Database system state         0

    Database system flags         0

    Page Size                     2048

    Date/Time created             06/27/97 08:39:23

    Version number of creator     4

    Last modified time stamp      0

Validating INFORMIX-OnLine reserved pages - PAGE_CONFIG

    ROOTNAME                     rootdbs

    ROOTPATH                     /u3/dev/rootdbs1

    ROOTOFFSET                   0

    ROOTSIZE                     500000

    MIRROR                       0

    MIRRORPATH 

    MIRROROFFSET                 0

    PHYSDBS                      rootdbs

    PHYSFILE                     2000

    LOGFILES                     12

    LOGSIZE                      1000

Problems with the Reserved Pages or any dbspace disk pages can be caused by disk failures, operating systems errors, or server crashes. However, most of the problems I have seen have been created by human error in configuring database servers. Two OnLine servers using the same disk space where the rootdbs offset overlaps can cause one server to overwrite the Reserved Pages of another. One client I worked with had two DBA’s that were not communicating very well. One created and configured an OnLine server using four chunks. The second DBA configured another server and unknowingly used chunk 2 from the first server as the rootdbs. This created a comical situation where when one DBA brought up an OnLine server, the other server would crash.

Another configuration issue is with the operating system. Some versions of UNIX use the first few pages of a disk to store configuration information. If you install your rootdbs using these pages, you will get corrupt Reserved Pages. Another client of mine had used these first few pages, and found the Reserved Pages corrupt after every reboot of the computer. Each time the operating system rebooted, it would check each disk for a valid disk label on the first page of the disk. It would not find one, because OnLine had overwritten it with the rootdbs. The operating system would then overwrite part of the rootdbs with a valid disk label. Some Logical Volume managers use the first page to hold configuration information. Most problems with the Reserved Pages can be avoided with careful configuration. Check with your hardware and operating system vendor for information on how it handles the first pages of disk or a Logical Volume.
Checking and displaying the database System Tables: oncheck -cc | oncheck -pc

The System Tables are the key structures which define all the tables, columns, indexes, stored procedures, and constraints for a database. This option checks, or checks and displays, the consistency of these structures. Again, this is another command that should be run on a regular basis to monitor your server. Figure 22 shows part of the output from this command.

Figure 22: Part of the output of the command oncheck -pc to print database datalogs 

lester@merlin > oncheck -pc stores7

    Database:                 stores7

    Owner                     lester 

    Date created              07/04/97 17:34:01



TBLspace stores7:informix.systables

    Physical Address          119fa9 

    Creation date             07/04/97 17:34:01

    TBLspace Flags            802         Row Locking

                                          TBLspace use 4 bit bit-maps

    Maximum row size          104 

    Number of special columns 0 

    Number of keys            2 

    Number of extents         1 

    Current serial value      112 

    First extent size         8 

    Next extent size          8 

    Number of pages allocated 8 

    Number of pages used      6 

    Number of data pages      3 

    Number of rows            44 

    Partition partnum         1048736 

    Partition lockid          1048736

    Extents 

    Logical Page     Physical Page     Size

            0         119eef             8



    Index information.

        Number of indexes         2

        Data record size          104

        Index record size         2048

        Number of records         44

The index structure is one of the keys to fast retrieval of data. In the early days of Informix products, before OnLine, when the database used UNIX files to hold data and indexes, Informix had a utility called "bcheck" to check and fix index structures. I used to run this utility on a weekly basis to make sure all my database indexes were okay. In OnLine, this function is built into the ONCHECK utility. To check all the indexes of a database type:

    oncheck -cI database_name

    oncheck -cI database_name:table_name

If it finds a corrupt index, you will be prompted to fix that index. Enter ‘y’ and press return to fix the index. On large tables it is faster to drop and create the index using the SQL commands than to use ONCHECK. Fixing indexes will lock the table in exclusive mode. The SQL commands to drop and re-create an index are "drop index index_name" and "create index". Refer to the Informix SQL Syntax manual for the details.

The ‘-pI’ option displays some very useful information about an index. Over time, as data is added and deleted from an index, it will grow by splitting a page to start new pages. Sometimes there is a performance benefit to dropping and rebuilding an index.

Checking and displaying Data Pages: oncheck -cD database:table | oncheck -pD

Data Pages are the pages where your table records are actually stored. This option checks or prints the pages that contain your data records. It is useful to provide a consistency check on your data.
Checking and displaying table space utilization: oncheck -pT database:table

This option checks a table’s usage of disk space, and the ‘-p’ print option display some very useful information about a table. See Figure 23 for an example of the output. Some of the key values this displays are:

Number of extents - This shows how fragmented your table is. I like to limit my databases to no more than one (1) extent per year. With over 8 extents you may notice a performance degradation, and depending on the size of your table, at around 180-200 extents, your table will not be able to add any more extents. When this happens, new records cannot be inserted. To fix this problem you must rebuild your table. The best way to accomplish the reload is to unload it, drop it, re-create it, and reload the data.

Number of pages allocated - This is the number of pages the table has on disk. It will include data pages, free space for new records, and indexes pages.

Number of pages used - This is the number of pages in use. To find out how many free pages you have subtract this value from the number of pages allocated.

Number of data pages - This is the number of pages used to store data records.

Index usage report - This shows how much of the index pages are used and how much is free.

Figure 23: Output from oncheck -pT to print table space utilization 

lester@merlin >oncheck -pT stores7:items



TBLspace Report for stores7:lester.items



    Physical Address              119fca 

    Creation date                 07/04/97 17:34:08

    TBLspace Flags                801     Page Locking

                                          TBLspace use 4 bit bit-maps

    Maximum row size                 18 

    Number of special columns        0 

    Number of keys                   3 

    Number of extents                1 

    Current serial value             1 

    First extent size                8 

    Next extent size                 8 

    Number of pages allocated        8 

    Number of pages used             5 

    Number of data pages             1 

    Number of rows                   67 

    Partition partnum                1048769 

    Partition lockid                 1048769 



Extents 

    Logical Page     Physical Page     Size

            0             11a039         8

TBLspace Usage Report for stores7:lester.items



Type             Pages      Empty      Semi-Full  Full       Very-Full

---------------- ---------- ---------- ---------- ---------- ----------

Free                 3

Bit-Map              1

Index                3

Data (Home)          1

                 ----------

Total Pages          8



Unused Space Summary

    Unused data slots                    23

    Unused bytes per data page           18

    Total unused bytes in data pages     18



Home Data Page Version Summary



        Version                         Count

             0 (current)                  1



Index Usage Report for index 104_10 on stores7:lester.items



                    Average  Average

    Level Total    No. Keys  Free Bytes

    ----- -------- -------- ----------

    1     1        67       1015

    ----- -------- -------- ----------

    Total 1        67       1015



Index Usage Report for index 104_11 on stores7:lester.items



                    Average Average

    Level Total    No. Keys Free Bytes

    ----- -------- -------- ----------

    1       1       67      1501

    ----- -------- -------- ----------

    Total   1       67      1501



Index Usage Report for index 104_12 on stores7:lester.items



                    Average   Average

    Level Total     No. Keys Free Bytes

    ----- -------- -------- ----------

    1       1       67      1334

    ----- -------- -------- ----------

    Total   1       67      1334

This option is useful when debugging. It dumps out the contents of a page from disk.

Checking and displaying extents: oncheck -pe

This option shows how your tables are spread out over chunks. It produces a report by dbspace and check, listing each extent for each table with the starting address and size. In version 5 of OnLine this is the only way to determine how spread out a table’s extents are. In version 7 the sysmaster database can be used to get the same information in a more useful manner. In the chapter on the SMI and sysmaster database, I list several scripts that provide this information. But remember, this option does check the consistency of your extents, and still has a very useful purpose. Figure 24 is an example of the output from this command.

Figure 24: Output from oncheck -pe 

lester@merlin >lester@merlin >oncheck -pe



DBspace Usage Report: rootdbs Owner: informix Created: 06/27/97

    Chunk: 1 /u3/dev/rootdbs1             Size     Used     Free

                                        250000     10337   239663



    Disk usage for Chunk 1                          Start Length

    ------------------------------------------- --------- ---------

    ROOT DBspace RESERVED Pages                     0    12

    CHUNK FREE LIST PAGE                            12   1

    TBLSPACE TBLSPACE                               13   50

    PHYSICAL LOG Pages                              63   1000

    LOGICAL LOG Pages - Log 1                       1063 500

    LOGICAL LOG Pages - Log 2                       1563 500

    LOGICAL LOG Pages - Log 3                       2063 500

    LOGICAL LOG Pages - Log 4                       2563 500

    LOGICAL LOG Pages - Log 5                       3063 500

    LOGICAL LOG Pages - Log 6                       3563 500

    LOGICAL LOG Pages - Log 7                       4063 500

    LOGICAL LOG Pages - Log 8                       4563 500

    LOGICAL LOG Pages - Log 9                       5063 500

    LOGICAL LOG Pages - Log 10                      5563 500

    LOGICAL LOG Pages - Log 11                      6063 500

    LOGICAL LOG Pages - Log 12                      6563 500

    DATABASE TBLSPACE                               7063 4

    sysmaster:informix.systables                    7067 8

    sysmaster:informix.syscolumns                   7075 16

    sysmaster:informix.sysindexes                   7091 8

    sysmaster:informix.systabauth                   7099 8

    sysmaster:informix.syscolauth                   7107 8

    sysmaster:informix.sysviews                     7115 8

    sysmaster:informix.sysusers                     7123 8

    sysmaster:informix.sysdepend                    7131 8

    sysmaster:informix.syssynonyms                  7139 8

    sysmaster:informix.syssyntable                  7147 8

These utilities provide a powerful toolkit for a DBA to care for and monitor a database server. Articles in future issues of this newsletter will explore the other OnLine utilities. My web site also has an article covering the eight basic OnLine utilities.
 

Informix invites you to download a PREVIEW RELEASE copy of Informix Dynamic Server - Personal Edition for Windows 95 and Windows NT. This Special release of IDS-PE (Personal Edition) is being released to IIUG members (at least initially) for testing purposes. To receive the password for the download you must fill out the Testing Agreement below.

What is Informix Dynamic Server - Personal Edition?

IDS/PE is a version of Informix Dynamic Server that can be installed on Windows95 & WindowsNT. The intended users are mobile users and small scale distributed sites. Since IDS/PE is built on the Dynamic Scalable Architecture (DSA), you will be able to use your favorite tools such as Data Director for VB, INFORMIX-Enterprise Command Center, and many, many more.

IDS/PE 7.22 is a preview release and is not intended for production use. This software is provided at no charge, FOR PREVIEW ONLY. This software is NOT freeware and is protected by copyright law and international treaties. Any computer program built with any component of the Informix Dynamic Server - Personal Edition product is subject to a per end-user cost at the time of deployment.

This preview release of IDS/PE v7.22 for Windows 95 and Windows NT is provided as is. The only product specific documentation available at this time is therelease notes. However, the functionality of the server, with the exceptions noted in the release notes, is substantially similar to IDS/WE v7.22. For information on how to use or administer IDS/PE v7.22, please refer to the IDS/WE v7.22 documentation.

System Requirements

MS Windows 95 or Windows NT 3.51 or higher 50 megabytes hard disk space (depending on installation options) 32 MB RAM VGA or higher resolution monitor (1024x768 recommended)

Feedback

Support for technical issues and problems will be available by sending an email to ids-pe-tech@informix.com. Any questions, bugs, or operational anomalies should be reported to the development team through the same email address.
Download Location

http://www.iiug.org/software/special_software/idspe/

This newsletter is published by the Washington Area Informix User Group

Lester Knutsen, President/Editor

Washington Area Informix User Group

4216 Evergreen Lane, Suite 136, Annandale, VA 22003

Phone: 703-256-0267

lester@advancedatatools.com