Teradata Forums / Data Warehousing / Teradata

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

BTEQ

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

is BTEQ a free tool? Can i download it over the net for free?Thank You

How to get the position of special char in a string?

Venkatesh G — Tue, 25 Aug 2009 03:37:14 GMT

How to get the position of special char in a string?I need to convert the string given below under type A into Type B.Type A Type B'Jiby Jose' 'Jiby''Jiby: Jose' 'Jiby''Jiby?: Jose' 'Jiby''Jiby(A) Jose' 'Jiby'for this I need to find the position of the special char and space in a string.Please help me in this regard.If possible please email me to venkatesh.gubba@gmail.com

Graphical rep of Data flow to/from TD

DAZZLER — Mon, 24 Aug 2009 12:33:52 GMT

Does anyone know about a tool which could be used to graphically show data flow from/to the Teradata? we have huge EDW, and I have heard such exist but don't remember what the name or vendor was. This tool could be used to see what type of data is being loaded (file names), and which tables it is being loaded to. It also contains drill down capabilities (much like + on explorer to expand the picture), and you can pretty much trace data from Source to the warehouse or vise versa. Any clue about tool's name?

SOA Questions

Raja_kt — Mon, 24 Aug 2009 04:11:38 GMT

Hi,Client has given an estimate of ~60 TB of data in SCD 2 in a span of 5 years. Can you please provide me the sets of questions and answers on SOA...example ....how much power usage,amps, etc etc....Thanks and regards,Raja

One to Many

marcmc — Fri, 14 Aug 2009 11:40:29 GMT

Hey.I am doing a proof of concept and have come unstuck, my design is as below and I believe this design could work but am not sure how. However I admit my design may be incorrect for the data structures I am trying to work with and I am open to any suggestions.Please advise.------------------------------------------------------------------------------1. I receive a 'LISTS' file which I process to staging.-------------------------------------------------------------------------------- A. Create POC Extract TableCREATE MULTISET TABLE MMG_Get_BCP_H_YLISTS (POLICY_CODE char(20),TRANS_TYPE CHAR(2),TRANS_SEQ_NO INTEGER,LISTS_NAME CHAR(3),UNIT_ID SMALLINT,LISTS_COMBO_STRING CHAR(12));-- B. Insert POC rows into Extract Table.INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 1, 'A', 0, 'abc123def456');INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 1, 'B', 1, '---abc------');INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'A', 0, 'ghi789jkl012');INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'A', 1, 'mno345pqr678');INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'B', 1, '---def------');INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'C', 1, '---xx-------');INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'C', 1, '---xx------');INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'C', 1, '---xx------');select * from MMG_Get_BCP_H_YLISTS order by 1----------------------------end of staging-------------------------------------------------------------------------------------------------------------------2. I build a dimension as follows:------------------------------------------------------------------------------CREATE MULTISET TABLE MMG_Lst_lu_Lists (Lst_Dim_Lists_Key_Id INTEGER,Lst_Lists_Name CHAR(3),Lst_Lists_Att_Id CHAR(12));INSERT INTO MMG_Lst_lu_Lists(Lst_Lists_Name, Lst_Lists_Att_Id)SELECT D1.List_Name_Rank, bcp.LISTS_NAME as List_Name, CASE WHEN bcp.LISTS_NAME = 'A' THEN SUBSTR(LISTS_COMBO_STRING,1,12) WHEN bcp.LISTS_NAME = 'B' THEN SUBSTR(LISTS_COMBO_STRING,4,3) WHEN bcp.LISTS_NAME = 'C' THEN SUBSTR(LISTS_COMBO_STRING,4,2) ELSE 'Not Defined' ENDFROM MMG_Get_BCP_H_YLISTS bcpJOIN (SELECT ROW_NUMBER() OVER (ORDER BY D2.LISTS_NAME) As List_Name_Rank, D2.LISTS_NAME FROM (SELECT LISTS_NAME FROM MMG_Get_BCP_H_YLISTS GROUP BY 1) D2) D1ON bcp.Lists_Name = D1.Lists_NameORDER BY 1,2,3;select * from MMG_Lst_lu_Lists order by 1-------------------------end of dimension-------------------------------------------------------------------------------------------------------------------3. I populate my fact as follows:------------------------------------------------------------------------------Insert into FACTtblSELECT c.policy_code, c.policy_rsk_no, c.product_class, c.effective_dt, c.trans_seq, m.Lst_Dim_Lists_Key_IdFROM cover_class_staging_tbl1 cLEFT JOIN cover_class_staging_tbl2 q ON c.POLICY_CODE = q.POLICY_CODE AND c.POLICY_RSK_NO = q.POLICY_RSK_NO AND c.PRODUCT_CLASS = q.PRODUCT_CLASS AND c.EFFECTIVE_DT = q.EFFECTIVE_DT AND c.TRANS_SEQ = q.TRANS_SEQ_NOLEFT JOIN MMG_Lst_lu_Lists mON substring(c.Lists_Name,1,1) = m.Lst_Lists_NameOR substring(c.Lists_Name,2,2) = m.Lst_Lists_Name--------------------------------end of fact------------------------------------/* DDL for cover_class_staging_tbl1 & 2 CREATE SET TABLE cover_class_staging_tbl1 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( POLICY_CODE CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, POLICY_RSK_NO INTEGER, PRODUCT_CLASS CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC, EFFECTIVE_DT CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC, TRANS_SEQ INTEGER, LISTS_NAME CHAR(3)) -- will have the list name of each list per transaction eg 'ABC' PRIMARY INDEX ( POLICY_CODE );INSERT INTO cover_class_staging_tbl1 VALUES('GEI/QMV/01879674', 1, 'QMV', '20090813', 1, 'AB'); CREATE MULTISET TABLE cover_class_staging_tbl2 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( POLICY_CODE CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, TRANS_SEQ_NO INTEGER, POLICY_RSK_NO SMALLINT, PRODUCT_CLASS CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC, EFFECTIVE_DT CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC) PRIMARY INDEX ( POLICY_CODE ,TRANS_SEQ_NO );*/ -- end DDLI need one row coming back which can link to the dimension and show the multiple lists, not sure how - please help!

Need help with converting date format in Multiload

TD-ETL Developer — Thu, 13 Aug 2009 12:11:16 GMT

Here is a sample record from my input file (which is comma delimited)3,840,7/3/2009,718143.47 Table structure is NBR1 SMALLINT NOT NULL, NBR2 SMALLINT NOT NULL, Date1 DATE FORMAT 'YYYY-MM-DD' NOT NULL, Amount DECIMAL(12,2) NOT NULL I am using Mload to load this file into table.LAYOUT SYSREC; .FIELD NBR1 * VARCHAR(4); .FIELD NBR2 * VARCHAR(4); .FIELD Date1 * VARCHAR(10); .FIELD Amount * VARCHAR(16); . . . . .IMPORT INFILE INFILE FORMAT VARTEXT ',' LAYOUT SYSREC APPLY INSERT; Since it is comma delimited file, i have to specify FORMAT VARTEXT ',' and have to define the fields as VARCHAR.My problem is-- How can I convert the date format in input file (mm/dd/yyyy) to load the table with date format('YYYY-MM-DD')When I run the script as shown above, I get 2665 error with date field.I have also tried adding (DATE, FORMAT 'YYYY-MM-DD') in the INSERT command. Still same error.Looking for some help here.

Proprietary versus Open source ETL tools

Jstatic — Wed, 12 Aug 2009 09:46:55 GMT

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.

MLoad Error

Greyghost — Wed, 05 Aug 2009 11:07:41 GMT

Hello,I need some help regarding an error that occurred while our production loading was running. Here is the error message:----------------------------------------------------------------------------Access module error '34' received during 'read' operation on record number '1361905': 'pmunxRBuf: fread error (Invalid argument)'----------------------------------------------------------------------------A fast look at the record everything looked fine. So, I removed the record from the input file (saving it for analysis) and reran the MLoad. It ran cleanly.I went back to analyze the single record, and started by just trying to MLoad this record. I expected an error, but the MLoad ran to a zero completion code!I am now very confused on why the production job abended when there appears to be nothing wrong with the data.Can anyone shed any light on this situation?Thanks!Paul

Show Table View Key(s)

ChelseaFC — Wed, 05 Aug 2009 08:39:55 GMT

Apologies if this has been asked many times before.I am trying to sum(budget_sales) from a table but when I join it onto another table it shows budget_sales in billions rather than in the more accurate hundreds of millions.Do I need to join it onto the primary key/secondary key and if so what is the command to show a VIEW's keys?Many thanks,Roy