CLOB In Teradata ?

Database

CLOB In Teradata ?

Hi All,

We have define a coulmn as CLOB in a teradata table as we have to load it from a CLOB column from Source(Oracle), but

during loading it from API/Uitlity mode in Abinito, we are getting an error and load is failing.

I belive we cannnot load a CLOB column thorugh utility...

Work around -->

We are thinking to define the column as varchar , after checking the length of the max possible value from source system.

Any suggestions/feedback  on the above pls?

Cheers!

Nishant

Tags (2)
2 REPLIES

Re: CLOB In Teradata ?

Nishant, you may use/ run program in ab initio , create a unix script that calls bteq....

.IMPORT INDICDATA FILE=...........; USING (xxxxxxx INTEGER, yyyyy CLOB ........)

INSERT INTO table_name(:field1, :field2);

Please let me know if it helps. I can think of run program now but will let you know better designs.

Cheers,

Raja

Re: CLOB In Teradata ?

Hi,

I am facing an issue while unloading table which has one of the column defined datatype as the BLOB/CLOB.

when I tired to unload table using below code,it gives result as the partial columns but not all the columns..Can any one please help us to get all the columns as the output

Table Defintion:

CREATE MULTISET TABLE DRT.DRI_RTG ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      COPY_WF_ACTY_TRANSIT_CODE DECIMAL(10,0)

      ,COPY_WF_ACTY_SOURCE_CODE DECIMAL(10,0)

      ,COPY_WF_ACTY_TARGET_CODE DECIMAL(10,0)

      ,RULE_TEXT CLOB(2097088000) CHARACTER SET LATIN

      ,ORDER_VAL DECIMAL(3,0)

      ,GROUP_VAL DECIMAL(10,0)

      ,GROUP_FROM_SPLIT_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC

      ,UPD_DATE TIMESTAMP(6)

      ,MIS_DATE TIMESTAMP(6))

Code: 

bteq <<EOI

.RUN FILE login_info.dat

.EXPORT INDICDATA FILE=output;

.SET LARGEDATAMODE ON;

sel COPY_WF_ACTY_TRANSIT_CODE      ,COPY_WF_ACTY_SOURCE_CODE      ,COPY_WF_ACTY_TARGET_CODE      ,ORDER_VAL      ,GROUP_VAL      ,GROUP_FROM_SPLIT_IND      ,UPD_DATE      ,MIS_DATE,RULE_TEXT from DRT.DRI_RTG;

.END EXPORT

.LOGOFF;

.EXIT

EOI