Restrict data query on Strategy Object Selection within RC/Migrator
Article ID: 52308
Updated On:
Products
Issue/Introduction
When choosing objects for RC/Migrator for Db2 for z/OS (RCM) 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.
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.
MIGRATION strategies and ALTERATION strategies 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 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 Database Management Solutions for DB2 for z/OS:
Review Interface Basics, Extended Query Facility (EQF), Define Replaceable Parameters section
Feedback
