could not retrieve schema from AS400 and Oracle

One Star

could not retrieve schema from AS400 and Oracle

Hello everybody,
i've tried to use TOS V3.2.2 with AS400, erverything works fine except i could not retrieve schema from database Db2 on AS400. Firstly i've created a connection with AS400, pressed on ''Check'', it said the connection is ok (see screenshot). Then i've put a tAS400Input component on the mapping windows, connected it with the AS400 connection., pressed on the button beside the table name tab to get the table list, it said that the connection failed. Pressing on 'Guess schema' button was also failed. Only the 'SQL Builder' works, you can see in the screenshot that there is a connection with AS400. I had to create a schema manuelly and everything works fine. Any suggestions for a workaround? Thank you in advance. Here are some informations:
OS: Windows XP Prof. SP3
TOS: TOS V3.2.2 Build id: r33000-20091120-1245
Java 1.6.0_17
AS400: i6/OS V6R1M1
DB2 on AS400: 9.7 fixpack 1
PS: I have the same problem with Oracle
Oracle DB2: Oracle9i
Oracle DB is running on Windows XP Prof. SP2
One Star

Re: could not retrieve schema from AS400 and Oracle

We have exactly the same problem.
Employee

Re: could not retrieve schema from AS400 and Oracle

Hi,
Into your Databases Metadata repository; is it possible that you right-click on the Item created ?
Then you use the "Retrieve schema" action to connect to the database and retrieve the Table definition that you're looking for in your tAS400Input.
Pick up all or some tables; then press next when the retrieve is done; you have access to the table definiton. You can save it via "Finish" button.
Then when you would like to add a Table in the JobDesign; just drag'n'drop from your Metadata repository the Table registered; it should works fine.
Have a good day.
Best regards;
One Star

Re: could not retrieve schema from AS400 and Oracle

But what if i have custom SQL schema.
Like:
SELECT col1 AS id, col2 AS name, concat(col2,col3) AS fullname
..
One Star

Re: could not retrieve schema from AS400 and Oracle

Hi cantone,
thank you for your answer. I've tried your tip but, unfornately, it doesn' work with AS400(see screenshot). It only works with Oracle, but there is an error on the tOracleOutput component: table or view not exists. Do you have any suggestions? Thank you.
Best regards;
Hi,
Into your Databases Metadata repository; is it possible that you right-click on the Item created ?
Then you use the "Retrieve schema" action to connect to the database and retrieve the Table definition that you're looking for in your tAS400Input.
Pick up all or some tables; then press next when the retrieve is done; you have access to the table definiton. You can save it via "Finish" button.
Then when you would like to add a Table in the JobDesign; just drag'n'drop from your Metadata repository the Table registered; it should works fine.
Have a good day.
Best regards;
One Star

Re: could not retrieve schema from AS400 and Oracle

hi,
i am again. I think it should be a bug with AS400. Should i report it in Bugtracker? We are working on a project in which we have to import our customer' datas from DB2 on AS400 into our DB2 with TOS. If it's good, we maybe purchase it. But we are blocking because of this error and don't know what we should do now? Is there another ways to work with AS400 in TOS. I 've tried ODBC but it has the same problem. Thank you for any advices
One Star

Re: could not retrieve schema from AS400 and Oracle

Hi,
I've the same problem like thongMobi. When i do a right-click on my as400 connection and select "Retrieve schema", TOS freeze during some seconds and display the same error with no information! (Details of the error is "Error" like thongMobi's screenshot)
I use TOS v 3.2.1 r31371
Do you have solutions or informations ? Thank you!
Sorry for my english!
Community Manager

Re: could not retrieve schema from AS400 and Oracle

Hello vincent31
First, make sure you can connect to AS400, means that it shows successfully when click on the check button to test the connnection. Did you try the version TOS3.2.2? I tested it on TOS3.2.2 and it works fine.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: could not retrieve schema from AS400 and Oracle

Hello,
It seems the problem is not Talend but the server! I've many servers and it does work on one of them! I tried the same thing in others servers and it works fine! I've Talend formation in few weeks and will ask why it doesnt work on one of my servers!
One Star

Re: could not retrieve schema from AS400 and Oracle

Hello vincent31,
have you made any upgrades on the trouble servers. After looking into the log file i find out that there was an error when Talend tried to retrieve schema from AS400. The SQL error is SQL0901. Unfornately it is difficult to solve this issue on AS400. It often occurs after upgrading the server. Has someone any ideas?
Best regards
Hello,
It seems the problem is not Talend but the server! I've many servers and it does work on one of them! I tried the same thing in others servers and it works fine! I've Talend formation in few weeks and will ask why it doesnt work on one of my servers!
One Star

Re: could not retrieve schema from AS400 and Oracle

Hi all,
for those who've got the same problem Please check this: http://talendforge.org/bugs/view.php?id=7792