Module: Sequel::DB2::DatabaseMethods

Extended by:
Sequel::Database::ResetIdentifierMangling
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

Methods included from Sequel::Database::ResetIdentifierMangling

extended

Instance Method Details

#database_type ⇒ Object

DB2 always uses :db2 as it’s database type



22
23
24
 
# File 'lib/sequel/adapters/shared/db2.rb', line 22

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.



28
29
30
31
 
# File 'lib/sequel/adapters/shared/db2.rb', line 28

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

#indexes(table, opts = OPTS) ⇒ Object

Use SYSCAT.INDEXES to get the indexes for the table



73
74
75
76
77
78
79
80
81
82
83
84
 
# File 'lib/sequel/adapters/shared/db2.rb', line 73

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

#schema_parse_table(table, opts = OPTS) ⇒ Object

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



35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
# File 'lib/sequel/adapters/shared/db2.rb', line 35

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) 


87
88
89
 
# File 'lib/sequel/adapters/shared/db2.rb', line 87

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) 


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

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



59
60
61
62
63
 
# File 'lib/sequel/adapters/shared/db2.rb', line 59

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



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

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