How to include Member in an SQL query for DB2 on AS/400

thebird · Post by **thebird** » Mon Nov 13, 2006 4:23 pm

Hi All,

I have to pull data out of a DB2 database residing on AS/400. We have defined 3 members for most of the tables in the same schema, so as to point out to 3 different vendors.

Can I use the ODBC or DRS stages to extract the data from a table for each of these vendors? If so what would be the syntax of the sql to be given?

The table names are something like -

i) Schemaname.Tablename.Member1
ii) Schemaname.Tablename.Member2
iii) Schemaname.Tablename.Member3

I tried using these in the sql in a normal way and it gave me a connection error. Is there a specific syntax that would have to be used in such cases?

Or does this scenario mandate creation of flat files by the AS/400 with the data from these tables?

Thanks in advance.

Regards
The Bird.

ray.wurlod · Post by **ray.wurlod** » Mon Nov 13, 2006 4:58 pm

To access data in DB2 tables on mainframe or AS/400 systems you need to use the DB2/UDB API stage and communicate via DB2-Connect.

thebird · Post by **thebird** » Mon Nov 13, 2006 5:16 pm

Ray,

Thanks for the quick response. I was able to connect to the database using both DRS and ODBC stages. Just that I am unable to use the member name in my query to extract data from the table. I am still trying to figure this out.

Does the DB2/API stage give me any advantage over these stages when dealing with DB2 on AS/400? Would also like to know if this stage give me an option to include the member name in my SQL query?

Thanks in advance.

The Bird.

ray.wurlod wrote:To access data in DB2 tables on mainframe or AS/400 systems you need to use the DB2/UDB API stage and communicate via DB2-Connect. ...

ray.wurlod · Post by **ray.wurlod** » Mon Nov 13, 2006 5:34 pm

Have you imported the table definitions for these tables? If so, how are these column names represented in the table definition? The short answer is that, whatever this is, that's the name that you need to use.

thebird · Post by **thebird** » Tue Nov 14, 2006 1:41 pm

ray.wurlod wrote:Have you imported the table definitions for these tables? If so, how are these column names represented in the table definition? The short answer is that, whatever this is, that's the name that you ...

Hi Ray,

I have imported the table definitions and they appear simply as Schemaname.Tablename. I can use them in the SQL query, but I cannot include the Member in the query, and so it extracts data from the default member.

The scenario is that there are 3 members creatd for Table A.

TableA.member 1 contains 800 records
TableA.member 2 contains 1600 records
TableA.member 3 contains 200 records

But becuase I cannot mention the member name in the SQL query (as it does not appear in the table definition) the job would only extract data from the default member.

Is there a way that I can extract data from each of the members?

Or would I have to get back to the DB team and ask them to create 3 different tables instead of different members for a single table.

Thanks in advance,

The Bird.

ray.wurlod · Post by **ray.wurlod** » Tue Nov 14, 2006 1:51 pm

Is there any clue in the data to guide you to discern from which member a particular row comes?

thebird · Post by **thebird** » Tue Nov 14, 2006 4:52 pm

ray.wurlod wrote:Is there any clue in the data to guide you to discern from which member a particular row comes? ...

Unfortunately Ray, there is nothing in the data that will give us an idea about the member.

Do you have any suggestions as to how we can go about the extraction other than creating 3 separate tables?

ray.wurlod · Post by **ray.wurlod** » Tue Nov 14, 2006 5:20 pm

Three separate views?