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     TRUNCATE TABLE - not available with WebFOCUS accessing Oracle?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
TRUNCATE TABLE - not available with WebFOCUS accessing Oracle?
 Login/Join
 
Silver Member
posted
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
 
Posts: 36 | Registered: November 11, 2003Report This Post
Expert
posted Hide Post
Show 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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Hi 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
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Silver Member
posted Hide Post
Very 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
 
Posts: 36 | Registered: November 11, 2003Report This Post
Expert
posted Hide Post
You 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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Silver Member
posted Hide Post
Sorry 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
 
Posts: 36 | Registered: November 11, 2003Report This Post
Expert
posted Hide Post
You 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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Silver Member
posted Hide Post
My 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
 
Posts: 36 | Registered: November 11, 2003Report This Post
Silver Member
posted Hide Post
Sorry 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
 
Posts: 36 | Registered: November 11, 2003Report This Post
Expert
posted Hide Post
Harry, 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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Silver Member
posted Hide Post
Thanks 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
 
Posts: 36 | Registered: November 11, 2003Report This Post
Expert
posted Hide Post
Yes, 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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
Harry,

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
 
Posts: 164 | Registered: March 26, 2003Report This Post
Gold member
posted Hide Post
I 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.
 
Posts: 60 | Location: Ellensburg Washington | Registered: May 22, 2009Report This Post
Silver Member
posted Hide Post
EricH, 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
 
Posts: 36 | Registered: November 11, 2003Report This Post
Expert
posted Hide Post
Too 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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Silver Member
posted Hide Post
Francis,

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
 
Posts: 36 | Registered: November 11, 2003Report This Post
Platinum Member
posted Hide Post
Harry - 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
 
Posts: 164 | Registered: March 26, 2003Report This Post
Silver Member
posted Hide Post
I 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
 
Posts: 36 | Registered: November 11, 2003Report This Post
Virtuoso
posted Hide Post
FOCLIST 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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
quote:
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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Expert
posted Hide Post
quote:
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<JG>
posted
Francis,

You ever just run a procedure once and once only?
 
Report This Post
Silver Member
posted Hide Post
The 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
 
Posts: 36 | Registered: November 11, 2003Report This Post
Expert
posted Hide Post
Harry,

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
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
<JG>
posted
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.
 
Report 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     TRUNCATE TABLE - not available with WebFOCUS accessing Oracle?

Copyright © 1996-2020 Information Builders