Tweet
Hello guys, quick question, i want to use sql to search member descriptions (the ones that appear on WRKOBJPDM), but i don't know the name and library of the file that contains this info. Can you tell me how to find this info? Thanks in advance
-
-
Re: Search member text that appears on WORKOBJPDM
There isn't one for member text.
Do a DSPFD FILE(yourlib/QxxxSRC) TYPE(*MBRLIST) OUTPUT(*OUTFILE) OUTFILE(QTEMP/xxxfile)
Then SQL the outfile.Denny
If authority was mass, stupidity would be gravity. -
Re: Search member text that appears on WORKOBJPDM
Thanks, that will work.Originally posted by Denny View PostComment
-
Re: Search member text that appears on WORKOBJPDM
Is there a way to search inside the source of each member? I want to use commands like FNDSTRPDM but with more than one search pattern, so i can combine them and further refine my searchComment
-
Re: Search member text that appears on WORKOBJPDM
This is an old one. I wrote this in 1985. It will let you search for 64 patterns of up to 20 characters each.
Command prompt:
Source for command, CL and RPG program attached.Code:Scan source (SCANSRC) Type choices, press Enter. Source file name . . . . . . . . Name Library name . . . . . . . . . *LIBL Name, *LIBL Member or *ALL . . . . . . . . . *ALL Name, *ALL Pattern(s) to scan for: 64@20 . + for more values Pattern length (1-20) . . . . . 20 1-20 Translate to upper case . . . . *YES *YES, *NO, Y, N, 0, 1 Trim off trailing blanks . . . . *YES *YES, *NO, Y, N, 0, 1 Wild card character . . . . . . Character value Output empty report or summary *YES *YES, *NO, *SUM Output file name or *NONE . . . *NONE Name, *NONE Library name . . . . . . . . . *LIBL Name, *LIBL Output file member name . . . . *FIRST Name, *FIRST Outfile member option . . . . . *REPLACE *REPLACE, *ADDDenny
If authority was mass, stupidity would be gravity.Comment
-
Re: Search member text that appears on WORKOBJPDM
thanks! i will try this and report back.Comment
-
Re: Search member text that appears on WORKOBJPDM
When I have to do such a thing, I use the TechTip "FileToClob" UDF published on MC Press Online on May 2003 created by Michael Sansoterra. This utility can read source members and find strings using the /QSYS.LIB file system as mentioned by the end of the article. It's very handy to search for compound search patterns in source files members.Originally posted by fjleon
You can read the article from here.
I paste below the RPG program as the download link is broken.
BTW, the search way is only usable under i Navigator using the "Run SQL Scripts" tag.
Program SQLRPGLE FileToClob
UDFPHP Code:h DftActGrp( *no )
h Option( *srcstmt: *nodebugio )
//-------------------------------------------------------------------
// FILETOCLOB - SQL User Defined Function (UDF)
// Convert an IFS file to a CLOB using SQL CLOB_FILE data type
//
// This program is meant to be used an SQL UDF. Its purpose is
// to receive a qualified file name on the IFS and return
// the file's contents as a CLOB locator.
//
// Member Type: SQLRPGLE
//
//
// Example: Say file ITEMMASTER has a column TEXT_COL which
// contains the qualified file name for an IFS file.
//
// SELECT ITEM, FILETOCLOB(TEXT_COL)
// FROM ITEMMASTER
//
// SQL Create Function Definition:
//
// Create Function xxxxxxxxxx/FILETOCLOB (ifs_file VarChar(255))
// Returns CLOB(1M) As Locator
// Language RPGLE
// Parameter Style SQL
// External
// Reads SQL Data
// Deterministic
// Returns NULL on NULL Input
//
// Don't forget, in order for SQL to use the function, either the
// function's library has to be in the library list or the
// library name must be appended to the SQL path using the
// SET PATH statement. Also, adopted authority does not apply
// to IFS files.
//--------------------------------------------------------------------
//
// These are special SQL data types. As of V5R2, SEU still
// does not recognize the keywords for these SQL data types.
//īJust ignore the editor's error message.
//
D CLOBData S SQLTYPE(CLOB_LOCATOR)
D CLOBFile S SQLTYPE(CLOB_FILE)
// Prototype entry parameters
D FileToCLOB PR
D pFileName 255a Varying
D pCLOBData Like(CLOBdata)
* NULL Indicator Variables (Small Integers)
D pFileNameInd 5i 0
D pCLOBDataInd 5i 0
* Other UDF parameters
D pSQLState 5a
D pFunctionName 139a
D pSpecificName 128a
D pMsgText 70a Varying
// Procedure Interface
D FileToCLOB PI
D pFileName 255a Varying
D pCLOBData Like(CLOBdata)
* NULL Indicator Variables (Small Integers)
D pFileNameInd 5i 0
D pCLOBDataInd 5i 0
* Other UDF parameters
D pSQLState 5a
D pFunctionName 139a
D pSpecificName 128a
D pMsgText 70a Varying
/free
// Main line
Exec SQL Set Option Commit = *NONE;
*InLR = *On;
// Pre-compiler constants:
// SQFRD (2) Read
// SQFCRT (8) Create
// SQFOVR (16) Overwrite
// SQFAPP (32) Append
// Set Attributes of CLOBFILE: Mode, name, name length
CLOBfile_fo = SQFRD;
CLOBfile_name = pFileName;
CLOBfile_nl = %Len(%trim(pFileName));
// Convert IFS File to CLOB and return Locator
Exec Sql Set :CLOBData = :CLOBFile;
// If the SQL State is not OK, then set the function result
// indicator to NULL. Otherwise, return the CLOB locator.
// Unfortunately, the SQL state doesn't indicate whether
// a file not found is due to a security violation or an
// invalid path/file name.
Select;
// File Not Found/Invalid Security - Return Null
When SQLSTT = '428A1';
pCLOBDataInd = -1;
// Other Error - Function should fail
When SQLSTT <> '00000';
pSQLState = SQLSTT;
pMsgText = %Subst(SQLERM:1:SQLERL);
// OK - Return Data
Other;
pCLOBDataInd = *Zero;
pCLOBData = CLOBdata;
EndSl;
Return;
ExamplePHP Code:DROP FUNCTION FILETOCLOB;
CREATE FUNCTION FILETOCLOB (IFS_FILE VARCHAR(255))
RETURNS CLOB(1M) AS LOCATOR
LANGUAGE RPGLE
PARAMETER STYLE SQL
EXTERNAL
READS SQL DATA
DETERMINISTIC
RETURNS NULL ON NULL INPUT;
Search patterns "ENVVAR" and "CPFA980". I want to display all members in MyLib/QCLLESRC that contain both search strings.
Proceed in 2 steps :
1/ DSPFD --> OutFile
2/ i Navigator/ Run SQL ScriptsPHP Code:DSPFD FILE(MyLib/QCLLESRC) TYPE(*MBRLIST) OUTPUT(*OUTFILE) OUTFILE(MyLib/MBRLIST)
And I get a list of all members in MyLib/QCLLESRC that contain "ENVVAR" and "CPFA980".PHP Code:With CL_Members As (
Select MLLib As SrcLib, MLFile As SrcFile, MLName As SrcMbr,
Ucase( FileToClob( '/qsys.lib/'
Concat Trim(MLLib)
Concat '.lib/'
Concat Trim(MLFile)
Concat '.file/'
Concat Trim(MLName)
Concat '.mbr')) As SrcText
From MyLib.MbrList
Where MLSEU2 In ('CLP','CLLE')
)
Select SrcLib, SrcFile, SrcMbr
From CL_Members
Where SrcText Like '%ENVVAR%'
And SrcText Like '%CPFA980%';
If I want either one pattern OR the other I just change the And by Or in the Where predicate above.
I can also do
To get the list of members that have "ENVVAR" and not "CPFA980" in their content.PHP Code:Where SrcText Like '%ENVVAR%'
and SrcText Not Like '%CPFA980%';
PhilippeComment
Comment