RE: Problem with SQL/Logical file at V7R1 -- MIDRANGE-L

If it was me, I'd replace the logical with a view and an index. This is one of those weird logicals that embody both a view and a logical. IBM tells us the new query engine will now handle this in 7.1, but I keep hearing of instances like this where it doesn't.

Create index F0005I1 on F0005 (DRSY, DRRT, DRKY);

Create view F0005L1 as
Select * from F0005 where DRSY = '00' and DRRT in ('01','02','03','04','06')
Union all
Select * from F0005 where DRSY = '09' and DRRT in ('22', '23', '24', '25', 'LT','11');

There's also an implicit "Order By" when using the logical from RPG. ORDER BY always comes only from the "outermost" query using the view, so you might need to add another level to order the selected records. Otherwise, using the view from RPG should be just the same as using the logical requiring no changes to the RPG at all.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of ALopez@xxxxxxxxxx
Sent: Monday, November 25, 2013 7:32 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Problem with SQL/Logical file at V7R1

Apologies at the outset for a verrrry long message with way too much
detail for a Monday morning.

We have a logical (PRODCTL/F0005L1) defined via DDS as:
A R F0005 PFILE(F0005)
A K DRSY
A K DRRT
A K DRKY
A*
A S DRSY CMP(EQ '00')
A DRRT CMP(EQ '01')
A S DRSY CMP(EQ '00')
A DRRT CMP(EQ '02')
A S DRSY CMP(EQ '00')
A DRRT CMP(EQ '03')
A S DRSY CMP(EQ '00')
A DRRT CMP(EQ '04')
A S DRSY CMP(EQ '00')
A DRRT CMP(EQ '05')
A S DRSY CMP(EQ '00')
A DRRT CMP(EQ '06')
A S DRSY CMP(EQ '09')
A DRRT CMP(EQ '22')
A S DRSY CMP(EQ '09')
A DRRT CMP(EQ '23')
A S DRSY CMP(EQ '09')
A DRRT CMP(EQ '24')
A S DRSY CMP(EQ '09')
A DRRT CMP(EQ '25')
A S DRSY CMP(EQ '09')
A DRRT CMP(EQ 'LT')
A S DRSY CMP(EQ '09')
A DRRT CMP(EQ '11')

Since moving to V7R1 we have found that our Cognos server retrieves no
records with an SQL request of:
Select * from prodctl/f0005l1

Other methods of access (RPG, DBU, HelpSystems' SEQUEL) will retrieve all
386 records we expect to be there. DSPFD indicates that there are 386
records in the file. The same SQL request above works in SEQUEL
(retrieves 386 records), but not in STRSQL. IBM had me a debug joblog and
it came back with an indication that PRODCTL/F0005L1 was not used because
of reason 11 'select/omit not compatible with query'.

Our PTFs are a bit of a mess because of problems we have with the latest
Java PTFs that seemed to be bundled with everything, but what's currently
installed is:
SF99710 13037 Installed
SF99709 96 Installed
SF99708 29 Installed
SF99707 6 Installed
SF99706 5 Installed
SF99705 6 Installed
SF99701 25 Installed
SF99627 10 Installed
SF99572 12 Installed
SF99381 6 Installed
SF99380 9 Installed
SF99368 22 Installed
SF99367 7 Installed
SF99366 9 Installed
SF99364 9 Installed
SF99363 13 Installed
SF99362 28 Installed
SF99145 5 Installed

Has anybody run into this? We don't seem to have much traction on our
open ticket. Maybe I missed it, but I don't think we had any indication
that logical files with omit/selects would no longer be supported when
accessed via SQL. I understand that more SQL requests are being routed
through the new(er) query engine, but is the expected result?

Andrew  Lopez
Systems Analyst

Phone:   803-714-2037

Email: ALopez@xxxxxxxxxx
Please consider the environment before printing this e-mail.

This message and any attachments should be treated as proprietary to the
sender
and confidential to the identified recipients and should not be disclosed to or
used by anyone other than the intended recipient unless pre arranged with
the
sender. If you are not an addressee of this communication, have received
this
e-mail by transmission error of the sender, recipient or due to another
originator by an error in transmission, you are hereby notified that any
disclosure, copying, use, distribution, or taking of any action in reliance on
the contents of this information is prohibited. In any such event, please
notify the sender immediately by contacting Spirax Sarco Inc., 803 714 2000 or
reply to this e-mail and then delete it from your system. Spirax Sarco Inc.
accepts no responsibility for software viruses and all recipients should check
for viruses before opening any attachments.