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 20 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 26 def db2_version return @db2_version if @db2_version @db2_version = metadata_dataset.with_sql("select service_level from sysibmadm.env_inst_info").first[:service_level] end
Use SYSCAT.INDEXES to get the indexes for the table
# File lib/sequel/adapters/shared/db2.rb, line 68 def indexes(table, opts = OPTS) m = output_identifier_meth indexes = {} metadata_dataset. from(: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
Use SYSIBM.SYSCOLUMNS to get the information on the tables.
# File lib/sequel/adapters/shared/db2.rb, line 33 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] == "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 82 def supports_transaction_isolation_levels? true end
Use SYSCAT.TABLES to get the tables for the database
# File lib/sequel/adapters/shared/db2.rb, line 54 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 61 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 89 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
DB2 uses an identity column for autoincrement.
# File lib/sequel/adapters/shared/db2.rb, line 123 def auto_increment_sql AUTOINCREMENT 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 138 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 147 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 153 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 160 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 175 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 181 def quote_identifiers_default false end
DB2 uses RENAME TABLE to rename tables.
# File lib/sequel/adapters/shared/db2.rb, line 186 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 192 def reorg(table) synchronize(opts[:server]){|c| c.execute(reorg_sql(table))} end
The SQL to use for REORGing a table.
# File lib/sequel/adapters/shared/db2.rb, line 197 def reorg_sql(table) "CALL ADMIN_CMD(#{literal("REORG TABLE #{table}")})" end
Treat clob as blob if use_clob_as_blob is true
# File lib/sequel/adapters/shared/db2.rb, line 202 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 207 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 215 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 220 def type_literal_generic_trueclass(column) :smallint end
DB2 uses clob for text types.
# File lib/sequel/adapters/shared/db2.rb, line 226 def uses_clob_for_text? true end
DB2 supports views with check option.
# File lib/sequel/adapters/shared/db2.rb, line 231 def view_with_check_option_support :local end