f



SQL - query execution time

I have this question:
I have two libraries LIB1 and LIB2 created as collection sql,
the second library was created using command CREATE COLLECTION from SQL PROMPT and then
CRTDUPOBJ OBJ(*ALL) FROMLIB(LIB1) OBJTYPE(*ALL) TOLIB(LIB2) DATA(*YES) to copy all tables, 
at the end STRJRNPF of all the tables
.... both contain the same tables and the same records

If I try to execute a query like this:

SELECT * FROM tab1                              
WHERE tab1_field1='..'  AND tab1_field2='.'                   
AND tab1_field3 NOT IN (SELECT tab2_field1 FROM tab2   
WHERE tab2_field2='.....') 

over first library is immediate
while over the second library is very slow and are highlighted many cartesian products

I also tried to recreate the tablea and the index on LIB2 but nothing has changed...

Can someone explain me what is the reason for this behavior

Thanks
0
Marco
7/25/2016 9:47:49 AM
comp.sys.ibm.as400.misc 9219 articles. 4 followers. Post Follow

4 Replies
751 Views

Similar Articles

[PageSpeed] 35

Il giorno luned=C3=AC 25 luglio 2016 11:47:58 UTC+2, Marco Franchini ha scr=
itto:
> I have this question:
> I have two libraries LIB1 and LIB2 created as collection sql,
> the second library was created using command CREATE COLLECTION from SQL P=
ROMPT and then
> CRTDUPOBJ OBJ(*ALL) FROMLIB(LIB1) OBJTYPE(*ALL) TOLIB(LIB2) DATA(*YES) to=
 copy all tables,=20
> at the end STRJRNPF of all the tables
> ... both contain the same tables and the same records
>=20
> If I try to execute a query like this:
>=20
> SELECT * FROM tab1                             =20
> WHERE tab1_field1=3D'..'  AND tab1_field2=3D'.'                  =20
> AND tab1_field3 NOT IN (SELECT tab2_field1 FROM tab2  =20
> WHERE tab2_field2=3D'.....')=20
>=20
> over first library is immediate
> while over the second library is very slow and are highlighted many carte=
sian products
>=20
> I also tried to recreate the tablea and the index on LIB2 but nothing has=
 changed...
>=20
> Can someone explain me what is the reason for this behavior
>=20
> Thanks

I add more information:
this behavior is only using SQL prompt (STRSQL)
if I execute the query using SQLEXPLORER or SQUIRREL it work properly
0
Marco
7/25/2016 10:02:50 AM
Il giorno luned=C3=AC 25 luglio 2016 11:47:58 UTC+2, Marco Franchini ha scr=
itto:
> I have this question:
> I have two libraries LIB1 and LIB2 created as collection sql,
> the second library was created using command CREATE COLLECTION from SQL P=
ROMPT and then
> CRTDUPOBJ OBJ(*ALL) FROMLIB(LIB1) OBJTYPE(*ALL) TOLIB(LIB2) DATA(*YES) to=
 copy all tables,=20
> at the end STRJRNPF of all the tables
> ... both contain the same tables and the same records
>=20
> If I try to execute a query like this:
>=20
> SELECT * FROM tab1                             =20
> WHERE tab1_field1=3D'..'  AND tab1_field2=3D'.'                  =20
> AND tab1_field3 NOT IN (SELECT tab2_field1 FROM tab2  =20
> WHERE tab2_field2=3D'.....')=20
>=20
> over first library is immediate
> while over the second library is very slow and are highlighted many carte=
sian products
>=20
> I also tried to recreate the tablea and the index on LIB2 but nothing has=
 changed...
>=20
> Can someone explain me what is the reason for this behavior
>=20
> Thanks

Non qualifichi il nome delle table, quindi immagino che tra il primo e il s=
econdo SQL esegui un cambio delle lista delle librerie. Ricordo che questa =
operazione era mal digerita dal DB2. Prova a qualificare il nome delle tabl=
e.
0
Danilo
7/26/2016 12:23:54 PM
On 25-Jul-2016 05:02 -0500, Marco Franchini wrote:
> <<SNIP>> this behavior is only using SQL prompt (STRSQL)
> if I execute the query using SQLEXPLORER or SQUIRREL it work
> properly

   The Start Interactive SQL Session (STRSQL) is likely to default to an 
environment different than what is established for remote connections; 
review job attributes and the settings in F13=Services of STRSQL, and 
then compare with equivalent attributes established from settings on the 
other interfaces.  One setting to verify, to compare betwixt, would be 
the Query Initialization feature; i.e. QAQQINI.

   Additionally note: by default, SELECT queries in STRSQL implicitly 
act as though essentially the clause OPTIMIZE FOR 24 ROWS had been coded 
as a hint to the optimizer; i.e. given the output is\defaults to the 
display vs being directed to printer or output file, for which the 
effect is instead OPTIMIZE FOR ALL ROWS.  The likely default for 
non-STRSQL interfaces is the latter.  In either case, the expectation is 
that the user will ask for the specific optimization [hint] that is 
desirable for their chosen interface and specific query, if the 
presumed\defaulted effect is not desirable -- thus always explicitly 
including the clause can be worthwhile.

   FWiW the STRSQL interface is there to help prototype queries for 
syntax and function [as in verifying result-sets], not to verify the 
performance of queries that will be run elsewhere.

-- 
Regards, Chuck
0
CRPence
7/26/2016 1:08:24 PM
On 25-Jul-2016 04:47 -0500, Marco Franchini wrote:
> I have this question:
> I have two libraries LIB1 and LIB2 created as collection SQL,
> the second library was created using command CREATE COLLECTION from
> SQL PROMPT and then CRTDUPOBJ OBJ(*ALL) FROMLIB(LIB1) OBJTYPE(*ALL)
> TOLIB(LIB2) DATA(*YES) to copy all tables,
> at the end STRJRNPF of all the tables

   FWiW: There is a command Start Journal Library (STRJRNLIB) that could 
be used after the CREATE COLLECTION and before the duplication of objects.

> ... both contain the same tables and the same records

   But the same dependencies?  Any cross-library relationships will be 
problematic for Create Duplicate Object and SAVLIB\RSTLIB.

>
> If I try to execute a query like this:
>
> SELECT * FROM tab1
> WHERE tab1_field1='..'  AND tab1_field2='.'
> AND tab1_field3 NOT IN (SELECT tab2_field1 FROM tab2
> WHERE tab2_field2='.....')
>
> over first library is immediate
> while over the second library is very slow

   Any cached access plans and Materialized Temporary Indexes (MTI) are 
not available against the other data.

   Any invalid keyed access path built asynchronously for a duplicated 
object could impact\compete-with the AccPth rebuild.

> and are highlighted many  cartesian products

   Presumably that is Explain output?  The example query is not 
correlated, but I am not sure why the join implementation would change, 
except perhaps lack of non-implicit statistics available on the new objects.

>
> I also tried to recreate the tablea and the index on LIB2 but
> nothing  has changed...

   That would be tab1?  And the INDEX on LIB2.tab1 [for 
(tab1_field1,tab1_field2)] perhaps?

>
> Can someone explain me what is the reason for this behavior
>

   Actual origin of such issues being described from afar are difficult 
to guess; typically requires /seeing/ a lot more.  The above comments 
are a cursory SWAG.  And of possible value is my recent reply about 
comparing against Start SQL Interactive Session (STRSQL); i.e. 
apparently the non-STRSQL "execution time" issue is resolved.?

-- 
Regards, Chuck
0
CRPence
7/26/2016 1:29:17 PM
Reply: