Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..
171 views

Exceptions during upgrade from SM 9.34P2 to 9..41P2,, svc.options from VARCHAR2 to BLOB

Hi everybody

We are upgrading one of our test environments from SM 9..34P2 to Sm 9..41P2. After the Application Upgrade process the exception log has several errors like this:

2016-02-01 15:33:28 No hay soporte del cambio de tipo SQL para incidents en svc.options dbdict de VARCHAR2(4000) a BLOB

Rougly translated:

2016-02-01 15:33:28 There is no support for the convertion of the SQL type for incidents in svcoptions dbdict from VARCHAR2(4000) to BLOB

So we need to know:

  1. Is svc.options stored as a BLOB (or CLOB) in SM 9.41? We have an SM that was first deployed with SM 9.20 and subsequently upgraded. Maybe at some point this changed but the upgrade process can't do this.
  2. If yes we need to know how to convert the field svc.options from VARCHAR2(4000) that we have now to BLOB or CLOB. We have oracle 11.2.0.

 

Thanks

 

SM version information:

Application version:  9..34.0032

RTE Version: 9.34.2003

Compilation Date: 31/08/2012 08:00:00

DB: Oracle 11.2.0

0 Likes
6 Replies
Highlighted
Super Contributor.
Super Contributor.

Re: Exceptions during upgrade from SM 9.34P2 to 9..41P2,, svc.options from VARCHAR2 to BLOB

Hello,

I have found that in the documentation :

Error message in Oracle: 
dbdict:incidents, field:svc.options, SQL type is
VARCHAR2(90) -- expected to be:BLOB

To fix these issues, change the SQL type to
RAW(255) or BLOB by using the Dbdict utility.
Additionally, you will need to set the “SQL RC”
to true to allow the field to store RAD
expressions. Note that the stored value of the
field in the database is encoded by Service
Manager.

Page 39 of Applications Upgrade Guide (from HP Service Manager 9.3x with Process Designer Content Pack 9.30.3 installed)

Have you tried to modify the data type using the Dbdict utility?

LPP

Global Support Delivery Software Support Expert

If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.
If you are satisfied with anyone’s response please remember to give them a KUDOS by clicking on the STAR at the bottom left of the post and show your appreciation.
0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Exceptions during upgrade from SM 9.34P2 to 9..41P2,, svc.options from VARCHAR2 to BLOB

hello,

 

I would suggest that you make a  note of this field change from Varchar2(4000) to blob but don't make this change till the upgrade is finished. The dbdict changes can be done after the upgrade. Check if you have field length more than 4000 characters in the svc.option after the upgrade and if the answer is yes, then you can change the datatype from Varchar to Blob else ignore this exception.

 

Hope this helps,

Rama

0 Likes
Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..

Re: Exceptions during upgrade from SM 9.34P2 to 9..41P2,, svc.options from VARCHAR2 to BLOB

Hi PLP, thanks, but this doen's work.

If I open the dbdict of incidents, I can see that svc.options is defined as "character" and SQL data type "VARCHAR2(4000)". If I try to change that BLOB and save, it fails throwing an error and you end with a damaged incidents table, not a nice outocome mind you. Besides, even if it worked what would happen with the data that is already stored? Would be wiped?

We tried on a test environment but from the dbdict there seemed to be no way of doing it.

Thanks

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Exceptions during upgrade from SM 9.34P2 to 9..41P2,, svc.options from VARCHAR2 to BLOB

Hi Stanum,

Normally you can keep VARCHAR2(4000) except you think it is not enough to store future incoming data.

Even if it worked what would happen with the data that is already stored? Would be wiped?

>> Not wiped, the data is there, but cannot be recognized by human being after converted to blob field.

Pitt,

SM RnD Upgrade Factory

Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..

Re: Exceptions during upgrade from SM 9.34P2 to 9..41P2,, svc.options from VARCHAR2 to BLOB

VARCHAR2(4000) (which is the maximum size for a VARCHAR2 in Oracle) is not always enough. The problem is if open the DBDICT of incidents and change svc.options from "Character", "VARCHAR2(4000)" to "Character", "BLOB" and click Save Oracle throws an error, the SQL Data Type gets not saved and the dbdict is inconsistent from then on for incidents, rendering the table mostly broken. Maybe data types can be changed with MS SQL Server backends, but not with Oracle.

To work around this we tried.

  1. Create a new field in incidents called svc.options2 "Array of characters" (which is created as a BLOB),
  2. Using a script we copied the data from svc.options to svc.options2 (since with SQL it proved difficult to get it done),
  3. Then renamed svc.options to svc.options.old and svc.options2 to svc.options (and the corresponding SQL field names).

After that Catalog Requests failed to save any data to svc.options, our guess is that the RADs that handle this on SM 9.3X didn't know how to do that. So we reverted the changes and left it as it was.

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Exceptions during upgrade from SM 9.34P2 to 9..41P2,, svc.options from VARCHAR2 to BLOB

Hi Stanum,

In out-of-box system, the svc.options field in incidents dbdict is not an array field, why your workaround created an new array of blob field to copy the data from svc.options? How about just a new single blob field?

About the script, I guess you meant the SM javascript code, right? It is better than SQL since SM javascript code can recognize the content in SM blob field.

About the failure when saving catalog request, can you add rtm:3 and debugdbquery:999 in sm.ini, relogin client, clear sm.log and redo the steps to get the failure again? After that, we can open sm.log to search "RTE E" or "RAD E" key word, and normally we can know which code (RAD application or SM javascript or Process) fails.

Anyway, I suggest you openning a support case to track the issue, since I am afraid you need to provide more debug log and the dbdict for support to review and check. If there is production defect, we will create one QCCR to track too, thx.

Pitt

SM RnD Upgrade Factory

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.