DB2 Information Management Software DB2 Migration 절차, 방법및 MTK 소개 - 타 DBMS와연계 - SQL의호환성 IBM Korea Software Group IM 사업부 2008 IBM Corporation
목차 1. DB2 Migration의장점 2. DB2 Migration 절차및방법 3. MTK에대한소개 4. SQL Conversion 5. MTK를활용한 Migration의장점 6. Access Guidelines 7. User Defined Function 8. 타 DBMS와연계 9. SQL 호환성 2 2008 IBM Corporation
1. DB2 Migration 의장점 TCO : 5년간총소유비용 (TCO:Total cost of Ownership) 이타 DBMS 사용시에비해 49%-70% 저렴합니다. Migration offering에는분석서비스, Migration tool kit제공, 교육지원서비스와 POC 서비스를진행하여고객의문제를최소화해드립니다. 분석서비스에는 IBM의다년간의 Know-how를바탕으로고객의문제를최소화하고최단시간에 DB2로이전을위한능력을보유하여제공합니다. 신규 Application개발시앞선 DB 기능들을활용할수있으며이는개발및운영의위험을최소화합니다. DB2는 IBM의앞선제품과기술력을가지고있으며, TPC-C, TPC-H 공인기관및 SAP등이인정하는최상의성능을제공하며, DB2는 Autonomic computing 기술과 Grid 환경에가장앞선기술력을가지고있습니다. 3 2008 IBM Corporation
2. DB2 migration 절차및방법 DB2 Migration 단계 Assessment & Planning 환경구성 Migration 의대상이되는시스템의구성요소및 database 의구성이 migration 작업에미치는영향을분석하고, 필요한작업과각작업의일정에대해계획수립 제품설치및 migration 에필요한 tool 과기타환경구성 Database object migration Database structure 및 Database 내의 object 에대한 migration Application conversion Application 에대한 conversion 수행 Data Migration Data 이관 Performance tuning 성능최적화를위한 performance tuning 수행 Test 및검증 Migration 이후 database, application 전반의통합테스트및검증 추가적인작업 HA 구성, replication, backup tool 과의연계등추가적인작업수행 4 2008 IBM Corporation
2. DB2 migration 절차및방법 DB2 Migration 단계 1. Assessment & Planning - 현재시스템은물론 migration 이후의시스템까지의모든정보를토대로작업을예측합니다 Migration 에필요한정보 Platforms Database structure Application Migration 보조 tool 운영환경 개발환경 추가적인환경 (Middleware, Tool, Compiler, etc.) Database size Table 수, row size, index 수 Stored procedure, User defined function Trigger Application architecture Application size Application SQL 특성 동시사용자수 Migration 작업에있어 tool 이자동화할수있는범위산정 IBM 제공문서 Assessment item 산정문서 DB2 porting guide 5 2008 IBM Corporation
2. DB2 migration 절차및방법 DB2 Migration 단계 2. 환경구성 - 작업에필요한시스템환경을구성합니다 하드웨어구성 OS 설치및구성 Disk 영역구성 시스템환경구성 소프트웨어구성 DB2 제품설치 DB2 환경설정 DB2 인스턴스생성 DB2 데이터베이스생성 Application 구성 사용자, 그룹생성및사용권한설정 Client 연결설정 Application 연결설정 IBM 제공문서및 Tool DB2 설치가이드 DB2 운영자가이드 DB2 구성지원 Tool 6 2008 IBM Corporation
2. DB2 migration 절차및방법 DB2 Migration 단계 3. Database object migration - 각 database object 별 migration 을수행하고테스트합니다 Database Object 별 Migration Database logical / physical layout 점검및변환 Table 변환 Database type Partitioning Tablespace Logs Column type Index Constraints Stored procedure, User defined function Stored procedure 와 user defined function 에포함된로직변환 IBM 제공문서및 Tool DB2 Migration tool kit DB2 UDB conversion guide (from Oracle/Sybase/SQL server ) 7 2008 IBM Corporation
2. DB2 migration 절차및방법 DB2 Migration 단계 4. Application Migration - DB2 에서운영될수있도록 application source 를 conversion 합니다 Source Conversion Application source 변환 호환가능한 SQL 과변환 SQL 분리 SQL 에대한변환및테스트 Isolation level, lock 의사용등고려 Error 처리방법변환 개발자를위한 SQL conversion tool 제공 IBM 에서제공하는 MTK 의 on the fly SQL translator 를이용하여 SQL conversion 기능지원 IBM 제공문서및 Tool DB2 Migration tool kit DB2 Development center DB2 UDB conversion guide (from Oracle/Sybase/SQL server ) 8 2008 IBM Corporation
2. DB2 migration 절차및방법 DB2 Migration 단계 5. Data Migration - 기존환경의데이터베이스로부터데이터를추출하여 DB2 로로드합니다. Table Data 이관 Type 간의호환성을고려하여 migration 방법선택 -Numeric -Date, datetime Source data 와 target data 의 sample 이관수행 Data migration 고려사항 Data 추출 DB2 에서 load 할수있는형식으로 data 를추출 Type 의호환성에따라추가적인 conversion 이필요한경우를위한 program 이나 script 작성 Data 로드 DB2 Load tool 을이용하여 data load Referential constraint 등을고려한작업계획 IBM 제공 Tool DB2 Migration tool kit DB2 Admin client DB2 Load tool 9 2008 IBM Corporation
2. DB2 migration 절차및방법 DB2 Migration 단계 6. Performance tuning - 기존성능또는그이상의성능을유지하기위하여 database 및 application 을튜닝합니다 Performance tuning Migration 완료후성능최적화를위한작업 DB2 database 에대한최적화수행 DB2 application 에대한최적화수행 최적화적용이후 monitoring 을통한튜닝 DB2 에서제공하는다양한 advisor 와 health center 를이용하여자동화된최적화작업가능 IBM 제공문서및 Tool DB2 Performance Tuning Guide DB2 Health center DB2 Advisor 10 2008 IBM Corporation
2. DB2 migration 절차및방법 DB2 Migration 단계 7. Test 및검증 - 기존운영환경데이터의손실없이 application 이동일하게동작되는지검사합니다 Migration 완료에대한 test 및검증 Data 이관의검증을위한대사방법마련및검증 Application 수행결과검증 Test 및검증 Migration 오류사항에대한수정 IBM 제공 Tool DB2 Migration tool kit 11 2008 IBM Corporation
2. DB2 migration 절차및방법 DB2 Migration 단계 8. 추가적인작업 - Migration 완료된 DB2 를보다효율적으로사용하기위하여추가적인구성을할수있습니다 HA 구성및테스트 Backup 정책적용 외부 backup tool 과의연계 추가작업 Database 유지보수및운영지침마련 모니터링툴 정기적인통계정보갱신 Table, index 에대한 REORG 장애대처 IBM 제공 Tool DB2 Migration tool kit DB2 admin guide DB2 HA 구성가이드 12 2008 IBM Corporation
3. DB2 MTK 에대한소개 DB2 MTK (DB2 Migration Tool Kit)? - 타 DB(Oracle, Sybase 등 ) 에서 DB2 로의 migration 을지원하기위한자동화 tool - 전체 migration작업중, 많은부분을자동화해줄수있음. - DB2에서무상으로제공됨. 제공되는기능 - Source DB의 DDL구문자동추출 ODBC/JDBC등의연결을통해, migration의대상인 source DB의 DDL을자동추출해줌. - DB Object 전체의변환수행 Table, index, trigger, sequence, procedure - Data이관 이관 script를작성해주는것은물론, 실제 data의이관도 tool을통해가능. - Migration 결과 report 제공 Migration 작업의결과를보기쉬운 HTML형식의 report로산출해줌. - 개발자를위한 SQL translator 제공 DB2의 SQL 문법에익숙하지않은개발자나, 관리자가쉽게 sql을변환할수있도록하는 tool제공. - Function호환성을위한 conversion library제공 각 DB가고유하게지원하고있는함수등을 DB2 에서도사용할수있도록 library를제공. 13 2008 IBM Corporation
3. DB2 MTK 에대한소개 작업단계 작업단계 1. Project 생성 ( 혹은선택 ) 2. Source 추출 - MTK를이용하여 source DB의 Metadata(DDL) 를추출혹은 import 3. Convert - Source DB로부터추출된 DDL을변환 4. Refine - 변환된스크립트를정제 5. Data 이관 script 생성 - Data이관을도와줄 script 생성 6. DB2로 deploy - DB2 DB로 migration 수행 14 2008 IBM Corporation
3. DB2 MTK 에대한소개 0) DB2 MTK 실행 사용환경 - 작업하는 PC 환경 : Xwindows 환경의 GUI tool 이므로, Xterminal S/W 가필요함. - 설치되는 Server 장비 : JRE 1.3 이상설치 실행환경 -DISPLAY=<pc 의 IP>:0.0 -CLASSPATH=$CLASSPATH:${ORACLE_HOME}/j dbc/lib/classes12.zip Oracle source DB 에바로연결하기위한환경. MTK 를설치디렉터리의 MTKMain 을실행 >./MTKMain 15 2008 IBM Corporation
3. DB2 MTK 에대한소개 1) Project 생성 전체 Migration 작업저장할 project 를생성 다음과같은정보를입력 -Project Name Project 의이름 -Project Path 작업중에생기는모든파일들이저장될위치 ( 이디렉터리아래 Project 이름의디렉터리가생김 ) -Source DB 종류 Oracle, Sybase,MS SQL Server 등선택 -DB2 target 종류 Target DB2 의종류선택 16 2008 IBM Corporation
3. DB2 MTK 에대한소개 2) source 추출 이미 source 에대한 DDL 을 SQL file 로가지고있다면, Import 를이용해그 sql file 을 import 한다. Extract 를선택하면, 이와같은 popup 이뜨는데, 각 DB 특성에맞게연결을위한정보를입력한다. 즉, Database 종류, Service name, 연결사용자계정등을입력하고, OK 를누른다. 소스 DB 로바로연결해서 metadata 를추출하려면, Extract 를선택한다. 17 2008 IBM Corporation
3. DB2 MTK 에대한소개 2) source 추출 DDL 을추출하여저장할 file 이름을지정한다. 이때확장자를제외하고지정한다. 확장자는파일의종류에따라결정된다. Source DB 에연결되고나면, source DB 의 object 를 tree 형식으로보여준다. 여기에서, Migration 의대상만 check 한다 Extract 를누르면추출을시작한다. 18 2008 IBM Corporation
3. DB2 MTK 에대한소개 2) source 추출 Source 에대한추출이끝나면, 다음 tab 인 Convert 를누른다. 소스의추출이완료되면, 오른쪽창에.src 확장자를가지는몇가지 file 이생성된다. View, delete 와같은버튼으로작업할수도있다. 19 2008 IBM Corporation
3. DB2 MTK 에대한소개 3) Convert Schema conversion 을위해 data type 의 mapping 을확인하거나수정한다. Type 수정화면은다음페이지참조. 확인사항을점검한뒤, Convert 를누른다. 20 2008 IBM Corporation
3. DB2 MTK 에대한소개 3) Convert 각 type 에대한 target type 의정보를확인하고, 수정하고자한다면, 수정한다. 21 2008 IBM Corporation
3. DB2 MTK 에대한소개 4) Refine Convert 작업이완료되면자동으로 refine 작업으로넘어간다. 22 2008 IBM Corporation
3. DB2 MTK 에대한소개 5) Generate Data Transfer scripts IMPORT / LOAD 중선택 ( 대량의경우 LOAD 권장 ) Data 를옮기기위해어떤방법을선택할것인지를지정한다. 작업이끝나면생기는 script file 들 File format 선택 Data 추가방법선택 (INSERT/ REPLACE) Create Script 버튼을누르면 script file 이생긴다. 23 2008 IBM Corporation
3. DB2 MTK 에대한소개 SQL Translator 사용 Tools SQL Translator 실행 Conversion 하고자하는 sql 구문을적는다. 이때, 변수가사용되는구문이라면, 그변수에해당되는 table 생성구문도함께적어주어야한다. 변환된 SQL 구문이출력됨. 24 2008 IBM Corporation
3. DB2 MTK 에대한소개 Reporting 기능 모든작업의결과는 HTML 형식의문서로 reporting됨. <MTK 설치디렉터리 >/projects/<project이름 >/Reports/ 에위치함. - Conversion_asset.html : 전체 project 의모든 report 를연결할수있는페이지 출력내용 - 작업대상 object의종류및각종류별개수 Database, Schema,Package,User type,table,view,column,index,foreign Key,Proc & Function,Trigger - 작업오류내역 report 대상별, 오류내역별로자세한 report 출력 - Tablespace 구성에필요한각 table의 rowsize 산정 report - 작업완료후전체 data 건수출력으로검증한결과출력. 소스 table의건수, target table의건수를함께출력. Table의 key에대한이관성공여부출력 오류 table은붉은색으로 toggle되며, click하면작업오류로그로이동. 25 2008 IBM Corporation
4. SQL conversion SQL Syntax : Sequence 생성구문은동일 값을참조하는구문의차이만있음. Oracle Sequence_name.NEXTVAL(CURRVAL) DB2 NEXTVAL(CURRVAL) for Sequence_name 26 2008 IBM Corporation
4. SQL conversion SQL Syntax : rownum Rownum 값을 select, insert, update 에서값을컬럼값의의미로사용하려면, - row_number() over() 함수사용. Rownum 값을 row 의범위를지정할때사용하려면, - row_number() over() 함수사용하거나, fetch first n rows only 구문이용 Oracle Select col_date, col_int + rownum from tab1 order by col_date; Select * from tab1 where rownum <10; Update tab1 set c1=v1 where c2=v2 and rownum <=10; DB2 Select col_date, col_int + row_number() over() from tab1 order by col_date; Select * from tab1 fetch first 9 rows only; Update ( select c1 from tab1 where c2=v2 fetch first 10 rows only) set c1=v1; 27 2008 IBM Corporation
4. SQL conversion SQL Syntax : outer join Oracle에서 (+) 기호를이용해서표현된구문을 outer join구문으로대체 Oracle에서는 outer join 대상 table의모든컬럼을조건식에사용할때 (+) 를사용하여야하나, DB2에서는기호를붙일필요없음. Oracle Select a.last_name, a.id, b.name From emp a, customer b Where a.id(+) = b.sales_rep_id ; Select a.last_name, a.id, b.name From emp a, customer b Where a.id = b.sales_rep_id(+) ; Select a.last_name, a.id, b.name From emp a, customer b Where a.id(+) = b.sales_rep_id(+) ; Select a.last_name, a.id, b.name From emp a, customer b Where a.id = b.sales_rep_id (+) and b.name(+) like K% ; DB2 Select a.last_name, a.id, b.name From emp a right outer join customer b on a.id=b.sales_rep_id ; Select a.last_name, a.id, b.name From emp a left outer join customer b on a.id=b.sales_rep_id ; Select a.last_name, a.id, b.name From emp a full outer join customer b on a.id=b.sales_rep_id ; Select a.last_name, a.id, b.name From emp a left outer join customer b on a.id=b.sales_rep_id Where b.name like K% ; 28 2008 IBM Corporation
4. SQL conversion SQL Syntax : dual table Oracle 에서 system information 을가져오는방법으로 dual table 을사용하였다면, - DB2에서는 SYSIBM.SYSDUMMY1에대한 select로변환하거나 VALUES를이용 Oracle Select SYSDATE from DUAL; DB2 VALUES(current timestamp) into <variable>; Or Select current timestamp From sysibm.sysdummy1; 29 2008 IBM Corporation
4. SQL conversion SQL Syntax : decode Oracle 의 decode 함수는 DB2 의 CASE 문으로대체 Oracle Select avg ( decode ( grade, A,1, B,2, C,3, D,4)) From students; DB2 Select avg ( case grade when A then 1 when B then 2 when C then 3 when D then 4 END) From students; 30 2008 IBM Corporation
4. SQL conversion SQL Syntax : nvl Oracle 의 nvl 함수는 DB2 의 COALESCE 로대체 Oracle Select Nvl(manager_id, No Manager ) From employee; DB2 Select coalesce(manager_id, No Manager ) From employee; 31 2008 IBM Corporation
4. SQL conversion SQL Syntax : 내장함수 to_char UDF 혹은 ora8.to_char to_number int, decimal등의 casting 함수혹은 ora8.to_number lpad, rpad UDF 혹은 ora8.lpad(rpad) trim ltrim / rtrim 함께사용. nvl COALESCE - MTK 는 ora8.*** 이름을가지는, 다양한대체함수를제공합니다. - UDF (user defined function) 은내장함수와같은 Logic 으로사용자정의함수를만들어, 그함수를사용하는방법을말한다. 함수작성예 ) CREATE FUNCTION RPAD (C1 VarChar(4000), N integer, C2 VarChar(4000)) RETURNS VARCHAR(4000) LANGUAGE SQL SPECIFIC RPADBase DETERMINISTIC CONTAINS SQL NO EXTERNAL ACTION RETURN substr(c1 repeat(c2,((sign(n-length(c1))+1)/2)*(n-length(c1)+length(c2))/(length(c2)+1- sign(length(c2)))),1,n) ; 32 2008 IBM Corporation
5. MTK 를활용한 Migration 의장점 DB 관리자 IBM에서무료로제공되는 MTK를이용하여빠르고원활하게 DB의 migration이가능함. 자동화된 Migration으로 Migration이후의 DB tuning이나, 운영부분에집중할수있음. Migration에따른결과보고자료출력기능으로보고서작성에용이 업무개발자 많은부분 SQL이 Oracle과호환가능하여, 많은수정이필요하지않음. 구문이다른 SQL의경우 MTK에서제공하는 SQL Translator를이용하여자동변환가능. IBM에서제공되는개발자를위한문서와 tool을이용하여빠른시간안에 DB2 습득가능. 33 2008 IBM Corporation
5. MTK References MTK Web Site - http://www.ibm.com/db2/migration/mkt Migration Station - http://www.ibm.com/developerworks/ondemand/migrate/db.html MTK Support, Mailbox : mtk@us.ibm.com 34 2008 IBM Corporation
MTK Demo DEMO 35 2008 IBM Corporation
6. Access Guidelines: TBSCAN Query: SELECT C1 FROM T1 /*<OPTGUIDELINES> <TBSCAN TABLE='T1'/> <OPTGUIDELINES>*/ Plan: RETURN TBSCAN TABLE: DDEACONU T1 36 2008 IBM Corporation
6. Access Guidelines: IXSCAN Query: SELECT C1 FROM T1 /*<OPTGUIDELINES> <IXSCAN TABLE='T1'/> <OPTGUIDELINES>*/ Plan: RETURN IXSCAN ( 2) INDEX: DDEACONU T1_I1 37 2008 IBM Corporation
6. Access Guidelines: LPREFETCH Query: SELECT C1 FROM T1 /*<OPTGUIDELINES> <LPREFETCH TABLE='T1'/><OPTGUIDELINES>*/ Plan: RETURN FETCH /------------------+-----------------\ RIDSCN TABLE: DDEACONU T1 SORT IXSCAN INDEX: DDEACONU T1_I1 38 2008 IBM Corporation
6. Access Guidelines: LPREFETCH Query: SELECT C1 FROM T1 /*<OPTGUIDELINES> <LPREFETCH TABLE='T1 INDEX= T1_I2 /> <OPTGUIDELINES>*/ Plan: RETURN FETCH /------------------+-----------------\ RIDSCN TABLE: DDEACONU T1 SORT IXSCAN INDEX: DDEACONU T1_I2 39 2008 IBM Corporation
6. Access Guidelines: IXAND Query: SELECT T1 FROM T1 WHERE C1 =? AND C2 =? Guideline: /*<OPTGUIDELINES> <IXAND TABLE='T1'> <INDEX IXNAME='T1_I1'/> <INDEX IXNAME='T1_I3'/> </IXAND> </OPTGUIDELINES>*/ Note: Indexes must be specified in a IXAND guideline. 40 2008 IBM Corporation
6. Access Guidelines: IXOR Query: SELECT * FROM T1 WHERE C1 in (?,?) Guideline: /*<OPTGUIDELINES> <IXOR TABLE='T1'/> </OPTGUIDELINES>*/ Note: We cannot specify indexes in an IXOR guideline 41 2008 IBM Corporation
6. Access Guidelines: JOIN Query: select * from t1 join t2 on t1.c1 = t2.c1 Guideline: /*<OPTGUIDELINES> <NLJOIN> <TBSCAN TABLE='T1'/> <TBSCAN TABLE='T2'/> </NLJOIN> </OPTGUIDELINES>*/ Guideline: /*<OPTGUIDELINES> <MSJOIN> <TBSCAN TABLE='T1'/> <TBSCAN TABLE='T2'/> </MSJOIN> </OPTGUIDELINES>*/ Guideline: /*<OPTGUIDELINES> <HSJOIN> <TBSCAN TABLE='T1'/> <TBSCAN TABLE='T2'/> </HSJOIN> </OPTGUIDELINES>*/ Guideline: /*<OPTGUIDELINES> <JOIN> <TBSCAN TABLE='T1'/> <TBSCAN TABLE='T2'/> </JOIN> </OPTGUIDELINES>*/ 42 2008 IBM Corporation
6. Access Guidelines: More complex Join Query: select * from t1, t2, t3 where t1.c1 = t3.c1 and t1.c1 = t2.c1 Guideline: /*<OPTGUIDELINES> <HSJOIN> <NLJOIN> <TBSCAN TABLE='T1'/> <TBSCAN TABLE='T2'/> </NLJOIN> <ACCESS TABLE='T3'/> </HSJOIN> </OPTGUIDELINES>*/ Note: Make sure the tables joined are part of the same QTB 43 2008 IBM Corporation
6. More ways to reference tables: TABID attribute Query: SELECT * FROM T1 Rewriten query: SELECT Q1.C1 AS "C1", Q1.C2 AS "C2 FROM DDEACONU.T1 AS Q1 Use of TABID attribute in an ACCESS hint: SELECT * FROM T1 /*<OPTGUIDELINES> <TBSCAN TABID= Q1 /> </OPTGUIDELINES>*/ OR SELECT * FROM T1 /*<OPTGUIDELINES> <IXSCAN TABID= Q1 /> </OPTGUIDELINES>*/ 44 2008 IBM Corporation
6. Specifying exposed names in ACCESS hints: Query: SELECT * FROM T1 as TAB Use of exposed name in an ACCESS hint: SELECT * FROM T1 /*<OPTGUIDELINES> <TBSCAN TABLE= TAB /> </OPTGUIDELINES>*/ Note: SELECT * FROM T1 /*<OPTGUIDELINES> <TBSCAN TABLE= T1 /> </OPTGUIDELINES>*/ Will return SQL0437 rc13, EXP0009W: TABLE attribute not found 45 2008 IBM Corporation
6. Accessing base tables through views: Tables, indexes and views: CREATE TABLE T1 (C1 INT, C2 INT); CREATE INDEX T1_I1 ON T1 (C1); CREATE VIEW V1 AS SELECT * FROM T1 as TAB; CREATE VIEW V2 AS SELECT C1 FROM V1; Query: SELECT C1 FROM v2 /*<OPTGUIDELINES> <TBSCAN TABLE= V2/V1/TAB /> </OPTGUIDELINES>*/; 46 2008 IBM Corporation
7. User Defined Function C sample void SQL_API_FN ToNum ( SQLUDF_DOUBLE *inamt, SQLUDF_CHAR *outval, SQLUDF_SMALLINT *amtnullind, SQLUDF_SMALLINT *valnullind, SQLUDF_TRAIL_ARGS_ALL, SQLUDF_DBINFO *dbinfo) { int intvalue, charlen; int i, j,k; char charvalue[30], *charp, *retp, *retvalue; char format[30]; sprintf(charvalue, "%f", *inamt); for ( k = 0 ; k < strlen(charvalue) ; k++ ) if ( charvalue[k] == '.' ) break; charlen = k; retp = retvalue = (char *)malloc(charlen + charlen/3 + 5); charp = charvalue; if (charvalue[0] == '-') { *retp++ = *charp++; charlen -= 1; } while (charlen > 3) { i = charlen % 3; if (i == 0) i = 3; for (j=0; j<i; j++) *retp++ = *charp++; *retp++ = ','; charlen -= i; } for (j=0; j<charlen; j++) *retp++ = *charp++; *retp = '\0'; strcpy(outval,retvalue); *valnullind = 0; # # Build procedure # db2 connect to sample db2 prep kc.sqc bindfile db2 bind kc.bnd cl -Zi -Od -c -W2 -DWIN32 -MD kc.c link -debug -out:kc.dll -dll kc.obj db2api.lib -def:kc.def copy kc.dll "c:\sqllib\function" return; 47 2008 IBM Corporation }
7. User Defined Function C sample void SQL_API_FN ToHg ( SQLUDF_DOUBLE *inamt, SQLUDF_CHAR *outval, SQLUDF_SMALLINT *amtnullind, SQLUDF_SMALLINT *valnullind, SQLUDF_TRAIL_ARGS_ALL, SQLUDF_DBINFO *dbinfo) { /* static char *suja[]={" 영 "," 일 "," 이 "," 삼 "," 사 "," 오 "," 육 "," 칠 "," 팔 "," 구 "}; static char *suja[]={" 영 "," 壹 "," 貳 "," 參 "," 四 "," 五 "," 六 "," 七 "," 八 "," 九 "}; */ static char *suja[]={" 영 "," 일 "," 이 "," 삼 "," 사 "," 오 "," 육 "," 칠 "," 팔 "," 구 "}; char charvalue[100], *charp; char retv[100], *retp, *returnvalue; char tmp_su[1]; int charlen,cur_num; int i,j,k; memset(retv,0x00,sizeof(retv)); retp = returnvalue = retv; sprintf( charvalue,"%f", *inamt); charp = charvalue; for ( i = 0; i < strlen(charvalue); i++ ) if ( charvalue[i] == '.' ) break; charlen = i; if ( charvalue[0] == '-' ) { sprintf(retp,"%c",*charp++); retp += strlen(retp); charlen--; } 48 2008 IBM Corporation
7. User Defined Function C sample while ( charlen > 0 ) { i = charlen % 4 ; if ( i == 0 ) i = 4; k = i; for ( j = 0; j < i ; j++ ) { sprintf(tmp_su,"%c",*charp++); cur_num = atoi(tmp_su); if ((cur_num>=1)&&(cur_num<= 9)) { strncpy(retp,suja[cur_num],2); retp += strlen(retp); switch ( k ){ case 4 : strncpy(retp," 阡 ",2); retp += strlen(retp); break; case 3 : strncpy(retp," 百 ",2); retp += strlen(retp); break; case 2 : strncpy(retp," 拾 ",2); retp += strlen(retp); break; } } charlen--; k--; } 49 2008 IBM Corporation
7. User Defined Function C sample # Build procedure db2 connect to sample db2 prep kc.sqc bindfile db2 bind kc.bnd cl -Zi -Od -c -W2 -DWIN32 -MD kc.c link -debug -out:kc.dll -dll kc.obj db2api.lib -def:kc.def copy kc.dll "C:\Program Files\IBM\SQLLIB\FUNCTION" CREATE FUNCTION TOHG(DOUBLE) RETURNS VARCHAR(45) EXTERNAL NAME 'kc!tohg' FENCED LANGUAGE C PARAMETER STYLE SQL DETERMINISTIC NO SQL NO EXTERNAL ACTION ALLOW PARALLEL DBINFO; CREATE FUNCTION ToHg(INTEGER) RETURNS VARCHAR(45) SOURCE TOHG(DOUBLE); CREATE FUNCTION ToHg(SMALLINT) RETURNS VARCHAR(45) SOURCE TOHG(DOUBLE); CREATE FUNCTION TONUM(DOUBLE) RETURNS VARCHAR(45) EXTERNAL NAME 'kc!tonum' FENCED LANGUAGE C PARAMETER STYLE SQL DETERMINISTIC NO SQL NO EXTERNAL ACTION ALLOW PARALLEL DBINFO; CREATE FUNCTION TONUM(INTEGER) RETURNS VARCHAR(45) SOURCE TONUM(DOUBLE); CREATE FUNCTION TONUM(SMALLINT) RETURNS VARCHAR(45) SOURCE TONUM(DOUBLE); 50 2008 IBM Corporation
8. 타 DBMS 와연계 WFS DEMO -------------------------------------------------------------- -------------------------------------------------------------- -- -- 환경정의 -- -------------------------------------------------------------- -------------------------------------------------------------- connect to sample; drop wrapper net8; create wrapper net8; CREATE SERVER ORCL_SVR TYPE ORACLE VERSION 10 WRAPPER NET8 --AUTHORIZATION SCOTT PASSWORD TIGER OPTIONS ( NODE 'orcl', ADD CPU_RATIO '1.0', COLLATING_SEQUENCE 'N', IO_RATIO '1.0', COMM_RATE '1.0', DB2_TWO_PHASE_COMMIT 'Y', VARCHAR_NO_TRAILING_BLANKS 'N', PUSHDOWN 'Y', DB2_MAXIMAL_PUSHDOWN 'Y' ); create user mapping for user server orcl_svr options ( remote_authid 'scott', remote_password 'tiger'); commit; 51 2008 IBM Corporation
8. 타 DBMS 와연계 -------------------------------------------------------------- -------------------------------------------------------------- -- -- Table 연계 : Nickname 생성 -- -------------------------------------------------------------- -------------------------------------------------------------- create nickname ora_dept for orcl_svr.scott.dept; create nickname ora_emp for orcl_svr.scott.emp; commit; -------------------------------------------------------------- -------------------------------------------------------------- -- -- Table 연계 : 분산 Data 처리 -- -------------------------------------------------------------- -------------------------------------------------------------- drop table db2_dept ; drop table db2_emp ; create table db2_dept like ora_dept; create table db2_emp like ora_emp ; insert into db2_dept select * from ora_dept; insert into db2_emp select * from ora_emp ; export to ora_dept.del of del select * from ora_dept; export to ora_emp.del of del select * from ora_emp; commit; 52 2008 IBM Corporation
8. 타 DBMS 와연계 -------------------------------------------------------------- -------------------------------------------------------------- -- -- Table 연계 : Nickname 생성 -- -------------------------------------------------------------- -------------------------------------------------------------- create nickname ora_dept for orcl_svr.scott.dept; create nickname ora_emp for orcl_svr.scott.emp; commit; -------------------------------------------------------------- -------------------------------------------------------------- -- -- Table 연계 : 분산 Data 처리 -- -------------------------------------------------------------- -------------------------------------------------------------- drop table db2_dept ; drop table db2_emp ; create table db2_dept like ora_dept; create table db2_emp like ora_emp ; insert into db2_dept select * from ora_dept; insert into db2_emp select * from ora_emp ; export to ora_dept.del of del select * from ora_dept; export to ora_emp.del of del select * from ora_emp; commit; 53 2008 IBM Corporation
8. 타 DBMS 와연계 -------------------------------------------------------------- -------------------------------------------------------------- -- -- Table 연계 : 분산 Query 수행 -- -------------------------------------------------------------- -------------------------------------------------------------- select empno, ename, job, a.deptno, b.dname from ora_emp a, ora_dept b where a.deptno = b.deptno; select empno, ename, job, a.deptno, b.dname from db2_emp a, db2_dept b where a.deptno = b.deptno; select empno, ename, job, a.deptno, b.dname from ora_emp a, db2_dept b where a.deptno = b.deptno; select empno, ename, job, a.deptno, b.dname from db2_emp a, ora_dept b where a.deptno = b.deptno; commit; 54 2008 IBM Corporation
8. 타 DBMS 와연계 -------------------------------------------------------------- -------------------------------------------------------------- -- -- set passthru : 원격지원 -- -------------------------------------------------------------- -------------------------------------------------------------- set passthru orcl_svr; drop table employee; drop table department; select * from tab; select * from all_users; set passthru reset; select * from tab; select * from all_users; 55 2008 IBM Corporation commit;
9. SQL 호환성 Data Type - DECFLOAT(16) / DECFLOAT(32) Function - ROWID - ROW CHANGE TOKEN / ROW CHANGE TIMESTAMP - TO_CHAR / TO_DATE - START WITH name = 'Goyal' - CONNECT BY PRIOR empid = mgrid - ORDER SIBLINGS BY salary; - FETCH FIRST n ROWS / ROW_NUMBER() / ROWNUM - NVL / DECODE - LEAST / GREATEST - BITAND, BITANDNOT, BITOR, BITXOR, BITNOT 56 2008 IBM Corporation
9. SQL 호환성 DUAL - select current date from dual Outer Join (+) - select * from org a, org2 b where a.deptnumb = b.deptnumb - select * from org a, org2 b where a.deptnumb(+) = b.deptnumb - select * from org a, org2 b where a.deptnumb = b.deptnumb(+) SEQUENCE - sequence-name.nextval can be specified in place of NEXT VALUE FOR sequence-name - sequence-name.currval can be specified in place of PREVIOUS VALUE FOR sequencename 57 2008 IBM Corporation
9. SQL 호환성 SELECT LEVEL, cast(sys_connect_by_path(name, '-') as char(30))as Path, EMPNO, MGR FROM EMP3 START WITH EMPNO = 7839 CONNECT BY PRIOR EMPNO = MGR Order siblings by name ; SELECT LEVEL,cast(SYS_CONNECT_BY_PATH(Name, '-') as char(30))as Path,empno, mgr, Name FROM EMP3 START WITH Name = 'KING' CONNECT BY PRIOR empno = mgr ORDER SIBLINGS BY Name ; 58 2008 IBM Corporation
Q&A Q&A 59 2008 IBM Corporation