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     [SOLVED] Check if table and master exists

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Check if table and master exists
 Login/Join
 
BobV
Gold member
posted
Hello all,
I'm not very good at dialog manager and I think I'll need it here. I've been looking at various posts to pick up informatio, but can't seem to get to what I want.
I want to place code in my focexecs to check for the existence of DB2 tables, masters and access files. If the table exists, delete it along with master and re-create it. Otherwise, just create it. Here's what I've tried to tailor to my application:

-******** TEST TO SEE IF DB2 TABLE EXISTS ********
STATE edausr1.DB_TEST --->>> edausr1 is the location of the table itself
-RUN
-IF &RETCODE EQ -1 THEN GOTO CHECK_MASTER;
SQL
DROP TABLE edausr1.DB_TEST;
COMMIT;
END
-CHECK_MASTER
-***** TEST TO SEE IF MASTER AND ACCESS FILES EXIST ****
APP PREPENDPATH DB2TABLES --->>> DB2TABLES is the path where the master and access files are created.
CHECK FILE DB_TEST
-IF &RETCODE EQ -1 THEN GOTO CREATE_DB2;
APP DELETEF DB2TABLES DB_TEST MASTER
APP DELETEF DB2TABLES DB_TEST ACCESS
-CREATE_DB2
APP ENABLE
APP HOLDMETA DB2TABLES
TABLE FILE CAR
SUM
MODEL
ON TABLE HOLD AS DB_TEST FORMAT DB2
END
SQL
COMMIT;
END

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


WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
 
Posts: 93 | Registered: February 20, 2008Report This Post
Waz
Expert
posted Hide Post
Would it be easier to hit the DB2 catalog and check to see if the table is there.

Can't remember what the DB2 table name is but should be simple.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
GamP
Virtuoso
posted Hide Post
quote:
STATE edausr1.DB_TEST --->>> edausr1 is the location of the table itself

I think that the STATE command is for checking if a certain physical file exists. In this case it is a logical name (edausr1 being the db2 username and db_test being the table name). But it looks like a real filename so the command will always return: Nope, not there...
Just do the DB2 Drop Table always, you'll get a warning message if the table does not exist, but it will not halt the processing.
The same is valid for the delete of the master and access file - just do it. If not present, you'll get a warning message but porocessing continues ...
Oh, you don't need the SQL COMMIT at the end.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
BobV
Gold member
posted Hide Post
GamP
you are correct in that edausr1 is the user id and DB_TEST is the table name. You are also correct that it does not kill my code when trying to delete a table that is not there. I was asked to create some code that might skip the DROP table code if it's not necessary to make sure there are no problems. We have been told by our DB Admin that COMMIT is required. Without it, tables were locking. With it, everything is fine.

Thanks for the response.

BobV


WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
 
Posts: 93 | Registered: February 20, 2008Report This Post
susannah
Expert
posted Hide Post
BobV, does that mean you now understand that STATE requires the full url?
and that CHECK isn't going to give you want you want, you need the STATE there as well.
You don't specify what platform you're on in your sig. Please attend to that bit of housekeeping.



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report 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     [SOLVED] Check if table and master exists

Copyright © 1996-2020 Information Builders