Transfer Data From Teradata To Sybase IQ

This post is a detailed description of porting data from Teradata to Sybase IQ. I worked on this some time back and came across few learning opportunities which i would like to share. I am sure the audience is aware of what Teradata & Sybase IQ are; i will not get into describing those and get straight to the problem.

Problem Description: Extract data from a table in Teradata to a flat file and load the extracted flat data file in Sybase IQ.

Solution Steps:

  1. Connect to TD via BTEQ and export parameters.
  2. Run the “select” command to extract data to a flat file.
  3. Perform some transformations in unix shell before loading the flat file in Sybase IQ.
  4. Load the flat file in Sybase IQ.
Teradata_Port_Data

Transfer Data From Teradata To Sybase IQ

The first two steps involve extraction of data. For this, we connect to Teradata using BTEQ. BTEQ is a utility that is installed with Teradata Utility pack. It is used to connect to TD from unix shell and perform SQL operations. The following command set can be leveraged for extracting data to a flat file. Complete code link will be shared at the end of post.

# Note : Please change the connection parameters as per your requirement.
bteq >> /home/allzhere/TD_Extract.log <<EOF
.logmech 'ldap'
.logon 'TD_Server_IP_Address'/'user_name','password'
.set width 10000;
.set separator '|';
.export report file = /home/allzhere/TD_Extract.out;
select * from person_dtls;
.export reset
.quit
EOF

Once the data file is generated, we need to do some transformations on it before we can load it in Sybase IQ. The reason for that are some expectations by the Sybase IQ load table command. Let me brief about the command before i proceed to transformation. The load table command is provided by Sybase and is used to load a ASCII-format file into a table. It expects each record to be ended with a separator. This is our first reason for transformation. First thing is to strip of the headers that were exported from TD. Standard head - tail unix commands can be leveraged to extract the records from exported flat file. Also, TD extract will have columns separated by the selected delimiter but each record is ended with a new line character. For Sybase load to work, we need to append delimiter at the end of record. Also, you must replace the end line characters else every record after the first one will be appended with a new line character. Use the unix tr command for that. The following command will assist you with the first transformation required. It reads the TD extract file and appends “|” at the end of each record. It then removes “new line character” and appends to final load ready file.

cat /home/allzhere.TD_Extract.out | awk -F"|" '{print $1"|"$2"|"$3"|"}' | tr -d '\n' /home/allzhere.TD_Extract.ext

The last step is to load this in Sybase IQ. Following command set will come in handy. It is a simple load table command to load the flat file separated with a “|”.

 'LOAD TABLE td_extract
( id, name, age )
FROM /home/allzhere/TD_Extract.ext
QUOTES OFF ESCAPES OFF delimited by '|' ;

This completes our task to transfer data from TD to Sybase. The code files are uploaded on github.
I hope this post is helpful. Please provide your feedback to make it better.

@Facebook

Subscribe to Blog via Email

Quick Enquiry

Leave a Reply

Your email address will not be published. Required fields are marked *