SYBASE - SAP IQ Performance Tuning

SAP Sybase IQ Sysmon - System Performance Analysis IQ Utility Sybase IQ's sysmon procedure produces a log file   declare local temporary table dummy_monitor_debug (dummy_column integer); declare local temporary table dummy_monitor1_debug (dummy_column integer); set temporary option Monitor_Output_Directory = "/gpfs/jio/ingest/LOAD_STATISTICS_14092014"; iq utilities main into dummy_monitor_debug start monitor '-debug -interval 10 -file_suffix main'; iq utilities private into dummy_monitor1_debug start monitor '-debug -interval 10 -file_suffix temp';   OR set option Monitor_Output_Directory = " /gpfs/jio/ingest/LOAD_STATISTICS_14092014 "   go   sp_iqsysmon start_monitor, filemode,'-interval 120 -file_suffix sysmon.2017_May6'   go   commit   go   waitfor delay '00:59:00'   commit   go   sp_iqsysmon stop_monitor   go     Below is a

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.

SAP SYBASE IQ Diagnostics and Hang Analysis   IQ Diagnostics and Analysis for Hung Server   1)       Symptoms Some of the symptoms, that could lead you (or users) to think that IQ server is hung are : - No new connections can be made to the server either from the same host machine or different host machine over TCP/IP network. - No tasks seems to be working and there is no new messages seen in the IQ server log (.iqmsg file), if no existing connections doing work. - The messages appear to be sparse in .iqmsg file, no task how-ever simple, does appear to get completed in a timely manner. - Catalog .db and .log time not getting updated on any IQ nodes. - Hardware bottleneck – IO stuck   2)       IQ Diagnostics checklist Collect and analyzethe following diagnostic information for all IQ problems and/or symptoms - Know clear problem description and/or exact error message. - Has anything changed recently on IQ or the OS? - It is useful to k

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

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 tr

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