Skewness in AMP

Database
Teradata Employee

Skewness in AMP

One job failed in production database LDW1LAP1 last night with comment ‘No more room in database LDW1LAP1’

 Here is a part of that logfile

…….. 

MPRINT(ETLS_SQL_INSERT_ROWS):   connect to TERADATA ( DEFER=YES CONNECTION=SHARED SERVER=TDPROD

USER=LDW1_ETL_USER PASSWORD= "{SAS002}D4344A274CE316A818CFE3E2159FB46859D7F5664A85E710" );

MPRINT(ETLS_SQL_INSERT_ROWS):   execute ( insert into LDW1LAP1.LA_VM_RATKAI_ALL (VAHNO, RATNO,

SOVTUNN, REKPVM, MUUTPVM, MUUTOPID, RATOPID, VAHINKOLAJI, OVMK, OMAVAST_PROSENTTI, OSALLINEN,

RATKOHDE, KOHDETEKS, OSAKER, OSAJAK, RATPVM, BONUSAL, VASTUU, ASSAAPVM, OIKAISU, VSOP17, VAKLAJI,

REGVALVONTA, HYLKSYY, RATKAISUTILA, La_id) select LA_VM_RATKAI.VAHNO, LA_VM_RATKAI.RATNO,

LA_VM_RATKAI.SOVTUNN, LA_VM_RATKAI.REKPVM, LA_VM_RATKAI.MUUTPVM, LA_VM_RATKAI.MUUTOPID,

LA_VM_RATKAI.RATOPID, LA_VM_RATKAI.VAHINKOLAJI, LA_VM_RATKAI.OVMK, LA_VM_RATKAI.OMAVAST_PROSENTTI,

LA_VM_RATKAI.OSALLINEN, LA_VM_RATKAI.RATKOHDE, LA_VM_RATKAI.KOHDETEKS, LA_VM_RATKAI.OSAKER,

LA_VM_RATKAI.OSAJAK, LA_VM_RATKAI.RATPVM, LA_VM_RATKAI.BONUSAL, LA_VM_RATKAI.VASTUU,

LA_VM_RATKAI.ASSAAPVM, LA_VM_RATKAI.OIKAISU, LA_VM_RATKAI.VSOP17, LA_VM_RATKAI.VAKLAJI,

LA_VM_RATKAI.REGVALVONTA, LA_VM_RATKAI.HYLKSYY, LA_VM_RATKAI.RATKAISUTILA, LA_VM_RATKAI.La_id from

LDW1LAP1.LA_VM_RATKAI as LA_VM_RATKAI ) by TERADATA;

ERROR: Teradata execute: No more room in database LDW1LAP1.

--------------------------->>>

Amp’s in target  table is lightly skewed but in  source table LDW1LAP1.LA_VM_RATKAI AMP’s are  more skewed.??

 Should I now create same Primary Index into both tables or how should this resolve???

3 REPLIES
Senior Apprentice

Re: Skewness in AMP

Hi,

 

Skew is a combination the  PI definition and the data values. If the error is in the target table then changing the source table PI won't have any effect. It is possible that your target database is just very nearly full and that this load fills up one AMP.

 

How full is your target database?

You say the target table is 'lightly skewed'. How skewed is 'lightly skewed'?

Is this set of data (in the source table) valid data? Check that it won't all end up in a one/two AMPs.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Skewness in AMP

source table LDW1LAP1.LA_VM_RATKAI is skewed .

and 2 or more amps in database:LDW1LAP1  CurrentPerm is larger than Maxperm for that i added some space-> so that job run succesfully.

SyntaxEditor Code Snippet

select VProc, CurrentPerm, MaxPerm from dbc.DiskSpace where DatabaseName='*******';

amp skewness.JPG

But as you know that is not permanent fix for the solution. for skewness.

 

How can i check that all data won't all end up in a one/two AMPs???

 

 

Highlighted
Senior Apprentice

Re: Skewness in AMP

Hi,

 

Use the following query to check for skewed data in your source table and how it will affect the target table:

 

In this query you need to provide the PI column(s) from the target table. If there are multiple columns the names should be separated by comma's.

SELECT HASHAMP(HASHBUCKET(HASHROW(target-table-pi-columns))) AS ampno
   ,COUNT(*)
FROM LDW1LAP1.LA_VM_RATKAI 
GROUP BY 1
ORDER BY 2 DESC;

Please note that this sql deliberately uses the PI columns from the target table but reads data from your source table.

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com