Its all about Journaling

JOURNALING

The literal meaning for that is a personal record of occurrences which are kept on a regular basis like a dairy with daily proceedings etc. So how does it applies to the Database world. Journal can be applied at the table level or at the database level. Journal tables are assigned perm space and can reside within the same or on different database. The limitation is only one journal table per database. Journaling is implemented for for data protection and disaster recovery. The whole reason of Permanent Journaling (PJ) is to capture and maintain any chronological changes made to the rows of the journal tables. It helps to protect corporate data for any committed, uncommitted or aborted transactions. since it has the ability to capture before and after snapshots, it is a safe haven for data restore capability. Data in the PJ will only be dropped if it is issued, it withstands system restart.

Syntax to define a Journal table:
CREATE SET TABLE databasename.J1 ,FALLBACK ,
NO BEFORE JOURNAL,
DUAL AFTER JOURNAL,
WITH JOURNAL TABLE = DATABASENAME.vasu_edw_after_jrnl_tbl ,
CHECKSUM = DEFAULT
(
col1 INTEGER NOT NULL,
col2 TIMESTAMP(6))
PRIMARY INDEX ( col1 )

so once defined the Journal there are various methods to handle it:
1. If the size grows then how to control it
2. if for temporarily you want to disable Journaling
3. if you want to permanently remove it?
4. how to handle at table level as opposed to database level?

Lets discuss case by case:

* Note: Journal table information is found in dbc.journals

Journal table size grows and grows until you move to a saved set using the clause "CHECKPOINT WITH SAVE" in the ARCMAIN script. this will move the data from the journal set to the saved set. you can back the saved set and then delete it to regain the space. Any ways let me give you the detail information how this works:

STEP 1:
we defined the journal table in the sql for table J1 and the journal table name is vasu_edw_after_jrnl_tbl. This table grows bigger and bigger.
STEP 2:
we need to reduce the size of it. we need to move this journal table to the saved set and the commands and syntax will be issued from the arcmain utility.
First backup the data tables in the database and then backup the journal tables
with "checkpoint with save " option so once moved to saved set you can delete the
set and thus you can reduce the size of the database.
a) logon TDPIT/backupuser, passwordnetvault2,netvault2009;
archive data tables
(database.tablename) ,
release lock,
use group read lock,
file = testtbls;
logoff;
b) logon tdpid/backupuser,password;
checkpoint (database.vasu_edw_after_jrnl_tbl) ,
with save,
use access lock,
named prdchkpt;
archive journal tables
(database.vasu_edw_after_jrnl_tbl) ,
release lock,
file = prdjrnl;
delete saved journal (dsw_prod_edw_jrnltbls.vasu_edw_after_jrnl_tbl);
logoff;

STEP 3:
If your goal is TURN OFF JOURNALING temporarily both at table and database level
then either MODIFY command is used for the database or the user or ALTER statement can be used for the table level.

ALTER TABLE ., no before journal, no after journal;
for the database level
MODIFY DATABASE AS NO BEFORE JOURNAL, NO AFTER JOURNAL;

once all the tables in the database which were earlier defined as before or after journal are modified using the alter statement, you can now drop the journal table.
so assuming all the alter statements are executed issue the modify database command to drop the journal table.

MODIFY DATABASE AS DROP DEFAULT JOURNAL TABLE = TEST.vasu_edw_after_jrnl_table.
The above one will get rid of the journal table.