Module: ArJdbc::DB2
- Included in:
- AS400
- Defined in:
- lib/arjdbc/db2/adapter.rb,
lib/arjdbc/db2/column.rb
Overview
This adapter doesn't support explain config.active_record.auto_explain_threshold_in_seconds
should be commented (Rails < 4.0)
Defined Under Namespace
Modules: Column
Constant Summary collapse
- ADAPTER_NAME =
'DB2'.freeze
- NATIVE_DATABASE_TYPES =
{ :string => { :name => "varchar", :limit => 255 }, :integer => { :name => "integer" }, :bigint => { :name => 'bigint' }, :float => { :name => "real" }, # :limit => 24 :double => { :name => "double" }, # :limit => 53 :text => { :name => "clob" }, :binary => { :name => "blob" }, :xml => { :name => "xml" }, :decimal => { :name => "decimal" }, # :limit => 31 :char => { :name => "char" }, # :limit => 254 :date => { :name => "date" }, :datetime => { :name => "timestamp" }, :timestamp => { :name => "timestamp" }, :time => { :name => "time" }, :boolean => { :name => "smallint" }, # no native boolean type #:rowid => { :name => "rowid" }, # rowid is a supported datatype on z/OS and i/5 #:serial => { :name => "serial" }, # supported datatype on Informix Dynamic Server #:graphic => { :name => "graphic", :limit => 1 }, # :limit => 127 }
- HAVE_LIMIT =
only record precision and scale for types that can set them via CREATE TABLE: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html
%w(FLOAT DECFLOAT CHAR VARCHAR CLOB BLOB NCHAR NCLOB DBCLOB GRAPHIC VARGRAPHIC)
- HAVE_PRECISION =
TIMESTAMP
%w(DECIMAL NUMERIC)
- HAVE_SCALE =
%w(DECIMAL NUMERIC)
- DRIVER_NAME =
'com.ibm.db2.jcc.DB2Driver'.freeze
Class Method Summary collapse
- .column_selector ⇒ Object
-
.emulate_booleans ⇒ Object
deprecated
Deprecated.
Use #emulate_booleans? instead.
- .emulate_booleans=(emulate) ⇒ Object
-
.emulate_booleans? ⇒ Boolean
Boolean emulation can be disabled using :.
- .jdbc_connection_class ⇒ Object
- .update_lob_values=(update) ⇒ Object
-
.update_lob_values? ⇒ Boolean
Updating records with LOB values (binary/text columns) in a separate statement can be disabled using :.
Instance Method Summary collapse
- #adapter_name ⇒ Object
- #add_column(table_name, column_name, type, options = {}) ⇒ Object
- #add_column_options!(sql, options) ⇒ Object
- #add_index(table_name, column_name, options = {}) ⇒ Object
- #add_limit_offset!(sql, options) ⇒ Object
- #change_column(table_name, column_name, type, options = {}) ⇒ Object
- #change_column_default(table_name, column_name, default) ⇒ Object
- #change_column_null(table_name, column_name, null) ⇒ Object
- #columns(table_name, name = nil) ⇒ Object
- #configure_connection ⇒ Object
- #create_table(name, options = {}, &block) ⇒ Object
- #drop_database(name = nil) ⇒ Object
- #empty_insert_statement_value ⇒ Object
- #execute_table_change(sql, table_name, name = nil) ⇒ Object protected
- #indexes(table_name, name = nil) ⇒ Object
- #jdbc_column_class ⇒ Object
-
#last_insert_id ⇒ Fixnum
Returns the value of an identity column of the last INSERT statement made over this connection.
- #modify_types(types) ⇒ Object
- #native_database_types ⇒ Object
- #next_sequence_value(sequence_name) ⇒ Object
- #pk_and_sequence_for(table) ⇒ Object
- #prefetch_primary_key?(table_name = nil) ⇒ Boolean
-
#quote(value, column = nil) ⇒ Object
Properly quotes the various data types.
- #quote_column_name(column_name) ⇒ Object
- #quote_time(value) ⇒ Object
- #quoted_date(value) ⇒ Object
- #recreate_database(name = nil, options = {}) ⇒ Object
- #remove_column(table_name, *column_names) ⇒ Object
- #remove_index!(table_name, index_name) ⇒ Object
-
#rename_column(table_name, column_name, new_column_name) ⇒ Object
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/t0020130.html ...not supported on IBM i, so we raise in this case.
- #rename_table(name, new_name) ⇒ Object
- #reset_column_information ⇒ Object
-
#runstats_for_table(tablename, priority = 10) ⇒ Object
deprecated
Deprecated.
seems not sued nor tested ?!
- #schema ⇒ Object
- #schema=(schema) ⇒ Object
- #select(sql, name = nil, binds = []) ⇒ Object
- #supports_views? ⇒ Boolean
- #table_definition(*args) ⇒ Object
- #tables ⇒ Object
- #truncate(table_name, name = nil) ⇒ Object
- #type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object
- #update_lob_value?(value, column = nil) ⇒ Boolean
Class Method Details
.column_selector ⇒ Object
5 6 7 |
# File 'lib/arjdbc/db2/column.rb', line 5 def self.column_selector [ /(db2|zos)/i, lambda { |config, column| column.extend(Column) } ] end |
.emulate_booleans ⇒ Object
Use #emulate_booleans? instead.
66 |
# File 'lib/arjdbc/db2/adapter.rb', line 66 def self.emulate_booleans; @@emulate_booleans; end |
.emulate_booleans=(emulate) ⇒ Object
68 |
# File 'lib/arjdbc/db2/adapter.rb', line 68 def self.emulate_booleans=(emulate); @@emulate_booleans = emulate; end |
.emulate_booleans? ⇒ Boolean
Boolean emulation can be disabled using :
ArJdbc::DB2.emulate_booleans = false
64 |
# File 'lib/arjdbc/db2/adapter.rb', line 64 def self.emulate_booleans?; @@emulate_booleans; end |
.jdbc_connection_class ⇒ Object
48 49 50 |
# File 'lib/arjdbc/db2/adapter.rb', line 48 def self.jdbc_connection_class ::ActiveRecord::ConnectionAdapters::DB2JdbcConnection end |
.update_lob_values=(update) ⇒ Object
81 |
# File 'lib/arjdbc/db2/adapter.rb', line 81 def self.update_lob_values=(update); @@update_lob_values = update; end |
.update_lob_values? ⇒ Boolean
This only applies when prepared statements are not used.
Updating records with LOB values (binary/text columns) in a separate statement can be disabled using :
ArJdbc::DB2.update_lob_values = false
79 |
# File 'lib/arjdbc/db2/adapter.rb', line 79 def self.update_lob_values?; @@update_lob_values; end |
Instance Method Details
#adapter_name ⇒ Object
103 104 105 |
# File 'lib/arjdbc/db2/adapter.rb', line 103 def adapter_name ADAPTER_NAME end |
#add_column(table_name, column_name, type, options = {}) ⇒ Object
444 445 446 447 448 449 |
# File 'lib/arjdbc/db2/adapter.rb', line 444 def add_column(table_name, column_name, type, = {}) # The keyword COLUMN allows to use reserved names for columns (ex: date) add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, [:limit], [:precision], [:scale])}" (add_column_sql, ) execute(add_column_sql) end |
#add_column_options!(sql, options) ⇒ Object
451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 |
# File 'lib/arjdbc/db2/adapter.rb', line 451 def (sql, ) # handle case of defaults for CLOB columns, # which might get incorrect if we write LOBs in the after_save callback if () column = [:column] if column && column.type == :text sql << " DEFAULT #{quote(.delete(:default))}" end if column && column.type == :binary # quoting required for the default value of a column : value = .delete(:default) # DB2 z/OS only allows NULL or "" (empty) string as DEFAULT value # for a BLOB column. non-empty string and non-NULL, return error! if value.nil? sql_value = "NULL" else sql_value = zos? ? "#{value}" : "BLOB('#{quote_string(value)}'" end sql << " DEFAULT #{sql_value}" end end super end |
#add_index(table_name, column_name, options = {}) ⇒ Object
544 545 546 547 548 549 550 551 552 553 554 555 556 |
# File 'lib/arjdbc/db2/adapter.rb', line 544 def add_index(table_name, column_name, = {}) if ! zos? || ( table_name.to_s == ActiveRecord::Migrator.schema_migrations_table_name.to_s ) column_name = column_name.to_s if column_name.is_a?(Symbol) super else statement = 'CREATE' statement << ' UNIQUE ' if [:unique] statement << " INDEX #{ActiveRecord::Base.table_name_prefix}#{[:name]} " statement << " ON #{table_name}(#{column_name})" execute statement end end |
#add_limit_offset!(sql, options) ⇒ Object
Only used with (non-AREL) ActiveRecord 2.3.
477 478 479 480 |
# File 'lib/arjdbc/db2/adapter.rb', line 477 def add_limit_offset!(sql, ) limit = [:limit] replace_limit_offset!(sql, limit, [:offset]) if limit end |
#change_column(table_name, column_name, type, options = {}) ⇒ Object
588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 |
# File 'lib/arjdbc/db2/adapter.rb', line 588 def change_column(table_name, column_name, type, = {}) data_type = type_to_sql(type, [:limit], [:precision], [:scale]) sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET DATA TYPE #{data_type}" execute_table_change(sql, table_name, 'Change Column') if .include?(:default) and .include?(:null) # which to run first? if [:null] or [:default].nil? change_column_null(table_name, column_name, [:null]) change_column_default(table_name, column_name, [:default]) else change_column_default(table_name, column_name, [:default]) change_column_null(table_name, column_name, [:null]) end elsif .include?(:default) change_column_default(table_name, column_name, [:default]) elsif .include?(:null) change_column_null(table_name, column_name, [:null]) end end |
#change_column_default(table_name, column_name, default) ⇒ Object
579 580 581 582 583 584 585 586 |
# File 'lib/arjdbc/db2/adapter.rb', line 579 def change_column_default(table_name, column_name, default) if default.nil? sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} DROP DEFAULT" else sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET WITH DEFAULT #{quote(default)}" end execute_table_change(sql, table_name, 'Change Column') end |
#change_column_null(table_name, column_name, null) ⇒ Object
570 571 572 573 574 575 576 577 |
# File 'lib/arjdbc/db2/adapter.rb', line 570 def change_column_null(table_name, column_name, null) if null sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} DROP NOT NULL" else sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET NOT NULL" end execute_table_change(sql, table_name, 'Change Column') end |
#columns(table_name, name = nil) ⇒ Object
645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 |
# File 'lib/arjdbc/db2/adapter.rb', line 645 def columns(table_name, name = nil) columns = @connection.columns_internal(table_name.to_s, nil, schema) # catalog == nil if zos? # Remove the mighty db2_generated_rowid_for_lobs from the list of columns columns = columns.reject { |col| "db2_generated_rowid_for_lobs" == col.name } end # scrub out sizing info when CREATE TABLE doesn't support it # but JDBC reports it (doh!) for column in columns base_sql_type = column.sql_type.sub(/\(.*/, "").upcase column.limit = nil unless HAVE_LIMIT.include?(base_sql_type) column.precision = nil unless HAVE_PRECISION.include?(base_sql_type) #column.scale = nil unless HAVE_SCALE.include?(base_sql_type) end columns end |
#configure_connection ⇒ Object
96 97 98 99 |
# File 'lib/arjdbc/db2/adapter.rb', line 96 def configure_connection schema = self.schema set_schema(schema) if schema && schema != config[:username] end |
#create_table(name, options = {}, &block) ⇒ Object
276 277 278 279 280 281 282 |
# File 'lib/arjdbc/db2/adapter.rb', line 276 def create_table(name, = {}, &block) if zos? zos_create_table(name, , &block) else super end end |
#drop_database(name = nil) ⇒ Object
672 673 674 |
# File 'lib/arjdbc/db2/adapter.rb', line 672 def drop_database(name = nil) tables.each { |table| drop_table("#{table}") } end |
#empty_insert_statement_value ⇒ Object
440 441 442 |
# File 'lib/arjdbc/db2/adapter.rb', line 440 def empty_insert_statement_value VALUES_DEFAULT # won't work as DB2 needs to know the column count end |
#execute_table_change(sql, table_name, name = nil) ⇒ Object (protected)
683 684 685 686 687 |
# File 'lib/arjdbc/db2/adapter.rb', line 683 def execute_table_change(sql, table_name, name = nil) outcome = execute(sql, name) reorg_table(table_name, name) outcome end |
#indexes(table_name, name = nil) ⇒ Object
664 665 666 |
# File 'lib/arjdbc/db2/adapter.rb', line 664 def indexes(table_name, name = nil) @connection.indexes(table_name, name, schema) end |
#jdbc_column_class ⇒ Object
53 54 55 |
# File 'lib/arjdbc/db2/adapter.rb', line 53 def jdbc_column_class ::ActiveRecord::ConnectionAdapters::DB2Column end |
#last_insert_id ⇒ Fixnum
Check the IDENTITY_VAL_LOCAL function for documentation.
Returns the value of an identity column of the last INSERT statement made over this connection.
701 702 703 |
# File 'lib/arjdbc/db2/adapter.rb', line 701 def last_insert_id @connection.identity_val_local end |
#modify_types(types) ⇒ Object
422 423 424 425 426 427 428 429 |
# File 'lib/arjdbc/db2/adapter.rb', line 422 def modify_types(types) super(types) types[:primary_key] = 'int not null generated by default as identity (start with 1) primary key' types[:string][:limit] = 255 types[:integer][:limit] = nil types[:boolean] = {:name => "decimal(1)"} types end |
#native_database_types ⇒ Object
201 202 203 204 205 206 207 |
# File 'lib/arjdbc/db2/adapter.rb', line 201 def native_database_types # NOTE: currently merging with what JDBC gives us since there's a lot # of DB2-like stuff we could be connecting e.g. "classic", Z/OS etc. # types = super types = super.merge(NATIVE_DATABASE_TYPES) types end |
#next_sequence_value(sequence_name) ⇒ Object
272 273 274 |
# File 'lib/arjdbc/db2/adapter.rb', line 272 def next_sequence_value(sequence_name) select_value("SELECT NEXT VALUE FOR #{sequence_name} FROM sysibm.sysdummy1") end |
#pk_and_sequence_for(table) ⇒ Object
333 334 335 336 337 338 339 340 341 |
# File 'lib/arjdbc/db2/adapter.rb', line 333 def pk_and_sequence_for(table) # In JDBC/DB2 side, only upcase names of table and column are handled. keys = super(table.upcase) if keys && keys[0] # In ActiveRecord side, only downcase names of table and column are handled. keys[0] = keys[0].downcase end keys end |
#prefetch_primary_key?(table_name = nil) ⇒ Boolean
263 264 265 266 267 268 269 270 |
# File 'lib/arjdbc/db2/adapter.rb', line 263 def prefetch_primary_key?(table_name = nil) # TRUE if the table has no identity column names = table_name.upcase.split(".") sql = "SELECT 1 FROM SYSCAT.COLUMNS WHERE IDENTITY = 'Y' " sql << "AND TABSCHEMA = '#{names.first}' " if names.size == 2 sql << "AND TABNAME = '#{names.last}'" select_one(sql).nil? end |
#quote(value, column = nil) ⇒ Object
Properly quotes the various data types.
347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 |
# File 'lib/arjdbc/db2/adapter.rb', line 347 def quote(value, column = nil) return value.quoted_id if value.respond_to?(:quoted_id) return value if sql_literal?(value) if column if column.respond_to?(:primary) && column.primary && column.klass != String return value.to_i.to_s end if value && (column.type.to_sym == :decimal || column.type.to_sym == :integer) return value.to_s end end column_type = column && column.type.to_sym case value when nil then 'NULL' when Numeric # IBM_DB doesn't accept quotes on numeric types # if the column type is text or string, return the quote value if column_type == :text || column_type == :string "'#{value}'" else value.to_s end when String, ActiveSupport::Multibyte::Chars if column_type == :binary && column.sql_type !~ /for bit data/i if update_lob_value?(value, column) value.nil? ? 'NULL' : BLOB_VALUE_MARKER # '@@@IBMBINARY@@@'" else "BLOB('#{quote_string(value)}')" end elsif column && column.sql_type =~ /clob/ # :text if update_lob_value?(value, column) value.nil? ? 'NULL' : CLOB_VALUE_MARKER # "'@@@IBMTEXT@@@'" else "'#{quote_string(value)}'" end elsif column_type == :xml value.nil? ? 'NULL' : "'#{quote_string(value)}'" # "'<ibm>@@@IBMXML@@@</ibm>'" else "'#{quote_string(value)}'" end when Symbol then "'#{quote_string(value.to_s)}'" when Time # AS400 doesn't support date in time column if column_type == :time quote_time(value) else super end else super end end |
#quote_column_name(column_name) ⇒ Object
418 419 420 |
# File 'lib/arjdbc/db2/adapter.rb', line 418 def quote_column_name(column_name) column_name.to_s end |
#quote_time(value) ⇒ Object
412 413 414 415 416 |
# File 'lib/arjdbc/db2/adapter.rb', line 412 def quote_time(value) value = ::ActiveRecord::Base.default_timezone == :utc ? value.getutc : value.getlocal # AS400 doesn't support date in time column "'#{value.strftime("%H:%M:%S")}'" end |
#quoted_date(value) ⇒ Object
402 403 404 405 406 407 408 409 410 |
# File 'lib/arjdbc/db2/adapter.rb', line 402 def quoted_date(value) if value.acts_like?(:time) && value.respond_to?(:usec) usec = sprintf("%06d", value.usec) value = ::ActiveRecord::Base.default_timezone == :utc ? value.getutc : value.getlocal "#{value.strftime("%Y-%m-%d %H:%M:%S")}.#{usec}" else super end end |
#recreate_database(name = nil, options = {}) ⇒ Object
668 669 670 |
# File 'lib/arjdbc/db2/adapter.rb', line 668 def recreate_database(name = nil, = {}) drop_database(name) end |
#remove_column(table_name, *column_names) ⇒ Object
611 612 613 |
# File 'lib/arjdbc/db2/adapter.rb', line 611 def remove_column(table_name, column_name, type = nil, = {}) db2_remove_column(table_name, column_name) end |
#remove_index!(table_name, index_name) ⇒ Object
559 560 561 |
# File 'lib/arjdbc/db2/adapter.rb', line 559 def remove_index!(table_name, index_name) execute "DROP INDEX #{quote_column_name(index_name)}" end |
#rename_column(table_name, column_name, new_column_name) ⇒ Object
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/t0020130.html ...not supported on IBM i, so we raise in this case
565 566 567 568 |
# File 'lib/arjdbc/db2/adapter.rb', line 565 def rename_column(table_name, column_name, new_column_name) #:nodoc: sql = "ALTER TABLE #{table_name} RENAME COLUMN #{column_name} TO #{new_column_name}" execute_table_change(sql, table_name, 'Rename Column') end |
#rename_table(name, new_name) ⇒ Object
629 630 631 632 |
# File 'lib/arjdbc/db2/adapter.rb', line 629 def rename_table(name, new_name) # http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000980.html execute_table_change("RENAME TABLE #{name} TO #{new_name}", new_name, 'Rename Table') end |
#reset_column_information ⇒ Object
196 197 198 |
# File 'lib/arjdbc/db2/adapter.rb', line 196 def reset_column_information initialize_type_map(type_map) end |
#runstats_for_table(tablename, priority = 10) ⇒ Object
seems not sued nor tested ?!
511 512 513 |
# File 'lib/arjdbc/db2/adapter.rb', line 511 def runstats_for_table(tablename, priority = 10) @connection.execute_update "call sysproc.admin_cmd('RUNSTATS ON TABLE #{tablename} WITH DISTRIBUTION AND DETAILED INDEXES ALL UTIL_IMPACT_PRIORITY #{priority}')" end |
#schema ⇒ Object
745 746 747 |
# File 'lib/arjdbc/db2/adapter.rb', line 745 def schema db2_schema end |
#schema=(schema) ⇒ Object
749 750 751 |
# File 'lib/arjdbc/db2/adapter.rb', line 749 def schema=(schema) set_schema(@db2_schema = schema) if db2_schema != schema end |
#select(sql, name = nil, binds = []) ⇒ Object
517 518 519 |
# File 'lib/arjdbc/db2/adapter.rb', line 517 def select(sql, name = nil, binds = []) exec_query(to_sql(suble_null_test(sql), binds), name, binds) end |
#supports_views? ⇒ Boolean
681 |
# File 'lib/arjdbc/db2/adapter.rb', line 681 def supports_views?; true end |
#table_definition(*args) ⇒ Object
259 260 261 |
# File 'lib/arjdbc/db2/adapter.rb', line 259 def table_definition(*args) new_table_definition(TableDefinition, *args) end |
#tables ⇒ Object
634 635 636 |
# File 'lib/arjdbc/db2/adapter.rb', line 634 def tables @connection.tables(nil, schema) end |
#truncate(table_name, name = nil) ⇒ Object
676 677 678 |
# File 'lib/arjdbc/db2/adapter.rb', line 676 def truncate(table_name, name = nil) execute "TRUNCATE TABLE #{quote_table_name(table_name)} IMMEDIATE", name end |
#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object
431 432 433 434 |
# File 'lib/arjdbc/db2/adapter.rb', line 431 def type_to_sql(type, limit = nil, precision = nil, scale = nil) limit = nil if type.to_sym == :integer super(type, limit, precision, scale) end |
#update_lob_value?(value, column = nil) ⇒ Boolean
85 86 87 |
# File 'lib/arjdbc/db2/adapter.rb', line 85 def update_lob_value?(value, column = nil) DB2.update_lob_values? && ! prepared_statements? # && value end |