Hi all,
I migrated a sapdb instance from 7.4.03.17 to 7.5.00.14 by restoring a complete backup
data
file. This was also a move from RedHat 8 to RedHat Enterprise ES 3.0
I've upgraded the system tables, enabled auto-logging and so forth, and the database
seems to work but it's not reliable. At least one query that previously worked now
returns a -
904 error.
There are other strange problems occuring, but right now I'm focusing on this one
query.
I'm under the gun to get this working again. I've searched the web for information
about this
error but so far, I have not found explicit instructions on how to solve the problem.
A sample query:
select se.participantid, c.firstname, c.lastname, se.entrydate,
se.sessiontype, se.meetingtype, se.meetlocation, se.readiness, se.importance,
se.confidence,
se.sessionoutcome, s.firstname specialist_firstname, s.lastname
specialist_lastname
from session se, contacts c, specialist s , specialistmanagerlink sm
where
(se.participantid = c.id (+) and
c.specialistid = s.specialistid
and s.specialistid = sm.specialistid (+) and
(sm.managerid = 'bkc' or c.specialistid = 'bkc')
and DATE(se.entrydate) >= '2002-1-1'
)
order by c.specialistid, se.entrydate, c.lastname, c.firstname
I ran the optimizer statistics in dbmgui, but that made no difference.
I went into sql studio and noticed that there are no (none, zero) indexes on any of
these
tables, though c.id, sm.managerid, sm.specialistid, and s.specialistid are primary
keys.
I added ascending indexes on se.participantid, se.entrydate, s.specialistid but that
made no
difference.
I went back to the optimizer stats and index use (after trying a few failing queries
after
adding the indexes) and I note that se.entryid is "not used".
I have one data file. In dbmgui under Information it says
Total: 25,600 KB Perm: 9,016 KB Temp: 312KB Used: 9328 KB Free: 16,272 KB
Log: Total: 4,208 KB Used: 968 KB Free: 3,240 KB
(I have autolog enabled)
The cache is 10000 pages, instance type is OLTP.
knldiag shows this::
2004-07-05 17:00:59 21734 WNG 48 Kernel_E 80 percent of data base occupied, 640
pages free
2004-07-05 17:00:59 21734 WNG 48 Kernel_E 90 percent of data base occupied, 320
pages free
2004-07-05 17:00:59 21734 WNG 44 Converte Force Rollback due to DB-Full. Tasks 39
holds too many temp pages #all p.= 3199 #all temp. p.=
1965 #task temp p.=
2004-07-05 17:00:59 21734 WNG 44 Converte 1953
2004-07-05 17:00:59 21734 50 Kernel_E DB usage fell below 90 percent, 320 pages
free
2004-07-05 17:00:59 21734 51 Kernel_E 90 percent of log area occupied, 2879
pages used
2004-07-05 17:00:59 21734 50 Kernel_E DB usage fell below 80 percent, 640 pages
free
2004-07-05 17:00:59 21734 51 Kernel_E 80 percent of log area occupied, 2559
pages used
But dbmgui still says "data, 36% in use"
This is very confusing
And I *am* using ASSUME_KERNEL_LD on this box.
--
Brad Clements, [EMAIL PROTECTED] (315)268-1000
http://www.murkworks.com (315)268-9812 Fax
http://www.wecanstopspam.org/ AOL-IM: BKClements
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
