Re: Problems Updating via DB link to AS400/DB2

From: ahogan <hoganAPLP_at_gmail.com>
Date: Fri, 08 Jun 2007 00:08:52 -0700
Message-ID: <1181286532.072523.10810@w5g2000hsg.googlegroups.com>

On 7 jun, 21:18, DA Morgan <damor..._at_psoug.org> wrote:
> ahogan wrote:
> > I have created a link using generic connectivity from a 10.2.0.1
> > instance running on windows 2003 64 bit server to an AS400/DB2
> > database (V5R2 AFAIK).
> > I can execute Select, Insert and Delete statements via the link
> > without error. When I launch an update from sqlplus I get the
> > following error:
> > SQL> update para.mfam_at_TEST set pcau = 'dummy'
> > 2 where cod=4281 and tip = 'P' and codu =2001;
> > update para.mfam_at_TEST set pcau = 'dummy'
> > *
> > ERROR at line 1:
> > ORA-28500: la conexión de ORACLE a un sistema no Oracle ha devuelto
> > este
> > mensaje:
> > [Generic Connectivity Using ODBC][A055] The column 'PCAU' is not
> > updateable.
> > ORA-02063: 2 lines precediendo a TEST
>
> > I can execute the same sentence directly on DB2 without error (same
> > userid):
>
> > update para/mfam set pcau = 'dummy'
> > where cod=4281 and tip = 'P' and codu
> > =2001
> > 1 filas actualizadas en MFAM de PARA.
>
> > The HS trace file shows:
> > (0) [IBM][Controlador ODBC de iSeries Access][DB2 UDB]SQL0204 - MFAM
> > en QGPL
> > (0) de tipo *FILE no encontrado. (SQL State: S0002; SQL Code: -204) --
> > *FILE not found
> > (0) (Last message occurred 2 times)
> > (0)
> > (0) [A055] The column 'PCAU' is not updateable.
> > (0)
>
> > Google seems to indicate that this is not exclusively an Oracle error
> > but I can't find any definite suggesions or solutions.
>
> > Any ideas,
>
> > Regards,
> > Anthony Hogan
>
> para.mfam_at_TEST may be a view rather than a table. Try these:
>
> SELECT object_type
> FROM all_objects_at_TEST
> WHERE owner = 'PARA'
> AND object_name = 'MFAM';
>
> and
>
> SELECT column_name, updatable, insertable, deletable
> FROM all_updatable_columns
> WHERE owner = 'PARA'
> AND object_name = 'MFAM';
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -

Thanks. I did think of that.

SQL> SELECT object_type
  2 FROM all_objects_at_TEST
  3 WHERE owner = 'PARA'
  4 AND object_name = 'MFAM';

OBJECT_TYPE

---

TABLE Your second query is an excellent suggestion but I can't get a result:

(running against remote DB)
SQL> SELECT column_name, updatable, insertable, deletable   2 FROM all_updatable_columns_at_test
  3 WHERE owner = 'PARA'
  4 AND TABLE_name = 'MFAM';
FROM all_updatable_columns_at_test

     *
ERROR at line 2:
ORA-00942: la tabla o vista no existe --table or view does not exist [Generic Connectivity Using ODBC][IBM][Controlador ODBC de iSeries Access][DB2
UDB]SQL0204 - ALL_UPDATABLE_COLUMNS en QGPL de tipo *FILE no encontrado. (SQL
State: S0002; SQL Code: -204)
ORA-02063: 2 lines precediendo a TEST

Do you know what the equivalent view is in DB2?

Regards,
Anthony Hogan Received on Fri Jun 08 2007 - 02:08:52 CDT