All Forums

Topics related to the Teradata Database, excluding database connectivity (e.g. JDBC) and extensibility (e.g. UDFs) . Typical topics are about SQL syntax and usage (SELECT, GROUP BY etc), database performance, use of database functions, comparison with / migration from Oracle or DB2, and generally why the Teradata Database rocks. For articles, blogs and more, see the Database section of the site.

You must login to post to the forums.
Bad data in table , need suggestion to isolate the problem Topic by HateOra 21 hours ago

Hello,
I have a unique situation here and I need your suggestions help to achieve it.
I have close to 10000 tables where I have a timestamp fields which (probably) has bad data(long story behind this).
Problem is : If I run select on any of the table which has bad timestamp value it fails with a  error.

27 views
4 replies, 7 min ago
How to identify Expired Partitions in Teradata v14.0 Topic by Dira 1 day ago #database#PPI

HI Folks,
We have requirement to identify Expired/About to expire Partitions [PPI] On Teradata V14.0 to prevent our batch jobs from failing.
So please help me out to figure out this issue.
 
Thanks.
DiRa
 

29 views
3 replies, 36 min ago
Execution modes in teradata Topic by Kishore_1 20 Jul 2010 execution modes

I know there are two execution modes in teradata:

1.Protected execution mode
2.Non-protected execution mode

What is the difference between these two modes of execution?
How to know which execution mode is set for the teradata database(what query)?
How to change from one execution mode to another(query)?

8672 views
11 replies, 1 hour ago
CAST Varchar Topic by Lee 2 days ago

This is a cross posting from a question in the Connectivity forum as I am not sure where the resolution fits - apologies if inappropriate:
We are having in issue in that a WebFocus report cannot read a view and we have narrowed it down to the columns below..

35 views
3 replies, 1 hour ago
Performance : Stored procedure Vs Bteq Topic by nimish_123 10 hours ago #performance

What is the difference between executing any DML(INSERT or DELETE) within stored procedure and outside of stored procedure?
Will there be any performance difference? If yes then why?

 

11 views
1 reply, 1 hour ago
Query :: Find tables which are accessed by users Topic by anand82 1 day ago pdcr, dbc.users

we are trying to find out the list of all tables being accessed by each user during DDL DML statements. we have access to PDCR.

42 views
4 replies, 4 hours ago
Tips to convert data on TPT Topic by rubenjdc 2 days ago

Hi everyone, i need load some data from a csv file to a teradata table using TPT.
I´m having some troubles on a field what has the following mask in the table: DD/MM/YYYYBHH:MI

97 views
5 replies, 12 hours ago
How to access a huge table. Topic by drmkd17 2 days ago Table access, #job #jobs #database #teradata #performance #tuning

Hi All,
 
I have a huge tbale having 323,456,1898 records. I need to join this table to other tables and also I have some views created on this table. But the Select to this table is really slow. 

24 views
1 reply, 13 hours ago
Bad character in format or data Topic by openbox 1 day ago

Hi I am a newbie in TD. I need your help in identifying the bad character on my table. I'm only expecting negative & positive numbers as my result.
 
column_abc CHAR(19) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT NULL ,
 
Thank you in advance.

31 views
4 replies, 13 hours ago
Temporal joins Topic by mmieszkowski 15 hours ago

What is the best way to join temporal and non-temporal tables together?
I have slowly changing dimensions type 2 that are all temporal (validtime) and facts (i.e. sales) that are normal tables.
Now I would like to join them all but not using AS OF. For examle:

6 views
0 replies
EXECUTE PROCEDURE access Topic by charleshugo 2 days ago

Hi,
1. I created a procedure in database dba
2. I set security of the stored procedure as "SQL SECURITY INVOKER"
3. user dbc is a memeber of role R_DBA
4. R_DBA holds execute procedure, select etc. rights on dba
5. dbc as a user holds none of rights on dba
when I logged into the box as dbc, I can not run procedures in dba with following error message:

32 views
2 replies, 1 day ago
Evaluating for Primary Index Topic by JustMe 2 days ago

Good morning,
I have a table that is highly skewed.  The currentperm per amp ranges from a high of  7,828,770,304  to a low of 1,762,582.528.  There are 42 amps on the system.
I would like to evaluate the data for a possible change to the primary index.
What is the best way to accomplish the evaluation?  Does Teradata have recommended queries for thi?
 

32 views
3 replies, 1 day ago
Stored Procedure doesn't recognize volatile table Topic by bcdjr1 19 Mar 2015 syntax, #STOREDPROCEDURE, #Volatile tables

I’m trying to do a test stored procedure on using volatile tables.  This is what I have:
 
CREATE PROCEDURE "DLPMC_PMCOE_DEV_LAB"."test_sp" ()
                DYNAMIC RESULT SETS 1
BEGIN
 
declare q1 varchar(50);

1738 views
3 replies, 1 day ago
Access Right Abbreviations Topic by foxbat 20 Jul 2007

Thought this might come in handy for those of you who had trouble understanding the Access Rights abbreviations displayed in the Data Dictionary.The script below maintains the full descriptions for the access rights and can be joined to DBC.ALLRIGHTS/ DBC.ALLROLERIGHTS./************************Set up Volatile Table**************************/DROP TABLE AccessRightsAbbv;CREATE VOLATILE SET TABLE AccessRightsAbbv( Description VARCHAR(50), AccessRight CHAR(2))PRIMARY INDEX(AccessRight) ON COMMIT PRESERVE ROWS;ins AccessRightsAbbv('CHECKPOINT','CP');ins AccessRightsAbbv('CREATE AUTHORIZATION','CA');ins AccessRightsAbbv('CREATE MACRO','CM');ins AccessRightsAbbv('CREATE PROCEDURE','PC');ins AccessRightsAbbv('CREATE TABLE','CT');ins AccessRightsAbbv('CREATE TRIGGER','CG');ins AccessRightsAbbv('CREATE VIEW','CV');ins AccessRightsAbbv('DELETE','D');ins AccessRightsAbbv('DROP AUTHORIZATION','DA');ins AccessRightsAbbv('DROP FUNCTION ','DF');ins AccessRightsAbbv('DROP MACRO','DM');ins AccessRightsAbbv('DROP PROCEDURE','PD');ins AccessRightsAbbv('DROP TRIGGER','DG');ins AccessRightsAbbv('DROP VIEW','DV');ins AccessRightsAbbv('DUMP','DP');ins AccessRightsAbbv('INSERT','I');ins AccessRightsAbbv('RESTORE','RS');ins AccessRightsAbbv('SELECT','R');ins AccessRightsAbbv('UPDATE','U');ins AccessRightsAbbv('CREATE DATABASE','CD');ins AccessRightsAbbv('CREATE USER','CU');ins AccessRightsAbbv('DROP DATABASE','DD');ins AccessRightsAbbv('DROP USER','DU');ins AccessRightsAbbv('ALTER EXTERNAL PROCEDURE','AE');ins AccessRightsAbbv('ALTER FUNCTION','AF');ins AccessRightsAbbv('ALTER PROCEDURE','AP');ins AccessRightsAbbv('CREATE EXTERNAL PROCEDURE','CE');ins AccessRightsAbbv('CREATE FUNCTION','CF');ins AccessRightsAbbv('EXECUTE FUNCTION','EF');ins AccessRightsAbbv('EXECUTE PROCEDURE','PE');ins AccessRightsAbbv('EXECUTE','E');ins AccessRightsAbbv('DROP TABLE','DT');ins AccessRightsAbbv('INDEX','IX');ins AccessRightsAbbv('REFERENCES','RF');ins AccessRightsAbbv('ABORT SESSION','AS');ins AccessRightsAbbv('MONRESOURCE','MR');ins AccessRightsAbbv('MONSESSION','MS');ins AccessRightsAbbv('SETRESRATE','SR');ins AccessRightsAbbv('SETSESSRATE','SS');ins AccessRightsAbbv('CREATE PROFILE','CO');ins AccessRightsAbbv('CREATE ROLE','CR');ins AccessRightsAbbv('DROP PROFILE','DO');ins AccessRightsAbbv('DROP ROLE','DR');ins AccessRightsAbbv('REPLCONTROL','RO');/***************SQL for AccessRights held by a user***********************/SELECT UserName, DatabaseName, TableName,ColumnName, CASE WHEN Abbv.AccessRight IS NOT NULL THEN Abbv.Description ELSE ALRTS.AccessRight END AS AccessRight, GrantAuthority, GrantorName, AllnessFlag, CreatorName, CreateTimeStampFROM DBC.ALLRIGHTS ALRTS LEFT OUTER JOIN AccessRightsAbbv AbbvON ALRTS.AccessRight = Abbv.AccessRight WHERE UserName='Josephm1' AND DatabaseName='E_COMPANYDB'Order By 2,3,4,5;/*************SQL for AccessRights held by a ROLE************************/SELECT RoleName, DatabaseName, TableName,ColumnName, CASE WHEN Abbv.AccessRight IS NOT NULL THEN Abbv.Description ELSE ALRTS.AccessRight END AS AccessRight, GrantorName, CreateTimeStampFROM DBC.ALLROLERIGHTS ALRTS LEFT OUTER JOIN AccessRightsAbbv AbbvON ALRTS.AccessRight = Abbv.AccessRight WHERE RoleName='E_COMPANYDB_DEVELOPER' AND DatabaseName='E_COMPANYDB'Order By 2,3,4,5;

31487 views
10 replies, 1 day ago
Secondary Index Concept Topic by bibhu23 2 days ago

Hi,
i have two Qus
1 ) In which scnario i will use Secondary Index  why not Primary Index?
2)select * from EMP_TAB -> which type of AMP operation 2 AMP,All AMP or FTS ?

19 views
1 reply, 2 days ago
Performance of the We do a MERGE into table HUB_ENCOUNTER_MESSAGE from Spool 18767. step of an insert Topic by lgscheurich 1 month ago insert, select, merge, table, multiset, identity

Team,
 
We have an insert statement that is inserting around 92 million records into a table based on a select statement from another table.  The select piece runs fine, but the following step takes a long time.
We do a MERGE into table HUB_ENCOUNTER_MESSAGE from Spool 18767.

466 views
12 replies, 2 days ago
3707 Error Topic by Gaurav Chordiya 4 days ago #Error 3707, OVER Partition by

SELECT

sales_segment,

pickup_yyyymm,

AVERAGE (Days) OVER (PARTITION  BY  sales_segment + pickup_yyyymm ORDER BY  sales_segment,pickup_yyyymm DESC)  AS mean_days,

MAX (Days) OVER (PARTITION  BY sales_segment + pickup_yyyymm ORDER BY sales_segment,pickup_yyyymm DESC) AS max_days,

71 views
5 replies, 2 days ago
Windows OLAP query PRECEDING ROWS bounded by column value Topic by Gupta_Pieeater 2 days ago

Can someone help with writing a query to return most recent previous row in a partition where the row has a column with a certian value but I want to return a different column value than the one which has the criteria.

19 views
1 reply, 2 days ago
Sort a cell value Topic by prasaddhoble 4 days ago sort function

Hello,
I want to sort the value inside a cell.
Is there a predefine function/UDF? or I need to write one?
for example :

column1

-------

BCA

RQP

YZX

 

sel sort(column1) as column2;

should give

 

column2

-------

ABC

PQR

XYZ

48 views
3 replies, 2 days ago
Need to calculate count of first name and last name based on Individual Names Topic by dwivedi.vivek589 2 days ago

Hi All,
I am new to teradata . I have query that i need to count INDV_NAME_COUNT based on logic below.
If Firstname and Last name is present then INDV_NAME_CT will be 2
If Firstname or Last Name is present ( means one of them present) then count should be 1
If neither present the count should be 0.

17 views
1 reply, 2 days ago
After Initial BLC Loading Topic by Kishore_1 2 months ago blc

As space saving initiative, we are applying BLC on certain tables in our Teradata system.

65 views
3 replies, 2 days ago
Difference between TCF & GCFR Topic by ankit2403 04 May 2015 GCFR, TCF

Hi,
Can anybody please let me know the difference between Teradata Control Framework and Teradata Global Control Framework?
Is GCFR just a new version of TCF with additional features? If yes, what new features?
Does GCFR work with TD 13?
Thanks,
Ankit
 

1924 views
3 replies, 2 days ago
Looking for help on full teradata database backup and restore steps. (preferably DSA-Netbackup) Topic by Washim N 2 months ago

Hello All,
I'm looking for help on full database backup and restore steps, which includes DBC, All users , accessrights , functions, procedures, Data, Join Indexes etc. to make the database back to normal.
I'm currently using DSA-Netbackup but any general BAR best practices/guidelines would help. Thanks
 

67 views
2 replies, 3 days ago
Common Table Expressions (CTE) Topic by Dominic77 23 Jul 2014
WITH FirstCTE (Attr1,Attr2) AS
(
 SELECT A.Attr1, A.Attr2 FROM DB.TBL_A A
 ),
SeccondCTE (FK_Attr1,Attr2,Attr3)  AS
(
 SELECT B.Attr1, B.Attr2, A.Attr1  
 FROM DB.TBL_B B
 JOIN FirstCTE A
  ON A.Attr1 = B.FK_Attr1
 )
SELECT * FROM SeccondCTE

Does TD support this kind if CTE? One CTE is referring to the prior CTE.
Thanks a lot!
 
 
 

4336 views
11 replies, 3 days ago
DSA Restore Job failing with Errorcode: 2640 Errormsg: "Specified table does not exist in All" Topic by Washim N 2 months ago

The DSA Back-up job was succuessful, but when I try to restore it from the same it gives the error: 2640 : " Specified Table does not exist in ALL".
Did anyone encountered this before? Looking for a root cause and solution for this issue.

50 views
2 replies, 3 days ago

Pages