BTEQ export through shell script

General
Enthusiast

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?

3 REPLIES
Enthusiast

Re: BTEQ export through shell script

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

Enthusiast

Re: BTEQ export through shell script

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.

Enthusiast

Re: BTEQ export through shell script

Thanks Raja .it worked.