Re: SQL SYSIXADV STRANGE RESULT

Thanks Birgitta

From the DSPFD you can see the file has no select/omits.

SELECT/OMIT SPECIFIED . . . . . . . . . . . : NO
so SQE should have found the access path, IIML02.

No I dont know what query, but BPCS uses a lot of imbedded SQL.
I dont know the difference between CQE and SQE optimizer.

I am guessing that the optimiser times out before it picks an
access path. I will read the SQL performance tuning section
of the manual again, to try to figure where is this timeout value.

So until I figure this out for me SYSIDXADV is useless.

Frank Kolmann

PS Reason Advised is I1.


These are the settings in QSYS/QAQQINI
ALLOW_TEMPORARY_INDEXES *DEFAULT
APPLY_REMOTE *DEFAULT
ASYNC_JOB_USAGE *DEFAULT
CACHE_RESULTS *DEFAULT
COMMITMENT_CONTROL_LOCK_LIMIT *DEFAULT
FORCE_JOIN_ORDER *DEFAULT
IGNORE_DERIVED_INDEX *DEFAULT
IGNORE_LIKE_REDUNDANT_SHIFTS *DEFAULT
LIMIT_PREDICATE_OPTIMIZATION *DEFAULT
LOB_LOCATOR_THRESHOLD *DEFAULT
MATERIALIZED_QUERY_TABLE_REFRESH_AGE *DEFAULT
MATERIALIZED_QUERY_TABLE_USAGE *DEFAULT
MESSAGES_DEBUG *DEFAULT
NORMALIZE_DATA *DEFAULT
OPEN_CURSOR_CLOSE_COUNT *DEFAULT
OPEN_CURSOR_THRESHOLD *DEFAULT
OPTIMIZATION_GOAL *DEFAULT
OPTIMIZE_STATISTIC_LIMITATION *DEFAULT
PARALLEL_DEGREE *DEFAULT
PARAMETER_MARKER_CONVERSION *DEFAULT
QUERY_TIME_LIMIT *DEFAULT
REOPTIMIZE_ACCESS_PLAN *DEFAULT
SQL_SUPPRESS_WARNINGS *DEFAULT
SQL_TRANSLATE_ASCII_TO_JOB *DEFAULT
SQLSTANDARDS_MIXED_CONSTANT *DEFAULT
STAR_JOIN *DEFAULT
STORAGE_LIMIT *DEFAULT
SYSTEM_SQL_STATEMENT_CACHE *DEFAULT
UDF_TIME_OUT *DEFAULT
VARIABLE_LENGTH_OPTIMIZATION *DEFAULT

Do you know which queries caused the advice?
If so do you know which query engine executes this queries?
I assume the advice cames from CQE.
CQE uses only estimated values for its advice.
Also CQE searches the access path from the last one created to the first

one

created.
CQE may also get a time out before all indexes are searched.

Mit freundlichen Grüßen / Best regards

Frank.Kolmann@xxxxxxxxxx
Gesendet: Thursday, July 31, 2008 01:51
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL SYSIXADV STRANGE RESULT (WAS Way to get STRSQL SELECT to run
inbatch?)


Hi
If it worked, the V5R4 file SYSIXADV would be a very powerful and time
saving function.
I use STRDBMON to get the same info, but I can only do that when the job
is running.
We have BPCS and IIM is the Item Master.

I used the SQL below and IIM came up.
I could not believe my eyes, we are quite diligent and anyway there exits

a

LGL with the exact key advised with no other Selects etc etc.

So either SYSIDXADV is broken or something else is not set properly.

Any suggestions
TIMES_ADVISED TABLE_SCHEMA NAME LAST_ADVISED KEY_COLUMNS_ADVISED
56,674,993 LIB IIM 2008-07-31-09.30.19.926679 IPROD

31/07/08 DISPLAY FILE DESCRIPTION
DSPFD COMMAND INPUT
FILE . . . . . . . . . . . . . . . . . . . : FILE IIML02
LIBRARY . . . . . . . . . . . . . . . . . : LIB
TYPE OF INFORMATION . . . . . . . . . . . . : TYPE *ACCPTH
FILE ATTRIBUTES . . . . . . . . . . . . . . : FILEATR *ALL
SYSTEM . . . . . . . . . . . . . . . . . . : SYSTEM *LCL
FILE DESCRIPTION HEADER
FILE . . . . . . . . . . . . . . . . . . . : FILE IIML02
LIBRARY . . . . . . . . . . . . . . . . . . : LIB
TYPE OF FILE . . . . . . . . . . . . . . . : LOGICAL
FILE TYPE . . . . . . . . . . . . . . . . . : FILETYPE *DATA
AUXILIARY STORAGE POOL ID . . . . . . . . . : 00001
ACCESS PATH DESCRIPTION
ACCESS PATH MAINTENANCE . . . . . . . . . . : MAINT *IMMED
UNIQUE KEY VALUES REQUIRED . . . . . . . . : UNIQUE NO
KEY ORDER . . . . . . . . . . . . . . . . . : NOT SPECIFIED

SELECT/OMIT SPECIFIED . . . . . . . . . . . : NO
ACCESS PATH JOURNALED . . . . . . . . . . . : NO
ACCESS PATH . . . . . . . . . . . . . . . . : KEYED
NUMBER OF KEY FIELDS . . . . . . . . . . . : 1
RECORD FORMAT . . . . . . . . . . . . . . . : IPI100IM
KEY FIELD . . . . . . . . . . . . . . . . : IPROD
SEQUENCE . . . . . . . . . . . . . . . : ASCENDING
SIGN SPECIFIED . . . . . . . . . . . . : UNSIGNED
ZONE/DIGIT SPECIFIED . . . . . . . . . : *NONE
ALTERNATIVE COLLATING SEQUENCE . . . . : NO
FILES ACCESSED BY LOGICAL FILE PFILE
FILE LIBRARY LF FORMAT
IIM LIB IPI100IM


Frank Kolmann

----- Message from David FOXWELL <David.FOXWELL@xxxxxxxxx>
on Wed, 30 Jul 2008 09:56:11 +0200 -----
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>

Subject: RE: AW: Way to get STRSQL SELECT to run in batch?

I had a quick look.
SELECT * from SYSIXADV where times_advised = (
SELECT max(TIMES_ADVISED) FROM SYSIXADV )

Found a file that has 4 500 000 record and has been advised 2 million

times.

Should I be worried about this?