Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    7

    Unanswered: [SOLVED]Convert query from MySQL to DB2

    Hello,
    I'm in a process of expanding my app to work with other databases and for that I need to convert the MySQL statements to
    DB2, InterBase, Oracle, ODBC, PostgreSQL, SQLite2 and SQLite3.
    It should be an easy task because there are only four statements I have to convert:

    Code:
    SHOW TABLES
    Code:
    SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='<schema_name>' AND TABLE_NAME='<table_name>'
    Code:
    SELECT REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA='<schema_name>' ORDER BY REFERENCED_TABLE_NAME, TABLE_NAME, REFERENCED_COLUMN_NAME, COLUMN_NAME
    Code:
    SELECT name, param_list FROM mysql.proc WHERE db='<schema_name>' AND type='PROCEDURE'
    Would you be so kind to tell me I it is possible to convert those statements to DB2 compatible ones, and if so what is their DB2 equivalent.
    Last edited by muisei; 08-23-11 at 17:03. Reason: Problem solved

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can use the "list tables" command (various options) or query the system catalog (SYCAT views). Check the DB2 Reference Manuals or InfoCenter for details. Please remember that DB2 LUW is different than DB2 z/OS or DB2 iSeries. If after reading the doc you still have a question, please post it here.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2011
    Posts
    7
    Thanks a lot

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by muisei View Post
    Code:
    SHOW TABLES
    For MySQL, this means that only the tables of the current schema are shown.
    So in DB2 that would be
    Code:
    LIST TABLES FOR SCHEMA <current schema name>
    Quote Originally Posted by muisei View Post
    Code:
    SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='<schema_name>' AND TABLE_NAME='<table_name>'
    Unfortunately, DB2 does not (yet) support the standard INFORMATION_SCHEMA catalog tables. Try something in the flavour of the following:
    Code:
    SELECT colname, typename||'('||char(length)||')',
           CASE nulls WHEN 'N' THEN 'NO' ELSE 'YES' END,
           <subquery on SYSCAT.INDEXCOLUSE>,
           DEFAULT, REMARKS
    FROM   syscat.columns
    WHERE  tabschema='<schema_name>' AND tabname = '<table_name>'
    Quote Originally Posted by muisei View Post
    Code:
    SELECT REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA='<schema_name>' ORDER BY REFERENCED_TABLE_NAME, TABLE_NAME, REFERENCED_COLUMN_NAME, COLUMN_NAME
    Again, not 100% similar, but more or less:
    Code:
    SELECT p.tabname, p.colname, d.tabname, d.colname
    FROM syscat.references r INNER JOIN syscat.keycoluse p
            ON r.constname = p.constname AND r.reftabschema=p.tabschema AND r.reftabname = p.tabname
            INNER JOIN syscat.keycoluse d
            ON r.constname = d.constname AND r.tabschema=d.tabschema AND r.tabname = d.tabname
    Quote Originally Posted by muisei View Post
    Code:
    SELECT name, param_list FROM mysql.proc WHERE db='<schema_name>' AND type='PROCEDURE'
    You'll need a recursive query to get a parameter list in the style of param_list; see SYSCAT.ROUTINEPARMS. (Joined with SYSCAT.ROUTINES.)
    This is a starting point:
    Code:
    SELECT r.routinename, 
    CASE p.rowtype WHEN 'P' THEN 'IN ' WHEN 'O' THEN 'OUT ' WHEN 'B' THEN 'INOUT ' END||
    p.parmname || ' ' || p.typename || '(' || p.length || ')'
    FROM syscat.sysroutines r inner join syscat.routineparams p ON r.routineschema=p.routineschema AND r.routinename=p.routinename
    WHERE r.routineschema = '<schema_name>' AND r.routinetype = 'P'
    ORDER BY r.routinename, p.ordinal
    Last edited by Peter.Vanroose; 08-20-11 at 02:35.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Aug 2011
    Posts
    7
    Thank you very much. That solved my problem.

Tags for this Thread

Posting Permissions

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