Capturing program execution code

There is a program called SQL Profiler that comes with SQL Server and is probably already installed. It is very easy to use. You may need the cooperation of your DBAs.

This is one of my specialties :)
What process is it that is causing you problems?
Here are the two primary tools I use at a high-level to track things down.
1. SQL Server activity monitor, etc. can be used to capture what's going on, and there are some queries you can use to locate problematic SQL.
2. Lawson's timestats utility is pretty useful for determining where bottlenecks are, and if you can locate an issue, you can usually figure out a different process and/or get Lawson to issue a patch to fix a specific performance issue (i.e. not using the right index, etc.

The job I'm trying to decipher is AC190 - Activity Posting.  Prior to going to LSF9, this job usually took less than 2 hours to complete.  Since going to LSF9, this job now takes up to 9 hours to run, sometimes longer.  Now granted, the environment hardware configuration is quite different on LSF9.  The old environment had the applications and SQL-Server 2000 database co-located on the same server.  We were also using a case-insensitive collation on the database.  Since going to LSF9, we've moved the database to SQL-Server 2005 on it's own dedicated server, and are also using the correct collation (Latin1_General_BIN).  The applications and web products reside on another box and it's my guess that breaking things up like this has introduced network latency into the mix, resulting in the job slowdown.  Our database is quite large (300+ gb) and I'm sure that has a lot to do with it, but the database was about the same size on the old environment too.

I want to find out what this AC190 job is doing and possibly write my own custom SQL that does the same thing, just more efficiently.  Now, I know this is not supported by Lawson, but I've done it before and quite successfully.  We've considered using the SQL-Profiler to capture what is going on but my DBAs complained that the captured data was too large to store (gigabytes of data supposedly).  Maybe if we pare down what exactly is captured, we might have a better shot of getting the info I need.

Trust me, you will not want to tackle a SQL version of AC190.
Go back and read where we discussed this before:
https://www.lawsonguru.co...v/topic/Default.aspx
You should use the timestats utility to capture the statistics for the AC190 job, and my guess is still that it's this:
When AC190 runs for that large of a population of activities, it creates a HUGE workfile, and requires locks on every activity, etc. In other words, if even only ONE transaction is postable in ACTRANS, and you run AC190 for all activity groups, etc. it will take a ton of resources to post that ONE transaction. I have a utility that I wrote that will alleviate this problem, if you're interested I can send you more info...

John,

Checking the job definition for AC190, we do run it for all groups. We currently have 2,872,331 activities in the ACACTIVITY table and this grows by several thousand activities monthly. Of those 2.7 million records, only 21k of them are in a posting status. The majority of new activity records are opened and update to closed status within 24 hours.

I would be very interested in the utility you're speaking of. Please either e-mail me or give me a call at (316) 858-6615. Thanks!

I'm going to make another suggestion that may help - a small customization that you can do with SQL.
The problem with AC190 is that it doesn't use the indexes very intelligently. I've found (on smaller systems, granted) that a simple select from actrans of all unposted records returns in seconds. I've also found that when I run AC190 for a list of activities, the program runs much much faster than when I run it for an entire group or sets of groups. So:
1) Create a manual list in MX10 with nothing in it (my example is called 'FAST;
2) Populate the list using sql with all activities with something to post (YSMV):
insert into db2lawp.mxlistmbr
( select 'ACTVY', 'FAST', acv.obj_id, acv.obj_id
from db2lawp.acactivity acv where acv.activity in
(select distinct activity from db2lawp.actrans where status = 1)
)
3) run the ac190 for the list.
I've found improvements of several orders of magnitude.
and I've touched nothing in Lawson.
Adam

Posted By Adam Jacobson on 08/21/2009 12:47 PM
1) Create a manual list in MX10 with nothing in it (my example is called 'FAST;
2) Populate the list using sql with all activities with something to post insert into db2lawp.mxlistmbr
( select 'ACTVY', 'FAST', acv.obj_id, acv.obj_id
from db2lawp.acactivity acv where acv.activity in
(select distinct activity from db2lawp.actrans where status = 1)
)
3) run the ac190 for the list.

That is essentially what the utility does that I am talking about...in addition to AC190 it can also be used for BR120, BR130, and a couple of other AC/BR programs.

Adam,

I took your suggestion and tried running an AC190 for a manual list I created in MX10. I named the list 'AC190-TEST'. I then ran the following SQL:

insert into PRODDEV.dbo.MXLISTMBR
select 'ACTVY', 'AC190-TEST', acv.OBJ_ID, acv.OBJ_ID
from PRODDEV.dbo.ACACTIVITY acv where acv.ACTIVITY in
(select distinct ACTIVITY from PRODDEV.dbo.ACTRANS where [STATUS] = 1)

The SQL identified identified 31,728 records. I then defined and ran AC190 job against my named list. When I ran the job, it completed succesfully but the output file indicated "No valid records to process".

Any suggestions?

Stand by....my posting date in the AC190 was wrong. Rerunning it now....

PEBCAK strikes again.

Adam / John,

I can't thank you guys enough. I ran an AC190 job on my underpowered DEV system using a SQL-generated list of Activities and posted 31,000 transactions in just over 2 hours. I can't imagine the time savings I should expect once I make implement these changes on my production system.

I owe you guys bigtime. Have a few on me!

- John

We will send you the bill.

YOur welcome. It is truly amazing that Lawson couldn't write AC190 in the correct way.
I mean, every single other posting program in the application looks at what records need to be posted. Ac190 seems to check each activity individually for possible posting records - even if many of these activities have been closed since 6.1

Before you get generous, though, know that I live in NYC and a few drinks makes LPS look inexpensive.