How to compare data from tables in Teradata with tables in Oracle

Database

How to compare data from tables in Teradata with tables in Oracle

Hi All,

We are migrating from Oracle to Teradata.

After the completion of data migration from Oracle to Teradata how can we compare data in Teradata with Data in Oracle.

How can I check if all the data is correctly moved into Teradata from Oracle.
We are dealing with millions of records and thousands of tables.

Please suggest a process which is easy and takes less time to implement.

Thanks In Advance..

My email id: gubba.venkatesh@gmail.com

12 REPLIES
N/A

Re: How to compare data from tables in Teradata with tables in Oracle

Before migration, collect demographic statistics on the table. We can run few GROUP BY queries and check for the number of distinct values. Examine whether the count values are different or not.

Re: How to compare data from tables in Teradata with tables in Oracle

While migrating you might be sticking to plan based on number of days. So you have to collect aggregate level of counts per day per table(for important tables alone) and compare them with teradata tables.
Usually we will consider number of records, sum of amount fields, etc under normal scenario's.

Re: How to compare data from tables in Teradata with tables in Oracle

how we did is, we divided all the tables into two groups - small tables and big tables. Then we created a Shell script to pull data from oracle & Teradata and match it. For small table entire data was matched & for large tables the entire data was grouped based on the data pattern & from each group 10% data was matched. For example - for large table date column was a good candidate for grouping the data.

Re: How to compare data from tables in Teradata with tables in Oracle

Hi All,

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.

Re: How to compare data from tables in Teradata with tables in Oracle

Hi Krish,

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.

Thanks

Manik

Re: How to compare data from tables in Teradata with tables in Oracle

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. 

Source

0447003411|MOBITV|TSDPCON|UltimateCombo|1800|12-07-2011:23:58:23|C|MobileTV|1|12-07-2011:23:58:23|12-07-2011:23:58:23|2011-07-21|

FASTLOAD SCRIPT

SESSIONS 12 2; 

.logon demotdat/tduser,tduser;

SET RECORD VARTEXT "|";

database samples;

drop table MOBILE_CONTENTS_SUB_E1;

drop table MOBILE_CONTENTS_SUB_E2 ;

BEGIN LOADING MOBILE_CONTENTS_SUBSCRIPTION

    ERRORFILES MOBILE_CONTENTS_SUB_E1, MOBILE_CONTENTS_SUB_E2 

    CHECKPOINT 100000 ; 

DEFINE 

SRVC_NUM                       (VARCHAR(10))

,PROVIDER_ID                    (VARCHAR(10))

,CATEGORY                       (VARCHAR(10))

,DESCRIPTION                    (VARCHAR(16))

,AMOUNT                         (VARCHAR(10))

,START_TIME                     (VARCHAR(26))

,MERCHANT_MODEL                 (VARCHAR(10))

,PRODUCT_NAME                   (VARCHAR(100))

,SUBSCRIPTION_PERIOD            (VARCHAR(105))

,FIRST_CREATE_DATE              (VARCHAR(26))

,LAST_UPDATE_DATE               (VARCHAR(26))

,DA_CREATE_DATE                 (VARCHAR(100))

,DA_UPDATE_DATE                 (VARCHAR(10))

FILE = C:\Documents and Settings\HK186002\My Documents\Teradata\GG.txt  ; 

INSERT INTO MOBILE_CONTENTS_SUBSCRIPTION

(    

SRVC_NUM

,PROVIDER_ID

,CATEGORY

,DESCRIPTION

,AMOUNT

,START_TIME

,MERCHANT_MODEL

,PRODUCT_NAME

,SUBSCRIPTION_PERIOD

,FIRST_CREATE_DATE    

,LAST_UPDATE_DATE      

,DA_CREATE_DATE      

,DA_UPDATE_DATE

)   

VALUES

(

:SRVC_NUM

,:PROVIDER_ID

,:CATEGORY

,:DESCRIPTION

,:AMOUNT

,:START_TIME

,:MERCHANT_MODEL

,:PRODUCT_NAME

,:SUBSCRIPTION_PERIOD

,:FIRST_CREATE_DATE    

         ,:LAST_UPDATE_DATE    

,:DA_CREATE_DATE         

,:DA_UPDATE_DATE         

);

END LOADING;

LOGOFF;

DDL

CREATE SET TABLE SAMPLES.MOBILE_CONTENTS_SUBSCRIPTION ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      SRVC_NUM VARCHAR(10) ,

      PROVIDER_ID VARCHAR(6),

      CATEGORY VARCHAR(8) ,

      DESCRIPTION VARCHAR(16),

      AMOUNT DECIMAL(5,0),

      START_TIME TIMESTAMP(0),

      MERCHANT_MODEL     

PRODUCT_NAME VARCHAR(11) ,

      SUBSCRIPTION_PERIOD DECIMAL(5,0),

      FIRST_CREATE_DATE TIMESTAMP(4),

      LAST_UPDATE_DATE TIMESTAMP(4),

      DA_CREATE_DATE DATE FORMAT 'YYYY-MM-DD',

      DA_UPDATE_DATE DATE FORMAT 'YYYY-MM-DD')

PRIMARY INDEX ( SRVC_NUM );

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

thankisssss

Re: How to compare data from tables in Teradata with tables in Oracle

Hey Krish,

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

Integer

Smallint

Bigint

Float

Char

Decimal(m,n) -- CHAR(m) here

etc.

Hope it helps.

Thanks

Manik

Re: How to compare data from tables in Teradata with tables in Oracle

hello Manik,

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:)

Re: How to compare data from tables in Teradata with tables in Oracle

Hi Krish,

Could you please let m know the answer for your date,timestamp error in your above fload script.