Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Posts
    5

    Unanswered: Stored Procedure O/P as a file

    Hi,

    I have created a procedure which is giving me 300+ records.I want to store them in a file or in other words I need to craete a stored procedure whose o/p will be stored in a file(tab delimineted file).
    I want to invoke them through a batch file.
    Is it possible to do it ? Can anyone help me out in this matter.
    I am giving my stored procedure for the reference.
    ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++

    CREATE PROCEDURE client_core_select_duplicate ( OUT var0 VARCHAR(4000) )
    DYNAMIC RESULT SETS 1
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    -- var0
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare variable
    DECLARE var0_TMP VARCHAR(4000) DEFAULT ' ';

    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT
    n.CLIENT_ID,
    n.NAME_BUSINESS,
    c.CORP_ID,
    a.HOUSE_NBR,
    a.ADDRESS1,
    a.ADDRESS2,
    a.ADDRESS3,
    a.ADDRESS4,
    a.CITY,
    a.STATE_PROV_CD,
    a.POSTAL_CODE
    FROM
    CLI_CLIENT c
    JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID = n.CLIENT_ID
    LEFT OUTER JOIN CLI_CLNT_ADDR_V ca ON n.CLIENT_ID = ca.CLIENT_ID
    LEFT OUTER JOIN CLI_ADDRESS_V a ON ca.ADDRESS_ID = a.ADDRESS_ID
    WHERE
    c.SEGMENTED_FLAG = 'N'
    AND n.NAME_BUSINESS IS NOT NULL
    ORDER BY
    n.NAME_BUSINESS,
    c.CORP_ID,
    a.HOUSE_NBR,
    a.ADDRESS1,
    a.ADDRESS2,
    a.ADDRESS3,
    a.ADDRESS4,
    a.CITY,
    a.STATE_PROV_CD,
    a.POSTAL_CODE,
    n.CLIENT_ID;

    -- Cursor left open for client application
    OPEN cursor1;
    SET var0 = var0_TMP;
    END P1

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    in developerswork there is a document, describing how to write to a file from a stored proc (small java function)
    maybe this will help
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    My suggestion for this specific scenario is to throw away the procedure and run the SELECT statement directly through the EXPORT command. There you can use DEL as filetype and choose the desired column delimiter.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jan 2007
    Posts
    5
    Quote Originally Posted by aadi
    Hi,

    I have created a procedure which is giving me 300+ records.I want to store them in a file or in other words I need to craete a stored procedure whose o/p will be stored in a file(tab delimineted file).
    I want to invoke them through a batch file.
    Is it possible to do it ? Can anyone help me out in this matter.
    You may look at:
    http://www.sqlmanager.net/en/products/db2

Posting Permissions

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