Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2013
    Posts
    11

    Unanswered: MS SQL Migration to DB2

    //Hi all, could you please help to covert this MS Sql code into DB2 code.

    CREATE FUNCTION pmc (PMCString Varchar(1000),PMCDelimiter Char(1))
    Returns Table(Items Varchar(1000))
    LANGUAGE SQL
    CONTAINS SQL
    BEGIN ATOMIC
    DECLARE idx Integer Default 0;
    DECLARE slice Varchar(1000);
    END@
    Select idx = 1
    If len(PMCString)<1 or PMCString is null Return
    While idx!= 0
    BEGIN
    Set idx = Charindex(PMCDelimiter,PMCString)
    If idx!=0
    Set slice = left(PMCString,idx - 1)
    else
    Set slice = PMCString
    If(len(slice)>0)
    Insert into temptable(Items) Values(slice)
    Set PMCString = right(PMCString,len(PMCString) - idx)
    If len(PMCString) = 0 break
    Return
    END;

    Thanks & Regards
    Dharmendra Kr. Yadav

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that you are working on migration project from MS SQL Server to DB2.

    So, I want to recommend for you to read a book
    IBM Redbooks | Microsoft SQL Server to IBM DB2 UDB Conversion Guide
    and to undersand basic differences between MS SQL and DB2 and considerations in conversion/migration project,
    before attempt to start conversion/migration of codes.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    IBM Redbooks | Microsoft SQL Server to IBM DB2 UDB Conversion Guide

    You might be especially interesting in "Chapter 4. SQL considerations".
    (For example: 4.3 Date and time considerations , 4.6 SQL language syntax and semantics , 4.7 Built-in SQL functions , so on...)

    Table of contents

    Chapter 1. Introduction
    Chapter 2. Architecture
    Chapter 3. DB2 UDB administration tools
    Chapter 4. SQL considerations
    Chapter 5. Planning for a conversion
    Chapter 6. The IBM DB2 Migration Toolkit for SQL Server
    Chapter 7. Database structure conversion
    Chapter 8. Data and script migration
    Chapter 9. Converting database objects
    Chapter 10. Application conversion considerations
    Chapter 11. Performing administrative tasks in DB2 UDB
    Chapter 12. Post-conversion tuning considerations
    Chapter 13. Testing and troubleshooting
    Chapter 14. Conversion scenario
    Appendix A. Terminology mapping
    Appendix B. Data type mapping
    Appendix C. Function mapping
    Appendix D. Operator mapping
    Appendix E. Administrative tasks mapping
    Appendix F. SQL limits
    Appendix G. Additional material

  4. #4
    Join Date
    Dec 2013
    Posts
    11

    Db2 conversion

    //Hi All please check this code, i am trying to convert into DB2, please suggest me about db2 conversion.

    Create Function CastZeroToSpace(Area float)
    Returns varchar(100)
    Language SQL
    Contains SQL
    No External Action
    Deterministic
    Begin Atomic
    Declare cstArea varchar(100);
    select cstArea, CASE Area
    WHEN 0 THEN ''
    ELSE Convert(varchar(100),Area)
    END
    Return cstArea;
    END

    //Error

    An unexpected token "END" was found following "100),Area)
    END
    ". Expected tokens may include: "<table_expr>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.66.46

    Tnaks & Regards
    Dharmendra Kr. Yadav

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to repeat again
    Quote Originally Posted by tonkuma View Post
    Anyway,

    I wanr to see your effort to be able to work some MS SQL code on DB2 environment.

    Until now,
    you showed only the codes on MS SQL Serve,
    and I couldn't see no effort to ammendments the codes to work on DB2.


    I (thought perhaps, most of pesistances on this forum) had no reword from the posts or responds on this forum.

    So, my hope was that you showed some efforts by yourself,
    to conform to DB2 syntax and semantics.
    Heven helps those who help themselves.


    Specific to your CastZeroToSpace function,
    there is no need to declare cstArea.
    You can return a value directly, like I showed in this thread...
    http://www.dbforums.com/db2/1699157-...n-problem.html


    Please read Manuals(and/or Information Center) and RedBooks.
    IBM Redbooks | Microsoft SQL Server to IBM DB2 UDB Conversion Guide

    Structured Query Language (SQL) - IBM DB2 9.7 for Linux, UNIX, and Windows
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

Posting Permissions

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