Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    28

    Unanswered: Stored procedures and file system

    I've read that Stored Functions have access to the file system - like reading flat files etc. may be possible.

    But is the same possible from within Stored Procedures? I am trying to create a SP with a LOAD statement and it seems like its not right.

    Could someone help?
    Thanks.

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by saltbits
    I've read that Stored Functions have access to the file system - like reading flat files etc. may be possible.

    But is the same possible from within Stored Procedures? I am trying to create a SP with a LOAD statement and it seems like its not right.

    Could someone help?
    Thanks.
    What type of Stored Procedure? (ie. SQL? C?)
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Jun 2004
    Posts
    28
    Just plain SQL SP. And by the way, going by your reply to my question on another thread - the SP is using LOAD to database table, not temporary table (was just trying an alternative...)

    Thanks.

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by saltbits
    Just plain SQL SP. And by the way, going by your reply to my question on another thread - the SP is using LOAD to database table, not temporary table (was just trying an alternative...)

    Thanks.
    Unfortunately I don't think LOAD is supported from an SQL Stored Proc... maybe someone will correct me.

    What's the problem you're trying to solve? Maybe there's another way to kick that cat.
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Jun 2004
    Posts
    28
    >What's the problem you're trying to solve? Maybe there's another way to kick that cat.

    OK, here is what I need to do:
    I have to read a txt file that has one column worth of data. I update a certain table in the db where PK = the value read from the flat file - like this:
    UPDATE Table1 SET stat='FAILURE' WHERE EXISTS (SELECT * FROM Temp1 WHERE Table1.PK = Temp1.key);

    I was hoping that Temp1 would be a global temp table which I now know I cant use. So basically, its a question of an alternative way to read the flat file and use it in the update statement.

    (I am doing all this in UNIX, so if there is a UNIX way to do it all, that would help too!)

    Thanks a lot.

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by saltbits
    >What's the problem you're trying to solve? Maybe there's another way to kick that cat.

    OK, here is what I need to do:
    I have to read a txt file that has one column worth of data. I update a certain table in the db where PK = the value read from the flat file - like this:
    UPDATE Table1 SET stat='FAILURE' WHERE EXISTS (SELECT * FROM Temp1 WHERE Table1.PK = Temp1.key);

    I was hoping that Temp1 would be a global temp table which I now know I cant use. So basically, its a question of an alternative way to read the flat file and use it in the update statement.

    (I am doing all this in UNIX, so if there is a UNIX way to do it all, that would help too!)

    Thanks a lot.
    What about a shell script? ie. if your db name is SAMPLE and your file is lines.txt:

    #!/bin/ksh

    db2 CONNECT TO SAMPLE
    for val in `cat lines.txt`
    do
    db2 UPDATE Table1 SET stat=\'FAILURE\' WHERE Table1.PK = \'$val\'
    done
    --
    Jonathan Petruk
    DB2 Database Consultant

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Something like this?
    Code:
    awk '{"db2 update table1 set stat='\''"FAILURE"'\''" where pk=" $1}' your_txt_file | sh
    May be a bit slow to run but quick to implement :-)

  8. #8
    Join Date
    Jun 2004
    Posts
    28
    Great! I think I will just try these UNIX ways.

    Thanks a lot, everyone.

Posting Permissions

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