I am having a problem with this one report running too slowly. I don't know if I'm able to speed it up, but I'm looking for feedback. I believe the problem is that the field we have the where statement on is a datetime stamp. Since we don't know the specific time and we want to get a range of dates, it's my understanding that it ignores the index (I was told you need to do eq to use the index). I tried to reformat to a date and use eq but that's no quicker so I'm guessing the reformat doesn't use the index. We have an index of start_time and user_group. The database is db2 on the ibm mainframe.
I left some of the dates that I've tried but all of them take 10 minutes or more to return anything back to me. The amount of rows in the table is 70,000 which doesn't seem like a lot to me but we are pulling back all 70,000 (there is one month of data in the test table). We are summarizing it later, but we can't summarize until after we join the two tables together. Production has about 1,000,000 rows of data and we will pull back 70,000 approx.

Any suggestions will be appreciated.

SET ASNAMES=ON
SET ALL=ON
SET NODATA=' ';

-* USE THIS LATER TO PUT ZEROES IN THE TABLE FOR THOSE VALUES THAT ARE MISSING. THE WHERE STATEMENTS
-* DROP RECORDS THAT DON'T EXIST IN BOTH TABLES.
DEFINE FILE NBTBSAT
RPTTYPESORT/A35=IF DE_FAULT EQ 'VF' THEN 'VERIFIED FINANCIAL' ELSE
IF DE_FAULT EQ 'VAA' THEN 'VERIFIED ANALYTICS & ATTRIBUTION' ELSE
IF DE_FAULT EQ 'VP' THEN 'VERIFIED PERFORMANCE' ELSE DE_FAULT;
XORDER/I4=ORDER;
JOINFLD/A30=CODE|EDIT(XORDER);
END
TABLE FILE NBTBSAT
PRINT *
RPTTYPESORT
XORDER
WHERE NBTBSAT.TYPE EQ 'MGMT_RPT'
BY JOINFLD
ON TABLE HOLD AS HOLDSAT FORMAT FOCUS INDEX JOINFLD
END

DEFINE FILE NBTBTRC
STARTDATE/YYMD=HDATE(START_TIME,'YYMD');
YEAR/YY=STARTDATE;
MONTH/M=STARTDATE;
STARTDATE1/MDYY=STARTDATE;
STARTDATEM/M=STARTDATE;
STARTDATEYY/YY=STARTDATE;
OPTIONPDF/I8 = IF (POSIT(OPTION, 255, 'PDF', 3, 'I3')) GE 1 THEN 1 ELSE 0;
OPTIONHTML/I8 = IF (POSIT(OPTION, 255, 'HTM', 3, 'I3')) GE 1 THEN 1 ELSE 0;
OPTIONCSV/I8 = IF (POSIT(OPTION, 255, 'CSV', 3, 'I3')) GE 1 THEN 1 ELSE 0;
OPTIONEXCEL/I8 = IF (POSIT(OPTION, 255, 'EX', 2, 'I3')) GE 1 THEN 1 ELSE 0;
-* FIND THE POSITION OF THE TILDE, SUBTRACT 1 TO GET THE SORT ORDER.
TILDEPOS/I3 = POSIT(OPTION, 255, '~', 1, 'I3');
SORTPOS/I3=TILDEPOS - 1;
SORTORDER/A1=SUBSTR(255,OPTION,SORTPOS,SORTPOS+1,1,SORTORDER);
XSORTORDER/I4=IF SORTORDER EQ ' ' THEN 1 ELSE EDIT(SORTORDER);
JOINFLD/A30=REPORT_NAME|EDIT(XSORTORDER);
END

TABLE FILE NBTBTRC
PRINT
YEAR
MONTH
USER_GROUP
USERID
START_TIME
STOP_TIME
OPTION
REPORT_NAME
USER_GROUP
OPTIONPDF
OPTIONHTML
OPTIONCSV
OPTIONEXCEL
STARTDATE1
XSORTORDER
WHERE STARTDATE1 FROM '2006-09-01' TO '2006-09-30';
-*WHERE START_TIME GE '2006-09-01' AND START_TIME LE '2006-09-30';
-*WHERE START_TIME FROM DT(2006-09-01 00:00:00.000000) TO DT(2006-09-30 11:59:59.000000)
-*WHERE START_TIME GE DT(2006-09-04 00:00:00.000000);
-*WHERE START_TIME FROM DT(&DATEFROM 00:00:00.000000) TO DT(&DATETHRU 11:59:59.000000)
BY JOINFLD
-*ON TABLE HOLD AS HOLDTRC FORMAT FOCUS INDEX JOINFLD
END
-EXIT

Pam

Add this line of code to the top of your procedure so that you can see the SQL that WebFOCUS/FOCUS is generating and sending to DB2. Post you results and We will see if we can help

-******* Turn Off All Tracing
SET TRACEOFF=ALL
-******* Turn on SQL Trace and have it sent back to the browser
SET TRACEON=STMTRACE//CLIENT
-******* Trun on SQL Aggration Trace and have it sent back to the browser
SET TRACEON=SQLAGGR//CLIENT
-******* Turn on User Tracing
SET TRACEUSER=ON
-******* Do Not Send SQL to DB2
SET XRETRIEVAL = OFF

The lines with -* are just comments

Scott

Pam:

I see a few performance issues.

First, if you could use DB2 SQL to extract
what you need with as many WHEREs on
indexes and cluster keys, that would speed
up the initial extract. And, don’t create a
FOCUS HOLD file (see example below).

I didn’t see the TYPE field in your DEFINE, so I’m assuming it is a real DB field: WHERE NBTBSAT.TYPE EQ 'MGMT_RPT' ; WHERE clause on this should be fine. (I don’t see the
timestamp as part of this field or another; not sure what you mean.)

Example:

SQL DB2
SELECT *
FROM db.tablename
WHERE …
FOR FETCH ONLY WITH UR;
TABLE
ON TABLE HOLD AS HOLD1
END
-RUN

If the number of fields being extracted is
a few to less than 20, SELECT * may be
alright if the field sizes are 4-8 bytes
(like quantity/dollar columns). If it is
many more, with long description fields,
performance will be an issue. So, see if you
can select only what you need in the SQL
query.

At this point, you want to apply the
DEFINEs against HOLD1, then create a HOLD2
FOCUS file. (Things process faster from this
point on.)

From there you can JOIN, DEFINE etc. to do
your report.

Use the SET TRACE . . commands too with MSG=ON
and ECHO=ALL.

We use HOLD FORMAT FOCUS all the time during our
initial extract with (on average) 15-20 fields.
But, our row count is usually less than 100.

In your case with 70,000 rows, be careful what
you “stuff” into a FOCUS file. FOCUS is not DB2. If, say for example, your row length plus pointers is only putting 2 rows in a 4096K page, then 35K * 4096K is a huge amount of inefficient disk space just to create a index on a 30 byte field (index space also gets added into the 4096K space).

I hope this helps.

Call me if you need more assistance.

Jim
(443)-348-4219 (EST)

quote:

WHERE STARTDATE1 FROM '2006-09-01' TO '2006-09-30';

I'd be willing to bet that this is your culprit. Because you've got a where clause on a defined field, that DEFINE has to be evaluated for EVERY row in the NBTBTRC table before the WHERE can be applied. Think about how the WHERE clause works -- it's saying give me only values that are between these two dates. Focus doesn't know what STARTDATE1 is until it evaluates it.

I'm assuming that STARTDATE in the NBTBTRC table is the datetime you're talking about? Try something like this:

quote:

WHERE ( STARTDATE GE DT(2006-09-01 00:00:00) ) AND ( STARTDATE LE DT(2006-09-30 23:59:59) );

The idea here is that you're letting the DBMS do the work -- which in this case should be much more efficient. This still lets you define a range of dates, but you're passing a datetime constant back to the DBMS. And as far as having to use an EQ to make indexes work? I don't know why that would be the case -- most of my 10 years of SQL experience has been around MS SQL Server, but I really don't know of any reason why an equality would make an index work where something else would not...

For what it's worth, the DT() function only allows constants to be passed to it -- but an amper variable qualifies as a constant...

Hope that helps -- we've done similar stuff on our reports and I've gained SIGNIFICANT performance increases by using this technique where I had DEFINEs before.

As I looked closer at your original code, I see that you had something similar to what I posted but it was commented out -- did that not work?

Using WHERE ( STARTDATE GE DT(2006-09-01 00:00:00) ) AND ( STARTDATE LE DT(2006-09-30 23:59:59) ); worked 2 weeks ago but yesterday it wasn't working. I don't know what changed between yesterday and 2 weeks ago to have such a change in processing time. I'll continue testing next week with the suggestions I've been given and definitely change to using the database field instead of the defined field. Thanks for the suggestions. I'll give an update on what suggestions worked. Thanks.

Pam,

As TexasStingray mentioned above, you ought to trap the SQL that is being passed to DB2. Get that to the DBAs and ask them to perform a DB2EXPLAIN on it. This will tell them what access path is being used to retrieve your result set. Years ago you had to allocate a couple of DD statements in your JCL, the main one was FSTRACE4 which held the SQL. FSTRACE3 (I think) held the various SQL interactions.

Using FOCUS with DB2 has always been notorious and that is why I would always suggest prototyping a report and then trapping the SQL. Then using this to get the DBAs to build a DB2 View that would best suit your report. Believe me, it can help reduce processing time no end.

If your site allows it, you could always write the view within your code as an SQL passthru and achieve the same benefits as using a view.

For your process to suddenly take more time when it was OK obviously something has changed and if it's not your program then it is something DB2 related. Your DB2 DBAs should be able to perform runstats etc if the DB has got exceptionally large recently and they should be able to decide whether it needs to be partitioned etc.

All in all, I would suggest trapping the SQL and discussing with your DBAs - make them feel involved - you know they want to!

T

Pam, something worked two weeks ago and doesn't now? It could be that the format of the date-time field in the DB2 table has changed, or it could be that someone has regenerated the WebFOCUS metadata (Master) with a non-date-time field. What is the error?

YOu should trace your WebFOCUS code as T.S. has suggested.

Pam,

Did you get the SQL Trace this will tell you exactly what WebFOCUS/FOCUS is sending to DB2. If the where statement is not there then DB2 is reading everything, not a good thing.

Scott

I plan to work on this tomorrow. I had an install today for another application, so I didn't have time to work on this. I'll keep you posted.

Well, I tried doing the sqltrace and all the other commands I was suggested to do but I couldn't find any of the output files and it did not put the information in my console window (I run my code on eclipse and that displays my webfocus code and any errors). Our server person is out so I'm going to follow up with him and find out where that information goes on the server. I looked on my desktop and couldn't find it there either (I looked all through my IBI directory).

Anyway, when I ran today, I made sure my where statement was using the starttime field that was on the database (same as what I tried last week) and today it ran quickly. I was able to figure out the tags based on another focal point question so that I didn't have to make a ton of changes to our java code that passes the tags.
This is what I used.

quote:

WHERE START_TIME FROM DT(&DATEFROM 00:00:00.000000) TO DT(&DATETHRU 11:59:59.000000)

I did make sure I deleted all the fields from the defines that I was no longer using since that takes extra processing time.

I thank everyone for all the suggestions and education on the defines and the where statements. I really appreciate it.

I don't know what Eclipse is (mainframe?)

The SQL trace shows when you run the report with HTML as output format. From the web browser window, select "view source".

Thanks Francis, that worked. I was able to see the sql by changing the output to html and doing the view source.