Highlighted
Outstanding Contributor.
Outstanding Contributor.
93 views

(SM) Support Tip: Convert IMAGE type field into TEXT field for reporting

There are several fields in SM that are image datatype that different businesses will require to report from them. Using a report tool that connects to the database directly this data is not accessible however it's possible to map the data out the of OOB field into a separate table following these steps:

1) Backup db because there may be error when changing dbdict
2) Don't change dbdict at the peak usage of SM system
3) Change dbdict like following:

Note: This example is for table audit

3.1) Log on to your Service Manager server as a System Administrator.
3.2) Click Tailoring > Database Dictionary.
3.3) Type audit in the File Name field, and then click Search.
3.4) Click the SQL Tables tab, and then add a row as follows:
Alias       Name            Type
a1          AUDITA1       <Specify this value as appropriate>

Note: use a2 or a3 or aX if other “A” tables are in use already.

3.5) Click the Fields tab, and make following changes:
Name: recorded.changes
Type: array
SQL Name: <empty>
SQL Type: <empty>
SQL Table: a1
SQL RC: False


Name: recorded.changes
Type: structure
SQL Name: <empty>
SQL Type: <empty>
SQL Table: <empty>
SQL RC: False

Name: filedname
Type: character
SQL Name: FIELD_NAME
SQL Type: TEXT
SQL Table: a1
SQL RC: False

Name: old.scalar
Type: character
SQL Name: OLD_SCALAR
SQL Type: TEXT
SQL Table: a1
SQL RC: False

Name: new.scalar
Type: character
SQL Name: NEW_SCALAR
SQL Type: TEXT
SQL Table: a1
SQL RC: False

Name: old.array
Type: array
SQL Name: <empty>
SQL Type: <empty>
SQL Table: <empty>
SQL RC: False

Name: old.array
Type: character
SQL Name: OLD_ARRAY
SQL Type: TEXT
SQL Table: a1
SQL RC: False

Name: new.array
Type: array
SQL Name: <empty>
SQL Type: <empty>
SQL Table: <empty>
SQL RC: False

Name: new.array
Type: character
SQL Name: NEW_ARRAY
SQL Type: TEXT
SQL Table: a1
SQL RC: False

4) Click save twice to save changes.

This process will take some time depending on the amount of data.

Once finished new data contents the data and reports can be created using new table.

SM Support Engineer

Thank you for using the Micro Focus Community. If you find that this or any post resolves your issue, please be sure to mark it as an "accept as solution".
Labels (1)
0 Likes
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.