module Sequel::DB2::DatabaseMethods

Constants

DATABASE_ERROR_REGEXPS
DISCONNECT_SQL_STATES

Attributes

use_clob_as_blob[RW]

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

Public Instance Methods

database_type() click to toggle source
   # File lib/sequel/adapters/shared/db2.rb
14 def database_type
15   :db2
16 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
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
Also aliased as: server_version
freeze() click to toggle source
Calls superclass method
   # File lib/sequel/adapters/shared/db2.rb
26 def freeze
27   db2_version
28   offset_strategy
29   super
30 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
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() click to toggle 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) click to toggle source

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

   # 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
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
97 def supports_transaction_isolation_levels?
98   true
99 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
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() click to toggle source

Use SYSCAT.TABLES to get the tables for the database

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

Use SYSCAT.TABLES to get the views for the database

   # 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

Private Instance Methods

alter_table_sql(table, op) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/db2.rb
122 def alter_table_sql(table, op)
123   case op[:op]
124   when :add_column
125     if op[:primary_key] && op[:auto_increment] && op[:type] == Integer
126       [
127       "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op.merge(:auto_increment=>false, :primary_key=>false, :default=>0, :null=>false))}",
128       "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{literal(op[:name])} DROP DEFAULT",
129       "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{literal(op[:name])} SET #{auto_increment_sql}"
130       ]
131     else
132       "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}"
133     end
134   when :drop_column
135     "ALTER TABLE #{quote_schema_table(table)} DROP #{column_definition_sql(op)}"
136   when :rename_column       # renaming is only possible after db2 v9.7
137     "ALTER TABLE #{quote_schema_table(table)} RENAME COLUMN #{quote_identifier(op[:name])} TO #{quote_identifier(op[:new_name])}"
138   when :set_column_type
139     "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} SET DATA TYPE #{type_literal(op)}"
140   when :set_column_default
141     "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} SET DEFAULT #{literal(op[:default])}"
142   when :add_constraint
143     if op[:type] == :unique
144       sqls = op[:columns].map{|c| ["ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(c)} SET NOT NULL", reorg_sql(table)]}
145       sqls << super
146       sqls.flatten
147     else
148       super
149     end
150   else
151     super
152   end
153 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
158 def apply_alter_table(name, ops)
159   alter_table_sql_list(name, ops).each do |sql|
160     execute_ddl(sql)
161     reorg(name)
162   end
163 end
auto_increment_sql() click to toggle source

DB2 uses an identity column for autoincrement.

    # File lib/sequel/adapters/shared/db2.rb
166 def auto_increment_sql
167   'GENERATED ALWAYS AS IDENTITY'
168 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
171 def can_add_primary_key_constraint_on_nullable_columns?
172   false
173 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
177 def column_list_sql(g)
178   ks = []
179   g.constraints.each{|c| ks = c[:columns] if [:primary_key, :unique].include?(c[:type])} 
180   g.columns.each{|c| c[:null] = false if ks.include?(c[:name]) }
181   super
182 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
186 def create_table_as(name, sql, options)
187   super
188   from(name).insert(sql.is_a?(Dataset) ? sql : dataset.with_sql(sql))
189 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
192 def create_table_as_sql(name, sql, options)
193   "#{create_table_prefix_sql(name, options)} AS (#{sql}) DEFINITION ONLY"
194 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
199 def create_table_prefix_sql(name, options)
200   if options[:temp]
201     "DECLARE GLOBAL TEMPORARY TABLE #{quote_identifier(name)}"
202   else
203     super
204   end
205 end
database_error_regexps() click to toggle source
    # File lib/sequel/adapters/shared/db2.rb
214 def database_error_regexps
215   DATABASE_ERROR_REGEXPS
216 end
disconnect_error?(exception, opts) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/db2.rb
219 def disconnect_error?(exception, opts)
220   sqlstate = database_exception_sqlstate(exception, opts)
221 
222   case sqlstate
223   when *DISCONNECT_SQL_STATES
224     true
225   else
226     super
227   end
228 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
232 def quote_identifiers_default
233   false
234 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
237 def rename_table_sql(name, new_name)
238   "RENAME TABLE #{quote_schema_table(name)} TO #{quote_schema_table(new_name)}"
239 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
243 def reorg(table)
244   execute_ddl(reorg_sql(table))
245 end
reorg_sql(table) click to toggle source

The SQL to use for REORGing a table.

    # File lib/sequel/adapters/shared/db2.rb
248 def reorg_sql(table)
249   "CALL SYSPROC.ADMIN_CMD(#{literal("REORG TABLE #{quote_schema_table(table)}")})"
250 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
253 def schema_column_type(db_type)
254   (use_clob_as_blob && db_type.downcase == 'clob') ? :blob : super
255 end
set_transaction_isolation_sql(level) click to toggle source

SQL to set the transaction isolation level

    # File lib/sequel/adapters/shared/db2.rb
258 def set_transaction_isolation_sql(level)
259   "SET CURRENT ISOLATION #{Database::TRANSACTION_ISOLATION_LEVELS[level]}"
260 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
266 def type_literal_generic_file(column)
267   use_clob_as_blob ? :clob : :blob
268 end
type_literal_generic_trueclass(column) click to toggle source

DB2 uses smallint to store booleans.

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

DB2 uses clob for text types.

    # File lib/sequel/adapters/shared/db2.rb
277 def uses_clob_for_text?
278   true
279 end
view_with_check_option_support() click to toggle source

DB2 supports views with check option.

    # File lib/sequel/adapters/shared/db2.rb
282 def view_with_check_option_support
283   :local
284 end