DB2/MFD/usage YYMD, actual DATE efficiencies
From : Paulin Nancy
                              I've been working with someone in another area in our company on their
                              Y2K focexec process - that is being changed partially due to an upgrade
                              in the underlying DB2 tables. I suggested that the USAGE of A10 should
                             be changed to YYMD in order to benefit from usage of smartdates. (That
                             also happens to be how our area describes all of their DB2 date fields.)

Following are his findings on processing time that surprised me and
concerned me because our area DOES use the YYMD usage for almost all
dates. 40 seconds versus 5 seconds is significant when considering that
this is based on a request against a very small test version of the real
data (eg. 10,000 records versus 5 million) Note that EFFDT is indexed,
in case that explains all.
Am I missing something, or is the Smartdate usage a sure but much slower
road for these DB2 dates?
Thanks!!
Nancy

EFFDT definition from master:
-------------------------------------------
FIELD=EFFDT ,EFFDT ,YYMD ,DATE
DESC='EFFECTIVE DATE FOR A JOURNAL',$

Table request:
---------------------
DEFINE FILE JRNLHIST
SDT1/YY=EFFDT;
END
TABLE FILE JRNLHIST
PRINT CORP SEGMENT
BY EFFDT
IF SDT1 EQ '1998'
END
-RUN

BUTTTT.... There is a problem....

The selection statement IF SDT1 EQ '1998' does not get translated to SQL
code. ALL years are returned to Focus, then the selection statement is
applied in Focus. Since JRNLHIST is over 5 million records/rows in the
Production environment, this could take a while.

The following is the SQL code generated from the program if it is
defined as a SMART date:

DEFINE FILE JRNLHIST
SDT1/YY=EFFDT;
END
TABLE FILE JRNLHIST
PRINT CORP SEGMENT
BY EFFDT
IF SDT1 EQ '1998'
END
-RUN

SELECT T1.CORP,T1.EFFDT,T1.SEGMENT FROM M3LLGLY.JRNLHIST T1
ORDER BY T1.EFFDT FOR FETCH ONLY;

The following is the SQL code generated from the program if it is
defined as A10:

TABLE FILE JRNLHIST
PRINT CORP SEGMENT
BY EFFDT
IF EFFDT FROM '1998-01-01' TO '1998-12-31'
END
-RUN

SELECT T1.CORP,T1.EFFDT,T1.SEGMENT FROM M3LLGLY.JRNLHIST T1
WHERE (T1.EFFDT BETWEEN '1998-01-01' AND '1998-12-31') ORDER BY
T1.EFFDT FOR FETCH ONLY;

NOTE: The 1998 in the above pgms is actually &CCYY from a -Crtform.

The difference in speed of the 2 programs running against the TEST DB2
tables loaded with about 10,000 records/rows is:

SMART Date : 40 secs.
Alpha 10 : 5 secs.

I totally backed out of the Focus environment, and re-entered between
tests. I also performed the tests several times with the same results.
The tests were performed at 10:45 am to 11:15am. AGAIN, note that the
JRNLHIST test DB2 table is only loaded with 10,000 records/rows. What
would happen if this was run against the production table with over 5
million.

Since these are mostly on-line reports, time makes a big difference.

If you have any other ideas, please let me know and I can check them
out. Thanks for this idea, it was well worth the shot.

Reply : From Mariani, Francis
Subject: DB2/MFD/usage YYMD, actual DATE efficiencies

If you try
WHERE EFFDT FROM '1998/01/01' TO '1998/12/31'
this should get translated to SQL and you will have an efficient program.
 

SmartDate Weirdness

From Dan Wilson

I'm creating a repository from a file which contains a multitude of dates.
As part of the process, I'm converting all the dates on the file to
smartdates, but have encountered an odd situation in testing.

Here's my define:
DATEOPEN is a database field, USAGE=P8, ACTUAL=P4, and contains dates in
either ymd or yymd format.
FILEDATE is being passed via an &-variables as yym.
CLOSEDT is a database field, USAGE=A4, ACTUAL=A4, and contains either
'0000' or a ym date.

DEFINE FILE FILENAME
-*-------------------------------------------------------------------
-* BUILD OPEN DATE AS A SMART DATE
-*-------------------------------------------------------------------
OD_9000 / A08 = EDIT (DATEOPEN) ;
OD_9001 / A02 = EDIT (OD_9000,'99$$$$$$') ;
OD_9002 / A02 = EDIT (OD_9000,'$$99$$$$') ;
OD_9003 / A06 = EDIT (OD_9000,'$$999999') ;
OD_9004 / A02 = IF OD_9001 NE '00' THEN OD_9001 ELSE
IF ((OD_9001 EQ '00') AND
(OD_9002 GT '45')) THEN '19' ELSE '20' ;
OD_0001 / A08 = OD_9004||OD_9003 ;
OD_0002 / A08YYMD = OD_0001 ;
OD_0003 / YMD = OD_0002 ;
CU_OPEN_DTE / MDYY = OD_0003 ;
CUAH_OPEN / YYM = CU_OPEN_DTE ;
-*------------------------------------------------------------------
-* BUILD FILE DATE AS A SMART DATE
-*------------------------------------------------------------------
FD_0001 / A06 = '&FILEDATE' ;
FD_0002 / A06YYM = FD_0001 ;
FILEDATE / YYM = FD_0002 ;
-*------------------------------------------------------------------
-* BUILD CLOSE DATE AS A SMART DATE
-*------------------------------------------------------------------
CD_0001 / A04YM = CLOSEDT ;
CD_0002 / YM = CD_0001 ;
CU_CLS_DTE / YYM = CD_0002 ;
-*------------------------------------------------------------------
-* SET FLAGS BASED ON RELATIONSHIPS BETWEEN OPEN/CLOSED AND CURRENT
-*------------------------------------------------------------------
CU_CLOSED_TM / P08 = IF FILEDATE EQ CU_CLS_DTE THEN 1
ELSE 0 ;
OPENED_TM / P08 = IF FILEDATE EQ CUAH_OPEN THEN 1
ELSE 0 ;
ACTIVE_TM / P08 = IF ((FILEDATE GE CUAH_OPEN) AND
(CLOSEDT EQ '0000')) THEN 1
ELSE 0 ;
END
-*
For testing, I'm just printing all the fields to verify that they're
'converting' properly.

TABLEF FILE FILENAME
PRINT DATEOPEN
OD_0001
CU_OPEN_DTE
CUAH_OPEN
FILEDATE
CLOSEDT
CU_CLS_DTE
END

Here's a sample of the output.

DATEOPEN OD_0001 CU_OPEN_DTE CUAH_OPEN FILEDATE CLOSEDT CU_CLS_DTE
-------- ------- ----------- --------- -------- ------- ----------
860501 19860501 05/01/1986 1986/05 1998/01 0000 1986/05
820701 19820701 07/01/1982 1982/07 1998/01 0000 1982/07
860501 19860501 05/01/1986 1986/05 1998/01 0000 1986/05
820701 19820701 07/01/1982 1982/07 1998/01 9708 1997/08
611001 19611001 10/01/1961 1961/10 1998/01 9710 1997/10
710201 19710201 02/01/1971 1971/02 1998/01 0000 1971/02
820701 19820701 07/01/1982 1982/07 1998/01 0000 1982/07
650901 19650901 09/01/1965 1965/09 1998/01 0000 1965/09
710301 19710301 03/01/1971 1971/03 1998/01 0000 1971/03
710301 19710301 03/01/1971 1971/03 1998/01 9709 1997/09

The open date fields all look OK, but when I build CU_CLS_DTE,
it appears that if CLOSEDT EQ '0000' the field is filling with CUAH_OPEN.
I suspect it has something to do with FOCUS confused over processing
'0000' as a smartdate, but can someone explain what's happening and
how to fill the field properly? I'm expecting to see blanks or spaces when
I
print the smartdate...not sure if these are stored internally as a 0,
low-values,
alpha spaces, or what....

Thanks in advance.

Reply : From Noreen Marie Redden (IBI )
Re: SmartDate Weirdness
Dan, this certainly looks like a problem to me, but I think it is one we
fixed. What release are you in? (and of course, what platform). You might
want to open up a call with Customer Support 1-800-736-6130 and, if we
have a PTF available we can ship it right to you, or we can open it up as
a problem with programming
 

YRT not working in Modify

From: Enderle, Karen
Setting DFC and YRT on a computed field in a modify does not appear to
be working. I am testing this in a level 2 VM/FOCUS 7.08 environment
where the system date is set to 01/04/2000. The FOCUS 7.08 PTF has been
applied (thanks Denise for your ? PTF suggestion yesterday).

I followed the instructions from a previous FOCUS-L message posted on
1/27.

My code is as follows:

MODIFY FILE MAINCOMB

COMPUTE

DATE/A8 =;
DATEA/I6MDY =;
CURDT/MDYY DFC 19 YRT 92 =;

CASE AT START
DATE = TODAY('A8');
DATEA = EDIT(EDIT(DATE,'99$99$99'));
CURDT = DATEA;

Results are as follows (DFC is always = 19):

If the global setting for YRT = 0 then I get 01/04/1900
If the global setting for YRT=51 then I get 01/04/2000

This appears to me that the field level setting is not being recognized
only the global setting. This is true when I compile the modify too.

Any suggestions?

Reply From: "B.J. Stepien"
Subject: Re: YRT no working in Modify
I've testing this and this is a 'problem', please open up a case
with Customer Support, and tell them to transfer the case to me.
B.J.,

Y2K

I've been given a copy of a year 2000 document to review for FOCUS. It
states that FOCUS 6.8 is not yet regarded as year 2000 compliant. Would
anyone out there say that it is year 2000 compliant due to it being able
to store and process dates that are stored in 8 bytes (eg dmyy), or is it
the case that many features of FOCUS just wouldn't work in year 2000
anyway?
Reply From: "B.J. Stepien"
Subject: Re: y2k

Keith,
FOCUS version 6.8 does NOT contain any of our PROJECT 2000 world.
IF --- IF --- IF you were only accessing FOCUS file -- every single
MFD, file, apllication and input screen used 8 character dates ---
Hey, -- you would be one of the few people that absolutely had no
Y2K problems. BUT -- given the real world, people use FOCUS to access
non-FOCUS/legacy systems -- date are stored in 6 characters ---
and then you have the question of what does the value '00' mean
in a date field --- is it 1800, 1900, 2000 ?????
One has to do a complete analysis of all of the FOCUS usage at the
site, current files accessed, current reports prodiced, input and
display screens. Chances are, you will realize that you need to
upgrade to release 7.0(7.0.8, is the recommended release).
B.J.

Reply From: Petter Stephen
Re :Subject: Y2K

Welcome to Focus-L. As a fellow-Brit (This group is 95% USA) may I say
it is a good idea to 'lurk' i.e. read postings in a mail group for a few
days before plunging in with a question. If you did that with Focus-L
you'd realise that there has been much on Y2K compliance. There is a URL
that archives old mail, but I do not have it to hand.

I have made my Focus application Y2K compliant partly by eliminating all
'fancy' date manipulation, because when I started the situation was much
less clear than now.

You need to understand Focus's date handling, basically it offers two
methods. The 'smart date' method is where the field is defined e.g.
START_DATE/DMY or YMD or YYMD (which is what I try to move everything
to). These dates are Y2K Compliant.

The other method is where you keep a numeric or an alphanumeric variable
(e.g. I6 or A8) and tell Focus it is a date by adding DMY or YMD or MDY
etc. e.g. I6DMY is 6 numerics to be regarded as ddmmyy. These need
attention, but Focus can handle them sensibly by means of two new
variables, &YRTHRESH and &DEFCENT. The first defines a threshold - a yy
year lower than it is regarded as in next Century, above it is the
default/previous century. Most people have DEFCENT = 19 and YRTHRESH at
70. So DMY of 251271 is 1971-12-25 and 251269 is 2069-12-25. (I STRONGLY
advocate holding dates according to the International standard i.e.
yyyymmdd which in Focus-speak is YYMD)

All is pretty well automatic and Y2K compliant EXCEPT the User Written
Subroutines (UWS) especially DOWK (the Day of the Week) routines. These
are to be made OK by release 7.0.8. (I refer only to MVS Focus).

That's my understanding. Here's an extract from a recent authoritative
posting:

(In the following quote D.M. is for Dialogue Manager. His quiet aside
re D.M. raised a bit of a storm)

QUOTE:
When you say 'Y2K compliant', that has several levels of meaning. As
written,
all the UWS will accept 4 digit years and product the correct result
(with a
4 digit year, except the DAxxx and DTxxx routines, which have been
re-written.
So, if you provide 4 digit years, they're already compliant.

A second level of 'compliance' is the ability to use DEFCENT and
YRTHRESH,
when converting from a 2 digit year to a 4 digit year. These are FOCUS
setting,
and, as such, the UWS had no access to them. We have modified them to
recognize
DEFCENT and YRTHRESH, and re-written the DA and DT routines to support 4
digit
years. This support is available with the 7.0.8 release, with a PTF
needed to
provide D.M. support.

In addition, we've modified the routines, such that, if you specify the
format
of the return value, you can control how many 'year' digits are
returned, while
the subroutines will use 4 digit year processing internally.
END OF QUOTE

Reply From: "Enderle, Karen, HMR/US"
Subject: Re: Y2K

My understanding of YRTHRESH and DEFCENT is that they will not work
directly with old date formats (I6MDY and A6MDY). YRTHRESH and DEFCENT
only come into play when you convert an old date to a smart date.

Reply From: Noreen Marie Redden ( IBI )
Subject: Re: y2k

Keith: The problem is in the definition of Y2K compliant, which seems to
mean lots of different things to lots of people. Certainly, in release 6.8,
if you always used our "smart dates", and always used 4 digit years (YYMD, etc.
then your application is Y2K compliant (at least I think so although I am
not a lawyer(solicitor??)). When we say that 6.8 is not Y2K compliant that
is because we do not have a sliding window to determine a century when one
is not supplied by the user.

 CHGDAT in MVS FOCUS v7.07M vs v7.08

From: Jim Hannibal

We have several Focexecs with the User-Written Subroutine CHGDAT used in
Dialogue Manager to validate a 6-character MMDDYY input date from a
CRTFORM. (It is a simple check for a legitimate month and day and year
before proceeding with the TABLE requests, etc.)

It works perfectly fine for our purposes using v7.07M except the MMDDYY of
022900 is not valid. Since CHGDAT in v7.07M does NOT use DEFCENT and
YRTHRESH settings, this is interpreted by CHGDAT as February 29, 1900 -
which is NOT a leap year.

Can someone tell me whether v7.08 does now look at 022900 as February 29,
2000 if the DEFCENT is set at 19 and the YRTHRESH is something other than
00?
Reply From: Randy Gleason
Subject: CHGDAT in MVS FOCUS v7.07M vs v7.08

IBI can always give you the official word but on something this
simple I bet if you posted a reasonably complete piece of code
(based upon the car file) you'd get a lot of folks willing to
try it out on their 7.08 implementation.
 

Question continues
From: Jim Hannibal, Contractor

Subject: Re: CHGDAT in MVS FOCUS v7.07M vs v7.08.

Here is the code for someone to clip and test using v7.08.
It uses only Dialogue Manager. No data files need to be allocated.
A couple of work-arounds are in the code, but commented out.
We're only interested to know if the focexec works, as is, in v7.08 when
022900 is entered.

-*********************************************************************
-* CAUTION: DEFCENT & YRTHRESH REMAIN AS SET UNTIL RESET OR EXIT FOCUS
SET YRTHRESH = 30
SET DEFCENT = 19
-RUN
-SET &BEGDATE = 'MMDDYY';
-STARTUP
-CRTFORM BEGIN
-" TEST FOR DIALOG MANAGER CHGDAT PROCESSING"
-" ENTER BEGDATE (MMDDYY): -" ENTER TO PROCESS OR PF01 TO EXIT"
-" <.HD.&SCRMSG1/70>"
-" <.HD.&SCRMSG2/70>"
-" CAUTION: DEFCENT & YRTHRESH REMAIN AS SET UNTIL RESET OR EXIT FOCUS"
-CRTFORM END
-SET &CURSOR = 'BEGDATE';
-* THE FOLLOWING "-IF" WORK-AROUND IS OK, BUT NOT EXPLICIT TO YEAR 2000:
-* -IF &BEGDATE EQ '022900' GOTO GOODDATE;
-SET &STMO = EDIT (&BEGDATE, '99$$$$');
-SET &STDY = EDIT (&BEGDATE, '$$99$$');
-SET &STYR = EDIT (&BEGDATE, '$$$$99');
-* FOLLOWING 4 LINES ADD CENTURY BASED ON DEFCENT AND YRTHRESH SETTINGS:
- ? SET YRTHRESH &GETYRT
- ? SET DEFCENT &GETDFC
-SET &STCN = IF &STYR LT &GETYRT THEN &GETDFC+1 ELSE &GETDFC;
-SET &STYYMD = &STCN||&STYR||&STMO||&STDY;
-SET &ALBEGDAT =&BEGDATE||'X';
-SET &CKBEGDAT =CHGDAT(MDY, MDY, &ALBEGDAT, 'A17');
-* FOLLOWING 2 "-SET"S USE DEFCENT AND YRTHRESH TO WORK AROUND 022900:
-* -SET &ALBEGDAT =&STYYMD||'X';
-* -SET &CKBEGDAT =CHGDAT(YYMD, YYMD, &ALBEGDAT, 'A17');
-IF &CKBEGDAT NE ' ' GOTO GOODDATE;
-SET &SCRMSG1 = 'INVALID BEGINNING DATE - MUST BE VALID MMDDYY';
-GOTO STARTUP
-GOODDATE
-SET &SCRMSG1 = ' ';
-CRTFORM
-" *** GOOD DATE **** "
-" BEG DATE (MMDDYY):  -" FULL DATE WITH DEFCENT -" CCYYMMDD = -" (PRESS ENTER) "
-" CAUTION: DEFCENT & YRTHRESH REMAIN AS SET UNTIL RESET OR EXIT FOCUS"
-CRTFORM END
-SET &SCRMSG1 = ' ';
-GOTO STARTUP;
-*********************************************************************
SUBJECT: Re: CHGDAT in MVS FOCUS v7.07M vs v7.08

On MVS Focus 7.08 with PTF79580 applied this routine says 022900 is
a valid date. On VM Focus 708 WITHOUT PTF79580 this routine says
022900 is an invalid date. Looks like this ptf is required for
this routine to work correctly.

Randy Gleason - Internet(GRGLEAS@PPCO.COM)
Subject: CHGDAT in MVS FOCUS v7.07M vs v7.08

We have several Focexecs with the User-Written Subroutine CHGDAT used in
Dialogue Manager to validate a 6-character MMDDYY input date from a
CRTFORM. (It is a simple check for a legitimate month and day and year
before proceeding with the TABLE requests, etc.)
It works perfectly fine for our purposes using v7.07M except the MMDDYY of
022900 is not valid. Since CHGDAT in v7.07M does NOT use DEFCENT and
YRTHRESH settings, this is interpreted by CHGDAT as February 29, 1900 -
which is NOT a leap year.
Can someone tell me whether v7.08 does now look at 022900 as February 29,
2000 if the DEFCENT is set at 19 and the YRTHRESH is something other than
00?
 

Reply From: Noreen Marie Redden ( IBI )
Subject: Re: CHGDAT in MVS FOCUS v7.07M vs v7.08
 

Jim: this works in 7.0.8, so long as the site has installed our
"unsolicited PTF" which was sent to everyone who's received 708. Noreen