sysadmin database task to check table next extent will fit into

mholzbauer's picture

 

It could be a problem for the application (or the admin) when database tables next extent size
is larger than the dbspace free size. Here is a example function/task for the IDS 11.X sysadmin database to
check these events:

 
DATABASE sysadmin;

DROP FUNCTION check_table_extent1;

CREATE FUNCTION check_table_extent1(task_id INT, task_seq INT) RETURNING INTEGER
DEFINE database_name CHAR(129);
DEFINE table_owner CHAR(33);
DEFINE table_name CHAR(129);
DEFINE dbspace_name CHAR(129);
DEFINE next_extent_size INTEGER;
DEFINE dbspace_pages_free INTEGER;

SELECT b.name dbspace,
       SUM(a.nfree) pagesfree
 FROM sysmaster:syschunks a,
      sysmaster:sysdbspaces b
  WHERE a.dbsnum = b.dbsnum
 GROUP BY 1
  INTO TEMP __t_dbsp_free_1;

FOREACH SELECT dbsname,
               owner,
               tabname,
               c.dbspace,
               b.nextsiz,
               c.pagesfree
         INTO database_name, table_owner, table_name, dbspace_name, next_extent_size, dbspace_pages_free
         FROM sysmaster:systabnames a,
              sysmaster:sysptnhdr b,
              __t_dbsp_free_1 c
          WHERE a.partnum = b.partnum
            AND b.nextsiz > c.pagesfree
            AND DBINFO("DBSPACE", a.partnum) = c.dbspace

  INSERT INTO ph_alert
          (ID, alert_task_id, alert_task_seq, alert_type,
           alert_color, alert_object_type,
           alert_object_name, alert_message, alert_action)
          VALUES
          (0,task_id, task_seq, "WARNING", "RED", "TABLE",
           TRIM(database_name) || ":" || TRIM(table_owner) || "." || TRIM(table_name),
           "Table [" || TRIM(database_name) || ":" || TRIM(table_owner) || "." || TRIM(table_name) || "] next extent (" ||
           next_extent_size || " pages) will not fit into dbspace [" || TRIM(dbspace_name) || "].",
           NULL
          );

END FOREACH

DROP TABLE __t_dbsp_free_1;
RETURN 0;

END FUNCTION;


DELETE FROM ph_task WHERE tk_name = "check_table_extent1";

INSERT INTO ph_task
(
tk_name,
tk_type,
tk_group,
tk_description,
tk_execute,
tk_start_time,
tk_stop_time,
tk_frequency
)
VALUES
(
"check_table_extent1",
"TASK",
"TABLES",
"Checks that table next extent will fit into dbspace.",
"check_table_extent1",
DATETIME(00:00:00) HOUR TO SECOND,
NULL,
INTERVAL ( 4 ) HOUR TO HOUR
);