Default schema in db2cli.ini intermittantly ignored 
Author Message
 Default schema in db2cli.ini intermittantly ignored

Hi,

We are encountering an odd error where the default schema name that is
defined in the db2cli.ini file is being ignored or bypassed on an
infrequent basis, causing all table references to be prefixed with the
connecting username, and therefor failing. There seems to be no
pattern to when this occurs, but the frequency is becoming critical.
Simply deactivating and reactivating the database corrects this
problem (until the next time..)

The platforms are
asp.net application running on a win2k server with IIS 5.0 using ODBC
2.0
connecting to:
DB2 UDB v 7.2 FP5 running on NT

Any help or pointers would be greatly appreciated.
Thanks,
Sean



Tue, 04 Oct 2005 02:55:48 GMT
 Default schema in db2cli.ini intermittantly ignored

Sean,

I thought I saw that same thing happen around that time period. I just
started prefixing all my SQL calls with the schema name to avoid the
problem. Other things to try include :

1. Update to FP9 - maybe it has been fixed by then
2. After you set up your ADO connection execute (on the connection) a "SET
SCHEMA <My Schema>" command

Quote:

> Hi,

> We are encountering an odd error where the default schema name that is
> defined in the db2cli.ini file is being ignored or bypassed on an
> infrequent basis, causing all table references to be prefixed with the
> connecting username, and therefor failing. There seems to be no
> pattern to when this occurs, but the frequency is becoming critical.
> Simply deactivating and reactivating the database corrects this
> problem (until the next time..)

> The platforms are
> asp.net application running on a win2k server with IIS 5.0 using ODBC
> 2.0
> connecting to:
> DB2 UDB v 7.2 FP5 running on NT

> Any help or pointers would be greatly appreciated.
> Thanks,
> Sean



Tue, 04 Oct 2005 04:18:13 GMT
 Default schema in db2cli.ini intermittantly ignored
Kurt,

Thanks for your feedback.
Do you know if a specific fixpak addressed this problem, or are all
your table references still prefixed with a hard-coded schema?

Sean

Quote:

> Sean,

> I thought I saw that same thing happen around that time period. I just
> started prefixing all my SQL calls with the schema name to avoid the
> problem. Other things to try include :

> 1. Update to FP9 - maybe it has been fixed by then
> 2. After you set up your ADO connection execute (on the connection) a "SET
> SCHEMA <My Schema>" command


> > Hi,

> > We are encountering an odd error where the default schema name that is
> > defined in the db2cli.ini file is being ignored or bypassed on an
> > infrequent basis, causing all table references to be prefixed with the
> > connecting username, and therefor failing. There seems to be no
> > pattern to when this occurs, but the frequency is becoming critical.
> > Simply deactivating and reactivating the database corrects this
> > problem (until the next time..)

> > The platforms are
> > asp.net application running on a win2k server with IIS 5.0 using ODBC
> > 2.0
> > connecting to:
> > DB2 UDB v 7.2 FP5 running on NT

> > Any help or pointers would be greatly appreciated.
> > Thanks,
> > Sean



Tue, 04 Oct 2005 21:33:06 GMT
 Default schema in db2cli.ini intermittantly ignored

Quote:

> Kurt,

> Thanks for your feedback.
> Do you know if a specific fixpak addressed this problem, or are all
> your table references still prefixed with a hard-coded schema?

> Sean


> > Sean,

> > I thought I saw that same thing happen around that time period. I just
> > started prefixing all my SQL calls with the schema name to avoid the
> > problem. Other things to try include :

> > 1. Update to FP9 - maybe it has been fixed by then
> > 2. After you set up your ADO connection execute (on the connection) a "SET
> > SCHEMA <My Schema>" command


> > > Hi,

> > > We are encountering an odd error where the default schema name that is
> > > defined in the db2cli.ini file is being ignored or bypassed on an
> > > infrequent basis, causing all table references to be prefixed with the
> > > connecting username, and therefor failing. There seems to be no
> > > pattern to when this occurs, but the frequency is becoming critical.
> > > Simply deactivating and reactivating the database corrects this
> > > problem (until the next time..)

> > > The platforms are
> > > asp.net application running on a win2k server with IIS 5.0 using ODBC
> > > 2.0
> > > connecting to:
> > > DB2 UDB v 7.2 FP5 running on NT

> > > Any help or pointers would be greatly appreciated.
> > > Thanks,
> > > Sean

Sean,

Perhaps where it appears the db2cli.ini is being ignored, the
connection is not through CLI.  I had a similar problem, and by
monitoring my connections I realized there were times a CLI connection
was not being used.

In Command Center, if I run a query from the Script tab I see it using
package SQLC2D04 which doesn't pick up the db2cli.ini setting, but if
I use the Interactive tab then it uses package SQLLF000 which will
pick up the setting.

Steve



Fri, 07 Oct 2005 21:22:58 GMT
 Default schema in db2cli.ini intermittantly ignored
Steve,

That would certainly explain the very annoying behavior I often
experience in Command Center. However, the senario I'm seeing now
involves an asp.net application connecting to the database. For weeks
at a time it has no problems, and then suddenly out of the blue, it
'forgets' the default schema and starts spitting out errors because
the loginid is being used as the schema name. And it seems very
strange that deactivating/reactivating the database sets things
straight again.

Quote:


> > Kurt,

> > Thanks for your feedback.
> > Do you know if a specific fixpak addressed this problem, or are all
> > your table references still prefixed with a hard-coded schema?

> > Sean


> > > Sean,

> > > I thought I saw that same thing happen around that time period. I just
> > > started prefixing all my SQL calls with the schema name to avoid the
> > > problem. Other things to try include :

> > > 1. Update to FP9 - maybe it has been fixed by then
> > > 2. After you set up your ADO connection execute (on the connection) a "SET
> > > SCHEMA <My Schema>" command


> > > > Hi,

> > > > We are encountering an odd error where the default schema name that is
> > > > defined in the db2cli.ini file is being ignored or bypassed on an
> > > > infrequent basis, causing all table references to be prefixed with the
> > > > connecting username, and therefor failing. There seems to be no
> > > > pattern to when this occurs, but the frequency is becoming critical.
> > > > Simply deactivating and reactivating the database corrects this
> > > > problem (until the next time..)

> > > > The platforms are
> > > > asp.net application running on a win2k server with IIS 5.0 using ODBC
> > > > 2.0
> > > > connecting to:
> > > > DB2 UDB v 7.2 FP5 running on NT

> > > > Any help or pointers would be greatly appreciated.
> > > > Thanks,
> > > > Sean

> Sean,

> Perhaps where it appears the db2cli.ini is being ignored, the
> connection is not through CLI.  I had a similar problem, and by
> monitoring my connections I realized there were times a CLI connection
> was not being used.

> In Command Center, if I run a query from the Script tab I see it using
> package SQLC2D04 which doesn't pick up the db2cli.ini setting, but if
> I use the Interactive tab then it uses package SQLLF000 which will
> pick up the setting.

> Steve



Sat, 08 Oct 2005 20:31:38 GMT
 Default schema in db2cli.ini intermittantly ignored
It's been a while, so I can't recall.

Currently, I am just prefixing all the table names with the schema and
using aliasing to shorten the call "

SELECT n.name,a.address
  FROM myschema.names n, myschema.address a
  WHERE n.id = a.id


Quote:
>Kurt,

>Thanks for your feedback.
>Do you know if a specific fixpak addressed this problem, or are all
>your table references still prefixed with a hard-coded schema?

>Sean


>> Sean,

>> I thought I saw that same thing happen around that time period. I just
>> started prefixing all my SQL calls with the schema name to avoid the
>> problem. Other things to try include :

>> 1. Update to FP9 - maybe it has been fixed by then
>> 2. After you set up your ADO connection execute (on the connection) a "SET
>> SCHEMA <My Schema>" command


>> > Hi,

>> > We are encountering an odd error where the default schema name that is
>> > defined in the db2cli.ini file is being ignored or bypassed on an
>> > infrequent basis, causing all table references to be prefixed with the
>> > connecting username, and therefor failing. There seems to be no
>> > pattern to when this occurs, but the frequency is becoming critical.
>> > Simply deactivating and reactivating the database corrects this
>> > problem (until the next time..)

>> > The platforms are
>> > asp.net application running on a win2k server with IIS 5.0 using ODBC
>> > 2.0
>> > connecting to:
>> > DB2 UDB v 7.2 FP5 running on NT

>> > Any help or pointers would be greatly appreciated.
>> > Thanks,
>> > Sean



Sat, 08 Oct 2005 21:29:00 GMT
 Default schema in db2cli.ini intermittantly ignored
connection pooling?

PM



Sun, 09 Oct 2005 03:08:39 GMT
 Default schema in db2cli.ini intermittantly ignored
Sean,

I can't say I have any idea on your asp.net application.  I haven't
run across a problem like you're seeing.  Have you tried monitoring
your connections to see if anything appears different when the problem
begins?

Sorry I can't be more helpful.

Steve

Quote:

> Steve,

> That would certainly explain the very annoying behavior I often
> experience in Command Center. However, the senario I'm seeing now
> involves an asp.net application connecting to the database. For weeks
> at a time it has no problems, and then suddenly out of the blue, it
> 'forgets' the default schema and starts spitting out errors because
> the loginid is being used as the schema name. And it seems very
> strange that deactivating/reactivating the database sets things
> straight again.



> > > Kurt,

> > > Thanks for your feedback.
> > > Do you know if a specific fixpak addressed this problem, or are all
> > > your table references still prefixed with a hard-coded schema?

> > > Sean


> > > > Sean,

> > > > I thought I saw that same thing happen around that time period. I just
> > > > started prefixing all my SQL calls with the schema name to avoid the
> > > > problem. Other things to try include :

> > > > 1. Update to FP9 - maybe it has been fixed by then
> > > > 2. After you set up your ADO connection execute (on the connection) a "SET
> > > > SCHEMA <My Schema>" command


> > > > > Hi,

> > > > > We are encountering an odd error where the default schema name that is
> > > > > defined in the db2cli.ini file is being ignored or bypassed on an
> > > > > infrequent basis, causing all table references to be prefixed with the
> > > > > connecting username, and therefor failing. There seems to be no
> > > > > pattern to when this occurs, but the frequency is becoming critical.
> > > > > Simply deactivating and reactivating the database corrects this
> > > > > problem (until the next time..)

> > > > > The platforms are
> > > > > asp.net application running on a win2k server with IIS 5.0 using ODBC
> > > > > 2.0
> > > > > connecting to:
> > > > > DB2 UDB v 7.2 FP5 running on NT

> > > > > Any help or pointers would be greatly appreciated.
> > > > > Thanks,
> > > > > Sean

> > Sean,

> > Perhaps where it appears the db2cli.ini is being ignored, the
> > connection is not through CLI.  I had a similar problem, and by
> > monitoring my connections I realized there were times a CLI connection
> > was not being used.

> > In Command Center, if I run a query from the Script tab I see it using
> > package SQLC2D04 which doesn't pick up the db2cli.ini setting, but if
> > I use the Interactive tab then it uses package SQLLF000 which will
> > pick up the setting.

> > Steve



Sun, 09 Oct 2005 04:20:42 GMT
 Default schema in db2cli.ini intermittantly ignored
Meaning.... Connection pooling Should be turned on, or should Not be?
Wouldn't a 'pooled', reused connection still retain the default schema
from the db2cli.ini file used when the connection was intially
created?
Quote:

> connection pooling?

> PM



Fri, 14 Oct 2005 20:15:22 GMT
 Default schema in db2cli.ini intermittantly ignored
If you monitor the SQL Statements, you should see an explicit statement
like:
   "SET CURRENT SCHEMA <db2cli.ini CURRENTSCHEMA string>"
for all your cli connection.
I am guessing that some application is making the connection without
reading the db2cli.ini.
I am also guessing that the database/server is not the source of the
problem, more likely to be something on the client side (e.g., making
the db2cli.ini dir unreadable for a time).

Aside, the SQLGetConnectAttr( ... SQL_CURRENT_SCHEMA) will show the
db2cli.ini setting. If its not expected string, your app could try to
read the db2cli.ini file itself (and log it?).

HTH,
Dave.

Quote:

>Hi,

>We are encountering an odd error where the default schema name that is
>defined in the db2cli.ini file is being ignored or bypassed on an
>infrequent basis, causing all table references to be prefixed with the
>connecting username, and therefor failing. There seems to be no
>pattern to when this occurs, but the frequency is becoming critical.
>Simply deactivating and reactivating the database corrects this
>problem (until the next time..)

>The platforms are
>asp.net application running on a win2k server with IIS 5.0 using ODBC
>2.0
>connecting to:
>DB2 UDB v 7.2 FP5 running on NT

>Any help or pointers would be greatly appreciated.
>Thanks,
>Sean



Sat, 15 Oct 2005 01:06:48 GMT
 Default schema in db2cli.ini intermittantly ignored
Hmmm.. After reconsidering PM's concise reply above, I think I may
have answered my own question. I was just looking at it backwards.

Could this be a possible cause for this scenario?:

APP-A runs from a client whose db2cli.ini file has no defaultschema

APP-B runs from a client whose db2cli.ini file has defaultschema=abc

APP-A connects to the database and finishes its UOW

APP-B subsequently connects to the database and reuses the same
connection created by APP-A. Since its reusing an extant connection,
it Doesn't use it's own db2cli.ini parameters (defaultschema=abc), but
inherits the ones associated with APP-A, which includes no
defaultschema value.

Is this possible?



Sun, 16 Oct 2005 22:10:09 GMT
 Default schema in db2cli.ini intermittantly ignored
No opinions on this?

Empirically, this looks like what may have been happening. There was a
process connecting to the database using a different default schema.
The db2cli.ini file for that process has now been modified to use the
same default schema as all other connections, and the problem has not
recurred.
It would be nice to get some third-party confirmation that this was in
fact the problem, though.
Can anyone confirm?

Thanks,
Sean

Quote:

> Hmmm.. After reconsidering PM's concise reply above, I think I may
> have answered my own question. I was just looking at it backwards.

> Could this be a possible cause for this scenario?:

> APP-A runs from a client whose db2cli.ini file has no defaultschema

> APP-B runs from a client whose db2cli.ini file has defaultschema=abc

> APP-A connects to the database and finishes its UOW

> APP-B subsequently connects to the database and reuses the same
> connection created by APP-A. Since its reusing an extant connection,
> it Doesn't use it's own db2cli.ini parameters (defaultschema=abc), but
> inherits the ones associated with APP-A, which includes no
> defaultschema value.

> Is this possible?



Fri, 21 Oct 2005 20:53:44 GMT
 
 [ 12 post ] 

 Relevant Pages 

1. Cannot set SCHEMA in db2cli.ini

2. How to ignore a column in Schema.ini file

3. Settings in DB2CLI.INI

4. question about plain text password in db2cli.ini

5. db2cli.ini question

6. schema.ini

7. Schema.ini With Adhoc ODBC Text File Connections

8. schema.ini - linked server help

9. Schema.ini File

10. Help -- Schema.Ini file format

11. Can I avoid schema.ini in VB5?

12. SCHEMA.INI Must I use this file name???


 
Powered by phpBB® Forum Software