RE: running DSPOBJD from sql table function

database catalog does not give you logical files. correct?

No, systables lists ALL files with the following table types:
P = DDS described physical File
L = DDS described logical File
T = SQL defined Table
V = SQL defined View
A = Alias
M = Materialized Query Table (MQT)



Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of Steve
Richter
Sent: Dienstag, 24. Juli 2018 18:21
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: running DSPOBJD from sql table function

database catalog does not give you logical files. correct? There is also
DSPDBR and DSPPGMREF which I do not see in the list of system provided table
functions. and DSPFD *MBRLIST.

what got me thinking about this was a change I was making to a service
program. Wanted the system to tell me what programs were bound to the
service program. Was thinking it would be neat to select from the table
function showing programs in a library, then select where the program name
exists in the table function that returns the *SRVPGM program information of
each program in a library.


On Tue, Jul 24, 2018 at 8:35 AM, Rob Berendt <rob@xxxxxxxxx> wrote:

So basically it's a purely academic question since there are
alternatives to all those commands in the database catalog?
I did a little searching on Create Function and the only things which
appeared to may be an implied switch to single threading are already
there at their default value so I must be interpreting them wrong.
Good luck.


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/23/2018 02:21 PM
Subject: Re: running DSPOBJD from sql table function
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I am not trying to recreate something IBM already provides. Just
illustrating my question. Why the "not threadsafe" message when
running a sql table function that runs DSPFD, DSPDBR, DSPPGMREF,
DSPOBJD to an OUTFILE?



On Sun, Jul 22, 2018 at 10:28 PM, Rob Berendt <rob@xxxxxxxxx> wrote:

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

--
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

--
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