Troubleshooting
Problem
When updating from WebSphere Application Server 8.5.5.0 or 8.5.5.1 to 8.5.5.2, there is a runtime update that requires table changes to the job scheduler database. This document will detail the required database updates for your platform.
Symptom
A batch job submission without the table updates in place will fail. A job scheduler SQL error for a missing JOBCLASS table will appear in the job scheduler server logs.
Resolving The Problem
A Java batch scheduler configured prior to WebSphere Application Server Version 8.5.5.2 must make table changes to the job scheduler database when upgrading to Version 8.5.5.2 or higher,
The necessary table update statements are provided in files in the product and are also provided below. Modify them appropriately for your configuration.
DB2 for z/OS
Product file system location of update files:
WAS_HOME/util/Batch/UPDLRS
WAS_HOME/util/Batch/SPF__updateLocalJobStatusConstraint
Contents of the files for quick reference:
SET CURRENT RULES ='STD';
ALTER TABLE LRSSCHEMA.LOCALJOBSTATUS DROP CONSTRAINT
"PK_LOCALJOBSTATUS";
COMMIT;
DROP INDEX LRSSCHEMA.LOCJOBSTSIX;
COMMIT;
CREATE UNIQUE INDEX LRSSCHEMA.LOCJOBSTSIX
ON LRSSCHEMA.LOCALJOBSTATUS
(JOBID,BJEENAME)
USING STOGROUP LRSCHSG;
ALTER TABLE LRSSCHEMA.LOCALJOBSTATUS
ADD CONSTRAINT PK_LOCALJOBSTATUS PRIMARY KEY
(JOBID,BJEENAME) ;
ALTER TABLE LRSSCHEMA.JOBSTATUS
ADD JOBCLASS VARCHAR(250);
CREATE TABLE LRSSCHEMA.CAPACITYDETECTION
(JOBCLASS VARCHAR(250) NOT NULL,
LASTRUNTIME VARCHAR(250))
IN LRSCHED.LRSSPACE VOLATILE;
GRANT ALL ON LRSSCHEMA.CAPACITYDETECTION TO PUBLIC;
CREATE UNIQUE INDEX LRSSCHEMA.CAPIX
ON LRSSCHEMA.CAPACITYDETECTION
(JOBCLASS)
USING STOGROUP LRSCHSG;
ALTER TABLE LRSSCHEMA.CAPACITYDETECTION
ADD CONSTRAINT PK_JOBCLASS PRIMARY KEY
(JOBCLASS);
DB2 ULW
Product file system location of update files:
WAS_HOME/util/Batch/updateSchedulerDBForJobClassRecoveryDB2.ddl
WAS_HOME/util/Batch/UpdateLocalJobStatusConstraintDB2.ddl
Contents of the files for quick reference:
ALTER TABLE "LRSSCHEMA"."JOBSTATUS"
ADD "JOBCLASS" VARCHAR(250);
CREATE TABLE "LRSSCHEMA"."CAPACITYDETECTION" (
"JOBCLASS" VARCHAR(250) NOT NULL ,
"LASTRUNTIME" VARCHAR(250) )
IN "USERSPACE1" ;
ALTER TABLE "LRSSCHEMA"."CAPACITYDETECTION"
ADD CONSTRAINT "PK_JOBCLASS" PRIMARY KEY
("JOBCLASS");
ALTER TABLE "LRSSCHEMA"."LOCALJOBSTATUS"
DROP CONSTRAINT "PK_LOCALJOBSTATUS";
ALTER TABLE "LRSSCHEMA"."LOCALJOBSTATUS"
ADD CONSTRAINT "PK_LOCALJOBSTATUS" PRIMARY KEY
("JOBID", "BJEENAME");
Oracle
Product file system location of update files:
WAS_HOME/util/Batch/updateSchedulerDBForJobClassRecoveryOracle.ddl
WAS_HOME/util/Batch/UpdateLocalJobStatusConstraintOracle.ddl
Contents of the files for quick reference:
ALTER TABLE "JOBSTATUS"
ADD "JOBCLASS" VARCHAR(250)
;
CREATE TABLE "CAPACITYDETECTION" (
"JOBCLASS" VARCHAR(250) NOT NULL ,
"LASTRUNTIME" VARCHAR(250) )
TABLESPACE @LRSCHED_TABLESPACE@ ;
ALTER TABLE "CAPACITYDETECTION"
ADD CONSTRAINT "PK_JOBCLASS" PRIMARY KEY
("JOBCLASS");
ALTER TABLE "LOCALJOBSTATUS"
DROP CONSTRAINT "PK_LOCALJOBSTATUS";
ALTER TABLE "LOCALJOBSTATUS"
ADD CONSTRAINT "PK_LOCALJOBSTATUS" PRIMARY KEY
("JOBID", "BJEENAME");
Derby
Product file system location of update files:
WAS_HOME/util/Batch/updateSchedulerDBForJobClassRecoveryDerby.ddl
Note: There is no script to update the local job status constraint for Derby. A user can drop their existing Derby database and recreate it using the CreateLRSCHEDTableDerby.ddl to take advantage of the constraints update or the user can create their own script that would model the updates found in CreateLRSCHEDTableDerby.ddl
Contents of the files for quick reference:
ALTER TABLE "LRSSCHEMA"."JOBSTATUS"
ADD "JOBCLASS" VARCHAR(250);
CREATE TABLE "LRSSCHEMA"."CAPACITYDETECTION" (
"JOBCLASS" VARCHAR(250) NOT NULL ,
"LASTRUNTIME" VARCHAR(250) ) ;
ALTER TABLE "LRSSCHEMA"."CAPACITYDETECTION"
ADD CONSTRAINT "PK_JOBCLASS" PRIMARY KEY
("JOBCLASS");
ALTER TABLE "LRSSCHEMA"."LOCALJOBSTATUS"
DROP CONSTRAINT "PK_LOCALJOBSTATUS";
ALTER TABLE "LRSSCHEMA"."LOCALJOBSTATUS"
ADD CONSTRAINT "PK_LOCALJOBSTATUS" PRIMARY KEY
("JOBID","BJEENAME");
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21690444