Module: ArJdbc::DB2

Defined in:
lib/arjdbc/db2/adapter.rb

Defined Under Namespace

Modules: Column

Constant Summary collapse

ADD_LOB_CALLBACK =

TODO kind of standard AR configuration option for this would be nice :

true
NATIVE_DATABASE_TYPES =
{
  :double     => { :name => "double" },
  :bigint     => { :name => "bigint" },
  :string     => { :name => "varchar", :limit => 255 },
  :text       => { :name => "clob" },
  :date       => { :name => "date" },
  :binary     => { :name => "blob" },
  :boolean    => { :name => "smallint" }, # no native boolean type
  :xml        => { :name => "xml" },
  :decimal    => { :name => "decimal" },
  :char       => { :name => "char" },
  :decfloat   => { :name => "decfloat" },
  #:rowid      => { :name => "rowid" }, # supported datatype on z/OS and i/5
  #:graphic    => { :name => "graphic", :limit => 1 },
  #:vargraphic => { :name => "vargraphic", :limit => 1 },
  # TODO datetime / timestamp / time mapping
}
HAVE_LIMIT =

only record precision and scale for types that can set them via CREATE TABLE: 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
@@_lob_callback_added =
nil

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.arel2_visitors(config) ⇒ Object



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

def self.arel2_visitors(config)
  require 'arel/visitors/db2'
  { 'db2'   => ::Arel::Visitors::DB2, 'as400' => ::Arel::Visitors::DB2 }
end

.column_selectorObject



43
44
45
# File 'lib/arjdbc/db2/adapter.rb', line 43

def self.column_selector
  [ /(db2|as400|zos)/i, lambda { |cfg, column| column.extend(::ArJdbc::DB2::Column) } ]
end

.extended(base) ⇒ Object



18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
# File 'lib/arjdbc/db2/adapter.rb', line 18

def self.extended(base)
  if ADD_LOB_CALLBACK && ! lob_callback_added?
    ActiveRecord::Base.class_eval do
      def after_save_with_db2_lob
        lob_columns = self.class.columns.select { |c| c.sql_type =~ /blob|clob/i }
        lob_columns.each do |column|
          value = ::ArJdbc::SerializedAttributesHelper.dump_column_value(self, column)
          next if value.nil? # already set NULL

          connection.write_large_object(
            column.type == :binary, column.name, 
            self.class.table_name, 
            self.class.primary_key, 
            quote_value(id), value
          )
        end
      end
      alias after_save_with_db2zos_blob after_save_with_db2_lob # <-compat
    end
    # TODO this should be changed to :after_save_with_db2_lob :
    ActiveRecord::Base.after_save :after_save_with_db2zos_blob # <-compat
    lob_callback_added!
  end
end

.jdbc_connection_classObject



47
48
49
# File 'lib/arjdbc/db2/adapter.rb', line 47

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

.lob_callback_added!Object

:nodoc



14
15
16
# File 'lib/arjdbc/db2/adapter.rb', line 14

def self.lob_callback_added! # :nodoc
  @@_lob_callback_added = true
end

.lob_callback_added?Boolean

:nodoc

Returns:

  • (Boolean)


10
11
12
# File 'lib/arjdbc/db2/adapter.rb', line 10

def self.lob_callback_added? # :nodoc
  @@_lob_callback_added
end

Instance Method Details

#_execute(sql, name = nil) ⇒ Object



208
209
210
211
212
213
214
215
216
# File 'lib/arjdbc/db2/adapter.rb', line 208

def _execute(sql, name = nil)
  if self.class.select?(sql)
    @connection.execute_query(sql)
  elsif self.class.insert?(sql)
    (@connection.execute_insert(sql) or last_insert_id(sql)).to_i
  else
    @connection.execute_update(sql)
  end
end

#adapter_nameObject



56
57
58
# File 'lib/arjdbc/db2/adapter.rb', line 56

def adapter_name
  'DB2'
end

#add_column_options!(sql, options) ⇒ Object

:nodoc:



361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
# File 'lib/arjdbc/db2/adapter.rb', line 361

def add_column_options!(sql, options) # :nodoc:
  # 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



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

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



357
358
359
# File 'lib/arjdbc/db2/adapter.rb', line 357

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

#add_quotes(name) ⇒ Object



541
542
543
544
# File 'lib/arjdbc/db2/adapter.rb', line 541

def add_quotes(name)
  return name unless name
  %Q{"#{name}"}
end

#as400?Boolean

Returns:

  • (Boolean)


636
637
638
639
640
641
642
643
644
# File 'lib/arjdbc/db2/adapter.rb', line 636

def as400?
  return @as400 unless @as400.nil?
  @as400 = 
    if url = @config[:url]
      !!( url =~ /^jdbc:as400:/ )
    else
      nil
    end
end

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



467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
# File 'lib/arjdbc/db2/adapter.rb', line 467

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}"
  as400? ? execute_and_auto_confirm(sql) : execute(sql)
  reorg_table(table_name)

  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



457
458
459
460
461
462
463
464
465
# File 'lib/arjdbc/db2/adapter.rb', line 457

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
  as400? ? execute_and_auto_confirm(sql) : execute(sql)
  reorg_table(table_name)
end

#change_column_null(table_name, column_name, null) ⇒ Object



447
448
449
450
451
452
453
454
455
# File 'lib/arjdbc/db2/adapter.rb', line 447

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
  as400? ? execute_and_auto_confirm(sql) : execute(sql)
  reorg_table(table_name)
end

#columns(table_name, name = nil) ⇒ Object



514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
# File 'lib/arjdbc/db2/adapter.rb', line 514

def columns(table_name, name = nil)
  columns = @connection.columns(table_name.to_s, name, db2_schema)

  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

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

:nodoc:



224
225
226
227
228
229
230
# File 'lib/arjdbc/db2/adapter.rb', line 224

def create_table(name, options = {}) #:nodoc:
  if zos?
    zos_create_table(name, options)
  else
    super(name, options)
  end
end

#execute_and_auto_confirm(sql) ⇒ Object

holy moly batman! all this to tell AS400 “yes i am sure”



187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
# File 'lib/arjdbc/db2/adapter.rb', line 187

def execute_and_auto_confirm(sql)
  begin
    @connection.execute_update "call qsys.qcmdexc('QSYS/CHGJOB INQMSGRPY(*SYSRPYL)',0000000031.00000)"
    @connection.execute_update "call qsys.qcmdexc('ADDRPYLE SEQNBR(9876) MSGID(CPA32B2) RPY(''I'')',0000000045.00000)"
  rescue Exception => e
    raise "Could not call CHGJOB INQMSGRPY(*SYSRPYL) and ADDRPYLE SEQNBR(9876) MSGID(CPA32B2) RPY('I').\n" +
          "Do you have authority to do this?\n\n" + e.to_s
  end

  result = execute sql

  begin
    @connection.execute_update "call qsys.qcmdexc('QSYS/CHGJOB INQMSGRPY(*DFT)',0000000027.00000)"
    @connection.execute_update "call qsys.qcmdexc('RMVRPYLE SEQNBR(9876)',0000000021.00000)"
  rescue Exception => e
    raise "Could not call CHGJOB INQMSGRPY(*DFT) and RMVRPYLE SEQNBR(9876).\n" +
          "Do you have authority to do this?\n\n" + e.to_s
  end
  result
end

#expand_double_quotes(name) ⇒ Object



552
553
554
555
# File 'lib/arjdbc/db2/adapter.rb', line 552

def expand_double_quotes(name)
  return name unless name && name['"']
  name.gsub(/"/,'""')
end

#indexes(table_name, name = nil) ⇒ Object



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

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

#jdbc_columns(table_name, name = nil) ⇒ Object



533
534
535
# File 'lib/arjdbc/db2/adapter.rb', line 533

def jdbc_columns(table_name, name = nil)
  columns(table_name, name)
end

#last_insert_id(sql) ⇒ Object



218
219
220
221
222
# File 'lib/arjdbc/db2/adapter.rb', line 218

def last_insert_id(sql)
  table_name = sql.split(/\s/)[2]
  result = select(ActiveRecord::Base.send(:sanitize_sql, %[SELECT IDENTITY_VAL_LOCAL() AS last_insert_id FROM #{table_name}], nil))
  result.last['last_insert_id']
end

#modify_types(types) ⇒ Object



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

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_typesObject



78
79
80
# File 'lib/arjdbc/db2/adapter.rb', line 78

def native_database_types
  super.merge(NATIVE_DATABASE_TYPES)
end

#next_sequence_value(sequence_name) ⇒ Object



182
183
184
# File 'lib/arjdbc/db2/adapter.rb', line 182

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

#pk_and_sequence_for(table) ⇒ Object



281
282
283
284
285
286
287
288
289
# File 'lib/arjdbc/db2/adapter.rb', line 281

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)


173
174
175
176
177
178
179
180
# File 'lib/arjdbc/db2/adapter.rb', line 173

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. value contains the data, column is optional and contains info on the field



293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
# File 'lib/arjdbc/db2/adapter.rb', line 293

def quote(value, column = nil) # :nodoc:
  return value.quoted_id if value.respond_to?(:quoted_id)
  
  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 ArJdbc::DB2.lob_callback_added?
        "NULL" # '@@@IBMBINARY@@@'"
      else
        "BLOB('#{quote_string(value)}')"
      end
    elsif column && column.sql_type =~ /clob/ # :text
      if ArJdbc::DB2.lob_callback_added?
        "NULL" # "'@@@IBMTEXT@@@'"
      else
        "'#{quote_string(value)}'"
      end
    elsif column_type == :xml
      "#{value}" # "'<ibm>@@@IBMXML@@@</ibm>'"
    else
      "'#{quote_string(value)}'"
    end
  when Symbol then "'#{quote_string(value.to_s)}'"
  else super
  end
end

#quote_column_name(column_name) ⇒ Object



339
340
341
# File 'lib/arjdbc/db2/adapter.rb', line 339

def quote_column_name(column_name)
  column_name.to_s
end

#recreate_database(name, options = {}) ⇒ Object



414
415
416
# File 'lib/arjdbc/db2/adapter.rb', line 414

def recreate_database(name, options = {})
  tables.each { |table| drop_table("#{db2_schema}.#{table}") }
end

#remove_column(table_name, *column_names) ⇒ Object



490
491
492
493
494
495
496
# File 'lib/arjdbc/db2/adapter.rb', line 490

def remove_column(table_name, *column_names) #:nodoc:
  for column_name in column_names.flatten
    sql = "ALTER TABLE #{table_name} DROP COLUMN #{column_name}"
    as400? ? execute_and_auto_confirm(sql) : execute(sql)
  end
  reorg_table(table_name)
end

#remove_index(table_name, options = { }) ⇒ Object



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

def remove_index(table_name, options = { })
  execute "DROP INDEX #{quote_column_name(index_name(table_name, options))}"
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object



438
439
440
441
442
443
444
445
# File 'lib/arjdbc/db2/adapter.rb', line 438

def rename_column(table_name, column_name, new_column_name) #:nodoc:
  if as400?
    raise NotImplementedError, "rename_column is not supported on IBM i"
  else
    execute "ALTER TABLE #{table_name} RENAME COLUMN #{column_name} TO #{new_column_name}"
    reorg_table(table_name)
  end
end

#rename_table(name, new_name) ⇒ Object



499
500
501
502
# File 'lib/arjdbc/db2/adapter.rb', line 499

def rename_table(name, new_name) #:nodoc:
  execute "RENAME TABLE #{name} TO #{new_name}"
  reorg_table(new_name)
end

#replace_limit_offset!(sql, limit, offset) ⇒ Object



385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
# File 'lib/arjdbc/db2/adapter.rb', line 385

def replace_limit_offset!(sql, limit, offset)
  if limit
    limit = limit.to_i
    if !offset
      if limit == 1
        sql << " FETCH FIRST ROW ONLY"
      else
        sql << " FETCH FIRST #{limit} ROWS ONLY"
      end
    else
      offset = offset.to_i
      sql.sub!(/SELECT/i, 'SELECT B.* FROM (SELECT A.*, row_number() over () AS internal$rownum FROM (SELECT')
      sql << ") A ) B WHERE B.internal$rownum > #{offset} AND B.internal$rownum <= #{limit + offset}"
    end
  end
  sql
end

#runstats_for_table(tablename, priority = 10) ⇒ Object



410
411
412
# File 'lib/arjdbc/db2/adapter.rb', line 410

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

#strip_quotes(str) ⇒ Object



546
547
548
549
550
# File 'lib/arjdbc/db2/adapter.rb', line 546

def strip_quotes(str)
  return str unless str
  return str unless /^(["']).*\1$/ =~ str
  str[1..-2]
end

#structure_dumpObject

:nodoc:



557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
# File 'lib/arjdbc/db2/adapter.rb', line 557

def structure_dump #:nodoc:
  schema_name = db2_schema.upcase if db2_schema.present?
  rs = @connection.connection..getTables(nil, schema_name, nil, ["TABLE"].to_java(:string))
  definition = ''
  while rs.next
    tname = rs.getString(3)
    definition << "CREATE TABLE #{tname} (\n"
    rs2 = @connection.connection..getColumns(nil,schema_name,tname,nil)
    first_col = true
    while rs2.next
      col_name = add_quotes(rs2.getString(4));
      default = ""
      d1 = rs2.getString(13)
      # IBM i (as400 toolbox driver) will return an empty string if there is no default
      if @config[:url] =~ /^jdbc:as400:/
        default = !d1.blank? ? " DEFAULT #{d1}" : ""
      else
        default = d1 ? " DEFAULT #{d1}" : ""
      end

      type = rs2.getString(6)
      col_precision = rs2.getString(7)
      col_scale = rs2.getString(9)
      col_size = ""
      if HAVE_SCALE.include?(type) and col_scale
        col_size = "(#{col_precision},#{col_scale})"
      elsif (HAVE_LIMIT + HAVE_PRECISION).include?(type) and col_precision
        col_size = "(#{col_precision})"
      end
      nulling = (rs2.getString(18) == 'NO' ? " NOT NULL" : "")
      autoincrement = (rs2.getString(23) == 'YES' ? " GENERATED ALWAYS AS IDENTITY" : "")
      create_col_string = add_quotes(expand_double_quotes(strip_quotes(col_name))) +
        " " +
        type +
        col_size +
        "" +
        nulling +
        default +
        autoincrement
      if !first_col
        create_col_string = ",\n #{create_col_string}"
      else
        create_col_string = " #{create_col_string}"
      end

      definition << create_col_string

      first_col = false
    end
    definition << ");\n\n"

    pkrs = @connection.connection..getPrimaryKeys(nil,schema_name,tname)
    primary_key = {}
    while pkrs.next
      name = pkrs.getString(6)
      primary_key[name] = [] unless primary_key[name]
      primary_key[name] << pkrs.getString(4)
    end
    primary_key.each do |name, cols|
      definition << "ALTER TABLE #{tname}\n"
      definition << "  ADD CONSTRAINT #{name}\n"
      definition << "      PRIMARY KEY (#{cols.join(', ')});\n\n"
    end
  end
  definition
end

#tablesObject



504
505
506
# File 'lib/arjdbc/db2/adapter.rb', line 504

def tables
  @connection.tables(nil, db2_schema, nil, ["TABLE"])
end

#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object



352
353
354
355
# File 'lib/arjdbc/db2/adapter.rb', line 352

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

#zos?Boolean

Returns:

  • (Boolean)


626
627
628
629
630
631
632
633
634
# File 'lib/arjdbc/db2/adapter.rb', line 626

def zos?
  return @zos unless @zos.nil?
  @zos = 
    if url = @config[:url]
      !!( url =~ /^jdbc:db2j:net:/ && @config[:driver] == DRIVER_NAME )
    else
      nil
    end
end