simple Query returns no result

[Former Member]

We have a problem with a simple query on a "old" Table in our Database. The Table has following Structure:

CREATE TABLE <table_name>

(

ROLE_ID INTEGER NOT NULL,

ROLE_NAME VARCHAR (99) ascii NOT NULL,

OBJECTDATA LONG BYTE,

UNIQUE (ROLE_NAME)

)

The table containts two rows and following querys get these results:

select role_id, role_name from <schema>.<table_name>

--> 2 rows

select role_id, role_name from <schema>.<table_name>

order by role_id

--> 2 rows

select role_id, role_name from <schema>.<table_name>

order by role_name

--> 0 rows ?? confusion

When we create a "new" table with the same structure, and insert the same content to this new table, the queries are working correctly.

What happened with our "old" table, so that these simple queries don't function anymore?

(Database Kernel 7.6.05 Build 009-123-191-997)

thx

gerri

Edited by: Gerfried on Jul 17, 2009 11:42 AM

[lbreddemann · Accepted solution]

Ah, before I forget...

The table does not seem to have a primary key - is that correct?

Could you generate the DDL for the current table via DBA Studio and past it here?

Please do also paste the result of this query:

select * from tables where tablename='<your table>'

select * from columns where tablename='<your table>'

regards,

Lars

[Former Member · Accepted solution]

Hi Lars,

DB-Version is 7.6.05 Build 009-123-191-997

and the command CHECK TABLE ... returns no result

The Table has a primary key. I forgot to paste the ddl:

ALTER TABLE <table_name> ADD PRIMARY KEY (ROLE_ID)

Resultset of first select:

APORTAL APORTAL TURBINE_ROLE SELUPDDELINSREFINDALT+ TABLE TABLE 2007-10-18 14:28:36 2007-10-18 14:28:36 2007-10-18 14:28:36 NO ? 20000 ? 00000000000003B4 NO NO NO NO

Resultset of second select:

APORTAL APORTAL TURBINE_ROLE ROLE_ID KEY INTEGER 10 0 NO SELUPD ? ? ? ? 1 1 1 2007-10-18 14:28:36 2007-10-18 14:28:36 TABLE ?

APORTAL APORTAL TURBINE_ROLE ROLE_NAME MAN VARCHAR ASCII 99 ? NO SELUPD ? ? ? ? 2 ? 3 2007-10-18 14:28:36 2007-10-18 14:28:36 TABLE ?

APORTAL APORTAL TURBINE_ROLE OBJECTDATA OPT LONG BYTE ? ? YES SELUPD ? ? ? ? 3 ? 2 2007-10-18 14:28:36 2007-10-18 14:28:36 TABLE ?

thx gerri

[Former Member · Accepted solution]

Hi Lars,

I assume that the long-column of this table has something to do with this problem.

Because we have the problem on 3 Tables including a long column and the queries on all other tables work correctly.

regards

gerri

[lbreddemann · Accepted solution]

Hi Gerfried,

hmm... never came across such an issue yet..

Please post for each of the statements the explain output.

And, please, use the code tags for that. Thanks.

regards,

Lars

[Former Member · Accepted solution]

Hi Lars,

here the output of the working statement:

explain select role_id, role_name from aportal.turbine_role
order by role_id

APORTAL	TURBINE_ROLE		TABLE SCAN	         1	
					   RESULT IS NOT COPIED , COSTVALUE IS	         1	
					QUERYREWRITE - APPLIED RULES:		
					   DistinctPullUp	         1	

and of the non-working statment:

explain select role_id, role_name from aportal.turbine_role
order by role_name

APORTAL	TURBINE_ROLE	INDEX001	INDEX SCAN	         1	
						ONLY INDEX ACCESSED		
						   RESULT IS NOT COPIED , COSTVALUE IS	         1	
						QUERYREWRITE - APPLIED RULES:		
						   DistinctPullUp	         1	

sorry for the non human-readable post

thx

gerri

Edited by: Gerfried on Jul 17, 2009 12:18 PM

[lbreddemann · Accepted solution]

> and of the non-working statment:

>

> explain select role_id, role_name from aportal.turbine_role
> order by role_name
> 
> APORTAL	TURBINE_ROLE	INDEX001	INDEX SCAN	         1	
> 						ONLY INDEX ACCESSED		
> 						   RESULT IS NOT COPIED , COSTVALUE IS	         1	
> 						QUERYREWRITE - APPLIED RULES:		
> 						   DistinctPullUp	         1	
> 

>

> sorry for the non human-readable post

Well, now it is readable - thanks for that.

INDEX001 here is the index that had been automagically created to enforce the UNIQUE constraint.

Let's make an experiment.

But first, please take a full database backup first - I don't want to loose the chance to find the root cause of this later by running the following changes...

Please run

DROP INDEX INDEX0001 on TURBINE_ROLE

and

CREATE UNIQUE INDEX I_RNAME_UQ on TURBINE_ROLE (ROLE_NAME)

and rerun the failing query.

Does the error reoccurs?

Regards,

Lars

ps,

Thanks a lot for throwing your points at me - I just passed the 8K point barrier

[Former Member · Accepted solution]

Hi Lars,

I think i won't get a backup today. But i have 3 Tables with the same problem in this database. So we can loos on table for testing, what to you think?

I have created a exactly? same table with another name. This queries on this table works correctly, as mentioned above.

Here the explain output of a functional query on this test-table:

explain select role_id, role_name from aportal.test_role
order by role_name

APORTAL	TEST_ROLE	INDEX001	INDEX SCAN	         1	
						ONLY INDEX ACCESSED		
						   RESULT IS NOT COPIED , COSTVALUE IS	         1	
						QUERYREWRITE - APPLIED RULES:		
						   DistinctPullUp	         1	

thx gerri

[lbreddemann · Accepted solution]

> I think i won't get a backup today. But i have 3 Tables with the same problem in this database. So we can loos on table for testing, what to you think?

Yes, go ahead then.

Right now I guess that the issue has nothing to do with the LONG columns (since they are not even touched - ONLY INDEX USED!) but that for some reason the indexes got damaged somehow.

So let's check whether the same problem reoccurs with a new index.

regards,

Lars

[Former Member · Accepted solution]

after dropping the index

-> it works

after recreating the named index:

-> it works

thx gerri

[lbreddemann · Accepted solution]

Hi Gerfried,

that's what I assumed.

Now we need to digg much deeper to figure out, what makes the original index so crappy.

Is it possible that you can provide a backup of your database that we can download?

Maybe you can create a copy of it first, remove everything else and provide this instance then for a backup.

Or - a different approach - if the index is not too big, you might just dump the whole index b*tree and

provide the dump.

What do you think would be possible for you?

regards,

Lars

[Former Member · Accepted solution]

the table has two rows. so the index should not be that big

A dump the whole index b*tree would be my preffered solution.

But how can i do that?

Please help me, because usually i'm an developer of an other DB-System and i have not a lot of experience with SAPDB.

thx

gerri

[Former Member · Accepted solution]

currently i found out how to dump an index b*tree.

but i did't have an idea how to get the object-id for my index.

The statement

SELECT * FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'INDEX001';

returns more than one Row where only the object_id's are different ...

thx

gerri

[lbreddemann · Accepted solution]

Hi Gerfried,

Please use this statement to get the correct root for the index:

select f.root from files f 
where f.fileid in 
(select max(fileid) from indexes 
where indexname='INDEX001' 
and tablename ='<TABLENAME>')

Then run the following in dbmcli as control user:

sql_execute DIAGNOSE TO 'indexdump.dmp' INDEX <root>

The indexdump.dmp can then be found in the rundirectory.

You may send this file to lars ( dot ) breddemann ( at ) sap ( dot ) com

regards,

Lars

[lbreddemann · Accepted solution]

Ok, Gerfried send me the dump file and this is what was in it:

INV ROOT/LEAF 15857  perm  entries : 0        [block 0]
     bottom  : 81         filevers: dummy     convvers: 9
                                              writecnt: 1
 
00001      nodepage.pno: 15857             nodepage.pt : data
00006      nodepage.pt2: inv               nodepage.chk: checksumData
00008      nodepage.mde: empty
08181      nd_checksum : 61937             nodepge2.pno: 15857
08189      nodepge2.pt : data              nodepge2.pt2: inv
08191      nodepge2.chk: checksumData
08192      nodepge2.mde: empty
00009      nd_bottom   : 81                nd_rec_cnt  : 0
00017      nd_level    : 0
00019      nd_filestate: empty
00020      nd_sorted   : false             nd_root     : 15857/F13D0000
00025      nd_right    : nil_pno           nd_left     : nil_pno
00033      nd_last     : nil_pno           nd_conv_vers: 9
00045      nd_str_vers : nil_pno           nd_file_vers: dummy
00052      ndPageVersio: 0                 nd_inv_usage: 0
00057      nd_leaf_cnt : 1                 nd_treeleavs: nil
00065      nd_trans_id : nil               ndInvRoot   : nil_pno
00077      nd_write_cnt: 1
END OF FILE

Obviously the reason for not delivering any data for the query is: this index is empty.

See "nd_filestate: empty" !

For some reasons (that I really don't know - sorry about that) this index had not been maintained anymore, since a very long time.

"nd_conv_vers: 9" tells us that it was the 9th savepoint that wrote down this page to the disks and that it had not been touched since then.

To view the current converter version you may just use the db_restartinfo command in dbmcli.

If you were a SAP customer the next thing I'd check would be with which database version these indexes had been created, what the internal file state is etc. - just to figure out the root cause.

As such an analysis is basically not possible via this forum all I can propose is to look for those indexes (containing 0 entries although the table has more rows) and rebuild them.

This statement should do the trick:

select i.owner, i.indexname, i.tablename, if.* 
from files if join files tf  on if.primaryfileid=tf.fileid
	          join indexes i on if.fileid=i.fileid
where if.entrycount =0
and if.type ='INDEX'
and tf.entrycount >0

best regards,

Lars

[Former Member · Accepted solution]

Hi Lars,

thanks for this analysis!

We will rebuild the broken indizes

regards

gerri

[lbreddemann]

Hi Gerfried,

what is the precise version of your MaxDB?

Do you get any error messages when running CHECK TABLE on your table?

regards,

Lars