anyone have a copy of dostats for 4gl?
A 4GL-only shop wanted to use Art Kagel's dostats utility but didn't know how to build the ESQL/C source. Answers: Informix 4GL's c4gl command can compile .ec files directly (I4GL 7.x includes a full ESQL/C compiler), and the utils2_ak makefile/README cover this; alternatively, download the free Client SDK with ESQL/C from IBM via the IIUG site. Others offered pure-SQL dbaccess scripts (and mentioned a Perl version) that generate the basic update statistics low/medium/high statements, though with fewer options than dostats; one poster reported Roland's script erroring ("unexpected null pointer") on 9.40, with no fix given.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
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;
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;