Module Sequel::DB2::DatabaseMethods
In: lib/sequel/adapters/shared/db2.rb

Methods

Constants

AUTOINCREMENT = 'GENERATED ALWAYS AS IDENTITY'.freeze
NOT_NULL = ' NOT NULL'.freeze
NULL = ''.freeze
DATABASE_ERROR_REGEXPS = { /DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505|One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index/ => UniqueConstraintViolation, /DB2 SQL Error: (SQLCODE=-530, SQLSTATE=23503|SQLCODE=-532, SQLSTATE=23504)|The insert or update value of the FOREIGN KEY .+ is not equal to any value of the parent key of the parent table|A parent row cannot be deleted because the relationship .+ restricts the deletion/ => ForeignKeyConstraintViolation, /DB2 SQL Error: SQLCODE=-545, SQLSTATE=23513|The requested operation is not allowed because a row does not satisfy the check constraint/ => CheckConstraintViolation, /DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502|Assignment of a NULL value to a NOT NULL column/ => NotNullConstraintViolation, /DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001|The current transaction has been rolled back because of a deadlock or timeout/ => SerializationFailure, }.freeze

Public Instance methods

DB2 always uses :db2 as it‘s database type

[Source]

    # File lib/sequel/adapters/shared/db2.rb, line 24
24:       def database_type
25:         :db2
26:       end

Return the database version as a string. Don‘t rely on this, it may return an integer in the future.

[Source]

    # File lib/sequel/adapters/shared/db2.rb, line 30
30:       def db2_version
31:         return @db2_version if @db2_version
32:         @db2_version = metadata_dataset.with_sql("select service_level from sysibmadm.env_inst_info").first[:service_level]
33:       end

Use SYSCAT.INDEXES to get the indexes for the table

[Source]

    # File lib/sequel/adapters/shared/db2.rb, line 75
75:       def indexes(table, opts = OPTS)
76:         m = output_identifier_meth
77:         indexes = {}
78:         metadata_dataset.
79:          from(:syscat__indexes).
80:          select(:indname, :uniquerule, :colnames).
81:          where(:tabname=>input_identifier_meth.call(table), :system_required=>0).
82:          each do |r|
83:           indexes[m.call(r[:indname])] = {:unique=>(r[:uniquerule]=='U'), :columns=>r[:colnames][1..-1].split('+').map{|v| m.call(v)}}
84:         end
85:         indexes
86:       end

Use SYSIBM.SYSCOLUMNS to get the information on the tables.

[Source]

    # File lib/sequel/adapters/shared/db2.rb, line 37
37:       def schema_parse_table(table, opts = OPTS)
38:         m = output_identifier_meth(opts[:dataset])
39:         im = input_identifier_meth(opts[:dataset])
40:         metadata_dataset.with_sql("SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = #{literal(im.call(table))} ORDER BY COLNO").
41:           collect do |column| 
42:             column[:db_type] = column.delete(:typename)
43:             if column[:db_type] =~ /\A(VAR)?CHAR\z/
44:               column[:db_type] << "(#{column[:length]})"
45:             end
46:             if column[:db_type] == "DECIMAL"
47:               column[:db_type] << "(#{column[:longlength]},#{column[:scale]})"
48:             end
49:             column[:allow_null] = column.delete(:nulls) == 'Y'
50:             identity = column.delete(:identity) == 'Y'
51:             if column[:primary_key] = identity || !column[:keyseq].nil?
52:               column[:auto_increment] = identity
53:             end
54:             column[:type]        = schema_column_type(column[:db_type])
55:             column[:max_length]  = column[:longlength] if column[:type] == :string
56:             [ m.call(column.delete(:name)), column]
57:           end
58:       end
server_version()

Alias for db2_version

DB2 supports transaction isolation levels.

[Source]

    # File lib/sequel/adapters/shared/db2.rb, line 89
89:       def supports_transaction_isolation_levels?
90:         true
91:       end

On DB2, a table might need to be REORGed if you are testing existence of it. This REORGs automatically if the database raises a specific error that indicates it should be REORGed.

[Source]

     # File lib/sequel/adapters/shared/db2.rb, line 96
 96:       def table_exists?(name)
 97:         v ||= false # only retry once
 98:         sch, table_name = schema_and_table(name)
 99:         name = SQL::QualifiedIdentifier.new(sch, table_name) if sch
100:         from(name).first
101:         true
102:       rescue DatabaseError => e
103:         if e.to_s =~ /Operation not allowed for reason code "7" on table/ && v == false
104:           # table probably needs reorg
105:           reorg(name)
106:           v = true
107:           retry 
108:         end
109:         false
110:       end

Use SYSCAT.TABLES to get the tables for the database

[Source]

    # File lib/sequel/adapters/shared/db2.rb, line 61
61:       def tables
62:         metadata_dataset.
63:           with_sql("SELECT TABNAME FROM SYSCAT.TABLES WHERE TYPE='T' AND OWNER = #{literal(input_identifier_meth.call(opts[:user]))}").
64:           all.map{|h| output_identifier_meth.call(h[:tabname]) }
65:       end

Use SYSCAT.TABLES to get the views for the database

[Source]

    # File lib/sequel/adapters/shared/db2.rb, line 68
68:       def views
69:         metadata_dataset.
70:           with_sql("SELECT TABNAME FROM SYSCAT.TABLES WHERE TYPE='V' AND OWNER = #{literal(input_identifier_meth.call(opts[:user]))}").
71:           all.map{|h| output_identifier_meth.call(h[:tabname]) }
72:       end

[Validate]