importing clob data in to table in teradata

Database
Enthusiast

importing clob data in to table in teradata

I am trying to import clob data in to one of the columns in teradata. First i exported data from db2 table where the column that i am exporting is defined as clob(250000)

Error:

0013 .LAYOUT INLAYOUT

     ;

0014 .FIELD    SRC_SYS_NM            * VARCHAR(20);

0015 .FIELD    TRG_SYS_NM            * VARCHAR(20);

0016 .FIELD    ACTN_CD               * VARCHAR(1);

0017 .FIELD    SRC_SYS_NATR_KEY      * VARCHAR(512);

0018 .FIELD    RTF_NATR_KEY          * VARCHAR(512);

0019 .FIELD    SRC_SYS_REC_TYP_ID    * VARCHAR(11);

0020 .FIELD    CURR_XSITN_ST         * VARCHAR(11);

0021 .FIELD    ATMP_CNT              * VARCHAR(11);

0022 .FIELD    SRC_SYS_TS            * VARCHAR(26);

0023 .FIELD    CRTE_TS               * VARCHAR(26);

0024 .FIELD    CHG_TS                * VARCHAR(26);

0025 .FIELD    RTF_UNQ_ID            * VARCHAR(26);

0026 .FIELD    CTF_UNQ_ID            * VARCHAR(26);

0027 .FIELD    RTF_REC_TYP_ID        * VARCHAR(11);

0028 .FIELD    MSG_CNTNT             *CLOB(2097088000);

**** 23:28:24 UTY0005 Bad data in the FIELD command at position 33, the name

     beginning with "CLOB(2097088000)" is not a valid data descriptor.

----------------------------------------------------------------------------
importing clob data in to table in teradata

--------------------------------------------------------

export command:

export to /etl/tst/arch/odsr3/ODSR3_ARCH_RTFARCHV_tera_9999_041315_8494289.dat of del lobs to /etl/tst/arch/odsr3 modified by lobsinfile nochardel coldel, MESSAGES /etl/tst/logs/odsr3/ODSR3_ARCH_RTFARCHV_tera_9999_041315_8494289.log select SRC_SYS_NM, TRG_SYS_NM, ACTN_CD, SRC_SYS_NATR_KEY, RTF_NATR_KEY,SRC_SYS_REC_TYP_ID,CURR_XSITN_ST,ATMP_CNT,SRC_SYS_TS,CRTE_TS,CHG_TS,RTF_UNQ_ID,CTF_UNQ_ID,RTF_REC_TYP_ID, MSG_CNTNT FROM PODDB03.ETL_RTF_ARCHV WHERE CRTE_TS <= '2015-04-13-00.00.00.000000' WITH UR

This created 2 files. One the data file and other a reference file with clob data. 

-----------------------------------------------------

Now when i am trying to imprort that in to teradata table using bteq script.

---------------------------------------------------

.LOGTABLE #jpTERA_TGT_DBNAME#.ETL_RTF_ARCHV_LG;

RELEASE MLOAD  #jpTERA_TGT_DBNAME#.ETL_RTF_ARCHV11;

.SET WORKTABLE  TO '#jpTERA_TGT_DBNAME#.ETL_RTF_ARCHV_WK';

.SET TABLERR1   TO '#jpTERA_TGT_DBNAME#.ETL_RTF_ARCHV_ERR1';

.SET TABLERR2   TO '#jpTERA_TGT_DBNAME#.ETL_RTF_ARCHV_ERR2';

DROP TABLE &TABLERR1;

DROP TABLE &TABLERR2;

.BEGIN IMPORT MLOAD

  TABLES      #jpTERA_TGT_DBNAME#.ETL_RTF_ARCHV11

  WORKTABLES  &WORKTABLE

  ERRORTABLES &TABLERR1

              &TABLERR2

  ERRLIMIT    1

  SESSIONS    23

  AMPCHECK NONE

  TENACITY    4

  SLEEP       10

;

.LAYOUT INLAYOUT

;

.FIELD    SRC_SYS_NM            * VARCHAR(20);

.FIELD    TRG_SYS_NM            * VARCHAR(20);

.FIELD    ACTN_CD               * VARCHAR(1);

.FIELD    SRC_SYS_NATR_KEY      * VARCHAR(512);

.FIELD    RTF_NATR_KEY          * VARCHAR(512);

.FIELD    SRC_SYS_REC_TYP_ID    * VARCHAR(11);

.FIELD    CURR_XSITN_ST         * VARCHAR(11);

.FIELD    ATMP_CNT              * VARCHAR(11);

.FIELD    SRC_SYS_TS            * VARCHAR(26);

.FIELD    CRTE_TS               * VARCHAR(26);

.FIELD    CHG_TS                * VARCHAR(26);

.FIELD    RTF_UNQ_ID            * VARCHAR(26);

.FIELD    CTF_UNQ_ID            * VARCHAR(26);

.FIELD    RTF_REC_TYP_ID        * VARCHAR(11);

.FIELD    MSG_CNTNT             *CLOB(2097088000);

.DML LABEL UPS1

IGNORE DUPLICATE INSERT ROWS;

INSERT INTO #jpTERA_TGT_DBNAME#.ETL_RTF_ARCHV11

(SRC_SYS_NM,TRG_SYS_NM,ACTN_CD,SRC_SYS_NATR_KEY,RTF_NATR_KEY,SRC_SYS_REC_TYP_ID,CURR_XSITN_ST,ATMP_CNT,SRC_SYS_TS,CRTE_TS,CHG_TS,RTF_UNQ_ID,CTF_UNQ_ID,RTF_REC_TYP_ID,MSG_CNTNT)

VALUES (

:SRC_SYS_NM,

:TRG_SYS_NM,

:ACTN_CD,

:SRC_SYS_NATR_KEY,

:RTF_NATR_KEY,

:SRC_SYS_REC_TYP_ID      (INTEGER,FORMAT '99999999999'),

:CURR_XSITN_ST           (INTEGER,FORMAT '99999999999'),

:ATMP_CNT                (INTEGER,FORMAT '99999999999'),

:SRC_SYS_TS,

:CRTE_TS,

:CHG_TS,

:RTF_UNQ_ID,

:CTF_UNQ_ID,

:RTF_REC_TYP_ID,

:MSG_CNTNT

);

.IMPORT INFILE #jpTERA_ARCH_FILE#

  FORMAT VARTEXT ','

  LAYOUT INLAYOUT

  APPLY UPS1;

.END MLOAD;

------------------------------------------------------------------------

thanks