#1097 DB2: don't display UDF source : SQL0206N "SCHEMA" is not valid in the context where it is used.

Georg Hartmann

since version 3.5 Squirrel doesn't show source code of DB2 UDF.
Version 3.4 shows it.
pls. see attached screens.
I've checked it on 2 machines and with db2 app driver and db2 net driver.

Win7 prof. 64bit:
C:\DEV>java -version
java version "1.7.0_40"
Java(TM) SE Runtime Environment (build 1.7.0_40-b43)
Java HotSpot(TM) 64-Bit Server VM (build 24.0-b56, mixed mode)

RHEL6.4 64bit:
java version "1.7.0"
Java(TM) SE Runtime Environment (build pxa6470sr4fp2-20130426_01(SR4 FP2))
IBM J9 VM (build 2.6, JRE 1.7.0 Linux amd64-64 Compressed References 20130422_146026 (JIT enabled, AOT enabled)
J9VM - R26_Java726_SR4_FP2_20130422_1320_B146026
JIT - r11.b03_20130131_32403ifx4
GC - R26_Java726_SR4_FP2_20130422_1320_B146026_CMPRSS
J9CL - 20130422_146026)
JCL - 20130425_01 based on Oracle 7u21-b09

Tried also different DBs (V9.7.7 / 9.7.8) on AIX and Windows.

In case you need more information:
I'm on vacation the next both weeks :)

kind regards Georg

3 Attachments


  • Andy Helmer
    Andy Helmer

    I have had the same problem.
    Error : when trying to see Source Code for UDFs, an SQL error is thrown. Tab does not display udf source code.

    2014-07-10 15:34:13,477 [AWT-EventQueue-1] ERROR net.sourceforge.squirrel_sql.client.session.MessagePanel - privateShowMessage: Exception was DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=SCHEMA, DRIVER=3.65.77
    com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=SCHEMA, DRIVER=3.65.77
    at com.ibm.db2.jcc.am.cd.a(cd.java:698)
    at com.ibm.db2.jcc.am.cd.a(cd.java:60)
    at com.ibm.db2.jcc.am.cd.a(cd.java:127)
    at com.ibm.db2.jcc.am.jo.c(jo.java:2753)
    at com.ibm.db2.jcc.am.jo.d(jo.java:2741)
    at com.ibm.db2.jcc.am.jo.a(jo.java:2190)
    at com.ibm.db2.jcc.am.ko.a(ko.java:7374)

    Reason : the sql to select the code of the udf from the system catalog in db2 is not generated correctly.
    The sql ist not suitable for LUW.

    File : DB2 Plugin : DB2SqlImpl.java getUserDefinedFunctionSourceSql()

    Line 60 missing BREAK in case LUW: results in running into ZOS Block and wrong SQL being generated

               case LUW:
                       result =
                                "SELECT " +
                                   "case " +
                                   "    when body is null then 'No source available' " +
                                   "    else body " +
                                   "end " +       
                                   "FROM SYSIBM.SYSFUNCTIONS " +
                                   "WHERE schema = ? " +
                                   "AND name = ? " +
                                   "AND implementation is null ";         
                        // break; is missing      
               case ZOS:
  • Gerd Wagner
    Gerd Wagner

    • status: open --> closed-fixed
  • Gerd Wagner
    Gerd Wagner

    Is fixed in our Git repository will be available in future snapshots and versions.