Focal Point
TRUNCATE TABLE - not available with WebFOCUS accessing Oracle?
April 18, 2011, 03:49 PM
Harry M ClevelandTRUNCATE TABLE - not available with WebFOCUS accessing Oracle?
Just started accessing Oracle data with WebFOCUS.
Needed to do:
ON TABLE HOLD FORMAT SQLORA
The DBA has issues with DROP TABLE...
He wants me to do TRUNCATE TABLE...
DROP TABLE works without error, but TRUNCATE TABLE produces:
(FOC1400) SQLCODE IS 955 (HEX: 000003BB)
(FOC1421) TABLE EXISTS ALREADY. DROP IT OR USE ANOTHER TABLENAME
: ORA-00955: name is already used by an existing object
(FOC1414) EXECUTE IMMEDIATE ERROR.
The DBA wants to know if TRUNCATE TABLE is supported or not.
Thanks in advance.
RELEASE 7.1.8
FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
April 18, 2011, 04:09 PM
Francis MarianiShow us the code.
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
April 18, 2011, 04:18 PM
Tom FlynnHi Harry,
Are you sure you want to
Truncate?
Hope the Link helps...
Tom
P.S. Hi Francis, THANKS!! for an earlier response to an earlier thread, appreciate it...
Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
April 19, 2011, 02:01 PM
Harry M ClevelandVery simple code:
SQL SQLORA
DROP TABLE WEBFOCUS1;
END
TABLE FILE CAR
PRINT *
ON TABLE HOLD AS WEBFOCUS1 FORMAT SQLORA
END
The DBA strongly requests TRUNCATE over DROP. The link that was kindly provided note:
Removing rows with the TRUNCATE TABLE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates dependent objects of the table, requires you to regrant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and respecify its storage parameters. Truncating has none of these effects.
FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
April 19, 2011, 02:19 PM
Francis MarianiYou may need to include the schema name, otherwise the SQLORA TRUNCATE request may not know which schema to work with:
SQL SQLORA
TRUNCATE TABLE BMLIBI.IB_HIERARCHY
END
-RUN
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
April 19, 2011, 04:35 PM
Harry M ClevelandSorry Francis,
That generates an error:
(FOC1400) SQLCODE IS 955 (HEX: 000003BB)
(FOC1421) TABLE EXISTS ALREADY. DROP IT OR USE ANOTHER TABLENAME
: ORA-00955: name is already used by an existing object
(FOC1414) EXECUTE IMMEDIATE ERROR.
FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
April 19, 2011, 04:47 PM
Francis MarianiYou get this error for the TRUNCATE statement? Weird. That example I gave was from working code in my archive.
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
April 19, 2011, 04:55 PM
Harry M ClevelandMy IBI Oracle interface manual is packed away in my attic somewhere. Is the TRUNCATE TABLE command valid? I did not find references to it when searching the IBI site.
FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
April 20, 2011, 07:51 AM
Harry M ClevelandSorry to say, neither methos worked:
SQL SQLORA
TRUNCATE TABLE WEBFOCUS1 ;
END
-RUN
SQL SQLORA
COMMIT ;
END
-RUN
TABLE FILE CAR
PRINT MODEL
ON TABLE HOLD AS WEBFOCUS1 FORMAT SQLORA
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 18
0 HOLDING SQLORA FILE...
(FOC1400) SQLCODE IS 955 (HEX: 000003BB)
(FOC1421) TABLE EXISTS ALREADY. DROP IT OR USE ANOTHER TABLENAME
: ORA-00955: name is already used by an existing object
(FOC1414) EXECUTE IMMEDIATE ERROR.
Can it be an issue of my using 7.1.8?
Thanks for the continued assistance.
FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
April 20, 2011, 09:32 AM
Francis MarianiHarry, as Tom mentioned, "Are you sure you want to Truncate?"
"Use the TRUNCATE TABLE statement to remove all rows from a table." -
it does not drop the table - the HOLD command fails because the table still exists.
I'm not sure why the DBA suggests TRUNCATE, but try TRUNCATE and DROP, then HOLD.
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
April 20, 2011, 09:59 AM
Harry M ClevelandThanks Francis,
The DBA mentioned that TRUNCATE was more efficient and was kinder/gentler to the system. Actually, my eyes glassed over when he went into a lenthy explanation.
I always make it a point NOT to argue with a DBA, so TRUNCATE was the way I had to go.
I tried the following without an error; however, not knowing the internals, I can only assume that this works and the DBA will be happy.
SQL SQLORA
TRUNCATE TABLE WEBFOCUS1 ;
END
-RUN
SQL SQLORA
DROP TABLE FOCUS.WEBFOCUS1 ;
END
-RUN
SQL SQLORA
COMMIT ;
END
If there are no technical objection to this approach, I guess I am sailing on smooth waters.
FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
April 20, 2011, 10:12 AM
Francis MarianiYes, this works, because the table was dropped.
You may not wish to argue with the DBA, but if I were you, I would point out that TRUNCATE does not delete the table - it's an important fact that an Oracle DBA should know, specially since most of them think they're worth their weight in gold.
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
April 20, 2011, 11:20 AM
EricHHarry,
Firstly, in your last example, if you are going to do a DROP TABLE, then there is no point in first doing a TRUNCATE.
Next, as you pointed out earlier in the thread, there may be good reasons not to DROP the table; however in making this decision (TRUNCATE vs. DROP) you need to take the bigger picture. Is your WEBFOCUS1 Oracle table a permanent part of your system? E.g., are there other reports or processes that require this WEBFOCUS1 table? Or is this table a temporary holding area for just this one process.
If it is a temporary holding area that is not used elsewhere, then I'd recommend using the DROP (although if it is temporary you might not even need it to be Oracle, a plain HOLD file might work just as well depending on how it is used down stream).
If the table is a permanent part of your system, then I'd recommend doing the TRUNCATE, but then instead of doing HOLD AS WEBFOCUS1 FORMAT SQLORA (which doesn't work), you add one more step:
SQL SQLORA
TRUNCATE TABLE WEBFOCUS1 ;
END
-RUN
TABLE FILE CAR
PRINT MODEL
ON TABLE HOLD AS TEMP1
END
-RUN
MODIFY FILE WEBFOCUS1
FIXFORM FROM TEMP1
MATCH * KEYS
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA ON TEMP1
END
-RUN
etc
etc
EricH
April 20, 2011, 12:28 PM
M MeagherI agree with EricH. You cannot truncate then hold to SQLORA format. I think WebFOCUS is trying to create the table, which is why you get the error. Also, you lose all your indexes. TRUNCATE, then MODIFY is the way I found works the best.
April 20, 2011, 12:42 PM
Harry M ClevelandEricH, you are probably on to something there...
I did have to change the code around a bit. The MATCH * KEYS threw a lot of errors - probably because I used the original webfocus1.mas which has FOCLIST as a key. Only the first record was loaded.
These are the errors that were thrown:
(FOC1400) SQLCODE IS 1 (HEX: 00000001)
(FOC1417) INSERT WOULD CREATE DUPLICATES FOR UNIQUE INDEX. NO ACTION TAKEN
: ORA-00001: unique constraint (FOCUS.WEBFOCUS1IX) violated
(FOC1416) EXECUTE ERROR : SEG01
Here's the .acx and .mas:
SEGNAME=SEG01, TABLENAME=WEBFOCUS1, KEYS=01, WRITE=YES, $
FILENAME=WEBFOCUS1, SUFFIX=SQLORA , $
SEGMENT=SEG01, SEGTYPE=S0, $
FIELDNAME=FOCLIST , ALIAS=FOCLIST, USAGE=I5, ACTUAL=I4, $
FIELDNAME='MODEL', ALIAS='MODEL', USAGE=A24, ACTUAL=A24, $
I changed the code to the following and it worked:
SQL SQLORA
TRUNCATE TABLE WEBFOCUS1 ;
END
-RUN
TABLE FILE CAR
PRINT MODEL
COMPUTE FOCLIST/I4 = 1 + FOCLIST; NOPRINT
BY TOTAL FOCLIST
ON TABLE HOLD AS TEMP1
END
-RUN
MODIFY FILE WEBFOCUS1
FIXFORM FROM TEMP1
MATCH FOCLIST
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA ON TEMP1
END
It's not too pretty but the DBA is happy to see the TRUNCATE working.
FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
April 20, 2011, 02:57 PM
Francis MarianiToo bad you didn't mention the other errors in your original post.
(FOC1400) SQLCODE IS 1 (HEX: 00000001)
(FOC1417) INSERT WOULD CREATE DUPLICATES FOR UNIQUE INDEX. NO ACTION TAKEN
: ORA-00001: unique constraint (FOCUS.WEBFOCUS1IX) violated
(FOC1416) EXECUTE ERROR : SEG01\
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
April 20, 2011, 03:06 PM
Harry M ClevelandFrancis,
They did not exist until I put the MODIFY code in.
I put all of the errors in my post as they appeared.
FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
April 25, 2011, 08:52 AM
EricHHarry - Something along these lines should eliminate the need for the FOCLIST:
TABLE FILE CAR
BY MODEL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS FOO
END
EricH
April 25, 2011, 09:50 AM
Harry M ClevelandI did try the PRINTONLY setting; however, whenever I review the .mas, FOCLIST is there. Nothing I did seemed to get rid of it so I gave up. I do not know if it's an issue with 7.1.8.
Thanks
FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
April 25, 2011, 01:23 PM
Dan SatchellFOCLIST is created when you PRINT and HOLD FORMAT FOCUS because FOCUS cannot ensure the uniqueness of the records when the query uses a PRINT statement. FOCLIST is created to ensure uniqueness. Try changing your PRINT to a SUM (with the necessary BYs) and FOCLIST should go away.
WebFOCUS 7.7.05
April 26, 2011, 11:06 AM
Wep5622quote:
Originally posted by Francis Mariani:
You may not wish to argue with the DBA, but if I were you, I would point out that TRUNCATE does not delete the table - it's an important fact that an Oracle DBA should know, specially since most of them think they're worth their weight in gold.
I'm pretty sure that's exactly the
intention of his DBA. If you TRUNCATE a table, you keep any referential integrity constraints that were defined (foreign keys to other tables in the database, for example) and any user access rights on the database level.
If you DROP the table, you lose those.
And it remains to be seen that the user that WebFOCUS uses to connect to the database has enough privileges to DROP or CREATE a table.
If I were a DBA, I wouldn't be eager to allow a reporting application access rights on that level.
To the OP: An alternative solution could be to use a FOCUS file to store your results in the file system, instead of in the Oracle database.
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 :
April 26, 2011, 11:13 AM
Francis Marianiquote:
ON TABLE HOLD FORMAT SQLORA
The original post never suggested there was a pre-existing table.
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
April 26, 2011, 12:08 PM
<JG>Francis,
You ever just run a procedure once and once only?
April 26, 2011, 12:27 PM
Harry M ClevelandThe DBA gave me the option of outputting to only to one table. Anything I do has to go to that table. So, it will be reused repeatedly.
Currently, the historical data resides in wonderfully optimized FOCUS databases, however, management wants all data to be moved to Oracle.
I had to show a proof of concept that I can use WebFOCUS to migrate data rather easily (which it can; however, the DBA took exception with the lack of TRUNCATE).
The full story is, management decided to go to Cognos. My 18 years of FOCUS/WebFOCUS development is going to be shut down. I have to move all of the FOCUS databases over Oracle.
Got asprin?
FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
April 26, 2011, 12:32 PM
Tom FlynnHarry,
Hopefully you will get training; if you do, make sure you take Framework Manager. COGNOS is all about staging the data for the users, so, it's analytical and SQL based.
Best wishes...
Tom
Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
April 26, 2011, 12:34 PM
<JG>quote:
My 18 years of FOCUS/WebFOCUS development is going to be shut down
Harry, get another job and let some other sucker do it.