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

Join The Discussion

Your email address will not be published. Required fields are marked *