How to cold start an UDB after an active log crash ?
Roberto Barraco
Basing on the sad history lived by Mr. Giulio Alfano(he did erase the
active logs of UDB 5.2 on a AIX platform) I want to know if possible by
IBM laboratory how to cold start a DB2 when you loose the active logs?
Mr. Giulio Alfano has forgottent to explain the scenario of the history
and I want to tell it:
Processor F40 IBM RAM 128 Mb.
O.S: AIX 4.2.1
UDB 5.2
CICS/6000
One only instance
One only database
Almost 400 tables based on 400 SMS tablespaces
Logretain on and userexit on
Bytes allocated almost 3 Gb.
This host is a typical developing system one and not production.
For some unlucky circumnstances (problems with the DAT drive) there was
no previously backup.
Luckly we had made the export of each table at the nigth before.
Now some considerations :
1. UDB is not so robust as they say.
2. I am sure that after the crash every table was OK and readable.
3. It has not been possible to make an offline backup
4. It’s not possible to read each physical file and download it in a
flat file(I don’t know any utility).
5. It’s not possible to preallocate an active log
6. The only possibility: recreate the database
7. During the recreation I wanted to use the output of the db2look
command (I made one contestually to the exports). But the file is a
snapshot of SYSIBM.SYSTABLES in alphabetical order. We needed to create
first all the mothers tables having referential integrity by other
tables based on .
Result : almost 6 hours to recreate the new database .
Even if this host is a developing system one , we have a lot of tables ,
referential integrity , grants , views and so on.
It reflects , in small, the production system.
And if this were happened on production system (90 Gb of tables) ?
Thanks for your patience
Roberto Barraco
.
Tex
>I want to know if possible by
>IBM laboratory how to cold start a DB2 when you loose the active logs?
Hi, Roberto.. here is a procedure which could have some change to bypass the error SQL1042C ("A
system error has occurred"):
Go in the failing database directory (the directory named SQL0000x, found by the command "db2 list
db directory") and look for a file named "SQLOGCTL.LFH" sizing 8192 bytes.
Now you should remove it (you can simply rename it) and have it replaced with the same file coming
from another healty database. if you have only one database, you can create the SAMPLE db (command
db2sampl), copy the file then drop it.
After this step, there's a possibility to succesfully start and connect to database.
Note: This procedure is NOT supported by IBM. If you are able to restart database after these steps,
you should immediately perform an export of all tables and table definitions, then you have to drop
the database and to recreate it. This because you have an instable scenario.
>Now some considerations :
>
>1. UDB is not so robust as they say.
>2. I am sure that after the crash every table was OK and readable.
Yes, data on database *probably* was still readable... However, maybe they're no longer consinstent.
>3. It has not been possible to make an offline backup
This because the database wasn't accessible.
>7. During the recreation I wanted to use the output of the db2look
>command (I made one contestually to the exports). But the file is a
>snapshot of SYSIBM.SYSTABLES in alphabetical order.
mmm, I've always used db2look to have all the DDL definitions for tables, indexes and
ref.integrity.. Maybe you used the wrong flag; the syntax to be used to create a list of all the DDL
needed is:
db2look -d <db_name> -a -e -o <output_file> NOTE: the <output_file> will be created with extension
".txt"
This statement creates a text file with all commands to recreate tables, indexes and ref.
constraint.
The only thing you need to do manually is create the database; if you need to allocate tablespaces
different than the default ones, you also need to create them manually, and modify db2look output
file reflecting this:
db2 create table <schema_name>.<table_name> ( -columns definition ) in <your_tablespace> indexes in
<if_you_need_a_different_ts_for_indexes>;
>And if this were happened on production system (90 Gb of tables) ?
Obviously, any software is aware of defects... We ALWAYS must backup our vital data. An offline
backup (taken every night) can help you in recovering such kind of crashes.. Simply type "db2
restore database <your_db_to_recreate> from <path_of_backup_file>" to have your db recreated, with
all data, tables and definitions.
How I explained to Mr Giulio, log files aren't only swap files used from DB2, which files we can
move, rename or delete when db is offline. Log files store all transaction informations, for open
transactions, but also for COMMITed - ROLLedBACK transactions.
More, keep in mind DB2 write COMMIT/ROLLBACK on database only when the predefinited page size in log
files is full; this for optimizing and performance reasons.
Now, suppose this scenario:
You perform some COMMITs on database;
These data committed are stored in log, DB2 wait to fill all the pages, then writes them to disk;
These data are not still written to database;
-- your database crashes --
You re-connect.. You think 'wow, fortunately I've committed my data!!'
DB2 check database consinstency, and situation stored in log;
DB2 found some transaction committed stored in log, but not ever written to database;
DB2 starts a "Crash recovery", synchronizing data/transaction between log/database.
This little example to explain you, in few words, what are logs for DB2.. He always need to access
them, and ANYONE should handle these files.
The only way certified by IBM to handle log files is to use the 'user exit' utility, which is
responsible to flag the inactive/old log, and move them away from DB2 working dirs, in places where
you can backup/compress/delete them..
Hoping this can help... Bye
--
Tex's new e-Mail: cybe...@technologist.com
========================================================
"I computer non saranno mai a prova di idiota,
perchč gli idioti continuano ad avere troppe risorse"
========================================================
alonso
>
> In data Wed, 01 Sep 1999 08:10:49 GMT, Tex <cybe...@technologist.com> cos' scrisse:
>
> ehm.... Sorry.. It's morning, holiday are finished, I've only a few minutes to wrote the post... So
> I've made some errors using my English...
I have understood everything ;)
Thank you again!
--
alfano giulio
alf...@tin.it
Tex
ehm.... Sorry.. It's morning, holiday are finished, I've only a few minutes to wrote the post... So
I've made some errors using my English...
However, I hope to have well-explained my opinions.
--
Tex's new e-Mail: cybe...@technologist.com
========================================================
"I computer non saranno mai a prova di idiota,
perchè gli idioti continuano ad avere troppe risorse"
========================================================
Roberto Barraco
I have also another email account eagle...@hotmail.com. Does it mean anything to you?
As regard your suggestions , as I am still in holiday, but I had to repair a database too, I will
simulate again the crash (using different database and instances too).
As regard the db2look command I think I use the right flags but in some cases this is not good.
Suppose to have a table named A and another one B. By the db2look command you obtain all you need to
recreate A and B in alphabetical order ( better in order of creator and table name). Now if you have a
foreign key in table A based on table B, you can't use (without changes) the output of db2look command
because DB2 wants 1) create TBALE B and 2) create table A 3) Alter TABLE A.
As regard the backup od database each nigth (how long you spent to backup your data and how many GB you
have and how many tapes or similar?) . If you backup alla data, you have to backup also every nigth all
historical tables (sometimes this kind of table never changes in a month). What about to limit your
nigthly backup of all DB2 system tables? They are very critical ! How ? As you can see , I think that
UDB has not been designed well for managing these situations . There is a lot of difference between DB2
for MVS and DB2 for AIX and this is one!!
I would appreciate your opinion if possible
Thanks
Roberto Barraco
>
Tex
>Hi Tex,
>I have also another email account eagle...@hotmail.com. Does it mean anything to you?
Hemmm... No.. Should it mean anything to me? Do you use it in other newsgroups? (pls, explain me, or
I will not sleep tonight!)
>you can't use (without changes) the output of db2look command
>because DB2 wants 1) create TBALE B and 2) create table A 3) Alter TABLE A.
The point is a bit different: DDL statements to create tables are wrote in the output file sorted by
<schema name>.<table name> (this is right); but statement related to primary key/foreign key are
added later, in the bottom of the file.
So, taking your scenario (table A, B, A referencing B), db2look file will look similar to this:
-- DDL Statements for tables
CREATE TABLE tblA (.........);
CREATE TABLE tblB (.........);
-- DDL Statements for indexes
CREATE UNIQUE INDEX idxA ON tblA (.......);
CREATE INDEX idxA2 ON tblA (.......); <-- Supposing we need more than one index on table A
CREATE UNIQUE INDEX idxB ON tblB (........);
-- DDL Statements for primary keys
ALTER TABLE tblB ADD CONSTRAINT ConstB1 PRIMARY KEY (........);
-- DDL Statements for foreign keys
ALTER TABLE tblA ADD CONSTRAINT ConstA1 FOREIGN KEY (...) REFERENCES tblB ON DELETE etc..etc...;
So, running a db2look-generated script file, the operations performed by DB2 are:
- Create all the tables,
- Create all the indexes for each tables,
- Create all constraint for primary keys,
- Create all constraint for foreign keys, linking them to primary keys....
>As regard the backup od database each nigth (how long you spent to backup your data and how many GB you
>have and how many tapes or similar?)
My situation is the following:
- We have two databases (production), each sizing approx 7GB on a RS/6000;
- 4 test databases, each sizing approx 7GB on another RS/6000;
- Each night we perform an offline backup and an online backup (for production DB), and backups for
all environments (6 CICS, Filesystems, etc.); we use IBM 3570 (Magstar) as tape device.
Obviuosly, if you are using standard 4mm DAT tape drive to backup data, this solution isn't
applicable, due to tape drive speed.
> What about to limit your
>nigthly backup of all DB2 system tables? They are very critical ! How ? As you can see , I think that
>UDB has not been designed well for managing these situations
mmm... I'd prefer to perform online backups during the week (this can be done concurrently with user
activity), and perform offline backup maybe on Saturday night, or Sunday.
So, if you have a crash suppose on Wednesday, you can decide to restore the online bkup performed on
Tuesday, or Monday, and then ROLLFORWARD transactions to wednesday situation (all transactions are
stored in log, so DB2 can rebuild your data), or any time you need (maybe you could prefer to
re-apply transaction only to tuesday situation..). Or, more, you can decide to restore from the
offline backup, losing transactions performed on Monday/Tuesday/Wednesday.
Also, you can create your 'history tables' in different tablespace(s) than other data, and backing
up only tablespaces containing
--
Tex's new e-Mail: cybe...@technologist.com
========================================================
"I computer non saranno mai a prova di idiota,
perchč gli idioti continuano ad avere troppe risorse"
========================================================