Re: Problem with load

Hi Sean, Sean Wong wrote: > > Hi, I wonder if somebody has come cross similar problem before and can = > give me some suggestions. > > Env: hp-ux 9.04, 9000 series. OnLine & DB-Access ver: 5.02.uc1. ISQL = > ver: 4.11.uc1 > Log Mode: Has been turned off. > > I have an ASCII file which was unloaded from a database, and it has = > 367,616 lines. When I loaded it back to the database, there was no = > error, the engine said that total 367,616 rows loaded. I then ran = > update stat, and select count(*), it said 367,616 records. However, our = > tester told me later that there were some records (with = > task_id=3D1576572) missing. BTW, the 'task_id' is the first field in = > the table and it is integer not null, allowed duplicate values. > > I went to Unix and issued following command: grep ^1576572 mytbl.unl --- = > It returned 6 lines, but when I went to isql & dbaccess and ran: > SELECT * FROM mytbl WHERE task_id=3D1576572> It returned nothing!=20 > > I suspected that the indexes or data may got corrupted, so I dropped the = > indexes, recreated them, same problem. Then I dropped the table, = > recreated the table in a different database without index, same problem. = > I then though maybe the ASCII file has got some funny characters = > inside, so I did a load from another file (same data, but older), same = > problem. > > A table with 367,616 rows is not big at all. Anyway, I split the = > 'mytbl.unl' to 10 files, and loaded them one by one individually, those = > 6 records found! I think version 5.02UC1 creates your problem. The "load" utility eats memory and causes unpredictable errors ( simply watch "sqlturbo" by using "top" ). Using "dbload" instead of "load" will not solve your problem, so I suggest to migrate to a newer version ( version 5.01 did NOT have the problem, too. ) I had the same problem years ago, but I had to load 6.5 million rows. I used an incremental load by "dbload". > Also, following SQL statements return records in different order, = > although there is only one index on column (task_id, col2): > --- SELECT task_id, col2 FROM mytbl; Okay, this might produce a key-only read. Did you run UPDATE STATISTICS FOR TABLE mytbl ? > --- SELECT * FROM mytbl; This will produce a SEQUENTIAL SCAN. The resulting rows will not be retrieved in a special order, unless you enter an "ORDER BY" clause. Bye Stefan