| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC69970 | Status: Closed |
DB2COMMANDBUILDER STATEMENTS RESULT IN TABLE NOT FOUND ERROR WHEN EXECUTING AGAINST IDS ANSI MODE DATABASES. | |
| product: | |
DB2 FOR LUW / DB2FORLUW / 980 - DB2 | |
| Problem description: | |
The IDS ANSI-mode database has some specific rules when it comes
to resolving the owner name of tables:
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?
topic=/com.ibm.sqls.doc/ids_sqs_1704.htm
(ommitted owner name) Reads or stores owner exactly as the
login name is stored in the system, but returns an error if the
user is not the owner.
The result is that if SQL statements are to have the owner name
included with the table name, care must be taken to ensure that
the use is consistent... the code below demonstrates an
inconsistency where the DB2DataAdapter is used. In the example
code below, the DB2CommandBuilder is going to generate a command
that includes the owner name without the use of quotes. When
that statement is used, an error is thrown indicating that the
table NEWTON.ownerprob is not found in the database. This
occurs even though the user did not use a owner in the original
SQL statement provided to the command builder.
This is not a problem when connected to either IDS buffered or
unbuffered logged databases.
static void IDS_owner_problem()
{
string connStr =
"database=mydb;server=mysystem.ibm.com:9089;uid=*****;pwd=******
;";
string tstSQL = "select col1 from ownerprob";
try
{
using (DB2Connection tstConn = new
DB2Connection(connStr))
{
tstConn.Open();
DB2Command tstCmd = tstConn.CreateCommand();
try
{
tstCmd.CommandText = "drop table ownerprob";
tstCmd.ExecuteNonQuery();
}
catch { /* don't care */ }
tstCmd.CommandText = "create table ownerprob
(col1 integer primary key constraint col1)";
tstCmd.ExecuteNonQuery();
tstCmd.CommandText = "insert into ownerprob
(col1) values (1)";
tstCmd.ExecuteNonQuery();
Console.WriteLine("Reading using DB2DataReader");
tstCmd.CommandText = tstSQL;
using (DB2DataReader tstRdr =
tstCmd.ExecuteReader())
{
while (tstRdr.Read())
{
Console.WriteLine("\tcol1: {0}",
tstRdr.GetInt32(0));
}
}
Console.WriteLine("add row using DataAdapter");
DB2DataAdapter tstDA = new DB2DataAdapter(tstSQL,
tstConn);
DataTable dt = new DataTable("ownerprob");
tstDA.Fill(dt);
Console.WriteLine("\tgot rows:");
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("\t\tcol1: {0}",
dr[0].ToString());
}
dt.Rows.Add(new object[] { 2 });
DB2CommandBuilder tstBldr = new
DB2CommandBuilder(tstDA);
tstDA.InsertCommand = tstBldr.GetInsertCommand();
tstDA.UpdateCommand = tstBldr.GetUpdateCommand();
int rowsAffected = tstDA.Update(dt); // throws
exception HERE!!
Console.WriteLine("\trows affected: {0}",
rowsAffected);
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("\t\tcol1: {0}",
dr[0].ToString());
}
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
using (DB2Connection tstConn = new
DB2Connection(connStr))
{
tstConn.Open();
DB2Command tstCmd = tstConn.CreateCommand();
tstCmd.CommandText = "drop table ownerprob";
try
{
tstCmd.ExecuteNonQuery();
}
catch { /* don't care */ }
}
}
} | |
| Problem Summary: | |
****************************************************************
* USERS AFFECTED: *
* .NET provider users on the Windows platform *
****************************************************************
* PROBLEM DESCRIPTION: *
* The IDS ANSI-mode database has some specific rules when it *
* comes *
* to resolving the owner name of tables: *
* *
* *
* *
* http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.js
* topic=/com.ibm.sqls.doc/ids_sqs_1704.htm *
* *
* (ommitted owner name) Reads or stores owner exactly as the *
* *
* login name is stored in the system, but returns an error if *
* the *
* user is not the owner. *
* *
* *
* *
* The result is that if SQL statements are to have the owner *
* name *
* included with the table name, care must be taken to ensure *
* that *
* the use is consistent... the code below demonstrates an *
* *
* inconsistency where the DB2DataAdapter is used. In the *
* example *
* code below, the DB2CommandBuilder is going to generate a *
* command *
* that includes the owner name without the use of quotes. *
* When *
* that statement is used, an error is thrown indicating that *
* the *
* table NEWTON.ownerprob is not found in the database. This *
* *
* occurs even though the user did not use a owner in the *
* original *
* SQL statement provided to the command builder. *
* *
* *
* *
* This is not a problem when connected to either IDS buffered *
* or *
* unbuffered logged databases. *
* *
* *
* *
* static void IDS_owner_problem() *
* *
* { *
* *
* string connStr = *
* *
* "database=mydb;server=mysystem.ibm.com:9089;uid=*****;pwd=***
* ;"; *
* *
* string tstSQL = "select col1 from ownerprob"; *
* *
* *
* *
* try *
* *
* { *
* *
* using (DB2Connection tstConn = new *
* *
* DB2Connection(connStr)) *
* *
* { *
* *
* tstConn.Open(); *
* *
* DB2Command tstCmd = tstConn.CreateCommand(); *
* *
* try *
* *
* { *
* *
* tstCmd.CommandText = "drop table *
* ownerprob"; *
* tstCmd.ExecuteNonQuery(); *
* *
* } *
* *
* catch { /* don't care */ } *
* *
* tstCmd.CommandText = "create table ownerprob *
* *
* (col1 integer primary key constraint col1)"; *
* *
* tstCmd.ExecuteNonQuery(); *
* *
* tstCmd.CommandText = "insert into ownerprob *
* *
* (col1) values (1)"; *
* *
* tstCmd.ExecuteNonQuery(); *
* *
* *
* *
* Console.WriteLine("Reading using *
* DB2DataReader"); *
* tstCmd.CommandText = tstSQL; *
* *
* using (DB2DataReader tstRdr = *
* *
* tstCmd.ExecuteReader()) *
* *
* { *
* *
* while (tstRdr.Read()) *
* *
* { *
* *
* Console.WriteLine("\tcol1: {0}", *
* *
* tstRdr.GetInt32(0)); *
* *
* } *
* *
* } *
* *
* *
* *
* Console.WriteLine("add row using *
* DataAdapter"); *
* DB2DataAdapter tstDA = new *
* DB2DataAdapter(tstSQL, *
* tstConn); *
* *
* DataTable dt = new DataTable("ownerprob"); *
* *
* tstDA.Fill(dt); *
* *
* Console.WriteLine("\tgot rows:"); *
* *
* foreach (DataRow dr in dt.Rows) *
* *
* { *
* *
* Console.WriteLine("\t\tcol1: {0}", *
* *
* dr[0].ToString()); *
* *
* } *
* *
* dt.Rows.Add(new object[] { 2 }); *
* *
* DB2CommandBuilder tstBldr = new *
* *
* DB2CommandBuilder(tstDA); *
* *
* tstDA.InsertCommand = *
* tstBldr.GetInsertCommand(); *
* tstDA.UpdateCommand = *
* tstBldr.GetUpdateCommand(); *
* int rowsAffected = tstDA.Update(dt); // *
* throws *
* exception HERE!! *
* *
* Console.WriteLine("\trows affected: {0}", *
* *
* rowsAffected); *
* *
* foreach (DataRow dr in dt.Rows) *
* *
* { *
* *
* Console.WriteLine("\t\tcol1: {0}", *
* *
* dr[0].ToString()); *
* *
* } *
* *
* } *
* *
* } *
* *
* catch (Exception e) *
* *
* { *
* *
* Console.WriteLine(e.ToString()); *
* *
* } *
* *
* finally *
* *
* { *
* *
* using (DB2Connection tstConn = new *
* *
* DB2Connection(connStr)) *
* *
* { *
* *
* tstConn.Open(); *
* *
* DB2Command tstCmd = tstConn.CreateCommand(); *
* *
* tstCmd.CommandText = "drop table ownerprob"; *
* *
* try *
* *
* { *
* *
* tstCmd.ExecuteNonQuery(); *
* *
* } *
* *
* catch { /* don't care */ } *
* *
* } *
* *
* } *
* *
* } *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 LUW Version 9.7 Fix Pack 3 or greater. The *
* fix for this APAR is not applicable to the version 9.8 *
* release since the 9.8 release is available only on the AIX *
* and Linux platforms. *
**************************************************************** | |
| Local Fix: | |
One solution to resolve the problem is to specify DB2CommandBuilder.QuotePrefix and DB2CommandBuilder.QuoteSuffix, setting both to a string containing a single quote ( QuotePrefix = "\""; ). Such an action will result in all database objects (table owner, table names, column names, etc.) being enclosed in quotes and preserving the case of the table owner. | |
| available fix packs: | |
DB2 Version 9.8 Fix Pack 4 for AIX and Linux | |
| Solution | |
Problem was first fixed in Version 9.7 Fix Pack 3. | |
| Workaround | |
not known / see Local fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 15.07.2010 08.08.2011 08.08.2011 |
| Problem solved at the following versions (IBM BugInfos) | |
9.7.FP3, 9.8. | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 9.8.0.4 |
|