Accessing QSYS2.SYSCOLUMNS from iSeries

Question

Post reply

Accessing QSYS2.SYSCOLUMNS from iSeries

swede

SSCommitted

Points: 1773
More actions
April 5, 2005 at 12:56 pm

#164161

Has anyone have any success accessing QSYS2.SYSCOLUMNS from iSeries with Query Analyzer?
I can run the following query from iSeries Naviagtor:
select table_schema, table_name, column_name,ordinal_position,
       data_type, length, numeric_scale, numeric_precision,
       is_nullable, has_default, column_default, ccsid,
       column_text
from qsys2.syscolumns
where table_name = 'AAGE200F'
         and table_schema = 'D500DATBQ'
order by table_schema, table_name, ordinal_position;
I have create a linked server called bisysb_systemcatalog in sql 2000. And can access other system catalog views through it, but not the syscolumns.
The query that I attempt to run in query analyzer is:
select table_schema, table_name, column_name,ordinal_position,
       data_type, length, numeric_scale, numeric_precision,
       is_nullable, has_default, column_default, ccsid,
       column_text
from bisysb_systemcatalog.bisysb.qsys2.syscolumns
where table_name = 'AAGE200F'
         and table_schema = 'D500DATBQ'
order by table_schema, table_name, ordinal_position;
bisysb is the catalog/schema name in iSeries.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

JaimeP Valued Member Points: 63 More actions · Answer 1

Try using

SELECT *

FROM OPENQUERY(bisysb_systemcatalog , 'select table_schema, table_name, column_name,ordinal_position,

data_type, length, numeric_scale, numeric_precision,

is_nullable, has_default, column_default, ccsid,

column_text

from qsys2.syscolumns

where table_name = 'AAGE200F'

and table_schema = 'D500DATBQ'

order by table_schema, table_name, ordinal_position')

swede SSCommitted Points: 1773 More actions · Answer 2

the four part name does work...just very slow.

I can run the same query under iSeries navigator and get results in matter of seconds. But through linked server it takes 45+ minutes. There are over 3 million rows on this table, but only 9 rows are in the results. The query explain show most of the work is done on iSeries not on SQL...so why is it taking so long to process.