Insert into - slower in SQL than RPG
kell...@gmail.com
return a result set in iseries navigator) but takes 90 minutes to
insert into a table. Their are a total of about 3,600,000 records in
the result set.
I'm using a "Delete from my_table;" then a "insert into my_table
(select xyz from myquery_that_takes_4_seconds_run);"
Is there a better way to do this? Our RPG programmer claims that he
can do the process in 'seconds'...which makes it very tough for us
that are using sql and not RPG to do our tasks as we would have to ask
him to take a day or so to write code to insert into our tables for
our reporting tasks.
Thanks!
Jason
as400 on v5r3
walker.l2
Have you tried doing this via DROP TABLE and CREATE TABLE AS SELECT
XYZ... ?
Elvis
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/rzaha/
batchblo.htm
Elvis
Graybeard
comments about SQL that might help.
1. I have never seen SQL outperform native database (ie RPG) based
strictly on time.
2. Goto V5R4 aas soon as possible. There are some significant SQL
improvements.
3. Get the lastest database PTF group and apply it.
4. Check that file QAQQINI exists in QUSRSYS. If not, do CRTDUPOBJ
from QSYS. Then add IGNORE_DERIVED_INDEX with a value of *YES. This
is especially important if you have any LF over that file that have
select/omit values.
I don't know for sure if any of this will help your INSERT, but it
certainly helps my SELECT and REFRESH big time.
kell...@gmail.com
On Jan 29, 11:53 am, "Elvis" <budim...@hotmail.com> wrote:
> It's probably not using blocking. Check this link out:
>
I researched the "with n values" clause for the insert into, but upon
trying to use it, I ended up getting nothing but errors with plain
sql. I then assumed that since the docs that I found over this
functionality were for db2 and not necessarily for the iseries, it
must be one of those things not supported on the iseries. just like
the incremental refresh of a MQT.
another mention was the "drop table" then "create table as select
(statement)"... We ran into problems doing this as well, and I
discovered that you must use the "with no data" clause first, then add
to the existing table.
Refreshing it as an MQT takes 90 minutes as well. I wrote a stored
procedure to update 1 record at a time, and it gained performance of
about 200 records per minute... should only take 10 days to refresh
the table using this manner. I then indexed more appropriately and
found a significant gain that allowed around a 90 minute update time
on the table using the stored procedure and a single record insert.
Another issue arose when attempting to use the visual explain tool.
I come from an Oracle/ MS Sql/ Postgress world where query plans mean
the life and death of an implementation. The IBM visual explain tool
will not start on my client machine and I am in the middle of working
the issue through with the IBM helpdesk.... it has been over a week
and about 3 save files created from our DbMon tables... we have yet to
come up with anything decisive as to why visual explain works for the
RPG programmers (who don't know what iseries navigator is, let alone
how to read a query plan) on their client machines, but does not work
on mine.
Any ideas on this issue would be greatly appreciated.
Why is it so incredibly difficult to find ANY documentation for these
issues for an iSeries? From what our RPG programmers have informed
me, the as400 is an extremely common server and even Microsoft uses
the iSeries for some applications. For a server to have this kind
of market share and for me to not find anything to document the issues
that I run into, I must assume that these issues are unique and that I
am doing something unusual in attempting to use SQL against this
server. The only decent documentation for the iSeries and SQL is the
redbook from IBM.
Should I just assume that SQL is an inefficient, complicated mess on
the as400 and adopt what the RPG coders have told me for the past six
months... to accept that RPG is a much more efficient, better
supported and more functional way of working with the iSeries, and
that SQL was added as a way for pc-based reporting tools to work
against the as400 and that the world of RPG will be around much longer
in the server realm?
They claim it's easy to learn and should only take hours....but I've
been staring at some of their simpler code for a few days off and on,
and I really don't understand it at all. I've coded quite a bit in
c#, vb 6.0, vb.net, asp and php for a few years now, and RPG is
nothing like anything I'm used to and will take me a very long time to
learn. Is it worth it to take this plunge, or should I just start
looking for a job in a non-IBM shop using the SQL and other languages
I already know? I want to become an enterprise level database
developer at some time during my career, I'm just not sure where to
focus on expertise.
Thank you for your time.
Jason
DBDriver
<kell...@gmail.com> wrote in message
news:1170125045.7...@p10g2000cwp.googlegroups.com...
>
>
> On Jan 29, 11:53 am, "Elvis" <budim...@hotmail.com> wrote:
>> It's probably not using blocking. Check this link out:
>>
>
> (statement)"... We ran into problems doing this as well, and I
> discovered that you must use the "with no data" clause first, then add
> to the existing table.
What about the logging? Could you look into switching this off for the
DELETE and INSERT transactions? I don't know of one off hand but you could
look around and see if they have a "Truncate" type command that avoids
logging the Deletes. I don't know enough about the SET options available but
the logging might be able to be disabled for INSERTS as well with something
like this.
Also, are there any Indices on the database? Particularly primary or unique
clustered keys? If there are you could try pre-sorting the SELECT statement
for the INSERT to match the destination key. It won't stop the index tree
going out of balance but it might help.
I am not sure what would differentiate the SQL INSERT from a repetitive RPG
version but I'd say they would both suffer. I've had to "speed up" many many
RPG programs so it's not that RPG is a silver bullet. What it does do though
is allow someone with sufficient knowledge of the database structure to
target individual record coies etc as required. SQL will try to work this
type of stuff out itself but can fail. Often when we look at these
situations the underlying schema is not well designed. The very notion that
you merely want to do an INSERT into a secondary table from a simple SELECT
statement makes you wonder.
>
> Why is it so incredibly difficult to find ANY documentation for these
> issues for an iSeries? From what our RPG programmers have informed
> me, the as400 is an extremely common server and even Microsoft uses
> the iSeries for some applications. For a server to have this kind
> of market share and for me to not find anything to document the issues
> that I run into, I must assume that these issues are unique and that I
> am doing something unusual in attempting to use SQL against this
> server. The only decent documentation for the iSeries and SQL is the
> redbook from IBM.
I'm an old RPG programmer. Here's the thing. An RPG programmer "COULD"
provide a faster solution - but often it will take a lot longer to code and
don't be surprised at the amount of code involved and potential bugs along
the way. There was the stat at one point - something like 1 line in 10 in
program code has a bug. Well a single simple SQL INSERT statement like you
want is 1 line - so you will be a mile ahead for quality from the beginning.
The SQL environment is quite good and can handle your requirements. You just
have to find the reason. I seen the same code dog an SQL Server that didn't
do anything in Oracle and vice versa. They each have their personalities.
>
> Should I just assume that SQL is an inefficient, complicated mess on
> the as400 and adopt what the RPG coders have told me for the past six
> months... to accept that RPG is a much more efficient, better
> supported and more functional way of working with the iSeries, and
> that SQL was added as a way for pc-based reporting tools to work
> against the as400 and that the world of RPG will be around much longer
> in the server realm?
The SQL environment was built around the pre-existing integrated database.
It always had this and is the only system I know of that has had a truly
integrated database - not a tack on. It was added for internal use. The
Client/Server attributes were the dovetailed on to this from at I see.
>
> They claim it's easy to learn and should only take hours....but I've
> been staring at some of their simpler code for a few days off and on,
> and I really don't understand it at all. I've coded quite a bit in
> c#, vb 6.0, vb.net, asp and php for a few years now, and RPG is
> nothing like anything I'm used to and will take me a very long time to
> learn. Is it worth it to take this plunge, or should I just start
> looking for a job in a non-IBM shop using the SQL and other languages
> I already know? I want to become an enterprise level database
> developer at some time during my career, I'm just not sure where to
> focus on expertise.
>
Stick with SQL. This is THE database access language. The RPG bandwagon is
still rolling on ISeries but it really just makes the system look like a
legacy box in a lot of people's eyes. Having said that - I'm off to write
another RPG program :).
> Thank you for your time.
> Jason
>
Rj.
Birgitt...@lp-gmbh.com
to get more information about SQL Performance Analyzis and
Optimization, you might check the following links:
1. Database Performance and Query Optimization
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/
rzajq.pdf
2. SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries
http://www.redbooks.ibm.com/abstracts/sg246654.html?Open
3. Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS
http://www.redbooks.ibm.com/abstracts/sg246598.html?Open
4. OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in
V5R4 (Attention it's only a draft, i.e. not yet officially published)
http://www.redbooks.ibm.com/redpieces/abstracts/sg247326.html?Open
... yet an other idea to solve your problem:
you say the query itself runs in seconds, but writing to a table takes
more than 90 minutes.
I assume, that the optimizer handles both differently, i.e. when
running the query interactively the optimizer uses an index, while
when writing to the table it uses an table scan.
This can occur, because interactive SQL is optimized to return the
first block of data as fast as possible (FIRST I/O), while when using
insert with selects it is optimized to finish the whole task as fast
as possible (ALL I/O). In this way in interactive SQL the optimizer
may use an index (even if it is only sub-optimal). In the insert
statement it perfers to use a table scan because there are only sub-
optimal indexes (BTW a tables scan is not always the worst decision).
To cut the long story short, just add the following clause at the end
of your select-statement (in the insert command):
OPTIMIZE FOR n ROWS
(where n is a rather small integer number)
This clause may affect the optimizer to change the optimization goal
from "ALL I/O" to "FIRST I/O", i.e. it may use an index (even if the
optimizer normally would prefer a table scan).
Hope this helps
IBM (ITSO) also offers a 3 1/2 day workshop about:
DB2 for i5/OS SQL and Query
Performance Tuning and Monitoring
Birgitta
walker.l2
> (statement)"... We ran into problems doing this as well, and I
> discovered that you must use the "with no data" clause first, then add
> to the existing table.
>
few times on a v5r2 system, and never had any problems.
If your SELECT returns in 4 seconds, do you really gain much by
porting the results into a separate table? Could you just run the
query again each time? Or perhaps, as Birgitta suggests, the query
doesn't really run in 4 seconds - if you run the query interactively
are you waiting 4 seconds for the first page of rows to be returned,
or does it take 4 seconds to run the query then go to the last page of
rows? Perhaps the last page also takes 90 minutes to calculate?
> Why is it so incredibly difficult to find ANY documentation for these
> issues for an iSeries? From what our RPG programmers have informed
> me, the as400 is an extremely common server and even Microsoft uses
> the iSeries for some applications. For a server to have this kind
> of market share and for me to not find anything to document the issues
> that I run into, I must assume that these issues are unique and that I
> am doing something unusual in attempting to use SQL against this
> server. The only decent documentation for the iSeries and SQL is the
> redbook from IBM.
>
Have you seen the InfoCenter? http://publib.boulder.ibm.com/infocenter/
iseries/v5r3/index.jsp
> Should I just assume that SQL is an inefficient, complicated mess on
> the as400 and adopt what the RPG coders have told me for the past six
> months... to accept that RPG is a much more efficient, better
> supported and more functional way of working with the iSeries
>
No, AS400s are fine database servers, and although the SQL
implementation might not be the best in every circumstance, it is more
than good enough for serious business use.
What's the CPW rating and RAM size of your AS400?
> They claim it's easy to learn and should only take hours....but I've
> been staring at some of their simpler code for a few days off and on,
> and I really don't understand it at all. I've coded quite a bit in
> c#, vb 6.0, vb.net, asp and php for a few years now, and RPG is
> nothing like anything I'm used to and will take me a very long time to
> learn. Is it worth it to take this plunge, or should I just start
> looking for a job in a non-IBM shop using the SQL and other languages
> I already know? I want to become an enterprise level database
> developer at some time during my career, I'm just not sure where to
> focus on expertise.
>
I know what you mean. We use COBOL, SQL, and Java on our AS400, but we
have some 3rd party stuff and some very old programs written in RPG;
personally I think it's a horribly terse language, and I seriously
doubt it could be learned in only a few hours. RPG isn't going away
any time soon, but I don't see any point in you learning it - it's not
a very transferrable skill, since it is only used on AS400s.
kell...@gmail.com
We do not run journaling on anything, and the insert into is done into
a staging table that has absolutely no indexing on it whatsoever.
The source tables are properly indexed as best as I can tell without
visual explain to back up my statements. I may have to just wait for
IBM to work through the visual explain problem...it's been over a week
now, I'm starting to get impatient.
You are right about the for ALL versus n Rows... when I run a select
count(1) from my_insert_query, it takes around 39 seconds to run.
I will post back later after I've tried what you have suggested, the
as400 is currently in use by the RPG team and I'm waiting my turn... a
few hours at most.
Thanks!
Elvis
tremendous.
I think Birgitta is right on the money with *FIRSTIO versus *ALLIO
suggestion. Add the OPTIMIZE FOR n ROWS clause at the end of your
statement (where 'n' is number of rows you expect to get back).
Here is a list of DB2 resources I compiled for some of our new guys:
SQL Performance and Tuning on DB2 for i5/OS Workshop (about 3 1/2
days)
http://www.redbooks.ibm.com/workshops/GR2029?Open
I work for Centerfield, company that specializes in SQL performance
tuning on System i, so I'd be remiss not to list our link :)
Centerfield Technology tools (especially insure/INDEX and insure/
ANALYSIS)
http://www.centerfieldtechnology.com/
Centerfield Technology newsletters (free)
http://www.centerfieldtechnology.com/publications/
Additional IBM Education
http://www-03.ibm.com/servers/enable/site/education/ibp/dada/
index.html?S_TACT=105AGX12&S_CMP=SPLT
http://www-03.ibm.com/servers/enable/site/education/ibo/curr.html?iser
http://www-03.ibm.com/servers/enable/site/education/ibo/view.html?oc
Download redbooks and/or whitebooks at following links:
SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries
http://www.redbooks.ibm.com/abstracts/sg246654.html?Open
OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4
http://www.redbooks.ibm.com/redpieces/abstracts/sg247326.html?Open
Database performance and query optimization
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/
rzajq.pdf
Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS
http://www.redbooks.ibm.com/abstracts/sg246598.html?Open
DB2 Universal Database for iSeries Administration: The Graphical Way
on V5R3
http://www.redbooks.ibm.com/abstracts/sg246092.html?Open
Modernizing IBM eServer iSeries Application Data Access - A Roadmap
Cornerstone
http://www.redbooks.ibm.com/abstracts/sg246393.html?Open
Using AS/400 Database Monitor and Visual Explain To Identify and Tune
SQL Queries
http://www.redbooks.ibm.com/abstracts/redp0502.html?Open
DB2 UDB for AS/400 Visual Explain: Illustrating the Secrets
http://www.redbooks.ibm.com/abstracts/redp0505.html?Open
Advanced Functions and Administration on DB2 Universal Database for
iSeries
http://www.redbooks.ibm.com/abstracts/sg244249.html?Open
MQT links
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/
rzajqmqt.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/
rbafymqt.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/
rbafzmsthrefresh.htm
deeb...@yahoo.com
> I have a sql query that is fairly well refined (takes a few seconds to
> return a result set in iseries navigator) but takes 90 minutes to
> insert into a table. Their are a total of about 3,600,000 records in
> the result set.
Are you issuing both statements from the navigator? Or is the insert
statement running over an iSeries Access/ODBC connection? It is my
experience that for some reason, depending on the statement type, a
SQL command tends to run relatively slower if issued from an app over
the Access/ODBC connection, especially for SQL insert's.
> Jason
> as400 on v5r3
Saml
1) Try to divorce the "delete *" from the "insert into".
a) Run your delete * and time it. Maybe it is taking a chunk of the time
but it seems unlikely. (The RPG guys can make this happen in about 3 seconds
by using the CLRPFM command, and, if necessary, you can execute this from
SQL.)
b) Run you select statment and create a brand new table with the 3.6
million records. Time it.
c) Insert the 3.6 million records into the cleared table from the brand new
table. Time it.
At this point you should have a pretty good idea where most of the time is
being taken.
2) If you machine is underpowered, or you are on an older version of the OS,
or you are not up to date on PTFs, those things may be contributing to the
slowness. You might share info about these issues.
3) When you are testing, are you running interactively or in batch? Running
in batch will often get better throughput if the machine is tuned correctly.
(Generally it gets a longer time slice.)
4) If uninstalling and re-installing iSeries Navigator doesn't fix it's
problems, you can get a glimmer of what is going on from the green screen
side. Turn on debug by issuing the STRDBG command, make sure the job is set
to log everything, then run your SQL. The optimizer will tell you which
access paths it uses and why it did or didn't chose an access path. Most of
this information is in the second level of the message. You'll need to ask
one of the RPG folk to help you with this and how to look at the job log and
the second level message detail because it is beyond my ability to explain
here. (Any competent AS/400 programmer should know how to do this.)
Sam
"Graybeard" <gray...@fastmail.co.uk> wrote in message
news:1170113152.2...@a34g2000cwb.googlegroups.com...
kell...@gmail.com
>a) Run your delete * and time it. Maybe it is taking a chunk of the time
>but it seems unlikely. (The RPG guys can make this happen in about 3 >seconds
>by using the CLRPFM command, and, if necessary, you can execute this >from
>SQL.)
all 3.6 million records.
>b) Run you select statment and create a brand new table with the 3.6
>million records. Time it.
I tried the "create table x as (myselect) with data" 87
minutes.
>c) Insert the 3.6 million records into the cleared table from the brand new
>table. Time it.
Deleted from table x, and did an insert into (myselect) optimized
for 100 rows ... 90 minutes.
Deleted from table x, and did an insert into (myselect)... 85
minutes.
>At this point you should have a pretty good idea where most of the time is
>being taken.
Yes, it is being taken by the huge select statement.
>2) If you machine is underpowered, or you are on an older version of the OS,
>or you are not up to date on PTFs, those things may be contributing to the
>slowness. You might share info about these issues.
Underpowered might be the case, but our RPG programmers have
listened to what I'm trying to do and told me that they can easily do
the same thing in seconds with RPG, on the same machine.
What is a PTF?
>3) When you are testing, are you running interactively or in batch? Running
>in batch will often get better throughput if the machine is tuned correctly.
>(Generally it gets a longer time slice.)
For some reason, Iseries navigator stuff always defaults
to an Interactive job... this makes the RPG programmers angry as the
whole company shares that as400. Occasionally they ask me to move it
to batch, there hasn't been a noticable time difference.
>4) If uninstalling and re-installing iSeries Navigator doesn't fix it's
>problems, you can get a glimmer of what is going on from the green screen
>side. Turn on debug by issuing the STRDBG command, make sure the job >is set
>to log everything, then run your SQL. The optimizer will tell you which
>access paths it uses and why it did or didn't chose an access path. Most of
>this information is in the second level of the message. You'll need to ask
>one of the RPG folk to help you with this and how to look at the job log and
>the second level message detail because it is beyond my ability to explain
>here. (Any competent AS/400 programmer should know how to do this.)
Just called about... he claims that STRDBG is outdated and he
has a tool that do the job much better for RPG. He then told me that
I should specify the access paths in my query directly...tried that,
and I can't create a view or MQT over an access path (which I assume
is an index from what they've told me) I asked about query plans and
the optimizer, he made a great point... he hasn't heard about
optimizers or query plans before or since he took a SQL class at a
local college... and I only know how to use these tools from an SQL
environment, so maybe because the company has never used SQL before,
the underlying components that handle query optimization and explain
plans just don't exist?
It's just a hypothesis... as you can see, I'm kind of grabbing for
anything here. I'm between having a 90 minute insert into and
spending months figuring out RPG to create something usable for this
simple report request that has been sitting on my desk for over two
weeks.
mike
A PTF is a "Program Temporary Fix" and is IBM speak for a patch to a
bug in the system code. Your system may be out of date and need one
or more such updates.
| It's just a hypothesis... as you can see, I'm kind of grabbing for
| anything here. I'm between having a 90 minute insert into and
| spending months figuring out RPG to create something usable for this
| simple report request that has been sitting on my desk for over two
| weeks.
In some situations SQL may be 2 to 5 times slower than RPG but it
should never be a thousand times slower. Since you only have an
unsubstantiated claim that your machine, with RPG, could add 3.6
million records in 3 seconds there is one more test I would recommend
before you start any major project. Try loading your 3.6 million
record dataset into an empty file with CPYF and measure the time. If
the TOFILE has many logicals or there is something else wrong, both
CPYF and RPG would also be very slow.
Mike Sicilian
deuxmille
things (you may have authority problems, but your RPG programmer will
understand what the following is meant to do, and will have authority to
do it)
this creates a library (=schema) and a source file and inserts the
select statement (no insert) into the source, 3 lines, each max 80 chars
CL: crtlib testquery;
CL: crtsrcpf testquery/qqmqrysrc mbr(myquery) rcdlen(80);
insert into testquery.qqmqrysrc values(1, 0, 'select x, y, z');
insert into testquery.qqmqrysrc values(2, 0, ' from file');
insert into testquery.qqmqrysrc values(3, 0, ' where ...');
commit;
this creates an executable query object
CL: crtqmqry testquery/myquery srcfile(testquery/qqmqrysrc);
this creates (if it doesn't exist), clear and fill the table
testquery.myresult from you query.
CL: strqmqry testquery/myquery output(*outfile)
outfile(testquery/myresult) outmbr(*first *replace);
I think this solution *may* use the native db engine to fill the result
table. But I'm really not sure.
Nicolas
kell...@gmail.com a écrit :
Karl Hanson
<snip>
>
>> 4) If uninstalling and re-installing iSeries Navigator doesn't fix it's
>> problems, you can get a glimmer of what is going on from the green screen
>> side. Turn on debug by issuing the STRDBG command, make sure the job >is set
>> to log everything, then run your SQL. The optimizer will tell you which
>> access paths it uses and why it did or didn't chose an access path. Most of
>> this information is in the second level of the message. You'll need to ask
>> one of the RPG folk to help you with this and how to look at the job log and
>> the second level message detail because it is beyond my ability to explain
>> here. (Any competent AS/400 programmer should know how to do this.)
> Just called about... he claims that STRDBG is outdated and he
> has a tool that do the job much better for RPG. He then told me that
> I should specify the access paths in my query directly...tried that,
> and I can't create a view or MQT over an access path (which I assume
> is an index from what they've told me) I asked about query plans and
> the optimizer, he made a great point... he hasn't heard about
> optimizers or query plans before or since he took a SQL class at a
> local college... and I only know how to use these tools from an SQL
> environment, so maybe because the company has never used SQL before,
> the underlying components that handle query optimization and explain
> plans just don't exist?
>
There are several debuggers, but that's not the point. The goal is to
put the job into debug mode prior to running your SQL statement(s), and
using STRDBG is probably the easiest way to do that. Once in debug
mode, the optimizer will send the informational messages (you need not
actually debug any program(s) - putting the job in debug mode is
sufficient).
SQL support is part of i5/OS and SLIC, including query optimization.
http://www.ibm.com/servers/eserver/iseries/db2/pdf/sqe_overview_v5r4.pdf
http://www.redbooks.ibm.com/abstracts/sg246598.html?Open
http://www.ibm.com/servers/eserver/iseries/db2/db2faq.html
--
Karl Hanson
Elvis
CALL QCMD
F10
STRDBG
STRSQL
Shift-F1
1
change "SELECT output" to option 3
Enter
type OUTPUT for file and QTEMP for library
Enter
Paste just your SELECT statement to STRSQL command line
Enter
F3
Enter
Let us know how long it took to run and what are the query optimizer
performance messages in the joblog.
Elvis
DBDriver
"mike" <mi...@mike.net> wrote in message
news:0a2wh.1485$zH1...@newssvr29.news.prodigy.net...
>| What is a PTF?
>
>
> In some situations SQL may be 2 to 5 times slower than RPG but it
> should never be a thousand times slower. Since you only have an
> unsubstantiated claim that your machine, with RPG, could add 3.6
> million records in 3 seconds there is one more test I would recommend
> before you start any major project. Try loading your 3.6 million
> record dataset into an empty file with CPYF and measure the time. If
> the TOFILE has many logicals or there is something else wrong, both
> CPYF and RPG would also be very slow.
>
> Mike Sicilian
Let them do it! Just time them and clock their hours as a standard hourly
rate. Then compare the result versus cost benefit. Also - make sure they
don't diddle the file structure/indices behind the scenes when they come to
the realisation that it doesn't actually run within the projected 3 seconds.
Rj.
Saml
1) Tell your RPG guy that you are not trying to use the debugger--you are
just trying to run your SQL after having issued the STRDBG command. With job
logging set to show everything, of course. The SQL optimizer will then
write messages to the log telling you what it is doing and why.
2) I really can't deterime from your response whether the problem is with
the select of with the insert, though I do think it is with the select.
Given that your file source and file target, what I was suggesting was:
a) Run something like
"create table alibrary/temp as (select * from somelibrary/source where
...) with data"
b) Run "insert into somelibrary/target (select * from alibrary/temp)".
My suspicion is a) will take a long time, probably 90 minutes, because it
has to do a table scan to find the data, and b) will take only a few minutes
because it is loading pre-selected data.
If this is the case, then it is critical to know what indexes are available
over file "source" and if/why the optimizer is chosing/rejecting them.
STRDBG will give you this an initial pass at this, maybe enough to figure
out the issue.
I don't think you mentioned how many rows are in "source". I know you said
"I have a sql query that is fairly well refined (takes a few seconds to
return a result set in iseries navigator)" but this few seconds is only to
return the first rows of the result set and the optimizer can make different
choices when running in an interactive job. When asked to return all the
rows, the optimizer may choose to do it differently. I believe the rule of
thumb is, or used to be, that if more than 20% of the rows of a table had to
be returned the optimizer would most often do a table scan.
Do you have a group by or order by clause? Sometime this will (used to)
cause the optimizer to decide to use an index when it is not the best
choice.
Are you doing any joining in the select statement?
And again, it would be nice to know what OS level you are running.
I know you don't want to learn RPG, but if you aren't a little familar with
the green screen interface then you may be at a disadvantage with the rest
of the shop. You do know that you don't have to run the SQL through iSeries
navigator or RPG? You can put things like "create table alibrary/temp as
(select * from somelibrary/source where ...) with data" in a source member
and run it as a batch job with SBMJOB CMD(RUNSQLSTM ...). That way you
won't impact interactive response time for everyone else.
I do a lot of SQL against iSeries databases, much of it interactive, though
I use a purchased product (Surveyor/400) instead of iSeries Navigator. If
what I am doing is complex and the files are large, I will sometimes revert
to the green screen interface and run the job in batch because it performs
better and is less intrusive. (But I'm extremely comfortable with the
iSeries and find doing this really easy.)
In iSeries parlance, a "logical file" is equivalent to an indexed view. We
may also use access path to refer to logical files. Specifying the access
path (or logical file) directly in your SQL rarely makes any difference.
The only time it would be significant, I think, is if there were many, many
logicals and the optimizer times out before it got to one that would be
useful. Specifiy that useful one in the SQL would make the optimizer look
at it first and perhaps cause it to be chosen. But in more recent versions
of th optimizer, I don't think this applies any longer.
Sam
<kell...@gmail.com> wrote in message
news:1170252747.3...@a75g2000cwd.googlegroups.com...
Hau...@sss-software.de
> may also use access path to refer to logical files. Specifying the access
> path (or logical file) directly in your SQL rarely makes any difference.
> The only time it would be significant, I think, is if there were many, many
> logicals and the optimizer times out before it got to one that would be
> useful. Specifiy that useful one in the SQL would make the optimizer look
> at it first and perhaps cause it to be chosen. But in more recent versions
> of th optimizer, I don't think this applies any longer.
>
> Sam
If an SQL statement is executed with the classic query engine (CQE),
specifying a DDS described logical file may help in some way, if the
optimizer times out.
But all queries where DDS described logical files are rerouted to the
classic query engine (CQE) and cannot profit form the advantages of
the new SQL Query Engine (SQE). This rerouting may decrease
performance up to 15%.
With SQE the optimizer won't time out. The access paths are searched
differently depending on which query engine executes the statement. If
the CQE executes the statement the access path are searched LIFO, i.e.
last created is checked first. If the SQE executes the statement, the
existing access path will be pre-sorted by the optimizer depending on
the join fileds, where clauses and order by clauses. The search stops
as soon as an access path is found that is more expensive than the
preceeding ones already checked.
Birgitta