Product: | DB Connectivity OLE DB Products |
Version: | All |
ID: | 1345 |
Summary: | How to update data on Db2 for i through the linked server while restricting the amount of data by joining the Db2 table with a table local to SQL Server |
To create a join between a remote Db2 table and a local SQL Server table, take the following steps.
From Query Analyzer, use an SQL statement similar to the one below:
UPDATE MY400.S109PR5N.MYLIB.MYTABLE
SET FLD1 = 'SomeValue'
FROM MyLocalDB.DBO.SQLTABLE MyLocalAlias
INNER JOIN MY400.S109PR5N.MYLIB.MYTABLE MYALIAS
ON MyLocalAlias.COL1 = MYALIAS.FLD1
WHERE MyLocalAlias.COL2 Is Not Null AND
MYALIAS.FLD2 Is Null
Where:
MY400: The name of the linked server
S109PR5N: The name of your DB2. If you do not know the name of your DB2, type in the command DSPRDBDIRE from the command console and look for the name labeled LOCAL.
MYLIB: The library where your AS/400 file is located
MYTABLE: The name of the AS/400 file you wish to access
MYALIAS: An alias you are giving to your AS/400 file
SQLTABLE: The table local to the SQL Server.
If you have a production library and a development library, you can create a stored procedure for the SQL statement above on the SQL Server, with all the default variables made for the development library first. To execute the procedure, simply type:
Exec PrJoinUpdate
Once the stored procedure is working, you can simply specify a different library in the variable by calling the stored procedure this way:
Exec PrJoinUpdate
@LibName = 'PRODLIB'
And the SQL statement will be done on MY400.S109PR5N.PRODLIB.MYTABLE instead of MY400.S109PR5N.MYLIB.MYTABLE
Code for the stored procedure
CREATE PROCEDURE PrJoinUpdate
(
@LinkSvrName varchar(20) = 'MY400',
@RDBName varchar(20) = 'S109PR5N',
@LibName varchar(20) = 'MYLIB',
@LinkTableName varchar(20) = 'MYTABLE',
@LinkUpdateField varchar(20) = 'FLD1',
@LinkUpdateFieldValue varchar(20) = 'SomeValue',
@LinkJoinField varchar(20) = 'FLD1',
@LinkWhereCondition varchar(50) = 'FLD1 Is Null',
@LocalDBName varchar(20) = 'MyLocalDB,
@LocalDBTable varchar(20) = 'SQLTABLE',
@LocalDBJoinField varchar(20) = 'COL1',
@LocalDBWhereCondition varchar(50) = 'COL2 Is Not Null'
)
AS
--SET NoCount ON
--If NoCount ON is enabled, the stored procedure runs faster
--but you will not get any record count
Declare
@querystring varchar(1000),
@MyLinkedServer varchar(200),
@MyLocalTable varchar(200)
SELECT
@MyLinkedServer = @LinkSvrName + '.' + @RDBName + '.' + @LibName + '.' + @LinkTableName,
@MyLocalTable = @LocalDBName + '.DBO.' + @LocalDBTable,
@querystring =
'UPDATE ' + @MyLinkedServer +
' SET ' + @LinkUpdateField + ' = ' + '''' + @LinkUpdateFieldValue + '''' +
' FROM ' + @MyLocalTable + ' MyLocalAlias' +
' INNER JOIN ' + @MyLinkedServer + ' LinkSvrAlias' +
' ON MyLocalAlias.' + @LocalDBJoinField + '=' + 'LinkSvrAlias.' + @LinkJoinField
If Len(@LocalDBWhereCondition) >0
Begin
set @querystring = @querystring +
' WHERE MyLocalAlias.' + @LocalDBWhereCondition
End
If Len (@LinkWhereCondition)>0
Begin
set @querystring = @querystring +
' AND LinkSvrAlias.' + @LinkWhereCondition
End
print @querystring
exec (@querystring)