Informix

nested-group-icon.png

DB2

Expand all | Collapse all

database list under dbspace

  • 1.  database list under dbspace

    Posted Thu January 28, 2021 01:17 AM
    Hello All,

         How can I select all database name under any dbspace thru SQL?

          I'm not getting any join conditions between sysdbspaces and sysdatabases. please suggest.


    Thanks
    Amit

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


  • 2.  RE: database list under dbspace

    Posted Thu January 28, 2021 03:29 AM

    Hello Amit,

    try this:

    select name[1,30], dbinfo("DBSPACE",partnum)::char(30) as dbspace from sysdatabases;

    Cheers,

    Markus



    ------------------------------
    Markus Holzbauer
    ------------------------------



  • 3.  RE: database list under dbspace

    Posted Thu January 28, 2021 08:02 AM
    Amit:

    select name, dbinfo( 'dbspace', partnum ) as dbspace from sysdatabases where dbinfo( 'dbspace', partnum ) = 'datadbs_1';

    name     art
    dbspace  datadbs_1

    name     forums
    dbspace  datadbs_1

    name     nologging
    dbspace  datadbs_1

    name     logged
    dbspace  datadbs_1

    name     pinn_db
    dbspace  datadbs_1

    name     antonin
    dbspace  datadbs_1

    name     sqlbenchmark
    dbspace  datadbs_1

    7 row(s) retrieved.



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------