Space announcement This documentation space provides the same content as before, but the organization of the content has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

DB2 Build SQL Easy Query (5.5.2)

Related Tpoics

Quick-Reference

Customizing-after-installation

The DB2 Build SQL Easy Query screen (5.5.2) lets you view a list of all columns defined for a single Db2 table. Use this list to prepare an EASY QUERY to retrieve data from the Db2 table and display the result table to review and edit.

You can perform the following:

  • Select columns to display from this table.
  • Select the left-to-right order of display for columns.
  • Select rows by using WHERE clauses.
  • Select columns for sort sequence using ORDER-BY clauses.

Access this screen by using the Q (Query) line command to select a table from the DB2 Table/View List (5.5.1) screen.

DB2 Build SQL Easy Query Screen (5.5.2)

 ----------------------- DB2 BUILD SQL EASY QUERY (5.5.2) -----------------C024
COMMAND ===>                                                   SCROLL ===> CSR
MODULE:          CSECT:
VALID COMMANDS: SHOW RESULT/SQL   CHECK   RESET   END

CREATOR: CWX0030  TABLE: COMPOSITE_TABLE                    ROW     1 OF 5
                                                       POSITION     1 OF 254

LINE COMMANDS:  A (After) B (Before) M/MM (Move) S/SS (Select) X/XX (eXclude)

                                      ORDER-BY           WHERE CLAUSE
CMD   COLUMN  NAME      ATTRIBUTES    SEQ A/D        VALUES AND OPERATORS
--------------------- --------------- --- --- ----+---10----+---20----+---30-->
S_ DATE_FIELD         DATE             __  _
S_ TIME_FIELD         TIME             __  _
S_ TIMESTAMP_FIELD    TIMESTMP         __  _
S_ CHAR_FIELD         CHAR(88)         __  _  IS NOT NULL
S_ VARCHAR_FIELD      VARCHAR(88)      __  _
   **END**

After you prepare an EASY QUERY, you have the following options:

  • Abort the query by transferring to another screen or exiting Code Debug CICS.
  • Display the SQL call that is generated in order to perform the query. Use the SHOW SQL command to transfer you to the DB2 Browse Generated SQL Call (5.5.3) screen.
  • Execute the query and display the result table from it by using the SHOW RESULT command. This transfers you to the DB2 Browse Result Table (5.5.4) screen.

Input Fields

CMD

Line command entry field. Valid entries are:

  • A (After): Destination of an M or MM line command.
  • B (Before): Destination of an M or MM line command.
  • M (Move): Specifies which column is moved after or before another column.
  • MM (Move block): Specifies which block of columns is moved after or before another column.
  • S (Select): Selects column from display.
  • SS (Select block): Selects a block of columns from display.
  • X (Exclude) Excludes column from display.
  • XX (Exclude block) Excludes block of columns from display.

ORDER-BY

Defines the order and sort sequence of columns displayed in the result table.

  • SEQ: Defines the order for sorting data in the column. To exclude a column from an ORDER-BY clause, do not key anything in this field. If a value is keyed in error, blank it out. Allowed values are integers in the range of 1 to 16.
  • A/D: Defines the sort order for data in the column. Values are:
    • A: Sorts column in ascending sequence.
    • D: Sorts column in descending sequence.

WHERE CLAUSE VALUES AND OPERATORS

Provides an area for entering search conditions used to select the rows returned by the query. The search conditions are combined with the column name to create an SQL WHERE clause. SQL WHERE clauses produce an intermediate result table by applying the search condition against the rows in the selected table. Data can consist of literals, other column names, parentheses (for grouping clauses or data), or any of the Db2 operators below.

Important

Due to the processing of WHERE clause data by Code Debug CICS, the first parameter entered in a field must be a relational operator and the last parameter must be either a blank or a Db2 operator.

Db2 Boolean Operators

Operator

Description

AND

Allows qualifying data and/or clauses to be logically ANDed together. If a WHERE clause does not end in AND or OR, AND is assumed.

OR

Allows qualifying data and/or clauses to be logically ORed together.

Output Fields

CREATOR

Name of the creator of the Db2 table for which you are creating an EASY QUERY.

TABLE

Name of the Db2 table for which you are creating an EASY QUERY.

ROW xxxxx OF yyyyy

Displays the current top row (xxxxx) within the total rows (yyyyy) of columns from the table that are available for display on this screen. If the X (Exclude) or XX (Exclude block) line commands are used to exclude rows from display, the total rows (yyyyy) value changes to reflect the new number of rows being displayed.

POSITION xxxxx OF yyyyy

Displays the current leftmost position of displayed data in the WHERE CLAUSE VALUES AND OPERATORS field as well as the maximum position allowed. The leftmost position (xxxxx) is relative to one. A scale line in the section eases data entry. The maximum position (yyyyy) is the maximum length of an individual WHERE clause. To scroll data in these fields, use the RIGHT and LEFT scrolling commands.

COLUMN NAME

Name of a column within the Db2 table being accessed. This title is also used for this column in the Db2 result table if the column is selected for display by a query.

ATTRIBUTES

Display only field that provides the attributes associated with the column in the COLUMN NAME field. The column names in this list are in the left-to-right order after the table being accessed.

  • SMALLINT: Whole numbers in the range -32768 to +32767.
  • INTEGER: Whole numbers in the range -2147483648 to +2147483647.
  • DECIMAL (xx,yy): Value with up to 31 significant digits. xx specifies the total number of significant digits. yy specifies the number of digits to the right of the decimal point.
  • FLOAT: Single precision floating point value in the range 5.4E-79 to 7.2E+75.
  • LONGFLT: Double precision floating point value in the range 5.4E-79 to 7.2E+75.
  • CHAR (xxx): Fixed length string of xxx characters, where xxx has a maximum value of 254.
  • VARCHAR (xxx): Variable length string of up to xxx characters, where xxx has a maximum value of 254.
  • LONGVAR (xxxxx): Variable length string of up to xxxxx characters. Value xxxxx is calculated by Db2 and may be as high as 32K.
  • GRAPHIC (xxx): Fixed length string of xxx two-byte characters, where xxx has a maximum value of 127. This is used for data such as kanji.
  • VARG (xxx): Variable length string of xxx two-byte characters, where xxx has a maximum value of 127. This is used for data such as kanji.
  • LONGVARG (xxxxx): Variable length string of xxxxx two-byte characters. Value xxxxx is calculated by Db2 and may be as high as 16K. This is used for data such as kanji.
  • DATE: Ten position date format value. The actual format is user-defined.
  • TIME: Fifteen position time format value. The actual format is user-defined.
  • TIMESTMP: The column contains data in time-stamp format, YYYY-MM-DD-HH.MM.SS.NNNNNN where:
    • YYYY: year
    • MM: month
    • DD: day
    • HH: hour
    • MM: minute
    • SS: second
    • NNNNNN: nano-second.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*