Sue M Wemp 27.Apr.06 04:44 PM a Web browser Connector for OLE DBLEI - All ReleasesAll Platforms
Background
I'm trying to connect Notes to a DB2 database. Right now, I'm just trying to connect to one table in the DB2 database, but before I can add new rows to the DB2 table, I need to update index information in several other tables. Here is the SQL that has been provided by the DB2 DBA:
DECLARE @NextCall_ID as NUMERIC
DECLARE @GetTableID as NUMERIC
DECLARE @GetTableID2 as NUMERIC
DECLARE @task_date as DATETIME
SET @task_date = getdate()
SELECT @NextCall_ID = idt_NextID FROM IdentityDef WHERE idt_TableName = 'CALL_ID'
UPDATE IdentityDef SET idt_NextId = (@NextCall_ID + 1) WHERE idt_TableName = 'CALL_ID' AND idt_NextID = @NextCall_ID
SELECT @GetTableID = idt_NextID FROM IdentityDef WHERE idt_TableName = 'DD_ID'
UPDATE IdentityDef SET idt_NextId = (@GetTableID + 1) WHERE idt_TableName = 'DD_ID' AND idt_NextID = @GetTableID
INSERT INTO datadescription values (@GetTableID,' ',' ','',1073742848)
SELECT @GetTableID2 = idt_NextID FROM IdentityDef WHERE idt_TableName = 'DD_ID'
UPDATE IdentityDef SET idt_NextId = (@GetTableID2 + 1) WHERE idt_TableName = 'DD_ID' AND idt_NextID = @GetTableID2
INSERT INTO datadescription values (@GetTableID2,' ',' ','',1073742848)
What I've tried:
Created an agent using lsxlc but can't map the names and insert without running the SQL first.
Creating a Command Line activity in LEI with a dependent Direct Transfer and I still get the following Error: Violation of PRIMARY KEY constraint 'PK_Call'. Cannot insert duplicate key in object 'Call (DB2 table I'm trying to update)'.
Creating a Replication activity but don't know where to put the SQL statement so that it updates the index field accordingly.
Where's I've looked:
Notes forums
Sandbox
Redbook - Implementing IBM Lotus Enterprise Integrator 6
Andre's presentation - Accessing Relational Data with the Lotus Connectors LSX
Lotus Connector LotusScript Extensions Guide
Lotus Connector and Connectivity Guide
Questions:
Is it possible to use this SQL statement either in LEI or in the connection.execute statement? and if so...how do I write it? each statement in quotes and concatinated?
Should I be looking at a different method of transferring the information? e.g. Direct Transfer to a text file and have the DB2 database do it's own updating of information?
Return to top
Complex SQL before Transfer/Replica... (Sue M Wemp 27.Apr.06)
. . 
Print this page
