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

Enable and Disable Trace in SAP Sybase IQ database Enable Trace in IQ DB   1.   Check the table meta info in dbisql:     sp_iqobjectinfo [owner],[table_name];   2.   Enable SQL or Query Plan generator – On the load target IQ server – run these commands with DBA: create a temp folder to save trace and logs:     mkdir /tmp/log_iqload   SQL Trace: in dbisql:     call sa_server_option('request_level_logging', 'SQL');     call sa_server_option('request_level_log_file','/tmp/log_iqload/sqltrace.log');   Query plan: in dbisql:     set option query_plan='on';     set option query_detail='on';     set option query_plan_as_html = 'on';     set option dml_options10='on';     set option query_plan_after_run='on';     set option query_name ='load_plan';     set option query_plan_text_caching='on'     set option query_plan_as_html_dire
Read more
SAP SYBASE IQ Index Advisor sp_iqindexadvice Procedure Displays stored index advice messages. Optionally clears advice storage. SQL> select * from sa_conn_options() where OptionName like '%Index_Adv%' and number=connection_property ('Number') Enable Index Advisor in IQ DB   Option1: 1.       SET OPTION index_advisor = 'ON'; 2.       SET OPTION index_advisor_max_rows = 100; 3.       commit; 4.       <query to be analyzed>; 5.       call sp_iqindexadvice ();   Option2: 1.       set temporary option INDEX_ADVISOR = on; 2.       set temporary option INDEX_ADVISOR_MAX_ROWS = 20; 3.       commit; 4.       -- sql statements 5.       call sp_iqindexadvice (); The above code does two things for you- 1. It tells you that your option are set correctly. 2. If table structure is not optimal relative to query, the advisor should give some advice.
Read more