V_S_H_Satish wrote:
[color=blue]
> Hai Friends
>
> Quite a long time back i ask these questions but everybody send me
> some snapshot and functions names but i need select statements for the
> following stuff pls help me in this[/color]
Once more unto the breach ...
I'm guessing you didn't actually *read* or *try* anything from the link
Serge posted last time round? If you had, you might have realized that
table functions are used within SELECT statements...
In case you need it again, here it is:
http://publib.boulder.ibm.com/infoce...om.ibm.db2.udb
..doc/admin/r0012548.htm
Read about and try out the functions in that list. You'll probably find
quite a bit (if not all) of what you're looking for. To get you started:
[color=blue]
> I need to create report for tablespace as follows:
>
> TABLESPACE TOTAL USED FREE %Used
> %Free[/color]
SELECT
TABLESPACE_NAME AS TABLESPACE,
TOTAL_PAGES AS TOTAL,
USED_PAGES AS USED,
FREE_PAGES AS FREE,
CASE
WHEN TOTAL_PAGES = 0 THEN NULL
ELSE (USED_PAGES * 100) / TOTAL_PAGES
END AS USED_PCT,
CASE
WHEN TOTAL_PAGES = 0 THEN NULL
ELSE (FREE_PAGES * 100) / TOTAL_PAGES
END AS FREE_PCT
FROM
TABLE(SNAPSHOT_TBS_CFG(CAST(NULL AS VARCHAR(255)), CAST(NULL AS
INTEGER))) AS T;
Produces something like:
TABLESPACE TOTAL USED FREE USED_PCT FREE_PCT
-------------------- ------- -------- ---------- -------- --------
SYSCATSPACE 8192 4716 3472 57 42
TEMPSPACE1 0 0 0 - -
USERSPACE1 4096 2016 2048 49 50
SYSTOOLSPACE 0 0 0 - -
SYSTOOLSTMPSPACE 0 0 0 - -
The tablespaces with 0 total pages (and hence NULL percentages) are SMS
tablespaces which don't have a fixed size
[color=blue]
> 1. instance name instance status from which table[/color]
Instance name, start time and last reset time:
SELECT
SERVER_INSTANCE_NAME,
DB2START_TIME,
LAST_RESET
FROM
TABLE(HEALTH_DBM_INFO(CAST(NULL AS INTEGER))) AS T;
See HEALTH_DBM_HI for lots of health-indicators which ought to give you
all the "status" information (whatever that might be) you could wish
for and then some
[color=blue]
> 2. db status and db name fro which table[/color]
For database name:
SELECT
DB_NAME
FROM TABLE(HEALTH_DB_INFO(CAST(NULL AS VARCHAR(255)), CAST(NULL AS
INTEGER))) AS T;
See HEALTH_DB_HI for health-indicators.
[color=blue]
> 3. tablespace free space available and how to increase the space[/color]
Dealt with above (to increase space see the ALTER TABLESPACE statement
in the SQL reference).
[color=blue]
> 4. to check backup details from which table[/color]
Information on the last backup of the databases in the current instance:
SELECT
HI_ADDITIONAL_INFO
FROM TABLE(HEALTH_DB_HI(CAST(NULL AS VARCHAR(255)), CAST(NULL AS
INTEGER))) AS T
WHERE HI_ID = 1018;
Dave.
P.S. People are a lot more willing to provide assistance if you can
demonstrate that you've at least *tried* to solve the problem yourself.
People are not here to do your work for you. If you come back a fourth
time asking the same thing without having lifted a finger to try any of
the suggestions people have taken the time to provide you with, expect
to be ignored.
If you do run into a problem with any of the suggestions from myself or
others, try and be explicit in your description of the problem
(including platform, version, what things you've tried, any error
message, and error codes are all helpful).
--
DB2 Daily Check Lists 
