All Forums

For topics on Teradata’s Unified Data Architecture and associated enterprise-level features and products. Potential topics could include integrations in and around Teradata, Aster, and Hadoop as well as supporting technologies like Connectors, QueryGrid, Backup/Restore, Multi-Systems, and the Unity product suite (Director, Loader, Data Mover, Ecosystem Manager).

You must login to post to the forums.
Query performance.... Topic by l.k 28 Dec 2010 query performance....

Hi All,

The below query is poorly performing while executing....Query is given below:


1 reply, 10 years ago
Table Query Topic by marcmc 16 Feb 2007

Hi, I am new to Teradata.I am looking for a table that holds the text of a Stored Procedure.In essence I am trying to create a query that will show me the dependant objects if I pass the query a table name or string that is contained in the text of each dependant object.For example: If the tableName 'Marc' exists in 2 stored procedures I want to return the following:SProc_Name---------------------sp_Marcsp_Marc1Is their a way to do this via SQL or a utility?ps: I used to use a join between the Sysobjects and Syscomments table in SQLServer.Thanks in advance.

4 replies, 10 years ago
How to take backup of a Teradata Database? Topic by abhishekr 17 Jan 2011


I have Teradata Database installed on the server. I need to take backup of the whole database in a file... Like .dmp in Oracle or .mdf in SQL Server.

Thanks in advance,

Best Regards

1 reply, 10 years ago
Performance Tuning........... Topic by l.k 24 Dec 2010 performance tuning...........

Hi All,
The below query is taking more than 1 hour 30 mintues to produce the result and cosuming more sumIO also.


Qualify Rank(IASD.Invest_Account_Summary_Dt) = 1;

6 replies, 10 years ago

I have the following case statement that works just fine in SQL Assistant..but fails when I move to batch JCL.. and gives me a 5407 error. (Failure 5407 Invalid operation on an ANSI Datetime or Interval value)Any ideas. What I need to do is adjust a reported time into a new time zone value.,TZ,CAST((ADTE || ' ' || CAST((ATME*100) AS TIME)) AS TIMESTAMP(0)) AS AUD_DTME,CASE WHEN TZ = 'NT' THEN ( AUD_DTME - interval '02:30:00' hour to second) WHEN TZ = 'TT' THEN ( AUD_DTME - interval '02:00:00' hour to second) WHEN TZ = 'ET' THEN ( AUD_DTME - interval '01:00:00' hour to second) WHEN TZ = 'CT' THEN (AUD_DTME) WHEN TZ = 'MT' THEN (AUD_DTME + interval '01:00:00' hour to second) WHEN TZ = 'PT' THEN (AUD_DTME + interval '02:00:00' hour to second) WHEN TZ = 'AT' THEN (AUD_DTME + interval '03:00:00' hour to second) ELSE AUD_DTME END AS TCS_DTME

7 replies, 10 years ago
Suggestion needed Topic by smith43017 17 Nov 2010 teradata sql question

When we are inserting the data from one table to another table with join statement can we commit for n no of rows ???

0 replies
New to Teradata Topic by smith43017 15 Nov 2010 sql

What is wrong with this SQL

UPDATE test.Summary1
SET test.Summary1.c1 = test.s_table.C1 and
test.Summary1.c2 = test.s_table.C2
WHERE test.Summary1.acc_nbr = test.s_table.AccessNbr ;

2 replies, 10 years ago
PARTITION INFORMATION Topic by rluebke 13 Feb 2009

Does anyone know where in the DBC partition information, that is not a PPI, is kept? I am trying to find the columns associated with a given partition on a table progamatically. A PPI's columns are kept in DBC.INDICES, but I can't find partition information on non-PPI partitions. R

3 replies, 10 years ago
SQL problem - selecting an earlier date in a join Topic by barcoo 12 Sep 2008

Hi,Anyone care to help with this please.TABLE1ClientId = 1Date= 20080103ClientId = 2Date = 20080103TABLE2ClientId = 1Date = 20080103Value = $1.20ClientId = 2Date = 20080101Value = $1.30ClientId = 2Date = 20080102Value = $1.40Now I wish to join TABLE1 to TABLE2 so that I can retrieve the value field in TABLE2 (plus the clientid and date fields from TABLE1)Thats easy TABLE1.clientid = TABLE2.clientid and = Based on this I will lose clientid 2 as the date fields cannot join but it will return clientid 1 which is correctWhat I want to do is join to the TABLE2 based on the clientid and, if the date fields dont join, then return the maximum date possible where the < and give me the value for this date instead.So the expected return is clientid = 1date = 20080103value = $1.20clientid = 2date = 20080102value = $1.40Can this be done?CheersChris

15 replies, 10 years ago
TERA DATA WARE HOUSE MODEL Topic by jayreddy 27 Sep 2010 teradata data modeler

My client is into banking domain ,Now we want to develop DATA ware house in TERA DATA.

Now the question is I need to decomission my existing application which is also a data ware house built on STAR schema model running on mainframes.

1) how can I replace the existing system and built a data ware house in TERA DATA .

2) where can I place my existing functionality either in integrated layer or untegrated layer .

3) What challanges i need to consider before decomisisoning existing data mart

Please let me know if you need more information and your comments


1 reply, 10 years ago
TASM Behaviour Topic by umer 25 Aug 2010 tasm performance.

i am facing issue with TASM behaviour. i realized that tactical workload, with rp of 50%, give results in 2-3 secs, while same workload at busy time give result in 30-40 secs.

i need to know if system is running 100% and tactical query hit system then how much time is reuiqred for tactical to take some cpu back.

8 replies, 10 years ago
Converting Hot Standby Node to Active Node Topic by murphrp 11 Aug 2010 hsn hot standby node

We're considering hardware changes and wondered about the option of a HSN for now but converting it to active in the future when we need more active capacity. I understand that if we did that, we'd be sacrificing full performance continuity and accepting degraded performance continuity.

3 replies, 10 years ago
example for automated solution to upload data using teradata macros Topic by TDlearner 23 Sep 2010

Can anybody help me with an example of teradata macro to upload data directly into teradata database in batch processing?

0 replies
Oracle Informatica Teradata Topic by jerome 14 Apr 2008

Hi Everyone,I'm going to work on an ETL project -where source is oracle ,target is teradata and ETL tool to be used is informatica.There are two levels -one is load into staging(staging is also teradata) and second is loading into target tables.I query the oracle source tables and load into staging area.Which of the approach is good -1.create a one to one mapping to do this or 2.Use any of the tools offered by Teradata -like Mload,Tpump,etc in informatica and do it.Please tell me the Pros and Cons of these two approaches.I've been told to use the first method(one to one mappings).Please advice on the second level as well ( from staging to target) whether to use one to one mapping or teradata tools.I'm really afraid because there is an automatic preimary index getting created in Teradata tables and this lead to rejection of records in some cases.Please clarify.Thank you all in advance.-Thanks Jerome

7 replies, 10 years ago
Drop Table "if exists" Topic by Portuga 07 Feb 2007

Hi,I can use the Drop Table "if exists" command in my SQL.but when I use this command in Teradata I get an error message.Is there any way I can drop a table only if it already exists in Teradata SQL Assistant 7.1?Thanks

5 replies, 10 years ago
How to enable Japanese language Support in Teradata Express Edition 13.0 Topic by markxue 27 Jun 2010

It said in the PDF doc of <>:
During the sysinit process, the following prompt appears:
Enable Japanese language support?
To optimize the database for Japanese language support, answer YES. Otherwise, answer NO.
If you answer NO, the Teradata Database uses standard language support.

4 replies, 10 years ago
Oracle Reports connectiing to TERADATA Topic by sweet_melody 17 Aug 2009

hi, i just wanted to know if it is possible for the Oracle Report builder to connect to Teradata. That means that the data to retrive by the Oracle Report will be from Teradata. Please tell me if it is possible. Thank you.If not, what are other tools that can be used? Thanks

12 replies, 10 years ago
Proprietary versus Open source ETL tools Topic by Jstatic 12 Aug 2009

Hi, As of today, we are still enjoying our Informatica tool but in a few months we will need to change. Basically we do not use our software at its full capacity and don't feel we need it anymore. So we are trying to find a less expensive solution that would have the same features (or almost...). We are looking at less expensive tools and Open source software. We have pretty much targeted a few companies and would like to know which ones would be the better solution compared to Informatica. -Apatar -Expressor -Pentaho -Talend Some are paying software, some are open source (but not free...), so i'm asking you to know which is the best software on the market.Thanks.

2 replies, 10 years ago
Data synchronization between two offices Topic by Jstatic 04 Aug 2009

Hi everyone, After building a database for our growing company, we have a problem with our data and its synchronization. Our company has a rather small, but growing, book warehouse and sells books online. We have been able to deal with orders and stock because sales were not that large. But today we sell more and more books and the addition of a database means we need to synchronize our data from the warehouse database to the main office one, mainly to be able to know what kind of stock we have. I think we will have to go with a batch syncing scheduled twice a day for now. We have been dealing with Talend open studio so far and enjoy the product. Do you think Talend will be able to stand that type of operation? What kind of software could be used in this case? Thanks all!

2 replies, 10 years ago
BULK INSERT in Teradata Topic by aolympio 22 Apr 2009

Hi guys!Is there a command that performs a similar function as BULK INSERT(SQL statement) in Teradata?Thanks for help,Anderson

6 replies, 10 years ago
Information/Data Architect Data Vault Modeling with Teradata Topic by burkereb 06 Apr 2010 teradata, vault

Any activity to report on? I'm begining to plan the process of migrating to Data Vault from relational and dimentional, and from Informatica to Talend.

1 reply, 10 years ago
Teradata - Oracle - Query Rewrite - Qualify & Rank Topic by prlmurali 24 Mar 2010 oracle, qualify, rank

I am working on Teradata to Oracle migration project.

Select frm_crncycd,to_crncycd,exch_rate from exchange_rate
group by 1,2
qualify(rank(frm_dt)) =1

Can someone explain what is the behavior of above query.
So that i can rewrite the query in Oracle.

4 replies, 10 years ago
joining table A to table B only once where condition satisfied Topic by barcoo 12 Feb 2010

Hello,Please help where possible. I have been asked to determine if this work can be done in teradata, otherwise the work goes to another team here at work. None of us here at work know of a way but we are not experts. :)Please read my example below along with my desciption. I have tried to be as clear as possible but will clarify if needed.I am trying to track the sale history for people. In the example below someone buys a quantity of 100, then buys another 100, then sells 100, then sells 100.What I want to do is track the 'SELL' history and find out which 'BUY' it relates to. The concept here is first 'SELL' out was the first 'BUY' in. In this case the first 'SELL' of 100 relates to the first 'BUY' of 100. The next 'SELL' of 100 relates to the second 'BUY' of 100.Please see the example of the BUY and SELL tables I have. Keeping it simple for now - If I join TABLE B to TABLE A on CLIENT ID and AMOUNT, I will get 4 records. What I am attempting to do is produce only two rows - The idea being that once the first SELL record joins to the first BUY, that first BUY record can no longer be found when the second SELL attempts to join on CLIENT ID and AMOUNT. It should find the second BUY record.ExampleI have TABLE A for a single CLIENT ID. The table has two records, four fields in each record. CLIENT ID = 1, AMOUNT = 100, MOVEMENT = 'BUY', DATE = 01/01/2000CLIENT ID = 1, AMOUNT = 100, MOVEMENT = 'BUY', DATE = 01/02/2000I have TABLE B for a single CLIENT ID. The table has two records, four fields in each record. CLIENT ID = 1, AMOUNT = 100, MOVEMENT = 'SELL', DATE = 01/03/2000CLIENT ID = 1, AMOUNT = 100, MOVEMENT = 'SELL', DATE = 01/04/2000Expected output is two rows returning everything from TABLE B and two fields, MOVEMENT and DATE from TABLE ACLIENT ID = 1, AMOUNT = 100, MOVEMENT = 'SELL', DATE = 01/03/2000, MOVEMENT = 'BUY', DATE = 01/01/2000CLIENT ID = 1, AMOUNT = 100,MOVEMENT = 'SELL', DATE = 01/04/2000, MOVEMENT = 'BUY', DATE = 01/02/2000This is a simple example. Often the AMOUNTS are not the same and I have to look at remainders etc but this is a starting point to figure out simply if this is possible.Thank you in advance for any assistance and direction.Chris.

16 replies, 10 years ago
Failure 3156 Request aborted by TDWM. Topic by Toshu 06 Oct 2009

Hello All,I started getting "Failure 3156 Request aborted by TDWM. Exception criteria exceeded: AMP CPU Skew. Statement# 1, Info =0Input row number = 1 *** Total elapsed time was 10 minutes and 27.21 seconds."Can anyone help me in this?Regards,Toshu

1 reply, 10 years ago
Teradata Virtual Storage. Topic by Random_Thought 18 Mar 2010 teradata 13, tvs, virtual storage


I am interested in any comments from users of Teradata regarding Teradata v13 Virtual Storage. Has anyone successfully implemented it in a live environment? an Active EDW? I am interested in real life experience rather than test setups or strictly controlled POC environments (Im a committed cynic!)

1 reply, 10 years ago