Focal Point
another problem with modify...
May 21, 2008, 07:04 AM
robyanother problem with modify...
Hy!
I have another problem....
I try this modify:
MODIFT FILE NXMBRRSE
COMMIT 1 --->This number What is?
FIXFORM FROM TBRRDTAY
MATCH USERID
ON NOMATCH REJECT
ON MATCH UPDATE &UPDATEFIELD DATE_CHANGED
DATA ON KEYDEL
END
... but I see with monitor DB2 that the command COMMIT is not excute. How it is possible?
When program focus is purge in the tables DB2 I find all lines. No lines is delete. Why?
Thank you for any help!
May 21, 2008, 07:36 AM
Danny-SRLRoby,
I would put after your ON MATCH UPDATE statement, ON MATCH COMMIT
The COMMIT 1 statement is meant to say that a COMMIT should be issued after each modification to the database. Nonetheless, I would feel more confident with ON MATCH COMMIT.
Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
May 21, 2008, 07:40 AM
Tom FlynnRoby,
quote:
MODIFT FILE NXMBRRSE
COMMIT 1 --->This number What is?
FIXFORM FROM TBRRDTAY
MATCH USERID
ON NOMATCH REJECT
ON MATCH UPDATE &UPDATEFIELD DATE_CHANGED
DATA ON KEYDEL
END
If you are bringing in data from TBRRDTAY, then why is DATA ON KEYDEL???
You are not deleting any records, ON MATCH DELETE, so why are you expecting any???
Also, does &UPDATEFIELD get evaluated an an actual column???
Maybe this will help:
MODIFY FILE NXMBRRSE
COMMIT 1 --->This number What is? Danny answered
FIXFORM FROM TBRRDTAY
MATCH USERID
ON NOMATCH REJECT
ON MATCH UPDATE &UPDATEFIELD DATE_CHANGED
DATA ON TBRRDTAY
END
Hope this helps...
Tom
Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
May 21, 2008, 09:12 AM
robyI try this modify:
MODIFT FILE NXMBRRSE
FIXFORM FROM TBRRDTAY
MATCH USERID
ON NOMATCH REJECT
ON MATCH UPDATE &UPDATEFIELD DATE_CHANGED
ON MATCH COMMIT
DATA ON TBRRDTAY
END
... but still I don't see with monitor DB2 the command COMMIT.
If my program focus go to in error in the tables DB2 I find all lines.
Are there set that I must write in my program?
May 21, 2008, 09:51 AM
Francis MarianiYou don't see a COMMIT statement in DB2 because each row modified is an individual SELECT and UPDATE statement.
Here's an example using a SQL Server table:
-*-- Set up SQL tracing --------------------------------------------------------
-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL
-*-- Enable Trace for the SQL Translator -------------------
SET TRACEON = SQLTRANS
-*-- Show SQL statements -----------------------------------
SET TRACEON = STMTRACE//CLIENT
-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT
-*-- Disable the trace stamp (Date/Time etc) ---------------
SET TRACESTAMP = OFF
-*-- Set trace line wrapping - # of characters -------------
SET TRACEWRAP = 78
-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON
-RUN
TABLE FILE TESTTABLE1
PRINT *
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
SELECT T1."keyfield1",T1."valuefield1" FROM
EIDW_test.dbo.TestTable1 T1;
0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3
MODIFY FILE TESTTABLE1
FIXFORM FROM H001
MATCH KEYFIELD1
ON MATCH COMPUTE VALUEFIELD1 = 'X';
ON MATCH UPDATE VALUEFIELD1
ON MATCH COMMIT
ON NOMATCH GOTO TOP
DATA ON H001
END
-RUN
SELECT T1."keyfield1",T1."valuefield1" FROM
EIDW_test.dbo.TestTable1 T1 WHERE (T1."keyfield1" = ?);
UPDATE EIDW_test.dbo.TestTable1 SET "valuefield1" = ? WHERE
("keyfield1" = ?);
SELECT T1."keyfield1",T1."valuefield1" FROM
EIDW_test.dbo.TestTable1 T1 WHERE (T1."keyfield1" = ?);
UPDATE EIDW_test.dbo.TestTable1 SET "valuefield1" = ? WHERE
("keyfield1" = ?);
SELECT T1."keyfield1",T1."valuefield1" FROM
EIDW_test.dbo.TestTable1 T1 WHERE (T1."keyfield1" = ?);
UPDATE EIDW_test.dbo.TestTable1 SET "valuefield1" = ? WHERE
("keyfield1" = ?);
0 TRANSACTIONS: TOTAL = 3 ACCEPTED= 3 REJECTED= 0
SEGMENTS: INPUT = 0 UPDATED = 3 DELETED = 0
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
May 21, 2008, 11:05 AM
robyexcuse-me.
But i have another question...
MODIFT FILE NXMBRRSE
FIXFORM FROM TBRRDTAY
MATCH USERID
ON NOMATCH REJECT
ON MATCH UPDATE &UPDATEFIELD DATE_CHANGED
ON MATCH goto LOGCOM
DATA ON TBRRDTAY
case logcom
sql commit (
if write only commit. The commit is not excute...WHY?) WHY?endcase
END
May 21, 2008, 11:46 AM
Francis Marianiroby, this document may help:
Modifying Data in DB2 I can't explain why the COMMIT is not displayed in the SQL trace.
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
May 22, 2008, 09:03 AM
jgelonaI don't know about DB2, but with Oracle I use the following:
MATCH KEY
ON NOMATCH INCLUDE
ON NOMATCH SQL COMMIT WORK
ON MATCH UPDATE/DELETE
ON MATCH SQL COMMIT WORK
I started using this syntax back with FOCUS for HP/UX and it has always worked in WebFOCUS.
When I first started updating Oracle (FOCUS for HP/UX), the "SQL" and "WORK" were required even though one did not get an error if they were omitted. If either was missing the INCLUDE/UPDATE/DELETE was not committed to the database.
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.