Module: ArJdbc::DB2

Included in:
ActiveRecord::ConnectionAdapters::DB2Adapter, AS400
Defined in:
lib/arjdbc/db2/adapter.rb,
lib/arjdbc/db2/column.rb

Overview

Note:

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

Instance Method Summary collapse

Class Method Details

.column_selector ⇒ Object

See Also:

  • ActiveRecord::ConnectionAdapters::JdbcColumn#column_types


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

Deprecated.

Use #emulate_booleans? instead.



125
 
# File 'lib/arjdbc/db2/adapter.rb', line 125

def self.emulate_booleans; @@emulate_booleans; end

.emulate_booleans=(emulate) ⇒ Object

See Also:

  • #emulate_booleans?


127
 
# File 'lib/arjdbc/db2/adapter.rb', line 127

def self.emulate_booleans=(emulate); @@emulate_booleans = emulate; end

.emulate_booleans? ⇒ Boolean

Boolean emulation can be disabled using :

ArJdbc::DB2.emulate_booleans = false

Returns:

  • (Boolean) 


123
 
# File 'lib/arjdbc/db2/adapter.rb', line 123

def self.emulate_booleans?; @@emulate_booleans; end

.jdbc_connection_class ⇒ Object

See Also:



107
108
109
 
# File 'lib/arjdbc/db2/adapter.rb', line 107

def self.jdbc_connection_class
  ::ActiveRecord::ConnectionAdapters::DB2JdbcConnection
end

.update_lob_values=(update) ⇒ Object

See Also:

  • #update_lob_values?


140
 
# File 'lib/arjdbc/db2/adapter.rb', line 140

def self.update_lob_values=(update); @@update_lob_values = update; end

.update_lob_values? ⇒ Boolean

Note:

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

Returns:

  • (Boolean) 


138
 
# File 'lib/arjdbc/db2/adapter.rb', line 138

def self.update_lob_values?; @@update_lob_values; end

Instance Method Details

#adapter_name ⇒ Object 



162
163
164
 
# File 'lib/arjdbc/db2/adapter.rb', line 162

def adapter_name
  ADAPTER_NAME
end

#add_column(table_name, column_name, type, options = {}) ⇒ Object 



464
465
466
467
468
469
 
# File 'lib/arjdbc/db2/adapter.rb', line 464

def add_column(table_name, column_name, type, options = {})
  # 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, options)}"
  add_column_options!(add_column_sql, options)
  execute(add_column_sql)
end

#add_column_options!(sql, options) ⇒ Object 



471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
 
# File 'lib/arjdbc/db2/adapter.rb', line 471

def add_column_options!(sql, options)
  # handle case of defaults for CLOB columns,
  # which might get incorrect if we write LOBs in the after_save callback
  if options_include_default?(options)
    column = options[:column]
    if column && column.type == :text
      sql << " DEFAULT #{quote(options.delete(:default))}"
    end
    if column && column.type == :binary
      # quoting required for the default value of a column :
      value = options.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 



564
565
566
567
568
569
570
571
572
573
574
575
576
 
# File 'lib/arjdbc/db2/adapter.rb', line 564

def add_index(table_name, column_name, options = {})
  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 options[:unique]
    statement << " INDEX #{ActiveRecord::Base.table_name_prefix}#{options[:name]} "
    statement << " ON #{table_name}(#{column_name})"

    execute statement
  end
end

#add_limit_offset!(sql, options) ⇒ Object

Note:

Only used with (non-AREL) ActiveRecord 2.3.

See Also:



497
498
499
500
 
# File 'lib/arjdbc/db2/adapter.rb', line 497

def add_limit_offset!(sql, options)
  limit = options[:limit]
  replace_limit_offset!(sql, limit, options[:offset]) if limit
end

#change_column(table_name, column_name, type, options = {}) ⇒ Object 



608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
 
# File 'lib/arjdbc/db2/adapter.rb', line 608

def change_column(table_name, column_name, type, options = {})
  data_type = type_to_sql(type, options)
  sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET DATA TYPE #{data_type}"
  execute_table_change(sql, table_name, 'Change Column')

  if options.include?(:default) and options.include?(:null)
    # which to run first?
    if options[:null] or options[:default].nil?
      change_column_null(table_name, column_name, options[:null])
      change_column_default(table_name, column_name, options[:default])
    else
      change_column_default(table_name, column_name, options[:default])
      change_column_null(table_name, column_name, options[:null])
    end
  elsif options.include?(:default)
    change_column_default(table_name, column_name, options[:default])
  elsif options.include?(:null)
    change_column_null(table_name, column_name, options[:null])
  end
end

#change_column_default(table_name, column_name, default) ⇒ Object 



599
600
601
602
603
604
605
606
 
# File 'lib/arjdbc/db2/adapter.rb', line 599

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 



590
591
592
593
594
595
596
597
 
# File 'lib/arjdbc/db2/adapter.rb', line 590

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 



665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
 
# File 'lib/arjdbc/db2/adapter.rb', line 665

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 



155
156
157
158
 
# File 'lib/arjdbc/db2/adapter.rb', line 155

def configure_connection
  schema = self.schema
  set_schema(schema) if schema && schema != config[:username]
end

#create_table(name, options = {}, &block) ⇒ Object 



346
347
348
349
350
351
352
 
# File 'lib/arjdbc/db2/adapter.rb', line 346

def create_table(name, options = {}, &block)
  if zos?
    zos_create_table(name, options, &block)
  else
    super
  end
end

#drop_database(name = nil) ⇒ Object 



692
693
694
 
# File 'lib/arjdbc/db2/adapter.rb', line 692

def drop_database(name = nil)
  tables.each { |table| drop_table("#{table}") }
end

#empty_insert_statement_value ⇒ Object 



460
461
462
 
# File 'lib/arjdbc/db2/adapter.rb', line 460

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) 



703
704
705
706
707
 
# File 'lib/arjdbc/db2/adapter.rb', line 703

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 



684
685
686
 
# File 'lib/arjdbc/db2/adapter.rb', line 684

def indexes(table_name, name = nil)
  @connection.indexes(table_name, name, schema)
end

#jdbc_column_class ⇒ Object

See Also:



112
113
114
 
# File 'lib/arjdbc/db2/adapter.rb', line 112

def jdbc_column_class
  ::ActiveRecord::ConnectionAdapters::DB2Column
end

#last_inserted_id(result) ⇒ Integer, NilClass

Note:

Check the IDENTITY_VAL_LOCAL function for documentation.

Returns the value of an identity column of the last INSERT statement made over this connection.

Returns:

  • (Integer, NilClass) 


720
721
722
 
# File 'lib/arjdbc/db2/adapter.rb', line 720

def last_inserted_id(result)
  @connection.identity_val_local
end

#modify_types(types) ⇒ Object 



442
443
444
445
446
447
448
449
 
# File 'lib/arjdbc/db2/adapter.rb', line 442

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 



260
261
262
263
264
265
266
 
# File 'lib/arjdbc/db2/adapter.rb', line 260

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 



342
343
344
 
# File 'lib/arjdbc/db2/adapter.rb', line 342

def next_sequence_value(sequence_name)
  select_value("SELECT NEXT VALUE FOR #{sequence_name} FROM sysibm.sysdummy1")
end

#pk_and_sequence_for(table) ⇒ Object 



403
404
405
406
407
408
409
410
411
 
# File 'lib/arjdbc/db2/adapter.rb', line 403

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

Returns:

  • (Boolean) 


322
323
324
325
326
327
328
329
 
# File 'lib/arjdbc/db2/adapter.rb', line 322

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

#primary_keys(table) ⇒ Object 



332
333
334
335
336
337
338
339
340
 
# File 'lib/arjdbc/db2/adapter.rb', line 332

def primary_keys(table)
  # If no schema in table name is given but present in URL parameter. Use the URL parameter one
  # This avoids issues if the table is present in multiple schemas
  if table.split(".").size == 1 && schema
    table = "#{schema}.#{table}"
  end

  super
end

#quote(value) ⇒ Object

Properly quotes the various data types.

Parameters:

  • value —

    contains the data



416
417
418
419
 
# File 'lib/arjdbc/db2/adapter.rb', line 416

def quote(value)
  return value if sql_literal?(value)
  super
end

#quote_column_name(column_name) ⇒ Object 



438
439
440
 
# File 'lib/arjdbc/db2/adapter.rb', line 438

def quote_column_name(column_name)
  column_name.to_s
end

#quote_time(value) ⇒ Object 



432
433
434
435
436
 
# File 'lib/arjdbc/db2/adapter.rb', line 432

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 



422
423
424
425
426
427
428
429
430
 
# File 'lib/arjdbc/db2/adapter.rb', line 422

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 



688
689
690
 
# File 'lib/arjdbc/db2/adapter.rb', line 688

def recreate_database(name = nil, options = {})
  drop_database(name)
end

#remove_column(table_name, *column_names) ⇒ Object 



631
632
633
 
# File 'lib/arjdbc/db2/adapter.rb', line 631

def remove_column(table_name, column_name, type = nil, options = {})
  db2_remove_column(table_name, column_name)
end

#remove_index!(table_name, index_name) ⇒ Object 



579
580
581
 
# File 'lib/arjdbc/db2/adapter.rb', line 579

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



585
586
587
588
 
# File 'lib/arjdbc/db2/adapter.rb', line 585

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 



649
650
651
652
 
# File 'lib/arjdbc/db2/adapter.rb', line 649

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 



255
256
257
 
# File 'lib/arjdbc/db2/adapter.rb', line 255

def reset_column_information
  initialize_type_map(type_map)
end

#runstats_for_table(tablename, priority = 10) ⇒ Object

Deprecated.

seems not sued nor tested ?!



531
532
533
 
# File 'lib/arjdbc/db2/adapter.rb', line 531

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 



764
765
766
 
# File 'lib/arjdbc/db2/adapter.rb', line 764

def schema
  db2_schema
end

#schema=(schema) ⇒ Object 



768
769
770
 
# File 'lib/arjdbc/db2/adapter.rb', line 768

def schema=(schema)
  set_schema(@db2_schema = schema) if db2_schema != schema
end

#select(sql, name = nil, binds = []) ⇒ Object 



537
538
539
 
# File 'lib/arjdbc/db2/adapter.rb', line 537

def select(sql, name = nil, binds = [])
  exec_query(to_sql(suble_null_test(sql), binds), name, binds)
end

#supports_views? ⇒ Boolean

Returns:

  • (Boolean) 


701
 
# File 'lib/arjdbc/db2/adapter.rb', line 701

def supports_views?; true end

#table_definition(*args) ⇒ Object 



318
319
320
 
# File 'lib/arjdbc/db2/adapter.rb', line 318

def table_definition(*args)
  new_table_definition(TableDefinition, *args)
end

#tables ⇒ Object 



654
655
656
 
# File 'lib/arjdbc/db2/adapter.rb', line 654

def tables
  @connection.tables(nil, schema)
end

#truncate(table_name, name = nil) ⇒ Object 



696
697
698
 
# File 'lib/arjdbc/db2/adapter.rb', line 696

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 



451
452
453
454
 
# File 'lib/arjdbc/db2/adapter.rb', line 451

def type_to_sql(type, limit: nil, precision: nil, scale: nil, **)
  limit = nil if type.to_sym == :integer
  super
end

#update_lob_value?(value, column = nil) ⇒ Boolean

Returns:

  • (Boolean)

See Also:



144
145
146
 
# File 'lib/arjdbc/db2/adapter.rb', line 144

def update_lob_value?(value, column = nil)
  DB2.update_lob_values? && ! prepared_statements? # && value
end