LDAP support for TPT Export and Update Operator

Tools & Utilities
Enthusiast

LDAP support for TPT Export and Update Operator

Hi All,

I'm trying to move data between two Teradata Environments through TPT. I want to use LDAP authentication. Below is my environment details:

Teradata Parallel Transporter Version 15.10.01.00 64-Bit

Red Hat Enterprise Linux Server release 6.8 (Santiago)

Below is my TPT Control File

DEFINE JOB JOB_EMPLOYEE_V
DESCRIPTION 'Export Import script for EMPLOYEE_V from TD Table'
(
/*DEFINE SCHEMA SCHEMA_EMPLOYEE_V FROM TABLE '<DatabaseName>.EMPLOYEE_V';*/

DEFINE SCHEMA SCHEMA_EMPLOYEE_V
(
 EMP_ID INTEGER
,EMP_NAME VARCHAR(100)
,SAL INTEGER
,DOB ANSIDATE
,DOJ ANSIDATE
);

DEFINE OPERATOR o_ExportOper
TYPE EXPORT
SCHEMA SCHEMA_EMPLOYEE_V
ATTRIBUTES
(
 VARCHAR UserName              = '<Domain>\<UserName>'
,VARCHAR UserPassword          = '<Password>'
,VARCHAR TdpId                 = '<IP_Address>'
,INTEGER MaxDecimalDigits      = 38
,INTEGER MaxSessions           = @MaxSessions
,INTEGER MinSessions           = @MinSessions
,VARCHAR PrivateLogName        = 'Export'
,VARCHAR SpoolMode             = 'NoSpool'
,VARCHAR SourceDatabase        = @SourceDatabase
,VARCHAR SourceTable           = @SourceTable
,VARCHAR SelectStmt            = @SelectStmt
,VARCHAR SourceLogonMech       = 'ldap'
,VARCHAR SourceAccountId       = @SourceAccountId
);
DEFINE OPERATOR o_mload
TYPE update
SCHEMA *
ATTRIBUTES
(
 VARCHAR TdpId           = @TargetTdpId
,VARCHAR UserName        = @TargetUserName
,VARCHAR UserPassword    = @TargetUserPassword
,INTEGER MaxSessions     = @MaxSessions
,INTEGER MinSessions     = @MinSessions
,VARCHAR TargetTable     = @TargetTable
,VARCHAR TargetDatabase  = @TargetDatabase
,VARCHAR LogTable        = @LogTable
,VARCHAR ErrorTable1     = @ErrorTable1
,VARCHAR ErrorTable2     = @ErrorTable2
,VARCHAR WorkTable       = @WorkTable
,VARCHAR PrivateLogName  = 'Load'
,VARCHAR AmpCheck        = 'None'
,VARCHAR TargetLogMech   = 'ldap'
,VARCHAR TargetAccountId = @TargetAccountId
);

APPLY
(
   'INSERT INTO ' || @TargetDatabase || '.' || @TargetTable ||
                '(
                                          EMP_ID
                                        , EMP_NAME
                                        , SAL
                                        , DOB
                                        , DOJ
                )
                VALUES
                (
                                          :EMP_ID
                                        , :EMP_NAME
                                        , :SAL
                                        , :DOB
                                        , :DOJ
                );'
 )

TO OPERATOR ( o_mload )
select * from OPERATOR ( o_ExportOper );
);

Below is my tbuild command:

tbuild -f <Full_Path_to_the TPT_Script> -v <Full_Path_to_the_Logon_File> -u " UserName='<Domain\UserName>' , UserPassword='<UserPassword Contains "@" so used "\" before @ >', SourceTdpId='<IP_Address>',SourceDatabase='<SourceDataBase>', SourceTable='EMPLOYEE_V', LogTable='<Log_Table>' , ErrorTable1='<Error_Table_1>' , ErrorTable2='<Error_Table_1>' , WorkTable='<Work_Table>' , SelectStmt='select * from <DatabaseName.TableName>' ,TargetTdpId='<Target_Teradata_IP_Address>', TargetDatabase='<Target_Database>' , TargetTable='employee' , MacroDatabase='<Macro_Database>' , load_op=o_ExportOper , LoadInst=1 , ReadInst=1 , MaxSessions=10 , MinSessions=5 , LOAD_DTS='2017-12-15 08:21:34' " employee_v

But whenever I'm running the script through tbuild command, I'm getting " Invalid User Id, Password or Account String error".

 

Teradata Parallel Transporter Version 15.10.01.00 64-Bit
Job log: /opt/teradata/client/15.10/tbuild/logs/employee_v-193.out
Job id is employee_v-193, running on <Server>
Teradata Parallel Transporter Export Operator Version 15.10.01.00
o_ExportOper: private log specified: Export
Teradata Parallel Transporter Update Operator Version 15.10.01.00
o_mload: private log specified: Load
o_ExportOper: connecting sessions
o_ExportOper: TPT10508: RDBMS error 8017: The UserId, Password or Account is invalid.
o_ExportOper: Total processor time used = '0.04 Second(s)'
o_ExportOper: Start : Fri Dec 15 11:13:57 2017
o_ExportOper: End   : Fri Dec 15 11:13:57 2017
o_mload: connecting sessions
o_mload: TPT10508: RDBMS error 8017: The UserId, Password or Account is invalid.
o_mload: Total processor time used = '0.04 Second(s)'
o_mload: Start : Fri Dec 15 11:13:57 2017
o_mload: End   : Fri Dec 15 11:13:57 2017
Job step MAIN_STEP terminated (status 12)
Job employee_v terminated (status 12)
Job start: Fri Dec 15 11:13:55 2017
Job end:   Fri Dec 15 11:13:57 2017

I'm able to login to BTEQ using the same user Id and Password. 

Can someone please help in this regard? Whether LDAP authentication is supported in TPT?

 

Thanks & Regards,

Arpan

8 REPLIES
Enthusiast

Re: LDAP support for TPT Export and Update Operator

Hi All,

Can someone please help us on this?

 

1) Whether LDAP authentication is supported in TPT?

2) Whether automatic schema generation is supported by TPT using LDAP authentication?

 

Thanks & Regards,

Arpan.

Tags (5)
Teradata Employee

Re: LDAP support for TPT Export and Update Operator

The attribute names in the script (to the left of the "=") should not have "Source" or "Target" prefixes. They need to match the attribute names as shown in the TPT Reference manual. There's no problem with using such prefixes on variable names (as the operator templates do, for example).

 

In particular, use LogonMech='ldap' (and fix the other attribute names also).

Teradata Employee

Re: LDAP support for TPT Export and Update Operator

One minor caveat to what Fred indicated.

 

For the consumer operator, the attribute "TargetTable" does have the "Target" prefix because "TargetTable" is the actual name of the attribute.

 

When writing a script with the DEFINE OPERATOR objects, please refer to the TPT Reference Manual for the proper names of all attributes.

 

Thanks!

 

-- SteveF
Enthusiast

Re: LDAP support for TPT Export and Update Operator

Thanks for your reply.

After few modifications in the TPT Control File and tbuild command,the data movement is happening.

But there are 2 issues that are present now.

1>when the td logon password is having "@" in it , getting error as " Invalid User Id, Password or Account String error".

we have tried to use "\" , but that is also not working. Actually no special character is working in password.

we had to change the password to make it run.

 

why is this happening and how to resolve it?

 

2>TargetDatabase is not getting recognised. It is always taking the default database tagged to that user id . only hardcoding the tablename with addition of databasename is working. Please suggest what needs to be done.

 

Below is the tbuild command and control script:

 

DEFINE JOB JOB_EMPLOYEE_V

DESCRIPTION 'Export Import script for EMPLOYEE_V from TD Table'

(

/*DEFINE SCHEMA SCHEMA_EMPLOYEE_V FROM TABLE 'NDW_TEMP.EMPLOYEE_V';*/

 

DEFINE SCHEMA SCHEMA_EMPLOYEE_V

(

EMP_ID INTEGER

,EMP_NAME VARCHAR(100)

,SAL INTEGER

,DOB ANSIDATE

,DOJ ANSIDATE

);

 

DEFINE OPERATOR o_ExportOper

TYPE EXPORT

SCHEMA SCHEMA_EMPLOYEE_V

ATTRIBUTES

(

VARCHAR UserName              = '<UserName>'
,VARCHAR UserPassword          = '<Password>'
,VARCHAR TdpId                 = '<IP_Address>'

,INTEGER MaxDecimalDigits = 38

,INTEGER MaxSessions = @MaxSessions

,INTEGER MinSessions = @MinSessions

,VARCHAR PrivateLogName = 'Export'

,VARCHAR SpoolMode = 'NoSpool'

,VARCHAR SourceDatabase        = @SourceDatabase
,VARCHAR SourceTable           = @SourceTable

,VARCHAR SelectStmt = @SelectStmt

,VARCHAR LogonMech = 'ldap'

/*,VARCHAR SourceAccountId = @SourceAccountId*/

);

 

DEFINE OPERATOR o_mload

TYPE update

SCHEMA *

ATTRIBUTES

(

 VARCHAR TdpId           = @TargetTdpId
,VARCHAR UserName        = @TargetUserName
,VARCHAR UserPassword    = @TargetUserPassword

,INTEGER MaxSessions = @MaxSessions

,INTEGER MinSessions = @MinSessions

,VARCHAR TargetTable = '<TargetDatabasename >.<TargetTablename>

,VARCHAR TargetDatabase  = @TargetDatabase

,VARCHAR LogTable = @LogTable

,VARCHAR ErrorTable1 = @ErrorTable1

,VARCHAR ErrorTable2 = @ErrorTable2

,VARCHAR WorkTable = @WorkTable

,VARCHAR PrivateLogName = 'Load'

,VARCHAR AmpCheck = 'None'

,VARCHAR LogonMech = 'ldap'

/*,VARCHAR TargetAccountId = @TargetAccountId*/

);

 

APPLY

(

'INSERT INTO ' || @TargetTable ||

'(

EMP_ID

, EMP_NAME

, SAL

, DOB

, DOJ

)

VALUES

(

:EMP_ID

, :EMP_NAME

, :SAL

, :DOB

, :DOJ

);'

)

 

TO OPERATOR ( o_mload )

select * from OPERATOR ( o_ExportOper );

);

 

 

The tbuild command :

tbuild -f <Full_Path_to_the TPT_Script> -u " UserName='<UserName>', TargetDatabase= '<Target_Database>' , TargetTable='<Target_Database>.EMPLOYEE_ABC' , LogTable='<Log_Table>' , ErrorTable1='<Error_Table_1>' , ErrorTable2='<Error_Table_1>' , WorkTable='<Work_Table>' , SelectStmt='select * from <DatabaseName.TableName>', MacroDatabase='<Macro_Database>' , load_op=o_ExportOper , LoadInst=1 , ReadInst=1 , MaxSessions=10 , MinSessions=5 , LOAD_DTS='2017-12-20 08:21:34' '" employee_v

 

Tags (2)
Teradata Employee

Re: LDAP support for TPT Export and Update Operator

The attribute name for the default qualifier for the UPDATE operator is WorkingDatabase, not TargetDatabase.

 

Yes, special characters can be a challenge with LDAP authentication. The necessary "escape syntax" depends on your LDAP provider and security configuration. (Not TPT)

 

Enthusiast

Re: LDAP support for TPT Export and Update Operator

Thanks you very much Fred and Steve for your help. We are now able to move data using LDAP authentication login.

But whenever I'm trying to use auto schema generation (DEFINE SCHEMA SCHEMA_EMPLOYEE_V FROM TABLE), I'm getting below error:

 

Teradata Parallel Transporter Version 15.10.01.00 64-Bit
TPT_INFRA: TPT05014: RDBMS error 8017: The UserId, Password or Account is invalid.
TPT_INFRA: TPT04032: Error: Schema generation failed for table '<DataBaseName>.EMPLOYEE_V' in DBS '<SourceDataBaseIP>':
  "GetTableSchema" status: 48.

Job script preprocessing failed.

Job terminated with status 12.

Can you please tell us whether "DEFINE SCHEMA FROM TABLE" syntax is supported using LDAP authentication?

 

 

Thanks & Regards,

Arpan.

 

Tags (4)
Teradata Employee

Re: LDAP support for TPT Export and Update Operator

Yes, LDAP support was added to schema generation in 14.10.00.005 and 15.00.00.001, which means it should be in 15.10.01.00.

 

 

-- SteveF
Enthusiast

Re: LDAP support for TPT Export and Update Operator

ks a lot Steve for your response. Is it something issue with TeraGSS? Is there any way we can test it? Just to let you know that when we are using non LDAP user, DEFINE SCHEMA FROM TABLE is working fine.

 

Thanks & Regards,

Arpan.