Teradata Forums / Data Warehousing / Teradata

Teradata Role in a Retail client project

meta — Tue, 17 Nov 2009 17:27:05 GMT

Hello AllIam new to Teradata...can any one suggest a material / explain me the basic role of a Teradata developer in a real time environment thanksmeta

Copying Table structure

Rupesh_rsb — Thu, 19 Nov 2009 21:31:31 GMT

Hi,I have to copy the table structures from one database to another.I am using below statementCREATE TABLE ABC.partyas XYZ.party WITH NO DATA ;But the problem is all tables in database XYZ are partitionedbut while copying the tables to ABC i do not want them to be partitioned.Above clause takes table defination as available in XYZ and creates.Anybody help me how to do this?

How to compare date in Varchar2(25 Byte) with dates

Coolguyz — Thu, 19 Nov 2009 11:38:18 GMT

I have a column "A" of type Varchar2(25 Bytes) which is getting data stored in below format,10/08/2009 12:04:23 AMI wante to comapre column A with dates so that I can delete the records older than (Sysdate - 30).Any help?

Numeric Overflow error

samvs — Sat, 14 Nov 2009 11:44:11 GMT

Hi,while firing the below sql, I am getting 'Numeric Overflow Occured during computation'Select A ,B ,C ,D ,E ,sum(F) AS G ,sum(H) AS I from dbname.tblname WHERE D <> 'R' group by 1,2,3,4,5order by 1,2,3,4,5Pls help!

BTEQ Signon problem CLI: 224

pahmed — Wed, 11 Nov 2009 11:16:57 GMT

I have installed Teradata demo version on windows. I have created a user as pervez. Also using odbc I have defined as data source pervezTD. I am trying to invoke BTEQ to submit a FASTLOAD Job.I am geeting sign0n problem to bteq I am getting CLI Error: EM_HOST (224). Name not in the host file or names database.How can I fix the problem. How do I assign a name in the host file or names database.One more question please: What is the TDPID on demo version and how can I get this value.Thanks.I will appreciate your answer.

surrogate key

dominic.xavier — Tue, 17 Nov 2009 04:41:59 GMT

hi guys,i am new to data warehousing , what is surrogate key? i am also looking for an e book on data warehousing. please provide me the link

Which "select..from..where" syntax is running faster?

ylai20 — Wed, 18 Nov 2009 02:19:26 GMT

Hi, i am wondering from the 2 syntax below, which is running faster or is the best practice?First option==========Select A.Col1,B.Col1,A.Col2,A.Col3From(Select Col1, Col2,Col3From Table1) ALeft join (Select Col1, Col2,Col3From Table2) B on A.Col1= B.Col1where A.Col1=XXXSecond option==========Select A.Col1,B.Col1,A.Col2,A.Col3From(Select Col1, Col2,Col3From Table1 where Col1=XXX)ALeft join (Select Col1, Col2,Col3From Table2) B on A.Col1= B.Col1Will be the second option more faster? Because the "where" is more upper than the first option?Thanks!

DWH Landing Server Configuration

faisal.mehmood — Tue, 17 Nov 2009 02:50:56 GMT

Hi My customer is asking me, to purpose them Landing Server configuration. This purposed server will be used as daily dwh loading through DataStage Server. All the scheduling will be done on this server. All the etl development will be done this server. Plus the file extracts from customer sources will be dumped on this machine.Thanks.

Grant create user/create database problem

pahmed — Sat, 07 Nov 2009 23:07:50 GMT

I have installed teradata demo version 12.o. Using dbc I created a user for the administration purpoises as DBAdmin. I want to grant previleges to the new user as grant create user to DBAdmin. I am getting the syntax error. Can some please let me know what is the excact syntax is. Does the demo version allows to grant create user or create database previleges to the other users?

Platform migration

hrmikwen — Wed, 04 Nov 2009 10:25:52 GMT

Hello,Kind of new here, this is my first post so Hello to all.I am trying to migrate some data from another platform. I am wondering how to do this knowing that I have a large database and that I would like to migrate it as one entity.Thank you lots.

To create a volatile table in crystal reports on terdata

kundan — Thu, 05 Nov 2009 10:12:55 GMT

Hi I am migrating a set of reports from SQL Server 2000 to Terdata V12. My existing crystal report got a command in SQl server using Temp tables, so i am trying to create a volatile table in crystal reports on teradata database and i am getting this error [size=4]"Only an ET or null statement is legal after a DDL Statement"[/size]Below is the code i am using create volatile table test_data_vt ( capst_a_dt timestamp(6),adx_an_id integer,adx_br_id integer) primary index (capst_appn_dt, adx_appn_id, adx_bor_id) on commit preserve rows; insert into test_data_vt select capst_a_dt,adx_an_id,adx_br_id from DADW.dms_ak where adx_appn_id=54625871; select * from shaw_data_vt; The Query is running fine in Teradata SQL Assistant and i am able to create the Volatile tableI am not able to solve the issuse, So please if any one can help me i really Appreciate

How to add in Select Statement inside Case?

ylai20 — Thu, 05 Nov 2009 03:04:13 GMT

TeraData vs Oracle

nicky — Sat, 30 Jun 2007 03:39:58 GMT

hi

Recently one of our client wants to make a selection between teradata and oracle 10g. I our evaluation tells me that oracle 10g is far better than Teradata. But it may be because there are so many persons working on oracle so will anybody here guide me

DBQL table definitions

jzlamal — Wed, 04 Nov 2009 07:50:37 GMT

Is there a document that defines the columns in the database query log tables (DBQLogTbl, DBQLSQL,... etc)? For example, I have seen various documents on Teradata performance that reference the field HotAmp1IO, but I have not see any document that actually defines what that field represents. I checked the "Performance Management" documentation from Teradata and didn't see the answer there either. Any ideas?Thanks!John

Select (SHOW sometable) into volatile table

pxr — Fri, 30 Oct 2009 07:50:04 GMT

hi,i am a tester. After changes in some object (or in some source object etc) the first thing we have to test is to compare old and new object whether only the changes documented are done. I would like to somehow make this process automatic.To compare two sometables (prod and test) we just manually compare the output of "SHOW sometable" from different environments. Is it somehow possible to select the results of this command? I would just select then the results to some volatile tables and create sql to compare the two tables.

Any DBC tables to find the row length of a table?

EDW ETL — Fri, 14 Mar 2008 19:34:24 GMT

Hi,Is there any DBC tables that I can use to find out what's the Total Row Length of a row in the TERADATA table?Thanks

Interval Overflow Error

papvan — Thu, 29 Oct 2009 13:26:26 GMT

Hi,I am having a column where the time is sorted as ddd hh:min:ss. The col has many 3 digit days & when database sums them all...the total is more than "4 digit days".If I try for interval: day(5) to second(0)....its not working.Is there a way to accomodate sum of days more than 4 digits? 953 21:34:15 846 19:54:20 748 03:24:65 702 11:34:94 675 23:42:58 644 20:13:40 562 23:08:55 534 16:12:39 468 12:53:58 394 15:25:14 343 08:12:28 298 07:30:98 250 05:39:74Let me know, how it can be resolved. Thanks

Altering a table

Greyghost — Thu, 03 Sep 2009 09:12:10 GMT

Hello,Can I add a key and partitions to a table by using the Alter statement, or are these best handled by creating a new table with the keys and partitions defined and then inserting the old table data into the new? The table is very, very large.Thanks!Paul

SQL Question.

rglass — Mon, 26 Oct 2009 13:53:10 GMT

User would like a view built on table that would return a record for each date between eff_bgn_dt and eff_end_dt having invry_qty constant for each group of records.Table Structure:CREATE TABLE PRODUCT.RTL_INVRY_RDF_EOD( Loc_Id SMALLINT NOT NULL , Wd_Cd INTEGER NOT NULL , Eff_Bgn_Dt DATE NOT NULL FORMAT 'YYYY-MM-DD' , Eff_End_Dt DATE NOT NULL FORMAT 'YYYY-MM-DD' , Invry_Qty DECIMAL(9,2) NOT NULL , Adt_Id INTEGER NOT NULL ) PRIMARY INDEX RTL_INVRY_RDF_EOD_NUPI ( Loc_Id, Wd_Cd );sel loc_id,wd_cd,eff_bgn_dt,eff_end_dt,invry_qty from dsb.rtl_invry order by 3 ;Loc_Id Wd_Cd Eff_Bgn_Dt Eff_End_Dt Invry_Qty2 200,041 2009-10-13 2009-10-17 35.002 200,041 2009-10-18 2009-10-19 33.002 200,041 2009-10-20 2009-10-20 30.002 200,041 2009-10-21 2009-10-21 21.002 200,041 2009-10-22 9999-12-31 33.00View:example:sel loc_id,wd_cd,eff_bgn_dt ,invry_qty from dsb.rtl_invry order by 3 ;Loc_Id Wd_Cd Eff_Bgn_Dt Invry_Qty2 200041 2009-10-13 352 200041 2009-10-14 352 200041 2009-10-15 352 200041 2009-10-16 352 200041 2009-10-17 352 200041 2009-10-18 332 200041 2009-10-19 332 200041 2009-10-20 302 200041 2009-10-21 212 200041 2009-10-22 33RDBMS 12.00.02.17Any Ideas appreciated.Thanks, R Glass

Tpump loader Error

UPENDRA.EX!LANT — Wed, 23 Sep 2009 10:30:18 GMT

I got the following Error while executing a Tpump loader script.**** 14:23:05 UTY3401 End of file was reached with an unbalanced number of quotes being found in the text.Please suggest if anyone is familiar with this type of error

A bit of mathermatics

marcmc — Tue, 06 Oct 2009 06:20:38 GMT

Hi.I have a formula to calculate a unique transaction sequence number that works as follows:transaction sequence(TS) * 100 + record sequence(RS)So take two examples:118(TS) * 100 + 103(RS) = 1190399(TS) * 100 + 50 = 9950From the results I need to be able to get back to determine the original TS and RS.The solution has to work for both examples above.So in the case of 11903 I need to get back to 118 and 103 and in the case of 9950 I need to get to 99 and 50.Any help thouroughly appreciated.

Data Warehousing/BI Software

john_sm — Sat, 03 May 2008 16:39:26 GMT

Hey guys, I was just wondering, what software do you primarily use in Business Intelligence/Datawarehousing, and are their any significant cutbacks in any proposed projects

performance tuning or query tuning

R lal — Fri, 25 Sep 2009 02:58:55 GMT

DELETE FROM A_BCIDDB.CREDIT_ENTRIES_CT T1WHERE EXISTS(SELECT '1' FROM L_DLYBCIDDB.CREDIT_ENTRIES_TEMP T2 WHERE T1.MI_ACCOUNT_IDENTIFIER = T2.MI_ACCOUNT_IDENTIFIER AND T1.ENTRY_AMOUNT = T2.ENTRY_AMOUNT AND ( (T1.ENTRY_SOURCE_CODE = T2.ENTRY_SOURCE_CODE AND T1.ENTRY_CODE = T2.ENTRY_CODE AND T1.ENTRY_DATE <= T2.ENTRY_DATE ) OR T1.ENTRY_SEQUENCE_NUMBER = T2.ENTRY_SEQUENCE_NUMBER) ) AND T1.ENTRY_AMOUNT_CODE = 4; CREDIT_ENTRIES_CT has nearly 2.2 billon rows.CREDIT_ENTRIES_TEMP has nearly 6 million rows.i have all the recommended stats.can we change the query ?Please help .....

Date Range Syntax

andscott — Wed, 07 Oct 2009 11:59:34 GMT

I have a syntax question on Teradata date ranges. I Have the following SQL, and am getting the following error. I am used to working with oracle, but couldn't find the needed syntax for this.Select * from db.table WHERE CREATED BETWEEN TO_DATE ('6/2/2009 10:11:06 PM','mm/dd/yyyy hh:mi:ss am') AND TO_DATE('8/31/2009 10:11:06 PM', 'mm/dd/yyyy hh:mi:ss am') Order By ROW_ID ERROR [42000] [NCR][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the string '6'.

Table alter timestamp

UPENDRA.EX!LANT — Tue, 06 Oct 2009 11:52:32 GMT

Is there any metadata table/view where one can find the last modification time of the table. If we alter a table to add a new column, or drop an existing column, or any chnage to table structure, in Oracle, there is a metadata table (all_objects) where we can find the last modification timestamp.I am looking for the same functionality in TERADATA. Please suggest.

Failure 3156 Request aborted by TDWM.

Toshu — Tue, 06 Oct 2009 05:03:25 GMT

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

Help with Table Partitioning

tony123 — Thu, 03 Sep 2009 09:12:47 GMT

Can some please help me as I am quite new to teradata.I've created a fact table as follows:CREATE MULTISET TABLE FACT ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( ACCOUNT_ID VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC, PERIOD_DATE DATE, REGION VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC, AMOUNT DECIMAL )PRIMARY INDEX ( ACCOUNT_ID )PARTIONED BY REGION; 1. There are multilpe jobs/scripts performing DELETE and INSERT commands to this table at the same time in parallel. For instance Job 1: DELETE FROM FACT WHERE PERIOD_DATE BETWEEN '2009-01-01' and '2009-10-01' AND REGION = 'ABC'; /* Start inserting into FACT for REGION = 'ABC' and for PERIOD_DATE BETWEEN '2009-01-01' and '2009-10-01' */ INSERT INTO FACT FROM TMP.TABLE; For instance Job 2: DELETE FROM FACT WHERE PERIOD_DATE BETWEEN '2009-01-01' and '2009-10-01' AND REGION = 'DEF'; /* Start inserting into FACT for REGION = 'DEF' and for PERIOD_DATE BETWEEN '2009-01-01' and '2009-10-01' */ INSERT INTO FACT FROM TMP.TABLE; For instance Job 3: DELETE FROM FACT WHERE PERIOD_DATE BETWEEN '2009-01-01' and '2009-10-01' AND REGION = 'GHI'; /* Start inserting into FACT for REGION = 'GHI' and for PERIOD_DATE BETWEEN '2009-01-01' and '2009-10-01' */ INSERT INTO FACT FROM TMP.TABLE; I've created my FACT table as a MULTISET with a NON-UNIQUE primary key, partitioned by REGION prevent locking when inserting into the table. 1) Since all 3 jobs are deleting and inserting at the same time, will there be any issues with locking ? I've read that row hash locking will still occur . 2) Any performance issues ? It is anticipated that the data for each REGION period will be in the 10's million's. tony

Data integration tool in Chinese?

ofdabler — Tue, 22 Sep 2009 12:14:14 GMT

Hello to all, Being in a pretty much international company, I have come here to ask a few things about ETL tools and their different languages. We have offices in the US, in Europe (Italy, France) and in China. We think English is fine but our European team and Chinese team especially would like to get software in their language. What we are trying to find is an ETL program to perform data integration, data synchronization and deduplication on our database in French, Italian and especially in Chinese. Thanks for your help.

distinct vs group by, insert vs create as

ctev — Thu, 17 Sep 2009 23:52:45 GMT

HiFrom a processing/ efficiency point of view, looking at two questions below, which method in each question is faster?Consider this for an average sized table(whatever you think that is ha ha!)CONSIDER ALL OTHER FACTORS, INDEXES ETC THE SAME. Basically I simply want to know if there is a difference in speed with the way teradata handles each problem.1) Which is faster - Using DISTINCT or GROUP BY statement for the example belowSel DISTINCT NAME, DOB, AGEfrom table1orSel NAME, DOB, AGEfrom table1group by 1,2,3Which is faster/ more efficient (for an average sized table)?===========2) Which is faster - Using INSERT or CREATE AS statement for the example belowCREATE VOLATILE TABLE table1,NO FALLBACK ,NO BEFORE JOURNAL ,NO AFTER JOURNAL ,CHECKSUM = DEFAULT ( NAME VARCHAR(20) ,DOB DATE FORMAT 'YY/MM/DD' , AGE VARCHAR(3) ) PRIMARY INDEX (NAME) ON COMMIT PRESERVE ROWS;INSERT INTO table1SEL NAME, DOB, AGE FROM TABLE2orCREATE VOLATILE TABLE table1 AS(SEL NAME, DOB, AGE FROM TABLE2) WITH DATA PRIMARY INDEX (NAME) ON COMMIT PRESERVE ROWS;Which is faster/ more efficient (for an average sized table)?

Teradata Schema

Ansh — Sun, 06 Sep 2009 08:28:26 GMT

Hi,Could you please tell me which schema does Teradata follows:Star Schemaor Snow Flake Schemaor 3rd Normalized schema

Hoe to check which user had deleted data

r04R04 — Wed, 16 Sep 2009 11:31:18 GMT

data in Teredata table has been deleted manually (Not through Informatica Workflows) is there any way to find when and who did the last delete action on that particular table from DBC database in teradata.Any Information on this willl be really helpfull

What mean TotalCPUTime

Dramis — Mon, 14 Sep 2009 15:23:24 GMT

I would like to know how to interpret the column TotalCPUTime in the table DBC.DBQLogTbl.I have 251.83, is it 251.83 millstones ?Thank you

ROW_NUMBER() - numeric overflow error

deagle — Mon, 14 Sep 2009 05:23:24 GMT

Hi,I am trying to run the following SQL but I get a 2616: Numeric overflow occured during computation error.Can someone explain why this happens and maybe suggest a solution.Thanks,KarenSELECT (CAST(((100000 * 100000000) + Ms_handler_key_id) AS NUMERIC(18))) lw_ms_tr_key_id, a.Ms_handler_id FROM MSt_lu_handler_mgmt_key aWHERE a.ms_handler_active_from_date_id < :v_start_of_week_dateQUALIFY ROW_NUMBER() OVER (PARTITION BY a.Ms_handler_id ORDER BY a.ms_handler_active_from_date_id desc , a.ms_handler_eff_date_id desc, a.ms_handler_seq_id DESC) = 1)

Could any one help me in learning teradata

praween — Mon, 07 Sep 2009 23:08:26 GMT

Hi friends, This is praveen. I heard about this software recently and I would like to learn this software. Could any one help me in learning this software. I would like to how to start. I am from computer science background. I don't have any tutorials.I would appreciate this help.

Oracle Reports connectiing to TERADATA

sweet_melody2504 — Mon, 17 Aug 2009 23:17:52 GMT

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

Training on an ETL tool

Jstatic — Tue, 01 Sep 2009 09:43:40 GMT

Hi all, We are in the process of getting an ETL program. We need it to perform some basic extract, transform and load jobs. But we want to get an open source tool with good training. Our team is mainly business oriented, with some computer knowledge. We would like to have someone come to our company and explain to all the team how to operate the tool. I can operate the tool on my own, but I am not experienced enough to show other people how to operate the software. So would someone know which open source companies offer ETL tools with good training and how much time would it take? Thanks.

Last updated date and time of any table in Teradata

Hardik — Tue, 01 Sep 2009 04:31:25 GMT

Can somebody help me in getting the Last updated date and time of any table in Teradata Database?

String manipulation in a MACRO

UPENDRA.EX!LANT — Fri, 28 Aug 2009 12:13:39 GMT

REPLACE MACRO mydb.emp_mac_test(in_emp_id varchar(300) )AS(sel * from db.emp where emp_id in (: in_emp_id););The requirement is that the macro will be executed as follows:exec mydb.emp_mac_test('A101,A102,A103,A104');How to modify the input string and convert it as 'A101','A102','A103','A104' in the macro.

MLoad Issue

Greyghost — Wed, 26 Aug 2009 08:44:39 GMT

I have asked this before, but have never received an answer. So, I will try again.Our production load jobs are abending with this message:**** 23:13:34 UTY4015 Access module error '34' received during 'read' operation on record number '5789498': 'pmunxRBuf: fread error (Invalid argument)'What is causing this to occur? When I restart the job, it will sometime complete successfully, with no changes being made, or it abends with a different record being flagged. This is large file 6+ million recs, but this error seems to randomly appear. Is it space related? Server space issues? Is this a Teradata issue?What can I do to prevent this error from happening in the future?Thanks!Paul

Load Balancing & Security

larry200 — Wed, 26 Aug 2009 03:16:20 GMT

Load balancing============I have two teradata boxes by names Box1 and Box2. How do I make sure the query requests are load balanced between these two boxes? Do I need another hardware component for that? OR Do I need to write my own code for load-balancing?Security=======I have a single teradata box Box1. How do I configure this box and the dbms, so that two of my clients would use the same hardware and database. But, it must be highly secure. Client-1 should not be able to view client-2's data and vice versa. Amp/node failure for client-1 should not affect client-2 and vice versa.Thanks,LarryThanks,Larry