Advantage of Export-Template is that it eliminates the need to define separate TPT script for each of the table.

TPT Export-Template

TPT Export-Template is a file producer which works similar to Fastexport utility. Advantage of Export-Template is that it eliminates the need to define separate TPT script for each of the table.

There are five data formats in which data can be exported using TPT. Please visit TPT-Export page for detail of each format type.

• Delimited
• Unformatted
• Formatted
• Binary
• Text

Points to be noted: Since TPT Export-Template is extension of TPT Export, please visit TPT-Export page to get more detail about export related attributes.

1. Six basic attributes are mandatory( that are UserName, UserPassword, SelectStmt, Tdpid, FileName and Format) for using TPT Export-Template.
2. TPT Export-Template eliminate the need of defining 'OPERATOR' separately.
3. There is no need to define schema as Export-Template generates schema from the query passed as variable.
4. Careful! always define maximum number of sessions(using MaxSessions) that can be allocated to the job, else it may end up taking all the sessions.

Sample Script: TPT Export-Template script(tpt_export_template.tpt) can be used to export file of any format.

#tpt_export_template.tpt
USING CHARACTER SET @var_utf 
DEFINE JOB tpt_export_template 
(
  APPLY TO OPERATOR ($FILE_WRITER() ATTR (FileName=@var_outfile, Format=@var_format, TextDelimiter=@var_delimiter_value,  DirectoryPath=@var_directorypath))
  SELECT * FROM OPERATOR($EXPORT() ATTR (TdpId=@var_tdpid, UserName=@var_userid, UserPassword=@var_password, SelectStmt=@var_exportquery, MaxSessions=@var_maxsessions, DateForm = @var_dateform, MaxDecimalDigits = 38));
);

Click here to download the above Export-Template script.


➠ Executing Export-Template scripts

• Generate Delimited File: Executing above TPT Export-Template script and generating file in delimited format.

  tbuild -f /path_to_file/tpt_export_template.tpt \
  -u " var_tdpid='TDSERVER', \
  var_userid='tutorial_user', \
  var_password='tutorial_user', \
  var_exportquery='LOCK ROW FOR ACCESS SELECT * FROM tutorial_db.employee;' , \
  var_directorypath='/path_to_output_file', \
  var_outfile='export_template_outfile.txt', \
  var_delimiter_value='|*|', \
  var_utf='UTF8', \
  var_maxsessions=4, \
  var_dateform='ANSIDATE' \
  var_format='DELIMITED'"
  

  
  
• Generate Unformatted File: Executing same TPT Export-Template script and generating file in 'UNFORMATTED' format.

  tbuild -f /path_to_file/tpt_export_template.tpt \
  -u " var_tdpid='TDSERVER', \
  var_userid='tutorial_user', \
  var_password='tutorial_user', \
  var_exportquery='LOCK ROW FOR ACCESS SELECT * FROM tutorial_db.employee;' , \
  var_directorypath='/path_to_output_file', \
  var_outfile='export_template_outfile_unformatted.txt', \
  var_delimiter_value='none', \
  var_utf='UTF8', \
  var_maxsessions=4, \
  var_dateform='ANSIDATE' \
  var_format='UNFORMATTED'"
  

  
  
• Generate Formatted File: Executing same TPT Export-Template script and generating file in 'FORMATTED' format.

  tbuild -f /path_to_file/tpt_export_template.tpt \
  -u " var_tdpid='TDSERVER', \
  var_userid='tutorial_user', \
  var_password='tutorial_user', \
  var_exportquery='LOCK ROW FOR ACCESS SELECT * FROM tutorial_db.employee;' , \
  var_directorypath='/path_to_output_file', \
  var_outfile='export_template_outfile_formatted.txt', \
  var_delimiter_value='none', \
  var_utf='UTF8', \
  var_maxsessions=4, \
  var_dateform='ANSIDATE' \
  var_format='FORMATTED'"
  

  
  
• Generate Binary File: Executing same TPT Export-Template script and generating file in 'BINARY' format.

  tbuild -f /path_to_file/tpt_export_template.tpt \
  -u " var_tdpid='TDSERVER', \
  var_userid='tutorial_user', \
  var_password='tutorial_user', \
  var_exportquery='LOCK ROW FOR ACCESS SELECT * FROM tutorial_db.employee;' , \
  var_directorypath='/path_to_output_file', \
  var_outfile='export_template_outfile_binary.txt', \
  var_delimiter_value='none', \
  var_utf='UTF8', \
  var_maxsessions=4, \
  var_dateform='ANSIDATE' \
  var_format='BINARY'"