My SYSLISTS table is 110Mb, (REV30010), and contains 2300 records, most averaging 32k in size.

Many of the records seem to be quite old.

What is this file for?

What is the expected method of cleaning this up to reduce the size.

Thanks

Colin

Colin,

The SYSLISTS table is the repose of both Saved SelectLists and Temp selects. Every time you do a Saved SelectList - thats where it goes and stays till it gets deleted. Also, when you do a select and the system needs to construct a cursor - it may use this table for those temporary files destinguished by a T* from memory.

Mike Ruane wrote a little utility to cleam out the old Tempory files. If you are really savage, you may just blat the lot - like Delete_Row 'SYSLISTS','*' - there is a good chance that the system will keep on working. In a production environment I might be more circumspect and clean out just the old records.

Richard

Colin,

The SYSLISTS table is the repose of both Saved SelectLists and Temp selects. Every time you do a Saved SelectList - thats where it goes and stays till it gets deleted. Also, when you do a select and the system needs to construct a cursor - it may use this table for those temporary files destinguished by a T* from memory.

Mike Ruane wrote a little utility to cleam out the old Tempory files. If you are really savage, you may just blat the lot - like Delete_Row 'SYSLISTS','*' - there is a good chance that the system will keep on working. In a production environment I might be more circumspect and clean out just the old records.

Richard

Well, this Sprezzie's take is that on ARev I would never just clear out the SYSLISTS file without pushing a few records into a temp file for restoration, but on OI it's probably OK.

My reasoning here is that the system items stored in SYSLISTS, like the query logs, last saved select, TCL stacks, aren't really used in OI. They are there, but you can't just hit Ctrl-F10 to bring up the query list, so it all tends to go unused.

So, really, the only things that will be in there are bits of select statements, temporary items and other things not cleaned up properly.

Now, there's always the possibility that your application writes something there, but only you can answer that.

All things being equal, if it's that large that it's bothering you, clear it out, though back it up first.

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

Thanks

I have cleared it out and all is fine.

The info in there is quite old, some over 10 years by the looks of it.

Colin

My SYSLISTS table is over 16 gig right now.

I have a lot of concurrent users hitting my app 24 hours a day via web interface.

I'd have to shut the app down to do a CLEARTABLE on this as it would probably break something for someone if I cleared it while running. It's a hassle to shut down because I have to notify so many people including ECC Dispatch, etc… It's a large fire department. I will go ahead and coordinate the outage to facilitate this for this time.

Where is this tool that Mike wrote and how can I get it? I'd like to schedule it to run periodically.

Thank you,

Jim Mabey,

INFORMATION MANAGEMENT TECHNOLOGIES

jimmabey@gmail.com

Since this is being used by the system it is very hard for the user to *know* when and what to remove. It would be nice if there was a SYSTEM routine that would/could be set to *automagically* remove things like automatically created *cursors*. Once the system uses it up it should remove it. Or at least have a setting to do so

Hmmm, from the system monitor I type RUN CLEAR_TABLE "SYSLISTS" and I get a message

SSP205: The "SYSLISTS" system table cannot be cleared.

I wrote an SSP to select then loop through and delete records and it just locks up OI.

Ran from SYSPROG with same results

Aaron addressed most of the reasons why SYSLISTS can or can't be cleared. The biggest reason is because we don't know what users have written to their SYSLISTS table or if the program was written to depend on it. In most applications SYSLISTS can be cleared.

This way is another quick and easy way to clear it. From the system monitor:

run alias_table "REVBOOT", "GLOBAL", "syslists", "syslists_alias"

run clear_table "SYSLISTS_ALIAS"

run detach_table "SYSLISTS_ALIAS"

Since your site runs 24x7 I recommend you create a new pre-sized and size locked table to replace SYSLISTS. Monitor SYSLISTS and determine the average record size and number of records. With this information create a new table of that size and size lock it so it won't shrink. Figure out what the .LK and .OV files are of the table's data portion (not the dictionary) and the .LK and .OV file names of SYSLISTS.

Stop the system and linear hash service. Overwrite the existing SYSLISTS .LK and .OV files with the new tables .LK. and .OV. Restart the linear hash service and you'll have an empty syslists table that is already at an optimal size.

Thanks Jared. I did the alias table and did a cleartable on the alias. All is good now. Not sure why it grew so large. I will keep an eye on it.

Jared, I am not sure that I understand. I get that OI doesn't know what the user creates AND no system should cleanout something the Dev/User creates (except from a tmp directory) but shouldn't OI know what it creates and clean up after itself. It seems that it does neither.

Thanks

David,

Seems the challenge for OI to clear out the lists is that the SYSLISTS table is used for all sorts of purposes, and many developers use it - again, for all sorts of purposes.

Now if developers wrote their programs so that custom lists were taken out after use, then much of the problem would go away, but programmers are lazy creatures, and hygiene / maintenance ranks low priority.

I would appreciate if there was a standard utility - on the database manager menu / callable that enables a measure of SYSLISTS usage and safe clear-out. I think this would reduce the problems of systems in which the syslist blows out to enormous size.

Like Jim's experience I cam across a client with a 10 to 15Gb syslist table. Got this under control and performance improved a notch or two. The reality is now-one notices until it becomes a big problem.

On a regular schedule I run a CleanLists routine. The routine runs in the early morning before humans arrive to work. Here is the theory:

open "SYSLISTS":

locate any @ID with a prefix "T*", "W*", "Q*", "B*", "TCL*", and "QUERY.TABLE". Trash them all.

If you are running O4W you may have session savelists that remain. The name of the list contains the internal date. So, I trash any that are not today's:

The createdate is field( @ID, "_", 2) e.g. SESS_16547_ is internal date 16547

If @ID[1,"F_"] = "SESS" or "SESS2" and createdate lt date() then trash it!

I found lists with the ID:

ID = timedate(): ".": machinename; convert " " to "*" in ID e.g. 09:46:45**13:NOV*2012.MYPC_8616

for part1date = iconv( field( @ID[1,"F."], "*", 3,3), "D")

if part1date gt 0 and part1date lt date() then to the trash…

OPEN "O4WTEMP":

Documentation states that these temporary rows are deleted by the system, but that didn't work in early versions of O4W.

createdate = field( @ID, "_", 2)

If num( createdate) and createdate lt ( date() - 2) then trash.

I have published the source on the blog section of [www.harmacek.com].

Dave Harmacek

Harmacek Database Systems

Richard, as mentioned, I agree that NO sw should ever delete files it doesn't create. BUT since OI and O4W create files in SYSLISTS, they should responsibly remove those files. Just as my routines should either automatically delete lists or at least give the user the option to do this.

David .. Great post. I will take a look at your bits and see if there is something that I have missed in mine. I KNOW that i had not added O4W lists to those. Good thinking about he dates

Thanks

Dsig

Only works doing:

Delete_Row 'SYSLISTS','*'

As a rule of thumb, the system will prevent you from doing a CLEARTABLE command on a table starting with SYS

Mike

View this thread on the Works forum...