前提:
Denodo Express インストール、起動済み
SQL Server 2022インストール、起動済み
-- 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. テスト用テーブル、データの作成(SQL Serverデータベースで実施)
drop table tab1;
create table tab1(
col1 int not null
, col2 bigint not null
, col3 numeric(20,5) not null
, col4 datetime2
, col5 char(10)
, col6 varchar(10)
, col7 varchar(max)
, col8 nchar(10)
, col9 nvarchar(10)
, col10 nvarchar(max)
, col11 binary (10)
, col12 varbinary(10)
, col13 varbinary(max)
);
alter table tab1 add constraint tab1pk primary key(col1);
create index ind11 on tab1(col2);
create index ind12 on tab1(col3);
sp_help tab1
insert into tab1 values(1,2,3,getdate(),'a','a','a','a','a','a',cast('a' as varbinary),cast('a' as varbinary),cast('a' as varbinary) );
select * from tab1;
-- 3. データソース作成
※jdbc接続文字でencrypt=falseが必要
CREATE OR REPLACE DATASOURCE JDBC ds01
FOLDER = '/01ds'
DRIVERCLASSNAME = 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
DATABASEURI = 'jdbc:sqlserver://192.168.137.62:1433;databaseName=test;encrypt=false'
USERNAME = 'sa'
USERPASSWORD = 'TSm29mYuTPkEfOlkRLC/AYrbdc0J37Lw0Q4kptQow92X2mAOCTeRi4TfUx1YrzdeegikQYVDvWe29uXhPD1zQK2otBM5T5AgHLEX+YcT3LVp6Rz2hCmdJ5R1xdpKtvSR' ENCRYPTED
CLASSPATH = 'mssql-jdbc-10.x'
DATABASENAME = 'sqlserver'
DATABASEVERSION = '2019'
FETCHSIZE = 1000
VALIDATIONQUERY = 'Select 1'
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
KERBEROSPROPERTIES ('authenticationScheme'='JavaKerberos', 'integratedSecurity'='true')
DATA_LOAD_CONFIGURATION (
BATCHINSERTSIZE = 1000
);
-- 4. ラッパー作成
CREATE OR REPLACE WRAPPER JDBC b_tab1
FOLDER = '/02bv'
DATASOURCENAME=ds01
CATALOGNAME='test'
SCHEMANAME='dbo'
RELATIONNAME='tab1'
OUTPUTSCHEMA (
col1 = 'col1' :'java.lang.Integer' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='10', sourcetypeid='4', sourcetypename='int') NOT NULL SORTABLE,
col2 = 'col2' :'java.lang.Long' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='19', sourcetypeid='-5', sourcetypename='bigint') NOT NULL SORTABLE,
col3 = 'col3' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='5', sourcetyperadix='10', sourcetypesize='20', sourcetypeid='2', sourcetypename='numeric') NOT NULL SORTABLE,
col4 = 'col4' :'java.time.LocalDateTime' (OPT) (sourcetypedecimals='7', sourcetypesize='27', sourcetypeid='93', sourcetypename='datetime2') SORTABLE,
col5 = 'col5' :'java.lang.String' (OPT) (sourcetypesize='10', sourcetypeid='1', sourcetypename='char') SORTABLE,
col6 = 'col6' :'java.lang.String' (OPT) (sourcetypesize='10', sourcetypeid='12', sourcetypename='varchar') SORTABLE,
col7 = 'col7' :'java.lang.String' (OPT) (sourcetypesize='2147483647', sourcetypeid='12', sourcetypename='varchar') SORTABLE,
col8 = 'col8' :'java.lang.String' (OPT) (sourcetypesize='10', sourcetypeid='-15', sourcetypename='nchar') SORTABLE,
col9 = 'col9' :'java.lang.String' (OPT) (sourcetypesize='10', sourcetypeid='-9', sourcetypename='nvarchar') SORTABLE,
col10 = 'col10' :'java.lang.String' (OPT) (sourcetypesize='2147483647', sourcetypeid='-9', sourcetypename='nvarchar') SORTABLE,
col11 = 'col11' :'java.sql.Blob' (OPT) (sourcetypesize='10', sourcetypeid='-2', sourcetypename='binary') NOT SORTABLE,
col12 = 'col12' :'java.sql.Blob' (OPT) (sourcetypesize='10', sourcetypeid='-3', sourcetypename='varbinary') NOT SORTABLE,
col13 = 'col13' :'java.sql.Blob' (OPT) (sourcetypesize='2147483647', sourcetypeid='-3', sourcetypename='varbinary') NOT SORTABLE
)
CONSTRAINT 'tab1pk' PRIMARY KEY ( 'col1' )
INDEX 'tab1pk' CLUSTER UNIQUE PRIMARY ( 'col1' )
INDEX 'ind11' OTHER ( 'col2' )
INDEX 'ind12' OTHER ( 'col3' );
-- 5. 基本ビュー作成
CREATE OR REPLACE TABLE b_tab1 I18N jp (
col1:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '10'),
col2:long (notnull, sourcetypeid = '-5', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '19'),
col3:decimal (notnull, sourcetypeid = '2', sourcetyperadix = '10', sourcetypedecimals = '5', sourcetypesize = '20'),
col4:timestamp (sourcetypeid = '93', sourcetypedecimals = '7', sourcetypesize = '27'),
col5:text (sourcetypeid = '1', sourcetypesize = '10'),
col6:text (sourcetypeid = '12', sourcetypesize = '10'),
col7:text (sourcetypeid = '12', sourcetypesize = '2147483647'),
col8:text (sourcetypeid = '-15', sourcetypesize = '10'),
col9:text (sourcetypeid = '-9', sourcetypesize = '10'),
col10:text (sourcetypeid = '-9', sourcetypesize = '2147483647'),
col11:blob (sourcetypeid = '-2', sourcetypesize = '10'),
col12:blob (sourcetypeid = '-3', sourcetypesize = '10'),
col13:blob (sourcetypeid = '-3', sourcetypesize = '2147483647')
)
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 col8 (any) OPT ANY
ADD col9 (any) OPT ANY
ADD col10 (any) OPT ANY
ADD col11 NOS ZERO ()
ADD col12 NOS ZERO ()
ADD col13 NOS ZERO ()
)
OUTPUTLIST (col1, col10, col11, col12, col13, col2, col3, col4, col5, col6, col7, col8, col9
)
WRAPPER (jdbc b_tab1)
);
-- 6. インターフェイスビュー作成
CREATE OR REPLACE INTERFACE VIEW i_tab1 (
col1:int (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '4'),
col2:long (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '19', sourcetypeid = '-5'),
col3:decimal (sourcetypedecimals = '5', sourcetyperadix = '10', sourcetypesize = '20', sourcetypeid = '2'),
col4:timestamp (sourcetypedecimals = '7', sourcetypesize = '27', sourcetypeid = '93'),
col5:text (sourcetypesize = '10', sourcetypeid = '1'),
col6:text (sourcetypesize = '10', sourcetypeid = '12'),
col7:text (sourcetypesize = '2147483647', sourcetypeid = '12'),
col8:text (sourcetypesize = '10', sourcetypeid = '-15'),
col9:text (sourcetypesize = '10', sourcetypeid = '-9'),
col10:text (sourcetypesize = '2147483647', sourcetypeid = '-9'),
col11:blob (sourcetypesize = '10', sourcetypeid = '-2', contenttype = 'autodetect'),
col12:blob (sourcetypesize = '10', sourcetypeid = '-3', contenttype = 'autodetect'),
col13:blob (sourcetypesize = '2147483647', sourcetypeid = '-3', contenttype = 'autodetect')
)
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;
