analytics/configuration/schemas/logging/sqlserver/LS_dbUpgrade0_019-to-84_001_mssql.sql

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

sp_rename 'COGIPF_MIGRATION', 'COGIPF_MIGRATION_OLD'

CREATE TABLE COGIPF_MIGRATION (
	COGIPF_HOST_IPADDR 	varchar (128) NULL ,
	COGIPF_HOST_PORT 	int NULL ,
	COGIPF_PROC_ID 		int NULL ,
	COGIPF_LOCALTIMESTAMP 	datetime NULL ,
	COGIPF_TIMEZONE_OFFSET 	int NULL ,
	COGIPF_SESSIONID 	varchar (255) NULL ,
	COGIPF_REQUESTID 	varchar (255) NOT NULL ,
	COGIPF_STEPID 		varchar (255) NULL ,
	COGIPF_SUBREQUESTID 	varchar (255) NULL ,
	COGIPF_THREADID 	varchar (255) NULL ,
	COGIPF_COMPONENTID 	varchar (64) NULL ,
	COGIPF_BUILDNUMBER 	int NULL ,
	COGIPF_LOG_LEVEL 	int NULL ,
	COGIPF_OPERATION	varchar (64) NULL,
	COGIPF_TARGET_TYPE    	varchar (64) NULL,
	COGIPF_TARGET_PATH     	nvarchar (1024) NULL,
	COGIPF_TARGET_NAME     	nvarchar (255) NULL,
	COGIPF_STATUS 		varchar (64) NULL ,
	COGIPF_DETAILS	 	nvarchar (2000) NULL ,
	COGIPF_PACKAGE 		nvarchar (512) NULL ,
	COGIPF_MIGRATION_TASK	nvarchar (1024) NULL ,
	COGIPF_MSGNUM	int NULL ,
	COGIPF_SOURCE_TYPE	varchar (64) NULL,
	COGIPF_SOURCE_PATH	nvarchar (1024) NULL,
	COGIPF_SOURCE_NAME	nvarchar (255) NULL
)

insert into COGIPF_MIGRATION (COGIPF_HOST_IPADDR,COGIPF_HOST_PORT,COGIPF_PROC_ID,COGIPF_LOCALTIMESTAMP,COGIPF_TIMEZONE_OFFSET,COGIPF_SESSIONID,COGIPF_REQUESTID,COGIPF_STEPID,COGIPF_SUBREQUESTID,COGIPF_THREADID,COGIPF_COMPONENTID,COGIPF_BUILDNUMBER,COGIPF_LOG_LEVEL,COGIPF_OPERATION,COGIPF_TARGET_TYPE,COGIPF_TARGET_PATH,COGIPF_TARGET_NAME ,COGIPF_STATUS,COGIPF_DETAILS,COGIPF_PACKAGE ,COGIPF_MIGRATION_TASK ) select  COGIPF_HOST_IPADDR,COGIPF_HOST_PORT,COGIPF_PROC_ID,COGIPF_LOCALTIMESTAMP,COGIPF_TIMEZONE_OFFSET,COGIPF_SESSIONID,COGIPF_REQUESTID,COGIPF_STEPID,COGIPF_SUBREQUESTID,COGIPF_THREADID,COGIPF_COMPONENTID,COGIPF_BUILDNUMBER,COGIPF_LOG_LEVEL,COGIPF_OPERATION,SUBSTRING(COGIPF_TARGET_TYPE, 0, 64),COGIPF_TARGET_PATH,COGIPF_TARGET_NAME, SUBSTRING(COGIPF_STATUS, 0, 64),COGIPF_DETAILS,COGIPF_PACKAGE ,COGIPF_MIGRATION_TASK from COGIPF_MIGRATION_OLD

drop table COGIPF_MIGRATION_OLD

CREATE TABLE COGIPF_POWERPLAY (
	COGIPF_HOST_IPADDR 	varchar (128) NULL ,
	COGIPF_HOST_PORT 	int NULL ,
	COGIPF_PROC_ID 		int NULL ,
	COGIPF_LOCALTIMESTAMP 	datetime NULL ,
	COGIPF_TIMEZONE_OFFSET 	int NULL ,
	COGIPF_SESSIONID 	varchar (255) NULL ,
	COGIPF_REQUESTID 	varchar (255) NOT NULL ,
	COGIPF_STEPID 		varchar (255) NULL ,
	COGIPF_SUBREQUESTID 	varchar (255) NULL ,
	COGIPF_THREADID 	varchar (255) NULL ,
	COGIPF_COMPONENTID 	varchar (64) NULL ,
	COGIPF_BUILDNUMBER 	int NULL ,
	COGIPF_LOG_LEVEL 	int NULL ,
	COGIPF_TARGET_TYPE 	varchar (255) NULL ,
	COGIPF_REPORTPATH 	nvarchar (512) NULL ,
	COGIPF_STATUS 		varchar (255) NULL ,
	COGIPF_RUNTIME 		int NULL ,
	COGIPF_REPORTNAME 	nvarchar (255) NULL ,
	COGIPF_PACKAGE 		nvarchar (512) NULL ,
	COGIPF_DATASOURCE 	nvarchar (512) NULL ,
	COGIPF_DATASOURCE_CONNECTION 	nvarchar (512) NULL ,
	COGIPF_OPERATION 		varchar (128) NULL,
	COGIPF_MESSAGE	 	nvarchar (2000) NULL ,
	COGIPF_REQUEST_TYPE 		int NULL ,
	COGIPF_SUB_COMPONENTID 	varchar (64) NULL 
)

CREATE TABLE COGIPF_POWERPLAY_DIM_USAGE (
	COGIPF_SESSIONID 		varchar (256) NULL ,
	COGIPF_REQUESTID 		varchar (256) NOT NULL ,
	COGIPF_DIM_CODE 		varchar (256) NULL ,
	COGIPF_DIM_NAME 		varchar (256) NULL ,
	COGIPF_DIM_COUNT 		int NULL 
)

CREATE TABLE COGIPF_POWERPLAY_LEVEL_USAGE (
	COGIPF_SESSIONID 		varchar (256) NULL ,
	COGIPF_REQUESTID 		varchar (256) NOT NULL ,
	COGIPF_DIM_CODE 		varchar (256) NULL ,
	COGIPF_LEVEL_CODE 	varchar (256) NULL ,
	COGIPF_LEVEL_NAME 	varchar (256) NULL ,
	COGIPF_LEVEL_COUNT 	int NULL 
)

CREATE TABLE COGIPF_POWERPLAY_MEASURE_USAGE (
	COGIPF_SESSIONID 		varchar (256) NULL ,
	COGIPF_REQUESTID 		varchar (256) NOT NULL ,
	COGIPF_MEASURE_CODE 	varchar (256) NULL ,
	COGIPF_MEASURE_NAME 	varchar (256) NULL ,
	COGIPF_MEASURE_COUNT 	int NULL 
)