IBM i Global

 View Only
Expand all | Collapse all

SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

  • 1.  SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Fri August 02, 2024 05:40 AM

    Hi,

    I tried this SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i :-
     
    "SELECT *                                      
    FROM TABLE(QSYS2.IFS_Object_Statistics(       
        Start_Path_Name => '/HOME/XYZ',         
        Subtree_Directories => 'YES'              
    )) x                                          
    WHERE LOWER(Path_Name) LIKE '/home/xyz%'    " 
    But I got this error now "Message ID . . . . . . :   SQL0332                                             
                                                                                   
                                                                                   
    Message . . . . :   Character conversion between CCSID 1200 and CCSID 65535    
      not valid. 
    Any idea to get the desired result here with any other modified SQL query for the same here please?
    Thanks..                        


    ------------------------------
    jerry ven
    ------------------------------


  • 2.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Sat August 03, 2024 02:29 AM

    Hi, 

    Could someone please respond if anyone has any idea on the same here? 

    Thanks. 


    Original Message


  • 3.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Sat August 03, 2024 05:01 PM
    Hello Jerry,

    I reran your exact query and did not encounter any errors. It was on IBM i v7.4 with all technology refreshes and all group fixes up to date. Also, my "Run SQl Scripts" job's CCSID was 37 (EBCDIC English) and the CCSID of the paths it processed was either 37, 819, or 1208 (i.e. all "translatable" to my job's CCSID 37 which is what the WHERE clause does behind the covers).

    So the following might be starting point to triage your issue further:
    • Your DB2 for i database engine is complaining that some of the strings have unspecified CCSID (that's what 65535 means). So a good guess would be that either some of the path names your query retrieves in the Path_Name column has CCSID of 65535, or, your actual job where the query runs has unspecified CCSID (=65535, I hope that is not the case!)
    • Make sure your jobs' CCSID is specified (i.e. it is not 65535!)
    • Make sure your sysadmin has installed all IBM i OS and DB2 related fixes on the system
    • Although less likely, there might be a bug in how the paths are getting translated for the WHERE clause evaluation. In that case, I would suggest to open a support ticket with IBM.

    You may try to re-run it with WHERE clause modify like so:

    WHERE LOWER(cast(Path_Name as varchar(255) CCSID 37 )) LIKE '/home/xyz%'
    ...or...
    WHERE LOWER(cast(Path_Name as varchar(255) CCSID 37 )) LIKE cast('/home/xyz%' as varchar(255) CCSID 37)

    ...where "37" would be CCSID of your job where you run the SQL query -- mine was 37.

    Hope this helps,
    Roman




    Original Message


  • 4.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Sun August 04, 2024 03:41 AM

    Hi,

    Thanks, but what i want is to see all the objects on that given IFS path so i tried this SQL query as per this link example here "Using SQL to list directories and files in IFS @ RPGPGM.COM"   

    SELECT PATH_NAME,OBJECT_TYPE,CREATE_TIMESTAMP,LAST_USED_TIMESTAMP,
       DATA_SIZE
FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/HOME/K21/','YES'))

    But instead of path name when i run this SQL query then i get '*POINTER'  like below:-

    Whereas I wanted to objects on that IFS path as the output of above SQL query in the PATH_NAME like as shown in the above link example. So what is wrong with my SQL query here why instead of that PATH_NAME I am seeing "*POINTER" and not the actual 'PATH_NAME' here?

    Just for example i have below objects on my IFS path but when i run above SQL query it does not show desired result neither the SQL query which i had posted in my first post shows the desired result here even after making suggested changes in my where clause :-

    So could some one please advise what wrong in my SQL query due to which iam seeing this '*POINTER'  instead of actual path name here?

    Thanks much...


    Original Message


  • 5.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Sun August 04, 2024 06:04 AM

    You should no longer use STRSQL it is stabilized since Release V4R5M0, i.e. everything that was added after to SQL is not supported. The path name is returned as DBCLOB (Double Byte Large Object) which was not yet supported in Release V4R5M0.

    Install IBM i Access Client Solutions (ACS) and then run your query with RUN SQL SCRIPTS ... and you will see all values

    IBM i Access Client Solutions Download:

    https://www.ibm.com/resources/mrs/assets/DownloadList?source=swg-ia&lang=en_US 



    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Selfemployed - Modernization-Education-Consulting on IBM i
    Kaufering
    +49 170 5269964
    ------------------------------

    Original Message


  • 6.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Sun August 04, 2024 08:21 AM

    Hi,

    1)      Thanks, running same SQL query on IBM i ACS under 'Run SQL scripts' did show the desired results here(with actual 'PATH_NAME'.).

    2)      However, I had run this SQL query using STRSQL on the 'V7R5M0' OS/400 version. still that SQL query showed that '*POINTER' so not sure whether it's really OS/400 version issue here?

    3)      When I ran this SQL query using STRSQL then got below results: -

    SELECT *                                  
    FROM TABLE(QSYS2.IFS_Object_Statistics(   
        Start_Path_Name => '/HOME/K21'   ,    
        Subtree_Directories => 'YES'          
    )) x                                      
    WHERE LOWER(cast(Path_Name as varchar(255) CCSID 37 )) LIKE '/home/xyz%'

    "



    4)      When I ran this SQL query using STRSQL: -

    S      "  SELECT *                                  
    FROM TABLE(QSYS2.IFS_Object_Statistics(   
        Start_Path_Name => '/HOME/K21'   ,    
        Subtree_Directories => 'YES'          
    )) x                                      
    WHERE LOWER(cast(Path_Name as varchar(255) CCSID 37 )) LIKE cast('/home/xyz%' as varchar(255) CCSID 37)

    "

    Then got below results: -


    5)      Using IBM I ACS 'RUN SQL Scripts' can we create a file like we can create in STRSQL when we do Shift + F1 in STRSQL like below:


    Then we select option '1' here then on below screen we can give option -'SELECT output'  as '3= File' 


    And then on below screen we find multiple options like '1=Create File' , '2=Replace File', '3=Create member' ,'4=Replace member' , '5=Add to member'  like these there are so many options commitment control,Date format, Date Separator,Time format, Time separator etc. for changing the session attributes there so can we get all these using 'RUN SQL Scripts' inside IBM i ACS tool  as well , If yes, Could someone please help by providing their navigation path for the same here?

    Thanks much..


    Original Message


  • 7.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Sun August 04, 2024 08:49 AM

    Sorry for typo for 3rd and 4th points in my previous post I meant from below SQL queries which gave the same results as shown in the screenshots for them: -

    3)  SELECT *                                  
    FROM TABLE(QSYS2.IFS_Object_Statistics(   
        Start_Path_Name => '/HOME/K21'   ,    
        Subtree_Directories => 'YES'          
    )) x                                      
    WHERE LOWER(cast(Path_Name as varchar(255) CCSID 37 )) LIKE '/home/k21%'

    4)  SELECT *                                  
    FROM TABLE(QSYS2.IFS_Object_Statistics(   
        Start_Path_Name => '/HOME/K21'   ,    
        Subtree_Directories => 'YES'          
    )) x                                      
    WHERE LOWER(cast(Path_Name as varchar(255) CCSID 37 )) LIKE cast('/home/k21%' as varchar(255) CCSID 37)

    Thanks much...


    Original Message


  • 8.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Mon August 05, 2024 02:04 AM

    Again: STRSQL is stabilized since a long time! Enhancements in Db2 and SQL after V4R5M0 are NOT included in the STRSQL Tool.

               There is no reason anymore to use  STRSQL. Not sure why you instist running your Query with STRSQL.

    The Path_Name is returned as DBCLOB (Double Byte Large Object) which was not supported in the old days and which is NOT supported in STRSQL.

    You may try to convert the DBCLOB column into a VARCHAR column.

    Select Cast(PATH_NAME as VarChar(256)) .....



    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Selfemployed - Modernization-Education-Consulting on IBM i
    Kaufering
    +49 170 5269964
    ------------------------------

    Original Message


  • 9.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Mon August 05, 2024 07:30 AM

    Birgitta,

    I agree with you 100%.  I can only think that Jerry might be dyslexic and confused V4R5 with V5R4.  That, and he might think he's a speed reader and skips a bunch of words.



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------

    Original Message


  • 10.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Mon August 05, 2024 09:53 AM

    as was mentioned don't use STRSQL.

    if you must, you will notice the "path_name" colum is a DBCLOB, so you would need to "cast" it to somehing the green screen understands.

    SELECT cast(Path_Name as varchar(100)) FROM                         
    TABLE(QSYS2.IFS_Object_Statistics( Start_Path_Name => '/HOME' ,
    Subtree_Directories => 'YES' )) x                                  



    ------------------------------
    Bryan Dietz
    ------------------------------

    Original Message


  • 11.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Mon August 05, 2024 11:30 AM

    thanks, but what about my other queries with respect to change session attributes inside STRSQL will 'RUN SQL Scripts' be able to provide all those things if yes, could someone please explain how?

    Thanks..


    Original Message


  • 12.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Mon August 05, 2024 12:12 PM

    What session attributes do you want to set (and why?)?



    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Selfemployed - Modernization-Education-Consulting on IBM i
    Kaufering
    +49 170 5269964
    ------------------------------

    Original Message


  • 13.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Mon August 05, 2024 12:22 PM

    Not a SINGLE one of those attributes that you displayed on the STRSQL screen will get the value instead of *POINTER to display.

    Again, either:

    1 - abandon STRSQL to Run SQL Scripts, or,

    2 - CAST that path name to a CHAR column.

    Another example of such a limitation:  Open up Run SQL Scripts, Edit, Examples, Insert from Examples and search for STRSQL.  In there they give another example of using VARCHAR to get a column to display.

    Also Notice on that example the   ;-(   emoticon immediately after STRSQL?  That example is from IBM and is to be taken as a statement that STRSQL does not, and never will, support the displaying of certain variable types without casting them to another variable type.



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------

    Original Message


  • 14.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Mon August 05, 2024 12:56 PM

    https://www.rpgpgm.com/2023/05/why-do-my-sql-results-show-pointer.html



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------

    Original Message


  • 15.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Mon August 05, 2024 01:22 PM

    Hi,

    1)What session attributes do you want to set (and why?)?
     
    Just for example as shown in my screenshots when we do SHIFT+F1 then we can create a new file ,add member etc. as shown in the screen shots so will 'RUN SQL Script' have such features? it is required very frequently whenever we want to create any file randomly or want to add records in existing member or inside a file and is very much useful feature of 'STRSQL'.
     
     
    2) >>Another example of such a limitation:  Open up Run SQL Scripts, Edit, Examples, Insert from Examples and search for STRSQL.  In there they give another example of using VARCHAR to get a column to display.--> where exactly could we get more explanation in which link exactly this example is shown ,  could we please get more clarification for this point ?
     
    >>Also Notice on that example the   ;-(   emoticon immediately after STRSQL?  That example is from IBM and is to be taken as a statement that STRSQL does not, and never will, support the displaying of certain variable types without casting them to another variable type. ---> Any link for this example please as I did not understand what did it mean ;-( emoticon do we mean to refer any link for this ? where exactly this example is present? could we please get more clarification here for this point?
     
     
     
    Thanks much...
     
     
     
     

    Original Message


  • 16.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Mon August 05, 2024 01:42 PM

    Jerry,

    As far as:

    Statement processing . . . . .   *RUN           *RUN, *VLD, *SYN    
    SELECT output  . . . . . . . .   1              1=Display, 2=Printer
                                                    3=File              

    and saving to a file, this is done differently in Run SQL Scripts.

    First, you have to ENABLE the saving of result sets.

    Go into Edit, Preferences

    Then you may need to stop/start Run SQL Scripts for this change to take effect.

    Then you can click on the results and save them to Excel, etc

    Now, if you want to create a new DB2 table on your IBM i you need to wrap your sql statement.  Using our example above you would change it to:

    Create table mylib.myfile as (
         select path_name, object_type, data_size, object_owner
           from table(qsys2.IFS_OBJECT_STATISTICS( 
                            start_path_name => '/usr',
                            subtree_directories => 'YES'))
            order by 3 desc
            limit 10
    ) with data
    ;



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------

    Original Message


  • 17.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Mon August 05, 2024 01:49 PM

    As far as

    <snip>

    >>Also Notice on that example the   ;-(   emoticon immediately after STRSQL?  That example is from IBM and is to be taken as a statement that STRSQL does not, and never will, support the displaying of certain variable types without casting them to another variable type. ---> Any link for this example please as I did not understand what did it mean ;-( emoticon do we mean to refer any link for this ? where exactly this example is present? could we please get more clarification here for this point?

    </snip>

    There is no link.  I am going to reformat my earlier response and maybe this will help you to digest it.

    Another example of such a limitation: 

    Open up Run SQL Scripts,

    Click on Edit,

    Click on Examples, Insert from Examples

    Search for STRSQL.  In there they give another example of using VARCHAR to get a column to display.



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------

    Original Message


  • 18.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Tue August 06, 2024 03:38 AM

    Hello Jerry,

    You could try to use DB2 for IBM i Query Manager (STRQM / STRQMQRY).
    I don't know if it is kept up to date or if it has some limitations like STRSQL.
    But I just tested and your query works fine there.

    And you can choose to save the result in a database file and you can choose to add records to an existing file. 



    ------------------------------
    Denis Roche
    ------------------------------

    Original Message


  • 19.  RE: SQL query to find objects located on IFS path on specific directory /subdirectory in IBM i

    Posted Tue August 06, 2024 07:56 AM
    STRSQL, STRQM, RUNSQLSTM, Run SQL Scripts, embedded sql in RPG can all save result sets the exact same way.
     
    If you have a statement which looks like this:
    SELECT *
    FROM MYLIB.MYFILE
     
    You simply wrap it with a line at the beginning and a line at the end and it becomes
     
    CREATE TABLE MYLIB.MYRESULTS AS (
    SELECT *
    FROM MYLIB.MYFILE
    ) WITH DATA


    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------

    Original Message