CHANGE MANAGER for DB2 - does BMC provide CM/PILOT tasks for generating DML?

Version 2
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    CHANGE MANAGER for DB2


    COMPONENT:

    CHANGE MANAGER for DB2



    QUESTION:

    CHANGE MANAGER – does BMC provide CM/PILOT tasks for generating DML?
     


    ANSWER:

    With CHANGE MANAGER, you can import CDL, DDL, and CM/PILOT DML (data manipulation language) files.
    DML is a SQL-like data manipulation language that updates, deletes, and migrates data structures, or estimates space.
    You can create DML statements by using the CM/PILOT component of CHANGE MANAGER, or by manually creating the statements in a file.
    The DML file should meet the following criteria:

       
    • The DML files can be either a sequential, 80-column data set or a member of a PDS.
    •  
    • The DML file can consist of DML statements that are extracted from a CM/PILOT worklist, or the file can be one of the members listed in the ACMDMLIX member of the HLQ.BMCCNTL data set.  These members are provided by BMC to support many common tasks.  Here is the list of the members:
      
    ACMDMLD1
    This task describes how you can import DML to delete all aliases that refer to tables and views that are no longer in the DB2 catalog.

    ACMDMLD2
    This task describes how you can use the CM/PILOT DELETE_WORKID script to delete WORKIDs.

    ACMDMLD3
    This task describes how you can use the CM/PILOT DELETE_WORKID script to delete the sync table entries for WORKIDs.

    ACMDMLL1
    This task describes how you can import DML to move all tables in a multi-table table space to single-table table spaces. This task is especially useful to DBAs who implement and manage an ERP application.

    ACMDMLL2
    This task describes how you can import DML to move all of the empty tables in a database to one table space. This task is especially useful to DBAs who implement and manage an ERP application and who want to reduce the creation of empty image copy data sets.

    ACMDMLM1
    This task describes how you can import DML to migrate one or more tablespaces and the dependent indexes and to change them to DEFINE NO. This task is especially useful to DBAs who implement and manage an ERP application and who want to reduce the creation of empty data sets.

    ACMDMLM2
    In this file, the example DML sets the length for inline LOB columns in tables that the product migrates. The DML also migrates all dependent objects of an inline LOB column.

    ACMDMLP1
    In this example, we show how to migrate stored procedures.

    ACMDMLP2
    In this file, we update the DETERMINISTIC and IMMEDWRITE fields of a native stored procedure. Note: While DETERMINISTIC is in SYSIBM.SYSROUTINES, IMMEDWRITE is actually in SYSIBM.SYSPACKAGES.

    ACMDMLP3
    In this file we update fields in SYSIBM.SYSPARMS related to a stored procedure.

    ACMDMLU1
    The example DML in this file updates one or more tablespaces and their dependent indexes to change them to DEFINE NO. This task is especially useful to DBAs who implement and manage an ERP application and who want to reduce the creation of empty data sets.After you edit the DML statement, you can use CHANGE MANAGER to import it.

    ACMDMLU2
    This task describes how you can import DML to create a new database and table space for tables. The example provided allows users to determine the row threshold for reassigning certain tables of a multi-table table space to new single-table table spaces. This task is especially useful to DBAs who implement and manage an ERP application.

    ACMDMLU3
    This task describes how you can import DML to create synonyms for selected tables and derives synonym names from the literal SYN_, which is then suffixed by the table name.

    ACMDMLU4
    This task describes how you can import DML to update one or more tablespaces and their dependent indexes to DEFINE NO based upon the cardinality of 0 of the underlying tables. This feature is only available in DB2 V6 and above. This task is especially useful to DBAs who implement and manage an ERP application and who want to reduce the creation of empty data sets.

    ACMDMLU5
    This task describes how you can import DML to create a Task ID that will perform space estimation for all table space and index within a database.  The estimated space value is used for the primary quantity.  The default value for the secondary quantity is one-tenth of the space value.

    ACMDMLU6
    The example DML in this file describes how you can import DML to upgrade DB2 PLAN_TABLE tables with columns that were added by a later DB2 version.

    ACMDMLU7
    The example DML in this file changes VCAT defined tableparts and indexparts to stogroup defined.

    ACMDMLU8
    Columns that are defined as ROWID GENERATED ALWAYS might not have a unique index defined on the column. For example, if you add a LOB column to a table, but do not add a ROWID column, DB2 creates the ROWID column on the table as GENERATED ALWAYS. DB2 does not create an index. If you want to use the BMC LOADPLUS utility to load data from the table, you must create a unique index for the column. This file provides DML statements that you can import to create these unique indexes.

    ACMDMLU9
    The example DML in this file converts index-controlled partitioned table spaces to table-controlled partitioned table spaces.

    ACMDMLUA
    The example DML in this file converts index-controlled partitioned table spaces to range-partitioned table spaces. The first DML statement modifies the SEGSIZE of the table space. The second DML statement converts all of the tables from index-controlled partitioned to table-controlled partitioned. The scope of the WHERE clause is the same for both statements.

    ACMDMLUB
    The example DML in this file converts table-controlled partitioned table spaces to range-partitioned table spaces.  

    ACMDMLUC
    The example DML in this file converts table-controlled partitioned table spaces, index-controlled partitioned table spaces, and nonpartitioned simple or segmented table spaces to partition-by-growth table spaces. CM/PILOT automatically deletes all but the first partition of a partitioned table space and any dependent indexes.

    ACMDMLUD
    The example DML in this file converts explicit databases and implicit table spaces to implicit databases and table spaces.

    ACMDMLUE
    The example DML in this file updates the length of inline LOB columns that exist in tables in partition-by-growth or range-partitioned table spaces. The file contains the following examples:  
       
    • DML to set the length of an inline CLOB to the system default.
    •  
    • DML to change an inline CLOB column to a non-inline CLOB column.
    •  
    • DML to change all non-inline LOB columns to inline LOB columns.
    •  
    • DML to change the length of all LOB columns in universal table spaces.
    •  
    • DML to set the length of inline CLOB columns based on the length of the columns and to set the maximum length.
      
    ACMDMLUF
    The example DML in this file changes the encoding scheme for table spaces to UNICODE.

    Available starting with SPE1904 (BQU1880)

    ACMDMLUG
    The example DML in this file converts SYNONYMS to ALIASES

    ACMDMLUH
    The example DML in this file updates the value of INSERT ALGORITH for table spaces.

    ACMDMLUI
    The example DML in this file turns on table access control for all tables in a database.

    ACMDMLUJ
    The example DML in this file enables all row permissions for a table.

    ACMDMLUK
    The example DML in this file creates a new partition by growth table space for each table in a mult-table table space.
      

     


    Article Number:

    000188747


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles