Using advanced object filters

Advanced filters panel fields

Table Owner

Enter the table owner (creator) of the desired table. Enter as much of the value as necessary until the end of the field. If the table owner is longer than the allotted space, see Using Db2 long names for information on how another panel displays so you can complete the value.

Table Name

Enter the table name of the desired table. Enter as much of the value as necessary until the end of the field. If the table name is longer than the allotted space, see Using Db2 long names for information on how another panel displays so you can complete the value.

Column Name

Enter the name of the column to be filtered on. The action taken for this column filter depends on the value of the Op field. The first occurrence of spaces in any column name field indicates the end of data, and no further checking, validation, or saving of entries beyond that line on the panel is done. This allows for quick exclusion of unneeded entries already entered, if you want to do so.

Op

Enter the operator. The operator determines the type of action to be taken against the column name and value. Valid operators are:

EQ

equal

NE

not equal

LT

less than

LE

less than or equal

GT

greater than

GE

greater than or equal

LK

LIKE (value must have single trailing percent sign)

NL

not LIKE (value must have single trailing percent sign)

CH

column value has changed

NC

column value has not changed

IC

include the column

XC

exclude the column

IC and XC:

IC and XC mean include or exclude this column from the detail report, any SQL, and any LOAD processes. This operator also ignores column values, if specified.

IC and XC are mutually exclusive at the table level. If one IC operator is found, you must explicitly include all desired columns. Any other column not listed will be excluded. For every XC operator found, that column will be excluded from the result set. These operators can be useful in examining only a subset of a table's columns. Be aware that exclusion of any columns can result in an UPDATE action with no apparent column update (that is, an excluded column was the only column updated).

Because the IC and XC operators result in only a subset of column data being reported on, be very careful if generating SQL with this option. It is actually most likely you would bypass SQL generation using these operators. For example, if you omitted any of the key columns for a table, unpredictable results would occur if running any SQL generated since the WHERE clause would not properly reflect full key values.

Column Value

Enter the value against which the column data will be compared. This field may be irrelevant depending on the value of the "Op" field. If relevant, the value to be compared against will come from the column data value for either the pre- or post-row image (never the current image, if present). For example, it is considered a match on an EQ condition if the column value of either the pre- or post-row image matches on the supplied filter value here. When you need to match a column based upon a hexadecimal value, you can enter the value using the pattern X'HH..HH' (where H is a valid hexadecimal number).

If the operator field is LK or NL, indicating some form of LIKE condition, only a single trailing percent sign (%) is allowed in the value. If omitted, the first blank will be considered the wildcard. If multiple percent signs are found, only the first is used and the rest of the data is considered all wild cards. Use single quotes only around character type data if and only the string contains embedded spaces, otherwise do not use quotes.

Cond

Enter the conditional value, if necessary. Conditionals are only necessary if multiple columns are present. Conditional values are A (AND), O (OR), with any set of parentheses as needed. (See the tutorial on this topic for complete description of these conditionals). If this field is left blank, it terminates all checking on subsequent lines of input and is considered an end-of-data marker. Therefore, do not leave this field blank if subsequent lines of input are needed and non-blank. When the Op field is either IC or XC, this field should always be set to O, unless it is the last line of input, in which case it can be left blank.

The Advanced filters panel commands

COLUMNS command

On the Advanced filters panel, enter a table name and then type the command 'COLUMNS'. This displays every column name for the specified table and allows you to set filters for each column. When the 'COLUMNS' command is used, the column names do not necessarily have to be typed in manually.

V3.5.0 ------------------------- Column list -------------------- SC01/SS1A    

  Hit 'Enter' to save current columns; 'PF3' to exit and use saved columns;      
 Type 'Cancel' to bypass use of all saved columns for this table.               
                                                                                
 Table Owner             Table Name                                             
 --------------------    ------------------------------                         
 PDUSER                  PMRTEST1                                               
                              Row 1 of 12                                
 ------------------------------------------------------------------------------ 
 Column Name                    Op  Column Value (case-sensitive)        Cond   
 ------------------------------ --  -----------------------------------  ----   
 NAME1                                                                          
 NAME10                                                                         
 NAME11                                                                         
 NAME12                                                                         
 NAME2                                                                          
 NAME3                                                                          
 NAME4                                                                          
 NAME5                                                                          
 NAME6                                                                          
 NAME7                                                                          
 NAME8                                                                          
 NAME9 


                                                                         
 ******************************** Bottom of Data *******************************
 Command ===>                                                  Scroll ===> PAGE

Column data filter examples

Listed here are three examples of the column data filters in the created JCL.

The first example shows a single table, single column filter.

//FILTERS      DD   *
TABLE OWNER=JOHNQ
TABLE NAME=TABLEX
COLUMN NAME=GROUPNAME
OPERATOR=EQ 
VALUE=WALDO GROUP
COND= 
/* 

The second example adds another table to Example 1.

//FILTERS      DD   * 
TABLE OWNER=JOHNQ
TABLE NAME=TABLEX  
COLUMN NAME=GROUPNAME
OPERATOR=EQ
VALUE=WALDO GROUP
COND=
TABLE OWNER=BOBX
TABLE NAME=TABLEZ
COLUMN NAME=DEPT
OPERATOR=EQ
VALUE=100
COND=   
/*

Example 3

This example shows how to use the COND field for a single table. In this example, ((GROUP_NAME = 'WALDO GROUP' AND CITY = 'NEW YORK') OR CITY = 'BOSTON').

//FILTERS  DD *
TABLE OWNER=JOSH1  
TABLE NAME =GROUP_LOCATIONS  
COLUMN NAME=GROUP_NAME  
OPERATOR   =EQ      
VALUE      =WALDO GROUP       
COND       =(A      
COLUMN NAME=CITY
OPERATOR   =EQ      
VALUE      =NEW YORK  
COND       =)O      
COLUMN NAME=CITY
OPERATOR   =EQ      
VALUE      =BOSTON  
COND       =