1
Natural for DB2 Beyond The Find and Read

• Darrell Skildum
• Advisory Systems Engineer
• Software Ag

2
DB2 Programming Tools

1. DBLOG
2. Traces all SQL calls
3. Entry point to LISTSQL and EXPLAIN
4. LISTSQL
5. Lists SQL generated
6. Entry point to EXPLAIN
7. EXPLAIN
8. DB2 estimate of cost of command
9. Shows INDEX use/non use
10. 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 gt '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 gt-----------------
----------------------------------------------- 50
003700 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

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

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 ? gt
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
LIBNDBDEMO Page 1
05-04-06
014757

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 15.3 93.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
  

51
USING DB2 functions
DEFINE DATA PARAMETER
01 SSN (A9) 01
TODAY (A10) 01 BEGIN-DATEA
(A10) 01 BEGIN-ANNUAL (P4.2) 01
EARNED-ANNUAL (P4.2) 01 TAKEN-ANNUAL
(P4.2) 01 ADJUST-ANNUAL (P4.2) 01
CURR-ANNUAL (P4.2) 01 PENDING-ANNUAL
(P4.2) 01 AVAIL-ANNUAL (P4.2) 01
BEGIN-DATES (A10)
52
USING DB2 functions
SELECT SUM(ALVB50D_NOHOUR) INTO
EARNED-ANNUAL FROM
PALV-ALVB50D WHERE
ALVB50D_NOSSN SSN AND
ALVB50D_CDTYLV 'AN' AND
ALVB50D_CDTYTR 'E' AND
ALVB50D_DCLVE gt BEGIN-DATEA GROUP BY
ALVB50D_NOSSN END-SELECT
53
Union

• DEFINE DATA LOCAL
  
• 1 EMP VIEW OF SADRS-EMPL_BASE
  
• 2 PERSONNEL_ID
  
• 2 NAME
  
• 2 FIRST_NAME
  
• 2 DEPT
  
• 2 COUNTRY
  
• END-DEFINE
  
• SELECT
  
• INTO VIEW EMP
  
• FROM SADRS-EMPLOY_DE
  
• WHERE DEPT LIKE 'COMP'
  
• UNION (SELECT FROM SADRS-EMPLOY_UK
  
• WHERE DEPT LIKE 'COMP')
  
• ORDER BY NAME
  
• DISPLAY PERSONNEL_ID FIRST_NAME NAME DEPT
  COUNTRY
• END-SELECT
  
• END

54
Union Results

• PERSONNEL_ID FIRST_NAME NAME
  DEPT COUNTRY
• ------------ --------------------
  -------------------- ------ -------
• 
  
• 30000231 ROBERT ACHIESON
  COMP02 UK
• 30021544 ELIZABETH AKROYD
  COMP03 UK
• 30034231 ROBIN ARCHER
  COMP02 UK
• 11500327 HANS BACHMANN
  COMP35 D
• 30008042 PAULINE BAKER
  COMP12 UK
• 30000287 JACK BIRKENSHAW
  COMP12 UK
• 30034311 IAN BRANGWIN
  COMP01 UK
• 30008509 CAROLINE BROOKS
  COMP02 UK
• 11300320 HELMUT BRUNNER
  COMP21 D
• 11300321 MANFRED BUCHERT
  COMP25 D
• 30021630 ANDREW BULLOCK
  COMP03 UK
• 11100110 GEORG BUNGERT
  COMP25 D
• 11300322 FRANZ BURKNER
  COMP25 D
• 30034107 COLIN CARROLL
  COMP01 UK
• 30016045 JULIAN CHESTER
  COMP02 UK
• 30034509 IAN COLQHOUN
  COMP12 UK

55
Updating

• 1 EMP VIEW OF I038-EMPLOYEES
  
• 2 NAME
  
• 2 PERSONNEL_ID
  
• 2 SALARY_1
  
• 1 EMP2 VIEW OF I038-EMPLOYEES
  
• 2 NAME
  
• 2 PERSONNEL_ID
  
• 2 SALARY_1
  
• 1 PID (A8)
  
• END-DEFINE
  
• INPUT PID
  
• SELECT INTO VIEW EMP FROM I038-EMPLOYEES
  
• WHERE PERSONNEL_ID PID
  
• END-SELECT
  
• INPUT (ADM) EMP.PERSONNEL_ID EMP.NAME
  EMP.SALARY_1
• SELECT INTO VIEW EMP2 FROM I038-EMPLOYEES
  
• WHERE PERSONNEL_ID PID
  
• MOVE BY NAME EMP TO EMP2
  
• UPDATE
  

56
Updating

• 1 EMP VIEW OF I038-EMPLOYEES
  
• 2 NAME
  
• 2 PERSONNEL_ID
  
• 2 SALARY_1
  
• 1 EMP2 VIEW OF I038-EMPLOYEES
  
• 2 NAME
  
• 2 PERSONNEL_ID
  
• 2 SALARY_1
  
• 1 PID (A8)
  
• END-DEFINE
  
• REPEAT
  
• INPUT PID
  
• SELECT INTO VIEW EMP FROM I038-EMPLOYEES
  
• WHERE PERSONNEL_ID PID
  
• END-SELECT
  
• INPUT (ADM) EMP.PERSONNEL_ID EMP.NAME
  EMP.SALARY_1
• UPDATE EMP SET
  
• WHERE PERSONNEL_ID PID
  
• END-REPEAT

57
Updating 2

• DEFINE DATA LOCAL
  
• 1 EMP VIEW OF I038-EMPLOYEES
  
• 2 NAME
  
• 2 PERSONNEL_ID
  
• 2 SALARY_1
  
• 1 PID (A8)
  
• END-DEFINE
  
• REPEAT
  
• INPUT PID
  
• SELECT INTO VIEW EMP FROM I038-EMPLOYEES
  
• WHERE PERSONNEL_ID PID
  
• END-SELECT
  
• INPUT (ADM) EMP.PERSONNEL_ID EMP.NAME
  EMP.SALARY_1
• UPDATE I038-EMPLOYEES SET
  
• NAME EMP.NAME, SALARY_1 EMP.SALARY_1
  
• WHERE PERSONNEL_ID PID
  
• END-REPEAT
  
• END

58
Cross Transation Update

• gt TEST DBLOG Q START gt
  Program EMPUPD1 Lib DRSLIB
• All ........1........2........3........4...
  .....5.....Mode Structured.
• 0010 DEFINE DATA LOCAL
  
• 0020 1 EMP VIEW OF I038-EMPLOYEES
  
• 0030 2 NAME
  
• 0040 2 PERSONNEL_ID
  
• 0050 2 SALARY_1
  
• 0060 1 EMP2 VIEW OF I038-EMPLOYEES
  
• 0070 2 NAME
  
• 0080 2 PERSONNEL_ID
  
• 0090 2 SALARY_1
  
• 0100 1 PID (A8)
  
• 0110 END-DEFINE
  
• 0120 INPUT PID
  
• 0130 RD1.
  
• 0140 SELECT INTO VIEW EMP FROM I038-EMPLOYEES
  
• 0150 WHERE PERSONNEL_ID PID
  
• 0160 INPUT (ADM) EMP.PERSONNEL_ID EMP.NAME
  EMP.SALARY_1
• 0170 UPDATE (RD1.)
  

59
Updating one record

• PID 30000100

60
Updating one record

• PERSONNEL_ID 30000100 NAME LLOYD
  SALARY_1 7200
• 
  

61
SQL Log from Update IMS with FS

• User DRS - DBLOG Trace -
  Library DRSLIB
• M No R SQL Statement (truncated) CU SN SREF
  M Typ SQLC/W Program Line LV
• _ 1 SELECT NAME,PERSONNEL_ID,SALA 01 01 0130
  D DB2 EMPUPD1 0130 01
• _ 2 FETCH CURSOR 01 01 0130
  D DB2 EMPUPD1 0130 01
• _ 3 ROLLOUTS TO FILE SERVER00000 01 01 0130
  D DB2 EMPUPD1 0160 01
• _ 4 SELECT NAME,PERSONNEL_ID,SALA 01 01 0130
  D DB2 EMPUPD1 0170 01
• _ 5 FETCH CURSOR 01 01 0130
  D DB2 EMPUPD1 0170 01
• _ 6 UPDATE I038.EMPLOYEES SET NAM 01 02 0130
  D DB2 EMPUPD1 0170 01
• _ 7 CLOSE CURSOR 01 01 0130
  D DB2 EMPUPD1 0170 01
• _ 8 READ FROM FILE SERVER 00 00 0130
  D DB2 100 EMPUPD1 0130 01
• _ 9 CLOSE FILE SERVER 00 00 0130
  D DB2 EMPUPD1 0130 01

62
SQL Log from Update - Complete

• M No R SQL Statement (truncated) CU SN SREF
  M Typ SQLC/W Program Line LV
• _ 1 SELECT NAME,PERSONNEL_ID FROM 01 01 0110
  D DB2 EMPUPD 0110 01
• _ 2 FETCH CURSOR NEX 01 01 0110
  D DB2 EMPUPD 0110 01
• _ 3 POSSIBLY IMPLICIT COMMIT 00 00 0000
  D DB2 EMPUPD 0140 01
• _ 4 UPDATE SADRS.EMPL_BASE SET NA 01 02 0110
  D DB2 EMPUPD 0150 01
• _ 5 FETCH CURSOR NEX 01 01 0110
  D DB2 100 EMPUPD 0110 01
• _ 6 CLOSE CURSOR 01 01 0110
  D DB2 EMPUPD 0110 01

63
Updating without reading row first

• DEFINE DATA LOCAL
• 1 PID (A8)
• END-DEFINE
• UPDATE I038-EMPLOYEES SET
• SALARY_1 SALARY_1 .05SALARY_1
• WHERE DEPT 'COMP02'
• END

64
Sample 1 code
SELECT INTO VIEW FT_FINAN_TRAN FROM
FT_FINAN_TRAN WHERE COY CCCWOTN-IN.COY
AND TRAN_ACCOUNT_ID CCCWOTN-IN.ACCOUNT_ID
AND TRAN_SA_ID CCCWOTN-IN.SA_ID ORDER BY
TRAN_ACCOUNT_ID, TRAN_DT, ADD_TIME REJECT IF
FT_FINAN_TRAN.TRAN_CANCELED 'Y' REJECT IF
FT_FINAN_TRAN.TRAN_MEMO_IND 'Y' REJECT IF
FT_FINAN_TRAN.TRAN_TYPE CFTTRTL.MEMO REJECT
IF FT_FINAN_TRAN.TRAN_TYPE CFTTRTL.CASH-DIST
REJECT IF FT_FINAN_TRAN.TRAN_TYPE
CFTTRTL.CASH-DIST-CANCEL-DETAIL REJECT IF
FT_FINAN_TRAN.TRAN_TYPE CFTTRTL.BILL-CANCEL-DE
TAIL REJECT IF FT_FINAN_TRAN.TRAN_TYPE
CFTTRTL.PAY-CANCEL-DETAIL REJECT IF
(FT_FINAN_TRAN.TRAN_TOT_AMT 0 AND
FT_FINAN_TRAN.TRAN_WRITE_OFF 0)
65
Sample 1 DBLOG
M No R SQL Statement (truncated) CU SN
SREF M Typ SQLC/W Program Line LV _ 1 SELECT
LOG_COUNTER,COY,ADD_US 01 01 2670 D DB2
DRSOYN5 2670 01 _ 2 FETCH CURSOR
01 01 2670 D DB2 DRSOYN5 2670 01 _
3 FETCH CURSOR 01 01 2670 D
DB2 DRSOYN5 2670 01 _ 4 FETCH CURSOR
01 01 2670 D DB2 DRSOYN5
2670 01 _ 5 FETCH CURSOR 01
01 2670 D DB2 DRSOYN5 2670 01 _ 6
FETCH CURSOR 01 01 2670 D DB2
DRSOYN5 2670 01 _ 7 FETCH CURSOR
01 01 2670 D DB2 DRSOYN5 2670
01 _ 8 FETCH CURSOR 01 01
2670 D DB2 DRSOYN5 2670 01 _ 9 FETCH
CURSOR 01 01 2670 D DB2
DRSOYN5 2670 01 _ 10 FETCH CURSOR
01 01 2670 D DB2 DRSOYN5 2670 01 _
11 FETCH CURSOR 01 01 2670 D
DB2 DRSOYN5 2670 01 _ 12 FETCH CURSOR
01 01 2670 D DB2 DRSOYN5
2670 01 _ 13 FETCH CURSOR 01
01 2670 D DB2 DRSOYN5 2670 01 _ 14
FETCH CURSOR 01 01 2670 D DB2
DRSOYN5 2670 01 _ 15 FETCH CURSOR
01 01 2670 D DB2 DRSOYN5 2670
01
.. _ 28 FETCH CURSOR
01 01 2670 D DB2 DRSOYN5 2670 01 _ 29
FETCH CURSOR 01 01 2670 D DB2
DRSOYN5 2670 01 _ 30 FETCH CURSOR
01 01 2670 D DB2 DRSOYN5 2670
01 _ 31 FETCH CURSOR 01 01
2670 D DB2 100 DRSOYN5 2670 01 _ 32 CLOSE
CURSOR 01 01 2670 D DB2
DRSOYN5 2670 01
66
Sample 1 Code Revision
SELECT INTO VIEW FT_FINAN_TRAN FROM
FT_FINAN_TRAN WHERE COY CCCWOTN-IN.COY
AND TRAN_ACCOUNT_ID CCCWOTN-IN.ACCOUNT_ID
AND TRAN_SA_ID CCCWOTN-IN.SA_ID AND
FT_FINAN_TRAN.TRAN_CANCELED ltgt 'Y' AND
TRAN_MEMO_IND ltgt 'Y' AND TRAN_TYPE NOT IN
(CFTTRTL.MEMO, CFTTRTL.CASH-DIST,CFTTRTL.
CASH-DIST-CANCEL-DETAIL, CFTTRTL.BILL-CANCE
L-DETAIL, CFTTRTL.PAY-CANCEL-DETAIL)
AND TRAN_TOT_AMT TRAN_WRITE_OFF ltgt 0 ORDER
BY TRAN_ACCOUNT_ID, TRAN_DT, ADD_TIME
67
Sample 1 Revised DBLOG
M No R SQL Statement (truncated) CU SN
SREF M Typ SQLC/W Program Line LV _ 1
SELECT LOG_COUNTER,COY,ADD_US 01 01 2420 D DB2
DRSOYN5Z 2420 01 _ 2 FETCH CURSOR
01 01 2420 D DB2 DRSOYN5Z 2420
01 _ 3 FETCH CURSOR 01 01
2420 D DB2 DRSOYN5Z 2420 01 _ 4 FETCH
CURSOR 01 01 2420 D DB2
DRSOYN5Z 2420 01 _ 5 FETCH CURSOR
01 01 2420 D DB2 DRSOYN5Z 2420 01 _
6 FETCH CURSOR 01 01 2420 D
DB2 DRSOYN5Z 2420 01 _ 7 FETCH CURSOR
01 01 2420 D DB2 DRSOYN5Z
2420 01 _ 8 FETCH CURSOR 01
01 2420 D DB2 100 DRSOYN5Z 2420 01 _ 9
CLOSE CURSOR 01 01 2420 D DB2
DRSOYN5Z 2420 01 _ 10 ROLLBACK
00 00 0000 D DB2 DBLOQ 0000 00
68
Sample 2
SELECT INTO VIEW CIS_ACCOUNT FROM
CIS_ACCOUNT WHERE COY INPUT.COY AND
ACCOUNT_ID AD_ADJUST.ACCOUNT_ID OPTIMIZE
FOR 1 ROW SP. SELECT INTO VIEW
CIS_PERSON FROM CIS_PERSON WHERE COY
INPUT.COY AND PERSON_ID
CIS_ACCOUNT.CUST_ID OPTIMIZE FOR 1 ROW
69
Sample 2
SELECT A.ACCOUNT_ID, A.CUST_ID,
B.PERSON_ID, B.LAST_NAME, B.FIRST_NAME,
B.BUSINESS_NAME INTO
CIS_ACCOUNT.ACCOUNT_ID,
CIS_ACCOUNT.CUST_ID ,CIS_PERSON.PERSON_ID,
CIS_PERSON.LAST_NAME ,CIS_PERSON.FIRST_NAME,
CIS_PERSON.BUSINESS_NAME
FROM CIS_ACCOUNT A, CIS_PERSON B WHERE
A.COY INPUT.COY AND
A.ACCOUNT_ID AD_ADJUST.ACCOUNT_ID
AND B.COY A.COY AND B.PERSON_ID A.CUST_ID
OPTIMIZE FOR 1 ROW
70
Sample 2
Queryno 1 EXPLAIN Result