module Sequel::DB2::DatabaseMethods
Constants
- DATABASE_ERROR_REGEXPS
- DISCONNECT_SQL_STATES
Attributes
Whether to use clob as the generic File type, false by default.
Public Instance Methods
# File lib/sequel/adapters/shared/db2.rb 14 def database_type 15 :db2 16 end
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
# File lib/sequel/adapters/shared/db2.rb 26 def freeze 27 db2_version 28 offset_strategy 29 super 30 end
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
# 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
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
DB2
supports transaction isolation levels.
# File lib/sequel/adapters/shared/db2.rb 97 def supports_transaction_isolation_levels? 98 true 99 end
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
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
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
# 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
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
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
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
Supply columns with NOT NULL if they are part of a composite primary key or unique constraint
# 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
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.
# 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
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
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/
# 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
# File lib/sequel/adapters/shared/db2.rb 214 def database_error_regexps 215 DATABASE_ERROR_REGEXPS 216 end
# 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
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
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
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
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
Treat clob as blob if use_clob_as_blob
is true
# 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
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
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
DB2
uses smallint to store booleans.
# File lib/sequel/adapters/shared/db2.rb 271 def type_literal_generic_trueclass(column) 272 :smallint 273 end
DB2
uses clob for text types.
# File lib/sequel/adapters/shared/db2.rb 277 def uses_clob_for_text? 278 true 279 end
DB2
supports views with check option.
# File lib/sequel/adapters/shared/db2.rb 282 def view_with_check_option_support 283 :local 284 end