Replacing linked tables with single command object CR9

[chrislarkin]

Hi --

A report for customizations in Lawson takes hours to run. On closer inspection, it appears that in addition to formulas that need to be rewritten, the query itself is very slow.

The generated SQL looks like:

SELECT DBACACV.DBACV_DESCRIPTION, DBACACV.DBACV_BILLABLE_FLAG, DBIFGLN1.DBGLN_DESCRIPTION, DBACACV.DBACV_ACTIVITY_GRP, DBGLGLT2.DBGLT_ACTIVITY, DBGLGLT2.DBGLT_ACCOUNT, DBGLGLT2.DBGLT_ACCT_UNIT, DBGLGLT2.DBGLT_TRAN_AMOUNT, DBGLGLT2.DBGLT_POSTING_DATE, DBGLGLT2.DBGLT_ACCT_PERIOD, DBGLGLT2.DBGLT_REFERENCE, DBGLGLT2.DBGLT_DESCRIPTION, DBGLGLT2.DBGLT_FISCAL_YEAR, DBGLGLT2.DBGLT_COMPANY, DBGLGLT2.DBGLT_SOURCE_CODE, DBACAJV2.DBAJV_MX_VALUE, DBACAJV2_1.DBAJV_MX_VALUE, DBACAJV2.DBAJV_MATRIX_CAT, DBACAJV2_1.DBAJV_MATRIX_CAT
FROM {oj (((LAWDBF7.DBGLGLT DBGLGLT2 INNER JOIN LAWDBF7.DBACACV DBACACV ON DBGLGLT2.DBGLT_ACTIVITY=DBACACV.DBACV_ACTIVITY) LEFT OUTER JOIN LAWDBF7.DBIFGLN DBIFGLN1 ON (DBGLGLT2.DBGLT_COMPANY=DBIFGLN1.DBGLN_COMPANY) AND (DBGLGLT2.DBGLT_ACCT_UNIT=DBIFGLN1.DBGLN_ACCT_UNIT)) INNER JOIN LAWDBF7.DBACAJV DBACAJV2 ON DBACACV.DBACV_OBJ_ID=DBACAJV2.DBAJV_OBJ_ID) INNER JOIN LAWDBF7.DBACAJV DBACAJV2_1 ON DBACAJV2.DBAJV_OBJ_ID=DBACAJV2_1.DBAJV_OBJ_ID}
WHERE DBACAJV2.DBAJV_MATRIX_CAT='PRBRANCH' AND DBGLGLT2.DBGLT_ACCT_PERIOD=4 AND DBGLGLT2.DBGLT_REFERENCE<>'** ACCRUAL' AND NOT (DBGLGLT2.DBGLT_ACCOUNT=14998 OR (DBGLGLT2.DBGLT_ACCOUNT>=30000 AND DBGLGLT2.DBGLT_ACCOUNT<=39999)) AND DBGLGLT2.DBGLT_FISCAL_YEAR=2006 AND DBGLGLT2.DBGLT_COMPANY=100 AND DBACACV.DBACV_BILLABLE_FLAG='Y' AND DBACACV.DBACV_ACTIVITY_GRP<>'NONREVENUE' AND DBGLGLT2.DBGLT_SOURCE_CODE='JB' AND DBACAJV2_1.DBAJV_MATRIX_CAT='SHOWEND'

This is on DB2 v5.2 for the AS400. After studying the DB2 cookbook for this old release, I got my code to:

SELECT
DBACACV.DBACV_DESCRIPTION,
DBACACV.DBACV_BILLABLE_FLAG,
DBIFGLN1.DBGLN_DESCRIPTION,
DBACACV.DBACV_ACTIVITY_GRP,
DBGLGLT2.DBGLT_ACTIVITY,
DBGLGLT2.DBGLT_ACCOUNT,
DBGLGLT2.DBGLT_ACCT_UNIT,
DBGLGLT2.DBGLT_TRAN_AMOUNT,
DBGLGLT2.DBGLT_POSTING_DATE,
DBGLGLT2.DBGLT_ACCT_PERIOD,
DBGLGLT2.DBGLT_REFERENCE,
DBGLGLT2.DBGLT_DESCRIPTION,
DBGLGLT2.DBGLT_FISCAL_YEAR,
DBGLGLT2.DBGLT_COMPANY,
DBGLGLT2.DBGLT_SOURCE_CODE
,DBACAJV2.DBAJV_MX_VALUE, DBACAJV2.DBAJV_MATRIX_CAT, DBACAJV2_1.DBAJV_MX_VALUE, DBACAJV2_1.DBAJV_MATRIX_CAT

FROM
LAWDBF7.DBGLGLT DBGLGLT2 LEFT OUTER JOIN LAWDBF7.DBIFGLN DBIFGLN1 ON (DBGLGLT2.DBGLT_COMPANY=DBIFGLN1.DBGLN_COMPANY)
AND (DBGLGLT2.DBGLT_ACCT_UNIT=DBIFGLN1.DBGLN_ACCT_UNIT)
INNER JOIN LAWDBF7.DBACACV DBACACV ON DBGLGLT2.DBGLT_ACTIVITY=DBACACV.DBACV_ACTIVITY
INNER JOIN LAWDBF7.DBACAJV DBACAJV2 ON DBACACV.DBACV_OBJ_ID=DBACAJV2.DBAJV_OBJ_ID
INNER JOIN LAWDBF7.DBACAJV DBACAJV2_1 ON DBACAJV2.DBAJV_OBJ_ID=DBACAJV2_1.DBAJV_OBJ_ID

WHERE
DBGLGLT2.DBGLT_REFERENCE<>'** ACCRUAL' AND
(DBGLGLT2.DBGLT_ACCOUNT<>14998 AND
(DBGLGLT2.DBGLT_ACCOUNT<30000 OR DBGLGLT2.DBGLT_ACCOUNT> 39999)) AND
DBGLGLT2.DBGLT_COMPANY=100 AND DBACACV.DBACV_BILLABLE_FLAG='Y' AND
DBACACV.DBACV_ACTIVITY_GRP<>'NONREVENUE' AND
DBGLGLT2.DBGLT_SOURCE_CODE='JB' AND
DBACAJV2_1.DBAJV_MATRIX_CAT='SHOWEND' AND
DBACAJV2.DBAJV_MATRIX_CAT='PRBRANCH' AND
DBGLGLT2.DBGLT_ACCT_PERIOD=4 AND
DBGLGLT2.DBGLT_FISCAL_YEAR=2006

and added these statements to both versions to confirm the data coming through is the same:

ORDER BY DBACAJV2_1.DBAJV_MX_VALUE DESC
FETCH FIRST 250 ROWS ONLY

...........................

With the 250 row limit, I get the same data from both queries with the new query running in less than a minute and the old one about 3 minutes.

When I run the new query with 1000 rows limit, the query is done in maybe 2 minutes. With the old query, I could get 1000 rows in about 2 hours.
----------------------------

T H E P R O B L E M
___________________________

How can I map all of the old linked tables to just one query's result set? The links are no longer needed but I'm having problems....

Thanks in advance!

 

[muffntuf]

How can I map all of the old linked tables to just one query's result set? The links are no longer needed but I'm having problems....


You add a command object, run through all your formulas and then exchange the fields in the report with the fields from the command object.

Once you have done that and made sure all the database fields have been unchecked for usage, you can disconnect the tables in the Data Explorer and leave the command object in tack.

Time consuming - yes, but then you don't have to recreate everything from scratch. Or maybe you just build one from a new command object, might be faster. Depends on how complex your formatting is.

Yes, I have done the same exact thing a few times.

 

[synapsevampire]

Using a command object is yet another client based solution.

Consider using a View or a stored procedure on the database instead, that way everything you design is available to other tools as well.

-k

 

[chrislarkin]

Hi --

I am using views to simplify the command object down to a single select statement with parameters at the command object level.

I realized that if I made sure to include fields for all of the fields from the existing linked tables, CR will map the the corresponding same-named fields and offer options for the non-matching fields.

Now I've got the command object in place, but i'm having a problem with the eportfolio/cmc configuration and ODBC which I am posting into a new thread.