Transcript birth, death, infinity - EMEA PUG Challenge Conference

Birth, Death, Infinity
Gus Björklund. ???.
Dan Foreman. BravePoint.
PUG Challenge Dusseldorf 2014
Dan Foreman – C.V.
 Progress User since 1984 – my 30 year anniversary
 V2.1 (there was not a commercial V1)
 Author of several Progress Publications
• Progress Performance Tuning Guide
• Progress Database Administration Guide
• Progress System Tables
 Basketball & Bicycle Fanatic…which sometimes leads to
unexpected trips to the Emergency Room
• Warning – potentially disturbing content
3
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
4
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Dan Foreman – C.V.
My New Wheels
(so I won’t fall over again)
5
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Audience Intro
 Longest Progress User?
 Progress version?
• V6
• V7
• V8
• V9
• V10
• V11
6
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Conception
8
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
A Table is defined
Methods for defining a new table:
 Use the Data Dictionary Tool
• Type in all the information
 Load a “.df" file
•
run prodict/load_df.p (os-getenv ("SCHEMADIR") + "/"
"customer.df").
 Execute a SQL DDL statement
• CREATE TABLE pub.customer (cust-num integer, . . . ) AREA "a51";
9
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Where does the definition go?
Table definitions
_file
_field
_field
_field
_field
_field
One _field record (row) for each field (column) of a table
The _* tables live in the Schema Area. Put yours elsewhere.
11
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
List Your Tables and Their Fields
output to tables.txt.
for each _file
where (0 < _file-num):
put _file-name skip.
for each _field of _file:
put “
“ _field-name skip.
end.
put “” skip.
end.
output close.
12
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Index definitions
_file
_index
_index-field
_index
_index-field
_index-field
_index-field
One _index record for each index of a table
One _index-field record for each key
component of an index
13
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
List indexes and key components by table
output to index.txt.
for each _file where (0 < _file-num):
put _file-name skip.
for each _index of _file:
put "
" _index-name skip.
for each _index-field of _index:
find _field
where recid (_field) = _field-recid.
put "
" _field-name skip.
end.
end.
put "" skip.
end.
output close.
14
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Where do the Initial values go?
The template record
 Each table has a template record
 Column values in the template become the Initial values for new
records
 Template records are NOT stored with schema metadata
• Go in table's home area or schema area (V11)
• Have to read from database to get template
 TODAY and NOW defaults replaced with current values from the
system clock
16
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Birth
17
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
CREATE a new row in a 4GL program
create customer.
18
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
CREATE a new row in a 4GL program
create customer.
what happens?
we read template record from database
(if we don't have it already)
client (local) buffer is populated with a copy of the template
record
current values inserted for TODAY, NOW
no write to database – db knows nothing about the create
at this point.
19
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Where does customer row go?
CONDB
Etc.
order
DBDES
customer
ICB for table
customer
CI for
customer
20
FDTBL for
database foo
© 2014 Progress Software Corporation. All rights reserved.
row buffer
Birth, Death, Infinity
assign column values
Address
Address2
City
Contact
Curr-bal
Cust-num
Discount
Max-credit
Mnth-sales
Name
21
© 2014 Progress Software Corporation. All rights reserved.
Phone
Sales-region
Sales-rep
St
Tax-no
Terms
Ytd-sls
Zip
Birth, Death, Infinity
when do we send it to the database ?
database accelerator
23
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Some basic rules for when a record is written
 VALIDATE statement
 RELEASE statement
 Buffer goes out of scope
 All fields for a unique key are ASSIGNed a value
 A LOB field is populated
 RECID function
 ROWID function
 Other stuff
24
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Remote client sends row to database
client's row buffer
client's network
message buffer
25
© 2014 Progress Software Corporation. All rights reserved.
server's row buffer
TCP/IP
server's network
message buffer
Birth, Death, Infinity
where do we put it ?
Database Pages (Blocks)







27
Database divided into fixed-size blocks or “pages”
Adjacent blocks form clusters in Type 2 Areas
Different kinds of blocks store different kinds of data
Disk i/o done in block size units
Each block has unique identifier – its “dbkey”
Data blocks are called RM (Record Manager) blocks
Index blocks are called IX blocks
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Search-for-RM-Space Hierarchy
1. Is there space on the RM Chain
• Per Object in Type 2
• Per Area in Type 1
2. Is there space on the Free Chain
3. Are there Empty Blocks above the Area HWM
4. Extend the DB
• Format the block
• Move the HWM
• Update various counters & chains
 See these slides for space allocation algorithm details
 Space, the final frontier
http://communities.progress.com/pcom/docs/DOC-107729
28
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Find data block, read into database buffer pool
“newest (MRU)”
M
M
Hash
Table
29
M
M
M
M
Checkpoint Queue
© 2014 Progress Software Corporation. All rights reserved.
“oldest (LRU)”
LRU Chain
M
M
M
Page Writer Queue
Birth, Death, Infinity
Update data block








note: simplified.
Acquire exclusive lock on buffer
some details
Generate "create" BI note
omitted to
Spool bi note
protect the
innocent
Copy from row buffer to data block
Update block free space if needed
Mark buffer as modified
Create index keys for all active indexes (details later)
Release buffer locks
 More stuff that happens that we don’t have time for:
• Trigger firing (some info in the Recovery Notes session)
• Sequences (“”)
• VST & promon counters updated
30
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Record In A Row Data Block (aka RM Block)
block header
RM block header
dir entry 0
dir entry 1
dir entry 2
dir entry 3
Newly created row is stored
in some row data block and
assigned an available
directory entry.
The chosen block and
directory entry determine the
ROWID
free space
record 3
record 2
record 1
record 0
31
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Birth Alternatives
32
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Other ways records are born
 sql:
INSERT INTO customer (cust-no, name street, city, state)
VALUES (642, 'gus', '14 oak park', 'bedford', 'ma');
 sql:
INSERT INTO customer (cust-no, name street, city, state)
SELECT . . . FROM . . . WHERE . . . ;
 binary load:
proutil foo -C load bar.bd
33
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Possible Problems
34
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Things to consider
 When record is sent to database before all the values are
assigned, one database operation
• create
 becomes several
• create
• update
• update
• etc.
 The record expands as more values are assigned
• fragmentation will occur
 Attend the Recovery Notes presentation for some
excellent examples
35
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Life
36
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
READ
find customer where custnum = 3447.
37
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
READ
 Compiler determines which index to use (perhaps custnum)
• R-code has index information
• Query used to form equality or range bracket












38
At runtime, load schema cache into client’s memory
Look up the table number (# 3)
Find _storage-object records for table #3 (for tenant #t) to get Area#
Look up the index number (# 113)
Find _storage-object records for index #113 (for tenant #t) to get Area#
Load into “OM cache” so we can use again (set -omsize)
Get location of the index root block from _storage-object
Traverse index B-tree to leaf, perhaps custnum = 3447
Get row's rowid 9006 from index leaf block
Get share lock on row 9006
Read data block(s) containing row fragments
Copy row fragments to 4GL buffer (SELF) or network buffer (REMC)
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Find row in database
Block’s DBKEY
Type
Next DBKEY in Chain
Num
Dirs.
Chain
Backup Ctr
Block Update Counter
Free
Dirs.
Rec 2 Offset
Free Space
Rec 0 Offset
Rec 1 Offset
Rec n Offset
Free Space
Share locked
RM block in
buffer pool
Used Data Space
Record 1
Record 2
Record 0
client's row buffer
server’s row buffer
server’s network
message buffer
39
© 2014 Progress Software Corporation. All rights reserved.
TCP/IP
client’s network
message buffer
Birth, Death, Infinity
row buffer connected
CONDB
Etc.
order
DBDES
customer
ICB for table
customer
CI for
customer
40
FDTBL for
database foo
© 2014 Progress Software Corporation. All rights reserved.
row buffer (cust 3447)
Birth, Death, Infinity
UPDATES
do transaction:
find customer
where cust-num = 3447 exclusive-lock:
customer.city = "Westford".
end.
end.
41
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Assign new value to city
4GL runtime stack
ICB for table customer
“Westford”
CI for customer
field n
Boston
row buffer (cust 3447)
42
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Assign new value to city
4GL runtime stack
ICB for table customer
“Westford”
CI for customer
field n
Westford
row buffer
43
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
when do we send it to the database ?
Remote client sends row to database
client's row buffer
client's network
message buffer
45
© 2014 Progress Software Corporation. All rights reserved.
server's row buffer
TCP/IP
server's network
message buffer
Birth, Death, Infinity
Update data block
 Acquire exclusive lock on block buffer
 Read data block into buffer pool
 Generate "record difference" bi note
 Spool bi note
 Modify data block
 Update free space
 Mark block modified
 Release buffer lock
46
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Update other data blocks
 Indexes have to be updated too (city is a key field)
 Delete existing index entry for "Boston"
 Insert new index entry for "Westford“
 Other stuff
• B-Tree might need to be rebalanced
• The addition of the new key might cause a block split
47
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Fragment chain
block header
block header
RM block header
dir entry 0
dir entry 1
dir entry 2
dir entry 3
RM block header
dir entry 0
dir entry 1
free
space
record
3
(part 1 of row)
free space
record 2
record 1
record 0
48
© 2014 Progress Software Corporation. All rights reserved.
record 1 (part 2 of row)
record 0
Birth, Death, Infinity
Death
49
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Deleting
do transaction:
for each customer where city = "Boston“ :
delete customer.
end.
end.
50
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Deleting (sql)
delete from customer where city = "Boston";
51
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Update data block
 Acquire exclusive lock on block buffer
 Read data block into buffer pool
 Generate "record delete" bi note
 Spool bi note
 Modify data block
 Update free space
 Rearrange the records in the block if necessary to keep free space
contiguous
 Mark block as modified
 Do approximately the same things for all indexes on the table (next)
 Release buffer locks
52
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Update other data blocks
 Indexes have to be updated too
 After each record delete
• Delete existing index entry for "Boston"
• Delete all the other index entries too
 For unique index entries, replace with a reservation (placeholder)
• The key value cannot be used until deleting transaction commits
 Deletes for Word Indexes….one operation per word/key
53
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
deleted ROWID Reservation
block header
RM block header
dir entry 0
dir entry 1
dir entry 2
dir entry 3
ROWID cannot be recycled
until after the deleting
transaction commits.
Why ?
free space
record 2
record 1
record 0
54
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
deleted ROWID Reservation
block header
RM block header
dir entry 0
dir entry 1
dir entry 2
dir entry 3
free space
ROWID cannot be recycled
until after the deleting
transaction commits.
Why ?
What ELSE can go wrong
during UNDO ?
record 2
record 1
record 0
55
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
What could go wrong ?
deleted ROWID Reservation
block header
RM block header
dir entry 0
dir entry 1
dir entry 2
dir entry 3
free space
ROWID cannot be recycled
until after the deleting
transaction commits.
Why ?
What ELSE can go wrong
during UNDO ?
record 2
record 1
record 0
57
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
deleted ROWID Reservation
block header
RM block header
dir entry 0
dir entry 1
dir entry 2
dir entry 3
free space
record 2
ROWID cannot be recycled
until after the deleting
transaction commits.
Why ?
What can go wrong during
UNDO ?
Not enough space. another
transaction has taken it.
record 1
record 0
58
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Infinity
How can a row live after death ?
Life after death
 Your records live on forever in:
• Backup archives
• Archived after-image extents
• Audit data
• Archived audit data
• Binary dump files
• Reports
• Dropbox
• etc.
 If you have any of these, where? Can you find a specific record?
 Is the data encrypted? Where are the keys ?
 Can we subpoena your records ? We may.
61
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
That’s all we have time for
today, except
62
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Thank You!
 Спасибо
 Danke
 dank u
 Aitäh
 Ačiū
 Kiitos
 ありがとう
63
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity
Answers
email:
[email protected]
[email protected]
64
© 2014 Progress Software Corporation. All rights reserved.
Birth, Death, Infinity