Find source file members

[CENTRINO]

Hi.

I'd need to find all source members in a system.

Then I'd have to put their names, file name and library and type of source
in a file so I can have a dictionary ...

What would be the approach to do so?

Thanks in advance

[DrewD]

try file qsys2/SYSTABLES

[CRPence]

The following requests would generate some more data than
desirable, which can then be subsetted:

CRTDUPOBJ QAFDMBRL QSYS *FILE QTEMP FDMBRL
CHGPF QTEMP/FDMBRL SIZE(*NOMAX)
ADDPFM QTEMP/FDMBRL SomeName
DSPFD *ALLUSR/*ALL *MBRLIST *OUTFILE FILEATR(*PF)
OUTFILE(QTEMP/FDMBRL) OUTMBR(*FIRST *ADD)

A quick test suggests the following UDF invoked in a SELECT of
the desired libraries & files from SYSTABLES in QSYS2 will get the
above information more directly for just the source physical files.
Best to pare down the selected libraries until after verifying the
UDF & query effect the desired output.

<code>

create function cmdexec (cmdstr varchar(2000) )
returns smallint language sql specific cmdexec
not deterministic modifies sql data called on null input
disallow parallel
set option dbgview=*LIST
BEGIN
declare cmdlen decimal(15, 5);
declare continue handler for SQLEXCEPTION return NULL;
set cmdlen=length(cmdstr);
call qsys/qcmdexc (cmdstr, cmdlen);
return 1;
END

SELECT CMDEXEC('DSPFD ' CONCAT RTRIM(SYS_DNAME) CONCAT '/'
CONCAT SYS_TNAME CONCAT '*MBRLIST *OUTFILE FILEATR(*PF)
OUTFILE(QTEMP/FDMBRL) OUTMBR(*FIRST *ADD) SYSTEM(*LCL)')
, sys_dname
, sys_tname
, case filetype when 'S' then 'SRC-PF' end
FROM qsys2/systables
WHERE sys_dname not like 'Q%'
and sys_dname not in ('SYSIBM', '#LIBRARY' /* more? */)
and "TYPE"='P'
and FILETYPE='S'

</code>

Regards, Chuck

[CENTRINO]

Thank you very much.

I have tested your first approach and then querying it works fine for me.

Now I have curiosity about UDFs. I have never used them.

¿The UDF code you wote is to be compiled? If so, what kind of source is it?
compiler to use .. Will it crate an objet in the system? in wich library?

Can it be run and stored? from wich interface: Iseries Access for windows,
interactive sql?

I have been searching about it, but the only thing I can find is an
explanation about what an UDF is, but not how to run or estore them:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/sqlp/rbafyudfduvc.htm

Thanks again.

"CRPence" <CRP...@vnet.ibm.com> escribió en el mensaje
news:huc2lp$85q$1...@speranza.aioe.org...

[CRPence]

On 07-Jun-2010 05:46, CENTRINO wrote:
>
> I have tested your first approach and then querying it works fine
> for me.

Be sure to use the DSPFD *MBRLIST only when there are few users
performing database [source or data] member activity against the
selected physical files which have many members; e.g. performed
overnight or other off-hours\low-activity times. In order to ensure
proper counts and other results are correct, a file processed for
DSPFD *MBRLIST is locked in a manner that prevents ADDPFM\RMVM
activity while the member list is obtained from that file.

> Now I have curiosity about UDFs. I have never used them.
>

> żThe UDF code you wote is to be compiled? If so, what kind of
> source is it? Compiler to use?

The source code is SQL. The code could be compiled into a *QMQRY
or compiled as embedded SQL in a HLL program. However, most
typically, the SQL would just be interpreted; i.e. run as dynamic
SQL. Dynamically the SQL would typically be processed by any of the
provided SQL statement processors such as RUNSQLSTM, the iNav Run
SQL", or by an in-house SQL statement processor.

> .. Will it create an object in the system? in which library?

The SQL CREATE will typically create its corresponding object
into the specified library or the default SCHEMA according to the
/naming/ rules, either *SYS or *SQL; using CURRENT SCHEMA in either
case, or using *CURLIB for system naming and /authorization id/ for
SQL naming.

However there are scenarios where an object will not be created,
or the created object exists only for tracking. A CREATE ALIAS
creates an /object/, but the object is merely an implementation
object. An INDEX on any other RDBMS will just be an entry in the
catalog unlike the database *FILE object created by the DB2 for i.
The [optional] CREATE PROCEDURE for the QSYS/QCMDEXC would be an
example of where an object is not created, and its representation is
merely an entry in the catalog; if the external stored procedure is
created, the definition [of the stored procedure] merely redirects
to the existing program object which is EXTERNAL to the SQL.

> Can it be run and stored? from which interface: Iseries Access
> for windows, interactive sql?

After the statement is run, from whatever interface, the UDF
exists as an entry in the catalog and as a procedure [a module
bound] in a service program.

> I have been searching about it, but the only thing I can find is
> an explanation about what an UDF is, but not how to run or

> restore them:
> http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/sqlp/rbafyudfduvc.htm

In the expanded-view of the "Contents" frame for the topic "Using
user-defined functions", where the above link to "UDF concepts" is
located, are descriptions of how to use and considerations for the
save\restore of UDFs:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyudfuudf.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafysaveconsider.htm

The example I wrote [a utility version only, with effectively no
error\recovery processing] is an "SQL Scalar Function", not an
External Function:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyudfsql.htm

The example I gave /ran the UDF/ by referencing the function in
the column\expression list of a SELECT; i.e. SELECT CMDEXEC(...)
FROM... which cause the function to be invoked for each row
selected. Such a function, a scalar function, could be used in a
SET, VALUES, or VALUES INTO as well; e.g. SET
MyVariable=CMDEXEC(...) just as might be done for any built-in
scalar like SET MyVariable=CONCAT(...)

Regards, Chuck

>
> "CRPence"<CRP...@vnet.ibm.com> escribió:

>> On 04-Jun-2010 13:16, CENTRINO wrote:
>>>
>>> I'd need to find all source members in a system.
>>>
>>> Then I'd have to put their names, file name and library and
>>> type of source in a file so I can have a dictionary ...
>>>
>>> What would be the approach to do so?
>>>
>>
>> The following requests would generate some more data than
>> desirable, which can then be subsetted:
>>
>> CRTDUPOBJ QAFDMBRL QSYS *FILE QTEMP FDMBRL
>> CHGPF QTEMP/FDMBRL SIZE(*NOMAX)
>> ADDPFM QTEMP/FDMBRL SomeName
>> DSPFD *ALLUSR/*ALL *MBRLIST *OUTFILE FILEATR(*PF)
>> OUTFILE(QTEMP/FDMBRL) OUTMBR(*FIRST *ADD)
>>
>> A quick test suggests the following UDF invoked in a SELECT of
>> the desired libraries & files from SYSTABLES in QSYS2 will get
>> the above information more directly for just the source
>> physical files. Best to pare down the selected libraries until

>> after verifying the UDF& query effect the desired output.