SQL Stored procedures

[andreyp#Antispam@mapsitnA#it4profit.com]

DB2 7.2 EE fixpack 10a

Monitoring time:

Last reset timestamp = 08/12/2003 13.13.33.298835
Snapshot timestamp = 08/12/2003 13.33.49.996443

Table snapshot

Table Schema = SYSIBM
Table Name = SYSPROCPARMS
Table Type = Catalog
Rows Read = 148058842
Rows Written = 0
Overflows = 0
Page Reorgs = 0

30 * 60 = 1800 sec
82255 rows per second

Any comments/ideas from ibm-team?

Andy

[Sean McKeough]

Re: SQL Stored procedures

Andy,

If you're running a lot of stored procedures (thinking multiple clients
here), and you have a good number of stored procs on v7, this could be
possible...in v7 CALL isn't a compiled statement, and so table scans for
sysprocedures, and sysprocparms are needed on every call. This stops in
v8, since call is compiled, and so we can use the package cache.

Or do you think the value just failed to reset?

andreyp#Antispa m@mapsitnA#it4p rofit.com wrote:[color=blue]
> DB2 7.2 EE fixpack 10a
>
> Monitoring time:
>
> Last reset timestamp = 08/12/2003 13.13.33.298835
> Snapshot timestamp = 08/12/2003 13.33.49.996443
>
> Table snapshot
>
> Table Schema = SYSIBM
> Table Name = SYSPROCPARMS
> Table Type = Catalog
> Rows Read = 148058842
> Rows Written = 0
> Overflows = 0
> Page Reorgs = 0
>
> 30 * 60 = 1800 sec
> 82255 rows per second
>
> Any comments/ideas from ibm-team?
>
> Andy
>[/color]

[Ian]

Re: SQL Stored procedures

andreyp#Antispa m@mapsitnA#it4p rofit.com wrote:[color=blue]
> DB2 7.2 EE fixpack 10a
>
> Monitoring time:
>
> Last reset timestamp = 08/12/2003 13.13.33.298835
> Snapshot timestamp = 08/12/2003 13.33.49.996443
>
> Table snapshot
>
> Table Schema = SYSIBM
> Table Name = SYSPROCPARMS
> Table Type = Catalog
> Rows Read = 148058842
> Rows Written = 0
> Overflows = 0
> Page Reorgs = 0[/color]

Have you run RUNSTATS on the catalog (SYSIBM.*) tables?




-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[andreyp#Antispam@mapsitnA#e-vision-group.com]

Re: SQL Stored procedures

sorry, but my previous post was ignored. Look:



This is part of "call procedure" statement

SELECT A.PROCSCHEMA, A.PROCNAME, A.PARMNAME, A.TYPESCHEMA, A.TYPENAME,
CASE WHEN TYPENAME = 'VARGRAPHIC' THEN A.LENGTH/2 ELSE A.LENGTH END,
A.SCALE,
A.PARM_MODE, A.ORDINAL, A.CODEPAGE
FROM "SYSIBM".SYSPRO CPARMS A, "SYSIBM".SYSPRO CEDURES B
WHERE

B.PROCSCHEMA = 'SCHEMA'AND B.PROCNAME = 'PROCNAME'

AND A.SPECIFICNAME = B.SPECIFICNAME AND A.PROCSCHEMA = B.PROCSCHEMA AND
B.PARM_COUNT = 1


We have near 14000 rows in first table and 3200 in second. This sql is not

optimal when explain because no indexes. May be rewrite to use index

...
WHERE

A.PROCSCHEMA = 'SCHEMA'AND A.PROCNAME = 'PROCNAME'
...

?

Andy

[Serge Rielau]

Re: SQL Stored procedures

Andy,

You are free to open a PMR on this problem. But the preferred solution
is to go to V8 where CALL has been addressed.
The next fixpack where anything can happen is FP12.
But given where V7 is in it's lifecycle it is not advised to add fixes
for problems that are not impeding with correctness or stability. It
just adds too much instability.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab