sql8 database loses backup events on rebuild

[Former Member]

Hi. An old customer, who has not kept current, asked us for help when our product, which they have running against sqla8, stopped working. I noticed that the sqla8 server was very low on disk space and that sqla8 had reported an assertion error.

I freed-up disk space and started the sql8 engine against the database. No problems were reported. Nevertheless, because of the previous assertion error, I suggested to the customer that the database be rebuilt. The customer provided for the additional down-time. During the down-time I unloaded and reloaded the database without incident. However, when checking the event folder, I found that the two backup events did not make it into the rebuilt database. Also, when a user started using our app against the rebuilt database, she reported seeing a division by zero error. I moved them back to the original database and our app seems to be working fine.

Is it normal for backup events to not be retained during an unload/reload?
How can I rebuild this database and have the sql8 engine report any problems during the unload/reload?

Thanks,
Doug

[VolkerBarth]

Do you have made the rebuild with a reload.sql script? If so (and if the unload has been run by a user with DBA priviledge), that should contain DDL for all database objects the database does contain itself.

In other words, like all other objects, database events should be retained during a rebuild, too.

(When doing rebuilds, I generally use the system catalog to compare that old and new database have the same contents - both structure and data.)

[jeff_albion]

Please include the exact steps you used to rebuild the database - including all commands used and any errors/messages you may have seen. Please also include the exact version and build of Adaptive Server Anywhere 8.

ASA8 required multiple unloads of stored procedures to deal with recursion, depending upon how you attempted to rebuild it - did you have to do this with your database?

[justin_willey]

With some very early versions of v8, I remember an issue of events not firing when expected after a rebuild (rather than not being in the database). The final version of v8 was 8.0.3.5597. The maintenance release and ebf are still available online.

[Breck_Carter]

Volker, Jeff and Justin will surely help with the rebuild problem

As for the "not kept current" problem, you might consider this vendor's approach:

[Former Member]

Yes, I set the number of passes to five.

[Former Member]

Breck, this customer has been leaving us... any day now... for five years! Do you have a cartoon which covers that scenario? I do, though, like the cartoon you supplied.

[Former Member]

I unloaded from SC, then used ISQL to run the reload file. Next time, I'll check the database, before and after, with this script...

select table_name from systable where table_type = 'base' and creator = 1 and table_name not like 'pbcat%' and table_name <> 'dual' order by table_name ; << ... then place the list of tables in a spread-sheet and build sql to check the number of rows in each table...

select 'activity' as table_name , count() from activity union select 'activity_descr' , count() from activity_descr union... <<

[VolkerBarth]

So that reload.sql script does not contain the according CREATE EVENT statements for the missing events?

[Breck_Carter]

Nothing really close, just this...

[VolkerBarth]

Thanks - Dilbert's final conclusion made my day:)