All Forums General
radsubra 33 posts Joined 10/12
09 Sep 2014
BTEQ export through shell script

Hi,
 
I have to do a BTEQ export to a flat file,Below is the bteq script .
 
#!/bin/ksh

bteq<<eom

.BEGIN EXPORT SESSIONS 20;
.set errorout stdout;

.run file= /opt/etl/informatica/ExtProc/securefile.logon.dev;

.set width 256;
.Export data  File = /opt/etl/informatica/ParmFiles/ rda_ida_if172_parms_large_1.txt;
.set recordmode off;
.set titledashes on;

SELECT
        TRIM(FILE_NAME) (TITLE ''),
        TRIM(SERVER_NAME) (TITLE ''),
        TRIM(FOLDER_LOCATION) (TITLE '')
FROM CUST_D_CORE_ETL.RDA_ADHOC_CTL
WHERE FILE_ID=52
;

.if errorlevel <> 0 then .quit errorcode;
.if activitycount=0 then .quit 19;

.export reset;

.exit

eom
 
==========================================
 
Iam getting the out like below
abc.txt     dev    /uo3
 
I need the output like below
FILE_NAME = abc.txt
SERVER_NAME = dev
 FOLDER_LOCATION=/u03
 
can someone tell me how to achieve this?
 

balu_td 22 posts Joined 03/13
09 Sep 2014

Hi Radsubra,
I haven't got a chance to test the below sql with bteq export. But it worked fine on sql assistant.
sel CAST ( 'FILE_NAME' || TRIM(TBL.FILENAME) || x'0A'
                 ||  'SERVER_NAME' || TRIM(TBL.SERVER_NAME) || x'0A'
                 ||  'FOLDER_LOCATION' || TRIM(TBL.FOLDER_LOCATION)  AS VARCHAR(100)) AS D
                FROM CUST_D_CORE_ETL.RDA_ADHOC_CTL TBL
WHERE FILE_ID=52;
 
Thanks,
Balu

Raja_KT 1246 posts Joined 07/09
09 Sep 2014

You can also think of doing thus if casting to varchar is too big--- if it is convenient--- if it does not produce redundant data
SELECT
        'FILENAME'||'='||TRIM(FILE_NAME) (TITLE '') FROM CUST_D_CORE_ETL.RDA_ADHOC_CTL
WHERE FILE_ID=52
union
SELECT
         'SERVER_NAME'||'='||TRIM(SERVER_NAME) (TITLE '')
FROM CUST_D_CORE_ETL.RDA_ADHOC_CTL
WHERE FILE_ID=52
union
SELECT
         'FOLDER_LOCATION'||'='||TRIM(FOLDER_LOCATION) (TITLE '')
FROM CUST_D_CORE_ETL.RDA_ADHOC_CTL
WHERE FILE_ID=52
;
If it produces redundant data, then you can restrict by partitioning.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

radsubra 33 posts Joined 10/12
10 Sep 2014

Thanks Raja .it worked.

You must sign in to leave a comment.