Error 3149

General
Enthusiast

Error 3149

Hi,

when we execute a query with many left joins in SQL assistant,

we are facing 3149 error code : TDWM filter violation for query request: For rule name '3HR_Alert'

Any suggestion on these error?

Thanks,

Karthik. N

Tags (1)
9 REPLIES
Senior Supporter

Re: Error 3149

From the documentation:

Explanation: This error occurs if a query request is

aborted by Teradata TDWM for a Filter rule violation.

Check currently active Teradata TDWM rules.

Remedy: Conform to the TDWM rules or contact your

TDWM Administrator.

You need to talk to your DBAs whats behind the rule to understand what you might need to change.

Enthusiast

Re: Error 3149

Thnaks Ulrich,

one more doubt I have is ,

when I select the column used in the join in the select part explain plan is showing more time.

 ex:

SEL col1,col2,col3,col4 
FROM tableA a
JOIN tableB b
ON a.col1=b.col1
JOIN tableC c
ON a.col2=c.col2
AND so ON

we have a query with some 15 left joins, explain plan is showing more time when I select the fileds used in the joins.

Is there any reason behind the explain plan taking more time when selecting those columns?

Thanks,

Karthik. N

Senior Supporter

Re: Error 3149

Did the plan itself changed? Or only the estimates?

What did you select before? Select *?

Enthusiast

Re: Error 3149

There is not much change in explain plan. other than the new field added.

before  the query is like

SEL col8,col9,col10
FROM tableA a
JOIN tableB b
ON a.col1=b.col1
LEFT JOIN tableC c
ON a.col2=c.col2

where  col8,col9,col10 are not part of join condition. 

after the query is like 

SEL col1,col2,col8,col9,col10
FROM tableA a
JOIN tableB b
ON a.col1=b.col1
LEFT JOIN tableC c
ON a.col2=c.col2

 where col1 and col2 are part of join condition.

sencond select showing more time in explain plan.

Senior Supporter

Re: Error 3149

"There is not much change in explain plan." So same steps and order in both?

Can it be related to bigger estimated spool files? 

Can you check the explain output.

Enthusiast

Re: Error 3149

Yes I verified the explain plan, they are same. steps and order:

 both has 26 steps and in same order.

without the field used in join:

to be 148,374,004 rows (247,932,960,684 bytes).  The

     estimated time for this step is 6 minutes and 14 seconds.

be 111,280,503 rows (495,977,201,871 bytes).  The estimated time

     for this step is 21 minutes and 42 seconds.

with the field used in join:

 to be 148,374,004 rows (248,674,830,704 bytes).  The

      estimated time for this step is 6 minutes and 15 seconds.

  be 111,280,503 rows (497,868,970,422 bytes).  The estimated time

      for this step is 3 hours and 31 minutes.

Thanks,

Karthik N

Senior Supporter

Re: Error 3149

no idea what might be the root caus for that.

Could be that optimizer internal thresholds are responcible for that. But thats only a guess. 

Maybe someone from Teradata can answer that?

Enthusiast

Re: Error 3149

Thanks Ulrich.

These is point I noticed.

There was a distinct in the select clause.

Optimizer is taking time, when there is distinct in the select on the columns used in the join.

Senior Supporter

Re: Error 3149

So the select you shared above was not fully what you did.

Adding columns to a distinct would clearly mean longer time to calculate...