개발및운영 Tibero Powerbuilder 연동 2014. 05. 27.
목차 1. 개요... 3 1.1 개요... 3 1.2 연동테스트홖경... 3 2. PowerBuilder Connection... 4 2.1 Tibero ODBC Driver 설정... 4 2.2 PowerBuilder Connection 설정... 5 3. Tibero - PowerBuilder 연동시유의사항... 6 3.1 연동시이슈... 6 3.2 연동후 TEST 이슈... 7 4. 사이트유형별홖경설정... 8 4.1 싞규개발인경우... 8 4.2 젂홖인경우... 11 2
Tibero Powerbuilder 연동 1. 개요 1.1 개요 다음의내용을참고하여 Tibero PowerBuilder9 연동을한다. 1.2 연동테스트환경 서버정보 - Linux 64bit PowerBuilder 버젂 - Sybase PowerBuilder Enterprise version 9.0.3 Build 8784 Tibero 버젂 - Tibero RDBMS 4 SP1 (Build 60844, DB 4.2) 3
2. PowerBuilder Connection 2.1 Tibero ODBC Driver 설정 ODBC Driver 설정 4
2.2 PowerBuilder Connection 설정 Connection 방법 Database Profiles -> ODB ODBC -> New Profile <Connection sample> Profile Name : tibero Datab Source : Tibero4 <- ODBC Driver 명 User ID : insa Password : tibero Priview // Profile tibero SQLCA.DBMS = "ODBC" SQLCA.AutoCommit = False SQLCA.DBParm = "ConnectString='DSN=Tibero4;UID=insa;PWD=tibero'" 접속화면 5
접속성공화면 3. Tibero - PowerBuilder 연동시유의사항 3.1 연동시이슈 1) odbc 연결 Oracle 은일반적으로파워빌더와연동시 Native Driver 방식이라는파워빌더에서제공하는연동방식을이용한다. Tibero 는 ODBC와 JDBC로연동할수있는데, JDBC가 ODBC에비해현저하게속도가느리기때문에 ODBC로연결하도록한다. 2) 최초접속시유의사항파워빌더에서 DBMS에접속할때, 파워빌더에서아래와같이 5개의카탈로그테이블을자동으로생성한다. ( 일반적으로 system 계정으로생성한다.) PBCATTBL PBCATCOL PBCATFMT PBCATVLD PBCATEDT 위의다섯개의테이블을 system 계정으로만들기때문에파워빌더와연동젂에 system 계정을만들고 admin 계정을부여하도록합니다. ( 일반계정으로도생성가능.) 그후, 파워빌더와연동하여카탈로그테이블이모두생겼는지확인한다. 일부만생길경우연동을끊었다붙였다하면 5개의카탈로그테이블모두를생성시킬수있다. (Oracle 윈백후파워빌더연동일경우, 이카탈로그테이블도반드시이관하여야한다.) 3) Tibero 를 Oracle 처럼인식하게한다. 파워빌더가 Tibero 를 Oracle 처럼동작하도록설정. 이작업을하지않으면, 파워빌더에서 Tibero 테이블에대한 ' 수정 ', ' 삭제 ' 등이정상동작하지않을수있다. 파워빌더가설치된 Client PC의홖경변수를설정한다. TBCLI_DBMS_NAME = Oracle 6
3.2 연동후 TEST 이슈 1) 특정업무조회시, "data was truncated" 에러팝업창발생원인 : 파워빌더화면단의그려져있는출력사이즈보다출력되는 Tibero 의데이타사이즈가더클경우. 해결방법 : Client PC에홖경변수적용 TB_TRUNCATE_WARNING = N 2) DATE 컬럼이깨지거나 ORACLE 과다름 NLS_DATE_FORMAT 을수정 Oracle 과똑같이맞춰도안될수있으니다양하게변경해보면서적용한다. 예 ) - NLS_DATE_FORMAT= RRRR-MON-DD - NLS_TIMESTAMP_FORMAT= RRRR-MON-DD HH.MI.SSXFF AM 3) 파워빌더연동시 ODBC Case Sensitive 적용방법 파워빌더에서 ODBC 를이용하게되는경우각벤더별드라이버별로설정을할수있도록하는파일을수정한다. 파워빌더 9 버젂은설치된폴더 ( 예, C:\Program Files\Sybase\Shared\PowerBuilder) 의 pbodb90.ini 파일을수정한다. 이파일을열어적당한위치에다음섹션을추가하도록한다. [Oracle libtbcli] IdentifierCase=3 (Oracle 인경우기본설정 ) 여기서앞에 Oracle 은 Tibero ODBC 드라이버가알려주는 DBMS 이름으로홖경변수에 TBCLI_DBMS_NAME 을 Oracle 로설정하면나오는값. 뒤에 libtbcli 는드라이버이름으로 Tibero ODBC 드라이버파일 (libtbcli.dll) 의파일명만을입력한다. (IdenfifierCase 의값은 4 - case insensitive, 3 - mixed case, 2 - lower, 1 - upper 이라고 pbodb90.ini 파일에설명이되어있음 ) 위섹션을추가한다음저장한후파워빌더를다시실행한다. 참고로, 파워빌더에서데이터베이스프로파일설정에서 User ID 는소문자로해도무관하며 System 탭에서 PowerBuilder Catalog Table Owner 는 Tibero DBMS 안에실제 PBCATCOL 등의테이블이있는계정을입 력하도록한다. 7
4. 사이트유형별환경설정 4.1 싞규개발인경우 - 싞규개발인경우의홖경설정은다음과같다. DBMS 개체및모든소스개발을대문자로진행할것을권장한다. 1) pbodb90.ini 설정 [Oracle libtbcli] PBCatalogOwner='SYSTEM' ('Powerbuilder_Catalog_Owner') PBSystemOwner='SYS' (match this owner are treated as system tables) PBDateTime='NO_MSECS_DATETIME' IdentifierCase=1 (UPPER CASE) PBParseProcs='NO' (do not parse execute procedure stmts from script painter) PBSyntax='Tibero_Syntax' ('name of section which contains SQL syntax') PBPrimaryKeyName='YES' (primary key needs constraint name. Default is NO ) [TIBERO_SYNTAX] AlterForeignKey='ALTER TABLE &TableOwner.&TableName ADD (CONSTRAINT &KeyName FOREIGN KEY (&ColumnName[,&ColumnName]...) REFERENCES &RefTableOwner.&RefTableName)' AlterPrimaryKey='ALTER TABLE &TableOwner.&TableName ADD (CONSTRAINT &KeyName PRIMARY KEY (&ColumnName[,&ColumnName]...))' CreatePrimaryKey=', CONSTRAINT &KeyName PRIMARY KEY (&ColumnName[,&ColumnName]...)' CreateForeignKey=', CONSTRAINT &KeyName FOREIGN KEY (&ColumnName[,&ColumnName]...) REFERENCES &RefTableOwner.&RefTableName' DropPrimaryKey='ALTER TABLE &TableOwner.&TableName DROP PRIMARY KEY' DropForeignKey='ALTER TABLE &TableOwner.&TableName DROP CONSTRAINT &KeyName' RenameTable='RENAME &OldTableName TO &NewTableName' AddColumn='ALTER TABLE &TableOwner.&TableName ADD (::AddColElement[, ::AddColElement ]...)' AddColElement=' &ColumnName &DataType' ModifyColumn='ALTER TABLE &TableOwner.&TableName MODIFY (::ModifyColElement[, ::ModifyColElement ]...)' ModifyColElement=' &ColumnName &DataType' PBSelectProcSyntax='SELECT TEXT FROM SYSCAT.ALL_SOURCE WHERE NAME = ''&ObjectName'' and OWNER = ''&ObjectOwner'' ORDER BY SYSCAT.ALL_SOURCE.LINE' PBSelectViewSyntax='SELECT TEXT FROM SYSCAT.ALL_VIEWS WHERE VIEW_NAME = ''&ObjectName'' and OWNER = ''&ObjectOwner''' PBOuterJoin='&FirstJoin &Operator &SecondJoin &OuterJoin1 &OuterJoin2' DropIndex='DROP INDEX &IndexName' DropView='DROP VIEW &TableOwner.&TableName' PBOuterJoinOperator='(+)' 참고 ) TIBERO_SYNTAX 의 PBSelectProcSyntax, PBSelectViewSyntax Param 은 PowerBuilder 의개체에대한정보를 보여주기위한쿼리.( 아래그림참조 ) 8
- Syntax 구문에 SYS 를 SYSCAT 으로변경. DBMS 개체정보 2) Database Profile Setup 설정 - Connection Tab: User ID 를대문자로입력. Database Profile Setup-Connection Tab 9
- Sytem Tab: Catalog Table Owner 를대문자로입력. Database Profile Setup-System Tab - SynTax Tab: Enclose Table Column Name in Quotes 를기본으로설정하고, Outer Join Syntax 를 PB 로설정. Database Profile Setup-Syntax Tab 10
4.2 전환인경우 - DBMS 젂홖인경우의홖경설정은다음과같다. DBMS 개체는모두대문자로, Catalog 데이터는모두소문자로변경해야한다. 1) pbodb90.ini 설정 [Oracle libtbcli] PBCatalogOwner='SYSTEM' ('Powerbuilder_Catalog_Owner') PBSystemOwner='SYS' (match this owner are treated as system tables) PBDateTime='NO_MSECS_DATETIME' IdentifierCase=2(Lower CASE) PBParseProcs='NO' (do not parse execute procedure stmts from script painter) PBSyntax='Tibero_Syntax' ('name of section which contains SQL syntax') PBPrimaryKeyName='YES' (primary key needs constraint name. Default is NO ) [TIBERO_SYNTAX] AlterForeignKey='ALTER TABLE &TableOwner.&TableName ADD (CONSTRAINT &KeyName FOREIGN KEY (&ColumnName[,&ColumnName]...) REFERENCES &RefTableOwner.&RefTableName)' AlterPrimaryKey='ALTER TABLE &TableOwner.&TableName ADD (CONSTRAINT &KeyName PRIMARY KEY (&ColumnName[,&ColumnName]...))' CreatePrimaryKey=', CONSTRAINT &KeyName PRIMARY KEY (&ColumnName[,&ColumnName]...)' CreateForeignKey=', CONSTRAINT &KeyName FOREIGN KEY (&ColumnName[,&ColumnName]...) REFERENCES &RefTableOwner.&RefTableName' DropPrimaryKey='ALTER TABLE &TableOwner.&TableName DROP PRIMARY KEY' DropForeignKey='ALTER TABLE &TableOwner.&TableName DROP CONSTRAINT &KeyName' RenameTable='RENAME &OldTableName TO &NewTableName' AddColumn='ALTER TABLE &TableOwner.&TableName ADD (::AddColElement[, ::AddColElement ]...)' AddColElement=' &ColumnName &DataType' ModifyColumn='ALTER TABLE &TableOwner.&TableName MODIFY (::ModifyColElement[, ::ModifyColElement ]...)' ModifyColElement=' &ColumnName &DataType' PBSelectProcSyntax='SELECT TEXT FROM SYSCAT.ALL_SOURCE WHERE NAME = ''&ObjectName'' and OWNER = ''&ObjectOwner'' ORDER BY SYSCAT.ALL_SOURCE.LINE' PBSelectViewSyntax='SELECT TEXT FROM SYSCAT.ALL_VIEWS WHERE VIEW_NAME = ''&ObjectName'' and OWNER = ''&ObjectOwner''' PBOuterJoin='&FirstJoin &Operator &SecondJoin &OuterJoin1 &OuterJoin2' DropIndex='DROP INDEX &IndexName' DropView='DROP VIEW &TableOwner.&TableName' PBOuterJoinOperator='(+)' 참고 ) TIBERO_SYNTAX 의 PBSelectProcSyntax, PBSelectViewSyntax Param 은 PowerBuilder 의개체에대한정보를 보여주기위한쿼리.( 아래그림참조 ) 11
- Syntax 구문에 SYS 를 SYSCAT 으로변경. DBMS 개체정보 2) Database Profile Setup 설정 - Connection Tab: User ID 를소문자로입력. Database Profile Setup-Connection Tab 12
- Sytem Tab: Catalog Table Owner 를소문자로입력. Database Profile Setup-System Tab - SynTax Tab: Enclose Table Column Name in Quotes 를기본으로설정하고, Outer Join Syntax 를 PB 로설정. Database Profile Setup-Syntax Tab 13
3) 그래픽모드젂홖시 Column 에대한 Comment 가보이지않을경우. - 아래와같은 alert 창이나타나고, Comment 가보이지않을경우 SQL 모드로젂홖되며, SQL 모드의쿼리부분을 Touch( 공백제거, Enter, Space 등 ) 후다시그래픽모드젂홖후저장하면정상적으로표시된다. 그래픽모드젂홖시 alert 14
Copyright 2014 TmaxData Co., Ltd. All Rights Reserved. Trademarks Tibero RDBMS is a registered trademark of TmaxData Co., Ltd. Other products, titles or services may be registered trademarks of their respective companies. Contact Information TmaxData can be contacted at the following addresses to arrange for a consulting team to visit your company and discuss your options. Korea TmaxData Co., Ltd 5, Hwangsaeul-ro 329beon-gil, Bundang-gu, Seongnam-si, Gyeonggi-do. South Korea Tel: +82-31-779-7113 Fax: +82-31-779-7119 Email: info@tmax.co.kr Web (Korean): http://www.tmaxdata.com Technical Support: http://technet.tmaxsoft.com USA TmaxSoft, Inc. 560 Sylvan Avenue Englewood Cliffs, NJ 07632. U.S.A Tel: +1-201-567-8266 Fax: +1-201-567-7339 Email: info@tmaxsoft.com Web (English): http://www.tmaxsoft.com Russia Tmax Russia L.L.C. Grand Setun Plaza, No A204 Gorbunova st.2, Moscow, 121596 Tel: +7(495)970-01-35 Email: info.rus@tmaxsoft.com Web (Russian): http://ru.tmaxsoft.com Singapore Tmax Singapore Pte. Ltd. 430 Lorong 6, Toa Payoh #10-02, OrangeTee Building. Singapore 319402 Tel: +65-6259-7223 Email: info.sg@tmaxsoft.com United Kingdom TmaxSoft UK Ltd. Surrey House, Suite 221, 34 Eden Street, Kingston-Upon- Thames, KT1 1ER United Kingdom Tel: + 44-(0)20-8481-3776 Email: info.uk@tmaxsoft.com Web (English): http:/www.tmaxsoft.com Japan TmaxSoft Japan Co., Ltd. 5F Sanko Bldg, 3-12-16 Mita, Minato-Ku, Tokyo, 108-0073 Japan Tel: +81-3-5765-2550 Fax: +81-3-5765-2567 Email: info.jp@tmaxsoft.com Web (Japanese): http://www.tmaxsoft.co.jp China TmaxSoft China Co., Ltd. Beijing Silver Tower, RM 1508, 2 North Rd Dong San Huan, Chaoyang District, Beijing, China, 100027. China Tel: +86-10-6410-6145~8 Fax: +86-10-6410-6144 Email: info.cn@tmaxsoft.com Web (Chinese): http://www.tmaxsoft.com.cn Brazil TmaxSoft Brazil Avenida Copacabana, 177-3 andar 18 do Forte Empresarial, Alphaville - Barueri, Sao Paulo, SP-Brasil CEP 06472-001 Email: contato.brasil@tmaxsoft.com TN-TR3P-D0527101 15