Skip to Content

 

  

Information
Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see ALTER and BMC AMI Change Manager for Db2 13.1.

Creating a system-period temporal table

Use the following procedure to create a system-period temporal table:

  1. On the BMC AMI Change Manager for Db2 Main Menu, select WORKID, and press Enter.
  2. On the WORKID Action Menu, type the name of the WORKID or type a wildcard pattern to display a list of work IDs. Then, select Specify Db2 definitions and press Enter.
  3. On the Object Specification panel, specify the name of a database and table space (or use wildcard characters) and press Enter.
  4. On the Mixed List panel, type CTB in the Act column adjacent to the table space (TS) for which you want to create a system-period temporal table and press Enter.
  5. On the Create Table panel, specify the information about the system-period temporal table:
    1. Type the owner of the table.
    2. Type the name of the table.
    3. Specify the attributes for the table.
    4. In the Link History TB field, type one of the following values:
      • Y to associate a history table to record audit information.
      • (PTF BQU1029 applied) E to associate a history table that includes rows of audit information that are deleted from the system-period temporal table.
    5. Type S to select Table Column List.
    6. Press Enter.
  6. On the Table Columns List panel, specify the columns for the system-period temporal table:

    1. Specify the begin column with the following attributes, and press Enter:

      Attribute

      Value

      Column Name

      userDefinedSystemBeginName

      Column Type

      TIMESTAMP

      Length

      12

      Nl

      N

      Df

      A

      Pd

      S

      The product automatically sets the default value to AS ROW BEGIN.

      Warning

      Note

      Until you specify the end column and the transaction ID column for the table, the product displays the following message:

      BMC396684E - Too many SYSTEM TIME start, end or transaction id columns or mismatch

      You can disregard the message and specify the next column.

    2. Type L in the Act column adjacent to the begin column name and press Enter.

    3. Specify the end column with the following attributes and press Enter:

      Attribute

      Value

      Column Name

      userDefinedSystemEndName

      Column Type

      TIMESTAMP

      Length

      12

      Nl

      N

      Df

      A

      Pd

      T

      The product automatically sets the default value to AS ROW END.

      Warning

      Note

      Until you specify the transaction ID column for the table, the product displays the following message:

      BMC396684E - Too many SYSTEM TIME start, end or transaction id columns or mismatch

      You can disregard the message and specify the next column.

    4. Type L in the Act column adjacent to the begin column name and press Enter.

    5. Specify the transaction ID column with the following attributes, in the order specified in the table:

      Attribute

      Value

      Column Name

      userDefinedSystemTransactionIDName

      Column Type

      TIMESTAMP

      Length

      12

      Pd

      blank

      Nl

      Y or N

      Df

      X

      The product automatically sets the default value to AS TRANSACTION START ID.

    6. Press Enter.
  7. (PTF BQU1029 applied) (optional) Specify a non-deterministic generated expression column.
    1. Type E in the Act column adjacent to a column name and press Enter.
    2. On the Table Column Detail panel, type G in the With Default field and press Enter.
    3. On the Table Column Non-deterministic Generated Expression panel, select a value and press Enter.
    4. On the pop-up panel, type Y to allow the product to make the required changes to the column and press Enter.
    5. Press END.
  8. Press END.
  9. On the Create Table panel, type S to select History Table Information.
  10. On the History Table Information panel, specify the table owner and name and press END.Now that the table has been created you can analyze a work ID and execute a worklist.


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

ALTER and BMC AMI Change Manager for Db2 12.1