Journaling

[vidhya]

Hi,

I am in the beginners stage in INGRES...

To enable journaling on single table I have used the sql stmt

SET JOURNALING ON <tablename>;

Is there any possibility to turn on Journaling for multi tables at a time.

For multi tables, can I use

SET JOURNALING ON <tablename1>, <tablename2>, <tablename3>;

My question may be silly... But please anyone can help me in this.

Regards,
Vidhya.

[kschendel]

The usual default is to create tables with journaling enabled; you don't need to use the set journaling statement unless you created the table explicitly WITH NOJOURNALING, and now want to change that.

(The default_journaling parameter in config.dat controls whether tables are created with journaling on by default.)

I don't believe you can specify multiple tables in the SET JOURNALING statement. There's no deep dark reason I can think of, it's just not implemented that way.

[gerhard.hofmann]

Hi Vidhya,

as Karl already pointed out, it is most likely that all your tables are already journaled.

To be able to recover a DB with
rollforwarddb +j...
it is necessary that
1. journaling has been activated for the DB with a ckpdb +j...
2. journaling is on for all the tables in the DB

You can check this with
1. infodb dbname | more
2. select table_name, is_journalled from iitables where is_journalled != 'Y'
and table_type = 'T' and table_owner != '$ingres'
(should return 0 rows)

I have written 2 Batch-Files
setjournal.bat [vnode::]db
setjournalonall.bat
that make use of Ingres' sreport / report tools and system catalogs and can be used to run a "set journal on..." for any non-journalled table in a db.

setjournalonall.bat will (on a Windows-based Ingres server) query iidbdb for all database names on the machine and do the job for each db.

Regards
Gerhard

[vidhya]

Thanx Karl...

And Thanx Gerhard....

Actually the journal was enabled in DB and in some of the tables (13 out of 84) it was disabled through coding. (SET NOLOGGING)

Now my work is to write a SQL script to switch ON the journaling option for those tables alone. Similarly I have to write script for 7 databases.

Thanks for your clarification. Really it helps me to understand the concept much more.

Regards,
Vidhya.

[rhann]

Vidhya, it may just be a typo in your posting, but in case it isn't, NOLOGGING is very different from NOJOURNALING. If you really meant NOLOGGING but you aren't sure of the difference you should read up on it before you do anything.

NOLOGGING turns off transaction logging. That has several effects, but the important one is that if an error occurs, of any kind whatever, Ingres will have no choice but to mark the database as inconsistent. In fact the chances are it really will be inconsistent, and you will have no way to know where or how, so you will be forced to recover from a checkpoint. Furthermore you will not be able to do a full rollforward recovery until you kick everyone off and do an offline checkpoint.

Using NOLOGGING requires special care, and even then it couldn't be called "best practice" (IMO it is very poor practice). If I found a system using it I would spend a lot of time proving to myself that it is actually unavoidable.

Forgive me for mentioning it if you already know all this.