Home » RDBMS Server » Networking and Gateways » Insert fails into AS400 from 10g using otgwyDRDA
Insert fails into AS400 from 10g using otgwyDRDA [message #333358] Fri, 11 July 2008 06:45 Go to next message
kuglerax
Messages: 4
Registered: June 2008
Location: NYC
Junior Member
I'm experiencing what I preceive as odd behavior when I try to insert into an AS400 from Oracle 10g2 using the OTGWY DRDA.

First, I was able to install and configure correctly. SELECT statements executed efficiently. I even created a table on AS400, journall edit, was able to insert and delete. Further along in our ETL development, one of our requirements was to dump data into some AS400 tables. We verified that they were in fact getting journalled and I ran a test insert statement and it executed correctly and I was able to delete the row as well and commit. Here is that statement:" insert into f4715_jde values('8',1,'00010',100054,'CE',1000,' ',' ','F47012' ,' ',0,' ',0,' ',100,'AF','MODEL N',' ',' ',' ',108191,134148);" Please note that f4715 is a public synonym to the AS400 table I am trying to insert into. Then, I placed a similiar statements in a PL/SQL block and they failed. here are those statements: "INSERT INTO F4715_JDE (ZDEDTY,ZDEDSQ,ZDEKCO,ZDEDOC,ZDEDCT
,ZDEDLN,ZDEDSP,ZDEDBT,ZDFILE,ZDKCOO
,ZDDOCO,ZDDCTO,ZDLNID,ZDPNTC,ZDLINS
,ZDTXLN,ZDTORG,ZDUSER,ZDPID ,ZDJOBN
,ZDUPMJ,ZDTDAY)
SELECT ZDEDTY,ZDEDSQ,ZDEKCO,ZDEDOC,ZDEDCT
,ZDEDLN,ZDEDSP,ZDEDBT,ZDFILE,ZDKCOO
,ZDDOCO,ZDDCTO,ZDLNID,ZDPNTC,ZDLINS
,ZDTXLN,ZDTORG,ZDUSER,ZDPID ,ZDJOBN
,ZDUPMJ,ZDTDAY
FROM F4715_ETL;"

"INSERT INTO F4715_JDE
(SELECT ZDEDTY,ZDEDSQ,ZDEKCO,ZDEDOC,ZDEDCT
,ZDEDLN,ZDEDSP,ZDEDBT,ZDFILE,ZDKCOO
,ZDDOCO,ZDDCTO,ZDLNID,ZDPNTC,ZDLINS
,ZDTXLN,ZDTORG,ZDUSER,ZDPID ,ZDJOBN
,ZDUPMJ,ZDTDAY
FROM F4715_ETL); "

Pleas note that F4715_ETL is the Oracle table and F4715_JDE is the synonym that points to the AS400 table. here is the error message I get:
INSERT INTO F4715_JDE
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
TG4DRDA v10.2.0.2.0 grc=0, drc=-777 (839C,0000), errp=QSQPLIST
sqlcode=-5001, sqlstate=42703, errd=0,0,0,0,103,0
errmc=*N,A2
ORA-02063: preceding 4 lines from JDE

Finally, I ran the COPY FROM statement and it inserted fine, but I can't use a SQLPLUS Command in PL/SQL.
stgdev:etladmin> copy from etladmin/etl123@stgdev.parpharm.com -
> insert testdta.f4715@jdedev -
> using select * FROM F4715_ETL;

Array fetch/bind size is 4000. (arraysize is 4000)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
1 rows selected from etladmin@stgdev.parpharm.com.
1 rows inserted into TESTDTA.F4715@JDEDEV.
1 rows committed into TESTDTA.F4715@JDEDEV at DEFAULT HOST connection.

stgdev:etladmin> commit;

Does anyone have any thoughts on what I am missing here????

Thank you in advance for any help that is offered.

Alex
Re: Insert fails into AS400 from 10g using otgwyDRDA [message #333365 is a reply to message #333358] Fri, 11 July 2008 07:01 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Read the OraFAQ Forum Guide before posting.
Re: Insert fails into AS400 from 10g using otgwyDRDA [message #333368 is a reply to message #333365] Fri, 11 July 2008 07:13 Go to previous messageGo to next message
kuglerax
Messages: 4
Registered: June 2008
Location: NYC
Junior Member
Can you be a bit more specific?
Re: Insert fails into AS400 from 10g using otgwyDRDA [message #333375 is a reply to message #333368] Fri, 11 July 2008 07:36 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

The section "How to format your post?" would be a nice start
Previous Topic: Problem with listener (tnslsnr) after install patch 10.2.0.4
Next Topic: [Client] How to multi home?
Goto Forum:
  


Current Time: Wed Apr 16 21:33:12 CDT 2014

Total time taken to generate the page: 0.08584 seconds