All Forums Tools
Arpan.Roy 24 posts Joined 04/16
10 May 2016
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_CO L,DELQ_FAMILY_DESCR_COL,DROP_DTE_COL,LS_WORK_DTE_COL,L S_TRAN_DTE_COL,NO_ACTS_COL,NO_MEMOS_COL,REACTIVE_DTE_C OL,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.

 

VishnuVardhan 8 posts Joined 08/10
10 May 2016

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/

-- Vishnu Vardhan --
 

Arpan.Roy 24 posts Joined 04/16
11 May 2016

Hi Vishnu,
In LD_LIBRARY_PATH, that is added. Still no luck.
Thanks & Regards,
Arpan.

VishnuVardhan 8 posts Joined 08/10
11 May 2016

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.
 

-- Vishnu Vardhan --
 

Arpan.Roy 24 posts Joined 04/16
12 May 2016

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

 

VishnuVardhan 8 posts Joined 08/10
12 May 2016

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

-- Vishnu Vardhan --
 

Arpan.Roy 24 posts Joined 04/16
19 May 2016

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.

feinholz 1234 posts Joined 05/08
19 May 2016

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

Arpan.Roy 24 posts Joined 04/16
20 May 2016

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.

Arpan.Roy 24 posts Joined 04/16
24 May 2016

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.

feinholz 1234 posts Joined 05/08
26 May 2016

Does the data have to go to a single output file?
Can you write to multiple output files?
If so, then use multiple instances of the DataConnector Consumer and the -C command line option to round-robin the data to the different files.
When you say you get better throughput with Informatica, is Informatica using ODBC and writing to a single file?
Do they open multiple threads with SELECT statements with different WHERE clauses to partition out the data?
Or are they using a lower level extraction from the source database at the disk file or block level?

--SteveF

Arpan.Roy 24 posts Joined 04/16
28 May 2016

Hi Steve,
Thanks for your response. I tried with -C option with LoadInst as 6 to write the data in 6 different files. There also I'm getting throughput around 800 rows/sec. But I haven't tried with multiple instances of Data Connector Consumer. I'll try that too and update you.
So, if I use multiple instances of DataConnector Consumer (say 3) and LoadInstances as 6, then data won't get duplicate accross output files right?
Now coming to Informatica, Informatica uses TNS to connect to Oracle and it's writting in a single file. In this case, Informatica is firing the same SELECT statement in Oracle with same WHERE clause which I'm using in TPT.
I'm not very much clear on the question "are they using a lower level extraction from the source database at the disk file or block level?", if you can please tell what exactly this mean.
What we are trying to find out is an alternative and effective way of data extraction from Oracle. I found TPT ODBC operator, but trying to understand and produce statistics whether TPT ODBS operator can be an effective option or not.
Thanks again for your help and advice.
Thanks & Regards,
Arpan.
 
 
 

feinholz 1234 posts Joined 05/08
31 May 2016

Data does not get duplicated across the output files.
The file writer instances will each be given a unique block to write out.
Because it is round-robin, if you have 3 file writer instances, then instance 1 will get blocks 1, 4, 7, 10, etc. Instances 2 would get blocks 2, 5, 8, 1, etc. Instance 3 would get blocks 3, 6, 9, 12, etc.
 
I am not familiar with how Informatica is pulling data from the non-Teradata database.
Are they using ODBC?
Or some other mechanism?
 

--SteveF

samy@7 2 posts Joined 06/16
06 Jun 2016

Hi,
while running TPT script in unix(putty) I am facing following error.
tlbulid -f TPT_script.txt SYNC_DATA;
ksh: tbulid:  not found
will you please tell me what is the solution for it?

CarlosAL 512 posts Joined 04/08
06 Jun 2016

Hi.
The solution is spell correctly "tbuild" ;-)
Cheers.
Carlos.

samy@7 2 posts Joined 06/16
06 Jun 2016

hi,
I wrote "tbulid" only while execution but is showing same error.Plz help with it.

feinholz 1234 posts Joined 05/08
06 Jun 2016

Check your PATH environment variable.
Maybe the installation was not completed correctly.
 

--SteveF

klaement 2 posts Joined 01/15
18 Jun 2016

Hi,
I work now with a Teradata client which would like to use the ODBC_operator in a TPT on AIX to connect to Oracle Base.
This client uses TD14.10 version of the base and has TD14.10 version of the TTU installed on his server.
I found out that he needs to install a datadirect/progress driver before to be able to use ODBC operator. however, this client doesn't want to pay for this functionnality.
I have been told that the driver is natively bundled with the TTU 15.10. Can you confirm this? Is it totally free of charge and is it totally bundled with the 15.10 or do we need to install a patch on top of this ? Do you know if a key license has to be requested and whom I need to contact for this, if necessary?
I know that my post concern more the prerequisite than the configuration of ODBC itself, but any information is welcome.
Best Regards

Clement

CarlosAL 512 posts Joined 04/08
19 Jun 2016

Hi.
The bundled versions of DataDirect Oracle ODBC drivers have a 'trial period' and then a message of 'license expired' appears.
I think the last TPT 14.10 versions have the drivers bundled too.
You'll have to purchase the drivers as Progress or contact someone at TD to get the license files.
HTH.
Cheers.
Carlos.

klaement 2 posts Joined 01/15
20 Jun 2016

Thanx Carlos,
I will try to convince them that they have no choice but to buy it if they want it.
Regards

Clement

feinholz 1234 posts Joined 05/08
20 Jun 2016

The bundling of DataDirect drivers was first supported with TPT 14.10.00.06.
If you upgrade to that efix level, you will get the drivers.
However, you do need license files in order to use the drivers.
There are no "trial" periods with the bundled drivers.
If you do not have the license files, you will get an error.
To obtain the license files, your account team will need to contact our Product Management team.
There is no charge for the license files, but they need to track the customers that are obtaining them.
 

--SteveF

CarlosAL 512 posts Joined 04/08
22 Jun 2016

Hi Steve.
I've been using the bundled DD drivers (_tora27) on TPT with ODBC operators with the "warning window / disclaimer" appearing on each execution, but working OK.
From some point on, it changed to errors 6060 ("You are not licensed...") and 6062 ("You can order a license...") with execution aborted.
No new driver installation or upgrading was made.
I assumed there was a "trial period" for the drivers.
Cheers.
Carlos.

feinholz 1234 posts Joined 05/08
22 Jun 2016

You are correct, my bad.
I was unaware that our bundled drivers would act that way (but checked with the developer to make sure and you are correct).
The bundled drivers will allow a 15 day trial period.
 

--SteveF

Arpan.Roy 24 posts Joined 04/16
23 Jun 2016

Hi Steve,
I've installed TTU15.10 in Linux. In which directory the Data Direct ODBC drivers will reside? Do we need any additional license or cost to het these drivers?
 
Thanks & Regards,
Arpan.

Arpan.Roy 24 posts Joined 04/16
23 Jun 2016

Hi Steve,
I'm able to extract data using TPT ODBC Operator and Data Connector Consumar. I'm using TTU 15.10. But while running the job, getting 6060  and 6062 like Carlos. Below is the log:

./col_base.ksh
Teradata Parallel Transporter Version 15.10.01.00 64-Bit
Job log: /opt/teradata/client/15.10/tbuild/logs/COL_BASE-2.out
Job id is COL_BASE-2, running on ndwetlapp-mit-a1m.sys.comcast.net
Teradata Parallel Transporter DataConnector Operator Version 15.10.01.00
o_FileWritter[1]: Instance 1 directing private log report to 'DataConnector-1'.
o_FileWritter[1]: DataConnector Consumer operator Instances: 1
Teradata Parallel Transporter ODBC Operator Version 15.10.01.00
o_ODBCOper: private log specified: ODBCloadlog-1
Teradata Parallel Transporter ODBC Operator Version 15.10.01.00
Teradata Parallel Transporter ODBC Operator Version 15.10.01.00
o_ODBCOper: private log specified: ODBCloadlog-1
o_ODBCOper: private log specified: ODBCloadlog-1
Teradata Parallel Transporter ODBC Operator Version 15.10.01.00
o_ODBCOper: private log specified: ODBCloadlog-1
o_FileWritter[1]: ECI operator ID: 'o_FileWritter-23340'
o_FileWritter[1]: Operator instance 1 processing file '/data/Sample/tgtfile/col_base.out'.
o_ODBCOper: connecting sessions
o_ODBCOper: connecting sessions
o_ODBCOper: connecting sessions
o_ODBCOper: connecting sessions
6066:
[TPT][ODBC 20101 driver]6060
[TPT][ODBC 20101 driver]6062
6066:
[TPT][ODBC 20101 driver]6060
[TPT][ODBC 20101 driver]6062
6066:
[TPT][ODBC 20101 driver]6060
[TPT][ODBC 20101 driver]6062
6066:
[TPT][ODBC 20101 driver]6060
[TPT][ODBC 20101 driver]6062
o_ODBCOper: sending SELECT request
o_ODBCOper: sending SELECT request
o_ODBCOper: sending SELECT request
o_ODBCOper: sending SELECT request
o_ODBCOper: data retrieval complete
o_ODBCOper: Total Rows Exported:  67015
6066:
[TPT][ODBC 20101 driver]6060
[TPT][ODBC 20101 driver]6062
6066:
[TPT][ODBC 20101 driver]6060
[TPT][ODBC 20101 driver]6062
6066:
[TPT][ODBC 20101 driver]6060
[TPT][ODBC 20101 driver]6062
o_ODBCOper: data retrieval complete
o_ODBCOper: Total Rows Exported:  87278
o_ODBCOper: data retrieval complete
o_ODBCOper: Total Rows Exported:  105094
o_ODBCOper: data retrieval complete
o_ODBCOper: Total Rows Exported:  186586
o_FileWritter[1]: Total files processed: 1.
o_ODBCOper: disconnecting sessions
o_ODBCOper: disconnecting sessions
o_ODBCOper: disconnecting sessions
o_ODBCOper: disconnecting sessions
o_ODBCOper: Total processor time used = '2.2 Second(s)'
o_ODBCOper: Start : Thu Jun 23 08:42:46 2016
o_ODBCOper: End   : Thu Jun 23 08:52:04 2016
o_ODBCOper: Total processor time used = '1.12 Second(s)'
o_ODBCOper: Start : Thu Jun 23 08:42:46 2016
o_ODBCOper: End   : Thu Jun 23 08:52:04 2016
o_ODBCOper: Total processor time used = '1.49 Second(s)'
o_ODBCOper: Start : Thu Jun 23 08:42:46 2016
o_ODBCOper: End   : Thu Jun 23 08:52:04 2016
o_ODBCOper: Total processor time used = '0.95 Second(s)'
o_ODBCOper: Start : Thu Jun 23 08:42:46 2016
o_ODBCOper: End   : Thu Jun 23 08:52:04 2016
Job step MAIN_STEP completed successfully
Job COL_BASE completed successfully
Job start: Thu Jun 23 08:42:45 2016
Job end:   Thu Jun 23 08:52:04 2016

Now, I've 2 question:
1) Whether I need to purchase ODBC license from Data Direct or I'll get the license files from Teradata? If, Teradata, does it require extract cost and how to apply those licenses?
2) Total no of rows written in the file is 445973 and total time taken by the job is 559 sec. So, the throughput becomes 796. Is this the right way to calculate throughput?
Thanks & Regards,
Arpan.

feinholz 1234 posts Joined 05/08
23 Jun 2016

The ODBC license files for the bundled DataDirect drivers are provided at no charge, but they must be obtained from our Product Management group, as they track to which customers they supply the licenses.
 
Please have your account team contact our Product Management group.
 
Throughput is calculated as bytes per second, not rows per second. It is also helpful to calculate bytes per cpu second. That is true nature of the throughput as it relates to the time it takes for the application to process the data, not wall clock time, which also accounts for delays and other bottlenecks, like network.
 

--SteveF

feinholz 1234 posts Joined 05/08
23 Jun 2016

BTW, the reason why the actual text messages were not output was because of this:
 
They are regular messages which arise when the license is not available. Looks like he is using multiple ODBC operators with unionall and hence gets the messages again and again.
 
ODBC_OPERATOR: TPT17101: Fatal error received from ODBC driver:
              STATE=HY000, CODE=6060,
              MSG='[TPT][ODBC SQL Server Wire Protocol driver]You are not licensed to use this DataDirect Technologies product under the license you have purchased.  If you wish to purchase a license for use with this application, then you may use this product for a period '
ODBC_OPERATOR: TPT17101: Fatal error received from ODBC driver:
              STATE=HY000, CODE=6062,
              MSG='[TPT][ODBC SQL Server Wire Protocol driver]You can order a license for a DataDirect Technologies product for use with this application by calling DataDirect Technologies at 800-876-3101 in North America and +44 (0) 1753-218 930 elsewhere.Thank you'
 
The locale settings have to be en_US for the messages to be clear. For example:
 
LC_CTYPE=en_US.UTF-8
 

--SteveF

Arpan.Roy 24 posts Joined 04/16
24 Jun 2016

Thanks a lot Steve.
Below is my locale settings in .bash_profile.

export NLS_LANG=american_america.UTF8
#export LANG=C
#export LC_ALL=C
export LANG=en_US.UTF-8
export LC_ALL=en_US.UTF-8
export LC_CTYPE=en_US.UTF-8

But while running the script I'm not getting the clear message. Instead I'm getting messages like I previously got. Do I need to add any other variable to get clear messages?
I'm using multiple ODBC Operator using UNION ALL.
Now, I'm going back to little bit older question to make the extraction and write to file as parallel as possible and the suggestion was to use multiple instances of DataConnector Consumer operator and -C option to write the data in round robin method.
By multiple instances of Data Connector means LoadInst in tbuild command ? I used LoadInst=7. Or to achive multiple instances of Data Connector I need to change my tpt control file. Below is my tpt control file:

DEFINE JOB EXPORT_ORA_TABLE_TO_FILE

DESCRIPTION 'export EXPORT_ORA_TABLE_TO_FILE'

     (

        DEFINE SCHEMA SCHEMA_COL_BASE

            (

                                C1 NUMBER(4),

                                C2 NUMBER(4),

                                C3 NUMBER(4),

                                C4 NUMBER(2),

                                C5 CHAR(3),

                                C6 VARCHAR(25),

                                C7 VARCHAR(19),

                                C8 VARCHAR(19),

                                C9 VARCHAR(19),

                                C10     NUMBER(3),

                                C11 NUMBER(3),

                                C12 VARCHAR(19),

                                C13 CHAR(16),

                                C14 VARCHAR(19),

                                C15 VARCHAR(19)

                        );

 

        DEFINE OPERATOR o_ODBCOper

        TYPE ODBC

        SCHEMA SCHEMA_COL_BASE

        ATTRIBUTES (

            VARCHAR UserName            = @UserName

           ,VARCHAR UserPassword        = @UserPassword

           ,VARCHAR DSNName             = @DSNName

           ,VARCHAR PrivateLogName      = 'ODBCloadlog'

           ,VARCHAR SelectStmt          = @SelectClause || @SourceSchema || @SourceTable

           ,VARCHAR TraceLevel          = 'all'

           ,INTEGER DataBlockSize       = 2048

        );

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] ATTRIBUTES (SelectStmt=@SelectClause || @SourceSchema || @SourceTable || ' WHERE C2 > = 0 and C2 < 2000;'))

UNION ALL

SELECT * FROM OPERATOR (o_ODBCOper[@ReadInst] ATTRIBUTES (SelectStmt=@SelectClause || @SourceSchema || @SourceTable || ' WHERE C2 > = 2000 and C2 < 3000;'))

UNION ALL

SELECT * FROM OPERATOR (o_ODBCOper[@ReadInst] ATTRIBUTES (SelectStmt=@SelectClause || @SourceSchema || @SourceTable || ' WHERE C2 > = 3000 and C2 < 4000;'))

UNION ALL

SELECT * FROM OPERATOR (o_ODBCOper[@ReadInst] ATTRIBUTES (SelectStmt=@SelectClause || @SourceSchema || @SourceTable || ' WHERE C2 > = 4000 and C2 < 5000;'))

UNION ALL

SELECT * FROM OPERATOR (o_ODBCOper[@ReadInst] ATTRIBUTES (SelectStmt=@SelectClause || @SourceSchema || @SourceTable || ' WHERE C2 > = 5000 and C2 < 6000;'))

UNION ALL

SELECT * FROM OPERATOR (o_ODBCOper[@ReadInst] ATTRIBUTES (SelectStmt=@SelectClause || @SourceSchema || @SourceTable || ' WHERE C2 > = 6000 and C2 < 7000;'))

UNION ALL

SELECT * FROM OPERATOR (o_ODBCOper[@ReadInst] ATTRIBUTES (SelectStmt=@SelectClause || @SourceSchema || @SourceTable || ' WHERE C2 > = 7000 and C2 < 9999;'));

     )

     ;
Below is the tbuild command:

tbuild -C -h 100M -f /data/Sample/ctl/ora_table_export.tpt.ctl -v /data/Sample/logon/tpt.logon -u " UserName='User_Name' , UserPassword='****' , DSNName='DSN_Oracle' , load_op=o_ODBCOper , LoadInst=7 , ReadInst=7 , FileName='/data/Sample/tgtfile/ora_table_to_file.out' , LOAD_DTS='2016-06-24 08:21:34' , Format='DELIMITED' TextDelimiter='$^$' , SkipRows=0 , SourceSchema='Source_Schema' , SourceTable='Source_Table', SelectClause='SELECT C1 ,C2 ,C3 ,C4 ,C5 ,C6 ,TO_CHAR(C7,''YYYY-MM-DD HH24:MI:SS'') ,TO_CHAR(C8,''YYYY-MM-DD HH24:MI:SS'') ,TO_CHAR(C9,''YYYY-MM-DD HH24:MI:SS'') ,C10 ,C11 ,TO_CHAR(C12,''YYYY-MM-DD HH24:MI:SS'') ,C13 ,TO_CHAR(C14,''YYYY-MM-DD HH24:MI:SS'') ,TO_CHAR(C15,''YYYY-MM-DD HH24:MI:SS'') FROM '" COL_BASE

Please let me know whether this was the suggestion or I misunderstood.
 
Also, how to get the CPU secongs for log? Is it "o_ODBCOper: Total processor time used = '3.25 Second(s)'" ?
Thanks & Regards,
Arpan.

feinholz 1234 posts Joined 05/08
08 Jul 2016

Since you are using LoadInst as the variable for the file writer, yes you just need to set LoadInst=7.
 
Yes, Total processor time is the CPU time for that operator.
 

--SteveF

Arpan.Roy 24 posts Joined 04/16
14 Jul 2016

Hi Steve,

 

Using TPT ODBC Operator and DATA CONNECTOR Operator we are trying to fetch data from Oracle table. But the performance is not that much exciting it seems. In the existing system, we are able to extract 6000 rows/sec where as using TPT, I'm only able to extract 2800 rows/sec. 

 

Below are the options are are using mainly to improve performance. 

1) Used UNION ALL with different key range to extract data in parallel from source.
2) Used multiple instances of file to write.
3) Used -C option in tbuild command.
4) DataBlockSize=2048 and TraceLevel='none' has been used.
5) Using 100M memory in -h option in tbuild command.

Are there any other performance tuning step we need to follow?
Thanks & Regards,
Arpan.

Arpan.Roy 24 posts Joined 04/16
28 Jul 2016

Hi Steve,
Are there any other tuning technique we meed to apply apart from those mentioned above.
 
Thanks & Regards,
Arpan.

You must sign in to leave a comment.