Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    14

    Unanswered: i/o operation isupport in DB2 Stored Procedure

    I want to read a file separated by | pipe delimiter namely loginId|password and use them in an update statement till the EOF.
    I want to achieve this using SQL Stored Procedure.
    Does DB2 SQL Stored Procedure support this? If yes, then please tell how to do it?

    Other alternative to this will be to create an temp table in DB2 and import this flat file into that table. create a cursor on that temp table and loop thro it and get loginid and passwords to update the table. I dont want this approach as I dont have create rights on that database.

    Please suggest.

    Thanks,
    Gopal

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

    Re: i/o operation isupport in DB2 Stored Procedure

    AFAIK, you cannot read/write files using SQL Stored Procedures

    You may need to consider writing a Java or a C procedure ...
    OR
    As an alternate to creating a temp table , you can write a Java/C table function which will read the file for the loginids and passwords and return a table ... You can use this in your SQL Procedure ... This method will enable you to keep the majority of business logic in SQL as you desire ... Search for 'Table Function' in the manuals ..

    I think I have a sample Java Code for the latter option .. If you want, let me know ... I'll see whether I can find and post it, but no promises ...

    Cheers
    Sathyaram

    Originally posted by g0pal
    I want to read a file separated by | pipe delimiter namely loginId|password and use them in an update statement till the EOF.
    I want to achieve this using SQL Stored Procedure.
    Does DB2 SQL Stored Procedure support this? If yes, then please tell how to do it?

    Other alternative to this will be to create an temp table in DB2 and import this flat file into that table. create a cursor on that temp table and loop thro it and get loginid and passwords to update the table. I dont want this approach as I dont have create rights on that database.

    Please suggest.

    Thanks,
    Gopal
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2003
    Posts
    14

    Re: i/o operation isupport in DB2 Stored Procedure

    satya, the system where db2 server is running doesn't have any JRE or Java installed on that. It is a prod. system and except DB2 nothing else is allowed to be installed there also.

    Originally posted by sathyaram_s
    AFAIK, you cannot read/write files using SQL Stored Procedures

    You may need to consider writing a Java or a C procedure ...
    OR
    As an alternate to creating a temp table , you can write a Java/C table function which will read the file for the loginids and passwords and return a table ... You can use this in your SQL Procedure ... This method will enable you to keep the majority of business logic in SQL as you desire ... Search for 'Table Function' in the manuals ..

    I think I have a sample Java Code for the latter option .. If you want, let me know ... I'll see whether I can find and post it, but no promises ...

    Cheers
    Sathyaram

  4. #4
    Join Date
    Aug 2003
    Posts
    14
    And also Satya, is there any facility in DB2 where we can create temporary tables as it is in MS SQL Server which get dropped once the program ends. Here I can create a temporary table like the one in MS SQL Server and do the other processing.

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

    Re: i/o operation isupport in DB2 Stored Procedure

    Unfortunate ...

    What about a C Table function ? You should have a C Compiler somewhere in your site (as you use SQL Procs) ...

    Cheers

    Sathyaram
    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
  •