IBM Support

Big SQL Auto-Analyze Enable & Disable - Hadoop Dev

Technical Blog Post


Abstract

Big SQL Auto-Analyze Enable & Disable - Hadoop Dev

Body

This is part 4 of a series on Auto-Analyze.

BigInsights Big SQL Auto-Analyze assures that statistics are available for tables regardless of how the data was added to the table. Running your own Analyze statement instead of the Auto-Analyze defaults will give you more control over how statistics are calculated. If you prefer to always run your own Analyze statement as part of a customized workflow you will want to disable Auto-Analyze.

Steps to Disable Auto-Analyze

These steps need to be run as the Big SQL administrator id (bigsql).

  1. Determine the NAME of the task(s) that are running procedure SYSHADOOP.BIGSQL_CHECK_ANALYZE.
  2.   SELECT NAME, PROCEDURE_SCHEMA, PROCEDURE_NAME FROM SYSTOOLS.ADMIN_TASK_LIST WHERE PROCEDURE_SCHEMA='SYSHADOOP' AND PROCEDURE_NAME='BIGSQL_CHECK_ANALYZE';  +----------------------+------------------+----------------------+  | NAME                 | PROCEDURE_SCHEMA | PROCEDURE_NAME       |  +----------------------+------------------+----------------------+  | BIGSQL_CHECK_ANALYZE | SYSHADOOP        | BIGSQL_CHECK_ANALYZE |  +----------------------+------------------+----------------------+  
  3. Stop running all tasks that call SYSHADOOP.BIGSQL_CHECK_ANALYZE by updating the task with a SCHEDULE of NULL. This will stop it from running. If you have added custom schedules the NAME of the task may not be BIGSQL_CHECK_ANALYZE.
  4.   CALL SYSPROC.ADMIN_TASK_UPDATE(    'BIGSQL_CHECK_ANALYZE',          -- TASK NAME     NULL, NULL,     0, NULL, NULL, NULL);  

    (See SYSTOOLS.ADMIN_TASK_UPDATE)

  5. Disable Analyze after Load by editing the bigsql-conf.xml file and setting properties biginsights.stats.auto.analyze.post.load and biginsights.stats.auto.analyze.post.syncobj to NEVER.
  6. Restart Big SQL service.

Steps to Enable Auto-Analyze with default settings

These steps need to be run as the Big SQL administrator id (bigsql).

  1. Determine the NAME of the task(s) that are running procedure SYSHADOOP.BIGSQL_CHECK_ANALYZE. (Same as step 1 in disable Auto-Analyze)
  2. Start periodic execution of all task(s) that call SYSHADOOP.BIGSQL_CHECK_ANALYZE by updating each task’s SCHEDULE with a valid cron schedule. This will start periodic execution of the task according to the schedule.
  3.   CALL SYSPROC.ADMIN_TASK_UPDATE(    'BIGSQL_CHECK_ANALYZE',      -- TASK NAME    NULL, NULL, NULL,     '0,10,20,30,40,50 * * * *',  -- SCHEDULE    NULL, NULL);  

    (See SYSTOOLS.ADMIN_TASK_UPDATE)

  4. Enable Analyze after Load by editing the bigsql-conf.xml file and setting property biginsights.stats.auto.analyze.post.load to ONCE.
  5. Enable Analyze after HCAT_SYNC_OBJECTS by editing the bigsql-conf.xml file and setting property biginsights.stats.auto.analyze.post.syncobj to DEFERRED.
  6. Restart Big SQL service.

Previous Big SQL Auto-Analyze blogs:

Faster BigInsights Big SQL queries with Auto-Analyze
Big SQL Auto-Analyze Customization
Big SQL Auto-Analyze Scheduling

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSCRJT","label":"IBM Db2 Big SQL"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

UID

ibm16259887