strange DB/2 OS/390 problem 
Author Message
 strange DB/2 OS/390 problem

Hello, All:

I have a following strange problem with DB/2 on OS/390 (v5.1). When I write
the following select:

SELECT A.PLACID, ZALET, ZAMES            -- DB2PRD.SKTPFST
     , #IFSTK                                                      --
DB2PRD.SKTPFST
     , GC                                                             --
DB2PRD.SKTPLAC
FROM DB2PRD.SKTPFST A
   , DB2PRD.SKTPLAC B
WHERE ZALET='00' AND ZAMES='03' AND #IFSTK='81' AND GC='10'
AND A.PLACID=B.PLACID;

I get an error -901 in QMF (Spufi say abend 0C4). But, if I write this
select:

SELECT A.PLACID, ZALET, ZAMES         -- DB2PRD.SKTPFST
     , #IFSTK                                                   --
DB2PRD.SKTPFST
     , GC                                                          --
DB2PRD.SKTPLAC
FROM DB2PRD.SKTPFST A
   , DB2PRD.SKTPLAC B
WHERE ZALET='00' AND ZAMES='03' AND #IFSTK='81' AND GC='01'
AND A.PLACID=B.PLACID

it works fine. Note that the only change is the WHERE on GC column. I
already did REORG, RUNSTATS and RECOVER INDEX on a teble DB2PRD.SKTPFST, but
no effect. I replaced DB2PRD.SKTPLAC table with another, which can also be
joined with DB2PRD.SKTPFST on the same column, but the results are the same.
If I drop out the join statement, select works fine.  This smells to me like
a DB/2 error and if anyone had similar problems, I would like to know.

Best regards, Srecko.



Sat, 26 Oct 2002 03:00:00 GMT
 strange DB/2 OS/390 problem

-901 are bad on DB2 for Workstations and I strongly suspect they are
equally evil
on the OS/390 platform. 901s get raised if DB2 finds itself in a state
it thought it should never be. So it decided to cancel a statement
before anything gets corrupted.

Bottomline: Call service

Cheers
Serge



Sat, 26 Oct 2002 03:00:00 GMT
 strange DB/2 OS/390 problem
The comment cards are out pretty far.  Doesn't qmf cutoff statments as column 72
?
I counted that the 3rd comment is in col 70 or 71.  QMF maybe seeing a sing dash
and not two and thus think it's suppose to be doing an expression of "gc -
db2prd.sktplac".
Quote:

> Hello, All:

> I have a following strange problem with DB/2 on OS/390 (v5.1). When I write
> the following select:

> SELECT A.PLACID, ZALET, ZAMES            -- DB2PRD.SKTPFST
>      , #IFSTK                                                      --
> DB2PRD.SKTPFST
>      , GC                                                             --
> DB2PRD.SKTPLAC
> FROM DB2PRD.SKTPFST A
>    , DB2PRD.SKTPLAC B
> WHERE ZALET='00' AND ZAMES='03' AND #IFSTK='81' AND GC='10'
> AND A.PLACID=B.PLACID;

> I get an error -901 in QMF (Spufi say abend 0C4). But, if I write this
> select:

> SELECT A.PLACID, ZALET, ZAMES         -- DB2PRD.SKTPFST
>      , #IFSTK                                                   --
> DB2PRD.SKTPFST
>      , GC                                                          --
> DB2PRD.SKTPLAC
> FROM DB2PRD.SKTPFST A
>    , DB2PRD.SKTPLAC B
> WHERE ZALET='00' AND ZAMES='03' AND #IFSTK='81' AND GC='01'
> AND A.PLACID=B.PLACID

> it works fine. Note that the only change is the WHERE on GC column. I
> already did REORG, RUNSTATS and RECOVER INDEX on a teble DB2PRD.SKTPFST, but
> no effect. I replaced DB2PRD.SKTPLAC table with another, which can also be
> joined with DB2PRD.SKTPFST on the same column, but the results are the same.
> If I drop out the join statement, select works fine.  This smells to me like
> a DB/2 error and if anyone had similar problems, I would like to know.

> Best regards, Srecko.



Sun, 27 Oct 2002 03:00:00 GMT
 strange DB/2 OS/390 problem
Sorry If I wrote this comment incorrectly. I just used CUT frm the 3270
emulator into the message, the comments were placed by QMF when I did the
draw command in QMF. If that was the reason, the second SELECT wouldn't work
as well, but it does. I did use some other methods for making select (trough
SPUFI, DB/2 client on WinNT), but all results are the same. The first select
works, the second doesn't.


Quote:
> The comment cards are out pretty far.  Doesn't qmf cutoff statments as
column 72
> ?
> I counted that the 3rd comment is in col 70 or 71.  QMF maybe seeing a
sing dash
> and not two and thus think it's suppose to be doing an expression of "gc -
> db2prd.sktplac".


> > Hello, All:

> > I have a following strange problem with DB/2 on OS/390 (v5.1). When I
write
> > the following select:

> > SELECT A.PLACID, ZALET, ZAMES            -- DB2PRD.SKTPFST
> >      , #IFSTK                                                      --
> > DB2PRD.SKTPFST
> >      , GC                                                             --
> > DB2PRD.SKTPLAC
> > FROM DB2PRD.SKTPFST A
> >    , DB2PRD.SKTPLAC B
> > WHERE ZALET='00' AND ZAMES='03' AND #IFSTK='81' AND GC='10'
> > AND A.PLACID=B.PLACID;

> > I get an error -901 in QMF (Spufi say abend 0C4). But, if I write this
> > select:

> > SELECT A.PLACID, ZALET, ZAMES         -- DB2PRD.SKTPFST
> >      , #IFSTK                                                   --
> > DB2PRD.SKTPFST
> >      , GC                                                          --
> > DB2PRD.SKTPLAC
> > FROM DB2PRD.SKTPFST A
> >    , DB2PRD.SKTPLAC B
> > WHERE ZALET='00' AND ZAMES='03' AND #IFSTK='81' AND GC='01'
> > AND A.PLACID=B.PLACID

> > it works fine. Note that the only change is the WHERE on GC column. I
> > already did REORG, RUNSTATS and RECOVER INDEX on a teble DB2PRD.SKTPFST,
but
> > no effect. I replaced DB2PRD.SKTPLAC table with another, which can also
be
> > joined with DB2PRD.SKTPFST on the same column, but the results are the
same.
> > If I drop out the join statement, select works fine.  This smells to me
like
> > a DB/2 error and if anyone had similar problems, I would like to know.

> > Best regards, Srecko.



Sun, 27 Oct 2002 03:00:00 GMT
 strange DB/2 OS/390 problem


Quote:
> -901 are bad on DB2 for Workstations and I strongly suspect they are
> equally evil
> on the OS/390 platform. 901s get raised if DB2 finds itself in a state
> it thought it should never be. So it decided to cancel a statement
> before anything gets corrupted.

> Bottomline: Call service

> Cheers
> Serge

I wonder if maybe an index and table are out of sync? Say you have an
index on GC, and the rid pointed to by the entry for '01' is OK but the
rid for entry '10' is invalid. We have had this happen occasionally in
OS/390, but I can't remember the error condition that gets raised.

Sent via Deja.com http://www.deja.com/
Before you buy.



Sun, 27 Oct 2002 03:00:00 GMT
 strange DB/2 OS/390 problem
submitting the identical query from different methods should/will get the same
results.  After all the same engine / black box is processing it.  You say that
you rebuilt the index.  Is their only 1 index on the table ? Are you able to do
an explain ?  Can you show the results between the one that works and one that
doesn't ? is one for example doing a ts scan and the other index usage.  try
something stupid like "optomize for 1 row".  this generally turns off all forms
of parallelism. if run in dsntep2 does it give any reason code or just an oc1 ?
can you paste the sqlcode block with the next 4/5/6 lines of the one that fails
from the dsntep2 --- or it just dies ?
Quote:

> Sorry If I wrote this comment incorrectly. I just used CUT frm the 3270
> emulator into the message, the comments were placed by QMF when I did the
> draw command in QMF. If that was the reason, the second SELECT wouldn't work
> as well, but it does. I did use some other methods for making select (trough
> SPUFI, DB/2 client on WinNT), but all results are the same. The first select
> works, the second doesn't.



> > The comment cards are out pretty far.  Doesn't qmf cutoff statments as
> column 72
> > ?
> > I counted that the 3rd comment is in col 70 or 71.  QMF maybe seeing a
> sing dash
> > and not two and thus think it's suppose to be doing an expression of "gc -
> > db2prd.sktplac".


> > > Hello, All:

> > > I have a following strange problem with DB/2 on OS/390 (v5.1). When I
> write
> > > the following select:

> > > SELECT A.PLACID, ZALET, ZAMES            -- DB2PRD.SKTPFST
> > >      , #IFSTK                                                      --
> > > DB2PRD.SKTPFST
> > >      , GC                                                             --
> > > DB2PRD.SKTPLAC
> > > FROM DB2PRD.SKTPFST A
> > >    , DB2PRD.SKTPLAC B
> > > WHERE ZALET='00' AND ZAMES='03' AND #IFSTK='81' AND GC='10'
> > > AND A.PLACID=B.PLACID;

> > > I get an error -901 in QMF (Spufi say abend 0C4). But, if I write this
> > > select:

> > > SELECT A.PLACID, ZALET, ZAMES         -- DB2PRD.SKTPFST
> > >      , #IFSTK                                                   --
> > > DB2PRD.SKTPFST
> > >      , GC                                                          --
> > > DB2PRD.SKTPLAC
> > > FROM DB2PRD.SKTPFST A
> > >    , DB2PRD.SKTPLAC B
> > > WHERE ZALET='00' AND ZAMES='03' AND #IFSTK='81' AND GC='01'
> > > AND A.PLACID=B.PLACID

> > > it works fine. Note that the only change is the WHERE on GC column. I
> > > already did REORG, RUNSTATS and RECOVER INDEX on a teble DB2PRD.SKTPFST,
> but
> > > no effect. I replaced DB2PRD.SKTPLAC table with another, which can also
> be
> > > joined with DB2PRD.SKTPFST on the same column, but the results are the
> same.
> > > If I drop out the join statement, select works fine.  This smells to me
> like
> > > a DB/2 error and if anyone had similar problems, I would like to know.

> > > Best regards, Srecko.



Sun, 27 Oct 2002 03:00:00 GMT
 strange DB/2 OS/390 problem
Hello, All:

I have an update to my problem (it was also submited to IBM for study). I
managed to make a workaround by coding the following select:

SELECT A.PLACID, ZALET, ZAMES            -- DB2PRD.SKTPFST
      , #IFSTK                                                  --
DB2PRD.SKTPFST
      , GC                               --DB2PRD.SKTPLAC
FROM DB2PRD.SKTPFST A
          , DB2PRD.SKTPLAC B
WHERE ZALET='00' AND ZAMES='03' AND #IFSTK BETWEEN '81' AND '81'
  AND GC='10' AND A.PLACID=B.PLACID;

Also, I tried to make a program with the same query which completes
with -901 in QMF, so that the program uses static SQL. The program works
perfectly. The problems are limited to dynamic SQL. As far as I discussed
this with IBM, there is an error in DB/2. I will see, what they will say. We
already made changes to our QMF queries, which use this table so the problem
is solved (kind of).

Best regards, Srecko.
P.S.:I left the original select wich doesn't work:

Quote:
> > > > SELECT A.PLACID, ZALET, ZAMES            -- DB2PRD.SKTPFST
> > > >      ,
       --
> > > > DB2PRD.SKTPFST
> > > >      ,
   --
> > > > DB2PRD.SKTPLAC
> > > > FROM DB2PRD.SKTPFST A
> > > >    , DB2PRD.SKTPLAC B
> > > > WHERE ZALET='00' AND ZAMES='03' AND #IFSTK='81' AND GC='10'
> > > > AND A.PLACID=B.PLACID;



Mon, 28 Oct 2002 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. VB6 -> OS/390 DB/2

2. OS/390, DB/2 and distributed processing

3. the so called UDB2 optmizer for z/OS and OS/390

4. z/OS OS/390 DB2 DBA Position available in Dallas, Texas

5. OS/400 and OS/390 for dummies?

6. Db2 V7 for z/OS and OS/390

7. DB2 v5.1.2 OS/390 Stored Proc problem

8. problem : accessing DB2 on os/390 with PHP on Linux

9. DB2 V6.1, OS/390 - LIKE predicat with CASE expression - problem

10. REORG Problems (DB2 V6.1 OS/390)

11. OS/390 DB2 Stored PROC problem

12. Problem with BLOBs (DB2 V6.1 on OS/390)


 
Powered by phpBB® Forum Software