Tablesize ??

[Former Member]

Hello Everybody

We build a testdatabase on a testserver with "restore with initialization" from our produktion database.(maxdb 7.6.00.34 no sap)

Everything works as expected.

When connecting to the testdatabase with sqlstudio, i found a view "tablesize" under tables/sysinfo

Columns: schemaname,tablename,usedsize,rowcount

We want use the rowcount column to avoid a "select count(*) from xyz" when we need information about the amount of records in a table.

This view is missing in our produktion database ???? Or to stupid to find it,-)

Do i have to create this view ???

If yes,how to do it? Doubleclick on the view of the testdatabase tells me

"Can not read view definition"

Any help welcomed

Best regards

Albert

[roland_mallmann · Accepted solution]

Hi Albert,

the mentioned system view TABLESIZE is owned by the schema SYSINFO; you should be able to see it when logged on as your SYSDBA user. The view is created by:

CREATE VIEW "SYSINFO"."TABLESIZE"
 (
	"SCHEMANAME", 
	"TABLENAME", 
	"USEDSIZE", 
	"ROWCOUNT"
)
AS SELECT DOMAIN.TABLES.SCHEMANAME, DOMAIN.TABLES.TABLENAME, 
FILES.TREELEAVESSIZE + FILES.TREEINDEXSIZE + FILES.LOBSIZE USEDSIZE, 
FILES.ENTRYCOUNT ROWCOUNT 
FROM DOMAIN.TABLES, SYSINFO.FILES 
WHERE DOMAIN.TABLES.TABLEID = SYSINFO.FILES.FILEID AND 
SYSINFO.FILES.TYPE = 'TABLE' INTERNAL   

You should be able to retrieve information like this from the Filedirectory (i.e. (SYSINFO.)FILES) joined with TABLES aswell.

Regards,

Roland

[Former Member · Accepted solution]

Hello Roland

I fire this command via ODBC.

SELECT DOMAIN.TABLES.SCHEMANAME, DOMAIN.TABLES.TABLENAME,

FILES.TREELEAVESSIZE + FILES.TREEINDEXSIZE + FILES.LOBSIZE USEDSIZE,

FILES.ENTRYCOUNT ROWCOUNT

FROM DOMAIN.TABLES, SYSINFO.FILES

WHERE DOMAIN.TABLES.TABLEID = SYSINFO.FILES.FILEID AND

SYSINFO.FILES.TYPE = 'TABLE'

The result cursor looks good. The entries in rowcount match to select count(*) from xyz, but:

There are 300 tables in the result cursor, about 250 show the correct values and about 50 show .NULL. in usedsize and rowcount ?????

All 300 tables belong to the same schema, same owner.

What am i missing?

Best regards

Albert

[roland_mallmann · Accepted solution]

Hi Albert,

that's very probably due to the fact that the Filedirectory-counters are updated continuously and haven't finished yet.

Regards,

Roland

[Former Member · Accepted solution]

Hello Roland

I could understand this for newer tables, but:

Most of the "missing" tables where created in 2005 !!!

And all of them are used every day ?????????

Is there a way to force an update ?

Best regards

Albert

[roland_mallmann · Accepted solution]

Hi,

can you show me a few of the involved tablenames?

Thx,

Roland

[Former Member · Accepted solution]

Hello Roland

SELECT DOMAIN.TABLES.SCHEMANAME, DOMAIN.TABLES.TABLENAME,

FILES.TREELEAVESSIZE + FILES.TREEINDEXSIZE + FILES.LOBSIZE USEDSIZE,

FILES.ENTRYCOUNT ROWCOUNT FROM DOMAIN.TABLES, SYSINFO.FILES

WHERE DOMAIN.TABLES.TABLEID = SYSINFO.FILES.FILEID AND SYSINFO.FILES.TYPE = 'TABLE'

example

BWALLE AUTONUM ? ?

BWALLE AUTONUMBACKUP 32 152

BWALLE BETRIEBSHAFTPFLICHT ? ?

BWALLE BETRIEBSHAFTSCHADENSART ? ?

Select * from tables(example)

BWALLE BWALLE AUTONUM SELUPDDELINSREFINDALT+ TABLE TABLE

2005-03-08 11:23:20 2008-02-20 05:40:44 2005-04-20 11:46:07

NO ? 20000 ? 000000000000034E NO NO NO

BWALLE BWALLE BETRIEBSHAFTPFLICHT SELUPDDELINSREFINDALT+ TABLE TABLE

2005-06-02 12:13:56 2008-02-20 05:39:15 2006-11-30 14:41:57

NO ? 20000 ? 0000000000000379 NO NO NO

BWALLE BWALLE BETRIEBSHAFTSCHADENSART SELUPDDELINSREFINDALT+ TABLE TABLE

2005-06-02 12:13:58 2008-02-20 05:40:46 2005-06-02 12:13:58

NO ? 20000 ? 000000000000037A NO NO NO

3 of about 50 tables with no rowcount

Other informations needed ?

Best regards

Albert

[roland_mallmann · Accepted solution]

Hi Albert,

hm, strange indeed.

Can you check:

- if you have any entries in the table SYSUPDATECOUNTERWANTED?

- if you DO have some entries, do they have relatively high CANCELCOUNTs?

___

You can also manually update the counters by issueing:

CHECK TABLE <TableName> WITH SHARE LOCK

Please be reminded that during the above command may take some time (depending on the size), can increase the I/O load on the machine and the involved table will be read only.

Regards,

Roland

[Former Member · Accepted solution]

Hello Roland

I have no table SYSUPDATECOUNTERWANTED ?

There is only a sysupdstatwanted with 1 entry.

Regards

Albert

[Former Member · Accepted solution]

Hello Roland

I want to fire the command:

CHECK TABLE <TableName> WITH SHARE LOCK

via programm.

I tried to do:

SELECT DOMAIN.TABLES.SCHEMANAME, DOMAIN.TABLES.TABLENAME, FILES.TREELEAVESSIZE + FILES.TREEINDEXSIZE + FILES.LOBSIZE USEDSIZE, FILES.ENTRYCOUNT ROWCOUNT FROM DOMAIN.TABLES, SYSINFO.FILES WHERE DOMAIN.TABLES.TABLEID = SYSINFO.FILES.FILEID AND SYSINFO.FILES.TYPE = 'TABLE' and DOMAIN.TABLES.SCHEMANAME = 'BWALLE' ORDER BY ROWCOUNT DESC

Then for each record in the result witch has rowcount .NULL. do the check table...

From sqlstudio i get the wanted resultcursor.

When connecting via odbc from my application (same user,pass as in sqlstudio)

i get an error: unkown base table table name FILES

Is there a difference between SQLStudio and ODBC ??

Best Regards

Albert

[roland_mallmann · Accepted solution]

Hi Albert,

- regarding SYSUPDATECOUNTERWANTED:

When was the last time you loaded the system tables?

- regarding the issue with ODBC:

There shouldn't be too much of a difference, because also SQL Studio connects through ODBC. So you're probably connected to either a different database, or maybe you didn't specify SYSINFO.FILES.

Regards,

Roland

[Former Member · Accepted solution]

Hello Roland

From ther DBMGui i tried configuration/upgrade systemtables.

First i got an error -24909 sydba unkown an then i got a login form ?

I typed in my dbauser and pass and the load system tables started?

Log shows everything OK.

Now i have a table sysupdatecounterwanted.

There are no entries with cancelcount > 0.

For example Table autonum has cancelcount 0, but in tablesize the column rowcount for autonum is still .NULL. ?

Regards

Albert

[Melanie · Accepted solution]

Hello Albert,

the dbmserver stores the dbm user and the sysdba user in the upc file. This is done during first authorization with the correct user (dbm) and during load_systab (sysdba). This stored user information is used to make some administrative tasks easier - when the user is already stored you don't have to specify it again during load_systab.

However, when it was not yet stored or when the upc file gets lost/corrupted, you have to store the entries again.

(For further information please check https://wiki.sdn.sap.com/wiki/x/UC8.)

So on your system it seems that the sysdba user was not stored in the upc file - therefore DBMGUI asked for the user information.

When the database was upgraded from an older version, which did not have the file directory counters, these counters are created in background - when there is no other activity on the system. When there is a lot of workload on the system, the counters get not updated. Tables created with the new version automatically get the counters from the beginning.

How many entries do you have in table sysupdatecounterwanted?

Please check, if the file directory counters exist after you executed the "CHECK TABLE <TableName> WITH SHARE LOCK" for these tables.

Best regards,

Melanie