SE: 'bcheck -y' anomaly

Question

ISQL-SE 4.10.DD6 (DOS 6.22):

My app has the following SQL script (CODE REVIEW) which my users exec at end of business day to reorg the transaction table:

DATABASE dbfiles;

UNLOAD TO "U:\UNL\ACTIVES.UNL"
   SELECT * FROM transaction
    WHERE trx_type IN ("E","I","C","P")
 ORDER BY trx_cust_fullname,
          trx_last_pymt;

UNLOAD TO "U:\UNL\INACTIVES.UNL"
   SELECT * FROM transaction
    WHERE trx_type IN ("F","R","T","X","V")
 ORDER BY trx_cust_fullname,
          trx_last_pymt DESC;

DROP TABLE transaction;

CREATE TABLE transaction
    (
     trx_store CHAR(2),
     trx_cust_fullname CHAR(30),
     trx_type CHAR(1),
     trx_num SERIAL,
     [...]
    );

LOAD FROM "U:\UNL\ACTIVES.UNL"   INSERT INTO transaction;
LOAD FROM "U:\UNL\INACTIVES.UNL" INSERT INTO transaction;

CREATE CLUSTER INDEX custn_idx ON transaction (trx_cust_fullname);

CREATE UNIQUE INDEX trxn_idx ON transaction (trx_num);

[3 more indexes...]

UPDATE STATISTICS FOR transaction;

After running this script: TRANS103.DAT size was 882,832 bytes, but TRANS103.IDX size was only 22,089 bytes!

Although this IDX file size didn't look good to me, I queried all rows in transaction table with a "Perform" screen, all 1083 rows were displayable, updated some, added others and deleted some with no problems, exited, went back again and encountered no problems whatsoever! Then I ran 'bcheck -y TRANS103' anyway and got the following result:

S:\DBFILES.DBS> bcheck –y TRANS103 

BCHECK  C-ISAM B-tree Checker version 4.10.DD6   

C-ISAM File: s:\dbfiles.dbs\trans103 

Checking dictionary and file sizes. 
Index file node size = 512 
Current C-ISAM index file node size = 512 
Checking data file records. 
Checking indexes and key descriptions. 
Index 1 = unique key   
    0 index node(s) used -- 1 index b-tree level(s) used 
Index 2 = duplicates  (2,30,0)  
    42 index node(s) used -- 3 index b-tree level(s) used 
Index 3 = unique key  (32,5,0)  
    29 index node(s) used -- 2 index b-tree level(s) used 
Index 4 = duplicates  (242,4,2)  
    37 index node(s) used -- 2 index b-tree level(s) used 
Index 5 = duplicates  (241,1,0)  
    36 index node(s) used -- 2 index b-tree level(s) used 
Index 6 = duplicates  (46,4,2)  
    38 index node(s) used -- 2 index b-tree level(s) used 
Checking data record and index node free lists. 

ERROR: 177 missing index node pointer(s) 
Fix index node free list ? yes 

Recreating index node free list. 
Recreating index 6. 
Recreating index 5. 
Recreating index 4. 
Recreating index 3. 
Recreating index 2. 
Recreating index 1. 
184 index node(s) used, 177 free -- 1083 data record(s) used, 0 free 

So after bcheck, now its .IDX size increased to 122,561 bytes, which sounds about the right size, so I went back into "Perform",repeated previous tests with no problems encountered. Out of curiosity, I ran bcheck on it again and it repeated the same results from the first bcheck I ran!.. like if the 1st bcheck never repaired the table!.. Anyone know why bcheck again repaired what was supposed to be already "repaired"?.. Could it have something to do with the cluster index?

Answer 1

I have worked with Informix a few years ago, but not recently. Have you tried bcheck on the table when you have only one index on it? This discussion and another here make me think bcheck fixes one (broken?) index at a time. I hope this helps.

Answer 2

So to circumvent the problems mentioned above,I didn't create clustered indexes, rather created non-clustered. Now all the tables appear to be ok when I bcheck'd them, but I need clustered indexes!..Is it a good idea to run bcheck on all datafiles from within an sql script in isql>query-language>run or as a sysmenuitems menuscript option while the SE engine is running and catalog files are open?.. before, I was running bcheck on all .DAT files, including the system catalogs, from the OS prompt while the engine was unloaded.