Preserving FME Server Job History with Upgrade

Note: the FME Server Repository schema can change between major and minor releases of FME Server (i.e. 2014 to 2015.1 to 2015.2).

We do not support the migration of the fme_job_history table. Although, in some instances we have been asked how to do this and we have provided some basic instructions here.

Have your DBA perform the following:

• Check differences between the old and new schema of the fme_job_history table. Correct any differences necessary to load the data from the older schema ensuring the final fme_job_history table is unchanged from its delivered schema.
• Load the old job history into the new schema. FME Desktop can be used to do this... using the Database Reader applicable to the Database in use.
• Determine the fme_jobs_job_id_seq value and reset it to the highest job_id found in the fme_job_history table.

Updating fme_jobs_job_id_seq: Once the new FME Server Repository database has all the old job history in fme_job_history. To change the sequence it will depend on the database. For PostgreSQL / Express Install, you'd need to update the sequence: fme_jobs_job_id_seq

PostgresSQL

By Default we deliver FME Server Repository on top of Postgres SQL database and the sequence name is called fme_jobs_job_id_seq.
Here is an example of how to update the next sequence value. ALTER SEQUENCE fme_jobs_job_id_seq RESTART WITH <next FME job id>; So when you migrated your old job history to the new schema and lets say for example you created 100 fme_job_history records, your "next FME job id" should be 101 otherwise you will get job submission errors.

Oracle

For Oracle, similar to PostgresSQL the fme_jobs_sequence needs to be adjusted to 1+max value for job_id in the job history table.

SQL Server

For SQL Server the job_id of the fme_jobs table is an IDENTITY field and this drives the job_id that would eventually be inserted in the Job History table. So adjust this IDENTITY value to 1+max value (job_id) in fme_job_history table.

• Test that FME Server functions fine... view the job history table in the Web Interface and ensure jobs can be run and the history is archived as expected into the fme_job_history table. Ensure the job_id is as expected.