How to find Invalid views on SAP Sybase IQ database


This section shows list of invalid view in IQ (SQL: “select U.user_name , T.table_name as object_name from sysobject O, systab T, sysuser U where T.object_id=O.object_id and U.user_id=T.creator and O.status=2 ”).


select U.user_name + '.' + T.table_name from sysobject O, systab T, sysuser U where T.object_id = O.object_id and U.user_id = T.creator and O.status = 2 /* Invalid */ and O.object_type = 2 /* views */
 SYSOBJECT

status
TINYINT
The status of the object. Values include:
  • 1 (valid) - The object is available for use by the database server. This status is synonymous with ENABLED. That is, if you ENABLE an object, the status changes to VALID.
  • 2 (invalid) - An attempt to recompile the object after an internal operation has failed, for example, after a schema-altering modification to an object on which it depends. The database server continues to try to recompile the object whenever it is referenced in a statement.
  • 4 (disabled) - The object has been explicitly disabled by the user, for example using an ALTER TABLE...DISABLE VIEW DEPENDENCIES statement.
object_type
TINYINT
Type of object. Values include:
  • 1 - Table
  • 2 - View
  • 3 - Materialized view
  • 4 - Column
  • 5 - Index
  • 6 - Procedure
  • 7 - Trigger
  • 8 - Event
  • 9 - User
  • 10 - Publication
  • 11 - Remote type
  • 12 - Login mapping
  • 13 - JAR
  • 14 - Java class
  • 16 - Service
  • 17 - Text configuration
  • 18 - Dbspace

Comments

Post a Comment

Popular posts from this blog

SAP SYBASE IQ Partitioned Tables This section help find list of Partitioned tables in IQ   List of all the Partitioned tables in IQ SQL1> select b.table_name,b.table_type,a.create_time,a.update_time,z.dbspace_name from sysiqtable a, systable b, sysfile z where z.file_id=b.file_id and a.table_id=b.table_id and a.table_id in (select distinct table_id from syspartitions)   Details of particular table which is partitioned SQL2> select partition_id,partition_values,partition_name from syspartitions where table_id in (select table_id from systable where table_name=' <table_name> ')
Read more
SAP Sybase IQ Index Fragmentation This section show the fragmentation on indexes of a table in IQ: (SQL: sp_iqindexfragmentation ('table <owner.table>') )” , this Stored procedure can be used to check the index fragmentation. Stored procedure reports information about the percentage of page space taken up within the B-trees, garrays, and bitmap structures in Sybase IQ indexes.   Note: All percentages are truncated to the nearest percentage point. HG indexes also display the value of option GARRAY_FILL_FACTOR_PERCENT . Index types that use a B-tree also display the number of node (nonleaf) pages. These are HG , LF , WD , DATE , and DTTM .   For garrays, the fill percentage calculation does not take into account the reserved space within the garray groups, which is controlled by the GARRAY_FILL_FACTOR_PERCENT option. It specifies the percent of space on each HG garray pages to reserve for future incremental inserts into existing groups. The garray t...
Read more