Create and Execute a Data Query

A data query is tied to a particular table as opposed to a function (like selection queries). A data query is not based on any pre-existing internal SELECT statements. You can specify the columns, column order, WHERE conditions, and row order. A data query can also be used as a model to create other queries by saving it under another name.
dm
A data query is tied to a particular table
 
as opposed to a function (like selection queries). A data query is not based on any pre-existing internal SELECT statements. You can specify the columns, column order, WHERE conditions, and row order. A data query can also be used as a model to create other queries by saving it under another name.
3
3
Create and Execute a Data Query
Follow these steps:
  1. Enter 
    Y
     in the Where field of a product panel whose functions support data queries and the name of an extended query (for example, editing a table in RC/Edit).
  2. Press Enter. The Data Query Edit panel appears:
    The For Table field is unique to this panel. This field displays the name of the table that is associated with the data query. For example, B0625JS.EMPTABLE.
  3. Complete the following fields to identify the data query, control the SQL statement information, generate test counts, and specify column information:
    • Query Name
      Specifies the name of the query, up to eight characters or leave it blank to assign a temporary name (TEMP). Temporary queries are not saved after you leave the function or table you are accessing. To save the query, enter a name.
      When you are editing a query and the name is changed, the creator ID used to store the query is your ID.
      For
      CA RC/Migrator™ for DB2 for z/OS
      , enter a name for a data query. TEMP is not accepted.
    • Description
      (Optional) Specifies a short description that is associated with the query.
    • Confirm Replace
      Indicates whether you request a confirmation to appear before an existing query is replaced with the query being edited.
    • Auto Cast
      Indicates the default cast option for a column that is specified as a user-defined type. This option is applicable only to columns that contain an entry in the WHERE condition.
    • Share
      Indicates how other users can access your query.
    • Default
      Indicates if the query is the default for the table or function.
    • Test Count
      Provides a count of the number of rows that are retrieved based on the current SELECT statement when you specify Y or the COUNT primary command. Otherwise, enter N.
    • View SQL
      Shows the SQL SELECT statement in standard SQL format when you specify Y or the SQL primary command. All conditions, including the extended where, are displayed. Otherwise, enter N.
    • Shrink
      Shows a subset of the columns that are listed in the SELECT statement section. This field is useful for reducing table columns to show only pertinent columns. The following values are valid:
      • Y
        Shows any columns that have been selected, have ordering specifications, or have a Where condition.
      • N
        Shows all columns in the table.
      • S
        Shows columns that are selected.
      • W
        Shows columns with a where condition.
      • O
        Shows columns that are used in an ORDER BY clause.
      You can use the SHRINK command to switch the shrink value between Y and N. This command can be assigned to a function key to allow easy invocation of the shrink mechanism.
    • Row Limit
      Specifies the maximum number of rows you want returned from the query. (This field has the same function as Row Limit on the Data Options panel.)
    • SEL and Extended Where
      Selects the columns that you want to include in the SELECT statement. The following values are valid:
      • S
        Selects a column. By default, all columns are selected. To unselect a column, blank it out or enter the RESET primary command.
      • #
        Selects the column order in the generated SELECT statement. For example, if you want the DEPARTMENT column to appear first (instead of NAME), type 1 in the SEL field. The numbers do not have to be in strict sequential order (for example, 1, 3, 5 is valid). Any column with a sequence number is placed in front of the other S columns. Therefore, if you number a column 99 and it is the only column with a sequence number, then it is placed first in the list
      Enter S or a number or a combination of both in either field. You can also specify the ALL, ALL NUMBER, SORT SEL, or SORT COL primary commands.
      For RC/Edit, you can select the column by entering F to freeze the field upon entry to the editor. Frozen columns are displayed before sequence-numbered columns and before selected columns.
    • C (Column level casting control)
      Casts a column to its internal source data type at the column level when a column is defined as a user-defined type and you enter Y. Column level casting overrides the current specified Auto Cast field setting. If you leave this field blank, the auto-case setting controls casting.
      This field is underscored for columns that are defined as a user-defined type when appropriate.
    • Order By (ORD)
      Controls the sort order of the retrieved rows in each column. Enter a sequence number. The numbers control the sort order, with the lowest number representing the outer-most sort field. You can enter a number 1-9 followed by an A (for ascending) or D (descending). Valid examples include 1, 1A, and 6D.
    • WHERE Condition
      Specifies column-specific WHERE information. The condition is applied to the column name on its left. All standard SQL syntax is supported.
      For more complex WHERE information that cannot be represented in the SELECT statement section, enter the WHERE condition in the EXTENDED WHERE fields.
      Specifies how all conditions that are listed in the WHERE Condition column are joined to each other in the AND/OR column, and specifies that all conditions must be true for the row selected.
    Press Enter.
    The panel information is updated. Any outstanding changes (including column expansion due to abbreviations) are performed and the panel reappears.
  4. Press F3.
    The query is saved and executed. To save the query without executing it, enter the SAVE command.
Adding Extended Where Conditions
If you cannot enter the WHERE conditions using the columns in the middle of the Data Query Edit panel to create your SQL SELECT statement, you can specify EXTENDED WHERE conditions. When you mix AND/OR conditions or you use parentheses to separate conditions you must specify these conditions.
Note the following:
  • You can enter up to 45 lines of free-form text for your EXTENDED WHERE condition.
  • You can use column abbreviations instead of typing in column names. To enter the column abbreviation, enter :
    n
     where 
    n
     is the column number. For example, if BIRTHDAY is column 2 and you want to include this column in your query, enter :2 wherever you want the column to appear. Column Name identifies the DB2 column number and name. The column abbreviation number appears to the left of each column name. If the column number is highlighted, then the column is defined as NOT NULL WITHOUT DEFAULT. The NOT NULL indicator can be useful when using RC/Edit because you cannot insert or replicate rows if you did not select all columns that are defined as NOT NULL. Therefore, you must select the NOT NULL columns if you are choosing columns for a target table, such as within RC/Edit. You can enter the AUTOSEL primary command to select all columns with this type of definition.
  • Type shows the column type. There is also a TYPE command that is used to toggle to the full DB2 type definition.
  • When you press Enter, the abbreviation is automatically expanded to the full column name. The abbreviation is expanded with a column cast when the column is a user-defined type and casting is specified for the column.
  • You can mix an EXTENDED WHERE with conditions in the WHERE Condition column. These conditions have assumed parentheses around them to separate them from the EXTENDED WHERE. If you are mixing conditions, you must start the EXTENDED WHERE with AND/OR.