After help from Midrange.com and Google we've been able to connect MS SQL to iSeries DB2 and run queries and stored procedures remotely returning the results to MS SQL
This is what was necessary:
1. On Microsoft SQL Server Management Studio set Server Options (Server Objects > Linked Servers > YourLinkedServerName ) RPC and RPC Out to 'True'. I think only the RPC Out is needed but we (the MS SQL Admin) set them both.
2. After that, we got a new error message:
The requested operation could not be performed because OLE DB provider "DB2OLEDB" for linked server "CFITESTLOANS" does not support the required transaction interface.
Based on an entry in a Microsoft Forum we (the Admin) changed Units of Work=RUW to DUW in the Linked Server connection string. I believe you will need journaling set on for any files being accessed which we already do system wide.
3. That got us connecting. Then a few more issues:
A. Permissions. Make sure the connector user profile has rights to your stored procedure and files. The error message is very generic
B. Parameter types. Our MS SQL SP has parameters defined as variable character but the DB2 SP was defined as regular or fixed character. Error message was couldn't find stored procedure with those parameters. Converting the types in MS SP fixed that.
-- Convert variable character types to fixed character
DECLARE @cBorrRcdID Char(9), @cBHCode Char(3), @cProgram Char(10), @cEntryID Char(12)
SET @cBorrRcdID = @BorrRcdID
SET @cBHCode = @BHCode
SET @cProgram = @Program
SET @cEntryID = @EntryID
-- Executes Insert INTO RBQHRESP on iSeries
EXEC ('Call IASPCFI.CODEMT.RQBHRES01 (?,?,?,?)', @cBorrRcdID, @cBHCode, @cProgram, @cEntryID)
AT CFITESTLOANS
C. If executing a stored procedure make sure the library qualifier ('CODEMT') specifies where the stored procedure is. On our iSeries they're in a different library than the data they access.
4. OPENQUERY (MS SQL speak for run SQL on remote or linked server) and EXEC Stored Procedure syntax.
DECLARE @TSQL varchar(max)
SELECT @TSQL = 'SELECT * FROM OPENQUERY(CFITESTLOANS,''SELECT * FROM IASPCFI.SLSFILESMT.SLBRREV1
WHERE Borrower_RcdID = ''''' + @BorrRcdID + ''''''')'
EXEC (@TSQL)
All the fooling around with quotes reminded me of Open Query File (still have Ted's book on the shelf!).
-- Retrieve Acct#, Last4 chars of Acct# from SLBRREP
INSERT INTO #tmpAcctNum
EXEC ('Call IASPCFI.CODEMT.SLBRRES02 (?)', @cBorrRcdID) AT CFITESTLOANS
-- Return results to caller
SELECT AcctNum, Last4 FROM #tmpAcctNum
Stored Procedure SLBRRES02 encapsulates the SELECT from a view.
CREATE PROCEDURE &Library/SLBRRES02 (
IN @BORROWER_RCDID CHAR(9) DEFAULT '000000000' )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC &Library/SLBRRES02
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
CONCURRENT ACCESS RESOLUTION DEFAULT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
P1 : BEGIN
DECLARE SLBRRES02_C1 CURSOR WITH RETURN FOR
SELECT DIGITS ( BORROWER_ACCT# ) AS ACCTNUM ,
SUBSTR ( DIGITS ( BORROWER_ACCT# ) , 6 , 4 ) AS LAST4
FROM SLBRREV1
WHERE BORROWER_RCDID = @BORROWER_RCDID
FETCH FIRST 1 ROW ONLY ;
OPEN SLBRRES02_C1 ;
END ;
Remove the IASP (Independent Auxiliary Storage Pool) name ('IASPCFI') if not in use in your installation.
I hope this helps the next programmer to save the several days of Googling and frustration in setting this up. Response went from tens of seconds when running the query on MS SQL to immediate when running queries directly or remotely on DB2. Don't believe it when the MS SQL guy sez it can't be done and wants to import/maintain your iSeries data in MS SQL so he can query it with the usual methods. We've already created several new SPs and are going back to update some already running processes to the quicker methods.
Thanks again to those who responded to my initial inquiry.
On Thu, May 24, 2018 at 11:16 AM, Jack Prucha <Jack.Prucha@xxxxxxx> wrote:
We're using a windows based imaging system that has a MS SQL document
repository. As part of the workflow we connect to the iSeries
application files. We're implementing the imaging system to interact
with our biggest iSeries application and the response can be slow because of the file sizes.
We speeded up this slow SELECT by creating a View on the iSeries side
returning just one column and subsetting the results with a join:
ALTER PROCEDURE [dbo].[SP_LNValidateBorrowerSSN]
@BorrSSN nvarchar(9)
AS
BEGIN
SELECT
Case
When count(*) >= 1 Then 'Valid'
Else 'Invalid'
End as SSNValid
FROM [CFITESTLOANS].[IASPCFI].[SLSFILESMT].[SLBRREV1]
Where Borrower_SSN = @BorrSSN
END
The process is still pulling over too many records so we want to push all
the work onto the iSeries side. Our smartest DB2 sql programmer helped me
create this Stored Procedure:
P1 : BEGIN
DECLARE EDITBORROWERSSN_C1 CURSOR WITH RETURN FOR
SELECT CASE
WHEN ( SELECT DISTINCT 1
FROM SLSFILESMT . SLBRREV1
WHERE BORROWER_SSN = @BORROWER_SSN
) IS NULL THEN RESULT
ELSE 'Valid'
END
FROM TABLE
( VALUES ( 'Invalid' ) )
AS TVALUES ( RESULT ) ;
OPEN EDITBORROWERSSN_C1 ;
END
This runs instantly when called from Run SQL scripts - CALL SLBRRES01
('123456789'). I cannot, however, come up with the correct syntax to call
the DB2 SP (SLBRRES01) from MS SQL. This "runs" without returning
anything as I can't setup the parameters without syntax errors:
EXEC('@Result = [CFITESTLOANS].[IASPCFI].[SLSFILESMT].[SLBRRES01]')
I'm trying different scenarios I've read on Google so I might be way off.
It doesn't have to be an SP. Could be a UDF or whatever on the
iSeries side. If this can be set up to run quickly we can use the
same method in many places.
Can anyone offer some help with this?
TIA
Ps
iSeries 7.3
MS SQL 2016 r13.x
This email, including any documents, files, or previous email messages attached to it, has been sent from an email account of College Foundation Inc., (CFI) and may contain confidential, proprietary, or legally privileged information belonging to CFI. If you are not the intended recipient, any dissemination, distribution, or copying of this email or its attachments is strictly prohibited. If you have received this email in error, please immediately notify the sender by email and destroy the original email and any attachments.
midrange.com
