SQL at Software AG

1
SQL at Software AG

• Developed by Systems Engineering Services
• Software AG, Inc.
• Darrell Skildum Advisory Systems Engineer.

2
First Generation of Natural
ADABAS
Natural on IBM mainframe
3
Current Generation of Natural (z/OS)
Natural on IBM mainframe
ADABAS
NDB
NSB
DB2
CONNX
Open Systems
Oracle
MS SQL
Excel, Access, Java, C VB, MS Query
ADABAS
DB2
4
Views Used

• Employees and Employees_Flat
• ISN_EMPLOYEES
• NAME (AE)
• CITY (AK)
• PERSONNEL_ID (AA)
• CITY (AJ)
• JOB_TITLE (AP)
• DEPT (AO)
• DEPARTMENT (S1)
• DEPT (AO) (1-4)
• DEPT-PERSON (S2)
• DEPT (AO)
• NAME (AE)

Vehicles 1.MAKE (AD) 2 MODEL (AE)

• EMPLOYEES_LEAVE_BOOKED
• ISN_EMPLOYEES
• This is also on the EMPLOYEES file for linking

5
Simple Descriptor Search

• Natural Code

DEFINE DATA LOCAL
1 EMPLOYEES VIEW OF
SADRS-EMPLOYEES_FLAT
2 ISN_EMPLOYEES
2 PERSONNEL_ID
2

END-DEFINE
SELECT
ISN_EMPLOYEES, PERSONNEL_ID, FIRST_NAME, NAME,
SEX, BIRTH, CITY, DEPT, DEPARTMENT
INTO VIEW EMPLOYEES

FROM SADRS-EMPLOYEES_FLAT
WHERE DEPARTMENT
'VENT'
END-SELECT
END
6
Simple Descriptor Search

• SQL

SELECT EMPLOYEES.ISN_EMPLOYEES,EMPLOYEES.PERSONNEL
_ID, EMPLOYEES.FIRST_NAME,
EMPLOYEES.NAME,
EMPLOYEES.SEX, EMPLOYEES.BIRTH,
EMPLOYEES.CITY, EMPLOYEES.DEPT,
EMPLOYEES.DEPARTMENT, EMPLOYEES.DEPT_PERSON
FROM EMPLOYEES WHERE
EMPLOYEES.DEPARTMENT 'VENT'

7
Simple Descriptor Search

• Command Log

5 L3 MV 11 1 1 0 0 0 6
L3 MV 11 3 2 0 0 0 7
RC 11 3 3 1 0 0 8
OP .. 0 9999 1 0 0 0
Multifetch
Read By Value
8
Simple Descriptor Search

• Command Log

• CMD OPT File ISN TH IOA IOD IOW
• L3 MV 11 1 1 0 0 0
• SB ( 16) S1,4,A,S,S1,4,A.
• VB ( 8) VENTVENT
• IB ( 36) 2,111,0,1,0,111,0,2,0
• 6 L3 MV 11 3 2 0 0 0
• SB ( 16) S1,4,A,S,S1,4,A.
• VB ( 8) VENTVENT
• IB ( 1508) 94,111,0,3,0,111,........
• ....... 111,0,200,0,0,3,0,0
• 7 RC 11 3 3 1 0 0
• 8 OP .. 0 9999 1 0 0 0

9
Simple Non Descriptor Search

• Natural Code

DEFINE DATA LOCAL 1 EMPLOYEES VIEW OF
SADRS-EMPLOYEES 2 PERSONNEL_ID 2 NAME 2
END-DEFINE FIND EMPLOYEES WITH FIRST_NAME
'ROBERT' DISPLAY NAME PERSONNEL_ID END-FIND END
10
Simple Non Descriptor Search

• ListSQL

Member,EMP2 , ,- LISTSQL -,
,Library,SADRS Natural statement,,at
line,0110, ,Stmt,
1,/,1 ,FIND EMPLOYEES WITH FIRST_NAME
'ROBERT' , ,

, ,

, Generated SQL statement, ,Mode ,dynamic ,DBRM
, , ,Line, 1,/,4 ,SELECT
PERSONNEL_ID, NAME, FIRST_NAME, SEX, BIRTH, DEPT,
CITY , ,FROM SADRS.EMPLOYEES
, ,WHERE
FIRST_NAME 'ROBERT'
, ,FOR FETCH ONLY
, ,

, ,
,
11
Simple Non Descriptor Search

• SQL

SELECT EMPLOYEES.ISN_EMPLOYEES,
EMPLOYEES.PERSONNEL_ID, EMPLOYEES.FIRST_NAME,
EMPLOYEES.NAME, EMPLOYEES.SEX, EMPLOYEES.BIRTH,
EMPLOYEES.CITY, EMPLOYEES.DEPT,
EMPLOYEES.DEPARTMENT, EMPLOYEES.DEPT_PERSON
FROM EMPLOYEES WHERE EMPLOYEES.FIRST_NAME
'ROBERT'
12
Simple Non Descriptor Search

• Command log

RECORD CMD RSP AD2(HEX)/AD2 COP X FILE
ISN 6 OP 0 00000000/....
0 0 7 L1 0 006F009A/..o.
MI 11 1 8 L1 0
006F009E/..o. MI 11 3 9
L1 0 006F00C5/..o. MI 11 287
10 L1 0 006F0101/..o. MI 11
571 11 L1 0 006F00F2/..o. MI
11 855 12 RC 0 00000000/....
11 855
Multifetch
Read By ISN
13
Simple Non Descriptor Search

• ISN Buffer

RECORD CMD RSP AD2(HEX)/AD2 COP X FILE
ISN 6 OP 0 00000000/.... 0
0 7 L1 0 006F009A/..o. MI
11 1 IB ( 36)
2,111,0,1,0,111,0,2,0 8 L1 0
006F009E/..o. MI 11 3 IB
( 1019) 284, 111,0,3,0, 111,0,4,0, 111,0,5,0,
111,0,6,0, 111,0,7,0,
Number of records
Return Code
ISN
Record Length
14
Simple Non Descriptor Search

• Last Record

RECORD CMD RSP AD2(HEX)/AD2 COP X FILE
ISN 111,0,1106,0,111,0,1107,0,0,3,
0,0
Return Code
ISN
Record Length
15
SimpleTwo Descriptor Search

• SQL

SELECT sadrs.EMPLOYEES.PERSONNEL_ID,
sadrs.EMPLOYEES.FIRST_NAME, sadrs.EMPLOYEES.NA
ME, sadrs.EMPLOYEES.CITY
FROM sadrs.EMPLOYEES WHERE sadrs.EMPLOYEES.NAME
'BROUSSE' AND sadrs.EMPLOYEES.CITY
'PARIS'
16
SimpleTwo Descriptor Search

• Command Log

5 18-SEP-2009 215446 0 S1 0 H
11 38 SB ( 18) AE,20,A,D,AJ,20,A.

VB ( 40) BROUSSE PARIS
6
18-SEP-2009 215446 0 L1 0 ON
11 38 7 18-SEP-2009 215446 0 RC
0
17
SimpleTwo Descriptor Search

• SQL

SELECT sadrs.EMPLOYEES.PERSONNEL_ID,
sadrs.EMPLOYEES.FIRST_NAME,
sadrs.EMPLOYEES.NAME, sadrs.EMPLOYEES.CITY,

lsadrs.EMPLOYEES.DEPT FROM sadrs.EMPLOYEES
WHERE
sadrs.EMPLOYEES.DEPT 'COMP73'
AND
sadrs.EMPLOYEES.CITY 'PARIS'

18
SimpleTwo Descriptor Search

• Command Log

5 18-SEP-2009 213600 0 S1 0 H
11 38 SB ( 17) AJ,20,A,D,S1,4,A.

VB ( 24) PARIS COMP
6
18-SEP-2009 213600 0 L1 0 ON
11 38 7 18-SEP-2009 213600 0
L1 0 MN 11 41
19
Multi Descriptor Search

• SQL

SELECT sadrs.EMPLOYEES.PERSONNEL_ID,
sadrs.EMPLOYEES.NAME, sadrs.EMPLOYEES.BIRTH,
sadrs.EMPLOYEES.CITY,
sadrs.EMPLOYEES.DEPT, sadrs.EMPLOYEES.JOB_TITLE
FROM sadrs.EMPLOYEES
WHERE
sadrs.EMPLOYEES.CITY 'PARIS'
AND sadrs.EMPLOYEES.PERSONNEL_ID
'50007600' AND
sadrs.EMPLOYEES.NAME 'MARX' AND
sadrs.EMPLOYEES.DEPT 'MARK06'
AND sadrs.EMPLOYEES.JOB_TITLE
'DIRECTEUR COMMERCIAL'
20
Multi Descriptor Search

• Command Log

5 20-SEP-2009 195931 0 S1 0 0
H 11 13 3 SB ( 46)
AA,8,A,D,S1,4,A,D,AP,25,A,D,AJ,20,A,D,AE,20,A.
VB ( 77) 50007600MARKDIRECTEUR
COMMERCIAL PARIS MARX 6
20-SEP-2009 195931 0 L1 0
N 11 13 2 7 20-SEP-2009 195931
0 RC 0 11 13 1

21
Simple Descriptor Search with Non Descriptor

• Natural Program

DEFINE DATA LOCAL 1 EMPLOYEES VIEW OF
SADRS-EMPLOYEES 2 PERSONNEL_ID 2 NAME 2
END-DEFINE SELECT PERSONNEL_ID, NAME,
FIRST_NAME, SEX, BIRTH, DEPT, CITY INTO VIEW
EMPLOYEES FROM SADRS-EMPLOYEES WHERE
SADRS-EMPLOYEES.FIRST_NAME ROBERT' AND
SADRS-EMPLOYEES.DEPT 'VENT' DISPLAY NAME
PERSONNEL_ID DEPT END-SELECT END '
22
Simple Descriptor Search with Non Descriptor

• SQL

SELECT EMPLOYEES.ISN_EMPLOYEES, EMPLOYEES.PERSONNE
L_ID, EMPLOYEES.FIRST_NAME,
EMPLOYEES.NAME, EMPLOYEES.SEX,
EMPLOYEES.BIRTH, EMPLOYEES.CITY, EMPLOYEES.DEPT,
EMPLOYEES.DEPARTMENT,
EMPLOYEES.DEPT_PERSON FROM EMPLOYEES WHERE
EMPLOYEES.DEPARTMENT 'VENT' AND
EMPLOYEES.FIRST_NAME 'ROBERT'
23
Simple Descriptor Search with Non Descriptor

• Command Log

RECORD CMD RSP COP X FILE ISN TH IOA
IOD IOW 7 L3 0 MV 11 1
2 0 0 0 8 L3 0 MV 11
3 3 0 0 0 9 RC 0
11 3 1 0 0 0
24
Simple Descriptor Search with Non Descriptor

• Command Log

RECORD CMD RSP COP X FILE ISN TH IOA
IOD IOW 7 L3 0 MV 11 1
2 0 0 0 SB ( 16) S1,4,A,S,S1,4,A.
VB ( 8) VENTVENT IB (
36) 2,111,0,1,0,111,0,2,0 8 L3 0 MV
11 3 3 0 0 0 9 RC 0
11 3 1 0 0 0
25
Simple Super Descriptor Search

• Natural Program

DEFINE DATA LOCAL
1 EMPLOYEES VIEW OF
SADRS-EMPLOYEES_FLAT
2 ISN_EMPLOYEES
2 PERSONNEL_ID
2
FIRST_NAME
2 NAME
2 SEX

2 ..
END-DEFINE

SELECT ISN_EMPLOYEES, PERSONNEL_ID,
FIRST_NAME, NAME, SEX, BIRTH, CITY, DEPT,
DEPARTMENT
INTO VIEW EMPLOYEES
FROM
SADRS-EMPLOYEES_FLAT
WHERE NAME'BRETON' AND
DEPARTMENT 'VENT06'
END-SELECT
END
26
Simple Super Descriptor Search

• SQL

SELECT EMPLOYEES.PERSONNEL_ID, EMPLOYEES.FIRST_NAM
E, EMPLOYEES.NAME, EMPLOYEES.JOB_TITLE,
EMPLOYEES.DEPT FROM EMPLOYEES
WHERE
EMPLOYEES.NAME 'BRETON' AND EMPLOYEES.DEPT
'VENT06'
27
Simple Super Descriptor Search

• Command Log

RECORD COP X FILE ISN TH IOA IOD IOW
------------------------------------------
-- 5 MV 11 166 1
0 0 0 SB ( 18)
S2,26,A,S,S2,26,A. VB
( 52) VENT06BRETON VENT06BRETON
IB ( 36) 2,79,0,166,0,0,3,0,0
6 RC 11 166 2 0 0
0
28
Array Processing from Flat Version

• SQL

SELECT EMPLOYEES_FLAT.NAME, EMPLOYEES_FLAT.DEPARTM
ENT, EMPLOYEES_FLAT.LEAVE_START_1,
EMPLOYEES_FLAT.LEAVE_END_1,
EMPLOYEES_FLAT.LEAVE_START_2, EMPLOYEES_FLAT.LEAV
E_END_2, EMPLOYEES_FLAT.LEAVE_START_3,
EMPLOYEES_FLAT.LEAVE_END_3,
EMPLOYEES_FLAT.LEAVE_START_4, EMPLOYEES_FLAT.LEAV
E_END_4, EMPLOYEES_FLAT.LEAVE_START_5,
EMPLOYEES_FLAT.LEAVE_END_5 FROM
EMPLOYEES_FLAT WHERE EMPLOYEES_FLAT.DEPARTMENT
'COMP'
29
Array Processing from Flat Version

• Natural Program

DEFINE DATA LOCAL 1
EMPLOYEES VIEW OF SADRS-EMPLOYEES_FLAT 2
DEPARTMENT 2 NAME
2 LEAVE_START_1
2 LEAVE_END_1
2 LEAVE_START_2
2 LEAVE_END_2
2 LEAVE_START_3 2
LEAVE_END_3
END-DEFINE FIND
EMPLOYEES WITH DEPT 'COMP' DISPLAY NAME
(AL10) DEPARTMENT LEAVE_START_1 /
LEAVE_END_1
LEAVE_START_2 /
END-FIND
END
30
Array Processing from Flat Version

• LISTSQL

Member EMP7 - LISTSQL -
Library SADRS

Natural statement at line 0160
Stmt 1 / 1

FIND EMPLOYEES WITH DEPT
'COMP27'
Generated
SQL statement Mode dynamic DBRM
Line 1 / 6

SELECT DEPARTMENT, NAME, LEAVE_START_1,
LEAVE_END_1, LEAVE_START_2,
LEAVE_END_2, LEAVE_START_3, LEAVE_END_3,
LEAVE_START_4,
LEAVE_END_4, LEAVE_START_5, LEAVE_END_5
FROM SADRS.EMPLOYEES_FLAT

WHERE DEPT 'COMP27'
FOR FETCH ONLY
31
Array Processing from Flat Version

• Command Log

RECORD CMD RSP COP X FILE ISN TH IOA IOD
IOW ----------------------------------------------
-------- 5 L3 0 MV 11 12
3 0 0 0 6 L3 0 MV 11
15 1 0 0 0 7 RC 0 11
15 2 1 0 0
32
Array Processing from Flat Version

• Results

GARCIA COMP 19981229 19981231 199810
02 19981003 0 0 LLAGUNO
COMP 19981013 19981017 19981222 19981231 0 0 RAMI
REZ COMP 19981224 19981231 0 0 0 0 GA
RAICOECHEA COMP 19981010 19981010 19981224
19981231 0 0 PERET
COMP 19981013 19981017 19981110 19981114 19981224
19981231 FERNANDEZ COMP 19980803 19980
804 0 0 0 0 SERRANO
COMP 19981231 19981231 0 0 0 0 OSEA
COMP 19980506 19980508 0 0 0 0 TYSON
COMP 19981229 19981231 19981002 19981003 0 0
MELLOR COMP 19981013 19981017 199812
22 19981231 0 0
33
Array Processing from Sub Table

• Natural Program

DEFINE DATA LOCAL
1 EMPLOYEES VIEW OF SADRS-EMPLOYEES
2 DEPT
2 ISN_EMPLOYEES
2 NAME
1 LEAVE VIEW OF
SADRS-EMPLOYEES_LEAVE_BOOKED 2
ISN_EMPLOYEES
2 LEAVE_BOOKED_COUNT
2 LEAVE_START
2 LEAVE_END
END-DEFINE
SELECT
A.DEPT,A.ISN_EMPLOYEES,A.NAME, B.ISN_EMPLOYEES,
B.LEAVE_BOOKED_COUNT,B.LEAVE_START,B.LEAVE_E
ND INTO VIEW EMPLOYEES , LEAVE
FROM SADRS-EMPLOYEES A,
SADRS-EMPLOYEES_LEAVE_BOOKED B WHERE
A.ISN_EMPLOYEES B.ISN_EMPLOYEES
AND A.DEPT 'COMP02'
DISPLAY NAME (ISON) DEPT
LEAVE_BOOKED_COUNT LEAVE_START
LEAVE_END
END-SELECT
34
Array Processing from Sub Table

• SQL

SELECT EMPLOYEES.NAME, EMPLOYEES_LEAVE_BOOKED.cnxa
rraycolumn, EMPLOYEES_LEAVE_BOOKED.LEAVE_START,

EMPLOYEES_LEAVE_BOOKED.LEAVE_END,
EMPLOYEES.DEPARTMENT,
EMPLOYEES_LEAVE_BOOKED.LEAVE_BOOKED_COUNT
FROM EMPLOYEES,
EMPLOYEES_LEAVE_BOOKED
WHERE EMPLOYEES.ISN_EMPLOYEES
EMPLOYEES_LEAVE_BOOKED.ISN_EMPLOYEES
AND EMPLOYEES.DEPARTMENT 'COMP'

35
Array Processing from Sub Table

• Command Log

RECORD CMD RSP COP X FILE ISN TH IOA
IOD IOW 11 L3 0 MV 11 12
1 0 0 0 12 L3 0 MV 11
15 2 0 0 0 13 RC 0 11
15 3 1 0 0 14 L1 0 RI
11 12 1 0 0 0 15 L1 0
RI 11 14 2 0 0 0 16 L1
0 RI 11 15 3 0 0 0 17
L1 0 RI 11 16 1 0 0 0 18
L1 0 RI 11 17 2 0 0
0 19 L1 0 RI 11 25 3 0
0 0 20 L1 0 RI 11 38 1
0 0 0 21 L1 0 RI 11
39 2 0 0 0 22 L1 0 RI 11
40 3 0 0 0 23 L1 0 RI
11 41 1 0 0 0 24 L1 0 RI
11 63 2 0 0 0 25 L1 0
RI 11 68 3 0 0 0 26 L1
0 RI 11 72 1 0 0 0
36
Array Processing from Sub Table

• Results

NAME cnxarraycolumn LEAVE_START LEAVE_END
DEPT Count BAILLET 0 19990715
19990815 COMP 1 D'AGOSTINO 0
19990801 19990831 COMP 1 LEROUGE
0 19990715 19990815 COMP 1 GRUMBACH
0 19990715 19990815 COMP 1 HEURTEBISE
0 19990715 19990815 COMP 1 TANCHOU
0 19990715 19990815 COMP 1
BROUSSE 0 19990715
19990815 COMP 1 GIORDA 0
19990715 19990815 COMP 1 TEYSSIER 0
19990715 19990815 COMP 1 SCHINDLER 0
19980101 19980115 COMP 2 SCHINDLER 1
19981222 19981231 COMP 2 SCHIRM 0
19980715 19980715 COMP 2 SCHIRM 1
19980730 19980730 COMP 2 SCHMITT 0
19980715 19980812 COMP 1 BUNGERT 0
19980801 19980808 COMP 2 BUNGERT 1
19981218 19981229 COMP 2 TREIBER 0
19980101 19980109 COMP 1
37
Using two descriptors with like

• SQL

SELECT EMPLOYEES.PERSONNEL_ID, EMPLOYEES.FIRST_NAM
E, EMPLOYEES.NAME, EMPLOYEES.JOB_TITLE,EMPLOYEES.D
EPT FROM EMPLOYEES WHERE EMPLOYEES.NAME LIKE
'C' AND EMPLOYEES.DEPT LIKE 'VENT'

38
Using two descriptors with like

• Command Log

RECORD CMD RSP COP X FILE ISN TH IOA
IOD IOW 5 S1 0 H 11 5
2 0 0 0 SB ( 42)
AE,20,A,GE,S,AE,20,A,D,S1,4,A,GE,S,S1,4,A.
VB ( 48) 00000000 43202020
20202020 20202020 20202020 C
00000010 20202020 43FEFEFE FEFEFE20
20202020 C...... 00000020
20202020 20202020 56454E54 56454E54
VENTVENT 6 L1 0 ON 11
5 3 0 0 0 7 L1 0 MN 11
43 1 0 0 0
39
Using two descriptors with like

• Command Log

AE,20,A,GE,S,AE,20,A,D,S1,4,A,GE,S,S1,4,A. Field
Name Length Format
Conditon Connector
(sFrom to Range
40
Update

• SQL

UPDATE SADRS-EMPLOYEES_FLAT SET SALARY_1
SALARY_1 1.05 WHERE DEPT 'COMP02' END
41
Update

• ListSQL

Natural statement,,at line,0010,
,Stmt, 1,/,1 ,UPDATE
SADRS-EMPLOYEES_FLAT
, , SET SALARY_1 SALARY_1
1.05 , ,

, Generated SQL statement,
,Mode ,dynamic ,DBRM , , ,Line,
1,/,2 ,UPDATE SADRS.EMPLOYEES_FLAT SET
SALARY_1 SALARY_1 1.05 , ,WHERE
DEPT 'COMP02'
,

42
Update

• Command Log

3 17-SEP-2009 134708 15 L6 0
RA 11 14 3 RB ( 11) COMP02.....

SB ( 7) AO,6,A.

VB ( 6) COMP02
4 17-SEP-2009
134708 16 A1 0 RH 11
14 2 RB ( 5) ...R\
5
17-SEP-2009 134708 0 L6 0
RA 11 90 1 RB ( 11) COMP02...Q,

SB ( 7) AO,6,A.

VB ( 6) COMP02
6 17-SEP-2009
134708 0 A1 0 RH 11
90 3 RB ( 5) ...7l

43
Simple Join Nested Loop

• Natural Program

DEFINE DATA LOCAL 1 EMPLOYEES VIEW OF
SADRS-EMPLOYEES_FLAT 2 PERSONNEL_ID 2
FIRST_NAME 2 NAME 2 DEPT 1 VEHICLES VIEW OF
SADRS-VEHICLES 2 PERSONNEL_ID 2 MAKE 2
MODEL END-DEFINE FIND EMPLOYEES WITH DEPT
'COMP02' FIND VEHICLES WITH VEHICLES.PERSONNEL_I
D EMPLOYEES.PERSONNEL_ID DISPLAY
FIRST_NAME(AL10) NAME (AL10) DEPT MAKE
MODEL END-FIND END-FIND END
44
Simple Join Nested Loop

• ListSql 1

Member,JOINOJR , ,- LISTSQL -,
,Library,SADRS Natural statement,,at
line,0120, ,Stmt,
1,/,2 ,FIND EMPLOYEES WITH DEPT 'COMP02'
, ,

, ,
, Generated SQL
statement, ,Mode ,dynamic ,DBRM , ,
,Line, 1,/,4 ,SELECT PERSONNEL_ID,
FIRST_NAME, NAME, DEPT
, ,FROM SADRS.EMPLOYEES_FLAT
, ,WHERE DEPT
'COMP02'
, ,FOR FETCH ONLY
,
45
Simple Join Nested Loop

• ListSql 2

member,EMP8 , ,- LISTSQL -,
,Library,SADRS

Natural statement,,at line,0130,
,Stmt, 2,/,2

,FIND VEHICLES WITH VEHICLES.PERSONNEL_
ID , ,
EMPLOYEES.PERSONNEL_ID
, ,
,

Generated SQL
statement, ,Mode ,dynamic ,DBRM , ,
,Line, 1,/,4
,SELECT
PERSONNEL_ID, MAKE, MODEL
, ,FROM SADRS.VEHICLES
,
,WHERE PERSONNEL_ID ?
, ,FOR FETCH ONLY

, ,
, ,

, ,
, ,

,

46
Simple Join Nested Loop

• Results

FIRST_NAME NAME DEPT MAKE
MODEL , ---------- ----------
------ -------------------- --------------------,
DANIEL GREGOIRE COMP02 DATSUN
80 SERGE RIGOLLET COMP02 CITROEN
LN ANTONIO VILLAR COMP02 RENAULT
21 GORKA NIEDA COMP02 RENAULT
11 JORDI ESPLA COMP02 PEUGEOT
205 MARIA JOSE PUERTOLAS COMP02
RENAULT 11 ARTURO SERRANO
COMP02 RENAULT 18 ROBERT
ACHIESON COMP02 FORD ESCORT
1.3 EILEEN HAMPSHIRE COMP02 HONDA
CIVIC WILFRED WOOD COMP02 AUSTIN
MONTEGO 1.3 BRIAN HARRISON
COMP02 AUSTIN MAESTRO DONALD
HANDLEY COMP02 FORD ESCORT
1.3 ARTHUR TIPTON COMP02 FORD
SIERRA 1.6 RODNEY WILLIAMS COMP02 FORD
CAPRI 2.8 WALTER HAMSON
COMP02 FORD SIERRA 1.6 CAROL
PARSONS COMP02 AUSTIN
METRO ROBIN ARCHER COMP02 VAUXHALL
CAVALIER WALTER REDMAN COMP02 FORD
SIERRA 1.6,
47
Simple Join Join Nested Loop

• Command Log

3 21-SEP-2009 123600 0 L3 0
MV 11 14 SB ( 16)
AO,6,A,S,AO,6,A.
VB ( 12) COMP02COMP02
4 21-SEP-2009
123600 0 L3 0 MV 11
107 SB ( 16) AO,6,A,S,AO,6,A.
VB ( 12)
COMP02COMP02
5 21-SEP-2009 123600 0
RC 0 11 107 6 21-SEP-2009
123603 0 L3 3 MV 12
0 SB ( 16) AC,8,A,S,AC,8,A.
VB ( 16)
5000370050003700
7 21-SEP-2009 123603 0
RC 0 12 0 8 21-SEP-2009
123604 0 L3 3 MV 12
0 SB ( 16) AC,8,A,S,AC,8,A.
VB ( 16)
5001660050016600
9 21-SEP-2009 123604 0
RC 0 12 0 10 21-SEP-2009
123606 0 L3 3 MV 12
0 SB ( 16) AC,8,A,S,AC,8,A.
VB ( 16)
5001900050019000
11 21-SEP-2009 123606 0
RC 0 12 0 ..
48
Simple Join SQL

• Natural Program (SQL)

1 EMPLOYEES VIEW OF SADRS-EMPLOYEES_FLAT 2
PERSONNEL_ID 2 FIRST_NAME 2 NAME 2 DEPT 1
VEHICLES VIEW OF SADRS-VEHICLES 2 PERSONNEL_ID 2
MAKE 2 MODEL END-DEFINE SELECT A.PERSONNEL_ID,
A.FIRST_NAME, A.NAME, A.DEPT, B.PERSONNEL_ID,
B.MAKE, B.MODEL INTO VIEW EMPLOYEES,
VEHICLES FROM SADRS-EMPLOYEES_FLAT A,
SADRS-VEHICLES B WHERE A.PERSONNEL_IDB.PERSONNE
L_ID AND A.DEPT 'COMP02' DISPLAY
FIRST_NAME(AL10) NAME (AL10) DEPT MAKE
MODEL END-SELECT END
49
Simple Join

• Results

FIRST_NAME NAME DEPT MAKE
MODEL , ---------- ----------
------ -------------------- --------------------,
DANIEL GREGOIRE COMP02 DATSUN
80 SERGE RIGOLLET COMP02 CITROEN
LN ANTONIO VILLAR COMP02 RENAULT
21 GORKA NIEDA COMP02 RENAULT
11 JORDI ESPLA COMP02 PEUGEOT
205 MARIA JOSE PUERTOLAS COMP02
RENAULT 11 ARTURO SERRANO
COMP02 RENAULT 18 ROBERT
ACHIESON COMP02 FORD ESCORT
1.3 EILEEN HAMPSHIRE COMP02 HONDA
CIVIC WILFRED WOOD COMP02 AUSTIN
MONTEGO 1.3 BRIAN HARRISON
COMP02 AUSTIN MAESTRO DONALD
HANDLEY COMP02 FORD ESCORT
1.3 ARTHUR TIPTON COMP02 FORD
SIERRA 1.6 RODNEY WILLIAMS COMP02 FORD
CAPRI 2.8 WALTER HAMSON
COMP02 FORD SIERRA 1.6 CAROL
PARSONS COMP02 AUSTIN
METRO ROBIN ARCHER COMP02 VAUXHALL
CAVALIER WALTER REDMAN COMP02 FORD
SIERRA 1.6,
50
Simple Join

• Command Log

3 17-SEP-2009 150521 0 L3 0
MV 11 14 2 0 0 0 SB
( 16) AO,6,A,S,AO,6,A.
VB (
12) COMP02COMP02
IB ( 36)
2,54,0,14,0,54,0,90,0
4 17-SEP-2009
150521 0 L3 0 MV 11
107 1 0 0 0 SB ( 16)
AO,6,A,S,AO,6,A.
VB ( 12)
COMP02COMP02
IB ( 548)
34,54,0,107,0,54,0,117,0,54,0,196,0,54,0,411,0,54,
0,432,0,54,0,447,0,
54,0,459,0,54,0,469,0,54,0,479,0,54,0,483

0,54,0,495,0,54,0,498,0,54,0,856,0,54,0,878,0,54,0
,899,0,
54,0,916,0,54,0,928,0,54,0,939,0,54,0,949,0,54,0,9
55
0,54,0,957,0,54,0,960,0,54,0,962,0,54,0,979,0,54,0
,991,0,
54,0,992,0,54,0,1024,0,54,0,1028,0,54,0,1049,0,54,
0
1056,0,54,0,1071,0,54,0,1078,0,54,0,1090,0,0,3,0,0
5 17-SEP-2009
150521 0 RC 0 11
107 3 0 0 0
51
Simple Join

• Command Log

6 17-SEP-2009 150521 0 L3 0
RA 12 699 2 SB ( 7) AC,8,A.

VB ( 8) 30000038

7 17-SEP-2009 150521 0
L3 0 RA 12 687 1
SB ( 7) AC,8,A. VB ( 8) 30000231

8 17-SEP-2009 150521
0 L3 0 RA 12 745 3 SB (
7) AC,8,A. VB ( 8) 30000231

9 17-SEP-2009 150521 0 L3
0 RA 12 705 2 SB ( 7) AC,8,A.

VB ( 8) 30000509

10 17-SEP-2009 150521 0
L3 0 RA 12 685 1 SB ( 7)
AC,8,A.
VB ( 8) 30000509

11 17-SEP-2009 150521
0 L3 0 RA 12 748 3 SB (
7) AC,8,A.
VB ( 8)
30008045

52
Simple Join Left Outer Join Natural Code

• Natural Program SQL

1 EMPLOYEES VIEW OF SADRS-EMPLOYEES_FLAT 2
PERSONNEL_ID 2
FIRST_NAME 2 NAME
2 DEPT
1 VEHICLES VIEW
SADRS-VEHICLES 2 PERSONNEL_ID
2 MAKE
2 MODEL
END-DEFINE
FIND EMPLOYEES WITH DEPT 'COMP02'
FIND VEHICLES WITH VEHICLES.PERSONNEL_ID
EMPLOYEES.PERSONNEL_ID IF NO
RECORDS FOUND IGNORE
END-NOREC
DISPLAY NAME (ISON)
DEPT MAKE MODEL END-FIND
END-FIND
END

53
Simple Join Left Outer Join Natural Code

• Natural Program SQL

NAME DEPT MAKE
MODEL , -------------------- ------
-------------------- --------------------,
D'AGOSTINO COMP02
GODEFROY
COMP02
BESSON COMP02
GREGOIRE
COMP02 DATSUN 80
RIGOLLET COMP02 CITROEN
LN VILLAR
COMP02 RENAULT 21
RODRIGUEZ COMP02
NIEDA
COMP02 RENAULT 11
ESPLA COMP02 PEUGEOT
205 PUERTAS
COMP02
PUERTOLAS COMP02 RENAULT
11 LLAGUNO
COMP02
SERRANO COMP02 RENAULT
18 OSEA
COMP02
MELLOR COMP02
ACHIESON
COMP02 FORD ESCORT 1.3
HAMPSHIRE COMP02 HONDA
CIVIC WOOD
COMP02 AUSTIN MONTEGO 1.3
54
Simple Join Left Outer Join

• Natural Program SQL

1 EMPLOYEES VIEW OF SADRS-EMPLOYEES_FLAT
2 PERSONNEL_ID
2 FIRST_NAME
2
NAME
2 DEPT
1 VEHICLES VIEW OF
SADRS-VEHICLES 2
PERSONNEL_ID
2 MAKE
2 MODEL

END-DEFINE
SELECT A.PERSONNEL_ID,
A.FIRST_NAME, A.NAME,
A.DEPT,
B.PERSONNEL_ID, B.MAKE, B.MODEL
INTO VIEW EMPLOYEES,
VEHICLES FROM
(SADRS-EMPLOYEES_FLAT A LEFT OUTER JOIN
SADRS-VEHICLES B ON (B.PERSONNEL_ID
A.PERSONNEL_ID)) WHERE
(A.DEPT'COMP02')
DISPLAY FIRST_NAME(AL10) NAME (AL10)
DEPT MAKE MODEL END-SELECT

55
Simple Join Left Outer Join

• ListSql

Natural statement,,at line,0120,
,Stmt, 1,/,1 ,SELECT
A.PERSONNEL_ID, A.FIRST_NAME,
, , A.NAME, A.DEPT,
, ,
B.PERSONNEL_ID, B.MAKE, B.MODEL
, Generated SQL statement,
,Mode ,dynamic ,DBRM , , ,Line,
1,/,6 ,SELECT A.PERSONNEL_ID, A.FIRST_NAME,
A.NAME, A.DEPT, B.PERSONNEL_ID, B , ,
.MAKE, B.MODEL
, ,FROM ( SADRS.EMPLOYEES_FLAT
A LEFT OUTER JOIN SADRS.VEHICLES B ON ( , ,
B.PERSONNEL_ID A.PERSONNEL_ID ) )
, ,WHERE ( A.DEPT 'COMP21'
) ,
,FOR FETCH ONLY ,
56
Simple Join Left Outer Join

• Results

FIRST_NAME NAME DEPT MAKE
MODEL ---------- ---------- ------
-------------------- -----------------

LOUIS D'AGOSTINO COMP02
ANNIE GODEFROY
COMP02
ROGER BESSON COMP02
DANIEL GREGOIRE COMP02
DATSUN 80 SERGE
RIGOLLET COMP02 CITROEN LN
ANTONIO VILLAR COMP02 RENAULT
21 VICTORIA RODRIGUEZ
COMP02
GORKA NIEDA COMP02 RENAULT
11 JORDI ESPLA COMP02
PEUGEOT 205 ANTONIO
PUERTAS COMP02
MARIA JOSE PUERTOLAS COMP02 RENAULT
11 AMANDA LLAGUNO
COMP02
ARTURO SERRANO COMP02 RENAULT
18 ROBERTO OSEA COMP02
AMANDA
MELLOR COMP02
ROBERT ACHIESON COMP02 FORD
ESCORT 1.3 EILEEN HAMPSHIRE
COMP02 HONDA CIVIC
WILFRED WOOD COMP02 AUSTIN
MONTEGO 1.3
57
Simple Join Left Outer Join

• Command Log

3 17-SEP-2009 212221 0 L3 0
MV 11 205 2 FB ( 30)
AA,8,A,AC,20,A,AE,20,A,AO
SB ( 16) AO,6,A,S,AO,6,A.
VB (
12) COMP21COMP21
IB ( 36)
2,54,0,205,0,54,0,213,0
4 17-SEP-2009 212221
0 L3 0 MV 11 214 3 FB (
30) AA,8,A,AC,20,A,AE,20,A,AO,6,A.
SB ( 16) AO,6,A,S,AO,6,A.
VB
( 12) COMP21COMP21
IB ( 132)
8,54,0,214,0,54,0,218,0,54,0,221,0,54,0,243,0,54,0
,244,0, 54,0,247,0,54,0,249,0,
0,3,0,0 5
17-SEP-2009 212221 0 RC 0
11 214 1 6 17-SEP-2009 212221
0 L3 0 RA 12 110
2 FB ( 23) AC,8,A,AD,20,A,AE,20,A.
SB ( 7)
AC,8,A.
VB ( 8) 11100105
7
17-SEP-2009 212221 0 L3 0
RA 12 111 3 FB ( 23)
AC,8,A,AD,20,A,AE,20,A.
SB ( 7) AC,8,A.
VB (
8) 11100105
8 17-SEP-2009 212221
0 L3 0 RA 12 114 1 FB
( 23) AC,8,A,AD,20,A,AE,20,A.
SB ( 7) AC,8,A.

VB ( 8) 11100113

58
Simple Join Left Outer Join

• SQL

Outer Join from Microsoft Query
SELECT
EMPLOYEES.PERSONNEL_ID, EMPLOYEES.FIRST_NAME,
EMPLOYEES.NAME, EMPLOYEES.DEPT,
VEHICLES.PERSONNEL_ID, VEHICLES.MAKE,
VEHICLES.MODEL FROM oj
sadrs.EMPLOYEES EMPLOYEES LEFT OUTER JOIN
sadrs.VEHICLES VEHICLES ON
EMPLOYEES.PERSONNEL_ID VEHICLES.PERSONNEL_ID
WHERE
(EMPLOYEES.DEPT'COMP21')

59
Simple Join Left Outer Join

• Command Log

3 17-SEP-2009 192815 0 L3 0
MV 11 205 3 SB ( 16)
AO,6,A,S,AO,6,A.
VB ( 12)
COMP21COMP21
4 17-SEP-2009 192815
0 L3 0 MV 11 214
2 SB ( 16) AO,6,A,S,AO,6,A.
VB (
12) COMP21COMP21
5 17-SEP-2009
192815 0 RC 0 11
214 1 6 17-SEP-2009 192815 0
L3 0 RA 12 110 3 RB ( 48)
11100105OPEL RECORD L
SB ( 7) AC,8,A.

VB ( 8) 11100105

7 17-SEP-2009 192815 0
L3 0 RA 12 111 2 RB ( 48)
11100106VW PASSAT LS
SB ( 7) AC,8,A.

VB ( 8) 11100105

8 17-SEP-2009 192815 0 -
L3 0 RA 12 114 1 B ( 48)
11100113AUDI 80 LS
SB ( 7) AC,8,A.

VB ( 8) 11100113

9 17-SEP-2009 192815 0
L3 0 RA 12 115 3 RB ( 48)
11100114BMW 325 E
SB ( 7) AC,8,A.

VB ( 8) 11100113
60
SQL with subquery

• SQL

select a.isn_employees,a.name,a.dept,b.salary,b.is
n_employees from sadrs.employees a,
sadrs.employees_income b where a.dept
'COMP21' and b.isn_employees a.isn_employees
and b.salary (select max(salary) from
sadrs.employees_income where isn_employees
a.isn_employees)
61
SQL with subquery

• Command Log

3 18-SEP-2009 140449 0 L3 0
MV 11 205 1 SB ( 16)
AO,6,A,S,AO,6,A.
VB ( 12)
COMP21COMP21
4 18-SEP-2009 140449
0 L3 0 MV 11 214
2 SB ( 16) AO,6,A,S,AO,6,A.
VB (
12) COMP21COMP21
5 18-SEP-2009
140449 0 RC 0 11
214 6 18-SEP-2009 140449 0
L1 0 RI 11 205 7 18-SEP-2009
140449 0 L1 0 RI 11
213 8 18-SEP-2009 140449 0
L1 0 RI 11 214 9 18-SEP-2009
140449 0 L1 0 RI 11
218 10 18-SEP-2009 140449 0
L1 0 RI 11 221 11 18-SEP-2009
140449 0 L1 0 RI 11
243 36 18-SEP-2009 140449 0
L3 0 MV 11 205 SB (
16) AO,6,A,S,AO,6,A.
VB ( 12)
COMP21COMP21
37 18-SEP-2009 140449
0 L3 0 MV 11 214
SB ( 16) AO,6,A,S,AO,6,A.
VB (
12) COMP21COMP21
38 18-SEP-2009
140449 0 RC 0 11
214 39 18-SEP-2009 140449 0
L1 0 RI 11 205 40
18-SEP-2009 140449 0 L1 0
RI 11 213 41 18-SEP-2009 140449
0 L1 0 RI 11 214
42 18-SEP-2009 140449 0 L1 0
RI 11 218 43 18-SEP-2009
140449 0 L1 0 RI 11
221
62
Histogram (Select Count)

• SQL

SELECT COUNT (), CITY FROM
SADRS.EMPLOYEES WHERE CITY gt '
GROUP BY CITY ORDER BY CITY
FOR FETCH ONLY
63
Histogram (Select Count)

• Results

COUNT( ) DEPT 5 ADMA0 1 3
ADMA0 2 16 COMP0 1 35 COMP0
2 22 COMP0 3 1 COMP0 5 1
COMP1 1 26 COMP1 2 3
COMP1 5 9 COMP2 1 15 COMP2
5 6 COMP2 6
64
Histogram (Select Count)

• Command Log

4 21-SEP-2009 072735 0 L3 0 MA
11 500 SB ( 7) AO,6,A.

VB ( 6)
5
21-SEP-2009 072735 0 L3 0 MA
11 524 SB ( 7) AO,6,A.

VB ( 6)
6 21-SEP-2009
072735 0 L3 0 MA 11
189 SB ( 7) AO,6,A.
VB (
6)

65
Histogram (Select Count) with Range

• SQL

SELECT COUNT (), DEPT
FROM SADRS.EMPLOYEES
WHERE DEPT between 'COMP' and 'COMPZ '
GROUP BY DEPT
ORDER BY DEPT
66
Histogram (Select Count) with Range

• SQL

21-SEP-2009 074813 0 L3 0 MA
11 17 SB ( 7) AO,6,A.
VB ( 6) COMP

21-SEP-2009 074813 0 L3 0 MA
11 41 SB ( 7) AO,6,A.
VB ( 6)
COMP

67
Histogram (Select Count) with Other Search

• SQL

SELECT COUNT (), DEPT FROM SADRS.EMPLOYEES
WHERE CITY 'BOSTON'
GROUP BY DEPT
ORDER BY DEPT

68
Histogram (Select Count) with Other Search

• Results

COUNT( ) DEPT 1 COMP01 1
COMP12 2 MGMT10 1 SALE40 2
TECH10
69
Histogram (Select Count) with Other Search

• Command Log

3 21-SEP-2009 120029 0 L3 0 MV 11
682 SB ( 18) AJ,20,A,S,AJ,20,A.
VB ( 40)
BOSTON BOSTON
4 21-SEP-2009 120029 0 L3 0 MV
11 841 SB ( 18)
AJ,20,A,S,AJ,20,A.
VB ( 40) BOSTON BOSTON