Re: Need list of dataareas in a library

John,

My first suggestion would be to update to 7.1/7.2 :-) Those releases have a
lot of SQL goodies that make me drool every time I read about a new TR....

Regarding UDTFs, as expressed so aptly by Rob and Chuck, it shouldn't be a
great deal. The following is a *very* quick and dirty code, with a lot of
shortcomings, specially in the area of error handling. It is, I think,
relatively harmless. Buyers beware, though :-)

****************
DROP FUNCTION @520.dsp_objd;

CREATE FUNCTION @520.dsp_objd(lib_name varchar(10), obj_type
varchar(07))
RETURNS TABLE(
libname char(10)
, objName char(10)
, objType char(10)
, objAttr char(10)
, objText char(50)
)
LANGUAGE SQL
DISALLOW PARALLEL
MODIFIES SQL DATA
NOT FENCED

BEGIN
declare w_cmd varchar(80);
declare w_len numeric(15, 5);
declare table_already_exists condition for '42710';
declare continue handler for table_already_exists
delete from session.ret_objects;

DECLARE GLOBAL TEMPORARY TABLE session.ret_objects (
libname char(10)
, objName char(10)
, objType char(10)
, objAttr char(10)
, objText char(50)
) ;

SET w_cmd = 'DSPOBJD ' || trim(lib_name) || '/*ALL ' ||
trim(obj_type) || ' OUTPUT(*OUTFILE) OUTFILE(QTEMP/DSP_OBJDF)' ;
SET w_len = char_length(w_cmd) ;

CALL QSYS.QCMDEXC(w_cmd, w_lent );

INSERT into session.ret_objects (
libname
, objName
, objType
, objAttr
, objText
)
SELECT
ODLBNM
, ODOBNM
, ODOBTP
, ODOBAT
, ODOBTX
FROM qtemp.dsp_objdf
;

RETURN
SELECT
libname
, objName
, objType
, objAttr
, objText
FROM session.ret_objects;
END;

****************

Change @520 with the Library you have your UDTF to reside. To execute it,
use something like the following:

*select * from table(dsp_objd('*ALLUSR', '*DTAARA')) as t1 *


Regards,
Luis



Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries