{Denodo}データソース・ラッパー・基本ビュー・インターフェイスビューの作成(ORACLE AUTONOMOUS DATABASE)

Denodo

 

https://community.denodo.com/kb/en/view/document/Accessing%20Oracle%20Autonomous%20Database%20from%20Denodo?category=Data%20Sources

 


前提: 
Denodo Express インストール、起動済み
OCI Autonomous Database Always Free 作成済み
Oracle Autonomous Database wallet fileをC:\testに格納して展開済み


-- 1. 準備

-- 1.1 wallet内のsqlnet.oraの修正
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
↓
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\test\Wallet_db01")))


-- 1.2 Oracle JDBCドライバーのインポート

https://www.oracle.com/jp/database/technologies/appdev/jdbc-downloads.html
Oracle Database 18c (18.15.0.0) JDBC Driver & UCP ダウンロード
ojdbc8-full.tar.gz
を展開すると下記が含まれている。

oraclepki.jar
osdt_core.jar
osdt_cert.jar

この3つと<DENODO_HOME>/lib/extensions/jdbc-drivers/oracle-21c  にあるjarを使用して
下記名称でインポートする

Resource type: jdbc_other
version: oracle-21c-custom


-- 2. データベース作成

-- 2.1 データベース作成
DROP DATABASE db07 cascade;

CREATE OR REPLACE DATABASE db07 'description of db07'
VCS OFF
CHARSET UNICODE
AUTHENTICATION LOCAL
ODBC AUTHENTICATION NORMAL
CHECK_VIEW_RESTRICTIONS ALWAYS
;

-- 2.2 フォルダ作成
CONNECT DATABASE db07;

CREATE FOLDER '/01ds'  DESCRIPTION 'description of folder01';
CREATE FOLDER '/02bv'  DESCRIPTION 'description of folder02';
CREATE FOLDER '/03iv'  DESCRIPTION 'description of folder03';

 

 

-- 3. データソース作成

※\は\\と記載必要


CREATE OR REPLACE DATASOURCE JDBC ds01
    FOLDER = '/01ds'
    DRIVERCLASSNAME = 'oracle.jdbc.OracleDriver'
    DATABASEURI = 'jdbc:oracle:thin:@db01_high?TNS_ADMIN="C:\\test\\Wallet_db01"'
    USERNAME = 'test'
    USERPASSWORD = 'XXXXX' ENCRYPTED
    CLASSPATH = 'oracle-21c-custom'
    DATABASENAME = 'oracle'
    DATABASEVERSION = '19c'
    FETCHSIZE = 1000
    VALIDATIONQUERY = 'SELECT COUNT(*) FROM SYS.DUAL'
    INITIALSIZE = 4
    MAXIDLE = -1
    MINIDLE = 0
    MAXACTIVE = 20
    EXHAUSTEDACTION = 1
    TESTONBORROW = true
    TESTONRETURN = false
    TESTWHILEIDLE = false
    TIMEBETWEENEVICTION = -1
    NUMTESTPEREVICTION = 3
    MINEVICTABLETIME = 1800000
    POOLPREPAREDSTATEMENTS = false
    MAXOPENPREPAREDSTATEMENTS = -1
    PROPERTIES ('remarksReporting'='false', 'includeSynonyms'='false')
    KERBEROSPROPERTIES ('oracle.net.authentication_services'='KERBEROS5')
    DATA_LOAD_CONFIGURATION (
        BATCHINSERTSIZE = 200
    );


-- 4. ラッパー作成


CREATE OR REPLACE WRAPPER JDBC "TAB1"
    FOLDER = '/02bv'
    DATASOURCENAME=ds01
    SCHEMANAME='TEST' 
    RELATIONNAME='TAB1' 
    OUTPUTSCHEMA (
        "COL1" = 'COL1' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='38', sourcetypeid='2', sourcetypename='NUMBER')  SORTABLE
    );

 

-- 5. 基本ビュー作成


CREATE OR REPLACE TABLE "b_TAB1" I18N jp (
        "COL1":decimal (sourcetypeid = '2', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '38')
    )
    FOLDER = '/02bv'
    CACHE OFF
    TIMETOLIVEINCACHE DEFAULT
    ADD SEARCHMETHOD "TAB1"(
        I18N jp
        CONSTRAINTS (
             ADD "COL1" (any) OPT ANY
        )
        OUTPUTLIST ("COL1"
        )
        WRAPPER (jdbc "TAB1")
    );

 

-- 6. インターフェイスビュー作成

CREATE OR REPLACE INTERFACE VIEW "i_TAB1" (
        "COL1":decimal (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '38', sourcetypeid = '2')
    )
    SET IMPLEMENTATION "b_TAB1"
    FOLDER = '/03iv';

 

-- 7. 動作確認

SELECT * FROM db07."b_TAB1" CONTEXT('cache_wait_for_load' = 'true') TRACE;

SELECT * FROM db07."i_TAB1" CONTEXT('cache_wait_for_load' = 'true') TRACE;