db2advis error code [-106]

meehange · #1 (**permalink**) 12-15-09, 00:45

9.1 FP4a on AIX 5.3

Hey guys I keep getting an error -106 on some SQLs swhen I try and use the db2advisor on them. I've seen the APAR about TABS but there's no tabs in these SQLs (others work fine).
Here's an example

Code:

select * from asset    where location =  'CUST1'  and siteid= 'MAIN'  and status not in ( 'DECOMMISSIONED' ) and (parent is null or  not exists (select 1 from asset q where q.assetnum=asset.parent  and q.location =  'CUST1'  and q.siteid= 'MAIN'  ))

Anyone got any ideas? I can't apply any patches due to compatibility issues with some of the other apps in the system... any workaround ideas?

db2girl · #2 (**permalink**) 12-15-09, 02:02

Does the query work if you execute it from the command line? Try qualifying tables with the schema name.

meehange · #3 (**permalink**) 12-15-09, 20:57

Quote:

Originally Posted by db2girl

Does the query work if you execute it from the command line? Try qualifying tables with the schema name.

Yup sorry should have mentioned that, yes it works.
If I qualify the schema name (as I should have done in the code snip, sorry) I can execute the db2advis up to the point of the subquery

.....

OK so I solved it...... I don't think I've seen this before but the subquery needed a schema qualification in the join (but only in db2advis)! o_0

a la
q.assetnum=schema1.asset.parent

Looks funny to you? Problem solved at least.....

Internet Services

Web Development

Digital Marketing

Consumer Tech