Hi All,

I have some ODBC processes that have been locking tables that I work
with, causing lock issues later at night when we run our billing and
datawarehousing programs. Since this began about a week ago, I have
found the issue but I continue to double check that I'm not locking
anything by doing a WRKOBJLCK command every evening before I leave,
for each file that I've had the locking issues with.

Of course, doing this manually gets cumbersome. There must be some
way to automate this check, and having a limited iSeries background I
prefer to do it in DB2-SQL so that I can set up a Windows/SQL Server-
based solution.

What I don't know is any DB2-SQL commands that accomplish the same
thing as WRKOBJLCK, returning information about any locks that exist.
Is there a table I can query against or a function call that I can
make that would return information about locks?

Thanks in advance for your responses!

CW

cynthia.wagner@gmail.com wrote:
> Hi All,
>
> I have some ODBC processes that have been locking tables that I work
> with, causing lock issues later at night when we run our billing and
> datawarehousing programs. Since this began about a week ago, I have
> found the issue but I continue to double check that I'm not locking
> anything by doing a WRKOBJLCK command every evening before I leave,
> for each file that I've had the locking issues with.
>
> Of course, doing this manually gets cumbersome. There must be some
> way to automate this check, and having a limited iSeries background I
> prefer to do it in DB2-SQL so that I can set up a Windows/SQL Server-
> based solution.
>
> What I don't know is any DB2-SQL commands that accomplish the same
> thing as WRKOBJLCK, returning information about any locks that exist.
> Is there a table I can query against or a function call that I can
> make that would return information about locks?
>

There may not be any table you can query or SQL statement to query
object locks directly. However it should be fairly straightforward to
create an (external) stored procedure to call the List Object Locks
(QWCLOBJL) API. The results from such a call could (for example) be
inserted into a table for subsequent SQL queries.
http://publib.boulder.ibm.com/infoce...s/qwclobjl.htm

Another thought would be an external table function (UDTF) that calls
the List Object Locks API:
http://publib.boulder.ibm.com/infoce...fzmstcftbe.htm

--
Karl Hanson

On Dec 11, 1:18 pm, Karl Hanson <kchan...@youess.ibm.com> wrote:
> cynthia.wag...@gmail.com wrote:
> > Hi All,
>
> > I have some ODBC processes that have been locking tables that I work
> > with, causing lock issues later at night when we run our billing and
> > datawarehousing programs. Since this began about a week ago, I have
> > found the issue but I continue to double check that I'm not locking
> > anything by doing a WRKOBJLCK command every evening before I leave,
> > for each file that I've had the locking issues with.
>
> > Of course, doing this manually gets cumbersome. There must be some
> > way to automate this check, and having a limited iSeries background I
> > prefer to do it in DB2-SQL so that I can set up a Windows/SQL Server-
> > based solution.
>
> > What I don't know is any DB2-SQL commands that accomplish the same
> > thing as WRKOBJLCK, returning information about any locks that exist.
> > Is there a table I can query against or a function call that I can
> > make that would return information about locks?
>
> There may not be any table you can query or SQL statement to query
> object locks directly. However it should be fairly straightforward to
> create an (external) stored procedure to call the List Object Locks
> (QWCLOBJL) API. The results from such a call could (for example) be
> inserted into a table for subsequent SQL queries.http://publib.boulder.ibm.com/infoce...ndex.jsp?topic...
>
> Another thought would be an external table function (UDTF) that calls
> the List Object Locks API:http://publib.boulder.ibm.com/infoce...ndex.jsp?topic...
>
> --
> Karl Hanson- Hide quoted text -
>
> - Show quoted text -

Karl,

Excellent - I really appreciate the quick response. I'll try one of
those techniques and post back here once I'm finished for everyone's
benefit.

Thanks,
Cynthia

cynthia.wagner@gmail.com wrote:
> On Dec 11, 1:18 pm, Karl Hanson <kchan...@youess.ibm.com> wrote:
>> cynthia.wag...@gmail.com wrote:
>>> Hi All,
>>
>>> I have some ODBC processes that have been locking tables that I work
>>> with, causing lock issues later at night when we run our billing and
>>> datawarehousing programs. Since this began about a week ago, I have
>>> found the issue but I continue to double check that I'm not locking
>>> anything by doing a WRKOBJLCK command every evening before I leave,
>>> for each file that I've had the locking issues with.
>>
>>> Of course, doing this manually gets cumbersome. There must be some
>>> way to automate this check, and having a limited iSeries background
>>> I prefer to do it in DB2-SQL so that I can set up a Windows/SQL
>>> Server- based solution.
>>
>>> What I don't know is any DB2-SQL commands that accomplish the same
>>> thing as WRKOBJLCK, returning information about any locks that
>>> exist. Is there a table I can query against or a function call that
>>> I can make that would return information about locks?
>>
>> There may not be any table you can query or SQL statement to query
>> object locks directly. However it should be fairly straightforward to
>> create an (external) stored procedure to call the List Object Locks
>> (QWCLOBJL) API. The results from such a call could (for example) be
>> inserted into a table for subsequent SQL
>> queries.http://publib.boulder.ibm.com/infoce...ndex.jsp?topic...
>>
>> Another thought would be an external table function (UDTF) that calls
>> the List Object Locks
>> API:http://publib.boulder.ibm.com/infoce...ndex.jsp?topic...
>>
>> --
>> Karl Hanson- Hide quoted text -
>>
>> - Show quoted text -
>
> Karl,
>
> Excellent - I really appreciate the quick response. I'll try one of
> those techniques and post back here once I'm finished for everyone's
> benefit.
>
> Thanks,
> Cynthia

Maybe these 2 Redbooks will be useful as well:

- "Stored Procedures, Triggers, and User Defined Functions on DB2 UDB for
iSeries"
http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf

- "SQL Performance Diagnosis on DB2 UDB for iSeries"
http://www.redbooks.ibm.com/redbooks/pdfs/sg246654.pdf

I'm not familiar with DB2 on iSeries, but always like to complement the
manuals/infocenter with the Redbooks as they often provide very useful, real
life examples.

HTH

--
Jeroen