Db2 Object Selection Criteria panel
The following panel is displayed when you select a Db2 object. However, if the extract request is a single Db2 table extract with Selective Columns and/or Delimited extract file, the Object Selection Criteria - Selective Columns screen is displayed.
DB2 Object Selection Criteria
Command ===> SCROLL ===> CSR
Specify selection criteria for table FRSAMP.ORDER_TABLE
Column Name Type(Length) Where More: >
- -------------------- ------------------ ----+---10----+---20----+---30---
P ORDER_NUMBER CHAR(6)
CUST_NUM CHAR(6)
SOC_SEC_NUM CHAR(11)
CREDIT_CARD_NUM CHAR(16)
ORD_TYPE CHAR(8)
ORD_DATE DATE
ORD_STAT CHAR(8)
ORD_AMOUNT DEC(9,2)
ORD_DEPOSIT DEC(9,2)
ORD_LINE_COUNT INTEGER
SHIP_CODE CHAR(2)
SHIP_DATE DATE
ORD_DESCRIPTION VCHAR(100)
Use the above panel to generate a Db2 SQL SELECT statement to select rows to extract from the Db2 object based on column values.
You can scroll the lower portion of this screen up and down if there are more column names on the list than will fit on the screen. In addition, you can scroll left and right if the values in the Where field exceed the screen width. For more information about scrolling, see Scrolling .
Key/Index Indicator
(Untitled 1 byte field) Indicates P Primary key, F Foreign Key, or I High Order Index key (if not Primary or Foreign) for the column as an aid for users to create SQL that will perform well.
Column Name
Displays the names of the columns in the Db2 object.
Type(Length)
Displays the data type of each column in the table. The length of the data is displayed in parentheses immediately following the data type for CHAR, CH, VCHAR, VC, GRPH, VGRPH, DEC, FIXPIC, National data types only.
Unicode Db2 character data types are identified as CH(x), VC(x) or LV(x) with a suffix of U16 or U-8.
See the following table for more information about Db2 data types.
Data Type Values
Data Type | Value |
|---|---|
CHAR or CH | Fixed-length character strings less than or equal to 254 characters. |
VCHAR or VC | Variable-length character strings less than or equal to the field length specified when the column is created. Maximum allowable field length is 4056 characters for 4k pages and 32,714 characters for 32k pages. |
LVCHAR or LV | Variable-length character strings less than or equal to 4056 characters for 4k pages and 32,714 characters for 32k pages. |
GRPH | Fixed-length graphic strings less than or equal to 127 characters. |
VGRPH | Variable-length graphic strings less than or equal to the field length specified when the column is created. Maximum allowable field length is 2028 characters for 4k pages and 16,357 characters for 32k pages. |
LVGRPH | Variable-length graphic strings less than or equal to 2028 characters for 4k pages and 16,357 characters for 32k pages. |
SMALLINT | Fixed-length, binary integers in the range of -32,768 to +32,767, requiring two bytes of storage. |
INTEGER | Fixed-length, binary integers in the range of -2,146,483,648 to +2,147,483,467, requiring four bytes of storage. |
BIGINT | Fixed-length, binary integers in the range of -9223372036854775808 to9223372036854775807, requiring eight bytes of storage. |
DEC | Fixed-length, decimal numbers in the range of -10E15 to +10E15. |
DECFLOAT | Decimal Floating Point can be either DECFLOAT(16) or DECFLOAT(34) representing either 16 or 34 significant digits. The range of a DECFLOAT(16) number is 10-383 to 10+384 and the range of a DECFLOAT(34) number is 10-6143 to 10+6144. A DECFLOAT(16) field occupies 8 bytes and a DECFLOAT(34) occupies 16 bytes. |
REAL | Fixed-length, single-precision, floating-point numbers in the approximate range of 5.4E-79 to 7.2E+75. |
DOUBLE | Fixed-length, double-precision, floating-point numbers in the approximate range of 5.4E-79 to 7.2E+75. |
DATE | A three-part value (year, month, day) whose format is determined by Db2. |
TIME | A three-part value (hour, minute, second) whose format is determined by Db2. |
TMSTMP(n) | TIMESTAMP: A seven-part value (year, month, day, hour, minute, second, and precision) whose format is YYYY-MM-DD-HH.MM.SS.TTTTTTTTTTTT. The precision value (TTTTTTTTTTTT) is variable length and has 0 to 12 digits (up to picosecond) with a default of 6 (microsecond). TMSTMP(n) specifies the scale (length) with n = 0 through 12. For Db2 Versions prior to Version 10, the length is always 6. |
TMESTZ(n) | TIMESTAMP WITH TIME ZONE: An eight-part value (year, month, day, hour, minute, second, precision, time zone) whose format is YYYY-MM-DD-HH.MM.SS.TTTTTTTTTTTT±HH:MM. The precision value (TTTTTTTTTTTT) is variable length and has 0 to 12 digits (up to picosecond) with a default of 6 (microsecond). The time zone is the difference in hours and minutes between local time and UTC. The range of the hour offset is -12 to +14, and the minute offset is 00 to 59. The time zone is specified in the format ±th.tm, with values ranging from -12.59 to +14.00. TMESTZ(n) specifies the scale (length) with n = 0 through 12. Db2 Version 10 and higher only. |
CLOB | A character large object (CLOB) is a varying-length string with a maximum length of 2 147 483 647 bytes (2 gigabytes minus 1 byte). |
DBCLOB | A double-byte character large object (DBCLOB) is a varying-length string with a maximum length of 1 073 741 823 double-byte characters. |
XML | A data type for storage of well formed XML documents. |
BLOB | A binary large object (BLOB) is a varying-length string with a maximum length of 2 147 483 647 bytes (2 gigabytes minus 1 byte). |
BINARY | BINARY is a fixed length binary string which extends the support for binary strings beyond BLOBs. Length attribute must between 1 and 255 inclusive. |
VARBINARY | VARBINARY is a variable length binary string which extends the support for binary strings beyond BLOBs. Length attribute must between 1 and 32704. |
ROWID | Row identifier. A value that uniquely identifies a row. If a table contains a large object it must also have a ROWID column. |
Where
Specify values of up to 255 characters in length to limit the rows extracted from the Db2 object. You can scroll this field left and right.
The Where column is analogous to the predicate specified in the WHERE clause in an SQL SELECT statement. The following operators are valid in the Where column:
Operator | Description | Operator | Description |
|---|---|---|---|
> | is greater than | Ø > | is not greater than |
< | is less than | Ø < | is not less than |
= | is equal to | IS NULL | has a null value |
Ø = or <> | is not equal to | LIKE | searches for a pattern |
>= | is greater than or equal to | IN | in a list of values |
<= | is less than or equal to | BETWEEN | within a range of values |
AND is implied as the logical operator between columns when multiple columns have WHERE values specified. Specify OR to override the implied AND. Use NOT to negate BETWEEN, LIKE, NULL, and IN operators.
Enter the WHERE clause information as you would in an SQL SELECT statement.
Use parentheses to indicate the order of operations.
Examples:
To select rows having a numeric column value between 9 and 20, specify:
BETWEEN 9 AND 20To select rows having a string, “SMITH,” anywhere in the column, specify:
LIKE '%SMITH%'
If you want to extract a specific row, we recommend the following:
- Use the equals (=) operator and be as specific as possible when entering values in the Where field.
- Specify values for columns that are part of an index.
Specifying Mixed Strings When Using DBCS
You can use mixed strings to specify values in the Where field on the Selection Criteria screen.
Use the following syntax for CHAR, VCHAR, and LVCHAR string:
where,
= | Any valid operator (see Where for a list of valid operators) |
’(apostrophe) | Delimiter |
> | Db2 shift out character |
< | Db2 shift out character |
sbcs-string | DBCS data |
dbcs-string | DBCS data |
Use the following syntax for GRPH, VGRPH, and LVGRPH strings:
where,
= | any valid operator (see Where for a list of valid operators) |
G | constant |
’(apostrophe) | delimiter |
> | Db2 shift out character |
< | Db2 shift out character |
dbcs-string | DBCS data |
Primary Commands
The following File-AID/RDX-specific primary commands are valid on this screen:
CANCEL (CAN)
Enter the CANCEL command to return to the Object Selection Criteria screen without saving any changes. There are no operands associated with this command.
CAPS
Enter the CAPS command to toggle caps mode on and off.
END
Verifies the SQL syntax and returns you to the Selection Criteria Specification screen. There are no operands associated with this command.
SQL
Invokes the SQL Edit screen. This screen enables you to edit or specify the selection criteria in its SQL format. For more information about editing your selection criteria in its SQL format, see SQL Edit. There are no operands associated with this command.
UDTSWAP (UDTS)
Swaps the display for distinct type columns from UDT name to the built-in data type and length and vice versa. There are no operands associated with this command.
UDTTYPE (UDT)
Lists all distinct data types (UDTs) that are part of the extract. The list displays the schema, UDT name, and the built-in data type and length. There are no operands associated with this command.
Distinct Data Types for This Extract
The Distinct Data Types for This Extract window shown in the following figure is displayed when you enter the UDTTYPE command on the Selection Criteria screen.
Distinct Data Types for This Extract Window
This window displays the list of all UDTs (distinct data types) that are included in the tables to be extracted. Use the END command to close the window.
Schema
Identifies the UDT’s schema ID.
Type
Identifies the name of the distinct type.
Base Data Type
Displays the built-in data type for the UDT.
Undisplayable Criteria Specified
If you specify an existing extract request containing an SQL SELECT statement that cannot be displayed in this format, the Selection Criteria screen is displayed as shown in the following figure. Use the SQL command to view or modify the SQL SELECT statement.
Object Selection Criteria Screen — Undisplayable Criteria Specified
Command ===>
Specify selection criteria for table LOCATION.FRSAMP.ORDER_TABLE
Column Name Type(Length) Where
-------------------- ------------ ----+---10----+---20----+---30----
The existing selection criteria is in error or cannot be displayed in
this format. To view or edit the criteria, enter the SQL command. To
use the criteria, specify option above and enter the END command.