Re: Interrupting a scripted sql call seizes all swap and won't
In article <52mqhk$5ji@cssun.mathcs.emory.edu>, Peter Tashkoff
<TASHKOP@kiwi.co.nz> writes
> die!
>
>Hi
>Has anyone come across this one before?
>The offending script follows the sig
>line. It is designed to show up any
>tables that may have been created
>erroneously as c-isam files by our
>cobol front end processes.
>
>But if you interrupt this guy the
>dbaccess process (not oninit, dbaccess)
>consumes hundreds of megabytes of RAM
>and causes the system to run out of
>swap space. The dbaccess PID has
>meanwhile become invulnerable to
>kill-9's issued from root and the
>system must be rolled to recover from
>this. onmode -z sessionid works but
>the PID still won't die.
>We have one Gig of RAM, and an Gig and
>a half of swap. We are heavily
>overconfigured for memory and usually
>never operate at more that 400 Meg of
>RAM usage.
>OS is solaris 2.4, Informix 7.12.
>
>Thanks for the assist.
>
>--
>--
>Peter Tashkoff <tashkop@kiwi.co.nz>
>NZ Kiwifruit Marketing Board Usual
>disclaimers apply.
>This posting may not be used by any
>party to vilify another.
>
Thanks for including above - sam applies for this post - go away
Oracle!!
>----script follows
>#Now check against the database
>echo "
set explain on;> --Load the synonyms from the
>cblconfig file
> create temp table pt_temp1
>(tabname char(18));
> load from /tmp/cblconfig.files
>insert into pt_temp1;>
> --Make sure that they are really
>synonyms by
> --checking against the system
>file.
> --NOTE: files names are not case
>sensitive
> -- in informix but have been
>entered as such by
> -- developers in copybook,
>so need to upshift
> select 'The following tables
>should be synonyms but are REAL tables'
>
> from systables where tabid = 99;
> --
> select systables.tabname
> from systables , copybook,>pt_temp1
> where systables.tabname =
>pt_temp1.tabname
> and copybook.table_name =
>systables.tabname
> and
>upshift(copybook.table_name) !=
> upshift(copybook.open_name)
>
> and systables.tabtype = 'T'
>
> and systables.tabid > 99 ;
>
>
> --Now see what copybooks exist
>for tables in this database
> --Select these into another temp
>table.
> select table_name, open_name
> from copybook, systables
> where copybook.table_name =>systables.tabname
> and tabid > 99
> into temp pt_temp2;
>
> --Make everything look right to
>everything else.
> update pt_temp1
> set tabname = upshift(tabname)
> where 1=1;
>
> update pt_temp2
> set open_name =
>upshift(open_name)
> where 1=1;
>
> --Delete from temp all those
>records which exist in the
> --cblconfig file leaving just the
>supicious ones.
> delete from pt_temp2 where>open_name in (select * from pt_temp1);
>
> --All that is left is tables that
>exist in informix
> --and for which there is a
>copybook, but for which
> --no entry exists in the
>cblconfig file.
> --These are potential problem
>areas.
> unload to /tmp/suspects delimiter>' '
> select unique>trim(upshift(open_name)) || '.dat' from
>pt_temp2;
>
>" | dbaccess nzkmb
>
>
Try the above modification and also try without the comments (lines
starting with --). Sounds like a dbaccess bug. Also try putting the sql
into a file and running dbaccess <database> -qr <filename> instead.
--
David Williams