DB2 – FETCH ,OPTIMIZE, QUERYNO, ISOLATION Clauses

TSQLhese are important clauses in SELECT statements of 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.

Refer 238 SQL queries given in http://sqlnplsqlguru.blogspot.in

 

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.