Skip to Content

Dropping an object

Related topics

Dropping and recovering objects

The following procedure describes how to drop a table space.

  1. Take a full-image copy of the table space that you intend to drop.

    Warning

    Important

    Catalog Manager

     does not permit you to perform a drop if a full-image copy of the object is not present in the SYSCOPY catalog table.

  2. Generate a table space list that contains the table space that you want to drop. For information, see  Generating lists in Catalog Manager.
  3. Type DROP in the Cmd column beside the table space to be dropped.DROP is a wait-for-enter command that can be issued for multiple objects on the list. (For information about dropping multiple objects, see  Issuing Wait-for-Enter commands against multiple objects.)

  4. Press Enter.The Confirm DROP panel is displayed in the following figure, providing several actions and options that relate only to the drop and drop recovery functions:

    DEFF-R                         Confirm DROP                         1 to 1 of 1
    Command ===>                                                  Scroll ===> PAGE

    Current SQLID. . . . . . . .  RDACRJ
    Edit Options . . . . . . . .  N       Y/N Modify SQL processing options
    Generate Dependency List . .  N       Y/N Only for a single drop
    Edit SQL . . . . . . . . . .  N       Y/N Edit SQL before executing
    Save in SQL table. . . . . .  N       A/Y/R/N A/Y-Append, R-Replace
     Name of saved SQL . . . . .  20220228_072236
    Save in PDS. . . . . . . . .  N       Y/N Save SQL in PDS
     PDS(member) . . . . . . . .
    Remove DROP RESTRICT . . . .  N       Y/N To remove RESTRICT ON DROP
    Drop Recovery. . . . . . . .  N       Y/N For DDL Only, NOT DATA
    Log Image Copies . . . . . .  N       Y/N Ignored unless Drop Recover is Y

    Execute SQL. . . . . . . . .  N       Y/N Execute the SQL
    Generate worklist. . . . . .  N       Y/N Execute the SQL in Batch
    -------------------------------------  SQL  -----------------------------------
    DROP TABLESPACE QZUDAC.QZUS01AC ;
    ******************************* Bottom of data ********************************
  5. On the Confirm DROP panel, you can edit and save the SQL to drop the object and then execute it.

    1. (Optional) From the Command line, issue the SET sqlid command to change the value of the Current SQLID field.

      Warning

      Important

      The ID shown in the Current SQLID field must have the proper authority to perform the specified SQL DROP statement. If you hold a primary- or secondary-authorization ID that has the proper authority, you can change the Current SQLID to that authorization ID and complete the drop. To change the Current SQLID, use the SET command.

    2. (Optional) In the Edit options field, type Y to modify the default values for the options on the Confirm DROP panel. Then, press Enter.The SQL and Confirm Options panel is displayed. Press END to return to the Confirm DROP panel.

    3. (Optional) In the Generate Dependency List field, type Y or N to view the list of dependent objects that will be dropped with the table space.

      Warning

      Important

      The Generate Dependency List option is not available when you drop multiple objects.

      Catalog Manager displays the Drop Dependency List and includes a list of the dependent objects that will be dropped (in the following figure). Review the list to verify that you want to drop all of the dependent objects.

      DEFF-R                        Drop Dependency List              Row 1 to 8 of 8
      Command ===>                                                  Scroll ===> PAGE

      Dependent Objects for TABLESPACE:
      QZUDAC.QZUS01AC

      Type       Object Name
      -------------------------------------------------------------------------------
      .TB      - QZU.QZUT01_DACS01
      ..IX     - QZU.QZUX01_DACS01T01
      ..IX     - QZU.QZUX02_DACS01T01
      ..IX     - QZU.QZUX03_DACS01T01
      ..IX     - QZU.QZUX04_DACS01T01
      ..IX     - QZU.QZUX05_DACS01T01
      ..IX     - QZU.QZUX06_DACS01T01
      ..IX     - QZU.QZUX07_DACS01T01
      ******************************* Bottom of data ********************************

    4. (Optional) In the Edit SQL field, type Y or N to invoke an ISPF edit session to edit the SQL statement. Then, press Enter.

      Warning

      Important

      For native SQL procedures, the following statement is generated:

      ALTER PROCEDURE procedureName
                     DROP VERSION versionName;

      This statement enables you to drop a single version of a procedure. To drop all versions of the procedure, edit the statement as follows:

       DROP PROCEDURE procedureName RESTRICT;
    5. Press END to save the SQL and return to the Confirm DROP panel.

    6. (optional) In the Save in SQL table field, enter A, Y, R, or N to specify whether to save the SQL in the Catalog Manager SQL_Table.Press END to save the SQL and return to the Confirm DROP panel.

      To perform this action

      Type

      Append the SQL to the SQL in the SQL_Table

      A

      Save the SQL in the SQL_Table

      Y

      Replace the SQL in the SQL_Table

      R

      Discard the SQL

      N

    7. (Optional) In the Name of saved SQL field, type a name for the SQL.
    8. (Optional) In the Save in PDS field, enter Y to save the SQL in a member of a partitioned data set (PDS).The saved SQL uses the ID displayed in the Current SQLID field as the object qualifier. If the SQL is not saved, the ID in the Current SQLID is used only to identify Db2 authority.
    9. (Optional) In the PDS(member) field, type the name of the PDS and member.
    10. (Optional) In the Remove DROP RESTRICT field, type Y to have Catalog Manager analyze each DROP command of a database, table space, or table.If a table includes the DROP RESTRICT attribute, the product generates an ALTER TABLE DROP RESTRICT ON DROP command before the DROP command. However, the Drop Recovery Log will not include the DROP RESTRICT attribute in the CREATE TABLE statement.
    11. (Optional) In the Drop Recovery field, type Y to log the dropped objects to the Drop Recovery Log. This option generates the DDL to re-create the object structures and Db2 authorizations.

    12. (Optional) In the Log Image Copies field, type Y to save image copy information so that you can recover a copy of the data for the table space and its dependent objects.

      Error
      Warning

      You must enable both the Drop Recovery and Log Image Copies options to perform the steps to recover data. If you set the Drop Recovery value to Y and the Log Image Copies value to N, you can recover the object structures but not the data.

    13. (optional) In the Generate worklist field, type Y to generate a batch job to execute the SQL in a worklist.The Generate Catalog Manager Worklist Job panel, from which you can build and submit the batch job, is displayed.

      DB2 allows some objects to be dropped in batch mode. To prevent possible problems with catalog contention while the worklist is executed, Catalog Manager generates COMMIT statements between DROP statements.

    14. (Optional) In the Execute SQL field, type Y to execute the SQL displayed on the Confirm DROP panel. Then, press Enter.The SQL Progress Indicator panel is displayed. After building the SQL statements that are required to recover the table space, Catalog Manager displays the SQL statements in this scrolling panel.

      After executing the drop, Catalog Manager displays the Table Space List panel with the marker *DROP beside the dropped table space name (shown in the following figure). The marker is removed when the list is refreshed.

      • If you have set the value of the Drop switch to N (see  Simulating a drop), Catalog Manager displays DROP NOT DONE in the short message field to alert you that the drop was not actually executed.
      • If the drop was executed, Catalog Manager displays the return code from the DROP command in the short message field.
      DEFF-R   ----------------------  TABLESPACE LIST  --------------- DROP RC= 000
      Command ===>                                                  Scroll ===> CSR
                                                                                   01
       CMD will show commands for this list.  Type command and press ENTER  
       Lists: ACCTB AL BMCHOBJ BMCHSTEP BMCUHIST CA CL CO DB DP DS FK IC IM IS IX LK
       LIKE QZU%.%
       Cmd Tablespace      Owner  Segsz Bpool Prts  Tbls     ActivPg    Status Enc Ty
       ----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v----
       *drop AC.QZUS01AC   ASUQA      0 BP0       4    1       23K        A    E
         QZUDAC.QZUS02AC   ASUQA      0 BP0       4    1       23K        A    E
         QZUDAC.QZUS03AC   ASUQA      4 BP0       1    1      5040        A    E     G
         QZUDAC.QZUS04AC   ASUQA      4 BP0       0    1       24K        A    E
         QZUDAC.QZUS05AC   ASUQA      8 BP0       0    1       24K        A    E
         QZUDAC.QZUS06AC   ASUQA     16 BP0       0    1       24K        A    E
         QZUDAC.QZUS07AC   ASUQA     32 BP0       0    1       24K        A    E
         QZUDAC.QZUS08AC   ASUQA     64 BP0       0    1       24K        A    E
         QZUDAC.QZUS09AC   ASUQA     16 BP0       2    1      307K        A    E     G
         QZUDA1.QZUS01A1   ASUQA      4 BP0       0    1       130        A    A
         QZUDA1.QZUS02A1   ASUQA     16 BP0       0    2       146        A    A
         QZUDA1.QZUS03A1   ASUQA      0 BP0       4    1      1440        A    A
         QZUDA1.QZUS04A1   ASUQA     64 BP0       0    2       540        A    A
         QZUDA1.QZUS05A1   ASUQA      0 BP0       4    1       720        A    A
         QZUDB1.QZUS01B1   ASUQA      4 BP0       0    1      8221        A    E
         QZUDB1.QZUS02B1   ASUQA      4 BP32K     0    1      1005        A    E

 

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

BMC AMI Catalog Manager for Db2 13.1