We are finally upgrading from 9.21 (ugh) to 10. I am starting to plan
the upgrade now. What pitfalls should I watch out for? What new
settings and features should I investigate. We run Alphas on tru64, if
it makes any difference (which it probably does).

What about the new VPCLASS onconfig parameters? How do you use these to
get the most out of a system?

I had a lot of problems with statement caching in 9.21 so I finally
turned it off. Is statement caching fixed and more reliable in 10. I
was assert failing on 9.21.

Thanks for any help.

Migration on same box?

Check the migration Guide...

http://publib.boulder.ibm.com/infoce....doc/mig31.htm

It looks like you can go 9.21 -> 10.0

And

http://publib.boulder.ibm.com/infoce....doc/mig60.htm

migration to a later version of the database server.

This has a nice secion on migration preperation.

The VPCLASS stuff replaces NUMCPUVPS, NUMAIOVPS, AFF_SPROC, AFF_NPROCS
and NOAGE.

See
http://publib.boulder.ibm.com/infoce...c/adref182.htm

E.g. now you will have :-

VPCLASS cpu,num=8,aff=0-7,noage
VPCLASS aio,num=2,aff=0-7,noage

Note that you can affine AIO VPS and noage them as well!

I would move to 10.00.UC4 apparently there is a bug in UC3 that has
caused at least one person I know problems.

I would also keep the onconfig the same (use BUFFERPOOL instead of
BUFFERS) and let the system run for a while
before trying new features.

Note that normal users can no longer run onstat -g ses as so be able to
see what you are inserting into a table.
In UC3 there was the onconfig paramater UNSECURE_ONSTAT
http://publib.boulder.ibm.com/infoce....doc/gsg55.htm

Check the new (I think UC4) onconfig parameter that seems to have
replaced UNSECURE_ONSTAT
http://publib.boulder.ibm.com/infoce...c/adref154.htm

SECURESTATG set to 0 so all DBA's can run onstat commands onstat -g ses
and onstat -g sql.

Funny that the link above links to a section that does not list
UNSECURE_ONSTAT as a valid onconfig parameter yet it
mentions UNSECURE_ONSTAT in the text for the description of
SECURESTATG???

Reporting this as another documentation bug...

> I had a lot of problems with statement caching in 9.21 so I finally
> turned it off. Is statement caching fixed and more reliable in 10. I
> was assert failing on 9.21.

Also in 10.00UC3R1.
IDS crashes when SSC is enabled and during evaluation of views with nested
subqueries.
Don't know if it's fixed in xC4, never tried it.

SSC? Is that Shared Statement Cache?

SQL Statement Cache.

"bozon" <curtis@crowson1.com> wrote in message
news:1134659196.580944.90680@g47g2000cwa.googlegro ups.com...
> SSC? Is that Shared Statement Cache?
>

Can you give us an example that breaks it?

> Can you give us an example that breaks it?

Yes, I can.

Here's the query:

SELECT tableX.* FROM tableX WHERE tableX.att1=?

It is run from Java app as a PreparedStatement.
tableX is in fact user's private synonym which points to a view w1 which is
defined as:

CREATE VIEW w1 AS
SELECT x.a1
, x.a2
, x.a3
FROM x
WHERE EXISTS (SELECT *
FROM y, z
WHERE x.a1 = y.a1
AND y.a2 = z.a1
AND z.a2 = USER);

If you run the query a few times IDS crashes with following trace from af
file:

0x0072e140 (oninit)afhandler(0xba1400, 0xa14000, 0x3f011668, 0x1, 0x601,
0x601)
0x0072d934 (oninit)afcrash_interface(0xb137cc, 0x48467b08, 0x3f011668,
0xb137e4, 0x1af, 0x0)
0x007322a8 (oninit)mt_ex_throw_sig(0xa12f58, 0xb108c8, 0xb9eb08, 0xb137a0,
0x44dcce18, 0x0)
0x006fdd7c (oninit)afsig_handler(0xb, 0x484680e0, 0x48467e28, 0x48467d48,
0xb8f4a8, 0xbab894)
0x001bd7f4 (oninit)sc_chkperm(0x40ba2470, 0x476457f8, 0x8000000, 0x2, 0x0,
0x418875c8)
0x001bae1c (oninit)ssc_link(0xbab800, 0x1, 0x1, 0xb8f4a8, 0x476ff310,
0x4761f020)
0x0035d594 (oninit)parsecmd(0xb8f400, 0xbab800, 0x0, 0x1, 0xb8f4a8,
0x40ba2558)
0x0035cdf4 (oninit)sqcmd (0xbab800, 0xbab894, 0x1, 0x4761b018, 0x0, 0x0)
0x0035ca68 (oninit)sq_cmnd (0xbab800, 0xb98aa4, 0x2, 0x2, 0x48468bd0, 0x0)
0x0035cab0 (oninit)sq_prepare(0xbab800, 0x1000, 0x0, 0x4761b018, 0xe, 0x1)
0x003da278 (oninit)sqmain (0x2, 0xbab800, 0xa11c00, 0x1004, 0xb98aa4,
0x1000)
0x00751708 (oninit)listen_verify(0xa45d58, 0xa, 0xa11e78, 0x0, 0x41aef8a0,
0xb16abc)
0x0070b7d8 (oninit)startup (0xba16d0, 0x7, 0x44dcce18, 0x3f0138e0,
0xffffffff, 0x3f0138a0)
0x00757f68 (oninit)net_wait_for_io(0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
0x00000000 (*nosymtab*)0x0

Designated IBM engineer said: "The issue in bug 158892 was with the checking
of permissions on every column every time the statement is executed.
Statement caching is having a problem with what you are doing."

Fixed in UC4? The release notes for UC4 are up on the IBM site but not
the list of bugs fixed!

It seems to have been packaged on 1st December. I'm not sure if it has
been released to the public yet?

I don't know if its fixed in UC4.
I changed the job in the meantime and I can't play with that instance
anymore.
I'll ask my ex-colleague to test it.
If I find some time I may even try to set up the same example on my current
test instance.

<david@smooth1.co.uk> wrote in message
news:1135114795.129276.306260@g43g2000cwa.googlegr oups.com...
>
> Fixed in UC4? The release notes for UC4 are up on the IBM site but not
> the list of bugs fixed!
>
> It seems to have been packaged on 1st December. I'm not sure if it has
> been released to the public yet?
>