Re: RUNSQLSTM not finding correct Access Path

What does Visual Explain show?

-----Original Message-----
From: Vinay Gavankar <vinaygav@xxxxxxxxx>
Sender: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>Date: Sat, 3 Jan 2015 17:45:30
To: Midrange Systems Technical Discussion<midrange-l@xxxxxxxxxxxx>
Reply-To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RUNSQLSTM not finding correct Access Path

Hi,

We have a physical file (say pffile) which has over 280 million records (no
key in physical). The file has 2 logical files, one with Key of Ref Number
REFNO and Seq Number SEQNO (lf1file) & another with key of Ref Number & Seq
number descending (lf2file).

The file is in use by numerous jobs (using logicals).

We want to delete records from the file for particular Reference numbers.

We tried to do it thru creating a sql script and running it via a Submitted
Job using RUNSQLSTM.

the sql statement is:

delete from lib1/pffile where refno = nnnnnnnn and SEQNO > 0 and SEQNO <
99999

where nnnnnnnn is the refno we are trying to delete
REFNO is 15,0 packed and SEQNO is a 5,0 packed. We want to leave SEQNO = 0
and SEQNO = 99999 in the file after or SQL is done.

Even as the sql is running, one of the jobs is trying to write records to
the file for the above REFNO, by getting the highest SEQNO for the REFNO,
adding 1 (which makes SEQNO 0) and tries to write. An exception happens,
which is trapped, and the program continues.

Killing the jobs which is doing this is not an option (at least not a
preferred option).

We want to run this for multiple REFNOs.

For one value of REFNO, the sql completed in a few seconds, even before we
could get to see how the submitted job was doing. So the assumption is that
it used the access path of the logical and deleted the required rows
(99,998 rows).

But for other values of REFNO, the sql job starts searching the full table,
ignoring the access path. We can see the open files for the job going thru
the physical file, starting with the highest RRN available and going
backwards (at least that is how it seems from the RRN of the open files).


We tried different things like using "delete from lib1/lf1file...." but
still the same thing happens.

Any ideas on why this could be happening, and how to force the access path
to be used?

Vinay