Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Location
    VA,USA
    Posts
    8

    Red face Unanswered: Db2 Procedure FileOutput

    Hi,
    I am able to create a procedure which calls a external C program, as explained below for creating Flat File output from a DB2 Procedure.

    #include<stdio.h>
    FILE *fp;
    void output_file(char filename[30],char str[150])
    {
    fp = fopen(filename,"a+");
    fprintf(fp,"%s",str);
    fclose(fp);
    }

    and compiling it using

    #! /bin/ksh

    # Compile the program.
    cc -Kthread -I/$DB2HOME/include -c $1.c
    # Link the program and create a shared library.
    cc -o $1 $1.o -G -Kthread -L/$DB2HOME/lib -ldb2 -ldb2apie
    # Copy the shared library to the sqllib/function subdirectory of the DB2 instance.
    # Note: this assumes the user has write permission to this directory.
    eval "H=~$DB2INSTANCE"
    cp $1 $H/sqllib/function

    The FileOutput.o file I am copying in the $DB2HOME/functions Dir.
    and then creating a Stored procedure as follows:

    CREATE PROCEDURE AKUMAR.FILE_OUTPUT
    (IN FIELPATH VARCHAR(40),
    IN FILETEXT VARCHAR(60)
    )
    SPECIFIC AKUMAR.SQL020611091730470
    RESULT SETS 1
    READS SQL DATA
    EXTERNAL NAME 'FileOutput.o!output_file'
    LANGUAGE C
    PARAMETER STYLE GENERAL
    NOT DETERMINISTIC
    FENCED
    CALLED ON NULL INPUT
    NO DBINFO
    PROGRAM TYPE SUB;


    I am not able to find out where the flat file is formed, once the procedure is executed successfully??

    Guidance Appreciated

    regards,
    Arvind

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Db2 Procedure FileOutput

    I believe that the file will be created in one of the subdirectories under sqllib/function .



    Originally posted by arvind_tcs
    Hi,
    I am able to create a procedure which calls a external C program, as explained below for creating Flat File output from a DB2 Procedure.

    #include<stdio.h>
    FILE *fp;
    void output_file(char filename[30],char str[150])
    {
    fp = fopen(filename,"a+");
    fprintf(fp,"%s",str);
    fclose(fp);
    }

    and compiling it using

    #! /bin/ksh

    # Compile the program.
    cc -Kthread -I/$DB2HOME/include -c $1.c
    # Link the program and create a shared library.
    cc -o $1 $1.o -G -Kthread -L/$DB2HOME/lib -ldb2 -ldb2apie
    # Copy the shared library to the sqllib/function subdirectory of the DB2 instance.
    # Note: this assumes the user has write permission to this directory.
    eval "H=~$DB2INSTANCE"
    cp $1 $H/sqllib/function

    The FileOutput.o file I am copying in the $DB2HOME/functions Dir.
    and then creating a Stored procedure as follows:

    CREATE PROCEDURE AKUMAR.FILE_OUTPUT
    (IN FIELPATH VARCHAR(40),
    IN FILETEXT VARCHAR(60)
    )
    SPECIFIC AKUMAR.SQL020611091730470
    RESULT SETS 1
    READS SQL DATA
    EXTERNAL NAME 'FileOutput.o!output_file'
    LANGUAGE C
    PARAMETER STYLE GENERAL
    NOT DETERMINISTIC
    FENCED
    CALLED ON NULL INPUT
    NO DBINFO
    PROGRAM TYPE SUB;


    I am not able to find out where the flat file is formed, once the procedure is executed successfully??

    Guidance Appreciated

    regards,
    Arvind

  3. #3
    Join Date
    May 2002
    Location
    Pune, India
    Posts
    23
    Hi Arvind,

    Try to give the absolute path to the function directory. Worked fine for me.

    fopen("<DB2INSTANCE DIR>/sqllib/function/filename","w");

    A fenced SP runs with the owner id of the owner of the file sqllib/adm/.fenced file. so ensure that this id has got the proper permission to write to the target directory.

    Let me know if this helped.

    Regards
    Sumeet

Posting Permissions

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