sp_iqjoinindex Procedure

Displays information about join indexes.

Syntax

sp_iqjoinindex [ left-table-name ], [ left-column-name ], [ left-table-owner ], [ right-table-name ], [ right-column-name ], [ right-table-owner ]

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Usage

The sp_iqjoinindex procedure can be invoked without any parameters. If no parameters are specified, sp_iqjoinindex displays information about all join indexes on IQ base tables. Join index tables are always IQ base tables. Join index tables cannot be temporary tables, remote tables, or proxy tables.

Parameters
Parameter	Description
left-table-name	The name of the table that forms the left side of the join operation.
left-column-name	The name of the column that is part of the left side of the join.
left-table-owner	The owner of the table that forms the left side of the join operation.
right-table-name	The name of the table that forms the right side of the join operation.
right-column-name	The name of the column that is part of the right side of the join.
right-table-owner	The owner of the table that forms the right side of the join operation.

If you do not specify any of the first five parameters, but specify the next parameter in the sequence, you must substitute NULL for the omitted parameters. For example, sp_iqjoinindex NULL, NULL, NULL, t2, n2, DB’ and sp_iqjoinindex t1, NULL, NULL, t2.

sp_iqjoinindex usage examples
Syntax	Output
sp_iqjoinindex	Displays information about all the join indexes
sp_iqjoinindex t1, NULL, DBA	Displays information about all join indexes in which `t1` owned by `DBA` forms the left side of the operation
sp_iqjoinindex t2, n1, DBA	Displays join index information with column `n1` of table `t2` owned by `DBA` as left side of the join
sp_iqjoinindex NULL, NULL, DBA, NULL, NULL, DBA	Displays information about all join indexes in which the left and right side tables are owned by `DBA`
sp_iqjoinindex NULL, NULL, NULL, t2, NULL, NULL	Displays information about all join indexes in which the table `t2` is on the right side of the join operation
sp_iqjoinindex t1, n1, DBA, t2, n1, DBA	Displays information about join indexes in which the left side is column `n1` of table `t1` owned by `DBA` and the right side is column `n1` of table `t2` owned by `DBA`
sp_iqjoinindex non_existing_table	No rows returned, as the table non_existing_table does not exist
sp_iqjoinindex NULL, NULL, non_existing_user	No rows returned, as the user non_existing_user does not exist

Description

The sp_iqjoinindex stored procedure displays information about join indexes in a database. If you specify one or more parameters, the result is filtered by the specified parameters. For example, if left-table-name is specified, sp_iqjoinindex displays all the join indexes in which that table forms the left side of the join. If left-table-owner is specified, sp_iqjoinindex only returns join indexes in which the left table is owned by the specified owner. If no parameters are specified, sp_iqjoinindex displays information about all join indexes in the database.

The sp_iqjoinindex procedure returns information in the following columns:

sp_iqjoinindex columns
Column name	Description
joinindex_name	The name of the join index.
creator	The owner of the join index.
left_table_name	The name of the table that forms the left side of the join operation.
left_table_owner	The name of the owner of the table that forms the left side of the join operation.
left_column_name	The name of the column that is part of the left side of the join.
join_type	The only currently supported value is “=”.
right_table_name	The name of the table that forms the right side of the join operation.
right_table_owner	The name of the owner of the table that forms the right side of the join operation.
right_column_name	The name of the column that is part of the right side of the join.
key_type	Defines the type of join on the keys: NATURAL: a natural join KEY: a key join ON: a left outer/right outer/full join
valid	Indicates whether this join index needs to be synchronized. ‘Y’ means that it does not require synchronization; ‘N’ means that it does require synchronization.
remarks	A comment string.
dbspace_id	Name of the dbspace in which specified join indexes reside.

Examples

Displays information about the join index in which table t1 forms the left side of the join operation:

sp_iqjoinindex t1

joinnidex_name creator left_table_name left_table_owner left_column_name
join_type right_table_name right_table_owner right_column_name key_type
valid dbspace_id remarks
t1_t2_t3_join  DBA     t1              DBA              n1 
=         t2               DBA               n1                NATURAL 
Y    16387    (NULL)

Displays information about the join index in which table t2 forms the left side of the join operation:

sp_iqjoinindex t2

joinnidex_name creator left_table_name left_table_owner left_column_name
join_type right_table_name right_table_owner right_column_name key_type
valid dbspace_id remarks
t1_t2_t3_join  DBA     t2              DBA              n1 
=         t3               DBA               n1                NATURAL 
Y    (NULL)
t1_t2_t3_join  DBA     t2              DBA              name 
=         t3               DBA               name              NATURAL 
Y    16387    ((NULL)

Displays information about join indexes in which the left side is column name of table t2 owned by DBA and the right side is column name of table t3 owned by DBA:

sp_iqjoinindex t2, name, DBA, t3, name, DBA

joinindex_name creator left_table_name left_table_owner left_column_name
join_type right_table_name right_table_owner right_column_name key_type
valid dbspace_id remarks
t1_t2_t3_join  DBA     t2              DBA              name 
=         t3               DBA               name              NATURAL 
Y    16387    ((NULL)