Issue with DS Server Routine

[das_nirmalya]

I am using DataStage server routine to connect and extract data from DB2 table.

Its running fine when '1'='1' used in where clause
DB2CntCmd = " db2 connect to " :DBName : " user " :User : " using " : Password
ResultText = @Null
Call DSExecute("UNIX", DB2CntCmd, ResultText, SystemReturnCode)

RET_REQ = "db2 -x " : "'SELECT MAX ( LAST_UPDT_DT ) FROM OZR00UC1.OXIUPD WHERE '1' = '1' WITH UR'"

Call DSExecute("UNIX", RET_REQ , ResultText, SystemReturnCode)
Ans=ResultText

Return(Ans)

But facing issue when i am using below query
RET_REQ = "db2 -x " : "'SELECT MAX ( LAST_UPDT_DT ) FROM OZR00UC1.OXIUPD WHERE 'c' = 'c' WITH UR'"

Prob is - I am not able to use any kind of Varchar field in where clause of the query

[qt_ky]

What is the issue? What do ResultText and SystemReturnCode show? Any specific error?

[das_nirmalya]

Result = SQL0206N "C" is not valid in the context where it is used. SQLSTATE=42703




This is the error i am getting

[qt_ky]

What happens if you replace the 'c' = 'c' part with an actual where clause using a real column from the table? First make sure to run the same statement successfully outside of the routine, for comparison.

[das_nirmalya]

Then also its throwing same issue.
What i noticed if any varchar value used in where clause its giving issue.

Both of the below two statement running properly in DB2. But when i am using in rouitine 2nd one throwing issue

RET_REQ = "db2 -x " : "'SELECT MAX ( LAST_UPDT_DT ) FROM OZR00UC1.OXIUPD WHERE '1' = '1' WITH UR'"

RET_REQ = "db2 -x " : "'SELECT MAX ( LAST_UPDT_DT ) FROM OZR00UC1.OXIUPD WHERE 'c' = 'c' WITH UR'"

[ArndW]

It looks like the internal single quotes are being stripped out. This is fine when "'1'" gets stripped to "1", but not when "'c'" get changed to "c".

Try doing

Code: Select all

\'c\'=\'c\'

p.s. SQL is removing those characters, not DataStage