Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2012
    Posts
    17

    Unanswered: Trapping SQL statement error

    I have a table with 2 columns. ID and STR. The STR column has a SQL statement. I need to iterate thru all the records in the table, execute the SQL in each record and finally create a file which has the following feilds:
    - ID
    - STR
    - No of records from the SQL
    - DB2 SQL error code ( if any )

    Any hints pls. Thank you.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Do you think it might be dependent on the DB2 version and the programming language you are planning to use?

  3. #3
    Join Date
    Apr 2012
    Posts
    17
    Quote Originally Posted by n_i View Post
    Do you think it might be dependent on the DB2 version and the programming language you are planning to use?
    I am planning to use DB2 stored procedure and using DB2 ver 8.x.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Here's an example from the manual: To create the CREATE_DEPT_TABLE SQL procedure

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    This works in recent versions (not sure about V8):

    Declare the following in the list of parms for the SP:
    Code:
    INOUT DB2_ERRMSG                VARCHAR(1024),
    Declare the following variables:
    Code:
    DECLARE at_end              SMALLINT DEFAULT 0;
    DECLARE not_found           CONDITION FOR SQLSTATE '02000';
    Add the following to your SP after variables and cursors are declared:

    Code:
    ---------------------------------------------
    -- Declare exit handlers
    ---------------------------------------------
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
       GET DIAGNOSTICS EXCEPTION 1 DB2_ERRMSG = MESSAGE_TEXT;
    
    DECLARE CONTINUE HANDLER FOR not_found
       SET at_end = 1;
    
    DECLARE EXIT HANDLER FOR SQLWARNING
       GET DIAGNOSTICS EXCEPTION 1 DB2_ERRMSG = MESSAGE_TEXT;
    Note that the program will not end and error will not be returned if SQL code is +100 (SQLSTATE = '02000')
    Last edited by Marcus_A; 04-02-12 at 16:21.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Post

    Quote Originally Posted by vskr72 View Post
    I have a table with 2 columns. ID and STR. The STR column has a SQL statement. I need to iterate thru all the records in the table, execute the SQL in each record and finally create a file which has the following feilds:
    - ID
    - STR
    - No of records from the SQL
    - DB2 SQL error code ( if any )

    Any hints pls. Thank you.
    I see only one way: Dynamic SQL.

    1. You declare a main_csr cursor for select id, str from your_table
    2. open and fetch cursor into :id, :str
    3. On each :str your prepare stmt and declare stnt_cse cursor base on this statement
    4. close the stmt_csr when sqlcode = +100, or got the sqlcode < 0
    5. write your-file from the :id, :str, :No_of_rows, sqlcode
    6. fetch the main_csr until sqlcode = +100, or sqlcode < 0
    7. close the main_csr

    Lenny

  7. #7
    Join Date
    Apr 2012
    Posts
    17
    I created the following procedure based on the my understanding.. The process is not able to export the data from a Global tmp table. Not sure if it is allowed. Any help please?

    Satish
    Attached Files Attached Files

  8. #8
    Join Date
    Apr 2012
    Posts
    17
    Just trying to understand what are the other possible ways to write data to a file from a stored procedure. Looks like Export is not working. I was using export from a Global temp table.

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Export is a Command. Only valid SQL Statements are permitted in a procedure
    To use export command, use ADMIN_CMD procedure .. Check

    ADMIN_CMD procedure
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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