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

snapshot
closed-fixed
nobody
5
2014-07-11
2013-09-20
Georg Hartmann
No

Hello,
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

Discussion

  • Andy Helmer
    Andy Helmer
    2014-07-11

    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()
    <
    http://sourceforge.net/p/squirrel-sql/code/HEAD/tree/trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2SqlImpl.java

    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
    2014-07-11

    • status: open --> closed-fixed
     
  • Gerd Wagner
    Gerd Wagner
    2014-07-11

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