Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SHARING] Capturing program execution info

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SHARING] Capturing program execution info
 Login/Join
 
Expert
posted
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,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
This is basically what we do as well.


WebFOCUS 8.1.05
 
Posts: 496 | Registered: January 04, 2008Report This Post
Platinum Member
posted Hide Post
Thanks for sharing!
Soon we will be able to set up a "Poor Man's" web store for these mini apps!


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
I don't use a FOCUS DB anymore, to avoid the concurrent update issue, but thanks for sharing your suggestions.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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?


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Virtuoso
posted Hide Post
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




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
"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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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;  


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Gold member
posted Hide Post
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.


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Expert
posted Hide Post
You must view the source to see what's going on. There should be a SQL message about rows inserted - zero or one...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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,


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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

  


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Gold member
posted Hide Post
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,


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Expert
posted Hide Post
I would code the SQL statement as follows:

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


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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?


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Gold member
posted Hide Post
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!


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Gold member
posted Hide Post
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;
  


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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,


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SHARING] Capturing program execution info

Copyright © 1996-2020 Information Builders