module Sequel::DB2::DatabaseMethods
Constants
- AUTOINCREMENT
- DATABASE_ERROR_REGEXPS
- NOT_NULL
- NULL
Public Instance Methods
DB2 always uses :db2 as it's database type
# File lib/sequel/adapters/shared/db2.rb, line 22 def database_type :db2 end
Return the database version as a string. Don't rely on this, it may return an integer in the future.
# File lib/sequel/adapters/shared/db2.rb, line 28 def db2_version return @db2_version if defined?(@db2_version) @db2_version = metadata_dataset.with_sql("select service_level from sysibmadm.env_inst_info").first[:service_level] end
# File lib/sequel/adapters/shared/db2.rb, line 34 def freeze db2_version offset_strategy super end
Use SYSCAT.INDEXES to get the indexes for the table
# File lib/sequel/adapters/shared/db2.rb, line 79 def indexes(table, opts = OPTS) m = output_identifier_meth indexes = {} metadata_dataset. from(Sequel[:syscat][:indexes]). select(:indname, :uniquerule, :colnames). where(:tabname=>input_identifier_meth.call(table), :system_required=>0). each do |r| indexes[m.call(r[:indname])] = {:unique=>(r[:uniquerule]=='U'), :columns=>r[:colnames][1..-1].split('+').map{|v| m.call(v)}} end indexes end
# File lib/sequel/adapters/shared/db2.rb, line 92 def offset_strategy return @offset_strategy if defined?(@offset_strategy) @offset_strategy = case strategy = opts[:offset_strategy].to_s when "limit_offset", "offset_fetch" opts[:offset_strategy] = strategy.to_sym else opts[:offset_strategy] = :emulate end end
Use SYSIBM.SYSCOLUMNS to get the information on the tables.
# File lib/sequel/adapters/shared/db2.rb, line 41 def schema_parse_table(table, opts = OPTS) m = output_identifier_meth(opts[:dataset]) im = input_identifier_meth(opts[:dataset]) metadata_dataset.with_sql("SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = #{literal(im.call(table))} ORDER BY COLNO"). collect do |column| column[:db_type] = column.delete(:typename) if column[:db_type] =~ /\A(VAR)?CHAR\z/ column[:db_type] << "(#{column[:length]})" end if column[:db_type] == "DECIMAL" column[:db_type] << "(#{column[:longlength]},#{column[:scale]})" end column[:allow_null] = column.delete(:nulls) == 'Y' identity = column.delete(:identity) == 'Y' if column[:primary_key] = identity || !column[:keyseq].nil? column[:auto_increment] = identity end column[:type] = schema_column_type(column[:db_type]) column[:max_length] = column[:longlength] if column[:type] == :string [ m.call(column.delete(:name)), column] end end
DB2 supports transaction isolation levels.
# File lib/sequel/adapters/shared/db2.rb, line 104 def supports_transaction_isolation_levels? true end
On DB2, a table might need to be REORGed if you are testing existence of it. This REORGs automatically if the database raises a specific error that indicates it should be REORGed.
# File lib/sequel/adapters/shared/db2.rb, line 111 def table_exists?(name) v ||= false # only retry once sch, table_name = schema_and_table(name) name = SQL::QualifiedIdentifier.new(sch, table_name) if sch from(name).first true rescue DatabaseError => e if e.to_s =~ /Operation not allowed for reason code "7" on table/ && v == false # table probably needs reorg reorg(name) v = true retry end false end
Use SYSCAT.TABLES to get the tables for the database
# File lib/sequel/adapters/shared/db2.rb, line 65 def tables metadata_dataset. with_sql("SELECT TABNAME FROM SYSCAT.TABLES WHERE TYPE='T' AND OWNER = #{literal(input_identifier_meth.call(opts[:user]))}"). all.map{|h| output_identifier_meth.call(h[:tabname]) } end
Use SYSCAT.TABLES to get the views for the database
# File lib/sequel/adapters/shared/db2.rb, line 72 def views metadata_dataset. with_sql("SELECT TABNAME FROM SYSCAT.TABLES WHERE TYPE='V' AND OWNER = #{literal(input_identifier_meth.call(opts[:user]))}"). all.map{|h| output_identifier_meth.call(h[:tabname]) } end
Private Instance Methods
Handle DB2 specific alter table operations.
# File lib/sequel/adapters/shared/db2.rb, line 130 def alter_table_sql(table, op) case op[:op] when :add_column if op[:primary_key] && op[:auto_increment] && op[:type] == Integer [ "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op.merge(:auto_increment=>false, :primary_key=>false, :default=>0, :null=>false))}", "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{literal(op[:name])} DROP DEFAULT", "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{literal(op[:name])} SET #{AUTOINCREMENT}" ] else "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}" end when :drop_column "ALTER TABLE #{quote_schema_table(table)} DROP #{column_definition_sql(op)}" when :rename_column # renaming is only possible after db2 v9.7 "ALTER TABLE #{quote_schema_table(table)} RENAME COLUMN #{quote_identifier(op[:name])} TO #{quote_identifier(op[:new_name])}" when :set_column_type "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} SET DATA TYPE #{type_literal(op)}" when :set_column_default "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} SET DEFAULT #{literal(op[:default])}" when :add_constraint if op[:type] == :unique sqls = op[:columns].map{|c| ["ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(c)} SET NOT NULL", reorg_sql(table)]} sqls << super sqls.flatten else super end else super end end
REORG the related table whenever it is altered. This is not always required, but it is necessary for compatibilty with other Sequel code in many cases.
# File lib/sequel/adapters/shared/db2.rb, line 166 def apply_alter_table(name, ops) alter_table_sql_list(name, ops).each do |sql| execute_ddl(sql) reorg(name) end end
DB2 uses an identity column for autoincrement.
# File lib/sequel/adapters/shared/db2.rb, line 174 def auto_increment_sql AUTOINCREMENT end
DB2 does not allow adding primary key constraints to NULLable columns.
# File lib/sequel/adapters/shared/db2.rb, line 179 def can_add_primary_key_constraint_on_nullable_columns? false end
Supply columns with NOT NULL if they are part of a composite primary key or unique constraint
# File lib/sequel/adapters/shared/db2.rb, line 185 def column_list_sql(g) ks = [] g.constraints.each{|c| ks = c[:columns] if [:primary_key, :unique].include?(c[:type])} g.columns.each{|c| c[:null] = false if ks.include?(c[:name]) } super end
Insert data from the current table into the new table after creating the table, since it is not possible to do it in one step.
# File lib/sequel/adapters/shared/db2.rb, line 194 def create_table_as(name, sql, options) super from(name).insert(sql.is_a?(Dataset) ? sql : dataset.with_sql(sql)) end
DB2 requires parens around the SELECT, and DEFINITION ONLY at the end.
# File lib/sequel/adapters/shared/db2.rb, line 200 def create_table_as_sql(name, sql, options) "#{create_table_prefix_sql(name, options)} AS (#{sql}) DEFINITION ONLY" end
Here we use DGTT which has most backward compatibility, which uses DECLARE instead of CREATE. CGTT can only be used after version 9.7. www.ibm.com/developerworks/data/library/techarticle/dm-0912globaltemptable/
# File lib/sequel/adapters/shared/db2.rb, line 207 def create_table_prefix_sql(name, options) if options[:temp] "DECLARE GLOBAL TEMPORARY TABLE #{quote_identifier(name)}" else super end end
# File lib/sequel/adapters/shared/db2.rb, line 222 def database_error_regexps DATABASE_ERROR_REGEXPS end
DB2 has issues with quoted identifiers, so turn off database quoting by default.
# File lib/sequel/adapters/shared/db2.rb, line 228 def quote_identifiers_default false end
DB2 uses RENAME TABLE to rename tables.
# File lib/sequel/adapters/shared/db2.rb, line 233 def rename_table_sql(name, new_name) "RENAME TABLE #{quote_schema_table(name)} TO #{quote_schema_table(new_name)}" end
Run the REORG TABLE command for the table, necessary when the table has been altered.
# File lib/sequel/adapters/shared/db2.rb, line 239 def reorg(table) execute_ddl(reorg_sql(table)) end
The SQL to use for REORGing a table.
# File lib/sequel/adapters/shared/db2.rb, line 244 def reorg_sql(table) "CALL SYSPROC.ADMIN_CMD(#{literal("REORG TABLE #{quote_schema_table(table)}")})" end
Treat clob as blob if use_clob_as_blob is true
# File lib/sequel/adapters/shared/db2.rb, line 249 def schema_column_type(db_type) (::Sequel::DB2::use_clob_as_blob && db_type.downcase == 'clob') ? :blob : super end
SQL to set the transaction isolation level
# File lib/sequel/adapters/shared/db2.rb, line 254 def set_transaction_isolation_sql(level) "SET CURRENT ISOLATION #{Database::TRANSACTION_ISOLATION_LEVELS[level]}" end
We uses the clob type by default for Files. Note: if user select to use blob, then insert statement should use use this for blob value:
cast(X'fffefdfcfbfa' as blob(2G))
# File lib/sequel/adapters/shared/db2.rb, line 262 def type_literal_generic_file(column) ::Sequel::DB2::use_clob_as_blob ? :clob : :blob end
DB2 uses smallint to store booleans.
# File lib/sequel/adapters/shared/db2.rb, line 267 def type_literal_generic_trueclass(column) :smallint end
DB2 uses clob for text types.
# File lib/sequel/adapters/shared/db2.rb, line 273 def uses_clob_for_text? true end
DB2 supports views with check option.
# File lib/sequel/adapters/shared/db2.rb, line 278 def view_with_check_option_support :local end