ALT (Alter) function

When you use the ALT line command to change an object, you invoke the ALT function of Db2 Admin Tool. The ALT function first analyzes the change to determine what actions need to be taken to implement the change. You can choose whether you want this analysis to be done in batch or online.

The general steps for making changes with the ALT line command are as follows:

  1. Issue the ALT line command against the object.
  2. On the change panel for the object type, specify the change that you want to make, and issue the NEXT command.
  3. On the Alter Objects (ADB27CA) panel, specify any other changes. If you want to change the method for analysis processing (batch or online), use the ALTOPT command.
  4. Issue NEXT.
    If you specified that analysis is to be done online, and the analysis process determines that SQL ALTER statements can accomplish the task, the ADB27CTC panel is displayed. On this panel, specify whether you want to run the SQL statements in the foreground (online) or generate a batch job:
    • If you select ALTER statements, the SQL is run in the foreground. When the SQL completes successfully, the change is made, and you can skip the remaining steps.
    • If you specify batch jobs, the ALTER - Build Analyze and Apply Job (ADBPALT) panel is displayed.
    Restriction: Start of changeFor accelerated tables where the change requires accelerator commands, running the SQL statements online is not allowed. In this situation, the ADB27CTC panel is not displayed and a batch job is always generated.End of change
  5. On the ALTER - Build Analyze and Apply Job (ADBPALT) panel, specify the options for building the work statement list (WSL) or batch job to implement the change. You can also use the BP command to update the unit type and space parameters that are used for allocating the work and unload data sets.
  6. Press Enter.

    An EDIT session is displayed with the generated job to analyze the change and build the apply jobs or WSL.

    Db2 Admin Tool generates the following jobs:

    Job name Description
    ST1RE Reverse engineering
    ST1REALL Reverse engineering when restore is enabled (optional)
    ST2ULnnn Unload data for table nnn
    ST3AC Convert
    ST4AR Alter related merge DDL (optional)
    ST5DC Drop and re-create
    ST6RLnnn Reload data
    ST7CD CHECK DATA (optional)
    ST8ICnnn Image copy
    ST9RS RUNSTATS (optional)
    ST10RB Rebind (optional)
    ST11DL Delete work data sets, except those data sets for restore and unload (optional)

    Db2 Admin Tool does not generate the conversion job step if it determines that data conversion is not needed.

    The numeric values in these job names are adjusted to occur sequentially if you omit optional steps or Db2 Admin Tool determines that the conversion step is not needed. For example, if related objects are not included, ST5DC becomes ST4DC.

  7. Review the jobs and submit them in the sequence that they are listed to perform the changes.

    If one of the SQL statements in the input stream fails, you can use the Batch Restart program, ADBTEP2, to restart or resume the execution of an ALTER job at an intermediate point. In addition, you can combine the generated ALTER batch jobs into a single job.

    If you choose to have the statements that make the changes put in a WSL and specify that the WSL is to be generated online (instead of with a batch job), JCL to create the WSL is generated and run online. Messages are displayed to indicate the status as each step is run. When the online processing is complete, a work data set is displayed. This work data set contains the messages that would be seen in the job output if the WSL had been generated with a batch job.