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 Eric,

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


Thanks
Amit

------------------------------
AMIT PATEL
------------------------------

Original Message:
Sent: Wed June 10, 2020 06:50 AM
From: Eric Vercelletto
Subject: Non Critical DbSpace drop

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
------------------------------

Hi SangGyu,

    Thanks for making it easy, will run this sql.

Thanks
Amit Patel

------------------------------
AMIT PATEL
------------------------------

Original Message:
Sent: Wed June 10, 2020 07:03 AM
From: SangGyu Jeong
Subject: Non Critical DbSpace drop

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 sysmasterDatabase selected.1 row(s) unloaded.Database closed.$ cat drop_tables.sqldrop table tab1;​

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

Original Message:
Sent: 6/10/2020 9:36:00 AM
From: AMIT PATEL
Subject: RE: Non Critical DbSpace drop

Hi SangGyu,

    Thanks for making it easy, will run this sql.

Thanks
Amit Patel

------------------------------
AMIT PATEL
------------------------------

Original Message:
Sent: 6/10/2020 7:46:00 AM
From: Art Kagel
Subject: RE: Non Critical DbSpace drop

Amit:

SangGyu's query will seem to work fine except for two gotchas. 

1. 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. 
2. 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.

Original Message:
Sent: 6/10/2020 9:21:00 AM
From: Paul Watson
Subject: RE: Non Critical DbSpace drop

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

Original Message:
Sent: 6/10/2020 10:05:00 AM
From: Art Kagel
Subject: RE: Non Critical DbSpace drop

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.

Original Message:
Sent: 6/10/2020 9:21:00 AM
From: Paul Watson
Subject: RE: Non Critical DbSpace drop

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

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
------------------------------

Original Message:
Sent: Wed June 10, 2020 07:45 AM
From: Art Kagel
Subject: Non Critical DbSpace drop

Amit:

SangGyu's query will seem to work fine except for two gotchas. 

1. 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. 
2. 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.