Db2 uses hints (guidelines) like oracle)

Db2 uses hints (guidelines) like oracle)
Db2 uses hints (guidelines) C: \ DB2> more f3.sqlSELECT d like oracle. DEPTNAME, e. FIRSTNME, e. lastnamefrom department d, employee ewhere d. DEPTNO = e. workdept and e. empno like '123 '/* */;
C: \ DB2> C: \ DB2> db2expln-d sample-f f3. SQL-g-t-z ";"
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991,201 2 Licensed Material-Program Property of IBMIBM DB2 Universal Database SQL and XQUERY Explain Tool
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991,201 2 Licensed Material-Program Property of IBMIBM DB2 Universal Database SQL and XQUERY Explain Tool
* ******************* DYNAMIC ******************* ********************
================================ STATEMENT ===================== ======================================
Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5
Partition Parallel = No Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", "LIAO"

Statement:
SELECT d. DEPTNAME, e. FIRSTNME, e. lastname from department d, employee e where d. DEPTNO = e. workdept and e. empno like '2016 '/* */

Section codes Page = 1208
Estimated cost= 13.634712 Estimated Cardinality = 9.816054
Access Table Name = LIAO. employee id = 2, 6 | Index Scan: Name = LIAO. PK_EMPLOYEE ID = 1 | Regular Index (Not Clustered) | Index Columns: | 1: EMPNO (Ascending) | # Columns = 4 | Skip Inserted Rows | Avoid Locking Committed Data | Currently Committed for Cursor Stability | Evaluate Predicates Before Locking for Key | # Key Columns = 1 | Start Key: wide sive Value | 1: '000000' | Stop Key: wide sive Value | 1: '000000' | Data Prefetch: Sequential (0), Readahead | Index Prefetch: none | Lock Intents | Table: Intent Share | Row: Next Key Share | Sargable Predicate (s) | Process Build Table for Hash JoinHash Join | Early Out: single Match Per Inner Row | Estimated Build Size: 4000 | Estimated Probe Size: 4000 | Access Table Name = LIAO. department id = 2, 5 | # Columns = 2 | Skip Inserted Rows | Avoid Locking Committed Data | Currently Committed for Cusor Stability | May participant in Scan Sharing structures | Scan may start anywhere and wrap, for completion | Fast scan, for purposes of scan sharing management | Scan can be throttled in scan sharing management | Relation Scan | Prefetch: eligible | Lock Intents | Table: Intent Share | Row: Next Key Share | Sargable Predicate (s) | Process Probe Table for Hash JoinReturn Data to Application | # Columns = 3
End of section

Optimizer Plan:
Rows Operator (ID) Cost
9.81605 RETURN (1) 13.6347 | 9.81605HSJOIN(2) 13.6347/\ 14 9.81605 tbscan fetch (3) (4) 6.81583 6.8181 |/\ 14 9.81605 42 Table: IXSCAN Table: LIAO (5) liao department 0.00986447 EMPLOYEE | 42 Index: LIAO PK_EMPLOYEE


C: \ DB2> db2set DB2_OPTPROFILE = YES
C: \ DB2> db2set-all [e] DB2PATH = C: \ v105 \ IBM \ SQLLIB[I] DB2_OPTPROFILE = YES[I] DB2INSTOWNER = LIAO-PC [I] DB2PORTRANGE = 60000: 60005 [I] DB2INSTPROF = C: \ V105 \ PROGRAMDATA \ IBM \ DB2 \ DB2COPY1 [I] DB2COMM = TCPIP [g] DB2_EXTSECURITY = NO [g] DB2_COMMON_APP_DATA_PATH = C: \ v105 \ ProgramData \ [g] DB2SYSTEM = LIAO-PC [g] DB2PATH = C: \ v105 \ IBM \ SQLLIB [g] DB2INSTDEF = DB2 [g] DB2ADMINSERVER = DB2DAS00
C: \ DB2> db2stop2015-12-04 00:43:34 0 0 SQL1025N database not stopped, because the database is still active. SQL1025N does not stop the database because the database is still active.
C: \ DB2> db2stop force00:43:40 0 0 SQL1064N DB2STOP is successfully processed. SQL1064N DB2STOP is successfully processed.
C: \ DB2> db2start00:43:48 0 0 SQL1063N DB2START is successfully processed. SQL1063N DB2START is successfully processed.
C: \ DB2> db2expln-d sample-f f3. SQL-g-t-z ";"
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991,201 2 Licensed Material-Program Property of IBMIBM DB2 Universal Database SQL and XQUERY Explain Tool
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991,201 2 Licensed Material-Program Property of IBMIBM DB2 Universal Database SQL and XQUERY Explain Tool
* ******************* DYNAMIC ******************* ********************
================================ STATEMENT ===================== ======================================
Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5
Partition Parallel = No Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", "LIAO"

Statement:
SELECT d. DEPTNAME, e. FIRSTNME, e. lastname from department d, employee e where d. DEPTNO = e. workdept and e. empno like '2016 '/* */

Section codes Page = 1208
Estimated cost= 13.648438 Estimated Cardinality = 9.816054
Access Table Name = LIAO. department id = 2, 5 | # Columns = 2 | Skip Inserted Rows | Avoid Locking Committed Data | Currently Committed for Cursor Stability | May participant in Scan Sharing structures | Scan may start anywhere and wrap, for completion | Fast scan, for purposes of scan sharing management | Scan can be throttled in scan sharing management | Relation Scan | Prefetch: Eligible | Lock Intents | Table: intent Share | Row: Next Key Share | Sargable Predicate (s) | Insert Into Sorted Temp Table ID = t1 | # Columns = 2 | # Sort Key Columns = 1 | Key 1: DEPTNO (Ascending) | Sortheap Allocation Parameters: | # Rows = 14.000000 | Row Width = 28 | PipedSorted Temp Table Completion ID = t1Access Temp Table ID = t1 | # Columns = 2 | Relation Scan | | Prefetch: eligibleMerge Join | Access Table Name = LIAO. employee id = 2, 6 | # Columns = 4 | Skip Inserted Rows | Avoid Locking Committed Data | Currently Committed for Cursor Stability | May participant in Scan Sharing structures | Scan may start anywhere and wrap, for completion | Fast scan, for purposes of scan sharing management | Scan can be throttled in scan sharing management | Relation Scan | Prefetch: eligible | Lock Intents | Table: Intent Share | Row: Next Key Share | Sargable Predicate (s) | # Predicates = 1 | Insert Into Sorted Temp Table ID = t2 | # Columns = 3 | # Sort Key Columns = 1 | | Key 1: WORKDEPT (Ascending) | Sortheap Allocation Parameters: | # Rows = 10.000000 | Row Width = 32 | Piped | Sorted Temp Table Completion ID = t2 | Access Temp Table ID = t2 | # Columns = 3 | Relation Scan | Prefetch: eligibleReturn Data to Application | # Columns = 3
End of section

Optimizer Plan:
Rows Operator (ID) Cost
9.81605 RETURN (1) 13.6484 | 9.81605MSJOIN(2) 13.6484/\-\ 14 * TBSCAN | (3) 9.81605 6.81692 TBSCAN | (7) 14 6.83002 SORT | (4) 9.81605 6.81674 SORT | (8) 14 6.82983 TBSCAN | (5) 9.81605 6.81583 TBSCAN | (9) 14 6.82912 Table: | LIAO 42 DEPARTMENT Table: LIAO EMPLOYEE



C: \ DB2>