Delphix Products

 View Only
Expand all | Collapse all

Can we mask TeraData Using Delphix ?

  • 1.  Can we mask TeraData Using Delphix ?

    Posted 02-15-2022 12:12:00 AM
    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
    ------------------------------


  • 2.  RE: Can we mask TeraData Using Delphix ?

    Posted 02-15-2022 12:56:00 PM
    Hi Pankaj,
    Good to hear from you.
    The answer to your question is further expanded on here (https://support.delphix.com/Delphix_Masking_Engine/Delphix_Extensibility_Support_(KBA5571)#Extensible_Masking_Connectors), but the short answer is that the underlying framework and API used to support JDBC connectors is supported. There is an Oracle Teradata JDBC driver that you can find more information about here: https://docs.oracle.com/cd/E21454_01/html/821-2596/agqky.html.

    What is not supported are source-specific issues that arise while attempting to mask data using a connector (like the one above, available from Oracle).

    I should add that any Type 4 compliant JDBC driver can be used, and Delphix supports the internal logistics. Delphix does not support issues with the drivers.

    Hope that helps,
    Michael

    ------------------------------
    Michael Torok
    Director of Knowledge and Community Management
    Delphix
    ------------------------------



  • 3.  RE: Can we mask TeraData Using Delphix ?
    Best Answer

    Posted 02-17-2022 01:47:00 PM
    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
    ------------------------------



  • 4.  RE: Can we mask TeraData Using Delphix ?

    Posted 07-28-2023 02:45:00 AM

    Hello Bhupinder,

    Thanks a lot forr your help..!

    I have successfully installed a plugin for Teradata

    URL:

    Central Repository: com/teradata/jdbc/terajdbc/20.00.00.11 (maven.org)

    Package Name: terajdbc-20.00.00.11.jar



    ------------------------------
    Pankaj Asegaonkar
    Masking/Virtualization Specialist
    +91 9689996500
    ------------------------------



  • 5.  RE: Can we mask TeraData Using Delphix ?

    Posted 08-21-2023 06:10:00 AM

    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
    ------------------------------



  • 6.  RE: Can we mask TeraData Using Delphix ?

    Posted 08-21-2023 07:04:00 AM

    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
    ------------------------------



  • 7.  RE: Can we mask TeraData Using Delphix ?

    Posted 08-23-2023 06:24:00 AM

    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
    ------------------------------



  • 8.  RE: Can we mask TeraData Using Delphix ?

    Posted 08-24-2023 01:05:00 AM

    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
    ------------------------------



  • 9.  RE: Can we mask TeraData Using Delphix ?

    Posted 08-24-2023 07:36:00 AM

    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
    ------------------------------



  • 10.  RE: Can we mask TeraData Using Delphix ?

    Posted 08-25-2023 06:39:00 AM

    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
    ------------------------------



  • 11.  RE: Can we mask TeraData Using Delphix ?

    Posted 09-04-2023 02:58:00 PM

    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
    ------------------------------



  • 12.  RE: Can we mask TeraData Using Delphix ?

    Posted 09-05-2023 05:09:00 AM

    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
    ------------------------------



  • 13.  RE: Can we mask TeraData Using Delphix ?

    Posted 09-19-2023 05:59:00 AM

    Hello Bhupinder

    I have successfully masked the Teradata database using batch updated mode (without batch update it will take very long time to mask).

    Steps that I followed to prevent errors:

    • Update all columns with the current date in case of NULL for column data type  DATE as a prescript at the source database
    • Update all columns in case of NULL with space for column data type INTEGER as prescript at source database
    • Now source database will not have any NULL values
    • Configure masking job and execute On the Fly Masking job
    • The masking job worked well.

    Kindly let me know above approach is correct or not ..!!



    ------------------------------
    Pankaj Asegaonkar
    Masking/Virtualization Specialist
    +91 9689996500
    ------------------------------