hi
I am trying to update the following method as the retry we have hard coded to ensure the sql transaction retires three times does not work.
When we get a failure it rolls back but does not retry.
Is there another I can add a retry or correct the following code
Gurdipe
def export_data( table_nickname, output_data_file, column_headers )
component,table_nickname = get_component(table_nickname)
data_warehouse_class = component.class
data_warehouse_instance = data_warehouse_class.instance
Get table name
teradata_table_name = data_warehouse_instance.send(table_nickname).to_s
start_time = Time.now()
Create column list, with various casting and reformatting
column_list =
column_headers.each do |column_header|
format = “”
if column_header.type == ‘DA’
format = “(format ‘YYYY/MM/DD’)”
end
column_def = “CAST( COALESCE( TRIM( #{column_header.name} #{format} ) , ‘’) as VARCHAR(#{column_header.max_length+10}) )” # Some data types need extra space so add 10 chars
column_list.push(column_def)
end
tries = 3 # try three times and then give up
begin
Create fastexport definition
fed = <<END_FED
.logtable DXWI_PROD_CRORDER_PLAY_PEN.promo_fexp_#{Time.now.strftime(“%Y%m%d%H%M%S”)}_log;
.logon tdpm/#{DATA_WAREHOUSE_CONFIG[‘username’]},#{DATA_WAREHOUSE_CONFIG[‘password’]};
.begin export sessions 10;
.export outfile #{output_data_file}
format fastload
mode record;
/* FETCH DATA */
SELECT
#{column_list.join(" ,\n")}
FROM #{teradata_table_name}
;
/* END */
.end export;
/* LOGOFF */
.logoff;
/* DONE */
END_FED
Write definition to a temporary file, because calling it from a ruby pipe can cause blocks
on tables with lots of columns (denorm_brag_report)
fed_file = “#{GENERAL_SYSTEM_SETTINGS[‘fastexport_temp_dir’]}/#{teradata_table_name.downcase}_data.fed”
File.open(fed_file, ‘w’) {|f| f.write(fed) }
RAILS_DEFAULT_LOGGER.info(“FLA_FILE_PATH: <#{output_data_file}>”)
RAILS_DEFAULT_LOGGER.info(“TERADATA_TABLE: <#{teradata_table_name}>”)
RAILS_DEFAULT_LOGGER.info(“Fastexport Definition File:\n #{fed_file}\n”)
RAILS_DEFAULT_LOGGER.info(“Fastexport Definition:\n #{fed}\n”)
Use fexp (fastexport) as system command
RAILS_DEFAULT_LOGGER.info(“Executing fastexport”)
output = nil
open_return = IO.popen(“fexp < #{fed_file}”, “w+”) do |pipe|
pipe.close_write()
output = pipe.read()
end
rescue
tries -= 1
sleep(1) # since we sometime get a time stamp problem, wait one second and then retry
retry if tries > 0
raise # re-raise the exception if it hasn’t been retried
end
Check results
result = $?
if result == 0
RAILS_DEFAULT_LOGGER.info(“–”)
RAILS_DEFAULT_LOGGER.info(“Finished Extracting data from Teradata (#{Time.now()-start_time} seconds)”)
RAILS_DEFAULT_LOGGER.info(“–”)
else
RAILS_DEFAULT_LOGGER.info(“Failed Extracting data from Teradata (#{Time.now()-start_time} seconds) - Error code: #{result}”)
RAILS_DEFAULT_LOGGER.info(“Output from FastExport:\n#{output}”)
raise(“ERROR: Export of Table <#{teradata_table_name}> failed - Error Code #{result}.”)
end
return
end