SQL statement problem
tom
I work on i5 OS v5r4m0.
I have quite complicated SQL statement.
When I run it from STRSQL system uses available logical files so - it
uses indexes and it run quite fast.
Problem is that when I run it from ODBC sesion (using iSeries Access
ODBC driver) system creates lot of temporary indexes what means that it
doesn't use available logical files and it runs for hours.
What can cause that problem?
What is the difference between STRSQL and ODBC session that once indexes
are used and via ODBC doesn't.
Shall I modify my SQL and change physical files to logical files to
point correct indexes?
regards,
tomasz
iseriesflorida
How does your library list look for each one??
tom
Is the same in both cases.
tomasz
iseriesflorida
>
> - Show quoted text -
Tom, I am going to guess, maybe the ODBC connection route is using a
different qaqqini file??
dieter...@t-online.de
- using logical files won't help
- might be an issue of sortsequence and/or ccsid
- STRSQL optimizes for showing view rows only
Dieter Bender
CRPence
STRSQL by default effects an optimization for /output to display/
and read-only queries with no isolation. The ODBC may default to
optimize for /run to completion/ and update-capable queries under
isolation. For a start, at a minimum, try adding to the query
SELECT, the following clauses:
OPTIMIZE FOR 20 ROWS FOR READ ONLY WITH NC
The queries really need to be analyzed with the performance tools
to infer best what to do. Peruse the OpsNav database feature and
investigate the queries using things like monitors, explain, & index
adviser; the statement can be run directly in the /Run SQL/ window
and analyzed from that interface as well.
Regards, Chuck
Kent Milligan
why STRSQL performs differently.
My guess is that you were using Optimizer Debug Messages from the STRSQL
interface to determine which indexes the optimizer was using. Your experience
is a good example of why Debug Messages should no longer be used.
These messages do not include feedback on all the settings that influence the
query optimizer. In addition, the debug messages have not been enhanced to
account for the new methods/algorithms used by the new SQE (SQL Query Engine)
optimizer.
Thus, you need to be using the SQL Performance tools provided in the System i
Navigator. If you had collected SQL monitor data for STRSQL and the ODBC
application, you could have compared the Visual Explain output to find the
setting that was causing the differences in performance behavior. Visual Explain
completely describes the plan produced by the DB2 Query Optimizer and all the
settings that influence the query optimizer.
I'd highly recommend attending the DB2 for i SQL Performance Workshop where
attendees are taught about all of the important settings and how to use the SQL
performance tooling in System i Navigator:
http://ibm.com/systems/i/software/db2/db2performance.html
--
Kent Milligan
ISV Enablement - System i
km...@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
>>> ibm.com/iseries/db2
(opinions stated are not necessarily those of my employer)
Tim M
AS/400's didn't require a database administrator?
Kent Milligan
(rebalance index, collect stats, etc) that are required by many other DBMS products
>>> STRSQL by default effects an optimization for /output to display/
>>> and read-only queries with no isolation. The ODBC may default to
>>> optimize for /run to completion/ and update-capable queries under
>>> isolation. For a start, at a minimum, try adding to the query
>>> SELECT, the following clauses:
>>> OPTIMIZE FOR 20 ROWS FOR READ ONLY WITH NC
>>>
>>> The queries really need to be analyzed with the performance tools
>>> to infer best what to do. Peruse the OpsNav database feature and
>>> investigate the queries using things like monitors, explain, & index
>>> adviser; the statement can be run directly in the /Run SQL/ window
>>> and analyzed from that interface as well.
>>>
>>> Regards, Chuck
>>
>>
Tim M
> Tim M wrote:
>> Does anyone else lament the passing of the good ole days when
>> AS/400's didn't require a database administrator?
>>
> IBM i still doesn't require the low-level database administration tasks
> (rebalance index, collect stats, etc) that are required by many other
> DBMS products
>
except that collecting stats is the only method to discern the O.Ps problem.
And no rebalancing of indexes is required unless, of course, you use EVI's
And woe betide the company that had the audacity to build their database
files with DDS. No performance enhancements for them. Shame on them for
buying into the AS/400 early.
And RPG programmers, you don't get any improvements either, unless you
drink the cool-aid and use embedded SQL or some other interface that
requires the complete rewrite of your code.
CRPence
> Kent Milligan wrote:
>> Tim M wrote:
>>> Does anyone else lament the passing of the good ole days when
>>> AS/400's didn't require a database administrator?
>>>
> [snip]
>> IBM i still doesn't require the low-level database
>> administration tasks (rebalance index, collect stats, etc) that
>> are required by many other DBMS products
>>
> [snip} except that collecting stats is the only method to discern
> the O.Ps problem.
Best<>Only
> And no rebalancing of indexes is required unless, of course, you
> use EVI's
Automatic.
> And woe betide the company that had the audacity to build their
> database files with DDS. No performance enhancements for them.
> Shame on them for buying into the AS/400 early.
What performance enhancements are not available from DDS created
files, that exist for SQL TABLE, VIEW, & INDEX?
> And RPG programmers, you don't get any improvements either,
> unless you drink the cool-aid and use embedded SQL or some other
> interface that requires the complete rewrite of your code.
What improvements are lacking in RPG for which one would have to
use SQL?
Regards, Chuck
Hal
> AS/400's didn't require a database administrator?
>
Are you kidding? They don't require a *system administrator*. The
whole premise of the as/400 from day one was to be a powerful -
database- engine, which by my logic requires the skills of a database
admin. The os/400 operating system on the other hand, in my 10+ years
of humble experience with the as/400, is the least maintenance-
intensive of any computer I've ever owned. I've been doing upgrades
and installs since around V4R1, and I've been able to get through it
every time without any problems(currently running V5R4M5).
Database administration on my M50 OTOH takes up 8 hours of my life
every day....sometimes more. But the base operating system and
LIC...it never, ever, ever, EVER goes down or gives me any trouble.
Amen. It is the Best Damn Computer I've worked with in my entire
computing career thus far.
Chris
Tim M
the OS is better. But believe it or not, 20+ years ago it was simpler
and cleaner.
CRPence
>
> I am not kidding. and no one is denying that the database is good
> and the OS is better. But believe it or not, 20+ years ago it was
> simpler and cleaner.
And the system can continue to be used the same way it was then.
There is no requirement to use SQL, for example.
Regards, Chuck
Kent Milligan
You need to use some other database server products for a while and then you'll
see how easy you have it.
A rotary dial phone 20+ years ago was simpler and cleaner to use too:)
--Kent
Tim M
> Tim M wrote:
>
>> I am not kidding. and no one is denying that the database is good and
>> the OS is better. But believe it or not, 20+ years ago it was simpler
>> and cleaner.
>
> through Release 1 into Release 2 was no picnic. (Before we really knew
> about "versions".) There were far more difficult issues back then than
> what is seen in similar systems today.
>
> I recall the advancements made in cume PTF packages and how pre-/co-req
> processing has improved. Working in government with law enforcement, I
> very clearly recall how advancements in secure communications have made
> life easier.
>
> I remember forcing developers away from STRPGMMNU to use STRPDM and its
> related commands.
>
> I could probably cover most areas with memories, all of which lead into
> how much better administrative tasks are today.
>
Actually I was administrator on System/38 and then on AS/400 these last
20 years. And I was lamenting how complex the database management has
become and waxing nostalgic on the simpler days.
I am personally affronted by the QAQQINI file and the central role that
it plays in tweaking performance; that IBM could make us use such a
nasty method.
I am also personally affronted by other similar inscrutable choices such
as the existence of two separate query engines. Or why I should have to
care about 2gigabyte access versus terabyte access paths.
Yes, I understand that the price of power is complexity. That doesn't
mean, that I don't miss the simpler days.
Kent Milligan
How often are using the QAQQINI options file? What options are using to tweak
performance? That really should be a last resort when it comes to performance
tuning.