DB2 – FETCH ,OPTIMIZE, QUERYNO, ISOLATION Clauses

These are important clauses in SELECT statements of DB2. Key parameters used in DB2.

FETCH FIRST 1 ROW ONLY:  Will limit the number of rows from the select statement. It avoids unnecessary I/O for select statements

OPTIMIZE FOR 2 ROWS: This is little different from previous one. The performance applies only for 2 rows. For the rest of the rows degradation of performance takes place.

The QUERYNO clause specifies the number to be used for this SQL statement in EXPLAIN output and trace records.

QUERYNO=999

How to override Isolation in select statement:

Isolation = > We can use WITH UR

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.