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_directory = '/tmp/log_iqload';

    set option query_plan_text_access='on';

    set option query_timing='on';

    set option revert_to_v15_optimizer='OFF';

 

3.  Run that load test, in the meantime – monitor the IO, CPU and thread status with NMON

nmon -f -s1 -c<seconds>

 

4.  After load test completed, switch off the SQL trace or Query Plan generator with DBA:

SQL Trace:

in dbisql:

    call sa_server_option('request_level_logging', 'OFF');

 

Query plan:

in dbisql:

    set option query_plan=’off’;

    set option query_detail='off';

    set option query_plan_as_html ='off';

 

5.  Collecting all output and logs above:

Output:

    sp_iqobjectinfo

 

Trace files

    all files in folder /tmp/log_iqload

 

call sa_get_request_profile('/sys1/users/jones/iqreqs1_zr.log');

select * from satmp_request_profile;

select * from satmp_request_time;

 

Note: IQ has the option QUERY_PLAN_MIN_TIME to print query plan only when query execution time exceeds the threshold. We can use this option for now to get query plans for high execution time queries.