1 / 46

May 12, 2009 • 02:45 p.m. –03:45 p.m. Platform: <z/OS>

Session: <B04> A Row’s Life. Ken McDonald BMC Software. May 12, 2009 • 02:45 p.m. –03:45 p.m. Platform: <z/OS>. 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,

havyn
Download Presentation

May 12, 2009 • 02:45 p.m. –03:45 p.m. Platform: <z/OS>

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. Session: <B04> A Row’s Life Ken McDonaldBMC Software May 12, 2009 • 02:45 p.m. –03:45 p.m. Platform: <z/OS>

  2. 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 Meet Rowdy

  3. Rowdy’s DBA 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. Needs multiple meanings and a quote. Needs notes about origin. Will insert words, sometimes with minimal info. Will want to access multiple rows by letter of the alphabet. Rowdy’s Table is Defined

  4. CREATE DATABASE DBAWORDS; CREATE TABLESPACE DBAWORDS IN DBAWORDS USING STOGROUP SYSDEFLT PRIQTY 1000 SECQTY 1000 FREEPAGE 1 PCTFREE 0; Rowdy’s Table is Defined - Preliminaries

  5. 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; Rowdy’s Table is Defined

  6. 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; Rowdy’s Table is Defined - Indexes

  7. 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'); Rowdy Climbs into the Data Base

  8. 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’.

  9. Rowdy Climbs into the Data Base The Data Page *** 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 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.

  10. 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 It feels a little weird. D000BF64 * z l l l * = * l 40404040 * rowdy 95844084 * N A rough and d 000001FF *isorderly person * origin unknown

  11. 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

  12. 00006900 0301 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

  13. Rowdy’s Neighborhood Gets Crowded *** 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

  14. Rowdy gets a Quote UPDATE DBA.WORDS SET QUOTE_DATE = CURRENT_DATE, QUOTE = 'There was a rowdy in the IDUG crowd.' WHERE WORD = 'rowdy';

  15. 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

  16. 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…

  17. Mistakes were Made! UPDATE DBA.WORDS SET QUOTE_SOURCE = 'Jim Dee as quoted in a presentation to BMODUG', MEANING2 = 'Full of rows, as a relational database'; . . . . SUCCESSFUL UPDATE OF 470 ROW(S) The DBA thought he would enter this quote source and a new meaning for Rowdy, but forgot the WHERE clause. He considered just setting the columns to NULL without a WHERE clause. But some words already entered had MEANING2. So he used his favorite log tool to generate updates to put things right. However, all this expanding of rows and contracting them again put internal formats in disarray (even though the unintentional changes are corrected) .

  18. 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

  19. 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

  20. 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

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

  22. Rowdy, Rover, and Royal are together rover Here I am! *** 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

  23. Not all available pages were used *** BEGINNING OF PAGE NUMBER 00000003 *** 0000 0002930B 9FF00000 00000300 0FE80014 00000100 00000000 00000000 00000000 .... LINES ARE ALL ZERO. 0FE0 00000000 00000000 00000000 00000000 00000000 00000000 00000000 000001C5 Remember the FREEPAGE 1 option on the CREATE TABLESPACE? It finally takes effect! So page 3 was not used, and Rover, Rowdy, and Royal find themselves on page 4.

  24. More Changes to Rowdy’s Table 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; 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!

  25. 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);

  26. 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?

  27. And we gained a page... *** BEGINNING OF PAGE NUMBER 00000005 *** 0000 1002930B A1E70800 00000528 0A1E05DC 00000200 00000000 04000000 00000000 0020 00000000 0001EA00 01012060 00000000 01E40003 00000000 01540000 00000000 .... LINES ARE ALL ZERO. 0060 00000001 00000004 000004BC 00060421 00000000 00000000 00000000 00000000 0080 00000000 00000000 00000000 00000000 02930B9D 52544040 40D10000 0144000B . . . . 0580 00000000 00000000 00400000 22AB0040 D6C2E7F2 00000800 000001BF 00000000 .... LINES ARE ALL ZERO. 05C0 00000000 00000000 00000000 00034442 41000000 0005574F 52445300 00000000 .... LINES ARE ALL ZERO. 0FE0 00000000 00000000 00000000 00000000 00000000 00000000 0000020E 002400D5 The WORDS table is now versioned! Ruff is version 1, and all the other rows are version 0. Every time a row is retrieved, it must be examined and possibly reformatted to fit the current version.

  28. 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 | +---------+------------------------------------------+----------------+

  29. 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.

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

  31. 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 Hey, I’m still there!! 9996A684A8 r o w d y

  32. Logging Delete Delete (contains row) Index updates update space map

  33. 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!

  34. 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 How Could Rowdy’s Life be Different?

  35. 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. A Different Life – Column changes

  36. FREEPAGE (and PCTFREE) are only honored on REORG and LOAD, so if you only INSERT rows then the order of the inserts, not the clustering key, will determine the order. But if you happen to insert in clustering order, REORG may actually cause MORE pages to be involved in a query for a section of rows. The setting of the FREE parameters is dependent upon the DML activity expected for each table… What is the nature of INSERTs in relation to the clustering key Random INSERTs? Need higher FREEs Only increasing INSERTs with the space growing at the end? Lower FREEs Do you expect UPDATEs which will significantly change row sizes? DELETEs expected to create holes? Intelligent setting of FREEPAGE & PCTFREE reduce need to REORG A Different Life – Change in space parameters

  37. 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 A Different Life - Compression

  38. ‘rowdy’ as a non compressed row: 0000 1004DD1C 51C9D100 00002100 000D0FCD 00001201 0100C400 030198A7 83818140 ... row header r o w d y 04A0 40C2D4D6 C4E4C7FF 00000000 0300E800 03019996 A684A840 40404040 40404040 04C0 40404040 40404040 40404040 40404040 00D54040 001E00C1 409996A4 87884081 04E0 95844084 89A29699 84859993 A8409785 99A29695 002700C6 A4939340 96864099 0500 96A6A26B 4081A240 81409985 9381A389 96958193 408481A3 818281A2 850001FF 0520 81D60020 07112700 2500E388 85998540 A681A240 81409996 A684A840 899540A3 0540 888540C9 C4E4C740 839996A6 844B000F 00969989 87899540 A4959295 96A69500 0560 2E00D189 9440C485 854081A2 4098A496 A3858440 89954081 40979985 A28595A3 next row begins here 0580 81A38996 9540A396 40C2D4D6 C4E4C700 800007D0 01010300 03019996 A8819340 ... 0FC0 9640C2D4 D6C4E4C7 FF000000 00000000 00000000 00000000 00000F09 0E450D81 0FE0 0CBD0BF9 0B350A71 09AD08E9 06970594 04AC03E8 03240260 019C00D8 001400D5 On page x’21’ – row 6 X’12’ or 18 rows on the page Length of x’00E8’/d’232’ Version 01 (on a DB2 V8.1 system) Character data is readable A Different Life – Compression example

  39. ‘rowdy’ as a compressed row: 0000 1004DD24 3253C900 00001500 00090E33 0000E101 05001200 03019736 7FCED965 ... row header not recognizible 0DE0 C9B63A81 BC51B99D 4C07004A 000301AC 9C7A24C3 EF3CC7B3 39920508 90A29D17 0E00 01C90700 9981F1AB D0810D61 151C11E3 1137D7AA EC41B2CC 3FC8F930 0400C9CB row end Free Space 0E20 266A9B74 971C57D4 9CC76B82 38454B60070D1000 00000000 00000000 0DDA0DC6 ... many more entries in the PGMAP 0E20 266A9B74 971C57D4 9CC76B82 38454B60 070D1000 00000000 00000000 0DDA0DC6 ... 0F60 05CF05C4 05B905A7 05960582 044403ED 0DE903E2 03D703CC 03C103B0 039C0391 0F80 0386037B 03700361 034D0342 0337032C 031A0309 02F102E6 02DB02D0 02C502B4 0FA0 02A00295 028A027F 02740263 024F0244 0239022E 021C020B 01F701EC 01E101D6 0FC0 01CB01B6 019E0193 0188017D 01720161 0149013E 01330128 01160105 00F100E6 0FE0 00DB00D0 00C500B0 0098008D 00820077 006C005B 0047003C 00310026 001400D5 On page x’15’ – row X’37’ ’X’E1’ or 225 rows on the page Length of x’004A’/d’74’ (158 bytes less than the non-compressed row) Version 01 (on a DB2 V8.1 system) Character and all data now mangled into 3 nibble dictionary index entries A Different Life – Compression example

  40. 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). A Different Life – Reordered Row Format

  41. The column layout at the end of our test: WORD CHAR(30) NOT NULL PRIMARY KEY ,SP_TYPE CHAR(3) ,MEANING VARCHAR(250) ,MEANING2 VARCHAR(250) ,MEANING3 VARCHAR(250) ,CODENUM SMALLINT GENERATED BY DEFAULT AS IDENTITY CACHE 50,CYCLE ,QUOTE_DATE DATE ,QUOTE VARCHAR(500) ,ORIGIN_NOTES VARCHAR(250) ,QUOTE_SOURCE VARCHAR(100) ,NUM_REFERENCES INTEGER A Different Life – Reordered Row Format

  42. ‘rowdy’ in Basic Row Format (DB2 Version 8.1): 0000 1004DD1C 51C9D100 00002100 000D0FCD 00001201 0100C400 030198A7 83818140 ... row header r o w d y WORD 04A0 40C2D4D6 C4E4C7FF 00000000 0300E800 03019996 A684A840 40404040 40404040 N SP_TYP len N MEANING 04C0 40404040 40404040 40404040 40404040 00D54040 001E00C1 409996A4 87884081 len N MEANING2 04E0 95844084 89A29699 84859993 A8409785 99A29695002700C6 A4939340 96864099 (MEANING3) len N 0500 96A6A26B 4081A240 81409985 9381A389 96958193 408481A3 818281A2850001FF CODENUM N QUOTE_DT len N QUOTE 0520 81D60020 071127002500E388 85998540 A681A240 81409996 A684A840 899540A3 len N ORIGIN_NOTES len 0540 888540C9 C4E4C740 839996A6844B000F 00969989 87899540 A4959295 96A69500 N QUOTE_SOURCE 0560 2E00D189 9440C485 854081A2 4098A496 A3858440 89954081 40979985 A28595A3 N NUM_REFS next row begins here 0580 81A38996 9540A396 40C2D4D6C4E4C700 800007D0 01010300 03019996 A8819340 ... 0FC0 9640C2D4 D6C4E4C7 FF000000 00000000 00000000 00000000 00000F09 0E450D81 0FE0 0CBD0BF9 0B350A71 09AD08E9 06970594 04AC03E8 03240260 019C00D8 001400D5 A Different Life – Basic Row Format

  43. ‘rowdy’ in Reordered Row Format (DB2 Version 9.1): 0000 10007D2C 6E02FB00 00002400 000D0FCD 00001201 0100C400 030298A7 83818140 ... row header r o w d y WORD 04A0 969540A3 9640C2D4 D6C4E4C7 0300E800 03029996A684A840 40404040 40404040 N SP_TYP COD N QUOTE_DT N NUM_REFS 04C0 40404040 40404040 40404040 40404040 00D54040 81D6002007112700 800007D0 off off off off off off N MEANING1 04E0 003A0058 007F0080 00A500B4 00C14099 96A48788 40819584 408489A2 96998485 N MEANING2 0500 9993A840 978599A2 969500C6A4939340 96864099 96A6A26B 4081A240 81409985 (MEANING3) N N QUOTE 0520 9381A389 96958193 408481A3 818281A285FF00E388859985 40A681A2 40814099 N ORIGIN_NOTES 0540 96A684A8 40899540 A3888540 C9C4E4C7 40839996A6844B00 96998987 899540A4 N QUOTE_SOURCE 0560 95929596 A69500D1 899440C4 85854081 A24098A4 96A38584 40899540 81409799 next row begins here 0580 85A28595 A381A389 969540A3 9640C2D4 D6C4E4C7 01010300 03029996 A8819340 ... 0FC0 89969540 A39640C2 D4D6C4E4 C7000000 00000000 00000000 00000F09 0E450D81 0FE0 0CBD0BF9 0B350A71 09AD08E9 06970594 04AC03E8 03240260 019C00D8 001400D5 A Different Life – Reordered Row Format

  44. 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 A Different Life – LOB or XML Columns

  45. 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’ A Different Life – ASCII or UNICODE

  46. Session: <B04> A Row’s Life Ken McDonald BMC Software Ken_McDonald@bmc.com

More Related