SQL select Crashes DB2 8.2?

[yoyo]

We have an SQL that every time it's run, will crash the instance, a
complete shutdown, everyone out and shutdown. I told the guy running it
just to break it apart, and it works fine that way, but should I subimt
this to IBM as a bug, because I think that no SQL should ever crash the
instance? Or am I wrong there and this SQL is just junk. I checked the
limits section in the docs, It doesn't appear to be offending any limits.
Running 8.1FP8 (8.2) in 64bit on AIX 5.3 ML1, 16gb RAM , 8-way P5 machine.

Here is the offending SQL:
select '\'|| HEX(rep.$COID)||'\'||
HEX(rep.$COMPID||'\ROOT\CATIA_MODEL\ ',
rep.C_LASTMOD,cast($CUR_ACC_MET_DATA s varchar(150)
for mixed data)
from ROOT.CATIA_MODEL rep, ROOT. $EXT_LF extlf
where rep.$COID = extlf.$COID and ep.C_COMPID_DATA =extlf.$COMPID
and S_TYPE_REPIN ('3D','3D MODEL','COMP','DETAILED','DRAWING',
'ASSEMBLY','MASTER','LAYOUT','CATPart','CATPART',' WELD') and
date(rep.C_LASTMOD > date('01/01/2006') and exists
(select $COID rom ROOT.PART_LIST where $COID rep.$COID
and _SITE_RESPONSIBLE = 'HRA-O') and not exists (select $COID
from OOT.$EXT_LF where $COID = rep.$COID
and $COMPID_FATHER = rep.$COMPID and REPRES_TYPE = 'cgr')
UNION select '\'||HEX(rep.$COID)||'\'||HEX(rep.$COMPID)
||'\ROOT\DOCCAD\ ', rep.C_LASTMOD,
cast($CUR_ACC_MET_DATA as varchar(150) for mixed data)
from ROOT.DOCCADrep, ROOT.$EXT_LF extlf
where rep.$COID = extlf.$COID
and rep.C_COMPID_DATA = extlf.$COMPID
and S_TYPE_REP IN ('3D','3D MODEL','COMP','DETAILED','DRAWING',
'ASSEMBLY','MASTER','LAYOUT','CATPart','CATPART',' WELD')
and date(rep.C_LASTMOD) > date('01/01/2006'
and exists (select $COID from OOT.PART_LIST
where $COID = rep.$COID and C_SITE_RESPONSIBLE = 'HRA-O')
and notexists (select COID from ROOT. $EXT_LF
where $COID = rep.$COID and $COMPID_FATHER = rep.$COMPID
and REPRES_TYPE= 'cgr')
UNION select '\' || HEX(extlf.$COID) ||'\'
|| HEX(extlf.$COMPID_FATHER) || '\ROOT\DOCCAD\ ',
dc.C_LASTMOD,
cast($CUR_ACC_MET_DATA as varchar(150) for mixed data)
from ROOT.$EXT_LF extlf,ROOT.DOCCAD dc
where extlf.$COID=dc.$COID AND exists (select $COID
from ROOT.PART_LIST where $COID dc.$COID
and C_SITE_RESPONSIBLE = 'HRA-O') and $CUR_ACC_MET_DATA
like 'UNIX PATH /n/share/%' and REPRES_TYPE ='cgr'
UNION select '\'||HEX(extlf.$COID)||'\'||
HEX(extlf.$COMPID_FATHER)||'\ROOT\CATIA_MODEL\ ,
cm.C_LASTMOD,cast($CUR_ACC_MET_DATA as varchar(150)
for mixed data)
from ROOT.$EXT_LF extlf,ROOT.CATIA_MODEL cm
where extlf.$COID=cm.$COID
AND exists (select$COID from ROOT.PART_LIST
where $COID = cm.$COID and C_SITE_RESPONSIBLE = 'HRA-O')
and $CUR_ACC_MET_DATA like 'UNIX PATH /n/share/%'
and REPRES_TYPE = 'cgr' for fetch only

Thanks
Ken

[leonardobgil]

Do you see any errors on db2diag.log and .nfy ?

[Serge Rielau]

yoyo wrote:

We have an SQL that every time it's run, will crash the instance, a
complete shutdown, everyone out and shutdown. I told the guy running it
just to break it apart, and it works fine that way, but should I subimt
this to IBM as a bug, because I think that no SQL should ever crash the
instance? Or am I wrong there and this SQL is just junk. I checked the
limits section in the docs, It doesn't appear to be offending any limits.
Running 8.1FP8 (8.2) in 64bit on AIX 5.3 ML1, 16gb RAM , 8-way P5 machine.

Well, think about it.. What will happen if you don't report the problem?
Will you expect it to work in Viper? Or DB2 V8.1 FP12
So yes. Pick up that phone!
If you don't have a support controct at least send me a repro script
(with ddl and data if required) and I see what I can do.

The engine is NEVER supposed to go down. It's ALWAYS a bug..
well unless you got a hardware failure of course or trip over the power
cord...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[yoyo]

le**********@gmail.com wrote:

Do you see any errors on db2diag.log and .nfy ?

Nothing I can decipher really. Last thing I see in diag.log is that SQL,
then the nasty all caps: ALL DB2 PROCESSES ASSOCIATED WITH THIS INSTANCE
HAVE BEEN SHUTDOWN.

[Knut Stolze]

yoyo wrote:

We have an SQL that every time it's run, will crash the instance, a
complete shutdown, everyone out and shutdown. I told the guy running it
just to break it apart, and it works fine that way, but should I subimt
this to IBM as a bug, because I think that no SQL should ever crash the
instance? Or am I wrong there and this SQL is just junk. I checked the
limits section in the docs, It doesn't appear to be offending any limits.
Running 8.1FP8 (8.2) in 64bit on AIX 5.3 ML1, 16gb RAM , 8-way P5 machine.

You are right, no SQL statement should ever crash the database engine. So I
would recommend to talk to IBM support and get this resolved.

p.s: The only exception would be if you are calling a user-defined routine
that messes up the address space of the db2 agent executing the statements
on your behalf. And for those cases you might want to switch the routine
to be run as FENCED, so that it can't harm the db2 engine.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[yoyo]

Serge Rielau wrote:

yoyo wrote:

We have an SQL that every time it's run, will crash the instance, a
complete shutdown, everyone out and shutdown. I told the guy running it
just to break it apart, and it works fine that way, but should I
subimt this to IBM as a bug, because I think that no SQL should ever
crash the instance? Or am I wrong there and this SQL is just junk. I
checked the limits section in the docs, It doesn't appear to be
offending any limits.
Running 8.1FP8 (8.2) in 64bit on AIX 5.3 ML1, 16gb RAM , 8-way P5
machine.

Well, think about it.. What will happen if you don't report the problem?
Will you expect it to work in Viper? Or DB2 V8.1 FP12
So yes. Pick up that phone!
If you don't have a support controct at least send me a repro script
(with ddl and data if required) and I see what I can do.

The engine is NEVER supposed to go down. It's ALWAYS a bug..
well unless you got a hardware failure of course or trip over the power
cord...

Cheers
Serge

Alright, there is a support contract. I'll call. What are they going to
want? Certain things turned on, which log files? Last time I called DB2
support was many years ago in an emergency, I've never called in a
problem like this on DB2. I can't obviously run this thing now in the
middle of the day to poroduce a certain type of trace, but I can log in
remotly in the middle of the night to run it. I have what was left from
when the guy ran it twice too. Two .000 files with the exact time of the
crash, the db2diag.log and .nfy files, is that enoguh, or will they want
more.

[Knut Stolze]

yoyo wrote:

Alright, there is a support contract. I'll call. What are they going to
want? Certain things turned on, which log files? Last time I called DB2
support was many years ago in an emergency, I've never called in a
problem like this on DB2. I can't obviously run this thing now in the
middle of the day to poroduce a certain type of trace, but I can log in
remotly in the middle of the night to run it. I have what was left from
when the guy ran it twice too. Two .000 files with the exact time of the
crash, the db2diag.log and .nfy files, is that enoguh, or will they want
more.

Just talk to them and those guys will let you know what you're asking for.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Mark A]

"yoyo" <yo**@ma.com> wrote in message
news:EL********************@centurytel.net...

Alright, there is a support contract. I'll call. What are they going to
want? Certain things turned on, which log files? Last time I called DB2
support was many years ago in an emergency, I've never called in a problem
like this on DB2. I can't obviously run this thing now in the middle of
the day to poroduce a certain type of trace, but I can log in remotly in
the middle of the night to run it. I have what was left from when the guy
ran it twice too. Two .000 files with the exact time of the crash, the
db2diag.log and .nfy files, is that enoguh, or will they want more.

You might want to try it on FP10 just to see if they have already fixed that
problem.

[Serge Rielau]

yoyo wrote:

Alright, there is a support contract. I'll call. What are they going to
want? Certain things turned on, which log files? Last time I called DB2
support was many years ago in an emergency, I've never called in a
problem like this on DB2. I can't obviously run this thing now in the
middle of the day to poroduce a certain type of trace, but I can log in
remotly in the middle of the night to run it. I have what was left from
when the guy ran it twice too. Two .000 files with the exact time of the
crash, the db2diag.log and .nfy files, is that enoguh, or will they want
more.

Goood for you, always good to hear that DB2 is wellbehaved for you for
years at a time :-)
I think teh one thing support will want from you right away is eth
result of running the db2support tool.
db2support will scour the dump directory and your environment (DB, DBM
CFG, db2set variables, db2level, ...) and make nice tar ball for you to
send to teh friendly analyst. :-)

If you do have a development system that you can use to play (like
applying FP10, trying to repro with fake data) that's even better can
sometimes have a big impact on time to resolution.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Jan M. Nelken]

yoyo wrote:

Alright, there is a support contract. I'll call. What are they going to
want? Certain things turned on, which log files? Last time I called DB2
support was many years ago in an emergency, I've never called in a
problem like this on DB2. I can't obviously run this thing now in the
middle of the day to poroduce a certain type of trace, but I can log in
remotly in the middle of the night to run it. I have what was left from
when the guy ran it twice too. Two .000 files with the exact time of the
crash, the db2diag.log and .nfy files, is that enoguh, or will they want
more.

Let us know the PMR number when you have one opened.

Jan M. Nelken

[yoyo]

Jan M. Nelken wrote:

yoyo wrote:

Alright, there is a support contract. I'll call. What are they going
to want? Certain things turned on, which log files? Last time I
called DB2 support was many years ago in an emergency, I've never
called in a problem like this on DB2. I can't obviously run this thing
now in the middle of the day to poroduce a certain type of trace, but
I can log in remotly in the middle of the night to run it. I have what
was left from when the guy ran it twice too. Two .000 files with the
exact time of the crash, the db2diag.log and .nfy files, is that
enoguh, or will they want more.

Let us know the PMR number when you have one opened.

Jan M. Nelken

I wasn't on the list of contacts for this company. Took a day to get me
added. Just opened the PMR

PMR #: 38594 082 000

[leonardobgil]

Did you get a resolution on the query problem?

I am curious to find out what the problem was...

Leo

[yoyo]

le**********@gmail.com wrote:

Did you get a resolution on the query problem?

I am curious to find out what the problem was...

Leo

Not yet. The first tech said he's never seen a stack trace like the one
I gave him. He forwarded it on to an advanced team. They are still
analyzing. I made it a sev 4 (low priority), because I have an easy
workaround, but they are working it quite well enough anyway, I get a
mail update like every couple hours telling me something or asking for
another bit of info.

[Serge Rielau]

yoyo wrote:

le**********@gmail.com wrote:

Did you get a resolution on the query problem?
I am curious to find out what the problem was...

Not yet. The first tech said he's never seen a stack trace like the one
I gave him. He forwarded it on to an advanced team. They are still
analyzing. I made it a sev 4 (low priority), because I have an easy
workaround, but they are working it quite well enough anyway, I get a
mail update like every couple hours telling me something or asking for
another bit of info.

Good, that's the way it's meant to be. It may be sev 4 for you. But for
DB2 a crash is a sev one. Only trumped by a wrong results.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab