Masking Engine and Informix Database

  • 0
  • 1
  • Idea
  • Updated 1 month ago
  • (Edited)

Hi Everyone,


 

Probably all of you who have met with the masking of the Informix database have learned that Masking Engine qualifies object names in double quotes - ". 

When you try to mask data in Informix, this problem is manifested by the following message:

2018/10/15 14:29:53 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-29 13.29.40 by buildguy) : Unexpected error
2018/10/15 14:29:53 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-29 13.29.40 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2018/10/15 14:29:53 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-29 13.29.40 by buildguy) : An error occurred executing SQL: 
2018/10/15 14:29:53 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-29 13.29.40 by buildguy) : SELECT "company" , "dept" , "email" , "first_name" , "last_name" , "phone" , "id" FROM dbo."employees"
2018/10/15 14:29:53 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-29 13.29.40 by buildguy) : A syntax error has occurred.

...


Masking Engine add double quotes characters to handle any space or special characters.

The problem is in the Informix instance, which by default does not support double quotes on table, column, or where clause. The solution for the database is very simple. The environment variable DELIMIDENT corresponds to the ability to accept commands in doulble quotes in Informix. Thanks to this parameter, the Infromix database can be shutdown before the masking process, add the variable DELIMIDENT=y to the environment and run the Informix database with the new setting. From that moment, Informix accepts double quotes and the masking process in Delphix Masking works just like any database.


See IBM documentation:

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqlr.doc/ids_sqr_233.htm


Testing environment:

- Informix 12.10 on Linux CentOS 6.6

- Delphix Masking Engine 5.2.6

 

Also you need to ask Delphix Support for installing JDBC Informix Driver .jar file on your Delphix Masking Engine.


Regards

Marcin


Photo of Marcin

Marcin

  • 616 Points 500 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of Marcin

Marcin

  • 616 Points 500 badge 2x thumb
HI Community,

We can add support for this method on:
- Infromix version 11.70
- AIX version 7.1
 
Another Informix hint: 
You need to be aware of Informix logical logs size, it forces us to set COMMIT SIZE in JOB definition to 5000 rows, or even 1000 rows on different Informix databases.

Regards
Marcin