analytics/configuration/schemas/logging/db2zOS/LS_dbUpgrade84_003-to-85_003_db2zOS.sql

-- Licensed Materials - Property of IBM
-- IBM Cognos Products: ccllogging
-- (C) Copyright IBM Corp. 2005, 2014
-- US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. 

alter table COGIPF_USERLOGON add COGIPF_REMOTE_IPADDR varchar (128);

alter table COGIPF_USERLOGON add COGIPF_CAMID	varchar (512);

CREATE TABLE  COGIPF_HUMANTASKSERVICE (
	COGIPF_HOST_IPADDR 	varchar (128),
	COGIPF_HOST_PORT 		integer,
	COGIPF_PROC_ID 		integer ,
	COGIPF_LOCALTIMESTAMP 	timestamp,
	COGIPF_TIMEZONE_OFFSET 	integer,
	COGIPF_SESSIONID 		varchar (255) ,
	COGIPF_REQUESTID 		varchar (255) ,
	COGIPF_STEPID 		varchar (255) ,
	COGIPF_SUBREQUESTID 	varchar (255) ,
	COGIPF_THREADID 		varchar (255) ,
	COGIPF_BUILDNUMBER 	integer,
	COGIPF_OPERATION 		varchar (128) ,
	COGIPF_TARGET_TYPE 	varchar (255) ,
	COGIPF_TARGET_PATH 	varchar (1024) ,
	COGIPF_STATUS 		varchar (50) ,
	COGIPF_LOGENTRYID 	varchar (50) not null,
	COGIPF_TASKID 		varchar (50) ,
	COGIPF_TRANSACTION_TYPE	varchar(255) ,
	COGIPF_USER			varchar(255) ,
	COGIPF_TASK_PRIORITY	integer,
	COGIPF_TASK_STATUS	varchar(255) ,
	COGIPF_TASK_ACTIVATION_TIME	bigint ,
	COGIPF_TASK_EXPIRATION_TIME	bigint ,
	COGIPF_TASK_NAME		clob(1G), 
	COGIPF_TASK_SUBJECT		clob(1G),
	COGIPF_TASK_DESCRIPTION		clob(1G),
	COGIPF_TASK_TIMEZONEID		varchar(50),
	COGIPF_TASK_ACTUAL_OWNER	varchar(255),
	COGIPF_TASK_INITIATOR		varchar(255),
	COGIPF_TASK_CLASS_NAME		varchar(255),
	COGIPF_TASK_CLASS_OPERATION	varchar(255),
	COGIPF_TASK_COMMENT  		varchar(2048)
) IN IPFSCRIPT_CREATE_IN;


CREATE AUXILIARY TABLE IPFAUXTB01
   IN IPFSCRIPT_DATABASE.IPFSCRIPT_LS_IDIPFL02
   STORES COGIPF_HUMANTASKSERVICE
   COLUMN COGIPF_TASK_NAME;
 
CREATE  UNIQUE  INDEX IPFAUXIX01 ON IPFAUXTB01 USING STOGROUP IPFSCRIPT_STOGROUP;

CREATE AUXILIARY TABLE IPFAUXTB02
   IN IPFSCRIPT_DATABASE.IPFSCRIPT_LS_IDIPFL03
   STORES COGIPF_HUMANTASKSERVICE
   COLUMN COGIPF_TASK_SUBJECT;
 
CREATE  UNIQUE  INDEX IPFAUXIX02 ON IPFAUXTB02 USING STOGROUP IPFSCRIPT_STOGROUP;

CREATE AUXILIARY TABLE IPFAUXTB03
   IN IPFSCRIPT_DATABASE.IPFSCRIPT_LS_IDIPFL04
   STORES COGIPF_HUMANTASKSERVICE
   COLUMN COGIPF_TASK_DESCRIPTION;
 
CREATE  UNIQUE  INDEX IPFAUXIX03 ON IPFAUXTB03 USING STOGROUP IPFSCRIPT_STOGROUP;

CREATE TABLE COGIPF_HUMANTASKSERVICE_DETAIL (
	COGIPF_HOST_IPADDR 	varchar (128) ,
	COGIPF_HOST_PORT 		integer ,
	COGIPF_SESSIONID 		varchar (255) ,
	COGIPF_REQUESTID 		varchar (255) ,
	COGIPF_STEPID 		varchar (255) ,
	COGIPF_SUBREQUESTID 	varchar (255) ,
	COGIPF_TASKID 		varchar (50) ,
	COGIPF_LOGENTRYID 		varchar (50) not null ,
	COGIPF_NOTIFICATION_DETAILS	clob(1G), 
	COGIPF_HUMANROLE_USER		varchar(255),
	COGIPF_HUMANROLE_ROLE		varchar(50),
	COGIPF_SUBSCRIPTION_OPERATION	varchar(50),
	COGIPF_SUBSCRIPTION_EVENT	smallint,
	COGIPF_SUBSCRIPTION_USER	varchar(255),
	COGIPF_TASK_MESSAGE		clob(1G), 
	COGIPF_TASK_MESSAGE_TYPE	varchar(20),
	COGIPF_DETAIL_ID			varchar(50) not null
) IN IPFSCRIPT_CREATE_IN;

CREATE AUXILIARY TABLE IPFAUXTB04
   IN IPFSCRIPT_DATABASE.IPFSCRIPT_LS_IDIPFL05
   STORES COGIPF_HUMANTASKSERVICE_DETAIL
   COLUMN COGIPF_TASK_MESSAGE;
 
CREATE  UNIQUE  INDEX IPFAUXIX04 ON IPFAUXTB04 USING STOGROUP IPFSCRIPT_STOGROUP;

CREATE AUXILIARY TABLE IPFAUXTB05
   IN IPFSCRIPT_DATABASE.IPFSCRIPT_LS_IDIPFL06
   STORES COGIPF_HUMANTASKSERVICE_DETAIL
   COLUMN COGIPF_NOTIFICATION_DETAILS;
 
CREATE  UNIQUE  INDEX IPFAUXIX05 ON IPFAUXTB05 USING STOGROUP IPFSCRIPT_STOGROUP;

CREATE TABLE COGIPF_ANNOTATIONSERVICE (
	COGIPF_HOST_IPADDR    		varchar (128),
	COGIPF_HOST_PORT      		integer,
	COGIPF_PROC_ID        		integer,
	COGIPF_LOCALTIMESTAMP         	timestamp,
	COGIPF_TIMEZONE_OFFSET        	integer,
	COGIPF_SESSIONID      		varchar (255),
	COGIPF_REQUESTID      		varchar (255) not null,
	COGIPF_STEPID         		varchar (255),
	COGIPF_SUBREQUESTID   		varchar (255),
	COGIPF_THREADID       		varchar (255),
	COGIPF_COMPONENTID    		varchar (64),
	COGIPF_BUILDNUMBER    		integer,
	COGIPF_LOG_LEVEL  		integer,
	COGIPF_OPERATION      		varchar (255),
	COGIPF_TARGET_TYPE    		varchar (255),
	COGIPF_TARGET_PATH    		varchar (1024),
	COGIPF_ANNOTATION_ID    	bigint,
	COGIPF_USER				varchar (1024) ,
	COGIPF_PARENT_ID			varchar (1024) ,
	COGIPF_CREATION_TIME 		timestamp,
	COGIPF_UPDATE_TIME 		timestamp 
) IN IPFSCRIPT_CREATE_IN;