search

schemacrawler / SchemaCrawler

Free database schema discovery and comprehension tool
http://www.schemacrawler.com/
Other
1.58k stars 200 forks source link

Cannot retrieve DB2 server information #1441

Closed alwaysbemark closed 5 months ago

alwaysbemark commented 5 months ago

Description

Hello, first of all - thanks for all the effort you put into SchemaCrawler, it's an amazing tool. Trying to retrieve catalog information for DB2 11.5.x does not retrieve the server information due to what seems like a broken SQL statement.

How to Reproduce

DB2 docker run -itd --name xdb2 --privileged=true -p 50000:50000 -e LICENSE=accept -e DB2INST1_PASSWORD=t1e2s3t4 -e DB2NAME=testdb -v .:/database cwds/db2

This runs the Dockerised DB2 database that we are. Then, fetch the catalog programatically.

Relevant log output

2024-01-21 15:50:01,924 WARN  [pool-1-thread-2] us.fatehi.utility.database.DatabaseUtility: Error executing SQL <SELECT
  'HOST_NAME' AS NAME,
  HOST_NAME AS VALUE,
  'Server host name.' AS DESCRIPTION
FROM
  SYSIBMADM.ENV_SYS_INFO
UNION ALL
SELECT
  'OS_NAME' AS NAME,
  OS_NAME AS VALUE,
  'Server operating system.' AS DESCRIPTION
FROM
  SYSIBMADM.ENV_SYS_INFO
UNION ALL
SELECT
  'TOTAL_CPUS' AS NAME,
  CAST(TOTAL_CPUS AS VARCHAR) AS VALUE,
  'Server total CPUs.' AS DESCRIPTION
FROM
  SYSIBMADM.ENV_SYS_INFO
UNION ALL
SELECT
  'TOTAL_MEMORY' AS NAME,
  CAST(TOTAL_MEMORY AS VARCHAR) AS VALUE,
  'Server total memory.' AS DESCRIPTION
FROM
  SYSIBMADM.ENV_SYS_INFO
UNION ALL  
SELECT 
  'CURRENT_SERVER' AS NAME,
  CURRENT SERVER AS VALUE,
  'Current server or database name.' AS DESCRIPTION
FROM 
  SYSIBM.SYSDUMMY1
UNION ALL    
SELECT 
  'INST_NAME' AS NAME,
  E.INST_NAME AS VALUE,
  'Instance name.' AS DESCRIPTION
FROM 
  SYSIBMADM.ENV_INST_INFO E 
;>
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;DM.ENV_INST_INFO E 
;END-OF-STATEMENT, DRIVER=4.25.13
    at com.ibm.db2.jcc.am.b6.a(b6.java:810)
    at com.ibm.db2.jcc.am.b6.a(b6.java:66)
    at com.ibm.db2.jcc.am.b6.a(b6.java:140)
    at com.ibm.db2.jcc.am.k3.c(k3.java:2824)
    at com.ibm.db2.jcc.am.k3.d(k3.java:2808)
    at com.ibm.db2.jcc.am.k3.a(k3.java:2234)
    at com.ibm.db2.jcc.t4.ab.i(ab.java:206)
    at com.ibm.db2.jcc.t4.ab.b(ab.java:96)
    at com.ibm.db2.jcc.t4.p.a(p.java:32)
    at com.ibm.db2.jcc.t4.av.i(av.java:150)
    at com.ibm.db2.jcc.am.k3.al(k3.java:2203)
    at com.ibm.db2.jcc.am.k3.a(k3.java:3330)
    at com.ibm.db2.jcc.am.k3.e(k3.java:1131)
    at com.ibm.db2.jcc.am.k3.execute(k3.java:1110)
    at us.fatehi.utility.database.DatabaseUtility.executeSql(DatabaseUtility.java:94)
    at schemacrawler.schemacrawler.QueryUtility.executeAgainstSchema(QueryUtility.java:68)
    at schemacrawler.crawl.MetadataResultSet.<init>(MetadataResultSet.java:85)
    at schemacrawler.crawl.DatabaseInfoRetriever.retrieveServerInfo(DatabaseInfoRetriever.java:272)
    at us.fatehi.utility.scheduler.TimedTask.call(TimedTask.java:68)
    at us.fatehi.utility.scheduler.TimedTask.call(TimedTask.java:45)
    at java.base/java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:264)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:829)
2024-01-21 15:50:01,925 WARN  [pool-1-thread-2] schemacrawler.crawl.DatabaseInfoRetriever: Could not retrieve server information
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;DM.ENV_INST_INFO E 
;END-OF-STATEMENT, DRIVER=4.25.13
    at com.ibm.db2.jcc.am.b6.a(b6.java:810)
    at com.ibm.db2.jcc.am.b6.a(b6.java:66)
    at com.ibm.db2.jcc.am.b6.a(b6.java:140)
    at com.ibm.db2.jcc.am.k3.c(k3.java:2824)
    at com.ibm.db2.jcc.am.k3.d(k3.java:2808)
    at com.ibm.db2.jcc.am.k3.a(k3.java:2234)
    at com.ibm.db2.jcc.t4.ab.i(ab.java:206)
    at com.ibm.db2.jcc.t4.ab.b(ab.java:96)
    at com.ibm.db2.jcc.t4.p.a(p.java:32)
    at com.ibm.db2.jcc.t4.av.i(av.java:150)
    at com.ibm.db2.jcc.am.k3.al(k3.java:2203)
    at com.ibm.db2.jcc.am.k3.a(k3.java:3330)
    at com.ibm.db2.jcc.am.k3.e(k3.java:1131)
    at com.ibm.db2.jcc.am.k3.execute(k3.java:1110)
    at us.fatehi.utility.database.DatabaseUtility.executeSql(DatabaseUtility.java:94)
    at schemacrawler.schemacrawler.QueryUtility.executeAgainstSchema(QueryUtility.java:68)
    at schemacrawler.crawl.MetadataResultSet.<init>(MetadataResultSet.java:85)
    at schemacrawler.crawl.DatabaseInfoRetriever.retrieveServerInfo(DatabaseInfoRetriever.java:272)
    at us.fatehi.utility.scheduler.TimedTask.call(TimedTask.java:68)
    at us.fatehi.utility.scheduler.TimedTask.call(TimedTask.java:45)
    at java.base/java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:264)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:829)
2024-01-21 15:50:01,925 INFO  [http-nio-127.0.0.1-8080-exec-9] schemacrawler.crawl.SchemaCrawler: 
-- generated by: SchemaCrawler 16.21.1
-- generated on: 2024-01-21T04:50:01.872742
-- database: DB2/LINUXX8664 SQL10055
-- driver: IBM Data Server Driver for JDBC and SQLJ 4.25.13
-- operating system: Mac OS X 14.2.1
-- JVM system: Eclipse Adoptium OpenJDK 64-Bit Server VM 11.0.15+10

SchemaCrawler Version

16.21.1

Java Version

11

Operating System and Version

Mac OS X 14.2.1

Relational Database System and Version

DB2/LINUXX8664 SQL10055

JDBC Driver and Version

IBM Data Server Driver for JDBC and SQLJ 4.25.13

sualeh commented 5 months ago

@alwaysbemark SchemaCrawler database tests run regularly with IBM DB2 11.5.8.0. The database server information is printed correctly in that case.

It seems that you are using IBM DB2 10.x, which is not supported by SchemaCrawler. Please take a look at the consulting information if you would like support for this version. The server information is reported as a warning and does not affect the operation of SchemaCrawler. Do you need this information?

You can override this with your own SQL by putting something like this below into a properties file, and specifying it on the command-line with -g <properties-file-path>.

select.ADDITIONAL_INFO.SERVER_INFORMATION= \
SELECT \
  'HOST_NAME' AS NAME, \
  HOST_NAME AS VALUE, \
  'Server host name.' AS DESCRIPTION \
FROM \
  SYSIBMADM.ENV_SYS_INFO
alwaysbemark commented 5 months ago

Thank you!