module Sequel::DB2::DatabaseMethods

  1. lib/sequel/adapters/shared/db2.rb

Constants

DATABASE_ERROR_REGEXPS = { /DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505|One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index/ => UniqueConstraintViolation, /DB2 SQL Error: (SQLCODE=-530, SQLSTATE=23503|SQLCODE=-532, SQLSTATE=23504)|The insert or update value of the FOREIGN KEY .+ is not equal to any value of the parent key of the parent table|A parent row cannot be deleted because the relationship .+ restricts the deletion/ => ForeignKeyConstraintViolation, /DB2 SQL Error: SQLCODE=-545, SQLSTATE=23513|The requested operation is not allowed because a row does not satisfy the check constraint/ => CheckConstraintViolation, /DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502|Assignment of a NULL value to a NOT NULL column/ => NotNullConstraintViolation, /DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001|The current transaction has been rolled back because of a deadlock or timeout/ => SerializationFailure, }.freeze  
DISCONNECT_SQL_STATES = %w'40003 08001 08003'.freeze  

Public Instance Aliases

server_version -> db2_version

Attributes

use_clob_as_blob [RW]

Whether to use clob as the generic File type, false by default.

Public Instance methods

database_type()
[show source] 
   # File lib/sequel/adapters/shared/db2.rb
14 def database_type
15   :db2
16 end
db2_version()

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

[show source] 
   # File lib/sequel/adapters/shared/db2.rb
20 def db2_version
21   return @db2_version if defined?(@db2_version)
22   @db2_version = metadata_dataset.with_sql("select service_level from sysibmadm.env_inst_info").first[:service_level]
23 end
freeze()
[show source] 
   # File lib/sequel/adapters/shared/db2.rb
26 def freeze
27   db2_version
28   offset_strategy
29   super
30 end
indexes(table, opts = OPTS)

Use SYSCAT.INDEXES to get the indexes for the table

[show source] 
   # File lib/sequel/adapters/shared/db2.rb
71 def indexes(table, opts = OPTS)
72   m = output_identifier_meth
73   table = table.value if table.is_a?(Sequel::SQL::Identifier)
74   indexes = {}
75   metadata_dataset.
76    from(Sequel[:syscat][:indexes]).
77    select(:indname, :uniquerule, :colnames).
78    where(:tabname=>input_identifier_meth.call(table), :system_required=>0).
79    each do |r|
80     indexes[m.call(r[:indname])] = {:unique=>(r[:uniquerule]=='U'), :columns=>r[:colnames][1..-1].split('+').map{|v| m.call(v)}}
81   end
82   indexes
83 end
offset_strategy()
[show source] 
   # File lib/sequel/adapters/shared/db2.rb
85 def offset_strategy
86   return @offset_strategy if defined?(@offset_strategy)
87 
88   @offset_strategy = case strategy = opts[:offset_strategy].to_s
89   when "limit_offset", "offset_fetch"
90     opts[:offset_strategy] = strategy.to_sym
91   else
92     opts[:offset_strategy] = :emulate
93   end
94 end
schema_parse_table(table, opts = OPTS)

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

[show source] 
   # File lib/sequel/adapters/shared/db2.rb
33 def schema_parse_table(table, opts = OPTS)
34   m = output_identifier_meth(opts[:dataset])
35   im = input_identifier_meth(opts[:dataset])
36   metadata_dataset.with_sql("SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = #{literal(im.call(table))} ORDER BY COLNO").
37     collect do |column| 
38       column[:db_type] = column.delete(:typename)
39       if column[:db_type] =~ /\A(VAR)?CHAR\z/
40         column[:db_type] << "(#{column[:length]})"
41       end
42       if column[:db_type] == "DECIMAL"
43         column[:db_type] << "(#{column[:longlength]},#{column[:scale]})"
44       end
45       column[:allow_null] = column.delete(:nulls) == 'Y'
46       identity = column.delete(:identity) == 'Y'
47       if column[:primary_key] = identity || !column[:keyseq].nil?
48         column[:auto_increment] = identity
49       end
50       column[:type]        = schema_column_type(column[:db_type])
51       column[:max_length]  = column[:longlength] if column[:type] == :string
52       [ m.call(column.delete(:name)), column]
53     end
54 end
supports_transaction_isolation_levels?()

DB2 supports transaction isolation levels.

[show source] 
   # File lib/sequel/adapters/shared/db2.rb
97 def supports_transaction_isolation_levels?
98   true
99 end
table_exists?(name)

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.

[show source] 
    # File lib/sequel/adapters/shared/db2.rb
104 def table_exists?(name)
105   v ||= false # only retry once
106   sch, table_name = schema_and_table(name)
107   name = SQL::QualifiedIdentifier.new(sch, table_name) if sch
108   from(name).first
109   true
110 rescue DatabaseError => e
111   if e.to_s =~ /Operation not allowed for reason code "7" on table/ && v == false
112     # table probably needs reorg
113     reorg(name)
114     v = true
115     retry 
116   end
117   false
118 end
tables()

Use SYSCAT.TABLES to get the tables for the database

[show source] 
   # File lib/sequel/adapters/shared/db2.rb
57 def tables
58   metadata_dataset.
59     with_sql("SELECT TABNAME FROM SYSCAT.TABLES WHERE TYPE='T' AND OWNER = #{literal(input_identifier_meth.call(opts[:user]))}").
60     all.map{|h| output_identifier_meth.call(h[:tabname]) }
61 end
views()

Use SYSCAT.TABLES to get the views for the database

[show source] 
   # File lib/sequel/adapters/shared/db2.rb
64 def views
65   metadata_dataset.
66     with_sql("SELECT TABNAME FROM SYSCAT.TABLES WHERE TYPE='V' AND OWNER = #{literal(input_identifier_meth.call(opts[:user]))}").
67     all.map{|h| output_identifier_meth.call(h[:tabname]) }
68 end