Conference ilbbak::ibi_focus

Hi Wayne,
The fact that you're using ADT looks like the field is an RdB date field.
An example of the .acx & .mas formats for a date field are below.
Regards,
Dave
 
Master file Format for date field in RdB

  SEGNAME=INCIDENT,SEGTYPE=S0,$
      FIELDNAME=INCIDENT_NUM  ,ALIAS=INCIDENT_ID   ,A3      ,A3         ,$
      FIELDNAME=DATE          ,ALIAS=DATE          ,YMD     ,DATE       ,$


.ACX Format for date field

SEGNAME=INCIDENT,TABLENAME=INCIDENT,$
     FIELD=INCID_TYPE,ALIAS=INCIDENT_TYPE,$
     FIELD=DATE,TYPE=ADT,$

    
    re .1
    
    Which version of FOCUS are you running ?
    
    FOCUS V5.2 doesn't seem to support this syntax from where I'm sitting.
    
    Regards - David
    

    re .1
    
    If you are running V5.2 the syntax for the .mas should be as follows
    
    FIELDNAME=DATE_DEACT   ,ALIAS=DEACT   ,MDY    ,A6   ,$
    
    When I tried using the word DATE in place of A6 it through my record
    length off by 2. Hope this helps.
    
    Kate

    The version we have is 5.2.2
    We specifically put in this version as FOCUS can then access RdB date
    fields properly thus using the RdB index on these date fields.
     

Hi gurus,

    After reading notes and testing some suggestions from them
    (27.2; 273.2; 295.3; 328 etc) I am still unable to speed up 
    processing of records joined through CALENDAR_DATE from one 
    RDB relation where this field is not indexed to another RDB 
    relation where it is and is also defined as an RDB DATE field.

    I have also tried to create a .FTM file from the first 
    relation with different formats of this field in USAGE (I8YYMD, 
    I8YYMD, YYMD, YMD etc.) and in ACTUAL (DATE, A8 etc).

    Having in .ACX this field with the TYPE=ADT I have joined the 
    .FTM file to the RDB relation (with the indexed CALENDAR_DATE 
    field) and then in the TABLE statement selected and printed 
    records to a new .FTM file not using any pre-defined fields 
    in IF statement .

    Unfortunately, despite all approaches I did not save any 
    processing time.

    So, how to speed up the processing in this situation ???

Thanks,

Mike.
    

    Mike,
    
    I'm replying to this based on experience and not knowledge, but FWIW :
    
    Are the majority of your IF tests screens off of the GUEST file in the
    join ?  If so, you may get better performance by reversing the HOST/
    GUEST relationship in your join statement (if possible).  I've had
    some response time gains in the past using this approach.  Also,
    you might try creating a hold file using your Indexed relation
    first, incorporating as many screens that you can and then perform
    the join.
    
    Maybe you've already tried these, but if not cross your fingers and
    give it a shot.  
    
    Sharon

Mike,

	[Answering completly this question would lead me to spend
	a large amount of time, (which I haven't got right now)
	however let me try to give you some directions and if
	it isn't sufficient I'll be glad to complete this]

	First of all, and it isn't probably a secret for you,
	because FOCUS date processing does not include the time
	portion, this is causing some problems (...) with DATE
	handling for the interfaces (either RMS/FOCSAM or RDB)

	The reason is, that FOCUS matches DATE field on the date
	portion only, causing the RdB interface for example to
	generate very often SUBSTR function comparison (at the
	BLR level). Unfortunately the resulting request pass by
	FOCUS to RDB might sometimes caused the optimizer not
	to be able to select indeces properly.


	Fortunately there some usefull tools that can help you
	knowing what's going on :

	the logical name RDMS$DEBUG_FLAGS if set to :

		S	will cause the optimizer to display the
			strategy selected (Indeces ... etc.)

		B	will display the BLR Request (Yes you can
			look at everything that FOCUS generates
			not easy to read though)

	(By the way this is describe in one of the RdB manual discussing
	tuning)

	Now to be practical :

	1)	If the two date fields are only used for doing
		the JOIN, e.g. you do not have an IF condition
		on the root one, which would not be optimize
		anyway since there are no index, and if the
		date field are equal (and you are not looking
		for a date portion match only).

		The best way is to let FOCUS think these two
		fields should not be treated as date, but process
		as raw data. If I remember correctly the way to
		do this is :

		in the master

		FIELD, USAGE=A8, ACTUAL=A8,$

		in the access file

		FIELD, TYPE=ADT,$

		Then you can eventually use the FUSELIB routines
		to convert this to a printable form.

		Note: In order to optimize the processing you have
		to make sure that RdB will perform the JOIN and not
		FOCUS.
		In all cases developper sould always make sure that
		the retrieval strategy selected by RdB is always the
		one they expect.

	2)	Another trick. If you need to apply IF test on the
		source field, you can eventually describe it twice
		in your master file with a different format (FOCUS DATE)
		an apply the test to this one.


	3)	I do not think that doing an extract from the root
		table is going to be helpfull here. However there
		are cases where this solutions should be investigated.


Can't spend much more time. Hope this will be helpfull.
Jean-Claude