Error extracting data from a AS/400 system

[I_Server_Whale]

Hi All,
I'm trying to extract data from a AS/400 system. The table name is INVDPT.
In my job, I have a ODBC stage, a transformer and a flat-file target.
In the ODBC stage, I selected the generated query option in the "General" tab of the "Outputs" page. I also added the INVDPT table. I loaded the columns of the table from the imported table definition in the "Columns" tab.

But when I try to "view data". I get this error.

Code: Select all

Test2..ODBC_0.DSLink3: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:SELECT COMPVIEW/DEV2LIB.INVDPT.IDEPT, COMPVIEW/DEV2LIB.INVDPT.ISDEPT, COMPVIEW/DEV2LIB.INVDPT.ICLAS, COMPVIEW/DEV2LIB.INVDPT.ISCLAS, COMPVIEW/DEV2LIB.INVDPT.DPTNAM, COMPVIEW/DEV2LIB.INVDPT.DPTSHT FROM COMPVIEW/DEV2LIB.INVDPT 
SQLSTATE=S1000, DBMS.CODE=-5016
[DataStage][SQL Client][ODBC][IBM][iSeries Access ODBC Driver][DB2 UDB]SQL5016 - Qualified object name INVDPT not valid.

So, I decided to use "User-defined query" instead. And when I type the query as

Code: Select all

SELECT * FROM DEV2LIB/INVDPT

I'm able to view data this time. Why is this happening? Any ideas and suggestions or answers would be appreciated. How would I be able to view the data without the user-defined query? Thanks,

Whale.

[Sainath.Srinivasan]

Try adding the fields in the list. This is useful in order to avoid any inclusion or alteration in the table structure.

[I_Server_Whale]

I didn't get what you exactly meant by adding the fields to the list. how do I do that? Could you please explain in detail. I don't understand why the generated SQL is giving me error.

Thanks,
Whale.

[vmcburney]

I'm going to take a wild stab in the dark, if selecting from DEV2LIB/INVDPT worked and selecting from COMPVIEW/DEV2LIB.INVDPT did not work then shouldn't you alter your SQL and take the COMPVIEW out? You may be over qualifying your table names. On the columns tab and the table name tab it should be possible to modify the names to remove the COMPVIEW prefix.

It may also be that when you leave COMPVIEW out you are pointing at a different table schema to when you leave it in, and the INVDPT table does not exist in COMPVIEW/DEV2LIB but it does exist in another DEV2LIB.

[ray.wurlod]

Note the different location of the "/" and "." characters in

• COMPVIEW/DEV2LIB.INVDPT (which failed)
  and
  DEV2LIB/INVDPT (which worked)

The solution should now be clear.

[garthmac]

I always clear the derivation column info in the DB2 stage when connecting to DB2 on AS400 (not sure about the ODBC stage). If you did this to your ODBC stage, you SQL would be as follows:

SELECT IDEPT,ISDEPT,ICLAS,ISCLAS,DPTNAM,DPTSHT
FROM DEV2LIB.INVDPT

I work currently in an AS400 environment, and the above SQL format would work. That said, I'm sure Ray and Vincent have already helped you to solve your problem.