-- This CLP file was created using DB2LOOK Version 7.2
-- Timestamp: Wed May 15 04:20:04 JST 2002
-- Database Name: PATENT         
-- Database Manager Version: DB2/6000 Version 7.2.2        
-- Database Codepage: 819


CONNECT TO PATENT user inst1 using inst1pw

-- :%s/^\([	A-Z ]..*\)$/\1 \\/
--   This /|\ is a tab.
-- Remove semicolons, but not from these two lines.
-- :15,$s/; \\$//
-- :15,$s/;  \\$//


------------------------------------------------
-- DDL Statements for table "INST1   "."MAIN"
------------------------------------------------
 
 CREATE TABLE "INST1   "."MAIN"  ( \
		  "PATN" CHAR(12) NOT NULL ,  \
		  "ICNT" CHAR(2) ,  \
		  "ILANG" CHAR(5) ,  \
		  "ITYPE" CHAR(2) ,  \
		  "SRC" CHAR(1) ,  \
		  "APT" CHAR(1) ,  \
		  "PBL" CHAR(1) ,  \
		  "APD" DATE ,  \
		  "ISD" DATE ,  \
		  "NCL" SMALLINT ,  \
		  "APN" CHAR(16) ,  \
		  "CLAS_EDF" CHAR(1) ,  \
		  "REIS_PNO10" VARCHAR(16) ,  \
		  "ECL" VARCHAR(31) ,  \
		  "TTL" VARCHAR(350) ,  \
		  "EXP" VARCHAR(100) ,  \
		  "EXA" VARCHAR(100) ,  \
		  "CLAS_OCL" VARCHAR(15) ,  \
		  "MSG_FLAG" CHAR(1) ,  \
		  "TEXT_CD" VARCHAR(15) ,  \
		  "TEXT_FN" VARCHAR(31) ,  \
		  "TEXT_LSTART" INTEGER ,  \
		  "TEXT_LEND" INTEGER ,  \
		  "PCTA_PCN" CHAR(14) ,  \
		  "PCTA_PCD" DATE ,  \
		  "PCTA_PD1" DATE ,  \
		  "PCTA_PD2" DATE ,  \
		  "PCTA_PD3" DATE ,  \
		  "PCTA_PCP" CHAR(10) ,  \
		  "TRM" CHAR(4) ,  \
		  "EXTRACT_DATE" DATE NOT NULL WITH DEFAULT CURRENT DATE,  \
		  "INSERT_DATE" DATE ,  \
		  "LAST_MODIFIED_DATE" DATE ,  \
		  "IMAGE_LOCATION" VARCHAR(15) ,  \
		  "ASSIGNEE_CODE" INTEGER ,  \
		  "ASSIGNEE_FLAG" CHAR(1) ,  \
		  "LICENSE_FLAG" CHAR(1) ,  \
		  "PATN10" CHAR(10) ,  \
		  "OCNT" CHAR(2) ,  \
		  "LOD" DATE ,  \
		  "LCRFNBR" INTEGER ,  \
		  "PATNLONG" VARCHAR(16) )    \
		 IN "USERSPACE1" 
 ALTER TABLE "INST1   "."MAIN" APPEND ON

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_APN" ON "INST1   "."MAIN"  \
		("APN" ASC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_ASSGCD" ON "INST1   "."MAIN"  \
		("ASSIGNEE_CODE" ASC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_ASSGCDISDPATN" ON "INST1   "."MAIN"  \
		("ASSIGNEE_CODE" ASC, \
		 "ISD" DESC, \
		 "PATN" ASC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_ISD_ASC" ON "INST1   "."MAIN"  \
		("ISD" ASC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_ISD_DESC" ON "INST1   "."MAIN"  \
		("ISD" DESC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_LMD" ON "INST1   "."MAIN"  \
		("PATN" ASC, \
		 "LAST_MODIFIED_DATE" ASC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_OCL_ISD_PATN" ON "INST1   "."MAIN"  \
		("CLAS_OCL" ASC, \
		 "ISD" DESC, \
		 "PATN" ASC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_PATN" ON "INST1   "."MAIN"  \
		("PATN" ASC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_PATN10CNT" ON "INST1   "."MAIN"  \
		("PATN10" ASC, \
		 "ICNT" ASC, \
		 "OCNT" ASC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_PATN10ILANG" ON "INST1   "."MAIN"  \
		("PATN10" ASC, \
		 "ILANG" ASC, \
		 "ITYPE" ASC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_PATN10ITYPE" ON "INST1   "."MAIN"  \
		("PATN10" ASC, \
		 "ITYPE" ASC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_PCTA_ISD_PATN" ON "INST1   "."MAIN"  \
		("PCTA_PCN" ASC, \
		 "ISD" DESC, \
		 "PATN" ASC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAIN_REIS_PNO10" ON "INST1   "."MAIN"  \
		("REIS_PNO10" ASC)

-- DDL Statements for indexes on Table "INST1   "."MAIN"

CREATE INDEX "INST1   "."MAINTXTCD" ON "INST1   "."MAIN"  \
		("TEXT_CD" ASC)


-- DDL Statements for primary key on Table "INST1   "."MAIN"

ALTER TABLE "INST1   "."MAIN"  \
	ADD CONSTRAINT "SQL980811072244570" PRIMARY KEY \
		("PATN")



------------------------------------------------
-- DDL Statements for table "INST1   "."ABST"
------------------------------------------------
 
 CREATE TABLE "INST1   "."ABST"  ( \
		  "PATN" CHAR(12) NOT NULL ,  \
		  "ILANG" CHAR(5) ,  \
		  "NUM" SMALLINT NOT NULL ,  \
		  "TYPE" CHAR(4) ,  \
		  "SLOT" CHAR(1) ,  \
		  "PAV" VARCHAR(3000) ,  \
		  "PAL" LONG VARCHAR )    \
		 IN "USERSPACE1" 
 ALTER TABLE "INST1   "."ABST" APPEND ON


-- DDL Statements for primary key on Table "INST1   "."ABST"

ALTER TABLE "INST1   "."ABST"  \
	ADD CONSTRAINT "ABST_PATN_NUM" PRIMARY KEY \
		("PATN", \
		 "NUM")



------------------------------------------------
-- DDL Statements for table "INST1   "."ASSG"
------------------------------------------------
 
 CREATE TABLE "INST1   "."ASSG"  ( \
		  "PATN" CHAR(12) NOT NULL ,  \
		  "NUM" SMALLINT NOT NULL ,  \
		  "NAM" VARCHAR(250) ,  \
		  "CTY" VARCHAR(250) ,  \
		  "STA" CHAR(2) ,  \
		  "CNT" CHAR(3) ,  \
		  "ZIP" VARCHAR(10) ,  \
		  "COD" CHAR(2) ,  \
		  "ITX" VARCHAR(127) )    \
		 IN "USERSPACE1" 
 ALTER TABLE "INST1   "."ASSG" APPEND ON

-- DDL Statements for indexes on Table "INST1   "."ASSG"

CREATE INDEX "INST1   "."ASSG_PATN_NUM" ON "INST1   "."ASSG"  \
		("PATN" ASC, \
		 "NUM" ASC)


-- DDL Statements for primary key on Table "INST1   "."ASSG"

ALTER TABLE "INST1   "."ASSG"  \
	ADD CONSTRAINT "SQL980928150815870" PRIMARY KEY \
		("PATN", \
		 "NUM")



------------------------------------------------
-- DDL Statements for table "INST1   "."CLAS_ICL"
------------------------------------------------
 
 CREATE TABLE "INST1   "."CLAS_ICL"  ( \
		  "PATN" CHAR(12) NOT NULL ,  \
		  "NUM" SMALLINT NOT NULL ,  \
		  "ICL" VARCHAR(127) )    \
		 IN "USERSPACE1" 
 ALTER TABLE "INST1   "."CLAS_ICL" APPEND ON

-- DDL Statements for indexes on Table "INST1   "."CLAS_ICL"

CREATE INDEX "INST1   "."CLAS_ICL_ICL" ON "INST1   "."CLAS_ICL"  \
		("ICL" ASC)

-- DDL Statements for indexes on Table "INST1   "."CLAS_ICL"

CREATE INDEX "INST1   "."CLAS_ICL_PATN_NUM" ON "INST1   "."CLAS_ICL"  \
		("PATN" ASC, \
		 "NUM" ASC)


-- DDL Statements for primary key on Table "INST1   "."CLAS_ICL"

ALTER TABLE "INST1   "."CLAS_ICL"  \
	ADD CONSTRAINT "SQL980913102832430" PRIMARY KEY \
		("PATN", \
		 "NUM")



------------------------------------------------
-- DDL Statements for table "INST1   "."CLAS_XCL"
------------------------------------------------
 
 CREATE TABLE "INST1   "."CLAS_XCL"  ( \
		  "PATN" CHAR(12) NOT NULL ,  \
		  "NUM" SMALLINT NOT NULL ,  \
		  "MOD" CHAR(1) ,  \
		  "SLOT" CHAR(1) ,  \
		  "XCL" VARCHAR(127) ,  \
		  "XCL_LONG" VARCHAR(1500) )    \
		 IN "USERSPACE1" 
-- DDL Statements for indexes on Table "INST1   "."CLAS_XCL"

CREATE INDEX "INST1   "."CLAS_XCL_PATN_NUM" ON "INST1   "."CLAS_XCL"  \
		("PATN" ASC, \
		 "NUM" ASC)

-- DDL Statements for indexes on Table "INST1   "."CLAS_XCL"

CREATE INDEX "INST1   "."CLAS_XCL_XCL" ON "INST1   "."CLAS_XCL"  \
		("XCL" ASC)


-- DDL Statements for primary key on Table "INST1   "."CLAS_XCL"

ALTER TABLE "INST1   "."CLAS_XCL"  \
	ADD CONSTRAINT "XCLPKPATNNUM" PRIMARY KEY \
		("PATN", \
		 "NUM")



------------------------------------------------
-- DDL Statements for table "INST1   "."COUNTRY"
------------------------------------------------
 
 CREATE TABLE "INST1   "."COUNTRY"  ( \
		  "CN2" CHAR(2) NOT NULL ,  \
		  "CNT" CHAR(3) NOT NULL ,  \
		  "NAME" VARCHAR(127) NOT NULL ,  \
		  "FROM_DATE" DATE ,  \
		  "TO_DATE" DATE ,  \
		  "CIA" CHAR(2) )    \
		 IN "USERSPACE1" 
 ALTER TABLE "INST1   "."COUNTRY" APPEND ON

-- DDL Statements for indexes on Table "INST1   "."COUNTRY"

CREATE UNIQUE INDEX "INST1   "."COUNTRY_CN2_CNT" ON "INST1   "."COUNTRY"  \
		("CN2" ASC, \
		 "CNT" ASC)

-- DDL Statements for indexes on Table "INST1   "."COUNTRY"

CREATE INDEX "INST1   "."COUNTRY_CNT" ON "INST1   "."COUNTRY"  \
		("CNT" ASC)

-- DDL Statements for indexes on Table "INST1   "."COUNTRY"

CREATE INDEX "INST1   "."COUNTRY_CNT_NAME" ON "INST1   "."COUNTRY"  \
		("CNT" ASC, \
		 "NAME" ASC)




------------------------------------------------
-- DDL Statements for table "INST1   "."DESIGNATED"
------------------------------------------------
 
 CREATE TABLE "INST1   "."DESIGNATED"  ( \
		  "PATN" CHAR(12) NOT NULL ,  \
		  "ICNT" CHAR(2) NOT NULL ,  \
		  "REGION" SMALLINT ,  \
		  "PTYPE" SMALLINT )    \
		 IN "USERSPACE1" 
 ALTER TABLE "INST1   "."DESIGNATED" APPEND ON

-- DDL Statements for indexes on Table "INST1   "."DESIGNATED"

CREATE UNIQUE INDEX "INST1   "."DESI_PATN_ICNT" ON "INST1   "."DESIGNATED"  \
		("PATN" ASC, \
		 "ICNT" ASC, \
		 "REGION" ASC, \
		 "PTYPE" ASC)




------------------------------------------------
-- DDL Statements for table "INST1   "."IMAG"
------------------------------------------------
 
 CREATE TABLE "INST1   "."IMAG"  ( \
		  "PATN" CHAR(12) ,  \
		  "SRH" SMALLINT ,  \
		  "ABS" SMALLINT ,  \
		  "DESC" SMALLINT ,  \
		  "AMEND" SMALLINT ,  \
		  "DRAWING" SMALLINT ,  \
		  "CLAIM" SMALLINT ,  \
		  "BIBLIO" SMALLINT ,  \
		  "IMAGE_PAGES" SMALLINT ,  \
		  "BIB_STATUS" CHAR(2) ,  \
		  "CDLABEL" CHAR(15) ,  \
		  "INSERT_TS" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP,  \
		  "BIB_CC" CHAR(1) ,  \
		  "BIB_EXPIRED" CHAR(2) ,  \
		  "BIB_EXTENDED" CHAR(1) ,  \
		  "BIB_REEXAM" CHAR(2) ,  \
		  "BIB_DATE" DATE ,  \
		  "BIB_CN2" CHAR(2) ,  \
		  "BIB_STA" CHAR(2) ,  \
		  "BIB_AC" INTEGER ,  \
		  "BIB_ISD" DATE ,  \
		  "XKIND" CHAR(2) )    \
		 IN "USERSPACE1" 
 ALTER TABLE "INST1   "."IMAG" APPEND ON

-- DDL Statements for indexes on Table "INST1   "."IMAG"

CREATE INDEX "INST1   "."IMAG_BIB_DATE" ON "INST1   "."IMAG"  \
		("PATN" ASC, \
		 "BIB_DATE" ASC)

-- DDL Statements for indexes on Table "INST1   "."IMAG"

CREATE INDEX "INST1   "."IMAG_BIB_EXPIRED" ON "INST1   "."IMAG"  \
		("PATN" ASC, \
		 "BIB_EXPIRED" ASC)

-- DDL Statements for indexes on Table "INST1   "."IMAG"

CREATE INDEX "INST1   "."IMAG_BIB_ISD" ON "INST1   "."IMAG"  \
		("BIB_ISD" ASC, \
		 "PATN" ASC)

-- DDL Statements for indexes on Table "INST1   "."IMAG"

CREATE INDEX "INST1   "."IMAG_BIB_REEXAM" ON "INST1   "."IMAG"  \
		("PATN" ASC, \
		 "BIB_REEXAM" ASC)

-- DDL Statements for indexes on Table "INST1   "."IMAG"

CREATE UNIQUE INDEX "INST1   "."IMAG_CDLABEL" ON "INST1   "."IMAG"  \
		("PATN" ASC, \
		 "CDLABEL" ASC)

-- DDL Statements for indexes on Table "INST1   "."IMAG"

CREATE INDEX "INST1   "."IMAGCDLABEL" ON "INST1   "."IMAG"  \
		("CDLABEL" ASC)




------------------------------------------------
-- DDL Statements for table "INST1   "."INVT"
------------------------------------------------
 
 CREATE TABLE "INST1   "."INVT"  ( \
		  "PATN" CHAR(12) NOT NULL ,  \
		  "NUM" SMALLINT NOT NULL ,  \
		  "NAM" VARCHAR(250) ,  \
		  "CTY" VARCHAR(250) ,  \
		  "STA" CHAR(2) ,  \
		  "CNT" CHAR(3) ,  \
		  "ZIP" VARCHAR(10) ,  \
		  "R47" CHAR(1) ,  \
		  "ITX" VARCHAR(250) ,  \
		  "NAMEPREFIX" VARCHAR(30) ,  \
		  "GIVENNAME" VARCHAR(30) ,  \
		  "MIDDLENAME" VARCHAR(30) ,  \
		  "FAMILYNAME" VARCHAR(30) ,  \
		  "NAMESUFFIX" VARCHAR(30) ,  \
		  "ADDRESS1" VARCHAR(96) ,  \
		  "ADDRESS2" VARCHAR(96) ,  \
		  "EMAIL" VARCHAR(30) ,  \
		  "TELEPHONE" VARCHAR(30) ,  \
		  "FAXNUMB" VARCHAR(30) )    \
		 IN "USERSPACE1" 
 ALTER TABLE "INST1   "."INVT" APPEND ON


-- DDL Statements for primary key on Table "INST1   "."INVT"

ALTER TABLE "INST1   "."INVT"  \
	ADD CONSTRAINT "SQL980921052217240" PRIMARY KEY \
		("PATN", \
		 "NUM")



------------------------------------------------
-- DDL Statements for table "INST1   "."PRIR"
------------------------------------------------
 
 CREATE TABLE "INST1   "."PRIR"  ( \
		  "PATN" CHAR(12) NOT NULL ,  \
		  "NUM" SMALLINT NOT NULL ,  \
		  "CNT" CHAR(3) ,  \
		  "APN" VARCHAR(31) ,  \
		  "APD" DATE )    \
		 IN "USERSPACE1" 
 ALTER TABLE "INST1   "."PRIR" APPEND ON

-- DDL Statements for indexes on Table "INST1   "."PRIR"

CREATE INDEX "INST1   "."A_PRIR_APN" ON "INST1   "."PRIR"  \
		("APN" ASC, \
		 "PATN" ASC) ALLOW REVERSE SCANS


-- DDL Statements for primary key on Table "INST1   "."PRIR"

ALTER TABLE "INST1   "."PRIR"  \
	ADD CONSTRAINT "A_PRIR_PATN_NUM" PRIMARY KEY \
		("PATN", \
		 "NUM")



------------------------------------------------
-- DDL Statements for table "INST1   "."VOL"
------------------------------------------------
 
 CREATE TABLE "INST1   "."VOL"  ( \
		  "IMAGE_SERVER" CHAR(8) NOT NULL ,  \
		  "CDLABEL" VARCHAR(15) NOT NULL ,  \
		  "CD_STATUS" CHAR(3) NOT NULL ,  \
		  "JUKENAME" CHAR(12) NOT NULL WITH DEFAULT 'NOTSET',  \
		  "DATESET" DATE NOT NULL WITH DEFAULT CURRENT DATE)    \
		 IN "USERSPACE1" 
 ALTER TABLE "INST1   "."VOL" PCTFREE 0

-- DDL Statements for indexes on Table "INST1   "."VOL"

CREATE UNIQUE INDEX "INST1   "."CDLBLX" ON "INST1   "."VOL"  \
		("CDLABEL" ASC)


-- DDL Statements for primary key on Table "INST1   "."VOL"

ALTER TABLE "INST1   "."VOL"  \
	ADD CONSTRAINT "IMGS_CD" PRIMARY KEY \
		("IMAGE_SERVER", \
		 "CDLABEL")



----------------------------
-- DDL Statements for Views
----------------------------
create view inst1.VW_MAIN_A (patn, patn10, isd, ttl, assg_nam, license_flag  \
, patnlong) as SELECT m.patn, patn10, isd, ttl, (SELECT nam FROM inst1.assg \
WHERE patn = m.patn and num = 1), m.license_flag , m.patnlong FROM inst1.main m

create view inst1.VW_MAIN_I (patn, patn10, isd, ttl, clas_icl, license_flag,  \
patnlong) as SELECT m.patn, patn10, isd, ttl, (SELECT SUBSTR(icl,1,4) ||  \
' ' || TRANSLATE(SUBSTR(icl,5,1), '','0') || CASE SUBSTR(icl,5,1) WHEN \
'0' THEN TRANSLATE(SUBSTR(icl,6,1), '','0') ELSE SUBSTR(icl,6,1) END || \
SUBSTR(icl,7,1) || '/' || SUBSTR(icl,8,5) FROM inst1.clas_icl WHERE patn  \
= m.patn and num = 1), m.license_flag , m.patnlong FROM inst1.main m

create view inst1.VW_MAIN_I_A (patn, patn10, isd, ttl, clas_icl, assg_nam,  \
license_flag , patnlong) as SELECT m.patn, patn10, isd, ttl, (SELECT SUBSTR(icl,1,4)  \
|| ' ' || TRANSLATE(SUBSTR(icl,5,1), '','0') || CASE SUBSTR(icl,5,1) WHEN \ 
'0' THEN TRANSLATE(SUBSTR(icl, 6,1), '','0') ELSE SUBSTR(icl,6,1) END || \
SUBSTR(icl,7,1) || '/' || SUBSTR(icl,8,5) FROM inst1.clas_icl WHERE patn \
= m.patn and num = 1), (SELECT nam FROM inst1.assg WHERE patn = m.patn \
and num = 1), m.license_flag , m.patnlong FROM inst1.main m


-- DDL Statements for foreign keys on Table "INST1   "."ABST"

ALTER TABLE "INST1   "."ABST"  \
	ADD CONSTRAINT "ABSTPATN" FOREIGN KEY \
		("PATN") \
	REFERENCES "INST1   "."MAIN" \
		("PATN") \
	ON DELETE CASCADE \
	ON UPDATE NO ACTION



-- DDL Statements for foreign keys on Table "INST1   "."ASSG"

ALTER TABLE "INST1   "."ASSG"  \
	ADD CONSTRAINT "ASSGPATN" FOREIGN KEY \
		("PATN") \
	REFERENCES "INST1   "."MAIN" \
		("PATN") \
	ON DELETE CASCADE \
	ON UPDATE NO ACTION



-- DDL Statements for foreign keys on Table "INST1   "."CLAS_ICL"

ALTER TABLE "INST1   "."CLAS_ICL"  \
	ADD CONSTRAINT "CLAS_ICLPATN" FOREIGN KEY \
		("PATN") \
	REFERENCES "INST1   "."MAIN" \
		("PATN") \
	ON DELETE CASCADE \
	ON UPDATE NO ACTION


-- DDL Statements for foreign keys on Table "INST1   "."CLAS_XCL"

ALTER TABLE "INST1   "."CLAS_XCL"  \
	ADD CONSTRAINT "CLAS_XCLPATN" FOREIGN KEY \
		("PATN") \
	REFERENCES "INST1   "."MAIN" \
		("PATN") \
	ON DELETE CASCADE \
	ON UPDATE NO ACTION


-- DDL Statements for foreign keys on Table "INST1   "."DESIGNATED"

ALTER TABLE "INST1   "."DESIGNATED"  \
	ADD CONSTRAINT "DESIGNATEDPATN" FOREIGN KEY \
		("PATN") \
	REFERENCES "INST1   "."MAIN" \
		("PATN") \
	ON DELETE CASCADE \
	ON UPDATE NO ACTION


-- DDL Statements for foreign keys on Table "INST1   "."INVT"

ALTER TABLE "INST1   "."INVT"  \
	ADD CONSTRAINT "INVTPATN" FOREIGN KEY \
		("PATN") \
	REFERENCES "INST1   "."MAIN" \
		("PATN") \
	ON DELETE CASCADE \
	ON UPDATE NO ACTION


-- DDL Statements for foreign keys on Table "INST1   "."PRIR"

ALTER TABLE "INST1   "."PRIR"  \
	ADD CONSTRAINT "PRIRPATN" FOREIGN KEY \
		("PATN") \
	REFERENCES "INST1   "."MAIN" \
		("PATN") \
	ON DELETE CASCADE \
	ON UPDATE NO ACTION


-------------------------------
-- DDL Statements for Triggers
-------------------------------

create trigger INST1.MAIN_PATN10 no cascade before insert on INST1.MAIN  \
referencing new as n for each row mode db2sql set n.patn10 = substr(patn,1,10)

create trigger INST1.RECORD_UPDATE no cascade before update on INST1.MAIN  \
referencing new as n for each row mode db2sql set n.last_modified_date  \
= current date



COMMIT WORK

CONNECT RESET

TERMINATE