Can't use SQL Assistant in DB2/400 (run in AS400)

Can not use SQL Assistant in DB2/400 (run in AS400), who can help me !! tks...

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...
.