Teradata DB getting full need to compress & export to a UNIX server

Database
Highlighted
Enthusiast

Teradata DB getting full need to compress & export to a UNIX server

Hi All,

 

I'm not familiar with this process of exporting and compressing data over to UNIX and wondered if anyone had done this before or had any useful scripts/tips?

 

Thanks


Accepted Solutions
Junior Contributor

Re: Teradata DB getting full need to compress & export to a UNIX server

It will not find the file it will create it.

The default location for the new file will be the directory from where you called the TPT script, if you want a different location you can add the file path, e.g. '/home/myuser/AA_PB_TBL_AUS_STATES.csv.zip'

1 ACCEPTED SOLUTION
9 REPLIES
Enthusiast

Re: Teradata DB getting full need to compress & export to a UNIX server

Also wondering if TPT can be used to do this task?

 

I've started creating a job with the destination being another teradata db but I would need an option to connect to the UNIX server.

So could I for example use a teradata driver & connect to the UNIX server as a destination?

All suggestions welcome.

Teradata Employee

Re: Teradata DB getting full need to compress & export to a UNIX server

Hi.

 

Have you considered a backup with good old armain, optionally compress it, transfer it to the new destination and restore it with good old arcmain again?

 

Cheers.

 

Carlos.

Enthusiast

Re: Teradata DB getting full need to compress & export to a UNIX server

Thanks CarlosAlvarez,

 

I installed every feature of TTU but I didn't see Arcmain there?

How do I access it?

 

Thanks Peter

Junior Contributor

Re: Teradata DB getting full need to compress & export to a UNIX server

TPT automatically compresses files when you use the .zip or .gz extension.

Easiest way would be running the export from the Unix server.

 

Enthusiast

Re: Teradata DB getting full need to compress & export to a UNIX server

Thanks dnoeth,

My first thought is to get a script from the Teradata userguide files to export to UNIX as a text file & I can use those suffixes to zip.

  1. If I write from the UNIX side, that would be an 'Import' wouldn't it? Importing from Teradata I mean.
  2. So could I use a Teradata import/load script & run this on the UNIX side? (I'm totally unfamiliar with anything but Windows) 
  3. If option 2 isn't possible, I have attached my (unfinished) Teradata Export script to send my data over to the UNIX server.
  4. As far as what I've got to work with, I have the credentials to log onto the UNIX server (and also the Tewradata credentials obviously)
  5. I've seen the variable files where an external text file can have the parameter definitions but I want to keep things simple for now so my script below still needs referring to the UNIX side but I'm unsure what goes where??

I'd appreciate it if you could give me an idea of what my script needs to work.

Note this file is from the Teradata User guide with some changes from me. 

 

/**************************************************************/
/*                                                            */
/* Explanation:                                               */
/*                                                            */
/* This script uses the Export operator as producer and       */
/* the DataConnector operator as consumer.                    */
/*                                                            */
/* This script shows how to use the following Teradata        */
/* Parallel Transporter features:                             */
/*                                                            */
/* - Load the data from Teradata table to a flat file using   */
/*   the TPT DataConnector operator, in delimited format.     */
/*                                                            */
/* - Using "job variables", which are defined in the external */
/*   file "jobvars.txt". These variables can be used anywhere */
/*   inside the job script by prepending the @ symbol to the  */
/*   variable name.                                           */
/*                                                            */
/* - Support of data files created in Delimited format, even  */
/*   when the source data is non-varchar.                     */
/*                                                            */
/**************************************************************/
/*                                                            */
/* Required customizations before executing this script:      */
/*                                                            */
/* Inside the file "jobvars.txt", modify the values for       */
/* the following job variables:                               */
/*                                                            */
/*    ExportTdpId, ExportUserName, ExportUserPassword         */
/*                                                            */
/* where                                                      */
/*                                                            */
/*    ExportTdpId        - Name of the TD database system.    */
/*                                                            */
/*    ExportUserName     - Logon UserName to logon to         */
/*                         Teradata database                  */
/*                                                            */
/*    ExportUserPassword - Logon Password to logon to         */
/*                         Teradata database                  */
/*                                                            */
/**************************************************************/
/* Execution:                                                 */
/*                                                            */
/* On Unix and Windows, use the following command to execute  */
/* this script:                                               */
/*                                                            */
/*    tbuild -f PTS00016 -v jobvars.txt -j PTS00016           */
/*                                                            */
/* For executing this script in mainframes, use the           */
/* accompanying JCL.                                          */
/*                                                            */
/**************************************************************/

DEFINE JOB EXPORT_DELIMITED_FILE

DESCRIPTION 'Export rows from a Teradata table to a delimited file'

(

--- my additions start below - I've created a sample table in Teradata 
--- to use in this export test

DEFINE SCHEMA datalab.AA_PB_TBL_AUS_STATES 

(
       STATE_ID		VARCHAR(2),
       STATE		VARCHAR(30),
       ABBR		VARCHAR(3),
       CAPITAL		VARCHAR(30),
       LARGEST_CITY     VARCHAR(30)
);

APPLY TO OPERATOR ($FILE_WRITER()[@WriterInstances] 	--- unsure what I should do here?
 
ATTR (FileName = 'AA_PB_TBL_AUS_STATES.txt') )		--- unsure what I should do here?	

SELECT * FROM OPERATOR ($EXPORT(Dest_Host)[@UNIX_Host] 	--- unsure what I should do here?
                                                        --- I would rather have the server name user & pwd added here
							--- rather than a variable table (Until I follow this better) 

ATTR (SelectStmt = 'select * from datalab.AA_PB_TBL_AUS_STATES);

); 
);

--------- Finally, how do I run this?
--------- The instructions advise to use 'tbuild -f PTS00016 -v jobvars.txt -j PTS00016'
--------- (Obviously, I change the table names) 

 

    

Junior Contributor

Re: Teradata DB getting full need to compress & export to a UNIX server

 1. If I write from the UNIX side, that would be an 'Import' wouldn't it? Importing from Teradata I mean.

 

No, this is exporting from Teradata. Either import TO or export FROM Teradata.

 

 

2. So could I use a Teradata import/load script & run this on the UNIX side? (I'm totally unfamiliar with anything but Windows) 

 

You run the script on any server where's TPT installed (this includes a Teradata node or Windows/Linux/Mac/Mainframe)

 

If option 2 isn't possible, I have attached my (unfinished) Teradata Export script to send my data over to the UNIX server.

As far as what I've got to work with, I have the credentials to log onto the UNIX server (and also the Teradata credentials obviously)

I've seen the variable files where an external text file can have the parameter definitions but I want to keep things simple for now so my script below still needs referring to the UNIX side but I'm unsure what goes where??

 

You an simply hardcode everthing in the script, but at least username/password should be applied using a job variable file.

 

Finally, how do I run this?

 

Create/modify jobvars.txt like this:

 

 /*** your TD system/user/password ***/
SourceTdpid        = '...'
,SourceUserName     = '...'
,SourceUserPassword = '...'

 /*** defining the export format ***/
,TargetFormat        = 'DELIMITED'
,TargetTextDelimiter = ','

 /*** name of the exported file (automatically zipped because of .zip) ***/
,TargetFileName = 'cust.csv.zip'

 /*** your Select ***/
,SelectStmt   = 
   'SELECT *
    FROM au.Customer;'

 

 

Then save this as mytest.tpt:

 

DEFINE JOB TPT_generic_export
DESCRIPTION 'Generic export using the EXPORT operator'
(
   APPLY TO OPERATOR( $FILE_WRITER 
)
   SELECT * FROM OPERATOR( $SELECTOR );
);

and finally run it on Unix command line:

tbuild -f mytest.tp -v jobvars.txt -j myexport

The -j option is not really needed, it's just the name assigned to the job (instead of automatically named).

 

 

Enthusiast

Re: Teradata DB getting full need to compress & export to a UNIX server

Thanks dnoeth,

With the jobvars.txt file & the Select statement.

How does the script find the .csv table I want to export?

My guess is the .csv file is on (available to) the UNIX server, (maybe in the same folder?)

Then the UNIX script is run using the 2 files created, the .csv & the .tpt.

So the select statement will find the .csv file?

 

Here's my script to run in the jobvars file.

/*** your TD system/user/password ***/
SourceTdpid        = 'tpid'
,SourceUserName     = 'xxxx'
,SourceUserPassword = 'xxxx'

 /*** defining the export format ***/
,TargetFormat        = 'DELIMITED'
,TargetTextDelimiter = ','

 /*** name of the exported file (automatically zipped because of .zip) ***/
,TargetFileName = 'AA_PB_TBL_AUS_STATES.csv.zip'

 /*** your Select ***/
,SelectStmt   = 
   'SELECT *
    FROM AA_PB_TBL_AUS_STATES;'

Thanks Peter

 

Junior Contributor

Re: Teradata DB getting full need to compress & export to a UNIX server

It will not find the file it will create it.

The default location for the new file will be the directory from where you called the TPT script, if you want a different location you can add the file path, e.g. '/home/myuser/AA_PB_TBL_AUS_STATES.csv.zip'

Enthusiast

Re: Teradata DB getting full need to compress & export to a UNIX server

Thanks dnoeth,

Much appreciated, I'll give it a try.

I'll need someone else to try it as I don't have access.

Cheers Peter

 

Tags (1)