Hello,

I have an IFS folder with thousands of XML files.

And I am developing an application coded in RPG, that will list on a subfile, each IFS file name that contain a specific search string.

My program is working as expected, but the "grep" took about 4 to 5 minutes to return successfully with the search results.

Here are my steps, please let know what I am doing Wrong OR what I can do to increase the performance.

One thing I can think is that there are 745 files in the folder that i am searching. 

And size of each file range from 18 KB up to 1 MB. Is that too many files & size are huge ?

So far i am only testing in a Test environment. And the production system could have up to a half a million IFS files.

1.  Change to Directory "/i3pl/XX/arc"

Hello,

I have an IFS folder with thousands of XML files.

And I am developing an application coded in RPG, that will list on a subfile, each IFS file name that contain a specific search string.

My program is working as expected, but the "grep" took about 4 to 5 minutes to return successfully with the search results.

Here are my steps, please let know what I am doing Wrong OR what I can do to increase the performance.

One thing I can think is that there are 745 files in the folder that i am searching. 

And size of each file range from 18 KB up to 1 MB. Is that too many files & size are huge ?

So far i am only testing in a Test environment. And the production system could have up to a half a million IFS files.

1.  Change to Directory "/i3pl/XX/arc"

It is not the answer for your problem ... but it can be easily (and much easier as with RPG) done with (embedded) SQL.

The following SELECT returns all IFS-Files under /home/Hauser including all Sub-trees which contain 'whatever'.

The only thing you have to consider is, the statement has to be excecuted under commitment control!

Select Path_Name

  From Table (Qsys2.Ifs_Object_Statistics(Start_Path_Name     => '/home/Hauser',

                                          Subtree_Directories => 'YES',

                                          Object_Type_List    => '*ALLSTMF')) x

  Where   Lower(Get_Clob_From_File(Path_Name)) like '%whatever%'

  Order By Path_Name      ;   

Hello,

I have an IFS folder with thousands of XML files.

And I am developing an application coded in RPG, that will list on a subfile, each IFS file name that contain a specific search string.

My program is working as expected, but the "grep" took about 4 to 5 minutes to return successfully with the search results.

Here are my steps, please let know what I am doing Wrong OR what I can do to increase the performance.

One thing I can think is that there are 745 files in the folder that i am searching. 

And size of each file range from 18 KB up to 1 MB. Is that too many files & size are huge ?

So far i am only testing in a Test environment. And the production system could have up to a half a million IFS files.

1.  Change to Directory "/i3pl/XX/arc"

Hi Birgitta,

Thank you for your answer.

I see that using Get_Clob_From_File table function will search within contents of the file.

May I ask if you have a simple example of any embedded SQL in RPG that will show how to execute the statement under commitment control.

It is not the answer for your problem ... but it can be easily (and much easier as with RPG) done with (embedded) SQL.

The following SELECT returns all IFS-Files under /home/Hauser including all Sub-trees which contain 'whatever'.

The only thing you have to consider is, the statement has to be excecuted under commitment control!

Select Path_Name

  From Table (Qsys2.Ifs_Object_Statistics(Start_Path_Name     => '/home/Hauser',

                                          Subtree_Directories => 'YES',

                                          Object_Type_List    => '*ALLSTMF')) x

  Where   Lower(Get_Clob_From_File(Path_Name)) like '%whatever%'

  Order By Path_Name      ;   

Hello,

I have an IFS folder with thousands of XML files.

And I am developing an application coded in RPG, that will list on a subfile, each IFS file name that contain a specific search string.

My program is working as expected, but the "grep" took about 4 to 5 minutes to return successfully with the search results.

Here are my steps, please let know what I am doing Wrong OR what I can do to increase the performance.

One thing I can think is that there are 745 files in the folder that i am searching. 

And size of each file range from 18 KB up to 1 MB. Is that too many files & size are huge ?

So far i am only testing in a Test environment. And the production system could have up to a half a million IFS files.

1.  Change to Directory "/i3pl/XX/arc"

You have to compile your embedded SQL Program with COMMIT *CHG.

Or you add a SET OPTION Statement (SQL first statement in your RPG source) with COMMIT = *CHG

 EXEC SQL                  
Set Option  Commit = *Chg;

Hi Birgitta,

Thank you for your answer.

I see that using Get_Clob_From_File table function will search within contents of the file.

May I ask if you have a simple example of any embedded SQL in RPG that will show how to execute the statement under commitment control.

It is not the answer for your problem ... but it can be easily (and much easier as with RPG) done with (embedded) SQL.

The following SELECT returns all IFS-Files under /home/Hauser including all Sub-trees which contain 'whatever'.

The only thing you have to consider is, the statement has to be excecuted under commitment control!

Select Path_Name

  From Table (Qsys2.Ifs_Object_Statistics(Start_Path_Name     => '/home/Hauser',

                                          Subtree_Directories => 'YES',

                                          Object_Type_List    => '*ALLSTMF')) x

  Where   Lower(Get_Clob_From_File(Path_Name)) like '%whatever%'

  Order By Path_Name      ;   

Hello,

I have an IFS folder with thousands of XML files.

And I am developing an application coded in RPG, that will list on a subfile, each IFS file name that contain a specific search string.

My program is working as expected, but the "grep" took about 4 to 5 minutes to return successfully with the search results.

Here are my steps, please let know what I am doing Wrong OR what I can do to increase the performance.

One thing I can think is that there are 745 files in the folder that i am searching. 

And size of each file range from 18 KB up to 1 MB. Is that too many files & size are huge ?

So far i am only testing in a Test environment. And the production system could have up to a half a million IFS files.

1.  Change to Directory "/i3pl/XX/arc"

Hi Birgitta,

I have followed your direction, but running into a problem.

I must be making a rookie mistake & I cannot identify what it is.

I am getting SQLSTATE = '42703' with SQLCOD = -206 after Prepare and Declare.

An undefined column or parameter name was detected.

Here is my SQL string:

Select Path_Name From Table(Qsys2.IFS_Object_Statistics(Start_Path_Name => '/i3pl/CD/arc/', Subtree_Directories => 'YES', Object_Type_List => '*ALLSTMF')) X Where Lower(Get_Clob_From_File(Path_Name)) like '%00353970985316338146%'    

Here is rest of my research thru debug & the SQLRPGLE source:

MY_RAW_SQL_STR =                                                                   ....5...10...15...20...25...30...35...40...45...50...55...60        1   'Select Path_Name From Table(Qsys2.IFS_Object_Statistics(Star'      61   't_Path_Name => '/i3pl/CD/arc/', Subtree_Directories => 'YES''     121   ', Object_Type_List => '*ALLSTMF')) X Where Lower(Get_Clob_Fr'     181   'om_File(Path_Name)) like '%00353970985316338146%'           '     241   '                                                            '     301   '                                                            '     361   '                                                            '     421   '                                                            '     481   '                    '       After Prepare and Declare ... An undefined column or parameter name was detected. SQLSTATE = '42703' SQLCOD = -000000206.         Class Code 42: Syntax Error or Access Rule Violation42703 An undefined column or parameter name was detected.After Close ...SQLSTATE = '24501'     SQLCOD = -000000501.   After Open ...SQLSTATE = '26501'       SQLCOD = -000000514.     Class Code 26: Invalid SQL Statement Identifier26501 The statement identified does not exist.After Fetch SQLSTATE = '24501'                                                         SQLCODE = -501  Class Code 24: Invalid Cursor State24501 The identified cursor is not open.         exec SQL           Set Option Commit = *Chg;         My_Raw_SQL_Str =           'Select Path_Name ' +           'From Table(' +                 'Qsys2.IFS_Object_Statistics(' +                     'Start_Path_Name => ' +                          Quote + Source_Dir + Quote +                     ', Subtree_Directories => ' +                          Quote + 'YES' + Quote +                     ', Object_Type_List => ' +                          Quote + '*ALLSTMF' + Quote +                                            ')' +                     ') X ' +           'Where Lower(Get_Clob_From_File(Path_Name)) like ' +                    Quote + '%' + %Trim(The_Search) + '%' + Quote;         exec SQL           Prepare Dynamic_SQL_Stmt From My_Raw_SQL_Str;         exec SQL           Declare The_Cursor Cursor for Dynamic_SQL_Stmt;         exec SQL           Close The_Cursor;         exec SQL           Open The_Cursor;         doW 1 = 1;             Clear Full_File_Path;             exec SQL               Fetch Next From The_Cursor Into :Full_File_Path;             if %Subst(SQLSTATE : 1 : 2) = '00';

You have to compile your embedded SQL Program with COMMIT *CHG.

Or you add a SET OPTION Statement (SQL first statement in your RPG source) with COMMIT = *CHG

 EXEC SQL                  
Set Option  Commit = *Chg;

Hi Birgitta,

Thank you for your answer.

I see that using Get_Clob_From_File table function will search within contents of the file.

May I ask if you have a simple example of any embedded SQL in RPG that will show how to execute the statement under commitment control.

It is not the answer for your problem ... but it can be easily (and much easier as with RPG) done with (embedded) SQL.

The following SELECT returns all IFS-Files under /home/Hauser including all Sub-trees which contain 'whatever'.

The only thing you have to consider is, the statement has to be excecuted under commitment control!

Select Path_Name

  From Table (Qsys2.Ifs_Object_Statistics(Start_Path_Name     => '/home/Hauser',

                                          Subtree_Directories => 'YES',

                                          Object_Type_List    => '*ALLSTMF')) x

  Where   Lower(Get_Clob_From_File(Path_Name)) like '%whatever%'

  Order By Path_Name      ;   

Hello,

I have an IFS folder with thousands of XML files.

And I am developing an application coded in RPG, that will list on a subfile, each IFS file name that contain a specific search string.

My program is working as expected, but the "grep" took about 4 to 5 minutes to return successfully with the search results.

Here are my steps, please let know what I am doing Wrong OR what I can do to increase the performance.

One thing I can think is that there are 745 files in the folder that i am searching. 

And size of each file range from 18 KB up to 1 MB. Is that too many files & size are huge ?

So far i am only testing in a Test environment. And the production system could have up to a half a million IFS files.

1.  Change to Directory "/i3pl/XX/arc"

1. Why you are using dynamic (embedded) SQL? It is not necessary in this situation. (also if you are only searching for numbers, there is no need to wrap the Get_CLOB_From_File with LOWER.
2. In the PREPARE Statement you missed the colon in front of My_Raw_SQL_Str

Hi Birgitta,

I have followed your direction, but running into a problem.

I must be making a rookie mistake & I cannot identify what it is.

I am getting SQLSTATE = '42703' with SQLCOD = -206 after Prepare and Declare.

An undefined column or parameter name was detected.

Here is my SQL string:

Select Path_Name From Table(Qsys2.IFS_Object_Statistics(Start_Path_Name => '/i3pl/CD/arc/', Subtree_Directories => 'YES', Object_Type_List => '*ALLSTMF')) X Where Lower(Get_Clob_From_File(Path_Name)) like '%00353970985316338146%'    

Here is rest of my research thru debug & the SQLRPGLE source:

MY_RAW_SQL_STR =                                                                   ....5...10...15...20...25...30...35...40...45...50...55...60        1   'Select Path_Name From Table(Qsys2.IFS_Object_Statistics(Star'      61   't_Path_Name => '/i3pl/CD/arc/', Subtree_Directories => 'YES''     121   ', Object_Type_List => '*ALLSTMF')) X Where Lower(Get_Clob_Fr'     181   'om_File(Path_Name)) like '%00353970985316338146%'           '     241   '                                                            '     301   '                                                            '     361   '                                                            '     421   '                                                            '     481   '                    '       After Prepare and Declare ... An undefined column or parameter name was detected. SQLSTATE = '42703' SQLCOD = -000000206.         Class Code 42: Syntax Error or Access Rule Violation42703 An undefined column or parameter name was detected.After Close ...SQLSTATE = '24501'     SQLCOD = -000000501.   After Open ...SQLSTATE = '26501'       SQLCOD = -000000514.     Class Code 26: Invalid SQL Statement Identifier26501 The statement identified does not exist.After Fetch SQLSTATE = '24501'                                                         SQLCODE = -501  Class Code 24: Invalid Cursor State24501 The identified cursor is not open.         exec SQL           Set Option Commit = *Chg;         My_Raw_SQL_Str =           'Select Path_Name ' +           'From Table(' +                 'Qsys2.IFS_Object_Statistics(' +                     'Start_Path_Name => ' +                          Quote + Source_Dir + Quote +                     ', Subtree_Directories => ' +                          Quote + 'YES' + Quote +                     ', Object_Type_List => ' +                          Quote + '*ALLSTMF' + Quote +                                            ')' +                     ') X ' +           'Where Lower(Get_Clob_From_File(Path_Name)) like ' +                    Quote + '%' + %Trim(The_Search) + '%' + Quote;         exec SQL           Prepare Dynamic_SQL_Stmt From My_Raw_SQL_Str;         exec SQL           Declare The_Cursor Cursor for Dynamic_SQL_Stmt;         exec SQL           Close The_Cursor;         exec SQL           Open The_Cursor;         doW 1 = 1;             Clear Full_File_Path;             exec SQL               Fetch Next From The_Cursor Into :Full_File_Path;             if %Subst(SQLSTATE : 1 : 2) = '00';

You have to compile your embedded SQL Program with COMMIT *CHG.

Or you add a SET OPTION Statement (SQL first statement in your RPG source) with COMMIT = *CHG

 EXEC SQL                  
Set Option  Commit = *Chg;

Hi Birgitta,

Thank you for your answer.

I see that using Get_Clob_From_File table function will search within contents of the file.

May I ask if you have a simple example of any embedded SQL in RPG that will show how to execute the statement under commitment control.

It is not the answer for your problem ... but it can be easily (and much easier as with RPG) done with (embedded) SQL.

The following SELECT returns all IFS-Files under /home/Hauser including all Sub-trees which contain 'whatever'.

The only thing you have to consider is, the statement has to be excecuted under commitment control!

Select Path_Name

  From Table (Qsys2.Ifs_Object_Statistics(Start_Path_Name     => '/home/Hauser',

                                          Subtree_Directories => 'YES',

                                          Object_Type_List    => '*ALLSTMF')) x

  Where   Lower(Get_Clob_From_File(Path_Name)) like '%whatever%'

  Order By Path_Name      ;   

Hello,

I have an IFS folder with thousands of XML files.

And I am developing an application coded in RPG, that will list on a subfile, each IFS file name that contain a specific search string.

My program is working as expected, but the "grep" took about 4 to 5 minutes to return successfully with the search results.

Here are my steps, please let know what I am doing Wrong OR what I can do to increase the performance.

One thing I can think is that there are 745 files in the folder that i am searching. 

And size of each file range from 18 KB up to 1 MB. Is that too many files & size are huge ?

So far i am only testing in a Test environment. And the production system could have up to a half a million IFS files.

1.  Change to Directory "/i3pl/XX/arc"