Sue M Wemp 27.Apr.06 04:44 PM a Web browser Connector for OLE DB LEI - All Releases All Platforms
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:
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
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?