Siirry sisältöön

DB2 data SQL server kantaan

marraskuu 6, 2012

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ä

image

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

image

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.

image

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

image

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:

image

Kohdetaulu:

image

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

image

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

Aja kysely uudelleen:

image

image

Liitä data:

image

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

image

image

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

image

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

From → BI / DW, ETL, SQL Server

One Comment
  1. Zege permalink

    Jees, itse olen jossain määrin hylännyt ssis-paketit iSeries-maailmasta tehtävistä tiedonsiirroista. Linked Serverillä ja dynaamisella Sql:llä siirtorutiinin rakentaa nopeammin… Näppärä skripti.

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out /  Muuta )

Google photo

Olet kommentoimassa Google -tilin nimissä. Log Out /  Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out /  Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out /  Muuta )

Muodostetaan yhteyttä palveluun %s

%d bloggers like this: