dbimport failed - IDS 9.30 UC3

[Hari Gupta]

Hi,

I have recently installed IDS 9.30 UC3 on Linux 7.3. Done a dbexport
with -ss and started dbimport with -d <dbspace-name>. After about 5
hrs, dbimport failed with following error:

------------------------------------
*** prepare sqlobj
201 - A syntax error has occurred.
------------------------------------

The statement just above where dbimport failed was a "create trigger"
as below:

-------------------------------------------------------------------
create trigger "auth".trg_altask_del delete on "auth".aualtask
referencing old as old
for each row
(
execute procedure "auth".sp_stop_clock('D' , , ,old.doc_typ
,old.doc_yer ,old.doc_num ,old.doc_prt ,old.wrk_typ ,old.seq_num
, , , ,old.mdu_ref ));

*** prepare sqlobj
201 - A syntax error has occurred.
-------------------------------------------------------------------

Pl. note that after this create statement, all statement were update
stats.
So what I did, created trigger seperately and ran update stats based
upon dbexport SQL file using dbaccess.

I did read few archives about this error but none of them reported
above error followed by 201 error.

There was an issues similar to this with dbimport in earlier verson of
IDS but I don't think it will be still an known bug with IDS 9.30 UC3.

Can some guru help me about this please ?

Pl. let me know if onconfig or some other info. is needed.

TIA

Hari

[David E. Grove]

We have also experienced this exact problem with dbimport. We opened a tech
support case with IBM. So far the issue is still in progress. In a
nutshell, IBM says there is a known bug with dbexport/dbimport. Dbimport
will sometimes fail when it attempts to use the product of dbexport. Our
engineer said the problem may have been fixed in 9.4.

Personally, I find it absolutely unacceptable that a fundamental, mature,
required utility, such as dbimport/dbexport, should fail. The tech suggests
that we might migrate to 9.4 (how do we migrate if dbimport/dbexport
fails?), or that we do not use dbimport/dbexport-- they suggest
onunload/onload or some other alternative, instead. (Of course,
onunload/onload can't be used to migrate between versions, so that is a
non-solution, also).

I got the impression that IBM may not be willing to fix dbexport/dbimport,
but I don't know this for sure, as we are still working through this tech
support case.

Anyway, some additional specifics on the "known bug", as sent to us by IBM
tech support, are posted below. I have not yet had the time to examine
thoroughly the .sql file produced by dbexport (and used by dbimport) to see
if the "tab problem" (described below in the material from IBM) is the
culprit, in our case. A cursory look seemed to indicate it is not. But,
maybe that is what is the problem in your case, in which some editing may
fix it.

One other thing-- due to the way error messages are buffered, the problem
may not be where the error message occurs.

I repeat my total surprise and disatisfaction to learn that such fundamental
utilities as dbimport/dbexport are broken, have been known to be broken for
years, and remain unfixed. I intend no disrespect to the tech support
engineer, who has been courteous and attentive. The problem is much deeper.




Bug: 157723 A DBIMPORT OF A DB ENDS WITH MSG *** PREPARE SQLOBJ, 201 - A
SYNTAX ERROR HAS OCCURED - WHEN ONE STORED PROCEDURE FOLLOWS ANOTHER IN SQL
FILE

Description:
When an imported stored procedure contains if ... end if; i.e.:


CREATE DBA procedure "informix".test1( )

define _anyone SmallInt;

if (SELECT Count(*) FROM Anything) = 0 then
let _anyone = 0;
end if;


end procedure;

CREATE DBA procedure "informix".test2( )


define _anyone SmallInt;

if (SELECT Count(*) FROM Anything) = 0 then
let _anyone = 0;
end if;

end procedure;


- it's caused by the tabs for the "end procedure" statement. Remove the tabs
and it's okay.

Reproduces on 9.21 and 9.30. Seems okay on 7.31 family.

WORKAROUND:

Remove the tab space from the "end procedure" statements in the
sql file so that the statement is fully left-justified

Appears that this will not reproduce in 9.4.





"Hari Gupta" <hariog@yahoo.com> wrote in message
news:1a1cd35b.0310052339.69ca95e2@posting.google.c om...

> Hi,
>
> I have recently installed IDS 9.30 UC3 on Linux 7.3. Done a dbexport
> with -ss and started dbimport with -d <dbspace-name>. After about 5
> hrs, dbimport failed with following error:
>
> ------------------------------------
> *** prepare sqlobj
> 201 - A syntax error has occurred.
> ------------------------------------
>
> The statement just above where dbimport failed was a "create trigger"
> as below:
>
> -------------------------------------------------------------------
> create trigger "auth".trg_altask_del delete on "auth".aualtask
> referencing old as old
> for each row
> (
> execute procedure "auth".sp_stop_clock('D' , , ,old.doc_typ
> ,old.doc_yer ,old.doc_num ,old.doc_prt ,old.wrk_typ ,old.seq_num
> , , , ,old.mdu_ref ));
>
> *** prepare sqlobj
> 201 - A syntax error has occurred.
> -------------------------------------------------------------------
>
> Pl. note that after this create statement, all statement were update
> stats.
> So what I did, created trigger seperately and ran update stats based
> upon dbexport SQL file using dbaccess.
>
> I did read few archives about this error but none of them reported
> above error followed by 201 error.
>
> There was an issues similar to this with dbimport in earlier verson of
> IDS but I don't think it will be still an known bug with IDS 9.30 UC3.
>
> Can some guru help me about this please ?
>
> Pl. let me know if onconfig or some other info. is needed.
>
> TIA
>
> Hari

[Neil Truby]

"David E. Grove" <david_grove@correct.state.ak.us> wrote in message
news:vo3rrqfnf163fe@corp.supernews.com...

> We have also experienced this exact problem with dbimport.

> The tech suggests
> that we might migrate to 9.4 (how do we migrate if dbimport/dbexport
> fails?), or that we do not use dbimport/dbexport-- they suggest
> onunload/onload or some other alternative, instead. (Of course,
> onunload/onload can't be used to migrate between versions, so that is a
> non-solution, also).

Depending upon IDS release, onload ususally has bugs or "features" that make
using it somewhere between "difficult and time-consuming" and "impossible".

> I got the impression that IBM may not be willing to fix dbexport/dbimport

....

The attitude seems to be that, if you have a reasonable workaround (which,
correct me if I'm wrong, they have provided) they'll probably not fix it in
current releases.

> I repeat my total surprise and disatisfaction to learn that such

fundamental

> utilities as dbimport/dbexport are broken, have been known to be broken

for

> years, and remain unfixed.

I echo that. Well, maybe not the surprise, but certainly the
dissatisfaction ... :-)

[Tsutomu Ogiwara]

Hi Hari.

I attemped your trigger on 9.30.UC3 and 9.40.UC2 on Linux.
Both products outputs -201.

create trigger "auth".trg_altask_del delete on "auth".aualtask
referencing old as old
for each row
(
execute procedure "auth".sp_stop_clock('D' , , ,old.doc_typ
# ^
# 201: A syntax error has occurred.
#

But If 'D' ,'' ,'' ,old.doc_typ... intead of 'D' , , ,old.doc_typ, probably
it works.

You may call IBM Informix response center to open the case.

Regards.
--
Tsutomu Ogiwara from Tokyo Japan.
ICQ#:168106592

>From: [email]hariog@yahoo.com[/email] (Hari Gupta)
>Reply-To: [email]hariog@yahoo.com[/email] (Hari Gupta)
>To: [email]informix-list@iiug.org[/email]
>Subject: dbimport failed - IDS 9.30 UC3
>Date: 6 Oct 2003 00:39:12 -0700
>
>Hi,
>
>I have recently installed IDS 9.30 UC3 on Linux 7.3. Done a dbexport
>with -ss and started dbimport with -d <dbspace-name>. After about 5
>hrs, dbimport failed with following error:
>
>------------------------------------
>*** prepare sqlobj
>201 - A syntax error has occurred.
>------------------------------------
>
>The statement just above where dbimport failed was a "create trigger"
>as below:
>
>-------------------------------------------------------------------
>create trigger "auth".trg_altask_del delete on "auth".aualtask
> referencing old as old
> for each row
> (
> execute procedure "auth".sp_stop_clock('D' , , ,old.doc_typ
> ,old.doc_yer ,old.doc_num ,old.doc_prt ,old.wrk_typ ,old.seq_num
> , , , ,old.mdu_ref ));
>
>*** prepare sqlobj
>201 - A syntax error has occurred.
>-------------------------------------------------------------------
>
>Pl. note that after this create statement, all statement were update
>stats.
>So what I did, created trigger seperately and ran update stats based
>upon dbexport SQL file using dbaccess.
>
>I did read few archives about this error but none of them reported
>above error followed by 201 error.
>
>There was an issues similar to this with dbimport in earlier verson of
>IDS but I don't think it will be still an known bug with IDS 9.30 UC3.
>
>Can some guru help me about this please ?
>
>Pl. let me know if onconfig or some other info. is needed.
>
>TIA
>
>Hari

__________________________________________________ _______________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
[url]http://join.msn.com/?page=features/virus[/url]

sending to informix-list

[Rüdiger Mähl]

Hi Hari,


[email]hariog@yahoo.com[/email] (Hari Gupta) wrote:

> Hi,
>
> I have recently installed IDS 9.30 UC3 on Linux 7.3. Done a dbexport
> with -ss and started dbimport with -d <dbspace-name>. After about 5
> hrs, dbimport failed with following error:
>
> ------------------------------------
> *** prepare sqlobj
> 201 - A syntax error has occurred.
> ------------------------------------
>
> The statement just above where dbimport failed was a "create trigger"
> as below:
>
> -------------------------------------------------------------------
> create trigger "auth".trg_altask_del delete on "auth".aualtask
> referencing old as old
> for each row
> (
> execute procedure "auth".sp_stop_clock('D' , , ,old.doc_typ
> ,old.doc_yer ,old.doc_num ,old.doc_prt ,old.wrk_typ ,old.seq_num
> , , , ,old.mdu_ref ));
>
> *** prepare sqlobj
> 201 - A syntax error has occurred.
> -------------------------------------------------------------------

IIRC, you need to insert 'null' at the empty fields:
execute procedure "auth".sp_stop_clock('D' ,null ,null ,old.doc_typ...

Then it should work. This is a known bug (looks like 158980) and
shall be gone with 9.30.UC4

see also (link wrapped):
[url]http://www-3.ibm.com/software/data/informix/pubs/[/url]
library/notes/relnotes/C170109.html

Regards,

Rüdiger

[Hari Gupta]

Thanks to David, Neil and special thanks to Tsutomu (for testing
trigger with 9.30 & 9.40) who have given me the right direction. I was
a bit concern till y'day when I did not see any reply to my post.
I was feeling orphan for a while without our newsgroup community
reply. Anyway.

I totally agree with David and his experience with Tech Support from
IBM. Sorry to say that I am also having almost simlilar experience
with IBM support through one of our application software vendor. Just
to mention an example - I have been using ER between IDS 9.21 UC3 and
IDS 9.21 TC3 for about 2 years without any major issue. Recently, we
have installed a new server and installed IDS 9.30 UC3 on primary
server. We asked especifically from IBM informix (probably IBM
Australia) if there is any known issue with ER between IDS 9.30 UC3
and IDS 9.21 TC3 and guess what ????? We were told to go ahead with
ER.Very next day the target server (which is also a production server)
crashed with an assert failure. We logged the call again with IBM and
this time they said we might need to upgrade tartget to IDS 9.30 TC6.
Is this a JOKE or CRAP ???? Anyway.

Sorry, forget to mention that I got a very good advise from Madison
Pruet thru newsgroup to use CDRSITES environment variables if we need
to use ER between two different IDS versons. That make sense.

I will contact IBM Tech support to see what they say this time.

Thanks again.

Hari

[Art S. Kagel]

On Mon, 06 Oct 2003 03:39:12 -0400, Hari Gupta wrote:

This may not be a dbexport/dbimport problem but an engine problem. Check the
trigger text in systrigbody and see if the stored procedure call there is
missing the NULL's for the second and third arguments to procedure
sp_stop_clock. There have been other problems with the engine mucking with
trigger text before saving it (the last was a tendency to add so many layers of
redundant parenthesis that the trigger text output by dbschema or dbexport
could not be executed to recreate the trigger without editing it). This may be
something similar. However, if the trigger text looks OK in systrigbody you
can use my dbschema replacement utility with its -l option to output a dbimport
compatible schema file which you can use to import the database, in general, or
just edit the schema and add the missing args manually.

Myschema is included in the package utils2_ak available from the IIUG Software
Repository.

Art S. Kagel

> Hi,
>
> I have recently installed IDS 9.30 UC3 on Linux 7.3. Done a dbexport with -ss
> and started dbimport with -d <dbspace-name>. After about 5 hrs, dbimport
> failed with following error:
>
> ------------------------------------
> *** prepare sqlobj
> 201 - A syntax error has occurred.
> ------------------------------------
>
> The statement just above where dbimport failed was a "create trigger" as
> below:
>
> ------------------------------------------------------------------- create
> trigger "auth".trg_altask_del delete on "auth".aualtask
> referencing old as old
> for each row
> (
> execute procedure "auth".sp_stop_clock('D' , , ,old.doc_typ
> ,old.doc_yer ,old.doc_num ,old.doc_prt ,old.wrk_typ ,old.seq_num , , ,
> ,old.mdu_ref ));
>
> *** prepare sqlobj
> 201 - A syntax error has occurred.
> -------------------------------------------------------------------
>
> Pl. note that after this create statement, all statement were update stats. So
> what I did, created trigger seperately and ran update stats based upon
> dbexport SQL file using dbaccess.
>
> I did read few archives about this error but none of them reported above error
> followed by 201 error.
>
> There was an issues similar to this with dbimport in earlier verson of IDS but
> I don't think it will be still an known bug with IDS 9.30 UC3.
>
> Can some guru help me about this please ?
>
> Pl. let me know if onconfig or some other info. is needed.
>
> TIA
>
> Hari

[Hari Gupta]

Thank you very much Art. Appreciated for explaining the issue and work
around solution as well.
BUT I still not don't get why one has to look for dbexport SQL file and edit
manually. When this is
a known bug then why can't IBM fix this ? When professional (Guru) like Art
can fix such issue with their
own developed utilities, IBM _SHOULD_ fix without any if and buts.

Sorry about my language if I said some thing wrong.

Hari
"Hari Gupta" <hariog@yahoo.com> wrote in message
news:1a1cd35b.0310052339.69ca95e2@posting.google.c om...

> Hi,
>
> I have recently installed IDS 9.30 UC3 on Linux 7.3. Done a dbexport
> with -ss and started dbimport with -d <dbspace-name>. After about 5
> hrs, dbimport failed with following error:
>
> ------------------------------------
> *** prepare sqlobj
> 201 - A syntax error has occurred.
> ------------------------------------
>
> The statement just above where dbimport failed was a "create trigger"
> as below:
>
> -------------------------------------------------------------------
> create trigger "auth".trg_altask_del delete on "auth".aualtask
> referencing old as old
> for each row
> (
> execute procedure "auth".sp_stop_clock('D' , , ,old.doc_typ
> ,old.doc_yer ,old.doc_num ,old.doc_prt ,old.wrk_typ ,old.seq_num
> , , , ,old.mdu_ref ));
>
> *** prepare sqlobj
> 201 - A syntax error has occurred.
> -------------------------------------------------------------------
>
> Pl. note that after this create statement, all statement were update
> stats.
> So what I did, created trigger seperately and ran update stats based
> upon dbexport SQL file using dbaccess.
>
> I did read few archives about this error but none of them reported
> above error followed by 201 error.
>
> There was an issues similar to this with dbimport in earlier verson of
> IDS but I don't think it will be still an known bug with IDS 9.30 UC3.
>
> Can some guru help me about this please ?
>
> Pl. let me know if onconfig or some other info. is needed.
>
> TIA
>
> Hari

[David E. Grove]

> The attitude seems to be that, if you have a reasonable workaround (which,

> correct me if I'm wrong, they have provided) they'll probably not fix it

in

> current releases.

We have not yet established that there is a workaround.

The last few days I've been diverted to other more urgent activities, so I
haven't determined for sure that the suggested work around applies. I need
to examine more closely the .sql file produced by dbexport. We have 1000
tables and similar number of SPs. An initial, brief examination of the
..sql file in the vicinity of the error message doesn't appear to display any
left justification issues. So, no workaround, yet.

DG




"Neil Truby" <neil.truby@ardenta.com> wrote in message
news:blt5l7$g78sr$1@ID-162943.news.uni-berlin.de...

> "David E. Grove" <david_grove@correct.state.ak.us> wrote in message
> news:vo3rrqfnf163fe@corp.supernews.com...

> > We have also experienced this exact problem with dbimport.

>

> > The tech suggests
> > that we might migrate to 9.4 (how do we migrate if dbimport/dbexport
> > fails?), or that we do not use dbimport/dbexport-- they suggest
> > onunload/onload or some other alternative, instead. (Of course,
> > onunload/onload can't be used to migrate between versions, so that is a
> > non-solution, also).

>
> Depending upon IDS release, onload ususally has bugs or "features" that

make

> using it somewhere between "difficult and time-consuming" and

"impossible".

>

> > I got the impression that IBM may not be willing to fix

dbexport/dbimport

> ...
>
> The attitude seems to be that, if you have a reasonable workaround (which,
> correct me if I'm wrong, they have provided) they'll probably not fix it

in

> current releases.
>

> > I repeat my total surprise and disatisfaction to learn that such

> fundamental

> > utilities as dbimport/dbexport are broken, have been known to be broken

> for

> > years, and remain unfixed.

>
> I echo that. Well, maybe not the surprise, but certainly the
> dissatisfaction ... :-)
>
>

[Fred Prose]

We have run into similar problems, and not just with this release. In
our case we had Stored Procedures that would not export our and import
back in due to special characters.

We learned a trick that can save you hours of watching a dbimport work
only to see it fail like you did.

We run a 'test fire'. Before you do the real import you do a simple
create database (in the dbspace you planned on putting it) and then
run the .SQL file as a script through dbaccess. This process takes
seconds or a few minutes and will check everything for syntax.

[Hari Gupta]

Sound an excellent trick. I will certainly try this.

Hari
"Fred Prose" <fprose@hotmail.com> wrote in message
news:195a4770.0310081229.153fe1e8@posting.google.c om...

> We have run into similar problems, and not just with this release. In
> our case we had Stored Procedures that would not export our and import
> back in due to special characters.
>
> We learned a trick that can save you hours of watching a dbimport work
> only to see it fail like you did.
>
> We run a 'test fire'. Before you do the real import you do a simple
> create database (in the dbspace you planned on putting it) and then
> run the .SQL file as a script through dbaccess. This process takes
> seconds or a few minutes and will check everything for syntax.