ca.portal.admin

OLQ Select index set

Discussion created by ca.portal.admin on Aug 20, 2010
Consider the situation:

Recordname : FSPR-VAFVAST
System-owned index : S-9999-783

Using OLQ I want to have the recordoccurrence of FSPR-VAFVAST where
(VAF-RNR = 210002735) Using GET statement it's quite simple:

GET FIR FSPR-VAFVAST WITHIN S-9999-783 WHERE SORT = 210002735


When I use the equivalent SQL statement :

SELECT * FROM FSPR-VAFVAST WHERE S-9999-783 AND VAF-RNR = 210002735

I get the errormessage:
OLQ 143005 08 Boolean conjunction (AND/OR/NOT) has non-bit operand.

Is the syntax incorrect or is it impossible to use index sets?


Schema definition of S-9999-783 is :

DIS SET S-9999-783
*+   ADD
*+   SET NAME IS S-9999-783
*+       ORDER IS SORTED
*+       MODE IS INDEX USING SIN-9999-783
*+       OWNER IS SYSTEM
*+           WITHIN AREA VAF-INDEX SUBAREA SYM-9999-783
*+       MEMBER IS FSPR-VAFVAST
*+           WITHIN AREA VAF SUBAREA SYMB-VAFVAST
*+           INDEX DBKEY POSITION IS 3
*+           MANDATORY AUTOMATIC
*+           KEY IS (
*+               VAF-RNR ASCENDING )
*+               DUPLICATES ARE LAST
*+               NATURAL SEQUENCE
*+               UNCOMPRESSED
*+       .

Kind regards,
Sibe Verbeek
ABN Amro bank
The Netherlands
0031-030-2260591





********************************* DISCLAIMER
*********************************

This message (including any attachments) is confidential and may be
privileged.
If you have received it by mistake please notify the sender by return
e-mail and delete this message from your system.
Any unauthorised use or dissemination of this message in whole or in
part is strictly prohibited.
Please note that e-mails are susceptible to change.
ABN AMRO Bank N.V, which has its seat at Amsterdam, the Netherlands, and
is registered in the Commercial Register under number 34334259,
including its group companies, shall not be liable for the improper or
incomplete transmission of the information contained in this
communication nor for any delay in its receipt or damage to your system.
ABN AMRO Bank N.V. (or its group companies) does not guarantee that the
integrity of this communication has been maintained nor that this
communication is free of viruses, interceptions or interference.
------------------------------------------------------------------------
-----
Dit bericht (inclusief de eventuele bijlagen) is vertrouwelijk.
Wanneer u dit bericht ten onrechte heeft ontvangen, dient u de afzender
hiervan onmiddellijk per kerende e-mail op de hoogte te brengen en dit
bericht te verwijderen uit uw systeem.
Elk onbevoegd gebruik en/of onbevoegde verspreiding van dit bericht is
niet toegestaan.
U wordt erop gewezen dat e-mail berichten aan wijziging onderhevig
kunnen zijn.
ABN AMRO Bank N.V., statutair gevestigd te Amsterdam en ingeschreven in
het handelsregister van de Kamer van Koophandel onder nummer 34334259,
en haar groepsmaatschappijen, is niet aansprakelijk voor de onjuiste en
onvolledige overdracht van de informatie in dit bericht noch voor
mogelijke vertraging in de ontvangst van dit bericht of schade aan uw
systeem als gevolg van dit bericht. ABN AMRO Bank N.V. (en haar
groepsmaatschappijen) staat er niet voor in dat de integriteit van dit
bericht behouden is gebleven noch dat dit bericht vrij is van virussen,
niet is onderschept of vatbaar is geweest voor tussenkomst (door
derden).
************************************************************************
*****

*****JuliusBaer Disclaimer***** This e-mail is for the intended recipient only and may contain confidential or privileged information. If you have received this e-mail by mistake, please contact us immediately and completely delete it (and any attachments) and do not forward it or inform any other person of its contents. If you send us messages by e-mail, we take this as your authorization to correspond with you by e-mail, however, we will not accept the electronic transmission of orders/instructions without a specific agreement being in place to govern the same. If you do not wish to receive any further e-mail correspondence please let us know. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, amended, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. Neither the Julius Baer Group nor the sender accept liability for any errors or omissions in the content of this message which arise as a result of its e-mail transmission. Please note that all e-mail communications to and from the Julius Baer Group may be monitored. This communication is for informational purposes only. It is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction.
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: OLQ Select index set
"If you have up to date statistics for your network DBs (UPDATE
STATISTICS FOR SCHEMA schema) and IDMS considers that the fastest way to
get that record or records is to go through S-999-783 to get it, then it
will. If IDMS considers that an area sweep will be faster then it will
do that.

Your statement in SQL is simply=20

SELECT * FROM FSPR-VAFVAST WHERE VAF-RNR =3D 210002735

You can fool IDMS if you think it is taking the wrong decision using the
OPTIMIZE FOR n ROWS clause. If you say OPTIMIZE FOR 1 ROWS then it will
almost certainly go for the index. If you say OPTIMIZE FOR 999999 ROWS
then it will probably go for the Area sweep.

You could also do an Explain on the SQL to see what IDMS is actually
going to do. The example to do it is in the DISTSRC library.

BROWSE CAI.GJR170.DISTSRC(EXPLDDL) Line 00000000 Col
001 080 .=20
Command =3D=3D=3D> =
Scroll
=3D=3D=3D> CSR .=20
********************************* Top of Data
********************************** .=20
------------------------------------------------------------------------
.=20
--CONTAINS PTF#r17 Support full outer join sort/merge  DEFJE01 06/08/21-
.=20
--CONTAINS PTF#IDMS/3170                               PERCA02 05/16/05-
.=20
--CONTAINS PTF#IDMS/3096                               RHORI01 12/10/04-
.=20
-- -
.=20
-- The following SQL definitions can be very helpful when using the -
.=20
-- EXPLAIN command. -
.=20


=20

Outcomes