Solved

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

Posted on 2006-06-16
3
280 Views
Last Modified: 2008-03-17
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
Comment
Question by:williak7
  • 2
3 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16924761
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
 

Author Comment

by:williak7
ID: 16925075
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 16925139
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
set default date format in mssql to mm/dd/yyyy 22 66
SQL Error in WHERE Clause 5 38
SQL Server Question 5 25
Help Extract Specific in SQL 8 19
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now