Hello. I was thinking I would like to try Art's dostats tool but we
are a 4gl shop and do not use ESQL and I would have not idea how to
compile the version he has placed out on IIUG. I am running IDS
9.40.FC2 on AIX 5.2 if that matter at all.

Thanks in advance to my new favorite forum!

If you are using standard Informix 4GL, you can compile ".ec" source files
directly using the "c4gl" command. Otherwise, you can download Client SDK
(which includes ESQL/C) for any platform from IBM (see Quick Links at
www.iiug.org).

Regards,
Doug Lawry
www.douglawry.webhop.org


"sumGirl" <emebohw@netscape.net> wrote:

> Hello. I was thinking I would like to try Art's dostats tool but we
> are a 4gl shop and do not use ESQL and I would have not idea how to
> compile the version he has placed out on IIUG. I am running IDS
> 9.40.FC2 on AIX 5.2 if that matter at all.
>
> Thanks in advance to my new favorite forum!

On Wed, 28 Apr 2004 10:10:43 -0400, sumGirl wrote:

> Hello. I was thinking I would like to try Art's dostats tool but we are a
> 4gl shop and do not use ESQL and I would have not idea how to compile the
> version he has placed out on IIUG. I am running IDS 9.40.FC2 on AIX 5.2 if
> that matter at all.
>
> Thanks in advance to my new favorite forum!

The makefile and README files have instructions for compiling the utils2_ak
utilities with C4GL 7.xx which contains a full ESQL/C compiler.

Art S. Kagel

sumGirl wrote:
> Hello. I was thinking I would like to try Art's dostats tool but we
> are a 4gl shop and do not use ESQL and I would have not idea how to
> compile the version he has placed out on IIUG. I am running IDS
> 9.40.FC2 on AIX 5.2 if that matter at all.
>
> Thanks in advance to my new favorite forum!

some time ago, I wrote a little SQL-script that tries to achieve the same
as Art's dostats but with pure SQL, so all you need is dbaccess. Sure
my script lacks of many features Art's program has, but it can be used
to perform the minimal update statistics statmenets as described in
the Performance Guide.
Comments and enhancements are always welcome.

Have fun

--
Roland Wintgen (Systemadministrator)

EVG Elektro-Vertriebs-Gesellschaft Martens GmbH & Co KG
Trompeterallee 244-246, D-41189 Moenchengladbach
Tel. +49 21 66 / 55 08 23, Fax +49 21 66 / 55 08 90
www.evg.de rw@evg.de

-- UPDATE STATISTICS MEDIUM

output to pipe "dbaccess daten -" without headings;

select 'update statistics medium for table',
trim(systables.tabname),
'distributions only;'
from systables
where systables.tabid > 99
and systables.tabtype = "T"
and exists (select sysindexes.tabid
from sysindexes
where sysindexes.tabid = systables.tabid)

union

select 'update statistics medium for table',
trim(systables.tabname),
';'
from systables
where systables.tabid > 99
and systables.tabtype = "T"
and not exists (select sysindexes.tabid
from sysindexes
where sysindexes.tabid = systables.tabid);

-- UPDATE STATISTICS HIGH

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part1) = syscolumns.colno
and sysindexes.part2 = 0

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part1) = syscolumns.colno
and sysindexes.part2 <> 0
and not exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = sysindexes.part1
and si.part2 = 0)

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part2) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) <> abs(sysindexes.part2))
and not exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part2))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part3) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) <> abs(sysindexes.part3))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part4) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) <> abs(sysindexes.part4))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part5) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) <> abs(sysindexes.part5))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part6) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) = abs(sysindexes.part5)
and abs(si.part6) <> abs(sysindexes.part6))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part7) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) = abs(sysindexes.part5)
and abs(si.part6) = abs(sysindexes.part6)
and abs(si.part7) <> abs(sysindexes.part7))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part8) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) = abs(sysindexes.part5)
and abs(si.part6) = abs(sysindexes.part6)
and abs(si.part7) = abs(sysindexes.part7)
and abs(si.part8) <> abs(sysindexes.part8))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part9) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) = abs(sysindexes.part5)
and abs(si.part6) = abs(sysindexes.part6)
and abs(si.part7) = abs(sysindexes.part7)
and abs(si.part8) = abs(sysindexes.part8)
and abs(si.part9) <> abs(sysindexes.part9))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part10) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) = abs(sysindexes.part5)
and abs(si.part6) = abs(sysindexes.part6)
and abs(si.part7) = abs(sysindexes.part7)
and abs(si.part8) = abs(sysindexes.part8)
and abs(si.part9) = abs(sysindexes.part9)
and abs(si.part10) <> abs(sysindexes.part10))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part11) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) = abs(sysindexes.part5)
and abs(si.part6) = abs(sysindexes.part6)
and abs(si.part7) = abs(sysindexes.part7)
and abs(si.part8) = abs(sysindexes.part8)
and abs(si.part9) = abs(sysindexes.part9)
and abs(si.part10) = abs(sysindexes.part10)
and abs(si.part11) <> abs(sysindexes.part11))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part12) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) = abs(sysindexes.part5)
and abs(si.part6) = abs(sysindexes.part6)
and abs(si.part7) = abs(sysindexes.part7)
and abs(si.part8) = abs(sysindexes.part8)
and abs(si.part9) = abs(sysindexes.part9)
and abs(si.part10) = abs(sysindexes.part10)
and abs(si.part11) = abs(sysindexes.part11)
and abs(si.part12) <> abs(sysindexes.part12))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part13) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) = abs(sysindexes.part5)
and abs(si.part6) = abs(sysindexes.part6)
and abs(si.part7) = abs(sysindexes.part7)
and abs(si.part8) = abs(sysindexes.part8)
and abs(si.part9) = abs(sysindexes.part9)
and abs(si.part10) = abs(sysindexes.part10)
and abs(si.part11) = abs(sysindexes.part11)
and abs(si.part12) = abs(sysindexes.part12)
and abs(si.part13) <> abs(sysindexes.part13))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part14) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) = abs(sysindexes.part5)
and abs(si.part6) = abs(sysindexes.part6)
and abs(si.part7) = abs(sysindexes.part7)
and abs(si.part8) = abs(sysindexes.part8)
and abs(si.part9) = abs(sysindexes.part9)
and abs(si.part10) = abs(sysindexes.part10)
and abs(si.part11) = abs(sysindexes.part11)
and abs(si.part12) = abs(sysindexes.part12)
and abs(si.part13) = abs(sysindexes.part13)
and abs(si.part14) <> abs(sysindexes.part14))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part15) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) = abs(sysindexes.part5)
and abs(si.part6) = abs(sysindexes.part6)
and abs(si.part7) = abs(sysindexes.part7)
and abs(si.part8) = abs(sysindexes.part8)
and abs(si.part9) = abs(sysindexes.part9)
and abs(si.part10) = abs(sysindexes.part10)
and abs(si.part11) = abs(sysindexes.part11)
and abs(si.part12) = abs(sysindexes.part12)
and abs(si.part13) = abs(sysindexes.part13)
and abs(si.part14) = abs(sysindexes.part14)
and abs(si.part15) <> abs(sysindexes.part15))

union

select 'update statistics high for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ') distributions only;'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part16) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) = abs(sysindexes.part5)
and abs(si.part6) = abs(sysindexes.part6)
and abs(si.part7) = abs(sysindexes.part7)
and abs(si.part8) = abs(sysindexes.part8)
and abs(si.part9) = abs(sysindexes.part9)
and abs(si.part10) = abs(sysindexes.part10)
and abs(si.part11) = abs(sysindexes.part11)
and abs(si.part12) = abs(sysindexes.part12)
and abs(si.part13) = abs(sysindexes.part13)
and abs(si.part14) = abs(sysindexes.part14)
and abs(si.part15) = abs(sysindexes.part15)
and abs(si.part16) <> abs(sysindexes.part16))

order by 2, 3 desc;

-- UPDATE STATISTICS LOW

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part1) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part2) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part3) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part4) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part5) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part6) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part7) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part8) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part9) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part10) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part11) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part12) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part13) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part14) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part15) = syscolumns.colno

union

select 'update statistics low for table',
trim(systables.tabname),
'(' || trim(syscolumns.colname) || ');'
from systables,
sysindexes,
syscolumns
where systables.tabid > 99
and systables.tabtype = 'T'
and syscolumns.tabid = systables.tabid
and sysindexes.tabid = systables.tabid
and abs(sysindexes.part16) = syscolumns.colno

order by 2, 3;

Roland Wintgen wrote:
> sumGirl wrote:
>
>> Hello. I was thinking I would like to try Art's dostats tool but we
>> are a 4gl shop and do not use ESQL and I would have not idea how to
>> compile the version he has placed out on IIUG. I am running IDS
>> 9.40.FC2 on AIX 5.2 if that matter at all.
>>
>> Thanks in advance to my new favorite forum!
>
>
> some time ago, I wrote a little SQL-script that tries to achieve the same
> as Art's dostats but with pure SQL, so all you need is dbaccess. Sure
> my script lacks of many features Art's program has, but it can be used
> to perform the minimal update statistics statmenets as described in
> the Performance Guide.
> Comments and enhancements are always welcome.
>

Littler :

unload to update_stats.sql delimiter ";"

select "update statistics low drop distributions" from systables
where tabid = 1

union ALL
select unique "update statistics medium for table
"||t.tabname||"("||trim(c.colname)||")"
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and c.colno in (
i.part2, i.part3, i.part4, i.part5, i.part6, i.part7, i.part8,
i.part9,
i.part10, i.part11, i.part12, i.part13, i.part14, i.part15, i.part16)
and tabtype = 'T'
and c.colno not in (select i1.part1 from sysindexes i1 where i1.tabid =
t.tabid)

union ALL
select unique "update statistics high for table
"||t.tabname||"("||trim(c.colname)||")"
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part1 = c.colno
and tabtype = 'T'

union ALL
select "update statistics for procedure" from systables where
tabid = 1;

On Thu, 29 Apr 2004 07:55:53 -0400, TBP wrote:

Roland's script is more complete than TBP's SQL. Also there is a Perl version
of dostats someone else wrote (sorry I can't keep track of who wrote what
anymore). As Roland says, not as many options as dostats, but they get the
badsic job done.

Art S. Kagel

> Roland Wintgen wrote:
>> sumGirl wrote:
>>
>>> Hello. I was thinking I would like to try Art's dostats tool but we are a
>>> 4gl shop and do not use ESQL and I would have not idea how to compile the
>>> version he has placed out on IIUG. I am running IDS 9.40.FC2 on AIX 5.2 if
>>> that matter at all.
>>>
>>> Thanks in advance to my new favorite forum!
>>
>>
>> some time ago, I wrote a little SQL-script that tries to achieve the same
>> as Art's dostats but with pure SQL, so all you need is dbaccess. Sure my
>> script lacks of many features Art's program has, but it can be used to
>> perform the minimal update statistics statmenets as described in the
>> Performance Guide.
>> Comments and enhancements are always welcome.
>>
>>
> Littler :
>
> unload to update_stats.sql delimiter ";"
>
> select "update statistics low drop distributions" from systables where
> tabid = 1
>
> union ALL
> select unique "update statistics medium for table
> "||t.tabname||"("||trim(c.colname)||")" from sysindexes i, syscolumns c,
> systables t where i.tabid > 99
> and i.tabid = c.tabid
> and i.tabid = t.tabid
> and c.colno in (
> i.part2, i.part3, i.part4, i.part5, i.part6, i.part7, i.part8,
> i.part9,
> i.part10, i.part11, i.part12, i.part13, i.part14, i.part15, i.part16) and
> tabtype = 'T'
> and c.colno not in (select i1.part1 from sysindexes i1 where i1.tabid =
> t.tabid)
>
> union ALL
> select unique "update statistics high for table
> "||t.tabname||"("||trim(c.colname)||")" from sysindexes i, syscolumns c,
> systables t where i.tabid > 99
> and i.tabid = c.tabid
> and i.tabid = t.tabid
> and i.part1 = c.colno
> and tabtype = 'T'
>
> union ALL
> select "update statistics for procedure" from systables where tabid =
> 1;

Hi Art,

Could you clarify what you mean by "more complete"?

The script I provided works on the following basis :

"update statistics low drop distributions"
"update statistics medium for trailing columns in indexes"
"update statistics high for leading columns in indexes"
"update statistics for stored procedures"

Also, I tried running Rolands against a V9.40 instance and this fails with :

and abs(sysindexes.part16) = syscolumns.colno
and exists (select si.tabid
from sysindexes si
where si.tabid = systables.tabid
and abs(si.part1) = abs(sysindexes.part1)
and abs(si.part2) = abs(sysindexes.part2)
and abs(si.part3) = abs(sysindexes.part3)
and abs(si.part4) = abs(sysindexes.part4)
and abs(si.part5) = abs(sysindexes.part5)
and abs(si.part6) = abs(sysindexes.part6)
and abs(si.part7) = abs(sysindexes.part7)
and abs(si.part8) = abs(sysindexes.part8)
and abs(si.part9) = abs(sysindexes.part9)
and abs(si.part10) = abs(sysindexes.part10)
and abs(si.part11) = abs(sysindexes.part11)
and abs(si.part12) = abs(sysindexes.part12)
and abs(si.part13) = abs(sysindexes.part13)
and abs(si.part14) = abs(sysindexes.part14)
and abs(si.part15) = abs(sysindexes.part15)
and abs(si.part16) <> abs(sysindexes.part16))

order by 2, 3 desc;
# ^
# 392: System error - unexpected null pointer encountered.

TBP
Art S. Kagel wrote:

> On Thu, 29 Apr 2004 07:55:53 -0400, TBP wrote:
>
> Roland's script is more complete than TBP's SQL. Also there is a Perl version
> of dostats someone else wrote (sorry I can't keep track of who wrote what
> anymore). As Roland says, not as many options as dostats, but they get the
> badsic job done.
>
> Art S. Kagel
>
>
>>Roland Wintgen wrote:
>>
>>>sumGirl wrote:
>>>
>>>
>>>>Hello. I was thinking I would like to try Art's dostats tool but we are a
>>>>4gl shop and do not use ESQL and I would have not idea how to compile the
>>>>version he has placed out on IIUG. I am running IDS 9.40.FC2 on AIX 5.2 if
>>>>that matter at all.
>>>>
>>>>Thanks in advance to my new favorite forum!
>>>
>>>
>>>some time ago, I wrote a little SQL-script that tries to achieve the same
>>>as Art's dostats but with pure SQL, so all you need is dbaccess. Sure my
>>>script lacks of many features Art's program has, but it can be used to
>>>perform the minimal update statistics statmenets as described in the
>>>Performance Guide.
>>>Comments and enhancements are always welcome.
>>>
>>>
>>
>>Littler :
>>
>>unload to update_stats.sql delimiter ";"
>>
>>select "update statistics low drop distributions" from systables where
>>tabid = 1
>>
>>union ALL
>>select unique "update statistics medium for table
>>"||t.tabname||"("||trim(c.colname)||")" from sysindexes i, syscolumns c,
>>systables t where i.tabid > 99
>>and i.tabid = c.tabid
>>and i.tabid = t.tabid
>>and c.colno in (
>>i.part2, i.part3, i.part4, i.part5, i.part6, i.part7, i.part8,
>>i.part9,
>>i.part10, i.part11, i.part12, i.part13, i.part14, i.part15, i.part16) and
>>tabtype = 'T'
>>and c.colno not in (select i1.part1 from sysindexes i1 where i1.tabid =
>>t.tabid)
>>
>>union ALL
>>select unique "update statistics high for table
>>"||t.tabname||"("||trim(c.colname)||")" from sysindexes i, syscolumns c,
>>systables t where i.tabid > 99
>>and i.tabid = c.tabid
>>and i.tabid = t.tabid
>>and i.part1 = c.colno
>>and tabtype = 'T'
>>
>>union ALL
>>select "update statistics for procedure" from systables where tabid =
>>1;

> The makefile and README files have instructions for compiling the utils2_ak
> utilities with C4GL 7.xx which contains a full ESQL/C compiler.
> Art S. Kagel

I downloaded and read trough, commenting in and out as needed. Gave
MAKE a try, and I keep raising errors:
"Makefile", line 53: make: 1254-055 Dependency line needs colon or
double colon
operator.
"Makefile", line 57: make: 1254-055 Dependency line needs colon or
double colon
operator.
"Makefile", line 63: make: 1254-055 Dependency line needs colon or
double colon
operator.

Anyone else fight this? I am running c4gl 7.30.UC6, on AIX 5.2.
-wc

sumGirl wrote:
> > The makefile and README files have instructions for compiling the
utils2_ak
> > utilities with C4GL 7.xx which contains a full ESQL/C compiler.
> > Art S. Kagel
>
> I downloaded and read trough, commenting in and out as needed. Gave
> MAKE a try, and I keep raising errors:
> "Makefile", line 53: make: 1254-055 Dependency line needs colon or
> double colon
> operator.
> "Makefile", line 57: make: 1254-055 Dependency line needs colon or
> double colon
> operator.
> "Makefile", line 63: make: 1254-055 Dependency line needs colon or
> double colon
> operator.
>
> Anyone else fight this? I am running c4gl 7.30.UC6, on AIX 5.2.
> -wc

I'm guessing that you lost something when "commenting in and out" the
makefile; it sounds like MAKE may be confusing a command line for a
dependency/rules line. Are you certain your command lines all begin with a
tab, for example?

--
June Hunt

On Thu, 29 Apr 2004 15:29:20 -0400, sumGirl wrote:

You might have missed one trick. From the README.1st:

Reworked to permit compilation of dostats.ec using c4gl versions that do not
support host variable declarations in ANSI style function parameter lists.
I've used GNU make syntax so if you only have UNIX make available you'll need
to edit the Makefile to either remove the ifeq verb or (if you are compiling
with C4GL) change it to shell logic.

Sounds like you are using UNIX make. Wherever you see a construct like:

ifeq ($(ESQL),c4gl)
....
else
....
endif

comment out or, better, remove the 'ifeq' line and everything from 'else' to
'endif' and unindent the remaining text. The remaining make file should work
with any UNIX compliant make version. If not send me details of your
environment/platform, a copy of the edited makefile, and your make version.

Or you could just get GNU Make. ;-)

Art S. Kagel

>> The makefile and README files have instructions for compiling the utils2_ak
>> utilities with C4GL 7.xx which contains a full ESQL/C compiler. Art S.
>> Kagel
>
> I downloaded and read trough, commenting in and out as needed. Gave MAKE a
> try, and I keep raising errors:
> "Makefile", line 53: make: 1254-055 Dependency line needs colon or double
> colon
> operator.
> "Makefile", line 57: make: 1254-055 Dependency line needs colon or double
> colon
> operator.
> "Makefile", line 63: make: 1254-055 Dependency line needs colon or double
> colon
> operator.
>
> Anyone else fight this? I am running c4gl 7.30.UC6, on AIX 5.2. -wc