Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    27

    Question Unanswered: Problem in creating View while converting to DB2

    Please help me to convert this view form Oracle to Db2 9.5. I am facing problem in the Time stamp area.

    query1:='CREATE OR REPLACE VIEW Step45_Flap1 AS
    SELECT time_stamp FROM ( SELECT time_stamp FROM(
    SELECT time_stamp FROM (SELECT time_stamp' ||query1||' FROM '||Param5||' GROUP BY time_stamp) WHERE time_stamp '||Param4||' TO_DATE('''||Param3||''',''MM/DD/YYYY HH24:MIS'')))';



    Thanks and Regards,
    Vivek

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    I can see few posts from you today regarding Oracle to DB2 migration for SQL code. May be the following Redbook will help you to get the answer of most of the questions you asked:

    IBM Redbooks | Oracle to DB2 Conversion Guide for Linux, UNIX, and Windows

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  3. #3
    Join Date
    Jan 2009
    Posts
    27
    Sir/Madam,

    I have this book and am refering this. But its not giving me a full idea. That is the reason why i am posting so many mails.
    I am new to this DB2.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by vivek.vivek
    TO_DATE(Param3,'MM/DD/YYYY HH24:MI: SS')
    This is essentially a datatype cast from a textual expression to a date/time field.
    DB2 has the datatype TIMESTAMP for this purpose.
    In DB2, casting from text to timestamp can be done through the standard CAST(... AS ...), provided the text is in the format yyyy-mm-dd-hh.mm.ss
    So you'll first have to convert Param3 to a rearranged textual form before passing it to the CAST:
    Code:
    CAST(   SUBSTR(P,7,4)||'-'||SUBSTR(P,1,2)||'-'||SUBSTR(P,4,2)||'-'
         || SUBSTR(P,12,2)||'.'||SUBSTR(P,15,2)||'.'||SUBSTR(P,18,2)
         AS TIMESTAMP)
    Plugging this in into your meta-query (after doubling the single quotes and replacing "P" by "'||Param3||'") should give what you want.
    (See also page 84 of the DB2 for LUW v9.5 SQL Reference, Volume 1 for info about using the TIMESTAMP datatype.)
    Last edited by Peter.Vanroose; 01-20-09 at 15:28.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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