Non Critical DbSpace drop

Hi Team, If I need to drop a non critical dbspace , do I need to drop all its tables first? If yes , how to see how many database and table inside particular dbspace and how to delete all tables at once from that dbspace? Please suggest. Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------ #Informix

Hi again To drop a dbspace, it must be empty, i.e you need to drop all tables and indexes in it For me the best and easiest way to see what is in a dbspace in oncheck -pe > filename and you search the dbspace in that file. Watch out that oncheck can take a long (very long) time and eventually block some users. Better run it when no one or as few people is connected. Else I think querying sysmaster/sysdatabases and sysmaster/syspartname: with hex(partnum) with the first part of partnum is the chunk number is a lighter way

Hi Amit, There are several ways to make a drop statement. Below is an example of creating a drop statement with reference to the sysmaster:systabnames table. $ echo "unload to drop_tables.sql delimiter ';' select 'drop table '|| trim(tabname) from systabnames where dbinfo('dbspace', partnum) = 'dbs1' and tabname not like 'sys%' and tabname <> 'TBLSpace' and tabname <> '_temptable'" | dbaccess sysmaster Database selected. 1 row(s) unloaded. Database closed. $ cat drop_tables.sql drop table tab1;​ ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Amit: SangGyu's query will seem to work fine except for two gotchas. If you have v14.10 there are entries in systabnames for the sysmaster pseudo-tables which do not have valid partnums, so you have to filter them out or the query will get an error. Many of the objects listed in sysmaster:systabnames are not tables at all but indexes! The simple query to get you the list of objects in the dbspace that you need to drop is: select dbinfo( 'dbspace', partnum ), dbsname, tabname from sysmaster:systabnames, where dbinfo( 'dbspace', partnum ) = 'datadbs' and partnum > 10240 order by 2, 3; Then you will have to go through them. If you run the following while connected to each of your databases it can work for you for that database. You could even modify it to produce the DROP command as SangGyu did: select which.what, dbinfo( 'dbspace', partnum ), st.tabname from sysmaster:systabnames as st, (select "Table" as what, tabname from systables union select "Index" as what, idxname from sysindices) AS which( what, tabname ) where dbsname = 'my_database' -- modify as appropriate and dbinfo( 'dbspace', partnum ) = 'datadbs' and partnum > 10240 and which.tabname = st.tabname order by 2, 3; to convert this to SangGyu's DROP generator, replace the first line with: unload to drop.mydatabase.sql delimiter ';' select 'drop ' || what || ' ' || trim( st.tabname )Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

There is an environment variable that will tell Dbinfo not look at partnum zero entries, if it has made a release it will be the latest. But might be scheduled for the next release Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

Hi Eric, Thanks for the reply, run the oncehck -pe command , yes it gives long list with all object :) Thanks Amit ------------------------------ AMIT PATEL ------------------------------

Hi SangGyu, Thanks for making it easy, will run this sql. Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------

Dear Art, Thanks for explaining, have read many of your blogs n post regarding Informix. First time joined IBM community and happy to get a reply from you. Thanks Amit ------------------------------ AMIT PATEL ------------------------------

You might need to filter in partnum != 0 if in a latter release Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

Paul: Many of the pseudo-tables have small non-zero partnums, though they all have a zero in the new dbsnum field! To wit: > select first 5 * from systabnames where partnum < 1000;partnum 6 dbsname sysmaster owner informix tabname syscfgtab collate en_US.819 dbsnum 0 partnum 10 dbsname sysmaster owner informix tabname sysptnhdr collate en_US.819 dbsnum 0 partnum 12 dbsname sysmaster owner informix tabname sysptnext collate en_US.819 dbsnum 0 partnum 15 dbsname sysmaster owner informix tabname systabnames collate en_US.819 dbsnum 0 partnum 23 dbsname sysmaster owner informix tabname sysdbstab collate en_US.819 dbsnum 0 5 row(s) retrieved. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

My bad the filter is dbsnum :-) Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC