I am about to migrate tables from oracle to teradata and this is for the first time I am doing. As of now I have converted the oracle DDLs to Teradata DDLs. Can you please let me know going forward wat issues i will be facing? and also I will be using fastload for dumping the data in teradata database.
Intitially you can focus on these areas to decide the Table structures in Teradata
1. Effective use of relevant Data Types to save uneccessary space consumption.
E.g I changed NUMBER(1) in Oracle to DECIMAL(1,0) in Teradata as it just consumes 1 Byte of data
2. I converted the TIMESTAMP fields in Oracle to DATE in Teradata for the date fields where the time part is really not important.
3. I convereted LOB data to Varchar(63000) if is used frequently and converted the VARCHAR(VERY LARGE) fields to CLOB where the usage of column is very very low.
4. As a general trend we have kept the PK as the PI in Teradata unless otherwise required for optimal joins. But we have kept the columns as NUPI since UPI slows the loads in the tables using MLOAD as it will check the duplicates BYTE by BYTE. Thats a separate discussion if you are implementing Soft RIs in your database as it requires Unique Keys (UPI or USI) to be referred as Soft RIs.
5. LONG will go as LONG VARCHAR but that might pose a problem of exeeding the maximum possible RowLength (depends upon the Data Block size).
6. Huge tables were vertically partitioned retaining the PI column in every table.
7. COMPRESS the fixed length columns.
Hi Manik thanks for the reply. I m just struck at the below. How did u load the TIMESTAMP filed in oracle to TEradtata. WHile inserting all the tables are going in error files and it says format error.Can you please provide me the sytax:
My issue is here:
I am using fastload lo load the data to teradata.
Hi All can you please tell me how to load the date+timestamp as shown in the source to target. Its really urgent.
I am using fastload. Is this possible in fastload if not please let me know how to define column in multiload
Honestly speaking I have been out of touch of coding from quite some time and there is separate team for my project which handles the ETL loading for these tables and that too is done from Ab-initio and not Teradata load utilities.
but If I remeber I can suggest the following. Might work for you:
Do not put Varchar(n) for fixed width columns where the width is always fixed as in the case of Date and Timestamp.
I would put CHAR(19) for timestamp and CHAR(10) for Date columns.
Like wise keep it for other fixed width columns like
Decimal(m,n) -- CHAR(m) here
Hope it helps.
thanks for the reply actually I am using fastload and i guess in fastload i cannot user char and i guess fastload can only use varchar, varbyet and long varchar.....however i got the anwer ..:) thanks for the reply:)
Could you please let m know the answer for your date,timestamp error in your above fload script.