Results 1 to 7 of 7

Thread: oracle vs db2

  1. #1
    Join Date
    Jul 2012
    Posts
    28

    Unanswered: oracle vs db2

    We use:
    v1 number:=25;
    dbms_output.put_line(v1);

    to display the output as 25 in oracle.


    DB2:
    begin atomic
    declare v1 integer;
    set v1=25;
    end

    pls tell me how to display the output as 25 in DB2?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2 has oracle compatibility
    all (allmost all) statements can be used in db2
    see
    DBMS_OUTPUT module
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    You have a choice: continue using Oracle syntax with DB2 (v9.7) on Linux/Unix/Windows, or convert to DB2 syntax.

    To use DB2-syntax, you just include CALL DBMS_OUTPUT.PUT_LINE(v1);

    To use Oracle syntax with DB2 V9.7, you must create a db2-instance , with db2-registry variable DB2_COMPATIBILITY_VECTOR=ORA, and then create a database in that instance.

    When you connect to that database you can use many (but not all) Oracle features and specifically you can use anonymous blocks like in Oracle like this:

    Code:
    SET SQLCOMPAT PLSQL;
    /
    
    set serveroutput on ;
    /
    
    DECLARE
     v1 number ;
    BEGIN
       v1 := 25;
      DBMS_OUTPUT.PUT_LINE(v1);
    END;
    /
    If you create a file orasampl.sql with the above code, then you can use:
    db2 connect to _your_database_name
    db2 -tvf orasampl.sql

  4. #4
    Join Date
    Jul 2012
    Posts
    28
    hi db2mor

    call dbms_output.put_line(v1) is not working.
    any alternate method to display the o/p in db2?

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Learn how to post!
    Never say "is not working".
    Always show the code and show the real result (including the full error message).

    This works OK for me in DB2 v9.7.5 on Windows...

    Code:
    --#SET TERMINATOR @
    set serveroutput on
    @
    
    begin 
       declare v1 integer default 25;
       call dbms_output.put_line(v1);
    end 
    @

  6. #6
    Join Date
    Jul 2012
    Posts
    28
    i have set terminator as u said and tried executing set serveroutput on @, but i am getting following error.


    SQL0104N An unexpected token "ON" was found following "SET SERVEROUTPUT ".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "ON" was found following "SET SERVEROUTPUT ". Expected tokens may include: "JOIN <joined_table> ".

    Explanation:

    A syntax error in the SQL statement or the input command string
    for the SYSPROC.ADMIN_CMD procedure was detected at the specified
    token following the text "<text>". The "<text>" field indicates
    the 20 characters of the SQL statement or the input command
    string for the SYSPROC.ADMIN_CMD procedure that preceded the
    token that is not valid.

    As an aid, a partial list of valid tokens is provided in the
    SQLERRM field of the SQLCA as "<token-list>". This list assumes
    the statement is correct to that point.

    The statement cannot be processed.

    User Response:

    Examine and correct the statement in the area of the specified
    token.

    sqlcode : -104

    sqlstate : 42601

  7. #7
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    I don't know what client tool or application you are using - because you give no information in your post.

    I use the command-line shell on Linux (Korn shell or Bash) or on Windows I use (db2cwadmin.bat).

    I use DB2 v9.7 on Windows x64, and DB2 v10.1 on Linux x64 - both work fine for me.

    You are getting this error because you client tool / DB2-version (which version? which fixpack? which operating system?) does not understand the syntax.

Posting Permissions

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