SPL
A daily dbexport on a development server failed partway through with "Illegal SPL Routine Entry", and dbschema -f all gave the same error; the poster couldn't identify which stored procedure was at fault. Suggestions were to list procedures from sysprocedures and loop running dbschema -d db -f <proc> on each to pinpoint the bad one(s), and to check for inconsistencies among sysprocauth/sysprocbody/sysprocedures/sysprocplan (likely caused by developers with DBA rights editing catalog rows). Doing that found and dropped the corrupt procedures, resolving the issue.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi fellows,
At our main development box when the daily dbexport is performed, the
following error appears:
"Illegal SPL Routine Entry"
just after a few procedures got exported.
I've tried droping a suspicious procedure but the error is still there.
Can you give me a clue whats going on? how to fix it? I thought on dropping
ALL the developers's SP but that idea sounds rather useless.
So the DB is crippled and if we need to restore from a backup I'm affraid the
DB will be unavailable.
An help is appreciated. Thank a lot.
How did you locate the "suspicious" procedure?
Do a dbschema of all procedures and see which one(s) give this error. Drop
them. Then do your export.
Cannot understand why anyone would do a daily export.
MW
> -----Original Message-----
> From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
> Behalf Of ESTEBAN RIEZNIK
> Sent: Friday, 29 October 2004 5:37 a.m.
> To: ids@iiug.org
> Subject: SPL [3585]
>
>
> Hi fellows,
>
> At our main development box when the daily dbexport is performed,
> the following error appears:
>
> "Illegal SPL Routine Entry"
>
> just after a few procedures got exported.
>
> I've tried droping a suspicious procedure but the error is still there.
> Can you give me a clue whats going on? how to fix it? I thought
> on dropping ALL the developers's SP but that idea sounds rather useless.
>
> So the DB is crippled and if we need to restore from a backup I'm
> affraid the DB will be unavailable.
>
> An help is appreciated. Thank a lot.
>
>
Well, in a development environment, a daily
export allows us to put database
schema, etc and configuration content into CVS...
Andy.
>From: "Murray Wood...." <ifxmaillist@quanta.co.nz>
>To: ids@iiug.org
>Subject: RE: SPL [3589] Date: Thu, 28 Oct 2004 15:54:04 -0400 (EDT)
>
>How did you locate the "suspicious" procedure?
>Do a dbschema of all procedures and see which one(s) give this error. Drop
>them. Then do your export.
>Cannot understand why anyone would do a daily export.
>
>MW
>
> > -----Original Message-----
> > From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
> > Behalf Of ESTEBAN RIEZNIK
> > Sent: Friday, 29 October 2004 5:37 a.m.
> > To: ids@iiug.org
> > Subject: SPL [3585]
> >
> >
> > Hi fellows,
> >
> > At our main development box when the daily dbexport is performed,
> > the following error appears:
> >
> > "Illegal SPL Routine Entry"
> >
> > just after a few procedures got exported.
> >
> > I've tried droping a suspicious procedure but the error is still there.
> > Can you give me a clue whats going on? how to fix it? I thought
> > on dropping ALL the developers's SP but that idea sounds rather useless.
> >
> > So the DB is crippled and if we need to restore from a backup I'm
> > affraid the DB will be unavailable.
> >
> > An help is appreciated. Thank a lot.
> >
> >
>
>
_________________________________________________________________
Want to block unwanted pop-ups? Download the free MSN Toolbar now!
http://toolbar.msn.co.uk/
Hi fellows
We use a daily dbexport rather than an ontape/archive, because:
- it takes up much less space (600 MB vs. 150 MB)
- it is portable!
- 100% of the time the developers ask:
- I need to retrieve all the records from table XXXXXX
- I need to retrieve the SP called YYYYY
- I need to retrieve the schema of table ZZZZZ
So its a lot easer and faster to perform that, than, say, recreate a whole DB
in another DB server and get the stuff
Well, so lets go to the point.
I thought the suspicious SP was the last one before the export interrumpted.
Yes, I was wrong.
As someone prompted, I performed a "dbschema -d databaseXXXX -f all", it also
gives the same error. Also run "update statistics for procedures", but nothing
new.
Got any clue? Any help is realy really appreciated.
thanks in advance
>From: "Murray Wood...." <ifxmaillist@quanta.co.nz>
>To: ids@iiug.org
>Subject: RE: SPL [3589] Date: Thu, 28 Oct 2004 15:54:04 -0400 (EDT)
>
>How did you locate the "suspicious" procedure?
>Do a dbschema of all procedures and see which one(s) give this error. Drop
>them. Then do your export.
>Cannot understand why anyone would do a daily export.
>
>MW
>
> > -----Original Message-----
> > From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
> > Behalf Of ESTEBAN RIEZNIK
> > Sent: Friday, 29 October 2004 5:37 a.m.
> > To: ids@iiug.org
> > Subject: SPL [3585]
> >
> >
> > Hi fellows,
> >
> > At our main development box when the daily dbexport is performed,
> > the following error appears:
> >
> > "Illegal SPL Routine Entry"
> >
> > just after a few procedures got exported.
> >
> > I've tried droping a suspicious procedure but the error is still there.
> > Can you give me a clue whats going on? how to fix it? I thought
> > on dropping ALL the developers's SP but that idea sounds rather useless.
> >
> > So the DB is crippled and if we need to restore from a backup I'm
> > affraid the DB will be unavailable.
> >
> > An help is appreciated. Thank a lot.
> >
> >
>
>
----LNX_Sun_Oct_31_2004_11:00:24_V3.33--
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
>Datum: 2004.10.30 00:11:25
>Sender: ESTEBAN RIEZNIK <erieznikr@repsolypf.com>
>
>I thought the suspicious SP was the last one before the export interrump=
ted.
>Yes, I was wrong.
>
>As someone prompted, I performed a "dbschema -d databaseXXXX -f all",it =
also
>gives the same error. Also run "update statistics for procedures", but n=
othing new.
=
Get all procedures from sysprocedures and check everyone with dbsche=
ma.
Assuming UNIX an IDS9.x (for procname[1,60]) and ksh/sh do something like=
:
echo "select procname[1,60] from sysprocedures" | dbaccess <your_db> | te=
e all_procs.out
=
Write a small shell script get_dbschema.sh like:
while read LINE
do
set -- $LINE
echo procedure $1
dbschema -d <your_db> -f $1done
=
cat all_procs.out | get_dbschema.sh 2>&1 | tee dbschema_procs.out
=
You will get a lot of missing procedures (IDS procedures) , but you shoul=
d
get on dbschema error with the illegal SPL entry and can identify t=
he
procedure from the echo line.
=
Regards,
Andreas Kutsche
------------------------------------------------
SPAR Oesterreichische Warenhandels-AG
Hauptzentrale
Europastra=DFe 3
A-5015 Salzburg
=
Telefon : +43 662 4470 24223
E-Mail : Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
------------------------------------------------
=
----LNX_Sun_Oct_31_2004_11:00:24_V3.33----
dbschema -d database -f procedurefor each procedure in your database.
MW
> -----Original Message-----
> From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
> Behalf Of ESTEBAN RIEZNIK
> Sent: Saturday, 30 October 2004 10:30 a.m.
> To: ids@iiug.org
> Subject: Re: RE: SPL [3603]
>
>
> Hi fellows
>
>
> Well, so lets go to the point.
> I thought the suspicious SP was the last one before the export
> interrumpted. Yes, I was wrong.
>
> As someone prompted, I performed a "dbschema -d databaseXXXX -f
> all", it also gives the same error. Also run "update statistics
> for procedures", but nothing new.
>
> Got any clue? Any help is realy really appreciated.
>
> thanks in advance
>
> > >
> > > -----Original Message-----
> > > From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
> > > Behalf Of ESTEBAN RIEZNIK
> > > Sent: Friday, 29 October 2004 5:37 a.m.
> > > To: ids@iiug.org
> > > Subject: SPL [3585]
> > >
> > >
> > > Hi fellows,
> > >
> > > At our main development box when the daily dbexport is performed,
> > > the following error appears:
> > >
> > > "Illegal SPL Routine Entry"
> > >
> > > just after a few procedures got exported.
> > >
> > > I've tried droping a suspicious procedure but the error is
> still there.
> > > Can you give me a clue whats going on? how to fix it? I thought
> > > on dropping ALL the developers's SP but that idea sounds
> rather useless.
> > >
> > > So the DB is crippled and if we need to restore from a backup I'm
> > > affraid the DB will be unavailable.
> > >
> > > An help is appreciated. Thank a lot.
> > >
> > >
> >
> >
>
You mentioned that this problem happens in your main
development system,
Can it be that some programmer became imaginative and deleted
sysprocbody some rows from your catalog tables?
Check the relationship between this tables
tabname sysprocauth
tabname sysprocbody
tabname sysprocedures
tabname sysprocplan
Walter
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Murray Wood....
Sent: Sunday, October 31, 2004 5:02 PM
To: ids@iiug.org
Subject: RE: RE: SPL [3609]
dbschema -d database -f procedurefor each procedure in your database.
MW
> -----Original Message-----
> From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
> Behalf Of ESTEBAN RIEZNIK
> Sent: Saturday, 30 October 2004 10:30 a.m.
> To: ids@iiug.org
> Subject: Re: RE: SPL [3603]
>
>
> Hi fellows
>
>
> Well, so lets go to the point.
> I thought the suspicious SP was the last one before the export
> interrumpted. Yes, I was wrong.
>
> As someone prompted, I performed a "dbschema -d databaseXXXX -f
> all", it also gives the same error. Also run "update statistics
> for procedures", but nothing new.
>
> Got any clue? Any help is realy really appreciated.
>
> thanks in advance
>
> > >
> > > -----Original Message-----
> > > From: forum.subscriber@iiug.org
[mailto:forum.subscriber@iiug.org]On
> > > Behalf Of ESTEBAN RIEZNIK
> > > Sent: Friday, 29 October 2004 5:37 a.m.
> > > To: ids@iiug.org
> > > Subject: SPL [3585]
> > >
> > >
> > > Hi fellows,
> > >
> > > At our main development box when the daily dbexport is performed,
> > > the following error appears:
> > >
> > > "Illegal SPL Routine Entry"
> > >
> > > just after a few procedures got exported.
> > >
> > > I've tried droping a suspicious procedure but the error is
> still there.
> > > Can you give me a clue whats going on? how to fix it? I thought
> > > on dropping ALL the developers's SP but that idea sounds
> rather useless.
> > >
> > > So the DB is crippled and if we need to restore from a backup I'm
> > > affraid the DB will be unavailable.
> > >
> > > An help is appreciated. Thank a lot.
> > >
> > >
> >
> >
>
Hi fellows
Thanks a lot for all your posts.
I did what you suggested, dbschema for every procedure and deleted all
crippled procedures... pretty silly from me i did not think of this before :-)
All the programmers have DBA role - because of management request - so it is
highly likely some "imaginative" guy has made some major f*** up while messing
with the DB.
Anyways guys, thanks a lot!