GBase 8a O&M Custom Stored Procedures: A Handy Toolkit for DBAs

Managing a distributed MPP database requires quick visibility into running queries, disk usage, and data distribution across nodes. GBase 8a, the China-domestically developed MPP cluster database from GBASE, provides a set of system tables that can be queried directly—but wrapping those queries into reusable stored procedures makes daily operations far more efficient.

This article collects seven custom stored procedures that every GBase 8a DBA should have in their toolkit. From dynamic SQL execution to segment-level space and row-count analysis, these procedures cover the most common O&M scenarios in a gbase database environment.

General-Purpose Utilities

executeSQL — Dynamic SQL Execution

A foundational helper that takes a SQL string and executes it dynamically. Almost every other procedure in this toolkit calls executeSQL under the hood. Keep in mind the maximum SQL string length is 10 KB.

delimiter //
create procedure executeSQL( S_SQL_TMP VARCHAR(21000) )
BEGIN
  set @executeSQL_sql = S_SQL_TMP;
  PREPARE executeSQL_s1 FROM @executeSQL_sql;
  EXECUTE executeSQL_s1;
  DEALLOCATE PREPARE executeSQL_s1;
END //
delimiter ;

check_version — Version Check

Some features only work on specific GBase 8a versions. This function checks whether the current version string contains a given substring—useful for guarding version-dependent logic.

drop function if exists check_version;
delimiter //
create function check_version(ver varchar) returns boolean
begin
  select instr(version(),ver) into @rtn;
  return @rtn;
end //
delimiter ;

Cluster and Node-Level SQL Monitoring

sp_cluster_processlist — Running Queries at the Cluster Level

Retrieves currently executing SQL tasks from the coordinator nodes. You can control how much of the query text to show and how many rows to return.

drop procedure if exists sp_cluster_processlist;
delimiter //
create procedure sp_cluster_processlist(showSize int,topN int)
begin
  set @sql=concat('select COORDINATOR_NAME, ID, user, host, command, start_time, time, state, substring(info,0,',showSize,') info
  from information_schema.COORDINATORS_TASK_INFORMATION
  where command=\'query\' and info is not null
  and info not like \'%information_schema.processlist%\'
  order by time desc limit ',topN);
  call executeSQL(@sql);
end //
delimiter ;
call sp_cluster_processlist(100,10);

sp_node_processlist — Running Queries at the Data Node Level

Drills down to individual data nodes, showing what each node is currently executing. Essential for diagnosing skewed workloads.

drop procedure if exists sp_node_processlist;
delimiter //
create procedure sp_node_processlist(showSize int,topN int)
begin
  set @sql=concat('select NODE_NAME, ID, user, host, command, start_time, time, state, substring(info,0,',showSize,') info
  from information_schema.GNODES_TASK_INFORMATION
  where command=\'query\' and info is not null
  and info not like \'%information_schema.processlist%\'
  order by time desc limit ',topN);
  call executeSQL(@sql);
end //
delimiter ;
call sp_node_processlist(100,10);

Table Space and Segment Statistics

sp_table_size — Table Disk Space Summary

Returns the total disk space occupied by a given table across the entire gbase database cluster.

drop procedure if exists sp_table_size;
delimiter //
create procedure sp_table_size(dbname varchar,tbname varchar)
begin
  set @sql=concat('select * from information_schema.cluster_tables a
  where table_schema=\'',dbname,'\' and table_name=\'',tbname,'\'');
  call executeSQL(@sql);
end //
delimiter ;
call sp_table_size('testdb','t1');

sp_table_segment_size — Per-Node Segment Disk Usage

Goes one level deeper: for each data node, it shows the segment's data size, storage size, and the percentage of the total table data it holds.

drop procedure if exists sp_table_segment_size;
delimiter //
create procedure sp_table_segment_size(dbname varchar,tbname varchar)
begin
  set @sql=concat('select SUFFIX,HOST,TABLE_DATA_SIZE,TABLE_STORAGE_SIZE,DATA_PERCENT
  from information_schema.CLUSTER_TABLE_SEGMENTS a
  where table_schema=\'',dbname,'\' and table_name=\'',tbname,'\'');
  call executeSQL(@sql);
end //
delimiter ;
call sp_table_segment_size('testdb','t1');

sp_table_segment_count — Row Count Per Segment

Counts rows on each segment. Requires GBase 8a version 9.5.3 or above. The procedure automatically checks the version and exits gracefully if the requirement is not met.

drop procedure if exists sp_table_segment_count;
delimiter //
create procedure sp_table_segment_count(dbname varchar,tbname varchar)
main:begin
  select check_version('9.5.3') into @rtn from dual;
  if !@rtn then
    select 'this function need 9.5.3 +';
    leave main;
  end if;
  call executeSQL('set gcluster_segment_id_replace=1');
  set @sql=concat('select segment_id,count(*) from ',dbname,'.',tbname,' group by segment_id');
  call executeSQL(@sql);
end //
delimiter ;
call sp_table_segment_count('testdb','t1');

Listing All Stored Procedures in the Current Database

show procedure status;

How These Fit Together

These seven procedures form a practical O&M stack. Start with sp_cluster_processlist or sp_node_processlist when you suspect a slow query is dragging down the cluster. When storage becomes a concern, use sp_table_size to identify large tables, then sp_table_segment_size to check whether data is evenly distributed across nodes. If you need row-level detail, sp_table_segment_count gives you per-segment counts—provided you're on 9.5.3 or later.

GBASE continues to enhance the GBase 8a MPP Cluster with each release, and these custom procedures can be extended or adapted as new system views become available. Building a personal library of reusable O&M procedures is one of the best investments a DBA can make when managing a China-domestically developed, independently controlled database platform at scale.

Looking ahead, as GBase 8a's adoption grows, having a solid set of operational tools will help teams maintain large clusters with less toil and greater confidence.