Need Help regarding TPT ODBC Operator

Tools & Utilities
Enthusiast

Need Help regarding TPT ODBC Operator

Hi All,

I create one ODBC Connection entry in ODBCINI file defined in .profile. OS is RHEL (Red Hat Enterprise Linux Server release 5.9 (Tikanga)). TPT Version is 14.10.00.08.

INFA_HOME=/opt/Informatica/PowerCenter9.6.0; export INFA_HOME
ODBCHOME=$INFA_HOME/ODBC7.1; export ODBCHOME
ODBCINI=$ODBCHOME/odbc.ini; export ODBCINI

Below is the ODBC entry. This is for Oracle source.

[VCMC_Oracle]
Driver=/opt/Informatica/PowerCenter9.6.0/ODBC7.1/lib/ddora27.so
Description=DataDirect 7.1 Oracle Wire Protocol
HostName=<HostName>
PortNumber=1521
ServiceName=<OracleServiceName>

Using ODBC and Data Connector Consumer operator, I'm trying to write the data into a adelimetted text file.

Below is my tpt script.

DEFINE JOB EXPORT_COL_BASE_TO_FILE
DESCRIPTION 'export EXPORT_COL_BASE_TO_FILE'
(
DEFINE SCHEMA SCHEMA_COL_BASE
(
SYS_COL NUMBER(4),
PRIN_COL NUMBER(4),
AGNT_COL NUMBER(4),
COLL_CODE_COL NUMBER(2),
DELQ_FAMILY_COL CHAR(3),
DELQ_FAMILY_DESCR_COL VARCHAR(25),
DROP_DTE_COL VARCHAR(19),
LS_WORK_DTE_COL VARCHAR(19),
LS_TRAN_DTE_COL VARCHAR(19),
NO_ACTS_COL NUMBER(3),
NO_MEMOS_COL NUMBER(3),
REACTIVE_DTE_COL VARCHAR(19),
SUB_ACCT_NO_COL CHAR(16),
START_DTE_COL VARCHAR(19),
WORK_DTE_COL VARCHAR(19)
);

DEFINE OPERATOR o_ODBCOper
TYPE ODBC
SCHEMA SCHEMA_COL_BASE
ATTRIBUTES (
VARCHAR UserName = @UserName
,VARCHAR UserPassword = @UserPassword
,VARCHAR DSNName = @DSNName
,VARCHAR PrivateLogName = 'loadlog'
,VARCHAR SelectStmt = @SelectStmt
,VARCHAR TraceLevel = 'all'
);

DEFINE OPERATOR o_FileWritter
TYPE DATACONNECTOR CONSUMER
SCHEMA SCHEMA_COL_BASE
ATTRIBUTES (
VARCHAR FileName = @FileName
,VARCHAR Format = @Format
,VARCHAR TextDelimiter = @TextDelimiter
,VARCHAR IndicatorMode = 'N'
,VARCHAR OpenMode = 'Write'
,VARCHAR PrivateLogName = 'DataConnector'
,VARCHAR TraceLevel = 'all'
);
APPLY TO OPERATOR (o_FileWritter[@LoadInst])
SELECT * FROM OPERATOR (o_ODBCOper[@ReadInst]);
)
;

Below is my tbuild command:

tbuild -f /home/aroy001c/Sample/ctl/col_base.tpt.ctl -v /home/aroy001c/Sample/logon/aroy001c_tpt.logon -u " UserName='XXXXX' , UserPassword='XXXXX' , DSNName='VCMC_Oracle' , load_op=o_ODBCOper , LoadInst=1 , ReadInst=1 , FileName='/home/aroy001c/Sample/tgtfile/col_base.out' , LOAD_DTS='2016-04-27 08:21:34' , Format='DELIMITED' TextDelimiter='$^$' , SkipRows=0 , SelectStmt='SELECT SYS_COL,PRIN_COL,AGNT_COL,COLL_CODE_COL,DELQ_FAMILY_COL,DELQ_FAMILY_DESCR_COL,DROP_DTE_COL,LS_WORK_DTE_COL,LS_TRAN_DTE_COL,NO_ACTS_COL,NO_MEMOS_COL,REACTIVE_DTE_COL,SUB_ACCT_NO_COL,START_DTE_COL,WORK_DTE_COL FROM COL_BASE;'" COL_BASE

When I'm running the tbuild command, I'm not able to coonect to source. 

[aroy001c@pacdcpaprdetl1 bin] tlogview -l /opt/teradata/client/14.10/tbuild/logs/COL_BASE-5847.out -f '*' -g

Public log:

Using memory mapped file for IPC

TPT_INFRA: TPT04101: Warning: Teradata PT cannot connect to Unity EcoSysetm Manager.
The job will continue without event messages being sent to Unity EcoSystem Manager.
TPT_INFRA: TPT04190: Warning: OMD API failed to initialize
Found CheckPoint file: /opt/teradata/client/14.10/tbuild/checkpoint/COL_BASELVCP
This is a restart job; it restarts at step MAIN_STEP.
Teradata Parallel Transporter Executor Version 14.10.00.08
Teradata Parallel Transporter Coordinator Version 14.10.00.08
Teradata Parallel Transporter Executor Version 14.10.00.08
Teradata Parallel Transporter DataConnector Version 14.10.00.08
o_FileWritter: Instance 1 directing private log report to 'DataConnector-1'.
o_FileWritter: DataConnector Consumer operator Instances: 1
o_FileWritter: ECI operator ID: 'o_FileWritter-25430'
o_FileWritter: Operator instance 1 processing file '/home/aroy001c/Sample/tgtfile/col_base.out'.
Teradata Parallel Transporter ODBC Operator Version 14.10.00.08
o_ODBCOper: private log specified: loadlog-1
o_ODBCOper: connecting sessions
o_ODBCOper: TPT17122: Error: unable to connect to data source
o_ODBCOper: TPT17101: Fatal error received from ODBC driver:
STATE=IM003, CODE=0,
MSG='[DataDirect][ODBC lib] Specified driver could not be loaded'
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
o_ODBCOper: disconnecting sessions
o_ODBCOper: TPT17124: Error: unable to disconnect from data source
o_ODBCOper: TPT17101: Fatal error received from ODBC driver:
STATE=08003, CODE=0,
MSG='[DataDirect][ODBC lib] Connection not open'
o_ODBCOper: Total processor time used = '0.01 Second(s)'
o_ODBCOper: Start : Tue May 10 08:21:24 2016
o_ODBCOper: End : Tue May 10 08:21:24 2016
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
o_FileWritter: Total files processed: 0.
Job step MAIN_STEP terminated (status 12)
Job COL_BASE terminated (status 12)
Job start: Tue May 10 08:21:20 2016
Job end: Tue May 10 08:21:24 2016
Total available memory: 20000676
Largest allocable area: 20000676
Memory use high water mark: 45020
Free map size: 1024
Free map use high water mark: 19
Free list use high water mark: 0

So, to create the ODBC DSN for Teradata to connect to Oracle, do I need to make the entry in some other place or I've made the entry in correct place?

Is there any tool to test the connection.

Thanks & Regards,

Arpan.


30 REPLIES
Enthusiast

Re: Need Help regarding TPT ODBC Operator

Hi Arpan,

Did you happen to set the LD_LIBRARY_PATH variable?

if not, I suggest you set the LD_LIBRARY_PATH variable to /opt/teradata/client/14.10/tbuild/odbc/lib and /opt/Informatica/PowerCenter9.6.0/ODBC7.1/lib/

Enthusiast

Re: Need Help regarding TPT ODBC Operator

Hi Vishnu,

In LD_LIBRARY_PATH, that is added. Still no luck.

Thanks & Regards,

Arpan.

Enthusiast

Re: Need Help regarding TPT ODBC Operator

Arpan,

will it be possible to list out the contents in the odbc.ini file? (xxxxx out the sensitive information)

Before that, i would suggest you do the following steps:

1) echo $LD_LIBRARY_PATH

2) check if the file specified in the odbc.ini file exists.

under the DSN settings,

Driver=<some file name>    -----> check if this file exists

3) if it does not exist, please make sure the driver is available. if it is available then please run the fillowing commands:

$file <the full driver path>

$ldd <the full driver path>

4) check if all the dependencies exist. if not make sure they r in place and re run the job.

it would be good if you post the above details.

Enthusiast

Re: Need Help regarding TPT ODBC Operator

Below are the output of the commands.

1) Below is the output for LD_LIBRARY_PATH.

echo $LD_LIBRARY_PATH
/usr/lib:/usr/local/lib:/opt/oracle/client-11.2.0/lib:/opt/Informatica/PowerCenter9.6.0/ODBC7.1/lib:/opt/Informatica/PowerCenter9.6.0/server/bin:/opt/Informatica/PowerCenter9.6.0/java/lib:/opt/Informatica/PowerCenter9.6.0/java/jre/lib:/opt/teradata/client/14.10/lib64:/opt/teradata/client/14.10/tbuild/lib64:/opt/Informatica/PowerCenter9.6.0/java/bin:/opt/Composite_Software/CIS_6.1.0/apps/odbc64/lib:/opt/teradata/client/14.10/tbuild/odbc/lib

 ldd ddora27.so
linux-vdso.so.1 => (0x00007fff052cd000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00002b1b8e740000)
librt.so.1 => /lib64/librt.so.1 (0x00002b1b8e95d000)
libddicu27.so => /opt/Informatica/PowerCenter9.6.0/ODBC7.1/lib/libddicu27.so (0x00002b1b8eb66000)
libdl.so.2 => /lib64/libdl.so.2 (0x00002b1b8fa5d000)
libodbcinst.so => /opt/Informatica/PowerCenter9.6.0/ODBC7.1/lib/libodbcinst.so (0x00002b1b8fc62000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00002b1b8fe8f000)
libm.so.6 => /lib64/libm.so.6 (0x00002b1b9018f000)
libc.so.6 => /lib64/libc.so.6 (0x00002b1b90413000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00002b1b9076c000)
/lib64/ld-linux-x86-64.so.2 (0x000000391c600000)
libDWicu27.so => /opt/Informatica/PowerCenter9.6.0/ODBC7.1/lib/libDWicu27.so (0x00002b1b9097a000)

ls -ltr /opt/Informatica/PowerCenter9.6.0/ODBC7.1/lib/ddora27.so
-rwxr-xr-x 1 etladmin9 etladmin9 3099128 May 10 06:31 /opt/Informatica/PowerCenter9.6.0/ODBC7.1/lib/ddora27.so

Below is the outout for file command.

 file /opt/Informatica/PowerCenter9.6.0/ODBC7.1/lib/ddora27.so
/opt/Informatica/PowerCenter9.6.0/ODBC7.1/lib/ddora27.so: ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), stripped
Enthusiast

Re: Need Help regarding TPT ODBC Operator

Arpan,

From this:

file /opt/Informatica/PowerCenter9.6.0/ODBC7.1/lib/ddora27.so

/opt/Informatica/PowerCenter9.6.0/ODBC7.1/lib/ddora27.so: ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), stripped

The driver you are trying to use is a 64bit driver and unfortunately TPT has only 32bit support in the version you are using. Please use a 32bit driver with this version(14.10).

Enthusiast

Re: Need Help regarding TPT ODBC Operator

Thanks a lot for the help. After installing Data Direct ODBC drivers for 32 bit, the connection is working. 

Now, is there any way to read source data simultaniously with different SQLs (basically WHERE caluse will be different) and write it to some file using TPT ODBC Operator?

Currently we read using Informatica with 8 partitions simultaniously and each partition is having different WHERE caluse. So, in effect we have 8 parallel pipelines. 

I want to replicate the same using TPT ODBC operator. I can dire 8 tbuild cammands parallely, each having different WHERE caluse.

Is there any better way or can we pass multiple SQL to same tbuild command?

Thanks & Regards,

Arpan.

Teradata Employee

Re: Need Help regarding TPT ODBC Operator

Yes, there is a way.

The syntax is (roughly, you will have to refer to the manual for the exact syntax):

   APPLY TO OPERATOR ($FILE_WRITER)

   SELECT * FROM ($ODBC ATTRIBUTES (SelectStmt = 'SELECT ... FROM TABLE WHERE ...;') )

   UNION ALL

   SELECT * FROM ($ODBC ATTRIBUTES (SelectStmt = 'SELECT ... FROM TABLE WHERE ...;') )

   UNION ALL

   SELECT * FROM ($ODBC ATTRIBUTES (SelectStmt = 'SELECT ... FROM TABLE WHERE ...;') )

   . . .

   SELECT * FROM ($ODBC ATTRIBUTES (SelectStmt = 'SELECT ... FROM TABLE WHERE ...;') );

I used shorthand, but I wanted to get the point across that you use the UNION ALL syntax, with each copy of the ODBC operator executing a different SELECT statement (well, the same SELECT statement but with a different WHERE clause).

TPT will actually run multiple individual copies of the ODBC operator and all of the data that comes from those operators will be sent to the file writer to write out to an output file.

And if you want to write the data out to multiple data files, specify multiple instances of the file writer operator, and use the -C command line option so that the data is sent to each file writer instance in a round robin fashion, creating multiple output files of (roughly) the same size.

-- SteveF
Enthusiast

Re: Need Help regarding TPT ODBC Operator

Thanks a lot Steve. I changed my tpt control file like below:

 APPLY TO OPERATOR (o_FileWritter[@LoadInst])
SELECT * FROM OPERATOR (o_ODBCOper[@ReadInst] ATTRIBUTES (SelectStmt=@SelectClause || @SourceSchema || @SourceTable || ' WHERE PRIN_COL=1000;'))
UNION ALL
SELECT * FROM OPERATOR (o_ODBCOper[@ReadInst] ATTRIBUTES (SelectStmt=@SelectClause || @SourceSchema || @SourceTable || ' WHERE PRIN_COL=5000;'))
UNION ALL
SELECT * FROM OPERATOR (o_ODBCOper[@ReadInst] ATTRIBUTES (SelectStmt=@SelectClause || @SourceSchema || @SourceTable || ' WHERE PRIN_COL=2000;'))
UNION ALL
SELECT * FROM OPERATOR (o_ODBCOper[@ReadInst] ATTRIBUTES (SelectStmt=@SelectClause || @SourceSchema || @SourceTable || ' WHERE PRIN_COL=3000;'));

But when I fires the tbuild command (with -h option 25M) I'm getting below error:

[aroy001c@pacdcpaprdetl1 bin] ./col_base.ksh
Teradata Parallel Transporter Version 14.10.00.08
Job log: /opt/teradata/client/14.10/tbuild/logs/COL_BASE-6013.out
Job id is COL_BASE-6013, running on pacdcpaprdetl1.cable.comcast.com
Teradata Parallel Transporter DataConnector Version 14.10.00.08
o_FileWritter: Instance 1 directing private log report to 'DataConnector-1'.
o_FileWritter: DataConnector Consumer operator Instances: 1
o_FileWritter: ECI operator ID: 'o_FileWritter-28074'
Teradata Parallel Transporter ODBC Operator Version 14.10.00.08
o_ODBCOper: private log specified: ODBCloadlog-1
Teradata Parallel Transporter ODBC Operator Version 14.10.00.08
o_ODBCOper: private log specified: ODBCloadlog-1
Teradata Parallel Transporter ODBC Operator Version 14.10.00.08
o_ODBCOper: private log specified: ODBCloadlog-1
Teradata Parallel Transporter ODBC Operator Version 14.10.00.08
o_ODBCOper: private log specified: ODBCloadlog-1
o_ODBCOper: connecting sessions
o_ODBCOper: connecting sessions
o_ODBCOper: connecting sessions
o_ODBCOper: connecting sessions
o_FileWritter: Operator instance 1 processing file '/data/infa_shared/NDW/Sample/tgtfile/col_base.out'.
o_ODBCOper: sending SELECT request
o_ODBCOper: sending SELECT request
o_ODBCOper: sending SELECT request
o_ODBCOper: sending SELECT request
TPT_INFRA: TPT02610: Error: Could not allocate a data message of size 1048420
TPT_INFRA: TPT02284: Error: Cannot create data buffer, Data Stream status = 3
TPT_INFRA: TPT02610: Error: Could not allocate a data message of size 524228
TPT_INFRA: TPT02284: Error: Cannot create data buffer, Data Stream status = 3
o_ODBCOper: TPT15111: Error 19 allocating memory for data buffer
o_ODBCOper: disconnecting sessions
o_ODBCOper: disconnecting sessions
TPT_INFRA: TPT02610: Error: Could not allocate a data message of size 1048420
TPT_INFRA: TPT02284: Error: Cannot create data buffer, Data Stream status = 3
TPT_INFRA: TPT02610: Error: Could not allocate a data message of size 524228
TPT_INFRA: TPT02284: Error: Cannot create data buffer, Data Stream status = 3
o_ODBCOper: TPT15111: Error 19 allocating memory for data buffer
o_ODBCOper: disconnecting sessions
o_ODBCOper: Total processor time used = '0.75 Second(s)'
o_ODBCOper: Start : Fri May 20 06:28:43 2016
o_ODBCOper: End : Fri May 20 06:28:48 2016
o_ODBCOper: Total processor time used = '0.75 Second(s)'
o_ODBCOper: Start : Fri May 20 06:28:43 2016
o_ODBCOper: End : Fri May 20 06:28:48 2016
o_ODBCOper: Total processor time used = '0.72 Second(s)'
o_ODBCOper: Start : Fri May 20 06:28:43 2016
o_ODBCOper: End : Fri May 20 06:28:48 2016
o_ODBCOper: disconnecting sessions
o_ODBCOper: Total processor time used = '0.78 Second(s)'
o_ODBCOper: Start : Fri May 20 06:28:43 2016
o_ODBCOper: End : Fri May 20 06:28:49 2016
Job step MAIN_STEP terminated (status 12)
Job COL_BASE terminated (status 12)
o_FileWritter: Total files processed: 0.
Job start: Fri May 20 06:28:36 2016
Job end: Fri May 20 06:28:50 2016
[aroy001c@pacdcpaprdetl1 bin]

Can you please advice what can be done for this error? Also, here I'm using only 4 UNION ALL, but we may want to use around 8 UNION ALL. Also, I tried tbuild command with -h as 15M and 20M also.

Thanks & Regards,

Arpan.

Enthusiast

Re: Need Help regarding TPT ODBC Operator

Hi Steve,

With -h as 100 M, I'm able to write the data into a flat file. But, when we are using Informatica we are getting throughput around 3000, but when I'm using TPT (ODBC Operator with Data Connector Consumer), throughput is around 800. Is there any way to increase throughput for both ODBC and Data Connector Consumer Operator?

Thanks & Regards,

Arpan.