DB2 data SQL server kantaan

Tuli eteen tilanne, jossa asiakas tarvitsee kertaluontoisen datan siiron DB2 kannasta AS400 koneelta Windows koneelle SQL Server 2008R2 kantaan. Ja luonnollisesti kertaluontoisuus tarkoittaa, että homma pitää pystyä toistamaan, mutta ei automatisoimaan – kuten ETL prosessit.

Esittelen yhden ratkaisun, joka löytyy varmasti muualtakin verkon blogi saiteilta – kuten Michelle Gutzait blogeissa linkatuista server konfiguraatioista http://www.mssqltips.com/sqlserverauthor/32/michelle-gutzait/

Lisäykseni on Michellen ratkaisuun muutos schema tiedon lisäämiseksi luotaviin tauluihin. Tällä tosin on merkistystä lähinnä SQL servereiden välillä tehtävästä siirrosta tai muiden kantojen välillä, jos halutaan määrittää schemat sql server puolella.

Ongelma joka pitäisi vielä ratkaista, on XML datan tuki tai kunnollinen virheenkorjaus, joka ohittaa mm. XML kentän kohdalla tulevan virheen ja raportoi siitä – ja jatkaa…

Linkatun Serverin tekeminen SQL servereiden välillä

AP1 koneen SQL server default instanssin Linked Server AP2 Adventure Works kantaan.

SQL servereiden välillä konfigurointi on todella suoraviivaista. Labraympäristössä voidaan vielä käyttää admin tunnuksia, joten security välilehden tunnus asetuksiinkaan eitarvitse koskea. Oikeassa ympäristössä security on yleensä asetettava tietylle user accountille.

Skripti

USE [master]
GO

/****** Object:  LinkedServer [AP2]    Script Date: 6.11.2012 9:58:25 ******/
EXEC master.dbo.sp_addlinkedserver @server = N’AP2′, @srvproduct=N’SQL Server’
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’AP2′,@useself=N’True’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’collation compatible’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’data access’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’dist’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’pub’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’rpc’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’rpc out’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’sub’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’connect timeout’, @optvalue=N’0′
EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’collation name’, @optvalue=null
EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’lazy schema validation’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’query timeout’, @optvalue=N’0′
EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’use remote collation’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server=N’AP2′, @optname=N’remote proc transaction promotion’, @optvalue=N’true’
GO

Siirtologiikka

Ensin on luotava taulu johon lähdekannan kaikki taulunimet tuodaan. SQL skripti luuppaa taulunimiä tästä taulusta ja luo ja täyttää taulut haluttuun scheemaan kohdekannassa. Tauluista tulee identtiset lähteen kanssa.

Aputaulun luonti

CREATE TABLE [dbo].[tblListOfTables](
[TableName] [varchar](200) NOT NULL,
[TableSchema] [varchar](200) NOT NULL,
[IsEnabled] [bit] NULL,
PRIMARY KEY CLUSTERED
(
[TableName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[tblListOfTables] ADD  DEFAULT ((1)) FOR [IsEnabled]
GO

Näppärin tapa siirtää data, on ajaa seuraava kysely linkatussa kyselyssä ja ottaa SQL Server management studiosta copy / pastella data talteen.

SQL Kysely:

SELECT TABLE_SCHEMA, TABLE_NAME FROM ap2.AdventureWorks.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN ( SELECT TABLE_NAME FROM ap2.AdventureWorks.INFORMATION_SCHEMA.COLUMNS)

Tulos:

Kohdetaulu:

muuta editoitavat 200 sellaiseksi että varmasti riittää esim: 2000 a pista tarpeeton enabled kenttä kyselystä.

SELECT     TOP (200) TableName, TableSchema, IsEnabled
FROM         tblListOfTables

Aja kysely uudelleen:

Liitä data:

Huomioi, että kenttäjärjestys tulee olla sama lähteessä ja kohteessa!

toimii muuten, mutta virheenkorjauksen vuoksi siirto loppuu ekaan XML datakenttään. Tämän voi korjata SQL skriptissä.

Ja itse SQL skripti on tässä:

Tarvittavat muuttujat:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @Loop INT,
@cmd VARCHAR(MAX),
@rc INT,
@TB VARCHAR(130),
@SN VARCHAR(130)

Temppi taulu ja siihen juokseva numerointi kenttä luuppia varten:

SELECT ROWID = IDENTITY(INT,1,1), TableName, TableSchema
INTO #Tables
FROM tblListOfTables
WHERE IsEnabled = 1
ORDER BY TableName

Loopin initialisointi:

SET @rc = @@ROWCOUNT

SET  @Loop = 1

— Looping on table names, dropping and recreating each:
WHILE @Loop <= @rc
BEGIN
SELECT @TB = TableName, @SN = TableSchema FROM #Tables WHERE ROWID = @Loop

SET @cmd = ”

Datan ja taulujen ‘siirto’:

SELECT @cmd = ’IF EXISTS (SELECT 1 FROM sys.objects WHERE Type = ”U”’ +
’AND name = ”’ + @TB  + ”’) DROP TABLE [’ + @TB + ’];’ +
’SELECT * INTO  [’ + @TB  + ’] ’ +
’FROM AP2.[AdventureWorks].[’+ @SN +’].[’ + @TB  + ’]’

EXEC (@cmd)
SET @Loop = @Loop + 1
END

Temppi taulun poisto:

DROP TABLE #Tables
GO