home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC69963 Status: Geschlossen

DB2COMMANDBUILDER STATEMENTS RESULT IN TABLE NOT FOUND ERROR WHEN
EXECUTING AGAINST IDS ANSI MODE DATABASES.

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
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-Zusammenfassung:
**************************************************************** 
* 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                    * 
****************************************************************
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.
verfügbare FixPacks:
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

Lösung
Problem was first fixed in Version 9.7 Fix Pack 3
Workaround
keiner bekannt / siehe Local-Fix
Bug-Verfolgung
Vorgänger  : APAR is sysrouted TO one or more of the following: IC69970 
Nachfolger : 
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
15.07.2010
16.09.2010
16.09.2010
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FP3
Problem behoben lt. FixList in der Version
9.7.0.3 FixList
9.7.0.3 FixList