Help this muppet! Progress tables dead?! Error 7870 Lock Table is full 1

[mdemon]

Picture this - new job, project managing in a new system. The deadline? A few weeks away when the old licence expires!!! I'm having a total mare and you guys are probably my last chance - read on for my tale of woe...

We have a Progress back end for an elderly manufacturing package called "Proteus". We are trying to extract the data into SQL Server before Proteus timebombs in the imminent future.

Unfortunately, trying to get at the data via MS SQL Server or Crystal Reports comes back with the following; "Query Engine Error HY000. DataDirect Technologies ODBC Progress driver - Lock Table is full (7870)"

1) Is this a flaky ODBC driver? If so, which tool would I use in Progress to view and fix the data? And what on earth is wrong?

2) Where can I find the lookup for this particular error code?

I am a total muppet when it comes to Progress Db and can barely find my way through SQL and Crystal. "Proteus" support claim there is nothing wrong with the data! Unfortunately, Iam the only IT resource and a contractor I had in to do the job got stuck on the same problem...

(Using Merant 3.60 32-BIT Progress SQL92 v9.1D ODBC on W2k Svr SP4. Also installed: Access, MS SQL 2000, Crystal Reports 9)

Any thoughts? Starting to get a bit desparate here!

TIA,

John

 

[HelloMike]

John,

To save me a lot of typing, have a look at

http://www.peg.com/lists/dba/history/200311/msg00462.html

You will probably need to change the -L database startup parameter for your Progress database(s).

Hope this helps,

Mike.

 

[mdemon]

Thanks Mike - glad to know I'm not alone out there!

The -L database startup parameter - any ideas what I would change it to? I don't know where the startup parameter is, so that might be useful info too!

I did say I was a muppet at Progress...

 

[HelloMike]

Something somewhere on your system starts up a Progress database server process. This will be something like "mprosrv <database-name> <some-parameters>" where <some-parameters> will either be written in-line or will point to a parameter file. in the case of a parameter file, the startup parameter pointing to this will be "-pf <param-filename>".

If there's no mention of -L, the db will start up with the default lock-table size (8192 for Version 9 databases). Each record that's read for potential update (within the scope of a single transaction) takes up 1 entry in the lock table. The maximum lock table size is limited only by available memory, and each entry uses 18 bytes. If something like 50000 gets the job done for you, you should be OK with that.

However, if you're just reading data for reporting or replication or migration purposes, and have no intention of updating any records, you need to read these records NO-LOCK (in Progress 4GL syntax). I'm not sure how you do this in SQL (I'm a Progress 4GL specialist), but I suspect that if you can somehow eliminate the FOR UPDATE clause in your SELECT statement, that should do the trick.

Let me know how it goes.

Also, it might help a bit if you post some details of your environment - OS (with version), and version of Progress.

Cheers,
Mike.

 

[mdemon]

(Using Merant 3.60 32-BIT Progress SQL92 v9.1D ODBC on W2k Svr SP4. Also installed: Access, MS SQL 2000, Crystal Reports 9)

Thanks Mike. We ran Report Builder on the table in question and it came back with over 1400 PAGES of data! I think this must be some transaction log or something.

I've been using DTS (MS's Data Transformation Services) in MS SQL server so I guess I could mod the SQL to stop the update and stop the (many!) locks. I'll have a go.

Plan B is to squirt out the non-offending tables into SQL and export the bad guys into Excel from Report Builder. Then DTS them back in with the other tables. About as sophisticated as a brick I know, but should be OK for reporting purposes.

You may ask why we don't just pay the Progress license and just use Report Builder. I am posting a separate licencing thread, but in a nutshell, our VAR has told us we have "application specific" Progress licenses and we can't use it once the front end is switched off. Oh good.

And they say we can't buy a non-app specific licence either! I think this is what's called "stiffing" by a supplier here in the UK...

 

[HelloMike]

John,

Plan C. Won't work if you need the database 24/7. If you don't, and you're short of something to do over the bank holiday weekend ... Stop the database server, and run your extract program directly from a single-user session (pro or bpro -p <yourprog> -db <yourdb> -1) or something like that. The big thing about opening a Progress database in single-user mode is that the lock table doesn't get used - there's no need for it when you're the only user.

As for licencing ... AFAIK if you buy a licence from a VAR as part of an app, usage of that licence is restricted to that app. As for buying a non-app specific licence, talk directly to Progress. If you need contact details, we should take this discussion off-line.

Cheers,
Mike.

True wisdom is knowing when to RTFM.

 

[mdemon]

You're a star! I'll add it to the list of things to do...