TPE_MM_SWAPSIZE and related commands, 500MB temp file

[valeron]

Hi.. I need some help.
Using 9.1D
A complete newbie in progress and while processing a Crystal Report for a large set of data, i hit the 500MB temp limit, and it errors -16001 on me.

I've searched around and it seems the solutions are here.
http://progresscustomersupport-surv...3960?retURL=/apex/KnowledgeSearch&popup=false

However, I don't know how to actually implement them.
For example it says

specifying the TPE_MM_SWAPSIZE
-SQLTempDisk database startup parameter

But the problems is, I don't know where to key in this command! A complete newbie who inherited a Progress developed system from ages gone by...
I'll need some hand holding and a walkthrough for each of the steps shown in that article..

For example it says
UPDATE STATISTICS to improve performance.. (How do I do it? I tried running this command in SQL Explorer, running it for all tables and it crashed on me. Running Update Stats for a single table, it ran successfully. but it says 0 rows effected, and my problem still remains.)


As it is, from what's mentioned in that link this is my two best bet.
UPDATE STATS and the startup parameters...
Can anyone please help me out, and 'hand holding needed' unfortunately... tell me how to do the above?

Help please!!

Thanks a lot!!

 

[RealHeavyDude]

AFAIK the -SQLTempDisk is a startup parameter. You need to specify it where you start the database, either in the script where you issue the proserve command or in the conmgr.properties file (which usually is located in the properties folder beneath the installation folder) when the database is managed by the Admin Server.

Still you need to be aware the Progress 9.1d is stone age software, more than 10 years old and from what I know not so stable when it comes to the SQL engine.

Heavy Regards, RealHeavyDude.

 

[valeron]

AFAIK the -SQLTempDisk is a startup parameter. You need to specify it where you start the database, either in the script where you issue the proserve command or in the conmgr.properties file (which usually is located in the properties folder beneath the installation folder) when the database is managed by the Admin Server.

Still you need to be aware the Progress 9.1d is stone age software, more than 10 years old and from what I know not so stable when it comes to the SQL engine.

Heavy Regards, RealHeavyDude.

Hi, thanks for the reply, I've checked the conmgr there's a few sections

[configuration.DBNAME.defaultconfiguration]
asynchronouspagewriters=1
beforeimagebuffers=10
blocksindatabasebuffers=120000
database=dbname
displayname=defaultConfiguration
locktableentries=256000
maxservers=16
maxusers=50
otherargs=
servergroups=dbname.defaultconfiguration.defaultservergroup, dbname.defaultconfiguration.odbc


[database.DBNAME]
...........

[environment]
[servergroup.DBNAME.defaultconfiguration.defaultservergroup]
...........

[servergroup.DBNAME.defaultconfiguration.odbc]
...........

and the file ends with the comments

# host=localhost # -H
# initialservers=0 # n/a
# maxclientsperserver=0 # -Ma (calculated value)
# maxdynamicport=5000 # -maxport (5000 for NT; 2000 for UNIX)
# messagebuffersize=350 # -Mm (4gl only)
# minclientsperserver=1 # -Mi
# mindynamicport=3000 # -minport (3000 for NT; 1025 for UNIX)
# networkclientsupport=true # false for self-service
# numberofservers=0 # -Mpb
# port=0 # -S ; Must be non-zero
# # when networkclientsupport=true
# prosqltrc=nnnnnnnnnnn # turn on various levels of SQL tracing
# reportinginterval=1 # -rpint (4gl only)
# serverexe=<4gl server location> # _mprosrv (4gl only)
# type=both # n/a

Under which area should I place the command, and what should I type?
TPE_MM_SWAPSIZE/SQLTempDisk = 1000000 ?

As mentioned, I really don't know the heads/tails of it even if it's staring me in the face

 

[RealHeavyDude]

I have never used that particular startup parameter, but I would stick it under otherargs:

otherargs=-SQLTempDisk ....

But i suggest you to double check the patch level you are running with because somewhere in the back of my brain I remember that that were the days when Progress struggled a lot with the SQL engine to make it some kind of stable and therefore introduced big changes between service packs.

Heavy Regards, RealHeavyDude.

 

[valeron]

Ok here's what i did

I've placed this command in all [configuration.DBNAME.defaultconfiguration] sections that I can find in conmgr

otherargs=-SQLTempDisk 1000000000
also
otherargs=-TPE_MM_SWAPSIZE 1000000000


And I tried placing
TPE_MM_SWAPSIZE=1000000000
SQLTempDisk=1000000000

But, still no luck. The temp file stopped at 499009 and bam, my CR crashed


Looking at my admsvr log I found this
Command = [D:\Prgs91d\bin\_mprosrv, -classpath, "D:\Prgs91d\jre\lib\rt.jar;D:\Prgs91d\jre\lib\i18n.jar;D:\Prgs91d\java\progress.jar;D:\Prgs91d\java\messages.jar;;D:\Prgs91d\java\sonicMQ\lib\sonic_Client.jar;D:\Prgs91d\java\sonicMQ\lib\activation.jar;;", -properties, "D:\Prgs91d\properties\conmgr.properties", -servergroup, db.defaultconfiguration.odbc, -adminport, 7835, -m3]

This entry is inserted everytime I start my DB.
Allright, this seems to be the startup script of some sort, it's reading the configuration file from conmgr.properties... so it seems that I got the right file edited.
However, seems like my changes aint doing anything.

What else am I missing here?

 

[valeron]

If I try inserting the command here, I got an error, saying argument -L why?

 

[valeron]

Digging abit more... I found this.

It says, SQL92 Login Broker... vs other commands that says Database Broker startup... seems like the file I edited, is for database broker startup?
Just wanted to make sure I covered all bases, how do I know if I've created a SQL92 Login Broker?

I figure, when the SQL engine is started, it'll check for a login broker, if non is available, load defaults?

9.1D + -pinshm [Database Broker startup parameter] Pin Shared Memory will prevent the database engine from swapping shared memory contents to disk.


9.1D + -SQLTempStoreDisk <KBytes> [SQL92 Login Broker parameter] Size of SQL Server temp table disk storage. In V9.1D06 it's replaced by the -SQLTempDisk parameter. Instead use TPE_MM_SWAPSIZE environment variable. Value range: 5000 - 100000000. Default value: 500000 (KBytes).

 

[RealHeavyDude]

Did you check the service pack level you running? AFAIK the knowledge base entry advises different depending on the service pack level of 9.1d. Again 9.1 is stone age software, it's concept dates back to the 1990s and is, especially when it comes to the SQL engine, buggy, not performing well and, most of all, unsupported. At least you should upgrade to a reasonably recent version of OpenEdge10.

Heavy Regards, RealHeavyDude.

 

[TomBascom]

Also, if you are going to insist on continuing to run ancient, obsolete and unsupported releases like v9 at least bring it up to the last service pack -- 9.1e04.

 

[valeron]

I think I'll do just that... setup another testing server and try patching it up.
As.. my application is coded on 9.1D, will upgrading progress to 9.1e break the app down?

 

[TomBascom]

Progress is extremely upward compatible. Changing the point release is trivial. Shutdown the db, truncate the bi (usually optional but a good idea anyway), install the new patch/new version and then restart.

Is it a home-grown app or a commercial app? If commercial knowing which application it is may help people familiar with it comment.

In general, if you can compile your code you are free to upgrade to any version. (Recompiling is only needed when changing major version #.)

 

[valeron]

Hi, it's a commercial app, I believe you have come across a few other users of Epicor/Vantage too... I saw Steve Kutcha, was talking about upgrading to 9.1E too, dropped him an email to see if he can let me know how it went.

As it is, I'm pretty much stumped, and will get the patch up running asap for testing.
I'm wasting too much time on this as it is...

It's especially frustrating to see ppl talking about -SQLTempDisk 100000000 will solve your problems, but it's not doing anything on my rig :/

My only worries about upgrading is, we can't possibly test everything... and 1 year down the road, that 0.05% chance of it going wrong pops up, and I'm left unprepared with a broken system, and a restore point that's 1 year ago..... that'll be seriously bad for my health.

 

[RealHeavyDude]

Just a word of clarification: There can only be one broker that manages the shared memory. This is the primary login broker. Most databases, even when they support ABL and SQL clients and therefore should make use of a secondary login broker, just start THE one. You can start additional login brokers, so called secondary login brokers as you need them. It is recommended that you use dedicated login brokers for ABL and SQL clients for various reasons. Probably that's where the confusion comes from. When you start a broker you have the option to specify the client type it supports (-SeverType parameter). The -m3 in the server group definition indicates that it is a secondary login broker and it's name suggests that it is dedicated to the SQL clients.

Probably this link helps you:

https://progress.my.salesforce.com/...d-with-database-startup-parameters?popup=true

I states that you should specify it like this:

sqltempdisk=...

I have just seen that.

Heavy Regards, RealHeavyDude.

 

[TomBascom]

The risk, if you want to call it that, is orders of magnitude lower than 0.05%.

You are at a much, much higher risk that known bugs in your current release will bite you.

 

[valeron]

The risk, if you want to call it that, is orders of magnitude lower than 0.05%.

You are at a much, much higher risk that known bugs in your current release will bite you.

Ok.... Just dropped by my progress support page, and found out, there's no links to 9.1E available anymore, sent them an email, I'll see if they can point me to their archive ftp or something.
Maybe it'll help me out with some of the other errors i'm having with odbc too.

Just wondering, is there anywhere else I may get a copy of 9.1E if they say, we dont have it anymore?

 

[RealHeavyDude]

You should not upgrade to 9.1e. It will take you the same effort to go to a supported OE10 release or to OE11 and you can benefit extremely from the improvements that got into the product, especially on the SQL92 side of things. Therefore I strongly recommend you to upgrade to a supported OE10 release or OE11.

Heavy Regards, RealHeavyDude.

 

[TomBascom]

I certainly agree the 10 or 11 would be vastly preferable.

But 9.x to 9.1e04 is a bit simpler from an effort point of view because no recompile is required.