module Sequel::DB2::DatabaseMethods

Constants

AUTOINCREMENT
DATABASE_ERROR_REGEXPS
NOT_NULL
NULL

Public Instance Methods

database_type() click to toggle source

DB2 always uses :db2 as it's database type

# File lib/sequel/adapters/shared/db2.rb, line 22
def database_type
  :db2
end
db2_version() click to toggle source

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
Also aliased as: server_version
freeze() click to toggle source
Calls superclass method
# File lib/sequel/adapters/shared/db2.rb, line 34
def freeze
  db2_version
  offset_strategy
  super
end
indexes(table, opts = OPTS) click to toggle source

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
offset_strategy() click to toggle source
# 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
schema_parse_table(table, opts = OPTS) click to toggle source

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
server_version()
Alias for: db2_version
supports_transaction_isolation_levels?() click to toggle source

DB2 supports transaction isolation levels.

# File lib/sequel/adapters/shared/db2.rb, line 104
def supports_transaction_isolation_levels?
  true
end
table_exists?(name) click to toggle source

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
tables() click to toggle source

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
views() click to toggle source

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

alter_table_sql(table, op) click to toggle source

Handle DB2 specific alter table operations.

Calls superclass method
# 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
apply_alter_table(name, ops) click to toggle source

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
auto_increment_sql() click to toggle source

DB2 uses an identity column for autoincrement.

# File lib/sequel/adapters/shared/db2.rb, line 174
def auto_increment_sql
  AUTOINCREMENT
end
can_add_primary_key_constraint_on_nullable_columns?() click to toggle source

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
column_list_sql(g) click to toggle source

Supply columns with NOT NULL if they are part of a composite primary key or unique constraint

Calls superclass method
# 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
create_table_as(name, sql, options) click to toggle source

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.

Calls superclass method
# 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
create_table_as_sql(name, sql, options) click to toggle source

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
create_table_prefix_sql(name, options) click to toggle source

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/

Calls superclass method
# 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
database_error_regexps() click to toggle source
# File lib/sequel/adapters/shared/db2.rb, line 222
def database_error_regexps
  DATABASE_ERROR_REGEXPS
end
quote_identifiers_default() click to toggle source

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
rename_table_sql(name, new_name) click to toggle source

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
reorg(table) click to toggle source

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
reorg_sql(table) click to toggle source

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
schema_column_type(db_type) click to toggle source

Treat clob as blob if use_clob_as_blob is true

Calls superclass method
# 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
set_transaction_isolation_sql(level) click to toggle source

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
type_literal_generic_falseclass(column)
type_literal_generic_file(column) click to toggle source

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
type_literal_generic_trueclass(column) click to toggle source

DB2 uses smallint to store booleans.

# File lib/sequel/adapters/shared/db2.rb, line 267
def type_literal_generic_trueclass(column)
  :smallint
end
uses_clob_for_text?() click to toggle source

DB2 uses clob for text types.

# File lib/sequel/adapters/shared/db2.rb, line 273
def uses_clob_for_text?
  true
end
view_with_check_option_support() click to toggle source

DB2 supports views with check option.

# File lib/sequel/adapters/shared/db2.rb, line 278
def view_with_check_option_support
  :local
end