sp_iqrebuildindexwide Procedure

Syntax

sp_iqrebuildindexwide [ table_name ] [, table_owner] [, level]

Parameters

table_name – identifies the table. This parameter is required, but can include an empty string. Substituting an empty string for the table_name rebuilds all wide-column tables in the database for the table owner specified in the command. Substituting an empty string for the table_name and table owner rebuilds all wide-column tables in the database.
table_owner – is the owner of the table. An explicit table owner name is optional; the default is an empty string. Substituting an empty string for the table_name rebuilds all wide-column tables in the database for the table owner specified in the command. Using an explicit table_name and an empty string as the table_owner rebuilds the table for all users. Substituting an empty string for table_owner and table owner rebuilds all wide-column tables in the database.
level – determines how sp_iqrebuildindexwide rebuilds the table or tables. This parameter is optional and includes four options:
- Level '1' rebuilds all pre-16.0 columns wider than 255 bytes for a given user
- Level '2' rebuilds all tokenized FPs (i.e., pre-16.0 1/2/3 byte FPs, projectable 1 & 2 byte FP, and 16.0 NBit FP) as well as VARCHAR or VARBINARY columns, and all pre-16.0 columns wider than 255 bytes
- Level '3' rebuilds all fixed Flat FPs, and all pre-16.0 columns wider than 255 bytes
- Level '4' applies levels 1, 2, 3, and rebuilds all pre-16.0 columns wider than 255 bytes, all tokenized FPs, all varchar and varbinary columns, and all FLAT fixed FPs
Omitting the level parameter executes sp_iqrebuildindexwide at level '1'.

Privileges

You must have EXECUTE privilege on the system procedure. You must also have one of the following:

INSERT ANY TABLE system privilege
INSERT privilege on a table to rebuild an index
User own the table

Remarks

CHAR, VARCHAR, BINARY, and VARBINARY columns wider than 255 characters, as well as all LONG VARCHAR and LONG BINARY columns in databases migrated to SAP Sybase IQ 16.0 must be rebuilt before the database engine can perform read/write activities on them

SAP Sybase IQ implicitly rebuilds these type of columns the first time a table is opened for read-write access. sp_iqrebuildindexwide explicitly rebuilds these columns to the state defined by the level parameter.

sp_iqrebuildindexwide writes execution results to the SAP Sybase IQ message file (<dbname>.iqmsg)

Examples

Example 1 – Assume that the vartab table is owned by the DBA and that running

select * from sp_iqrowdensity('table user1.vartab3')

returns these results:

DBA.vartab  rid  Flat style FP
DBA.vartab  lvb1  Long varbinary FP
DBA.vartab  blob2  Long binary FP
DBA.vartab  blob1  Long binary FP
DBA.vartab  vc1  Flat style FP
DBA.vartab  lvc1  Long varchar FP
DBA.vartab  clob2  Long binary FP
DBA.vartab  clob1  Long binary FP
DBA.vartab  part  Flat style FP
DBA.vartab  vb1  Flat style FP

Running sp_iqrebuildindexwide at level '1' with vartab as the table_name and DBA as the table_owner:

call sp_iqrebuildindexwide('vartab', 'DBA', 1)

rebuilds columns clob1, colb2, lvc1, lvb1, blob1, and blob2 and writes the following message in .iqmsg:

Index Rebuild in progress for "DBA"."vartab" column "clob1" column "clob2" column "lvc1" column "lvb1" column "blob1" column "blob2"

Example 2 – Assume that the vartab3 table is owned by the user1 and that running

select * from sp_iqrowdensity('table user1.vartab3')

returns these results:

user1.vartab3  rid  Flat style FP
user1.vartab3  part  Flat style FP
user1.vartab3  vb1  Long varbinary FP
user1.vartab3  tk5  Two Byte FP
user1.vartab3  tk4  One Byte FP
user1.vartab3  tk3  Three Byte FP
user1.vartab3  vc1  Long varchar FP
user1.vartab3  tk2  Projectable Two Byte FP
user1.vartab3  b1  Flat style FP
user1.vartab3  c1  Long varchar FP
user1.vartab3  tk1  Projectable One Byte FP

Running sp_iqrebuildindexwide at level '2' with vartab3 as the table_name and user1 as the table_owner:

sp_iqrebuildindexwide('vartab3', 'user1', 2)

rebuilds columns vc1, vb1, c1, b1, tk1, tk2, tk3, tk4, and tk5 and writes the following message in .iqmsg:

Index Rebuild in progress for "user1"."vartab3" column "vc1" column "vb1" column "c1" column "b1" column "tk1" column "tk2" column "tk3" column "tk4" column "tk5"

Example 3 – Assume that the vartab3 table is owned by the user1 and that running

select * from sp_iqrowdensity('table user1.vartab3')

returns these results:

user1.vartab3  part  Flat style FP
user1.vartab3  c1  2 Bit FP
user1.vartab3  tk4  2 Bit FP
user1.vartab3  tk5  2 Bit FP
user1.vartab3  vb1  2 Bit FP
user1.vartab3  tk3  2 Bit FP
user1.vartab3  tk2  2 Bit FP
user1.vartab3  tk1  2 Bit FP
user1.vartab3  b1  2 Bit FP
user1.vartab3  rid  Flat style FP
user1.vartab3  vc1  2 Bit FP

Running sp_iqrebuildindexwide at level '3' with vartab3 as the table_name and user1 as the table_owner:

call sp_iqrebuildindexwide('vartab3', 'user1', 3)

rebuilds columns rid and part and writes the following message in .iqmsg:

Index Rebuild in progress for "user1"."vartab3" column "rid" column "part"

Running

select * from sp_iqrowdensity('table user1.vartab3')

against the rebuilt table, returns these results:

user1.vartab3  rid  2 Bit FP
user1.vartab3  vc1  2 Bit FP
user1.vartab3  vb1  2 Bit FP
user1.vartab3  b1  2 Bit FP
user1.vartab3  tk1  2 Bit FP
user1.vartab3  tk4  2 Bit FP
user1.vartab3  tk5  2 Bit FP
user1.vartab3  tk3  2 Bit FP
user1.vartab3  c1  2 Bit FP
user1.vartab3  part  2 Bit FP
user1.vartab3  tk2  2 Bit FP