UPDATE:

We believe we have a clue.

The high cpu use is due to the fact that a couple of indexes were dropped. (We think this
happened when the table repair was done.)

So we think (still waiting for confirmation from the customer), that this is the sequence:

a) DB was dinged
b) Customer ran repair
c) Now indexes are gone
d) Now system runs at high cpu

We do not have the original dinged db. We only have the 'b' step db.


Question:
Is there some simple code out there that:

1) Runs against a clean 'reference' dbisam db, and makes an inventory of all indexes
2) Saves this index library
3) Can then be run against an arbitrary dbisam db, checking for missing indexes, and
create them as needed?

I ask because:

a) We have run into the issue of 'gone' indexes a few times now.
b) Our db creation code is a combination of dynamic, machine generated indexes and hand
coded indexes. So we have no place in code where we have an 'all table' list of indexes.

Thanks!!!

"Sam Jones" <rafael@zfirmllc.com> wrote in message
news:7F1A5228-7D1D-4379-8EAD-B4C0E3016763@news.elevatesoft.com...
>
> Question:
> Is there some simple code out there that:
>
> 1) Runs against a clean 'reference' dbisam db, and makes an inventory of
> all indexes
> 2) Saves this index library
> 3) Can then be run against an arbitrary dbisam db, checking for missing
> indexes, and
> create them as needed?
>
> I ask because:
>
> a) We have run into the issue of 'gone' indexes a few times now.
> b) Our db creation code is a combination of dynamic, machine generated
> indexes and hand
> coded indexes. So we have no place in code where we have an 'all table'
> list of indexes.
>

CREATE INDEX IF NOT EXISTS ....

Doesn't get much simpler than that.

just run a reverse engineer on DBSYS, and copy the index definitions. Add
the IF NOT EXISTS clause. Release them as SQL with your program, then when
you do a repair, do a sql.loadfromfile and run the index definitions.

Robert

Sam,

<< So we think (still waiting for confirmation from the customer), that this
is the sequence:

a) DB was dinged
b) Customer ran repair
c) Now indexes are gone
d) Now system runs at high cpu

We do not have the original dinged db. We only have the 'b' step db. >>

The only way the indexes would be "gone" is is the corruption was so bad
that it wiped out some of the index definitions in the .idx file so that the
repair could not recover them.   Were the missing indexes all at the end of
the defined list of indexes for the table ?  It's possible that junk data
was written to the index count field in the index file header, thus changing
its value.  Of course, all of this would be reported in the repair log
messages generated by DBISAM.

<< a) We have run into the issue of 'gone' indexes a few times now. >>

If a table becomes corrupted, then all bets are off.  Especially with a
single-user machine where the data is stored on the local hard drive.  You
could have all sorts of things missing after a repair, depending upon the
level of corruption.   You need to start taking measures to prevent the
corruption outlined here:

http://www.elevatesoft.com/dbisam_faqt_4.htm

You should also make sure to instruct your customer to not just power-down
the machine or kill your application using the task manager.  Both of those
situations could possibly cause issues with the data integrity of the
database.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

We are going to add bunch of

CREATE INDEX IF NOT EXISTS ...

into our custom "database repair" functionality. Everything looks fine, but I cannot come
up with syntax to rebuild primary key. It appears that primary keys do not have a name
that I can use for IF NOT EXISTS condition.

Question for you - what is the "CREATE INDEX IF NOT EXISTS..." syntax for primary keys on
the table?

We had at least one case where after DB repair data was OK, but primary index was gone
from the table. Everything was running dog-slow, so we just added index on the primary ID
field and everything went to normal. For us rebuild primary index in cases like that -
acceptable solution.

What is the SQL command to check if primary index does exist, and if not - recreate it?

All our primary keys use the field with the same name of type "integer"

---- here is a sample table definition -------

CREATE TABLE IF NOT EXISTS "FRM_ID"
(
  "ID" INTEGER,
PRIMARY KEY ("ID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);

Alex,

<< Question for you - what is the "CREATE INDEX IF NOT EXISTS..." syntax for
primary keys on the table? >>

You need to use the ALTER TABLE statement to add or redefine an existing
primary key for a table:

http://www.elevatesoft.com/scripts/manual.dll?action=mantopic&id=dbisam4&product=d&version=7&category=3&topic=15

--
Tim Young
Elevate Software
www.elevatesoft.com

According to the ALTER TABLE syntax docs it should take IF EXISTS condition, but I cannot
make it work with v4.25

Could you help with ALTER TABLE IF EXISTS statement?

this one works
alter table account redefine primary key (id)

this one does not
alter table IF EXISTS account redefine primary key (id)
DBISAM Engine Error # 11949 SQL error - 'End of ALTER TABLE statement expected, instead
found 'EXISTS''

Thank you

-------
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Alex,

<< Question for you - what is the "CREATE INDEX IF NOT EXISTS..." syntax for
primary keys on the table? >>

You need to use the ALTER TABLE statement to add or redefine an existing
primary key for a table:

http://www.elevatesoft.com/scripts/manual.dll?action=mantopic&id=dbisam4&product=d&version=7&category=3&topic=15

--
Tim Young
Elevate Software
www.elevatesoft.com

Alex,

<< According to the ALTER TABLE syntax docs it should take IF EXISTS
condition, but I cannot make it work with v4.25

Could you help with ALTER TABLE IF EXISTS statement? >>

This works fine for me here with 4.26 (although this hasn't changed since
4.25 at all):

ALTER TABLE IF EXISTS customer
REDEFINE PRIMARY KEY (CustNo)

--
Tim Young
Elevate Software
www.elevatesoft.com