CA RC/Migrator for Db2 for Z/OS : Restrict data query on Strategy Object Selection

book

Article ID: 52308

calendar_today

Updated On:

Products

CA RC/Migrator for DB2 for z/OS

Issue/Introduction

When choosing objects for Migration, Alteration or Compare there is often a very big list returned by accident. Instead of using the provided WHERE clause this can happen simply because the enter key is pressed before realizing that it would list all the objects. Is there a way that this can be avoided? Sometimes there is a long wait before the unnecessary query comes back so a more specific selection can be made.

 

Environment

Release: R19, R20
Component: RCM

Resolution

In each screen location where there is a WHERE clause field in the header area it is possible to set in place a predefined query called an EQF. The Migration, Alteration and Compare strategy creation screens all have facilities for object selection by object type. Each screen which is used for object selection has a WHERE clause field to optionally restrict the records returned.

At each of these places it is possible to create and name a permanent EQF query which can be used from that location and that object type only. When the query is created using the "Y" option in the WHERE field the query can be set to be the DEFAULT query by setting the "Default" field to "Y" on the "SQL Selection Panel" where the EQF is written.

The specific query that will be the default can be changed at any time by updating the EQF record. Produce a list using "S" (Where => S)  and then use "Y" to edit it and change the DEFAULT field to a "Y".

Having a default query is optional unless you want it to start up automatically.

An EQF is essentially a piece of user written SQL that can be run by the author and can only be seen by the author unless the "Share" option has been set to "Y" or "U". The default setting applies to the author only.

The EQF can be a fixed piece of SQL like this:

AND CREATOR = 'mycreator'
AND NAME = 'objname'

or it can contain Replaceable Parameters like this:

AND CREATOR = '&CREATOR'
AND NAME = '&NAME'

or

AND NAME LIKE '&NAME'
AND CREATOR LIKE '&CREATOR'

With LIKE you would need to enter the % sign at parameter entry like %TSDE% or TS% for example to enter the correct mask if a mask is required.

&CREATOR and &NAME are two variables that must be given values upon execution of the query. The text after the '&' can be anything and is restricted to a maximum of 8 characters. They are not defined system variables but simply user defined for this query only.

For this example, this EQF has been written on a WHERE clause from the ALTER TABLESPACE function of an ALTERATION STRATEGY. Normally when tablespaces are altered , the CREATOR and NAME of the tablespace are known.

Having created the above first EQF and set to be the DEFAULT query,  the next time that the ALTER TABLESPACE function is used from an ALTERATION strategy the default query will execute when the function type and object type to be altered have been chosen. Instead of a big list of tablespaces that are not required, the user will be presented with this screen instead:

nn.n                Replaceable Parms  ( Caps Off  )      yyyy/mm/dd  hh:mm:ss
Command ==>
 
       Name: MYTS                              Share: Y            Default: Y
Description:                                   Panel: RMTABSPC
------------------------------------------------------------------------------
 Enter Values for Run Time Parameters:
 
    1 CREATOR  ___________________________________________________________>
    2 NAME     ___________________________________________________________>

Now the name and creator that are required can be entered. The EQF SQL can be written in any way that is valid SQL that will satisfy the need to be more specific when asking for lists of objects.

Take note that there can be a predefined EQF for each OBJECT TYPE. The two above are for tablespaces since the EQF SQL is specific to the DB2 Catalog table holding the object information.

MIGRATIONS and ALTERATIONS can see the same object specific EQF's. On Compare Strategies the WHERE clauses on the object mappings can have one default EQF for the FROM objects and one default for the TO objects.

EQF's can be found in a variety of places throughout the CA Database Management for DB2 for Z/OS tools. They all share the same method of operation; however the saved queries from other product screens may not be visible on other product screens depending on their relevance.

Additional Information

For more details on EQF's check the CA Database Management Solutions for DB2 for z/OS, Review Interface Basics, Extended Query Facility (EQF), Define Replaceable Parameters section.