birth, death, infinity gus björklund.??. dan foreman. bravepoint. pug challenge dusseldorf 2014
TRANSCRIPT
© 2014 Progress Software Corporation. All rights reserved.3 Birth, Death, Infinity
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
© 2014 Progress Software Corporation. All rights reserved.5 Birth, Death, Infinity
Dan Foreman – C.V.
My New Wheels(so I won’t fall over again)
© 2014 Progress Software Corporation. All rights reserved.6 Birth, Death, Infinity
Audience Intro
Longest Progress User?
Progress version?
• V6
• V7
• V8
• V9
• V10
• V11
© 2014 Progress Software Corporation. All rights reserved.9 Birth, Death, Infinity
A Table is defined
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";
Methods for defining a new table:
© 2014 Progress Software Corporation. All rights reserved.11 Birth, Death, Infinity
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.
© 2014 Progress Software Corporation. All rights reserved.12 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.
© 2014 Progress Software Corporation. All rights reserved.13 Birth, Death, Infinity
Index definitions
_file _index _index-field
_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
_index
© 2014 Progress Software Corporation. All rights reserved.14 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.
© 2014 Progress Software Corporation. All rights reserved.16 Birth, Death, Infinity
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
© 2014 Progress Software Corporation. All rights reserved.18 Birth, Death, Infinity
CREATE a new row in a 4GL program
create customer.
© 2014 Progress Software Corporation. All rights reserved.19 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.
© 2014 Progress Software Corporation. All rights reserved.20 Birth, Death, Infinity
Where does customer row go?
CONDB
customer
orderEtc.
DBDES
CI for customer
FDTBL fordatabase foo
ICB for tablecustomer
row buffer
© 2014 Progress Software Corporation. All rights reserved.21 Birth, Death, Infinity
assign column values
Address Address2 City Contact Curr-bal Cust-num Discount Max-credit Mnth-sales Name
Phone Sales-region Sales-rep St Tax-no Terms Ytd-sls Zip
© 2014 Progress Software Corporation. All rights reserved.23 Birth, Death, Infinity
database accelerator
© 2014 Progress Software Corporation. All rights reserved.24 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
© 2014 Progress Software Corporation. All rights reserved.25 Birth, Death, Infinity
Remote client sends row to database
client's row buffer
server's networkmessage buffer
server's row buffer
client's networkmessage buffer
TCP/IP
© 2014 Progress Software Corporation. All rights reserved.27 Birth, Death, Infinity
Database Pages (Blocks)
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.28 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 frontierhttp://communities.progress.com/pcom/docs/DOC-107729
© 2014 Progress Software Corporation. All rights reserved.29 Birth, Death, Infinity
Find data block, read into database buffer pool
HashTable
LRU Chain “oldest (LRU)”“newest (MRU)”
Page Writer Queue
M MM
M M
M MMM
Checkpoint Queue
© 2014 Progress Software Corporation. All rights reserved.30 Birth, Death, Infinity
Update data block
Acquire exclusive lock on buffer Generate "create" BI note Spool bi note 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
note: simplified.
some details omitted to protect the innocent
© 2014 Progress Software Corporation. All rights reserved.31 Birth, Death, Infinity
Record In A Row Data Block (aka RM Block)
block header
RM block header
dir entry 0
free space
record 0
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
record 1record 2
record 3
dir entry 1dir entry 2dir entry 3
© 2014 Progress Software Corporation. All rights reserved.32 Birth, Death, Infinity
Birth Alternatives
© 2014 Progress Software Corporation. All rights reserved.33 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
© 2014 Progress Software Corporation. All rights reserved.34 Birth, Death, Infinity
Possible Problems
© 2014 Progress Software Corporation. All rights reserved.35 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
© 2014 Progress Software Corporation. All rights reserved.37 Birth, Death, Infinity
READ
find customer where custnum = 3447.
© 2014 Progress Software Corporation. All rights reserved.38 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
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.39 Birth, Death, Infinity
Find row in database
Block’s DBKEY Type Chain Backup Ctr
Next DBKEY in Chain Block Update Counter
Free SpaceFree
Dirs.Rec 0 Offset Rec 1 Offset
Rec 2 Offset Rec n Offset
Num
Dirs.
Free Space
Used Data Space
Record 0
Record 2
Record 1
server’s row buffer
Share locked RM block in buffer pool
client’s networkmessage buffer
client's row buffer
server’s networkmessage buffer
TCP/IP
© 2014 Progress Software Corporation. All rights reserved.40 Birth, Death, Infinity
row buffer connected
CONDB
customer
orderEtc.
DBDES
CI for customer
FDTBL fordatabase foo
ICB for tablecustomer
row buffer (cust 3447)
© 2014 Progress Software Corporation. All rights reserved.41 Birth, Death, Infinity
UPDATES
do transaction:
find customer where cust-num = 3447 exclusive-lock:
customer.city = "Westford".
end.end.
© 2014 Progress Software Corporation. All rights reserved.42 Birth, Death, Infinity
Assign new value to city
“Westford”
4GL runtime stack
row buffer (cust 3447)
Boston
ICB for table customer
CI for customer
field n
© 2014 Progress Software Corporation. All rights reserved.43 Birth, Death, Infinity
Assign new value to city
“Westford”
4GL runtime stack
row buffer
Westford
ICB for table customer
CI for customer
field n
© 2014 Progress Software Corporation. All rights reserved.45 Birth, Death, Infinity
Remote client sends row to database
client's row buffer
server's networkmessage buffer
server's row buffer
client's networkmessage buffer
TCP/IP
© 2014 Progress Software Corporation. All rights reserved.46 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
© 2014 Progress Software Corporation. All rights reserved.47 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
© 2014 Progress Software Corporation. All rights reserved.48 Birth, Death, Infinity
Fragment chain
block header
RM block header
dir entry 0
free space
record 0record 1record 2
record 3(part 1 of row)
dir entry 1dir entry 2dir entry 3
block header
RM block header
dir entry 0
free space
record 0record 1 (part 2 of row)
dir entry 1
© 2014 Progress Software Corporation. All rights reserved.50 Birth, Death, Infinity
Deleting
do transaction:
for each customer where city = "Boston“ :
delete customer.
end.end.
© 2014 Progress Software Corporation. All rights reserved.51 Birth, Death, Infinity
Deleting (sql)
delete from customer where city = "Boston";
© 2014 Progress Software Corporation. All rights reserved.52 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
© 2014 Progress Software Corporation. All rights reserved.53 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
© 2014 Progress Software Corporation. All rights reserved.54 Birth, Death, Infinity
deleted ROWID Reservation
block header
RM block header
dir entry 0
free space
record 0record 1record 2
dir entry 1dir entry 2dir entry 3
ROWID cannot be recycled until after the deleting transaction commits.
Why ?
© 2014 Progress Software Corporation. All rights reserved.55 Birth, Death, Infinity
deleted ROWID Reservation
block header
RM block header
dir entry 0
free space
record 0record 1record 2
dir entry 1dir entry 2dir entry 3
ROWID cannot be recycled until after the deleting transaction commits.
Why ?
What ELSE can go wrong during UNDO ?
© 2014 Progress Software Corporation. All rights reserved.57 Birth, Death, Infinity
deleted ROWID Reservation
block header
RM block header
dir entry 0
free space
record 0record 1record 2
dir entry 1dir entry 2dir entry 3
ROWID cannot be recycled until after the deleting transaction commits.
Why ?
What ELSE can go wrong during UNDO ?
© 2014 Progress Software Corporation. All rights reserved.58 Birth, Death, Infinity
deleted ROWID Reservation
block header
RM block header
dir entry 0
free space
record 0record 1
record 2
dir entry 1dir entry 2dir entry 3
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.
© 2014 Progress Software Corporation. All rights reserved.61 Birth, Death, Infinity
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.
© 2014 Progress Software Corporation. All rights reserved.62 Birth, Death, Infinity
That’s all we have time for today, except
© 2014 Progress Software Corporation. All rights reserved.63 Birth, Death, Infinity
Thank You!
Спасибо
Danke
dank u
Aitäh
Ačiū
Kiitos
ありがとう
© 2014 Progress Software Corporation. All rights reserved.64 Birth, Death, Infinity
Answersemail: