DB2 SCHEMA errors

[Scott Calkins]

I am using SQL Workbench to access a mainframe DB2.  I can connect with no issue and access my tables via SQL using SELECT *, etc.  The problem is I cannot use the DBExplorer as it is not pulling the schema info correctly.  All that is brought up under the correct schema name is ALIAS, SEQUENCE, Procedures, and Triggers. As a side note, I also use DBVisulizer without any issues using the same JDBC driver. 

Information about the current database connection: From help email

     Product Name: DB2
     Product Version: 10.1
     Product Info: DSN10015
     Driver Name: IBM Data Server Driver for JDBC and SQLJ
     Driver Class: com.ibm.db2.jcc.DB2Driver
     Driver Version: 4.21.29
     URL:
     Isolation Level: READ COMMITTED
     Username:
     Schema: xxxx  (shows my user name, not schema?) 
     Location (Catalog):
     Workbench DBID: db2h
    

2017-10-24 15:46:18 INFO  Connected to: [DB2], Database version info: [DSN10015], Database version number: [10.1], Driver version: [4.21.29], JDBC version: [4.1], ID: [Wb1-WbExp-1]
2017-10-24 15:46:37 ERROR Error retrieving procedures using query:
SELECT '' as PROCEDURE_CAT, 
       schema as PROCEDURE_SCHEM,
       name as PROCEDURE_NAME,
       remarks,
       CASE 
         WHEN routinetype = 'F' THEN 2 
         ELSE 1 
       END as PROCEDURE_TYPE,
       NULL as SPECIFIC_NAME
FROM SYSIBM.SYSROUTINES
WHERE routinetype in ('F', 'P')
  AND origin in ('Q', 'U')
 DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=NULL, DRIVER=4.21.29 [SQL State=42703, DB Errorcode=-206]
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=NULL, DRIVER=4.21.29
 at com.ibm.db2.jcc.am.kd.a(kd.java:810)
 at com.ibm.db2.jcc.am.kd.a(kd.java:66)
 at com.ibm.db2.jcc.am.kd.a(kd.java:140)
 at com.ibm.db2.jcc.am.sp.c(sp.java:2796)
 at com.ibm.db2.jcc.am.sp.d(sp.java:2784)
 at com.ibm.db2.jcc.am.sp.a(sp.java:2212)
 at com.ibm.db2.jcc.am.sp.a(sp.java:2188)
 at com.ibm.db2.jcc.t4.bb.i(bb.java:143)
 at com.ibm.db2.jcc.t4.bb.b(bb.java:41)
 at com.ibm.db2.jcc.t4.p.a(p.java:32)
 at com.ibm.db2.jcc.t4.vb.i(vb.java:145)
 at com.ibm.db2.jcc.am.sp.kb(sp.java:2181)
 at com.ibm.db2.jcc.am.sp.a(sp.java:3300)
 at com.ibm.db2.jcc.am.sp.a(sp.java:717)
 at com.ibm.db2.jcc.am.sp.executeQuery(sp.java:696)
 at workbench.db.ibm.Db2ProcedureReader.getProcedures(Db2ProcedureReader.java:90)
 at workbench.gui.dbobjects.ProcedureListPanel._retrieve(ProcedureListPanel.java:497)
 at workbench.gui.dbobjects.ProcedureListPanel.access$000(ProcedureListPanel.java:121)
 at workbench.gui.dbobjects.ProcedureListPanel$2.run(ProcedureListPanel.java:473)

2017-10-24 15:46:37 ERROR DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=4.21.29 [SQL State=26501, DB Errorcode=-514]

[Thomas Kellerer]

Scott Calkins schrieb am 25.10.2017 um 01:32:
> I am using SQL Workbench to access a mainframe DB2. I can connect
> with no issue and access my tables via SQL using SELECT *, etc. The
> problem is I cannot use the DBExplorer as it is not pulling the
> schema info correctly. All that is brought up under the correct
> schema name is ALIAS, SEQUENCE, Procedures, and Triggers. As a side
> note, I also use DBVisulizer without any issues using the same JDBC
> driver.

Just to rule out the obvious: you did select the other object types (e.g. TABLE) in the filter dropdown?

If it doesn't load the tables, then I would need the *complete* logfile to see any possible other error that occurs during the table list retrieval.

> 2017-10-24 15:46:37 ERROR Error retrieving procedures using query:
> SELECT '' as PROCEDURE_CAT,
> schema as PROCEDURE_SCHEM,
> name as PROCEDURE_NAME,
> remarks,
> CASE
> WHEN routinetype = 'F' THEN 2
> ELSE 1
> END as PROCEDURE_TYPE,
> NULL as SPECIFIC_NAME
> FROM SYSIBM.SYSROUTINES
> WHERE routinetype in ('F', 'P')
> AND origin in ('Q', 'U')

Hmm, that query is only used on the procedures tab, not on the objects tab. So that error shouldn't prevent the table list to show up.

As I don't have access to a DB2 on z/OS (only LUW and iSeries): can you show me the columns available in SYSIBM.SYSROUTINES ?

SQLCODE=-206 means that the query uses a column which isn't valid as far as I know.
The query was contributed by a DB2 user, so maybe it's a version problem.

You can also tell SQL Workbench to use the driver's API to retrieve the procedure list by running the following statement in a SQL tab:

WbSetConfig workbench.db.db2h.procedurereader.use.jdbc=true

> Schema: xxxx (shows my user name, not schema?)

That shows whatever the driver returns as the schema name.

Regards
Thomas

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Scott Calkins]

> Just to rule out the obvious: you did select the other object types (e.g. TABLE) in the filter dropdown? 

The only items in the dropdown box are:  ALIAS, SEQUENCE, PROCEDURE, and TRIGGER.

After running the " WbSetConfig workbench.db.db2h.procedurereader.use.jdbc=true "  this is the full log:

2017-10-25 10:10:39 INFO  =================== Log started ===================
2017-10-25 10:10:39 INFO  Using configdir: C:\Users\calkinsd\.sqlworkbench
2017-10-25 10:10:39 INFO  Setting default locale to: en
2017-10-25 10:10:39 INFO  Starting SQL Workbench/J, Build 122 (2017-02-04 16:55)
2017-10-25 10:10:39 INFO  Java version=1.8.0_144, java.home=C:\Program Files\Java\jre1.8.0_144, vendor=Oracle Corporation, name=Java HotSpot(TM) 64-Bit Server VM
2017-10-25 10:10:39 INFO  Operating System=Windows 7, version=6.1, platform=amd64
2017-10-25 10:10:39 INFO  Available memory: 4615MB
2017-10-25 10:10:39 INFO  Current DPI: 96, Default DPI: 96, scale factor: 1.0
2017-10-25 10:10:39 INFO  Using sizes: toolbar: 16, menu: 16
2017-10-25 10:10:40 INFO  Loading connection profiles from C:\Users\calkinsd\.sqlworkbench\WbProfiles.xml
2017-10-25 10:10:44 WARN  Could not read version information Server returned HTTP response code: 403 for URL: http://www.sql-workbench.net/release.property
java.io.IOException: Server returned HTTP response code: 403 for URL: http://www.sql-workbench.net/release.property
 at sun.net.www.protocol.http.HttpURLConnection.getInputStream0(Unknown Source)
 at sun.net.www.protocol.http.HttpURLConnection.getInputStream(Unknown Source)
 at workbench.util.WbVersionReader.readBuildInfo(WbVersionReader.java:121)
 at workbench.util.WbVersionReader.access$000(WbVersionReader.java:43)
 at workbench.util.WbVersionReader$1.run(WbVersionReader.java:90)

2017-10-25 10:10:44 INFO  No updates found
2017-10-25 10:10:53 INFO  Creating new connection for [{STP}/STP-DEV] for driver=com.ibm.db2.jcc.DB2Driver and URL=[jdbc:db2://devplex-db2a.xxxxxxxx.xxx:7017/DT4G]
2017-10-25 10:10:53 INFO  Adding ClassLoader URL=file:/C:/Users/calkinsd/.dbvis/jdbc/DB2new/db2jcc4.jar
2017-10-25 10:10:53 INFO  Adding ClassLoader URL=file:/C:/Users/calkinsd/.dbvis/jdbc/DB2new/
2017-10-25 10:10:53 INFO  Adding C:\Users\calkinsd\.dbvis\jdbc;C:\Users\calkinsd\.dbvis\jdbc\DB2new; to java.library.path
2017-10-25 10:10:53 INFO  Creating new connection for [{STP}/STP-DEV] for driver=com.ibm.db2.jcc.DB2Driver and URL=[jdbc:db2://devplex-db2a.xxxxxxxx.xxx:7017/DT4G]
2017-10-25 10:10:53 INFO  Using DBID=db2h
2017-10-25 10:10:53 INFO  Using DBID=db2h
2017-10-25 10:10:53 INFO  Using identifier quote character: "
2017-10-25 10:10:53 INFO  Using search string escape character: \
2017-10-25 10:10:53 INFO  Using identifier quote character: "
2017-10-25 10:10:53 INFO  Using search string escape character: \
2017-10-25 10:10:54 INFO  Using regular expression for valid identifiers: [a-zA-Z][\w\$]*
2017-10-25 10:10:54 INFO  Using catalog separator: .
2017-10-25 10:10:54 INFO  Connected to: [DB2], Database version info: [DSN10015], Database version number: [10.1], Driver version: [4.21.29], JDBC version: [4.1], ID: [DbTree-1]
2017-10-25 10:10:54 INFO  Using regular expression for valid identifiers: [a-zA-Z][\w\$]*
2017-10-25 10:10:54 INFO  Using catalog separator: .
2017-10-25 10:10:54 INFO  Connected to: [DB2], Database version info: [DSN10015], Database version number: [10.1], Driver version: [4.21.29], JDBC version: [4.1], ID: [Wb1-10]
2017-10-25 10:12:07 INFO  Using template for trigger list based on DBID: [$dbid:db2h] instead of product name: DB2

When I open the tree on the schema I need, or any of them, I get the following" ALIAS(empty), SEQUENCE(2 misc. column names from one of the tables), Procedures(empty), Triggers(empty).  I should be seeing 9 tables with any where from 5 to 35 columns each

[Thomas Kellerer]

Can you switch the loglevel to DEBUG please and then send me the **complete** logfile (you can do that offlist directly to the support email)

Thomas

[Scott Calkins]

> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbench+unsub...@googlegroups.com>.

[Thomas Kellerer]

This is the culprit:

2017-10-26 10:09:00 DEBUG DbMetadata.<init> Using table types returned by the JDBC driver: []

The dropdown in the DbExplorer is populated with the information about available object types returned by call to the JDBC driver:

https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getTableTypes--

Apparently the DB2 driver does not return this information and thus the dropdown only contains the non-standard types for which I I have added explicit support.

You can manually define the types of objects the server supports, by adding the following line to workbench.settings

workbench.db.db2h.tabletypes=TABLE,VIEW

Thomas

[Scott Calkins]

Unfortunately that did not seem to resolve the issue.  Now under the Objects tab dropdown I get only ALIAS and SEQUENCE.  Sequence list two items that are column names in one of my tables. 

workbench.db.createpkname=false
workbench.db.db2h.procedurereader.use.jdbc=true
workbench.db.db2h.tabletypes=TABLE,VIEW
workbench.db.edit.verify.updateable=true
workbench.db.generate.tablesource.include.grants=true
workbench.db.oracle.fixdatetype=false
workbench.db.oracle.searchstringescape=/
workbench.db.previewsql=true

workbench.dbexplorer.allow.alter=false
workbench.dbexplorer.allow.source.edit=false
workbench.dbexplorer.assumewildcards=true
workbench.dbexplorer.datapanel.applysqlorder=true
workbench.dbexplorer.fktree.autoload=true
workbench.dbexplorer.generate.drop=none
workbench.dbexplorer.instantfilter=false
workbench.dbexplorer.mainwindow=true
workbench.dbexplorer.remember.columnorder=false
workbench.dbexplorer.rememberObjectType=false
workbench.dbexplorer.retrieveonopen=true
workbench.dbexplorer.synonyms.showtarget=true
workbench.dbexplorer.tablelist.filter.retrieve=false
workbench.dbexplorer.tablelist.history=false
workbench.dbexplorer.triggerpanel.show=true

[Thomas Kellerer]

That's strange, because this line:

2017-10-27 09:07:39 DEBUG ObjectCache.addTableList() Added 7444 objects

indicates that the DbExplorer retrieved 7444 objects.

I really have no idea what is going on there.

Thomas

> https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getTableTypes-- <https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getTableTypes-->

>
> Apparently the DB2 driver does not return this information and thus the dropdown only contains the non-standard types for which I I have added explicit support.
>
> You can manually define the types of objects the server supports, by adding the following line to workbench.settings
>
> workbench.db.db2h.tabletypes=TABLE,VIEW
>
> Thomas
>
> Scott Calkins schrieb am 26.10.2017 um 21:29:
> >
> >
> > On Wednesday, October 25, 2017 at 11:14:39 AM UTC-7, Thomas Kellerer wrote:
> >
> > Can you switch the loglevel to DEBUG please and then send me the **complete** logfile (you can do that offlist directly to the support email)
> >
> > Thomas
>

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.

> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.