DROPRECOVERY statement

The DROPRECOVERY statement enables Log Master to work with the BMC product BMC AMI Recover (or DSN1COPY) to recover Db2 objects that have been dropped from the Db2 catalog.

The following figure shows the DROPRECOVERY statement syntax.

During a drop recovery job step, Log Master obtains information from the Db2 log and (depending on the type of recovery requested) creates the following forms of output:

Data definition language (DDL) statements
Either:
- BMC AMI Recover SYSIN syntax
- DSN1COPY syntax (and other associated files)
SQL statements
Db2 Command

The Log Master online interface also creates JCL to run the different types of output in the correct order to accomplish the recovery. The generated JCL contains multiple job steps. If necessary, you can edit the JCL to adapt it to your environment.

The DROPRECOVERY statement determines what actions Log Master takes to obtain the log information, what types of output it produces, and what syntax Log Mastergenerates for BMC AMI Recover or DSN1COPY.

Important

You can also obtain log information from input logical log files. For more information, see INPUT statement.

Logical log files include all catalog activity (that is, WHERE DBNAME = DSNDB06) during the time the object was dropped. For a DSN1COPY recovery, the logical log file also includes all transactions from the point of the image copy to the point just before the drop occurred.

Be aware of the following points as you specify an automated drop recovery:

To get meaningful results, you must select a minimum of
- One type of object to recover (using the DATABASE NAME, TABLESPACE NAME, or TABLE NAME keywords)
- One form of output that processes recovery information (using the RECOVER DATASET keyword or the REPORT keyword)
Log Master supports the use of BMC AMI Recover to recover table spaces and tables. Log Master supports the use of DSN1COPY only to recover a single dropped table space.
The Db2 Recover utilities that Log Master works with do not support the recovery of 'versioned' dropped objects. A Db2 object is versioned during the period after its structure is changed by a DDL statement (for example, an ALTER COLUMN SET DATA TYPE statement) and before a Db2 Reorg utility is run to reconcile the multiple versions.
Drop recovery recovers dependent objects based on the URID of their referenced object. For example, a table and its table space are dependent objects. If a table is dropped in one URID and its table space is then dropped in a different URID, you must specify both in drop recovery. At execution, Log Master determines that recovery of the table space is required and recovery of the table is unnecessary.
Example
If a table space, TS1, and its tables, TB1 and TB2, are dropped in separate units of recovery, you should specify the following syntax:
```
DROPRECOVERY
   TABLE NAME TB1
   TABLE NAME TB2
   TABLESPACE NAME TS1
```
Important

When you recover a segmented table space with tables that have been dropped in separate units of recovery, Log Master sets the TOLOGPOINT option to the URID of the first dropped table. Consequently, Log Master automatically recovers objects updated only to the URID of the first dropped table. That is, drop recovery does not recover any update or rows inserted in other tables after the first drop. To recover those, you should perform manual recovery.
To perform a drop recovery, Log Master must perform row completion processing on Db2 catalog log records. Because of the large number of log records related to the catalog, this processing can cause your job to run longer and require more resources than a job that does not read the Db2 catalog. If you frequently operate on objects stored in the catalog, you can improve performance by creating more frequent image copies of the catalog or by defining tables in the Db2 catalog with Data Capture Changes (DCC).
Log Master provides automated drop recovery for LOB and XML table space or table objects. When you specify a LOB or XML base table or table space in the DROPRECOVERY statement, Log Master extends drop recovery discovery to include all of its LOB and XML auxiliary table spaces and tables.
Log Master supports the use of copies registered in BMCXCOPY to recover table spaces, with the exception of cabinet copies.

Option Description

DATABASE NAME databaseName

Directs Log Master to obtain information about and generate output to recover a complete database that has been dropped from the Db2 catalog. The databaseName defines the name of the database as it was known to Db2 before it was dropped.

TABLESPACE NAME DSNDB04 | databaseName.tableSpaceName

Directs Log Master to obtain information about and generate output to recover the dropped table space defined by a given database name and table space name.

Value	Description
DSNDB04	(default) Directs Log Master to create syntax to recover a table space stored in Db2’s default database.
*databaseName*	Directs Log Master to create syntax to recover a table space stored in the database defined by databaseName.
*tablespaceName*	Directs Log Master to create syntax to recover the table space defined by tablespaceName.

Recover parameters

Specifies the values of keywords in BMC AMI Recover syntax. The specific keywords in the Recover parameters relate to the output image copies that BMC AMI Recover can create as it recovers a table space. For more information about these parameters, see Recover parameters.

Depending on the utility programs available in your environment, you might be able to recover a single dropped table space by using DSN1COPY instead of BMC AMI Recover. For more information, see DSN1COPY DATASET Output definition.

TABLE NAME userID.tableName

Directs Log Master to obtain information about and generate output to recover a specific table defined by tableName. Use this keyword when a table has been dropped, but the original database and table space still exist in the Db2 catalog.

Value	Description
*userID*	Specifies an optional user ID that Log Master uses to qualify the tableName in BMC AMI Recover syntax.
*tableName*	Directs Log Master to create BMC AMI Recover syntax to recover the table defined by tableName.

To recover a dropped table, Log Master generates data sets containing DDL, SQL and BMC AMI Recover syntax. If you use the Log Master online interface, Log Master also generates JCL that uses the generated data sets to:

Create a temporary copy of the original table space
Create a temporary copy of the empty table within the temporary table space
Re-create the empty table structures within the original table space
Recover data into the temporary copy of the table
If the original dropped table is a dependent of a referential integrity constraint, you might need to modify the JCL before running it. For more information about troubleshooting automated drop recoveries, see Recovering dropped objects.

Migrate data from the temporary copy of the table into the empty table structure within the original table space
Delete the temporary table and temporary table space

Important

When a table space contains a single table, you can improve drop recovery performance by recovering the dropped table directly into the original table space. For more information, see INDEP YES | NO in the Temporary objects definition topic.

Temporary objects definition

Defines whether Log Master creates temporary copies of the table defined by tableName (and the table space that tableName was originally part of) during drop recovery. These keywords also define the physical and logical location where BMC AMI Recover can store the temporary copies. When defined, Log Master uses this information in the BMC AMI Recover INDEP OUTSPACE syntax. For more information, see Temporary objects definition.

OUTPUT ddName DATASET

Specifies the output data set characteristics that the BMC AMI Recover product uses to build image copies during the recovery. You can repeat this option within the same DROPRECOVERY statement.

You can use the symbolic variables &DSNAME, &TSNAME, &DSNUM, &DATE, &TIME, &JOBNAME, &SSID, &SYSUID and &GATN. To create an output image copy for each table space partition, you must use &DSNUM.

Example

DATABASE NAME dbname OUTCOPY YES

TABLESPACE NAME dbname.tsname OUTCOPY YES         
REGISTER ALL                                          
OUTCOPYDDN(BMCCPY,BMCCPZ) RECOVERYDDN(BMCRCY,BMCRCZ)  
OUTPUT BMCCPY                                         
   DDNAME hlq.OUTCPY.&DBNAME..&TSNAME..P&DSNUM NEW
      CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE        
OUTPUT BMCCPZ                                         
   DDNAME hlq.OUTCPY.&DBNAME..&TSNAME..P&DSNUM NEW
      TRACKS SPACE(20,10) UNIT(SYSDA   ) RELEASE      
OUTPUT BMCRCY                                         
   DDNAME hlq.OUTCPY.&DBNAME..&TSNAME..P&DSNUM NEW
      CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE        
OUTPUT BMCRCZ                                         
   DDNAME hlq.OUTCPY.&DBNAME..&TSNAME..P&DSNUM NEW
      CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE

Important

You cannot use existing data sets for BMC AMI Recover outcopy outputs.

RECREATE DATASET Output definition

Specifies the characteristics of the output data set that contains generated data definition language (DDL) statements. When executed, the DDL statements recreate the structures of the dropped objects (using UNDO DDL) and re-establish their relationships (including granting authorizations, re-creating VIEWs, and so forth). They also create structures for the temporary objects used to recover a dropped table. Output choices for this data set are similar to other data sets. For more information, see LOGSCAN output definition.

Value

Description

EXECUTE

Directs Log Master to execute the generated DDL statements in the data set specified by the RECREATE DATASET keyword.

SQLID authID

Directs Log Master to generate a SET CURRENT SQLID statement and insert it at the beginning of the data set specified by the RECREATE DATASET keyword. When you execute the DDL in the data set, Db2 runs it under the authority granted to the authorization ID represented by authid.

Specify an authorization ID that has the appropriate authority in your environment.

BINDOWNER authID

Specifies an authorization ID that has authority within Db2 security to bind programs and generate plans. Log Master uses this authorization ID to bind an application plan when Log Master executes the SQL or DDL statements that it generates as part of a drop recovery action.

The default value of this keyword depends on the value of the BINDOWN installation option (for more information, see BINDOWN= in the Installation option descriptions ). If your environment does not specify a value for BINDOWN, Log Master uses the user ID of the user who submitted the job.

SCHEMA

( BMC.DB2.SPE2110 )

Directs Log Master to generate a SET SCHEMA statement and insert it at the beginning of the generated DDL statements.

The SET SCHEMA statement sets the default schema for the current environment to the specified schema. The default schema is used as the target schema for all statements issued from the environment that do not explicitly specify a schema name.

Important

SET SCHEMA and TRANSFER OWNERSHIP YES are mutually exclusive.

For more information about SCHEMA, see SCHEMA.

TRANSFER OWNERSHIP

( BMC.DB2.SPE2110 )

Indicates whether to generate TRANSFER OWNERSHIP DDL after CREATE an object DDL when original OWNER value in the log does not match CREATOR or CREATEDBY value. The DDL will transfer ownership of the object to original OWNER value in the log, or to the specified NewOwner.

Important

TRANSFER OWNERSHIP YES and SET SCHEMA are mutually exclusive.

Value

Description

YES

Directs Log Master to generate CREATE TABLE, VIEW, or INDEX statements with unqualified names, then generate TRANSFER OWNERSHIP to original owner values from the logs if OWNER values do not match CREATOR values in the log. For CREATE DATABASE, TABLESPACE, and STOGROUP, generate TRANSFER OWNERSHIP to original CREATOR values in the log if CREATOR and CREATEDBY values do not match in the log.

This option only applies to CREATE Object DDL.

YES USER|OWNER NewOwner

For a user issued TRANSFER OWNERSHIP statement, set the owner to the specified NewOwner.

This option only applies to CREATE objects and TRANSFER OWNERSHIP statements.

NO

Directs Log Master to generate CREATE statements with fully qualified names, using the CREATOR values from the log as the qualifiers. TRANSFER OWNERSHIP statements are not generated after the CREATE object statements.

For more information about TRANSFER OWNERSHIP, see TRANSFER OWNERSHIP.

RECOVER DATASET Output definition

Specifies the characteristics of the output data set that contains generated SYSIN syntax for BMC AMI Recover. When BMC AMI Recover executes the syntax, it recovers the dropped objects. Depending on the type of recovery you request, BMC AMI Recover recovers the objects either to their original database location or to a temporary location. The output choices for this data set are the same as those for other product data sets. For more information, see LOGSCAN output definition.

Log Master supports the automatic drop recovery of a clone table. To accomplish this, Log Master generates BMC AMI Recover syntax in the following files:

RECOVER output has BMC AMI Recover syntax for recovering base tables and table spaces.
RECOVER2 output has BMC AMI Recover syntax for clone tables. If no clone tables are involved in the recovery, RECOVER2 is empty.

Important

Image copies of clone tables are required to recover clone tables.

If dropped objects have clone tables, Log Master generates syntax that instructs BMC AMI Recover to use image copies to recover the clone tables. (You do not have to make any DROPRECOVERY command syntax change for the clone recovery.) Log Master allocates a RECOVER2 output data set by using the same attributes as ones for RECOVER output. RECOVER2 output can be DSN model, PDS, or GDG, as well as OUTCOPYDDN for clone tables. The naming convention of the RECOVER2 output data set is based on types of the RECOVER data set. The "Data set type and description of RECOVER2 data set name" table describes each data set type for RECOVER 2 data sets. The "Examples for RECOVER and RECOVER2 output file names" table provides examples of RECOVER and RECOVER2 output file names.

Data set type and description of RECOVER2 data set name

Data set type

Description

DSN model

The RECOVER2 data set name is RECOVER DSN name model appended with .R2. Log Master limits the RECOVER DSN name model to 41 bytes or less.

Partitioned Data Set (PDS) member names

RECOVER2 uses the same PDS as RECOVER. The RECOVER2 member name is RECOVER member name appended with R if the name is less than 8 bytes; otherwise, the 8th byte is replaced with R.

Generated Data Group

(GDG)

RECOVER2 uses a GDG base similar to the RECOVER data set.

Examples for RECOVER and RECOVER2 output file names

Data set type	RECOVER	RECOVER2
DSN model	MYTS.DROPREC.CNTL	MYTS.DROPREC.CNTL.R2
PDS	MYTS.DROPREC.CNTLPSD(MYCNTL)	MYTS.DROPREC.CNTLPSD(MYCNTLR)
GDG	MYTS.DROPREC.CNTLGDG(+1)	MYTS.DROPREC.CNTLGDG(+1)

Option

Description

LOGONLY

Directs Log Master to generate syntax that instructs BMC AMI Recover to use only data from log records to perform the recovery. By default, Log Master instructs BMC AMI Recover to use image copies during recovery. Use this keyword only if you do not want BMC AMI Recover to use image copies (for example, if your site restores data from backups outside of Db2 during recovery). This keyword applies to all of the Db2 objects in the current drop recovery action.

OUTCOPY

Directs Log Master to generate syntax that determines how BMC AMI Recover creates output image copies for partitioned Db2 objects. Depending on the input image copies (the image copies that Log Master selects as input sources for the drop recovery), BMC AMI Recover can create separate output image copies for each partition or create one combined image copy for the entire object. This keyword applies to the output image copies that BMC AMI Recover creates for all table spaces that you are recovering.

If a table space to recover has a clone table, Log Master directs BMC AMI Recover to create separate output image copies for the clone.

Be sure to define data set names for your output image copies before you run the automated drop recovery. For more information, see Recover parameters.

Value	Description
ASCODED	Creates output image copies in the same format (either DSNUM 0 or partitioned) as the input image copies. If you want to know the format of the input image copies, review the information in the Drop Recovery Report.
BYPART	Creates output image copies in the same format (either DSNUM 0 or partitioned) as the input image copies. If you want to know the format of the input image copies, review the information in the Drop Recovery Report.

You might be able to recover a single dropped table space by using DSN1COPY instead of BMC AMI Recover. For more information, see DSN1COPY DATASET Output definition. You cannot generate BMC AMI Recover syntax and DSN1COPY syntax in the same drop recovery action.

DSN1COPY DATASET Output definition

Specifies the characteristics of a partitioned data set (PDS) that contains generated SYSIN syntax for the IBM utility DSN1COPY and other associated files. Log Master supports the use of DSN1COPY only to recover a single dropped table space.

The JCL that Log Master generates for a drop recovery action with DSN1COPY contains additional job steps to perform tasks that are otherwise handled by BMC AMI Recover. You cannot generate BMC AMI Recover syntax and DSN1COPY syntax in the same drop recovery action.

Log Master generates the following members in this PDS:

SYSIN syntax for the DSN1COPY utility (member name COPY)
Object identifier (OBID) information for DSN1COPY (member name XLAT)
Commands for the DSNUTILB utility to rebuild indexes on the table space (member name REBUILD)
SYSIN syntax for an additional run of Log Master to generate MIGRATE SQL (member name MIGRATE)
Log Master generates MIGRATE SQL to capture changes to the table space from the point when the image copy data set was created to the point when the table space was dropped.

For the output choices available for this PDS, see LOGSCAN output definition.

REPAIR DATASET Output definition

Specifies the characteristics of the output data set that contains generated Db2 commands. When the commands are executed, they repair versions or catalog table spaces that were versioned when the Db2 objects were dropped. The output choices for this data set are the same as those for other product data sets. For more information, see LOGSCAN output definition.

CHECK DATASET Output definition

Db2 might place a table space into check pending status after the recovery job step in drop recovery processing. This option specifies the characteristics of the output data set that contains generated Db2 commands that remove the check pending status. When the commands are executed, they perform

CHECK DATA on table spaces with an RI constraint
CHECK DATA on LOB or XML table spaces
CHECK DATA on auxiliary LOB table spaces
REBUILD INDEX on temporary auxiliary LOB tables

The output choices for this data set are the same as those for other product data sets. For more information, see LOGSCAN output definition.

MIGRATE DATASET Output definition

Specifies the characteristics of the output data set that contains generated SQL statements. Use this keyword only if you have used the TABLE NAME keyword to recover an individual table. When the SQL statements are executed, they migrate data from a temporary table in a temporary table space into a re-created empty version of the table in the original table space. The output choices for this data set are the same as those for other product data sets. For more information, see LOGSCAN output definition.

RUNSTATS DATASET Output definition

Specifies the characteristics of the output data set that contains generated RUNSTATS commands. When the commands are executed, they execute the RUNSTATS utility before the REBIND step is executed. Depending on the dropped object that you are recovering, Log Masterwrites RUNSTATS commands into the RUNSTATS data set as follows:

If you are recovering a dropped database, Log Master generates the RUNSTATS command for all dropped table spaces, (except auxiliary table spaces), for the specified database. (No statistics on the LOB table space affect access path selection.)
Example
```
RUNSTATS TABLESPACE DBNAME.TSNAME_1 TABLE(ALL) INDEX(ALL)
RUNSTATS TABLESPACE DBNAME.TSNAME_2 TABLE(ALL) INDEX(ALL)
```
If you are recovering a dropped table space, Log Master generates the RU NSTATS command for the specified table space.
Example
```
RUNSTATS TABLESPACE DBNAME.TSNAME TABLE(ALL) INDEX(ALL)
```
If you are recovering a dropped table, Log Master generates the RUNSTATS command for the specified table and its indexes.
Example
```
RUNSTATS TABLESPACE DBNAME.TSNAME TABLE(CREATOR.MYTABLE)
RUNSTATS INDEX (CREATOR.MYTABLE_INDEX_1)
RUNSTATS INDEX(CREATOR.MYTABLE_INDEX_2)
```
The output choices for this data set are the same as those for other product data sets. For more information, see LOGSCAN output definition.

REBIND DATASET Output definition

Specifies the characteristics of the output data set that contains generated Db2 commands. When the commands are executed, they rebind any application plans that were invalidated when the Db2 objects were dropped. The output choices for this data set are the same as those for other product data sets. For more information, see LOGSCAN output definition.

FROM Range definition TO Range definition

Specifies a portion of the Db2 log when the Db2 objects were dropped. Log Master scans this portion of the log (also referred to as a time frame) to obtain information needed for the drop recovery action. FROM indicates the start point and TO indicates the end point of the log scan. Each Range definition defines one point in the Db2 log. For more information, see Range definition.

REPORT definition

Directs Log Master to create an output report. The Drop Recovery report contains information about the specified recovery, including information about the dropped Db2 objects, the object ID translation (OBIDXLAT) information, and the application plans that were invalidated when the Db2 objects were dropped. For more information, see Report definition.

More information about SCHEMA and TRANSFER OWNERSHIP YES mutual exclusive

( BMC.DB2.SPE2110 )

When the SCHEMA option is specified in DDL, SQL, or DROPRECOVERY syntax, Log Master generates a SET SCHEMA statement (the default SCHEMA) at the beginning of the output dataset and does not generate other SET SCHEMA statements afterwards. With TRANSFER OWNERSHIP YES specified, Log Master builds CREATE TABLE, INDEX, or VIEW with an unqualified name, the default SCHEMA is the qualifier. However, a fully qualified name is still used in other DDL statements, such as ALTER and GRANT. These DDL statements may get an SQL error if the qualifier in the log does not match the default SCHEMA.

More information about TRANSFER OWNERSHIP

If you run a TRANSFER OWNERSHIP statement without the proper authorization to perform the transfer, you will get an SQL error. The error will occur when the user executing the statement is not the owner of the object or does not have SECADM authority.

To prevent the SQL error, Log Master generates a SET CURRENT SQLID statement with the OWNER value from the log before the TRANSFER OWNERSHIP statement for TABLE, INDEX, and VIEW objects. However, the error can still occur when any one of the following conditions exists:

The old owner is a ROLE or the owner name is longer than 8 bytes in length and cannot be used in a SET CURRENT SQLID statement.
The use ID executing the DDL is not the owner of the DATABASE, TABLESPACE, or STOGROUP.
The CREATOR and OWNER of the TABLE, INDEX, or VIEW are different.

Use one of the following resolutions for the error:

Run the generated DDL using the user ID that initially ran the statements being migrated.
Generate DDL with TRANSFER OWNERSHIP YES if the CREATOR and OWNER of an object are different.
Manually add or modify the SET CURRENT SQLID statement to the object owner before the TRANSFER OWNERSHIP statement.
Run the generated DDL with a user ID that has SECADM authority.

This section contains the following topics:

DROPRECOVERY statement

More information about SCHEMA and TRANSFER OWNERSHIP YES mutual exclusive

More information about TRANSFER OWNERSHIP

Comments