473,543 Members | 1,925 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

No select privilege on sysibm.sysdummy 1

create table ppp
(
t int
)
@

create view v_ppp as
select t from ppp
@

select 1 from sysibm.sysdummy 1
@

create trigger q
instead of insert on v_ppp
for each row
begin atomic
declare x int;
set x = (select 1 from sysibm.sysdummy 1);
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.SYSDUMM Y1". LINE NUMBER=7. SQLSTATE=42501

The standalone select from sysibm.sysdummy 1 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

Nov 12 '05 #1
5 5666
That was ESE on Windows XP.

Nov 12 '05 #2
"Mike Gemmell" <gr************ *@hotmail.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .
create table ppp
(
t int
)
@

create view v_ppp as
select t from ppp
@

select 1 from sysibm.sysdummy 1
@

create trigger q
instead of insert on v_ppp
for each row
begin atomic
declare x int;
set x = (select 1 from sysibm.sysdummy 1);
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.SYSDUMM Y1". LINE NUMBER=7. SQLSTATE=42501

The standalone select from sysibm.sysdummy 1 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.
Nov 12 '05 #3
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

Nov 12 '05 #4
This is not an answer, just a suggestion.

Perhaps TABLE() would be better?

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

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

Nov 12 '05 #5
Actually, it was added to maintain consistency with DB2 for z/OS.

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3673
by: Andrew Werden | last post by:
I've inherited some code that calls SYSIBM.SQLPROCEDURES to validate user provided parameters and text prior to executing a stored procedure. This code was written on an early UDB release (V6?) and presumably ran fine. On some UDB v8.1 systems, calls to SYSIBM.SQLPROCEDURES take in excess of 58 seconds. My actual application sproc takes <...
9
7031
by: Raquel | last post by:
When I create a package (by db2sqljcustomize command), the new package does not immediately appear in SYSIBM.SYSPLAN for that database. It appears after some time. Does anyone know when SYSIBM.SYSPLAN gets refreshed so that the new package appears in it? I would have thought that this should be a synchronous activity. TIA Raquel.
4
4015
by: pgp.coppens | last post by:
All, Seeing the behaviour below on DB2 v8 on zOS create table test(intcol integer); insert into test values (1); insert into test values (2); insert into test values (3); insert into test values (4);
3
14899
by: bughunter | last post by:
IMHO, statements like this is mistake typically. May be more better made this construction - I said about empty WHERE - invalid? A lot of data will saved... :-) Andy
1
2247
by: alex.mcshane | last post by:
Hi - a straight-forward question for which I would be grateful for an explanation. Why are ALL SQL/PL Stored Procedures associated with SYSIBM.SYSDUMMY1 even though the latter is NOT directly accessed via SQL within the SP? Both an Explain against the Plan_Table and SYSPACKDEP state that the Table is used. >From the Plan_Table:- - Lock...
19
2269
by: natG | last post by:
On a warehouse app, our Java clients constantly load/insert rows into the db. I would like to throttle these inserts (1.5 million rows per hr) from the Java app, based on current 'busy state' of the server. It would be nice if I can get the server CPU utilization (and other key factors) from db2 via jdbc. However, it needs to be low cost,...
5
18100
by: whitsey | last post by:
Here is what I have: SELECT (SELECT COUNT(*) AS SEARCHES FROM SEARCHES INNER JOIN GROUPS ON SEARCHES.SITE_ID = GROUPS.SITE_ID WHERE
2
2019
by: gimme_this_gimme_that | last post by:
The following statement doesn't work and I need a tip. Here is a draft: This selects data from the z table. And fetches the next value for r_id_seq and creates a table named b. select z.r_name,b.r_id from (select r_name from z.r_lookup where r_id=821 ) z
1
5158
by: kropinek | last post by:
Hello! I have got: SELECT char(week_iso(current date)) FROM sysibm.sysdummy1 +------------+ | 00001 | +------------+ | 45 | +------------+
0
7354
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7746
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7693
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5888
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5282
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4898
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3394
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1824
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
643
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.