Over the past year, I've gotten quite a few questions about a new feature that SAP has added to SAP IQ (SAP IQ 16.1 PL03), SAP HANA dynamic tiering (SAP HANA 2 SP04), and to the SAP HANA Cloud, data lake (all versions): Zone Maps. I included a zone map discussion in my blog
Index Strategy for SAP HANA Cloud, Data Lake.
Recently, though, I am getting more and more questions on how to determine whether or not zone maps exist. This is only applicable to customers that have done a database restore or upgraded from previous versions where the feature did not exist (SAP IQ and SAP HANA, dynamic tiering users). Being somewhat cautious and always wanting to double check things, even those that have built new systems can use this just to make sure that all is working properly (this feature is always on, so it should be).
I have take the system stored procedure,
sp_iqzonemapenable, and put a wrapper around it so that it will traverse your database looking for tables that do not have zone maps for any reason. You will only see output for objects that are missing zone maps.
The procedure is called
sp_iqzonemaps.
call sp_iqzonemaps ( [summary_info], [tblnm], [tblown] )
- summary_info -- Y|N flag that tells the procedure to print the summary information (just owner and table) or detailed information which includes the commands to rebuild columns to enable zone maps.
- tblnm -- the full or partial table name
- tblown -- the full or partial object owner
- Both the table name and table owner can use SQL wildcards like '_' and '%'
- Use 'table%' to run it on all tables that begin with "table"
- Use '%table%' to run it on all tables that contain the word "table"
The SAP IQ and SAP HANA, data lake source code for this is here. Feel free to use it for your needs.
--*******DISCLAIMER*********
--This script is provided "as is" without warranty or support of any kind.
--*******DISCLAIMER*********
drop procedure if exists sp_iqzonemaps;
create procedure sp_iqzonemaps(
in summary_info varchar(3) default 'Y',
in tblnm varchar(128) default '%',
in tblown varchar(128) default '%' )
begin
declare local temporary table ZoneMap_res (
table_owner varchar(128)
, table_name varchar(128)
, RebuildCommand varchar(255)
) in SYSTEM;
declare o varchar(128);
declare t varchar(128);
declare ot varchar(255);
FOR FORLOOP as FORCRSR CURSOR
FOR select table_name, user_name table_owner
from sys.systable, sys.sysuser
where creator <> 3 and server_type = 'IQ'
and table_type <> 'PARTITION'
and creator=user_id
and table_owner like tblown
and table_name like tblnm
do
set o=table_owner;
set t=table_name;
set ot=o+'.'+t;
-- uncomment to see progress
--message 'Table: '||ot to client;
insert into ZoneMap_res
select o,t,rebuildcommand
from dbo.sp_iqzonemapenable( t,o)
where RebuildCommand not like 'No indexes require rebuilding%'
and RebuildCommand not like 'List of FP indexes to be rebuilt%'
;
end for;
-- output
select distinct table_owner, table_name
, convert( varchar(255), case lower( summary_info ) when 'y' then
'Some columns need rebuilding'
else RebuildCommand
end ) Notes
from ZoneMap_res
order by 1,2;
end;
A system where all columns do contain zone maps will not yield any output:
call sp_iqzonemaps();
table_owner table_name Notes
----------------------------
(0 rows)
An example, with summary output:
call sp_iqzonemaps( tblnm='Cust%' );
table_owner table_name Notes
---------------------------------------------------
tpch_user customer Some columns need rebuilding
(1 rows)
An example, with detailed output:
call sp_iqzonemaps( 'N', 'Cust%' );
-- equivalent to
-- call sp_iqzonemaps( summary_info='N', tblnm='Cust%' );
table_owner table_name Notes
-----------------------------------------------------------------------------------------------
tpch_user customer call "sp_iqrebuildindex"( 'tpch_user.customer', 'column c_phone')
tpch_user customer call "sp_iqrebuildindex"( 'tpch_user.customer', 'column c_nationkey 25')
tpch_user customer call "sp_iqrebuildindex"( 'tpch_user.customer', 'column c_name')
tpch_user customer call "sp_iqrebuildindex"( 'tpch_user.customer', 'column c_mktsegment 5')
tpch_user customer call "sp_iqrebuildindex"( 'tpch_user.customer', 'column c_custkey')
tpch_user customer call "sp_iqrebuildindex"( 'tpch_user.customer', 'column c_comment')
tpch_user customer call "sp_iqrebuildindex"( 'tpch_user.customer', 'column c_address')
tpch_user customer call "sp_iqrebuildindex"( 'tpch_user.customer', 'column c_acctbal')
(8 rows)
I would like to draw your attention to two things in the detailed output. At the end of the
sp_iqrebuildindex commands, you will notice that most lines have a number at the end. Some lines do not. The manuals for
sp_iqzonemapenable doesn't currently explain what that number is and what it does to the system. For details, you can look at the manuals for
the sp_iqrebuildindex command. This number, if present, represents the current cardinality or number of distinct values in that column. This allows SAP IQ to rebuild that column with a tailored, optimized n-bit index structure with exact cardinality.
Enjoy! Please reach out to be via the comments with any questions or comments.
