Re: running DSPOBJD from sql table function

Why would one want to call DSPOBJD from a table when one can get all that
info from OBJECT_STATISTICS?
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/
rzajq/rzajqudfobjectstat.htm
http://ibm.biz/DB2foriServices


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Steve Richter" <stephenrichter@xxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: 07/19/2018 02:12 PM
Subject: running DSPOBJD from sql table function
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I have an SQL table function that runs DSPOBJD to an outfile and then
returns that outfile as the output of the table function. The table
function works. But I get an info message CPD000D - Command *LIBL/DSPOBJD
not safe for a multithreaded job.

What does that message mean exactly? Can I mark the table function to
tell
the system it will be running single threaded code?

here is SQL that runs the table function:

select a.objname, a.objsize, a.crtdate
from table(sql_dspobjd('audrcv*','qgpl')) a
where a.crtdate < current date - 10 days

Here is the table function:
CREATE OR REPLACE FUNCTION sql_dspobjd(
inName char(10),
inLib char(10) default '*LIBL',
inObjType char(10) default '*ALL')
RETURNS table ( libName char(10),
objName char(10),
objType char(10),
objAttr char(10),
objSize decimal(10,0),
textdesc char(50),
locked char(1),
damaged char(1),
crtDate date )
language sql
modifies sql data
BEGIN

declare vSqlCode decimal(5,0) ;
declare sqlCode int ;
declare cmds varchar(2000) ;
declare cmdsLx decimal(15,5) ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET VSQLCODE = SQLCODE ;


-- run DSPPGMREF to outfile.
set cmds = 'dspOBJD obj(' || trim(INLib) || '/' ||
trim(inName) || ') objtype(' ||
trim(inObjType) || ') output(*outfile) ' ||
'outfile(qtemp/core0043o)' ;
set cmdsLx = length(trim(cmds)) ;
CALL QCMDEXC( cmds, cmdsLx ) ;


return
select a.odlbnm libName, a.odobnm objname,
a.odobtp objType, a.odobat objAttr,
a.odobsz objSize, a.odobtx textDesc,
a.odoblk locked, a.odobdm damaged,
date(timestamp_format(
case when a.odccen = '0' then '19' else '20' end
|| substr(a.odcdat,5,2) || substr(a.odcdat,1,4),
'YYYYMMDD')) crtDate
from qtemp/core0043o a ;

END
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD