module Sequel::DB2::DatabaseMethods
Constants
- DATABASE_ERROR_REGEXPS
Attributes
Whether to use clob as the generic File type, false by default.
Public Instance Methods
# File lib/sequel/adapters/shared/db2.rb 13 def database_type 14 :db2 15 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 19 def db2_version 20 return @db2_version if defined?(@db2_version) 21 @db2_version = metadata_dataset.with_sql("select service_level from sysibmadm.env_inst_info").first[:service_level] 22 end
# File lib/sequel/adapters/shared/db2.rb 25 def freeze 26 db2_version 27 offset_strategy 28 super 29 end
Use SYSCAT.INDEXES to get the indexes for the table
# File lib/sequel/adapters/shared/db2.rb 70 def indexes(table, opts = OPTS) 71 m = output_identifier_meth 72 table = table.value if table.is_a?(Sequel::SQL::Identifier) 73 indexes = {} 74 metadata_dataset. 75 from(Sequel[:syscat][:indexes]). 76 select(:indname, :uniquerule, :colnames). 77 where(:tabname=>input_identifier_meth.call(table), :system_required=>0). 78 each do |r| 79 indexes[m.call(r[:indname])] = {:unique=>(r[:uniquerule]=='U'), :columns=>r[:colnames][1..-1].split('+').map{|v| m.call(v)}} 80 end 81 indexes 82 end
# File lib/sequel/adapters/shared/db2.rb 84 def offset_strategy 85 return @offset_strategy if defined?(@offset_strategy) 86 87 @offset_strategy = case strategy = opts[:offset_strategy].to_s 88 when "limit_offset", "offset_fetch" 89 opts[:offset_strategy] = strategy.to_sym 90 else 91 opts[:offset_strategy] = :emulate 92 end 93 end
Use SYSIBM.SYSCOLUMNS to get the information on the tables.
# File lib/sequel/adapters/shared/db2.rb 32 def schema_parse_table(table, opts = OPTS) 33 m = output_identifier_meth(opts[:dataset]) 34 im = input_identifier_meth(opts[:dataset]) 35 metadata_dataset.with_sql("SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = #{literal(im.call(table))} ORDER BY COLNO"). 36 collect do |column| 37 column[:db_type] = column.delete(:typename) 38 if column[:db_type] =~ /\A(VAR)?CHAR\z/ 39 column[:db_type] << "(#{column[:length]})" 40 end 41 if column[:db_type] == "DECIMAL" 42 column[:db_type] << "(#{column[:longlength]},#{column[:scale]})" 43 end 44 column[:allow_null] = column.delete(:nulls) == 'Y' 45 identity = column.delete(:identity) == 'Y' 46 if column[:primary_key] = identity || !column[:keyseq].nil? 47 column[:auto_increment] = identity 48 end 49 column[:type] = schema_column_type(column[:db_type]) 50 column[:max_length] = column[:longlength] if column[:type] == :string 51 [ m.call(column.delete(:name)), column] 52 end 53 end
DB2
supports transaction isolation levels.
# File lib/sequel/adapters/shared/db2.rb 96 def supports_transaction_isolation_levels? 97 true 98 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 103 def table_exists?(name) 104 v ||= false # only retry once 105 sch, table_name = schema_and_table(name) 106 name = SQL::QualifiedIdentifier.new(sch, table_name) if sch 107 from(name).first 108 true 109 rescue DatabaseError => e 110 if e.to_s =~ /Operation not allowed for reason code "7" on table/ && v == false 111 # table probably needs reorg 112 reorg(name) 113 v = true 114 retry 115 end 116 false 117 end
Use SYSCAT.TABLES to get the tables for the database
# File lib/sequel/adapters/shared/db2.rb 56 def tables 57 metadata_dataset. 58 with_sql("SELECT TABNAME FROM SYSCAT.TABLES WHERE TYPE='T' AND OWNER = #{literal(input_identifier_meth.call(opts[:user]))}"). 59 all.map{|h| output_identifier_meth.call(h[:tabname]) } 60 end
Use SYSCAT.TABLES to get the views for the database
# File lib/sequel/adapters/shared/db2.rb 63 def views 64 metadata_dataset. 65 with_sql("SELECT TABNAME FROM SYSCAT.TABLES WHERE TYPE='V' AND OWNER = #{literal(input_identifier_meth.call(opts[:user]))}"). 66 all.map{|h| output_identifier_meth.call(h[:tabname]) } 67 end
Private Instance Methods
# File lib/sequel/adapters/shared/db2.rb 121 def alter_table_sql(table, op) 122 case op[:op] 123 when :add_column 124 if op[:primary_key] && op[:auto_increment] && op[:type] == Integer 125 [ 126 "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op.merge(:auto_increment=>false, :primary_key=>false, :default=>0, :null=>false))}", 127 "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{literal(op[:name])} DROP DEFAULT", 128 "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{literal(op[:name])} SET #{auto_increment_sql}" 129 ] 130 else 131 "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}" 132 end 133 when :drop_column 134 "ALTER TABLE #{quote_schema_table(table)} DROP #{column_definition_sql(op)}" 135 when :rename_column # renaming is only possible after db2 v9.7 136 "ALTER TABLE #{quote_schema_table(table)} RENAME COLUMN #{quote_identifier(op[:name])} TO #{quote_identifier(op[:new_name])}" 137 when :set_column_type 138 "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} SET DATA TYPE #{type_literal(op)}" 139 when :set_column_default 140 "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} SET DEFAULT #{literal(op[:default])}" 141 when :add_constraint 142 if op[:type] == :unique 143 sqls = op[:columns].map{|c| ["ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(c)} SET NOT NULL", reorg_sql(table)]} 144 sqls << super 145 sqls.flatten 146 else 147 super 148 end 149 else 150 super 151 end 152 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 157 def apply_alter_table(name, ops) 158 alter_table_sql_list(name, ops).each do |sql| 159 execute_ddl(sql) 160 reorg(name) 161 end 162 end
DB2
uses an identity column for autoincrement.
# File lib/sequel/adapters/shared/db2.rb 165 def auto_increment_sql 166 'GENERATED ALWAYS AS IDENTITY' 167 end
DB2
does not allow adding primary key constraints to NULLable columns.
# File lib/sequel/adapters/shared/db2.rb 170 def can_add_primary_key_constraint_on_nullable_columns? 171 false 172 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 176 def column_list_sql(g) 177 ks = [] 178 g.constraints.each{|c| ks = c[:columns] if [:primary_key, :unique].include?(c[:type])} 179 g.columns.each{|c| c[:null] = false if ks.include?(c[:name]) } 180 super 181 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 185 def create_table_as(name, sql, options) 186 super 187 from(name).insert(sql.is_a?(Dataset) ? sql : dataset.with_sql(sql)) 188 end
DB2
requires parens around the SELECT, and DEFINITION ONLY at the end.
# File lib/sequel/adapters/shared/db2.rb 191 def create_table_as_sql(name, sql, options) 192 "#{create_table_prefix_sql(name, options)} AS (#{sql}) DEFINITION ONLY" 193 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 198 def create_table_prefix_sql(name, options) 199 if options[:temp] 200 "DECLARE GLOBAL TEMPORARY TABLE #{quote_identifier(name)}" 201 else 202 super 203 end 204 end
# File lib/sequel/adapters/shared/db2.rb 213 def database_error_regexps 214 DATABASE_ERROR_REGEXPS 215 end
DB2
has issues with quoted identifiers, so turn off database quoting by default.
# File lib/sequel/adapters/shared/db2.rb 219 def quote_identifiers_default 220 false 221 end
DB2
uses RENAME TABLE to rename tables.
# File lib/sequel/adapters/shared/db2.rb 224 def rename_table_sql(name, new_name) 225 "RENAME TABLE #{quote_schema_table(name)} TO #{quote_schema_table(new_name)}" 226 end
Run the REORG TABLE command for the table, necessary when the table has been altered.
# File lib/sequel/adapters/shared/db2.rb 230 def reorg(table) 231 execute_ddl(reorg_sql(table)) 232 end
The SQL
to use for REORGing a table.
# File lib/sequel/adapters/shared/db2.rb 235 def reorg_sql(table) 236 "CALL SYSPROC.ADMIN_CMD(#{literal("REORG TABLE #{quote_schema_table(table)}")})" 237 end
Treat clob as blob if use_clob_as_blob
is true
# File lib/sequel/adapters/shared/db2.rb 240 def schema_column_type(db_type) 241 (use_clob_as_blob && db_type.downcase == 'clob') ? :blob : super 242 end
SQL
to set the transaction isolation level
# File lib/sequel/adapters/shared/db2.rb 245 def set_transaction_isolation_sql(level) 246 "SET CURRENT ISOLATION #{Database::TRANSACTION_ISOLATION_LEVELS[level]}" 247 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 253 def type_literal_generic_file(column) 254 use_clob_as_blob ? :clob : :blob 255 end
DB2
uses smallint to store booleans.
# File lib/sequel/adapters/shared/db2.rb 258 def type_literal_generic_trueclass(column) 259 :smallint 260 end
DB2
uses clob for text types.
# File lib/sequel/adapters/shared/db2.rb 264 def uses_clob_for_text? 265 true 266 end
DB2
supports views with check option.
# File lib/sequel/adapters/shared/db2.rb 269 def view_with_check_option_support 270 :local 271 end