Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Location
    Delhi, India
    Posts
    15

    Unanswered: SQL compatible to DB2 and MSSQL

    Hi All,

    We have a application that uses two database DB2 and MSSQL. I have a situation with our application regarding MSSQL. We have complete access of MSSQL database in prod server but in development server we only have read access. We cann't make any changes in DEVELOPMENT mode.

    So we have decided to move all the tables to DB2 for DEV and TEST environment.

    Now when user is in DEV mode data will be fetch by the DEV DB2 database and in PROD it will be fetched with MSSQL database.

    So we have to make SQL Queries compatible with both the database..

    Is there any equivalent available for *= and @@IDENTITY in DB2? what else challenges can be there?

    Please help...

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That's a bad idea because SQL statements may differ not only in the syntax but also the performance. You should fix the real problem and get a MS SQL Server installation with which you can do real work.

    Regarding your specific question, you may want to explain to us what exactly "*=" and @@IDENTITY is doing. Both are not standard SQL, so it may be a good idea to avoid such constructs in the first place.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nitingautam
    So we have to make SQL Queries compatible with both the database..
    three words: not. gonna. happen.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    In MSSQL, @@IDENTITY is a system function that returns the last-inserted identity value (an identity column is a numeric auto-incremented column).

    In DB2 you create an identity column using GENERATED ALWAYS AS IDENTITY clause of the CREATE TABLE statement. I am not aware of a DB2 function that will return the last-inserted identity value in a table.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by aflorin27
    In MSSQL, @@IDENTITY is a system function that returns the last-inserted identity value (an identity column is a numeric auto-incremented column).

    In DB2 you create an identity column using GENERATED ALWAYS AS IDENTITY clause of the CREATE TABLE statement. I am not aware of a DB2 function that will return the last-inserted identity value in a table.
    In DB2 it is Identity_Val_Local:

    http://publib.boulder.ibm.com/infoce.../r0004231.html

    Andy

  6. #6
    Join Date
    Nov 2008
    Location
    Delhi, India
    Posts
    15
    Thanks you guys for your valuable inputs in this issue...I was also sure about this that this can't be happen as both have different syntax but as I am not expert in SQL so I take help from you

    Thanks you very much for your time

Posting Permissions

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