sp_iqdbspaceobjectinfo Procedure

Lists objects and subobjects of type table (including columns, indexes, metadata, primary keys, unique constraints, foreign keys, and partitions) for a given dbspace. Not supported for RLV dbspaces.

Syntax

sp_iqdbspaceobjectinfo [ dbspace-name ] [ , owner_name ] [ ,  object_name ] [ , object-type ]

Privileges

No specific system privilege required.

Usage

All parameters are optional and any parameter may be supplied independent of the value of other parameters.

Parameters

Parameter

Description

dbspace-name

If specified, sp_iqdbspaceobjectinfo displays output only for the specified dbspace. Otherwise, it shows information for all dbspaces in the database.

owner-name

Owner of the object. If specified, sp_iqdbspaceobjectinfo displays output only for tables with the specified owner. If not specified, sp_iqdbspaceobjectinfo displays information for tables for all users in the database.

object-name

Name of the table. If not specified, sp_iqdbspaceobjectinfo displays information for all tables in the database.

object-type

Valid object types for table objects.

The sp_iqdbspaceobjectinfo stored procedure supports wildcard characters for interpreting dbspace_name, object_name, and owner_name. It displays information for all dbspaces that match the given pattern in the same way as the LIKE clause matches patterns inside queries.

Description

The procedure returns no results if you specify an RLV dbspace.

For tables, sp_iqdbspaceobjectinfo displays summary information for all associated subobjects sorted by dbspace_name, owner and object_name.

sp_iqdbspaceobjectinfo displays the following information, based on the input parameter values:

sp_iqdbspaceobjectinfo columns

Column Name

Description

dbspace_name

Name of the dbspace.

dbspace_id

Identifier of the dbspace.

object_type

Table.

owner

Name of the owner of the object.

object_name

Name of the table object on the dbspace.

object_id

Global object ID of the object.

id

Table ID of the object.

columns

Number of table columns which are located on the given dbspace. If a column or one of the column-partitions is located on a dbspace, it is counted to be present on that dbspace. The result is shown in the form n/N (n out of total N columns of the table are on the given dbspace).

indexes

Number of user-defined indexes on the table which are located on the given dbspace. Shown in the form n/N (n out of total N indexes on the table are on the given dbspace). This does not contain indexes which are system-generated, such as FP indexes and HG indexes in the case of unique constraints.

metadata

Boolean field (Y/N) that denotes whether the metadata information of the subobject is also located on this dbspace.

primary_key

Boolean field (1/0) that denotes whether the primary key of the table, if any, is located on this dbspace.

unique_constraint

Number of unique constraints on the table that are located on the given dbspace. Appears in the form n/N (n out of total N unique constraints on the table are in the given dbspace).

foreign_key

Number of foreign_keys on the table that are located on the given dbspace. Appears in the form n/N (n out of total N foreign keys on the table are in the given dbspace).

partitions

Number of partitions of the table that are located on the given dbspace. Appears in the form n/N (n out of total N partitions of the table are in the given dbspace).

Examples

These examples show objects in the iqdemo database to better illustrate output. iqdemo includes a sample user dbspace named iq_main that may not be present in your own databases.

Displays information about a specific dbspace in the database:
sp_iqdbspaceobjectinfo iq_main
dbspace_name  dbspace_id object_type  owner   object_name  object_id  id  columns
iq_main       16387      table        DBA     emp1           3689     741  4/4
iq_main       16387      table        DBA     iq_dummy       3686     740  1/1
iq_main       16387      table        DBA     sale           3698     742  4/4
iq_main       16387      table        GROUPO  Contacts       3538     732  12/12
iq_main       16387      table        GROUPO  Customers      3515     731  10/10
iq_main       16387      table        GROUPO  Departments    3632     738  3/3
iq_main       16387      table        GROUPO  Employees      3641     739  21/21
iq_main       16387      table        GROUPO  FinancialCodes 3612     736  3/3
iq_main       16387      table        GROUPO  FinancialData  3621     737  4/4
iq_main       16387      table        GROUPO  Products       3593     735  8/8
iq_main       16387      table        GROUPO  SalesOrderItems3580     734  5/5
iq_main       16387      table        GROUPO  SalesOrders    3565     733  6/6

indexes  metadata  primary_key  unique_constraint  foreign_key  partitions
0/0      Y                   0  0/0                0/0          0/0
0/0      Y                   0  0/0                0/0          0/0
0/0      Y                   0  0/0                0/0          0/0
0/0      Y                   1  0/0                1/1          0/0
1/1      Y                   1  0/0                0/0          0/0
0/0      Y                   1  0/0                1/1          0/0
0/0      Y                   1  0/0                1/1          0/0
0/0      Y                   1  0/0                0/0          0/0
0/0      Y                   1  0/0                1/1          0/0
4/4      Y                   1  0/0                0/0          0/0
0/0      Y                   1  0/0                2/2          0/0
0/0      Y                   1  0/0                3/3          0/0
Displays information about the objects owned by a specific user in a specific dbspace in the database:
sp_iqdbspaceobjectinfo iq_main,GROUPO
dbspace_name  dbspace_id object_type  owner   object_name    object_id  id  columns
iq_main       16387      table        GROUPO  Contacts       3538       732 2/12
iq_main       16387      table        GROUPO  Customers      3515       731 10/10
iq_main       16387      table        GROUPO  Departments    3632       738 3/3
iq_main       16387      table        GROUPO  Employees      3641       739 21/21
iq_main       16387      table        GROUPO  FinancialCodes 3612       736 3/3
iq_main       16387      table        GROUPO  FinancialData  3621       737 4/4
iq_main       16387      table        GROUPO  Products       3593       735 8/8
iq_main       16387      table        GROUPO  SalesOrderItems3580       734 5/5
iq_main       16387      table        GROUPO  SalesOrders    3565       733 6/6

indexes  metadata  primary_key  unique_constraint  foreign_key  partitions
0/0      Y                   1  0/0                1/1          0/0
1/1      Y                   1  0/0                0/0          0/0
0/0      Y                   1  0/0                1/1          0/0
0/0      Y                   1  0/0                1/1          0/0
0/0      Y                   1  0/0                0/0          0/0
0/0      Y                   1  0/0                1/1          0/0
4/4      Y                   1  0/0                0/0          0/0
0/0      Y                   1  0/0                2/2          0/0
0/0      Y                   1  0/0                3/3          0/0
In this example, the commands move all tables on dbspace_x to dbspace_y.
SELECT 'ALTER TABLE ' || owner || '.' || 
object_name || ' MOVE TO dbspace_y;'
FROM sp_iqdbspaceobjectinfo()
WHERE object_type = 'table' AND
dbspace_name = 'dbspace_x';
The following ALTER TABLE commands are the result:
ALTER TABLE DBA.dt1 MOVE TO dbspace_y;
ALTER TABLE DBA.dt2 MOVE TO dbspace_y;
ALTER TABLE DBA.dt3 MOVE TO dbspace_y;
Parent topic: Alphabetical List of System Stored Procedures