Writer instructions | |
|---|---|
Page title | For most spaces, this page must be titled Space announcements. For spaces with localized content, this page must be titled Space announcements l10n. |
Purpose | Provide an announcement banner on every page of your space. |
Location | Move this page outside of your home branch. |
Guidelines | |
IN predicate
In
BMC AMI Utilities
, the IN predicate supports the standard Db2 subselect.
To process the subselect, BMC AMI Utilities complete this process:
- Extract the subselect statement.
- Pass the SQL to Db2 for processing.
- Collect the output.
- Filter the output from Db2 to exclude null and duplicate rows.
- Placed the output in an IBM MVS/ESA data space.
- Search the output by using a high-speed search engine.
The data type of the IN predicate operands must match exactly:
- The left operand of the IN predicate data type must match the right operand, which is the result that Db2 returned.
- The precision and scale of one operand must match the precision and scale of the other.
If the precision and scale are not the same, you can change them using the INTEGER, FLOAT, or DECIMAL Db2 built-in function. To improve performance when conversion is required, BMC recommends that you convert the right hand operand to the type and length of the left hand operand.
The following table shows the possible data type combinations of C1 and C2, and the action required.
Data type C1 | Data type C2 | Action |
|---|---|---|
SMALLINT | INTEGER | INTEGER(C1) |
SMALLINT | DECIMAL(P,S) | DECIMAL(C1,P,S) |
SMALLINT | REAL | (C1) IN (SELECT FLOAT(B) FROM TB1) |
SMALLINT | FLOAT | FLOAT(C1) |
INTEGER | SMALLINT | (SELECT INTEGER(C2) FROM TB1) |
INTEGER | DECIMAL(P,S) | DECIMAL (C1,P,S) |
INTEGER | REAL | FLOAT(C1) IN (SELECT FLOAT(B) FROM TB1) |
INTEGER | FLOAT | FLOAT(C1) |
REAL | SMALLINT | FLOAT(C1) IN (SELECT FLOAT(B) FROM TB1) |
REAL | INTEGER | FLOAT(C1) IN (SELECT FLOAT(B) FROM TB1) |
REAL | DECIMAL | DECIMAL(C1,P,S) |
REAL | FLOAT | FLOAT(C1) |
FLOAT | SMALLINT | (SELECT FLOAT(C2) FROM TB1) |
FLOAT | INTEGER | (SELECT FLOAT(C2) FROM TB1) |
FLOAT | DECIMAL | DECIMAL(C1,P,S) |
FLOAT | REAL | (SELECT FLOAT(C2) FROM TB1) |
DECIMAL(P,S) | SMALLINT | (SELECT DECIMAL(C2,P,S) FROM TB1) |
DECIMAL(P,S) | INTEGER | (SELECT DECIMAL(C2,P,S) FROM TB1) |
DECIMAL(P,S) | REAL | (SELECT DECIMAL(C2,P,S) FROM TB1) |
DECIMAL(P,S) | FLOAT | (SELECT DECIMAL(C2,P,S) FROM TB1) |
DECIMAL(P1,S1) | DECIMAL | DECIMAL(C1,P,S) IN (SELECT DECIMAL(C2,P,S) FROM TB1 where: p= min(31,max*s1,s2)+maxp1-s1,p2-s2)) s=max(s1,s2) |
External file technique
- ==
To further accelerate the search process, or if the data does not reside in the Db2 table, use the external file technique. This technique utilizes non-Db2 syntax and requires an input file that has been generated by either BMC AMI Unload or the Db2 REORG UNLOAD product.
The full syntax of the feature is defined as follows:
WHERE columnName IN (FILE(ddname) FORMAT(format))
In this syntax:
- columnName is the left operand of the IN predicate. This operand must be a column name and cannot be a constant. The attributes of this column dictate the data type, length, and null attribute of the first field of the external file.
- ddname is the name of the DD statement in the current job step that references the external data file.
format is the internal format of the data in the external file. Specify FORMAT(UNLOAD). For more information about unload formats, see FORMAT (LOAD syntax).
Processing an IN predicate by using an external file causes BMC AMI Utilities to complete these actions:
- Read the specified DDNAME (which can be on DISK or TAPE).
- Filter out duplicate and null rows.
- Place the data in an MVS/ESA data space.
- Search the data via a high-speed search engine.
The IN predicate external file can contain several data fields or columns. However, only the first data field is extracted from the file; the remaining data fields are ignored. The first data field is recognized by using the same attributes of the left operand and can be the concatenation of columns.
In one WHERE clause, you can have up to 200 IN predicates with different files, subselects, or both. You can use Boolean logic to form very complex pattern matching on large amounts of data at very high speeds. However, WHERE clause syntax errors might occur if memory space is exceeded. If that occurs, attempt to reduce the select data or add more paging data sets to your paging subsystem.