Question Running procedure in single-user

[Potish]

I have a Progress program that I would like to run in single-user mode as it is currently locking a high number of tables causing a "Lock table overflow, increase -L on server (915)" error. It is not necessary for the program to lock any of the tables as no other users need access to these records until the entire procedure has completed processing. The program has input and output parameters.

It's my understanding that I can use "prowin32 -db <dbName> -1" to run a program in single user mode but I cannot find information on the full syntax to call the program with input and output parameters. I am looking for an example of code to do this

OE 11.1 64-bit
Windows 7 Prof (Dev) and Windows 2008 Srv (Prod)

 

[Rob Fitzpatrick]

I have a Progress program that I would like to run in single-user mode as it is currently locking a high number of tables ...

A large number of records?

It is not necessary for the program to lock any of the tables as no other users need access to these records until the entire procedure has completed processing. The program has input and output parameters.

It's my understanding that I can use "prowin32 -db <dbName> -1" to run a program in single user mode but I cannot find information on the full syntax to call the program with input and output parameters. I am looking for an example of code to do this

OE 11.1 64-bit
Windows 7 Prof (Dev) and Windows 2008 Srv (Prod)

Yes, you can connect to the offline database with the -1 parameter for single-user. Or just type "pro dbname" in a proenv session; same thing. That will be a character session rather than GUI, but unless your code depends on GUI libraries it shouldn't matter.

Since your program has parameters, you'll need a wrapper procedure.

/* caller.p */
/* set up propath, logging, etc. if necessary */
run callee.p (input param1, input param2, output param3).

/* callee.p */
define input parameter p1 as char no-undo.
define input parameter p2 as char no-undo.
define output parameter p3 as char no-undo.
/* etc... */
/* this program does the work */

At the command line you can run prowin32.exe -db dbname -1 -p c:\path\caller.p. Or you can just run caller.p in a procedure editor, if you have a dev license available (i.e. you can compile on the fly).

You will be doing this on the DB server, not on your PC.

 

[RealHeavyDude]

As Rob pointed out, you can not pass parameters to the first procedure in a session - nor can you pass parameters back to the shell script. You need to have a wrapper as the first procedure in the session ( which you specify with -p on the command line ) that just calls the procedure you actually need to run. When you need to pass information to a runtime session on the command line you can use the -param parameter and the PARAMETERS attribute on the SESSION system handle to retrieve the value in the ABL procedure. The only way that I have come up to pass a value back to the operating system is to generate an output file that holds the value in the ABL procedure which can then be picked up by the OS shell procedure.

Single uers connection means that the runtime client opens each and every database file for read and manages the shared memory just like the broker would. Such connections are only possible on the same machine where the database resides. A single user session, AFAIK, does not maintain the lock table but you'll lack many other features that a "real" database broker provides from a performance point of view.

There is only one reason as to why a procedure locks many records: They all need to be updated as part of the same transaction. If that is not the case in your procedure than you should have a look a the transaction scope rather than running the procedure in single user mode. Another alternative would be to bump up the lock table which you could do online ( proutil ... -C increaseto ... ) if you really need that "much".

Heavy Regards, RealHeavyDude.

 

[TomBascom]

There is only one reason as to why a procedure locks many records: They all need to be updated as part of the same transaction. If that is not the case in your procedure than you should have a look a the transaction scope rather than running the procedure in single user mode.

Heavy Regards, RealHeavyDude.

And 999 times out of 100 that "reason" is a case of not really thinking through the problem and just taking the easy way out by conflating a db transaction with a business transaction. Almost every such case I have seen really just needs to be made restartable. A few cases also need to be made reversible.