Tags

, , , , , , , ,

Predicates that we commonly use are Equality operators (=, <>, >, <, …), BETWEEN, IN, NOT, LIKE, EXIST and many more. Do you know that there is a processing order for each predicate. That is, at what stage of the SQL Query execution, particular predicate is applied.

Predicates are applied by stage in the following order:

  1. Indexable predicates that match on index key columns are applied and evaluated when the index is accessed.
  2. Stage 1 index screening predicates that have not been picked as index matching predicates but still refer to index columns, are applied to the index.
  3. Stage 1 page range screening predicates refer to partitioning columns are applied to limit the number of partitions that are accessed.
  4. Other stage 1 predicates are applied to the data, after data page access.
  5. The stage 2 predicates are applied on the returned data rows.

And list of all predicates and above information can be found at the IBM link

http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/perf/src/tpc/db2z_summarypredicateprocessing.html

 

Important SQL CODES and ABEND CODES
SORT JOIN – TO JOIN TWO FILES BASED ON A KEY
KNOW YOUR MAINFRAME
REXX – INITIAL SETUP
DB2 SQL Query to read COMP (COBOL) data stored in CHAR column
DB2 Performance – Using SET vs SYSDUMMY1 table
DB2 Performace – Predicates Processing Order
DB2 External Stored Procedures
DB2 UPDATE QUERY – UPDATE TABLE1 FROM TABLE2 DATA
DB2 PERFORMANCE ISSUE (When using EXISTS)
HOW DOES DB2 INTERNALLY (PHYSICALLY) STORE THE DATA
HOW DO YOU FIND WHO HAS ACTUALLY LOADED THE DB2 TABLE RECENTLY
TERMINATE A STOPPED DB2 UTILITY
SELECT UNIQUE RECORDS USING “GROUP BY”
DB2 and SQL INTERVIEW QUESTIONS
DB2 TIPS
Optimistic Locking vs. Pessimistic Locking
DB2 BIND OPTIONS and ISOLATION
Is SCHEMA name necessary for DYNAMIC QUERY
DB2 SQL – REPLACE CHARACTERS WITH ACCENTS (NON ENGLISH ALPHABETS)
Advertisements