Archives of the TeradataForum

Message Posted: Mon, 26 Jun 2006 @ 15:42:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Here is a Knowledge Article on the subject.

-dave.clark

------------------------------------------------

Question:

How do you remove or reduce the size of a Permanent Journal?

Answer:

Once defined, a permanent journal can only be deleted after stopping the journaling being done to that journal. Use the ALTER USER or ALTER DATABASE statement to stop the journaling and optionally drop the table.

If you wish to reduce the size of a permanent journal without removing the journal entirely, you must use the Archive utility ARCMAIN. Typically this is done after using the "CHECKPOINT WITH SAVE" command to save the journal.

     CREATE A SAMPLE PERMANENT JOURNAL (PJ)
     --------------------------------------
     create database temp AS PERM-10000000
     after journal default journal table - tempPJ;


     ISSUE SQL TO POPULATE THE PJ
     ----------------------------
     create table temp.t1(c1 int);
     ins into temp.t1(1);
     ins into temp.t1(2);


     WHAT PJ'S ARE DEFINED?
     ----------------------
     sel dbase.databasename (FORMAT 'X(15)'),
     tvm.tvmname (FORMAT 'X(25)')
     from dbc.tvm,dbc.dbase where
     dbase.databaseid-tvm.databaseid
     and tvm.tablekind-'j'
     order by 1,2;

     DatabaseName     TVMName
     ---------------  -------------
     temp             tempPJ


     WHAT IS THE SIZE OF THE PJ?
     ---------------------------
     sel sum(currentperm)
     from dbc.tablesize where databasename-'temp'
     and tablename - 'tempPJ';


     Sum(CurrentPerm)
     ----------------
                 5,120


     ONCE DEFINED, A PERMANENT JOURNAL CAN ONLY BE DELETED AFTER STOPPING THE
     JOURNALING BEING DONE TO THAT JOURNAL.  TO IDENTIFY THE DATABASES OR
     TABLES BEING JOURNALED, USE THE FOLLOWING SQL:


     DATABASES OR USERS FOR WHOM A DEFAULT JOURNAL TABLE WAS SPECIFIED:
     ------------------------------------------------------------------
     sel d.databasename (title'Database')
        ,trim(dx.databasename)||'.'||trim(t.tvmname)(title'Journal')
     from DBC.Dbase d
         ,DBC.TVM   t
         ,DBC.Dbase dx
     where d.journalid is not null
      and  d.journalid <> '00'xb
      and  d.journalid = t.tvmid
      and  t.databaseid - dx.databaseid
     order by 1;


     WHAT TABLES ARE BEING JOURNALED?
     --------------------------------
     sel TRIM(Tables_DB)||'.'||TableName
         (TITLE 'Table', CHAR(26))
        ,'Assigned To' (TITLE ' ')
        ,TRIM(jOURNALS_db)||'.'||JournalName
         (title'Journals',CHAR(26))
     from DBC.Journals
     order by 1,2;


     TO STOP THE JOURNALING AND (OPTIONALLY) DELETE THE JOURNAL:
     -----------------------------------------------------------
     alter table temp.t1, no before journal, no after journal;
     modify database temp as no after journal;
     modify database temp as drop default journal table-temp.tempPJ;


     IF YOU WISH TO REDUCE THE SIZE OF A PERMANENT JOURNAL WITHOUT REMOVING
     THE JOURNAL ENTIRELY, YOU MUST USE THE ARCHIVE UTILITY "ARCMAIN".
     TYPICALLY THIS IS DONE AFTER USING THE "CHECKPOINT WITH SAVE" COMMAND TO
     SAVE THE JOURNAL.


     THE EXAMPLE BELOW ILLUSTRATES HOW TO ARCHIVE JOURNAL TABLES.  IN THIS
     EXAMPLE, JOURNAL TABLES FOR DATABASE TEMP ARE ARCHIVED TO A FILE WITH A
     FILENAME OF "ARCHIV1".  WE THEN DELETE  ROWS FROM THE TABLE, PRESERVING
     THE TABLE ITSELF.


     CREATE THE ARCMAIN JOB:
     -----------------------
     # vi /tmp/arcjob

     LOGON DBC,DBC;           <<< change the login to that used by
     the site
     CHECKPOINT (temp) ALL, WITH SAVE;
     ARCHIVE JOURNAL TABLES (temp) ALL,
     RELEASE LOCK,
     FILE-ARCHIV1;
     DELETE SAVED JOURNAL (temp.tempPJ);
     LOGOFF;


     HERE'S AN ALTERNATE JOB WITHOUT THE SAVE PJ OPTION:
     ------------------------------------------------
     LOGON DBC,DBC;     <<< change the login to that used by the site
     DELETE SAVED JOURNAL (temp.tempPJ);
     LOGOFF;


     SUBMIT THE ARCMAIN JOB:
     -----------------------
     # arcmain sessions-4  

 
 
 
 


 
 
 
 
 
 
 
 
 
 
 
 
 
<Prev
 
Next>
 
 
 
<<First
 
<Prev
 
Next>
 
Last>>