Results 1 to 7 of 7

Thread: SQL help needed

  1. #1
    Join Date
    Apr 2011
    Posts
    31

    Red face Unanswered: SQL help needed

    There are 2 schemas/libararies in the database, A & B.
    The qsys2.sysindexes table has column index_name and schema_name and contains all indexes for both schemas A & B.
    The schema A has 183 indexes. The schema B has 1 index by name of idxemp which is in the schema A also.
    I am looking for a query whic gives output showing me 182 indexes in schema A which are not in the schema B
    I need this so that I can add missing indexes into schema B.


    Thanks
    Last edited by ajh; 04-29-11 at 10:02. Reason: Added Information

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I know a little about iSeries.

    But, the query may be like this...
    Code:
    SELECT VARCHAR(index_schema , 10) AS index_schema
         , VARCHAR(index_name   , 30) AS index_name
         , VARCHAR(table_schema , 10) AS table_schema
         , VARCHAR(table_name   , 30) AS table_name
     FROM  qsys2.sysindexes si_a
     WHERE index_schema = 'SCHEMA A'
       AND NOT EXISTS
           (SELECT 0
             FROM  qsys2.sysindexes si_b
             WHERE si_b.index_schema = 'SCHEMA B'
               AND si_b.index_name   = si_a.index_name
    ;

  3. #3
    Join Date
    Apr 2011
    Posts
    31

    Red face Please Help

    There are 2 schemas/libararies in the database, A & B.
    The qsys2.sysindexes table has column index_name and schema_name and contains all indexes for both schemas A & B.
    The schema A has 183 indexes. The schema B has 1 index by name of idxemp which is in the schema A also.
    I am looking for a query whic gives output showing me 182 indexes in schema A which are not in the schema B
    I need this so that I can add missing indexes into schema B.


    Thanks

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The schema A has 183 indexes. The schema B has 1 index by name of idxemp which is in the schema A also.
    I am looking for a query whic gives output showing me 182 indexes in schema A which are not in the schema B
    Are you kidding me?

    What the problem in a query like this?
    Code:
    SELECT
           VARCHAR(index_name   , 30) AS index_name
         , VARCHAR(table_schema , 10) AS table_schema
         , VARCHAR(table_name   , 30) AS table_name
     FROM  qsys2.sysindexes
     WHERE index_schema =  'A'
       AND index_name   <> 'idxemp'
    ;

  5. #5
    Join Date
    Apr 2011
    Posts
    31

    Red face SQL Help

    Quote Originally Posted by tonkuma View Post
    Are you kidding me?

    What the problem in a query like this?
    Code:
    SELECT
           VARCHAR(index_name   , 30) AS index_name
         , VARCHAR(table_schema , 10) AS table_schema
         , VARCHAR(table_name   , 30) AS table_name
     FROM  qsys2.sysindexes
     WHERE index_schema =  'A'
       AND index_name   <> 'idxemp'
    ;
    thanks for your reply. Its an example that i have given to you.
    The schema A actually has 183 indexes and schema B has 130.
    Now 53 indexes out of 183 in the A schema are not in the schema B. Thats why the 53 indexes difference.
    So i want a list of the 53 indexes in schema A that are not in Schema B.
    Please Help.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that I already answered like this...

    Code:
    SELECT ...
    ...
    
     WHERE index_schema = 'SCHEMA A'
       AND NOT EXISTS
           (SELECT 0
    ...

  7. #7
    Join Date
    Apr 2011
    Posts
    31

    Smile Thanks

    Thanks for your time and help i got it working fine using your guidance.
    appreciation !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •