Module: Sequel::DB2::DatabaseMethods

Included in:
IBMDB::Database, JDBC::DB2::DatabaseMethods
Defined in:
lib/sequel/adapters/shared/db2.rb

Constant Summary collapse

AUTOINCREMENT = 
'GENERATED ALWAYS AS IDENTITY'.freeze
NOT_NULL = 
' NOT NULL'.freeze
NULL = 
''.freeze

Instance Method Summary collapse

Instance Method Details

#database_type ⇒ Object

DB2 always uses :db2 as it’s database type



25
26
27
 
# File 'lib/sequel/adapters/shared/db2.rb', line 25

def database_type
  :db2
end

#db2_version ⇒ Object Also known as: server_version

Return the database version as a string. Don’t rely on this, it may return an integer in the future.



31
32
33
34
 
# File 'lib/sequel/adapters/shared/db2.rb', line 31

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

#freeze ⇒ Object 



37
38
39
40
41
 
# File 'lib/sequel/adapters/shared/db2.rb', line 37

def freeze
  db2_version
  offset_strategy
  super
end

#indexes(table, opts = OPTS) ⇒ Object

Use SYSCAT.INDEXES to get the indexes for the table



82
83
84
85
86
87
88
89
90
91
92
93
 
# File 'lib/sequel/adapters/shared/db2.rb', line 82

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 ⇒ Object 



95
96
97
98
99
100
101
102
103
104
 
# File 'lib/sequel/adapters/shared/db2.rb', line 95

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) ⇒ Object

Use SYSIBM.SYSCOLUMNS to get the information on the tables.



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
# File 'lib/sequel/adapters/shared/db2.rb', line 44

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

#supports_transaction_isolation_levels? ⇒ Boolean

DB2 supports transaction isolation levels.

Returns:

  • (Boolean) 


107
108
109
 
# File 'lib/sequel/adapters/shared/db2.rb', line 107

def supports_transaction_isolation_levels?
  true
end

#table_exists?(name) ⇒ Boolean

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.

Returns:

  • (Boolean) 


114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
 
# File 'lib/sequel/adapters/shared/db2.rb', line 114

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 ⇒ Object

Use SYSCAT.TABLES to get the tables for the database



68
69
70
71
72
 
# File 'lib/sequel/adapters/shared/db2.rb', line 68

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 ⇒ Object

Use SYSCAT.TABLES to get the views for the database



75
76
77
78
79
 
# File 'lib/sequel/adapters/shared/db2.rb', line 75

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