BTEQ export is storing as Unformatted data format in UNIX

Database
Enthusiast

BTEQ export is storing as Unformatted data format in UNIX

Hi ,

I am facing a issue while creating a file in unix.

I have created a Sored procedure which will create a unix script dynamically and I am storing the created script in a table. later I will create a file from table and run that script.

the problem is when I am using a BTEQ and retrieving the created script from TD table,It is created as single record and all the script formating is missed and entire content is stored as single line.

I have tried all the modes of BTEQ and TPT but no use.when I am using the export in SQL assistant it is creating a file as correct formatted UNIX format and also working if we copy the table to notepad.only the issue is when we are exporting

 

Kindly let me know if any one know the solution for this issue.

Thanks

10 REPLIES
Apprentice

Re: BTEQ export is storing as Unformatted data format in UNIX

Hi,

In your BTEQ script what is your EXPORT mode? (".export ??? file = ..")

In your table is the script in a single row or spread across multiple rows?

 

From your description, you'll need to use 'export report' and make sure to put "(title '')" after every column in your select list.

 

If that doesn't help then please post some code? It is usually much easier to help you if you provide some code - preferably to re-create your error situation.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: BTEQ export is storing as Unformatted data format in UNIX

Thanks for the reply Dave

My table is having only two columns one is required column and another one is time_stamp column and my data is only single row.

 

I am using below mentioned BTEQ:

.QUIET ON;
.PACK 10000;
.logon SERVER/USERNAME,PWD
.IF ERRORCODE <> 0 THEN EXIT;
.EXPORT REPORT FILE=PATH/DYNAMIC_EXTRACT.ksh
.SET RECORDMODE OFF;
.SET SEPARATOR ' '
.SET FORMAT ON;
.SET WIDTH 22000;
.SET PAGELENGTH 40000;
.SET TITLEDASHES OFF;
.SET ECHOREQ OFF;

SEL col1 FROM DB_NAME.DYNAMIC_SQL_HOLDER;
.EXPORT RESET;
.QUIT;

 

 

sample output what I am currently getting is 

 

#!/bin/ksh set -x ##### Purpose:  This script will load the Iforce Brand level,DDD level,DDD le   help() {   cat <<HELP  This script is used for Generating a dynamic extract as requested  HELP exit 0 }  error() {     # print an error and exit     echo "$1" "$2" >&1     cif_log $0 error "$1"     exit 1 }  # The option parser while [ -n "$1" ]; do case $1 in     -h) help;shift 1;; # function help is called     --) shift;break;; # end of options     -*) echo "error: no such option $1. -h for help";exit 1;;     *)  break;; esac done  #checking for parameters passed if test $# -lt 2 then     error "Wrong number of parameters have been passed to the script.\012Usage: $0 listfilename  env_file\012Aborting  process...."      exit 50 fi  ts=`date +%Y%m%d`    ............

 

REQUIRED OUTPUT SHOULD BE:

#!/bin/ksh

set -x

##### Purpose: This script will load the Iforce Brand level,DDD level,DDD le

# The option parser
while [ -n "$1" ]; do
case $1 in
-h) help;shift 1;; # function help is called
--) shift;break;; # end of options
-*) echo "error: no such option $1. -h for help";exit 1;;
*) break;;
esac
done

if test $# -lt 1
then
error "Wrong number of parameters have been passed to the script.\012Usage: $0 env_file \012Aborting process...."

exit 50
fi.........

Apprentice

Re: BTEQ export is storing as Unformatted data format in UNIX

Hi,

If the data in your table is a single row then BTEQ is doing what is expected. Your exporting a single value which BTEQ is writing to a single record.

I the data 'displays' as you'd like it in SQLA then I think this is simply that application formatting the display for you.

 

Just a thought, when you build that 'single value' are you putting in line breaks?

 

If so are you putting in Windows line breaks (CR/LF) or Unix ones (I think LF).?

 

If Windows, use Unix ones instead as that is where you're exporting the data to.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: BTEQ export is storing as Unformatted data format in UNIX

Hi,

I am just using the script of final to be script and storing to a variable(it is not continuous text it is formatted) in procedure,then I am doing some calculations and then adding to final variable and loading that variable to a table.

is there a way to get the final file as the formated one?

 

Thanks

Junior Contributor

Re: BTEQ export is storing as Unformatted data format in UNIX

REPORT mode replaces any control characters (including CR/LF) with spaces (well, it's supposed to be printed):

http://info.teradata.com/htmlpubs/DB_TTU_16_00/Query_Management_Tools/B035-2414-086K/FieldModeTransl...

 

There's a new option in 16.10, TRANSLATECTRLSTOSPACES, which seems to disable that (never tried it).

 

Before you can switch to DATA format, but then you get a 2 byte record length and 2 byte VarChar length in front of the data, which you have to strip off after export, e.g. using tail -c +5

 

 

 

Enthusiast

Re: BTEQ export is storing as Unformatted data format in UNIX

Hi Dnoeth,

Thanks for reply

My version is 15.

is there any way(by different utilites/methoods) in acheiving my output?

Apprentice

Re: BTEQ export is storing as Unformatted data format in UNIX

Dieter's change with EXPORT DATA and the tail command might be easiest.

 

An alternative (which means changing your SP) is to build each line of your final script as a separate row in the table.. That way each comes out as a separate line in the output file.

 Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: BTEQ export is storing as Unformatted data format in UNIX

Hi ,

I tried by using 

 

.EXPORT REPORT FILE

but no use finally I have added some group of special characters and then replaced with new line(an extra step) :)

 

Thanks

Junior Contributor

Re: BTEQ export is storing as Unformatted data format in UNIX

It's .EXPORT DATA instead of .EXPORT REPORT