sqlpackage size

gerd_renz3

VIP Member
Hi List,
the sqlpackage associated with our production Business Data is growing over 1Gb if not deleted daily. It reaches some upper limit and becomes unable to have information written to it. Is this size of 1Bg for an sqlpackage normal?
I am not too familiar with AS400/DB2 so I would appreciate some expert comments on this.
We are on E8.9, SP3, AS400 V5R2, 300+ users .

Thanks in advance, Gerd
 
Hi Gerd!

1GB limit is the maximum on the iSeries so there’s nothing you can do about that. It is normal practice for OneWorld shops to delete their SQL packages daily. They just get repopulated the next day so there are no significant drawbacks to deleting them.

More important question is do your SQL packages actually reach that 1GB limit? If they do, next logical question is why? You would need to have lots of unique SQL statements to hit that limit and it is quite possible that is the case. However, it is also possible that he is not up to latest DB CUME package (PTFs), which could potentially alter the behavior you're seeing.

Realistically, what you're seeing is probably normal and OK. You should read the info in our newsletter and IBM web page to get more familiar with SQL packages on the iSeries irregardless. If there is no business rule prohibiting you from upgrading to the latest DB CUME package you may want to consider doing that.

Here's the newsletter link - the article's called SQL Packages in February issue, and the IBM link's in the article.
http://www.centerfieldtechnology.com/publications/


Jen
 
Thanks for your answer Jen.
As I am still AS400-half-illiterate I´ll pass it on to our system man. We have IBM suport on-site as well today.
We are in a first-month-ajustment phase after Golive. There are probably all kind of odd SQLs and UBEs being run by users and consultants to fix and fine tune things. That would explain the rapid daily growth of the SQL package.

Thanks again, Gerd
 
Gerd,
I saw this very problem about 1-1/2 years ago.
We ended up turning off SQL packages in the ODBC clients.
There was no discernable performance differences.
I had about 300 fat clients as well as Excel extracts and MS Access queries.
 
First, what is the primary purpose of a SQL package. It is to speed up the SQL query, and to help define what index/logical or scan type (table vs index) to use. I have seen 1Gb and 500Mb limits on SQLPKG's. If the SQLPKG is too large, to defeats of purpose of using it, every query will spend more time searching through the PKG, then it would actually running the query.

So the questions really are - how to keep them from growing and what is going in them?

We have a Citrix farm of about 10 servers (about 300 concurrent users) and even if I deleted the package everynight - it would be full by 08:00 every morning - not very benifical to the users.

So what is going into these packages? A lot of what was filling it was the large MRP/SRS (or what ever other large batch jobs) that we would have submitted early in the morning.

Another thing that fills up a SQLPKG is security - the more granular the security, the more parameters are passed through the query. Since most security is not the same for every role or person, this gets written over and over (where the file they are trying to hit stays the same).
To find out what is in a SQLPKG on the iSeries - use this command (PRTSQLINF OBJ(lib/pkg) OBJTYP(*SQLPKG)).

How to avoid them from growing and the users from benefiting:

If you do not have a lot of MFrame servers - you can assign a library for each SQLPKG to go to that is unique to each MFrame (this is a pain because you have to change the SQLPKG's after every package - or not push ODBC changes in packages).

The second way is to create a package with a the most queried files on a profile that has no security on it, then turn the ODBC Package tab to read only - not write. This will create a SQLPKG with the preferred indexes without the filler security. Again - a bit extreme, but it does work.

Lastely - you can try and delete them every night and hope that they do not get filled up before every one has had a chance to populate their queries in them. As a reminder on this approach, you have to get a lock on them to delete them and the initial query that writes to it will be slower than those using it there after.

Anyway - SQLPKG's can be a good thing if users benefit from them, but are a pain to manage.
 
Back
Top