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

Document ID : KB000007923
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

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.

Environment:
CA Workload Automation DE R12MS SQL with Windows authentication
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)

 

 

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