How do I dump selected records from sql server to a mainframe?

[Ken011]

Greetings team:


I am in a bit of a bind here.

I have 2 databases running on 2 different servers.

One of the database is a SQL Server database. The other runs on a mainframe.

There are over 6,000 records in the SQL Server db and counting.

We would like to automate a process that initially copies selected records from the sql server db into the mainframe db and then perform an hourly update. With the hourly update, after the initial data dump, this automated process will then copy and transfer only records that are inserted into the SQL Server db since the last data transfer.

My first thought is to use DTS package but I can't use DTS because I am not transferring all the records from db to another. I am only selecting records where the data definition from the sql server db is the same as the other db.

Second, I am not sure that dts gives me the control to determine when a new record has been added since the last update.

A script will probably do the job but I am just not sure how to proceed with the script.

Can someone, please give me any ideas you may have.

Thank you very much.

 

[mrdenny]

Your best bet will be to setup a flag column. Then as you transfer the records change the flag so that you know they have been transfered.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Ken011]

--Your best bet will be to setup a flag column. Then as you transfer the records change the flag so that you know they have been transfered.

this is a bit puzzling response but thanks anyway.

 

[AlexCuse]

'flag column' is just a bit field, or whatever else you want to use as an indicator. For example, you set this column to default to zero. Set up your DTS to grab only rows where this field is zero. After transfering the records, the field that defaults to zero is set to one. This prevents you from grabbing any records that have already been transfered.

[shot in the dark]
(I don't work with mainframes at all but I think you may need to export to .csv or text file, then pull into mainframe)
[/shot in the dark]

HOpe it helps,

Alex

TH

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[Ken011]

hi Alex,

Thanks for further clarification but I believe I understood his original intention.

What I found puzlling was I didn't think that the issue that I have.

For instance, take a look at this snip:

--get selected records from source db (this is sql server)
CREATE PROCEDURE dbo.copyData
AS
BEGIN
DECLARE Notify_Cursor CURSOR FOR
SELECT the_fields_I_want from sourceTable where flag = 0
ORDER BY by id DESC

OPEN Notify_Cursor
--Declare variables for selected fields
-- Get the current MAX ID
Declare @id as int
-- Start reading each record from the cursor.
FETCH Notify_Cursor into @declared variables
WHILE @@FETCH_STATUS = 0
BEGIN
set @ID = (SELECT max(autoID)+ 1 from destinationTable)
INSERT into destinationTable (autoid,
selected_Fields_From_SourceTable)
VALUES (
@declared variables
)
--get those variables again into a cursor

FETCH Notify_Cursor into @declared variables
END

CLOSE Notify_Cursor
DEALLOCATE Notify_Cursor
END



--Set the flag ecord in in the destinationTable table where flag = 0?????

Update destinationTable SET flag = 1 WHERE flag = 1 and autoid = @id1

is this where you guys are suggesting?

I know you came up with a nice idea of exporting to a .csv file first but is this process going to be part of the automation?

I am so lost with this.

I have bought a bit more time with this and I really need you guys assistance to pull this off.

Thanks much

 

[mrdenny]

There isn't any reason to do this in a cursor. A set based operation will be much faster.

CREATE PROCEDURE dbo.CopyData AS
BEGIN DISTRIBUTED TRANSACTION
    insert into RemoteMachine..Database.Table
    select col1, col2, col4, ...
    from table
    where flag = 0

    update table
    set flag = 1
    where flag = 0
COMMIT TRANSACTION
GO

If you don't want to do it within a transaction because of locking / blocking issues this will work.

CREATE PROCEDURE dbo.CopyData AS
DECLARE @table TABLE (id INT) /*Create this table variable to hold the primary keys to transfer.*/
    insert into @table
    (id)
    select id
    from table
    where flag = 0

    insert into RemoteMachine..Database.Table
    select col1, col2, col4, ...
    from table
    where id in (select id from table)

    update table
    set flag = 1
    where id in (select id from table)
GO

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Ken011]

Denny, I will give a star for this effort so far.

Thank you.

I have 2 follow up questions, if you don't mind.

1, since the destination db is on a mainframe, I am suspecting that the db is db2, is the code you modified still valid?

2, According to your first post, you set create a flag and set it to 0.

Ok, fine. If I update it to 1 after inserting data to destination table, how does this impact the hourly updates that follow?

Remember that after the initial dump, an hourly update will be scheduled in dts to select from source table and append to destination table only record(s) that do(es) not exist in the destination table.

So, if we update the flag from the source table to 1, then we can no longer select from source those records whose flag = 0. This might be naive on my part. Please help me clarify this.

Thanks again for your efforts so far.

 

[mrdenny]

1. It should still work fine. You'll just need to change the remove table name to what ever it needs to be for the db2 database.

2. When you insert new records the flag will be set to one. You then transfer everything that has a flag of 0, then change those records to 1.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Ken011]

    insert into @table
    (id)
    select id
    from table
    where flag = 0

I am having a little problem with your logic in the above code. Perhaps, I am a little slow in the uptake but we don't control what is being inserted. In other words, a different dept has the responsibility of inserting records into the sql db. What we are trying to do is copy the records inserted since out last update.

What would be ideal is to code it using date so we can ask the script to grab anything that is added to the source db since our last run, something like:

-- Get recently changed (inserted/updated) records
SELECT *
INTO maybe a temptable or something
FROM SourceTable
WHERE LastChangeDateTime >= @LastRunDate

and @LastRunDate might be something like this:
-- Get most recent Change Date (from last batch)
SELECT @LastRunDate = = COALESCE(CONVERT(datetime, '19700101'), MyStoredLastUpdateDate)
FROM sourceTable

The 19700101 means -- if none then default to '19700101'

I have not used sql server for like ages and I am struggling with the syntax.

I am just struggling to put the whole thing together but something along these lines would really be great.

 

[mrdenny]

The problem what that approach is what is you miss a run.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com