1

I use linked server to connect AS400 DB2.

For example: select query can work

select * 
from openquery([DB2], 'select t1.* from  lib.table01 t1
                       fetch first 1 rows only')

But I want to use query

Create Alias Library.T_temp For Library.T1 (MemberName)

in SQL Server.

It returned an error because it have no return rows.

As following (it will return error):

Select * from OpenQuery([DB2],' Create Alias...')

Update OpenQuery([DB2],' Create Alias...')

Is there any method to do that?

Thanks

mswatosh
  • 466
  • 2
  • 8
White Gallime
  • 11
  • 2
  • `exec ('Create Alias .... ' ) at [DB2];` ?? – lptr Dec 04 '20 at 12:43
  • 2
    Better question is why you think this is an appropriate task to do at all. An alias is an object and you (especially without intimate knowledge of your remote database) should not be creating new objects in it. That usually requires elevated permissions. But once created, it will continue to exist forever (or until dropped). So the second time you travel this path, what happens? An error occurs, correct? Suggest you step back and reconsider the path you have chosen. – SMor Dec 04 '20 at 12:47
  • @SMor I know it will be created.If "create alias.." can work than I will consider drop alias.Just want to know that statement if it would work in MSSQL or not. – White Gallime Dec 04 '20 at 13:46
  • I see nothing wrong with creating an alias. In fact it is necessary if you want to use SQL against a multiple-member database file. I just don't see why you would route a database structural change through SQL server. It would be better done via OLEDB or directly on the green screen terminal. If you don't have access to that, then it becomes a request to your DB admin, appropriate because it is a permanent object for accessing that member. – Mike Dec 07 '20 at 16:01

1 Answers1

0

Don't try..

Your openquery() is the preferred solution.

By using openquery(), the SQL statement is passed to Db2 and run there. Since you've included a fetch first 1 rows only only 1 row is returned.

the query form

select TOP 1 t1.* 
from db2.myibmi.lib.table01 t1
offset 0 rows
first first 1 row only

Will actually pull back all rows to SQL Server, then filter them on the SQL Server. (At least I know that's how it used when a WHERE clause was included. I assume TOP isn't any better)

Charles
  • 21,637
  • 1
  • 20
  • 44