Masking Engine and Informix Database

By Marcin Kwasninski posted 10-15-2018 02:16:32 PM

  

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.
I have removed this reference, as this is only available through partner-led support. Please contact our partners for this type of procedure. -- @Michael Torok

Regards

Marcin



#Masking
2 comments
13 views

Permalink

Comments

01-31-2020 10:08:46 AM

Hi @Marcin Kwasninski, I needed to edit your blog (you can see that I struck through one of your sentences). That process is led by partners only, at this time. So a customer/prospect looking to engage in this process needs to work through a partner.
Sorry for any misunderstanding.

Michael​

10-26-2018 08:28:48 AM

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