Bteq to unload/load BLoB/CLOB data

General

Bteq to unload/load BLoB/CLOB data

Hi ,

I am facing an issue while unloading BLOB data using bteq.

When I tried to run bteq code using LARGEDATAMODE ON,I am getting only 3 columns as ouput instead of all the columns in the table.

Can any one help us ,how could we get all the columns in the output file if any one of the column is defined with BLOB/CLOB data type.

Below is the code which i tried...

rm -f output

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 from  D_MX

_CB_TEMP_T1.DRI_484_HDR_COPY_WF_TRNST_RULE;

.END EXPORT

.LOGOFF;

.EXIT

EOI

Table definition :

CREATE MULTISET TABLE Q_MX_CB_EDW_BIC_COB_T1.DRI_484_HDR_COPY_WF_TRNST_RULE ,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))


2 REPLIES

Re: Bteq to unload/load BLoB/CLOB data

Below is the bteq script works fine for unloading table data that has clob datatype defined in it but facing an some issue in load part.

Can any one please help me in fixing the issue in load script.

Unload:

#!/bin/sh

#########################################

rm -f output

bteq<<EOI

.RUN FILE SOURCE_PD.info;

.SET LARGEDATAMODE ON;

.EXPORT INDICDATA FILE=output;

sel 'COB_RESULT'||cast(COPY_WF_ACTY_TRANSIT_CODE as decimal(10,0))||cast(COPY_WF_ACTY_SOURCE_CODE as decimal(10,0))||cast(COPY_WF_ACTY_TARGET_CODE as decimal(10,0))||cast(ORDER_VAL as decimal(3,0))||cast(GROUP_VAL as decimal(10,0))||cast(GROUP_FROM_SPLIT_IND as char(1))||cast(UPD_DATE as char(26))||cast(MIS_DATE as char(26)) from  D_MX_CB_TEMP_T1.DRI_484_HDR_COPY_WF_TRNST_RULE;

.END EXPORT

.LOGOFF;

.EXIT

EOI

sed 's/.*COB_RESULT|\(.*\)/\1/g' output >output1

 cat output

xuCOB_RESULT|        123.        123.        456. 789.        987.Y2015-06-19 16:39:17.0000002015-06-19 16:19:17.000000

xuCOB_RESULT|        111.        123.        456. 789.        987.Y2015-06-19 16:39:17.0000002015-06-19 16:19:17.000000

cat output1

        123.        123.        456. 789.        987.Y2015-06-19 16:39:17.0000002015-06-19 16:19:17.000000

        111.        123.        456. 789.        987.Y2015-06-19 16:39:17.0000002015-06-19 16:19:17.000000

Load:

#!/bin/sh

#########################################

bteq <<EOI

.RUN FILE TARGET_PD.info;

.set defaults;

.set format on;

.decimaldigits 38;

.SET LARGEDATAMODE ON;

.IMPORT indicdata file=output1;

.REPEAT *

USING(

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

insert into Q_MX_CB_EDW_BIC_COB_T1.DRI_484_HDR_COPY_WF_TRNST_RULE(COPY_WF_ACTY_TRANSIT_CODE

      ,COPY_WF_ACTY_SOURCE_CODE

      ,COPY_WF_ACTY_TARGET_CODE

      ,RULE_TEXT

      ,ORDER_VAL

      ,GROUP_VAL

      ,GROUP_FROM_SPLIT_IND

      ,UPD_DATE

      ,MIS_DATE

)

values

(

:COPY_WF_ACTY_TRANSIT_CODE

      ,:COPY_WF_ACTY_SOURCE_CODE

      ,:COPY_WF_ACTY_TARGET_CODE

      ,:RULE_TEXT

      ,:ORDER_VAL

      ,:GROUP_VAL

      ,:GROUP_FROM_SPLIT_IND

      ,:UPD_DATE

      ,:MIS_DATE

) ;

.LOGOFF;

.EXIT;

EOI

Error:

*** Starting Row 0 at Sun Jul 12 08:02:15 2015

 *** Error: The following occurred during an Access Module read:

Unexpected data format !ERROR! EOF encountered before expected EOR.

*** Warning: Out of data.

 *** Finished at Sun Jul 12 08:02:15 2015

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

.LOGOFF;

*** You are now logged off from the DBC.

+---------+---------+---------+---------+---------+---------+---------+----

.EXIT;

*** Exiting BTEQ...

*** RC (return code) = 0

Re: Bteq to unload/load BLoB/CLOB data

Hi ,

You did multiple casting while exporting and using the format as like table structure in import. It trying to treat entire record as one field and failing. Better do export with some delimeter like | and user vartext format while importing. that will work.

Thanks,