ÕªÒª:
DB2 V10.5 ÕýÊÇÔÚÕâÒ»±³¾°ÏÂÓ¦Ô˶øÉú£¬ÕâҲʹµÃ¹¹½¨»ùÓÚÁд洢µÄÊý¾Ý²Ö¿â³ÉΪ¿ÉÄÜ¡£ÕâÆªÎÄÕ½«½éÉÜʹÓÃ
DB2 Warehouse V10.5 ʱ£¬¹¹½¨Áд洢Êý¾Ý²Ö¿âµÄһϵÁÐ×î¼Ñʵ¼ù£¬²¢ÇÒ´©²å²ûÊöÁËÊý¾Ý²Ö¿âÊý¾ÝÁ÷Éè¼ÆÊ±µÄһЩϸ
...
¸ÅÊö
ÔÚ DB2 V10.5 ֮ǰ£¬DB2 ÔÚÎïÀíʵÏÖÉÏÒ»Ö±ÊÇ»ùÓÚÐд洢£¬µ«ÔÚµäÐ͵ÄÊý¾Ý²Ö¿âÓ¦ÓÃÖУ¬ÓÉÓÚ¶Á²Ù×÷µÄƵÂÊ£¨²éѯ¡¢Á¬½Ó¡¢¾Û¼¯µÈ£©»áÔ¶´óÓÚд²Ù×÷£¨Ôöɾ¸Ä£©£¬ÕâÖִ洢ʵÏÖ·½Ê½²¢²»ÄܸøÊý¾Ý²Ö¿â¡¢ÊµÊ±·ÖÎö´øÀ´ÏÔÖøµÄÓÅÊÆ¡£
½üЩÄêÀ´Áд洢Êý¾Ý¿âÒÔÆä¶Á´ÅÅÌЧÂÊ£¬´æ´¢¿Õ¼äµÄ¿ÉѹËõ±ÈÂÊ£¬ÅÅÐò / Ë÷ÒýЧÂÊ£¬ÒÔ¼°ÓÉ´Ë´øÀ´µÄ¼¼Êõ¡¢¹ÜÀíºÍÓ¦ÓÃÓÅÊÆ£¬³ÉΪÁËÊý¾Ý²Ö¿âÓ¦ÓõÄÒ»´óÈȵ㡣
DB2 V10.5 ÕýÊÇÔÚÕâÒ»±³¾°ÏÂÓ¦Ô˶øÉú£¬ÕâҲʹµÃ¹¹½¨»ùÓÚÁд洢µÄÊý¾Ý²Ö¿â³ÉΪ¿ÉÄÜ¡£ÕâÆªÎÄÕ½«½éÉÜʹÓÃ
DB2 Warehouse V10.5 ʱ£¬¹¹½¨Áд洢Êý¾Ý²Ö¿âµÄһϵÁÐ×î¼Ñʵ¼ù£¬²¢ÇÒ´©²å²ûÊöÁËÊý¾Ý²Ö¿âÊý¾ÝÁ÷Éè¼ÆÊ±µÄһЩϸ½Ú´¦Àí¡£
×¼±¸»î¶¯
ΪÁËÑÝʾºóÐøµÄÓû§³¡¾°£¬Ê×ÏÈÎÒÃÇÔÚ DB2 V10.5 ÉÏ´´½¨ÃûΪ BLUTEST
µÄÊý¾Ý¿â£¬²¢·Ö±ð½¨Á¢Á½¸öÐд洢£¬Á½¸öÁд洢µÄ±í£º
Çåµ¥ 1. preparation ddl
CREATE DB BLUTEST; CONNECT TO BLUTEST; CREATE TABLE CDE.AROMA_CLASS (CLASSKEY INTEGER, CLASS_TYPE, VARCHAR(12), CLASS_DESC VARCHAR(60)) ORGANIZE BY COLUMN; CREATE TABLE CDE.AROMA_CLASS1 (CLASSKEY INTEGER, CLASS_TYPE VARCHAR(12), CLASS_DESC VARCHAR(60)) ORGANIZE BY COLUMN; CREATE TABLE ROW.AROMA_CLASS (CLASSKEY INTEGER, CLASS_TYPE VARCHAR(12), CLASS_DESC VARCHAR(60)); CREATE TABLE ROW.AROMA_CLASS1 (CLASSKEY INTEGER, CLASS_TYPE VARCHAR(12), CLASS_DESC VARCHAR(60)); |
½¨ÍêÖ®ºó£¬ÎªÁËÔÚ Design Studio ÖÐʹÓÃÕâ¸öÊý¾Ý¿â£¬ÐèÒª½«Æä·´Ïò¹¤³ÌΪ±¾µØµÄÊý¾ÝÄ£ÐÍÎļþ£¨.dbm
Îļþ£©¡£¾ßÌå²½Öè´Ë´¦Ê¡ÂÔ¡£
³¡¾°Ò»£º½«ÎļþÄÚÈÝ×°ÔØÖÁÁд洢±í
ÔںܶàÊý¾Ý²Ö¿âµÄÓ¦Óó¡¾°Ï£¬ÖмäÊý¾Ý£¨ÀýÈç´Ó OLTP Êý¾ÝÖÐת»»²¢µ¼³ö£©»áÒÔÎļþÐÎʽ±£´æ£¬È»ºóÔÙ½«ÆäÖ±½Óµ¼ÈëÊý¾Ý²Ö¿â±íÖС£´Ë³¡¾°½«½éÉÜÈçºÎ½«ÎļþÄÚÈÝ×°ÔØÖÁÁд洢±í¡£
Ê×ÏÈ´´½¨Ò»¸öÊý¾ÝÁ÷£¬²¢ÍÏ×§¡°ÎļþÔ´¡±²Ù×÷·ûÖÁÊý¾ÝÁ÷±à¼Æ÷ÖУ¬ÆäÖгéÈ¡·½·¨ÓÐÈýÖÖÑ¡Ï
ʹÓà DB2 LOAD ʵÓóÌÐò´ÓÎļþ³éÈ¡Êý¾Ý
ʹÓà DB2 IMPORT ʵÓóÌÐò´ÓÎļþ³éÈ¡Êý¾Ý
ʹÓà DB2 INGEST ʵÓóÌÐò´ÓÎļþ³éÈ¡Êý¾Ý
ÕâÈýÖÖ·½Ê½¾ùÄÜÕýÈ·½«ÎļþÄÚÈÝ×°ÔØÖÁÁд洢±í£¬ÕâÀïÎÒÃDzÉȡĬÈ쵀 DB2 LOAD ·½Ê½¡£
½Ó×ÅÖ¸¶¨¸ÃÎļþÔÚ¿Í»§»úµÄλÖᣣ¨×¢Ò⣺ÈôÑ¡Ôñ¡°Êý¾Ý¿â·þÎñÆ÷¡±Ñ¡ÏÔòÒâζ×ÅÎļþÐè´æÔÚÓÚ´ËÊý¾ÝÁ÷µÄ SQL
Ö´ÐÐÊý¾Ý¿âÉÏ£©

ͼ 1. ÎļþÔ´²Ù×÷·û
µã»÷¡°ÏÂÒ»²½¡±£¬´Ë´¦ÐèÒªÖ¸¶¨Îļþ¸÷×ֶεÄÁÐÃûÓëÊý¾ÝÀàÐÍ¡ª¡ª¼È¿ÉÊÖ¹¤ÊäÈ룬Ҳ¿Éͨ¹ý¡°Éú³ÉÎļþ¸ñʽ¡±µÈÆäËû·½Ê½×Ô¶¯Éú³É¡£½Ó×ŵã»÷¡°Íê³É¡±£¬ÕâÑù¶ÔÒ»¸öÔ´ÎļþµÄ¶¨Òå¾ÍÍê³ÉÁË¡£
½ÓÏÂÀ´ÔÚÑ¡ÔñÄ¿±ê±íµÄ²Ù×÷·ûʱ£¬ÓÐÁ½¸öÑ¡Ôñ£º
1.³ÉÅú×°ÈëÄ¿±ê£¨ÍƼö£©
2.±íÄ¿±ê
Æä¾ßÌåÇø±ðÔÚÓÚÔËÓó¡¾°²»Í¬£º
³ÉÅú×°ÈëÄ¿±ê»áÖ±½ÓʹÓà DB2 µÄ Load ÃüÁîÖ±½Óµ¼ÈëÊý¾Ý£¬ÕâÊÇËÙ¶ÈÉÏÊÇ×îÓŵġ£¶ø±íÄ¿±êÊʺϵij¡¾°¸üÁé»î£¬»á¸ù¾ÝÄ¿±ê±íµÄÀàÐÍÉú³É²»Í¬µÄ
SQL¡£ÀýÈçÈôÄ¿±ê±í²»ÔÚ SQL Ö´ÐÐÊý¾Ý¿âÉÏ£¬ÄÇ×îÖÕµÄÖ´Ðз½Ê½»áÊÇ JDBC£»Èô»áÐèÒªÒ²»áÉú³ÉÒ»¸ö²»¼ÇÈÕÖ¾µÄÁÙʱ±í×÷ΪÖÐתÐèÒª¡£
¶ø¶ÔÓÚ½«ÎļþÄÚÈÝ×°ÔØÖÁÁд洢±íÕâÀೡ¾°£¬ÎÒÃÇÍÆ¼öʹÓÃÎļþÔ´ -> ³ÉÅú×°ÈëÄ¿±êµÄ²Ù×÷·û×éºÏ¡£
³¡¾°¶þ£º½«ÈÎÒâÊý¾ÝÁ÷µÄ½×¶ÎÐÔÊä³öµ¼ÏòÁд洢±í
ÉÏÒ»³¡¾°ÖУ¬ÎļþÆäʵ´ú±íÁË¡°Ôݴ桱µÄÊý¾Ý£¬¼´ÎÒÃÇÊÖ¹¤µØ°ÑÕâЩÊý¾ÝÒÔÎļþµÄ¸ñʽ±£´æµ½Á˱¾µØ¡£¶øÔÚµäÐ͵Ä
ETL£¨Êý¾ÝµÄ³éÈ¡£¬×ª»»£¬×°ÔØ£©Á÷³ÌÖУ¬ÕâÒ»¹ý³ÌÊDz»ÐèÒªÈ˹¤¸ÉÔ¤£¬¶øÇÒÐèÒª±È³Ö¾Ã»¯Îļþ¸üºÃµÄ½â¾ö·½°¸¡£
Ò»¸öµäÐ͵ÄÊý¾Ý²Ö¿â ETL Á÷ÐèÒª½«ÒµÎñ±íÖУ¨Í¨³£ÔÚ OLTP Êý¾Ý¿âÖУ©µÄÊý¾ÝÓëά¶È±í½øÐмü²éѯ£¬È»ºó×°ÔØÖÁÄ¿±êµÄÊÂʵ±íÖУ¬ÈçÏÂͼËùʾ£ºÒµÎñ±í
1 Ϊ Ðд洢µÄÒµÎñ±í ROW.AROMA_CLASS£¬±íÔ´ 2 Ϊ Áд洢µÄά¶È±í CDE.AROMA_CLASS¡£

ͼ 2. µäÐÍ ETL Á÷ʾÀý
´Ë´¦£¬±íÄ¿±êÒ²¿ÉÒÔÌæ»»³É³ÉÅú×°ÈëÄ¿±ê²Ù×÷·û¡£ÏÂÃæÎÒÃǼòµ¥¶Ô±Èһ϶þÕßµÄÖ´ÐÐ SQL ´úÂë >
1¡¢³ÉÅú×°ÈëÄ¿±ê
Ö´Ðз½Ê½£ºDB2 µÄ SYSPROC.ADMIN_CMD ´æ´¢¹ý³Ì
Çåµ¥ 2. ³ÉÅú×°ÈëÄ¿±êÉú³É´úÂë
LOAD FROM (SELECT Q200.CLASSKEY AS CLASSKEY, Q200.CLASS_TYPE AS CLASS_TYPE, Q200.CLASS_DESC AS CLASS_DESC FROM ROW.AROMA_CLASS Q200, CDE.AROMA_CLASS Q362 WHERE (Q200.CLASSKEY = Q362.CLASSKEY) ) OF CURSOR METHOD P(1, 2, 3) MESSAGES ON SERVER INSERT INTO "CDE"."AROMA_CLASS1"("CLASSKEY", "CLASS_TYPE", "CLASS_DESC") NONRECOVERABLE |
2¡¢±íÄ¿±ê
Ö´Ðз½Ê½£ºJDBC
Çåµ¥ 3. ±íÄ¿±êÉú³É´úÂë
INSERT INTO CDE.AROMA_CLASS1 (CLASSKEY, CLASS_TYPE, CLASS_DESC) SELECT Q200.CLASSKEY AS CLASSKEY, Q200.CLASS_TYPE AS CLASS_TYPE, Q200.CLASS_DESC AS CLASS_DESC FROM ROW.AROMA_CLASS Q200, CDE.AROMA_CLASS Q340 WHERE (Q200.CLASSKEY = Q340.CLASSKEY) |
¿ÉÒÔ¿´³ö¶þÕßµÄÖ´ÐÐ SQL ´úÂë»ù±¾ÏàËÆ£¬Çø±ðÔÚÓÚ£º
1.³ÉÅú×°ÈëÄ¿±êÊǽ«Æä°üÔÚÒ»¸öÄäÃûµÄ CURSOR ÖУ¬²¢Í¨¹ý DB2
µÄ ADMIN_CMD ´æ´¢¹ý³ÌÀ´Ö´ÐÐ
2.¶ø±íÄ¿±êÊÇͨ¹ý JDBC Ö±½ÓÖ´ÐС£ÎÒÃÇÖªµÀ Load ·½Ê½²¢²»»á²úÉúÈÕÖ¾£¬¶ø¶ÔÓÚ±íÄ¿±êÔÚĬÈÏÇé¿öÏ£¨Ðд洢±í£©Ò²¿ÉÒÔÊÖ¹¤¹ØµôÈÕÖ¾£º±íÄ¿±ê
> ¸ß¼¶Ñ¡Ïî > NOT LOGGED INITIALLY¡£µ«¶ÔÓÚÁд洢±íÀ´Ëµ£¬´ËÑ¡Ïî²¢²»¿ÉÓ㬹ʶÔÓÚÓû§½ûֹдÈÕÖ¾ÇÒÔËÐиü¿ìËÙµÄÐèÇó£¬ÎÒÃÇÍÆ¼öʹÓóÉÅú×°ÈëÄ¿±ê£»¶øÈôÓû§ÐèÒª¸ü¼ÓÁé»îµØ´¦ÀíÄ¿±ê±í£¨ÀýÈç¶ÔÄ¿±ê±í½øÐÐÔ¤´¦Àí»òÍÆ³Ù´¦Àí£¬¼Ç¼ÈÕÖ¾£¬ÒÔ¼°ÐèÒªÔÚÄ¿±ê±í½ÓÆäËû
ETL ¶¯×÷£©£¬Ôò±íÄ¿±êÊǸüºÃµÄÑ¡Ôñ¡£
³¡¾°Èý£ºÊ¹ÓÃÁд洢ѡÏî´´½¨ºÏÊʵıí
ÔÚ ³¡¾°¶þÖУ¬ÊÂʵ±í 1 ÊÇÒѾ´´½¨ºÃµÄÁд洢±í£¬¶øÔÚ DB2 Warehouse V10.5 ËùÖ§³ÖµÄÊý¾Ý²Ö¿âÉè¼Æ³¡¾°ÖУ¬ÆäʵÕâ¸ö±íÊÇ¿ÉÒÔËæ×Å¿ªÊ¼µÄ½ø³Ì¶¯Ì¬´´½¨µÄ£¬¶øËùÓõŦÄܾÍÊÇ¡°´´½¨ºÏÊÊµÄ±í¡±¡£
¼ÙÉèÎÒÃÇÔÝʱûÓÐÕâ¸ö»ùÓÚÁд洢µÄÊÂʵ±í£¬Ö»ÓÐÒµÎñ±íºÍά¶È±íÁ½¸öÔ´±í£¬È»ºó¾¹ýÁ˼ü²éѯ£¬²¢ÌÞ³ýµôÎÞÓÃÁУ¬ÐÞ¸ÄÁÐÀàÐÍ£¬»òÖØÃüÃûһЩÁк󣬵õ½Õâ¸öÊÂʵ±íÐèÒªµÄÁÐÃû£¬ÁÐÀàÐÍ¡£ÄÇôºÜÈ»Ïëµ½µÄÏÂÒ»²½¶¯×÷Ó¦¸Ã¾ÍÊÇÖ´ÐÐÏàÓ¦µÄ
db2 create Óï¾ä£¬¼´ÔÚÊý¾Ý¿âÉÏÊÖ¹¤´´½¨Õâ¸ö±í¡£È»¶øÊ¹Óà ´´½¨ºÏÊʵıí¿ÉÒԺܷ½±ãÔÚͬʱÔÚ db2
ºÍÊý¾ÝÄ£ÐÍÖд´½¨Õâ¸ö¼´Ê±ÐèÒªµÄ±í£¬¾ßÌå²½ÖèÊÇÔÚ¼ü²éѯ²Ù×÷·ûµÄ ÏàÆ¥Åä¶Ë¿ÚÉÏ ÓÒ¼ü > ´´½¨ºÏÊʵıí
... > ÌîдºÏÊʵıíÃû > Ñ¡ÔñÊý¾Ý¿âÁ¬½Ó£¬È»ºó»á¿´ÏÂÈçϽçÃæ£º

ͼ 3. ´´½¨ºÏÊʵıí
ÔÚ Columns Ò³£¬´´½¨´Ë±íµÄËùÓÐÁÐÐÅÏ¢ÊÇ´Ó¼ü²éѯ²Ù×÷·ûµÄ¡°ÏàÆ¥Å䡱¶Ë¿Ú´«µÝ¹ýÀ´µÄ£¬ÆäËûÒ³µÄÐÅÏ¢²ÉÓÃÁËĬÈÏÖµ£¬¾ù¿ÉÊÖ¹¤Ð޸ġ£¶ÔÓÚÁд洢À´Ëµ£¬×îÖØÒªµÄÊÇÐÅÏ¢ÊDZí×éÖ¯ÏÐèÒªÔÚ´ËÑ¡Ôñ¡°ÁС±Öµ¡£
½Ó×ŵã»÷¡°Íê³É¡±¼´¿ÉͬʱÔÚÊý¾Ý¿âºÍÊý¾ÝÄ£ÐÍÖд´½¨Õâ¸ö¼´Ê±ÐèÒªµÄ±í¡£
ÕâÖÖ³¡¾°Í¬Ê±¿ÉÒÔÓ¦ÓÃÓÚËùÓÐÓÐÊä³ö¶Ë¿ÚµÄ²Ù×÷·ûÉÏ£¬Èç±íÔ´£¬ÎļþÔ´£¬½¥±äάµÈµÈ¡£
³¡¾°ËÄ£ºÓÅ»¯Ðд洢±í¼°Áд洢±í¼äµÄÊý¾Ý³éÈ¡£¬×ª»»£¬×°ÔØ
DB2 Warehouse V10.5 ×÷ΪÊý¾Ý²Ö¿âµÄ¶Ëµ½¶ËÍêÕû½â¾ö·½°¸£¬Æä¶ÔÓÚÁд洢±íµÄ×î´óÓÅÊÆÔÚÓÚÆäÑÓÐøÁ˸ßÖÊÁ¿µÄ´úÂëÉú³É¼¼Êõ¡ª¡ªÓû§²»±Ø¿¼ÂÇÐд洢±íÓëÁд洢±íÔÚÖ®¼äÔÚÊý¾Ý³éÈ¡£¬×ª»»£¬×°ÔØÊ±µÄ¸÷ÖÖ¿¼Á¿£¬¿ÉÄܵÄÐÔÄÜÏûºÄ£¬ÕâЩ¶¼ÓÉÄÚ²¿»ùÓÚ²éѯͼģÐÍ£¨Query
Graph Model£©µÄ´úÂëÉú³É¼¼ÊõÀ´Íê³É¡£¶ÔÓÚÓû§À´Ëµ£¬Ö»ÐèÒª¿¼ÂǶ˵½¶ËµÄ±íÀàÐÍÑ¡Ôñ£¬ÖмäµÄ¹ý³Ì¶ÔÓÚÆäÊÇ͸Ã÷µÄ¡£
³¡¾°¶þµÄÓÅ»¯ºó²éѯͼģÐÍÈçÏÂͼËùʾ£º

ͼ 4. ÓÅ»¯ºóµÄ²éѯͼģÐÍ
³¡¾°Î壺¸÷²Ù×÷·û¶ÔÓÚÁд洢±íµÄϸ½Úµ÷Õû¼°ÏÞÖÆ
ÓÉÓÚ DB2 Warehouse ²úÆ·Êǽ¨Á¢ÔÚ DB2 Ö®Éϵģ¬ËùÒÔ DB2 V10.5 ÖÐÁд洢±íµÄһЩÏÞÖÆÒ²Ö±½Ó»ò¼ä½ÓµØ·´Ó³µ½ÁË
DB2 Warehouse ÉÏ¡£ÏÂÃæ½«ÒÔ²Ù×÷·ûΪÐò£¬½éÉÜÆä¶ÔÓÚÁд洢±íµÄϸ½Úµ÷Õû¼°ÏÞÖÆ£º
1¡¢³ÉÅú×°ÈëÄ¿±ê
×¼±¸ºÍÇå³ýÒ³ÖУ¬ÔÏÈĬÈ쵀 SET INTEGRITY Óï¾ä»á±»ÉèÖÃΪ¿Õ¡£

ͼ 5. ³ÉÅú×°ÈëÄ¿±ê
¸ß¼¶Ñ¡Ïî > »Ö¸´Ñ¡Ï¡¸Ê¹Óñ£´æµÄÊäÈëÊý¾Ý¸±±¾À´»Ö¸´µ½Ä¿Â¼ÖС¹Ñ¡Ïî¶ÔÓÚÁд洢±í²»¿ÉÓ㬹ʻáÊÕµ½ÈçÏ´íÎó£º

ͼ 6. COPY YES ³ö´íÐÅÏ¢
2¡¢±íÄ¿±ê
¸ß¼¶Ñ¡ÏîÒ³ÖУ¬REORG ºÍ NOT LOGGED INITIALLY Ñ¡Ï²»¿ÉÓá£

ͼ 7. ±íÄ¿±ê²Ù×÷·û¸ß¼¶Ñ¡Ïî
3¡¢Êý¾ÝÕ¾
ÈôÕ¾ÀàÐÍΪ³£¹æ±í£¬INDEX ºÍ NOT LOGGED INITIALLY Ñ¡Ï²»¿ÉÓá£

ͼ 8. Êý¾ÝվѡÏî
4¡¢SQL ºÏ²¢£¬½¥±äά
ÕâÁ½¸ö²Ù×÷·ûÒòΪÉú³ÉµÄ SQL ´úÂëÖлáʹÓà MERGE Óï·¨£¬¶øÆäÔÝʱÔÚ DB2 V10.5 µÄÁд洢±íÖв»Ö§³Ö£¬¹ÊÕâÁ½¸ö²Ù×÷·ûÔÝʱ²»ÄÜ×÷ÓÃÓÚÁд洢±í¡£
5¡¢ÖØ×飨¿ØÖÆÁ÷ÖУ©
ÖØ×é²Ù×÷·ûÈô×÷ÓÃÓÚÁд洢±í£¬»á³öÏÖÈçÏ´íÎó£º

ͼ 9. ÖØ×é²Ù×÷·û³ö´íÐÅÏ¢
|