I have successfully masked the Teradata database using batch updated mode (without batch update it will take very long time to mask).
Original Message:
Sent: 09-05-2023 05:08:32 AM
From: Bhupinder Kwatra
Subject: Can we mask TeraData Using Delphix ?
Hi Pankaj,
Below info should help :
1.) Error ([Error 857] [SQLState HY000] Two different data types are being set for parameter 17 (497 & 600):
Cause: This issue is caused by presence of both null & not null values are present in a integer type col. JDBC driver using batch update method set the value using setint if not null value is present and setnull if null value is present.
Workaround: We need to either cast this value to string data type or use NVL function to update null value to some constant value and then resetting those constant value to null post masking.
2.) Error [Error 7451] [SQLState HY000] Invalid timestamp:
Cause: This issue is caused if data source holds value in a format other than the default format of timestamp.
Workaround: We need to either cast this value to string data type or cast the value to the default format.
3.) In Place Masking Issue(Job is getting stucked when it is trying to commit (at 9999 records when Commit Size = 10000 & at 19999 records when Commit Size = 20000):
Cause: Delphix via Teradata JDBC driver trying to select subset of rows in the table with read lock and trying update the same subset of rows in the same table. Teradata don't accept the read lock and write lock same time on same table.
Workaround: On The fly Masking is the possible solution here.
All these workaround involves some manual interventions & effort but that is the only way forward.
Regards,
Bhupinder Kwatra
------------------------------
Bhupinder Kwatra
Senior System Engineer
Delphix Community Members
Original Message:
Sent: 09-04-2023 02:57:34 PM
From: Pankaj Asegaonkar
Subject: Can we mask TeraData Using Delphix ?
Hello Bhupinder,
I have tested masking job with small table and I could see that when there is NULL value preset in table for the column data type INTEGER, DATE then below error message after executing masking job
2023/08/24 10:48:09 - DelphixTableInsert.0 -
2023/08/24 10:48:09 - DelphixTableInsert.0 - Error inserting/updating row
2023/08/24 10:48:09 - DelphixTableInsert.0 - [redact] [redact] [redact] [redact] Two different data types are being set for parameter 15 (497 & 600)
--> Column number 15 have NULL value
The masking job works fine by unchecking the batch update check box, but the masking speed will be very slow 6K/MIN which will take days to mask teradata.
Also, I have tried to update the NULL column with dummy values and ran a masking job with the batch update, it worked fine.
in practice we will have more than 100 tables and we could not find a NULL value in the individual table, could you provide any workaround for this problem?
------------------------------
Pankaj Asegaonkar
Masking/Virtualization Specialist
+91 9689996500
Original Message:
Sent: 08-25-2023 06:38:44 AM
From: Bhupinder Kwatra
Subject: Can we mask TeraData Using Delphix ?
Hi Pankaj,
Pls confirm if the job errored out before the commit ?
Additionally, it seems that below error are caused by constraint violation ( [Error 1338] [SQLState HY000])
I will suggest create a small table and first test the compatibility of the driver.
Regards,
Bhupinder Kwatra
------------------------------
Bhupinder Kwatra
Senior System Engineer
Delphix Community Members
Original Message:
Sent: 08-24-2023 07:35:40 AM
From: Pankaj Asegaonkar
Subject: Can we mask TeraData Using Delphix ?
Hello Bhupinder,
Thanks for providing URL for the JDBC driver..!
I have downloaded and imported it to delphix masking engine.
I have tried to execute the same ON THE FLY masking job that we had, After masking a few records in the table i could see the masking job failed for the below reasons.
Kindly find the below logs and let me know if we have any solution to that
ERROR 1: WHEN LK= EMPTY
2023/08/24 10:18:30 - DelphixTableInsert.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Because of an error, this step can't continue:
2023/08/24 10:18:30 - DelphixTableInsert.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : org.pentaho.di.core.exception.KettleException:
2023/08/24 10:18:30 - DelphixTableInsert.0 -
2023/08/24 10:18:30 - DelphixTableInsert.0 - Error inserting row into table ["EMPL_BCK_TEST_MASK"] with values: [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact]
2023/08/24 10:18:30 - DelphixTableInsert.0 -
2023/08/24 10:18:30 - DelphixTableInsert.0 - Error inserting/updating row
2023/08/24 10:18:30 - DelphixTableInsert.0 - [redact] [redact] [redact] [redact] Two different data types are being set for parameter 15 (497 & 600)
2023/08/24 10:18:30 - DelphixTableInsert.0 -
2023/08/24 10:18:30 - DelphixTableInsert.0 -
2023/08/24 10:18:30 - DelphixTableInsert.0 -
2023/08/24 10:18:30 - DelphixTableInsert.0 - at dmsuite.customFileOutput.plugin.DelphixTableInsertStep.getRedactedLogs(DelphixTableInsertStep.java:40)
2023/08/24 10:18:30 - DelphixTableInsert.0 - at dmsuite.customFileOutput.plugin.DelphixTableInsertStep.logError(DelphixTableInsertStep.java:29)
2023/08/24 10:18:30 - DelphixTableInsert.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:137)
2023/08/24 10:18:30 - DelphixTableInsert.0 - at dmsuite.customFileOutput.plugin.DelphixTableInsertStep.processRow(DelphixTableInsertStep.java:50)
2023/08/24 10:18:30 - DelphixTableInsert.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2023/08/24 10:18:30 - DelphixTableInsert.0 - at java.lang.Thread.run(Thread.java:750)
*********************************************************************************************************************************************************
ERROR 2= WHEN LK= EMPL_ID
023/08/24 10:48:09 - DelphixTableInsert.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Because of an error, this step can't continue:
2023/08/24 10:48:09 - DelphixTableInsert.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : org.pentaho.di.core.exception.KettleException:
2023/08/24 10:48:09 - DelphixTableInsert.0 -
2023/08/24 10:48:09 - DelphixTableInsert.0 - Error inserting row into table ["EMPL_BCK_TEST_MASK"] with values: [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact], [redact]
2023/08/24 10:48:09 - DelphixTableInsert.0 -
2023/08/24 10:48:09 - DelphixTableInsert.0 - Error inserting/updating row
2023/08/24 10:48:09 - DelphixTableInsert.0 - [redact] [redact] [redact] [redact] Two different data types are being set for parameter 15 (497 & 600)
2023/08/24 10:48:09 - DelphixTableInsert.0 -
2023/08/24 10:48:09 - DelphixTableInsert.0 -
2023/08/24 10:48:09 - DelphixTableInsert.0 -
2023/08/24 10:48:09 - DelphixTableInsert.0 - at dmsuite.customFileOutput.plugin.DelphixTableInsertStep.getRedactedLogs(DelphixTableInsertStep.java:40)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at dmsuite.customFileOutput.plugin.DelphixTableInsertStep.logError(DelphixTableInsertStep.java:29)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:137)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at dmsuite.customFileOutput.plugin.DelphixTableInsertStep.processRow(DelphixTableInsertStep.java:50)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at java.lang.Thread.run(Thread.java:750)
2023/08/24 10:48:09 - Select values.0 - Finished processing (I=0, O=0, R=13730, W=13730, U=0, E=0)
2023/08/24 10:48:09 - Get All Lookups Values.0 - Finished processing (I=0, O=0, R=13629, W=13629, U=0, E=0)
2023/08/24 10:48:09 - Merge Join.0 - Finished processing (I=0, O=0, R=7854, W=3926, U=0, E=0)
2023/08/24 10:48:09 - String Cut.0 - Finished processing (I=0, O=0, R=3722, W=3722, U=0, E=0)
2023/08/24 10:48:09 - Add sequence.0 - Finished processing (I=0, O=0, R=13828, W=27656, U=0, E=0)
2023/08/24 10:48:09 - SelectValues_MetaData.0 - Finished processing (I=0, O=0, R=3822, W=3822, U=0, E=0)
2023/08/24 10:48:09 - DelphixTableInsert.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Unexpected batch update error committing the database connection.
2023/08/24 10:48:09 - DelphixTableInsert.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException:
2023/08/24 10:48:09 - DelphixTableInsert.0 -
2023/08/24 10:48:09 - DelphixTableInsert.0 - Error updating batch
2023/08/24 10:48:09 - DelphixTableInsert.0 - [Teradata JDBC Driver] [TeraJDBC 16.20.00.13] [Error 1338] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request. Details of the failure can be found in the exception chain that is accessible with getNextException.
2023/08/24 10:48:09 - DelphixTableInsert.0 -
2023/08/24 10:48:09 - DelphixTableInsert.0 - Next Exception: SQLState( 23000) ErrorCode(-2801)
2023/08/24 10:48:09 - DelphixTableInsert.0 - Next Exception: SQLState( 23000) ErrorCode(-2801)
2023/08/24 10:48:09 - DelphixTableInsert.0 - Next Exception: SQLState( 23000) ErrorCode(-2801)
2023/08/24 10:48:09 - DelphixTableInsert.0 - Next Exception: SQLState( 23000) ErrorCode(-2801)
2023/08/24 10:48:09 - DelphixTableInsert.0 - Next Exception: SQLState( 23000) ErrorCode(-2801)
2023/08/24 10:48:09 - DelphixTableInsert.0 - Next Exception: SQLState( 23000) ErrorCode(-2801)
2023/08/24 10:48:09 - DelphixTableInsert.0 - Next Exception: SQLState( 23000) ErrorCode(-2801)
2023/08/24 10:48:09 - DelphixTableInsert.0 -
2023/08/24 10:48:09 - DelphixTableInsert.0 - at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1449)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1434)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:586)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at dmsuite.customFileOutput.plugin.DelphixTableInsertStep.dispose(DelphixTableInsertStep.java:88)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:96)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at java.lang.Thread.run(Thread.java:750)
2023/08/24 10:48:09 - DelphixTableInsert.0 - Caused by: java.sql.BatchUpdateException: [Teradata JDBC Driver] [TeraJDBC 16.20.00.13] [Error 1338] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request. Details of the failure can be found in the exception chain that is accessible with getNextException.
2023/08/24 10:48:09 - DelphixTableInsert.0 - at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeBatchUpdateException(ErrorFactory.java:149)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeBatchUpdateException(ErrorFactory.java:138)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatchDMLArray(TDPreparedStatement.java:277)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatch(TDPreparedStatement.java:2755)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1421)
2023/08/24 10:48:09 - DelphixTableInsert.0 - ... 4 more
2023/08/24 10:48:09 - DelphixTableInsert.0 - Caused by: java.sql.SQLException: [Teradata Database] [TeraJDBC 16.20.00.13] [Error -2801] [SQLState 23000] Duplicate unique prime key error in A4_TEC_MASKED.EMPL_BCK_TEST_MASK.
2023/08/24 10:48:09 - DelphixTableInsert.0 - at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:302)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:114)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:311)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:200)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:137)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.run(PreparedBatchStatementController.java:58)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:389)
2023/08/24 10:48:09 - DelphixTableInsert.0 - at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatchDMLArray(TDPreparedStatement.java:257)
2023/08/24 10:48:09 - DelphixTableInsert.0 - ... 6 more
2023/08/24 10:48:09 - DelphixTableInsert.0 - Finished processing (I=0, O=0, R=8, W=0, U=0, E=1)
2023/08/24 10:48:09 - KETTLE_MASK_XML_507_EMPL_BCK_TEST_MASK-9db69f38-e6bb-3762-8526-c5626eefaf31_1197 - Transformation detected one or more steps with errors.
------------------------------
Pankaj Asegaonkar
Masking/Virtualization Specialist
+91 9689996500
Original Message:
Sent: 08-24-2023 01:04:54 AM
From: Bhupinder Kwatra
Subject: Can we mask TeraData Using Delphix ?
Hi Pankaj,
As i mentioned earlier, we have masked data successfully on Teradata using JDBC 16 version. Download the driver from https://repo1.maven.org/maven2/com/teradata/jdbc/terajdbc/16.20.00.13/ and give a try.
It seems that current error that you are getting with 20 version is caused by driver incompatibility.
Also, while masking Teradata with Delphix; keep an eye on the cost.
Regards,
Bhupinder Kwatra
------------------------------
Bhupinder Kwatra
Senior System Engineer
Delphix Community Members
Original Message:
Sent: 08-23-2023 06:24:25 AM
From: Pankaj Asegaonkar
Subject: Can we mask TeraData Using Delphix ?
Hello Bhupinder,
While performing masking i am facing "[Teradata JDBC Driver] [TeraJDBC 20.00.00.11] [Error 1536] [SQLState HY000] Invalid connection parameter name URL" (As per Support team)
I have successfully created the connector, able to connect to databases and able to see the tables present from the configured database.
Successfully created and executed Profiling job, its worked fine.
While executing on the fly masking job, the masking job fails.
I have also checked with delphix support team, as per support team it is issue of Teradata JDBC driver.
Could you please help me to provide the same JDBC driver that you have used in Terdata?
------------------------------
Pankaj Asegaonkar
Masking/Virtualization Specialist
+91 9689996500
Original Message:
Sent: 08-21-2023 07:03:40 AM
From: Bhupinder Kwatra
Subject: Can we mask TeraData Using Delphix ?
Hi Pankaj,
Teradata JDBC driver can be downloaded from :
https://downloads.teradata.com/download/connectivity/jdbc-driver
We have performed Teradata masking successfully using 16 version (Don't remember the fully qualified version name).
Also, can you let me know what error you are getting ?
Regards,
Bhupinder Kwatra
------------------------------
Bhupinder Kwatra
Senior System Engineer
Delphix Community Members
Original Message:
Sent: 08-21-2023 06:10:19 AM
From: Pankaj Asegaonkar
Subject: Can we mask TeraData Using Delphix ?
Hello Bhupinder,
Could you please send me Teradata JDBC driver version and download link.
I am using "terajdbc-20.00.00.11.jar" version, while performing masking facing error.
------------------------------
Pankaj Asegaonkar
Masking/Virtualization Specialist
+91 9689996500
Original Message:
Sent: 02-17-2022 01:47:14 PM
From: Bhupinder Kwatra
Subject: Can we mask TeraData Using Delphix ?
Delphix support Teradata masking via JDBC driver. You can configure teradata driver via Delphix Extended connector.(ensure to use correct driver class name and driver need to be uploaded as a zip file and not jar file) Please note only on the fly masking works for teradata and hence you will need source and target db for teradata masking.
With in place masking, you will observe that job is getting stucked while issuing commit due to inherent table locking mechanism of teradata
------------------------------
Bhupinder Kwatra
Senior System Engineer
Delphix Community Members
Original Message:
Sent: 02-15-2022 12:11:38 AM
From: Pankaj Asegaonkar
Subject: Can we mask TeraData Using Delphix ?
Hello Team,
There is new requirement to mask the Teradata, kindly let me know can we mask the Teradata using delphix?
I could not see the inbuilt connector to mask the Teradata.
Kindly let me know how to connect to Teradata using Delphix masking tool
------------------------------
Pankaj Asegaonkar
Delphix Community Members
------------------------------