Grammar Definition

linkspec_stmt := CREATE LINKSPEC linkspec_name AS link_method;

linkindex_stmt := CREATE (FULL) LINKINDEX FOR tbl_name.col_name USING native_link;

link_method := native_link | link_clause_expr | UDF;

native_link := see Supported Links section below;

link_clause_expr := link_clause AND link_clause_expr |
 link_clause OR link_clause_expr |
 link_clause;

link_clause := LINK src_tbl_name.col_name WITH tgt_tbl_name.col_name USING link_terminal;

link_terminal := native_link | UDF | linkspec_name;

Supported Links

• synonym
• hyponym
• weightedJaccard
• token intersect
• Cosine
  
• BM25
• HMM

Required UDFs on DB2

CREATE FUNCTION Reverse( InStr VARCHAR(4000) ) RETURNS VARCHAR(4000) SPECIFIC REVERSEProcLang LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL BEGIN ATOMIC DECLARE RetVal VARCHAR(4000) DEFAULT ''; DECLARE I INTEGER; SET I = LENGTH(InStr); WHILE I > 0 DO SET RetVal = RetVal || SUBSTR(InStr, I, 1); SET I = I - 1; END WHILE; RETURN RetVal; END

Examples

1. Simple Link Using Synonym
2. Simple Link Using Weighted Jaccard, with Link Index on visits.diagnosis Column

Example 1. Simple Link Using Synonym

SELECT v.*, c.* FROM visits v, condition c WHERE LINK v.diagnosis WITH c.name USING synonym;
translated into
SELECT v.*, c.* FROM visits v, condition c WHERE AND v.diagnosis = c.name UNION SELECT v.*, c.* FROM synonym AS syn, visits v, condition c WHERE (src in (SELECT src FROM synonym s WHERE s.tgt = c.name)) AND v.diagnosis = syn.tgt;

Example 2. Simple Link Using Weighted Jaccard, with Link Index on Both Columns

create linkindex for visits.diagnosis using weightedJaccard; create linkindex for condition.name using weightedJaccard; SELECT visits.*, condition.* FROM visits, condition WHERE visits.tid < 50 LINK visits.diagnosis WITH condition.name USING weightedJaccard;
translated into
CREATE TABLE wj_tokens_visits__diagnosis (tid BIGINT, token VARCHAR(255)); INSERT INTO wj_tokens_visits__diagnosis ( SELECT tid, SUBSTR(diagnosis, integers.i, 3) as token FROM integers INNER JOIN visits ON integers.i <= LENGTH(diagnosis) + 2 ); CREATE INDEX wj_tokens_visits__diagnosis__tid ON wj_tokens_visits__diagnosis(tid); CREATE INDEX wj_tokens_visits__diagnosis__token ON wj_tokens_visits__diagnosis(token); CREATE TABLE wj_tokenweights_visits__diagnosis (token VARCHAR(255), weight FLOAT); INSERT INTO wj_tokenweights_visits__diagnosis ( SELECT token, LOG(size - df + 0.5) - LOG(df + 0.5) as weight FROM (SELECT token, count(*) as df FROM (SELECT * FROM wj_tokens_visits__diagnosis GROUP BY tid, token) f GROUP BY token ) D, ( SELECT count(*) as size FROM wj_tokens_visits__diagnosis ) S ); CREATE INDEX wj_tokenweights_visits__diagnosis__token ON wj_tokenweights_visits__diagnosis(token); CREATE TABLE wj_weights_visits__diagnosis (tid BIGINT, token VARCHAR(255), weight FLOAT); INSERT INTO wj_weights_visits__diagnosis ( SELECT tid, B.token, weight FROM wj_tokenweights_visits__diagnosis idf, (SELECT DISTINCT tid, token FROM wj_tokens_visits__diagnosis B) B WHERE B.token = idf.token ); CREATE INDEX wj_weights_visits__diagnosis__tid ON wj_weights_visits__diagnosis(tid); CREATE TABLE wj_sumweights_visits__diagnosis (tid BIGINT, sumw FLOAT); INSERT INTO wj_sumweights_visits__diagnosis ( SELECT tid, sum(weight) as sumw FROM wj_weights_visits__diagnosis GROUP BY tid ); CREATE INDEX wj_sumweights_visits__diagnosis__tid ON wj_sumweights_visits__diagnosis(tid); CREATE TABLE wj_tokens_condition__name (tid BIGINT, token VARCHAR(255)); INSERT INTO wj_tokens_condition__name ( SELECT tid, SUBSTR(name, integers.i, 3) as token FROM integers INNER JOIN condition ON integers.i <= LENGTH(name) + 2 ); CREATE INDEX wj_tokens_condition__name__tid ON wj_tokens_condition__name(tid); CREATE INDEX wj_tokens_condition__name__token ON wj_tokens_condition__name(token); CREATE TABLE wj_tokenweights_condition__name (token VARCHAR(255), weight FLOAT); INSERT INTO wj_tokenweights_condition__name ( SELECT token, LOG(size - df + 0.5) - LOG(df + 0.5) as weight FROM (SELECT token, count(*) as df FROM (SELECT * FROM wj_tokens_condition__name GROUP BY tid, token) f GROUP BY token ) D, ( SELECT count(*) as size FROM wj_tokens_condition__name ) S ); CREATE INDEX wj_tokenweights_condition__name__token ON wj_tokenweights_condition__name(token); CREATE TABLE wj_weights_condition__name (tid BIGINT, token VARCHAR(255), weight FLOAT); INSERT INTO wj_weights_condition__name ( SELECT tid, B.token, weight FROM wj_tokenweights_condition__name idf, (SELECT DISTINCT tid, token FROM wj_tokens_condition__name B) B WHERE B.token = idf.token ); CREATE INDEX wj_weights_condition__name__tid ON wj_weights_condition__name(tid); CREATE TABLE wj_sumweights_condition__name (tid BIGINT, sumw FLOAT); INSERT INTO wj_sumweights_condition__name ( SELECT tid, sum(weight) as sumw FROM wj_weights_condition__name GROUP BY tid ); CREATE INDEX wj_sumweights_condition__name__tid ON wj_sumweights_condition__name(tid); WITH wj_scores_0a631c4f AS ( SELECT tid1, tid2, (SI.sinter/(BSUMW.sumw + QSUMW.sumw - SI.sinter)) as score FROM (SELECT BTW.tid AS tid1, QT.tid AS tid2, SUM(BTW.weight) as sinter FROM (SELECT * FROM wj_weights_visits__diagnosis) AS BTW, wj_tokens_condition__name AS QT WHERE BTW.token = QT.token GROUP BY BTW.tid, QT.tid) AS SI, (SELECT * FROM wj_sumweights_visits__diagnosis) AS BSUMW, (SELECT Q.tid, SUM(BTW.weight) AS sumw FROM wj_tokens_condition__name AS Q, wj_tokenweights_visits__diagnosis AS BTW WHERE 1=1 AND Q.token = BTW.token GROUP BY Q.tid) AS QSUMW WHERE BSUMW.tid = SI.tid1 AND SI.tid2 = QSUMW.tid) SELECT visits.*, condition.* FROM wj_scores_0a631c4f AS s, visits, condition WHERE visits.tid < 50 AND s.tid1 = visits.tid AND s.tid2 = condition.tid AND s.score > 0.5;