Zum besseren Verständnis finden Sie nachfolgend fünf Beispiele, die den Aufbau und das Format der Ausgabe von db2expln und dynexpln verdeutlichen. Diese Beispiele wurden für die Beispieldatenbank SAMPLE ausgeführt, die mit DB2 ausgeliefert wird. Jedes Beispiel wird kurz erläutert. Die signifikanten Unterschiede von einem Beispiel zum nächsten wurden fett hervorgehoben.
In diesem Beispiel wird einfach eine Liste aller Namen von Mitarbeitern (employee), ihrer Aufgaben (job), ihrer Abteilungen (department) und Standorte (location) sowie der Namen der Projekte, an denen sie arbeiten, abgerufen. Das wesentliche Merkmal dieses Zugriffsplans besteht darin, daß über Operationen zur Mischverknüpfung die relevanten Daten aus allen angegebenen Tabellen verknüpft werden. Da keine Indizes verfügbar sind, führt der Zugriffsplan eine Tabellensuche in allen Tabellen aus, und jede Tabelle muß sortiert werden, bevor sie mit einer anderen verknüpft werden kann.
******************** PACKAGE *************************************** Package Name = DOOLE.DYNEXPLN Prep Date = 2000/01/03 Prep Time = 15:47:58 Bind Timestamp = 2000-01-03-15.47.58.607455 Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = No Intra-Partition Parallel = No Function Path = "SYSIBM", "SYSFUN", "DOOLE" -------------------- SECTION --------------------------------------- Section = 1 SQL Statement: SELECT x.lastname, x.job, y.deptname, y.location, z.projname FROM employee AS x, department AS y, project AS z WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno = z.deptno Estimated Cost = 126 Estimated Cardinality = 153 Access Table Name = DOOLE.DEPARTMENT ID = 2,4 | #Columns = 3 | Relation Scan | | Prefetch: Eligible | Lock Intents | | Table: Intent Share | | Row : Next Key Share | Insert Into Sorted Temp Table ID = t1 | | #Columns = 3 | | #Sort Key Columns = 1 | | | Key 1: DEPTNO (Ascending) | | Sortheap Allocation Parameters: | | | #Rows = 40 | | | Row Width = 48 | | Piped Sorted Temp Table Completion ID = t1 Access Temp Table ID = t1 | #Columns = 3 | Relation Scan | | Prefetch: Eligible Merge Join | Access Table Name = DOOLE.PROJECT ID = 2,7 | | #Columns = 2 | | Relation Scan | | | Prefetch: Eligible | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | | Insert Into Sorted Temp Table ID = t2 | | | #Columns = 2 | | | #Sort Key Columns = 1 | | | | Key 1: DEPTNO (Ascending) | | | Sortheap Allocation Parameters: | | | | #Rows = 38 | | | | Row Width = 28 | | | Piped | Sorted Temp Table Completion ID = t2 | Access Temp Table ID = t2 | | #Columns = 2 | | Relation Scan | | | Prefetch: Eligible Merge Join | Access Table Name = DOOLE.EMPLOYEE ID = 2,5 | | #Columns = 3 | | Relation Scan | | | Prefetch: Eligible | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | | Insert Into Sorted Temp Table ID = t3 | | | #Columns = 3 | | | #Sort Key Columns = 1 | | | | Key 1: WORKDEPT (Ascending) | | | Sortheap Allocation Parameters: | | | | #Rows = 63 | | | | Row Width = 32 | | | Piped | Sorted Temp Table Completion ID = t3 | Access Temp Table ID = t3 | | #Columns = 3 | | Relation Scan | | | Prefetch: Eligible Return Data to Application | #Columns = 5 End of section Optimizer Plan: RETURN ( 1) | MSJOIN ( 2) / \ MSJOIN TBSCAN ( 3) ( 12) / \ | TBSCAN TBSCAN SORT ( 4) ( 8) ( 13) | | | SORT SORT TBSCAN ( 5) ( 9) ( 14) | | | TBSCAN TBSCAN Table: ( 6) ( 10) DOOLE | | EMPLOYEE Table: Table: DOOLE DOOLE DEPARTMENT PROJECT
Im ersten Teil des Plans wird auf die Tabellen DEPARTMENT und PROJECT zugegriffen, die über eine Mischverknüpfung verknüpft werden. Das Ergebnis dieser Verknüpfung wird mit der Tabelle EMPLOYEE verknüpft. Die Ergebniszeilen werden an die Anwendung zurückgegeben.
In diesem Beispiel wird dieselbe SQL-Anweisung wie in Beispiel 1: Plan ohne Parallelität gezeigt, jedoch wurde diese Abfrage für eine 4-Wege-SMP-Maschine kompiliert.
******************** PACKAGE *************************************** Package Name = DOOLE.DYNEXPLN Prep Date = 2000/01/03 Prep Time = 15:48:51 Bind Timestamp = 2000-01-03-15.48.51.402403 Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = No Intra-Partition Parallel = Yes (Bind Degree = 4) Function Path = "SYSIBM", "SYSFUN", "DOOLE" -------------------- SECTION --------------------------------------- Section = 1 SQL Statement: SELECT x.lastname, x.job, y.deptname, y.location, z.projname FROM employee AS x, department AS y, project AS z WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno = z.deptno Intra-Partition Parallelism Degree = 4 Estimated Cost = 142 Estimated Cardinality = 153 Process Using 4 Subagents | Access Table Name = DOOLE.DEPARTMENT ID = 2,4 | | #Columns = 3 | | Parallel Scan | | Relation Scan | | | Prefetch: Eligible | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | | Insert Into Sorted Shared Temp Table ID = t1 | | | #Columns = 3 | | | #Sort Key Columns = 1 | | | | Key 1: DEPTNO (Ascending) | | | Use Round-Robin Sort | | | Sortheap Allocation Parameters: | | | | #Rows = 40 | | | | Row Width = 48 | | | Piped | Sorted Shared Temp Table Completion ID = t1 | Access Temp Table ID = t1 | | #Columns = 3 | | Relation Scan | | | Prefetch: Eligible | Merge Join | | Access Table Name = DOOLE.PROJECT ID = 2,7 | | | #Columns = 2 | | | Parallel Scan | | | Relation Scan | | | | Prefetch: Eligible | | | Lock Intents | | | | Table: Intent Share | | | | Row : Next Key Share | | | Insert Into Sorted Shared Temp Table ID = t2 | | | | #Columns = 2 | | | | #Sort Key Columns = 1 | | | | | Key 1: DEPTNO (Ascending) | | | | Use Replicated Sort | | | | Sortheap Allocation Parameters: | | | | | #Rows = 38 | | | | | Row Width = 28 | | | | Piped | | Sorted Shared Temp Table Completion ID = t2 | | Access Temp Table ID = t2 | | | #Columns = 2 | | | Relation Scan | | | | Prefetch: Eligible | Insert Into Sorted Shared Temp Table ID = t3 | | #Columns = 5 | | #Sort Key Columns = 1 | | | Key 1: (Ascending) | | Use Partitioned Sort | | Sortheap Allocation Parameters: | | | #Rows = 61 | | | Row Width = 72 | | Piped | Access Temp Table ID = t3 | | #Columns = 5 | | Relation Scan | | | Prefetch: Eligible | Merge Join | | Access Table Name = DOOLE.EMPLOYEE ID = 2,5 | | | #Columns = 3 | | | Parallel Scan | | | Relation Scan | | | | Prefetch: Eligible | | | Lock Intents | | | | Table: Intent Share | | | | Row : Next Key Share | | | Insert Into Sorted Shared Temp Table ID = t4 | | | | #Columns = 3 | | | | #Sort Key Columns = 1 | | | | | Key 1: WORKDEPT (Ascending) | | | | Use Partitioned Sort | | | | Sortheap Allocation Parameters: | | | | | #Rows = 63 | | | | | Row Width = 32 | | | | Piped | | Sorted Shared Temp Table Completion ID = t4 | | Access Temp Table ID = t4 | | | #Columns = 3 | | | Relation Scan | | | | Prefetch: Eligible | Insert Into Asynchronous Local Table Queue ID = q1 Access Local Table Queue ID = q1 #Columns = 5 Return Data to Application | #Columns = 5 End of section Optimizer Plan: RETURN ( 1) | LTQ ( 2) | MSJOIN ( 3) / \ TBSCAN TBSCAN ( 4) ( 15) | | SORT SORT ( 5) ( 16) | | MSJOIN TBSCAN ( 6) ( 17) / \ | TBSCAN TBSCAN Table: ( 7) ( 11) DOOLE | | EMPLOYEE SORT SORT ( 8) ( 12) | | TBSCAN TBSCAN ( 9) ( 13) | | Table: Table: DOOLE DOOLE DEPARTMENT PROJECT
Dieser Plan stimmt mit dem Plan des ersten Beispiels weitgehend überein. Der Unterschied besteht in der Erstellung von vier Subagenten beim ersten Starten des Plans und der Tabellenwarteschlange am Ende des Plans, um die Ergebnisse der Arbeit aller Subagenten aufzunehmen, bevor sie an die Anwendung zurückgegeben werden.
Außerdem ist die Beobachtung interessant, daß vor der Verknüpfung mit EMPLOYEE eine weitere Sortierung erforderlich ist. Dies ist nötig, weil die Subagenten, die die Mischverknüpfung zwischen DEPARTMENT und PROJECT verarbeiten, verknüpfte Zeilen in falscher Reihenfolge hervorbringen können.
Dieses Beispiel zeigt wiederum dieselbe SQL-Anweisung wie Beispiel 1: Plan ohne Parallelität, aber hier wurde die Abfrage auf einer partitionierten Datenbank kompiliert, die aus drei Datenbankpartitionen besteht.
******************** PACKAGE *************************************** Package Name = DOOLE.DYNEXPLN Prep Date = 2000/01/03 Prep Time = 15:21:29 Bind Timestamp = 2000-01-03-15.21.29.990983 Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = Yes Intra-Partition Parallel = No Function Path = "SYSIBM", "SYSFUN", "DOOLE" -------------------- SECTION --------------------------------------- Section = 1 SQL Statement: SELECT x.lastname, x.job, y.deptname, y.location, z.projname FROM employee AS x, department AS y, project AS z WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno = z.deptno Estimated Cost = 118 Estimated Cardinality = 263 Coordinator Subsection: Distribute Subsection #2 | Broadcast to Node List | | Nodes = 13, 82, 193 Distribute Subsection #3 | Broadcast to Node List | | Nodes = 13, 82, 193 Distribute Subsection #1 | Broadcast to Node List | | Nodes = 13, 82, 193 Access Table Queue ID = q1 #Columns = 5 Return Data to Application | #Columns = 5 Subsection #1: Access Table Queue ID = q2 #Columns = 3 | Output Sorted | | #Key Columns = 1 | | | Key 1: (Ascending) Merge Join | Access Table Name = DOOLE.DEPARTMENT ID = 2,4 | | #Columns = 3 | | Relation Scan | | | Prefetch: Eligible | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | | Insert Into Sorted Temp Table ID = t1 | | | #Columns = 3 | | | #Sort Key Columns = 1 | | | | Key 1: DEPTNO (Ascending) | | | Sortheap Allocation Parameters: | | | | #Rows = 40 | | | | Row Width = 48 | | | Piped | Sorted Temp Table Completion ID = t1 | Access Temp Table ID = t1 | | #Columns = 3 | | Relation Scan | | | Prefetch: Eligible Merge Join | Access Table Queue ID = q3 #Columns = 2 | | Output Sorted | | | #Key Columns = 1 | | | | Key 1: (Ascending) Insert Into Asynchronous Table Queue ID = q1 | Broadcast to Coordinator Node | Rows Can Overflow to Temporary Table Subsection #2: Access Table Name = DOOLE.EMPLOYEE ID = 2,5 | #Columns = 3 | Relation Scan | | Prefetch: Eligible | Lock Intents | | Table: Intent Share | | Row : Next Key Share | Insert Into Sorted Temp Table ID = t2 | | #Columns = 3 | | #Sort Key Columns = 1 | | | Key 1: WORKDEPT (Ascending) | | Sortheap Allocation Parameters: | | | #Rows = 27 | | | Row Width = 32 | | Piped Sorted Temp Table Completion ID = t2 Access Temp Table ID = t2 | #Columns = 3 | Relation Scan | | Prefetch: Eligible | Insert Into Asynchronous Table Queue ID = q2 | | Hash to Specific Node | | Rows Can Overflow to Temporary Tables Insert Into Asynchronous Table Queue Completion ID = q2 Subsection #3: Access Table Name = DOOLE.PROJECT ID = 2,7 | #Columns = 2 | Relation Scan | | Prefetch: Eligible | Lock Intents | | Table: Intent Share | | Row : Next Key Share | Insert Into Sorted Temp Table ID = t3 | | #Columns = 2 | | #Sort Key Columns = 1 | | | Key 1: DEPTNO (Ascending) | | Sortheap Allocation Parameters: | | | #Rows = 38 | | | Row Width = 28 | | Piped Sorted Temp Table Completion ID = t3 Access Temp Table ID = t3 | #Columns = 2 | Relation Scan | | Prefetch: Eligible | Insert Into Asynchronous Table Queue ID = q3 | | Hash to Specific Node | | Rows Can Overflow to Temporary Tables Insert Into Asynchronous Table Queue Completion ID = q3 End of section Optimizer Plan: RETURN ( 1) | BTQ ( 2) | MSJOIN ( 3) / \ MSJOIN MDTQ ( 4) ( 14) / \ | MDTQ TBSCAN TBSCAN ( 5) ( 10) ( 15) | | | TBSCAN SORT SORT ( 6) ( 11) ( 16) | | | SORT TBSCAN TBSCAN ( 7) ( 12) ( 17) | | | TBSCAN Table: Table: ( 8) DOOLE DOOLE | DEPARTMENT PROJECT Table: DOOLE EMPLOYEE
Dieser Plan enthält dieselben Bestandteile wie der Plan im ersten Beispiel, aber der Bereich wurde in vier Teilbereiche (Subsection) unterteilt. Die Teilbereiche haben folgende Aufgaben:
In diesem Beispiel wird dieselbe SQL-Anweisung wie in Beispiel 1: Plan ohne Parallelität gezeigt, aber die Abfrage wurde auf einer partitionierten Datenbank mit drei Datenbankpartitionen kompiliert, die jeweils auf einer 4-Wege-SMP-Maschine sind.
******************** PACKAGE *************************************** Package Name = DOOLE.DYNEXPLN Prep Date = 2000/01/03 Prep Time = 15:22:14 Bind Timestamp = 2000-01-03-15.22.14.659970 Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = Yes Intra-Partition Parallel = Yes (Bind Degree = 4) Function Path = "SYSIBM", "SYSFUN", "DOOLE" -------------------- SECTION --------------------------------------- Section = 1 SQL Statement: SELECT x.lastname, x.job, y.deptname, y.location, z.projname FROM employee AS x, department AS y, project AS z WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno = z.deptno Intra-Partition Parallelism Degree = 4 Estimated Cost = 140 Estimated Cardinality = 263 Coordinator Subsection: Distribute Subsection #2 | Broadcast to Node List | | Nodes = 13, 82, 193 Distribute Subsection #3 | Broadcast to Node List | | Nodes = 13, 82, 193 Distribute Subsection #1 | Broadcast to Node List | | Nodes = 13, 82, 193 Access Table Queue ID = q1 #Columns = 5 Return Data to Application | #Columns = 5 Subsection #1: Process Using 4 Subagents | Access Table Queue ID = q3 #Columns = 3 | Insert Into Sorted Shared Temp Table ID = t1 | | #Columns = 3 | | #Sort Key Columns = 1 | | | Key 1: (Ascending) | | Use Partitioned Sort | | Sortheap Allocation Parameters: | | | #Rows = 27 | | | Row Width = 32 | | Piped | Access Temp Table ID = t1 | | #Columns = 3 | | Relation Scan | | | Prefetch: Eligible | Merge Join | | Access Table Name = DOOLE.DEPARTMENT ID = 2,4 | | | #Columns = 3 | | | Parallel Scan | | | Relation Scan | | | | Prefetch: Eligible | | | Lock Intents | | | | Table: Intent Share | | | | Row : Next Key Share | | | Insert Into Sorted Shared Temp Table ID = t2 | | | | #Columns = 3 | | | | #Sort Key Columns = 1 | | | | | Key 1: DEPTNO (Ascending) | | | | Use Partitioned Sort | | | | Sortheap Allocation Parameters: | | | | | #Rows = 40 | | | | | Row Width = 48 | | | | Piped | | Sorted Shared Temp Table Completion ID = t2 | | Access Temp Table ID = t2 | | | #Columns = 3 | | | Relation Scan | | | | Prefetch: Eligible | Insert Into Sorted Shared Temp Table ID = t3 | | #Columns = 6 | | #Sort Key Columns = 1 | | | Key 1: (Ascending) | | Use Partitioned Sort | | Sortheap Allocation Parameters: | | | #Rows = 44 | | | Row Width = 76 | | Piped | Access Temp Table ID = t3 | | #Columns = 6 | | Relation Scan | | | Prefetch: Eligible | Merge Join | | Access Table Queue ID = q5 #Columns = 2 | | Insert Into Sorted Shared Temp Table ID = t4 | | | #Columns = 2 | | | #Sort Key Columns = 1 | | | | Key 1: (Ascending) | | | Use Partitioned Sort | | | Sortheap Allocation Parameters: | | | | #Rows = 38 | | | | Row Width = 28 | | | Piped | | Access Temp Table ID = t4 | | | #Columns = 2 | | | Relation Scan | | | | Prefetch: Eligible | Insert Into Asynchronous Local Table Queue ID = q2 Access Local Table Queue ID = q2 #Columns = 5 Insert Into Asynchronous Table Queue ID = q1 | Broadcast to Coordinator Node | Rows Can Overflow to Temporary Table Subsection #2: Process Using 4 Subagents | Access Table Name = DOOLE.EMPLOYEE ID = 2,5 | | #Columns = 3 | | Parallel Scan | | Relation Scan | | | Prefetch: Eligible | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | | Insert Into Sorted Shared Temp Table ID = t5 | | | #Columns = 3 | | | #Sort Key Columns = 1 | | | | Key 1: WORKDEPT (Ascending) | | | Use Round-Robin Sort | | | Sortheap Allocation Parameters: | | | | #Rows = 27 | | | | Row Width = 32 | | | Piped | Sorted Shared Temp Table Completion ID = t5 | Access Temp Table ID = t5 | | #Columns = 3 | | Relation Scan | | | Prefetch: Eligible | Insert Into Asynchronous Local Table Queue ID = q4 Access Local Table Queue ID = q4 #Columns = 3 Insert Into Asynchronous Table Queue ID = q3 | Hash to Specific Node | Rows Can Overflow to Temporary Tables Subsection #3: Process Using 4 Subagents | Access Table Name = DOOLE.PROJECT ID = 2,7 | | #Columns = 2 | | Parallel Scan | | Relation Scan | | | Prefetch: Eligible | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | | Insert Into Sorted Shared Temp Table ID = t6 | | | #Columns = 2 | | | #Sort Key Columns = 1 | | | | Key 1: DEPTNO (Ascending) | | | Use Round-Robin Sort | | | Sortheap Allocation Parameters: | | | | #Rows = 38 | | | | Row Width = 28 | | | Piped | Sorted Shared Temp Table Completion ID = t6 | Access Temp Table ID = t6 | | #Columns = 2 | | Relation Scan | | | Prefetch: Eligible | Insert Into Asynchronous Local Table Queue ID = q6 Access Local Table Queue ID = q6 #Columns = 2 Insert Into Asynchronous Table Queue ID = q5 | Hash to Specific Node | Rows Can Overflow to Temporary Tables End of section Optimizer Plan: RETURN ( 1) | BTQ ( 2) | LTQ ( 3) | MSJOIN ( 4) / \ TBSCAN TBSCAN ( 5) ( 20) | | SORT SORT ( 6) ( 21) | | MSJOIN DTQ ( 7) ( 22) / \ | TBSCAN TBSCAN LTQ ( 8) ( 16) ( 23) | | | SORT SORT TBSCAN ( 9) ( 17) ( 24) | | | DTQ TBSCAN SORT ( 10) ( 18) ( 25) | | | LTQ Table: TBSCAN ( 11) DOOLE ( 26) | DEPARTMENT | TBSCAN Table: ( 12) DOOLE | PROJECT SORT ( 13) | TBSCAN ( 14) | Table: DOOLE EMPLOYEE
Dieser Plan ist ähnlich wie der in Beispiel 3: Zugriffsplan für eine Datenbank mit mehreren Partitionen und partitionsübergreifender Parallelität, nur daß mehrere Subagenten jeden Teilbereich ausführen. Außerdem sammelt am Ende eines jeden Teilbereichs eine lokale Tabelle die Ergebnisse von allen Subagenten, bevor die entsprechenden Zeilen in die zweite Tabellenwarteschlange eingefügt werden, die per Hash-Verfahren an einen bestimmten Knoten gesendet wird.
In diesem Beispiel wird dieselbe SQL-Anweisung wie in Beispiel 1: Plan ohne Parallelität gezeigt. Die Abfrage wurde jedoch auf einer zusammengeschlossenen Datenbank kompiliert, bei der sich die Tabellen DEPARTMENT und PROJECT auf einer Datenquelle befinden und die Tabelle EMPLOYEE auf dem Server mit zusammengeschlossenen Datenbanken ist.
******************** PACKAGE *************************************** Package Name = DOOLE.DYNEXPLN Prep Date = 2000/01/03 Prep Time = 16:29:01 Bind Timestamp = 2000-01-03-16.29.01.479230 Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = No Intra-Partition Parallel = No Function Path = "SYSIBM", "SYSFUN", "DOOLE" -------------------- SECTION --------------------------------------- Section = 1 SQL Statement: SELECT x.lastname, x.job, y.deptname, y.location, z.projname FROM employee AS x, department AS y, project AS z WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno = z.deptno Estimated Cost = 1954 Estimated Cardinality = 100800 Distribute Subquery #2 | #Columns = 3 Insert Into Sorted Shared Temp Table ID = t1 | #Columns = 3 | #Sort Key Columns = 1 | | Key 1: Remote Query #2, Output Column 1 (Ascending) | Sortheap Allocation Parameters: | | #Rows = 1000 | | Row Width = 56 | Piped Access Temp Table ID = t1 | #Columns = 3 | Relation Scan | | Prefetch: Eligible Merge Join | Access Table Name = DOOLE.DEPARTMENT ID = 2,5 | #Columns = 3 | Relation Scan | | Prefetch: Eligible | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | | Insert Into Sorted Temp Table ID = t2 | | | #Columns = 3 | | | #Sort Key Columns = 1 | | | | Key 1: WORKDEPT (Ascending) | | | Sortheap Allocation Parameters: | | | | #Rows = 63 | | | | Row Width = 32 | | | Piped | Sorted Temp Table Completion ID = t2 | Access Temp Table ID = t2 | | #Columns = 3 | | Relation Scan | | | Prefetch: Eligible Merge Join | Distribute Subquery #1 | | #Columns = 2 | Insert Into Sorted Temp Table ID = t3 | | #Columns = 2 | | | Key 1: Remote Query #1, Output Column 1 (Ascending) | | Sortheap Allocation Parameters: | | | #Rows = 1000 | | | Row Width = 36 | | Piped | Access Temp Table ID = t3 | | #Columns = 2 | | Relation Scan | | | Prefetch: Eligible Return Data to Application | #Columns = 5 Distributed Subquery #1: Server: REMOTE_SAMPLE (DB2/CS 7.1) Subquery SQL Statement: SELECT A0."DEPTNO", A0."PROJNAME" FROM "DOOLE"."PROJECT" A0 Nicknames Referenced: REMOTE.PROJECT ID = 7 Base = DOOLE.PROJECT #Output Columns = 2 Distributed Subquery #2: Server: REMOTE_SAMPLE (DB2/CS 7.1) Subquery SQL Statement: SELECT A0."DEPTNO", A0."DEPTNAME", A0."LOCATION" FROM "DOOLE"."DEPARTMENT" A0 Nicknames Referenced: REMOTE.DEPARTMENT ID = 4 Base = DOOLE.DEPARTMENT #Output Columns = 3 End of section Optimizer Plan: RETURN ( 1) | MSJOIN ( 2) / \ MSJOIN TBSCAN ( 3) ( 13) / \ | TBSCAN TBSCAN SORT ( 4) ( 9) ( 14) | | | SORT SORT DSBQRY ( 5) ( 10) ( 15) | | | DSBQRY TBSCAN Nickname: ( 6) ( 11) REMOTE | | PROJECT Nickname: Table: REMOTE DOOLE DEPARTMENT EMPLOYEE
Dieser Plan enthält dieselben Bestandteile wie der Plan im ersten Beispiel. Ausgenommen hiervon sind die Daten für zwei der Tabellen, die von Datenquellen kommen. Auf diese zwei Tabellen wird über verteilte Unterabfragen zugegriffen, die in diesem Fall einfach alle Zeilen der betreffenden Tabellen auswählen. Wenn die Daten zum Server mit zusammengeschlossenen Datenbanken zurückgegeben werden, werden sie mit den Daten aus der lokalen Tabelle verknüpft.