WORKLOAD AUTOMATION COMMUNITY
  • Home
  • Blogs
  • Forum
  • Resources
  • Events
    • IWA 9.5 Roadshows
  • About
  • Contact
  • What's new

How To Check The Consistency Of Your Workload Scheduler Database Schema

10/26/2017

2 Comments

 
Picture
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.
​
Picture
​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!
​
Picture
    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.
2 Comments
Nigel Jowett
11/10/2017 06:03:28 am

We have tried the ./launchUpgradeIWSDB.sh command but it fails....

START: Fri Nov 10 10:11:27 GMT 2017
Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc11 in java.library.path


this is a 32bit version

however we do have

libocijdbc11.so IA64 module

Kind regards Nige Jowett

Reply
Eliana Cerasaro
11/10/2017 01:39:00 pm

Hi Nigel,
this problem happens when client and server libraries don't have same bit level.
Starting from Workload Scheduler 9.4, to avoid this issue, the tool uses the JDBC driver type 4 which does not use native ocijdbc libraries.
If you are using previous version of Workload Scheduler, which uses JDBC driver type 2, the bit level must be aligned, otherwise Oracle client and server cannot communicate.

Hope this can help you.
Best regards
Eliana



Reply

Your comment will be posted after it is approved.


Leave a Reply.

    Archives

    March 2023
    February 2023
    January 2023
    December 2022
    September 2022
    August 2022
    July 2022
    June 2022
    May 2022
    April 2022
    March 2022
    February 2022
    January 2022
    December 2021
    October 2021
    September 2021
    August 2021
    July 2021
    June 2021
    May 2021
    April 2021
    March 2021
    February 2021
    January 2021
    December 2020
    November 2020
    October 2020
    September 2020
    August 2020
    July 2020
    June 2020
    May 2020
    April 2020
    March 2020
    January 2020
    December 2019
    November 2019
    October 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    February 2018
    January 2018
    December 2017
    November 2017
    October 2017
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017

    Categories

    All
    Analytics
    Azure
    Business Applications
    Cloud
    Data Storage
    DevOps
    Monitoring & Reporting

    RSS Feed

www.hcltechsw.com
About HCL Software 
HCL Software is a division of HCL Technologies (HCL) that operates its primary software business. It develops, markets, sells, and supports over 20 product families in the areas of DevSecOps, Automation, Digital Solutions, Data Management, Marketing and Commerce, and Mainframes. HCL Software has offices and labs around the world to serve thousands of customers. Its mission is to drive ultimate customer success with their IT investments through relentless innovation of its products. For more information, To know more  please visit www.hcltechsw.com.  Copyright © 2019 HCL Technologies Limited
  • Home
  • Blogs
  • Forum
  • Resources
  • Events
    • IWA 9.5 Roadshows
  • About
  • Contact
  • What's new