Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    53

    Unhappy Unanswered: What is in DB2 what Transac SQL is in SQL Server

    We currently have DB2 scripts that execute from a DB2 command line (DB2 -vtf <jobname.db2>) and in which all statements (UPDATEs, DELETEs, INSERTs, CREATEs, etc.) execute one after another without caring whether the previous one completed successfully or failed.

    We want do be able to direct the execution a little bit more and I have read on IBM's website about IF's, FOR's, REPEAT's, WHILE's, throwing and catching exceptions, etc. How exactly do these work? What do I need to create in order to execute such scripts? Is it stored procedures or is there something else like SQL Server's Transac SQL? Keep in mind we may want to connect to more than one database as we do with our current scripts (exporting data from database 1 to a file and then loading into database 2).

    Thank you all in advance!
    Last edited by Razor1973; 07-30-04 at 16:47. Reason: Subject was wrong

  2. #2
    Join Date
    Mar 2004
    Posts
    448
    1. the various commands are control statements and they are used in the sp and triggers.the sp runs under the connectivity of the client.

    2. You can use type 2 connections and move back and forth among them.

    3. You can write scripts that connect to db and did some work , reset the
    connection and connected to another db and so on.

    4. you can use the s options like db2 -stvf to stop on error.


    regards,

    mujeeb

  3. #3
    Join Date
    Jul 2004
    Posts
    53
    Quote Originally Posted by bmujeeb
    1. the various commands are control statements and they are used in the sp and triggers.the sp runs under the connectivity of the client.

    2. You can use type 2 connections and move back and forth among them.

    3. You can write scripts that connect to db and did some work , reset the
    connection and connected to another db and so on.

    4. you can use the s options like db2 -stvf to stop on error.


    regards,

    mujeeb
    mujeeb,

    Are we talking a Java or SQL stored procedure?

    Also, as far as stopping on error, we don't necessarily want to do that, but say, we want to skip certain statements if there is an exception, but still execute others or something of the sort. We don't want exiting the procedure to be the only way to handle exceptions. For example:

    if
    <statement to update rows> is unsuccessful
    then
    <statement to insert rows>
    <statement to update rows>
    end if

    <statements to export rows>


    Thank you for your response.

  4. #4
    Join Date
    Mar 2004
    Posts
    448
    I read somewhere this procedure,may be this could help you

    CREATE PROCEDURE simple_error_token ( IN p_midinit CHAR
    ,IN p_empno CHAR(6)
    ,OUT p_sqlcode_out int
    ,OUT p_sqlstate_out char(5)
    ,OUT p_token_string VARCHAR(100) )
    SPECIFIC simple_error_token
    LANGUAGE SQL
    setk: BEGIN
    -- Declare variables
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;
    -- Declare condition handlers
    DECLARE EXIT HANDLER FOR SQLEXCEPTION -- (1)
    BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -- (4)
    GET DIAGNOSTICS EXCEPTION 1 p_token_string = DB2_TOKEN_STRING;
    SELECT SQLCODE -- (2)
    ,SQLSTATE
    INTO p_sqlcode_out
    ,p_sqlstate_out
    FROM sysibm.sysdummy1;
    RESIGNAL; -- (3)
    END;
    -- Procedure logic
    SET p_token_string = '';
    VALUES (SQLCODE, SQLSTATE)
    INTO p_sqlcode_out
    ,p_sqlstate_out;

    if p_sql_code_out // use your logic
    UPDATE employee
    SET midinit = p_midinit
    WHERE empno = p_empno;
    END setk

    regards,

    mujeeb

  5. #5
    Join Date
    Jul 2004
    Posts
    53
    mujeeb,

    I am trying to run such sp, but I am getting an error. I've posted a new thread, as the issue at hand is different from this one. Feel free to check it out and respond.

    Thanks for all your help!

  6. #6
    Join Date
    Aug 2004
    Location
    London, UK
    Posts
    31

    Cool Re:What is in DB2 what Transac SQL is in SQL Server

    Personally,
    I'd rewrite SQL*Server TransactSQL and/or Oracle PL/SQL scripts in Java rather than SQL/PL. The reason is:-

    1. You don't have to fiddle around setting up your compiler (Stinger interprets SQL/PL, however, so it'll soon not be a problem)

    2. You don't have to learn a whole new language

    3. Java is functionally much richer than SQL/PL and will continue to enhance

    4. SQL/PL is mainly for stored procedures whereas you can write scripts and stored procedures in Java for DB2.

    Check out <DB2 installation directory>/samples/java/jdbc

Posting Permissions

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