Here is simple code that captures the execution of a program and records it in a file or database table. This can be used for usage analysis - a poor man's Resource Analyzer.

I include these three fexes in the program I want to track. One include at the beginning to capture the start time. One include at the end to capture the stop time and calculate the elapsed time. The third include is to write the info into a file or table.

I'm sure someone knows how to trigger execution of these from some kins of server or user profile, but to keep it simple, I just include these programs...

These are simplified versions of the code I use.

wftime1.fex

-*-- Capture Program Start Date and Time
-SET &P_ST_DATE = &YYMD;
-SET &P_ST_TIME = EDIT(HHMMSS('A8'),'99$99$99');

-TYPE
-TYPE ++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE Program Start Time: &P_ST_TIME
-TYPE ++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE

wftime2.fex

-*-- Capture Program End Date and Time
-SET &P_EN_DATE = &YYMD;
-SET &P_EN_TIME = EDIT(HHMMSS('A8'),'99$99$99');

-SET &P_EN_DATEX = EDIT(&P_EN_DATE,'9999/99/99');
-SET &P_EN_TIMEX = EDIT(&P_EN_TIME,'99:99:99');

-*-- Calculate Program Elapsed Time
-SET &DTTM1 = HINPUT(14, '&P_ST_DATE.EVAL&P_ST_TIME.EVAL', 14, 'HYYMDS');
-SET &DTTM2 = HINPUT(14, '&P_EN_DATE.EVAL&P_EN_TIME.EVAL', 14, 'HYYMDS');

-SET &ELAPSED_S = HDIFF(&DTTM2, &DTTM1, 'SECOND', 'D12');

-SET &ELAPSED_H  = &ELAPSED_S / 3600;
-SET &ELAPSED_MR = FMOD(&ELAPSED_S, 3600, 'F2');

-SET &ELAPSED_M = &ELAPSED_MR / 60;
-SET &ELAPSED_SR = FMOD(&ELAPSED_MR, 60, 'F2');

-SET &ELAPSED_H  = FPRINT(&ELAPSED_H,  'I4',  'A4');
-SET &ELAPSED_M  = FPRINT(&ELAPSED_M,  'I2L', 'A2');
-SET &ELAPSED_SR = FPRINT(&ELAPSED_SR, 'I2L', 'A2');

-TYPE
-TYPE ++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE Program End Time: &P_EN_DATEX &P_EN_TIMEX
-TYPE ++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE

-TYPE
-TYPE ++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE Program Elapsed Time: &ELAPSED_H hrs &ELAPSED_M mins &ELAPSED_SR secs
-TYPE ++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE

wftime3.fex (sql version)

-DEFAULTH &IBIMR_user = '';
-DEFAULTH &DIMENSION = '';
-DEFAULTH &TIME_DIMENSION = '';
-DEFAULTH &MEASURE = '';
-DEFAULTH &INFO_TEXT = '';
-DEFAULTH &ROW_COUNT = 0;
-DEFAULTH &P_ST_DATE = '20120626';
-DEFAULTH &P_ST_TIME = '160110';
-DEFAULTH &P_EN_DATE = '20120626';
-DEFAULTH &P_EN_TIME = '160115';

-SET &ST_DATEX = EDIT(&P_ST_DATE,'9999-99-99');
-SET &ST_TIMEX = EDIT(&P_ST_TIME,'99:99:99');

-SET &EN_DATEX = EDIT(&P_EN_DATE,'9999-99-99');
-SET &EN_TIMEX = EDIT(&P_EN_TIME,'99:99:99');

SQL MYSQL
INSERT INTO schema1.wftimestamp (FOCEXEC, USER_ID, DIMENSION, TIME_DIMENSION, MEASURE, INFO_TEXT, ST_DTTM, EN_DTTM, ROW_COUNT)
VALUES ('&FOCFOCEXEC', '&IBIMR_user', '&DIMENSION', '&TIME_DIMENSION', '&MEASURE', '&INFO_TEXT', '&ST_DATEX &ST_TIMEX', '&EN_DATEX &EN_TIMEX', &ROW_COUNT);
END
-RUN

wftimestamp (SQL table)

CREATE TABLE `wftimestamp` (
  `ROW_ID` int(11) NOT NULL AUTO_INCREMENT,
  `ROW_TIMESTAMP` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `FOCEXEC` varchar(66) DEFAULT NULL,
  `USER_ID` varchar(50) DEFAULT NULL,
  `DIMENSION` varchar(256) DEFAULT NULL,
  `TIME_DIMENSION` varchar(256) DEFAULT NULL,
  `MEASURE` varchar(256) DEFAULT NULL,
  `INFO_TEXT` varchar(256) DEFAULT NULL,
  `ST_DTTM` datetime DEFAULT NULL,
  `EN_DTTM` datetime DEFAULT NULL,
  `ROW_COUNT` int(11) DEFAULT NULL,
  PRIMARY KEY (`ROW_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=10889 DEFAULT CHARSET=latin1 COMMENT='WebFOCUS program elapsed time capture db'

wftime3.fex (FOCUS DB version)

-DEFAULTH &P_ST_DATE = '20120626';
-DEFAULTH &P_ST_TIME = '160110';
-DEFAULTH &P_EN_DATE = '20120626';
-DEFAULTH &P_EN_TIME = '160115';

MODIFY FILE WFTIMESTAMP
FREEFORM FOCFOCEXEC/20 USER_ID/8 ST_DATE/8 ST_TIME/6 EN_DATE/8 EN_TIME/6
DATA
FOCFOCEXEC=&FOCFOCEXEC,
USER_ID=&IBIC_user,
ST_DATE=&P_ST_DATE,
ST_TIME=&P_ST_TIME,
EN_DATE=&P_EN_DATE,
EN_TIME=&P_EN_TIME,$
END

wftimestamp file (FOCUS DB)

FILENAME=WFTIMESTAMP, SUFFIX=FOC, $
  SEGMENT=SEG01, SEGTYPE=S0, $
    FIELDNAME=FOCFOCEXEC, ALIAS=FOCFOCEXEC, FORMAT=A66, $
    FIELDNAME=USER_ID, ALIAS=USER_ID, FORMAT=A66, $
    FIELDNAME=ST_DATE, ALIAS=ST_DATE, FORMAT=A08YYMD, $
    FIELDNAME=ST_TIME, ALIAS=ST_TIME, FORMAT=A08, $
    FIELDNAME=EN_DATE, ALIAS=EN_DATE, FORMAT=A08YYMD, $
    FIELDNAME=EN_TIME, ALIAS=EN_TIME, FORMAT=A08, $

This message has been edited. Last edited by: Francis Mariani,

This is basically what we do as well.

Thanks for sharing!
Soon we will be able to set up a "Poor Man's" web store for these mini apps!

Hi Francis

Don't forget that if using a FOCUS db, you should be using the SU access for updates. Otherwise, and speaking from experience, you will get a corrupt file.

Also don't forget the AUTODATE feature on FOCUS db, automatic date/timestamp so you don't have to.

I don't use a FOCUS DB anymore, to avoid the concurrent update issue, but thanks for sharing your suggestions.

Converting "wftime3" to Oracle, I was working with this simple example:

 
SET SQLENGINE=SQLORA
ENGINE SQLORA SET DEFAULT_CONNECTION ODS

SQL SQLORA INSERT INTO ODSMGR.WFTIMESTAMP (ROW_ID, FOCEXEC, USER_ID,
 DIMENSION, TIME_DIMENSION, MEASURE, INFO_TEXT, ST_DTTM, EN_DTTM,
 ROW_COUNT) VALUES (ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL, 'WFTIME3',
 'USER1', 'DIMENSION', 'TIME_DIMENSION', 'MEASURE',
 'INFO_TEXT',NULL,NULL, 1);

END
-RUN

SQL SQLORA
COMMIT;
END
-RUN

EXIT

In TOAD, under our WebFOCUS Data Adapter user, I can successfully run the Insert statement. However, in this simple FEX, nothing will write. Am I missing something?

thanks for sharing this Francis
we use the Resource Analyzer, but this might be a good thing to keep in mind since RA has a big overhead in resource use

"nothing will write" - do you get an error message?
Also, I'm not sure about "ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL"... I've not used this syntax before.

quote:

Originally posted by Francis Mariani:
"nothing will write" - do you get an error message?
Also, I'm not sure about "ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL"... I've not used this syntax before.

No error message - that:

ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL 

is how Oracle uses sequences since there is no "auto-increment" datatype. The sequence was defined as:

CREATE SEQUENCE ODSMGR.WFTIMESTAMP_SEQ
  START WITH 1
  MAXVALUE 9999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;  

quote:

Originally posted by FrankDutch:
thanks for sharing this Francis
we use the Resource Analyzer, but this might be a good thing to keep in mind since RA has a big overhead in resource use

Indeed! I think this is a great example and thank you again for sharing it with us.

You must view the source to see what's going on. There should be a SQL message about rows inserted - zero or one...

Ok, I was able to get a real error message:

SQL
 INSERT INTO ODSMGR.WFTIMESTAMP (ROW_ID,
 FOCEXEC=IBFS:/WFC/ USER_ID, USER_ID, DIMENSION,
 TIME_DIMENSION, MEASURE, INFO_TEXT, ST_DTTM,
 EN_DTTM, ROW_COUNT) 
VALUES (ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL,
 'WFTIME3', 'WFUSER', 'DIMENSION',
 'TIME_DIMENSION', 'MEASURE',
 'INFO_TEXT',NULL,NULL, 1);

 END
 SQL
 COMMIT;
 END
 -RUN
 (FOC14011) SYNTAX ERROR MISSING CLOSE PARENTHESES ON LINE 34 AT '('
 EXIT
 (FOC1517) UNRECOGNIZED COMMAND EXIT

  

Looks like something is going astray with:

FOCEXEC=IBFS:/WFC/ USER_ID, USER_ID,...  

The source statement is:

SQL
INSERT INTO ODSMGR.WFTIMESTAMP 
(ROW_ID, FOCEXEC, USER_ID, DIMENSION,
 TIME_DIMENSION, MEASURE, INFO_TEXT, ST_DTTM,
 EN_DTTM, ROW_COUNT) VALUES
 (ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL, 'WFTIME3',
 'WFUSER', 'DIMENSION', 'TIME_DIMENSION',
 'MEASURE', 'INFO_TEXT',NULL,NULL, 1);

END  

??

This message has been edited. Last edited by: globalwm,

SQL INSERT INTO ODSMGR.WFTIMESTAMP (
ROW_ID, 
FOCEXEC=IBFS:/WFC/ USER_ID, 
USER_ID, 
DIMENSION, 
TIME_DIMENSION, 
MEASURE, 
INFO_TEXT, 
ST_DTTM, 
EN_DTTM, 
ROW_COUNT
) 

VALUES 
(
ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL, 
'WFTIME3', 
'WFUSER', 
'DIMENSION', 
'TIME_DIMENSION', 
'MEASURE', 
'INFO_TEXT',
NULL,
NULL, 
1
); 
END 

SQL COMMIT; 
END 
-RUN

It looks like WebFOCUS 8 is doing something weird to your SQL - The word FOCEXEC shouldn't have "IBFS:/WFC/ USER_ID" next to it. It looks like it assumes FOCEXEC is a reserved word and is automagically substituting the string...

Try changing the column name in the table and SQL INSERT statement.

That looks like it was causing that error. I changed the code and now when I run:

-SET &ECHO=ALL;
ENGINE SQLORA SET DEFAULT_CONNECTION ODS
SQL
INSERT INTO WFTIMESTAMP (ROW_ID, FOCNAME, USER_ID, DIMENSION, TIME_DIMENSION, 
MEASURE, INFO_TEXT, ST_DTTM, EN_DTTM, 
ROW_COUNT) 
VALUES (ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL,
'WFTIME3.FEX', 'WFUSER', 'DIMENSION',
'TIME_DIMENSION', 'MEASURE',
'INFO_TEXT',NULL,NULL, 1);
END
-RUN
SQL
COMMIT;
END
-RUN

EXIT
  

I get this error:

ENGINE SQLORA SET DEFAULT_CONNECTION ODS
 SQL
 INSERT INTO WFTIMESTAMP (ROW_ID, FOCNAME,
USER_ID, DIMENSION, TIME_DIMENSION, MEASURE,
INFO_TEXT, ST_DTTM, EN_DTTM, ROW_COUNT) VALUES
(ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL, 'WFTIME3.FEX',
'WFUSER', 'DIMENSION', 'TIME_DIMENSION',
'MEASURE', 'INFO_TEXT',NULL,NULL, 1);
 END
 -RUN
 0 ERROR AT OR NEAR LINE      5  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: WFTIMESTAMP
 SQL
 COMMIT;
 END
 -RUN
 EXIT
 (FOC1517) UNRECOGNIZED COMMAND EXIT

  

So I created a Synonym for the Table (didn't think I needed to with SQL Passthru?) and now it seems to choke on:

(FOC14010) NOT A VALID COLUMN NAME: WFTIMESTAMP_SEQ.NEXTVAL
  

Any options to overcome? Is their support for Sequence Pseudocolumns? This may shed some light:

http://forums.informationbuild...037049206#3037049206

This message has been edited. Last edited by: globalwm,

I would code the SQL statement as follows:

  
ENGINE SQLORA SET DEFAULT_CONNECTION ODS
SQL SQLORA
INSERT INTO ODSMGR.WFTIMESTAMP ...

quote:

SQL SQLORA

That worked!

-SET &ECHO=ALL;
ENGINE SQLORA SET DEFAULT_CONNECTION ODS
SQL SQLORA
INSERT INTO ODSMGR.WFTIMEST
(ROW_ID, FOCNAME, USER_ID, DIMENSION,
 TIME_DIMENSION, MEASURE, INFO_TEXT,
 ST_DTTM, EN_DTTM, ROW_COUNT) VALUES
 (ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL,
 'WFTIME3.FEX', 'WFUSER', 'DIMENSION',
 'TIME_DIMENSION', 'MEASURE', 'INFO_TEXT'
 ,NULL,NULL, 1);
END
-RUN
SQL SQLORA
COMMIT;
END
-RUN

EXIT  

Results:

ENGINE SQLORA SET DEFAULT_CONNECTION ODS
 SQL SQLORA
 INSERT INTO ODSMGR.WFTIMEST
 (ROW_ID, FOCNAME, USER_ID, DIMENSION,
 TIME_DIMENSION, MEASURE, INFO_TEXT,
 ST_DTTM, EN_DTTM, ROW_COUNT) VALUES
 (ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL,
 'WFTIME3.FEX', 'WFUSER', 'DIMENSION',
 'TIME_DIMENSION', 'MEASURE', 'INFO_TEXT'
 ,NULL,NULL, 1);
 END
 -RUN
 (FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND  : 1/INSERT
 SQL SQLORA
 COMMIT;
 END
 -RUN
 EXIT
 (FOC1517) UNRECOGNIZED COMMAND EXIT



  

Interesting, the 'SQLORA' command wasn't shown in all the v8 documentation I reviewed.

On the COMMIT - is that not needed?

Final results (Oracle version):

-SET &ECHO=ALL;

-DEFAULTH &IBIMR_user = '';
-DEFAULTH &DIMENSION = '';
-DEFAULTH &TIME_DIMENSION = '';
-DEFAULTH &MEASURE = '';
-DEFAULTH &INFO_TEXT = '';
-DEFAULTH &ROW_COUNT = 0;
-DEFAULTH &P_ST_DATE = '20130419';
-DEFAULTH &P_ST_TIME = '160110';
-DEFAULTH &P_EN_DATE = '20130419';
-DEFAULTH &P_EN_TIME = '160115';

-SET &ST_DATEX = EDIT(&P_ST_DATE,'9999-99-99');
-SET &ST_TIMEX = EDIT(&P_ST_TIME,'99:99:99');

-SET &EN_DATEX = EDIT(&P_EN_DATE,'9999-99-99');
-SET &EN_TIMEX = EDIT(&P_EN_TIME,'99:99:99');

ENGINE SQLORA SET DEFAULT_CONNECTION ODS
SQL SQLORA
INSERT INTO ODSMGR.WFTIMEST (ROW_ID, FOCNAME, USER_ID, DIMENSION, TIME_DIMENSION, MEASURE, INFO_TEXT, ST_DTTM, EN_DTTM, ROW_COUNT)
VALUES (ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL, '&FOCFOCEXEC', '&IBIMR_user', '&DIMENSION', '&TIME_DIMENSION', '&MEASURE', '&INFO_TEXT',
 TO_DATE('&ST_DATEX &ST_TIMEX','yyyy-mm-dd hh24:mi:ss'), to_date('&EN_DATEX &EN_TIMEX','yyyy-mm-dd hh24:mi:ss'), &ROW_COUNT);
 COMMIT;
END
-RUN

EXIT
  

Thanks so much for your help Francis!

Table create:

ALTER TABLE ODSMGR.WFTIMEST
 DROP PRIMARY KEY CASCADE;

DROP TABLE ODSMGR.WFTIMEST CASCADE CONSTRAINTS;

CREATE TABLE ODSMGR.WFTIMEST
(
  ROW_ID          INTEGER                       NOT NULL,
  ROW_TIMESTAMP   TIMESTAMP(9) WITH TIME ZONE,
  FOCNAME         VARCHAR2(66 CHAR),
  USER_ID         VARCHAR2(50 CHAR),
  DIMENSION       VARCHAR2(256 CHAR),
  TIME_DIMENSION  VARCHAR2(256 CHAR),
  MEASURE         VARCHAR2(256 CHAR),
  INFO_TEXT       VARCHAR2(256 CHAR),
  ST_DTTM         DATE,
  EN_DTTM         DATE,
  ROW_COUNT       INTEGER
)
TABLESPACE ODSMGR_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          160K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE ODSMGR.WFTIMEST IS 'WebFOCUS program elapsed time capture db';



CREATE UNIQUE INDEX ODSMGR.WFTIMESTAMP_PK ON ODSMGR.WFTIMEST
(ROW_ID)
LOGGING
TABLESPACE ODSMGR_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          160K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


ALTER TABLE ODSMGR.WFTIMEST ADD (
  CONSTRAINT WFTIMESTAMP_PK
  PRIMARY KEY
  (ROW_ID)
  USING INDEX ODSMGR.WFTIMESTAMP_PK
  ENABLE VALIDATE);

GRANT INSERT, SELECT, UPDATE ON ODSMGR.WFTIMEST TO WEBFOCUS;
  

Sequence:

DROP SEQUENCE ODSMGR.WFTIMESTAMP_SEQ;

CREATE SEQUENCE ODSMGR.WFTIMESTAMP_SEQ
  START WITH 1
  MAXVALUE 9999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;


GRANT ALTER, SELECT ON ODSMGR.WFTIMESTAMP_SEQ TO WEBFOCUS;
  

Glad it works for you. Thanks for discovering the FOCEXEC issue in WF8 - I'll change the column names in my real-world table and program before we upgrade.

For Oracle, if I need a unique key, instead of using the technique you use, I use SYS_GUID, as in the following:

SQL
SELECT SYS_GUID() AS ROW_ID FROM DUAL;
TABLE ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN
-IF &FOCERRNUM NE 0 GOTO FOC_ERROR;

-READFILE H001

One could probably add this as a sub-select to determine the row id.

quote:

CREATE TABLE ODSMGR.WFTIMEST
(
  ROW_ID          INTEGER                       NOT NULL,

You can use that sequence in a default value so you don't need to specify the ROWID/NEXTVAL combination in your INSERT statements. It will be used automatically.

I'm not sure about Oracle's syntax for nextval, but I'm used to calling that as a function and not as some kind of pseudo-column. You may need to use NEXTVAL() instead of bare NEXTVAL.

CREATE TABLE ODSMGR.WFTIMEST
(
  ROW_ID          INTEGER                       NOT NULL DEFAULT ODSMGR.WFTIMESTAMP_SEQ.NEXTVAL,