SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Can't use SQL Assistant in DB2/400 (run in AS400)

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Can't use SQL Assistant in DB2/400 (run in AS400)
Author Message
martin.yu



Joined: 20 Sep 2007
Posts: 1
Country: Taiwan, Province of China

Post Can't use SQL Assistant in DB2/400 (run in AS400) Reply with quote
Can not use SQL Assistant in DB2/400 (run in AS400), who can help me !! tks...
Thu Sep 20, 2007 9:14 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Hi,

You need to modify database catalog queries for DB2, which are available in SQL Assistant options and make them compatible with DB2 for iSeries. The pre-configured queries that come with SQL Assistant are for DB2 UDB version. so you need to change them for iSeries.

I cannot help with all queries as I don't have OS/400 nearby.

Here are just some ideas

The current query in options for DB2 Schemas is like this
Code:
select
   RTRIM(schemaname), 'SC'
from
   syscat.schemata

union all
select
   RTRIM(t.grantee), 'SL'
from
   (
   select grantee
     from syscat.dbauth
    group by grantee
   ) t

order by 1


For iSeries you should change it as

Code:
SELECT DISTINCT TABLE_SCHEMA
FROM QSYS2.SYSTABLES
WHERE TABLE_TYPE IN ('T', 'P')
ORDER BY 1



The current query in options for DB2 objects is like this

Code:
select
   tabname,
   case when type in ('V','W') then 'VI' else 'TA' end
from
   syscat.tables
where
   tabschema = :SCHEMA_NAME

union all
select
   routinename,
   case
   when functiontype = 'T' then 'TF'
   when routinetype = 'F' then 'FU'
   else 'PR' end
from
   syscat.routines
where
   routineschema = :SCHEMA_NAME
and routinetype in ('F','P')

For iSeries you should change it as

Code:
SELECT ROUTINE_NAME,
   CASE ROUTINE_TYPE WHEN 'FUNCTION' THEN 'FU' ELSE 'PR' END
FROM QSYS2.SYSROUTINES
WHERE ROUTINE_SCHEMA = :SCHEMA_NAME
AND ROUTINE_TYPE IN ('FUNCTION', 'PROCEDURE')

UNION ALL
SELECT TABLE_NAME,
   CASE TABLE_TYPE WHEN 'V' THEN 'VI' ELSE 'TA' END
FROM QSYS2.SYSTABLES
WHERE TABLE_SCHEMA = :SCHEMA_NAME
AND TABLE_TYPE IN ('T', 'V')


The current query in options for DB2 table/view columns is like this

Code:

select
   colname,
   case when typename in ('CHAR','VARCHAR') then typename CONCAT '(' CONCAT RTRIM(CHAR(length)) CONCAT ')' else typename end,
   nulls
from
   syscat.columns
where
   tabschema = :SCHEMA_NAME
and tabname = :OBJECT_NAME
order by
   colon

For iSeries you should change it as

Code:
SELECT
   COLUMN_NAME,
   (
      CASE
         WHEN DATA_TYPE = 'VARCHAR' OR
         DATA_TYPE = 'LONG VARCHAR' OR
         DATA_TYPE = 'BLOB' OR
         DATA_TYPE = 'CLOB' OR
         DATA_TYPE = 'DBCLOB' OR
         DATA_TYPE = 'GRAPHIC' OR
         DATA_TYPE = 'VARGRAPHIC' OR
         DATA_TYPE = 'LONG VARGRAPHIC' THEN RTRIM(DATA_TYPE) || '(' || RTRIM(CHAR(LENGTH)) || ')'
         WHEN DATA_TYPE = 'CHARACTER' OR
         DATA_TYPE = 'CHAR' THEN 'CHAR(' || RTRIM(CHAR(LENGTH)) || ')'
         WHEN DATA_TYPE = 'DECIMAL' THEN 'DECIMAL(' || RTRIM(CHAR(NUMERIC_PRECISION)) || ',' || RTRIM(CHAR(NUMERIC_SCALE)) || ')'
         ELSE DATA_TYPE
      END
   ),
   posstr(IS_NULLABLE, 'Y') NULLS
FROM QSYS2.SYSCOLUMNS
WHERE TABLE_SCHEMA = :SCHEMA_NAME
AND TABLE_NAME = :OBJECT_NAME
ORDER BY ORDINAL_POSITION



And so on...
.
Thu Sep 20, 2007 10:05 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.