RE: Need list of dataareas in a library

I would love to take option 1 but I'm only a lowly consultant here and have no say in their machines OS. I just have to be able to code on whatever release they are at. My machine is at 7.1 and thus the reason I was looking for goodies that might have existed in 6.1 that I missed.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis Rodriguez
Sent: Wednesday, May 20, 2015 3:50 PM
To: Midrange Systems Technical Discussion
Subject: 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