Solved

My query times out with error msg 666

Posted on 2007-04-03
41
403 Views
Last Modified: 2008-02-01
This is not good. I have a search screen in my .net app. Works fine in SQL Server. In AS400 , it times out. Of course, If I add more criteria to my search screen to narrow down the search, i dont get a timeout. OR if I make the search criteria longer, then it's fine. For example, i'm searching for phone number, if I type 314..it takes a long time, but if I do "314225", then no time-outs. I do a wildcard : 314% or 314225%...

I found this: http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzala/rzalamsg.html
Do a search for 666 and the section comes up...

I tried:              fetch first 20 rows only
                       OPTIMIZE FOR 20 ROWS
but still took forever. What else can I do? I dont understand "the access path" they're talking about. Any suggestion? My query is below:

=========

Select D1PHN as COL1,
                                   Rtrim(d1lname) || ',' || RTrim(d1fname) as COL2,
                                   sum(d1tot) as COL3,
                                   D2REF as COL4,
                                   d2stat as COL5,
                                   D1COLL as COL6,
                                   d1ssno as COL7,
                                   d1addr as COL8,
                                   D1City as COL9,
                                   D1ST as COL10,
                                   D1Zip as COL11,
                                  d1xphn as COL12,
                                  d1ephn as COL13,
                                 d2snam as COL14 ,
                                 d2cred as COL15,
                                 d1dcod as ID
     FROM Table1 INNER JOIN Table2 ON D1DCOD=D2DCODE
      WHERE
        exists ( select SU4SRC from DB2.usersrc where  SU4PRF='JOE'  AND SU4CLT# = Right(current schema,3) )
        AND D2DEL <> 'D' AND  D1DEL <> 'D' and d1phn like '314%'
        GROUP BY d1dcod,d1lname,d1fname,d1ephn,d1addr,d1ssno,d1phn,d1xphn,d2ref,d2snam,d2cred ,d2stat,d1coll,d1city,d1st,d1zip
                       order by D1PHN
                       fetch first 20 rows only
                       OPTIMIZE FOR 20 ROWS

0
Comment
Question by:Camillia
  • 25
  • 16
41 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18845378
Hi

access path is the algorithem that db2 choose to process a sql statement
sqlcode -666 means your statement was canceld by the query governer, which is configurable by the dba
anyway, in order for me to help you with your query, can you post here the indexes that are defined on the tables - table1, table2, db2.usersrc ?
thanks
0
 
LVL 7

Author Comment

by:Camillia
ID: 18845568
Hmm, in SQL Server , I see many indexes. IN AS400, I right clicked on the table in iSeries Navigator....i selected "show indexes". I see a list. The first one is :
SQL NAME     TYPE    SCHEMA   Owner     ShortName    Text
DBMASTIDX   Index    ....               ....
Address         Keyed Logical File

and so on. Note the column "TYPE". So this means this table only has one index on it?

Same fot Table2.
For DB2.usersrc,  I only see one row with "type" as "Keyed Physical File".

What do u think??
       
0
 
LVL 7

Author Comment

by:Camillia
ID: 18846171
and this is what i dont understand :

if I enter "314"..it times out
i I enter "314225", for example, it pulls back the rows...

if the cause is index, howcome "314225" works??
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18846239
the reason you get a time out is not necessarly because of the index
it might be because of the order by
like 314 needs to order many rows
like 314225 needs to order small number of rows
can you try without the order by ?

and regarding the indexes,
i need you to tell me on which column you have indexes defined and what is the sequnece of columns in each index
0
 
LVL 7

Author Comment

by:Camillia
ID: 18846316
I tried it without the "order by" and it takes a long time. Then I tried without-orer-by-and-"314225"..came back quickly.

I also did : select count(*) from TB1 where d1phn like '314%' and came back with 140,257 rows....

Regarding indexes....that's what I see...i only see one index and the rest are marked as "keyed logical file" or I see "type" as "keyed physical file". Are these the same as indexes??

0
 
LVL 7

Author Comment

by:Camillia
ID: 18846336
and when I did "select count(*) from TB1 where d1phn like '314225%' ", i got 92 rows back...

My manager says we need to put a restriction on the user and ask them to enter 6 numbers or more...
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18847286
from which table does d1phn  comes from ?
0
 
LVL 7

Author Comment

by:Camillia
ID: 18847345
From table1....

I think what you said makes sense....it's a large resultset , 140,000 something, no index and maybe that's why it takes longer to search for "314%" than for "314225%"

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18848546
yep
you should defenatly define two indexes - one on d1phn and another on d2dcode
that should speed your query up
0
 
LVL 7

Author Comment

by:Camillia
ID: 18850332
yeah but i dont think I can because the vendor app does funcky stuff behind the scene...

what are those "keyed logical files"...they're not indexes...so what are they used for? my manager keeps saying ..."but we have keyed logical files...but we have keyed logical files..."
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18850544
keyed logical files are like indexes, the only problem is db2 might not always recognize the if the create index statement was not issued (keyed logical files are created using dds sometime)
can you query sysindexes and check which indexes are define on the tables ?

are you allowed to change the sql statmenet ?
0
 
LVL 7

Author Comment

by:Camillia
ID: 18850614
I can change the sql statement yes...if I do change the sql, then have to use the "logical files" whatever?

Dont know how to query sysindexes. I tried select * from sysindexes...

BUT, in iSeries Navigator, I expanded the library, there's an "index" node .There are 6 indexes listed on the right pane. For TB1, I only see one index and that's on d1dcod field...
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18850655
so define another index on the d1phn field and it should fix you up
0
 
LVL 7

Author Comment

by:Camillia
ID: 18850746
I cant do that. The vendor app does  checks behind the scenes and if something doesnt match what they're checking against, the app breaks...

I am developing a .net app to go against the database (both in sql server and as400). I cant change anything with the database...but i can change my code/sql...

I'm trying something else...trying to see if I can use the logicals...not sure how that works in as400 but trying it now...will post back soon...
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18850802
can you try this

Select D1PHN as COL1,
                                   Rtrim(d1lname) || ',' || RTrim(d1fname) as COL2,
                                   sum(d1tot) as COL3,
                                   D2REF as COL4,
                                   d2stat as COL5,
                                   D1COLL as COL6,
                                   d1ssno as COL7,
                                   d1addr as COL8,
                                   D1City as COL9,
                                   D1ST as COL10,
                                   D1Zip as COL11,
                                  d1xphn as COL12,
                                  d1ephn as COL13,
                                 d2snam as COL14 ,
                                 d2cred as COL15,
                                 d1dcod as ID
     FROM
(select * from table 1 where d1phn like '314%')
 INNER JOIN Table2 ON D1DCOD=D2DCODE
      WHERE
        exists ( select SU4SRC from DB2.usersrc where  SU4PRF='JOE'  AND SU4CLT# = Right(current schema,3) )
        AND D2DEL <> 'D' AND  D1DEL <> 'D' and d1phn like '314%'
        GROUP BY d1dcod,d1lname,d1fname,d1ephn,d1addr,d1ssno,d1phn,d1xphn,d2ref,d2snam,d2cred ,d2stat,d1coll,d1city,d1st,d1zip
                       order by D1PHN
                       fetch first 20 rows only
                       OPTIMIZE FOR 20 ROWS



does this help ?
0
 
LVL 7

Author Comment

by:Camillia
ID: 18850992
I tried that and ran more tests on the orig one i had..

With yours:
  using TB1: 57120ms
 using Logical : 39301ms

With Mine:
searching for "3142%" :  with TB1: (24797 ms)
                      "3142%":  with Logical: 736 ms

**** I think this DB is just not designed correctly. The vendor app does row by row processing. They dont use sql joins. It's done in a proprietory software. For example, they start at the first row, go row by row and do whatever...

I think this is what i should do: Use Logical instead of the tables AND have a business rule to ask users to enter more than 3 digits to narrow down the resultset (you mentioned something like us above)

Your explanation above that
"like 314 needs to order many rows
like 314225 needs to order small number of rows"

 makes sense. I can only work with what i have: vendor's crappy design :)
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 18851026
yep
the number of rows does not only affect the ordering, it also affect the join operation
if table1 is chosen as the outer table, and table2 is the inner table,
so if table1 has 100 qualifing rows, table2 is scanned 100 times
if table1 has 10000 qualifing rows, table is scanned 10000 times

when table2 needs to be scan many times, db2 might chose it as the outer table, and if table1 is big, it will be bad

basically, limiting the number of rows has many effects beside the ordering, but 1 thing for sure, the less rows you get, the faster the query will run
0
 
LVL 7

Author Comment

by:Camillia
ID: 18851029
one other thing i dont understand...

If I run the sql , for example, say on '31422%"....time is 256ms
I ran it again, same sql,  and it's 118 ms

Is this because the first time i run it, execution plan or something is created to make the second run faster??
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18851125
there are couple of reasons this might happen
1) what you said - the execution plan already exists in memory so there is no need to prepare it
2) that actual data exists in db2's memory, so instead of reading it from file, it is reading it from memory
0
 
LVL 7

Author Comment

by:Camillia
ID: 18851174
thanks for all your help as usual and sticking with me thru this.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 18851207
your welcome
0
 
LVL 7

Author Comment

by:Camillia
ID: 18855341
my manager said to go ahead and create indexes...

So, I created indexes ( followed what the vendor has for sql server):
created indexes on:
d1addr,d1phn, etc : TB1.
Created indexes for DB2.Usersrc
created indexes for TB2 as well.

I did that thru iSeries Navigator , "indexes" node, then "new". I see the indexes now.
I ran my orig sql above, searching for "314%" and it took 119547ms. Still long...

do I need to run anything else after I create the indexes??

0
 
LVL 7

Author Comment

by:Camillia
ID: 18855448
if I do a stright "select * from TB1 where d1phn like '314%'...it takes 1922ms. I have an index on d1phn now...

but that sql of mine, joining takes time. I dont have an index on D1DEL or D2DEL .
0
 
LVL 7

Author Comment

by:Camillia
ID: 18855462
tried your sql as well with the new indexes, and it took : 47078 ms

so did i create the indexes correctly?? do i need to run something to make sure the indexes are registered or whatever??
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18855906
hi

try to create this indexes
table1 - d1phn
table2 - d2dcod

that should do it
if you want to see if db2 picks up the indexes you can run the explain sql statement, but i don't know how to do it using the navigator
0
 
LVL 7

Author Comment

by:Camillia
ID: 18856648
i know i created d1phn but let me see if I created d2dcode.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18857429
no
you said you created
d1addr,d1phn
that is not good
d1phn needs to be first in the index
0
 
LVL 7

Author Comment

by:Camillia
ID: 18870533
sorry, i was out of town...
ok, i deleted the indexes i had created and just created 3:
d1phn, d1dcod and d2dcode.
took: 49531 ms.

Found out about Navigator's sql analysis. Turned it on. It has "index used information". (it has lots of anlysis info) I looked at that and this is what I see: 4 rows and a lot of columns. I will only list row 1 and i've skipped some columns. I've only listed the important ones. *** look at the "table scan" column. it has four. so table was scanned 4 times??
 For row1:
  column "most expensive use": 2007-04-07 18:11:31.308664      
               "Average index used" :1
               "Number index entries": 750944.000      
               "total indexes created": 0
               "total index creates advised" : 4
               "maximun run time": 49.152384      
               "average run time": 48.573316      
                "min runtime: 47.994248      
                "max open time":49.152384      
                "max fetch time": -
                "max close time": -
                "max other time": -
                "operation": OPEN
                "statement usage count": 2
                "statement text":
                            Select D1PHN as COL1,
                                   Rtrim(d1lname) || ? || RTrim(d1fname) as COL2,
                                   sum(d1tot) as COL3,
                                   D2REF as COL4,
                                   d2stat as COL5,
                                   D1COLL as COL6,
                                   d1ssno as COL7,
                                   d1addr as COL8,
                                   D1City as COL9,
                                   D1ST as COL10,
                                   D1Zip as COL11,
                                  d1xphn as COL12,
                                 d1ephn as COL13,
                                 d2snam as COL14 ,
                                 d2cred as COL15,
                                 d1dcod as ID
     FROM TB1 INNER JOIN TB2 ON D1DCOD=D2DCODE
      WHERE
        exists ( select SU4SRC from vquectl.usersrc where  SU4PRF=? AND SU4CLT# = ? )
        AND
   "Host Variable values": ,, JOE, 020, D, D, 314%      
    "Full Opens": 2
    "Toal table scans": 4
     "total temp tables": 1
     "total sorts" 2
     "total optimizer timeouts": 0
     "max table rows": 846537      
     "max estimated rows": 144382      
     "average table rows": 532886.333      
     "average estimated rows": 73632.666      
    "cursor type": Non-scrollable Asensitive      
    "binding type": Row-wise      
   "statement type": System-wide Cache Dynamic      

 
      

0
 
LVL 7

Author Comment

by:Camillia
ID: 18870986

ah, Navigator has a visual representation of the sql when it's running (like sql server). This is what i see also:

Table scan for Table2: It says "optimizer chose table scan over available indexes". Estimate rows selected: 76188

Same for DB2.Usersrc: Estimated rows for this 328

For TB1, I see: Index Scan. Estimated rows: 144328.

Other stuff i see: Temp Hash table, Nested Join Loop, Hash Grouping and Sort....

so it's not using the indexes....
0
 
LVL 7

Author Comment

by:Camillia
ID: 18870996
it's using the index on TB1 (because of the Index Scan) BUT not using the index on TB2 and DB2.UserSrc.....
0
 
LVL 7

Author Comment

by:Camillia
ID: 18871207
Yeah, it only uses the index on TB1 which is :d1phn and d1dcod

Doesnt use the index on TB2 (d2dcode). It does a table scan.
Doesnt use the index on DB2.UserSrc. It does a table scan...
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18871684
you did not create any indexes on usersrc, that is why it does a tablespace scan
regarding d1dcode and d2dcode, how many values does those columns have ?

does the navigator has an option of recommending which indexes to build ?
0
 
LVL 7

Author Comment

by:Camillia
ID: 18873311
Actually, I did have indexes on Usersrc but forgot to mention them. Anyway, they werent being used. So I made changes...looks much better:

1. Created index on DB2.UserSrc, field SU4PRF.
2. Ceated index on TB1: D1COD and D1PHN

3. There's an "index advisor". It says to create an index on D1DEL and D1PHN. I did but the graph shows it not being used so I removed it.

Runtime is now: 36359ms

***But still not using index on TB2. I think maybe this is why:
There's a "Keyed Physical File" o Still does table scan. n TB2 named "TB2". Maybe when I have:
  TB1 INNER JOIN TB2 : it's actually picking up that "Keyed Physical File" instead of the actual Table named TB2. But i dont know...
0
 
LVL 7

Author Comment

by:Camillia
ID: 18873705
ok, made some progress for TB2. I made this change:

1. First, I changed the name of the TB2 to TB2_22 just to make sure i'm picking up the table not a logical file whatever . I am picking up the table.

2. Changed the "inner join" to " ...From TB1, TB2 Where d1dcod=d2dcode and...."
3. Index Advisor says to create an index on D2DEL . Type of Index : BInary Radix.

*** I create the index but I dont see a type "Binary Radix" in the drop-down list of index types. I create "non unique" BUT this index is not picked up. I check the 'advisor" again and it says to create that index.

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzajq/rzajqbinary.htm

hmm, so to create an index of that type??
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18874542
yes
i don't see any reason not to
0
 
LVL 7

Author Comment

by:Camillia
ID: 18875134

but i dont get that type "Binary Radix" in the drop-down of index types. I'll play around with it some more and see where it gets me. Wonder why it doesnt pick up the index for TB2.D2dcode.
0
 
LVL 7

Author Comment

by:Camillia
ID: 18876122
I took TB2 out of it , took Group By and whatever field related to TB2 out and it's 328 ms....

I dont mind breaking the sql down to 2...maybe first get the results from TB1, dump into a temp table, then join the temp table to TB2...

Any other ideas?
0
 
LVL 7

Author Comment

by:Camillia
ID: 18876163
it's the Group By that slows it down.

I have the GroupBy because of that "SUM" in the select...

hmmm
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18876502
well
since you need only the first 20 rows, it will be better for you to perform the group by only on the 20 different values of d1phn you select,
you could achive this using some code and breaking the select into 2 queries
i'll try to think of a single query that does that
0
 
LVL 7

Author Comment

by:Camillia
ID: 18884299
Someone here said maybe the database is using CQE instead of SQE and CQE is slower than SQE engine. He said if we have logicals that use "omit and select", then DB is using CQE..

For now this is what I did:
1. Do a straight select from TB1. No groub by, no SUM
2. I created a second table and I dump the results of step#1 in it ( a temp table)
3. I join TB2 to the second-temp table , do my group by and that D2DEL <> 'D'
4. I clear the temp table in the beginning of the stored proc

i ran it and it took 306ms.  Now , this wont be an issue if more than one user is using the system and calls the stored proc...right?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18887927
that is right
a temporary table cannot be shared by 2 transactions, each transaction get's it's own copy
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now