Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance. Project price only 1 $
You can buy this project and download/modify it how often you want.
--#SET TERMINATOR @
--This value should be changed, use the same user that BPMS is using to access the DB2
SET CURRENT SCHEMA BPMS@
-- to execute this script open connect in the DB2 console: db2 connect to BPMS
-- Then perform this command: db2 -tf jbpm-6.1-to-6.2.sql
CREATE OR REPLACE PROCEDURE UPDATE_IDENTITY()
MODIFIES SQL DATA
BEGIN
DECLARE counter INTEGER;
DECLARE queryDrop VARCHAR(255);
DECLARE queryAlterSessionInfo VARCHAR(255);
DECLARE queryGenerateIdentity VARCHAR(255);
SET (counter) = (SELECT NVL(MAX(id), 0) AS id FROM SessionInfo);
SET queryDrop = ('ALTER TABLE SessionInfo ALTER COLUMN id DROP IDENTITY');
EXECUTE IMMEDIATE queryDrop;
SET queryAlterSessionInfo = ('ALTER TABLE SessionInfo ALTER COLUMN id SET DATA TYPE BIGINT');
EXECUTE IMMEDIATE queryAlterSessionInfo;
SET queryGenerateIdentity = ('ALTER TABLE SessionInfo ALTER COLUMN id SET GENERATED ALWAYS AS IDENTITY (start with '|| counter ||')');
EXECUTE IMMEDIATE queryGenerateIdentity;
END
@
call UPDATE_IDENTITY()@
DROP PROCEDURE UPDATE_IDENTITY@
Call SYSPROC.ADMIN_CMD('reorg table SessionInfo')@
Call SYSPROC.ADMIN_CMD('runstats on table SessionInfo and indexes all')@
alter table AuditTaskImpl alter column processSessionId set data type bigint@
alter table AuditTaskImpl alter column activationTime set data type timestamp@
alter table AuditTaskImpl alter column createdOn set data type timestamp@
alter table ContextMappingInfo alter column KSESSION_ID set data type bigint@
alter table Task alter column processSessionId set data type bigint@
-- the following statement works in the SQL clients like Squirrel and db2' console, Note: When using db2 console it is necessary to use SCHEMA.TABLENAME
-- Call SYSPROC.ADMIN_CMD('reorg table SessionInfo')
-- Call SYSPROC.ADMIN_CMD('reorg table AuditTaskImpl')
-- Call SYSPROC.ADMIN_CMD('reorg table ContextMappingInfo')
-- Call SYSPROC.ADMIN_CMD('reorg table Task')
-- Call SYSPROC.ADMIN_CMD('reorg table TaskEvent')
-- it should be executed after to reorg the tables
Call SYSPROC.ADMIN_CMD('reorg table AuditTaskImpl')@
alter table AuditTaskImpl alter column dueDate set data type timestamp@
create table DeploymentStore (
id bigint generated by default as identity,
attributes varchar(255),
DEPLOYMENT_ID varchar(255),
deploymentUnit clob(65535),
state integer,
updateDate timestamp,
primary key (id)
)@
create unique index UK_DeploymentStore_1 on DeploymentStore (DEPLOYMENT_ID)@
alter table ProcessInstanceLog add processInstanceDescription varchar(255)@
alter table RequestInfo add owner varchar(255)@
alter table Task add description varchar(255)@
alter table Task add name varchar(255)@
alter table Task add subject varchar(255)@
-- update all tasks with its name, subject and description
Call SYSPROC.ADMIN_CMD('reorg table Task')@
update Task t set name = (select shortText from I18NText where Task_Names_Id = t.id)@
update Task t set subject = (select shortText from I18NText where Task_Subjects_Id = t.id)@
update Task t set description = (select shortText from I18NText where Task_Descriptions_Id = t.id)@
-- It is necessary to reorg again to work
Call SYSPROC.ADMIN_CMD('reorg table AuditTaskImpl')@
INSERT INTO AuditTaskImpl (activationTime, actualOwner, createdBy, createdOn, deploymentId, description, dueDate, name, parentId, priority, processId, processInstanceId, processSessionId, status, taskId)
SELECT activationTime, actualOwner_id, createdBy_id, createdOn, deploymentId, description, expirationTime, name, parentId, priority,processId, processInstanceId, processSessionId, status, id FROM Task@
alter table TaskEvent add workItemId bigint@
alter table TaskEvent add processInstanceId bigint@
update TaskEvent t set workItemId = (select workItemId from Task where id = t.taskId)@
update TaskEvent t set processInstanceId = (select processInstanceId from Task where id = t.taskId)@