Restrict data query on Strategy Object Selection

Document ID : KB000052308
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

When choosing objects for Migration, Alteration or Compare I often get a very big list by accident instead of using the provided WHERE clause simply because I pressed enter before realizing that it would list all the objects. Is there a way that I can avoid this? Sometimes I have to wait a while before the unnecessary query comes back so I can make a more specific choice.

Solution:

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. Gets a list using "S" and then use "Y" to edit it and change the DEFAULT field.

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.

Let's just say for arguments sake that this EQF has been written on a WHERE clause from the ALTER TABLESPACE function of an ALTERATION STRATEGY. Normally when I alter tablespaces I know the CREATOR and NAME of the tablespace that I want. Having created the above first EQF and set it to be the DEFAULT query the next time that I go into the ALTER TABLESPACE function from an ALTERATION strategy the default query will execute when I have chosen the function type and the object type to be altered. Instead of a big list of tablespaces that I don't want I will be presented with this screen instead:

19.0                Replaceable Parms  ( Caps Off  )      2016/01/13  01:25:40
Command ==>
 
       Name: MYTS                              Share: Y            Default: Y
Description:                                   Panel: RMTABSPC
------------------------------------------------------------------------------
 Enter Values for Run Time Parameters:
 
    1 CREATOR  ___________________________________________________________>
    2 NAME     ___________________________________________________________>

Now I can enter the name and creator that I want. 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.

For more details on EQF's check the CA Database Management Solutions for DB2 for z/OS, General Facilities Reference Guide, Using the Extended Query Facility (EQF), Define Replaceable Parameters section.

EQF's can be found in a variety of places throughout the DB2 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.