This documentation supports the 22.1 version of Action Request System.
To view an earlier version, select the version from the Product version menu.

Changes in BMC Helix Innovation Suite to support PostgreSQL database

This topic content is applicable only to SaaS subscribers.


Starting with version 21.02, Service Management applications run on a single environment, BMC Helix Innovation Suite, that combines the capabilities of AR System and BMC Helix Innovation Studio. BMC Helix Innovation Suite supports PostgreSQL which is a shared database among Action Request System, BMC Helix Innovation Studio, and other service management applications. 

Related topic

Advantages of moving to PostgreSQL

In your BMC Helix SaaS environments, data is moved from Microsoft SQL Server to PostgreSQL database for all BMC co-located data centers for operational efficiency and the ability to use vendor-specific functionality. We have not made structural changes to the DB schema, but because of the move to PostgreSQL database, there are changes in the upgrade process that result in some changes to the DB structure.

Back to top

Changes in BMC Helix Innovation Suite to support the move to PostgreSQL database

  • Use of CITEXT datatype for case insensitivity handlingPostgreSQL supports a special datatype called citext for case insensitive matching. If the db case sensitivity is false, the server appends ::citxet to the string columns and to the string literal values in Where clauses for case insensitivity matching.  
    citext does not support limiting the size of the column itself as compared to Microsoft SQL Server or Oracle. The product adds explicit DB constraints in PostgreSQL to limit the size of the columns.
  • Use of CASCADE DELETE for dropping viewsPostgreSQL does not support DROP COLUMN, DROP VIEW, or ALTER COLUMN statements if there are dependent views on top of that column view.
    You must drop the column by using the CASCADE DELETE statement, which deletes the dependent views. Numerous metadata operations in AR System require that a column be dropped or altered or that a view be dropped. Because of this strict restriction, the AR Server places the dependent view definition in memory and then issues the drop column or view command with the cascade option to delete the column or views. After the view is re-created, the stored dependent views are also re-created.
  • Use of savepoints in transactions—AR System uses the PostgreSQL savepoint functionality to ignore a few errors in between transactions for cases, such as creating user friendly views, and so on. If it fails to create the view, ignore the failure and make the transaction successful. Without savepoints, even though errors are ignored, PostgreSQL fails the transaction and rolls back the transaction.
  • Use of limit and offset to fetch records in chunksMicrosoft SQL and Oracle have a certain logic to fetch records in chunks for getListEntry, getListValuesFromMultiSchemaEntries APIs. For PostgreSQL, instead of applying custom logic to do the chunking, we use a limit and offset clause provided by PostgreSQL to implement chunking.
  • Escape LIKE patterns—For the LIKE operator to match patterns that contain special characters, such as  % or [ as literals, we need to escape the pattern by using  backslash (\).
  • Update SQL changesPostgreSQL does not allow prefixing a table name to a column name that is getting set in an update command. 
    Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid. Use UPDATE tab set col = 1 instead.
  • String concatenationUse || operator for string concatenation. 

Back to top

Limiting integrations and customizations at the database level

In addition to PostgreSQL, BMC Helix Innovation Suite platform and applications support other databases, such as Oracle and SQL Server. BMC provides you with the option of moving to other databases based on factors, such as data centers and the public cloud on which the applications run. However, we strongly recommend moving to PostgreSQL database. BMC Helix Innovation Suite platform and applications will continue to function normally after the upgrade without additional configurations as long you follow best practices for external integrations.

BMC plans to restrict your database access in the cloud. This access restriction will happen over a period of time.

Certain types of workflow actions will be restricted, such as direct SQL actions.  Access to databases that use ODBC will be deprecated and no custom DB constructs will be allowed for triggers, indexes, and stored procedures.

We recommend that you start planning and transitioning your integrations and customizations to any one of the following supported integrations:

  • APIs (REST API, SOAP Web Services, or RPC)
  • Workflow actions
  • Integration templates available through BMC Helix iPaaS
  • Integration connectors

Back to top

Changes to DB structure due to changes in the upgrade process

Starting with version 21.x, during the upgrade process, a fresh installation of platform and applications is performed, and then your customization and data are migrated from the previous system to your new system. If your customizations involve direct SQL Sever, the upgrade process might result in the following important changes:

  • The T table names will change. If the integrations assumed a certain physical table name for a form, those names will no longer be valid. For example, if a Helpdesk form name is T2100, after the upgrade, it might be T2900.  
  • Entry Ids or values of field ID1 (Request ID field) might change for certain records depending on whether a conflict occurs during upgrade.
  • Individual customizations that worked in the past might not work in the same way after the upgrade. In most cases, BMC will facilitate the required fixes, but some customer involvement might be needed.

Back to top

Differences in behavior of applications when using PostgreSQL versus Microsoft SQL Server

This section describes some changes in the behavior of your applications, primarily due to DB collation differences, when you move the database engine from Microsoft SQL Server to PostgreSQL database.

Sorting data containing special characters

If the data being sorted contains special characters, such as hyphens, brackets, and so on, such data is sorted differently in different databases.

For example, the ticket summary contains the following data:

Summary

Laptop issues

[QA] email issues

QA environment upgrade

The following table describes the differences in behavior of BMC Helix IT Service Management applications when you are using PostgreSQL and MS SQL Server, the limitations, and the workaround:

Using PostgreSQL Using Microsoft SQL Server

Limitation or customer experience after upgrading to version 21.x and later

Resolution/Workaround

In PostgreSQL with default settings, sorting on the Summary field in Ascending order sorts and displays the data as follows:

Summary

Laptop issues

[QA] email issues

QA environment upgrade

In Microsoft SQL Server with default settings, sorting on the Summary field in Ascending order sorts and displays the data as follows:

Summary

[QA] email issues

Laptop issues

QA environment upgrade

The data can be sorted differently on the UI or workflow after upgrading to version 21.x and later.

If data sorting is not acceptable for business reasons, use one of the following options to resolve the issue:

  • Fix the form properties or workflow that might be impacted because of a change in the sorting behavior.
  • Identify specific form fields and raise a request with Support team to change collation at the database column level.

Accent sensitive searches

Search results based on accented characters works differently in Microsoft SQL Server and PostgreSQL.  If searches use qualifications (WHERE clause in database) that rely on accent characters, the search results might be different in Microsoft SQL and PostgreSQL.

The following table describes the differences in behavior of BMC Helix IT Service Management applications when you are using PostgreSQL and MS SQL Server, the limitations, and the workaround:

Using PostgreSQL Using Microsoft SQL Server

Limitation or customer experience after upgrading to version 21.x and later

Resolution/Workaround

PostgreSQL database does not support accent insensitive collation searches.

Therefore, similar sounding words, such as “èvan” and “evan” are considered as different strings. 

Best practice: We recommend that you use FTS indexes so that accent insensitive setting of databases are not used.

Microsoft SQL Server supports accent insensitive collation in searches.

Therefore, similar sounding words, such as “èvan” and “evan” are treated as the same string. 


Accent sensitive searches will not produce expected results as explained in the PostgreSQL example.

If it is important to treat accented strings in the same manner as non-accented strings in searches, enable FTS indexes on those fields. FTS indexes have a configuration to enable accent insensitive searches.


Handling ASCII NULL characters

An example of handling ASCII NULL characters is as follows:

You run a UDM job to sync LDAP user data into BMC Helix system. One of the fields in LDAP user records is binary, which contains an ASCII NUL character. This LDAP binary field is mapped to one of the character fields on the CTM:LoadPeople form.

The following table describes the differences in behavior of BMC Helix IT Service Management applications when using PostgreSQL and MS SQL Server, the limitations, and the workaround:

Using PostgreSQL Using Microsoft SQL Server

Limitation or customer experience after upgrading to version 21.x and later

Resolution/Workaround
ASCII NULL characters are represented as \0.
PostgreSQL does not allow null byte ('\0') in a string on char/text/varchar fields. If you try to store a string containing null bytes, you receive an error.
Microsoft SQL Server and other databases allow null byte ('\0') in a string on char/text/varchar fields.

ASCII NULL characters will not get loaded from external data entry.

Any existing data containing such characters will be lost during migration.

Such characters cannot be loaded in the target PostgreSQL database. Modify the calling program/workflow to stop sending this character.

If there was ASCII NULL character pre-upgrade, remove it from the source and then perform migration.

Attachment size difference

When you upload files to the attachment fields in a server, the maximum file size limit is different for Microsoft SQL Server and PostgreSQL databases.

The following table describes the differences in behavior of BMC Helix IT Service Management applications when using PostgreSQL and MS SQL Server, the limitations, and the workaround:

Using PostgreSQL Using Microsoft SQL Server

Limitation or customer experience after upgrading to version 21.x and later

Resolution/Workaround
In a PostgreSQL database, the limit for storage in a row is 1 GB. Typically, the file size should be less than 1 GB.In a Microsoft SQL Server database, the limit for storage of files is 2 GB.

Attachments or data with cumulative size of 1 GB will not be accepted in the system.

Attachments that are larger than 1 GB at source before migration will be lost in migration.

Do not upload attachments that are larger than 1 GB.

BMC will provide a list of entries that might contain attachments that are larger than 1 GB. You must download such attachments from the source system prior to migration and upload them elsewhere, such as internal FTP site or OneDrive, and provide links in the ticket for end users.

Searching for backslash (\) character

Searching for backslash (\) character in Microsoft SQL Server does not return correct results unless it is escaped, because \ is a default escape character in PostgreSQL.

The following table describes the differences in behavior of BMC Helix IT Service Management applications when using PostgreSQL and SQL Server, the limitations, and the workaround:

Using PostgreSQL Using Microsoft SQL Server

Limitation or customer experience after upgrading to version 21.x and later

Resolution/Workaround

If the data contains a single backslash character, for example (onbmc\user), and it needs to be used in a WHERE clause of a query, it has to be escaped in PostgreSQL.

Example:

SELECT name FROM table WHERE login = ‘onbmc\\user’ will return the result as expected.

If the data contains single backslash character, for example, (onbmc\user), and it needs to be used in a WHERE clause of a query, it can be used as it is in Microsoft SQL Server.

Example:

SELECT name FROM table WHERE login = ‘onbmc\user’  will return the result as expected.


If there are custom workflows that use direct SQL and make use of the backslash character, it will not return the expected results after migration.

Edit the custom workflows to make use of the correct qualification based on database type. Use $DATABASE$ keyword in the workflow qualification to ensure foolproof behavior that is independent of the database.

Creating or modifying data on a View form fails in PostgreSQL

Creating or modifying data on a View form fails if you use a View form that fetches data from a Database View. This issue occurs in PostgreSQL database when the Database view is not automatically updatable. See the information on Updatable Views in the PostgreSQL online documentation. Open link .

The following table describes the differences in behavior of BMC Helix IT Service Management applications when using PostgreSQL and SQL Server, the limitations, and the workaround:

Using PostgreSQL Using Microsoft SQL Server

Limitation or customer experience after upgrading to version 21.x and later

Resolution/Workaround

Most Database views are not updatable by default. Views with joins or those using aggregate functions are not updatable in PostgreSQL.


Many views may be updatable by default because SQL Server Database Engine automatically finds and maps the update to underlying tables.


Direct SQLs from workflows that were directly updating the database views might fail due to the PostgreSQL limitation.    

Change the logic in the workflow to use better constructs, such as Set Field or Push Field action, wherever possible.
If Direct SQL is the only option, change the SQL to make sure it works for all supported database types. For example, use a T table or simple view updates instead of join view updates.
Any use of direct SQL in a workflow must be compliant with all supported database types so that any change in the database does not affect the workflow.

Best practice to improve overall DB performance

During the migration from Microsoft SQL Server to PostgreSQL, the index definitions in the BMC Helix Innovation Suite and applications that performed well with the same volume of data are not utilized by the query optimizer in PostgreSQL. To address this issue, for any custom index or integrations, you must create new index definitions during the UAT or go-live activities. However, out-of-the-box applications and their index definitions will perform correctly.

Back to top

Differences in syntax of database functions in MS SQL Server and PostgreSQL

The following table shows syntax differences between MS SQL and PostgreSQL related to certain computational operations. These operations are datatype specific and are accomplished by DB specific functions, which are different for each database.  These functions are used in Smart Reports, ODBC integrations, custom database views, and so on.

Database functions

Supported in MS SQL

Supported in PostgreSQL

CAST

Yes

Yes

  • to_char
  • to_number
  • to_date

For more information about these functions, see the PostgreSQL documentation.

CONVERT

Yes

Yes

  • to_char
  • to_number
  • to_date

For more information about these functions, see the PostgreSQL documentation.

ROW_NUMBER

Yes

No

RANK

Yes

No

DENSE_RANK

Yes

No

NTILE

Yes

No

CHARINDEX

Yes

Yes

  • strpos

For more information about this function, see the PostgreSQL documentation.

CONCAT

Yes

Yes

DATALENGTH

Yes

Yes

  • LEN

For more information about this function, see the PostgreSQL documentation.

LEFT

Yes

Yes

LEN

Yes

Yes

LOWER

Yes

Yes

LTRIM

Yes

Yes

REPLACE

Yes

Yes

REPLICATE

Yes

Yes

  • repeat

For more information about this function, see the PostgreSQL documentation.

REVERSE

Yes

Yes

RIGHT

Yes

Yes

RTRIM

Yes

Yes

SPACE

Yes

No

STR

Yes

  • to_char
  • to_number
  • to_date

For more information about these functions, see the PostgreSQL documentation.

SUBSTRING

Yes

  • substr

For more information about this function, see the PostgreSQL documentation.

TRANSLATE

Yes

Yes

TRIM

Yes

Yes

UPPER

Yes

Yes

DATEADD

Yes

Yes

DATEDIFF

Yes

Yes

DATENAME

Yes

Yes

DATEPART

Yes

Yes

DAY

Yes

Yes

MONTH

Yes

Yes

YEAR

Yes

Yes

QUARTER

Yes

Yes

STUFF

Yes

No

PATINDEX

Yes

Yes

  • strpos

For more information about this function, see the PostgreSQL documentation.

LEAD

Yes

Yes

LAG

Yes

Yes

INSTR

Yes

Yes

  • strpos

For more information about this function, see the PostgreSQL documentation.

LPAD

Yes

No

MONTHS_BETWEEN

Yes

Yes

  • AGE

For more information about this function, see the PostgreSQL documentation.

ADD_MONTHS

Yes

No

LAST_DAY

Yes

No

EXTRACT

Yes

No

NEXT_DAY

Yes

No

TO_NUMBER

Yes

Yes

Concat_ws

Yes

Yes

COALESCE

Yes

Yes

EOMONTH

Yes

Yes

CEILING

Yes

Yes

FLOOR

Yes

Yes

Back to top



Was this page helpful? Yes No Submitting... Thank you

Comments