Teradata PX

kumar66 · Post by **kumar66** » Fri Aug 15, 2008 5:44 pm

Hi All,

I am loading data to Teradata from a dataset. My source has around 6 million rows and I have 6 fields.

The jobs takes around 5 hours to complete. Is there any way to make it faster. And my table is a full refresh.

Please Advice.

Thanks & Regards,
Kumar66

toshea · Post by **toshea** » Fri Aug 15, 2008 8:32 pm

You should be using the Teradata Enterprise stage.

kumar66 · Post by **kumar66** » Fri Aug 15, 2008 9:50 pm

Yes, I am using Teradata Enterprise Stage. And even I tried with Multiload stage.

The job runs for 5- 6 hours.

Please Advise.

Thanks & Regards,
Kumar66

keshav0307 · Post by **keshav0307** » Fri Aug 15, 2008 10:02 pm

how any sessions are used?

kumar66 · Post by **kumar66** » Fri Aug 15, 2008 10:27 pm

Hi keshav,

8 Sessions are connected.

Thanks & Regards,
Kumar66

keshav0307 · Post by **keshav0307** » Fri Aug 15, 2008 10:50 pm

are you doing anything else in your job, then just reading the dataset and load using teradata Enterprise stage?
it should not take more then 10 min to load this volume.
the atrget is set table or multiset? have you choosen correct key and Primary Index.
for lnsert Teradata Enterprise stage is fastest.

kumar66 · Post by **kumar66** » Sat Aug 16, 2008 12:57 am

Hi Keshav,

It is a multiset table. I have pasted my DDL below:

CREATE MULTISET TABLE FFRED.STG_PMOTN,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
FREESPACE = 0 PERCENT,
CHECKSUM = DEFAULT
(
PMOTN_CD VARCHAR(20) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
PMOTN_ID INTEGER NOT NULL,
BI_INTF_PROC_NM CHAR(30) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
BI_INTF_CRT_DTTM TIMESTAMP(0) NOT NULL,
BI_ETL_BTCH_ID INTEGER NOT NULL,
BI_ETL_PROC_DTTM TIMESTAMP(0) NOT NULL)
UNIQUE PRIMARY INDEX ( PMOTN_CD ,PMOTN_ID );

Is there anything to add to the DDL?

Please Advise.

Thank & Regards,
Kumar66

nishadkapadia · Post by **nishadkapadia** » Sat Aug 16, 2008 12:08 pm

following could be some pointers related :
check pls on the index and it's skewness whilst loading into table.[ can get in touch with DBA if required ]
any full row duplicate records exist
any index related duplicate records exist

kumar66 · Post by **kumar66** » Mon Aug 18, 2008 2:44 pm

Hi All,

I checked the log file and it gives me teradata error code 6750- Invalid Timestamp.

Then I changed TIMESTAMP(0) NOT NULL to CHAR(19) NOT NULL in my DDL.

The job takes 5 mins to complete the 6 million rows. Is it right to chnage the timestamo datatype to char? And

How to define the TIMESTAMP datatype in Teradata?

Please Advise.

Thanks & Regards,
Kumar66

toshea · Post by **toshea** » Mon Aug 18, 2008 2:58 pm

Rather than changing the DDL of your target table column to CHAR(19), you should change the data type of your input link column to Timestamp.

kumar66 · Post by **kumar66** » Mon Aug 18, 2008 3:09 pm

Yes my input column type is Timestamp. But if dont chnage my DDL to Char , I am getting this error.

toshea · Post by **toshea** » Mon Aug 18, 2008 3:29 pm

Is it possible that you are setting the Extended attribute on the Timestamp column to Microseconds? You should not set that attribute. Intead you should leave it blank. Since your target table's column is defined as TIMESTAMP(0), you don't want the Microseconds extended attribute.

kumar66 · Post by **kumar66** » Mon Aug 18, 2008 4:22 pm

No I am not. I have give datatype as timeatamp and size as 23 .

Thanks & Regards,
Kumar66

toshea · Post by **toshea** » Mon Aug 18, 2008 4:37 pm

I would expect the length of a TIMESTAMP(0) to be 19 (YYYY-MM-DD HH:MM:SS).

kumar66 · Post by **kumar66** » Mon Aug 18, 2008 5:21 pm

I tried with Timestamp 19 in the input link, but the job gets aborted. So I changed the DDL to char(19) , it works fine.