Search code examples
oracledb2db2-zos

How to execute bind variable based sql in DB2 DB2 ZOS

I am trying to execute one SQL, which is made up of bind variables. It's working fine with ORACLE DB but not with DB2

Here is the sample SQL: (UPPER/UCASE it can be anything)

SELECT Col1 FROM tab1 WHERE Col1 = 'abc' AND (UPPER(Col1) LIKE (UPPER(:1) || '%' ESCAPE '\') ORDER BY 1

Error is as follows:

db2 => SELECT Col1 FROM tab1 WHERE Col1 = 'abc' AND (UPPER(Col1) LIKE (UPPE
R(:1) || '%' ESCAPE '\') ORDER BY 1
SQL0104N  An unexpected token "1" was found following "".  Expected tokens mayinclude:  "<IDENTIFIER>".  SQLSTATE=42601

I am not sure, what is the problem here. Its not prompting for input bind value. In Oracle DB, everything is working fine.

In DB2, I used command line to execute the query. For Oracle, I used ORACLE SQL developer.

Solution

  • You are using the interactive CLP for Db2 on Microsoft Windows, while connected to a remote Db2-for-Z/OS database.

    This CLP interface does not have any functionality for prompting for host-variable values in dynamic-SQL. That's why you get the error message.

    You can either use an alternative interface (for example IBM Data Studio, or many other java based database front-end tools) , or continue to use Oracle-SQL-Developer to access Db2 for Z/OS.

    You can configure 'Oracle SQL Developer' to use the Db2 type-4 jdbc driver (db2jcc4.jar) along with the licence file for Db2 for Z/OS ('db2jcc_license_cisuz.jar'). You will need to get the Db2 licence file from your mainframe DBA or from Passport-Advantage, or alternatively connect via a Db2-connect gateway server (in which case you won't need a separate licence file on the workstation running Oracle-SQL-Developer).

    However, the degree to which each such tool understands Db2-for-Z/OS varies, so if you get issues that you cannot easily workaround, then use instead the free IBM Data Studio which works with Db2-for-Z/OS, and Db2-for-i, and Db2 for LUW.

    Configuring SQL-Developer to access Db2 is documented widely, including on this website, and also by Oracle. So do your research, this is not programming but configuration. Stackoverflow is for programming questions.