Error 52010 in store procedure 1

[jliang]

Hi,

When I create tables (perm tables and global temporary tables) in the store procedure and try to insert data to this table, sometimes, I got error 52010. But when I explain the SQL, there is no error. I do not understand why this error is happened for some create table and insert into statements.

I hope you can help me with this problem.

Thanks very much in Advance.

J

 

[jliang]

I also got another error T2646. When I explain this SQL, it is also fine. Can you help me with this one also?

Thanks

J

 

[dnoeth]

SP error 52010 maps to Teradata error 5319:
Two indexes with the same columns.
Explanation: The user tried to create an index containing
the same columns as an existing index.

Could you post the DDL statement?

2646: Did you check peak spool space per AMP? Maybe the data is skewed so only a single AMP reaches it's limit...

Dieter

 

[jliang]

Hi, Dieter,

Thanks very much for your help. I think it is data skew caused spool space problem. I created two temporary tables and insert data to these two tables. the problem is that these two tables' data contain most data with one cat_no. before I did not create frist temporary table, I did not meet T2646 problm, but I still had 52010 error. I will try to create first table as perm table instead of temporary table, do you think it will help for 2646 problem? but I do not know how can I solve the 52010 error. I created several tables with same column as index, but I think Teradata will distinguish them by table name. How it can be confused by Teradta? the following are the SQLs.


create global temporary table table1
(hh_no decimal(13,0),
cat_no smallint,
trips integer,
nms decimal(18,2),
netitems decimal(18,2) ) on commit preserve rows;

delete table1 all;

I use Dynammic SQL for the following insert statement:

insert into table1
select hh_no, cat_no, count(trips), sum(nms), sum(netitems)
from :table3
group by hh_no, cat_no;

I got 52010 error for above SQL.

Then I created this temporary table:

create global temporary table table2
(cat_no smallint,
cat_nm varchar(150),
cnt_tot decimal(18,2) ) on commit preserve rows;

DELETE table2 ALL;

use dynammic SQL for following statement. This sql causes 2646 problem.

insert into table2
select cat_no, cat_nm, sum(hh_no)
FROM cat_tbl a left outer join table2 b
on a.cat_no=b.cat_no
group by a.cat_no, cat_nm;

Thanks again for your help

J

 

[dnoeth]

You'll get rid of the 2646 if you specify a different Primary Index (with a better distribution) for that table, without PI it defaults to the first column:

create global temporary table table1
(hh_no decimal(13,0),
cat_no smallint,
trips integer,
nms decimal(18,2),
netitems decimal(18,2) )
unique primary index (hh_no, cat_nm)
on commit preserve rows;

create global temporary table table2
(cat_no smallint,
cat_nm varchar(150),
cnt_tot decimal(18,2) )
unique primary index (cat_no, cat_nm)
on commit preserve rows;



I checked the 52010 again, it maps to several errors similar to "xxx already exists".
Do you create the Global Temporary Table within your SP and drop it again? For that purpose there's the Volatile Table.

A Global Temporary Table is usually created only once outside the SP, then you can use it within your SP.


Dieter

 

[jliang]

I modified the temp tables and add primary index, but I still got both errores: 2646 and 52010. For this table:

create global temporary table table2
(cat_no smallint,
cat_nm varchar(150),
cnt_tot decimal(18,2) )
unique primary index (cat_no, cat_nm)
on commit preserve rows;

I can not control the data since we need data for people who has certain cat_no. this cat_no data will go to same AMP. Since we already have max spool space assigned to this user, I do not know how can I fix this problem now.

About the Volatile Table, we can not specify primary index for Volatile Table. It still can not solve the problem.

I do not know if there is a way to work around this problem.

Thanks so much for your help

J

 

[dnoeth]

I made a mistake:
You run out of spool *before* the data is merged into table2 -> the join is skewed.
Did you collect statistics on the join columns in both tables? Maybe the optimizer will use a different plan then avoiding the skewed spool.
How many rows are in both tables?
Maximum rows per cat_no value?


Btw, you can specify a PI on a Volatile table, too. But the bad news, volatile tables are stored in spool...

Could you post the whole SP?

Dieter

 

[jliang]

The SP ran out of spool space during insert into table2. I found out the insert SQL has outer join and count(distinct), both skew and spool space and ran out of spool space for one AMP. I modify the SQL to create subquery, and now it works fine. I also follow your insturction to collect statistics for the temporary table after insert. but I got another error T5343. Now if the system is not very busy, the sql would not get T2646 error.

My SP is very large, I copy the SQL that I met T2646 error,

INSERT INTO table2 '||
' SELECT a.cat_no, ' ||
' cat_nm, count(distinct gro_id_no) '||
' FROM tableA a left outer join '||
' table1 b '||
' on a.cat_no=b.cat_no '||
' where b.trips >0 ' ||
' group by a.cat_no, cat_nm;' as X

the count distinct get skew and make one AMP full. I modify it:

INSERT INTO table2 '||
' SELECT a.cat_no, ' ||
' cat_nm, sum(hh_cnt) ' ||
' FROM tableA a left outer join '||
' (select cat_no, count(distinct gro_id_no) hh_cnt'||
' from table1 '||
' where trips >0 ' ||
' group by cat_no) b' ||
' on a.cat_no=b.cat_no '||
' group by a.cat_no, cat_nm;' as X

I still do not understand why I got T5343 error now.

Thanks so much for your help.

J

 

[dnoeth]

5343 This statement is not allowed when the
target temporary table is already materialized."

"Create Global Temporary Table..." creates a template in the data dictionary, which can be used by any user with access rights to it. If data is inserted a session local copy of it is materialized: "sel * from dbc.alltemptables" shows all currently materialized GT tables...

If you submit a "COLLECT STATISICS ON table1..." that information is added to the template, this will fail if there's any materialized version of it. If you want that statistics only on your session local materialized version you have to user "COLLECT STATISICS ON TEMPORARY table1...".

But you're using GT tables like real tables and that's the main problem: If you have to run the SP over and over again create the GT table *once* outside of the SP, COLLECT STATS
on it and then just use it within your SP. And don't drop the template!

create global temporary table table1....;
collect stats on table1 column ...;

create procedure ...;
begin
/*** If it's already materialized, clear it ***/
delete from table1;

/*** then populate it, this materializes it, if necessary ***/
insert into table1 select...;

/*** recollect the statistics peviously defined on the template ***/
collect statistics on table1;

do what you have to do...

/*** If you don't need the data outside of the SP then clear/drop the session local version ***/
delete from table1;
or
drop TEMPORARY table table1;

end;

Dieter

 

[jliang]

Hi, Dieter,

I changed the collect stats statement with temporary. Now the T5343 error is gone. but I still get 52010 error. I DO create temporary table outside the SP, but in the SP, I still have create temp table statement and insert data to this temp table. I think maybe the create temporary table statement in SP caused this error. but I noticed I also got this error when I create a perm table inside the SP and insert data to this perm table. I do drop table first, and then create this perm table. Even though the SP got this error, but it still work fine. I want to know if there is a best way to fix 52010 error.

Thanks very much

J

 

[dnoeth]

You must be shure that there's no other object with the same name in the current default database. If the SP is run simultaneously in different sessions then trying to create a perm table might cause 52010 ...

Dieter