create table ppp
(
t int
)
@

create view v_ppp as
select t from ppp
@

select 1 from sysibm.sysdummy1
@

create trigger q
instead of insert on v_ppp
for each row
begin atomic
declare x int;
set x = (select 1 from sysibm.sysdummy1);
end@

With DB2 8 fp 7b, 8a I have been getting the following error when
creating the trigger in the above code

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0551N "CMM" does not have the privilege to perform operation
"SELECT" on
object "SYSIBM.SYSDUMMY1". LINE NUMBER=7. SQLSTATE=42501

The standalone select from sysibm.sysdummy1 works fine.

I did not have this problem with Fix Pack 7a.

Has anybody encountered this, should I even bother with downloading Fix
pack 9?

Mike

That was ESE on Windows XP.

"Mike Gemmell" <greyeggsandspam@hotmail.com> wrote in message
news:1124399362.105218.150160@o13g2000cwo.googlegr oups.com...
> create table ppp
> (
> t int
> )
> @
>
> create view v_ppp as
> select t from ppp
> @
>
> select 1 from sysibm.sysdummy1
> @
>
> create trigger q
> instead of insert on v_ppp
> for each row
> begin atomic
> declare x int;
> set x = (select 1 from sysibm.sysdummy1);
> end@
>
> With DB2 8 fp 7b, 8a I have been getting the following error when
> creating the trigger in the above code
>
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it
> returned:
> SQL0551N "CMM" does not have the privilege to perform operation
> "SELECT" on
> object "SYSIBM.SYSDUMMY1". LINE NUMBER=7. SQLSTATE=42501
>
> The standalone select from sysibm.sysdummy1 works fine.
>
> I did not have this problem with Fix Pack 7a.
>
> Has anybody encountered this, should I even bother with downloading Fix
> pack 9?
>
> Mike
>
I noticed some similarly strange behavior with FP8. I checked the privileges
on the view and noticed select on the view was not granted to public. Select
access is granted to public with databases created with FP4 and FP9 in my
shop.

First check the privileges on the view and grant select to public if not
already done. Also see APAR JR19986, which was supposed to have fixed the
problem in the following fixpacks (FP8 seems to be conspicuously absent from
the list):

Version 8 FixPak 7a
Version 8 FixPak 8a
Version 8 FixPak 9 (also known as Version 8.2 FixPak 2)
Version 8 FixPak 9a

Not sure if an upgrade of an existing db created in FP8 would fix the
problem.

Thanks Mark,

I was aware of that problem.

However, it turns out I didn't RTFM.
It appears that a user must be explicitly granted select (unless they
are a DBADM) on any tables or views referenced in any trigger they
create. No group privileges are considered in this context.

As for me not having the same symptoms on Fix Pack 7a, it turns out I
actually had DBADM privileges on the database I was using (arg).

Mike

This is not an answer, just a suggestion.

Perhaps TABLE() would be better?

set x = (select 1 from TABLE(VALUES(1)) A)

sysibm.sysdummy1 was added for help in migrating Oracle code to DB2.

Actually, it was added to maintain consistency with DB2 for z/OS.