Re: Searching across three files

Do you already have an index by part number on each file in each of it's
libraries? ie
VIEWPRT1, with VIEWPRTL1 by part number
VIEWPRT2, with VIEWPRTL2 by part number
VIEWPRT3, with VIEWPRTL3 by part number
then it will use them. See the following:

strsql

CREATE TABLE VIEWPRT1(PARTNO INT as identity

(start with 1),
DUH CHAR (1 ) NOT NULL WITH
DEFAULT)
Table VIEWPRT1 created in ROB.

INSERT INTO ROB/VIEWPRT1 (DUH) VALUES('a')

1 rows inserted in VIEWPRT1 in ROB.
... repeated a lot ...

CREATE TABLE VIEWPRT2(PARTNO INT as identity

(start with 2001),
DUH CHAR (1 ) NOT NULL WITH
DEFAULT)
Table VIEWPRT2 created in ROB.

CREATE TABLE VIEWPRT3(PARTNO INT as identity

(start with 3001),
DUH CHAR (1 ) NOT NULL WITH
DEFAULT)
Table VIEWPRT3 created in ROB.

INSERT INTO ROB/VIEWPRT2 (DUH) VALUES('c')

1 rows inserted in VIEWPRT2 in ROB.
... repeated a lot ...

INSERT INTO ROB/VIEWPRT3 (DUH) VALUES('d')

1 rows inserted in VIEWPRT3 in ROB.
... repeated a lot ...

CREATE INDEX ROB/viewprtl1 ON ROB/VIEWPRT1 (PARTNO ASC)

Index VIEWPRTL1 created in ROB on table VIEWPRT1 in ROB.

CREATE INDEX ROB/viewprtl2 ON ROB/VIEWPRT2 (PARTNO ASC)

Index VIEWPRTL2 created in ROB on table VIEWPRT2 in ROB.

CREATE INDEX ROB/viewprtl3 ON ROB/VIEWPRT3 (PARTNO ASC)

Index VIEWPRTL3 created in ROB on table VIEWPRT3 in ROB.

create view rob/viewprtc as

select * from rob/viewprt1 union all
select * from rob/viewprt2 union all
select * from rob/viewprt3
View VIEWPRTC created in ROB.

<F3> to exit sql

STRDBG UPDPROD(*YES)

strsql

select * from rob/viewprtc

order by partno
SELECT statement run complete.

<F3> to exit sql

DSPJOBLOG

...
All access paths were considered for file VIEWPRT1.
All access paths were considered for file VIEWPRT2.
All access paths were considered for file VIEWPRT3.
Access path of file VIEWPRTL1 was used by query.
Access path of file VIEWPRTL2 was used by query.
Access path of file VIEWPRTL3 was used by query.
...


Rob Berendt