Zero records are fetching when using where statement in TPT

Tools & Utilities
Enthusiast

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

21 REPLIES
Teradata Employee

Re: Zero records are fetching when using where statement in TPT

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
Enthusiast

Re: Zero records are fetching when using where statement in TPT

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

Teradata Employee

Re: Zero records are fetching when using where statement in TPT

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
Enthusiast

Re: Zero records are fetching when using where statement in TPT

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

Teradata Employee

Re: Zero records are fetching when using where statement in TPT

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

Enthusiast

Re: Zero records are fetching when using where statement in TPT

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

Teradata Employee

Re: Zero records are fetching when using where statement in TPT

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
Enthusiast

Re: Zero records are fetching when using where statement in TPT

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

 

Enthusiast

Re: Zero records are fetching when using where statement in TPT

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