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>')

Comments

  1. RayApril 13, 2022 at 11:40 AM

    how to obtain storage sizes at each partition here for that partitioned table ?

    ReplyDelete

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
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 depen
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