Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement