如何从表 SYSIBM 中获取column_def值.DB2 V11.5 Linux 上使用 unixODBC - Ru

Question

我在 Ubuntu 18.04 上安装 Db2 V11.5。为了连接Ruby，我使用unixODBC，ruby-odbcgem和IBM CLI/ODBC Driver。配置如下：

# /etc/odbc.ini
[SAMPLES]
Description=DB2 Conn String
Driver=DB2
Database=SAMPLE
Dbalias=SAMPLE
CurrentSchema=MYLIB_NAME
# /etc/odbcinst.ini
[DB2]
Description=DB2 Driver
Driver=/opt/ibm/db2/V11.5/lib64/libdb2.so
FileUsage=1
DontDLClose=1
LongDataCompat=1
# /opt/ibm/db2/V11.5/cfg/db2cli.ini
[SAMPLE]
Database=SAMPLE
DBName=SAMPLE
Protocol=IPC
Hostname=LOCALHOST
Port=0
Uid=db2inst1
Pwd=xxxxxxxx

连接运行良好，

$ isql -v SAMPLE
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select substr(column_name, 1,20) as colname, substr(column_def, 1,10) as coldef from sysibm.sqlcolumns where table_name='MYTABLE_NAME' and table_schem='MYLIB_NAME'
+---------------------+-----------+
| COLNAME             | COLDEF    |
+---------------------+-----------+
| AUTHOR_VISIBILITY   | 0         |
| STATUS              | 0         |
| READ_STATUS         | 0         |
| NULLABLE_STATUS     | 0         |
| LANGUAGE            | 0         |
| ILLUSTRATOR_VISIBILI| 0         |
| ID                  | ID        |
| FONT_SIZE           | 0         |
| COVER               | 'hard'    |
| NAME                | ''        |
| FORMAT              | ''        |
+---------------------+-----------+
SQLRowCount returns -1
11 rows fetched
SQL> 

不幸的是，当我尝试使用 SQL 从 ruby 中的SYSIBM.SQLCOLUMNS中获取表列元数据时，如下所示

# test.rb
require "odbc"
DB2  = ODBC.connect("SAMPLE", "db2inst1", "xxxxxxxx")
class DB
def self.connect
sql_string = <<-SQL
SELECT COLUMN_NAME, COLUMN_DEF 
FROM SYSIBM.SQLCOLUMNS
WHERE TABLE_NAME='MYTABLE_NAME' AND TABLE_SCHEM='MYLIB_NAME'
SQL
stmt = DB2.run(sql_string)
stmt.fetch_all
ensure
stmt.drop
end
end

导致

$ ./bin/console
irb(main):001:0> DB.connect
/home/yohanes/Projects/db/test.rb:10: [BUG] Segmentation fault at 0x0000564429b99c0f
ruby 2.6.5p114 (2019-10-01 revision 67812) [x86_64-linux]
-- Control frame information -----------------------------------------------
c:0021 p:---- s:0102 e:000101 CFUNC  :fetch_all
c:0020 p:0023 s:0098 e:000097 METHOD /home/yohanes/Projects/db/test.rb:10
c:0019 p:0010 s:0092 e:000091 EVAL   (irb):1 [FINISH]
c:0018 p:---- s:0089 e:000088 CFUNC  :eval
c:0017 p:0021 s:0081 e:000080 METHOD /home/yohanes/.rbenv/versions/2.6.5/lib/ruby/2.6.0/irb/workspace.rb:85
c:0016 p:0056 s:0073 e:000071 METHOD /home/yohanes/.rbenv/versions/2.6.5/lib/ruby/2.6.0/irb/context.rb:385
c:0015 p:0020 s:0064 e:000063 BLOCK  /home/yohanes/.rbenv/versions/2.6.5/lib/ruby/2.6.0/irb.rb:493
c:0014 p:0025 s:0061 e:000060 METHOD /home/yohanes/.rbenv/versions/2.6.5/lib/ruby/2.6.0/irb.rb:647
c:0013 p:0008 s:0055 e:000054 BLOCK  /home/yohanes/.rbenv/versions/2.6.5/lib/ruby/2.6.0/irb.rb:490
c:0012 p:0100 s:0050 e:000049 BLOCK  /home/yohanes/.rbenv/versions/2.6.5/lib/ruby/2.6.0/irb/ruby-lex.rb:246 [FINISH]
.
.
.
and many more lines
.
.
7f9663110000-7f9663113000 rw-p 0036f000 08:09 10097722                   /home/yohanes/.rbenv/versions/2.6.5/lib/libruby.so.2.6.5
7f9663113000-7f9663124000 rw-p 00000000 00:00 0 
7f9663124000-7f966314b000 r-xp 00000000 08:08 1185563                    /lib/x86_64-linux-gnu/ld-2.27.so
7f9663155000-7f9663175000 rw-s 00000000 00:01 196660                     /SYSV00000000 (deleted)
7f9663175000-7f966332c000 rw-p 00000000 00:00 0 
7f9663342000-7f9663349000 r--s 00000000 08:08 267796                     /usr/lib/x86_64-linux-gnu/gconv/gconv-modules.cache
7f9663349000-7f966334b000 rw-p 00000000 00:00 0 
7f966334b000-7f966334c000 r--p 00027000 08:08 1185563                    /lib/x86_64-linux-gnu/ld-2.27.so
7f966334c000-7f966334d000 rw-p 00028000 08:08 1185563                    /lib/x86_64-linux-gnu/ld-2.27.so
7f966334d000-7f966334e000 rw-p 00000000 00:00 0 
7ffebcfea000-7ffebd7e9000 rw-p 00000000 00:00 0                          [stack]
7ffebd7e9000-7ffebd7ec000 r--p 00000000 00:00 0                          [vvar]
7ffebd7ec000-7ffebd7ed000 r-xp 00000000 00:00 0                          [vdso]
ffffffffff600000-ffffffffff601000 --xp 00000000 00:00 0                  [vsyscall]

[NOTE]
You may have encountered a bug in the Ruby interpreter or extension libraries.
Bug reports are welcome.
For details: https://www.ruby-lang.org/bugreport.html
Aborted (core dumped)

但是如果我删除COLUMN_DEF，查询将按预期工作。以前我想这是ruby-odbc宝石错误。但是当我用easysoft ODBC driver替换驱动程序时，查询按预期工作，这意味着与ruby-odbcgem 无关。我的问题是如何通过使用上述配置来获取column_def值。我是否错过了odbc.ini或db2cli.ini上的任何基本 odbc 关键字配置？

Answer 1

经过反复试验，我终于找到了答案。由于没有默认值的列的COLUM_DEF包含 null 值，因此我们必须将 null 转换为其他内容，在这种情况下，我使用"NULL"代替。为此，我们可以使用NVL或COALESCE。

SELECT COLUMN_NAME, NVL(COLUMN_DEF, 'NULL') AS COLUMN_DEF
FROM SYSIBM.SQLCOLUMNS
WHERE TABLE_NAME='MYTABLE_NAME' AND TABLE_SCHEM='MYLIB_NAME'