0

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.

cpp_learner
  • 362
  • 2
  • 4
  • 14
  • Which "command line" do you mean? If you are using the shell command line on Windows or Linux or Unix (while connected to the remote database on z/os) then it does not offer prompting for parameter-markers. Oracle-SQL-Developer should be able to work with remote Db2-for-Z/Os when properly configured and licensed. – mao May 28 '19 at 12:44
  • Yeah currently I am using Window cmd prompt. So your suggestion is use Oracle-SQL-Develper to connect DB2? could you please provide me if there Is any reference document to setup the connection between Oracle SQL and Db2 – cpp_learner May 29 '19 at 10:43

1 Answers1

1

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.

mao
  • 11,321
  • 2
  • 13
  • 29
  • Yeah. It works for me 80%. Now I am able to see the DB2 configuration window in Oracle SQL developer. But I stuck at connection establishment, giving following error. An error was encountered performing the requested operation: [jcc][t4][2057][11264][3.72.24] The application server rejected establishment of the connection. An attempt was made to access a database, "DBNAME", which was either not found or does not support transactions. ERRORCODE=-4499, SQLSTATE=08004 Vendor code -4499 – cpp_learner May 30 '19 at 05:16