• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Can't get 4 part naming to work on as400 link

I have a two part question:

1)  I have an as400 linked through my sqlexpress 2005.  I am successfull in quering the database using this statement:

Select * From OpenQuery(MHAS400,'Select * From QGPL.DLY_PROD')

However, I would like to use the four part naming.  I am using this statement, but not sure what to use for the QSECOFR.

select * from MHAS400.QGPL.QSECOFR.DLY_PROD

MHAS400 -linked server
QGPL - library
QSECOFR - user with acces to library (this is where I am not sure I have the correct info)
DLY_PROD- file/table

So what is the 3rd section of the 4 part naming convention?  

2) And will I be able to accomplish my goal pulling data from both the AS400 and SQL in the same statement similiar to this...

select * from MHAS400.QGPL.??.DLY_PROD as A
inner join SQLServer.SQLDBe.DBO.SQLTable as B on B.USADATE = A.USADATE

and retrieve data from both server much like querying two sql servers?

Thanks for the help on this.
0
williak7
Asked:
williak7
  • 2
1 Solution
 
LowfatspreadCommented:
you can write 2) as ...

select *
from (Select * From OpenQuery(MHAS400,'Select * From QGPL.DLY_PROD')) as A
inner join SQLServer.SQLDBe.DBO.SQLTable as B
on B.USADATE = A.USADATE

for 1) since you don't specify qsecfor in the openquery... i'm not sure what you're trying to achieve...

0
 
williak7Author Commented:
I tried qsecofr, but that didn't work.  The ?? were to represent the fact that I didn't know what went there.  I also tried a number of user id's that have access to the db.  I have seen where the 3rd part was respresented as dbo or schema, but I am not familiar enough with as400/db2 to know what that would be.

i did get your response to work.  I will see if my first part gets answered and post the points.

kevin
0
 
LowfatspreadCommented:
i think qgpl (your library) is actually the owner or schema ....

(a user doesn't normally own ... a table or view in db2... although he can have an alias or synonm defined for a table/view)

you may be able to get

mhas400.mhas400.qgpl.dlt_prod

to work ...

but the second mhas400 need to be the name of the subsystem or instance of DB2/UDB on the AS400...

you need to ask the as400 dba how his system is named internally and what aliases/nicknames he's exposed wants you to use...


btw do you know the version of DB2/UDB you're trying to connect to...  


hth
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now