Teradata Bteq .export file truncating the result.

Tools

Teradata Bteq .export file truncating the result.

Hi All,

I have a Bteq script and have an export file. the SQL generates set if insert statements and then i call the export file. but it fails with an error that one of the insert is truncated. when i see the exported file the result exported truncates the insert statements after some inserts. below is script. can you please let me know what is causing the result to get truncated and what to do in order to avoid it ?

 

.RUN FILE=/data_uat/shared/ANP_SERVICE/LkpFiles/SAS_WRITEBACKS/ZA/Test/login.txt;
.EXPORT DATA FILE=/data_uat/shared/ANP_SERVICE/LkpFiles/SAS_WRITEBACKS/ZA/Test/export2.txt;
.set RECORDMODE OFF;
.set WIDTH 65531;
.set RETRY OFF;
SELECT 'INSERT INTO TD_82933_L_PARATESTDB.COLUMNS_COUNT_RANK SELECT '''
|| TRIM(CTC.SRC_DATABASE) || ''','''||TRIM(CTC.SRC_TABLE)||''','''||TRIM(CTC.SRC_COLUMN)||''','||
'CAST('||TRIM(CTC.SRC_COLUMN)||' AS VARCHAR(250))'||',COUNT(*) AS CNT,ROW_NUMBER() OVER (PARTITION BY '''|| TRIM(CTC.SRC_COLUMN) ||''' ORDER BY CNT DESC) AS RNK,'''||TRIM(LENGTH_OF_COL)||''','||'CURRENT_TIMESTAMP FROM '|| TRIM(CTC.SRC_DATABASE) || '.' || TRIM(CTC.SRC_TABLE)
|| ' GROUP BY 1,2,3,4,7 QUALIFY RNK<255 AND CNT>(SELECT CNT FROM TD_82933_L_PARATESTDB.VAL30_CNT WHERE SRC_DB='''||TRIM(CTC.SRC_DATABASE)||''' AND SRC_TBL='''||TRIM(SRC_TABLE)||''' AND SRC_COL='''||TRIM(SRC_COLUMN)||''');'
FROM TD_82933_L_PARATESTDB.COLUMNS_TO_COMP CTC;
.RUN FILE=/data_uat/shared/ANP_SERVICE/LkpFiles/SAS_WRITEBACKS/ZA/Test/export2.txt;
.quit

 

 

  • bteq
  • export
  • Teradata

Accepted Solutions

Re: Teradata Bteq .export file truncating the result.

Hi.

 

You must execute an .EXPORT RESET to close the file before calling it with the .RUN FILE

 

...

FROM TD_82933_L_PARATESTDB.COLUMNS_TO_COMP CTC;

.EXPORT RESET;

.RUN FILE=/data_uat/shared/ANP_SERVICE/LkpFiles/SAS_WRITEBACKS/ZA/Test/export2.txt;

 

 

HTH.

 

Cheers.

 

Carlos.

1 ACCEPTED SOLUTION
2 REPLIES

Re: Teradata Bteq .export file truncating the result.

Hi.

 

You must execute an .EXPORT RESET to close the file before calling it with the .RUN FILE

 

...

FROM TD_82933_L_PARATESTDB.COLUMNS_TO_COMP CTC;

.EXPORT RESET;

.RUN FILE=/data_uat/shared/ANP_SERVICE/LkpFiles/SAS_WRITEBACKS/ZA/Test/export2.txt;

 

 

HTH.

 

Cheers.

 

Carlos.

Re: Teradata Bteq .export file truncating the result.

Thanks Carlos, it worked.