1 / 34

Rick Weaver BMC Software

A Row’s Life. Rick Weaver BMC Software. Meet Rowdy. We’ll follow Rowdy, a row in a wordsmith’s new data base as he goes from raw data to row, gets inserted and indexed, gets logged, gets neighbors, gets updated and accessed and makes some moves, and gets affected by table version changes.

clingerman
Download Presentation

Rick Weaver BMC Software

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. A Row’s Life Rick WeaverBMC Software

  2. Meet Rowdy • We’ll follow Rowdy, a row in a wordsmith’s new data base as he • goes from raw data to row, gets inserted and indexed, • gets logged, • gets neighbors, • gets updated and accessed and makes some moves, and • gets affected by table version changes. • We will see the definitions for Rowdy’s table and other objects and his internal format and how they affect performance, recovery and security. • And finally the end will come…Rowdy gets deleted. • We will discuss how his life could have been different • Compression and Reordered Row Format examples

  3. Rowdy’s Table is Defined Rowdy’s DBA is creating a dictionary application • Wants the WORD to be a primary key. • Might like to have a random word select and thinks an identity column might be a good idea. • WORD might have multiple meanings and may need a quote. • Needs notes about origin. • Will insert words, sometimes with minimal info. • Will want to access multiple rows by letter of the alphabet.

  4. Rowdy’s Table is Defined CREATE TABLE DBA.WORDS ( WORD CHAR(30) NOT NULL PRIMARY KEY ,SP_TYPE CHAR(3) ,MEANING VARCHAR(100) ,MEANING2 VARCHAR(100) ,MEANING3 VARCHAR(100) ,CODENUM SMALLINT GENERATED BY DEFAULT AS IDENTITY ( CACHE 50,CYCLE ) ,QUOTE_DATE DATE ,QUOTE VARCHAR(500) ,ORIGIN_NOTES VARCHAR(250) ) IN DBAWORDS.DBAWORDS;

  5. Rowdy’s Table is Defined - Indexes CREATE UNIQUE INDEX DBA.WORDINDX ON DBA.WORDS (WORD) USING STOGROUP SYSDEFLT PRIQTY 500 SECQTY 500 CLUSTER; COMMIT; CREATE INDEX DBA.CODEINDX ON DBA.WORDS (CODENUM) USING STOGROUP SYSDEFLT PRIQTY 500 SECQTY 500; COMMIT;

  6. Rowdy Climbs into the Data Base INSERT INTO DBA.WORDS (WORD, SP_TYPE, MEANING, MEANING2, MEANING3, QUOTE_DATE, QUOTE, ORIGIN_NOTES) VALUES ('rowdy', 'N', 'A rough and disorderly person', NULL, NULL, NULL, NULL, 'origin unknown');

  7. Rowdy Climbs into the Data Base An Index Page *** BEGINNING OF PAGE NUMBER 00000003 *** 0000 1002930A 51D1AB00 0000037C 00000000 00000000 00044080 00010FA1 005B0000 0020 0000001E 00000002 01000001 00010000 00000000 00000000 9996A684 A8404040 0040 40404040 40404040 40404040 40404040 40404040 4040400000020100 00000000 Rowdy’s data in this index (on WORD) includes his WORD value (rowdy or X’9996A684A8’ followed by blanks) and the value used to locate his page and hence his row data: X’00000201’ (page 2, row 1)

  8. Rowdy Climbs into the Data Base The Data Page Row Header *** BEGINNING OF PAGE NUMBER 00000002 *** 0000 1002930A 51D05800 00000200 0F7F007D 00000101 02006900 03019996 A684A840 0020 40404040 40404040 40404040 40404040 40404040 40404040 00D54040 001E00C1 0040 409996A4 87884081 95844084 89A29699 84859993 A8409785 99A29695 0001FF00 0060 01FF8001 FF000000 000001FF 000F0096 99898789 9540A495 929596A695000000 .... LINES ARE ALL ZERO. 0FE0 00000000 00000000 00000000 00000000 00000000 00000000 00000000 001400D5 Rowdy Data pages identify the rows by representing them in two byte entries beginning (for row 1, Rowdy’s ID) two bytes from the end of page. The offset where the data is located is in this entry. The first 6 bytes are Row Header, then ROWDY data begins.

  9. 02930A51D058 Rowdy Appears on the Log 02930A51D058 URID(02930A51CA73) LRSN(BF643C337E37) DBID(0377) OBID(0002) PAGE(00000002) TYPE( UNDO REDO ) SUBTYPE(INSERT IN A DATA PAGE) CLR(NO) PROCNAME(DSNISMRT) *LRH* 00A90058 06000001 0E800293 0A51CA73 02930A51 D0000526 02930A51 3C337E37 0000 *LG** 00037700 02000000 02000293 0A51D000 3100 0000 00714001 00030000 00006900 03019996 A684A840 40404040 40404040 0020 40404040 40404040 40404040 00D54040 001E00C1 409996A4 87884081 0040 89A29699 84859993 A8409785 99A29695 0001FF00 01FF8001 FF000000 0060 000F0096 99898789 9540A495 929596A6 95 D000BF64 * z l l l * = * l 40404040 * rowdy 95844084 * N A rough and d 000001FF *isorderly person * origin unknown INSERT RBA

  10. Rowdy gets neighbors PGMAXID – page can hold x’18’ rows (24) *** BEGINNING OF PAGE NUMBER 00000002 *** 0000 0002930A 5C05C600 00000200 00720F5C 00001817 00006900 03019996 A684A840 0020 40404040 40404040 40404040 40404040 40404040 40404040 00D54040 001E00C1 0040 409996A4 87884081 95844084 89A29699 84859993 A8409785 99A29695 0001FF00 0060 01FF8001 FF000000 000001FF 000F0096 99898789 9540A495 929596A6 950200C3 0080 00030299 96A88193 40404040 40404040 40404040 40404040 40404040 40404040 00A0 4000C140 40003400 D6864096 99409785 99A38189 95899587 40A39640 81409289 00C0 95876B40 98A48585 956B4096 994096A3 88859940 94969581 99838800 1C00E2A4 00E0 97859989 96994089 9540A289 A9854096 994098A4 819389A3 A80001FF 8002FF00 0100 00000000 01FF0038 00D48984 84938540 C5958793 89A28840 99968981 93408699 0120 969440D6 938440C6 99859583 88408699 969440D3 81A38995 40998587 819389A2 0140 0200AB00 03038296 96958496 87879385 40404040 40404040 40404040 40404040 0160 40404040 00D54040 003800E4 95958583 85A2A281 99A86B40 E681A2A3 8586A493 0180 6B408195 84409686 A3859540 8396A495 A3859997 999684A4 83A389A5 8540A696 01A0 99920001 FF0001FF 8003FF00 00000000 01FF0037 00C39689 95858440 82A840D9 01C0 4BC84B40 D3899592 6B40F2F0 A3886083 8595A3A4 99A840C1 94859989 83819540 01E0 A28396A4 A39481A2 A3859902 01180003 048296A3 99A8A389 A2404040 40404040 0200 40404040 40404040 40404040 40404000 D5404000 4200C195 A8409686 40A58199 0220 8996A4A2 4086A495 87A4A240 968640A3 88854087 8595A4A2 408296A3 99A8A389 . . . . 0F40 00000000 0001FF00 1300C699 969440D3 81A38995 40979697 A493A4A2 00000000 .... LINES ARE ALL ZERO. 0FC0 00000000 00000000 00000000 00000F02 0E950E11 0D730D00 0CA10C45 0BA70B29 0FE0 0A9B0A23 09BC0926 089A07B5 06CC0609 051A0456 030301EB 0140007D 001400C5 rover

  11. Rowdy gets a Quote UPDATE DBA.WORDS SET QUOTE_DATE = CURRENT_DATE, QUOTE = 'There was a rowdy in the IDUG crowd.' WHERE WORD = 'rowdy'; This makes him too big for his current space

  12. Rowdy finds some Space This was the end of the page before the update... 0FC0 00000000 00000000 00000000 00000F02 0E950E11 0D730D00 0CA10C45 0BA70B29 0FE0 0A9B0A23 09BC0926 089A07B5 06CC0609 051A0456 030301EB 0140007D 001400C5 And now... *** BEGINNING OF PAGE NUMBER 00000002 *** 0000 1002930B 9D3C7800 00000200 004E0F80 00001801 0000C300 03029996 A8819340 0020 40404040 40404040 40404040 40404040 40404040 40404040 00C14040 003400D6 . . . . 0EE0 00C69996 9440D381 A3899540 979697A4 93A4A202 008D0003 019996A6 84A84040 0F00 40404040 40404040 40404040 40404040 40404040 40404000 D5404000 1E00C140 0F20 9996A487 88408195 84408489 A2969984 859993A8 40978599 A2969500 01FF0001 0F40 FF800100 20060911 002500E3 88859985 40A681A2 40814099 96A684A8 40899540 0F60 A3888540 C9C4E4C7 40839996 A6844B00 0F009699 89878995 40A49592 9596A695 .... LINES ARE ALL ZERO. 0FC0 00000000 00000000 00000000 00000E99 0E2C0DA8 0D0A0C97 0C380BDC 0B3E0AC0 0FE0 0A3209BA 095308BD 0831074C 066305A0 04B103ED 029A0182 00D70014 0EF300D5 Row Header Rowdy Rowdy is still Rowid 1 – but not the first row on the page

  13. New Columns for Rowdy’s Table • ALTER TABLE DBA.WORDS • ADD QUOTE_SOURCE VARCHAR(100); • ALTER TABLE DBA.WORDS • ADD NUM_REFERENCES SMALLINT; • A brief word about versioning • In DB2 V8, these ALTERs would not cause versioning • But in DB2 9, with RRF, they would. • Addition of fixed length column after varchar • Impacts Reordered Row Format

  14. Rowdy gets too big for his page UPDATE DBA.WORDS SET QUOTE_SOURCE = 'Jim Dee as quoted in a presentation to BMODUG', MEANING2 = 'Full of rows, as a relational database'; WHERE WORD = ‘rowdy’

  15. Gone fishing… on page 3! Where is Rowdy Now?? *** BEGINNING OF PAGE NUMBER 00000002 *** 0000 0C02930B 9DDE3400 00000200 00EC0FC1 09A0180F 50000000 03055000 00000306 0020 50000000 03075000 00000308 50000000 03095000 0000030A 50000000 030B5000 0040 00000304 0200F600 03029996 A8819340 40404040 40404040 40404040 40404040 0060 40404040 40404040 00C14040 003400D6 86409699 40978599 A3818995 89958740 0080 A3964081 40928995 876B4098 A4858595 6B409699 4096A388 85994094 96958199 . . . . 0FC0 00000000 00000000 00000000 00000913 00380836 074D0032 06A0002C 05BB0026 0FE0 04E10415 0020033A 001A0EA9 0D8D0C97 02180014 0B1109C6 013A0044 003E00C5 500000000304 – now a pointer, not a row header

  16. Where is Rowdy Now?? *** BEGINNING OF PAGE NUMBER 00000003 *** 0000 0802930B 9DE11600 00000300 07E50B20 0A550B08 80007E00 00019996 95849640 0020 40404040 40404040 40404040 40404040 40404040 40404040 00D54040 003100D4 . . . . 0180 FF801BFF 00000000 0001FF00 0A00E285 85409996 A881932000E60003049996A6 01A0 84A84040 40404040 40404040 40404040 40404040 40404040 40404000 D5404000 01C0 1E00C140 9996A487 88408195 84408489 A2969984 859993A8 40978599 A2969500 01E0 2700C6A4 93934096 86409996 A6A26B40 81A24081 40998593 81A38996 95819340 0200 8481A381 8281A285 0001FF80 01002006 09110025 00E38885 998540A6 81A24081 0220 409996A6 84A84089 9540A388 8540C9C4 E4C74083 9996A684 4B000F00 96998987 0240 899540A4 95929596 A695002E 00D18994 40C48585 4081A240 98A496A3 85844089 0260 95408140 979985A2 8595A381 A3899695 40A39640 C2D4D6C4 E4C7FF00 002200F7 . . . . 0FE0 00000000 00000000 074E0689 05D40A6F 046C0392 027D0197 09B808D3 07FC00C5 rowdy

  17. But Everyone Else Thinks Rowdy’s Still in the Old Neighborhood INSERT INTO DBA.WORDS (WORD, SP_TYPE, MEANING, ORIGIN_NOTES) VALUES ('rowdy', 'N', 'A rough and disorderly person', 'origin unknown'); ---------+---------+---------+---------+---------+---------+---------+---------+ DSNT408I SQLCODE = -803, ERROR: AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE INDEX IN INDEX SPACE WORDIND CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS X'0000000201’ Page 2 Page 3 Index rowdy ---> page 2 RID 01

  18. Rowdy hopes REORG can reunite him with Royal and Rover REORG TABLESPACE DBAWORDS.DBAWORDS SHRLEVEL NONE COPYDDN SYSCOPY rover I like this.. I’ll get to see my buddies again! And they’re taking a picture of me for posterity!

  19. Rowdy, Rover, and Royal are together rover *** BEGINNING OF PAGE NUMBER 00000004 *** 0000 0002930B 9FF05600 00000400 086C077E 00000A00 0000A600 03019196 A2A24040 . . . . 0420 0000E500 03079996A5859940 40404040 40404040 40404040 40404040 40404040 0440 40404040 00D54040 001F00D6 958540A6 88964099 96A585A2 5E40A681 95848599 0460 85995E40 95969481 84002700 C6A49393 40968640 9996A6A2 6B4081A2 40814099 0480 859381A3 89969581 93408481 A3818281 A2850023 00C19983 888599A8 4B40C140 04A0 94819992 40A28593 8583A385 844082A8 40838881 95838580 1AFF0000 00000001 04C0 FF000F00 D6998987 899540A4 95929596 A695002E 00D18994 40C48585 4081A240 04E0 98A496A3 85844089 95408140 979985A2 8595A381 A3899695 40A39640 C2D4D6C4 0500 E4C7FF00 000000E6 0003089996A684A8 40404040 40404040 40404040 40404040 0520 40404040 40404040 4000D540 40001E00 C1409996 A4878840 81958440 8489A296 0540 99848599 93A84097 8599A296 95002700 C6A49393 40968640 9996A6A2 6B4081A2 0560 40814099 859381A3 89969581 93408481 A3818281 A2850001 FF800100 20060911 0580 002500E3 88859985 40A681A2 40814099 96A684A8 40899540 A3888540 C9C4E4C7 05A0 40839996 A6844B00 0F009699 89878995 40A49592 9596A695 002E00D1 899440C4 05C0 85854081 A24098A4 96A38584 40899540 81409799 85A28595 A381A389 969540A3 05E0 9640C2D4 D6C4E4C7 FF000000 00F60003 099996A881934040 40404040 40404040 . . . . 0FE0 00000000 00000000 000006E1 05EB05050420036F 02E20242 018B00BA 001400C5 Royal Rowdy Rover

  20. More Changes to Rowdy’s Table After some experience with the WORDS table, the DBA decides that the meaning columns need to be longer. At the same time, interest is so high that the number of references column must be increased in size (causing VERSION) ALTER TABLE DBA.WORDS ALTER COLUMN MEANING SET DATA TYPE VARCHAR(250) ALTER COLUMN MEANING2 SET DATA TYPE VARCHAR(250) ALTER COLUMN MEANING3 SET DATA TYPE VARCHAR(250) ALTER COLUMN NUM_REFERENCES SET DATA TYPE INTEGER;

  21. Ruff joins the crowd INSERT INTO DBA.WORDS (WORD, SP_TYPE, MEANING, MEANING2, MEANING3, ORIGIN_NOTES, NUM_REFERENCES) VALUES ('ruff', 'N', 'A stiffly starched, frilled or pleated circular collar of lace, ' || 'muslin, or other fine fabric worn by men and women in the 16th ' || 'and 17th centuries.', 'A distinctive, collar-like projection around the neck, as of ' || 'feathers on a bird or of fur on a mammal.', 'Card Games. The playing of a trump card when one cannot ' || 'follow suit.', 'For senses one and two, short for ruffle (frill). For sense ' || 'three, Old French roffle, earlier ronfle, probably from Italian ' || 'ronfa, perhaps alteration of trionfa, "triumph," trump card, ' || 'from Latin triumphus.', 33072);

  22. But Ruff doesn’t look quite the same! *** BEGINNING OF PAGE NUMBER 00000004 *** 0000 1002930B A1F00000 00000400 061809D0 00000B01 0000A600 03019196 A2A24040 . . . . 0760 89954081 40979985 A28595A3 81A38996 9540A396 40C2D4D6 C4E4C7FF 00000302 0780 52000301 99A48686 40404040 40404040 40404040 40404040 40404040 40404040 09C0 89A49497 88A4A24B 0001FF00 80008130 00000000 00000000 00000000 00000000 .... LINES ARE ALL ZERO. 0FE0 00000000 00000000 077E06E1 05EB0505 0420036F 02E20242 018B00BA 001400D5 ruff 030252000301 01 isn’t an ID in this case. What is it? A VERSION ID!

  23. The versioned data works! SELECT WORD, MEANING, MEANING2, NUM_REFERENCES FROM DBA.WORDS WHERE WORD = 'rowdy' OR WORD = 'ruff'; +---------------------------------------------------------------------+ | WORD | MEANING ...| NUM_REFERENCES | +---------------------------------------------------------------------+ 1_| rowdy | A rough and disorderly person ...| ? | 2_| ruff | A stiffly starched, frilled or pleated...| 33072 | +---------+------------------------------------------+----------------+

  24. Versioning works, but... • Rendering older versions to the current version requires more CPU • Altering an indexed column forces REBUILD. • Updating many rows in older version causes overflow. • REORG as soon as possible.

  25. Rowdy Gets Deleted • Scraps of rowdy may still be in the database. • Rowdy’s row will be on the log. • Rowdy is still in old image copies.

  26. Scraps of rowdy *** BEGINNING OF PAGE NUMBER 00000004 *** 0000 0802930B A4891900 00000400 06FE09D0 05050B02 0000A600 03019196 A2A24040 . . . . 0500 E4C7FF00 008000E600000899 96A684A8 40404040 40404040 40404040 40404040 0520 40404040 40404040 4000D540 40001E00 C1409996 A4878840 81958440 8489A296 0540 99848599 93A84097 8599A296 95002700 C6A49393 40968640 9996A6A2 6B4081A2 0560 40814099 859381A3 89969581 93408481 A3818281 A2850001 FF800100 20060911 0580 002500E3 88859985 40A681A2 40814099 96A684A8 40899540 A3888540 C9C4E4C7 05A0 40839996 A6844B00 0F009699 89878995 40A49592 9596A695 002E00D1 899440C4 05C0 85854081 A24098A4 96A38584 40899540 81409799 85A28595 A381A389 969540A3 05E0 9640C2D4 D6C4E4C7 FF000000 00F60003 099996A8 81934040 40404040 40404040 . . . . 0FE0 00000000 00000000 077E06E1 05EB8000 0420036F 02E20242 018B00BA 001408C5 First hole in page 9996A684A8 r o w d y Hey, I’m still there!!

  27. Final Performance - The Delete 02930BA48919 URID(02930BA48366) LRSN(BF644EBCBBA1) DBID(0377) OBID(0002) PAGE(00000004) TYPE( UNDO REDO ) SUBTYPE(DELETE IN A DATA PAGE) CLR(NO) PROCNAME(DSNIDILS) *LRH* 01260034 06000001 0E800293 0BA48366 02930BA4 85390526 02930BA4 8539BF64 * 4EBCBBA1 0000 * *LG** 00037700 02000000 04000293 0BA1F000 3900 * 0000 00EE2008 00030000 0300E600 03019996A684A840 40404040 40404040 40404040 * 0020 40404040 40404040 40404040 00D54040 001E00C1 409996A4 87884081 95844084 * 0040 89A29699 84859993 A8409785 99A29695 002700C6 A4939340 96864099 96A6A26B * 0060 4081A240 81409985 9381A389 96958193 408481A3 818281A2 850001FF 80010020 * 0080 06091100 2500E388 85998540 A681A240 81409996 A684A840 899540A3 888540C9 * 00A0 C4E4C740 839996A6 844B000F 00969989 87899540 A4959295 96A69500 2E00D189 * 00C0 9440C485 854081A2 4098A496 A3858440 89954081 40979985 A28595A3 81A38996 * 00E0 9540A396 40C2D4D6 C4E4C7FF 0000 * I’m famous, they can’t get enough of me! (UNDO)

  28. How Could Rowdy’s Life be Different? • Some we’ll talk about a little • ALTER-ing of COLUMNs… either adding or increasing size • Row Versioning in DB2 V8.1 and above • Compression / Encryption? • Reordered Row Format in DB2 V9.1 • LOB or XML COLUMNs (V6.1 for LOBs, V9.1 for XML) • What if I’m an ASCII or UNICODE table? • Others we won’t • NON-PADDED INDEX • Compressed Index

  29. A Different Life – Column changes • When VARCHARs are expanded later or columns are added, rows grow and may move off the original page and can actually take a extra read page to get a single row. REORG or design changes can avoid this to some degree (avoiding VARCHAR, avoiding adding columns later or REORG after adding them). • Versioning to increase column sizes has the same issues, plus conversion overhead during retrieval. • Certain ALTERs which did not Version a row in V8.1 will now induce Versioning due to Reordered Row Format in V9.1 • Example of Versioning discussed in the main flow of the presentation.

  30. A Different Life - Compression • If Rowdy and his friends were compressed then • it would be harder to read the many images of the rows on logs, spaces and copies (which might provide a small measure of data security… but the data dictionary used for compression is contained within the same space or image copy) • the variable nature of the compressed results might cause more overflow • more rows would probably fit on a page (reducing the number of pages read for certain queries) • Additional ENCRYPTION is possible • For image copies destined for offsite transport • DB2 has some native encryption capabilities • Hardware and Software (hardware independent) solutions are available

  31. A Different Life – Reordered Row Format • BRF (Basic Row Format) only option prior to DB2 9.1 • Columns appear in logical order (e.g. COL1, COL2, etc.) • Length attributes of VAR columns included with the column • RRF (Reordered Row Format) introduced in Version 9.1 of DB2 • All fixed columns appear in logical order at the beginning of the row • Followed by an array of offsets to the beginning of each VAR column (from beginning of data) • All VAR columns appear in logical order after the offset array • Length attribute not included, it must be calculated via the difference between its offset and the subsequent offset or the length of the row for the last column. • All newly created tables are in RRF • Migrated Spaces (Partitions) converted on first REORG or LOAD REPLACE (with caveats… see a Version 9 presentation).

  32. A Different Life – LOB or XML Columns • LOB and XML Column differences • No longer just the base TABLESPACE (partitions) • An additional space per LOB/XML column per part (LOB only) exists to hold the column data • Additional indexes for AUXILLARY parts too • Additional column (ROWID for LOB, DOCID for XML) in base table to reference • Base table has a reference for each LOB/XML column used to index into LOB/XML parts • Implicates more logging (even if LOG NO) • Implicates more I/O to fetch a single row • Utilities (e.g. COPY) need to consider all related spaces

  33. A Different Life – ASCII or UNICODE • All examples so far have been on an EBCDIC table • What if DBAWORDS was defined as ASCII or UNICODE? • Really, no physical difference… only character code points change • Basic Latin character set for UNICODE same as ASCII • Encoding scheme and CCSID used to store data would be different • CHAR and VARCHAR data would be in the appropriate CCSIDs • SORT sequence is different for EBCDIC than ASCII/UNICODE • EBCDIC – Lower case, Upper case, Numeric • UNICODE – Numeric, Upper case, Lower case • Are you as familiar with ASCII code points as you are with EBCDIC? e.g: • c’a’ = x’81’ EBCDIC and x’61’ ASCII • c’N’ = x’D5’ EBCDIC and x’4E’ ASCII • c’1’ = x’F1’ EBCDIC and x’31’ ASCII • c’rowdy’ or x’9996A684A8’ becomes x’726F776479’

  34. A Row’s Life Ken McDonald BMC Software Rick_Weaver@bmc.com

More Related