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
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
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
Comment