Thanks Anand.
The value of using the GET_DBSIZE_INFO Procedure for me is in getting both the actual database size and the total capacity (thereby % used).
I've tried coding the call using variables as suggested, but the procedure is objecting to the host variables I supply. Unfortunately, as this is in Rexx, it's hard to force a database_type to be associated with a variable, and I usually get a "SQL0304N A value cannot be assigned to a host variable" message. SO I've tried both pre-setting the variables and/or using CAST, but to no avail.
Anyone UDB and Rexx experienced got any advice? Example of what I'm doing below
Cheers
Mark
DBSIZE_int = '-1'
SNAPSHOTTIMESTAMP = '2006-07-28-10.25.54.861001'
DATABASESIZE = 400000
DATABASECAPACITY = 400000
Statement = "CALL GET_DBSIZE_INFO(:SNAPSHOTTIMESTAMP, :DATABASESIZE, :DATABASECAPACITY, :DBSIZE_int)"
say statement
call SQLEXEC statement
If SQLCA.sqlcode < 0 Then
call ErrorMessage Statement "returned" SQLCA.sqlcode sqlmsg
say SNAPSHOTTIMESTAMP DATABASESIZE DATABASECAPACITY
-----Original Message-----
From: Kaushal Anand
[mailto:db2udbdba-ezmlmshield-x15967568.[Email address protected]
Sent: 28 July 2006 06:56
To: LazyDBA Discussion
Subject: RE: GET_DBSIZE_INFO
why dont you use this instead
db2 select (SUM(total_pages)*4)/(1024.0*1024) TOTAL_ALLOCATED_SPACE_IN_GB from table(snapshot_tbs_cfg('<DBNAME>',-1)) TBS_SPCE
oh. by the way. i forgot to mention. the three question marks you see in the procedure are OUT parameters. you could use variables and get values in them. that would solve your problem i guess. no need for the above query.
Regards,
______________________________
Anand Kaushal
Software Engineer - Database
Lionbridge
5th Floor, Spectra Building,
Hiranandani Business Park,
Powai, Mumbai - 400 076
+91-22-55567000x7079 (office)
+91-9819210090 (mobile)
www.lionbridge.com
-----Original Message-----
From: Gillis Mark
[mailto:db2udbdba-ezmlmshield-x41158529.[Email address protected]
Sent: Thursday, July 27, 2006 6:46 PM
To: LazyDBA Discussion
Subject: GET_DBSIZE_INFO
Folks
I can use the above Procedure to retrieve data about a database, as
advertised:
db2 => CALL GET_DBSIZE_INFO(?, ?, ?, 0)
Value of output parameters
--------------------------
Parameter Name : SNAPSHOTTIMESTAMP
Parameter Value : 2006-07-27-14.08.54.093000
Parameter Name : DATABASESIZE
Parameter Value : 76894208
Parameter Name : DATABASECAPACITY
Parameter Value : 22295486976
Return Status = 0
What I'd like to do, is to retrieve this information by row, similar to the
function GET_DBM_CONFIG:
db2 => select dbmconfig_type, numdb, diaglevel from
table(sysfun.get_dbm_config()) as dbmcfg
DBMCONFIG_TYPE NUMDB DIAGLEVEL
-------------- ----------- -----------
0 3 3
1 3 3
I'm trying to execute a Rexx program that will get the DBSIZE info back from
each of 250+ databases that it will iterate through. Can this be done a)
without writing my own Function to call the Procedure or (if not) b) any
pointers on how to code a function to do this?
Thanks
Mark
This e-mail (and any attachments) may contain privileged and/or confidential information. If you are not the intended recipient please do not disclose, copy, distribute, disseminate or take any action in reliance on it. If you have received this message in error please reply and tell us and then delete it. Should you wish to communicate with us by e-mail we cannot guarantee the security of any data outside our own computer systems. For the protection of Legal & General's systems and staff, incoming emails will be automatically scanned.
Any information contained in this message may be subject to applicable terms and conditions and must not be construed as giving investment advice within or outside the United Kingdom.
The following companies are subsidiary companies of the Legal & General Group Plc which are authorised and regulated by the Financial Services Authority for advising and arranging the products shown: Legal & General Partnership Services Limited (insurance and mortgages), Legal & General Insurance Limited (insurance), Legal & General Assurance Society Limited
(life assurance, pensions and investments), Legal & General Unit Trust Managers Limited and Legal & General Portfolio Management Services Limited (investments).
They are registered in England under numbers shown.
The registered office is Temple Court, 11 Queen Victoria Street, London EC4N 4TP.
Legal & General Partnership Services Limited: 5045000 Legal & General Assurance Society Limited: 166055 Legal & General (Unit Trust Managers) Limited: 1009418 Legal & General (Portfolio Management Services) Limited: 2457525 Legal & General Insurance Limited: 423930
They are registered with the Financial Services Authority under numbers shown. You can check this at www.fsa.gov.uk/register
Legal & General Partnership Services Limited: 300792 Legal & General Assurance Society Limited: 117659 Legal & General (Unit Trust Managers) Limited: 119273 Legal & General (Portfolio Management Services) Limited: 146786 Legal & General Insurance Limited: 202050
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page