The Tbcheck Utility
The tbcheck utility is used to inspect the actual data on the disks. This is an important utility because this data is stored as binary data broken into pages. Short of writing specialized code to decode this binary data, tbcheck is the only way of accessing the actual data and index pages on the disk. In fact, in most UNIX installations, it is the only way to even see the data. This is because most UNIX installations will use raw disk devices to hold the OnLine chunks. Since these devices are not mounted in the UNIX file system, they never appear in most UNIX outputs. Even if the system were using UNIX cooked files for data storage, the data is stored in a binary format that cannot be viewed by normal UNIX commands.
UNIX commands such as dd can be used to relocate these chunks, but instances when this would be necessary are rare. Such instances could be the result of replacing a physical disk device. In such instances, the system needs to be down during the operations, as the UNIX commands have no concept of data integrity or consistency.
Commands such as od (octal dump) or hd (hex dump) could be used to inspect the data on the raw partitions, but these commands do not decipher the data layout of the OnLine chunks.
The Informix Customer Support engineers have access to additional tools to inspect and modify the data. They use an internal tool called tbdump to dump pages from disk and one called tbpatch to modify the pages. When the engineers call into your system to make repairs, they place these tools in the $INFORMIXDIR/astools directory. These tools are often left in the directory at the end of the service call.
The tbdump utility can provide some insights into the layout of the chunks if you have time to poke around in the chunks. The tbpatch utility can be very dangerous. Don't use it unless you are willing to risk losing all of your data.
If you ever get into a situation where things are totally bollixed up, check with Informix Technical Support. Some of their wizards may be able to twiddle the bits and get you back online.
One thing that they do quite often is change the online/offline bits that determine whether a chunk is usable or not. If you ever have a chunk that is down and you cannot get it to come back online, it's time to either restore from tape or call Informix Technical Support. IDS systems allow you to bring a chunk back online yourself without assistance.
Many of the miracles that Informix Technical Support perform are brought about by some of the software tools that they have available to them. Short of having these specialized tools, tbcheck should be your tool of choice.
This utility can have serious side effects, though, and the DBA should be careful when using it. In many instances, tbcheck will place a shared lock on the tables it is addressing. This prevents others from updating the table during its use. This is most visible using the -pt and -pT options. Since these options look at every data (and index for -pT) page in the table, this shared lock can last for a long time. Use these options only during times when your users will not be bothered by your locking the tables. It's best to reserve these options for system downtime.
The other options that inspect data or index pages for specific tables may have similar effects. These include the -ci, -cI, -ck, -cK, -cL, and -cl options, along with their -p counterparts. Use them carefully.
Of the other useful options, -pe is one of the most useful. It does not have any harmful effects on the database and can be used freely without worrying about interfering with your users. The same command, with the check option -cc is also safe to use. The -pc and -cc options are also relatively innocuous. We'll note these side effects as we talk about each option.
The full invocation options of tbcheck are:
joe 26> thcheck --
TBCHECK
Usage: tbcheck [-clist] [-plist] [-qny]
[ { database[:[owner.]table] | TBLSpace number I Chunk number }
{ rowid | page number } ]
c - check
r - reserved pages
e - TBLSpace extents and chunk extents
c - database catalogs
i - table indexes
I - table indexes and rowids in index
d - TBLSpace data rows including bitmaps
D - TBLSpace data rows including bitmaps, remainder pages and blobs
p - print
r - reserved pages (-cr)
e - extents report (-ce)
c - catalog report (-cc)
k - keys in index (-ci)
K - keys and rowids in index (-cI)
l - leaf node keys only (-ci)
L - leaf node keys and rowids (-cI)
d - TBLSpace data rows (-cd)
D - TBLSpace data rows including bitmaps, remainder pages and blobs (-cD)
t - TBLSpace report (CAUTION: Locks the table!)
T - TBLSpace disk utilization report (CAUTION: Locks the table!!)
p - dump page for the given [table and rowid | TBLSpace and page number]
P - dump page for the given chunk number and page number
B - BLOBSpace utilization for given table(s) [database:[owner.]]table
q - quiet mode - print only error messages
n - answer NO to all questions
y - answer YES to all questions
-- - print this help text
DANGEROUS OPTIONS: due to locking problems, be careful with the "i", "k",
"l" (ell), and "t" options, along with their capitalized
counterparts.
We will cover only a few of the more commonly used options here. If you need further detail, go to the tbcheck sections of the Informix DBA manuals for more detail. We'll try to cover the options that you will use in normal operations here. The other options are usually used for debugging specific problems. If you are sophisticated enough to be doing this level of debugging, you can certainly handle the manual. The items that we will gloss over will be the sections that allow you to check and/or print specific data and index pages. If you're using these options, you are probably looking to correct specific data or index problems. Most of the time this is academic, because you'll have to restore or rebuild your data or indexes if your data is this corrupted.
Tbcheck -ce and Tbcheck -pe
These two options are options that can safely be used at any time to dump information about the extent usage of your OnLine databases. These options check the chunk free list and tablespace extents. The -ce option just does the checks. The -pe option checks the data and prints the extent and table information.
Both options first give you a check of any tablespaces that occupy more than eight extents. This number of extents is important. When a tablespace goes over eight extents, an additional disk access is needed to retrieve data. This is due to the sizing of the data structures that hold the disk access pointers. As a general rule, your tables should be sized such that they and all their related indexes occupy eight or fewer extents. This is not always possible, but it's a good thing to strive for. Oddly enough, some of the most common tables that you find with more than eight extents in larger databases are the system catalogue tables, notably systables, syscolumns, and sysindexes. If you are expecting to deal with a large number of tables and indexes, it is advisable to increase the NEXT EXTENT parameter of the system tables before you begin to build the regular tables. This can be done with the following SQL statement:
ALTER TABLE systables MODIFY NEXT EXTENT 32;
The table name and actual extent size will vary with your applications. You can alter the NEXT EXTENT, but not the initial extent.
The printing option (-pe) gives you a report in the following format:
WARNING:TBLSpace joe:informix.queries has more than 8 extents.
WARNING:TBLSpace joe:informix.performance has more than 8 extents.
DBSpace Usage Report: rootdbs Owner: Informix Created: 06/24/92
Chunk: 1 /dev/rootdbs3 Size Used Free
135000 99713 35287
Disk usage for Chunk 1 Start Length
------------------------------------------- --------- ---------
ROOT DBSpace RESERVED Pages 0 12
CHUNK FREE LIST PAGE 12 1
PARTITION PARTITION 13 2705
dba:joe.test 2718 8
dba:informix.syscolumns 2726 8
dba:joe.dummy 2734 8
dba:informix.sysreferences 2742 8
dba:informix.extent_sizes 2750 50
FREE 2800 132200
This report will go on to cover every chunk, every extent, and every tablespace in your OnLine system, giving you useful information about the space utilization, fragmentation, and free space.
Practical Uses for Tbcheck -pe
Of all the information available from tbcheck, this report is the most useful. In addition to being safe to run no matter what users are doing, it runs relatively quickly and efficiently. It covers all data in the instance, spanning all databases.
One of the things that I do with my database is have the UNIX cron command run the following script every morning before users log into the system:
tbcheck -pe >& /u2/informix/last_tbcheckpe
The output file last_tbcheckpe is useful for many things. First, it is a good reference to have around if you just want to locate data about a chunk or tablespace. This file can be compressed and saved in an archive on a regular basis to give you a sense of exactly how your databases are growing or changing.
A third very important use is to provide data for a table size tracking and reporting system. If you've looked at the data in the system tables, it is not always clear just exactly how much space is being taken up by a table's extents. Since the output of this daily report shows exactly how the extents are being used, it is worth the effort to massage the data a little more.
One of the more important things that the output of this command gives you is a picture of exactly how fragmented your tables have become. As a table grows in size, it attempts to allocate additional extents that are contiguous to the last extent. As you add tables, drop tables, add chunks, and generally fill up your dbspaces, it becomes harder for the OnLine engine to keep your extents contiguous. As the tables become more fragmented, the I/O system has to do more work to find all of the pieces of the tables.
This results in a generalized slowdown of access and increased cost in resources to do the work. If you begin to experience a general slowdown of operations, watch the fragmentation of the tables. It may be necessary to occasionally rebuild the tables in another dbspace to collect all of the extents and make them contiguous again. This shows up most emphatically when the engine needs to do long sequential scans of the data.
Tbcheck -pt
This invocation of tbcheck generates a report that gives more detailed information about specific tables. It is invoked as
tbcheck -pt admin:system
TBLSpace Report for admin:dba.system
Physical Address 300220
Creation date 06/05/92 05:09:47
TBLSpace Flags 1 Page Locking
Maximum row size 64
Number of special columns 0
Number of keys 1
Number of extents 3
Current serial value 1
First extent size 6
Next extent size 4
Number of pages allocated 18
Number of pages used 17
Number of data pages 15
Number of data bytes 13952
Number of rows 218
Extents
Logical Page Physical Page Size
0 3030d4 6
......<several lines deleted>.......
In the output of this command the columns for number of pages used and number of data pages used represent maximums for the table as currently configured. It does not break down the number of pages into allocated versus currently used. Note that all of the numbers are in pages
Tbcheck –pT
This command gives more detailed information about the use and allocation of extents for tablespaces and also includes information about index usage. It is invoked as
tbcheck –pT admin:system
TBLSpace Report for admin:dba.system
Physical Address 300220
Creation date 06/05/92 05:09:47
TBLSpace Flags 1 page Locking
Maximium row size 64
Number of special columns 0
Number of keys 1
Number of extents 3
Current serial value 1
First extent size 6
Next extent size 4
Number of pages allocated 18
Number of pages used 17
Number of data pages 15
Number of data bytes 13952
Number of rows 218
Extents
Logical Page Physical Page Size
0 3030d4 6
6 303192 4
TBLSpace Usage Report for admin:dba.system
Type Pages Empty Semi-Full Full Very-Full
---------------- --------- ---------- ------- ------ ----
Free 6
Bit-Map 1
Index 3
Data (Home) 8
----------
Total Pages 18
Unused Space Summary
Unused data slots 14
Unused bytes per data page 48
Total unused bytes in data pages 384
Index Usage Report for index system_ci on admin:dba.system
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 2 2002
2 2 109 930
----- -------- -------- ----------
Total 3 73 1287
The output from the tbcheck –pT command contains all of the information that is in the tbcheck –pt command, with the additional information about unused space and index usage. Note that both the tbcheck –pT and tbcheck –pT place locks on the target tables, making it impossible for others to update, delete, or insert into these tables while the utilities are running.
This locking is unfortunate in that it limits the usability of tbcheck and makes it difficult to get detailed information about actual space utilization without locking tables. Some of the information can be gleaned from the innocuous options such as the tbcheck -pe option. None of the other options allows you to see what you need, which is the number of pages free in a tablespace. One way of working around this while the database is running is to compute some of the information from tbstat -t. This only works if the target table you want to check is currently open. The following script shows a way of opening the table and running a tbstat at the same time Create this script and name it tstat:
#!/bin/csh (sleep 1; tbstat -t > tstat.out & isql $1 << EOF select * from $2; EOF
In all of the scripts in this book, we are assuming that you are using isql as a data retrieval client. If you are using dbaccess rather than isql, simply change the isql to dbaccess in all of the scripts.
This script creates an output file called tstat.out. The program is invoked as:
tstat database_name table_name
For this sample run, the invocation was:
tstat admin test_table
joe 61> cat tstat.out
RSAM Version 5.00.UC2 -- On-Line -- Up 29 days 00:04:50 -- 12976 Kbytes
Tblspaces
n address flgs ucnt tblnum physaddr npages nused npdata nrows nextns
0 e00b85f4 1 1 1000001 10000e 5405 408 0 0 1
3 e00b8e40 1 1 4000001 500004 1355 2 0 0 1
4 e00b9104 1 11 3000002 300005 64 63 38 659 8
31 e00bdbb0 1 1 300021d 300220 18 17 15 218 3
4 active, 1100 total, 512 hash buckets
Looking at the tstat.out output file, we see that the last table is tblnum 300021d. By using the table script from Chapter 6, we can confirm that tblnum 300021d is indeed test_table.
The difference between the npages and the nused column is the pages free output of the tbcheck -pT program. The only problem with this script is that it outputs all of the target table's rows to standard output. If the table is large, this could take a while. You can run it for a second and then hit a CONTROL-C to exit and still get the output without danger of crashing the system. It's not as good as tbcheck, but it doesn't lock the tables.
Tbcheck - ci and Tbcheck -cI
The options for checking and repairing indexes with tbcheck are similar. The uppercase version is more complete and encompassing than the lowercase version. The tbcheck -ci utility just checks the key values of the indexes. The tbcheck -cI version adds to that a check of the rowids of the keys. If the indexes are consistent, there is no output. If there are problems, they are reported.
Both of these options include the capability of attempting to repair the indexes. You will be given these options only if the system is quiescent when the tbcheck is run. You can give the -y or -n flags to indicate that you either do or don't want tbcheck to try to repair the indexes.
The option to repair the indexes looks good on paper, but it's really pretty useless. Repairing an index requires your system to be quiescent. It is much slower than simply dropping and recreating the index. As a bonus, dropping and re-creating the index does not require you to bring down the instance. About the only time that there is an advantage to attempting an index repair is if the index is on a system table and you think that dropping the system table's index might hurt your performance or integrity. In that case, it might be worthwhile to attempt a repair.
Tbcheck -cc and Tbcheck -pc
These options will check and print the contents of the system catalog tables. The check option will often complain about missing synonym and authorization records. I have yet to see any instances where these complaints are valid.
The printing options are somewhat useful in that they not only provide the same checks as does the -cc options, but they also print extent information ala tbcheck -pt. Again, on all of these options, watch for locking.
Tbcheck -pr database_name
This option is another option that may be useful to the DBA. It provides the following output of data that is contained in the root dbspace reserved pages:
joe 71> tbcheck -pr
Validating INFORMIX-OnLine reserved pages - PAGE_PZERO
Identity INFORMIX-OnLine
Database system state 0
Database system flags 0
Page Size 2048
Date/Time created 06/04/92 13:24:33
Version number of creator 2048
Last modified time stamp 1
Validating INFORMIX-OnLine reserved pages - PAGE_CONFIG
ROOTNAME rootdbs
ROOTPATH /dev/root_dbs
ROOTOFFSET 128
ROOTSIZE 540000
PHYSDBS rootdbs
PHYSFILE 4000
LOGFILES 3
LOGSIZE 60000
MSGPATH /u2/informix/online.log
CONSOLE /u2/informix/online.sys
TAPEDEV /dev/rmtl
TAPEBLK 16
TAPESIZE 3950000
LTAPEDEV /dev/null
LTAPEBLK 16
LTAPESIZE 13107200
DBSERVERNAME robin
SERVERNUM 1
DEADLOCK_TIMEOUT 60
RESIDENT 0
USERS 110
LOCKS 20000
BUFFERS 4750
TBLSPACES 1100
CHUNKS 27
DBSPACES 8
PHYSBUFF 128
LOGBUFF 128
LOGSMAX 35
CLEANERS 2
BUFFSIZE 2048
CKPTINTVL 1200
Validating INFORMIX-OnLine reserved pages - PAGE_1CKPT & PAGE_2CKPT
Using check point page PAGE_1CKPT.
Time stamp of checkpoint 528303532
Time of checkpoint 01/13/94 17:47:12
Physical log begin address 10152a
Physical log size 2000
Physical log position at Ckpt 208
Logical log unique identifier 349
Logical log position at Ckpt If9c760
DBSpace descriptor page 100004
Chunk descriptor page 100007
Mirror chunk descriptor page 100009
Log file number 1
Log file flags 13 Log file in use
Current log file
Log written to archive tape
Time stamp 0
Date/Time file filled 12/31/69 18:00:00
Unique identifier 349
Physical location lOlcfa
Log size 30000
Number pages used 8093
Log file number 2
Log file flags 0
Time stamp 521789106
Date/Time file filled 11/18/93 15:06:51
Unique identifier 0
Physical location 10922a
Log size 30000
Number pages used 0
Validating INFORMIX-OnLine reserved pages - PAGE_1DBSP & PAGE_2DBSP
Using dbspace page PAGE_1DBSP.
DBSpace number 1
Flags 1 No mirror chunks
First chunk 1
Number of chunks 1
Date/Time created 06/04/92 13:24:33
DBSpace name rootdbs
DBSpace owner informix
DBSpace number 2
Flags 1 No mirror chunks
First chunk 2
Number of chunks 1
Date/Time created 06/04/92 13:31:44
DBSpace name log_dbspace2
DBSpace owner informix
DBSpace number 3
Flags 1 No mirror chunks
First chunk 3
Number of chunks 2
Date/Time created 06/04/92 13:32:42
DBSpace name dbspace1
DBSpace owner informix
DBSpace number 4
Flags 1 No mirror chunks
First chunk 5
Number of chunks 1
Date/Time created 06/04/92 13:33:42
DBSpace name log_dbspace
DBSpace owner informix
Validating INFORMIX-OnLine reserved pages - PAGE_1PCHUNK & PAGE_2PCHUNK
Using primary chunk page PAGE_2PCHUNK.
Chunk number 1
Next chunk in DBSpace 0
Chunk offset 64
Chunk size 270000
Number of free pages 70645
DBSpace number 1
Overhead 0
Flags 2041 Chunk resides on RAW device
Chunk is online
Chunk name length 13
Chunk path /dev/root_dbs
Chunk number 2
Next chunk in DBSpace 0
Chunk offset 16
Chunk size 45000
Number of free pages 14092
DBSpace number 2
Overhead 0
Flags 2041 Chunk resides on RAW device
Chunk is online
Chunk name length 10
Chunk path /dev/logs2
Chunk number 3
Next chunk in DBSpace 4
Chunk offset 16
Chunk size 500000
Number of free pages 18527
DBSpace number 3
Overhead 0
Flags 2041 Chunk resides on RAW device
Chunk is online
Chunk name length 11
Chunk path /dev/chunk2
Chunk number 4
Next chunk in DBSpace 0
Chunk offset 16
Chunk size 500000
Number of free pages 88404
DBSpace number 3
Overhead 0
Flags 2041 Chunk resides on RAW device
Chunk is online
Chunk name length 11
Chunk path /dev/chunk3
Chunk number 5
Next chunk in DBSpace 0
Chunk offset 16
Chunk size 67500
Number of free pages 6142
DBSpace number 4
Overhead 0
Flags 2041 Chunk resides on RAW device
Chunk is online
Chunk name length 9
Chunk path /dev/logs
Validating INFORMIX-OnLine reserved pages - PAGE_1MCHUNK & PAGE_2MCHUNK
Using mirror chunk page PAGE_2MCHUNK.
Validating INFORMIX-OnLine reserved pages - PAGE_1ARCH & PAGE_2ARCH
Using archive page PAGE_2ARCH.
Archive Level 0
Real Time Archive Began 01/12/94 09:18:23
Time Stamp Archive Began 527989114
Logical Log Unique Id 349
Logical Log Position a9c224
There's a lot of information available from the tbcheck -pr utility, and some of it is available nowhere else. It provides a good snapshot of the condition of your database at any particular time. It would be wise to occasionally run this option and save it in an archive file. If this is accompanied by running newschema for the entire database, it would provide useful disaster recovery capabilities for the DBA. The newschema script is presented in Chapter 6. It allows you to generate a correct, detailed database schema that contains more detail than the Informix dbschema program.
The logfile and checkpoint information presented is also unique to this program. I know of no other way to get access to this data other than by using the tbcheck utility. Of course, all of this data can be massaged by UNIX scripts, should the DBA have some more specific needs in mind.