Module: ArJdbc::DB2

Included in:
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.



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

def self.emulate_booleans; @@emulate_booleans; end

.emulate_booleans=(emulate) ⇒ Object

See Also:

  • #emulate_booleans?


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

Returns:

  • (Boolean) 


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

def self.emulate_booleans?; @@emulate_booleans; end

.jdbc_connection_class ⇒ Object

See Also:



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

See Also:

  • #update_lob_values?


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

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) 


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 



394
395
396
397
398
399
 
# File 'lib/arjdbc/db2/adapter.rb', line 394

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[:limit], options[:precision], options[:scale])}"
  add_column_options!(add_column_sql, options)
  execute(add_column_sql)
end

#add_column_options!(sql, options) ⇒ Object 



401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
 
# File 'lib/arjdbc/db2/adapter.rb', line 401

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 



494
495
496
497
498
499
500
501
502
503
504
505
506
 
# File 'lib/arjdbc/db2/adapter.rb', line 494

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:



427
428
429
430
 
# File 'lib/arjdbc/db2/adapter.rb', line 427

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 



538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
 
# File 'lib/arjdbc/db2/adapter.rb', line 538

def change_column(table_name, column_name, type, options = {})
  data_type = type_to_sql(type, options[:limit], options[:precision], options[:scale])
  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 



529
530
531
532
533
534
535
536
 
# File 'lib/arjdbc/db2/adapter.rb', line 529

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 



520
521
522
523
524
525
526
527
 
# File 'lib/arjdbc/db2/adapter.rb', line 520

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 



595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
 
# File 'lib/arjdbc/db2/adapter.rb', line 595

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, options = {}, &block)
  if zos?
    zos_create_table(name, options, &block)
  else
    super
  end
end

#drop_database(name = nil) ⇒ Object 



622
623
624
 
# File 'lib/arjdbc/db2/adapter.rb', line 622

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

#empty_insert_statement_value ⇒ Object 



390
391
392
 
# File 'lib/arjdbc/db2/adapter.rb', line 390

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) 



633
634
635
636
637
 
# File 'lib/arjdbc/db2/adapter.rb', line 633

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 



614
615
616
 
# File 'lib/arjdbc/db2/adapter.rb', line 614

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

#jdbc_column_class ⇒ Object

See Also:



53
54
55
 
# File 'lib/arjdbc/db2/adapter.rb', line 53

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

#last_insert_id ⇒ Fixnum

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:

  • (Fixnum) 


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

def last_insert_id
  @connection.identity_val_local
end

#modify_types(types) ⇒ Object 



372
373
374
375
376
377
378
379
 
# File 'lib/arjdbc/db2/adapter.rb', line 372

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

Returns:

  • (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) ⇒ Object

Properly quotes the various data types.

Parameters:

  • value —

    contains the data



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

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

#quote_column_name(column_name) ⇒ Object 



368
369
370
 
# File 'lib/arjdbc/db2/adapter.rb', line 368

def quote_column_name(column_name)
  column_name.to_s
end

#quote_time(value) ⇒ Object 



362
363
364
365
366
 
# File 'lib/arjdbc/db2/adapter.rb', line 362

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 



352
353
354
355
356
357
358
359
360
 
# File 'lib/arjdbc/db2/adapter.rb', line 352

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 



618
619
620
 
# File 'lib/arjdbc/db2/adapter.rb', line 618

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

#remove_column(table_name, *column_names) ⇒ Object 



561
562
563
 
# File 'lib/arjdbc/db2/adapter.rb', line 561

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 



509
510
511
 
# File 'lib/arjdbc/db2/adapter.rb', line 509

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



515
516
517
518
 
# File 'lib/arjdbc/db2/adapter.rb', line 515

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 



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

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

Deprecated.

seems not sued nor tested ?!



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

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 



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

def schema
  db2_schema
end

#schema=(schema) ⇒ Object 



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

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

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



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

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

#supports_views? ⇒ Boolean

Returns:

  • (Boolean) 


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

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 



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

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

#truncate(table_name, name = nil) ⇒ Object 



626
627
628
 
# File 'lib/arjdbc/db2/adapter.rb', line 626

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 



381
382
383
384
 
# File 'lib/arjdbc/db2/adapter.rb', line 381

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

Returns:

  • (Boolean)

See Also:



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