Lists objects of type table and join index and their sub-objects (columns, indexes, metadata, primary keys, unique constraints, foreign keys, and partitions) for a given dbspace.
sp_iqdbspaceobjectinfo [ dbspace-name ] [ , owner_name ] [ , object_name ] [ , object-type ]
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
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 and join indexes with the specified owner. If not specified, sp_iqdbspaceobjectinfo displays information for tables and join indexes for all users in the database.
object-name Name of the table or join index. If not specified, sp_iqdbspaceobjectinfo displays information for all tables and join indexes in the database.
object-type Valid object types are table (for table information) or joinindex (for join index information). If unspecified, object type defaults to table.
All parameters are optional and any parameter may be supplied independent of the value of other parameters.
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.
For tables, sp_iqdbspaceobjectinfo displays summary information for all associated sub-objects. For join indexes, it displays sizing information for the join index and for all of its associated sub-objects. The output of the stored procedure is sorted by dbspace_name, owner and object_name.
sp_iqdbspaceobjectinfo displays the following information, based on the input parameter values:
Column name |
Description |
|---|---|
dbspace_name |
Name of the dbspace. |
object_type |
Table or join index. |
owner |
Name of the owner of the object. |
object_name |
Name of the object (of type tables and join indexes only) located on the dbspace. |
object_id |
Global object id of the object. |
id |
Table id or join-index 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 displayed 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. Displayed 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) to denote if the metadata information of the sub-object is also located on this dbspace. |
primary_key |
Boolean field (1/0) to denote if the primary key of the table, if any, is located on this dbspace. |
unique_constraint |
Number of unique constraints on the table which are located on the given dbspace. Displayed 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 which are located on the given dbspace. Displayed 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 which are located on the given dbspace. Displayed in the form n/N (n out of total N partitions of the table are in the given dbspace). |
Displays information about a specific dbspace in the database:
sp_iqdbspaceobjectinfo iq_main
dbspace_name object_type owner object_name object_id id columns iq_main table DBA emp1 3689 741 4/4 iq_main table DBA iq_dummy 3686 740 1/1 iq_main table DBA sale 3698 742 4/4 iq_main table GROUPO Contacts 3538 732 12/12 iq_main table GROUPO Customers 3515 731 10/10 iq_main table GROUPO Departments 3632 738 3/3 iq_main table GROUPO Employees 3641 739 21/21 iq_main table GROUPO FinancialCodes 3612 736 3/3 iq_main table GROUPO FinancialData 3621 737 4/4 iq_main table GROUPO Products 3593 735 8/8 iq_main table GROUPO SalesOrderItems 3580 734 5/5 iq_main 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 object_type owner object_name object_id id columns iq_main table GROUPO Contacts 3538 732 12/12 iq_main table GROUPO Customers 3515 731 10/10 iq_main table GROUPO Departments 3632 738 3/3 iq_main table GROUPO Employees 3641 739 21/21 iq_main table GROUPO FinancialCodes 3612 736 3/3 iq_main table GROUPO FinancialData 3621 737 4/4 iq_main table GROUPO Products 3593 735 8/8 iq_main table GROUPO SalesOrderItems 3580 734 5/5 iq_main 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
Uses sp_iqdbspaceobjectinfo to construct commands that can be used to move objects. 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;
