a way to check contents of a iq dbfile

[jmtorres]

Hello,

In IQ 16, is there any way to view or check which objects(user tables, indexex ,etc) are inside a specific dbfile?

In the o,d days of IQ 12.7, sp_dbspaceinfo showed this( because there were no db files)

Thank you

Regards

Jose

[c_baker · Accepted solution]

I think you are looking for sp_iqfile.

sp_iqfile Procedure - Reference: Building Blocks, Tables, and Procedures - SAP Library

Chris

[jmtorres · Accepted solution]

Thanks Chris,

But actually sp_iqfile  or sp_iqfile <dbspace> does not show which objects(user tables, etc) are in the iq db file. For example here , which  tables or sp are in dbfiles "iq_main" and iq_main_02 :

Regards

Jose

[c_baker · Accepted solution]

As you mentioned before, IQ 16 is much different from 12.x.  Now within any dbspace, IQ by default will stripe writes across ALL dbfiles in a dbspace.  With partitioning and object placement strategies, you can only specify the dbspace, not the dbspace dbfile to use.

The blocks for any object are spread across all dbfiles in the dbspace.  You cannot select which file in a dbspace will contain objects.  Assume all dbfiles in the dbspace contain the object.

From the docs "A DBA can determine the dbspace in which tables and indexes are located by running the stored procedures sp_iqspaceinfo, sp_iqdbspaceinfo, and sp_iqindexinfo. These procedures show the number of blocks used by each table and index in each dbspace."

If you need to add dbfiles to a dbspace, you can ALTER DBSPACE ADD...to perform the operation.  Subsequent DML operations will now use this dbfile automatically (writes will now use this dbfile in the striping).

To remove a dbfile from a dbspace, first mark the dbfile as RO using ALTER DBSPACE ALTER DBFILE .. READONLY so the file is no longer writeable by DML operations (the actual OS file is still RW, so the next step will still work).  Then use sp_iqemptyfile to empty the blocks from the dbfile and stripe them across the remaining dbfiles in the dbspace.  Once that is done, you can then ALTER DBSPACE and drop the dbfile from the dbspace.

HTH

Chris

[jmtorres · Accepted solution]

Thanks a lot Chris

Regards

Jose

[former_member232292 · Accepted solution]

Dear Chris,

    But if there's any way to check the table partition's size? -- It's useful for DB maintenance activity -- as we can split the fast growing partition to new dbspace before it grows too large...

    Thanks

Regards

Eisen

[c_baker · Accepted solution]

Taken singly, each of the sp's mentioned, and others such as sp_iqindexinfo, sp_iqfile, etc, can provide you the information you are seeking.

Whether that means you just have to add a dbfile to a dbspace or alter and move objects to another dbspace is up to you.

I would suggest reviewing the docs on all the sp's and see what meets your needs.  Don't forget, you can also take the sp output as a table and further combine several more to get what  you may want.

If the provided sp's do not give the information you need, then you may want to combine them to get what you want.

A simple example below - there is no specific sp that lists whether an HG is tiered or non-tiered (a tiered HG index may or may not be created depending on the setting of CREATE_HG_WITH_EXACT_DISTINCTS), but sp_iqindexmetadata is the only sp that shows the type of HG.  The problem is that there is no 'list' of such indexes.  The below sp provides a list based on sp_iqindex and sp_iqindexmetadata and is given as an example of how to use the output of an sp to call another. 

create or replace procedure DBA.sp_iqshowtiered()

begin

  declare "tiered" char(1);

  select rtrim("table_name") as 'table_name',rtrim("table_owner") as 'table_owner',rtrim("index_name") as 'index_name',rtrim("column_name") as 'column_name',"index_type","unique_index",' ' as "tiered_index"

    into #tier_temp

    from "sp_iqindex"()

    where "index_type" = 'HG';

commit;

--create indexes to avoid index advisor

--create unique HG index tier_HG on #tier_temp(table_name, table_owner, index_name);

create HG index index_name_HG on #tier_temp(index_name);

create HG index table_name_HG on #tier_temp(table_name);

create LF index table_owner_LF on #tier_temp(table_owner);

  for "FORLOOP" as "FORCRSR" dynamic scroll cursor for

    select "table_name","table_owner","index_name" from #tier_temp order by "table_name" asc,"table_owner" asc,"index_name" asc

  do

    execute immediate 'select (case when substring(value2,1,1)=''N'' then ''Y'' else ''N'' end) into tiered from sp_iqindexmetadata (''' || "index_name" || ''',''' || "table_name" || ''',''' || "table_owner" || ''') where value1 = ''Maintains Exact Distinct'';';

    execute immediate 'update #tier_temp set tiered_index = tiered where table_name=''' || "table_name" || ''' and table_owner=''' || "table_owner" || ''' and index_name=''' || "index_name" || ''';'

  end for;

  select * from #tier_temp

end;

I used this sp to help determine whether to rebuild an index using sp_iqrebuildindex with the 'retier' option when I was working on a POC and kept changing the setting of CREATE_HG_WITH_EXACT_DISTINCTS between creating HG indexes.

Chris

[former_member232292 · Accepted solution]

Dear Chris,

   Thanks a lot... Using index size to replace the partition size is a good idea... I'll follow your instruction to test the effect.

   Thanks again.

Regards

Eisen