Access paths using dynamic SQL vs. RUNSQLSTM

[Guest.Visitor]

PEZ wrote: > I expected it to use a logical called MBSHPL01 which is keyed by > MBNMNO. > Same for the other ITEML01 which is keyed by DMNMNO. Sometimes it's hard to figure out why it picked what it picked. Have you tried specifying those logicals in the statements instead of the physical files? Bill

[PEZ]

Access paths using dynamic SQL vs. RUNSQLSTM

Sorry for the long post but I assume more info is better than less. I have a dynamic SQL program where I am using two NOT IN subselects but SQL can't find the appropriate logical to use for the subselects. Basically here is the SQL I am trying to run: INSERT INTO SPCLIB/DIRTEST (SELECT DISTINCT BAnmno FROM GROUP,INDIVID, accum, intrst where (some simple where stuff to link the files) AND inNMNO NOT IN (SELECT MBNMNO FROM MBRSHIP WHERE (mbpspr = 'C98AKPK')) AND BANMNO NOT IN (SELECT DMNMNO FROM ITEMDTL WHERE (DMTYP1 = 'SBS' AND DMPROD = 'SRRA')) INNMNO, BANMNO, MBNMNO, and DMNMNO are all defined as 9,0. When the program ran over 8 hours (backups killed it), I ran it again using STRDBG to find the problem. Here are the messages I got for one of the files (the other file had the same message): All access paths were considered for file MBRSHIP. Additional access path reason codes were used. Arrival sequence access was used for file MBRSHIP. I expected it to use a logical called MBSHPL01 which is keyed by MBNMNO. Same for the other ITEML01 which is keyed by DMNMNO. I F1 the line with "All access paths..." and it stated that most logicals including the ones I thought it should use stated that they were not used for reason code 17. Which is (message was in the "Additional access path.." line): 17 - The left-most key of the access path did not match any fields specified for the selection criteria. Therefore, key row positioning could not be performed, making the cost to use this access path higher than the cost associated with the chosen access method. Does this have to do with not using MBNMNO in the subselect where clause??? There are no logicals based on the fields in my where clause. I assumed SQL would try to link the files by ID number (the nmno fields) then look at the where clause. Next I ran debug in my program and stopped it right before the execute statement and copied my variable that contained the SQL statement into a member and ran RUNSQLSTM on it and it worked fine and ran in 15 minutes. Here are the messages I got: File GROUP processed in join position 1. File ITEMDTL processed in join position 2. File INTRST processed in join position 3. File INDIVID processed in join position 4. File MBRSHIP processed in join position 5. File ACCUM processed in join position 6. When I F1 the "All access paths..." it used the logicals I thought it should. Another note, the other logicals over the file had a reason code of 06 (fields don't match) which is what I would expect but when the program ran the SQL statement all of the other logicals had a reason code of 17 as well. Any suggestions? I tried execuate immediately and prepare with execute but it changed nothing. Why would it treat access paths differently if using dynamic SQL versus non dynamic?

[PEZ]

Access paths using dynamic SQL vs. RUNSQLSTM

I changed the selection to add the key fields (innmno=banmno) in the where and it used the logicals I hoped it would. Not sure why but I am going with it.