Specifying a SELECT statement for the source or target definition
You can specify a SELECT statement against the Db2® catalog to identify the source or target
for a comparison. Db2 Object Comparison Tool uses all objects
that are returned by the query for the source or target definition. The advantage
of using a SELECT statement to identify the source or target is that you can use clauses to
filter the object list.
Before you begin
This procedure assumes that you have completed the steps in 1. Specifying source objects or 2. Specifying target objects and specified option 2 on the Specify Compare Source (GOC1) panel or the Specify Compare Target (GOC1) panel. The Specify DB2 Source Catalog Extract (GOC12) panel or the Specify DB2 Target Catalog Extract (GOC12) panel should be displayed.
Procedure
To specify a SELECT statement for the source or target definition:
On the Specify DB2 Source Catalog Extract (GOC12) panel or the Specify DB2 Target Catalog Extract (GOC12) panel, specify where the version file is to
be written:
Compare -------------- Specify DB2 Catalog Extract --------------------- 11:08
Option ===>
1 - Source is databases from the DB2 catalog
2 - Source is table spaces from the DB2 catalog
3 - Source is tables from the DB2 catalog
4 - Add schema objects to the DB2 Source catalog extract
5 - Source is the result of an SQL SELECT statement
Specify compare version file output:
Version table entry:
Owner . . . (? to look up)
Name . . . (? to look up)
Data set:
Data set name . .
Enter a description (optional):
Description . .
The version file contains the object definitions that are extracted by the
Object Comparison Tool DDL extract program and
become input to the compare process. You can specify one of the following locations for
the version file:
Output data set
To specify that the version file be written to an output data set, enter a value
in the Data set name field. The specified data set must be
one of the following types:
A variable-block sequential data set
A member of a partitioned data set with a record length of 16 KB
(RECFM=Vx,LRECL=16384)
If the specified data set does not exist, it is created. If an existing data
set is specified, it is overwritten.
CM database
To specify that the version file be written to the CM database, specify values in
the version table Owner and Name
fields. To select a version file from the CM database, specify a question mark (?)
in the field. You can choose a value on the subsequent CM Versions (ADB2C41) panel.
If the
Owner and Name fields are not
visible, CM is not enabled, and you must specify an output data set
instead.
If you select the option to store the version file in the CM database, an additional
step is created in the compare job to store the version file for both the source and
target objects in the database.
If both the database and the data set are specified, the information in the data set
field is used to determine the output destination for the version file.
Optional: In the Description field, specify a description of the source or
target Db2 objects.
The description is printed in the comparison report, placed in the header record of
the version file, and used to describe the common properties of the Db2 objects.
Specify option 5, and press Enter.
The Define SQL SELECT Statement For SOURCE (GOCSQ) panelor Define SQL SELECT Statement For TARGET (GOCSQ) panel is displayed:Figure 2. Specify Source DB2 Databases (GOC1D) panel
GOCSQ min ----------- Define SQL SELECT Statement For SOURCE ------------ 12:12
Option ===>
1 - Edit SQL SELECT statement
Data set name . .
(Default if blank: 'TS6462.GOCSQL.SOURCE')
Location name . . RS22DC1A >
(Default if blank: RS22DD1A)
Optional: Specify a data set name and the location of the subsystem that contains the objects to
be compared.
If you do not specify a data set name, a temporary data set is created with the default
name listed. If you do not specify a location, the local subsystem is used.
If the specified data does not already exist, it is created.
Specify option 1, and press Enter.
Write or edit the SQL statement as needed.
The SELECT statement must query the Db2 catalog and return the following
columns:
TYPE CHAR(2)
QUAL VARCHAR(128)
NAME VARCHAR(128)
The SELECT statement can also optionally return the following additional columns:
VERSION VARCHAR(122)
INCLUDE CHAR(8)
XDTYPE CHAR(8) (exclude)
For example, the following query returns table spaces created by TS3071:
SELECT 'TS' AS TYPE, DBNAME AS QUAL, NAME
FROM SYSIBM.SYSTABLESPACE
WHERE CREATOR = 'TS3071'
Note: If the SQL returns XDTYPE requests, make sure that the SQL returns those rows
first.
Exit (PF3) to the DB2 Object Comparison Tool Menu (GOCMENU) panel.
SQL SELECT (DB2 catalog extract) is listed as the
Specification Status for the source or target, depending on which
one you specified.