OmniFind Background:
• IBM i product offering: 5733-OMF
– Available on all IBM i operating system releases
– No-charge offering
• Db2 Family text search technology
– Advanced, linguistic high-speed searches
– Search character-based columns
– IBM i specific search capability:
• Spool Files and Output Queues
• IFS stream files
– 26 supported languages
• Alternative search to:
– LIKE predicate
– Regular Expressions
Example:
select * from QSYS2.SYSTEXTDEFAULTS;
select * from QSYS2.SYSTEXTSERVERS;
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzash/srchz_systextservers.htm
call SYSPROC.SYSTS_STOP(50);
call SYSPROC.SYSTS_START(50);
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzash/srchz_sps_systs_start.htm
select * from QSYS2.SYSTEXTSERVERS;
CALL SYSPROC.SYSTS_CRTCOL(
'COLLECTION_FOR_SCOTT',
'FORMAT TEXT UPDATE FREQUENCY D(*) H(*) M(0, 15, 30, 45)');
SET CURRENT SCHEMA COLLECTION_FOR_SCOTT;
SET CURRENT PATH COLLECTION_FOR_SCOTT;
-- Repeat the ADD_IFS_STMF_OBJECT_SET call for every sub-directory you want this collection to search
CALL ADD_IFS_STMF_OBJECT_SET('/usr/scottf');
-- This update is not required, but is helpful for testing. Otherwise, the update frequency will be used
CALL UPDATE;
CREATE or replace function collection_for_scott.search_IFS (
p_search_string varchar(1000),
p_SEARCH_OPTIONS VARCHAR(32700) CCSID 1208 DEFAULT NULL,
p_RESULT_OPTIONS VARCHAR(32700) CCSID 1208 DEFAULT NULL)
returns table(
IFS_OBJECT CLOB(2G),
SCORE DOUBLE,
OBJTYPE CHAR(10) CCSID 1208,
OBJATTR CHAR(10) CCSID 1208,
MODIFY_TIME TIMESTAMP
)
LANGUAGE SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION COMMIT = *NONE, DBGVIEW = *SOURCE
BEGIN
DECLARE V_OBJTYPE CHAR(10) CCSID 1208;
DECLARE V_OBJATTR CHAR(10) CCSID 1208;
DECLARE V_CONTAINING_OBJECT_LIB CHAR(10) CCSID 1208;
DECLARE V_CONTAINING_OBJECT_NAME CHAR(10) CCSID 1208;
DECLARE V_OBJECTINFOR XML;
DECLARE V_XML XML;
DECLARE V_OBJECT_NAME CLOB(2G);
DECLARE V_MODIFY_TIME TIMESTAMP;
DECLARE V_SCORE DOUBLE;
DECLARE SQLCODE INT DEFAULT 0 ;
DECLARE V_Result_Set_Locator RESULT_SET_LOCATOR VARYING;
DECLARE GETout INTEGER DEFAULT 0;
-- Declare condition handler /* end loop when row not found is hit */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET GETout = 1; /* end loop when row not found is hit */
DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
SET GETout = 1; /* SQL0204 means the procedure has not been called */
DECLARE CONTINUE HANDLER FOR SQLSTATE '42815'
SET GETout = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '22503'
SET GETout = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '51030'
SET GETout = 1; /* SQL0480 means the procedure has not been called */
CALL qdbtslib.SYSTS_SEARCH_RETURN_TO_CALLER('COLLECTION_FOR_SCOTT',
p_search_string,
p_SEARCH_OPTIONS,
p_RESULT_OPTIONS);
ASSOCIATE LOCATOR (V_Result_Set_Locator) WITH PROCEDURE qdbtslib.SYSTS_SEARCH_RETURN_TO_CALLER;
ALLOCATE OMF_SEARCH_CURSOR CURSOR FOR RESULT SET V_Result_Set_Locator;
LOOP_ON_RESULTS: WHILE (GETout = 0) DO
FETCH FROM OMF_SEARCH_CURSOR
INTO V_OBJTYPE,
V_OBJATTR,
V_CONTAINING_OBJECT_LIB,
V_CONTAINING_OBJECT_NAME,
V_OBJECTINFOR,
V_MODIFY_TIME,
V_SCORE;
IF (GETout = 1) THEN
LEAVE LOOP_ON_RESULTS;
END IF;
SET V_OBJECT_NAME = (SELECT "file_path" FROM
XMLTABLE(
XMLNAMESPACES('http://www.ibm.com/xmlns/prod/db2textsearch/obj1' as "x"),
'x:Stream_File' PASSING V_OBJECTINFOR
COLUMNS
"file_path" CLOB(2G) PATH 'x:file_path' ) Stream_File);
PIPE ( V_OBJECT_NAME,
V_SCORE,
V_OBJTYPE,
V_OBJATTR,
V_MODIFY_TIME );
END WHILE; /* LOOP_ON_RESULTS */
CLOSE OMF_SEARCH_CURSOR;
RETURN;
END ;
select * from
table(collection_for_scott.search_IFS
('"Quarry Hill" and -raptors and -dinosaurs')) x
ORDER BY SCORE DESC;
select * from
table(collection_for_scott.search_IFS
('2394*')) x
ORDER BY SCORE DESC;
select * from
table(collection_for_scott.search_IFS
('card number')) x
ORDER BY SCORE DESC;