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.
DW Database Designer III Topic by Silas 19 Jan 2006

Job Description : Transforms a Logical Data Model into a high performance Teradata database design according to business and technical requirements. Creates and tests table structures, indexes, views, etc. Optimizes performance of the database based on user access and data loading requirements. Optimizes data warehousing environments based on specific requirements, including physical database design, end-user access (query) structure and performance, as well as data transformation processes and procedures. Requires 8-10 years of related experience.; Additional Job Description: Expert Level Teradata v2r5 consultant to assist in the following: - Tune Queries for optimization - Address problematic user queries - Continued Execution of Workload Management Recommendations - Provide user mentoring on best practices in SQL development - Batch Analysis & Optimization - Analyze batch processes to include timings and data volumes - Assist with setting up FAQ’s on internal customer website. May be traveling to Dallas for some of the work. Atlanta anticipated to be the primary work location.

0 replies
Parameterized queries vs using queries Topic by vinodG 20 Jan 2006

1.Can any one tell me the exact difference between Parameterized query and Using query.2.Can any one tell me the difference between P mode and S mode requests??ThanksVinod

0 replies
Populating QCD from DBQL Topic by Krishan.R 25 Jan 2006

Hi,I need your help on this one. How you populate your QCD database from DBQL database? What I am looking for is to populate the QCD databases with data from DBQL(Demographics/Statistics/Plan etc). This data in QCD will be used to analyse all the CPU intensive queries using Statistics / Index wizard. Your help would be highly appreciatedKrishnan.R

0 replies
Teradata V2R5.1 on Windows 2000 would not come up. Topic by A-2368 06 Feb 2006

I have installed Teradata V2R5.1 on Windows 2000 Advanced Server. The componenents i have installed are the Cliv 2.04.07, Teradata Database(TNT., piom. Connectivity Driver), and BTEQ along with Bynet. I am installing all this on a virutal machine. The BYNET network connection could not get an IP Address from the DHCP, hence i had to configure it manually. However, when i go in Teradata Multitool i find both PDE and the database are not started. When trying to start them, i get an hour glass that never comes back. Starting the Teradata RDBMS Service from the services panel, the service comes up for a moment and then goes down.I haven't seen anything like BYNET in V2R5 or earlier versions of Teradata. I presume it is something new in V2R5.1 onwards which i am unalbe to configure correctly and hence the database does not come up. Any help would be appreciated.Thanks,A

0 replies
Problem executing recursive query from Sql Assistant Topic by 07 Feb 2006

HiWhen i execute the below mentionet sql from BTEQ it works fine however when it is executed from Sql Assistant it gives the error "3706: Syntax error: Expected something between the begenning of the request and the 'with' keyword".Please assist.WITH RECURSIVE All_Trips(Origin,Destination,Cost,Depth) AS(SELECT Origin, Destination, Cost, 0FROM FlightsWHERE Origin = 'LAX'UNION ALLSELECT All_Trips.Origin, Flights.Destination, All_Trips.Cost + Flights.cost, All_Trips.Depth + 1FROM All_Trips INNER JOIN Flights ON All_Trips.Destination = Flights.Origin AND All_Trips.Depth < 3)SELECT *FROM All_Trips;Regards

0 replies
Scheduing a stored procedure Topic by 28 Feb 2006

Hi,Is there is a way to schedule the execution of a Stored Prodecure or Macro in Teradata ?My basic requirement is to execute a Stored Procedure / Macro daily.Akhil-

0 replies
Compound FK allows nulls - is that normal behaviour? Topic by MNagat 03 Mar 2006

Hello TeraData experts,I've been surprised with the following behaviour of TD. Please see this example. Here are my tables:create table mnagat.A ( a1 INTEGER, a2 INTEGER)UNIQUE PRIMARY INDEX ( a1, a2 )create table mnagat.B ( b1 INTEGER, b2 INTEGER, FOREIGN KEY ( b1, b2 ) REFERENCES A ( a1 , a2 ))Statements likeinsert into mnagat.B values (1, 1)are rejected as expected when there's no such record in A.But TD allows all these inserts, even when there's not any row on the A table:insert into mnagat.B values (null, null)insert into mnagat.B values (null, 1)insert into mnagat.B values (1, null)Shouldn't they be rejected by FK constraint (code 2700)?Thanks.

0 replies
fastexport to fastload Topic by 06 Mar 2006

Gurus and all,I am using fastexport to write data to a file and the default MODE = INDICATORS and the default FORMAT = I am not sure how to use this output file in the fastload script that i created.FastExport uses some 2 byte integer bytes for format indicator, record start indicator and End of record markes, and the default delimiter it use is not PIPE.( i am not sure what it uses)can some one send an example of a fastload script that uses a file written by a fastexport using fastload format as the output format.Here are the fast export and fast load scripts that i have.FAST EXPORT..LOGTABLE DEVDATA.FEROZ_EMPFASTEXP_LOG;.Logon dbc/userid,#######;.BEGIN EXPORTSESSIONS 2;.EXPORT OUTFILE C:\EMP\EMP_OUTPUT.TXTFORMAT fastload;SELECT emp_no (varchar(10)),fname (varchar(20)),lname (varchar(20)),mgr_emp_no (varchar(10)),DOB (varchar(10)),start_date (varchar(10)),dept_no (BYTEINT)FROM DEVDATA.FEROZ_EMP;.END EXPORT;.LOGOFF;FAST LOAD SCRIPT:sessions 2;Logon dbc/userid,#######;set record vartext DISPLAY_ERRORS;DEFINE emp_no (varchar(10)),fname (varchar(20)),lname (varchar(20)),mgr_emp_no (varchar(10)),DOB (varchar(10)),start_date (varchar(10)),dept_no (varchar(3))FILE=c:\EMP\EMP_OUTPUT.TXTSHOW;Begin Loading devdata.feroz_empErrorfiles devdata.feroz_emp_err1, devdata.feroz_emp_err2CHECKPOINT 2;INSERT INTO DEVDATA.FEROZ_EMPVALUES(:emp_no,:fname,:lname,:mgr_emp_no,:dob,:start_date,:dept_no);END LOADING;LOGOFF;is there a way I an avoid writing the extra bytes in tye output file while using fast export?and is there a way to specify a delimiter in the fast export (like a |) other than writing the queryselect fname||'|'|| .......I appreciate your help and Thanks in advance.

0 replies
AMP in Online Catchup state Topic by GreatSushant 10 Mar 2006

Some days back due to some problem my v2r6 database wen down . It is MA-RAS unix box and was down . The AMP state is shown as Online catchup modethis is the error log for this DElogd: Event number 33-13816-00 (severity 10, category 12) Applications not started because the startedappls flagin the Control GDO is off.000067 22:59:51 000086f5 ... -32754 1 140006401|bynet |1|H|I|O|1|0|B|0|0|logging.c|0|0|0|3#BLM: BIC 1 cannot connect to BYNET.000068 22:59:51 000086f5 ... -32754 1 140006401|bynet |1|H|I|O|1|0|B|0|0|logging.c|0|0|0|4#BLM: BIC 2 cannot connect to BYNET.000069 22:59:51 000086f5 ... -32754 1 140006401|bynet |1|H|I|O|1|0|B|0|0|logging.c|0|0|0|5#BLM: BIC 3 cannot connect to BYNET. Parallel Database Extension state is TPA/READY.can anybody suggest me on this

0 replies
Need syntax on Dynamic SQL Topic by rlaskar 10 Mar 2006

I am trying to write a SQL which would generate SQL based on table name and column provided. The SQL should ask for tname and colname and after that it would generate a SQL. Sample of the SQL would be select * from tname where colname is NULL (say for example).

0 replies
environment file in bteq script Topic by poonam 16 Mar 2006

hi, i am trying to get the names of input/ export files into my bteq script from an environment file.using .i have used.os /pimecris/temp/pims.envbut .import data file= $export does not work.Any sugggestions would be very helpul as i want to avoid hard coding any paths.Thanks in advance,poonam.

0 replies
How to validate date field in dd/mm/yyyy or yyyy/mm/dd format ? Topic by rlaskar 17 Mar 2006

I was trying to write a SQL which would validate a date column field. The validation would be whether the rows have data in 'dd/mm/yyyy' or 'mm/dd/yyyy' format.

0 replies
Teradata Professional needed (South Africa) Topic by Kks 19 Mar 2006

Hello all,Teradata Professional needed for a leading financial company in South Africa ,you should have more than 1 yr experience on Teradata as Teradata Developer/ ETL / DBA etc.

0 replies
steps to work on TERADATA Topic by dixon 23 Mar 2006

Hi Allonce i installed teradata demo into my system. what is the next step. when, where and what i need to start to work Onto it. since bcoz i am new to TERADATA i need this basic information.Thanks.

0 replies
command line Topic by Jonny 23 Mar 2006

Does anyone know how to submit backups from a command line.I googled and saw the text below from Jose. I need to wait for the Arcmain return code. Is there any documentation on how to do this?Thanks for any help.JonThe only problem with the "command line option" is that it will submit the job and then finish immediately, without waiting for the Arcmain return code. So, you can not use this approach if you're planning to use that command lie from your job scheduler to build dependencies between jobs.There's a workaround though, you will still need to use the windows GUI to create your bch and scr files, then ftp them to the machine where Arcmain and the tdbackex utility reside and generate the Arcmain command line, using the IOPARM and IOMODULE to specify the exact target or source of your backup. It's ugly, but it works fine.Jose Lora

0 replies
number of rows returned..rowcount Topic by poonam 27 Mar 2006

hi,how do i get the rowcount or the number of rows affected by the last query?somethign like rowcount in oracle.

0 replies
Welcome to the Teradata Developer Exchange discussion forums! Topic by neilotoole 14 Jul 2009 devx

Please see this topic in the General forum for an introduction to the new discussion forums, and a chance to give feedback.

0 replies
Create table and insert data into Teradata database from MS SQL Server Topic by Tesh 02 Sep 2009


I have setup a link server from my Microsoft SQL Server database to a Teradata database. Question is now, how do I create a table and insert data into it from Microsoft SQL Server database? I'm pretty new to Teradata, so I don't know all the tricks.

Thank you,

0 replies
Join Index on table causing 2616 overflow error. Topic by idg_tddevex 25 Nov 2009


I have a table that is defined with an Aggregate Join Index. The AJI was created successfully after table was populated with data. But when we try to insert/update the data into the table it gives a 2616 - Numeric overflow error. We dropped the AJI and then did the insert/update and then recreated the AJI on it and it worked fine.


0 replies
Heart beat queries Topic by Karam 26 Jan 2010


0 replies
joining table A to table B then update table B Topic by barcoo 23 Feb 2010


Please help where you can. I have tried to be clear in my explanation but will clarify if needed.

I am trying to track the SALE history for people and calculate the value of each SALE.

In the example below someone buys a quantity of 100, then buys another 40, then sells 75, then sells 30. The BUYs and SELLs are in one table but I break them into two tables as I think this is clearer but it doesn't have to be.

I have TABLE A for a single CLIENT ID. The table has two records, 5 fields in each record.

0 replies
Need to make SMP to MMP Topic by singleamp 05 Jan 2006

Hello,Can you please share your expriences with Teradata SMP systems(single node)? 1. What are the NCR machines (WorldMark 4800?) supporting SMP?2. Whats the next compatile Hardware to make it MPP?3. Whats the MAX size (in Terabytes) an SMP can store and MAX number of Nodes/AMPs?4. Did NCR published any white paper with Pros and Cons of SMP and its comparision with MPP.Thanks.Rao

0 replies
V2R6 Topic by fouttyp 10 Jan 2006

We are planning an upgrade to V2R6 and was wondering if anyone would tell me their experiences with their upgrade to V2R6. Philip Foutty

0 replies
PPI Topic by Rick-2304 19 Jan 2006

I'm trying to use " insert into ... select * " to populate a table with PPI and am getting an error saying "Invalid Date Supplied for tablename.columnname" where tablename.columnname is the target table. The source and target table structures are exactly alike, with the same PPI range specified. I'm just trying to copy existing data into a another table for some testing.

0 replies
BTEQSQL: .EXPORT FILE DATA Topic by 19 Jan 2006

We would like create a bteq with the command ".EXPORT FILE DATA", but we must used the result of this execution bteqsql to the command ".IMPORT FILE".The result of this exported file must be separated whit “tabulations” and mustn’t have the header columns.How can I make this command? That’s the parameters for this “.EXPORT FILE”

0 replies