UNLOAD FILE ON REORGANIZE

[monchotgu]

Hi I hope everybody and their families are safe .
I have one question regarding the reorganize command. From what I understand the reorganize does an UNLOAD. Does any body know where this unload file is located? I made a terrible mistake of not doing a backup of my database and ran the REORGANIZE COMMAND. When it started to load again I got an error of "INVALID password", and it onle reloaded 1/10th of my database. I want to find the file created when the reorganize dis the unload.


Thanks inn advance

[Steve Leighton]

.
uh-oh ! Never - and I can only say this 1000x without getting boring, never use the 'REORGANIZE' command .
Always use UNLOAD and RELOAD commands manually - 10010x better.
.
Too late to tell you of course, but the 'REORGANIZE' command does use a physical unload file, and if you used SQLTalk, it creates a temporary file called sqltmp.nnn for unloading. So search your whole path for 'sqltmp.*'

BUT Note: If you must use 'REORGANIZE' , make a copy of the database file *.DBS before executing this command.
If an error occurs during the reorganization, both the temporary file and the database itself could be lost.

When you perform a REORGANIZE, SQLBase does an UNLOAD(unencrypted) and then creates a new unencrypted database. Regardless if it was encrypted to start with.
It is highly recommended that, instead of performing a REORGANIZE ( esp. with encrypted databases ), you manually reorganize by executing the following steps:
1. Perform a UNLOAD command.
2. DROP the database.
3. Create a new database.
4. Encrypt the new empty database.
5. LOAD the database.
6. Delete the UNLOAD file.

Sorry couldn't be more help. Maybe you have a Network backup i.e. the whole file system, containing the .DBS ?

[monchotgu]

Thanks Steve for your reply!! I will definately take your advise!!
I was able to find the file sqltmp!! I could recover my databse thro this file.

Agin your help is much appreciated!!

[FRBhote]

Before doing the load, advisable to:

SET RECOVERY OFF;

This makes it much faster because log files are not created, and:

LOCK DATABASE;

For some obscure reason, I find indexes are created much faster with this, and finally:

SET RECOVERY ON;

after the db is loaded.

[TerryP]

I also try to do a LOCK DATABASE before the unload, if possible, to prevent other users from connecting to or changing the database, though I'm not sure it improves the unload performance. However, this command will fail if other users are already connected, so I treat it as optional.

Also, after reloading the database, don't forget to UPDATE STATISTICS ON DATABASE, or the optimizer might not work effectively. In the past, Gupta/Unify/OpenText have said that this happens automatically after a REORGANIZE or a LOAD, but I've checked every SQL Base version since 7.6.1 and it still doesn't do it!

BTW, don't forget to UNLOCK DATABASE after the LOAD and UPDATE STATISTICS. Here's the script that I use for reorganizing a database:

Code: Select all

connect <database>;
lock database;
unload database <unload file path/name> overwrite;
disconnect all;

set server <server>;
drop database <database>;
create database <database>;
connect <database>;
set recovery off;
lock database;
load sql <unload file path/name>;
update statistics on database;
unlock database;
set recovery on;
commit;

disconnect all;

Note that I manually invoke all the commands up to the connect, as I sometimes find the drop/create/connect before the reload sometimes happen too quickly for the new database to be created correctly. (There's probably a smarter way to do this, but I've been doing it that way for so long, it's second nature!) Any suggestions for changes or improvements will be gratefully accepted.

Cheers,

[Steve Leighton]

.
For the LOAD try :

Set BULK ON;
Set OUTMESSAGE 32000;

Rows from the LOAD file are then buffered into the output message buffer as much as possible, so reducing network traffic , particularly across a network.
Data values are buffered so that many rows can be sent to the server in one message. i.e. from the LOAD file.
Increasing the size of the output message buffer with SET OUTMESSAGE increases the number of rows that can be buffered in one message to the server, which improves performance.

SET OPTIMIZEDBULK ON;

Similar to BULK option, except:
- Inserts are much faster because there is only a minimum amount of generated transaction log ( if RECOVERY is still on ).
- Any error in the INSERT results in the entire transaction being rolled back, not just the command.
A typical situation is when you cannot set RECOVERY OFF, such as when you are loading a single table into an on line database.
In this situation, the transaction is rolled back when an error occurs, but you can just restart the LOAD.

[TerryP]

Hi Steve,

Thanks for the additional suggestions. I recommend to my clients to perform the unload/reload on the server itself, rather than across the network, so they may not have much effect

However, they're well worth doing if you can't work on the server, though you can always use the "ON SERVER" option after the filenames on the UNLOAD/LOAD commands to minimize the network traffic.

Cheers,