SQL help needed

ajh · #1 (**permalink**) 04-29-11, 06:49

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

tonkuma · #2 (**permalink**) 04-29-11, 08:45

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
;

ajh · #3 (**permalink**) 04-29-11, 09:17

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

tonkuma · #4 (**permalink**) 04-29-11, 09:35

Quote:

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'

;

ajh · #5 (**permalink**) 04-29-11, 09:41

Quote:

Originally Posted by tonkuma

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.

tonkuma · #6 (**permalink**) 04-29-11, 09:47

I think that I already answered like this...

Code:

SELECT ...
...

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

ajh · #7 (**permalink**) 04-29-11, 10:11

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

Internet Services

Web Development

Digital Marketing

Consumer Tech