Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: db2advis error code [-106]

    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?
    Last edited by meehange; 12-15-09 at 01:47. Reason: error in prob desc

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Does the query work if you execute it from the command line? Try qualifying tables with the schema name.

  3. #3
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by db2girl View Post
    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.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •