Joining 3 Files

[Guest.Visitor]

How about something like this?

---

Code

[Libby Mathai]

Joining 3 Files

I have the following SQL running for almost 30 minutes. One of the files have 3.5 million records. How does indexing work in AS/400? I tried explicity including key fields in the where clause with no effect. Any suggestions will be appreciated.

---

Code

[K.Forsythe]

Joining 3 Files

select STNUM, STNAME, SPPHONE#, SMMODEM# from STOREMAST left join STOREMODEM on SPNUM=SMNUM left join STOREPHONE on STNUM=SPNUM

[dacust]

Joining 3 Files

I'd check to make sure the following access paths exist: DNJYNB by OECSSL
GHJYNB by CSCSP#
DNJYNB by OECSSL
GDJYNB by CSCPRO
DNJYNB by OECSSL
CLJYNB by CPWPCM

Also, for the fields in the WHERE clause, an index and/or EVI on the following: DNCOMP by OECSSL
CLCMNS by CPWPCM
GDNLNB by CSCPRO

Without knowing what your data distribution, I don't know which of the above would be most likely to give you a performance boost. Given the way index maintenance works now, I'd try all of them, including boat an index AND an EVI for the fields in the where clause. -dan

[Guest.Visitor]

Joining 3 Files

What is the best approach to the following simple example? I have 3 separate databases: STOREMAST: Contains the fields STNUM, STNAME STOREPHONE: Contains the fields SPNUM, SPPHONE# STOREMODEM: Containst the fields SMNUM, SMMODEM# The 3 files are linkable via the first field. In a large majority of STOREMAST records, both of the other 2 files will contain a matching record. In rare cases, only one of the files will have a matching record. There are no STOREMAST records that have no matching records on the other 2 files. I came up with the below SELECT statement to join the 3 files together, so that the select statement will obtain ALL the STOREMAST's joining to the other 2 files. If one file was missing a matching record, it's equivelent field would be blank. Is there an easier way to accomplish this? I have a feeling I might be missing a function or a statement mechanic to make this a bit easier. select STNUM, STNAME, SPPHONE#, SMMODEM# from STOREMAST join (select SPNUM, SPPHONE#, SMMODEM# from STOREPHONE left join STOREMODEM on SPNUM=SMNUM union select SMNUM as SPNUM, ' ' as SPPHONE#, SMMODEM# from STOREMODEM where not exists (select * from STOREPHONE where SPNUM=SMNUM)) as JOINOTHER2 on STNUM=SPNUM