retry problem

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