sysmaster info

Hi. I am trying to find a way to get a list of database names and what dbspace they were created in via a query. I'm looking for a query to give me the same result I can get using onmonitor and choosing Status and then Databases. Thanks in advance for any assistance.....mick

See my listdb utility in the package utils2_ak, it prints a similar report to the onmonitor information (plus similar table reports). The query is in there. Utils2_ak is available for download from the IIUG Software Repository. Art S. Kagel ----- Original Message ----- From: Mick J Putillo <MickPutillo@chevrontexaco.com> At: 9/10 11:16 > Hi. I am trying to find a way to get a list of database names and what > dbspace they > were created in via a query. I'm looking for a query to give me the > same result > I can get using onmonitor and choosing Status and then Databases. > Thanks in advance for any assistance.....mick

Hi, It'll be something like : select name, partnum from sysmaster:sysdatabases; where the partnum is an indicator for the dbspace, i.e. I think the first two or three nibbles or so of the partnum will be the dbspace number. The first three nibbles are the first three digits when you print the partnum in hex format (i.e. "hex(partnum)" in the above select). Unfortunately I can't verify/test this since I'm currently off-site ... :( , but it should be something like this. You can probably also apply some shift operation on the partnum to get just the dbspace number. Then you can probably join this with some other table in sysmaster (of which I don't recount the name currently) to get the dbspace name (rather than just the number) ... Anyway, It should give you an idea ... Regards, Martin -- "Putillo, Mick J" <MickPutillo@chevrontexaco.com> Sent by: forum.subscriber@iiug.org 10.09.2003 16:22 To: ids@iiug.org cc: Subject: sysmaster info [1818] Hi. I am trying to find a way to get a list of database names and what dbspace they were created in via a query. I'm looking for a query to give me the same result I can get using onmonitor and choosing Status and then Databases. Thanks in advance for any assistance.....mick

You can try something like this - select s1.name, s2.name from sysdatabases s1, sysdbspaces s2 whererarg1@rarg_srv:"e50kadm".tohex(s1.partnum) = s2.dbsnum This uses a stored proc in database called rarg1 which is on informix server rarg_srv , owned by e50kadm and name of proc is tohex. ( You have to change these accordingly). The Stored proc - ( which u need to create in the database where u have perms ) -------------------- create procedure "e50kadm".tohex(ip_dec int) returning int; define ret_val int; let ret_val = ip_dec; while ret_val > 16 let ret_val = round(ip_dec/16,0) ; let ip_dec = ret_val; end while; return ret_val; end procedure; -------------------------------------- Rgds Preetinder Putillo, Mick J wrote: >Hi. I am trying to find a way to get a list of database names and what >dbspace they >were created in via a query. I'm looking for a query to give me the >same result >I can get using onmonitor and choosing Status and then Databases. >Thanks in advance for any assistance.....mick > > > > > >