Is it possible to execute Progress code from JSP?

Your problem with -L is because the SQL order lock a lot of records,

may be you can check, in the progress documentation, the option

WITH NOLOCK pased on SELECT statements.

In order to execute 4GL progress code from java
there are a lot of posibilites:

1) Using JDBC and SQL

2) Start an Appserver and use the JavaDirect connection
to Appserver to execute a 4GL Procedure

3) Start a Appserver (state-free) and expose the 4GL procedures
as WebServices, use JavaAxis package to call the procedure.

4) Start an Appserver and generate ProxyGen the sckeleton to be used in Java.

5) You can use SonicMQ (progress product) and JMS Messaging.
A 4GL batch procedure connect direct to SonicMQ broker and 
wait for a JMS Message into a Queue or Topic. 
The java application send a JMS message to Sonic and 
start the 4GL proceure call.

6) start a webspeed broker and from java you can invoke a URL like:
http://localhost/cgi-bin/wspd_cgi.sh/myproc.p?param1=A&param2=B
by passing parameterts.

7) You can start a SERVER SOCKET in 4GL and call the URL from 
java application
(see: OpenEdge-samples/sockets/WebServer)


as you see, in all these case, you need another 
progress procuct (AppServer/SonicMQ/WebSpeed)

If you want I can give you details from any of these solutions, sample,

config issues, etc.

Thank you. It does help. Unfortunately, the company does not want to spend to get any of these products. So, I guess we are going to have to figure out what is wrong with the SQL statements.

Thanks!

The solution number 7 use only one product: the 4GL client

see these samples, you will understand.

In the meen time, check the progress SQL documentation

and fix all these SELECT * FROM table with the option NOLOCK !!!

You don't mention whether the problematic SQL is read only or whether it is doing updates.

Have you looked at the query plan?

yes, good ideea

If you want to see what is happening on SQL,

you have to export this (un-documented) variable, on system.

PROSQLTRC="Y"

after restarting the database server you will find a file type [pid].trc

like "trace" on your WRKDIR or on the database directory, which

trace all SQL statements.

Be attention with this, because is very "verbose" file ...

It is a combination: Here is the code:

Step 1:

--updateDates
insert into pub.xxpctdt_mstr
(xxpctdt_eff_date, xxpctdt_start, xxpctdt_end, xxpctdt_per_start, xxpctdt_add_dt,
xxpctdt_add_userid, xxpctdt_add_prgm, xxpctdt_add_tm)
values (NewEffectiveDate,NewEffectiveDate,HistoryEndDate,NewPeriodStartDate,actualDate,'[GLOBAL_ID]','CCSPCCProcess',actualDate)

Step 2:

--backupCurrentData
lock table pub.xxbpctrod_det in exclusive mode;
delete from pub.xxbpctrod_det;
lock table pub.xxbpctro_mstr in exclusive mode;
delete from pub.xxbpctro_mstr;
lock table pub.xxpctro_mstr in share mode;
lock table pub.xxbpctro_mstr in exclusive mode;
insert into pub.xxbpctro_mstr
(xxbpctro_nbr, xxbpctro_date, xxbpctro_cust_ord, xxbpctro_memo_date,
xxbpctro_memo_nbr, xxbpctro_cust,xxbpctro_mcn, xxbpctro_sold_to,
xxbpctro_ship_to, xxbpctro_bol_date, xxbpctro_curr, xxbpctro_max_memo,
xxbpctro_wh, xxbpctro_distr, xxbpctro_add_dt, xxbpctro_add_userid,
xxbpctro_add_prgm, xxbpctro_add_tm)
select xxpctro_nbr, xxpctro_date, xxpctro_cust_ord, xxpctro_memo_date,
xxpctro_memo_nbr, xxpctro_cust, xxpctro_mcn, xxpctro_sold_to,
xxpctro_ship_to, xxpctro_bol_date, xxpctro_curr, xxpctro_max_memo, xxpctro_wh,
xxpctro_distr, xxpctro_add_dt, xxpctro_add_userid, xxpctro_add_prgm, xxpctro_add_tm
from pub.xxpctro_mstr;

Step 3:

--deleteOldData
lock table pub.xxpctrod_det in exclusive mode;
lock table pub.xxpctro_mstr in exclusive mode;
delete from pub.xxpctrod_det where xxpctrod_nbr in
(select xxpctro_nbr from pub.xxpctro_mstr where xxpctro_memo_date
delete from pub.xxpctro_mstr where xxpctro_memo_date

Step 4: (Final, also breaks)

--copyNewData
lock table pub.xxpctro_mstr in exclusive mode;
lock table pub.xxpctrod_det in exclusive mode;

insert into pub.xxpctro_mstr
(xxpctro_nbr, xxpctro_date,
xxpctro_cust_ord, xxpctro_memo_date, xxpctro_memo_nbr, xxpctro_cust, xxpctro_mcn,
xxpctro_sold_to, xxpctro_ship_to, xxpctro_bol_date, xxpctro_curr, xxpctro_max_memo, xxpctro_distr,
xxpctro_wh, xxpctro_add_dt, xxpctro_add_tm, xxpctro_add_userid, xxpctro_add_prgm)
select distinct xxsor_nbr, xxsor_entered_date, xxsor_cust_ro_rbr, xxsor_crd_memo_dt,
xxsor_crd_memo_nbr, xxsor_cust_one, xxsor_mcn_one, xxsor_leg_soldto_one, xxsor_leg_shipto_one,
xxsor_bol_date, xxsor_cur, xxsor_crd_memo_nbr,
substring(xxsor_leg_shipto_one, 3, 4),
substring(xxsor_leg_shipto_one, 1, 2),
sysdate,
[ActualDate], '[GLOBAL_ID]', 'CCSPCCProcess'
from pub.xxsor_mstr inner join pub.xxmcnwp_mstr on (xxsor_mcn_one = xxmcnwp_mcn)
inner join pub.xxsord_det on (xxsor_nbr = xxsord_nbr)
inner join pub.xxfc_mstr on (xxsord_major_fault_code = xxfc_fault_code) 
where xxsor_crd_memo_dt between '[NewPeriodStart]' and  '[HistoryEndDate]' 
and xxmcnwp_pct_cust_crd = 1
and xxsor_status =  45
and xxsord_major_fault_code not in (select xxmstrcd_value from pub.xxmstrcd_mstr where xxmstrcd_code = 'NOT_ANALYZED_FC')
and xxfc_war_code = 1 and xxfc_activated = 1;

What is really weird is that this code had been working like this for almost 3 years and it is until now we have run accross this issue.

Would it be a lot of trouble if you can share an example with me of option 7?

I really appreciate your help