1. ๋ฌธ์„œ์˜ ๋ชฉ์ 

์ด ๋ฌธ์„œ๋Š” 2016๋…„ S๊ณ ๊ฐ์‚ฌ ERP ํ”„๋กœ์ ํŠธ์—์„œ ์ˆ˜ํ–‰ํ•œ ์ฃผ์š” ์„ฑ๋Šฅ ๊ฐœ์„  ํ™œ๋™ ๋‚ด์šฉ์— ๋Œ€ํ•ด ๊ธฐ์ˆ ํ•˜์˜€๋‹ค.
๋‹น์‹œ S๊ณ ๊ฐ์‚ฌ ERP ํ”„๋กœ์ ํŠธ๋Š” pureScale ํ™˜๊ฒฝ์„ ์ฒ˜์Œ์œผ๋กœ ์‚ฌ์šฉํ•˜๋ฉด์„œ Migration ์„ฑ๋Šฅ ์ด์Šˆ๋กœ ํฐ ์–ด๋ ค์›€์„ ๊ฒช๊ณ  ์žˆ์—ˆ๋‹ค. ์ˆ˜๊ฐœ์›” ๊ฐ„์˜ ๋…ธ๋ ฅ ๋์— ์„ฑ๋Šฅ ์ด์Šˆ๋ฅผ ํ•ด๊ฒฐํ•  ์ˆ˜ ์žˆ๋Š” ๋ฐฉ์•ˆ์„ ์ฐพ์•„๋‚ด์—ˆ๊ณ , ๊ทธ ๋‚ด์šฉ์„ ๋ณธ ๋ฌธ์„œ๋กœ ์ •๋ฆฌํ•˜์˜€๋‹ค.
ํ–ฅํ›„ ๋น„์Šทํ•œ DB2 purescale ํ™˜๊ฒฝ์—์„œ ์„ฑ๋Šฅ ์ €ํ•˜ ๋“ฑ์˜ ์ด์Šˆ ๋ฐœ์ƒ ์‹œ ์ด ๋ฌธ์„œ์˜ ๋‚ด์šฉ์„ ํ†ตํ•ด ์„ฑ๋Šฅ ์ด์Šˆ๋ฅผ ์กฐ๊ธฐ์— ํ•ด๊ฒฐํ•˜์—ฌ ์‹œ์Šคํ…œ์˜ ์•ˆ์ •์  ์šด์˜์— ๋„์›€์ด ๋˜๋Š” ๊ฒƒ์ด ์ด ๋ฌธ์„œ์˜ ๋ชฉ์ ์ด๋‹ค.

๋‹ค์Œ๊ณผ ๊ฐ™์€ ์ˆœ์„œ๋กœ 3ํšŒ์— ๊ฑธ์ณ SAP on DB2 pureScale ํ™˜๊ฒฝ์—์„œ DB ์„ฑ๋Šฅ ๊ฐœ์„ ์„ ์œ„ํ•œ ์‚ฌ๋ก€๋ฅผ ๊ณต์œ ํ•˜๊ณ ์ž ํ•œ๋‹ค.

1) DB2 purescale ์„ฑ๋Šฅ ๊ฐœ์„  ์‚ฌ๋ก€ ๊ณต์œ  – 1. ํ…Œ์ด๋ธ” ํŒŒํ‹ฐ์…˜

2) DB2 purescale ์„ฑ๋Šฅ ๊ฐœ์„  ์‚ฌ๋ก€ ๊ณต์œ  – 2. LOB Inline, Extent Size, Append Mode

3) DB2 purescale ์„ฑ๋Šฅ ๊ฐœ์„  ์‚ฌ๋ก€ ๊ณต์œ  – 3. CF ์„ฑ๋Šฅ๊ฐœ์„ , ์œ ํ‹ธ๋ฆฌํ‹ฐ

 

2. DB ์„ฑ๋Šฅ ๊ฐœ์„ 

Migration ์ž‘์—… ์‹œ ์„ฑ๋Šฅ ํ–ฅ์ƒ์„ ์œ„ํ•ด pureScale ์˜ 2๊ฐœ ๋ฉค๋ฒ„๋ฅผ ๋ชจ๋‘ ์‚ฌ์šฉํ•˜๋ฉด์„œ ์ž‘์—…์˜ ๋ณ‘๋ ฌ๋„๋ฅผ ๋†’์—ฌ ์ˆ˜ํ–‰ํ•˜๋Š” ๊ฒฝ์šฐ, ์—ฌ๋Ÿฌ ํ…Œ์ด๋ธ”์—์„œ Insert / Merge ๋ฌธ์žฅ์— ๋Œ€ํ•œ ์„ฑ๋Šฅ ์ €ํ•˜๊ฐ€ ๋ฐœ์ƒํ•˜์˜€๋‹ค. ์ด๋Ÿฌํ•œ ์„ฑ๋Šฅ ์ด์Šˆ์˜ ํ™•์ธ ๋ฐ ํ•ด๊ฒฐ์ฑ…์œผ๋กœ ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๊ธฐ๋ฒ•๋“ค์„ ํ™œ์šฉํ•˜์˜€๋‹ค.

2.1. Table Partition

๋™์ผ ํ…Œ์ด๋ธ”์— ๋Œ€๋Ÿ‰์˜ ๋ฐ์ดํ„ฐ๊ฐ€ ๋™์‹œ์— ์ ์žฌ๋  ๊ฒฝ์šฐ, ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ ํŽ˜์ด์ง€ ๋˜๋Š” ์ธ๋ฑ์Šค ํŽ˜์ด์ง€์— ๋Œ€ํ•œ ๊ฒฝํ•ฉ์œผ๋กœ ์„ฑ๋Šฅ ์ €ํ•˜๊ฐ€ ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ๋‹ค.
์ด ๊ฒฝ์šฐ ๋™์‹œ์— ์—ฌ๋Ÿฌ ์„ธ์…˜์ด ์‚ฌ์šฉํ•˜๊ณ ์ž ๊ฒฝํ•ฉ์ด ๋ฐœ์ƒํ•˜๋Š” ํŽ˜์ด์ง€๋ฅผ hot page ๋ผ๊ณ  ๋ถ€๋ฅด๋Š”๋ฐ, ์ด๋ฅผ ํ•ด์†Œํ•˜๊ธฐ ์œ„ํ•ด Table Partition ์ ์šฉ์„ ๊ฒ€ํ† ํ•˜์˜€๋‹ค.
Table Partition ์˜ ๋ฐฉ๋ฒ•์œผ๋กœ๋Š” HASH ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜๋Š” ๋ฐฉ๋ฒ•๊ณผ, Current Member ๋ฅผ ํ™œ์šฉํ•˜๋Š” ๋‘ ๊ฐ€์ง€ ๋ฐฉ๋ฒ•์ด ์žˆ๋‹ค

2.1.1. Hash Partition

Migration ์ค‘ INSERT / MERGE ์„ฑ๋Šฅ ์ด์Šˆ๊ฐ€ ๋ฐœ์ƒํ•˜๋Š” ๋Œ€๋‹ค์ˆ˜์˜ ํ…Œ์ด๋ธ”์˜ ๊ฒฝ์šฐ, HOT PAGE ํ•ด์†Œ๋ฅผ ์œ„ํ•œ ๋ฐฉ๋ฒ•์œผ๋กœ HASH ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•œ PARTITION KEY ๋ฅผ ์ ์šฉํ•˜๋Š” ๊ธฐ๋ฒ•์„ ์‚ฌ์šฉํ•˜์˜€๋‹ค.

์ด ๋ฐฉ๋ฒ•์€ Oracle ์˜ HASH PARTITION ๊ธฐ๋ฒ•์„ ์ฐจ์šฉํ•œ ๊ฒƒ์œผ๋กœ, ์‹ค์ œ DB2 ์—๋Š” v11 ๋ถ€ํ„ฐ ์ œ๊ณต๋˜๋Š” HASH4 ํ•จ์ˆ˜๋ฅผ, IBM Toronto Lab. ์— ์š”์ฒญํ•˜์—ฌ v10.5 ์˜ Special Build ํ˜•ํƒœ๋กœ ์ œ๊ณต๋ฐ›์•„ ์‚ฌ์šฉํ•˜๊ณ  ์žˆ๋‹ค.

Hash Partition ์€ ๋™์ผํ•œ ํŽ˜์ด์ง€์— ์ง‘์ค‘์ ์œผ๋กœ ๋“ค์–ด์˜ค๋Š” ๋ฐ์ดํ„ฐ๋ฅผ, ํ•ด๋‹น ๋ฐ์ดํ„ฐ์—์„œ ์ค‘๋ณต์ด ๊ฐ€์žฅ ์ ์€ ์ปฌ๋Ÿผ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ง€๊ณ  HASH4 ํ•จ์ˆ˜๋ฅผ ์ˆ˜ํ–‰ํ•œ ๊ฐ’์œผ๋กœ 63๊ฐœ ํŒŒํ‹ฐ์…˜์œผ๋กœ ๋ถ„์‚ฐ INSERT ๋˜๊ฒŒ ํ•จ์œผ๋กœ์จ, ๊ฒฝํ•ฉ์˜ ์ˆ˜์ค€์„ ๋‚ฎ์ถ”๋Š” ๋ชฉ์ ์œผ๋กœ ์ ์šฉ๋˜์—ˆ๋‹ค.

HASH4 ํ•จ์ˆ˜์˜ ์‚ฌ์šฉ๋ฌธ๋ฒ•์€ HASH4( ์ปฌ๋Ÿผ๋ช…, ์•Œ๊ณ ๋ฆฌ์ฆ˜ )์ด๋‹ค.

HASH4 ํ•จ์ˆ˜์— ์‚ฌ์šฉ๋  ์ปฌ๋Ÿผ์˜ ์„ ํƒ ๊ธฐ์ค€์€ PK ์ปฌ๋Ÿผ ์ค‘์—์„œ COLCARD ๊ฐ€ ๊ฐ€์žฅ ๋†’์€ ์ปฌ๋Ÿผ์„ ์„ ํƒํ•˜๋„๋ก ํ•œ๋‹ค. ์•Œ๊ณ ๋ฆฌ๋“ฌ์€ IBM LAB ์—์„œ ๊ถŒ๊ณ ํ•œ 1 ์„ ์‚ฌ์šฉํ–ˆ๋‹ค.

HASH4 ํ•จ์ˆ˜๋ฅผ ์ˆ˜ํ–‰ํ•œ ๊ฒฐ๊ณผ์˜ ๊ณ ์œ ํ•œ ๊ฐ’์ด 232 ๊ฐ€์ง€(-231~231 )๊ฐ€ ๋‚˜์˜ค๊ฒŒ ๋˜๋Š”๋ฐ, ์ด๋ฅผ ์ ๋‹นํ•œ ๋ฒ”์œ„๋กœ ๋ถ„๋ฅ˜ํ•˜๊ธฐ ์œ„ํ•ด MOD ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•œ๋‹ค. MOD ํ•จ์ˆ˜์— ์‚ฌ์šฉํ•˜๋Š” ๊ฐ’์„ ํ†ตํ•ด ํŒŒํ‹ฐ์…˜ ์ˆ˜๋ฅผ ์กฐ์ •ํ•œ๋‹ค. ํ˜„์žฌ ๊ตฌ์„ฑ๋œ 63๊ฐœ(-31~+31) ํŒŒํ‹ฐ์…˜์€ IBM ์—์„œ ๊ถŒ์žฅํ•œ ํŒŒํ‹ฐ์…˜ ๊ฐœ์ˆ˜๋ฅผ ๋”ฐ๋ฅธ ๊ฒƒ์ด๋‹ค.

ํŒŒํ‹ฐ์…˜๋ณ„ ๋ฐ์ดํ„ฐ ๋ถ„ํฌ๋Š” ์ด๋ก ์ƒ ๋Œ€๋ถ€๋ถ„์˜ ํŒŒํ‹ฐ์…˜์ด ๋น„์Šทํ•œ ๊ฑด์ˆ˜๋ฅผ ๊ฐ€์ง€๊ณ , 31๋ฒˆ์งธ ํŒŒํ‹ฐ์…˜ (SAPDB6_GC ๊ฐ’์ด 0) ์€ ๋‹ค๋ฅธ ํŒŒํ‹ฐ์…˜์˜ 2๋ฐฐ์ •๋„ ๊ฑด์ˆ˜๋ฅผ ๊ฐ€์ง€๊ฒŒ ๋œ๋‹ค. 31๋ฒˆ์งธ ํŒŒํ‹ฐ์…˜์˜ ๊ฑด์ˆ˜๊ฐ€ 2๋ฐฐ์ •๋„ ๋˜๋Š” ๊ฒƒ์€ HASH4 ํ•จ์ˆ˜ ๊ฒฐ๊ณผ ๊ฐ’์„ 32๋กœ ๋‚˜๋ˆˆ ๋‚˜๋จธ์ง€์ด๋ฏ€๋กœ ์–‘์ˆ˜ ๋ถ€๋ถ„์—์„œ ๋‚˜๋จธ์ง€๊ฐ€ 0์ธ๊ฒƒ๊ณผ ์Œ์ˆ˜ ๋ถ€๋ถ„์—์„œ ๋‚˜๋จธ์ง€๊ฐ€ 0์ธ ๊ฒƒ์ด ํ•ฉ์ณ ์ง€๊ฒŒ ๋˜๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค.

์‹ค์ œ ํŒŒํ‹ฐ์…˜ ํ‚ค๋กœ ์‚ฌ์šฉ๋  ์ปฌ๋Ÿผ์€ SAPDB6_GC ๋ผ๊ณ  ํ•˜๋Š” HIDDEN COLUMN (DB2 ์—์„œ ์ œ๊ณตํ•˜๋Š” ์ปฌ๋Ÿผ์˜ ํ•œ ํ˜•ํƒœ๋กœ, ์‹ค์ œ ํ…Œ์ด๋ธ”์—๋Š” ์กด์žฌํ•˜๋‚˜ ๊ทธ ์ปฌ๋Ÿผ ๋ช…์„ ๋ช…์‹œ์ ์œผ๋กœ ์ฟผ๋ฆฌ์— ์‚ฌ์šฉํ•˜์ง€ ์•Š์œผ๋ฉด SELECT * ๋กœ ์กฐํšŒํ•ด๋„ ์ถœ๋ ฅ๋˜์ง€ ์•Š์Œ) ์œผ๋กœ ์ถ”๊ฐ€ํ•˜์˜€๋‹ค.

์•„์šธ๋Ÿฌ ์ธ๋ฑ์Šค์—๋„ HOT PAGE ๊ฐ€ ๋ฐœ์ƒํ•˜์ง€ ์•Š๋„๋ก, SAPDB6_GC ์ปฌ๋Ÿผ์„ PK ์—๋„ ์ถ”๊ฐ€ํ•˜์—ฌ ๋ชจ๋“  ์ธ๋ฑ์Šค๊ฐ€ LOCAL INDEX ๋กœ ์ƒ์„ฑ๋˜๋„๋ก ํ•˜์˜€๋‹ค. PK ์— ์ถ”๊ฐ€๋˜๊ธฐ ์œ„ํ•ด NOT NULL ์†์„ฑ์ด ์ง€์ •๋˜์—ˆ๋‹ค.

์ปฌ๋Ÿผ๋ช…์„ SAPDB6_GC ๋กœ ํ•œ ๊ฒƒ์€, SAP ์—์„œ ํžˆ๋“  ์ปฌ๋Ÿผ์œผ๋กœ ์ถ”๊ฐ€ํ•˜๋Š” ๊ฒฝ์šฐ ์ปฌ๋Ÿผ ๋ช…์„ SAPDB6_ ์ ‘๋‘์–ด๋ฅผ ๋ถ™์ด๋ผ๊ณ  ํ•œ SAP Note 1701181 ์— ๋”ฐ๋ฅธ ๊ฒƒ์ด๋ฉฐ, GC ๋Š” Generated Column ์˜ ์•ฝ์–ด์ด๋‹ค.

์‹ค์ œ DDL ์— ์‚ฌ์šฉ๋œ HASH PARTITION ์˜ ์˜ˆ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

# HASH ํŒŒํ‹ฐ์…˜ ํ…Œ์ด๋ธ” ์ƒ์„ฑ DDL

CREATE TABLE โ€œ์Šคํ‚ค๋งˆโ€.โ€ํ…Œ์ด๋ธ”๋ช…โ€ (

โ€œCLIENTโ€ VARCHAR(9 OCTETS) NOT NULL WITH DEFAULT โ€˜000โ€˜,

โ€œPOLICY_IDโ€ VARCHAR(66 OCTETS) NOT NULL WITH DEFAULT โ€˜ โ€˜,

…์ค‘๋žต

โ€œSAPDB6_GCโ€ INTEGER NOT NULL IMPLICITLY HIDDEN

GENERATED ALWAYS AS (MOD(HASH4(RTRIM(POLICY_ID),1)),32)

COMPRESS YES ADAPTIVE

VALUES COMPRESSION

PARTITION BY RANGE(โ€œSAPDB6_GCโ€)

{ PART โ€œPART00โ€ STARTING(-31) ENDING(-31) IN โ€œZPART_G3_TS_00โ€ INDEX IN โ€œZPART_G3_IS_00โ€,

ย PART โ€œPART01โ€ STARTING(-30) ENDING(-30) IN โ€œZPART_G3_TS_00โ€ INDEX IN โ€œZPART_G3_IS_00โ€,

PART โ€œPART02โ€ STARTING(-29) ENDING(-29) IN โ€œZPART_G3_TS_00โ€ INDEX IN โ€œZPART_G3_IS_00โ€,

PART โ€œPART03โ€ STARTING(-28) ENDING(-28) IN โ€œZPART_G3_TS_00โ€ INDEX IN โ€œZPART_G3_IS_00โ€,

PART โ€œPART04โ€ STARTING(-27) ENDING(-27) IN โ€œZPART_G3_TS_01โ€ INDEX IN โ€œZPART_G3_IS_01โ€,

PART โ€œPART05โ€ STARTING(-26) ENDING(-26) IN โ€œZPART_G3_TS_01โ€ INDEX IN โ€œZPART_G3_IS_01โ€,

PART โ€œPART06โ€ STARTING(-25) ENDING(-25) IN โ€œZPART_G3_TS_01โ€ INDEX IN โ€œZPART_G3_IS_01โ€,

PART โ€œPART07โ€ STARTING(-24) ENDING(-24) IN โ€œZPART_G3_TS_01โ€ INDEX IN โ€œZPART_G3_IS_01โ€,

PART โ€œPART08โ€ STARTING(-23) ENDING(-23) IN โ€œZPART_G3_TS_02โ€ INDEX IN โ€œZPART_G3_IS_02โ€,

์ค‘๋žต

PART โ€œPART55โ€ STARTING(24) ENDING(24) IN โ€œZPART_G3_TS_13โ€ INDEX IN โ€œZPART_G3_IS_13โ€,

PART โ€œPART56โ€ STARTING(25) ENDING(25) IN โ€œZPART_G3_TS_14โ€ INDEX IN โ€œZPART_G3_IS_14โ€,

PART โ€œPART57โ€ STARTING(26) ENDING(26) IN โ€œZPART_G3_TS_14โ€ INDEX IN โ€œZPART_G3_IS_14โ€,

PART โ€œPART58โ€ STARTING(27) ENDING(27) IN โ€œZPART_G3_TS_14โ€ INDEX IN โ€œZPART_G3_IS_14โ€,

PART โ€œPART59โ€ STARTING(28) ENDING(28) IN โ€œZPART_G3_TS_14โ€ INDEX IN โ€œZPART_G3_IS_14โ€,

PART โ€œPART60โ€ STARTING(29) ENDING(29) IN โ€œZPART_G3_TS_15โ€ INDEX IN โ€œZPART_G3_IS_15โ€,

PART โ€œPART61โ€ STARTING(30) ENDING(30) IN โ€œZPART_G3_TS_15โ€ INDEX IN โ€œZPART_G3_IS_15โ€,

PART โ€œPART62โ€ STARTING(31) ENDING(31) IN โ€œZPART_G3_TS_15โ€ INDEX IN โ€œZPART_G3_IS_15โ€)

ORGANIZE BY ROW;

 

CREATE UNIQUE INDEX โ€œ์Šคํ‚ค๋งˆโ€.โ€์ธ๋ฑ์Šค๋ช…โ€ ON โ€œ์Šคํ‚ค๋งˆโ€.โ€ํ…Œ์ด๋ธ”๋ช…โ€

(โ€œCLIENTโ€ ASC,

โ€œPOLICY_IDโ€ ASC,

…์ค‘๋žต

โ€œSAPDB6_GCโ€ ASC)

ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย  PARTITIONED

PCTFREE 0

COMPRESS YES

INCLUDE NULL KEYS ALLOW REVERSE SCANS;

 

ALTER TABLE โ€œ์Šคํ‚ค๋งˆโ€.โ€ํ…Œ์ด๋ธ”๋ช…โ€

ADD CONSTRAINT โ€œPK-constraint๋ช…โ€ PRIMARY KEY

(โ€œCLIENTโ€,

โ€œPOLICY_IDโ€,

…์ค‘๋žต

โ€œSAPDB6_GCโ€);

 

… ํ›„๋žต

<๊ทธ๋ฆผ 1-1 Hash Partition Table DDL ์˜ˆ์ œ>

 

2.1.2. Current Member Partition

์ผ๋ถ€ ํ…Œ์ด๋ธ”์€ Hash Partition ์ ์šฉ์—๋„ INSERT / MERGE ์„ฑ๋Šฅ์ด ํฌ๊ฒŒ ๊ฐœ์„ ๋˜์ง€ ์•ˆ์•˜๋‹ค.

์ด๋“ค์€ ์ฃผ๋กœ INDEX PAGE RECLAIM ์ด ์„ฑ๋Šฅ ์ €ํ•˜์˜ ์›์ธ์œผ๋กœ ์ž‘์šฉํ–ˆ์œผ๋ฉฐ, RECLAIM ์ด ๋ฐœ์ƒํ•œ INDEX ๋ฅผ ๊ตฌ์„ฑํ•˜๋Š” KEY COLUMN ์˜ CARDINALITY ๊ฐ€ ๋†’์ง€ ์•Š์€ ๊ฒƒ์ด ํŠน์ง•์ด์—ˆ๋‹ค. INDEX PAGE RECLAIM ์„ ํ•ด์†Œํ•  ๋ชฉ์ ์œผ๋กœ ๊ฐ ๋ฉค๋ฒ„ ๋ณ„๋กœ ํŽ˜์ด์ง€๋ฅผ ๋ณ„๋„๋กœ ํ• ๋‹น ๋ฐ›์•„์„œ ๋“ค์–ด์˜ค๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ์ฒ˜๋ฆฌํ•˜๋„๋ก ํ•˜๊ธฐ๋กœ ํ–ˆ๋‹ค. ์ด๋ฅผ ์œ„ํ•ด SAPDB6_CURMEM ์ด๋ผ๋Š” HIDDEN ์ปฌ๋Ÿผ์„ ์ถ”๊ฐ€ํ•˜๊ณ  ๊ธฐ๋ณธ๊ฐ’์œผ๋กœ CURRENT MEMBER ๋ฅผ ์ง€์ •ํ•˜์—ฌ 0๋ฒˆ ๋ฉค๋ฒ„๋กœ ๋“ค์–ด์˜ค๋Š” ๋ฐ์ดํ„ฐ๋Š” 0๋ฒˆ ํŒŒํ‹ฐ์…˜์œผ๋กœ, 1๋ฒˆ ๋ฉค๋ฒ„๋กœ ๋“ค์–ด์˜ค๋Š” ๋ฐ์ดํ„ฐ๋Š” 1๋ฒˆ ํŒŒํ‹ฐ์…˜์œผ๋กœ ์ ์žฌํ•˜๋„๋ก ํ•˜์˜€๋‹ค.

ํ…Œ์ด๋ธ” ์ŠคํŽ˜์ด์Šค ๊ตฌ์„ฑ ๊ธฐ์ค€์€ ํŒŒํ‹ฐ์…˜ ๋ณ„๋กœ ์„œ๋กœ ๋‹ค๋ฅธ ํ…Œ์ด๋ธ” ์ŠคํŽ˜์ด์Šค๋ฅผ ์ง€์ •ํ•˜๊ณ , ๋ฐ์ดํ„ฐ์™€ ์ธ๋ฑ์Šค์šฉ ํ…Œ์ด๋ธ” ์ŠคํŽ˜์ด์Šค๋„ ๋ณ„๋„๋กœ ๊ตฌ๋ถ„ํ–ˆ๋‹ค.

PK ์ปฌ๋Ÿผ์— SAPDB6_CURMEM ์„ ์ถ”๊ฐ€ํ•œ LOCAL INDEX ๋กœ ์ƒ์„ฑํ•œ ๊ฒฝ์šฐ์™€ GLOBAL INDEX ๋กœ ์ƒ์„ฑํ•œ ๊ฒฝ์šฐ๊ฐ„์— ์„ฑ๋Šฅ์€ ๊ฑฐ์˜ ์ฐจ์ด๊ฐ€ ์—†์—ˆ๊ธฐ ๋•Œ๋ฌธ์—, CURRENT MEMBER PARTITION ํ…Œ์ด๋ธ”์—๋Š” UNIQUE ์†์„ฑ์„ ๋ณดํ˜ธํ•˜๊ธฐ ์œ„ํ•ด PK ์— SAPDB6_CURMEM ์„ ์ถ”๊ฐ€ํ•˜์ง€ ์•Š๊ณ , GLOBAL INDEX ๋กœ ์‚ฌ์šฉํ•˜๋„๋ก ํ–ˆ๋‹ค.

 

# CURMEM ํŒŒํ‹ฐ์…˜ ํ…Œ์ด๋ธ” ์ƒ์„ฑ DDL

CREATE TABLE โ€œ์Šคํ‚ค๋งˆโ€.โ€ํ…Œ์ด๋ธ”๋ช…โ€ (

โ€œCLIENTโ€ VARCHAR(9 OCTETS) NOT NULL WITH DEFAULT โ€˜000โ€™,

โ€œDOCNRNC_IDโ€ VARCHAR(60 OCTETS) NOT NULL WITH DEFAULT โ€˜ โ€˜,

… ์ค‘๋žต

โ€œSAPDB6_CURMEMโ€ SMALLINT NOT NULL IMPLICITLY HIDDEN

WITH DEFAULT CURRENT MEMBER)

COMPRESS YES ADAPTIVE

VALUE COMPRESSION

PARTITION BY RANGE (โ€œSAPDB6_CURMEMโ€)

(PART โ€œPART00โ€ STARTING(0) ENDING(0) IN โ€œZCURMEM_TS_00โ€ INDEX IN โ€œZCURMEM_IS_00โ€,

PART โ€œPART01โ€ STARTING(1) ENDING(1) IN โ€œZCURMEM_TS_01โ€ INDEX IN โ€œZCURMEM_IS_01โ€)

ORGANIZE BY ROW;

 

CREATE UNIQUE INDEX โ€œ์Šคํ‚ค๋งˆโ€.โ€์ธ๋ฑ์Šค๋ช…โ€ ON โ€œ์Šคํ‚ค๋งˆโ€.โ€ํ…Œ์ด๋ธ”๋ช…โ€

(โ€œCLIENTโ€ ASC,

โ€œDOCNRNC_IDโ€ ASC)

NOT PARTITIONED IN โ€œZCURMEM_IS_00โ€

PCTFREE 0

COMPRESS YES

INCLUDE NULL KEYS ALLOW REVERSE SCANS;

 

ALTER TABLE โ€œ์Šคํ‚ค๋งˆโ€.โ€ํ…Œ์ด๋ธ”๋ช…โ€

ADD CONSTRAINT โ€œ์Šคํ‚ค๋งˆโ€.โ€P{K-constraint๋ช…โ€ PRIMARY KEY

(โ€œCLIENTโ€ ASC,

โ€œDOCNRNC_IDโ€ ASC);

… ํ›„๋žต

<๊ทธ๋ฆผ 1-2. Current Member Partition Table DDL ์˜ˆ์ œ>

 

ํŒŒํ‹ฐ์…˜ ๋ณ„๋กœ ์ ์žฌ๋˜๋Š” ๋ฐ์ดํ„ฐ๋Š” ๋ฉค๋ฒ„ ๋ณ„๋กœ ์ˆ˜ํ–‰๋˜๋Š” ์ ์žฌ SQL ์ˆ˜ํ–‰ ํšŸ์ˆ˜์— ๋”ฐ๋ผ ๋‹ฌ๋ผ์ง€๊ฒŒ ๋œ๋‹ค.๋ฉค๋ฒ„ ๋ณ„๋กœ Insert/Update/Delete ๊ฐ€ ๊ท ๋“ฑํ•˜๊ฒŒ ์ˆ˜ํ–‰๋œ๋‹ค๋ฉด ํŒŒํ‹ฐ์…˜ ๋ณ„ ๋ฐ์ดํ„ฐ ๊ฑด์ˆ˜ ์ฐจ์ด๊ฐ€ ํฌ์ง€ ์•Š์„ ๊ฒƒ์ด๋‹ค.

 

 

2.1.3.VBํ…Œ์ด๋ธ” ํŒŒํ‹ฐ์…˜

SAP ์—์„œ ์ž์ฒด์ ์œผ๋กœ update ์„ฑ๋Šฅ ํ–ฅ์ƒ์„ ์œ„ํ•ด ์‚ฌ์šฉํ•˜๋Š” VB ํ…Œ์ด๋ธ”๋“ค์ด ์žˆ๋‹ค. ์ด ํ…Œ์ด๋ธ”๋“ค์˜ ๊ฒฝ์šฐ SAP ์ž์ฒด์ ์œผ๋กœ SAP ์„ค์น˜ ๊ฐ€์ด๋“œ ๋ฌธ์„œ (Running an SAP System on IBM DB2 10.5 with the DB2 pureScale Feature) ๋‚ด์šฉ ์ค‘ VBKEY ์ปฌ๋Ÿผ(๋ฐ์ดํ„ฐ๋Š” AP์˜ hostname)์œผ๋กœ RANGE PARTITION ์„ ๊ตฌ์„ฑํ•˜๋„๋ก ํ•˜๋Š” ๊ฐ€์ด๋“œ๋ฅผ ๋”ฐ๋ผ ์ƒ์„ฑํ–ˆ๋‹ค.

VB ํ…Œ์ด๋ธ”์˜ RANGE PARTITION ์€ DBA ์ž‘์—…์ด ์•„๋‹Œ BC ์ž‘์—…์œผ๋กœ ์ƒ์„ฑ๋˜์—ˆ์œผ๋ฉฐ, AP ๊ฐœ์ˆ˜ ๋ณ€๊ฒฝ์— ๋”ฐ๋ฅธ RANGE PARTITION ๋ณ€๊ฒฝ ์ž‘์—… ์—ญ์‹œ BC ์ž‘์—…์œผ๋กœ ์ˆ˜ํ–‰ํ•˜๊ณ  ์žˆ๋‹ค.

 

๋‹ค์ŒํŽธ์— ์ด์–ด์„œ LOB Inline, Extent Size, Append Mode์„ ํ†ตํ•œ ์„ฑ๋Šฅ ๊ฐœ์„ ์— ๋Œ€ํ•ด์„œ ๊ธฐ์ˆ ํ•˜๋„๋ก ํ•˜๊ฒ ๋‹ค.

 

์ž‘์„ฑ์ž : ์ด๋•ํ˜ธ ๊ทธ๋ฃน์žฅ, ์กฐํ˜„๋‘ ์„ ์ž„, ๊น€๊ทผ์˜ ์„ ์ž„ (S์‚ฌ)

๊น€๊ธฐํ™” ๋ถ€์žฅ, ์ด์žฌํ˜ ๊ณผ์žฅ (IBM)

์กฐ์šฉํฌ ๋ถ€์žฅ, ๊น€์ผ๋ž€ ๊ณผ์žฅ (๊ณ ์›์‹œ์Šคํ…œ)

ํ† ๋ก  ์ฐธ๊ฐ€

์ด๋ฉ”์ผ์€ ๊ณต๊ฐœ๋˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค. ํ•„์ˆ˜ ์ž…๋ ฅ์ฐฝ์€ * ๋กœ ํ‘œ์‹œ๋˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค.