Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Operating system defect

Operating system defect

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:

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.


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)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.