All Forums Tools
venkata_k01 24 posts Joined 07/16
15 Jul 2016
Zero records are fetching when using where statement in TPT

Hi,
 I am giving the below select statement in job variable file but eventhough there are around 2K records present in the source table, when i am doing the data copy from source to target table using tdload , its not fetching any records.
 
 SelectStmt='SELECT * FROM SOURCEDB.SOURCETABLE WHERE ROW_EFF_DT >= ''2016-07-10'' AND ROW_EFF_DT <=''2016-07-12'''
 
 I am using double single quotes before & after date value but still its not working.Format of the ROW_EFF_DT is same (YYYY-MM-DD).
 I am using TPT version 15.10.00.03.
 
 Thanks
 

feinholz 1234 posts Joined 05/08
15 Jul 2016

Thank you for providing the TPT version information!
Have you tried the exact same query using BTEQ to see if there is a result set?
I am assuming from the information you provided that the job does not actually terminate with an error. You just seem to get 0 rows exported.
Is that correct?
Have you looked at the log of the job?
The output of the Export operator might give you a clue.
 

--SteveF

venkata_k01 24 posts Joined 07/16
15 Jul 2016
2016-07-13 09:44:16 Connecting to Teradata Database
Teradata Load Utility Version 15.10.00.03 64-Bit
Teradata Parallel Transporter Version 15.10.00.03 64-Bit
Job log: /opt/teradata/client/15.10/tbuild/logs/vkakara-86.out
Job id is vkakara-86, running on etlld0013
Teradata Parallel Transporter SQL Inserter Operator Version 15.10.00.03
$INSERTER: private log specified: InserterLog
Teradata Parallel Transporter Export Operator Version 15.10.00.03
$EXPORT: private log specified: ExportLog
$INSERTER: connecting sessions
$EXPORT: connecting sessions
$EXPORT: sending SELECT request
$EXPORT: entering End Export Phase
$EXPORT: Total Rows Exported:  0
$INSERTER: sending data
$INSERTER: finished sending data
$EXPORT: disconnecting sessions
$INSERTER: Total Rows Sent To RDBMS:      0
$INSERTER: Total Rows Applied:            0
$INSERTER: disconnecting sessions
$INSERTER: Total processor time used = '0.36 Second(s)'
$INSERTER: Start : Wed Jul 13 09:44:20 2016
$INSERTER: End   : Wed Jul 13 09:44:35 2016
$EXPORT: Total processor time used = '0.1 Second(s)'
$EXPORT: Start : Wed Jul 13 09:44:20 2016
$EXPORT: End   : Wed Jul 13 09:44:37 2016
Job step MAIN_STEP completed successfully
Job vkakara completed successfully
Job start: Wed Jul 13 09:44:18 2016
Job end:   Wed Jul 13 09:44:37 2016
2016-07-13 09:44:47 Tdload is successful

Hi Steve,
Yes.Script is not failing but fetching zero records.
 PFA the log.
Thanks,
Hanu

feinholz 1234 posts Joined 05/08
15 Jul 2016

That is the console output.
The log itself will have the output from the Export operator.
However, before looking at the log, you should try the exact same SELECTG request with BTEQ just to make sure that the query is supposed to return rows.
 

--SteveF

venkata_k01 24 posts Joined 07/16
16 Jul 2016

Hi Steve,

I ran the same select query using bteq and it fetched 126332 records as expected.

SELECT * FROM UDWBASEVIEW1.MBR WHERE SRC_ROW_EFF_DT >='2016-07-10' AND SRC_ROW_EFF_DT <='2016-07-12';

 *** Query completed. 126332 rows found. 102 columns returned.
 *** Total elapsed time was 24 seconds.

Same select statement when running using TPT is giving below error.
Teradata Load Utility Version 15.10.00.03 64-Bit
TDLOAD: TPT05516: Syntax error in the job variable file.
                  Expected comma after the specification of variable 'SelectStmt'.

So tried with the double single quotes as below but it didn't fetched any records.

SelectStmt='SELECT * FROM UDWBASEVIEW1.MBR WHERE SRC_ROW_EFF_DT >=''2016-07-10'' AND SRC_ROW_EFF_DT <=''2016-07-12'''

TPT log

Using memory mapped file for IPC

TPT_INFRA: TPT04101: Warning: Teradata PT cannot connect to Unity EcoSystem Manager.
             The job will continue without event messages being sent to Unity EcoSystem Manager.
TPT_INFRA: TPT04197: Warning: OMD API failed to initialize
Teradata Parallel Transporter Coordinator Version 15.10.00.03
Teradata Parallel Transporter Executor Version 15.10.00.03
Teradata Parallel Transporter Executor Version 15.10.00.03
Teradata Parallel Transporter Export Operator Version 15.10.00.03
$EXPORT: private log specified: ExportLog
Teradata Parallel Transporter Load Operator Version 15.10.00.03
$LOAD: private log specified: LoadLog
$LOAD: connecting sessions
$EXPORT: connecting sessions
$LOAD: preparing target table
$LOAD: entering Acquisition Phase
Job is running in Buffer Mode
Task(APPLY_1[0001]): checkpoint completed, status = Success
Task(SELECT_2[0001]): checkpoint completed, status = Success
$EXPORT: sending SELECT request
Data Block size: 3470696  Buffers/Block: 54  Data Buffer size: 64272
$EXPORT: entering End Export Phase
$EXPORT: Total Rows Exported:  0
Task(SELECT_2[0001]) ready to checkpoint

Task(SELECT_2[0001]): checkpoint completed, status = Success
Task(APPLY_1[0001]): checkpoint completed, status = Success
Task(SELECT_2[0001]) ready to take the EOD checkpoint

Task(SELECT_2[0001]): checkpoint completed, status = Success
Task(APPLY_1[0001]): checkpoint completed, status = Success
$LOAD: entering Application Phase
$LOAD: Statistics for Target Table:  'ADJD_MCE_TST_tmp'
$LOAD: Total Rows Sent To RDBMS:      0
$LOAD: Total Rows Applied:            0
$LOAD: Total Rows in Error Table 1:   0
$LOAD: Total Rows in Error Table 2:   0
$LOAD: Total Duplicate Rows:          0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
$LOAD: disconnecting sessions
$EXPORT: disconnecting sessions
$LOAD: Total processor time used = '0.19 Second(s)'
$LOAD: Start : Sat Jul 16 03:20:53 2016
$LOAD: End   : Sat Jul 16 03:21:04 2016
$EXPORT: Total processor time used = '0.1 Second(s)'
$EXPORT: Start : Sat Jul 16 03:20:53 2016
$EXPORT: End   : Sat Jul 16 03:21:05 2016
Job step MAIN_STEP completed successfully
Job vkakara completed successfully
Job start: Sat Jul 16 03:20:52 2016
Job end:   Sat Jul 16 03:21:05 2016
Total available memory:          20000632
Largest allocable area:          20000632
Memory use high water mark:       3482000
Free map size:                       1024
Free map use high water mark:          18
Free list use high water mark:          0
Please let me know your suggestions to make it work in TPT as well.
 
Thanks,
Hanu

Fred 1096 posts Joined 08/04
18 Jul 2016

If you are using the $EXPORT template and providing the SELECT at the job level, use the ExportSelectStmt variable.

venkata_k01 24 posts Joined 07/16
18 Jul 2016

Getting the below error when ExportSelectStmt variable is used in the job variable file.
Teradata Load Utility Version 15.10.00.03 64-Bit
TDLOAD: TPT05528: Error: No data source is not specified.
                  You need to specify one of the following:
                      SourceFileName
                      SourceTable
                      SelectStmt
 
I don't see ExportSelectStmt variable in the tdload options.
 
Thanks ,
Hanu

feinholz 1234 posts Joined 05/08
19 Jul 2016

For your versiobn of TPT, continue to use SelectStmt.
We are "fixing" TPT to also accept ExportSelectStmt, but that is not completed yet.
I will need to see a complete log (the .out file).
I may also need you to run with trace enabled to see what SELECT statement is actually being sent to Teradata.
But for now, just send me the .out file.
 

--SteveF

venkata_k01 24 posts Joined 07/16
19 Jul 2016

Hi Steve,

PFB the select statement from the .out file.

Single quote before & after the date value is missing and that is why it is fetching 0 records.

k   ===================================================================
     =                                                                 =
     =                         Select Request                          =
     =                                                                 =
     ===================================================================
**** 11:54:25
eSELECT * FROM UDWBASEVIEW1.ADJD_MCE WHERE SRC_ROW_EFF_DT >=2016-07-10 AND SRC_ROW_EFF_DT <=2016-07-12
$EXPORT
vkakara-124,88,5,ExportReqSubmitBegin,MAIN_STEP,$EXPORT,1,2016-07-19,SELECT * FROM UDWBASEVIEW1.ADJD_MCE WHERE SRC_ROW_EFF_DT >=2016-07-10 AND SRC_ROW_EFF_DT <=2016-07-12,2,0
**** 11:54:28
Gvkakara-124,89,0,ExportReqSubmitEnd,MAIN_STEP,$EXPORT,1,2016-07-19,,2,0
Fvkakara-124,99,37,ExportStmtCount,MAIN_STEP,$EXPORT,1,2016-07-19,1,2,0
Hvkakara-124,100,37,ExportBlockCount,MAIN_STEP,$EXPORT,1,2016-07-19,0,2,0
[vkakara-124,90,5,ExportReqFetchBegin,MAIN_STEP,$EXPORT,1,2016-07-19,2016-07-19 11:54:28,2,0
Ivkakara-124,92,0,ExportStmtFetchBegin,MAIN_STEP,$EXPORT,1,2016-07-19,,2,0
Gvkakara-124,94,37,ExportStmtNumber,MAIN_STEP,$EXPORT,1,2016-07-19,1,2,0
Kvkakara-124,95,37,ExportStmtBlockCount,MAIN_STEP,$EXPORT,1,2016-07-19,0,2,0
Gvkakara-124,93,0,ExportStmtFetchEnd,MAIN_STEP,$EXPORT,1,2016-07-19,,2,0
Lvkakara-124,96,37,ExportStmtRowsFetched,MAIN_STEP,$EXPORT,1,2016-07-19,0,2,0
Yvkakara-124,91,5,ExportReqFetchEnd,MAIN_STEP,$EXPORT,1,2016-07-19,2016-07-19 11:54:28,2,0

Below is the Select statment in the job variable file.I am using double single quotes whereever single quotes are needed.
SelectStmt='SELECT * FROM UDWBASEVIEW1.ADJD_MCE WHERE SRC_ROW_EFF_DT >=''2016-07-10'' AND SRC_ROW_EFF_DT <=''2016-07-12'''
 
Thanks,
Hanu
 
 

venkata_k01 24 posts Joined 07/16
19 Jul 2016

Hi Steve,
 
I am facing issue while uploading complete .out file.
Please let me know if the above information is enough for furthur analysis before my attempt to upload the file is successful.
 
Thanks,
Hanu

feinholz 1234 posts Joined 05/08
19 Jul 2016

I have enough information.
I needed to know if the single-quotes were being stripped.
 

--SteveF

venkata_k01 24 posts Joined 07/16
20 Jul 2016

Any way to make sure single-quotes present in the SELECT statement that is sent to Teradata and overcome this issue?
 
Thanks,
Hanu

feinholz 1234 posts Joined 05/08
20 Jul 2016

If you write a script and put the SELECT statement in the script, the single quotes will not be stripped.

--SteveF

venkata_k01 24 posts Joined 07/16
21 Jul 2016

Currently also I am creating job variable file in perl script and then in the same script calling the tdload. Still issue exists.
It would be very helpful if you could please elaborate/give me sample example how to make it .
 
Thanks,
Hanu

feinholz 1234 posts Joined 05/08
21 Jul 2016

Our documentation will describe examples, and we ship sample scripts with TPT.
Look in the "samples" directory where TPT is installed.
 

--SteveF

venkata_k01 24 posts Joined 07/16
20 Aug 2016

Hi Steve,
 
We tried many ways by putting select statement in the script but still no luck.
It would be very helpful if you could please provide one simple sample example.
 
Thanks & Regards,
Hanu

feinholz 1234 posts Joined 05/08
22 Aug 2016

If you tried to put the SELECT statement in the script, please send me your script and I will make adjustments as necessary.
 

--SteveF

Naresh12 1 post Joined 08/16
22 Aug 2016

SelectStmt='SELECT * FROM TABLE_NAME WHERE EFF_DT='2016-08-12' '
 
Unable to Enclose date inside single quotes while passing the variable in job_variable file for tdload script.
Please let me other ways to use this.
using version 14.10.

feinholz 1234 posts Joined 05/08
22 Aug 2016

You need to escape (double) the single quote characters around the date.

--SteveF

venkata_k01 24 posts Joined 07/16
23 Aug 2016

Hi Steve,

PFB the details.

Code in the script where select statement is being assigned.

$parms{SelectStmt}="SELECT * FROM ${srcdb}.${srctblnm} WHERE STATUS=''A''";

Calling tdload in the script:

tdload --SourceTdpId '$parms{SourceTdpId}' --SourceLogonMech '$parms{SourceLogonMech}' --SourceUserName '$parms{SourceUserName}' --SourceUserPassword '$parms{SourceUserPassword}' --TargetTable '$parms{TargetTable}' --TargetTdpId '$parms{TargetTdpId}' --TargetLogonMech '$parms{TargetLogonMech}' --TargetUserName '$parms{TargetUserName}' --TargetUserPassword '$parms{TargetUserPassword}' --TargetWorkingDatabase '$parms{TargetWorkingDatabase}' --SelectStmt '$parms{SelectStmt}'

Select statement in the .out file:

SELECT * FROM sourcedb.sourcetablename WHERE STATUS=A

Error in the Tdlaod log:

TPT10508: RDBMS error 5628: Column A not found in sourcedb.sourcetablename

Please let me know if any details are needed.

Thanks & Regards,
Hanu
 

 
 
 

feinholz 1234 posts Joined 05/08
23 Aug 2016

As I indicated, you have to write a script for this, not use Easy Loader.
We currently have a bug when using Easy Loader and the quotes are being stripped.
Thus, if you want to use a SELECT statement with embedded single-quotes, you will have to write a script.
(A TPT script, not a Perl script.)

--SteveF

Ghalia 11 posts Joined 12/15
25 Aug 2016

Hi,
 
After using LOCK TABLE MY_TABLE EXCLUSIVE
I have an update request
Then , I want to add something like RELASE LOCK from MY_TABLE after the update, in case of fail.
Is it possible in Teradata (14) please ?

Ghalia

You must sign in to leave a comment.