---------- Script start ----------------------------------- -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSACTUALER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSARCHIVEDREPORT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAHOMEDEPARTMENT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEXCLUDEUSERTOKEN'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSHOMEDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSINCLUDEEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSLATEPAY'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSORGDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPLTANK'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPILOTPARTICIPANT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPOSSIBLEER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSCHDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSPONSOREDPROJECT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSUSER'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSUSER DROP CONSTRAINT ERSUser_FK_EMP; ALTER TABLE ERSPILOTPARTICIPANT DROP CONSTRAINT ERSPilotPart_FK_E; ALTER TABLE ERSPOSSIBLEER DROP CONSTRAINT ERS_PER_FK_EMP; ALTER TABLE ERSPI DROP CONSTRAINT ERSPI_FK_EMP; ALTER TABLE ERSLATEPAY DROP CONSTRAINT ERSLatePay_FK; ALTER TABLE ERSORGDEPT DROP CONSTRAINT ERS_ORGDEPT_FK2; ALTER TABLE ERSEXCLUDEUSERTOKEN DROP CONSTRAINT ERS_EUTOKEN_FK_EMP; ALTER TABLE ERSEARNINGS DROP CONSTRAINT ERSEARN_FK_EMP; ALTER TABLE ERSCSEMPL DROP CONSTRAINT ERSCSEMPL_FK_EMP; ALTER TABLE ERSCRITERIAEMPLOYEE DROP CONSTRAINT ERS_CE_FK_EMP; select distinct TC.tabname, TC.constname, TC.type, SKCU.colseq, SKCU.colname from sysCAT.tabconst TC ,sysibm.syskeycoluse SKCU where TC.tabschema = TC.tabschema and TC.constname like SKCU.constname and TC.tabname = SKCU.tbname and SKCU.constname LIKE 'ERS%' and SKCU.tbname = 'ERSEMPL' order by constname, colseq; ALTER TABLE ERSEMPL DROP CONSTRAINT ERSEmpl_PK; ALTER TABLE ERSEMPL ADD PPS_EMPLOYEE_ID CHAR(9); UPDATE ERSEMPL set pps_employee_id = employee_id; ALTER TABLE ERSEMPL ALTER COLUMN PPS_EMPLOYEE_ID SET DATA TYPE varchar(9); ALTER TABLE ERSEMPL ALTER COLUMN PPS_EMPLOYEE_ID SET NOT NULL; select distinct TC.tabname, TC.constname, TC.type, SKCU.colseq, SKCU.colname from sysCAT.tabconst TC ,sysibm.syskeycoluse SKCU where TC.tabschema = TC.tabschema and TC.constname like SKCU.constname and TC.tabname = SKCU.tbname and SKCU.constname LIKE 'ERS%' and SKCU.tbname = 'ERSEMPL' order by constname, colseq; ALTER TABLE ERSEMPL ALTER COLUMN EMP_NAME SET DATA TYPE varchar(120); ALTER TABLE ERSEMPL ALTER COLUMN EMPLOYEE_ID SET DATA TYPE varchar(11); ALTER TABLE ERSEMPL ALTER COLUMN EMPLOYEE_ID SET NOT NULL; select distinct TC.tabname, TC.constname, TC.type, SKCU.colseq, SKCU.colname from sysCAT.tabconst TC ,sysibm.syskeycoluse SKCU where TC.tabschema = TC.tabschema and TC.constname like SKCU.constname and TC.tabname = SKCU.tbname and SKCU.constname LIKE 'ERS%' and SKCU.tbname = 'ERSEMPL' order by constname, colseq; -- proactive reorgs to help avoid -668 errors - start1 CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSACTUALER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSARCHIVEDREPORT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAHOMEDEPARTMENT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEXCLUDEUSERTOKEN'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSHOMEDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSINCLUDEEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSLATEPAY'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSORGDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPLTANK'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPILOTPARTICIPANT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPOSSIBLEER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSCHDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSPONSOREDPROJECT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSUSER'); -- proactive reorgs to help avoid -668 errors - end DROP INDEX ERSEARNINGS_IDX; DROP INDEX ERSEARNING_UNQ_IDX; ALTER TABLE ERSEARNINGS ALTER COLUMN Employee_ID SET DATA TYPE varchar(11); -- 9/10/18 at 1:45pm - failed with -668 at the following statement, so added a reorg: -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end CREATE INDEX ERSEARNINGS_IDX ON ERSEARNINGS (Employee_ID ASC, Pay_Per_End_Date ASC); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end -- 9/10/18 at 1:45pm - failed with -668 at the following statement, so added a reorg: -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSCSEMPL DROP CONSTRAINT ERSCSEmpl_PK; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSCSEMPL ALTER COLUMN EMPLOYEE_ID SET DATA TYPE varchar(11); ALTER TABLE ERSCSEMPL ALTER COLUMN EMPLOYEE_ID SET NOT NULL; -- 9/10/18 at 2:04pm - failed with -668 at the following statement, so added a reorg: -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSCSEMPL ADD CONSTRAINT ERSCSEMPL_PK PRIMARY KEY (FS_ID, EMPLOYEE_ID, START_DATE); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSUSER ALTER COLUMN EMPLOYEE_ID SET DATA TYPE varchar(11); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSCRITERIAEMPLOYEE DROP CONSTRAINT ERS_CE_PK; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSCRITERIAEMPLOYEE ALTER COLUMN EMPLOYEE_ID SET DATA TYPE varchar(11); ALTER TABLE ERSCRITERIAEMPLOYEE ALTER COLUMN EMPLOYEE_ID SET NOT NULL; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSCRITERIAEMPLOYEE ADD CONSTRAINT ERS_CE_PK PRIMARY KEY (SC_ID, Employee_ID); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSLATEPAY DROP CONSTRAINT ERSLatePay_PK; --- 9/11/18 - 7:27am stopping point -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSACTUALER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSARCHIVEDREPORT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAHOMEDEPARTMENT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEXCLUDEUSERTOKEN'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSHOMEDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSINCLUDEEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSLATEPAY'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSORGDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPLTANK'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPILOTPARTICIPANT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPOSSIBLEER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSCHDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSPONSOREDPROJECT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSUSER'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSLATEPAY ALTER COLUMN EMPLOYEE_ID SET DATA TYPE varchar(11); ALTER TABLE ERSLATEPAY ALTER COLUMN EMPLOYEE_ID SET NOT NULL; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSLATEPAY'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSLATEPAY ADD CONSTRAINT ERSLATEPAY_PK PRIMARY KEY(Employee_ID,Period_ID); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSLATEPAY'); -- proactive reorgs to help avoid -668 errors - end -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPOSSIBLEER'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSPOSSIBLEER ALTER COLUMN EMPLOYEE_ID SET DATA TYPE varchar(11) ; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPOSSIBLEER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSPI DROP CONSTRAINT ERSPI_PK; ALTER TABLE ERSPI ALTER COLUMN EMP_ID SET DATA TYPE varchar(11) ; ALTER TABLE ERSPI ALTER COLUMN EMP_ID SET NOT NULL; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSPI ADD CONSTRAINT ERSPI_PK PRIMARY KEY (SP_ID, Emp_ID); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSEXCLUDEUSERTOKEN DROP CONSTRAINT ERS_EUToken_PK; ALTER TABLE ERSEXCLUDEUSERTOKEN ALTER COLUMN EMPLOYEE_ID SET DATA TYPE varchar(11); ALTER TABLE ERSEXCLUDEUSERTOKEN ALTER COLUMN EMPLOYEE_ID SET NOT NULL; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEXCLUDEUSERTOKEN'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSEXCLUDEUSERTOKEN ADD CONSTRAINT ERS_EUTOKEN_PK PRIMARY KEY (Employee_ID); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEXCLUDEUSERTOKEN'); -- proactive reorgs to help avoid -668 errors - end DROP INDEX ERS_AER_UNQ; ALTER TABLE ERSACTUALER ALTER COLUMN Employee_ID SET DATA TYPE varchar(11); ALTER TABLE ERSACTUALER ALTER COLUMN Employee_ID SET NOT NULL; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSACTUALER'); -- proactive reorgs to help avoid -668 errors - end CREATE UNIQUE INDEX ERS_AER_UNQ ON ERSACTUALER( employee_id, period_id ); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSACTUALER'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSINCLUDEEMPL DROP CONSTRAINT ERSINCLUDEEMPL_PK; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSACTUALER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSARCHIVEDREPORT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAHOMEDEPARTMENT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEXCLUDEUSERTOKEN'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSHOMEDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSINCLUDEEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSLATEPAY'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSORGDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPLTANK'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPILOTPARTICIPANT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPOSSIBLEER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSCHDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSPONSOREDPROJECT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSUSER'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSINCLUDEEMPL ALTER COLUMN Employee_ID SET DATA TYPE varchar(11); ALTER TABLE ERSINCLUDEEMPL ALTER COLUMN Employee_ID SET not null; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSINCLUDEEMPL'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSINCLUDEEMPL ADD CONSTRAINT ERSIncludeEmpl_PK PRIMARY KEY (Employee_ID); ALTER TABLE ERSPIEMPLTANK DROP CONSTRAINT ERSPIEmplTank_PK; ALTER TABLE ERSPIEMPLTANK ALTER COLUMN Emp_ID SET DATA TYPE varchar(11); ALTER TABLE ERSPIEMPLTANK ALTER COLUMN Emp_ID SET not null; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPLTANK'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSPIEMPLTANK ADD CONSTRAINT ERSPIEmplTank_PK PRIMARY KEY (Project_ID, Emp_ID); ALTER TABLE ERSPILOTPARTICIPANT DROP CONSTRAINT ERSPilotPart_PK; ALTER TABLE ERSPILOTPARTICIPANT ALTER COLUMN emp_id SET DATA TYPE varchar(11); ALTER TABLE ERSPILOTPARTICIPANT ALTER COLUMN emp_id SET not null; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPILOTPARTICIPANT'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSPILOTPARTICIPANT ADD CONSTRAINT ERSPilotPart_PK PRIMARY KEY (period_id, Emp_ID); select distinct TC.tabname, TC.constname, TC.type, SKCU.colseq, SKCU.colname from sysCAT.tabconst TC ,sysibm.syskeycoluse SKCU where TC.tabschema = TC.tabschema and TC.constname like SKCU.constname and TC.tabname = SKCU.tbname and SKCU.constname LIKE 'ERS%' and SKCU.tbname = 'ERSEMPL' order by constname, colseq; ALTER TABLE ERSEMPL ADD CONSTRAINT ERSEmpl_PK PRIMARY KEY(Employee_ID); select distinct TC.tabname, TC.constname, TC.type, SKCU.colseq, SKCU.colname from sysCAT.tabconst TC ,sysibm.syskeycoluse SKCU where TC.tabschema = TC.tabschema and TC.constname like SKCU.constname and TC.tabname = SKCU.tbname and SKCU.constname LIKE 'ERS%' and SKCU.tbname = 'ERSEMPL' order by constname, colseq; ALTER TABLE ERSCSEMPL ADD CONSTRAINT ERSCSEmpl_fk_emp FOREIGN KEY (Employee_ID) REFERENCES ERSEmpl (Employee_ID); ALTER TABLE ERSCRITERIAEMPLOYEE ADD CONSTRAINT ERS_CE_FK_EMP FOREIGN KEY (Employee_ID) REFERENCES ERSEmpl (Employee_ID); ALTER TABLE ERSLATEPAY ADD CONSTRAINT ERSLatePay_FK FOREIGN KEY (Employee_ID) REFERENCES ERSEmpl (Employee_ID); ALTER TABLE ERSEXCLUDEUSERTOKEN ADD CONSTRAINT ERS_EUTOKEN_FK_EMP FOREIGN KEY (EMPLOYEE_ID) REFERENCES ERSEmpl (Employee_ID); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSACTUALER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSARCHIVEDREPORT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAHOMEDEPARTMENT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEXCLUDEUSERTOKEN'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSHOMEDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSINCLUDEEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSLATEPAY'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSORGDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPLTANK'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPILOTPARTICIPANT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPOSSIBLEER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSCHDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSPONSOREDPROJECT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSUSER'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSPILOTPARTICIPANT ADD CONSTRAINT ERSPilotPart_FK_E FOREIGN KEY (EMP_ID) REFERENCES ERSEmpl (Employee_ID); select distinct TC.tabname, TC.constname, TC.type, SKCU.colseq, SKCU.colname from sysCAT.tabconst TC ,sysibm.syskeycoluse SKCU where TC.tabschema = TC.tabschema and TC.constname like SKCU.constname and TC.tabname = SKCU.tbname and SKCU.constname LIKE 'ERS%' and SKCU.tbname = 'ERSEMPL' order by constname, colseq; ALTER TABLE ERSPOSSIBLEER ADD CONSTRAINT ERS_PER_FK_EMP FOREIGN KEY (EMPLOYEE_ID) REFERENCES ERSEmpl (Employee_ID); ALTER TABLE ERSPI ADD CONSTRAINT ERSPI_FK_EMP FOREIGN KEY (EMP_ID) REFERENCES ERSEmpl (Employee_ID); ALTER TABLE ERSUSER ADD CONSTRAINT ERSUser_fk_emp FOREIGN KEY (Employee_ID) REFERENCES ERSEmpl (Employee_ID); ALTER TABLE ERSEARNINGS ADD CONSTRAINT ERSEarn_FK_emp FOREIGN KEY (Employee_ID) REFERENCES ERSEmpl (Employee_ID); DROP INDEX ERSARCHIVEDREPORT_INDEX2; ALTER TABLE ERSARCHIVEDREPORT ALTER COLUMN EMP_ID SET DATA TYPE varchar(11); ALTER TABLE ERSARCHIVEDREPORT ALTER COLUMN EMP_ID SET NOT NULL; ALTER TABLE ERSArchivedReport ALTER COLUMN EMP_NAME SET DATA TYPE VARCHAR(120); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSARCHIVEDREPORT'); -- proactive reorgs to help avoid -668 errors - end CREATE INDEX ERSARCHIVEDREPORT_INDEX2 ON ERSARCHIVEDREPORT( emp_id ); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSARCHIVEDREPORT'); -- proactive reorgs to help avoid -668 errors - end -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSACTUALER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSARCHIVEDREPORT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAHOMEDEPARTMENT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEXCLUDEUSERTOKEN'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSHOMEDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSINCLUDEEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSLATEPAY'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSORGDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPLTANK'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPILOTPARTICIPANT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPOSSIBLEER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSCHDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSPONSOREDPROJECT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSUSER'); -- proactive reorgs to help avoid -668 errors - end select distinct TC.tabname, TC.constname, TC.type, SKCU.colseq, SKCU.colname from sysCAT.tabconst TC ,sysibm.syskeycoluse SKCU where TC.tabschema = TC.tabschema and TC.constname like SKCU.constname and TC.tabname = SKCU.tbname and SKCU.constname LIKE 'ERS%' and SKCU.tbname = 'ERSEMPL' order by constname, colseq; update ERSEMPL set employee_id=trim(employee_id); select distinct TC.tabname, TC.constname, TC.type, SKCU.colseq, SKCU.colname from sysCAT.tabconst TC ,sysibm.syskeycoluse SKCU where TC.tabschema = TC.tabschema and TC.constname like SKCU.constname and TC.tabname = SKCU.tbname and SKCU.constname LIKE 'ERS%' and SKCU.tbname = 'ERSEMPL' order by constname, colseq; ALTER TABLE ERSEMPL DROP CONSTRAINT ERSEmpl_FK ; select distinct TC.tabname, TC.constname, TC.type, SKCU.colseq, SKCU.colname from sysCAT.tabconst TC ,sysibm.syskeycoluse SKCU where TC.tabschema = TC.tabschema and TC.constname like SKCU.constname and TC.tabname = SKCU.tbname and SKCU.constname LIKE 'ERS%' and SKCU.tbname = 'ERSEMPL' order by constname, colseq; ALTER TABLE ERSHOMEDEPT DROP CONSTRAINT ERSHomeDept_PK; ALTER TABLE ERSHOMEDEPT ALTER COLUMN DEPT_ID SET DATA TYPE varchar(10); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSHOMEDEPT'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSHOMEDEPT ADD CONSTRAINT ERSHomeDept_PK PRIMARY KEY(DEPT_ID); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSHOMEDEPT'); -- proactive reorgs to help avoid -668 errors - end select distinct TC.tabname, TC.constname, TC.type, SKCU.colseq, SKCU.colname from sysCAT.tabconst TC ,sysibm.syskeycoluse SKCU where TC.tabschema = TC.tabschema and TC.constname like SKCU.constname and TC.tabname = SKCU.tbname and SKCU.constname LIKE 'ERS%' and SKCU.tbname = 'ERSEMPL' order by constname, colseq; ALTER TABLE ERSEMPL ALTER COLUMN DEPT_ID SET DATA TYPE varchar(10); select distinct TC.tabname, TC.constname, TC.type, SKCU.colseq, SKCU.colname from sysCAT.tabconst TC ,sysibm.syskeycoluse SKCU where TC.tabschema = TC.tabschema and TC.constname like SKCU.constname and TC.tabname = SKCU.tbname and SKCU.constname LIKE 'ERS%' and SKCU.tbname = 'ERSEMPL' order by constname, colseq; ALTER TABLE ERSORGDEPT DROP CONSTRAINT ERSORGDEPTPK; ALTER TABLE ERSORGDEPT ALTER COLUMN DEPT_ID SET DATA TYPE varchar(10); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSORGDEPT'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSORGDEPT ADD CONSTRAINT ERSORGDEPTPK PRIMARY KEY(Org_CD, Dept_ID); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSORGDEPT'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSORGDEPT ADD CONSTRAINT ERS_ORGDEPT_FK2 FOREIGN KEY(DEPT_ID) REFERENCES ERSHOMEDEPT (DEPT_ID); ALTER TABLE ERSCRITERIAHOMEDEPARTMENT DROP CONSTRAINT ERS_CHD_PK; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEMPL'); -- proactive reorgs to help avoid -668 errors - end -- the following was failing with -573 bef adding reorg ALTER TABLE ERSEMPL ADD CONSTRAINT ERSEmpl_FK FOREIGN KEY (Dept_ID) REFERENCES ERSHomeDept (Dept_ID); ALTER TABLE ERSSCHDEPT DROP CONSTRAINT ERSSchDept_PK ; ALTER TABLE ERSSCHDEPT ALTER COLUMN DEPT_ID SET DATA TYPE varchar(10); ALTER TABLE ERSSCHDEPT ALTER COLUMN DEPT_ID SET NOT NULL; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSCHDEPT'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSSCHDEPT ADD CONSTRAINT ERSSchDept_PK PRIMARY KEY (Sch_CD, Dept_ID); ALTER TABLE ERSCRITERIAHOMEDEPARTMENT ALTER COLUMN DEPT_ID SET DATA TYPE varchar(10); ALTER TABLE ERSCRITERIAHOMEDEPARTMENT ALTER COLUMN DEPT_ID SET NOT NULL; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAHOMEDEPARTMENT'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSCRITERIAHOMEDEPARTMENT ADD CONSTRAINT ERS_CHD_PK PRIMARY KEY (SC_ID, DEPT_ID); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSACTUALER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSARCHIVEDREPORT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAHOMEDEPARTMENT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEXCLUDEUSERTOKEN'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSHOMEDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSINCLUDEEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSLATEPAY'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSORGDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPLTANK'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPILOTPARTICIPANT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPOSSIBLEER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSCHDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSPONSOREDPROJECT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSUSER'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSCRITERIAHOMEDEPARTMENT ADD CONSTRAINT ERS_CHD_FK_HD FOREIGN KEY (DEPT_ID) REFERENCES ERSHOMEDEPT (DEPT_ID); UPDATE ERSHOMEDEPT set DEPT_ID=TRIM(DEPT_ID); alter table ERSEARNINGS add Run_Id varchar(10) ; alter table ERSEARNINGS add Empl_Rcd integer; alter table ERSEARNINGS add Eff_Date TIMESTAMP ; alter table ERSEARNINGS add Eff_Seq integer; alter table ERSEARNINGS add Off_Cycle varchar(1); alter table ERSEARNINGS add Run_Id_Earn varchar(10) ; alter table ERSEARNINGS add Addl_Seq varchar(10); alter table ERSEARNINGS add Journal_Id varchar(10); alter table ERSEARNINGS add Journal_Line varchar(10); alter table ERSEARNINGS add Journal_Line_Ref varchar(10); alter table ERSEARNINGS add Business_Unit varchar(5); alter table ERSEARNINGS add Pay_Cat varchar(2); alter table ERSEARNINGS add Time_Code varchar(2); alter table ERSEARNINGS add Restatement varchar(1); alter table ERSEARNINGS add Interface varchar(1); Alter TABLE ERSEARNINGS ALTER COLUMN DOS_TIME_CODE SET DATA TYPE varchar(2); Alter TABLE ERSEARNINGS ALTER COLUMN DOS_PAY_CATEGORY SET DATA TYPE varchar(2); Alter TABLE ERSEARNINGS ALTER COLUMN TITLE_CODE SET DATA TYPE varchar(6); update ERSVERSION set version_id='11.0'; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end update ERSEARNINGS set RESTATEMENT='N', INTERFACE='P' where year(PAY_CYCLE_END_DATE) = 2010; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end update ERSEARNINGS set RESTATEMENT='N', INTERFACE='P' where year(PAY_CYCLE_END_DATE) = 2011; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end update ERSEARNINGS set RESTATEMENT='N', INTERFACE='P' where year(PAY_CYCLE_END_DATE) = 2012; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end update ERSEARNINGS set RESTATEMENT='N', INTERFACE='P' where year(PAY_CYCLE_END_DATE) = 2013; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end update ERSEARNINGS set RESTATEMENT='N', INTERFACE='P' where year(PAY_CYCLE_END_DATE) = 2014; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end update ERSEARNINGS set RESTATEMENT='N', INTERFACE='P' where year(PAY_CYCLE_END_DATE) = 2015; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end update ERSEARNINGS set RESTATEMENT='N', INTERFACE='P' where year(PAY_CYCLE_END_DATE) = 2016; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end update ERSEARNINGS set RESTATEMENT='N', INTERFACE='P' where year(PAY_CYCLE_END_DATE) = 2017; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end update ERSEARNINGS set RESTATEMENT='N', INTERFACE='P' where year(PAY_CYCLE_END_DATE) = 2018; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end update ERSEARNINGS set RESTATEMENT='N', INTERFACE='P' where year(PAY_CYCLE_END_DATE) = 2019; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSACTUALER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSARCHIVEDREPORT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAHOMEDEPARTMENT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEXCLUDEUSERTOKEN'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSHOMEDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSINCLUDEEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSLATEPAY'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSORGDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPLTANK'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPILOTPARTICIPANT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPOSSIBLEER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSCHDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSPONSOREDPROJECT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSUSER'); -- proactive reorgs to help avoid -668 errors - end insert into ERSDOSCODE values ('9AC','O', current timestamp,'*INSTALL*',null,null); insert into ERSDOSCODE values ('9TC','O', current timestamp,'*INSTALL*',null,null); insert into ERSDOSCODE values ('9CC','O', current timestamp,'*INSTALL*',null,null); insert into ERSDOSCODE values ('9AS','O', current timestamp,'*INSTALL*',null,null); insert into ERSDOSCODE values ('9CS','O', current timestamp,'*INSTALL*',null,null); insert into ERSDOSCODE values ('9DC','O', current timestamp,'*INSTALL*',null,null); insert into ERSDOSCODE values ('AAC','O', current timestamp,'*INSTALL*',null,null); insert into ERSDOSCODE values ('ACS','O', current timestamp,'*INSTALL*',null,null); insert into ERSDOSCODE values ('ACT','O', current timestamp,'*INSTALL*',null,null); insert into ERSDOSCODE values ('ADC','O', current timestamp,'*INSTALL*',null,null); insert into ERSDOSCODE values ('APA','O', current timestamp,'*INSTALL*',null,null); insert into ERSDOSCODE values ('ASN','O', current timestamp,'*INSTALL*',null,null); insert into ERSDOSCODE values ('SSC','O', current timestamp,'*INSTALL*',null,null); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSPayCat ALTER COLUMN DOS_PAY_CAT SET DATA TYPE VARCHAR(2); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); -- proactive reorgs to help avoid -668 errors - end insert into ERSPAYCAT values('1','R', current timestamp,'*INSTALL*',null,null); insert into ERSPAYCAT values('1','O', current timestamp,'*INSTALL*',null,null); CREATE INDEX ERS_EARNINGS_I129_UNIQUE ON ERSEARNINGS (Interface, Empl_Rcd, Eff_date, Eff_Seq, Run_Id, Run_Id_Earn, Business_Unit, Off_Cycle, Dist_Dos, Journal_Id, Journal_Line, Journal_Line_Ref, Addl_Seq); CREATE INDEX ERS_EARNINGS_PAR_UNIQUE ON ERSEARNINGS(Interface, Pay_Cycle_End_Date, Pay_Cycle_Code, Record_Type, Pri_Gross_Ctl, Entry_Seq_No); ALTER TABLE ERSEARNINGS ALTER COLUMN Paid_Hours SET DATA TYPE decimal(6,2); ALTER TABLE ERSEARNINGS ALTER COLUMN Paid_Hours SET NOT NULL; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); -- proactive reorgs to help avoid -668 errors - end -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSEARNINGS ALTER COLUMN Paid_Amount SET DATA TYPE decimal(12,2); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSEARNINGS ALTER COLUMN Paid_Amount SET NOT NULL; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSEARNINGS ALTER COLUMN Pay_Rate SET DATA TYPE decimal(14,4); ALTER TABLE ERSEARNINGS ALTER COLUMN Pay_Rate SET NOT NULL; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); -- proactive reorgs to help avoid -668 errors - end insert into ERSPERSPGMCODE values('1','3', CURRENT TIMESTAMP, 'sys.admin', CURRENT TIMESTAMP,'sys.admin'); insert into ERSPERSPGMCODE values('1','B', CURRENT TIMESTAMP, 'sys.admin', CURRENT TIMESTAMP,'sys.admin'); insert into ERSPERSPGMCODE values('1','C', CURRENT TIMESTAMP, 'sys.admin', CURRENT TIMESTAMP,'sys.admin'); insert into ERSPAYCAT values('2','R', CURRENT TIMESTAMP, 'sys.admin', CURRENT TIMESTAMP,'sys.admin'); insert into ERSPAYCAT values('2','O', CURRENT TIMESTAMP, 'sys.admin', CURRENT TIMESTAMP,'sys.admin'); alter table ERSEARNINGS add Over_The_Cap varchar(10); alter table ERSTIMECODE add Interface varchar(1); alter table ERSPAYCAT add Interface varchar(1); Update ERSTimeCode set interface = 'P' ; ALTER TABLE ERSTimeCode DROP CONSTRAINT ERSTimeCode_PK; ALTER TABLE ERSTimeCode ALTER COLUMN DOS_TIME_CODE SET DATA TYPE VARCHAR(2); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSTIMECODE ALTER COLUMN Interface set data type Varchar(1); ALTER TABLE ERSTIMECODE ALTER COLUMN Interface SET NOT NULL; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSTIMECODE ADD CONSTRAINT ERSTimeCode_PK PRIMARY KEY (DOS_Time_Code, Period_Type, Interface); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); -- proactive reorgs to help avoid -668 errors - end -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSACTUALER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSARCHIVEDREPORT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAHOMEDEPARTMENT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEXCLUDEUSERTOKEN'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSHOMEDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSINCLUDEEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSLATEPAY'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSORGDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPLTANK'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPILOTPARTICIPANT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPOSSIBLEER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSCHDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSPONSOREDPROJECT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSUSER'); -- proactive reorgs to help avoid -668 errors - end insert into ERSTIMECODE values ('1','O', CURRENT TIMESTAMP, '*UPGRADE*',null,null,'L'); insert into ERSTimeCode values ('2','O', current timestamp, '*UPGRADE*',null,null,'L'); insert into ERSTimeCode values ('5','O', current timestamp, '*UPGRADE*',null,null,'L'); insert into ERSTimeCode values ('1','R', current timestamp, '*UPGRADE*',null,null,'L'); insert into ERSTimeCode values ('2','R', current timestamp, '*UPGRADE*',null,null,'L'); insert into ERSTimeCode values ('5','R', current timestamp,'*UPGRADE*',null,null,'L'); Update ERSPayCat set interface = 'P' where DOS_PAY_CAT = 'N'; Update ERSPayCat set interface = 'L' where DOS_PAY_CAT IN ('1', '2'); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSPAYCAT DROP CONSTRAINT ERSPayCat_PK; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSPAYCAT ALTER COLUMN Interface SET DATA TYPE Varchar(1) ; ALTER TABLE ERSPAYCAT ALTER COLUMN Interface SET NOT NULL; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); -- proactive reorgs to help avoid -668 errors - end ALTER TABLE ERSPAYCAT ADD CONSTRAINT ERSPayCat_PK PRIMARY KEY (DOS_Pay_Cat, Period_Type, Interface); -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); -- proactive reorgs to help avoid -668 errors - end Update ERSPayCat set interface = 'L' where DOS_Pay_Cat='1'; alter table ERSEARNINGS add Cap_Rate Decimal (14,4); update ersearnings set cap_rate=0; ALTER TABLE ERSPIEMPL ALTER COLUMN PI_EMP_ID SET DATA TYPE varchar(11); ALTER TABLE ERSPIEMPL ALTER COLUMN CO_PI_EMP_ID SET DATA TYPE varchar(11); ALTER TABLE ERSSPONSOREDPROJECT ALTER COLUMN PI_EMP_ID SET DATA TYPE varchar(11); ALTER TABLE ERSSPONSOREDPROJECT ALTER COLUMN CO_PI_EMP_ID SET DATA TYPE varchar(11); select max(Import_Seq_Nbr) from ERSEarnings ; ALTER TABLE ERSEarnings ALTER COLUMN Import_Seq_Nbr RESTART WITH 20000000 ; -- proactive reorgs to help avoid -668 errors - start CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSACTUALER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSARCHIVEDREPORT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAEMPLOYEE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCRITERIAHOMEDEPARTMENT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSCSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEARNINGS'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSEXCLUDEUSERTOKEN'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSHOMEDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSINCLUDEEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSLATEPAY'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSORGDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPAYCAT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPI'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPL'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPIEMPLTANK'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPILOTPARTICIPANT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSPOSSIBLEER'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSCHDEPT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSSPONSOREDPROJECT'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSTIMECODE'); CALL SYSPROC.ADMIN_CMD ('REORG TABLE ERSUSER'); /* -- proactive reorgs to help avoid -668 errors - end ---------- Script end ----------------------------------- */