Sybase IQ stored procedures

The following procedures work specifically on the IQ Store. They are owned by the DBA user ID.

NoteStored procedures that produce size information assume that the database was created with the default block size, as described in “Block size” in Sybase IQ Performance and Tuning Guide. If a database was created with a non-default block size, the output from the following stored procedures is inaccurate: sp_iqestjoin, sp_iqestdbspaces, sp_iqestspace.

Table 1-2: Stored Procedures for the IQ Store

Procedure name

Purpose

sp_iqaddlogin

Adds a new Sybase IQ user account, and specifies the number of current logins the user may have and the password expiration interval

sp_iqcheckdb

Checks the validity of the current database and repairs indexes

sp_iqcolumn

Displays columns in a database and information about them

sp_iqcommandstats

Displays statistics for queries executed during an interval of time

sp_iqconnection

Shows information about connections and versions, including which users are using temporary dbspace, which users are keeping versions alive, what the connections are doing inside Sybase IQ, connection status, and database version status

sp_iqconstraint

Lists referential integrity constraints for the specified table or column

sp_iqcontext

Displays the currently executing statement for each active connection, or for a single connection

sp_iqdbsize

Gives the size of the current database

sp_iqdbspace

Lists detailed information including usage, properties, and types of data on each dbspace.

sp_iqdbspaceinfo

Displays the number of blocks user per index per main or local dbspace for one or all dbspaces.

sp_iqdbstatistics

Reports results of the most recent sp_iqcheckdb

sp_iqdroplogin

Drops an IQ login

sp_iqestjoin

Estimates the space needed to create join indexes for the tables you specify

sp_iqestdbspaces

Estimates the number and size of dbspaces needed for a given total index size

sp_iqestspace

Estimates the amount of space needed to create a database, based on the number of rows in the underlying database tables

sp_iqindex

Lists indexes and information about them. Omitting the parameter lists all indexes in the database. Specifying the table_name parameter lists indexes for this table only.

sp_iqindexinfo

Displays the number of blocks user per index per main or local dbspace for a given object.

sp_iqindex_alt

Same as sp_iqindex, except that for multicolumn indexes it lists one row for each column.

sp_iqindexsize

Gives the size of the specified index

sp_iqjoinindexsize

Gives the size of the specified join index

sp_iqlistexpiredpass words

Lists user accounts with expired passwords

sp_iqlistlockedusers

Lists locked user accounts

sp_iqlistpasswordexpira tions

Lists user accounts, their password creation dates, and number of days password is valid from creation date

sp_iqlocklogin

Locks an IQ user account so that the user cannot log in

sp_iqlocks

Shows information about locks in the IQ Store and the Catalog Store

sp_iqmodifyadmin

Enables or disables Sybase IQ User Administration, and sets defaults for user account characteristics in the IQ_SYSTEM_LOGIN_INFO_TABLE system table

sp_iqmodifylogin

Modifies the maximum number of connections or the password expiration interval for a single user or all users

sp_iqpassword

Adds or changes a password for an IQ login account

sp_iq_process_login

When the LOGIN_PROCEDURE database option is set to sp_iq_process_login, determines which users can log on

sp_iqrelocate

Relocates specified tables and indexes on main dbspaces with relocate mode to main dbspaces with readwrite mode. Can also be used with local dbspaces.

sp_iqspaceinfo

Lists the number of blocks used per object per dbspace.

sp_iqspaceused

Shows information about space available and space used in IQ Store and IQ Temporary Store

sp_iqstatus

Displays a variety of IQ status information about the database

sp_iqtable

Lists tables and information about them. Omitting the parameter lists all tables in the database. Specifying the table_name parameter lists columns for this table only.

sp_iqtablesize

Gives the size of the specified table

sp_iqtransaction

Shows information about transactions and versions

sp_iqview

Displays views in a database and information about them

NoteSeveral multiplex stored procedures are also available. See Chapter 9, “System Procedures” in Sybase IQ Reference Manual for more information.