Thursday, September 30, 2021
Tip of the Day: How Healthy is your Database?
Product.......: R:BASE X.5 and R:BASE X.5 Enterprise (Version 10.5)
Build.........: 10.5.3.20812 or higher
Sections......: Database Maintenance
Keywords......: Index, Keys, Default, Data Integrity
Have you ever wondered how healthy your database really is?
Are all defined indexes properly indexed?
Are there any duplicate indexes?
Are all primary/foreign/referenced keys valid?
Are all defined default column values still valid?
Are all defined views still functional?
Is there any inconsistency in performance?
Regardless of when your database was designed, if managed using
the latest table/data designers, built using the commands or the
GUI, or migrated to the current release, a healthy database
should always be an important concern.
Do yourself and your clients a favor by performing one additional
task to validate the integrity of the database, and follow the
steps below.
01. Make a backup copy of the database.
02. Copy the entire database (.RX1-.RX4/.RB1-.RB4 files) on
your local drive in a separate folder with at least twice
as much available free disk space. The below steps use the
.RX1-.RX4 file extension. If you are not ye using the
Enterprise product, use .RB1-.RB4 for the file extension.
03. Start R:BASE using the latest version/update and switch the
current folder to the appropriate database folder on your
local drive.
04. At the R> prompt:
DISCONNECT
SET MESSAGES ON
SET ERROR MESSAGES ON
SET MULTI OFF
-- CONNECT the database with OWNER password, if any
CONNECT DBName IDENTIFIED BY owner password
-- CONNECT the database with no passwords
CONNECT DBName
05. Check the connected database CHARacter settings, especially
IDQUOTES.
At the R> prompt:
CLS
SHOW CHAR
-- Notice the setting for IDQUOTES (the last item on list).
If this setting is blank/null, make sure to set the IDQUOTES
settings to ` (that is a single reversed quote, on the same
key as the ~ tilde).
SET IDQUOTES=`
06. Now create the unload file with NULL set to -0-.
At the R> prompt:
CLS
SET NULL -0-
OUTPUT NewDB.ALL
UNLOAD ALL
OUTPUT SCREEN
This step will create two files (NewDB.ALL and NewDB.LOB)
07. DISCONNECT, and then rename this database to some other
name.
At the R> prompt:
DISCONNECT
RENAME DBName.RX? DBNameBK.RX?
08. Now, let's rebuild the fresh database and see if it passes
the integrity check.
At the R> prompt:
CLS
RUN NewDB.ALL
-- Watch the activity and all messages on the screen.
-- Don't fall asleep. You might miss an important warning.
-- Completion time may vary based on size of the database
-- (number of tables/records/indexes)
If there were no warnings or error messages, you've got a
healthy database. Give yourself a pat on the back!
If there are any warning or -ERROR- message(s), take them
seriously. In that case:
-- DISCONNECT the bad database (built using RUN NewDB.ALL)
At the R> prompt:
DISCONNECT
-- Delete the bad database
At the R> prompt:
DELETE DBName.RX?
DELETE NewDB.ALL
DELETE NewDB.LOB
-- Rename the previously saved database (Step 07.)
RENAME DBNameBK.RX? DBName.RX?
-- CONNECT to the database
CONNECT DBName
09. CORRECT all -ERROR-s accordingly and then repeat Step 06.
10. Do not quit or give up until you see a completely fresh
database without any warnings or errors.
The time spent on this exercise will save you hours of hassle
and embarrassment, instead of chasing something obvious that
should have been taken care of a long time ago.
Very Best R:egards,
Razzak
https://www.rbase.com
http://www.facebook.com/rbase/
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/0M1V6N-1mqIql2BRZ-00tUNc%40mrelay.perfora.net.
