Reorganize Object File System Tables | Teradata VantageCloud Lake - Reorganize Object File System Tables - Teradata VantageCloud Lake

Lake - Manage and Move Data
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-05-16
dita:mapPath
atx1683670417382.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
atx1683670417382

The default reorganization criteria is that a table has been modified 5 or more times. This factor can be changed using TD_OFSDB.SettingsTbl.SettingName='REORG_FACTOR'.

Query
sel * from td_ofsdb.SettingsTbl where SettingName='REORG_FACTOR';
Result
    SettingName REORG_FACTOR
   DefaultValue 5
   CurrentValue 5
Defaultjson_col ?
Currentjson_col ?
    Description How often an OFS table is reorganized; the default factor is 5 updated versions.

Procedures TD_OFSDB.CreateReorgList and TD_OFSDB.AddReorgObjectsToList (User-defined List)

You can create a list and add tables to be reorganized to the list, along with a specified schedule string using procedures TD_OFSDB.CreateReorgList and TD_OFSDB.AddReorgObjectsToList.

The ScheduleString in the list is used to update TD_OFSDB.ReorgObjectsTbl.NextRuntime. See "ScheduleString Syntax" in OFS Scheduler. Only tables have been changed since the last reorganization will be added to the work table to be reorganized.

Create lists and add tables to lists

The wildcard character ‘%’ can be used for input DatabaseName and TableName.
CALL TD_OFSDB.CreateReorgList('list10', 'REPEAT DAYS=1-7;HOURS=0;',NULL, outtext);
CALL TD_OFSDB.CreateReorgList('list20', 'REPEAT DAYS=1-7;HOURS=0;',NULL, outtext);

CALL TD_OFSDB.AddReorgObjectsToList('list10', 'db10', '%', 'N',
     'add all tables in database db10 to list10', NumTablesAdded);
CALL TD_OFSDB.AddReorgObjectsToList('list10', 'db20', 'tab1', 'Y',
     'exclude table db20.tab1', NumTablesAdded);
CALL TD_OFSDB.AddReorgObjectsToList('list20', 'db20', 'tab2', 'N',
     'add table db20.tab2 to list20', NumTablesAdded);
Query
sel ListName (char(20)), DatabaseName (char(10)), TableName (char(10)), 
    IsExcluded, NextRuntime, CreatedTimeStamp, ScheduleString 
from td_ofsdb.ReorgObjectsV where ListName in ('list10','list20') order by 1,2,3;
Result
ListName DatabaseName  TableName IsExcluded         NextRuntime    CreatedTimeStamp ScheduleString
-------- ------------ ---------- ---------- ------------------- ------------------- ------------------------
list10           DB10     table1 N          2025-01-09 00:00:00 2025-01-08 21:32:05 REPEAT DAYS=1-7;HOURS=0;
list10           DB10     table2 N          2025-01-09 00:00:00 2025-01-08 21:32:05 REPEAT DAYS=1-7;HOURS=0;
list10           DB10     table3 N          2025-01-09 00:00:00 2025-01-08 21:32:05 REPEAT DAYS=1-7;HOURS=0;
list10           DB20       tab1 Y          2025-01-09 00:00:00 2025-01-08 21:32:05 REPEAT DAYS=1-7;HOURS=0;
list20           DB20       tab2 N          2025-01-09 00:00:00 2025-01-08 21:32:05 REPEAT DAYS=1-7;HOURS=0;

Remove tables from a list/Drop a list

CALL TD_OFSDB.RemoveReorgObjectsFromList('list10', 'db10', 'table1',
     'remove db10.table1 from list10', NumTablesRemoved);
CALL TD_OFSDB.DropReorgList('list20', 'drop list list20');
Query
sel ListName (char(20)), DatabaseName (char(10)), TableName (char(10)), 
    IsExcluded, NextRuntime, CreatedTimeStamp, ScheduleString 
from td_ofsdb.ReorgObjectsV where ListName in ('list10','list20') order by 1,2,3;
Result
ListName DatabaseName  TableName IsExcluded         NextRuntime    CreatedTimeStamp ScheduleString
-------- ------------ ---------- ---------- ------------------- ------------------- ------------------------
list10           DB10     table2 N          2025-01-09 00:00:00 2025-01-08 21:32:05 REPEAT DAYS=1-7;HOURS=0;
list10           DB10     table3 N          2025-01-09 00:00:00 2025-01-08 21:32:05 REPEAT DAYS=1-7;HOURS=0;
list10           DB20       tab1 Y          2025-01-09 00:00:00 2025-01-08 21:32:05 REPEAT DAYS=1-7;HOURS=0;

Monitor/Verify the scheduled reorganize activity

Query
sel DatabaseName (char(15)), TableName (char(15)), VersionNumber, TimestampValue, ElapsedTime 
from td_ofsdb.ReorgHistoryV order by 1,2,3,4;
Result
DatabaseName     TableName               VersionNumber              TimestampValue         ElapsedTime
---------------  ---------------  --------------------  --------------------------  ------------------
DB10             table2                              6  2025-01-09 00:00:01.910000      0:00:00.670000
DB10             table2                              8  2025-01-10 00:00:00.910000      0:00:00.760000
DB10             table3                             18  2025-01-09 00:00:01.700000      0:00:01.300000
DB10             table3                             24  2025-01-10 00:00:02.170000      0:00:01.430000