Re: RUNSQLSTM not finding correct Access Path

[Kirk Goins <kirkgoins@xxxxxxxxx>]

What does the library list look like for the job?

On Sat, Jan 3, 2015 at 2:45 PM, Vinay Gavankar <vinaygav@xxxxxxxxx> wrote:

> 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
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.