Program Between Systems Runs Slow

[abercrombieande]

I have two iSeries sytsems A & B both on V7 and one SQLRPGLE program. The program is mostly SQL. When it is compiled on system A and runs on A it completes in a matter of minutes. When it is compiled on system A and runs on B it appears to be stuck on a SQL statement. It sits there for hours and never completes. The tables on System A & B are the same as wells as the indexes. To do some testing I have taken the SQL out of the program and created it as a procedure. The procedure runs in minutes on System A & B so I am guessing the problem is in the compile. Any ideas would be appreciated.

[MichaelCatalani]

Re: Program Between Systems Runs Slow

I have two iSeries sytsems A & B both on V7 and one SQLRPGLE program. The program is mostly SQL. When it is compiled on system A and runs on A it completes in a matter of minutes. When it is compiled on system A and runs on B it appears to be stuck on a SQL statement. It sits there for hours and never completes. The tables on System A & B are the same as wells as the indexes. To do some testing I have taken the SQL out of the program and created it as a procedure. The procedure runs in minutes on System A & B so I am guessing the problem is in the compile. Any ideas would be appreciated.

Here's a list off the top of my head:

1) PTF issue / difference between machines

2) If the SQL is an update, then record locking, especially if B is a production machine and A is a development machine

3) Indexes / Views are different or missing on B, therefore the SQL statement is having to do a lot more work on one machine versus the other.

[Mercury]

Re: Program Between Systems Runs Slow

... and maybe also

• not the same QAQQINI file.
• the sqlpkg is in conflict b/w A and B.

Recompile on B and give it a try.

[abercrombieande]

Re: Program Between Systems Runs Slow

Thanks for the replies everyone.

1. This is a difference in machines but all other programs produce close to the same results. The weird thing is that this program was running fine on System B (no code changes) and now does not complete.
2. There is a sql update in the code but the table is not used by any other processes.
3. All of the indexes are the same as well as table record sizes.
4. The QAQQINI files have the same number of records and are all set to *default
5. I looked for SQLPKG files but there aren't very many. I thought they were only for ODBC connections?

I really would like to compile the program on B but I do not have authority. The current architecture is source on A, compile on A, move compiled object to B.

[Mercury]

Re: Program Between Systems Runs Slow

4. The QAQQINI files have the same number of records and are all set to *default

Same number of records does not mean same value. For example the IGNORE_DERIVED_INDEX option with a parameter value of *YES is specified on A and *NO on B. In that case, a query will typically be reverted back to CQE from SQE whenever the sql optimizer processes table objects that have logical files with the SELECT/OMIT DDS keyword specified.

[abercrombieande]

Re: Program Between Systems Runs Slow

Same number of records does not mean same value. For example the IGNORE_DERIVED_INDEX option with a parameter value of *YES is specified on A and *NO on B. In that case, a query will typically be reverted back to CQE from SQE whenever the sql optimizer processes table objects that have logical files with the SELECT/OMIT DDS keyword specified.

They are all *DEFAULT on both systems

[K2r400]

Re: Program Between Systems Runs Slow

The access plan is stored into the *PGM.
Can you post the 2 spools after a PRTSQLINF on both systems

[abercrombieande]

Re: Program Between Systems Runs Slow

The access plan is stored into the *PGM.
Can you post the 2 spools after a PRTSQLINF on both systems

---A---- (Runs Ok)
5770SS1 V7R1M0 100423 Print SQL information Program PACOBJUSV/BI00009MOD 12/29/10 10:06:07 Page 001
Object name...............PACOBJUSV/BI00009MOD
Object type...............*PGM
CRTSQLRPGI
OBJ(PACOBJUSV/BI00009MOD)
SRCFILE(QTEMP/QSQLPRE)
SRCMBR(BI00009MOD)
COMMIT(*NONE)
OPTION(*SYS *NOEXTIND *PERIOD)
TGTRLS(V7R1M0)
ALWCPYDTA(*OPTIMIZE)
CLOSQLCSR(*ENDACTGRP)
RDB(*LOCAL)
DATFMT(*ISO)
DATSEP('-')
TIMFMT(*ISO)
TIMSEP('.')
DFTRDBCOL(*NONE)
DYNDFTCOL(*NO)
SQLPKG(PACOBJUSV/BI00009MOD)
MONITOR(*USER)
SQLCURRULE(*DB2)
ALWBLK(*ALLREAD)
DLYPRP(*NO)
DYNUSRPRF(*USER)
USRPRF(*NAMING)
SRTSEQ(*HEX)
LANGID(ENU)
RDBCNNMTH(*DUW)
TEXT('Stage Risk Auto Dim - Veh Mods RW')
SQLPATH(*LIBL)
DECRESULT(31 31 0)
DECFLTRND(*HALFEVEN)
CONACC(*DFT)
STATEMENT TEXT CCSID(37)
DECLARE MODCUR CURSOR FOR SELECT VRSPOLIDN , VRSTRM , VRSPOLVRS , VRSAJMSEQ ,
SEQNBR , MODTYP FROM QTEMP / TMPMODS FOR READ ONLY
SQL4013 Access plan has not been built.
CREATE TABLE QTEMP / TMPMODS AS ( SELECT STG . VRSCMPCDE , STG . VRSPOLIDN ,
STG . VRSTRM , STG . VRSPOLVRS , STG . VRSAJMSEQ , STG . SEQNBR , MD . MODTYP
FROM PLVRSMST V INNER JOIN PLPOLMST AS PMMOD ON V . CMPCDE = PMMOD . CMPCDE AND
V . POLIDN = PMMOD . POLIDN AND V . TRM = PMMOD . TRM AND V . POLVRS = PMMOD .
POLVRS AND V . AJMSEQ = PMMOD . AJMSEQ INNER JOIN RIVEHMDT AS MD ON PMMOD .
CMPNME = MD . CMPCDE AND PMMOD . IDNNBR = MD . POLIDN AND PMMOD . TRMNBR = MD .
TRM AND PMMOD . VRSNBR = MD . POLVRS AND PMMOD . AJMNBR = MD . AJMSEQ AND PMMOD
. SEQNBR1 = MD . RSKSEQNBR AND PMMOD . SEQNBR2 = MD . SEQNBR INNER JOIN
WSAURSKT AS STG ON V . POLIDN = STG . VRSPOLIDN AND V . TRM = STG . VRSTRM AND
V . POLVRS = STG . VRSPOLVRS AND V . AJMSEQ = STG . VRSAJMSEQ AND MD .
RSKSEQNBR = STG . SEQNBR WHERE PMMOD . TBLNME = 'RIVEHMDT' AND MD . MODTYP =
'PASSRESTR' OR MD . MODTYP = 'ANTITHEFT' OR MD . MODTYP = 'ABS' OR MD . MODTYP
= 'DRL' ) WITH DATA
Output generated by AHARTNE2
5770SS1 V7R1M0 100423 Print SQL information Program PACOBJUSV/BI00009MOD 12/29/10 10:06:07 Page 002
OPEN MODCUR
FETCH NEXT FROM MODCUR INTO : H ,: H ,: H ,: H ,: H ,: H
UPDATE WSAURSKT SET ABSIND = 'Y' WHERE VRSPOLIDN = : H AND VRSTRM = : H AND
VRSPOLVRS = : H AND VRSAJMSEQ = : H AND SEQNBR = : H
SQL5065 Access plan not found.
UPDATE WSAURSKT SET ATFIND = 'Y' WHERE VRSPOLIDN = : H AND VRSTRM = : H AND
VRSPOLVRS = : H AND VRSAJMSEQ = : H AND SEQNBR = : H
SQL5065 Access plan not found.
UPDATE WSAURSKT SET PASRSTIND = 'Y' WHERE VRSPOLIDN = : H AND VRSTRM = : H AND
VRSPOLVRS = : H AND VRSAJMSEQ = : H AND SEQNBR = : H
SQL5065 Access plan not found.
UPDATE WSAURSKT SET DRLIND = 'Y' WHERE VRSPOLIDN = : H AND VRSTRM = : H AND
VRSPOLVRS = : H AND VRSAJMSEQ = : H AND SEQNBR = : H
SQL5065 Access plan not found.
FETCH NEXT FROM MODCUR INTO : H ,: H ,: H ,: H ,: H ,: H
CLOSE MODCUR
INSERT INTO BIPCSCTT VALUES ( : H ,: H ,: H ,: H ,: H ,: H ,: H ,: H ,: H ,: H ,
: H )
SQL5065 Access plan not found.
UPDATE BIPCSCTT SET RUNTME = REPLACE ( CHAR ( TIME ( '00:00:00' ) + : H SECONDS
, ISO ) , '.' , ':' ) , CPLDTE = CURRENT_DATE , CPLTME = CURRENT_TIME , JOBSTS
= 'COMPLETE ERROR CNT ' CONCAT : H WHERE JOBNBR = : H AND SBMDTE = : H AND
SBMTME = : H AND PRGNME = : H
SQL5065 Access plan not found.
* * * * * E N D O F L I S T I N G * * * * *


---B--- (Does not Complete)
5770SS1 V7R1M0 100423 Print SQL information Program PACOBJUSV/BI00009MOD 12/29/10 11:15:37 Page 001
Object name...............PACOBJUSV/BI00009MOD
Object type...............*PGM
CRTSQLRPGI
OBJ(PACOBJUSV/BI00009MOD)
SRCFILE(QTEMP/QSQLPRE)
SRCMBR(BI00009MOD)
COMMIT(*NONE)
OPTION(*SYS *NOEXTIND *PERIOD)
TGTRLS(V7R1M0)
ALWCPYDTA(*OPTIMIZE)
CLOSQLCSR(*ENDACTGRP)
RDB(*LOCAL)
DATFMT(*ISO)
DATSEP('-')
TIMFMT(*ISO)
TIMSEP('.')
DFTRDBCOL(*NONE)
DYNDFTCOL(*NO)
SQLPKG(PACOBJUSV/BI00009MOD)
MONITOR(*USER)
SQLCURRULE(*DB2)
ALWBLK(*ALLREAD)
DLYPRP(*NO)
DYNUSRPRF(*USER)
USRPRF(*NAMING)
SRTSEQ(*HEX)
LANGID(ENU)
RDBCNNMTH(*DUW)
TEXT('Stage Risk Auto Dim - Veh Mods RW')
SQLPATH(*LIBL)
DECRESULT(31 31 0)
DECFLTRND(*HALFEVEN)
CONACC(*DFT)
STATEMENT TEXT CCSID(37)
DECLARE MODCUR CURSOR FOR SELECT VRSPOLIDN , VRSTRM , VRSPOLVRS , VRSAJMSEQ ,
SEQNBR , MODTYP FROM QTEMP / TMPMODS FOR READ ONLY
SQL4013 Access plan has not been built.
CREATE TABLE QTEMP / TMPMODS AS ( SELECT STG . VRSCMPCDE , STG . VRSPOLIDN ,
STG . VRSTRM , STG . VRSPOLVRS , STG . VRSAJMSEQ , STG . SEQNBR , MD . MODTYP
FROM PLVRSMST V INNER JOIN PLPOLMST AS PMMOD ON V . CMPCDE = PMMOD . CMPCDE AND
V . POLIDN = PMMOD . POLIDN AND V . TRM = PMMOD . TRM AND V . POLVRS = PMMOD .
POLVRS AND V . AJMSEQ = PMMOD . AJMSEQ INNER JOIN RIVEHMDT AS MD ON PMMOD .
CMPNME = MD . CMPCDE AND PMMOD . IDNNBR = MD . POLIDN AND PMMOD . TRMNBR = MD .
TRM AND PMMOD . VRSNBR = MD . POLVRS AND PMMOD . AJMNBR = MD . AJMSEQ AND PMMOD
. SEQNBR1 = MD . RSKSEQNBR AND PMMOD . SEQNBR2 = MD . SEQNBR INNER JOIN
WSAURSKT AS STG ON V . POLIDN = STG . VRSPOLIDN AND V . TRM = STG . VRSTRM AND
V . POLVRS = STG . VRSPOLVRS AND V . AJMSEQ = STG . VRSAJMSEQ AND MD .
RSKSEQNBR = STG . SEQNBR WHERE PMMOD . TBLNME = 'RIVEHMDT' AND MD . MODTYP =
'PASSRESTR' OR MD . MODTYP = 'ANTITHEFT' OR MD . MODTYP = 'ABS' OR MD . MODTYP
= 'DRL' ) WITH DATA
5770SS1 V7R1M0 100423 Print SQL information Program PACOBJUSV/BI00009MOD 12/29/10 11:15:37 Page 002
OPEN MODCUR
FETCH NEXT FROM MODCUR INTO : H ,: H ,: H ,: H ,: H ,: H
UPDATE WSAURSKT SET ABSIND = 'Y' WHERE VRSPOLIDN = : H AND VRSTRM = : H AND
VRSPOLVRS = : H AND VRSAJMSEQ = : H AND SEQNBR = : H
SQL5065 Access plan not found.
UPDATE WSAURSKT SET ATFIND = 'Y' WHERE VRSPOLIDN = : H AND VRSTRM = : H AND
VRSPOLVRS = : H AND VRSAJMSEQ = : H AND SEQNBR = : H
SQL5065 Access plan not found.
UPDATE WSAURSKT SET PASRSTIND = 'Y' WHERE VRSPOLIDN = : H AND VRSTRM = : H AND
VRSPOLVRS = : H AND VRSAJMSEQ = : H AND SEQNBR = : H
SQL5065 Access plan not found.
UPDATE WSAURSKT SET DRLIND = 'Y' WHERE VRSPOLIDN = : H AND VRSTRM = : H AND
VRSPOLVRS = : H AND VRSAJMSEQ = : H AND SEQNBR = : H
SQL5065 Access plan not found.
FETCH NEXT FROM MODCUR INTO : H ,: H ,: H ,: H ,: H ,: H
CLOSE MODCUR
INSERT INTO BIPCSCTT VALUES ( : H ,: H ,: H ,: H ,: H ,: H ,: H ,: H ,: H ,: H ,
: H )
SQL4021 Access plan last saved on 12/01/10 at 13:12:19.
SQL4020 Estimated query run time is 1 seconds.
SQL4027 Access plan was saved with DB2 Symmetric Multiprocessing installed on the system.
SQL4010 Table scan access for table 1.
UPDATE BIPCSCTT SET RUNTME = REPLACE ( CHAR ( TIME ( '00:00:00' ) + : H SECONDS
, ISO ) , '.' , ':' ) , CPLDTE = CURRENT_DATE , CPLTME = CURRENT_TIME , JOBSTS
= 'COMPLETE ERROR CNT ' CONCAT : H WHERE JOBNBR = : H AND SBMDTE = : H AND
SBMTME = : H AND PRGNME = : H
SQL5065 Access plan not found.
* * * * * E N D O F L I S T I N G * * * * *

[tomholden]

Re: Program Between Systems Runs Slow

i think that this: SQL4027 Access plan was saved with DB2 Symmetric Multiprocessing installed on the system. is your issue. DB2 SMP is installed on one box and not the other.

[abercrombieande]

Re: Program Between Systems Runs Slow

can it be disabled for a particular program? In what cases would it have a negative effect?

[tomholden]

Re: Program Between Systems Runs Slow

can it be disabled for a particular program? In what cases would it have a negative effect?

not that i'm aware of. recreate the SQLPKG on each machine instead of creating on one and restoring to the other & see if that helps.

[abercrombieande]

Re: Program Between Systems Runs Slow

How do I go about recreating the SQLPKGs? When I try to do CRTSQLPGK I get command not found in *LIBL

[tomholden]

Re: Program Between Systems Runs Slow

CRTSQLPKG...not CRTSQLPGK

[abercrombieande]

Re: Program Between Systems Runs Slow

Thanks, I rebuilt the SQL packages and now it runs fine. How do I avoid having this problem ? What caused the SQL package to get outdated or corrupt?

[tomholden]

Re: Program Between Systems Runs Slow

it's not corrupt/outdated the issue is that the package was created on the system with SMP installed. in the future i would create the package on the system that does not have SMP and push that across to the other system.