Topic
  • 15 replies
  • Latest Post - ‏2014-01-30T12:37:32Z by mexdisuza
SystemAdmin
SystemAdmin
17917 Posts

Pinned topic How to unlock Excel file in DB2 9

‏2006-11-30T16:46:37Z |
Hi,

I create a DB2 9 UDF linking to a Excle file using Microsoft Excel ODBC
driver in my project. However, once I select data from this UDF, the Excle
file is locked. Users can not update data in the file any more.

Is there a way to unlock the file, or never lock the file?

Thanks

Luke

Updated on 2006-12-04T18:03:37Z at 2006-12-04T18:03:37Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-11-30T20:03:04Z  
    you stated you are 'linking' the file from excel to the db. when you set it up, did you set it up as sharing? and, is everyone updating the spreadsheet when they install onto their machine?
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-11-30T23:03:12Z  
    Luke Xu wrote:

    > Hi,
    >
    > I create a DB2 9 UDF linking to a Excle file using Microsoft Excel ODBC
    > driver in my project. However, once I select data from this UDF, the Excle
    > file is locked. Users can not update data in the file any more.
    >
    > Is there a way to unlock the file, or never lock the file?

    Could you outline how exactly the "linking" is done? Have you written your
    own user-defined function (UDF), or are you employing a federated wrapper?
    If it is your own UDF, you should make sure that the file is properly
    closed once you are done reading it, i.e. in the FINAL call of the
    function.

    Knut Stolze
    DB2 Information Integration Development
    IBM Germany
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-12-01T00:54:09Z  
    Luke Xu wrote:

    > Hi,
    >
    > I create a DB2 9 UDF linking to a Excle file using Microsoft Excel ODBC
    > driver in my project. However, once I select data from this UDF, the Excle
    > file is locked. Users can not update data in the file any more.
    >
    > Is there a way to unlock the file, or never lock the file?

    Could you outline how exactly the "linking" is done? Have you written your
    own user-defined function (UDF), or are you employing a federated wrapper?
    If it is your own UDF, you should make sure that the file is properly
    closed once you are done reading it, i.e. in the FINAL call of the
    function.

    Knut Stolze
    DB2 Information Integration Development
    IBM Germany
    Hi,

    I describe the process here:

    I create my own DB2 UDF that links an Excel. The purpose is that I can get
    the data while users can update or add data in the Excel file.

    The following command is used to create UDF:

    CREATE FUNCTION ASO.HR_EMPLOYEES()
    RETURNS TABLE (EMPLOYEE_ID INT,
    NAME VARCHAR(30),CITY VARCHAR(30))
    LANGUAGE OLEDB
    EXTERNAL NAME '!Sheet1$!Driver={Microsoft Excel Driver (*.xls)};
    DriverID=790;
    Dbq=c:\luke\data_hub\hr_employees.xls;'

    After the UDF is created, I use SQL command to retrieve data:

    SELECT * FROM TABLE(ASO.HR_EMPLOYEES()) T

    I can get all data. However, if I try open Excel file, it tells me it is
    locked and I can only open it read-only. So my question is how to unlock it
    that I could update the data in Excel file.

    Pls let me know if more information is required.

    Thanks

    Luke
    "Knut Stolze" <stolze@de.ibm.com> wrote in message
    news:eknnvg$3n80m$1@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> Hi,
    >>
    >> I create a DB2 9 UDF linking to a Excle file using Microsoft Excel ODBC
    >> driver in my project. However, once I select data from this UDF, the
    >> Excle
    >> file is locked. Users can not update data in the file any more.
    >>
    >> Is there a way to unlock the file, or never lock the file?
    >
    > Could you outline how exactly the "linking" is done? Have you written
    > your
    > own user-defined function (UDF), or are you employing a federated wrapper?
    > If it is your own UDF, you should make sure that the file is properly
    > closed once you are done reading it, i.e. in the FINAL call of the
    > function.
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany

  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-12-01T09:51:22Z  
    Hi,

    I describe the process here:

    I create my own DB2 UDF that links an Excel. The purpose is that I can get
    the data while users can update or add data in the Excel file.

    The following command is used to create UDF:

    CREATE FUNCTION ASO.HR_EMPLOYEES()
    RETURNS TABLE (EMPLOYEE_ID INT,
    NAME VARCHAR(30),CITY VARCHAR(30))
    LANGUAGE OLEDB
    EXTERNAL NAME '!Sheet1$!Driver={Microsoft Excel Driver (*.xls)};
    DriverID=790;
    Dbq=c:\luke\data_hub\hr_employees.xls;'

    After the UDF is created, I use SQL command to retrieve data:

    SELECT * FROM TABLE(ASO.HR_EMPLOYEES()) T

    I can get all data. However, if I try open Excel file, it tells me it is
    locked and I can only open it read-only. So my question is how to unlock it
    that I could update the data in Excel file.

    Pls let me know if more information is required.

    Thanks

    Luke
    "Knut Stolze" <stolze@de.ibm.com> wrote in message
    news:eknnvg$3n80m$1@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> Hi,
    >>
    >> I create a DB2 9 UDF linking to a Excle file using Microsoft Excel ODBC
    >> driver in my project. However, once I select data from this UDF, the
    >> Excle
    >> file is locked. Users can not update data in the file any more.
    >>
    >> Is there a way to unlock the file, or never lock the file?
    >
    > Could you outline how exactly the "linking" is done? Have you written
    > your
    > own user-defined function (UDF), or are you employing a federated wrapper?
    > If it is your own UDF, you should make sure that the file is properly
    > closed once you are done reading it, i.e. in the FINAL call of the
    > function.
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany

    Luke Xu wrote:

    > Hi,
    >
    > I describe the process here:
    >
    > I create my own DB2 UDF that links an Excel. The purpose is that I can get
    > the data while users can update or add data in the Excel file.
    >
    > The following command is used to create UDF:
    >
    > CREATE FUNCTION ASO.HR_EMPLOYEES()
    > RETURNS TABLE (EMPLOYEE_ID INT,
    > NAME VARCHAR(30),CITY VARCHAR(30))
    > LANGUAGE OLEDB
    > EXTERNAL NAME '!Sheet1$!Driver={Microsoft Excel Driver (*.xls)};
    > DriverID=790;
    > Dbq=c:\luke\data_hub\hr_employees.xls;'

    As I mentioned before, you have to close the file (ODBC cursor or whatever)
    at the end of the SQL statement, i.e. in the SQLUDF_TF_CLOSE call. Here is
    an example showing C code: http://tinyurl.com/ylqjfm

    Also, I would guess that your file is not opened in read but rather in
    read-write mode. (I don't know how your code actually opens the file.) If
    you change that to read-only, the locking behavior of Windows may expose
    different results.

    Knut Stolze
    DB2 Information Integration Development
    IBM Germany
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-12-01T14:36:18Z  
    Luke Xu wrote:

    > Hi,
    >
    > I describe the process here:
    >
    > I create my own DB2 UDF that links an Excel. The purpose is that I can get
    > the data while users can update or add data in the Excel file.
    >
    > The following command is used to create UDF:
    >
    > CREATE FUNCTION ASO.HR_EMPLOYEES()
    > RETURNS TABLE (EMPLOYEE_ID INT,
    > NAME VARCHAR(30),CITY VARCHAR(30))
    > LANGUAGE OLEDB
    > EXTERNAL NAME '!Sheet1$!Driver={Microsoft Excel Driver (*.xls)};
    > DriverID=790;
    > Dbq=c:\luke\data_hub\hr_employees.xls;'

    As I mentioned before, you have to close the file (ODBC cursor or whatever)
    at the end of the SQL statement, i.e. in the SQLUDF_TF_CLOSE call. Here is
    an example showing C code: http://tinyurl.com/ylqjfm

    Also, I would guess that your file is not opened in read but rather in
    read-write mode. (I don't know how your code actually opens the file.) If
    you change that to read-only, the locking behavior of Windows may expose
    different results.

    Knut Stolze
    DB2 Information Integration Development
    IBM Germany
    The UDF will be used in SQL stored procedure. It looks like the Excel file
    is locked by DB2 once the UDF is openned. I don't know how to set the file
    read-only or release the lock in SQL stored procedure.

    Any suggestion is welcome.
    "Knut Stolze" <stolze@de.ibm.com> wrote in message
    news:ekotuq$3tg7s$1@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> Hi,
    >>
    >> I describe the process here:
    >>
    >> I create my own DB2 UDF that links an Excel. The purpose is that I can
    >> get
    >> the data while users can update or add data in the Excel file.
    >>
    >> The following command is used to create UDF:
    >>
    >> CREATE FUNCTION ASO.HR_EMPLOYEES()
    >> RETURNS TABLE (EMPLOYEE_ID INT,
    >> NAME VARCHAR(30),CITY VARCHAR(30))
    >> LANGUAGE OLEDB
    >> EXTERNAL NAME '!Sheet1$!Driver={Microsoft Excel Driver (*.xls)};
    >> DriverID=790;
    >> Dbq=c:\luke\data_hub\hr_employees.xls;'
    >
    > As I mentioned before, you have to close the file (ODBC cursor or
    > whatever)
    > at the end of the SQL statement, i.e. in the SQLUDF_TF_CLOSE call. Here
    > is
    > an example showing C code: http://tinyurl.com/ylqjfm
    >
    > Also, I would guess that your file is not opened in read but rather in
    > read-write mode. (I don't know how your code actually opens the file.)
    > If
    > you change that to read-only, the locking behavior of Windows may expose
    > different results.
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany

  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-12-01T14:45:23Z  
    The UDF will be used in SQL stored procedure. It looks like the Excel file
    is locked by DB2 once the UDF is openned. I don't know how to set the file
    read-only or release the lock in SQL stored procedure.

    Any suggestion is welcome.
    "Knut Stolze" <stolze@de.ibm.com> wrote in message
    news:ekotuq$3tg7s$1@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> Hi,
    >>
    >> I describe the process here:
    >>
    >> I create my own DB2 UDF that links an Excel. The purpose is that I can
    >> get
    >> the data while users can update or add data in the Excel file.
    >>
    >> The following command is used to create UDF:
    >>
    >> CREATE FUNCTION ASO.HR_EMPLOYEES()
    >> RETURNS TABLE (EMPLOYEE_ID INT,
    >> NAME VARCHAR(30),CITY VARCHAR(30))
    >> LANGUAGE OLEDB
    >> EXTERNAL NAME '!Sheet1$!Driver={Microsoft Excel Driver (*.xls)};
    >> DriverID=790;
    >> Dbq=c:\luke\data_hub\hr_employees.xls;'
    >
    > As I mentioned before, you have to close the file (ODBC cursor or
    > whatever)
    > at the end of the SQL statement, i.e. in the SQLUDF_TF_CLOSE call. Here
    > is
    > an example showing C code: http://tinyurl.com/ylqjfm
    >
    > Also, I would guess that your file is not opened in read but rather in
    > read-write mode. (I don't know how your code actually opens the file.)
    > If
    > you change that to read-only, the locking behavior of Windows may expose
    > different results.
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany

    Luke Xu wrote:

    > The UDF will be used in SQL stored procedure. It looks like the Excel file
    > is locked by DB2 once the UDF is openned. I don't know how to set the file
    > read-only or release the lock in SQL stored procedure.

    As I said: You have to close the file inside the UDF in the SQLUDF_TF_CLOSE
    call.

    p.s: The lock is a file system/operating system thing. DB2 is just another
    application from the OS point of view.

    Knut Stolze
    DB2 Information Integration Development
    IBM Germany
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-12-01T19:59:01Z  
    Luke Xu wrote:

    > The UDF will be used in SQL stored procedure. It looks like the Excel file
    > is locked by DB2 once the UDF is openned. I don't know how to set the file
    > read-only or release the lock in SQL stored procedure.

    As I said: You have to close the file inside the UDF in the SQLUDF_TF_CLOSE
    call.

    p.s: The lock is a file system/operating system thing. DB2 is just another
    application from the OS point of view.

    Knut Stolze
    DB2 Information Integration Development
    IBM Germany
    Knut,

    Could you let me know the syntax of how to close the file inside UDF? I have
    no idea and didnt get answer from google search either.

    Thanks
    "Knut Stolze" <stolze@de.ibm.com> wrote in message
    news:ekpf63$3cg18$1@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> The UDF will be used in SQL stored procedure. It looks like the Excel
    >> file
    >> is locked by DB2 once the UDF is openned. I don't know how to set the
    >> file
    >> read-only or release the lock in SQL stored procedure.
    >
    > As I said: You have to close the file inside the UDF in the
    > SQLUDF_TF_CLOSE
    > call.
    >
    > p.s: The lock is a file system/operating system thing. DB2 is just
    > another
    > application from the OS point of view.
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany

  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-12-01T20:28:17Z  
    Knut,

    Could you let me know the syntax of how to close the file inside UDF? I have
    no idea and didnt get answer from google search either.

    Thanks
    "Knut Stolze" <stolze@de.ibm.com> wrote in message
    news:ekpf63$3cg18$1@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> The UDF will be used in SQL stored procedure. It looks like the Excel
    >> file
    >> is locked by DB2 once the UDF is openned. I don't know how to set the
    >> file
    >> read-only or release the lock in SQL stored procedure.
    >
    > As I said: You have to close the file inside the UDF in the
    > SQLUDF_TF_CLOSE
    > call.
    >
    > p.s: The lock is a file system/operating system thing. DB2 is just
    > another
    > application from the OS point of view.
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany

    Luke Xu wrote:

    > Knut,
    >
    > Could you let me know the syntax of how to close the file inside UDF? I
    > have no idea and didnt get answer from google search either.

    How exactly is your UDF implemented? You have only shown is the CREATE
    FUNCTION statement, which registers the UDF in your DB2 database. The
    external code is the piece that must take care of this...

    Knut Stolze
    DB2 Information Integration Development
    IBM Germany
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-12-01T21:46:40Z  
    Luke Xu wrote:

    > Knut,
    >
    > Could you let me know the syntax of how to close the file inside UDF? I
    > have no idea and didnt get answer from google search either.

    How exactly is your UDF implemented? You have only shown is the CREATE
    FUNCTION statement, which registers the UDF in your DB2 database. The
    external code is the piece that must take care of this...

    Knut Stolze
    DB2 Information Integration Development
    IBM Germany
    I am doing testing in DB2 tools like DBW or Command Editor before I can
    release the UDF to developer. The external application will be ASP.NET web
    application. I will test it from web application if it can unlock the file.

    The issue is I can not update the data in Excel file during testing stage.
    "Knut Stolze" <stolze@de.ibm.com> wrote in message
    news:ekq392$3t444$1@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> Knut,
    >>
    >> Could you let me know the syntax of how to close the file inside UDF? I
    >> have no idea and didnt get answer from google search either.
    >
    > How exactly is your UDF implemented? You have only shown is the CREATE
    > FUNCTION statement, which registers the UDF in your DB2 database. The
    > external code is the piece that must take care of this...
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany

  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-12-01T23:04:39Z  
    I am doing testing in DB2 tools like DBW or Command Editor before I can
    release the UDF to developer. The external application will be ASP.NET web
    application. I will test it from web application if it can unlock the file.

    The issue is I can not update the data in Excel file during testing stage.
    "Knut Stolze" <stolze@de.ibm.com> wrote in message
    news:ekq392$3t444$1@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> Knut,
    >>
    >> Could you let me know the syntax of how to close the file inside UDF? I
    >> have no idea and didnt get answer from google search either.
    >
    > How exactly is your UDF implemented? You have only shown is the CREATE
    > FUNCTION statement, which registers the UDF in your DB2 database. The
    > external code is the piece that must take care of this...
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany

    Luke Xu wrote:

    > I am doing testing in DB2 tools like DBW or Command Editor before I can
    > release the UDF to developer. The external application will be ASP.NET web
    > application. I will test it from web application if it can unlock the
    > file.

    We have the following pieces in the picture which you have to sort out
    first. They are essential that you understand your own architecture. From
    what I gathered, a top-down summary of the architecture would be this:

    (1) An application running on top of DB2 - that's your DBW or
    Command Editor.
    (2) DB2 itself.
    (3) A UDF which is called by DB2 to do some calculations - reading the file
    and returning it in relational fashion.
    (4) Some library or program that manages Excel files for you. This is
    actually part of the UDF.
    (5) The operating system that handles the file system and locks in the
    file system.

    What I'm asking about is the implementation of (3). Maybe you are just
    using some sort of library - after all, I have no idea what "!Sheet1$"
    stands for in your CREATE FUNCTION statement. In any case, that's the
    place you have to investigate.

    Your problem occurs at level (5): one application (the Excel reader in (3)
    and (4)) opens the file and locks it in the process. When another
    application accesses the file, you get the lock contention. So you have to
    fix the problem in (3) and (4). DB2 is not to blame for that at all! It
    means, you have to figure out how (3) and (4) is implemented and make sure
    that the file is closed properly, there.

    Notice: A CREATE FUNCTION statement (level (2)) just registers the library
    (3) in a DB2 database. It does not implement a function at all. Big
    difference there!

    > The issue is I can not update the data in Excel file during testing stage.

    That's bad. How can you do any serious testing? Anyway, this question is
    irrelevant to what you try to do.
    >
    >
    > "Knut Stolze" <stolze@de.ibm.com> wrote in message
    > news:ekq392$3t444$1@news.boulder.ibm.com...
    >> Luke Xu wrote:
    >>
    >>> Knut,
    >>>
    >>> Could you let me know the syntax of how to close the file inside UDF? I
    >>> have no idea and didnt get answer from google search either.
    >>
    >> How exactly is your UDF implemented? You have only shown is the CREATE
    >> FUNCTION statement, which registers the UDF in your DB2 database. The
    >> external code is the piece that must take care of this...
    >>
    >> --
    >> Knut Stolze
    >> DB2 Information Integration Development
    >> IBM Germany

    Knut Stolze
    DB2 Information Integration Development
    IBM Germany
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-12-04T14:53:24Z  
    Luke Xu wrote:

    > I am doing testing in DB2 tools like DBW or Command Editor before I can
    > release the UDF to developer. The external application will be ASP.NET web
    > application. I will test it from web application if it can unlock the
    > file.

    We have the following pieces in the picture which you have to sort out
    first. They are essential that you understand your own architecture. From
    what I gathered, a top-down summary of the architecture would be this:

    (1) An application running on top of DB2 - that's your DBW or
    Command Editor.
    (2) DB2 itself.
    (3) A UDF which is called by DB2 to do some calculations - reading the file
    and returning it in relational fashion.
    (4) Some library or program that manages Excel files for you. This is
    actually part of the UDF.
    (5) The operating system that handles the file system and locks in the
    file system.

    What I'm asking about is the implementation of (3). Maybe you are just
    using some sort of library - after all, I have no idea what "!Sheet1$"
    stands for in your CREATE FUNCTION statement. In any case, that's the
    place you have to investigate.

    Your problem occurs at level (5): one application (the Excel reader in (3)
    and (4)) opens the file and locks it in the process. When another
    application accesses the file, you get the lock contention. So you have to
    fix the problem in (3) and (4). DB2 is not to blame for that at all! It
    means, you have to figure out how (3) and (4) is implemented and make sure
    that the file is closed properly, there.

    Notice: A CREATE FUNCTION statement (level (2)) just registers the library
    (3) in a DB2 database. It does not implement a function at all. Big
    difference there!

    > The issue is I can not update the data in Excel file during testing stage.

    That's bad. How can you do any serious testing? Anyway, this question is
    irrelevant to what you try to do.
    >
    >
    > "Knut Stolze" <stolze@de.ibm.com> wrote in message
    > news:ekq392$3t444$1@news.boulder.ibm.com...
    >> Luke Xu wrote:
    >>
    >>> Knut,
    >>>
    >>> Could you let me know the syntax of how to close the file inside UDF? I
    >>> have no idea and didnt get answer from google search either.
    >>
    >> How exactly is your UDF implemented? You have only shown is the CREATE
    >> FUNCTION statement, which registers the UDF in your DB2 database. The
    >> external code is the piece that must take care of this...
    >>
    >> --
    >> Knut Stolze
    >> DB2 Information Integration Development
    >> IBM Germany

    Knut Stolze
    DB2 Information Integration Development
    IBM Germany
    Hi,

    I wish it is not DB2 issue locking the Excel file. However, how can I
    explain the following testing:

    • Open DB2 DWB tool and issue SELECT command to retrieve data from UDF.
    • After I run the command, the Excel file is locked.
    • Exit DWB, the Excel file is still locked.
    • Stop DB2 service, the Excel file in UNLOCKed.

    I try to find any DB2 information about if UDF locks or unlocks the file
    like Excel or text, csv files, but unfortunately no answer i can get.

    Thanks

    Luke
    "Knut Stolze" <stolze@de.ibm.com> wrote in message
    news:ekqce7$3uo6c$2@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> I am doing testing in DB2 tools like DBW or Command Editor before I can
    >> release the UDF to developer. The external application will be ASP.NET
    >> web
    >> application. I will test it from web application if it can unlock the
    >> file.
    >
    > We have the following pieces in the picture which you have to sort out
    > first. They are essential that you understand your own architecture.
    > From
    > what I gathered, a top-down summary of the architecture would be this:
    >
    > (1) An application running on top of DB2 - that's your DBW or
    > Command Editor.
    > (2) DB2 itself.
    > (3) A UDF which is called by DB2 to do some calculations - reading the
    > file
    > and returning it in relational fashion.
    > (4) Some library or program that manages Excel files for you. This is
    > actually part of the UDF.
    > (5) The operating system that handles the file system and locks in the
    > file system.
    >
    > What I'm asking about is the implementation of (3). Maybe you are just
    > using some sort of library - after all, I have no idea what "!Sheet1$"
    > stands for in your CREATE FUNCTION statement. In any case, that's the
    > place you have to investigate.
    >
    > Your problem occurs at level (5): one application (the Excel reader in (3)
    > and (4)) opens the file and locks it in the process. When another
    > application accesses the file, you get the lock contention. So you have
    > to
    > fix the problem in (3) and (4). DB2 is not to blame for that at all! It
    > means, you have to figure out how (3) and (4) is implemented and make sure
    > that the file is closed properly, there.
    >
    > Notice: A CREATE FUNCTION statement (level (2)) just registers the library
    > (3) in a DB2 database. It does not implement a function at all. Big
    > difference there!
    >
    >> The issue is I can not update the data in Excel file during testing
    >> stage.
    >
    > That's bad. How can you do any serious testing? Anyway, this question is
    > irrelevant to what you try to do.
    >>
    >>
    >> "Knut Stolze" <stolze@de.ibm.com> wrote in message
    >> news:ekq392$3t444$1@news.boulder.ibm.com...
    >>> Luke Xu wrote:
    >>>
    >>>> Knut,
    >>>>
    >>>> Could you let me know the syntax of how to close the file inside UDF? I
    >>>> have no idea and didnt get answer from google search either.
    >>>
    >>> How exactly is your UDF implemented? You have only shown is the CREATE
    >>> FUNCTION statement, which registers the UDF in your DB2 database. The
    >>> external code is the piece that must take care of this...
    >>>
    >>> --
    >>> Knut Stolze
    >>> DB2 Information Integration Development
    >>> IBM Germany
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany

  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-12-04T15:01:20Z  
    Hi,

    I wish it is not DB2 issue locking the Excel file. However, how can I
    explain the following testing:

    • Open DB2 DWB tool and issue SELECT command to retrieve data from UDF.
    • After I run the command, the Excel file is locked.
    • Exit DWB, the Excel file is still locked.
    • Stop DB2 service, the Excel file in UNLOCKed.

    I try to find any DB2 information about if UDF locks or unlocks the file
    like Excel or text, csv files, but unfortunately no answer i can get.

    Thanks

    Luke
    "Knut Stolze" <stolze@de.ibm.com> wrote in message
    news:ekqce7$3uo6c$2@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> I am doing testing in DB2 tools like DBW or Command Editor before I can
    >> release the UDF to developer. The external application will be ASP.NET
    >> web
    >> application. I will test it from web application if it can unlock the
    >> file.
    >
    > We have the following pieces in the picture which you have to sort out
    > first. They are essential that you understand your own architecture.
    > From
    > what I gathered, a top-down summary of the architecture would be this:
    >
    > (1) An application running on top of DB2 - that's your DBW or
    > Command Editor.
    > (2) DB2 itself.
    > (3) A UDF which is called by DB2 to do some calculations - reading the
    > file
    > and returning it in relational fashion.
    > (4) Some library or program that manages Excel files for you. This is
    > actually part of the UDF.
    > (5) The operating system that handles the file system and locks in the
    > file system.
    >
    > What I'm asking about is the implementation of (3). Maybe you are just
    > using some sort of library - after all, I have no idea what "!Sheet1$"
    > stands for in your CREATE FUNCTION statement. In any case, that's the
    > place you have to investigate.
    >
    > Your problem occurs at level (5): one application (the Excel reader in (3)
    > and (4)) opens the file and locks it in the process. When another
    > application accesses the file, you get the lock contention. So you have
    > to
    > fix the problem in (3) and (4). DB2 is not to blame for that at all! It
    > means, you have to figure out how (3) and (4) is implemented and make sure
    > that the file is closed properly, there.
    >
    > Notice: A CREATE FUNCTION statement (level (2)) just registers the library
    > (3) in a DB2 database. It does not implement a function at all. Big
    > difference there!
    >
    >> The issue is I can not update the data in Excel file during testing
    >> stage.
    >
    > That's bad. How can you do any serious testing? Anyway, this question is
    > irrelevant to what you try to do.
    >>
    >>
    >> "Knut Stolze" <stolze@de.ibm.com> wrote in message
    >> news:ekq392$3t444$1@news.boulder.ibm.com...
    >>> Luke Xu wrote:
    >>>
    >>>> Knut,
    >>>>
    >>>> Could you let me know the syntax of how to close the file inside UDF? I
    >>>> have no idea and didnt get answer from google search either.
    >>>
    >>> How exactly is your UDF implemented? You have only shown is the CREATE
    >>> FUNCTION statement, which registers the UDF in your DB2 database. The
    >>> external code is the piece that must take care of this...
    >>>
    >>> --
    >>> Knut Stolze
    >>> DB2 Information Integration Development
    >>> IBM Germany
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany

    Luke Xu wrote:

    > Hi,
    >
    > I wish it is not DB2 issue locking the Excel file. However, how can I
    > explain the following testing:
    >
    > - Open DB2 DWB tool and issue SELECT command to retrieve data from UDF.
    > - After I run the command, the Excel file is locked.
    > - Exit DWB, the Excel file is still locked.
    > - Stop DB2 service, the Excel file in UNLOCKed.
    >
    > I try to find any DB2 information about if UDF locks or unlocks the file
    > like Excel or text, csv files, but unfortunately no answer i can get.

    As I tried to explain: it is not DB2 that locks your file. It is the UDF.
    The UDF is some external library - and not the CREATE FUNCTION statement.
    Since you don't provide any information how this UDF is implemented, I
    don't see any point further discussing your issue.

    Knut Stolze
    DB2 Information Integration Development
    IBM Germany
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-12-04T15:51:25Z  
    Luke Xu wrote:

    > Hi,
    >
    > I wish it is not DB2 issue locking the Excel file. However, how can I
    > explain the following testing:
    >
    > - Open DB2 DWB tool and issue SELECT command to retrieve data from UDF.
    > - After I run the command, the Excel file is locked.
    > - Exit DWB, the Excel file is still locked.
    > - Stop DB2 service, the Excel file in UNLOCKed.
    >
    > I try to find any DB2 information about if UDF locks or unlocks the file
    > like Excel or text, csv files, but unfortunately no answer i can get.

    As I tried to explain: it is not DB2 that locks your file. It is the UDF.
    The UDF is some external library - and not the CREATE FUNCTION statement.
    Since you don't provide any information how this UDF is implemented, I
    don't see any point further discussing your issue.

    Knut Stolze
    DB2 Information Integration Development
    IBM Germany
    Knut,

    I feel confusion about your question. What more information do you need
    about UDF implemention? Let me carify it again:

    - It is not CREATE FUNCTION that locks the file, it is the following SELECT
    statement that locks the file:

    SELECT * FROM TABLE(ASO.HR_EMPLOYEES()) T

    • I already provided source code of CREATE FUNCTION

    • I will select the data from this UDF in DB2 DWB or Command Editor

    • I will put the above SELECT statement in ASP.NET as data source like what
    I did for other regular DB2 tables

    Wherever I issue SELECT statement in DB2 tool or in ASP.NET, the file is
    locked until I stop DB2 service. If it is not DB2 issue, why the file can be
    unlocked only when DB2 service stops. It may not be DB2 issue, but I didn't
    find the solution so far.

    I don't care which software or process causes the problem. I just want to
    fix the problem and keep using DB2 UDF.

    Thanks

    Luke
    "Knut Stolze" <stolze@de.ibm.com> wrote in message
    news:el1d80$1e46o$1@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> Hi,
    >>
    >> I wish it is not DB2 issue locking the Excel file. However, how can I
    >> explain the following testing:
    >>
    >> - Open DB2 DWB tool and issue SELECT command to retrieve data from UDF.
    >> - After I run the command, the Excel file is locked.
    >> - Exit DWB, the Excel file is still locked.
    >> - Stop DB2 service, the Excel file in UNLOCKed.
    >>
    >> I try to find any DB2 information about if UDF locks or unlocks the file
    >> like Excel or text, csv files, but unfortunately no answer i can get.
    >
    > As I tried to explain: it is not DB2 that locks your file. It is the
    > UDF.
    > The UDF is some external library - and not the CREATE FUNCTION statement.
    > Since you don't provide any information how this UDF is implemented, I
    > don't see any point further discussing your issue.
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany

  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to unlock Excel file in DB2 9

    ‏2006-12-04T18:03:37Z  
    Knut,

    I feel confusion about your question. What more information do you need
    about UDF implemention? Let me carify it again:

    - It is not CREATE FUNCTION that locks the file, it is the following SELECT
    statement that locks the file:

    SELECT * FROM TABLE(ASO.HR_EMPLOYEES()) T

    • I already provided source code of CREATE FUNCTION

    • I will select the data from this UDF in DB2 DWB or Command Editor

    • I will put the above SELECT statement in ASP.NET as data source like what
    I did for other regular DB2 tables

    Wherever I issue SELECT statement in DB2 tool or in ASP.NET, the file is
    locked until I stop DB2 service. If it is not DB2 issue, why the file can be
    unlocked only when DB2 service stops. It may not be DB2 issue, but I didn't
    find the solution so far.

    I don't care which software or process causes the problem. I just want to
    fix the problem and keep using DB2 UDF.

    Thanks

    Luke
    "Knut Stolze" <stolze@de.ibm.com> wrote in message
    news:el1d80$1e46o$1@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> Hi,
    >>
    >> I wish it is not DB2 issue locking the Excel file. However, how can I
    >> explain the following testing:
    >>
    >> - Open DB2 DWB tool and issue SELECT command to retrieve data from UDF.
    >> - After I run the command, the Excel file is locked.
    >> - Exit DWB, the Excel file is still locked.
    >> - Stop DB2 service, the Excel file in UNLOCKed.
    >>
    >> I try to find any DB2 information about if UDF locks or unlocks the file
    >> like Excel or text, csv files, but unfortunately no answer i can get.
    >
    > As I tried to explain: it is not DB2 that locks your file. It is the
    > UDF.
    > The UDF is some external library - and not the CREATE FUNCTION statement.
    > Since you don't provide any information how this UDF is implemented, I
    > don't see any point further discussing your issue.
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany

    In article <el1g5v$1f046$1@news.boulder.ibm.com>, linkxu@hotmail.com
    says...
    > Knut,
    >
    > I feel confusion about your question. What more information do you need
    > about UDF implemention? Let me carify it again:
    >
    > - It is not CREATE FUNCTION that locks the file, it is the following SELECT
    > statement that locks the file:
    >
    > SELECT * FROM TABLE(ASO.HR_EMPLOYEES()) T
    >
    > - I already provided source code of CREATE FUNCTION
    >
    > - I will select the data from this UDF in DB2 DWB or Command Editor
    >
    > - I will put the above SELECT statement in ASP.NET as data source like what
    > I did for other regular DB2 tables
    >
    > Wherever I issue SELECT statement in DB2 tool or in ASP.NET, the file is
    > locked until I stop DB2 service. If it is not DB2 issue, why the file can be
    > unlocked only when DB2 service stops. It may not be DB2 issue, but I didn't
    > find the solution so far.
    >
    > I don't care which software or process causes the problem. I just want to
    > fix the problem and keep using DB2 UDF.
    >
    > Thanks
    >
    > Luke
    >
    >
    > "Knut Stolze" <stolze@de.ibm.com> wrote in message
    > news:el1d80$1e46o$1@news.boulder.ibm.com...
    > > Luke Xu wrote:
    > >
    > >> Hi,
    > >>
    > >> I wish it is not DB2 issue locking the Excel file. However, how can I
    > >> explain the following testing:
    > >>
    > >> - Open DB2 DWB tool and issue SELECT command to retrieve data from UDF.
    > >> - After I run the command, the Excel file is locked.
    > >> - Exit DWB, the Excel file is still locked.
    > >> - Stop DB2 service, the Excel file in UNLOCKed.
    > >>
    > >> I try to find any DB2 information about if UDF locks or unlocks the file
    > >> like Excel or text, csv files, but unfortunately no answer i can get.
    > >
    > > As I tried to explain: it is not DB2 that locks your file. It is the
    > > UDF.
    > > The UDF is some external library - and not the CREATE FUNCTION statement.
    > > Since you don't provide any information how this UDF is implemented, I
    > > don't see any point further discussing your issue.
    > >
    > > --
    > > Knut Stolze
    > > DB2 Information Integration Development
    > > IBM Germany
    >
    >
    >

    Since you are using the ODBC wrapper the restrictions below apply
    (http://tinyurl.com/yd5qml). It looks like you're out of luck. Maybe
    using the excel wrapper does give you less restrictions but I'm familiar
    with that.

    Restrictions

    * The ODBC wrapper cannot access a worksheet when the workbook is
    already opened by a user or an application in the read/write mode.
    However, if the ODBC wrapper opens the workbook before a user or an
    application opens the workbook, the user or application can open the
    workbook in read-only mode.
    * The Excel ODBC driver expects that the first nonblank row contains
    the labels for the worksheet columns. You must insert a row of column
    labels in the worksheet if the worksheet does not have the labels.
    * Because the Excel ODBC driver is only available for Windows®
    operating systems, you can use the ODBC wrapper to access Excel data
    only on federated servers that run Windows.
    * You can perform insert and update operations on Excel worksheets,
    but you cannot perform delete operations. The Excel ODBC driver does not
    support delete operations. To delete data from the worksheet, you must
    open the worksheet in Excel to make the changes.
  • mexdisuza
    mexdisuza
    1 Post

    Re: How to unlock Excel file in DB2 9

    ‏2014-01-30T12:37:32Z  

    hi

    Need to recover MS Excel password and unlock excel file so just use MS Excel password recovery software which really help full tool to open your password protected Excel file. I also used this software when i forgot my Excel file password

    Read More - https://coderwall.com/p/8vlnqg