Alert - SP 20 Index Problem

[got_to_love_jde]

Hi All,
Found a problem in SP20 after some work.
Backround: Changed a date field in a table to decending from ascending and could not figure out why my UBE was not using the latest date instead of the earliest date.
I went into the SQL enterprise manager and found out that the index field had not been changed to decending after I had regenerated the table indexes.
It turns out that I was the first to discover (besides JDE, that is) that the middleware is not passing along the decending/ascending keywords when it generates the indexes. After I went into the SQL enterprise manager and manually checked the box to decending my UBE worked fine.
JDE says this will be fixed in either SP21 or SP22. I suggested that this might be a 'breaking news' event but did not get a confirmation that this would happen.
Regards,
Dave

 

[Christian Audet]

Dave I'm on SP20 and I want to confirm the bug that you are reporting. My problem is when I go to enterprise manager I don't know where to find the ASC DESC check box or any way to see the order. Can somebody tell me where to find it. I went to "All Task", 'Manage Index' and I'm not able to find it, where is this flag ?

Christian

 

[Christian Audet]

Dave,

I'm sorry to tell you that I don't seems to have the bug even under SP20. I've tried to generate a new index on F03b11 using descending date and it seems to work when I use it in a select / fetch next sequence. I'm still not able to see the flag (ASC, DESC) in the index, but everything seems to work fine here under SP20.

Christian

 

[got_to_love_jde]

Hi Christian,
Just getting back. This is the correct way to see that check box. After you get to the manage indexes screen, highlight an index and the click Edit.
Dave

 

[got_to_love_jde]

Christian,
Not sure how you could not have the problem. JDE confirms that this problem is known. What version of SQL server are you on ?
Dave

 

[Christian Audet]

Maybe I'm blind ! I don't see it, when I do edit I see all field, but no check box for ascending or descending.

Do you have a print screen ?

Christian

 

[Christian Audet]

OneWorld B7334 SP20 using SQL 7.00.623

One of the test that I did was to create a descending index in F03B11 on :

AN8 (ASCENDING)
PO (ASCENDING)
DIVJ (DESCENDING)
DOC (DESCENDING)

We did a "SELECT" using the newly created index on field AN8 and PO and it DID retrieve the most recent DIVJ wich I think is a proof that the index is working using descending.

If you can give more info or prepare some print screen to show the error it will be my pleasure to test it here. If this is a issue for everybody then it's better for us to know it right now. Up to now I'm not able to reproduce the error. Can it be only for primary key ?

Christian Audet

 

[Zoltan_Gyimesi]

Christian,

There is two way to chand Ascending behaviour to Descending and back in Table Design Aid:

A.) Select a field in an index and open the Indicies menu of TDA and you will find a selection: Ascending
B.) Right click on a field in an index and you will get the Ascending selection in the pop-up menu.

Both selection is a "markable" entry. If the selection is unmarked, then the sort option is descending.

Hope, it helps a bit.

Regards,

Zoltán

 

[Christian Audet]

Zoltan,

I actually know where is the Ascending / Descending for TDA. My problem is that I don't see it at the SQL Database level using the Enterprise Manager of "SQL Server".

If you have a answer to this last question, I will appreciate.

Thanks,

Christian Audet

 

[got_to_love_jde]

Here is a screen print Christian.

 

[barrier]

I believe that the "DESC" check box column is a SQL 2K thing that didn't exist in SQL 7. However, you should be able to press the Edit SQL button to see the actual SQL command that created the index.

Barry

 

[Christian Audet]

Thanks now I see, I don't have this column in SQL 7, are you in SQL 2000 ?

When in SQL 7 I go to edit SQL I don't even see the DESC param on any of the existing DESCENDING index.

Christian Audet

 

[got_to_love_jde]

Christian,
Yes, I am on Sql 2000. That is why I was asking what version you were on. And, actually, if you are not seeing the problem in SQL 7 maybe it is only affecting SQL 2000 instalations.

Trust me, there is a problem on MY system. And, obviously, you can manually fix the index through SQL so it's not a critical problem.

Regards,
Dave

PS: As a side note, the JDE log does not ever detail the ascending/descending qualification in it's SQL statements. JDE knows of this. I am not very big on SQL but I thought that this sequencing information COULD be included in the statement. Can anyone help me here ? Is that correct ?