DE reports are not running and tracelog shows "Invalid column name 'DOCUMENT

Products

DSERIES- SERVER CA Workload Automation DE - System Agent (dSeries)

Issue/Introduction

When running reports from Services perspective in CA WA Desktop Client, the user may lose connection and see the following error in the Console:

[Sun Jan 14 02:14:09 EDT 2017] [CAWPRD] - Server restart has been initiated

The DE server tracelog and errors.txt may show these kinds of errors related to column of table.

20180114 02:14:09.562 [relationaldatabase] [ERROR] RMI TCP Connection(22181)-10.232.168.150: [2018-01-14_02:14:09.562] SQL Exception for query: SELECT ID, REPORT_DESIGN_NAME, FORECAST_SPEC_NAME, EVENT_NAME, EXECUTION_USER, START_TIME, END_TIME, SUCCESS, FAILED_REASON, DOCUMENT_SIZE FROM ESP_SS_REPORT_RESULT WHERE FORECAST_SPEC_NAME IS NULL AND (EXECUTION_USER=? OR ? IN (SELECT USER_ID FROM ESP_SS_REPORT_RESULT_READER WHERE ID=REPORT_RESULT_ID)); the exception is: Invalid column name 'DOCUMENT_SIZE'.

The problem is that 'DOCUMENT_SIZE' field is missing in ESP_SS_REPORT_RESULT table.

Cause

When DE R12 SP2 patch is applied, the patch may encounter an error. The DB user may have had permissions changed and is not able to add new tables and columns. If Windows authentication is used for MS-SQL database, then the user that started the patch upgrade may not be the same user that starts the DE or may not have proper privileges.

The patch upgrade will show errors related to DB updates.

Upgrade log shows error message:
20170813 08:21:27.676 INFO com.ca.wa.de.migrateruntimedata.DBConnector - Database is using Windows integrated authentication
20170813 08:21:28.643 INFO com.ca.wa.de.migrateruntimedata.DBConnector - Database connection successfully created.
20170813 08:21:28.659 INFO com.ca.wa.de.migratestaticdata.UpdateStaticData - dbType is: Microsoft SQL Server
20170813 08:21:29.501 ERROR com.ca.wa.de.migratestaticdata.DatabaseUtils - Failed sql query: SELECT VERSION from ESP_VERSION
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'ESP_VERSION'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:297)
at com.ca.wa.de.migratestaticdata.DatabaseUtils.executeSelectStatement(DatabaseUtils.java:68)
at com.ca.wa.de.migratestaticdata.UpdateStaticData.executeSelectStatement(UpdateStaticData.java:170)
at com.ca.wa.de.migratestaticdata.UpdateStaticData.getSourceVersion(UpdateStaticData.java:165)
at com.ca.wa.de.migratestaticdata.UpdateStaticData.main(UpdateStaticData.java:78)

Environment

CA Workload Automation DE R12MS SQL with Windows authentication

Resolution

The DE R12 SP2 upgrade patch has utilities/scripts that will recreate the columns and convert the data. The patch is (should have been) unpacked in DE install directory. The utilities are located in <DE_install_dir>/upgrades/bin. The following scripts will need to be re-executed:

convertstaticdata.bat /.sh
convertruntimedata.bat /.sh

Before running the scripts, perform the following steps

1. Shut down DE server (if HA, shutdown both standby and primary)

2. Backup the database.

3. Navigate to <DE_install_dir>/upgrades/bin

4. Run the convertstaticdata.bat and convertstaticdata.bat (in Unix/Linux: convertstaticdata.sh and convertstaticdata.sh)

5. Check if the new column 'DOCUMENT_SIZE' exists in ESP_SS_REPORT_RESULT table.

6. Start DE and run a report from Services -> Reports