前提:
Denodo Express インストール、起動済み
Oracle19cインストール、起動済み
-- 1. 事前準備
-- 1.1 データベース作成
DROP DATABASE db01 cascade;
CREATE OR REPLACE DATABASE db01 'description of db01'
VCS OFF
CHARSET UNICODE
AUTHENTICATION LOCAL
ODBC AUTHENTICATION NORMAL
CHECK_VIEW_RESTRICTIONS ALWAYS
;
-- 1.2 フォルダ作成
CONNECT DATABASE db01;
CREATE FOLDER '/01ds' DESCRIPTION 'description of folder01';
CREATE FOLDER '/02bv' DESCRIPTION 'description of folder02';
CREATE FOLDER '/03iv' DESCRIPTION 'description of folder03';
-- 2. テスト用テーブル、データの作成(Oracleデータベースで実施)
drop table tab1 purge;
create table tab1(
col1 number not null
, col2 number(20,5) not null
, col3 timestamp not null
, col4 char(10)
, col5 varchar2(10)
, col6 nchar(10)
, col7 nvarchar2(10)
, col8 blob
, col9 clob
);
alter table tab1 add constraint tab1pk primary key(col1);
create index ind11 on tab1(col2);
create index ind12 on tab1(col3);
desc tab1
insert into tab1 values(1,2,systimestamp,'a','a','a','a','a','a');
commit;
select * from tab1;
-- 3. データソース作成
CREATE OR REPLACE DATASOURCE JDBC ds01
FOLDER = '/01ds'
DRIVERCLASSNAME = 'oracle.jdbc.OracleDriver'
DATABASEURI = 'jdbc:oracle:thin:@192.168.137.65:1521/pdb1.example.com'
USERNAME = 'test'
USERPASSWORD = 'JfvgDy/DbIFmBcvNOfXXzvUXWYWnp7zuk/RgwTTvYx6cYIKreZ9tP1Bi5rrcdLmNGTrL1JoOvwFW6o3aRPqS2lUli/PiyOlgUrQglyiOMxfh89Wpe2/SEeNUpcTHKjgK' ENCRYPTED
CLASSPATH = 'oracle-21c'
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 "b_TAB1"
FOLDER = '/02bv'
DATASOURCENAME=ds01
SCHEMANAME='TEST'
RELATIONNAME='TAB1'
OUTPUTSCHEMA (
"COL1" = 'COL1' :'java.math.BigDecimal' (OPT) (sourcetyperadix='10', sourcetypeid='2', sourcetypename='NUMBER') NOT NULL SORTABLE,
"COL2" = 'COL2' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='5', sourcetyperadix='10', sourcetypesize='20', sourcetypeid='2', sourcetypename='NUMBER') NOT NULL SORTABLE,
"COL3" = 'COL3' :'java.time.LocalDateTime' (OPT) (sourcetypedecimals='6', sourcetyperadix='10', sourcetypesize='11', sourcetypeid='93', sourcetypename='TIMESTAMP(6)') NOT NULL SORTABLE,
"COL4" = 'COL4' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='10', sourcetypeid='1', sourcetypename='CHAR') SORTABLE,
"COL5" = 'COL5' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='10', sourcetypeid='12', sourcetypename='VARCHAR2') SORTABLE,
"COL6" = 'COL6' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='10', sourcetypeid='-15', sourcetypename='NCHAR') SORTABLE,
"COL7" = 'COL7' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='10', sourcetypeid='-9', sourcetypename='NVARCHAR2') SORTABLE,
"COL8" = 'COL8' :'java.sql.Blob' (OPT) (sourcetyperadix='10', sourcetypeid='2004', sourcetypename='BLOB') NOT SORTABLE,
"COL9" = 'COL9' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypeid='2005', sourcetypename='CLOB') NOT SORTABLE
)
CONSTRAINT 'TAB1PK' PRIMARY KEY ( 'COL1' )
INDEX 'TAB1PK' CLUSTER UNIQUE PRIMARY ( 'COL1' )
INDEX 'IND11' CLUSTER ( 'COL2' )
INDEX 'IND12' CLUSTER ( 'COL3' );
-- 5. 基本ビュー作成
CREATE OR REPLACE TABLE "b_TAB1" I18N jp (
"COL1":decimal (notnull, sourcetypeid = '2', sourcetyperadix = '10'),
"COL2":decimal (notnull, sourcetypeid = '2', sourcetyperadix = '10', sourcetypedecimals = '5', sourcetypesize = '20'),
"COL3":timestamp (notnull, sourcetypeid = '93', sourcetyperadix = '10', sourcetypedecimals = '6', sourcetypesize = '11'),
"COL4":text (sourcetypeid = '1', sourcetyperadix = '10', sourcetypesize = '10'),
"COL5":text (sourcetypeid = '12', sourcetyperadix = '10', sourcetypesize = '10'),
"COL6":text (sourcetypeid = '-15', sourcetyperadix = '10', sourcetypesize = '10'),
"COL7":text (sourcetypeid = '-9', sourcetyperadix = '10', sourcetypesize = '10'),
"COL8":blob (sourcetypeid = '2004', sourcetyperadix = '10'),
"COL9":text (sourcetypeid = '2005', sourcetyperadix = '10')
)
FOLDER = '/02bv'
CONSTRAINT 'TAB1PK' PRIMARY KEY ( 'COL1' )
CACHE OFF
TIMETOLIVEINCACHE DEFAULT
ADD SEARCHMETHOD "b_TAB1"(
I18N jp
CONSTRAINTS (
ADD "COL1" (any) OPT ANY
ADD "COL2" (any) OPT ANY
ADD "COL3" (any) OPT ANY
ADD "COL4" (any) OPT ANY
ADD "COL5" (any) OPT ANY
ADD "COL6" (any) OPT ANY
ADD "COL7" (any) OPT ANY
ADD "COL9" (any) OPT ANY
ADD "COL8" NOS ZERO ()
)
OUTPUTLIST ("COL1", "COL2", "COL3", "COL4", "COL5", "COL6", "COL7", "COL8", "COL9"
)
WRAPPER (jdbc "b_TAB1")
);
-- 6. インターフェイスビュー作成
CREATE OR REPLACE INTERFACE VIEW "i_TAB1" (
"COL1":decimal (sourcetyperadix = '10', sourcetypeid = '2'),
"COL2":decimal (sourcetypedecimals = '5', sourcetyperadix = '10', sourcetypesize = '20', sourcetypeid = '2'),
"COL3":timestamp (sourcetypedecimals = '6', sourcetyperadix = '10', sourcetypesize = '11', sourcetypeid = '93'),
"COL4":text (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '1'),
"COL5":text (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '12'),
"COL6":text (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '-15'),
"COL7":text (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '-9'),
"COL8":blob (sourcetyperadix = '10', sourcetypeid = '2004', contenttype = 'autodetect'),
"COL9":text (sourcetyperadix = '10', sourcetypeid = '2005')
)
SET IMPLEMENTATION "b_TAB1"
FOLDER = '/03iv';
-- 7. 動作確認
SELECT * FROM db01."b_TAB1" CONTEXT('cache_wait_for_load' = 'true') TRACE;
SELECT * FROM db01."i_TAB1" CONTEXT('cache_wait_for_load' = 'true') TRACE;
