How To Check The Consistency Of Your Workload Scheduler Database Schema

The schema of the Workload Scheduler is only changed by the product's installation and update but sometimes can be happen that automatic scripts or manually changes are voluntarily or involuntarily applied to the database.

This situation could reduce the Workload Scheduler performances and data consistency if, for example, an index is dropped or a foreign key is removed. If you have installed the Workload Scheduler on a DB2 or Oracle RDBMS, you can easily check the status of your database schema.

Since the Workload Scheduler 9.3 FP2 version, a new tool is distributed when you install or upgrade the product.

This tool can automatically and dynamically verify if the database schema of your Workload Scheduler is the expected one or if something is accidentally modified or deleted.

This tool produces a SQL file with all the unexpected changes. So, for instance, if the schema lacks an index, into the SQL file you will find the statement which allows you to recreate it, or, if a default value of a column is not the expected one, into the SQL file you will find the statement that alters the column to go back to the correct default value and so on.

How To Check the Schema

Into the Workload Scheduler installation, in the <TWS_HOME>/TWS/dblighttool directory, you find two scripts and two properties files. 
​
​The two scripts are:

• launchUpgradeIWSDB.bat, for Windows platforms
• launchUpgradeIWSDB.sh, for Linux/UNIX operating systems

​
and the two properties files are:

• upgradeDB2IWSDB.properties, for Workload Scheduler on DB2 RDBMS
• upgradeOracleIWSDB.properties, for Workload Scheduler on Oracle RDBMS

​

​The steps you must run to check the Workload Scheduler schema are:
 
1.      Customize the upgrade<RDBMS>IWSDB.properties file suitable for your situation, providing the information about your database connection:

• DB_HOST_NAME
• DB_NAME
• DB_PORT
• DB_ADMIN_USER (only for DB2)
• DB_ADMIN_PASSWORD (only for DB2)
• DB_USER
• DB_PASSWORD (only for Oracle)
• IWS_TS_NAME, IWS_LOG_TS_NAME, IWS_PLAN_TS_NAME

 
Workload Scheduler information:

• COMPONENT_TYPE
• HOST_NAME
• WAS_SEC_PORT

 
and the property which specify if the tool must apply or not the SQL dynamically generated statements:

• UPGRADE_DB

In the following screenshot, you can see the property file for DB2 connections.
​

​Last property, UPGRADE_DB, is the one which allows you to check your database without to make any changes on it. If you set it to FALSE, you receive the schema status without impact it.

​2.   Launch the launchUpgradeIWSDB.bat (or .sh) script to get the following usage:

3.   Customize the script’s arguments and launch it again.

4.      Open the file customSQl.sql file, just created into the tool directory, to verify if the schema must be modified.
 
a.      If you find as first statements the DROP and CREATE of a VIEW, it means that you have the Workload Scheduler database schema to the latest version.
b.     If you find as first statement any other Data Definition Language (DDL), it means that the Workload Scheduler database schema is not aligned with the release version and must be updated. 

5.      If you are in the case 4.b and must update the database schema, you can apply one the following steps:

​case 4.b ​

Conclusion

You can verify if your Workload Scheduler database schema has not changed or modified.
Simply running the dblighttool, you can check if some tables, columns, indexes, constraints and so on, have been deleted or modified. To  avoid some degradation of the Workload Scheduler performance and consistency only run a simple script. 

If you want to talk more about new dblighttool and IBM Workload Scheduler in general, leave a comment below. See you next time!
​

    Eliana Cerasaro
​    Senior Software Engineer 
​    Workload Automation at HCL
    Shoot me an email: eliana.cerasaro@hcl.com

---

Eliana Cerasaro has worked in the Workload Automation area since 2006. In 2016, she moved from IBM to HCL Technologies and is currently part of the distributed development team of Workload Scheduler as a Senior Software Engineer. She specializes in design and development of backend applications and databases.