This snippet is useful for customers who never, or rarely, use the cleaner job and want to get rid of obsolete mat table stages.
- The example only outputs the last 10 stages (last WHERE clause) of the mat tables
SELECT a.*
FROM (SELECT "t.Name" as "TableName", "mt.accessState", SUBSTRING("t.Name",0,LOCATE('_st',"t.Name")-1)as "TablePrefix", CAST(SUBSTRING("t.Name",11,LOCATE('_st',"t.Name")-11) as integer) as "TableNumberInfix",
CAST(SUBSTRING("t.Name",LOCATE('_st',"t.Name")+3) as integer)as "MatTableStage", RANK() OVER (PARTITION BY SUBSTRING("t.Name",0,LOCATE('_st',"t.Name")-1) ORDER BY CAST(SUBSTRING("t.Name",LOCATE('_st',"t.Name")+3) as integer) DESC) as "StagePriority"
FROM "SYS.Tables" t INNER JOIN "SYSADMIN.MaterializedTable" mt ON("mt.name" = "t.name")
WHERE "t.SchemaName" = 'dwh' AND "mt.accessState" = 'READY'
ORDER BY CAST(SUBSTRING("t.Name",11,LOCATE('_st',"t.Name")-11) as integer)) as a
WHERE "a.StagePriority" <= 10
ORDER BY "a.TableNumberInfix", "a.StagePriority" ASC
;;
The line 'WHERE a.StagePriority <= 10' marks the recency of stages that we are interested in. You can replace '<=10' with '>10' and see all stages that are older than the 10 current ones.
Please note that the table with stage priority 1 is the single most current one.
With this in mind, we can use a loop to create a procedure that drops all tables that do not belong to the n most current stages. Below is the code to create a procedure that accepts the minimum priority from whence mat table stages shall be dropped:
CREATE VIRTUAL PROCEDURE views.deleteMatTableStages(IN maxStagesToKeep integer NOT NULL)
AS
BEGIN
IF (maxStagesToKeep <1)
BEGIN
ERROR 'The value of the input parameter must be at least 1. You must keep at least the most current stage for each mat table group.';
END
LOOP ON (SELECT a.*
FROM (SELECT "t.Name" as "TableName", "mt.accessState", SUBSTRING("t.Name",0,LOCATE('_st',"t.Name")-1)as "TablePrefix", CAST(SUBSTRING("t.Name",11,LOCATE('_st',"t.Name")-11) as integer) as "TableNumberInfix",
CAST(SUBSTRING("t.Name",LOCATE('_st',"t.Name")+3) as integer)as "MatTableStage", RANK() OVER (PARTITION BY SUBSTRING("t.Name",0,LOCATE('_st',"t.Name")-1) ORDER BY CAST(SUBSTRING("t.Name",LOCATE('_st',"t.Name")+3) as integer) DESC) as "StagePriority"
FROM "SYS.Tables" t INNER JOIN "SYSADMIN.MaterializedTable" mt ON("mt.name" = "t.name")
WHERE "t.SchemaName" = 'dwh' AND "mt.accessState" = 'READY'
ORDER BY CAST(SUBSTRING("t.Name",11,LOCATE('_st',"t.Name")-11) as integer)) as a
WHERE "a.StagePriority" > maxStagesToKeep
ORDER BY "a.TableNumberInfix", "a.StagePriority" ASC) as cur
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE dwh.' || cur.TableName;
END
END;;
The parameter maxStagesToKeep allows to define intuitively how many last stages shall be kept in the system. The procedure above also shows an error message and aborts if the user tries to drop all mat tables completely. This would mean to drop tables which are being used by the Data Virtuality Server at the moment.
Another error will be prompted if the user does not specify a value for maxStagesToKeep.
Comments
0 comments
Please sign in to leave a comment.