TPT Stream failing with Macro does not exist

Tools & Utilities
spk
Enthusiast

TPT Stream failing with Macro does not exist

Hi,

We are using TPT Stream to load from multiple jobs into the same target table.

Two jobs start around the same time. One of the jobs completes in say 2 mins and it immediately drop the macro that it created for the stream operation.

The second job at the same time fails saying macro not found.

From looking at the query log table, two macros are getting created. But one macro is dropped in 7 seconds.

We kidna infer that both the jobs are using the same macro as its the same table. And when the first job completes, the macro gets dropped.

 

Have any of you faced this issue? Are we looking at this incorrectly.

Please advise.

Tags (3)

Accepted Solutions
Teradata Employee

Re: TPT Stream failing with Macro does not exist

When a utility job initiates, it connects a "control" session (used for DDL requests, etc.) that requests a new LSN. That LSN is then passed / associated with the "auxiliary" (checkpoint) and all "special" (data transfer) sessions that make up the same job. In your case, it seems that the first/only operator instance of the second job is not being correctly recognized as a new master thread / separate job, and it's bypassing the initiation process. Whether that's due to an issue with TPTAPI or with Informatica isn't clear.

 

I'd suggest you open incidents with both Teradata and Informatica support for assistance. They may need additional trace details to isolate the problem.

1 ACCEPTED SOLUTION
8 REPLIES
spk
Enthusiast

Re: TPT Stream failing with Macro does not exist

With a little bit more detail.

 

When we load into a single Teradata table from more than one job using tpt stream, we are getting the below error at random.

 

2018-05-23 11:39:04.570 <TASK_3342-WRITER_1_*_1> SEVERE: [TD_1097] Putbuffer has returned error status [3824].

2018-05-23 11:39:04.570 <TASK_3342-WRITER_1_*_1> SEVERE: [TD_1013] Teradata Parallel Transporter API recieved error code [3824]. For more information, look up the error code in the Teradata Parallel Transporter API documentation.

2018-05-23 11:39:04.570 <TASK_3342-WRITER_1_*_1> SEVERE: [TD_1009] Formatting the Teradata Parallel Transporter API error message...

2018-05-23 11:39:04.570 <TASK_3342-WRITER_1_*_1> SEVERE: [APPSDK_Msg_1762] Type:(Teradata DBS Error), Error: (Macro 'IDQPDISCUST.M18143_41779_82895_000_000' does not exist.)

2018-05-23 11:39:04.570 <TASK_3342-WRITER_1_*_1> SEVERE: [TD_1072] PutData function call failed.

2018-05-23 11:39:04.570 <TASK_3342-WRITER_1_*_1> SEVERE: [TD_1015] Could not load data to the Teradata database. See the previous messages in the session log for more information.

 

We ran three jobs concurrently. We could see that for these three jobs only two macros were created (by tpt api for use in stream operation).

Of these three, two loaded data in parallel to this single target table.

The third job did not load any data. This created the macro for stream and dropped it immediately (in 7 seconds). No problems with this one.

 

First job that loaded data completed at 11:39 and that seems to have dropped the macro.

The second job that loaded data which was running and loading data into this table at this time, failed immediately.

 

Looks like these two jobs that started at the same time, loading data into the same table, seems to have used the same macro.

From the looks of it, macro created by one job seems to have been re-used by the other job.

 

23/05/2018 11:39:04

Drop Macro         

DDL Alter

DROP MACRO IDQPDISCUST.M18143_41779_82895_000_000;

23/05/2018 11:36:19

Create Macro       

DDL Create

CREATE MACRO IDQPDISCUST.M18143_41779_82895_000_000 (DQ_Rule_SK(INTEGER),DQ_Rule_ID(INTEGER),DQ_As_Of_Date(CHAR(10)),Book_Name(VARCHAR(300), CHARACTER SET UNICODE),DQ_Data_Src_Obj_Type(VARCHAR(300), C

23/05/2018 11:34:28

Drop Macro         

DDL Alter

DROP MACRO IDQPDISCUST.M18143_41661_82885_000_000;

23/05/2018 11:34:21

Create Macro       

DDL Create

CREATE MACRO IDQPDISCUST.M18143_41661_82885_000_000 (DQ_Rule_SK(INTEGER),DQ_Rule_ID(INTEGER),DQ_As_Of_Date(CHAR(10)),Book_Name(VARCHAR(300), CHARACTER SET UNICODE),DQ_Data_Src_Obj_Type(VARCHAR(300), C

 

 

Also, from the submitting job point of view, we don’t have any way to give the macro name.

Macro name seems to be generated in random by the API. Per the TPT API specification, we can give the macro database name, but not the actual macro name.

 

Macro name -> M18143_41779_82895_000_000:

The first part is the year number and the day of year.

The second part is the seconds from the beginning of day.

I am not sure how the next part is calculated.

 

The first and second part is common between the two mappings.

Highlighted
Teradata Employee

Re: TPT Stream failing with Macro does not exist

Macros are created/dropped by the Stream operator.

Every TPT (or TPTAPI) job creates a unique macro name by default.

Even 2 jobs that are started at the exact same time would have unique macro names because part of the macro name is the LSN number, and the DBS assigns a unique LSN number to each job.

If you would like to create and provide your own macro, that is fine, you just provide the EXECUTE command with your macro name and we will use that instead of taking the DML statements and creating the macro for you.

Also, if you wish to keep the macros around after the job completes, there is a DropMacro option that can be set to "no".

Be careful, though, as the number of macros created can grow quickly and it would be up to the user to manually drop them later.

 

-- SteveF
Teradata Employee

Re: TPT Stream failing with Macro does not exist

Is this Informatica on Unix / Linux, using STREAM via TPTAPI?

If so, did you set the environment variable THREADONOFF=1 to enable CLIv2 multi-threaded support?

spk
Enthusiast

Re: TPT Stream failing with Macro does not exist

Hi,
 
Thanks for the inputs. Yes it is Informatica on AIX. Yes, I did suspect the thread setting and I confirmed twice that this is set. 
I will build a mapping to display the environment variables and ensure that the process running the job is seeing this parameter correctly. 
 
[mmdev] /var/opt/sw/mm/backup $ set | grep THRE
AIXTHREAD_STK=2097152
THREADONOFF=1
[mmdev] /var/opt/sw/mm/backup $
 
Thanks again good people.  
 
Cheers,
SPK. 
spk
Enthusiast

Re: TPT Stream failing with Macro does not exist

Hi,

I ensured that the THREADONOFF=1 is set and it is picked by the job correctly.

 

@SteveF, Thanks for your inputs. Below are my findings:

 

Regarding LSN number, could these numbers be re-used? 

In JDBC spec, looks like there is an option to re-use LSNs (CONNECT_FUNCTION).

https://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html

Could something like this be happening here?

 

We are using Informatica to invoke TPT and we don't have option to give a macro or option to NOT drop macro. 

May be these are newer options and have not been exposed by Informatica connector.

 

I enabled tracing on these TPT connections and saw three trace files being created. 

From the trace files also, we can see that two sessions are using the same macro.

 

[mmdev] /var/opt/sw/mm/backup/runlog1 $ grep M18144 *
exception_stream1.trc:TESTPACKFACTOR:
exception_stream1.trc:**** 14:22:37 Dropping macro 'IDQPDISCUST.M18144_51571_95895_000_000'
exception_stream1.trc:**** 14:22:39 Macro 'IDQPDISCUST.M18144_51571_95895_000_000' has been dropped

 

exception_stream2.trc:PX_EVENT: DBS text: 'Macro 'IDQPDISCUST.M18144_51571_95895_000_000' does not exist.'
exception_stream2.trc:PX_EVENT: DBS text: 'Macro 'IDQPDISCUST.M18144_51571_95895_000_000' does not exist.'
exception_stream2.trc:STREAM_OPERATOR: TPT10508: RDBMS error 3824: Macro 'IDQPDISCUST.M18144_51571_95895_000_000' does not exist.
exception_stream2.trc:**** 14:22:40 TPT10508: RDBMS error 3824: Macro 'IDQPDISCUST.M18144_51571_95895_000_000' does not exist.


exception_stream3.trc:TESTPACKFACTOR:
exception_stream3.trc:**** 14:18:05 Dropping macro 'IDQPDISCUST.M18144_51481_95894_000_000'
exception_stream3.trc:**** 14:18:05 Macro 'IDQPDISCUST.M18144_51481_95894_000_000' has been dropped
[mmdev] /var/opt/sw/mm/backup/runlog1 $

 

I queried dbc.querylog and there as well, only two sets of create macro and delete macro statements show up.

They show with different LSNs but the third job does not have an entry at all.

 

Looks like the TPT API has the smarts to detect parallel loads that are initiated at the same time?

 

I don't know what I am missing here.

Teradata Employee

Re: TPT Stream failing with Macro does not exist

When a utility job initiates, it connects a "control" session (used for DDL requests, etc.) that requests a new LSN. That LSN is then passed / associated with the "auxiliary" (checkpoint) and all "special" (data transfer) sessions that make up the same job. In your case, it seems that the first/only operator instance of the second job is not being correctly recognized as a new master thread / separate job, and it's bypassing the initiation process. Whether that's due to an issue with TPTAPI or with Informatica isn't clear.

 

I'd suggest you open incidents with both Teradata and Informatica support for assistance. They may need additional trace details to isolate the problem.

Teradata Employee

Re: TPT Stream failing with Macro does not exist

That is an Informatica issue.

 

Fred explained about the LSN, but Informatica has probably not handled the different jobs in different threads properly and treating 2 different jobs as one.

 

This is not a TPTAPI issue (TPTAPI is just a set of library routines that are called and controlled by an application; TPTAPI internally cannot handle 2 different jobs with the same LSN).

 

Another thing it could be is that Informatica is passing the wrong LSN between the instances of 2 of the jobs.

 

As Fred explained, the master SQL sessions asks the DBS for an LSN. The master instance of the TPTAPI job then passes that LSN to the other instances of the job; that passing of the LSN is done by the application (TPTAPI gives a buffer of data to the application and the application then gives it to the other instances of the operator).

 

It is possible that Informatica is not passing that information to the correct instances.

 

 

-- SteveF
spk
Enthusiast

Re: TPT Stream failing with Macro does not exist

Thanks a lot Fred and Steve. I actually kinda inferred this yesterday and highlighted this to Informatica.

A case was already open and now this should give them the right place to look.

 

Thanks a lot again guys. You both are awesome.

I will report back on the progress. 

 

Below is what I told them:

LSN seems to be the distinguishing factor.

In looking at the “Teradata Parallel Transporter - Operator Programmer Guide”, 2435.pdf,

There seems to be option to get the LSN and create session with LSN.

May be if an existing running STREAM session exists, we are using the LSN that was already source?

 api docu.jpg