Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Non Critical DbSpace drop

  • 1.  Non Critical DbSpace drop

    Posted 29 days ago
    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
    ------------------------------


  • 2.  RE: Non Critical DbSpace drop

    Posted 29 days ago

    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

     






  • 3.  RE: Non Critical DbSpace drop

    Posted 28 days ago
    Hi Eric,

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


    Thanks
    Amit

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



  • 4.  RE: Non Critical DbSpace drop

    Posted 29 days ago
    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
    ------------------------------



  • 5.  RE: Non Critical DbSpace drop

    Posted 28 days ago
    Hi SangGyu,

        Thanks for making it easy, will run this sql.

    Thanks
    Amit Patel

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



  • 6.  RE: Non Critical DbSpace drop

    Posted 28 days ago
    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





  • 7.  RE: Non Critical DbSpace drop

    Posted 29 days ago
    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


    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.








  • 8.  RE: Non Critical DbSpace drop

    Posted 28 days ago
    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





  • 9.  RE: Non Critical DbSpace drop

    Posted 28 days ago
    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


    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.








  • 10.  RE: Non Critical DbSpace drop

    Posted 28 days ago
    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





  • 11.  RE: Non Critical DbSpace drop

    Posted 28 days ago
    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
    ------------------------------