DB2 Reorg-Pending Maintenance Guard

id: db2-reorg-pending-maintenance-guard
namespace: company.team

description: |
  Find DB2 tables stuck in reorg-pending state and clear them. A table enters reorg-pending after
  schema changes or heavy churn (three reorg-recommended ALTER TABLE operations). From that point
  reads and further changes against it fail until it is reorganized. This blueprint reads
  reorg-pending state from SYSPROC.ADMIN_GET_TAB_INFO and alerts Slack. When allowed it runs
  a classic REORG then RUNSTATS through SYSPROC.ADMIN_CMD, one table at a time, then reads the state
  again and fails the run if any table is still pending. Read-only by default.

inputs:
  - id: jdbc_url
    type: STRING
    displayName: JDBC URL
    defaults: "jdbc:db2://localhost:50000/testdb"

  - id: jdbc_username
    type: STRING
    displayName: Database user
    defaults: "db2inst1"

  - id: jdbc_password
    type: STRING
    displayName: Database password
    description: Defaults to the DB2_PASSWORD secret. Override per run only for
      local testing.
    defaults: "{{ secret('DB2_PASSWORD') }}"

  - id: table_schema
    type: STRING
    displayName: Table schema
    description: Schema to scan, passed to ADMIN_GET_TAB_INFO. Case sensitive
      (usually upper case). One schema per run.
    defaults: "DB2INST1"

  - id: runstats_options
    type: STRING
    displayName: RUNSTATS options
    defaults: "WITH DISTRIBUTION AND DETAILED INDEXES ALL"

  - id: auto_remediate
    type: BOOL
    displayName: Auto remediate
    defaults: false

  - id: dry_run
    type: BOOL
    displayName: Dry run
    description: Must be false, together with auto_remediate, for REORG and RUNSTATS to run.
    defaults: true

variables:
  detect_sql: |
    SELECT
      RTRIM(TABSCHEMA) || '.' || RTRIM(TABNAME) AS NAME,
      '"' || RTRIM(TABSCHEMA) || '"."' || RTRIM(TABNAME) || '"' AS QUALIFIED_NAME
    FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO(:table_schema, NULL))
    WHERE REORG_PENDING = 'Y'
    ORDER BY TABSCHEMA, TABNAME

tasks:
  - id: detect
    type: io.kestra.plugin.jdbc.db2.Query
    description: Read-only. Lists reorg-pending tables in the schema from
      SYSPROC.ADMIN_GET_TAB_INFO.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.jdbc_username }}"
    password: "{{ inputs.jdbc_password }}"
    fetchType: FETCH
    sql: "{{ render(vars.detect_sql) }}"
    parameters:
      table_schema: "{{ inputs.table_schema }}"

  - id: plan
    type: io.kestra.plugin.core.output.OutputValues
    description: The quoted schema and table names to reorganize, one per
      reorg-pending table.
    values:
      names: "{{ outputs.detect.rows | jq('map(.QUALIFIED_NAME)') | first | toJson }}"

  - id: summary
    type: io.kestra.plugin.core.log.Log
    message: >-
      DB2 reorg-pending scan on {{ inputs.jdbc_url }}: {{ outputs.detect.size }}
      table(s) in reorg-pending state{{ outputs.detect.size > 0 ? ' (' ~
      (outputs.detect.rows | jq('map(.NAME) | join(", ")') | first) ~ ')' : ''
      }}.

  - id: route
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.detect.size > 0 }}"
    then:
      - id: alert
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {{ {'text': 'DB2 ' ~ inputs.jdbc_url ~ ': ' ~ outputs.detect.size ~ ' table(s) in reorg-pending state, which blocks access until reorganized (' ~ (outputs.detect.rows | jq('map(.NAME) | join(", ")') | first) ~ ').' ~ (inputs.auto_remediate and not inputs.dry_run ? ' Reorganizing now.' : ' No change applied (auto_remediate=' ~ inputs.auto_remediate ~ ', dry_run=' ~ inputs.dry_run ~ ').')} | toJson }}

      - id: remediate_gate
        type: io.kestra.plugin.core.flow.If
        condition: "{{ inputs.auto_remediate and not inputs.dry_run }}"
        then:
          - id: remediate
            type: io.kestra.plugin.core.flow.Loop
            description: Sequential remediation (concurrencyLimit 1). One table at a time,
              REORG then RUNSTATS.
            concurrencyLimit: 1
            values: "{{ outputs.plan.values.names }}"
            tasks:
              - id: reorg
                type: io.kestra.plugin.jdbc.db2.Query
                description: Classic REORG through ADMIN_CMD. Reorg-pending from schema changes
                  needs a classic reorg, not INPLACE.
                url: "{{ inputs.jdbc_url }}"
                username: "{{ inputs.jdbc_username }}"
                password: "{{ inputs.jdbc_password }}"
                fetchType: NONE
                sql: "CALL SYSPROC.ADMIN_CMD(:cmd)"
                parameters:
                  cmd: "REORG TABLE {{ item.value }}"

              - id: runstats
                type: io.kestra.plugin.jdbc.db2.Query
                description: Refresh statistics so the optimizer sees the reorganized table.
                url: "{{ inputs.jdbc_url }}"
                username: "{{ inputs.jdbc_username }}"
                password: "{{ inputs.jdbc_password }}"
                fetchType: NONE
                sql: "CALL SYSPROC.ADMIN_CMD(:cmd)"
                parameters:
                  cmd: "RUNSTATS ON TABLE {{ item.value }} {{ inputs.runstats_options }}"

          - id: reverify
            type: io.kestra.plugin.jdbc.db2.Query
            description: Read the flag again. The run fails if any table is still
              reorg-pending.
            url: "{{ inputs.jdbc_url }}"
            username: "{{ inputs.jdbc_username }}"
            password: "{{ inputs.jdbc_password }}"
            fetchType: FETCH
            sql: "{{ render(vars.detect_sql) }}"
            parameters:
              table_schema: "{{ inputs.table_schema }}"

          - id: verify_result
            type: io.kestra.plugin.core.flow.If
            condition: "{{ outputs.reverify.size > 0 }}"
            then:
              - id: remediation_failed
                type: io.kestra.plugin.core.execution.Fail
                errorMessage: "Still reorg-pending after REORG: {{ outputs.reverify.rows |
                  jq('map(.NAME) | join(\", \")') | first }}. Check the
                  ADMIN_CMD output and that the user can reorganize these
                  tables."
            else:
              - id: remediated
                type: io.kestra.plugin.core.log.Log
                message: "Reorganized and verified: {{ fromJson(outputs.plan.values.names) |
                  join(', ') }}. Every listed table is back to REORG_PENDING N."
    else:
      - id: clean
        type: io.kestra.plugin.core.log.Log
        message: "No table is in reorg-pending state in schema {{ inputs.table_schema
          }}."

errors:
  - id: alert_flow_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {{ {'text': 'DB2 reorg-pending guard failed on ' ~ inputs.jdbc_url ~ ' (execution ' ~ execution.id ~ '). Check the logs.'} | toJson }}

triggers:
  - id: daily
    type: io.kestra.plugin.core.trigger.Schedule
    description: Shipped disabled. Always runs in safe mode.
    cron: "15 4 * * *"
    disabled: true
    inputs:
      auto_remediate: false
      dry_run: true