1 / 102

“””Natural for DB2 Beyond The Find and Read

“””Natural for DB2 Beyond The Find and Read. Darrell Skildum Advisory Systems Engineer Software Ag. DB2 Programming Tools. DBLOG Traces all SQL calls Entry point to LISTSQL and EXPLAIN LISTSQL Lists SQL generated Entry point to EXPLAIN EXPLAIN DB2 estimate of cost of command

alastair
Download Presentation

“””Natural for DB2 Beyond The Find and Read

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. “””Natural for DB2 Beyond The Find and Read Darrell Skildum Advisory Systems Engineer Software Ag

  2. DB2 Programming Tools • DBLOG • Traces all SQL calls • Entry point to LISTSQL and EXPLAIN • LISTSQL • Lists SQL generated • Entry point to EXPLAIN • EXPLAIN • DB2 estimate of cost of command • Shows INDEX use/non use • Shows columns of index used

  3. ADABAS vs DB2 Purpose is not to debate merits of each Database, but to encourage the best use of the tools for each

  4. ADABAS vs DB2 • ADABAS is a Record Oriented Database • A FIND results in a set of ISNs which can be retrieved one at a time • A READ is a true browse function • DB2 is set oriented • A FIND translates to a SELECT which produces a subset of the records as a separate table which is then browsed. • A READ may result is SELECT GE which may produce a subset much larger than wanted

  5. Adabas vs DB2 programming • For the most part, the same syntax works for both ADABAS and DB2 • More thought should be applied because in some cases, the same syntax may be less efficient in DB2 • DB2 allows (requires) much more complicated syntax to effectively retrieve the selected information

  6. Creation of Tables • Employees Creator = SADRS Employees ADA EMPLOYEE_PAY EMPLOYEE_LEAVE EMPL_BASE EMPLOY_FR EMPLOY_SP EMPLOY_US EMPLOY_DE EMPLOY_UK

  7. Creation of Tables • Vehicles -Creator = SADRS Vehicles (ADA) VEHICLES

  8. Natural Read DEFINE DATA LOCAL 1 EMP VIEW OF SADRS-EMPL_BASE 2 PERSONNEL_ID 2 NAME 2 FIRST_NAME 2 DEPT END-DEFINE READ EMP WITH DEPT = 'COMP21' THRU 'COMP21' DISPLAY PERSONNEL_ID NAME FIRST_NAME END-READ END

  9. Output from Read Page 1 PERSONNEL_ID NAME FIRST_NAME ------------ -------------------- -------------------- 11300324 DAMMBACH FLORIAN 11400325 SPAEHT KLAUS 11700324 MOELLER MARION 11700323 LIST FRANZ 11300323 DAMM WOLFGANG 11300320 BRUNNER HELMUT 11100114 UNGER KLAUS 11100113 TREIBER KLAUS 11100105 SCHIRM CHRISTIAN

  10. List SQL from READ Member NATREAD - LISTSQL - Library NDBDEMO NATURAL statement at line 0080 Stmt 1 / 1 READ EMP WITH DEPT = 'COMP21' THRU 'COMP21' Generated SQL statement Mode : dynamic DBRM : Line 1 / 5 SELECT PERSONNEL_ID, NAME, FIRST_NAME, DEPT FROM SADRS.EMPL_BASE WHERE DEPT >= 'COMP21' ORDER BY DEPT FOR FETCH ONLY

  11. DBLOG from READ User SADRS - DBLOG Trace - Library NDBDEMO M No R SQL Statement (truncated) CU SN SREF M Typ SQLC/W Program Line LV _ 1 ROLLBACK 00 00 0000 D DB2 6660 00 _ 2 SELECT PERSONNEL_ID,NAME,FIRS 01 01 0080 D DB2 NATREAD 0080 01 _ 3 FETCH CURSOR NEX 01 01 0080 D DB2 NATREAD 0080 01 _ 4 FETCH CURSOR NEX 01 01 0080 D DB2 NATREAD 0080 01 _ 5 FETCH CURSOR NEX 01 01 0080 D DB2 NATREAD 0080 01 _ 6 FETCH CURSOR NEX 01 01 0080 D DB2 NATREAD 0080 01 _ 7 FETCH CURSOR NEX 01 01 0080 D DB2 NATREAD 0080 01 _ 8 FETCH CURSOR NEX 01 01 0080 D DB2 NATREAD 0080 01 _ 9 FETCH CURSOR NEX 01 01 0080 D DB2 NATREAD 0080 01 _ 10 FETCH CURSOR NEX 01 01 0080 D DB2 NATREAD 0080 01 _ 11 FETCH CURSOR NEX 01 01 0080 D DB2 NATREAD 0080 01 _ 12 FETCH CURSOR NEX 01 01 0080 D DB2 NATREAD 0080 01 _ 13 CLOSE CURSOR 01 01 0080 D DB2 NATREAD 0080 01

  12. Explain from Read Estimated cost : 428.5 timerons Qblockno Mixop Access Match Index Pre- Access- Column- Planno seq type cols only fetch creator name fn_eval --- --- --- ---- ----- ----- ----- -------- ------------------ - 1 1 R S 1 2 Table Tslock -- sortn -- -- sortc -- Tabno Creator Tname mode Method uq jo or gr uq jo or gr --- -------- ------------------ ------ ------ -- -- -- -- -- -- -- -- 1 SADRS EMPL_BASE IS N N N N N N N N 3 N N N N N N Y N

  13. Read READ

  14. Read Natural V4 DEFINE DATA LOCAL 1 EMP VIEW OF SADRS-EMPL_BASE 2 PERSONNEL_ID 2 NAME 2 FIRST_NAME 2 DEPT END-DEFINE READ EMP WITH DEPT = 'COMP21' TO 'COMP21' DISPLAY PERSONNEL_ID NAME FIRST_NAME END-READ END

  15. ListSQL V4 Read Member NATREAD2 - LISTSQL - Library NDBDEMO NATURAL statement at line 0080 Stmt 1 / 1 READ EMP WITH DEPT = 'COMP21' TO 'COMP21' Generated SQL statement Mode : dynamic DBRM : Line 1 / 5 SELECT PERSONNEL_ID, NAME, FIRST_NAME, DEPT FROM SADRS.EMPL_BASE WHERE DEPT BETWEEN 'COMP21' AND 'COMP21' ORDER BY DEPT FOR FETCH ONLY

  16. Explain for V4 Read Estimated cost : 16.5 timerons Qblockno Mixop Access Match Index Pre- Access- Column- Planno seq type cols only fetch creator name fn_eval --- --- --- ---- ----- ----- ----- -------- ------------------ - 1 1 I 1 L SADRS DEPT_NAME 1 2 Table Tslock -- sortn -- -- sortc -- Tabno Creator Tname mode Method uq jo or gr uq jo or gr --- -------- ------------------ ------ ------ -- -- -- -- -- -- -- -- 1 SADRS EMPL_BASE IS N N N N N N N N 3 N N N N N N Y N 16.5 timerons vs 428.5

  17. Coding SQL SPUFI - ISQL ***** ********************** 00001 SELECT * 00002 FROM SADRS.EMPL_BASE 00003 WHERE DEPT = 'COMP02' ***** **********************

  18. Coding SQL SPUFI - ISQL ----------------------------------------------------------------- PERSONNEL_ID FIRST_NAME MIDDLE_NAME NAME >---------------------------------------------------------------- 50003700 LOUIS D'AGOSTINO 50016600 ANNIE GODEFROY 50019000 ROGER BESSON 50020600 DANIEL GREGOIRE 50006700 SERGE RIGOLLET 60000231 ANTONIO VILLAR 60000509 VICTORIA RODRIGUEZ 60008045 GORKA NIEDA 60008231 JORDI MONTERREY ESPLA 60008745 ANTONIO PUERTAS 60008509 MARIA JOSE PUERTOLAS

  19. Coding SQL DEFINE DATA LOCAL 1 EMP VIEW OF SADRS-EMPL_BASE 2 PERSONNEL_ID 2 NAME 2 FIRST_NAME 2 DEPT END-DEFINE SELECT * INTO VIEW EMP FROM SADRS-EMPL_BASE WHERE DEPT = 'COMP02' DISPLAY PERSONNEL_ID NAME FIRST_NAME END-SELECT END

  20. Coding SQL DEFINE DATA LOCAL 1 EMP VIEW OF SADRS-EMPL_BASE 2 PERSONNEL_ID 2 NAME 2 FIRST_NAME 2 DEPT END-DEFINE INTO VIEW EMP FROM SADRS-EMPL_BASE WHERE DEPT = 'COMP02' DISPLAY PERSONNEL_ID NAME FIRST_NAME END-SELECT END SELECT PERSONNEL_ID, NAME, FIRST_NAME, DEPT

  21. LIKE DEFINE DATA LOCAL 1 EMP VIEW OF SADRS-EMPL_BASE 2 PERSONNEL_ID 2 NAME 2 FIRST_NAME 2 DEPT END-DEFINE SELECT PERSONNEL_ID, NAME, FIRST_NAME, DEPT INTO EMP.PERSONNEL_ID, EMP.NAME, EMP.FIRST_NAME, EMP.DEPT FROM SADRS-EMPL_BASE WHERE DEPT LIKE 'COMP0%' DISPLAY EMP.PERSONNEL_ID EMP.NAME EMP.FIRST_NAME EMP.DEPT END-SELECT END

  22. LIKE RESULTS PERSONNEL_ID NAME FIRST_NAME DEPT ------------ -------------------- -------------------- ------ 50003700 D'AGOSTINO LOUIS COMP02 50003100 HEURTEBISE MICHEL COMP01 50001700 TANCHOU GERARD COMP01 50001200 SIECA FRANCOIS COMP01 50000300 FISCHER RENE COMP03 50014000 LEFEBVRE CLAUDE COMP01 50015800 CATILLON MARCEL COMP03 50016000 ROLLET GEORGES COMP01 50016600 GODEFROY ANNIE COMP02 50019000 BESSON ROGER COMP02 50019500 YOT RENE COMP01 50020600 GREGOIRE DANIEL COMP02 50000100 GASET JEAN COMP01 50000500 RIVIERE JEAN-LUC COMP05 50006800 THIEBAULT ROBERT COMP03 50006700 RIGOLLET SERGE COMP02 50006600 TEYSSIER MICHEL COMP01 60000231 VILLAR ANTONIO COMP02

  23. IN SELECT PERSONNEL_ID, NAME, FIRST_NAME, DEPT INTO EMP.PERSONNEL_ID, EMP.NAME, EMP.FIRST_NAME, EMP.DEPT FROM SADRS-EMPL_BASE WHERE DEPT IN ('COMP02', 'COMP03', 'COMP21') DISPLAY EMP.PERSONNEL_ID EMP.NAME EMP.FIRST_NAME EMP.DEPT END-SELECT

  24. IN - Results PERSONNEL_ID NAME FIRST_NAME DEPT ------------ -------------------- -------------------- ------ 50003700 D'AGOSTINO LOUIS COMP02 50000300 FISCHER RENE COMP03 50015800 CATILLON MARCEL COMP03 50016600 GODEFROY ANNIE COMP02 50019000 BESSON ROGER COMP02 50020600 GREGOIRE DANIEL COMP02 50006800 THIEBAULT ROBERT COMP03 50006700 RIGOLLET SERGE COMP02 11100105 SCHIRM CHRISTIAN COMP21 11100113 TREIBER KLAUS COMP21 11100114 UNGER KLAUS COMP21 11300320 BRUNNER HELMUT COMP21 11300323 DAMM WOLFGANG COMP21 11700323 LIST FRANZ COMP21 11700324 MOELLER MARION COMP21

  25. BETWEEN DEFINE DATA LOCAL 1 EMP VIEW OF SADRS-EMPL_BASE 2 PERSONNEL_ID 2 NAME 2 FIRST_NAME 2 DEPT END-DEFINE SELECT PERSONNEL_ID, NAME, FIRST_NAME, DEPT INTO EMP.PERSONNEL_ID, EMP.NAME, EMP.FIRST_NAME, EMP.DEPT FROM SADRS-EMPL_BASE WHERE DEPT BETWEEN 'COMP02' AND 'COMP05' DISPLAY EMP.PERSONNEL_ID EMP.NAME EMP.FIRST_NAME EMP.DEPT END-SELECT

  26. BETWEEN - RESULTS PERSONNEL_ID NAME FIRST_NAME DEPT ------------ -------------------- -------------------- ------ 50003700 D'AGOSTINO LOUIS COMP02 50000300 FISCHER RENE COMP03 50015800 CATILLON MARCEL COMP03 50016600 GODEFROY ANNIE COMP02 50019000 BESSON ROGER COMP02 50020600 GREGOIRE DANIEL COMP02 50000500 RIVIERE JEAN-LUC COMP05 50006800 THIEBAULT ROBERT COMP03 50006700 RIGOLLET SERGE COMP02 60000231 VILLAR ANTONIO COMP02 60000509 RODRIGUEZ VICTORIA COMP02 60008045 NIEDA GORKA COMP02 60008100 PELEGRIN MANUEL COMP03 60008231 ESPLA JORDI COMP02 60008745 PUERTAS ANTONIO COMP02 60008168 MARTINEZ ANTONIO COMP03 60008287 PEREZ ANTONIO COMP03 60008509 PUERTOLAS MARIA JOSE COMP02

  27. Nested Select DEFINE DATA LOCAL 1 EMP VIEW OF SADRS-EMPL_BASE 2 PERSONNEL_ID 2 NAME 2 FIRST_NAME 2 DEPT 1 VEH VIEW OF SADRS-VEHICLES 2 PERSONNEL_ID 2 MAKE 2 MODEL 2 REG_NUM END-DEFINE SELECT * INTO VIEW EMP FROM SADRS-EMPL_BASE WHERE DEPT = 'COMP21' SELECT * INTO VIEW VEH FROM SADRS-VEHICLES WHERE PERSONNEL_ID = EMP.PERSONNEL_ID DISPLAY PERSONNEL_ID FIRST_NAME NAME MAKE END-SELECT

  28. Nested Select test dblog q ? > + Program SQLEX5 Lib NDBDEMO p ....+....1....+....2....+....3....+....4....+....5....+....6....+....7 0010 DEFINE DATA LOCAL 0020 1 EMP VIEW OF SADRS-EMPL_BASE 0030 2 PERSONNEL_ID 0040 2 NAME 0050 2 FIRST_NAME 0060 2 DEPT 0070 1 VEH VIEW OF SADRS-VEHICLES 0080 2 PERSONNEL_ID 0090 2 MAKE 0100 2 MODEL 0110 2 REG_NUM 0120 END-DEFINE 0130 SELECT * INTO VIEW EMP 0140 FROM SADRS-EMPL_BASE 0150 WHERE DEPT = 'COMP21' 0160 SELECT * INTO VIEW VEH 0170 FROM SADRS-VEHICLES 0180 WHERE PERSONNEL_ID = EMP.PERSONNEL_ID 0190 IF NO RECORDS FOUND 0200 MOVE 'NONE' TO VEH.MAKE

  29. Nested Select User SADRS - DBLOG Menu - Library NDBDEMO Code Function ---- ------------------------------------ B Begin Logging of SQL Statements E End and Display Log Records S Snapshot of Specific SQL Statement . Exit ---- ------------------------------------ Code .. B Statement .. select__ Skip ....... _____ Program .... ________ Line from .. 0000 Low SQLC .. ______ High SQLC .. ______ Line to .... 0000

  30. Nested Select NEXT test dblog q show LIB=NDBDEMO Page 1 05-04-06 01:47:57 PERSONNEL_ID FIRST_NAME NAME MAKE ------------ -------------------- -------------------- -------------------- 11100105 CHRISTIAN SCHIRM OPEL 11100113 KLAUS TREIBER AUDI 11100114 KLAUS UNGER BMW 11300320 HELMUT BRUNNER AUDI 11300323 WOLFGANG DAMM VW 11700323 FRANZ LIST VW 11700324 MARION MOELLER NONE 11400325 KLAUS SPAEHT AUDI 11300324 FLORIAN DAMMBACH VW

  31. Nested Select User SADRS - DBLOG Trace - Library NDBDEMO M No R SQL Statement (truncated) CU SN SREF M Typ SQLC/W Program Line LV _ 1 SELECT PERSONNEL_ID,NAME,FIRS 03 03 0130 D DB2 SQLEX5 0130 01 _ 2 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 3 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 4 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 5 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 6 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 7 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 8 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 9 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 10 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01

  32. Nested Select Member SQLEX5 - LISTSQL - Library NDBDEMO NATURAL statement at line 0130 Stmt 1 / 2 SELECT * INTO VIEW EMP FROM SADRS-EMPL_BASE WHERE DEPT = 'COMP21' Generated SQL statement Mode : dynamic DBRM : Line 1 / 4 SELECT PERSONNEL_ID, NAME, FIRST_NAME, DEPT FROM SADRS.EMPL_BASE WHERE DEPT = 'COMP21' FOR FETCH ONLY

  33. Nested Select Queryno 1 EXPLAIN Result Row 1 / 1 Estimated cost : 5.3 timerons Qblockno Mixop Access Match Index Pre- Access- Column- Planno seq type cols only fetch creator name fn_eval --- --- --- ---- ----- ----- ----- -------- ------------------ - 1 1 I 1 L SADRS DEPT_NAME Table Tslock -- sortn -- -- sortc -- Tabno Creator Tname mode Method uq jo or gr uq jo or gr --- -------- ------------------ ------ ------ -- -- -- -- -- -- -- -- 1 SADRS EMPL_BASE IS N N N N N N N N

  34. Nested Select Member SQLEX5 - LISTSQL - Library NDBDEMO NATURAL statement at line 0160 Stmt 2 / 2 SELECT * INTO VIEW VEH FROM SADRS-VEHICLES WHERE PERSONNEL_ID = EMP.PERSONNEL_ID Generated SQL statement Mode : dynamic DBRM : Line 1 / 4 SELECT PERSONNEL_ID, MAKE, MODEL, REG_NUM FROM SADRS.VEHICLES WHERE PERSONNEL_ID =? FOR FETCH ONLY

  35. Nested Select Queryno 1 EXPLAIN Result Row 1 / 1 Estimated cost : 3.7 timerons Qblockno Mixop Access Match Index Pre- Access- Column- Planno seq type cols only fetch creator name fn_eval --- --- --- ---- ----- ----- ----- -------- ------------------ - 1 1 I 1 VEHICLES PERSSNNEL_ID Table Tslock -- sortn -- -- sortc -- Tabno Creator Tname mode Method uq jo or gr uq jo or gr --- -------- ------------------ ------ ------ -- -- -- -- -- -- -- -- 1 SADRS VEHICLES IS N N N N N N N N

  36. Nested Select PERSONNEL_ID FIRST_NAME NAME MAKE ------------ -------------------- -------------------- -------------------- 11100105 CHRISTIAN SCHIRM OPEL 11100113 KLAUS TREIBER AUDI 11100114 KLAUS UNGER BMW 11300320 HELMUT BRUNNER AUDI 11300323 WOLFGANG DAMM VW 11700323 FRANZ LIST VW 11400325 KLAUS SPAEHT AUDI 11300324 FLORIAN DAMMBACH VW

  37. Nested Select User SADRS - DBLOG Trace - Library NDBDEMO M No R SQL Statement (truncated) CU SN SREF M Typ SQLC/W Program Line LV _ 1 SELECT PERSONNEL_ID,NAME,FIRS 03 03 0130 D DB2 SQLEX5 0130 01 _ 2 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 3 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 4 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 5 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 6 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 7 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 8 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 9 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 _ 10 SELECT PERSONNEL_ID,MAKE,MODE 04 04 0160 D DB2 SQLEX5 0160 01 Total cost = 1*5.3 + 9*3.7 = 38.6

  38. Normal Join DEFINE DATA LOCAL 1 EMP VIEW OF SADRS-EMPL_BASE 2 NAME 2 PERSONNEL_ID 2 DEPT 2 FIRST_NAME 1 VEH VIEW OF SADRS-VEHICLES 2 MAKE 2 MODEL 2 PERSONNEL_ID 2 REG_NUM END-DEFINE SELECT A.NAME, A.PERSONNEL_ID, A.DEPT, A.FIRST_NAME, B.MAKE, B.MODEL, B.PERSONNEL_ID, B.REG_NUM INTO VIEW EMP,VEH FROM SADRS-EMPL_BASE A , SADRS-VEHICLES B WHERE DEPT = 'COMP21' AND B.PERSONNEL_ID = A.PERSONNEL_ID ORDER BY A.NAME DISPLAY EMP.NAME EMP.PERSONNEL_ID VEH.MAKE DEPT Alias or Correlation Name

  39. Join Results PERSONNEL_ID FIRST_NAME NAME MAKE ------------ -------------------- -------------------- -------------------- 11100105 CHRISTIAN SCHIRM OPEL 11100113 KLAUS TREIBER AUDI 11100114 KLAUS UNGER BMW 11300320 HELMUT BRUNNER AUDI 11300323 WOLFGANG DAMM VW 11700323 FRANZ LIST VW 11400325 KLAUS SPAEHT AUDI 11300324 FLORIAN DAMMBACH VW

  40. Join ListSql Member SQLEX6 - LISTSQL - Library NDBDEMO NATURAL statement at line 0130 Stmt 1 / 1 SELECT A.NAME, A.PERSONNEL_ID, A.DEPT, A.FIRST_NAME, B.MAKE, B.MODEL, B.PERSONNEL_ID, B.REG_NUM INTO VIEW EMP,VEH Generated SQL statement Mode : dynamic DBRM : Line 1 / 6 SELECT A.NAME, A.PERSONNEL_ID, A.DEPT, A.FIRST_NAME, B.MAKE, B.MODEL, B .PERSONNEL_ID, B.REG_NUM FROM SADRS.EMPL_BASE A, SADRS.VEHICLES B WHERE DEPT = 'COMP21' AND B.PERSONNEL_ID = A.PERSONNEL_ID ORDER BY A.NAME FOR FETCH ONLY

  41. Join Explain Estimated cost : 26.5 timerons Qblockno Mixop Access Match Index Pre- Access- Column- Planno seq type cols only fetch creator name fn_eval --- --- --- ---- ----- ----- ----- -------- ------------------ - 1 1 I 1 L SADRS DEPT_NAME 1 2 I 1 VEHICLES PERSSNNEL_ID 1 3 Table Tslock -- sortn -- -- sortc -- Tabno Creator Tname mode Method uq jo or gr uq jo or gr --- -------- ------------------ ------ ------ -- -- -- -- -- -- -- -- 1 SADRS EMPL_BASE IS N N N N N N N N 2 SADRS VEHICLES IS 1 N N N N N N N N 3 N N N N N N Y N vs 38.6

  42. Join Explain Estimated cost : 26.5 timerons Qblockno Mixop Access Match Index Pre- Access- Column- Planno seq type cols only fetch creator name fn_eval --- --- --- ---- ----- ----- ----- -------- ------------------ - 1 1 I 1 L SADRS DEPT_NAME 1 2 I 1 VEHICLES PERSSNNEL_ID 1 3 Table Tslock -- sortn -- -- sortc -- Tabno Creator Tname mode Method uq jo or gr uq jo or gr --- -------- ------------------ ------ ------ -- -- -- -- -- -- -- -- 1 SADRS EMPL_BASE IS N N N N N N N N 2 SADRS VEHICLES IS 1 N N N N N N N N 3 N N N N N N Y N vs 38.6

  43. Nested Select – No Records SELECT * INTO VIEW EMP FROM SADRS-EMPL_BASE WHERE DEPT = 'COMP21' SELECT * INTO VIEW VEH FROM SADRS-VEHICLES WHERE PERSONNEL_ID = EMP.PERSONNEL_ID IF NO RECORDS FOUND MOVE 'NONE' TO VEH.MAKE END-NOREC DISPLAY EMP.PERSONNEL_ID FIRST_NAME NAME MAKE END-SELECT END-SELECT

  44. Nested Select – No Records PERSONNEL_ID FIRST_NAME NAME MAKE ------------ -------------------- -------------------- -------------------- 11100105 CHRISTIAN SCHIRM OPEL 11100113 KLAUS TREIBER AUDI 11100114 KLAUS UNGER BMW 11300320 HELMUT BRUNNER AUDI 11300323 WOLFGANG DAMM VW 11700323 FRANZ LIST VW 11700324 MARION MOELLER NONE 11400325 KLAUS SPAEHT AUDI 11300324 FLORIAN DAMMBACH VW

  45. Left Outer Join SELECT A.NAME, A.PERSONNEL_ID, A.DEPT, B.MAKE, B.MODEL, B.PERSONNEL_ID INTO VIEW EMP,VEH FROM (SELECT NAME,PERSONNEL_ID, DEPT FROM SADRS-EMPL_BASE WHERE DEPT = 'COMP21') AS A LEFT OUTER JOIN (SELECT MAKE, MODEL,PERSONNEL_ID FROM SADRS-VEHICLES) AS B ON B.PERSONNEL_ID = A.PERSONNEL_ID ORDER BY A.NAME IF VEH.MAKE = ' ' MOVE '***NONE***' TO VEH.MAKE END-IF DISPLAY EMP.NAME EMP.PERSONNEL_ID VEH.MAKE DEPT END-SELECT

  46. Left Outer Join Results NAME PERSONNEL_ID MAKE DEPT -------------------- ------------ -------------------- ------ BRUNNER 11300320 AUDI COMP21 DAMM 11300323 VW COMP21 DAMMBACH 11300324 VW COMP21 LIST 11700323 VW COMP21 MOELLER 11700324 ***NONE*** COMP21 SCHIRM 11100105 OPEL COMP21 SPAEHT 11400325 AUDI COMP21 TREIBER 11100113 AUDI COMP21 UNGER 11100114 BMW COMP21

  47. SubQuery DEFINE DATA LOCAL 1 EMP VIEW OF SADRS-EMPL_BASE 2 PERSONNEL_ID 2 NAME 2 FIRST_NAME 1 SAL VIEW OF SADRS-EMPLOYEE_PAY 2 PAY_SEQ 2 SALARY END-DEFINE SELECT A.PERSONNEL_ID, A.NAME, A.FIRST_NAME, B.PAY_SEQ, B.SALARY INTO EMP.PERSONNEL_ID, EMP.NAME, EMP.FIRST_NAME, SAL.PAY_SEQ, SAL.SALARY FROM SADRS-EMPL_BASE A, SADRS-EMPLOYEE_PAY B WHERE A.PERSONNEL_ID = '11100114' AND B.PERSONNEL_ID = A.PERSONNEL_ID AND PAY_SEQ = (SELECT MAX(PAY_SEQ) FROM SADRS-EMPLOYEE_PAY WHERE PERSONNEL_ID = A.PERSONNEL_ID) DISPLAY NAME SALARY PAY_SEQ END-SELECT

  48. SubQuery Results Page 1 NAME SALARY PAY_SEQ -------------------- ---------- ----------- UNGER 46000 4

  49. SubQuery and calculated field SELECT A.PERSONNEL_ID, A.NAME, A.FIRST_NAME, B.PAY_SEQ, B.SALARY + B.BONUS1 + B.BONUS2 + B.BONUS3 AS TOTAL_PAY INTO EMP.PERSONNEL_ID, EMP.NAME, EMP.FIRST_NAME, SAL.PAY_SEQ, #TOTAL-PAY FROM SADRS-EMPL_BASE A, SADRS-EMPLOYEE_PAY B WHERE A.PERSONNEL_ID = '11100112' AND B.PERSONNEL_ID = A.PERSONNEL_ID AND PAY_SEQ = (SELECT MAX(PAY_SEQ) FROM SADRS-EMPLOYEE_PAY WHERE PERSONNEL_ID = A.PERSONNEL_ID) DISPLAY NAME #TOTAL-PAY PAY_SEQ END-SELECT

  50. SubQuery and calculated field Page 1 NAME #TOTAL-PAY PAY_SEQ -------------------- ----------- ----------- THOMA 46000.00 4

More Related