[beg] which langage for stored procs ?
Christian BRUNO, new to Informix, asked which language is used for stored procedures. Paul Watson answered SPL, basically SQL with flow control. Thomas Parsli joked about it. glyn_lewis warned that SPL can cause performance problems, advising few, short procedures, no inline comments, no system calls, no nesting, no temp-table SQL, and monitoring with onstat -g ses and -g seg.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
hello, i'm new to informix (not yet installed) and i would like to know which langage i can use to write my stored procedures ? Thanks, Christian
SPL also known as Stored Procedure Language, basically SQL with flow control. Christian BRUNO wrote: > > hello, > > i'm new to informix (not yet installed) and i would like to know which > langage i can use to write my stored procedures ? > > Thanks, > Christian -- Paul Watson # WF Software Ltd # You are only young once Tel: +44 1436 674729 # but you can be immature Fax: +44 1436 678693 # for ever www.wfsoftware.com #
Paul Watson <paulw@wfsoftware.com> writes: > SPL also known as Stored Procedure Language, basically SQL with flow > control. > > Christian BRUNO wrote: > > > > hello, > > > > i'm new to informix (not yet installed) and i would like to know which > > langage i can use to write my stored procedures ? > > > > Thanks, > > Christian AKA: The Devils way of mixing VB and SQL Thomas
In article <r9d3b8ih.fsf@startsiden.no>,
Thomas Parsli <thomas.parsli@startsiden.no> wrote:
> Paul Watson <paulw@wfsoftware.com> writes:
>
> > SPL also known as Stored Procedure Language, basically SQL with flow
> > control.
> >
> > Christian BRUNO wrote:
> > >
> > > hello,
> > >
> > > i'm new to informix (not yet installed) and i would like to know
which
> > > langage i can use to write my stored procedures ?
> > >
> > > Thanks,
> > > Christian
>
> AKA: The Devils way of mixing VB and SQL
>
> Thomas
>
SPL HEALTH WARNING
Watch out when writing SPL it can cause some major performance headaches
if used for the wrong purposes. I suggest the following guidelines:-
1/ don't have too many of them as the cache for SPL is of a limited
size - see commands below that can alter some SPL settings.
2/ for the same reason don't make them too large, plus they are stored
in the databsae in relatively small chunks so the engine has to assemble
all the pieces before loading into memory and executing it.
3/ don't write in line comments, they don't get stripped out like
compiled code. Actually if you need to comment it, then it's probably
too long and not atomic - see point 8.
4/ don't make system calls from them as this forces a context switch.
5/ don't nest SPL functions as this adds to cache contention.
6/ don't use SQL inside your program that makes use of temp tables as
this forces the engine to re-optimise the SQL every time it executes -
this defeats the principle of SPL being optimised once, execuetd many
times.
7/ don't code complete applications in SPL.
8/ DO keep SPL code short and snappy and should really be an atomic
function.
9/ Monitor memory consumption using onstat -g ses and onstat -g seg and
look out for users claiming too much memory or informix launching
dynamic segments. Monitor SPL activty by running onstat -g prc
10/ Adjust the undocumented values PC_HASHSIZE, PC_POOLSIZE and
DB_MAXPROC. I seem to recall seeing a tech article saying default size
of PC_HASHSIZE is 31 (should be set to a prime number), and PC_POOLSIZE
is 50. Dunno what the default is for DB_MAXPROC but try 2 to 4 as a
starting point.
If you are starting out on Informix SPL, take a look at Michael L.
Gonzales book "Informix Stored Procedure Programming" (isbn
0-13-206723-4) to help you get started. If the onstat commands look
mysterious, then Joe Lumbley's Informix DBA Survival Guide (get the
second edition isbn 0-13-079623-9) is quite the best I have ever seen.
Glyn
Sent via Deja.com http://www.deja.com/
Before you buy.