db.upgrade-scripts.db2.jbpm-6.1-to-6.2.sql Maven / Gradle / Ivy

--#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)@