My query times out with error msg 666

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

LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
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
CamilliaAuthor Commented:
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
CamilliaAuthor Commented:
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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

momi_sabagCommented:
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
CamilliaAuthor Commented:
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
CamilliaAuthor Commented:
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
momi_sabagCommented:
from which table does d1phn  comes from ?
0
CamilliaAuthor Commented:
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
momi_sabagCommented:
yep
you should defenatly define two indexes - one on d1phn and another on d2dcode
that should speed your query up
0
CamilliaAuthor Commented:
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
momi_sabagCommented:
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
CamilliaAuthor Commented:
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
momi_sabagCommented:
so define another index on the d1phn field and it should fix you up
0
CamilliaAuthor Commented:
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
momi_sabagCommented:
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
CamilliaAuthor Commented:
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
momi_sabagCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
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
momi_sabagCommented:
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
CamilliaAuthor Commented:
thanks for all your help as usual and sticking with me thru this.
0
momi_sabagCommented:
your welcome
0
CamilliaAuthor Commented:
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
CamilliaAuthor Commented:
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
CamilliaAuthor Commented:
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
momi_sabagCommented:
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
CamilliaAuthor Commented:
i know i created d1phn but let me see if I created d2dcode.
0
momi_sabagCommented:
no
you said you created
d1addr,d1phn
that is not good
d1phn needs to be first in the index
0
CamilliaAuthor Commented:
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
CamilliaAuthor Commented:

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
CamilliaAuthor Commented:
it's using the index on TB1 (because of the Index Scan) BUT not using the index on TB2 and DB2.UserSrc.....
0
CamilliaAuthor Commented:
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
momi_sabagCommented:
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
CamilliaAuthor Commented:
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
CamilliaAuthor Commented:
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
momi_sabagCommented:
yes
i don't see any reason not to
0
CamilliaAuthor Commented:

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
CamilliaAuthor Commented:
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
CamilliaAuthor Commented:
it's the Group By that slows it down.

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

hmmm
0
momi_sabagCommented:
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
CamilliaAuthor Commented:
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
momi_sabagCommented:
that is right
a temporary table cannot be shared by 2 transactions, each transaction get's it's own copy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.