DSNTEJ6O
This JCL prepares and executes the following sample application programs.
//*
//* DB2 Sample Application
//* Phase 6
//* Sample Callers of DB2 Text Search feature stored procedures
//* - SYSPROC.SYSTS_START
//* - SYSPROC.SYSTS_CREATE
//* - SYSPROC.SYSTS_UPDATE
//* - SYSPROC.SYSTS_DROP
//* - SYSPROC.SYSTS_STOP
//*
//* C language
//*
//* LICENSED MATERIALS - PROPERTY OF IBM
//* 5650-DB2
//* (C) COPYRIGHT 1982, 2016 IBM CORP. ALL RIGHTS RESERVED.
//*
//* Status = VERSION 12
//*
//* Function =
//* This JCL prepares and executes the following sample application
//* programs:
//* - DSN8DTS1: Creates a text search index on a specified table
//* table and column
//* - DSN8DTS2: Drops a specified text search index
//*
//* Pseudocode =
//* PH06OS01 Step Drop the sample search table
//* PH06OS02 Step Create and populate sample search table
//* PH06OS03 Step Prepare DSN8DTS1
//* PH06OS04 Step Bind the plan and package for DSN8DTS1
//* PH06OS05 Step Create a text search index on a VARCHAR col
//* PH06OS06 Step Create a text search index on a CHAR col
//* PH06OS07 Step Create a text search index on a VARGRAPHIC col
//* PH06OS08 Step Create a text search index on a XML col
//* PH06OS09 Step Create a text search index on a CLOB col
//* PH06OS10 Step Select data using CONTAINS and SCORE functions
//* PH06OS11 Step Prepare DSN8DTS2
//* PH06OS12 Step Bind the plan and package for DSN8DTS2
//* PH06OS13 Step Drop the text search index on the VARCHAR col
//* PH06OS14 Step Drop the text search index on the CHAR col
//* PH06OS15 Step Drop the text search index on the VARGRAPHIC cl
//* PH06OS16 Step Drop the text search index on the XML col
//* PH06OS17 Step Drop the text search index on the CLOB col
//*
//* Dependencies =
//* - This job requires the DB2-provided JCL procedure DSNHC
//* - This job assumes that DB2 Text Search services are
//* installed.
//*
//* Notes =
//* PRIOR TO RUNNING THIS JOB, customize it for your system:
//* (1) Copy it to your prefix.NEW.SDSNSAMP library or other library
//* where you keep your customized DB2
//* (2) Edit the copy you made in step (1) and proceed as follows
//* (3) Add a valid job card
//* (4) Locate and change all occurrences of the following strings
//* as indicated:
//* (A) The subsystem name '!DSN!' to the name of your DB2
//* (B) 'DSN!!0' to the prefix of the target library for db2
//* (C) 'CEE!!' to the prefix of your target library for
//* IBM Language Environment
//* (D) 'DSNTIA!!' to the plan name for DSNTIAD on your DB2
//* (E) 'DSNTEP!!' to the plan name for DSNTEP2 on your DB2
//* (F) 'DSN8!!0' to current-version creator name for DB2 IVPs
//* (for example DSN8910 for V9, DSN81010 for V10, etc.)
//* (5) Save your changes
//*
//* Change Activity =
//* 10/16/2013 Don't use prelinker by default PI13612 DM1812
//* 08/18/2014 Single-phase migration s21938_inst1 s21938
//*
//*********************************************************************
//*
//JOBLIB DD DISP=SHR,DSN=DSN!!0.SDSNEXIT
// DD DISP=SHR,DSN=DSN!!0.SDSNLOAD
// DD DISP=SHR,DSN=CEE!!.SCEERUN
//*
//*
//* Step 1: Drop the sample search table
//*
//PH06OS01 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) PARM('RC0') -
LIB('DSN!!0.RUNLIB.LOAD')
END
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
DROP TABLE DSN8!!0.TEXT_SEARCH;
//*
//* Step 2: Create and populate sample search table
//*
//PH06OS02 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP!!) PARM('/ALIGN(LHS)') -
LIB('DSN!!0.RUNLIB.LOAD')
END
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
CREATE TABLE DSN8!!0.TEXT_SEARCH
( INT1 INTEGER NOT NULL
,VC2 VARCHAR(50) NOT NULL
,AA3 CHAR(30)
,VG1 VARGRAPHIC(50)
,XM1 XML
,CLB CLOB(1G)
,AA4 ROWID NOT NULL
GENERATED BY DEFAULT
) CCSID UNICODE;
INSERT INTO DSN8!!0.TEXT_SEARCH(INT1,VC2,VG1,AA3,XM1,CLB)
VALUES(1
,'This line item LI704 describes'
,'Mary had a'
,'the new'
,XMLPARSE (DOCUMENT '<A><B>B1</B><C>C</C>Hello</A>')
,'This is clob data');
INSERT INTO DSN8!!0.TEXT_SEARCH(INT1,VC2,VG1,AA3,XM1,CLB)
VALUES(2
,'DB2 for z/OS text search'
,'little lamb, little lamb'
,'feature.'
,XMLPARSE (DOCUMENT '<A><B>B1</B><C>C</C>my</A>')
,'Herbs have been used');
INSERT INTO DSN8!!0.TEXT_SEARCH(INT1,VC2,VG1,AA3,XM1,CLB)
VALUES(3
,'This DB2 feature allows users'
,',little lamb, Mary'
,'to create'
,XMLPARSE (DOCUMENT '<A><B>B1</B><C>C</C>name</A>')
,'around the world for centuries.');
INSERT INTO DSN8!!0.TEXT_SEARCH(INT1,VC2,VG1,AA3,XM1,CLB)
VALUES(4
,'and maintain full text indexes'
,'had a little lamb,'
,'on any'
,XMLPARSE (DOCUMENT '<A><B>B1</B><C>C</C>is</A>')
,'For over 70 years');
INSERT INTO DSN8!!0.TEXT_SEARCH(INT1,VC2,VG1,AA3,XM1,CLB)
VALUES(5
,'character based column in any'
,'whose fleece was white'
,'DB2 table'
,XMLPARSE (DOCUMENT '<A><B>B1</B><C>C</C>Brian</A>')
,', Ricola has been making');
INSERT INTO DSN8!!0.TEXT_SEARCH(INT1,VC2,VG1,AA3,XM1,CLB)
VALUES(6
,'similar to the workstation DB2'
,'as snow'
,'Content'
,XMLPARSE (DOCUMENT '<A><B>B1</B><C>C</C>What</A>')
,'pleasant tasting natural herbal');
INSERT INTO DSN8!!0.TEXT_SEARCH(INT1,VC2,VG1,AA3,XM1,CLB)
VALUES(7
,'Manager Text Search and'
,'Humpty Dumpty sat on a '
,'provides'
,XMLPARSE (DOCUMENT '<A><B>B1</B><C>C</C>is</A>')
,' products for natural relief');
INSERT INTO DSN8!!0.TEXT_SEARCH(INT1,VC2,VG1,AA3,XM1,CLB)
VALUES(8
,'text search capabilities'
,'wall, Humpty'
,'in DB2.'
,XMLPARSE (DOCUMENT '<A><B>B1</B><C>C</C>your</A>')
,'of the discomfort due to sore throat');
INSERT INTO DSN8!!0.TEXT_SEARCH(INT1,VC2,VG1,AA3,XM1,CLB)
VALUES(9
,'Extra line to test EXTRA LINE'
,' Dumpty had a great fall'
,'Extraline'
,XMLPARSE (DOCUMENT '<A><B>B1</B><C>C</C>name</A>')
,' and minor irratation due to');
INSERT INTO DSN8!!0.TEXT_SEARCH(INT1,VC2,VG1,AA3,XM1,CLB)
VALUES(10
,'DB2 text search this an 2'
,'All the KINGS men ... '
,'on text DB'
,XMLPARSE (DOCUMENT '<A><B>B1</B><C>C</C>?</A>')
,' coughs, hoarseness, dry throat.');
GRANT INDEX ON TABLE DSN8!!0.TEXT_SEARCH
TO PUBLIC;
//*
//* Step 3: Prepare DSN8DTS1
//*
//PH06OS03 EXEC DSNHC,MEM=DSN8DTS1,COND=(4,LT),
// PARM.PC=('HOST(C),CCSID(1047),MARGINS(1,72),STDSQL(NO)',
// SOURCE,XREF),
// PARM.C='SOURCE LIST MAR(1,72) LO RENT OPTFILE(DD:CCOPTS)',
// PARM.LKED='AMODE=31,RMODE=ANY,MAP,RENT,REUS,UPCASE'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8DTS1),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8DTS1),
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8DTS1),
// DISP=SHR
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNELI)
INCLUDE SYSLIB(DSNTIAR)
//*
//* Step 4: Bind the package and plan for DSN8DTS1
//*
//PH06OS04 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//DBRMLIB DD DISP=SHR,DSN=DSN!!0.DBRMLIB.DATA
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
BIND PACKAGE(DSN8ED!!) MEMBER(DSN8DTS1) APPLCOMPAT(V!!R1) +
ACT(REP) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC)
BIND PLAN(DSN8DTS1) -
PKLIST(DSN8ED!!.DSN8DTS1 ) -
ACTION(REPLACE) RETAIN +
ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
END
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
GRANT BIND, EXECUTE ON PLAN DSN8DTS1
TO PUBLIC;
//*
//* Step 5: Create a text search index on VARCHAR column VC2
//*
//PH06OS05 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSN8DTS1) PLAN(DSN8DTS1) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARMS('DSN8!!0 IVC2 DSN8!!0.TEXT_SEARCH(VC2)')
END
//*
//* Step 6: Create a text search index on CHAR column AA3
//*
//PH06OS06 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSN8DTS1) PLAN(DSN8DTS1) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARMS('DSN8!!0 IAA3 DSN8!!0.TEXT_SEARCH(AA3)')
END
//*
//* Step 7: Create a text search index on VARGRAPIC column VG1
//*
//PH06OS07 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSN8DTS1) PLAN(DSN8DTS1) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARMS('DSN8!!0 IVG1 DSN8!!0.TEXT_SEARCH(VG1)')
END
//*
//* Step 8: Create a text search index on XML column XM1
//*
//PH06OS08 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSN8DTS1) PLAN(DSN8DTS1) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARMS('DSN8!!0 IXML DSN8!!0.TEXT_SEARCH(XM1)')
END
//*
//* Step 9: Create a text search index on CLOB column CLB
//*
//PH06OS09 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSN8DTS1) PLAN(DSN8DTS1) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARMS('DSN8!!0 ICLB DSN8!!0.TEXT_SEARCH(CLB)')
END
//*
//* Step 10: Select data using the CONTAINS and SCORE functions
//*
//PH06OS10 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP!!) PARM('/ALIGN(LHS)') -
LIB('DSN!!0.RUNLIB.LOAD')
END
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
SELECT INT1, CONTAINS(VC2, 'text')
FROM DSN8!!0.TEXT_SEARCH;
SELECT INT1, SCORE(VC2, 'text')
FROM DSN8!!0.TEXT_SEARCH
WHERE CONTAINS(VC2, 'text') = 1;
SELECT INT1, CONTAINS(AA3, 'DB2')
FROM DSN8!!0.TEXT_SEARCH;
SELECT INT1, SCORE(AA3, 'DB2')
FROM DSN8!!0.TEXT_SEARCH
WHERE CONTAINS(AA3, 'DB2') = 1;
SELECT INT1, VC2, AA3
FROM DSN8!!0.TEXT_SEARCH
WHERE CONTAINS(AA3, 'DB2') = 1
AND CONTAINS(VC2, 'text') = 1;
//*
//* Step 11: Prepare DSN8DTS2
//*
//PH06OS11 EXEC DSNHC,MEM=DSN8DTS2,COND=(4,LT),
// PARM.PC=('HOST(C),CCSID(1047),MARGINS(1,72),STDSQL(NO)',
// SOURCE,XREF),
// PARM.C='SOURCE LIST MAR(1,72) LO RENT OPTFILE(DD:CCOPTS)',
// PARM.LKED='AMODE=31,RMODE=ANY,MAP,RENT,REUS,UPCASE'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8DTS2),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8DTS2),
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8DTS2),
// DISP=SHR
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNELI)
INCLUDE SYSLIB(DSNTIAR)
//*
//* Step 12: Bind the package and plan for DSN8DTS2
//*
//PH06OS12 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//DBRMLIB DD DISP=SHR,DSN=DSN!!0.DBRMLIB.DATA
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
BIND PACKAGE(DSN8ED!!) MEMBER(DSN8DTS2) APPLCOMPAT(V!!R1) +
ACT(REP) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC)
BIND PLAN(DSN8DTS2) -
PKLIST(DSN8ED!!.DSN8DTS2 ) -
ACTION(REPLACE) RETAIN +
ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
END
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
GRANT BIND, EXECUTE ON PLAN DSN8DTS2
TO PUBLIC;
//*
//* Step 13: Drop the text search index on VARCHAR column VC2
//*
//PH06OS13 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSN8DTS2) PLAN(DSN8DTS2) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARMS('DSN8!!0 IVC2')
END
//*
//* Step 14: Drop the text search index on CHAR column AA3
//*
//PH06OS14 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSN8DTS2) PLAN(DSN8DTS2) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARMS('DSN8!!0 IAA3')
END
//*
//* Step 15: Drop the text search index on VARGRAPIC column VG1
//*
//PH06OS15 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSN8DTS2) PLAN(DSN8DTS2) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARMS('DSN8!!0 IVG1')
END
//*
//* Step 16: Drop the text search index on XML column XM1
//*
//PH06OS16 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSN8DTS2) PLAN(DSN8DTS2) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARMS('DSN8!!0 IXML')
END
//*
//* Step 17: Drop the text search index on CLOB column CLB
//*
//PH06OS17 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(!DSN!)
RUN PROGRAM(DSN8DTS2) PLAN(DSN8DTS2) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARMS('DSN8!!0 ICLB')
END
//*