Focal Point
[SOLVED] Check if table and master exists

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/29010586

April 02, 2009, 03:08 PM
BobV
[SOLVED] Check if table and master exists
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
April 02, 2009, 04:23 PM
Waz
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!

April 06, 2009, 08:01 AM
GamP
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
April 06, 2009, 09:42 AM
BobV
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
April 06, 2009, 10:02 AM
susannah
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