module ArJdbc::DB2

@note This adapter doesn't support explain `config.active_record.auto_explain_threshold_in_seconds` should be commented (Rails < 4.0)

Constants

ADAPTER_NAME
BLOB_VALUE_MARKER

@see quote @private

CLOB_VALUE_MARKER

@see quote @private

DRIVER_NAME
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

HAVE_PRECISION
HAVE_SCALE
IS_NOT_NULL

@private

IS_NULL

@private

NATIVE_DATABASE_TYPES
VALUES_DEFAULT

@private

Public Class Methods

column_selector() click to toggle source

@see ActiveRecord::ConnectionAdapters::JdbcColumn#column_types

# File lib/arjdbc/db2/column.rb, line 5
def self.column_selector
  [ /(db2|zos)/i, lambda { |config, column| column.extend(Column) } ]
end
emulate_booleans() click to toggle source

@deprecated Use {#emulate_booleans?} instead.

# File lib/arjdbc/db2/adapter.rb, line 66
def self.emulate_booleans; @@emulate_booleans; end
emulate_booleans=(emulate) click to toggle source

@see emulate_booleans?

# File lib/arjdbc/db2/adapter.rb, line 68
def self.emulate_booleans=(emulate); @@emulate_booleans = emulate; end
emulate_booleans?() click to toggle source

Boolean emulation can be disabled using :

ArJdbc::DB2.emulate_booleans = false
# File lib/arjdbc/db2/adapter.rb, line 64
def self.emulate_booleans?; @@emulate_booleans; end
extended(adapter) click to toggle source

@private

# File lib/arjdbc/db2/adapter.rb, line 34
def self.extended(adapter); initialize!; end
initialize!() click to toggle source

@private

# File lib/arjdbc/db2/adapter.rb, line 40
def self.initialize!
  return if @@_initialized; @@_initialized = true

  require 'arjdbc/util/serialized_attributes'
  Util::SerializedAttributes.setup /blob|clob/i, 'after_save_with_db2_lob'
end
jdbc_connection_class() click to toggle source

@see ActiveRecord::ConnectionAdapters::JdbcAdapter#jdbc_connection_class

# File lib/arjdbc/db2/adapter.rb, line 48
def self.jdbc_connection_class
  ::ActiveRecord::ConnectionAdapters::DB2JdbcConnection
end
update_lob_values=(update) click to toggle source

@see update_lob_values?

# File lib/arjdbc/db2/adapter.rb, line 81
def self.update_lob_values=(update); @@update_lob_values = update; end
update_lob_values?() click to toggle source

Updating records with LOB values (binary/text columns) in a separate statement can be disabled using :

ArJdbc::DB2.update_lob_values = false

@note This only applies when prepared statements are not used.

# File lib/arjdbc/db2/adapter.rb, line 79
def self.update_lob_values?; @@update_lob_values; end

Public Instance Methods

_execute(sql, name = nil) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 712
def _execute(sql, name = nil)
  if self.class.select?(sql)
    @connection.execute_query_raw(sql)
  elsif self.class.insert?(sql)
    @connection.execute_insert(sql) || last_insert_id
  else
    @connection.execute_update(sql)
  end
end
adapter_name() click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 103
def adapter_name
  ADAPTER_NAME
end
add_column(table_name, column_name, type, options = {}) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 444
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) click to toggle source
Calls superclass method
# File lib/arjdbc/db2/adapter.rb, line 451
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 = {}) click to toggle source
Calls superclass method
# File lib/arjdbc/db2/adapter.rb, line 544
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) click to toggle source

@note Only used with (non-AREL) ActiveRecord 2.3. @see Arel::Visitors::DB2

# File lib/arjdbc/db2/adapter.rb, line 477
def add_limit_offset!(sql, options)
  limit = options[:limit]
  replace_limit_offset!(sql, limit, options[:offset]) if limit
end
as400?() click to toggle source

@private @deprecated no longer used

# File lib/arjdbc/db2/adapter.rb, line 741
def as400?
  false
end
change_column(table_name, column_name, type, options = {}) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 588
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) click to toggle source
# 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) click to toggle source
# 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) click to toggle source
# 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() click to toggle source
# 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) click to toggle source
Calls superclass method
# 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) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 672
def drop_database(name = nil)
  tables.each { |table| drop_table("#{table}") }
end
empty_insert_statement_value() click to toggle source

@override

# 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
indexes(table_name, name = nil) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 664
def indexes(table_name, name = nil)
  @connection.indexes(table_name, name, schema)
end
initialize_type_map(m) click to toggle source

@override

# File lib/arjdbc/db2/adapter.rb, line 129
def initialize_type_map(m)
  register_class_with_limit m, %r(boolean)i,   ActiveRecord::Type::Boolean
  register_class_with_limit m, %r(char)i,      ActiveRecord::Type::String
  register_class_with_limit m, %r(binary)i,    ActiveRecord::Type::Binary
  register_class_with_limit m, %r(text)i,      ActiveRecord::Type::Text
  register_class_with_limit m, %r(date)i,      ActiveRecord::Type::Date
  register_class_with_limit m, %r(time)i,      ActiveRecord::Type::Time
  register_class_with_limit m, %r(datetime)i,  ActiveRecord::Type::DateTime
  register_class_with_limit m, %r(float)i,     ActiveRecord::Type::Float
  register_class_with_limit m, %r(int)i,       ActiveRecord::Type::Integer

  m.alias_type %r(blob)i,      'binary'
  m.alias_type %r(clob)i,      'text'
  m.alias_type %r(timestamp)i, 'datetime'
  m.alias_type %r(numeric)i,   'decimal'
  m.alias_type %r(number)i,    'decimal'
  m.alias_type %r(double)i,    'float'
  m.alias_type %r(real)i,      'float'

  m.register_type(%r(decimal)i) do |sql_type|
    scale = extract_scale(sql_type)
    precision = extract_precision(sql_type)
    limit = extract_limit(sql_type)
    if scale == 0
      ActiveRecord::Type::BigInteger.new(:precision => precision, :limit => limit)
    else
      ActiveRecord::Type::Decimal.new(:precision => precision, :scale => scale)
    end
  end

  m.alias_type %r(for bit data)i,  'binary'
  m.alias_type %r(smallint)i,      'boolean'
  m.alias_type %r(serial)i,        'int'
  m.alias_type %r(decfloat)i,      'decimal'
  #m.alias_type %r(real)i,          'decimal'
  m.alias_type %r(graphic)i,       'binary'
  m.alias_type %r(rowid)i,         'int'

  m.register_type(%r(smallint)i) do
    if DB2.emulate_booleans?
      ActiveRecord::Type::Boolean.new
    else
      ActiveRecord::Type::Integer.new(:limit => 1)
    end
  end

  m.register_type %r(xml)i, XmlType.new
end
jdbc_column_class() click to toggle source

@see ActiveRecord::ConnectionAdapters::JdbcAdapter#jdbc_column_class

# File lib/arjdbc/db2/adapter.rb, line 53
def jdbc_column_class
  ::ActiveRecord::ConnectionAdapters::DB2Column
end
last_insert_id() click to toggle source

Returns the value of an identity column of the last INSERT statement made over this connection. @note Check the IDENTITY_VAL_LOCAL function for documentation. @return [Fixnum]

# File lib/arjdbc/db2/adapter.rb, line 701
def last_insert_id
  @connection.identity_val_local
end
modify_types(types) click to toggle source
Calls superclass method
# 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() click to toggle source

@override

Calls superclass method
# 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) click to toggle source
# 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) click to toggle source
Calls superclass method
# 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) click to toggle source
# 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) click to toggle source

Properly quotes the various data types. @param value contains the data @param column (optional) contains info on the field @override

Calls superclass method
# 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) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 418
def quote_column_name(column_name)
  column_name.to_s
end
quote_time(value) click to toggle source
# 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) click to toggle source

@override

Calls superclass method
# 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 = {}) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 668
def recreate_database(name = nil, options = {})
  drop_database(name)
end
remove_column(table_name, column_name, type = nil, options = {}) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 611
def remove_column(table_name, column_name, type = nil, options = {})
  db2_remove_column(table_name, column_name)
end
remove_index!(table_name, index_name) click to toggle source

@override

# 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_table(name, new_name) click to toggle source
# 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
replace_limit_offset!(sql, limit, offset, orders = nil) click to toggle source

@private shared with {Arel::Visitors::DB2}

# File lib/arjdbc/db2/adapter.rb, line 483
def replace_limit_offset!(sql, limit, offset, orders = nil)
  limit = limit.to_i

  if offset # && limit
    over_order_by = nil # NOTE: orders matching got reverted as it was not complete and there were no case covering it ...

    start_sql = "SELECT B.* FROM (SELECT A.*, row_number() OVER (#{over_order_by}) AS internal$rownum FROM (SELECT"
    end_sql = ") A ) B WHERE B.internal$rownum > #{offset} AND B.internal$rownum <= #{limit + offset.to_i}"

    if sql.is_a?(String)
      sql.sub!(/SELECT/i, start_sql)
      sql << end_sql
    else # AR 4.2 sql.class ... Arel::Collectors::Bind
      sql.parts[0] = start_sql # sql.sub! /SELECT/i
      sql.parts[ sql.parts.length ] = end_sql
    end
  else
    limit_sql = limit == 1 ? " FETCH FIRST ROW ONLY" : " FETCH FIRST #{limit} ROWS ONLY"
    if sql.is_a?(String)
      sql << limit_sql
    else # AR 4.2 sql.class ... Arel::Collectors::Bind
      sql.parts[ sql.parts.length ] = limit_sql
    end
  end
  sql
end
reset_column_information() click to toggle source

@override

# File lib/arjdbc/db2/adapter.rb, line 196
def reset_column_information
  initialize_type_map(type_map)
end
runstats_for_table(tablename, priority = 10) click to toggle source

@deprecated seems not sued nor tested ?!

# 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() click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 745
def schema
  db2_schema
end
schema=(schema) click to toggle source
# 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 = []) click to toggle source
# 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?() click to toggle source

@override

# File lib/arjdbc/db2/adapter.rb, line 681
def supports_views?; true end
table_definition(*args) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 259
def table_definition(*args)
  new_table_definition(TableDefinition, *args)
end
tables() click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 634
def tables
  @connection.tables(nil, schema)
end
truncate(table_name, name = nil) click to toggle source
# 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) click to toggle source
Calls superclass method
# 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) click to toggle source

@see update_lob_values? @see ArJdbc::Util::SerializedAttributes#update_lob_columns

# File lib/arjdbc/db2/adapter.rb, line 85
def update_lob_value?(value, column = nil)
  DB2.update_lob_values? && ! prepared_statements? # && value
end
zos?() click to toggle source

@private

# File lib/arjdbc/db2/adapter.rb, line 728
def zos?
  @zos = nil unless defined? @zos
  return @zos unless @zos.nil?
  @zos =
    if url = config[:url]
      !!( url =~ /^jdbc:db2j:net:/ && config[:driver] == DRIVER_NAME )
    else
      nil
    end
end

Protected Instance Methods

execute_table_change(sql, table_name, name = nil) click to toggle source
# 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

Private Instance Methods

db2_remove_column(table_name, column_name) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 773
def db2_remove_column(table_name, column_name)
  sql = "ALTER TABLE #{quote_table_name(table_name)} DROP COLUMN #{quote_column_name(column_name)}"
  execute_table_change(sql, table_name, 'Remove Column')
end
db2_schema() click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 759
def db2_schema
  @db2_schema = false unless defined? @db2_schema
  return @db2_schema if @db2_schema != false
  schema = config[:schema]
  @db2_schema =
    if schema then schema
    elsif config[:jndi] || config[:data_source]
      nil # let JNDI worry about schema
    else
      # LUW implementation uses schema name of username by default
      config[:username] || ENV['USER']
    end
end
reorg_table(table_name, name = nil) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 690
def reorg_table(table_name, name = nil)
  exec_update "call sysproc.admin_cmd ('REORG TABLE #{table_name}')", name, []
end
set_schema(schema) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 755
def set_schema(schema)
  execute("SET SCHEMA #{schema}")
end
suble_null_test(sql) click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 534
def suble_null_test(sql)
  return sql unless sql.is_a?(String)
  # DB2 does not like "= NULL", "!= NULL", or "<> NULL" :
  sql = sql.dup
  sql.gsub! IS_NOT_NULL, 'IS NOT NULL'
  sql.gsub! IS_NULL, 'IS NULL'
  sql
end
zos_create_table(name, options = {}) { |table_definition| ... } click to toggle source
# File lib/arjdbc/db2/adapter.rb, line 284
def zos_create_table(name, options = {})
  table_definition = new_table_definition TableDefinition, name, options[:temporary], options[:options], options[:as]

  unless options[:id] == false
    table_definition.primary_key(options[:primary_key] || primary_key(name))
  end

  yield table_definition if block_given?

  # Clobs in DB2 Host have to be created after the Table with an auxiliary Table.
  clob_columns = []
  table_definition.columns.delete_if do |column|
    if column.type && column.type.to_sym == :text
      clob_columns << column; true
    end
  end

  drop_table(name, options) if options[:force] && table_exists?(name)

  create_sql = "CREATE#{' TEMPORARY' if options[:temporary]} TABLE "
  create_sql << "#{quote_table_name(name)} ("
  create_sql << table_definition.to_sql
  create_sql << ") #{options[:options]}"
  if @config[:database] && @config[:tablespace]
    create_sql << " IN #{@config[:database]}.#{@config[:tablespace]}"
  end

  execute create_sql

  # Table definition is complete only when a unique index is created on the primary_key column for DB2 V8 on zOS
  # create index on id column if options[:id] is nil or id ==true
  # else check if options[:primary_key]is not nil then create an unique index on that column
  # TODO someone on Z/OS should test this out - also not needed for V9 ?
  #primary_column = options[:id] == true ? 'id' : options[:primary_key]
  #add_index(name, (primary_column || 'id').to_s, :unique => true)

  clob_columns.each do |clob_column|
    column_name = clob_column.name.to_s
    execute "ALTER TABLE #{name} ADD COLUMN #{column_name} clob"
    clob_table_name = "#{name}_#{column_name}_CD_"
    if @config[:database] && @config[:lob_tablespaces]
      in_lob_table_space = " IN #{@config[:database]}.#{@config[:lob_tablespaces][name.split(".")[1]]}"
    end
    execute "CREATE AUXILIARY TABLE #{clob_table_name} #{in_lob_table_space} STORES #{name} COLUMN #{column_name}"
    execute "CREATE UNIQUE INDEX #{clob_table_name} ON #{clob_table_name};"
  end
end