ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

files and fields

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • files and fields

    Hi,

    If a physical file has multiple dependent logical files on it and if i want
    to search a specific key field defined in one of these dependent logical files so instead of searching
    each file manually isn't there any other easier and faster way to get this information?


    Thanks...

  • #2
    Could you explain in more detail what you mean by "searching each file manually". I can't guess whether you're trying to find out which file has that key field name, or which file has a record with that key value.

    By the way, the title of your post ("files and fields") doesn't really describe the actual topic.

    Comment


    • #3
      1) I meant that manually we need to go each time to check DSPFFD in that file and then need to check whether that specific key field which we are looking is present in that specific file or not.

      2) I meant here key fields name not their value.


      Thanks...

      Comment


      • #4
        My earlier reply got flagged as potential spam...sigh.
        Can someone un-flag it ?
        Last edited by MFisher; January 24, 2022, 03:57 PM.

        Comment


        • #5
          I was specifically looking for the first option.


          Thanks a lot !

          ,

          Comment


          • #6
            Reposting...

            1) You want to know every logical on Physical_A that includes a specific field ?
            Or
            2) every logical that uses a specific field as part of the key ?

            I think I found a way for both options.
            (Based on quick search & minimal testing. Might be a better way)

            First:
            Code:
            DSPDBR FILE(MyLib/MyFile) OUTPUT(*OUTFILE) OUTFILE(MyLib/OUTF_NAME)
            ( Is there a system view equivalent to DSPDBR ? )

            1)
            Code:
            select * from qsys2.syscolumns
            join MyLib.OUTF_NAME on table_schema = WHRLI and TABLE_NAME = WHREFI
            JOIN QSYS2.SYSTABLES S on S.table_schema = C.table_schema and S.TABLE_NAME = C.TABLE_NAME and TABLE_TYPE = 'L'
            where COLUMN_NAME = 'My_Column'
            ORDER BY C.TABLE_NAME , ORDINAL_POSITION
            2)
            Code:
            Select K.* from QSYS.QADBXREF X
            left join QSYS.QADBKFLD K on DBXFIL = DBKFIL and DBXLIB = DBKLIB
            join MyLib.OUTF_NAME on DBXLIB = WHRLI and DBXFIL = WHREFI
            where DBXATR = 'LF' and DBKFLD = 'My_Column'
            ORDER BY DBXFIL , DBKPOS
            Last edited by MFisher; January 25, 2022, 09:57 AM.

            Comment


            • #7
              Have you ever had a look to the SYSPARTITIONINDEXSTAT catalog view?
              It displays all (keyed) access path. Then such search through the COLUMN_NAMES:

              Code:
              Select *
              from SysPartitionIndexStat
              Where System_Table_Schema = 'YOURLIB'
              and System_Table_Name = 'YOURPF'
              and Column_Names like '%SEARCHKEY%';

              Comment


              • #8
                Originally posted by B.Hauser View Post
                Have you ever had a look to the SYSPARTITIONINDEXSTAT catalog view?
                It displays all (keyed) access path. Then such search through the COLUMN_NAMES:

                Code:
                Select *
                from SysPartitionIndexStat
                Where System_Table_Schema = 'YOURLIB'
                and System_Table_Name = 'YOURPF'
                and Column_Names like '%SEARCHKEY%';
                Good alternate solution. Thanks
                Last edited by MFisher; January 26, 2022, 12:30 PM.

                Comment


                • #9
                  but if it were any normal field and if it exists in multiple files and different libraries then how to find that details?


                  Thanks..

                  Comment


                  • #10
                    You can probably modify the above examples to get what you're looking for. Omit library in your WHERE clause.
                    I'm not clear on what you want in your most recent post.

                    Comment


                    • #11
                      I was asking that if it is any field (not specifically the key field of any file whether it's PF or LF ) then how can we find it's presence across IBM i server wherever it exists in which libraries and in which files.


                      So for this how can we get that information using SQL query or through any other ways ( apart from searching it by doing DSPFFD in each file and in each library individually in the IBM i (AS/400) server)?



                      Thanks...

                      Comment


                      • #12
                        You can do it by doing exactly what MFisher already said, by modifying the earlier examples. He provided you an example that searches by column name, table name, and library name. Change it so it only searches by column name

                        Comment

                        Working...
                        X