Anyone out there successfully implement resource limits under DB2 OS/390?
We’re using the standard resource limit facility that comes with DB2 V6.1.
I’m having trouble identifying all of the various ways the users
can hit our system. I’ve identified a package called SQLLF000.
Are there others?
Also, the few tests I’ve run, indicate that the -905 SQL return code is
not being handled properly by BO (or gateway, not sure). The users
are just getting back a blank screen when the limit is exceeded.
HI Scott - we’re DB2 OS390 but I am not the systems person. If you could tell me a bit more about the packages, I’d be glad to ask here and learn more.
I assume that you know the correlations IDs for BO
wiqt.exe - ZABO
BUSOBJ.EXE - full client
SUPERVSR.EXE
DESIGNER.EXE
WISiteLog.ex - audit databse
or at least this is how we are setup
Plan is DISTSERV
your SQLLF000 is called a PROGRAM in Insight when I look at threads and all the corelations IDs have the same SQLLF000 so maybe that answers your question. later - Scott
Wow, the BO tech said you guys were fast on this forum but, wow!
I hope you’re a patient sort, since I’m totally green with BO.
I’m sure correlation ids will come in handy. Just starting to
figure out all the moving parts with this beast.
Not sure about Insight, but under AppTune and Mainview, there’s
a program type associated with the “programs”.
SQLLF000 is shown as a DB2 PACKAGE (PKGE) which indicates that
there’s been a BIND done for some product (ZABO?) on the OS390 box.
I believe all distributed data requests come in with plan name DISTSERV.
I also see other SQLxxxx packages that seem to be associated with
BO as well. Maybe for accessing meta-data kind’a operations.
SQLLF000 is definitely the heavy hitter.
As you can probably tell, I haven’t got access to the documentation yet.
I’m hoping the BO install docs will clear this up for me.
My biggest concern is how the gateway is responding to DB2 killing the
queries once they exceed their limit. The business analyst is just getting
a blank screen back. The gateway is running on an AIX box somewhere
in Cincinnati so it might take a bit for me to track down the problem!
One other issue I’ve run into, is the gateway reusing threads. Again,
I’m not sure of the architecture, but the BO tech I’ve spoken with
mentioned Universe Connection/Edit/Advanced/Connect stuff… screens
that control how long they linger. By holding these threads, mods to the
resource limits don’t take affect until they’re terminated and reestablish.
This can take quite awhile on a busy system (if ever!). I’ve never seen
a BO screen in my life, but the screens he’s talking about must be
under some sort of Administration facility.
If you do talk with your systems guy and he has implemented RLF,
a cut and paste of a few rows from your SYSIBM.DSNRLST01 table
would make my day.
SYSIBM.DSNRLST01 - for what it’s worth - there are only 2 rows and first is no different except it doesn’t have DISTSERV for plan. We have a 5 minute CPU limit on jobs. Is this table what set it? I’ll ask the DBA if they have time for a few questions and some education. And yes the BO user should get a message back when a thread is timed out OR something called “partial results” is displayed - that may depend on where the whole process is when it aborts - reading or sending. I sure don’t know much on the systems side. I’ve seen threads get reused but that hasn’t changed the way the time limit works; the timer stills seems to start over. BTW - 5 CPU minutes is 48 bucks at our regional data center - ouch, they been expensive lessons!!
Thanks for the info. Your DSNRLST01 entry looks almost identical
to the one I came up with. Looks like SQLLF000 is the guy to pick on.
You may be able to do a -DISPLAY RLIMIT command to verify that
RLF is active and that table suffix 01 is the one being used.
You’re right that each request gets it’s own limit. Thread reuse is only
an issue when you change your DSNRLST01 entry. Existing threads
continue to use the limit in affect when they were established. New
requests continue to use the “old” limit. This only showed up during
testing. I’d set the limit low to verify that it worked and when set back
up, the user continued to abend. I even stopped RLF completely and
they kept going down with a -905.
Have you ever seen a BO user exceed the limit at your site?
If so, was a message presented that let them know?
Yes, It’s cryptic … After a while the users start recognizing it (at least the usual culprit’s do ) … Here’s our message:
Error during SQL execution: [IBM][CLI Driver][DB2] SQL0905N Unsuccessful execution due to resource limit being exceeded. Resource name = "ASUTIME", limit = "000000000000" CPU seconds ("000000000000" service units) derived from "SYSIBM.DSNRLST01". SQLSTATE=57014 :-905.
Are these “full client” users that are receiving this?
Are you using InfoView? Do they receive a msg?
In our configuration, there’s a gateway between the
client and OS/390. It’s an AIX box running DB2 Connect
Enterprise Edition I believe. I’m affraid the error isn’t
being propagated back to the client
When they are running a long running query. OR
When they try to login when we take the system down for table loads (our repository is also DB2 … we set the limit down to 0 seconds during table loads, effectively locking out people from logging in)
YES
Also YES
Same Infrastructure as ours … wonder what’s different
Excellent, at least it sounds like may be just a
configuration issue and not a real product deficiency.
This is a relatively new installation and I can hardly
spell BO, so it wouldn’t surprise me that there’s more
work to be done.
One of the business analysts did state that they had
implemented ZABO recently. Not sure what that exactly
means, but is this also consistent with your configuration?
It’s starting to look I’ll need to learn how to spell AIX next!
) … Here’s our message:
(BOB member since 2002-06-18)
