Code to automatically generate Teradata stats

teradata_stats.rb

# You are going to need to use jruby and install the jdbc-teradata gem

# Create a file named database.yml and fill it out.  It will look something like this:
# production:
#   adapter: jdbc
#   driver: com.teradata.jdbc.TeraDriver
#   url: jdbc:teradata://localhost/DATABASE=ods,DBS_PORT=1025,COP=OFF
#   username: your_username
#   password: your_password

require 'jdbc/teradata'
Jdbc::Teradata::load_driver
require 'active_record'
require 'pp'

dbconfig = YAML::load(File.open('database.yml'))

ActiveRecord::Base.establish_connection(dbconfig['production'])

def get_index_data(table_name, index_number, index_type)
  sql = []
  sql << "SELECT DatabaseName, TableName, IndexNumber, IndexType, IndexName, ColumnName"
  sql << "FROM dbc.indices"
  sql << "WHERE databasename = 'ods'"
  sql << "AND TableName = '#{table_name}'"
  sql << "AND IndexNumber = #{index_number}"
  sql << "AND IndexType = '#{index_type}'"
  sql << "AND indextype in ('P','S','Q','K','V','I')"

  ActiveRecord::Base.connection.execute(sql.join(' '))
end

sql = []
sql << "SELECT DatabaseName, TableName, IndexNumber, IndexType, IndexName"
sql << "FROM dbc.indices"
sql << "WHERE databasename = 'ods'"
sql << "and indextype in ('P','S','Q','K','V','I')"
sql << "GROUP BY 1,2,3,4,5"
sql << "ORDER BY 1,2,3,4,5"

items = ActiveRecord::Base.connection.execute(sql.join(' '))

items.each do |item|

  sql = []

  if item['IndexType'].strip == 'I'
    sql << "collect statistics on"
    sql << "#{item['DatabaseName'].strip}.#{item['TableName'].strip}"
    sql << "column"
  else
    sql << "collect statistics on"
    sql << "#{item['DatabaseName'].strip}.#{item['TableName'].strip}"
    sql << "index"
  end

  data = get_index_data(item['TableName'].strip, item['IndexNumber'], item['IndexType'].strip)

  columns = data.map { |d| d['ColumnName'].strip }.join(", ")

  sql << "(#{columns})"

  puts sql.join(' ')

  ActiveRecord::Base.connection.execute(sql.join(' '))
end