DB/2 Table Load, using "WHERE" clause

ArndW · Post by **ArndW** » Mon Aug 08, 2005 4:59 am

I have been frustrated today with what seems to be a simple problem, but I cannot get it to work; perhaps someone here knows the solution:

I am reading from DB/2 using the ODBC stage. I have specified a "table" load and it is working as I expected. Since this is a large (17 million row) daabase on the host I wanted to limit the number of rows and specified the "where clause" as a parameter.

Now when I run the job whenever I have a non-empty value in the where clause I get aborts, the first message in the log is a "main_program: Could not check all operators because of previous error(s)" and then it gives me lots of warnings about missing operators for all the columns.

I've tried several different syntaxes for this line:

WHERE IDYEAR = '2002'
IDYEAR = '2002'
Schema.IDYEAR = '2002'
SELECT * FROM Schema.Table WHERE Schema.IDYEAR = '2002'

and can't seem to get a result or a meaningful error message. The generated code doesn't help either, it doesn't put the parameter into a meaningful select.

ArndW · Post by **ArndW** » Mon Aug 08, 2005 5:33 am

I found it, should have figured it out right away.

The quotes in the WHERE clause need to be escaped, i.e.

IDYEAR = \'2002\'

and then it worked!

ray.wurlod · Post by **ray.wurlod** » Mon Aug 08, 2005 6:08 pm

Where did you find it? In a manual? Or trial and error? I just had a quick skirmish through the Parallel Job Developer's Guide and it wasn't obvious there.

ArndW · Post by **ArndW** » Mon Aug 08, 2005 11:10 pm

Ray,

I just kept plugging away at the problem, once I tried changing the quotes between single and double ones I was on the right track.

dsxdev · Post by **dsxdev** » Wed Aug 10, 2005 5:55 am

Hi
ArndW are you sure that the quote is the real problem if so
Is it the issue with ODBC stage only.
We have been using ' in many of my of our jobs in DB2 Stages and they work fine.

In any case ' should not be a problem because in the query we are not expecting Data Stage to interpret the ' as part of value but take the value between the ' characters.

ArndW · Post by **ArndW** » Wed Aug 10, 2005 6:23 am

dsxdev,

I am sure that the quotes need to be escaped when you use a parameter in the "where" column; if you type the values in directly they need not be.

-Arnd.