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.
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
All parameters are optional and any parameter may be supplied independent of the value of other 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 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. |
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. |
dbspace_id |
Identifier 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). |
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
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
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;