This problem really has me puzzled. Basically, an SQL VIEW created with interactive SQL functions very differently to a VIEW created within an SQLRPGLE program using EXACTLY (i.e. copied and pasted) the same statement.My view joins a file to itself then to another file. I then do a CPYF to a keyed PF for maximum access speed of the 9 million records for the program processing the data.
When I do a CPYF from the view created with interactive SQL, the CPYF starts copying records after a couple of seconds and does the whole lot in 30 minutes.
When I do the CPYF from the view created within the SQLRPGLE program, it sits doing an index build for several hours. I've yet to see it finish as its killing our machine.
The SQL statement is as follows:
CREATE VIEW PGMRRC1/SALESORD (P_CONO71, P_ORDN71, P_ORDL71, P_DREF71, S_CONO71, S_SREF71, S_DREF71, S_CONO40, S_ORDN40, S_CUSN40, S_DSEQ40) AS SELECT P.CONO71, P.ORDN71, P.ORDL71, P.DREF71, S.CONO71, S.SREF71, S.DREF71, CONO40, ORDN40, CUSN40, DSEQ40 FROM INP71 P join inp71 S on S.cono71 = P.cono71 and P.dref71 = S.sref71 join oep40 on cono40 = S.cono71 and ordn40 = substr( S.dref71,1,7)
INP71 contains 19 million records and OPE40 contains 8 million. Both have suitable access paths for the VIEW to use. It makes no difference whether the SQLRPGLE program runs interactively or on batch and it makes no difference whether the CPY runs interactively or in batch. The CPYF from the view created by the program always tries to build an index and the CPYF using the view created with interactive SQL doesn't.
I have done a DSPFD of both views and they are identical apart from the file level ID. The format level IDs are the same.
The PF I'm copying to has exactly the same field layout and I've tried defining it without keys but this made no difference. Any suggestions or solutions will be greatly appreciated.
A couple of things. First, it appears that you have uncovered an operating system defect -- please apply the latest Database Group PTF and if the problem still exists, then report it to IBM Service.
Next, I'm not sure why you need to perform a CPYF for your program. The program processing the data should have similar performance accessing the view -- assuming the proper indexes are defined. Check this page out for more information.
MORE INFORMATION ON THIS TOPIC
Check out this Search400.com Featured Topic: Database issues resolved
Search400.com's targeted search engine: Get relevant information on DB2/400.
The Best Web Links: Tips, tutorials and more.
Dig Deeper on DB2 UDB (universal databases)
Related Q&A from Kent Milligan
Create a host variable of the where in statement on the fly with dynamic SQL. Continue Reading
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading